SGShardedCluster: a query router added after a reference table exists is never activated since citus-update-nodes replicates the reference tables in the transaction that activated it

Summary

Since 1.19.2 (#3245 (closed)) the query routers of a citus SGShardedCluster are registered in pg_dist_node as inactive placeholders before their Patroni starts. The coordinator SGScript entry citus-update-nodes (executed every SGShardedCluster.spec.configurations.citus.updateNodeInterval) then activates them. That entry activates the node and, in the same DO block, calls replicate_reference_tables('block_writes'). Citus refuses that call when the current transaction has already modified pg_dist_node:

ERROR: cannot replicate reference tables in a transaction that modified node metadata
  Where: SQL statement "SELECT replicate_reference_tables('block_writes')"
  PL/pgSQL function inline_code_block line 74 at PERFORM

The DO block is a single transaction, so the activation is rolled back with it, and the entry fails the same way at each execution. A query router added (by increasing coordinator.queryRouterClusters) after a reference table exists is never activated.

The failure is silent: the query router Pods are Running and Ready, the SGShardedCluster reports nothing and no event is emitted. It only shows in the coordinator SGCluster status, under status.managedSql.scripts[].scripts[] of the citus-update-nodes entry (failureCode: 0A000).

Reported on StackGres 1.19.2, PostgreSQL 18.4, Citus 14.1.0.

Steps to reproduce

  1. Create a type: citus SGShardedCluster with coordinator.queryRouterClusters: 1. The first query router is activated, since no reference table exists yet.
  2. Create a distributed table and at least one reference table (create_reference_table).
  3. kubectl patch sgshardedcluster <name> --type merge -p '{"spec":{"coordinator":{"queryRouterClusters":2}}}'
  4. The new query router gets its start gate annotation, its Patroni starts and replaces the placeholder host in pg_dist_node with the Pod address, but the node stays with isactive = false and hasmetadata = false forever.

Cause

stackgres-k8s/src/operator/src/main/resources/citus/citus-update-nodes.sql does, in one DO $$ ... $$:

    PERFORM citus_activate_node(node.nodename, node.nodeport);
    activated := true;
  ...
  IF activated THEN
    PERFORM replicate_reference_tables('block_writes');
  END IF;

citus_activate_node modifies pg_dist_node and EnsureReferenceTablesExistOnAllNodesExtended (src/backend/distributed/utils/reference_table_utils.c) errors out when TransactionModifiedNodeMetadata is set, to avoid a deadlock with the metadata sync of the shard copy.

The replication is needed: citus_activate_node does not copy the reference tables to the node (after activating it by hand the new group had no placement of any reference table). It only needs to run in a separate transaction: executed as two statements, citus_activate_node and then replicate_reference_tables, both succeed.

Side effects of the failure, since the cluster-controller stops executing the entries of an SGScript after the first one that fails (SGScript.spec.continueOnError is not set for the generated one):

  • the rest of citus-update-nodes is rolled back too (the shouldhaveshards fix-up, the registration of further placeholder groups, the node auto-removal);
  • the entries that follow it are not executed: citus-query-routers-without-shards (that feeds the start gate of the query routers), citus-registered-groups and citus-update-poolinfo;
  • any SGScript referenced after the generated one in SGShardedCluster.spec.coordinator.managedSql.scripts is not executed either, unless continueOnSGScriptError is true.

Workaround

Reference from the coordinator a user SGScript with two scheduled entries, each a separate DO block and so a separate transaction (each script entry is executed on its own connection): the first activates the query routers that can be reached, the second copies the reference tables to the active nodes that miss them. They have the same schedule as citus-update-nodes (0/10 * * * * ? for the default updateNodeInterval of 10 seconds) and are executed right after it.

continueOnSGScriptError: true is required: the generated SGScript keeps failing until the node is activated, and without it the cluster-controller would stop before reaching the user SGScript. Once the query router is active citus-update-nodes finds nothing to activate and succeeds again at the next execution.

Replace <namespace>, <name> (the SGShardedCluster name) and <database> (SGShardedCluster.spec.database). 1024 is the default coordinator.queryRouterIndexOffset; use its value if it is set.

apiVersion: stackgres.io/v1
kind: SGScript
metadata:
  namespace: <namespace>
  name: <name>-activate-query-routers
spec:
  scripts:
  - name: activate-query-routers
    database: <database>
    cron: "0/10 * * * * ?"
    script: |
      DO $$
      DECLARE
        node record;
      BEGIN
        PERFORM set_config('citus.node_connection_timeout', '5000', true);
        FOR node IN
          SELECT nodename, nodeport FROM pg_dist_node
          WHERE NOT isactive AND NOT hasmetadata AND noderole = 'primary'
          AND groupid > 1024
        LOOP
          IF citus_check_connection_to_node(node.nodename, node.nodeport) THEN
            PERFORM citus_activate_node(node.nodename, node.nodeport);
          END IF;
        END LOOP;
      END$$;
  - name: replicate-reference-tables
    database: <database>
    cron: "0/10 * * * * ?"
    script: |
      DO $$
      BEGIN
        IF EXISTS (
          SELECT FROM pg_dist_node
          CROSS JOIN pg_dist_partition
          WHERE pg_dist_node.isactive AND pg_dist_node.noderole = 'primary'
          AND pg_dist_partition.partmethod = 'n' AND pg_dist_partition.repmodel = 't'
          AND NOT EXISTS (
            SELECT FROM pg_dist_shard
            JOIN pg_dist_placement USING (shardid)
            WHERE pg_dist_shard.logicalrelid = pg_dist_partition.logicalrelid
            AND pg_dist_placement.groupid = pg_dist_node.groupid)) THEN
          PERFORM replicate_reference_tables('block_writes');
        END IF;
      END$$;

Then reference it from the coordinator. A merge patch replaces the whole scripts list, so include any entry already present in spec.coordinator.managedSql.scripts:

kubectl patch sgshardedcluster -n <namespace> <name> --type merge -p '{
  "spec": {
    "coordinator": {
      "managedSql": {
        "continueOnSGScriptError": true,
        "scripts": [{"sgScript": "<name>-activate-query-routers"}]
      }
    }
  }
}'

Notes:

  • replicate_reference_tables is only called when an active node misses a placement of a reference table, so the entry does not block the writes to the reference tables at each execution.
  • 'block_writes' blocks the writes to every reference table while they are copied, at each activation of a query router. On clusters with large reference tables this is a visible write pause per scale-up.
  • continueOnSGScriptError: true also lets the remaining SGScripts of the coordinator be executed when another one fails. Remove the user SGScript and the flag once upgraded to a version with the fix.
  • A one-off alternative is to run, on the coordinator primary, as two separate statements: SELECT citus_activate_node('<query router Pod IP>', 5432); and then SELECT replicate_reference_tables('block_writes');. It has to be repeated at each scale-up.

Proposed solution

Split the replication out of citus-update-nodes into a new entry of the generated coordinator SGScript, executed right after it with the same cron, with the guard used in the workaround ("some active primary node lacks a placement of some reference table"). Being a separate entry it runs in its own transaction, and it also copies the reference tables to a node activated by someone else.

Add an e2e check that increases queryRouterClusters after a reference table is created and waits for the new query router to be active and to hold the reference table placements.

Document the write pause caused by 'block_writes' when a query router is added.