PostgreSQL Cascading Truncates – Why It Affects Unrelated Tables

postgresqlpostgresql-9.5

I've created a number of tables in my public schema.

Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART IDENTITY CASCADE.

However, I do not want any other tables but those related to the users table to be affected. The only foreign key referencing the users table is on the teams table which references users through owner_id. The teams table truncates as expected.

What isn't expected, is that the attributes table is also truncated by TRUNCATE TABLE users RESTART IDENTITY CASCADE.

The attributes table has one foreign key on the accounts table which references the users table. However, on the attributes table, ALL account_id values are NULL so there is no actual reference back on any of the records to a specific record in the accounts table. In fact, at the time of executing the truncate query, the entire accounts table is already empty.

How then is TRUNCATE TABLE users RESTART IDENTITY CASCADE causing my attributes table to be truncated based on NULL foreign keys?

Best Answer

I think TRUNCATE ... CASCADE doesn't do any reference checking on foreign key values, that would defeat the purpose. The mere presense of referential integrity constraints connecting attributes to users via accounts causes all three (and possibly more) tables to be truncated (which you would have noticed if accounts weren't empty).

Related Solutions

Postgresql – Do not reject entire statement when deleting rows in batch

I was able to make this work with the below setup Process: 1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record 2. Before deleting a record verify no reference record exists in any of the dependent table

Detailed implementation can be found below. Let me know your thoughts on this.

            #1: DDL

            CREATE TABLE core.images
            (
                id serial NOT NULL,
                name character varying(1024) NOT NULL
                CONSTRAINT "PK_images" PRIMARY KEY (id)
            );

            create table core.app_images
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            );

            create table core.app_images1
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            )

            #2: Funciton to check ref data integrity

            CREATE OR REPLACE FUNCTION core.check_ref_exists (
                p_schema TEXT,
                p_table TEXT,
                p_column TEXT,
                p_value TEXT
            ) 
            RETURNS BOOLEAN 
            LANGUAGE plpgsql 
            AS $$ 
            DECLARE
                r record ; 
                v_sql TEXT ; 
                v_return BOOLEAN DEFAULT FALSE ;
            BEGIN
                FOR r IN (
                    SELECT
                        pn.nspname s_name,
                        r_tab.relname AS t_name,
                        pa.attname AS c_name
                    FROM
                        pg_constraint pc
                    INNER JOIN pg_class tab ON tab.oid = pc.confrelid
                    INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
                    INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
                    INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
                    INNER JOIN pg_attribute pa ON (
                        pa.attrelid = pc.conrelid
                        AND pa.attnum = ANY (pc.conkey)
                    )
                    INNER JOIN pg_attribute pa_d ON (
                        pa_d.attrelid = pc.confrelid
                        AND pa_d.attnum = ANY (pc.confkey)
                    )
                    WHERE
                        contype = 'f'
                    AND tab.relname = p_table
                    AND pn_drv.nspname = p_schema
                    AND pa_d.attname = p_column
                ) loop 

                    v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s 
                        WHERE s. ID = ' || p_value || ' 
                        AND EXISTS ( 
                            SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ; 
                    v_return := COALESCE (v_return, FALSE) ;
                    IF v_return = TRUE THEN
                        RETURN v_return ;
                    END IF ;

                END loop ; 
                RETURN v_return ;
            END ; $$

            #3: Trigger Function
            CREATE OR REPLACE FUNCTION core.images_tr_func() 
            RETURNS TRIGGER AS 
            $BODY$ 
            DECLARE 
                v_ref_exists boolean;
            BEGIN 
                select core.check_ref_exists (
                    p_schema := 'core',
                    p_table :='images',
                    p_column :='id',
                    p_value := old.id::text
                ) into v_ref_exists;

                if v_ref_exists then 
                    return null;
                else
                    return old;
                end if;
            EXCEPTION 
                    WHEN OTHERS THEN
                            RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                            RETURN NULL;
            END;
            $BODY$ 
            LANGUAGE plpgsql;

            #4: Trigger
            DROP TRIGGER if exists images_tr ON core.images;

            CREATE TRIGGER images_tr BEFORE DELETE
                 ON core.images FOR EACH ROW 
                 EXECUTE PROCEDURE core.images_tr_func();

            #5: Sample Data
            insert into  core.images(name) values('image1'),('image2'),('image3'),('image4');
            select * from  core.images;

            insert into core.app_images(image_id) values
            (1),(1),(2)

            select * from core.app_images

            insert into core.app_images1(image_id) values
            (2),(3),(3)

            select * from core.app_images1

            #6: Testing
            delete from core.images where id = 1; --Nothing happened as it is still referred
            delete from core.images where id = 4; --Deleted as it is an orphan record

Postgresql – Insertion of data violates the foreign key constraint

In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.

a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.

For an example of using deferrable constraints, see my answer in this question:
Implementation of a many-to-many relationship with total participation constraints in SQL.

b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:

with
  insert_emp as
  ( insert into employee 
        (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
    values
        ('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
    returning 
        *
  ), 
  insert_dep
  ( insert into sp0090.department 
        (Dname, Dnumber, Mgr_ssn, Mgr_start_date) 
    values 
        ('Headquarter', 1, '888665555', '1981-06-19')
    returning 
        *
  ) 
select *
from insert_emp as e
  full join insert_dep as d
  on  e.dno = d.dnumber
  and e.ssn = d.mgr_ssn ;

Best Answer

Related Solutions

Postgresql – Do not reject entire statement when deleting rows in batch

Postgresql – Insertion of data violates the foreign key constraint

Related Question