From: | Pierre Ducroquet <pierre(dot)ducroquet(at)people-doc(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | ALTER TABLE does not check for column existence before starting operations |
Date: | 2018-03-02 11:36:41 |
Message-ID: | 1897070.0DItnajBuJ@pierred-pdoc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
While working on a big table recently, I noticed that ALTER TABLE does not
check for column existence in operations like SET NOT NULL before starting
working on the table, for instance adding a primary key.
It is thus possible, if a typo has been made, to generate a long lock and a
lot of WAL that will serve no purpose since the whole transaction will be
discarded.
For example :
toto=# alter table test add primary key(i), alter column typo set not null;
ERROR: column "typo" of relation "test" does not exist
Time: 10.794 s
The attached patch fixes this behaviour by adding a small check in the first
pass of alter table to make sure that a column referenced by an alter command
exists first. It also checks if the column is added by another alter sub-
command. It does not handle every scenario (dropping a column and then
altering it for instance), these are left to the exec code to exclude.
The patch has been checked with make check, and I see no documentation change
to do since this does not alter any existing documented behaviour.
Regards
Pierre
Attachment | Content-Type | Size |
---|---|---|
at_checkcolumnexists_v1.patch | text/x-patch | 4.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2018-03-02 11:40:27 | Re: zheap: a new storage format for PostgreSQL |
Previous Message | Amit Langote | 2018-03-02 11:24:59 | Re: [HACKERS] path toward faster partition pruning |