Re: GENERATED ... AS IDENTITY, Was: Re: Feature Freeze

> > For db restoration (pg_dump), how do you restore to the same values as
> > previously if it is always regenerated? By making ALWAYS a suggestion
> > for some users instead of always enforced and providing an override
> > mechanism for it. I assume it only works for relation owners but I've
> > not figured out how the spec does permissions.
> >
> > <override clause> ::=
> > OVERRIDING USER VALUE
> > | OVERRIDING SYSTEM VALUE
> >
> > In short, pg_dump should append OVERRIDING SYSTEM VALUE to any insert or
> > copy for relations with an GENERATED ALWAYS identity column and the
> > backend will need to respect that.
> >
>
> Aren't INSERT and COPY distinguished in code paths?

Yes, they are separate but they also use the same permission set.

Any user can copy into a structure at any time and virtually every
restriction will be applied normally (CHECK, DEFAULT, etc.). Copy
bypasses Rules, significant parsing overhead since there is no need to
look for subselects, and possibly some triggers are bypassed. I'm fairly
sure that foreign key triggers fire.

In short, COPY doesn't bypass enforcement. GENERATED ALWAYS is part of
that enforcement and should be included in that.

If it is not included, we cannot recommend GENERATED ALWAYS for uses
like recording CURRENT_USER in an audit log since the data could be
fudged.

> > ALWAYS is really only enforced for anyone who doesn't have permission to
> > specify otherwise.
> >
> >
> > Another one that got me is what do you do if you do this:
> >
> > CREATE TABLE tab (col integer);
> > INSERT INTO tab VALUES (10);
> > ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY;
> >
> > What is the value for "tab"."col"? It would seem that the table should
> > be rewritten with all values for "col" recalculated -- thus it would be
> > '1'. But wait! Can we add the <override clause> here too to keep the old
> > values and change the enforcement for new tuples only?
> >
>
> I don't think we should rewrite existing rows because
> when it was inserted, the stored value was valid
> according to the rules at that time. What if you
> have more than one rows in that table?

SERIAL has, until recently, been described as a macro. A tool for
setting things up quickly but many parts of which can be changed by hand
after-ward. It's not exactly a good source for information on how this
structure should work. For one, you can easily override the suggested
default a serial gives at any time as any user. The intention of ALWAYS
is to prevent exactly that behaviour.

I don't have an opinion on ALTER TABLE changes for this one way or the
other. It was my intention to advise that a group decision is required
and some research into what other databases do in this case. I believe
MSSQL and DB2 both implement this functionality.

Oh, and one more item. These expressions have the same abilities as a
CHECK constraint for referencing other columns.

This example comes from an IBM Guide:

CREATE TABLE T1(c1 INT, c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2),
c4 SMALLINT GENERATED ALWAYS AS
(CASE
WHEN c1 > c2 THEN 1
ELSE NULL
END)
);

Here is what IBM has to say about ALTER TABLE and GENERATED ALWAYS:

GENERATED
Specifies that DB2 generates values for the column.

ALWAYS
Specifies that DB2 will always generate
a value for the column when a row is
inserted into the table, or whenever the
result value of the
generation-expression might change. The
result of the expression is stored in
the table. GENERATED ALWAYS is the
recommended option unless data
propagation or unload and reload
operations are being performed.
GENERATED ALWAYS is the required option
for generated columns.

BY DEFAULT
Specifies that DB2 will generate a value
for the column when a row is inserted
into the table, or updated, specifying
DEFAULT for the column, unless an
explicit value is specified. BY DEFAULT
is the recommended option when using
data propagation or performing unload
and reload operations.

identity-options
This clause cannot be specified when adding a column to
an existing table.

AS (generation-expression)
Specifies that the definition of the column is based on
an expression. Requires that the table be put in check
pending state, using the SET INTEGRITY statement. After
the ALTER TABLE statement, the SET INTEGRITY statement
with FORCE GENERATED must be used to update and check
all the values in that column against the new
expression. For details on specifying a column with a
generation-expression, see "CREATE TABLE".

I'm not sure what they're describing, actually. It appears they don't
require it to be updated but that the option is available to do so when
the equation changes.

> Which record gets which value? You cannot know

Note that when you add a new column with a default, including nextval of
a sequence, it is expected that those values will be assigned by tuple
order in the table.

CREATE SEQUENCE foo;
ALTER TABLE tab ADD col integer DEFAULT nextval('foo');

> which record was inserted first because subsequent
> updates may ruin that order before the ALTER TABLE.
> And recalculating the max value of col isn't too reliable
> if another session is also inserting records.
>
> And what about non-unique columns?

I'm not sure what unique versus non-unique has to do with this. The
question I have is what is the meaning of ALWAYS.

There is never a guarantee that alter table will succeed in all cases.

ALTER TABLE tab ALTER col TYPE bigint USING 2;

> Plain SERIALs aren't declared unique automatically, either.
> Consider the following:
>
> CREATE TABLE tab (col integer);
> INSERT INTO tab VALUES (10);
> ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY( MINVALUE 1 MAXVALUE 12 CYCLE );

--