Re: Add column if not exists (CINE)

Hello,

At least from a performance point of view CINE should never cause a table rewrite, it should either execute as a plain CREATE or as "nothing". I don't mind if the CINE fails if the column already exists but with a different definition, so maybe it could be worded differently to make it clearer what you get? How about something like:

"ALTER TABLE foo ADD OR MATCH COLUMN bar INTEGER"
a) doesn't exist => create
b) exists and matches => nothing
c) exists and doesn't match => error

if COR semantics should ever be implmented they could be

"ALTER TABLE foo ADD OR REPLACE COLUMN bar INTEGER"
a) doesn't exist => create
b) exists and matches => nothing
c) exists and doesn't match => replace

However, I don't want it to fail unless there's an explicit conflict, because I tend to modify the columns later:
"ALTER TABLE foo ADD COLUMN bar INTEGER"
"ALTER TABLE foo ALTER COLUMN bar SET DEFAULT 0"
"ALTER TABLE foo ALTER COLUMN bar SET NOT NULL"
"ALTER TABLE foo ADD OR MATCH COLUMN bar INTEGER" <-- succeed or fail?

Personally, I'm only interested to match on TYPE so possibly:
"ALTER TABLE foo ADD OR MATCH TYPE COLUMN bar INTEGER" <-- succeed
"ALTER TABLE foo ADD OR MATCH [ALL] COLUMN bar INTEGER" <-- fail

To be honest, I think this becomes more complicated than a CINE, but as I felt that got a rather lukewarm reception maybe this sounds better. The syntax leaves it open for COR later, and the matching code should be useful to determine if the COR actually needs to do a REPLACE. Opinions?

Regards,
Kjell Rune

--- Den tor 2010-07-22 skrev Simon Riggs <simon(at)2ndQuadrant(dot)com>:

> Fra: Simon Riggs <simon(at)2ndQuadrant(dot)com>
> Emne: Re: [HACKERS] Add column if not exists (CINE)
> Til: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Kopi: "Robert Haas" <robertmhaas(at)gmail(dot)com>, "Heikki Linnakangas" <heikki(dot)linnakangas(at)enterprisedb(dot)com>, "Andrew Dunstan" <andrew(at)dunslane(dot)net>, "Takahiro Itagaki" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, "Kjell Rune Skaaraas" <kjella79(at)yahoo(dot)no>, pgsql-hackers(at)postgresql(dot)org
> Dato: Torsdag 22. juli 2010 02.43
> On Wed, 2010-04-28 at 21:15 -0400,
> Tom Lane wrote:
>
> > I still say
> > that COR rather than CINE semantics would be
> appropriate for columns.
>
> Viewed from a locking perspective, I would disagree.
>
> COR semantics force a table rewrite, in certain cases. That
> makes it
> hard to predict externally how long the command will run
> for.
>
> As a DBA, I would want a command that executes without
> rewrite (if
> appropriate) or does nothing.
>
> Predictable behaviour is the most important concern.
>
> That isn't necessarily an argument in favour of CINE, which
> seems
> slightly less clear about what we might expect from that,
> in my reading
> at least.
>
> --
> Simon Riggs       
>    www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Training and
> Services
>
>