autoincrement primary keys handled incorrectly by schema designer - IHP would better support identity columns

[michaelfliegner]

trafficstars

Selecting BIGSERIAL for a primary key column "id" in the column editor generates

id BIGSERIAL PRIMARY KEY NOT NULL,

while

id BIGSERIAL PRIMARY KEY would be correct.

For a BIGSERIAL primary key definition <TABLE_NAME>.id postgres generates a column like this

Column | Type | Collation | Nullable | Default
-------------+--------+-----------+----------+--------------------------------------- id | bigint | | not null | nextval('versions4_id_seq'::regclass)

There is a subtle difference in the semantics of NOT NULL between

• a non null value must be given on INSERT and
• column is not updatable to NULL

With autoincrement columns only the second meaning is valid. IHP does not respect that difference but applies the first meaning on all primary keys.

If you don't supply a value on create new row in ShowTable you get an error. If, on the other hand, you supply a value, that defies the idea of autoincrementing.

Proposal:

From POSTGRES v11 on identity primary keys are supported that make said difference explicit and easily handled:

It is

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

vs

id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

See: https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/

As far as I can see, implementation would affect not only schema generation, but code generation as well.

This also relates to #1649 which I will update soon

[s0kil]

id columns being UUID is crucial for IHP, the code generator and routing rely on it being UUID.

[michaelfliegner]

crucial in what way?

Note that IHP does not enforce UUID primary keys. It allows serisl and bigserial even if it doesn't handle them correctly.