pglogical.replicate_ddl_command(...) can fail to apply due to user rights differences

[ringerc]

If a superuser invokes pglogical.replicate_ddl_command they can do things like alter tables that they do not own. Fine.

If the same user name exists on the downstream but is not a superuser, applying the DDL will fail with something like ERROR: must be owner of relation ....

The issue isn't confined to superuser rights though. I think it can also occur when a a role on the upstream has been GRANTed roles that it hasn't been on the downstream. Possibly other cases too.

It'd be nice to help users understand what's going on here, even if we can't fix it. Unsure if we can do more than document it though...

(See RT#46213)

[ringerc]

Maybe just docs improvements to discuss general case that:

You can have different users, different rights (within limits), replicate subsets of databases and tables, and even do things like transform replicated data to mask rows or whatever. But on the flip side you have to ensure that you keep the schema compatible on upstream and downstream, ensure users on upstream exist on downstream, etc

[ringerc]

Also need to warn about DDL that uses volatile DEFAULTs like nextval etc. Especially when adding a primary key!

[ringerc]

Example written for internal

Imagine I queue for replication the following command

ALTER TABLE my_table ADD COLUMN rand float not null default random();

Am I going to get the same values in the column when it executes on the upstream and downstream?

No, because we generate different random numbers on upstream and downstream.

OK, and now if I change random() to nextval('some_seq') is it OK? Assume that the sequence has the same start value, increment, etc.

It's actually still unsafe, because ALTER TABLE rewrites the table in physical on-disk order. It doesn't ORDER BY any particular column. Since the physical row order is probably different on upstream and downstream, it's likely you'll get different rows assigned different values from the sequence even if they start at the same point.

There's no ORDER BY. And we don't support one in ALTER TABLE, either. When there's no ORDER BY, PostgreSQL usually does things in the underlying physical table order. Since the physical table row order is probably different between upstream and downstream (since the downstream is maintained by a bunch of individual row inserts/updates/deletes), the rows likely get assigned values from the sequence in a different order.