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

Rod Taylor írta:
>>> 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.
>

OK. So COPY needs an OVERRIDING close, too, not just INSERT.
In the meantime I implemented the discussed restrictions on
UPDATE for GENERATED ALWAYS columns, allowing
"UPDATE tab SET col = default" only for
GENERATED ALWAYS AS. I also implemented
INSERT ... OVERRIDING { SYSTEM | USER } VALUE.
If I got it correctly, OVERRIDING USER VALUE
seems to be the same as omitting the OVERRIDING clause...

>>> 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.
>

I see.

I looked a bit into the TODO entry that's about
ALTER TABLE tab ALTER col RENAME newcol
should also rename the sequence. My question is:
is it legal to call pg_get_serial_sequence() from
src/backen/parser/analyze.c:transformAlterTableStmt()?
This would be the easiest way to issue an
ALTER TABLE oldseq RENAME newseq
command automatically.

And I think I found a bug in PostgreSQL.
If I do this:

create table tab1 (id serial, t text); -- creates tab1_id_seq
create table tab2 (id serial, t text) inherits (tab1); -- creates
tab2_id_seq
drop table tab1 cascade;

then tab1_id_seq gets also dropped but tab2_id_seq doesn't.
Both 8.1.4 and current 8.2CVS do this.

> 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)
> );
>
>

For this to work, we need to lift the restriction
on DEFAULT so cother columns can appear in the
expression. Dependencies must be tracked between
columns so GENERATED ALWAYS columns on UPDATE
and DEFAULT/ GENERATED ALWAYS columns on
INSERT get their computed values. Circular dependencies
must be avoided, etc. Hm.

> 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.
>

This is even more strict then SQL2003 which allows
only one IDENTITY column at any time, so this is allowed:

CREATE TABLE tab (id serial, ...);
ALTER TABLE tab DROP id;
ALTER TABLE tab ADD id serial GENERATED AS IDENTITY (...);

I deliberately omitted the check to enforce it.

>
> 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 );
>>