When using schema-based sharding, standalone sequences not propagated to workers after created on the coordinator

[lefteris-tsallas-leanix]

Hello!

I am using schema-based sharding in Citus version 13.2.0.

I noticed that sequences owned by columns (e.g., from BIGSERIAL) are propagated to workers, but standalone sequences created with CREATE SEQUENCE are not.

You can easily reproduce this behavior with the following steps:

 CREATE SCHEMA tenant_abc;                                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                           
 -- Table with BIGSERIAL (creates an owned sequence automatically)                                                                                                                                                                                                                                                         
 CREATE TABLE tenant_abc.orders (                                                                                                                                                                                                                                                                                          
     id BIGSERIAL PRIMARY KEY,                                                                                                                                                                                                                                                                                             
     description TEXT,                                                                                                                                                                                                                                                                                                     
     amount NUMERIC                                                                                                                                                                                                                                                                                                        
 );                                                                                                                                                                                                                                                                                                                        
                                                                                                                                                                                                                                                                                                                           
 -- Standalone bigint sequence (not owned by any column)                                                                                                                                                                                                                                                                          
 CREATE SEQUENCE tenant_abc.custom_sequence_standalone;                                                                                                                                                                                                                                                                    

After connecting to the worker, we can verify that orders_id_seq exists there but the standalone sequence does not.

I've also noticed some inconsistent behavior:

• I can create a sequence directly on the worker node, whereas I would expect the CREATE SEQUENCE DDL to fail as it does for tables ❓
• Renaming orders_id_seq on the coordinator propagates the name change to the worker. ✅
• Renaming orders_id_seq directly on the worker throws an error as expected, stating that you cannot run DDL commands on worker nodes. ✅
  Is this expected behavior or a bug?

If standalone sequences are not supported in distributed schemas, I would expect an error to be thrown when creating a standalone sequence on the coordinator within a distributed schema or when creating a sequence directly on a worker node

[onurctirtir]

Yes, this is expected. We don't propagate sequences to other nodes unless a distributed object depends on them, like a distributed table or a schema-distributed table.

And the reason why we don't disallow this on workers is the assumption that, e.g., it may be later used by a local table on that node. And this also applies to the sequences created at schemas used for schema-based-sharding. This is because, except that we treat the tables created at those schemas as schema-distributed tables, we treat the objects created at those schemas just as node-local objects unless a distributed object depends on them, as it's the case for other schemas.

From users' perspective, would you prefer sequences created at those schemas to be automatically distributed as well, or do you think not allowing this (at schemas used for schema-based sharding) would make more sense?

Side-node: If we automatically distributed such sequences, we would probably assign different sequence min / max ranges for them on workers as we do for the sequences that we automatically propagate to all nodes.

[lefteris-tsallas-leanix]

Thanks for your response!

I can only speak about schema-based sharding. In a multi-tenant SaaS app, all queries are scoped to a single tenant.
If a sequence object (or any other object created) is not distributed, then all DML commands using the object will fail when querying from any node.I cannot think of a use case for a local-node object inside the schema in a multi-tenant app.

Regarding your question, I would prefer sequences to not be allowed by default, but rather distributed with an explicit option if they come with some limitations.I think this is the case for triggers.
If the limitations are also properly documented alongside that option, users can identify potential incompatibilities with multi-tenant SaaS apps early and decide whether, for example, the max/min limitations of a distributed sequence are blockers—or consider workarounds.

I would also expect all DDL commands to fail on worker nodes, regardless of the object type being created in a distributed schema.

[onurctirtir]

Hey @lefteris-tsallas-leanix, thank you for your responses, it’s super useful to hear about user-side expectations.

I see two feature requests here - I’ll take note of them so we can evaluate them within the team:

1. Consider disallowing the creation of local objects in schemas used for schema-based sharding, especially via the worker nodes.
2. Consider propagating sequences created in schemas used for schema-based sharding, even when no distributed objects (= distributed tables) depend on them, via a GUC. This would need to be gated behind a GUC because we would assign different sequence min/max ranges on workers, and this may not be the "fully-desired" user experience.