ALTER TABLE does not check for column existence before starting operations

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

Responses

Browse pgsql-hackers by date

  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