"relation is not distributed" on worker after upgrade

[crabhi]

Hi, I did two upgrades recently (Citus 11.1 -> 11.2, then PG 14 -> 15).

The database works mostly normally but I experience a strange behaviour with some queries. While this one works:

SELECT get_shard_id_for_distribution_column('my_table', 8);
 get_shard_id_for_distribution_column 
--------------------------------------
                               102008
(1 row)

Using it in a larger query fails:

SELECT get_shard_id_for_distribution_column('my_table', id), id FROM my_table ORDER BY id DESC LIMIT 10;
ERROR:  relation is not distributed
CONTEXT:  while executing command on worker-a:6006

Connecting to the worker directly, I can see all the tables from metadata as well as the shard tables (e.g. my_table_102008) even though citus.show_shards_for_app_name_prefixes is empty.

Where can I look further to figure out what's wrong?

[crabhi]

Today, I hit another error while trying to add nodes to the Citus cluster. I can't rebalance shards because I hit:

postgres@[local]:5432/monitora_db=# SELECT replicate_reference_tables();
NOTICE:  replicating reference table 'media_monitor_publisher' to xxxxxxxxxxx ...
ERROR:  could not find valid entry for shard 102136
CONTEXT:  while executing command on xxxxxxxxxxxxxx
while executing command on localhost:5432

[crabhi]

OK, so the issue was that I misread the PostgreSQL upgrade instructions and ran citus_prepare_pg_upgrade() only on the coordinator node. This resulted in empty pg_dist_... tables.

This is how I fixed it:

1. Restore all workers to a point just before upgrade.

2. Run SELECT citus_prepare_pg_upgrade() on each restored node.

3. Create a fixup script (on each restored node).

   (          
   cat <<EOF    
       -- copied from citus_prepare_pg_upgrade()
       DELETE FROM pg_depend WHERE
           objid IN (SELECT oid FROM pg_proc WHERE proname = 'array_cat_agg') AND
           refobjid IN (select oid from pg_extension where extname = 'citus');
   
       DROP AGGREGATE IF EXISTS array_cat_agg(anyarray);
       DROP AGGREGATE IF EXISTS array_cat_agg(anycompatiblearray);
   EOF
   
   pg_dump  \
     -t public.pg_dist_partition \
     -t public.pg_dist_shard \
     -t public.pg_dist_placement \
     -t public.pg_dist_node_metadata \
     -t public.pg_dist_local_group \
     -t public.pg_dist_transaction \
     -t public.pg_dist_colocation \
     -t public.pg_dist_poolinfo \
     -t public.pg_dist_rebalance_strategy \
     -t public.pg_dist_object \
     -t public.pg_dist_cleanup \
     -t public.pg_dist_clock_logical_seq
    
   # In our case we don't need data for all the tables.
   #   
   # - pg_dist_node is already filled by Patroni
   # - pg_dist_authinfo is managed by our automation
   pg_dump --schema-only \
     -t public.pg_dist_node \
     -t public.pg_dist_authinfo 
   
   echo 'SELECT citus_finish_pg_upgrade();'
   )  > worker_$PGPORT.sql
   

4. Apply these scripts on each worker node of the main cluster. The scripts should differ only in pg_dist_local_group and pg_dist_node_metadata values.