[BUG]: Multi column foreign key columns are sorted individually on introspection (critical bug) #3993
Comments
|
Possibly related #3238 Introspect after push gives N.B.
vs the original
The introspected version is entirely wrong. I suspect the introspection code is responsible for building the tree used for the diff, and that’s where the critical failure occurs. The two arrays should be sorted based on the indices of the first array while maintaining their original pairings. For example: Input: Expected Sorting: Incorrect (current) Outcome: Perhaps there is a better mechanism to describe these also, rather than relying on the pairing of two distinct arrays. foreignKey({
name: "whatever_fk",
references: [
{column: table.blah, foreignColumn: other.blah},
{column: table.otherBlah, foreignColumn: other.otherBlah},
]
})or better foreignKey({
name: 'whatever_fk',
columns: [
[table.ownerId, userProfileImages.ownerId],
[table.profileImageId, userProfileImages.id],
],
}).onDelete('cascade')or even better foreignKey('whatever_fk')
.from(table.blah).to(other.blah)
.from(table.otherBlah).to(other.otherBlah)
.onDelete('cascade') |
Mardoxx
changed the title
|
Related #3764 ? |
Mardoxx
changed the title
|
drizzle-orm/drizzle-kit/src/serializer/pgSerializer.ts Lines 1259 to 1324 in 49d2930 I don't think this query is correct. 
With this our columnsFrom and columnsTo arrays end up as When Setted these become [owner_id, profile_image_id] and [id, owner_id] We need.. Are some of the rows in that statement from the unique index? |
|
The issue is caused by how pg_constraint.conkey and pg_constraint.confkey store column references as arrays. When joined with pg_attribute, the query results in Cartesian-like duplication, leading to incorrect mappings. SELECT
con.contype AS constraint_type,
nsp.nspname AS constraint_schema,
con.conname AS constraint_name,
rel.relname AS table_name,
att.attname AS column_name,
fnsp.nspname AS foreign_table_schema,
frel.relname AS foreign_table_name,
fatt.attname AS foreign_column_name,
CASE con.confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS update_rule,
CASE con.confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS delete_rule
FROM
pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace
JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid
JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace
CROSS JOIN LATERAL unnest(con.conkey, con.confkey) AS colnum(ref_colnum, foreign_colnum)
JOIN pg_catalog.pg_attribute att ON att.attnum = colnum.ref_colnum AND att.attrelid = con.conrelid
JOIN pg_catalog.pg_attribute fatt ON fatt.attnum = colnum.foreign_colnum AND fatt.attrelid = con.confrelid
WHERE
nsp.nspname = '${tableSchema}'
AND rel.relname = '${tableName}'
AND con.contype IN ('f');
no cap bussin frfr similar fixes needed elsewhere most likely |
SELECT
con.contype AS constraint_type,
nsp.nspname AS constraint_schema,
con.conname AS constraint_name,
rel.relname AS table_name,
att.attname AS column_name,
fnsp.nspname AS foreign_table_schema,
frel.relname AS foreign_table_name,
fatt.attname AS foreign_column_name,
CASE con.confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS update_rule,
CASE con.confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS delete_rule
FROM
pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace
JOIN LATERAL unnest(con.conkey, con.confkey) WITH ORDINALITY AS fk_map(attnum, fattnum, ord) ON TRUE
LEFT JOIN pg_catalog.pg_attribute att
ON att.attnum = fk_map.attnum AND att.attrelid = con.conrelid
LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid
LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace
LEFT JOIN pg_catalog.pg_attribute fatt
ON fatt.attnum = fk_map.fattnum AND fatt.attrelid = con.confrelid
WHERE
nsp.nspname = '${tableSchema}'
AND rel.relname = '${tableName}'
AND con.contype IN ('f');
ORDER BY ord;
Better. |
SELECT
con.contype AS constraint_type,
nsp.nspname AS constraint_schema,
con.conname AS constraint_name,
rel.relname AS table_name,
att.attname AS column_name,
fnsp.nspname AS foreign_table_schema,
frel.relname AS foreign_table_name,
fatt.attname AS foreign_column_name,
CASE con.confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS update_rule,
CASE con.confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'n' THEN 'SET NULL'
WHEN 'c' THEN 'CASCADE'
WHEN 'd' THEN 'SET DEFAULT'
END AS delete_rule
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_class rel
ON rel.oid = con.conrelid
JOIN pg_catalog.pg_namespace nsp
ON nsp.oid = con.connamespace
-- Important part: unnest con.conkey and con.confkey in parallel by index
-- The subscript "s(i)" will range from 1..array_length(conkey)
CROSS JOIN generate_subscripts(con.conkey, 1) AS s(i)
LEFT JOIN pg_catalog.pg_attribute att
ON att.attrelid = con.conrelid
AND att.attnum = con.conkey[s.i]
LEFT JOIN pg_catalog.pg_class frel
ON frel.oid = con.confrelid
LEFT JOIN pg_catalog.pg_namespace fnsp
ON fnsp.oid = frel.relnamespace
LEFT JOIN pg_catalog.pg_attribute fatt
ON fatt.attrelid = con.confrelid
AND fatt.attnum = con.confkey[s.i]
WHERE nsp.nspname = '${tableSchema}'
AND rel.relname = '${tableName}'
AND con.contype IN ('f')Up to you to decide if you want to This should work for older version of postgres. |
|
This does indeed fix it, someone needs to do a PR now. |
|
Okay done it for you |
Prior title: Composite/multi column foreign key constraints keep getting dropped and created
Report hasn't been filed before.
What version of
drizzle-ormare you using?0.30.2
What version of
drizzle-kitare you using?0.38.4
Other packages
No response
Describe the Bug
Run
drizzle-kit pushtwice on this and it will keep doing:If you try using
It tries to drop non null values in the
idfieldSee below comment for why this happens.
The text was updated successfully, but these errors were encountered: