--defer-constraints defers constraints on all schemas, not just the ones being synced

[philefstat]

Hi there, first of all, thanks for this very useful tool - have been using it for years and it's been very helpful in many different roles.

For context, we're using Supabase which acts as our postgres host and does not provide super user privilege. So far this hasn't been an issue but we recently added a self-referential table which means we need to defer constraints.

Using the command pgsync --schemas public --debug --defer-constraints results in the following output:

From: [redacted]
To: postgres on localhost:54322
[from] SELECT unnest(current_schemas(true)) AS schema
[from] SELECT table_schema AS schema, table_name AS table FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY 1, 2
[to] SELECT table_schema AS schema, table_name AS table FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY 1, 2
[from] SELECT table_schema AS schema, table_name AS table, column_name AS column, data_type AS type FROM information_schema.columns WHERE is_generated = 'NEVER' ORDER BY 1, 2, 3
[to] SELECT table_schema AS schema, table_name AS table, column_name AS column, data_type AS type FROM information_schema.columns WHERE is_generated = 'NEVER' ORDER BY 1, 2, 3
[to] SELECT nspname AS schema, relname AS table, pg_attribute.attname AS column, format_type(pg_attribute.atttypid, pg_attribute.atttypmod), pg_attribute.attnum, pg_index.indkey FROM pg_index, pg_class, pg_attribute, pg_namespace WHERE indrelid = pg_class.oid AND pg_class.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary
[from] SELECT nt.nspname as schema, t.relname as table, a.attname as column, n.nspname as sequence_schema, s.relname as sequence FROM pg_class s INNER JOIN pg_depend d ON d.objid = s.oid INNER JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid INNER JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) INNER JOIN pg_namespace n ON n.oid = s.relnamespace INNER JOIN pg_namespace nt ON nt.oid = t.relnamespace WHERE s.relkind = 'S'
[to] SELECT nt.nspname as schema, t.relname as table, a.attname as column, n.nspname as sequence_schema, s.relname as sequence FROM pg_class s INNER JOIN pg_depend d ON d.objid = s.oid INNER JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid INNER JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) INNER JOIN pg_namespace n ON n.oid = s.relnamespace INNER JOIN pg_namespace nt ON nt.oid = t.relnamespace WHERE s.relkind = 'S'
[to] BEGIN
[to] SELECT table_schema AS schema, table_name AS table, constraint_name FROM information_schema.table_constraints WHERE constraint_type = 'FOREIGN KEY' AND is_deferrable = 'NO'
[to] ALTER TABLE "storage"."objects" ALTER CONSTRAINT "objects_bucketId_fkey" DEFERRABLE
[to] ALTER TABLE "pgsodium"."key" ALTER CONSTRAINT "key_parent_key_fkey" DEFERRABLE
/opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/data_source.rb:142:in `exec_params': ERROR:  must be owner of table key (PG::InsufficientPrivilege)
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/data_source.rb:142:in `execute'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:277:in `block (3 levels) in maybe_defer_constraints'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:276:in `each'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:276:in `block (2 levels) in maybe_defer_constraints'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:275:in `each'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:275:in `block in maybe_defer_constraints'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/data_source.rb:151:in `block in transaction'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pg-1.4.6/lib/pg/connection.rb:262:in `transaction'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/data_source.rb:150:in `transaction'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:272:in `maybe_defer_constraints'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:244:in `run_tasks'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/table_sync.rb:28:in `perform'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/sync.rb:67:in `perform'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/client.rb:26:in `perform'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/lib/pgsync/client.rb:35:in `start'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/gems/pgsync-0.7.4/exe/pgsync:7:in `<top (required)>'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/bin/pgsync:23:in `load'
        from /opt/homebrew/Cellar/pgsync/0.7.4_1/libexec/bin/pgsync:23:in `<main>'

Is it possible to limit deferring constraints to only the schemas being synced?

[ankane]

Hi @philefstat, thanks for the suggestion. I think it should be possible to only defer necessary constraints, but since foreign keys can cascade and span multiple schemas, requires a bit of complexity. Will try to find an approach that works.

[philefstat]

@ankane Thank you for the quick reply. Yeah it sounds like it could become quite complex, hadn't thought about the keys cascading / spanning multiple schemas. We've found an alternative solution in the mean time (a bit of schema restructuring). Happy to close the issue if you'd like.

[ankane]

Sounds good. Added to ideas (#93).