Test.sh

text/plain

Download

#!/bin/sh
schema="pfcb_test"
cat <<EOF | psql -q

SET client_min_messages TO WARNING;
CREATE SCHEMA $schema;
SET search_path TO $schema;
\qecho

CREATE TABLE junk (junk int);	-- just to supress some select results
CREATE TABLE master (master_id int PRIMARY KEY);
CREATE TABLE dependent (dependent_id serial PRIMARY KEY,
    master_id  int REFERENCES master ON DELETE CASCADE);

\qecho Outside a function, inserting into the dependent before the master
\qecho correctly produces a referential integrity error:
BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
INSERT INTO dependent (master_id) VALUES (200);
--INSERT INTO master (master_id) VALUES (200);
COMMIT;
\qecho

CREATE OR REPLACE FUNCTION reverse_order_insert() RETURNS int AS '
    SET CONSTRAINTS ALL IMMEDIATE;
    INSERT INTO dependent (master_id) VALUES (300);
    INSERT INTO master (master_id) VALUES (300);
    SELECT 0;
' LANGUAGE 'SQL';

\qecho The reverse order insert, when in a function, somehow passes
\qecho the integrity constraint, even in immediate mode:
INSERT INTO junk SELECT reverse_order_insert();
SELECT count(*) = 1 AS reverse_order_insert_in_function_succeeded
    FROM master NATURAL JOIN dependent
    WHERE master_id = 300;


\qecho Outside a function, a cascaded delete works:
INSERT INTO master (master_id) VALUES (100);
INSERT INTO dependent (master_id) VALUES (100);
DELETE FROM master WHERE master_id = 100;
SELECT count(*) = 0 AS inserted_row_not_present
    FROM master NATURAL JOIN dependent
    WHERE master_id = 100;

CREATE OR REPLACE FUNCTION delete_using_cascade() RETURNS int AS '
    INSERT INTO master (master_id) VALUES (400);
    INSERT INTO dependent (master_id) VALUES (400);
    DELETE FROM master WHERE master_id = 400;
    SELECT 0;
' LANGUAGE 'SQL';

\qecho A properly ordered insert and cascaded delete, inside a function,
\qecho does not work:
INSERT INTO junk SELECT delete_using_cascade();
\qecho

DROP SCHEMA $schema CASCADE;
EOF