Two very minor points with the new alter table (not sure if they are
even bugs are have already been addressed).

1. alter table alter type on a clustered index seems to drop the cluster
(by design)?

2. alter table cluster on seems to give a strange error message of the
index name is really the name of a table.

Ex:
alter table test cluster on test;
returns:
ERROR:  cache lookup failed for index 19013

Merlin

Log:
test=# create table test (id int);
CREATE TABLE
test=# create index test_id_idx on test(id);
CREATE INDEX
test=# alter table test cluster on test;
ERROR:  cache lookup failed for index 19046
test=# alter table test cluster on test_id_idx;
ALTER TABLE
test=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Indexes:
    "test_id_idx" btree (id) CLUSTER


test=# alter table test alter id type bigint;
ALTER TABLE
test=# \d test
     Table "public.test"
 Column |  Type  | Modifiers
--------+--------+-----------
 id     | bigint |
Indexes:
    "test_id_idx" btree (id)


test=#

Thu, 6 May 2004 09:50:52 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: ALTER TABLE TODO items 
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 1. alter table alter type on a clustered index seems to drop the cluster
> (by design)?

Hmm.  Not by design really, but because it's rebuilding all of the
indexes and that status bit doesn't get passed through.  I'll see how
hard it is to fix.

> 2. alter table cluster on seems to give a strange error message of the
> index name is really the name of a table.

This is a pre-existing bug, but should be easy enough to fix.

			regards, tom lane

Thu, 06 May 2004 10:21:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> 1. alter table alter type on a clustered index seems to drop the cluster

I tweaked things so that the clustered flag is preserved for indexes
that aren't directly affected by the ALTER TYPE.  It would take more
work to preserve the setting for an index that is rebuilt by ALTER TYPE,
and I'm not even sure that it's sensible --- the new index could have
a significantly different ordering from the old.  What do you think?

> 2. alter table cluster on seems to give a strange error message of the
> index name is really the name of a table.

There were several bugs there ... think I got them all ...

			regards, tom lane

Thu, 06 May 2004 12:14:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > 1. alter table alter type on a clustered index seems to drop the cluster
> 
> I tweaked things so that the clustered flag is preserved for indexes
> that aren't directly affected by the ALTER TYPE.  It would take more
> work to preserve the setting for an index that is rebuilt by ALTER TYPE,
> and I'm not even sure that it's sensible --- the new index could have
> a significantly different ordering from the old.  What do you think?

At a minimum, we should indicate we dropped the cluster on the index.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 May 2004 12:43:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> At a minimum, we should indicate we dropped the cluster on the index.

[shrug] If you're going to make me do that, I might as well reinstall
the bit on the new index.  The code's problem is it doesn't know that
any of the indexes it dropped were clustered, and finding that out is
90% of the issue.

What I want to know is whether it is sensible to mark the revised index
as clustered, given that its semantics might be significantly different
from before.

			regards, tom lane

Thu, 06 May 2004 12:51:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > At a minimum, we should indicate we dropped the cluster on the index.
> 
> [shrug] If you're going to make me do that, I might as well reinstall
> the bit on the new index.  The code's problem is it doesn't know that
> any of the indexes it dropped were clustered, and finding that out is
> 90% of the issue.
> 
> What I want to know is whether it is sensible to mark the revised index
> as clustered, given that its semantics might be significantly different
> from before.

OK, yea, just leave the bit.  We can add documentation that they should
run CLUSTER again if they radically modified the column as part of the
ALTER>

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 May 2004 12:56:39 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: ALTER TABLE TODO items
> I tweaked things so that the clustered flag is preserved for indexes
> that aren't directly affected by the ALTER TYPE.  It would take more
> work to preserve the setting for an index that is rebuilt by ALTER TYPE,
> and I'm not even sure that it's sensible --- the new index could have
> a significantly different ordering from the old.  What do you think?

Out of interest what happens to other column features such as the 
existing statistics level and the existing storage spec?  I guess these 
might have to change when type changes??

Chris

Fri, 07 May 2004 08:40:32 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER TABLE TODO items
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Out of interest what happens to other column features such as the 
> existing statistics level and the existing storage spec?  I guess these 
> might have to change when type changes??

I left the statistic setting as-is (do you think that's wrong?) but the
storage spec gets reset to whatever the default for the new type is.
We could talk about doing something more complicated, such as "keep the
old setting if both old and new types support toasting, else reset to
new default".  Not sure if that'd be better or not.

			regards, tom lane

Thu, 06 May 2004 20:45:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
> I left the statistic setting as-is (do you think that's wrong?) but the
> storage spec gets reset to whatever the default for the new type is.

Seems reasonable.

> We could talk about doing something more complicated, such as "keep the
> old setting if both old and new types support toasting, else reset to
> new default".  Not sure if that'd be better or not.

Yeah, I was thinking along those lines.  I don't now though...

What happens with ordering of operations in the ALTER TABLE statement? 
Like if I put an alter TYPE and a SET STORAGE in the same statement 
(wiht commas between), in what order will things happen?  Is it 
deterministic?  Is it documented?  Are there situations where a crazy 
collection of 20 commands in a single ALTER TABLE will have 
unpredictable effects?

Also, should the syntax be SET TYPE, not just TYPE?

Chris


Fri, 07 May 2004 09:50:24 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER TABLE TODO items
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> What happens with ordering of operations in the ALTER TABLE statement? 
> Like if I put an alter TYPE and a SET STORAGE in the same statement 
> (wiht commas between), in what order will things happen?

The "right thing" will happen --- in this case, the SET STORAGE will
take effect (before we actually rewrite the table, so the end result
will be exactly what you want).  Check out the multiple-pass structure
in commands/tablecmds.c.

> Is it deterministic?  Is it documented?  Are there situations where a
> crazy collection of 20 commands in a single ALTER TABLE will have
> unpredictable effects?

Yes, no (I did say we needed more docs effort), and I hope not.

> Also, should the syntax be SET TYPE, not just TYPE?

Shrug ... I dunno whether Rod had a precedent for that choice or not.

			regards, tom lane

Thu, 06 May 2004 22:02:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 
> > Also, should the syntax be SET TYPE, not just TYPE?
> 
> Shrug ... I dunno whether Rod had a precedent for that choice or not.

FireBird: ALTER COLUMN <column> TYPE <type>
DB2:	ALTER COLUMN <column> SET DATA TYPE <type>.
Oracle:	MODIFY <column> <type>
MSSQL:	ALTER COLUMN <column> <type> <constraints>
MySQL:	Both Oracle and MSSQL
Sap:	MODIFY <column> <type>

Spec:	Nothing (obvious) on changing column types

MODIFY is horrible. It seems to drop all constraints, defaults, etc that
are not specified in the second definition. It is essentially a
replacement of the column.

FireBird is the closest to our implementation. DB2 only allows changing
the length of a VARCHAR and even then it is restrictive in the amount
the length can change by.

I remember polling -hackers to see if there were objections at the time,
but the syntax is easy enough to change if you wish.


Thu, 06 May 2004 23:21:08 -0400
Rod Taylor <pg@rbt.ca>
Re: ALTER TABLE TODO items
Rod Taylor <pg@rbt.ca> writes:
>>> Also, should the syntax be SET TYPE, not just TYPE?

>> Shrug ... I dunno whether Rod had a precedent for that choice or not.

> FireBird: ALTER COLUMN <column> TYPE <type>
> DB2:	ALTER COLUMN <column> SET DATA TYPE <type>.
> Oracle:	MODIFY <column> <type>
> MSSQL:	ALTER COLUMN <column> <type> <constraints>
> MySQL:	Both Oracle and MSSQL
> Sap:	MODIFY <column> <type>

Given that, I'm happy with what we got ...

			regards, tom lane

Thu, 06 May 2004 23:25:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE TODO items 

> I did say we needed more docs effort

Yes, where should the docs for this go? The Alter table reference page,
or Chapter 5.5 titled "Modifying Tables"?



Thu, 06 May 2004 23:33:55 -0400
Rod Taylor <pg@rbt.ca>
Re: ALTER TABLE TODO items
>>FireBird: ALTER COLUMN <column> TYPE <type>
>>DB2:	ALTER COLUMN <column> SET DATA TYPE <type>.
>>Oracle:	MODIFY <column> <type>
>>MSSQL:	ALTER COLUMN <column> <type> <constraints>
>>MySQL:	Both Oracle and MSSQL
>>Sap:	MODIFY <column> <type>
> 
> 
> Given that, I'm happy with what we got ...

Yeah same, I was just wondering whether it should be like SET NOT NULL, 
that's all.

Chris


Fri, 07 May 2004 11:47:39 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER TABLE TODO items