Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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