BUG #16057: Faulty PK violation

The following bug has been logged on the website:

Bug reference:      16057
Logged by:          Michael Sageryd
Email address:      michael@sageryd.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.