Collected from the PG bugs email list.
|OS||OSX and AWS Aurora|
|Reported by||Michael Sageryd|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16057 Logged by: Michael Sageryd Email address: (redacted) PostgreSQL version: 10.6 Operating system: OSX and AWS Aurora Description: Both my dev environment (PG 10.6, Docker, OSX) and my prod environment (PG 10.6 AWS Aurora) has had this odd error a couple of times. I cannot reliably reproduce it, but I have managed to force it to appear. I have a primary key with `GENERATED BY DEFAULT AS IDENTITY `. In some odd cases the identity counter seems to lag and I get a PK violation just by inserting a new record. Could this be a bug related to my these two circumstances: - GENERATED BY DEFAULT AS IDENTITY - DEFERRABLE INITIALLY DEFERRED ``` CREATE TABLE main.project_report_sequence ( project_id integer NOT NULL , report_type_id integer NOT NULL , sequence_id integer NOT NULL CONSTRAINT project_report_sequence_pkey PRIMARY KEY ( project_id, report_type_id ) ); CREATE TABLE main.sequence ( id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL , modified_at timestamptz , last_value integer , CONSTRAINT sequence_pkey PRIMARY KEY ( id ) ); CREATE OR REPLACE FUNCTION main.tr_sequence_assert_project_sequence() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER AS $function$ BEGIN --Asserts that the created sequence is used, i.e. being referenced from a child table --The only way to create a sequence is to also define it's usage in the same transaction --The trigger itself will be declared as DEFERRABLE INITIALLY DEFERRED to enable creation in the same transaction IF NOT EXISTS (SELECT 1 FROM main.project_sequence WHERE sequence_id = NEW.id) AND NOT EXISTS (SELECT 1 FROM main.project_report_sequence WHERE sequence_id = NEW.id) AND NOT EXISTS (SELECT 1 FROM main.project_report_bundle_sequence WHERE sequence_id = NEW.id) THEN RAISE EXCEPTION 'Cannot create sequence (%). A sequence can only be created together with its usage (project_sequence, project_report_sequence or project_report_bundle_sequence ) in the same transaction', NEW.id; ELSE RETURN NEW; END IF; END; $function$ CREATE CONSTRAINT TRIGGER ai_au__sequence__assert_project_sequence AFTER INSERT OR UPDATE OF id ON main.sequence DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE main.tr_sequence_assert_project_sequence(); DO $$ DECLARE _sequence_id int; BEGIN INSERT INTO main.sequence (last_value) values (0) RETURNING id INTO _sequence_id; INSERT INTO main.project_report_sequence (project_id, report_type_id, sequence_id) VALUES (2, 2, _sequence_id); END;$$; ``` Testing the setup with the DO-statement works in 99% of the cases. But now and then I get a primary key violation on `sequence_pkey`. With an identity default it should not be possible to get a PK violation. I suspect this has to do with the deferred trigger. As of now I can't have this setup as it's not stable. I'm also thinking that it's overly complicated. My goal is to ensure that there are no unused (un-referenced) records in the sequence table. I'll solve this with some kind of periodic cleaning procedure instead. If this is a bug, it's quite worrisome. If I'm doing stupid things, I'll happily take some pointers.
|2019-10-15 07:51:11+00||PG Bug reporting form||BUG #16057: Faulty PK violation|