This is an automated email from the ASF dual-hosted git repository. yjhjstz pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit e1d4b817d9eb0537a3ec84499624124d1d091e79 Author: Xing Guo <[email protected]> AuthorDate: Wed Feb 22 09:14:37 2023 +0800 [7X] Skip the foreign key constraint validation. (#14344) Currently, Greenplum doesn't support foreign key constraint validation. However, we only emit a warning message saying Greenplum doesn't support it without skipping the validation process. I noticed this issue when I was running the following query: CREATE TABLE fk_test_reference (col2 text unique not null); INSERT INTO fk_test_reference VALUES ('stuff'); CREATE UNLOGGED TABLE id_taptest_table ( col1 bigint, col2 text not null default 'stuff', col3 timestamptz DEFAULT now(), col4 text, FOREIGN KEY (col2) REFERENCES fk_test_reference(col2)) PARTITION BY RANGE (col1); CREATE TABLE id_taptest_table_p3000000000 ( LIKE id_taptest_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING GENERATED INCLUDING INDEXES); ALTER TABLE id_taptest_table ATTACH PARTITION id_taptest_table_p3000000000 FOR VALUES FROM (3000000000) TO (3000000010); There're 2 possible failures when attaching the newly created partitioned table. 1. Greenplum emits an error message: ERROR: XX000: no pre-assigned OID for pg_constraint tuple "id_taptest_table_col2_fkey" (namespace:0 keyOid1:16472 keyOid2:102) (oid_dispatch.c:374) (seg2 127.0.0.1:7004 pid=195321) (oid_dispatch.c:374) LOCATION: GetNewOrPreassignedOid, oid_dispatch.c:374 2. Greenplum emits an error message: ERROR: 0A000: function cannot execute on a QE slice because it accesses relation "public.id_taptest_table_p3000000000" (seg2 127.0.0.1:7004 pid=108395) CONTEXT: SQL statement "SELECT fk."col2" FROM ONLY "public"."id_taptest_table_p3000000000" fk LEFT OUTER JOIN ONLY "public"."fk_test_reference" pk ON ( pk."col2" OPERATOR(pg_catalog.=) fk."col2") WHERE pk."col2" IS NULL AND (fk."col2" IS NOT NULL)" LOCATION: querytree_safe_for_qe_walker, functions.c:238 The root cause analysis: When attaching a partitioned table with foreign key references, Greenplum will check the constraints by running the query both on the coordinator and on segments: SELECT fk."col2" FROM ONLY "public"."id_taptest_table_p3000000000" fk LEFT OUTER JOIN ONLY "public"."fk_test_reference" pk ON ( pk."col2" OPERATOR(pg_catalog.=) fk."col2") WHERE pk."col2" IS NULL AND (fk."col2" IS NOT NULL) If the check get passed, coordinator will dispatch the real command for 'ALTER TABLE ... ATTACH PARTITION ...' to segments. When performing the 'ALTER TABLE' command, coordinator needs to generate an OID for the pg_constraint tuple 'id_taptest_table_col2_fkey' to keep the catalog table consistent across the cluster. The dispatched OID will be cleaned up if that transaction is aborted or committed. The 1st possible failure occurs if we execute the 'ALTER TABLE' command directly, the dispatched OID for altering table get cleaned up after the constraint checking query finishing (the constraint checking query executed in function 'RI_Initial_Check'). The 2nd possible failure occurs if we execute the 'ALTER TABLE' command in a transaction block. The constraint checking query on coordinator will success and coordinator will dispatch the real 'ALTER TABLE' command to segments. The segment will also run the constraint checking query but in QE mode (Gp_role == GP_ROLE_EXECUTE), so the query cannot be executed because it's accessing some relation. Anyway, Greenplum doesn't support foreign key constraints validation, the simplest fixing is skipping the validation. Fix #14279 --- src/backend/commands/tablecmds.c | 1 + src/test/regress/expected/alter_table_gp.out | 50 ++++++++++++++++++++++++++++ src/test/regress/sql/alter_table_gp.sql | 27 +++++++++++++++ 3 files changed, 78 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1fcf89b618..92f34467d5 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -13037,6 +13037,7 @@ validateForeignKeyConstraint(char *conname, ereport(WARNING, (errcode(ERRCODE_GP_FEATURE_NOT_YET), errmsg("referential integrity (FOREIGN KEY) constraints are not supported in Cloudberry Database, will not be enforced"))); + return; /* * Build a trigger call structure; we'll need it either way. diff --git a/src/test/regress/expected/alter_table_gp.out b/src/test/regress/expected/alter_table_gp.out index 713662fc89..8149d5f901 100644 --- a/src/test/regress/expected/alter_table_gp.out +++ b/src/test/regress/expected/alter_table_gp.out @@ -364,3 +364,53 @@ execute checkrelfilenodediff('alter column diff type', 'attype_indexed_constr_dk (4 rows) drop table relfilenodecheck; +-- Test that we are able to attach a newly created partition table when it has foreign key reference. +CREATE TABLE issue_14279_fk_reference (col2 text unique not null); +INSERT INTO issue_14279_fk_reference VALUES ('stuff'); +CREATE TABLE issue_14279_taptest_table ( + col1 BIGINT, + col2 TEXT NOT NULL DEFAULT 'stuff', FOREIGN KEY (col2) REFERENCES issue_14279_fk_reference(col2)) + PARTITION BY RANGE (col1); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced +CREATE TABLE issue_14279_taptest_table_p3000000000 ( + LIKE issue_14279_taptest_table + INCLUDING DEFAULTS INCLUDING CONSTRAINTS); +NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table +ALTER TABLE issue_14279_taptest_table ATTACH PARTITION issue_14279_taptest_table_p3000000000 FOR VALUES FROM (3000000000) TO (3000000100); +WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced +BEGIN; +CREATE TABLE issue_14279_taptest_table_p3000000100 ( + LIKE issue_14279_taptest_table + INCLUDING DEFAULTS INCLUDING CONSTRAINTS); +NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table +ALTER TABLE issue_14279_taptest_table ATTACH PARTITION issue_14279_taptest_table_p3000000100 FOR VALUES FROM (3000000100) TO (3000000200); +WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced +END; +INSERT INTO issue_14279_taptest_table SELECT generate_series(3000000000, 3000000001); +INSERT INTO issue_14279_taptest_table SELECT generate_series(3000000100, 3000000101); +-- The parent table shouldn't have anything. +SELECT * FROM ONLY issue_14279_taptest_table; + col1 | col2 +------+------ +(0 rows) + +-- The newly attached table should have 2 rows. +SELECT * FROM issue_14279_taptest_table_p3000000000; + col1 | col2 +------------+------- + 3000000000 | stuff + 3000000001 | stuff +(2 rows) + +-- The newly attached table should have 2 rows. +SELECT * FROM issue_14279_taptest_table_p3000000100; + col1 | col2 +------------+------- + 3000000100 | stuff + 3000000101 | stuff +(2 rows) + +DROP TABLE issue_14279_taptest_table; +DROP TABLE issue_14279_fk_reference; diff --git a/src/test/regress/sql/alter_table_gp.sql b/src/test/regress/sql/alter_table_gp.sql index 1fd87d50b9..bf6e9e27e0 100644 --- a/src/test/regress/sql/alter_table_gp.sql +++ b/src/test/regress/sql/alter_table_gp.sql @@ -303,3 +303,30 @@ execute checkrelfilenodediff('alter column diff type', 'attype_indexed_constr_dk drop table relfilenodecheck; +-- Test that we are able to attach a newly created partition table when it has foreign key reference. +CREATE TABLE issue_14279_fk_reference (col2 text unique not null); +INSERT INTO issue_14279_fk_reference VALUES ('stuff'); +CREATE TABLE issue_14279_taptest_table ( + col1 BIGINT, + col2 TEXT NOT NULL DEFAULT 'stuff', FOREIGN KEY (col2) REFERENCES issue_14279_fk_reference(col2)) + PARTITION BY RANGE (col1); +CREATE TABLE issue_14279_taptest_table_p3000000000 ( + LIKE issue_14279_taptest_table + INCLUDING DEFAULTS INCLUDING CONSTRAINTS); +ALTER TABLE issue_14279_taptest_table ATTACH PARTITION issue_14279_taptest_table_p3000000000 FOR VALUES FROM (3000000000) TO (3000000100); +BEGIN; +CREATE TABLE issue_14279_taptest_table_p3000000100 ( + LIKE issue_14279_taptest_table + INCLUDING DEFAULTS INCLUDING CONSTRAINTS); +ALTER TABLE issue_14279_taptest_table ATTACH PARTITION issue_14279_taptest_table_p3000000100 FOR VALUES FROM (3000000100) TO (3000000200); +END; +INSERT INTO issue_14279_taptest_table SELECT generate_series(3000000000, 3000000001); +INSERT INTO issue_14279_taptest_table SELECT generate_series(3000000100, 3000000101); +-- The parent table shouldn't have anything. +SELECT * FROM ONLY issue_14279_taptest_table; +-- The newly attached table should have 2 rows. +SELECT * FROM issue_14279_taptest_table_p3000000000; +-- The newly attached table should have 2 rows. +SELECT * FROM issue_14279_taptest_table_p3000000100; +DROP TABLE issue_14279_taptest_table; +DROP TABLE issue_14279_fk_reference; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
