From: | David Steele <david(at)pgmasters(dot)net> |
---|---|
To: | Pierre Ducroquet <pierre(dot)ducroquet(at)people-doc(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE does not check for column existence before starting operations |
Date: | 2018-03-02 13:44:16 |
Message-ID: | 0de17018-b3bd-e5e1-9c24-330d7554b784@pgmasters.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Pierre,
On 3/2/18 6:36 AM, Pierre Ducroquet wrote:
>
> 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.
This looks like a good idea. However, the last CF for PG11 is in
progress so it might be difficult to attract much comment/review right now.
I recommend entering this patch in the 2018-09 CF so it doesn't get lost.
Regards,
--
-David
david(at)pgmasters(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Claudio Freire | 2018-03-02 13:47:54 | Re: [HACKERS] [PATCH] Vacuum: Update FSM more frequently |
Previous Message | Aleksander Alekseev | 2018-03-02 13:36:53 | Re: zheap: a new storage format for PostgreSQL |