Hi Mark and others,
On Sun, Jan 24, 2021, at 09:22, Mark Rofail wrote:
> Changelog:
> - v13 (compatible with current master 2021-01-24, commit
> 7e57255f6189380d545e1df6a6b38827b213e3da)
...
> I encourage everyone to take review this patch. After considerable reviews
> and performance testing, it will be ready for a commitfest.
...
> Array-ELEMENT-foreign-key-v13.patch
This is awesome, a million thanks for this!
I've tested the patch and tried to use it in the pg_catalog-diff-tool I'm
working on.
I found one problem, described in Test #3 below.
*** Test #1 OK: Multi-key FK on (oid, smallint[])
Find a suitable row to do testing on:
joel=# SELECT oid,conrelid,conkey FROM catalog_clone.pg_constraint WHERE
cardinality(conkey) > 1 LIMIT 1;
oid | conrelid | conkey
-------+----------+----------
12112 | 1255 | {2,20,3}
(1 row)
Corrupting the row will not be detected since no FK yet:
joel=# UPDATE catalog_clone.pg_constraint SET conkey = '{2,20,3,1234}' WHERE
oid = 12112;
UPDATE 1
Trying to add a FK now will detect the corrupted row:
joel=# ALTER TABLE catalog_clone.pg_constraint ADD FOREIGN KEY (conrelid, EACH
ELEMENT OF conkey) REFERENCES catalog_clone.pg_attribute (attrelid, attnum);
ERROR: insert or update on table "pg_constraint" violates foreign key
constraint "pg_constraint_conrelid_conkey_fkey"
DETAIL: Key (conrelid, EACH ELEMENT OF conkey)=(1255, {2,20,3,1234}) is not
present in table "pg_attribute".
OK, good, we got an error.
Fix row and try again:
joel=# UPDATE catalog_clone.pg_constraint SET conkey = '{2,20,3}' WHERE oid =
12112;
UPDATE 1
joel=# ALTER TABLE catalog_clone.pg_constraint ADD FOREIGN KEY (conrelid, EACH
ELEMENT OF conkey) REFERENCES catalog_clone.pg_attribute (attrelid, attnum);
ALTER TABLE
OK, good, FK added.
Thanks to the FK, trying to corrupt the column again will now give an error:
joel=# UPDATE catalog_clone.pg_constraint SET conkey = '{2,20,3,1234}' WHERE
oid = 12112;
ERROR: insert or update on table "pg_constraint" violates foreign key
constraint "pg_constraint_conrelid_conkey_fkey"
DETAIL: Key (conrelid, EACH ELEMENT OF conkey)=(1255, {2,20,3,1234}) is not
present in table "pg_attribute".
OK, good, we got an error.
*** Test #2 OK: FK on oid[]
Find a suitable row to do testing on:
joel=# \d catalog_clone.pg_proc
proallargtypes | oid[] | | |
joel=# SELECT oid,proallargtypes FROM catalog_clone.pg_proc WHERE
cardinality(proallargtypes) > 1 LIMIT 1;
oid | proallargtypes
------+----------------
3059 | {25,2276}
(1 row)
Corrupting the row will not be detected since no FK yet:
joel=# UPDATE catalog_clone.pg_proc SET proallargtypes = '{25,2276,1234}' WHERE
oid = 3059;
UPDATE 1
Trying to add a FK now will detect the corrupted row:
joel=# ALTER TABLE catalog_clone.pg_proc ADD FOREIGN KEY (EACH ELEMENT OF
proallargtypes) REFERENCES catalog_clone.pg_type (oid);
ERROR: insert or update on table "pg_proc" violates foreign key constraint
"pg_proc_proallargtypes_fkey"
DETAIL: Key (EACH ELEMENT OF proallargtypes)=({25,2276,1234}) is not present
in table "pg_type".
OK, good, we got an error.
Fix row and try again:
joel=# UPDATE catalog_clone.pg_proc SET proallargtypes = '{25,2276}' WHERE oid
= 3059;
UPDATE 1
joel=# ALTER TABLE catalog_clone.pg_proc ADD FOREIGN KEY (EACH ELEMENT OF
proallargtypes) REFERENCES catalog_clone.pg_type (oid);
ALTER TABLE
OK, good, FK added.
Thanks to the FK, trying to corrupt the column again will now give an error:
joel=# UPDATE catalog_clone.pg_proc SET proallargtypes = '{25,2276,1234}' WHERE
oid = 3059;
ERROR: insert or update on table "pg_proc" violates foreign key constraint
"pg_proc_proallargtypes_fkey"
DETAIL: Key (EACH ELEMENT OF proallargtypes)=({25,2276,1234}) is not present
in table "pg_type".
OK, good, we got an error.
*** Test 3 NOT OK: FK on oidvector
Find a suitable row to do testing on:
joel=# \d catalog_clone.pg_proc
proargtypes | oidvector | | |
joel=# SELECT oid,proargtypes FROM catalog_clone.pg_proc WHERE
cardinality(proargtypes) > 1 LIMIT 1;
oid | proargtypes
-----+-------------
79 | 19 25
(1 row)
Corrupting the row will not be detected since no FK yet:
joel=# UPDATE catalog_clone.pg_proc SET proargtypes = '19 25 12345'::oidvector
WHERE oid = 79;
UPDATE 1
Trying to add a FK now will detect the corrupted row:
joel=# ALTER TABLE catalog_clone.pg_proc ADD FOREIGN KEY (EACH ELEMENT OF
proargtypes) REFERENCES catalog_clone.pg_type (oid);
ERROR: insert or update on table "pg_proc" violates foreign key constraint
"pg_proc_proargtypes_fkey"
DETAIL: Key (EACH ELEMENT OF proargtypes)=(19 25 12345) is not present in
table "pg_type".
OK, good, we got an error.
Fix row and try again:
joel=# UPDATE catalog_clone.pg_proc SET proargtypes = '19 25'::oidvector WHERE
oid = 79;
UPDATE 1
joel=# ALTER TABLE catalog_clone.pg_proc ADD FOREIGN KEY (EACH ELEMENT OF
proargtypes) REFERENCES catalog_clone.pg_type (oid);
ALTER TABLE
OK, good, FK added.
Now, with the FK on the oidvector column, let's try to corrupt the column:
joel=# UPDATE catalog_clone.pg_proc SET proargtypes = '19 25 12345'::oidvector
WHERE oid = 79;
ERROR: operator does not exist: oidvector pg_catalog.@> oid[]
LINE 1: ... 1 FROM ONLY "catalog_clone"."pg_type" x WHERE $1 OPERATOR(p...
^
HINT: No operator matches the given name and argument types. You might need to
add explicit type casts.
QUERY: SELECT 1 WHERE (SELECT pg_catalog.count(DISTINCT y) FROM
pg_catalog.unnest($1) y) OPERATOR(pg_catalog.=) (SELECT pg_catalog.count(*)
FROM (SELECT 1 FROM ONLY "catalog_clone"."pg_type" x WHERE $1
OPERATOR(pg_catalog. @>) ARRAY["oid"] FOR KEY SHARE OF x) z)
It seems to me there is some type conversion between oidvector and oid[] that
isn't working properly?
/Joel
