You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When trying to get my production database data into my local development database using npx supabase db dump -f supabase/seed.sql --data-only command I get the following error:
Dumping data from remote database...
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: detail: key
pg_dump: hint: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump to avoid this problem.
Upon researching I found out that pgsodiumis causing the issue. I don't understand why this it is not mentioned in the docs as supabase comes with pgsoodium by default.
I tried to identify the circular dependency with multiple different SQL queries (below), but only ended up being able to identify pgsodium for causing it.
This is hilarious as the supabse cli seems not to provide a way to dump the data and the schema together but only after each other which makes it useless. So I have to use Postgres native functions anyway as it seems like the only solution is to dump the schema and data in one go or ignore triggers. Link to the supabase docs here
Or am I missing something here?
What would be the best solution to this problem?
My ways to identify problems with circular foreign key relationships:
-- Query to List Foreign Key RelationshipsSELECTtc.constraint_name,
tc.table_nameAS child_table,
kcu.column_nameAS child_column,
ccu.table_nameAS parent_table,
ccu.column_nameAS parent_column
FROMinformation_schema.table_constraintsAS tc
JOINinformation_schema.key_column_usageAS kcu
ONtc.constraint_name=kcu.constraint_nameJOINinformation_schema.constraint_column_usageAS ccu
ONccu.constraint_name=tc.constraint_nameWHERE constraint_type ='FOREIGN KEY';
-- Automated Cycle Detection
WITH RECURSIVE fk_cycles AS (
SELECTkcu1.table_nameAS child_table,
ccu1.table_nameAS parent_table,
ARRAY[kcu1.table_name] ASpath,
false AS cycle
FROMinformation_schema.table_constraints tc1
JOINinformation_schema.key_column_usage kcu1
ONtc1.constraint_name=kcu1.constraint_nameJOINinformation_schema.constraint_column_usage ccu1
ONtc1.constraint_name=ccu1.constraint_nameWHEREtc1.constraint_type='FOREIGN KEY'UNION ALLSELECTkcu2.table_nameAS child_table,
ccu2.table_nameAS parent_table,
path||kcu2.table_name,
kcu2.table_name= ANY(path) AS cycle
FROM
fk_cycles
JOINinformation_schema.table_constraints tc2
ONfk_cycles.parent_table=tc2.table_nameJOINinformation_schema.key_column_usage kcu2
ONtc2.constraint_name=kcu2.constraint_nameJOINinformation_schema.constraint_column_usage ccu2
ONtc2.constraint_name=ccu2.constraint_nameWHEREtc2.constraint_type='FOREIGN KEY'AND NOT cycle
)
SELECT*FROM fk_cycles WHERE cycle;
-- Identify foreign key constraints where a table is referencing itself—that is, self-referencing foreign keysSELECT conrelid::regclass AS table_name, conname AS constraint_name
FROM pg_constraint
WHERE contype ='f'AND conrelid = confrelid;
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
When trying to get my production database data into my local development database using
npx supabase db dump -f supabase/seed.sql --data-only
command I get the following error:Upon researching I found out that
pgsodium
is causing the issue. I don't understand why this it is not mentioned in the docs as supabase comes with pgsoodium by default.I tried to identify the circular dependency with multiple different SQL queries (below), but only ended up being able to identify pgsodium for causing it.
This is hilarious as the supabse cli seems not to provide a way to dump the data and the schema together but only after each other which makes it useless. So I have to use Postgres native functions anyway as it seems like the only solution is to dump the schema and data in one go or ignore triggers. Link to the supabase docs here
Or am I missing something here?
What would be the best solution to this problem?
My ways to identify problems with circular foreign key relationships:
Beta Was this translation helpful? Give feedback.
All reactions