BUG #16057: Faulty PK violation

The following bug has been logged on the website:

Bug reference: 16057
Logged by: Michael Sageryd
Email address: michael(at)sageryd(dot)se
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.