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 );
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
