Re: Rearranging ALTER TABLE to avoid multi-operations bugs
movead.li@highgo.ca <movead.li@highgo.ca>
From: movead li <movead.li@highgo.ca>
To: pgsql-hackers@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2019-06-13T08:33:19Z
Lists: pgsql-hackers
I applied the 'alter-table-with-recursive-process-utility-calls-wip.patch'
on the master(e788e849addd56007a0e75f3b5514f294a0f3bca). And
when I test the cases, I find it works well on 'alter table t1 add column
f2 int not null, alter column f2 add generated always as identity' case,
but it doesn't work on #14827, #15180, #15670, #15710.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the test result with #14827 failed
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
postgres=# create table t10 (f1 int);
CREATE TABLE
postgres=# alter table t10 add column f2 int not null,
postgres-# alter column f2 add generated always as identity;
ALTER TABLE
postgres=#
postgres=# insert into t10 values(0);
INSERT 0 1
postgres=# create table test_serial ( teststring varchar(5));
CREATE TABLE
postgres=# alter table test_serial add column if not exists uid BIGSERIAL;
ALTER TABLE
postgres=# alter table test_serial add column if not exists uid BIGSERIAL;
psql: NOTICE: column "uid" of relation "test_serial" already exists, skipping
ALTER TABLE
postgres=#
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+--------------
public | t10 | table | lichuancheng
public | t10_f2_seq | sequence | lichuancheng
public | test_serial | table | lichuancheng
public | test_serial_uid_seq | sequence | lichuancheng
public | test_serial_uid_seq1 | sequence | lichuancheng
(5 rows)
postgres=#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
So it's wrong with a 'test_serial_uid_seq1' sequence to appear.
The new status of this patch is: Waiting on Author
Commits
-
Clarify behavior of adding and altering a column in same ALTER command.
- 9b9c5f279e82 13.0 landed
-
Restructure ALTER TABLE execution to fix assorted bugs.
- 1281a5c907b4 13.0 landed
-
doc: Add best practises section to partitioning docs
- e788e849addd 12.0 cited
-
Allow on-the-fly capture of DDL event details
- b488c580aef4 9.5.0 cited