Skip to content

Checking the Node Number

This recipe shows what happens when the snowflake.node setting is not set. Every Snowflake ID carries the number of the node that made it, so the extension refuses to make IDs on a server whose node is 0, and the error tells you that the node is not set.

These examples are for Snowflake 2.6.0.

The example reads the setting, sets it to 0 for one transaction to cause the error, and then shows that the setting is back to its original value. The output looks like this on a server whose node is 1:

Node: 1
Node inside the transaction: 0
nextval() says: value for snowflake.node is not set
Node after the rollback: 1
nextval() says: ok

Preparing the Ingredients

The example needs the same setup as the Generating Snowflake IDs recipe. Changing snowflake.node needs a superuser, or a role that has been granted SET on the parameter with GRANT SET ON PARAMETER snowflake.node TO role_name (PostgreSQL 15 and later), because the setting has the context superuser.

Reading the Setting

The current_setting() function reads the node number. The following statement prints it:

-- The node number identifies this server in every ID it makes.
SELECT 'Node: ' || current_setting('snowflake.node');

Catching the Error

The snowflake.nextval() function raises an error when the node is 0. The following statement creates a helper function in the temporary schema that catches the error and returns its message, so that the program can print the message and carry on:

-- A helper in the temporary schema that returns the error message from
-- snowflake.nextval(), or ok when the call works.
CREATE FUNCTION pg_temp.nextval_message() RETURNS text
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM snowflake.nextval();
    RETURN 'ok';
EXCEPTION WHEN OTHERS THEN
    RETURN SQLERRM;
END;
$$;

Unsetting the Node for One Transaction

The SET LOCAL command changes the setting only until the end of the transaction, so the server keeps its real node number. The following statements set the node to 0, call the helper, and roll back:

-- Node 0 means that the node is not set. SET LOCAL lasts only until the end
-- of the transaction, so the server setting is untouched; the setting
-- needs a superuser to change.
BEGIN;
SET LOCAL snowflake.node = 0;
SELECT 'Node inside the transaction: ' || current_setting('snowflake.node');
SELECT 'nextval() says: ' || pg_temp.nextval_message();
ROLLBACK;

Checking That the Setting Is Back

After the rollback, the node has its original value and nextval() works again. The following statements confirm both:

-- The original value is back, and IDs work again.
SELECT 'Node after the rollback: ' || current_setting('snowflake.node');
SELECT 'nextval() says: ' || pg_temp.nextval_message();

Setting the Node on a Server

Each server in a cluster needs a different node number, from 1 to 1023. A managed pgEdge deployment assigns snowflake.node for you, and the value comes from each node's place in the cluster:

  • The Control Plane sets snowflake.node from the node ordinal.
  • The pgEdge Helm charts derive snowflake.node from each node name.
  • The pgEdge Ansible playbooks set snowflake.node from the node zone.

When you deploy with one of these methods, no further action is needed. For a self-managed server, set the value in postgresql.conf, or run ALTER SYSTEM SET snowflake.node = 2 and then SELECT pg_reload_conf(). A value given on the postgres command line takes precedence over both.

Running the Example

The complete program is available in the cookbook repository. Set the PG* environment variables for your server, and then run the following command from the sql directory:

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Viewing the Complete Program

The following block shows the complete program:

-- Ellie's Postgres Cookbook
-- Checking the Snowflake node number, and what happens when it is not set.
--
-- Copyright (c) 2026, pgEdge, Inc.
-- This code is released under the PostgreSQL License.

\pset format unaligned
\pset tuples_only on

-- The node number identifies this server in every ID it makes.
SELECT 'Node: ' || current_setting('snowflake.node');

-- A helper in the temporary schema that returns the error message from
-- snowflake.nextval(), or ok when the call works.
CREATE FUNCTION pg_temp.nextval_message() RETURNS text
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM snowflake.nextval();
    RETURN 'ok';
EXCEPTION WHEN OTHERS THEN
    RETURN SQLERRM;
END;
$$;

-- Node 0 means that the node is not set. SET LOCAL lasts only until the end
-- of the transaction, so the server setting is untouched; the setting
-- needs a superuser to change.
BEGIN;
SET LOCAL snowflake.node = 0;
SELECT 'Node inside the transaction: ' || current_setting('snowflake.node');
SELECT 'nextval() says: ' || pg_temp.nextval_message();
ROLLBACK;

-- The original value is back, and IDs work again.
SELECT 'Node after the rollback: ' || current_setting('snowflake.node');
SELECT 'nextval() says: ' || pg_temp.nextval_message();

Next Steps

The following resources provide more information about the topics in this recipe: