Pivotal Knowledge Base

Follow

How to truncate a table referenced in a FK constraint for Greenplum

Environment

  • Pivotal Greenplum 4.3.x
  • OS: RHEL 6.x

Symptom

A user is trying to create an external table after dropping it, but it fails and returns the following error:

20170808:12:52:45:047869 gptransfer:mdw:gpadmin-[DEBUG]:-[worker0] finished cmd: transfer of analyticsdb.heuristics.executions cmdStr='None' had result: cmd had rc=1 completed=False halted=False 
stdout='error 'ERROR: cannot truncate a table referenced in a foreign key constraint 
DETAIL: Table "document_tags" references "executions".

Cause

According to the doc: https://gpdb.docs.pivotal.io/4330/ref_guide/sql_commands/CREATE_TABLE.html

The REFERENCES and FOREIGN KEY clauses specify referential integrity constraints (foreign key constraints). 
Greenplum accepts referential integrity constraints as specified in PostgreSQL syntax but does not enforce them.

This is the reason when creating a table with the reference key would get the message: the

"WARNING: Referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced."

The reference key would be created but it won't check the integrity from the reference table. Meanwhile, it would check the FK constraint when you try to truncate/drop the table and got the message

"ERROR: cannot truncate a table referenced in a foreign key constraint".

Reproduce

Step1. Create sample table and insert data, then you can see the data has been inserted successfully and no referential integrity check:

gpadmin=# CREATE TABLE cities (
gpadmin(# city varchar(80) primary key,
gpadmin(# location point
gpadmin(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
CREATE TABLE
gpadmin=# CREATE TABLE weather (
gpadmin(# city varchar(80) references cities(city),
gpadmin(# temp_lo int,
gpadmin(# temp_hi int,
gpadmin(# prcp real,
gpadmin(# date date
gpadmin(# );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'city' 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
gpadmin=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
INSERT 0 1
gpadmin=# select * from cities;
city | location
------+----------
(0 rows)
gpadmin=# select * from weather;
city | temp_lo | temp_hi | prcp | date
----------+---------+---------+------+------------
Berkeley | 45 | 53 | 0 | 1994-11-28
(1 row)

Step2. Truncate table "cities" and got below error message:

gpadmin=# truncate table cities;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "weather" references "cities".
HINT: Truncate table "weather" at the same time, or use TRUNCATE ... CASCADE.

Step3. Alter table to drop the constraint then the table "cities" can be truncated:

gpadmin=# \d+ cities
Table "public.cities"
Column | Type | Modifiers | Storage | Description
----------+-----------------------+-----------+----------+-------------
city | character varying(80) | not null | extended |
location | point | | plain |
Indexes:
"cities_pkey" PRIMARY KEY, btree (city)
Referenced by:
TABLE "weather" CONSTRAINT "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(city)
Has OIDs: no
Distributed by: (city)

gpadmin=# alter table weather drop constraint weather_city_fkey;
ALTER TABLE
gpadmin=# truncate table cities;
TRUNCATE TABLE

An alternative solution is that you can follow the HINT to use "TRUNCATE ... CASCADE" to truncate table:

gpadmin=# truncate table cities cascade;
NOTICE: truncate cascades to table "weather"
NOTICE: truncate cascades to table "weather" (seg1 gpdb-sandbox.localdomain:40001 pid=87916)
NOTICE: truncate cascades to table "weather" (seg0 gpdb-sandbox.localdomain:40000 pid=87914)
TRUNCATE TABLE

Resolution

For user's gptransfer case,  the resolution is to drop the constraint key by below command:

alter table <table_name> drop constraint <constraint_key_name>;

Additional Information

Generally, the referential integrity should be support in the RDBMS. But this is a big difference in GPDB compare to Postgres and referential integrity (FOREIGN KEY) constraints are supported in Postgres. Sample as below:

fzou=# CREATE TABLE cities (
fzou(#         city     varchar(80) primary key,
fzou(#         location point
fzou(# );
CREATE TABLE
fzou=# 
fzou=# CREATE TABLE weather (
fzou(#         city      varchar(80) references cities(city),
fzou(#         temp_lo   int,
fzou(#         temp_hi   int,
fzou(#         prcp      real,
fzou(#         date      date
fzou(# );
CREATE TABLE
fzou=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

Comments

Powered by Zendesk