Re: [HACKERS] GSoC 2017: Foreign Key Arrays
От | Joel Jacobson |
---|---|
Тема | Re: [HACKERS] GSoC 2017: Foreign Key Arrays |
Дата | |
Msg-id | bdd09a0e-aaca-423d-a84e-b18912c5fabf@www.fastmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] GSoC 2017: Foreign Key Arrays (Mark Rofail <markm.rofail@gmail.com>) |
Ответы |
Re: [HACKERS] GSoC 2017: Foreign Key Arrays
(Mark Rofail <markm.rofail@gmail.com>)
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: