1

When I first run

SET session_replication_role = DEFAULT;

CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
);

And then run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

DELETE FROM users;

It successfully deletes the row in users even though this violates referential integrity because the session_replication_role = replica. So far so good.

However, when I instead run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

TRUNCATE users;

I get this output:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "posts" references "users".
HINT:  Truncate table "posts" at the same time, or use TRUNCATE ... CASCADE.

For performance reasons, I really want to be able to use TRUNCATE and not DELETE. How can I get around this issue without using TRUNCATE users CASCADE? I can not use cascading deletes because my DB may have other foreign keys that reference the users table that I do not want to delete.

  • Just a question, if you agree leaving posts table in an inconsistent state, why are you using a foreign key? – McNets Nov 23 '18 at 18:34
  • @McNets it's difficult to answer without the full problem context, but basically this is only temporary and eventually the same treatment will be applied to posts and tables it references. I would normally wrap it in a transaction and defer the constraint, but there are hundreds of millions of rows in this db that need to be deleted. – James Stonehill Nov 24 '18 at 19:39
  • I can't check it due to fiddle permissions, but have a look: dbfiddle.uk/… – McNets Nov 24 '18 at 19:51
  • @McNets I'm still getting the error "ERROR: cannot truncate a table referenced in a foreign key constraint". This is definitely looking to me like a postgres feature issue. – James Stonehill Nov 24 '18 at 22:42
  • Well, you could remove FK temporarily. – McNets Nov 25 '18 at 15:11
0

First and foremost you should know the name of the FOREIGN KEY constraint:

CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER,

    CONSTRAINT fk_post_users 
        FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

Then you can DROP FOREIGN KEY and after that, TRUNCATE the table.

ALTER TABLE posts DROP CONSTRAINT fk_post_users;
TRUNCATE users;

Now, posts table remains in an inconsistent state, but you can add the foreign key again using NOT VALID option, that does not check current values.

ALTER TABLE posts ADD CONSTRAINT fk_post_users
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

db<>fiddle here

Quoted from Postgres docs:

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. Foreign key constraints on partitioned tables may not be declared NOT VALID at present.

The addition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the referenced table.

Additional restrictions apply when unique or primary key constraints are added to partitioned tables; see CREATE TABLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.