Hey Peter,

I notice that here:

http://candle.pha.pa.us/main/writings/pgsql/sgml/ddl-alter.html

You mention that you cannot remove a column.  This is no longer true as of
last friday.

And here:

http://candle.pha.pa.us/main/writings/pgsql/sgml/ddl-depend.html

You say:

"All drop commands in PostgreSQL support specifying CASCADE. Of course"

Which isn't technically true for the ALTER TABLE/DROP NOT NULL statement..

Chris


Tue, 6 Aug 2002 10:27:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
New manual chapters
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> You say:
> "All drop commands in PostgreSQL support specifying CASCADE. Of course"
> Which isn't technically true for the ALTER TABLE/DROP NOT NULL statement..

Come to think of it, that's probably a bug: you should not be able to
DROP NOT NULL on a column that's part of a PRIMARY KEY.  Unless you 
cascade to remove the primary key, that is.

			regards, tom lane

Mon, 05 Aug 2002 22:56:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New manual chapters 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > You say:
> > "All drop commands in PostgreSQL support specifying CASCADE. Of course"
> > Which isn't technically true for the ALTER TABLE/DROP NOT NULL
> statement..
>
> Come to think of it, that's probably a bug: you should not be able to
> DROP NOT NULL on a column that's part of a PRIMARY KEY.  Unless you
> cascade to remove the primary key, that is.

I did ask you about this before, Tom :)

The DROP NOT NULL code I submitted will not allow you to drop a not null on
a column that participates in a primary key.  I was very careful about that.
So basically, it's a restrict-only implementation.  Although it would be
fairly easy I guess to make it support cascade and restrict keywords...?
Perhaps not thru the dependency mechanism, but it can be done explicitly.

Chris


Tue, 6 Aug 2002 11:06:04 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: New manual chapters 
On Tue, 2002-08-06 at 09:58, Christopher Kings-Lynne wrote:
> 
> Anyone have any ideas on how we could implement changing column type.  We'd
> have to move all dependencies and any object that refers to the column by
> its attnum,  etc...  I guess we could steal a lot of the renameatt() code...

As discussed some time ago, introducing attlognum would help here a lot
if you want the changed column not to hop to the end of column list for
"SELECT * " . Or are attlognum changes done already ?

-----------
Hannu


06 Aug 2002 08:36:23 +0500
Hannu Krosing <hannu@tm.ee>
Re: New manual chapters
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Come to think of it, that's probably a bug: you should not be able to
>> DROP NOT NULL on a column that's part of a PRIMARY KEY.  Unless you
>> cascade to remove the primary key, that is.

> I did ask you about this before, Tom :)

> The DROP NOT NULL code I submitted will not allow you to drop a not null on
> a column that participates in a primary key.  I was very careful about that.

Duh, so you were.

> So basically, it's a restrict-only implementation.  Although it would be
> fairly easy I guess to make it support cascade and restrict keywords...?
> Perhaps not thru the dependency mechanism, but it can be done explicitly.

Yeah, I doubt it's worth trying to force NOT NULL into the dependency
mechanism for this.  Do you feel like trying to do it "by hand"?  It
doesn't seem like a very important issue to me.

			regards, tom lane

Mon, 05 Aug 2002 23:54:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New manual chapters 
On Tue, 2002-08-06 at 11:02, Christopher Kings-Lynne wrote:
> > But we *have* drop column.
> 
> I know that....I think I must have been on crazy pills when I mentioned it
> in regards to DROP COLUMN...
> 
> > If we don't do attlognum for 7.3, there's little point in doing it at
> > all.  By the time 7.4 comes out, clients that formerly expected a
> > consecutive series of attnums will have found some way to cope.
> 
> I think that what Hannu is saying is that if we implemented changing column
> type with a drop and an add, all queries that used to go 'select * from'
> would return the modified column at the end of the list of columns instead
> of in its original position.

There is at least one type of change that can be safely done in-place,
namely making variable length types longer (varchar(2) --> varchar(5)).

Making them shorter would be doable in-place, but then there are two
ways to go - 

1. we check that the data fits the new length

2. i/o funtions would enforce length

the 2. way has an unexpected behaviour if we first make a field shorter
and then longer again.

> > I'm not sure that I feel any strong sense of urgency about this ---
> > 7.3 will break clients that examine the system catalogs in many ways,
> > and this doesn't seem like the nastiest of 'em.

It's not about inspecting system catalogs by clients, it is the change
in select * after a column type change (say from float to numeric(15,2))
if done by "add column + update + drop column + rename column" if we had
lognums, we could do "move column" as a final step.

Using the above scenario we already can do "alter table alter column
type" manually, so I'd suggest that implementing attlognum (move column)
gives more additional functionality than putting in a frontend to do
move column manually.

The "drop column + rename column + move column" could also be rolled
into one "replace column" command ;)

-----------
Hannu


06 Aug 2002 09:23:28 +0500
Hannu Krosing <hannu@tm.ee>
Re: New manual chapters
> > So basically, it's a restrict-only implementation.  Although it would be
> > fairly easy I guess to make it support cascade and restrict keywords...?
> > Perhaps not thru the dependency mechanism, but it can be done
> explicitly.
>
> Yeah, I doubt it's worth trying to force NOT NULL into the dependency
> mechanism for this.  Do you feel like trying to do it "by hand"?  It
> doesn't seem like a very important issue to me.

Well it'd be nice from a consistency point of view.  And it wouldn't be too
hard.  I don't think it's essential for 7.3, as it would be perfectly
backwards compatible.  I'll add it to my TODO list, right after changing
column type :)

Anyone have any ideas on how we could implement changing column type.  We'd
have to move all dependencies and any object that refers to the column by
its attnum,  etc...  I guess we could steal a lot of the renameatt() code...

Chris


Tue, 6 Aug 2002 12:58:28 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: New manual chapters 
> As discussed some time ago, introducing attlognum would help here a lot
> if you want the changed column not to hop to the end of column list for
> "SELECT * " . Or are attlognum changes done already ?

Arg.  That's something I didn't think of.  No, attlognums aren't done.  I
think having drop column is more important than the above concern tho.  I'll
add it to my TODO.

Chris


Tue, 6 Aug 2002 13:46:23 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: New manual chapters
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Arg.  That's something I didn't think of.  No, attlognums aren't done.  I
> think having drop column is more important than the above concern tho.  I'll
> add it to my TODO.

But we *have* drop column.

If we don't do attlognum for 7.3, there's little point in doing it at
all.  By the time 7.4 comes out, clients that formerly expected a
consecutive series of attnums will have found some way to cope.

I'm not sure that I feel any strong sense of urgency about this ---
7.3 will break clients that examine the system catalogs in many ways,
and this doesn't seem like the nastiest of 'em.

I just wanted to point out that "we'll do it later" isn't a profitable
attitude towards attlognum.  Either it's done by the end of August,
or it never gets done at all.

			regards, tom lane

Tue, 06 Aug 2002 01:54:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New manual chapters 
> But we *have* drop column.

I know that....I think I must have been on crazy pills when I mentioned it
in regards to DROP COLUMN...

> If we don't do attlognum for 7.3, there's little point in doing it at
> all.  By the time 7.4 comes out, clients that formerly expected a
> consecutive series of attnums will have found some way to cope.

I think that what Hannu is saying is that if we implemented changing column
type with a drop and an add, all queries that used to go 'select * from'
would return the modified column at the end of the list of columns instead
of in its original position.

> I'm not sure that I feel any strong sense of urgency about this ---
> 7.3 will break clients that examine the system catalogs in many ways,
> and this doesn't seem like the nastiest of 'em.
>
> I just wanted to point out that "we'll do it later" isn't a profitable
> attitude towards attlognum.  Either it's done by the end of August,
> or it never gets done at all.

Ok - don't know if I'll be able to tho.

Chris


Tue, 6 Aug 2002 14:02:53 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: New manual chapters 