Column ADDing issues - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Column ADDing issues
Date
Msg-id Pine.LNX.4.21.0001252139410.9535-100000@localhost.localdomain
Whole thread Raw
Responses Re: [HACKERS] Column ADDing issues
Re: [HACKERS] Column ADDing issues
List pgsql-hackers
I took up the issue of

* Alter TABLE ADD COLUMN does not honor DEFAULT, add CONSTRAINT

and chances are excellent that this will get all done in the next day or
three.

First a syntactical issue. We currently allow the following statement,
although it is not legal SQL:

create table test (a int4,b int4 check (a>b)
);

It's not legal because the column constraint for "b" may only reference
column "b". Instead you could legally write

create table test (a int4,b int4,check (a>b)
);

because the check constraint is now a table constraint. Big deal. Now the
problem is that because I reuse the same syntactical elements, the
following will work:

create table test (a int4);
alter table test add column b int4 check (a>b);

No harm done, but how about:

create table test (a int4, b int4);
alter table test add column c text check (a>b);

I guess this would be sort of equivalent to saying

alter table test add column c text;
alter table test add constraint check (a>b);

So, I guess what I'm saying is whether you want to allow the mentioned
weirdness or not.


Secondly, an internal question. If I use SearchSysCacheTuple() on a
condition with several potential matches, what is the defined behaviour?
Can I call it again to get the next tuple?


Thirdly, about TODO item

* ALTER TABLE ADD COLUMN to inherited table put column in wrong place

Actually, according to what I would expect out of the blue, it puts it
into the *right* place. Even good ol' SQL, although they naturally do not
know about inheritance, seems to agree:

"... the degree of [the table] is increased by 1 and the ordinal position
of that [new] column is equal to the new degree of [the table] ..."
(11.11)

What that says to me is that if you add a column to a table (during create
or alter) then the new column gets placed after all the others. Thus,
we're in compliance without even knowing it. 

Or maybe look at it this way:
create table test1 (a int4);
create table test2 (b int4) inherits (test1);                   ^ col #1          ^ col #2
alter table test1* add column c int4;                             ^ col #3

Everything has its order and it's not like the inheritance as such is
broken.

Surely, trying to stick the column in between is going to be three times
as much work as dropping columns would be, whichever way you do it. (And
making attributes invisible ain't gonna help you. ;)

What do you all say?

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Happy column adding (was RE: [HACKERS] Happy column dropping)
Next
From: Peter Eisentraut
Date:
Subject: Re: AW: [HACKERS] Some notes on optimizer cost estimates