Skip to content

Instantly share code, notes, and snippets.

@nextstopsun
Last active May 31, 2019 09:38
Show Gist options
  • Save nextstopsun/42bb7d0037b9aac128388329b0d39bf5 to your computer and use it in GitHub Desktop.
Save nextstopsun/42bb7d0037b9aac128388329b0d39bf5 to your computer and use it in GitHub Desktop.
SQL ownership and trigger insert problem
DROP TABLE IF EXISTS parent CASCADE;
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS log;
DROP ROLE IF EXISTS tbl_owners;
CREATE ROLE tbl_owners;
GRANT tbl_owners TO current_user;
CREATE TABLE parent (
id SERIAL PRIMARY KEY
);
ALTER TABLE parent OWNER TO tbl_owners;
CREATE TABLE child (
id SERIAL PRIMARY KEY,
parent_id INTEGER,
CONSTRAINT fk_parent FOREIGN KEY (parent_id)
REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);
ALTER TABLE child OWNER TO tbl_owners; --THIS CAUSES TROUBLE
CREATE TABLE log (
id SERIAL PRIMARY KEY,
message TEXT
);
CREATE OR REPLACE FUNCTION add_log() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
INSERT INTO log (message) VALUES (TG_RELNAME || ' logged');
RETURN NULL;
END;
$$;
CREATE TRIGGER log_parent
BEFORE DELETE
ON parent
EXECUTE PROCEDURE add_log();
CREATE TRIGGER log_child
BEFORE DELETE
ON child
EXECUTE PROCEDURE add_log();
INSERT INTO parent (id) VALUES (1);
INSERT INTO child (parent_id) VALUES (1);
DELETE FROM parent; --BOOM!
SELECT * FROM log;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment