OK,

DROP COLUMN now seems to work perfectly.  All the old test cases that failed
now work fine.

However, I'm not happy with the way dropped columns are renamed.  I want to
give them a name that no-one would ever want to use as a legit column name.
I don't like this behaviour:

test=# create table test (a int4, b int4);
CREATE TABLE
test=# alter table test drop a;
ALTER TABLE
test=# select dropped_1 from test;
ERROR:  Attribute "dropped_1" not found
test=# alter table test add dropped_1 int4;
ERROR:  ALTER TABLE: column name "dropped_1" already exists in table "test"

It's a bit confusing, hey?

What should we do about it?

Maybe I could make ADD COLUMN give this message instead for dropped columns?

ERROR:  ALTER TABLE: column name "dropped_1" is a dropped column in table
"test" ... or something ...

We could name the fields "________dropped_x" sort of thing perhaps????

Chris


Mon, 15 Jul 2002 11:45:44 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
More DROP COLUMN
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> However, I'm not happy with the way dropped columns are renamed.

Okay...

> We could name the fields "________dropped_x" sort of thing perhaps????

In practice that would certainly work, especially if we increase
NAMEDATALEN to 128 or so, as has been proposed repeatedly.

Alternatively, we could invest a lot of work to make it possible for
attname to be NULL, but I don't see the payoff...

			regards, tom lane

Sun, 14 Jul 2002 23:57:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More DROP COLUMN 
On Mon, 15 Jul 2002, Christopher Kings-Lynne wrote:

> However, I'm not happy with the way dropped columns are renamed.  I want to
> give them a name that no-one would ever want to use as a legit column name.
> ...
> We could name the fields "________dropped_x" sort of thing perhaps????

I suggest you _dropped_N_XXXXXXXXXXXXXXXX where "n" is that same
sequence number (1, 2, 3, etc.) and the Xs are the hexedecimal
representation of a 64-bit random number. So you'd get names like
"_dropped_2_719fe940a46eb39c".

This is easy to generate and highly unlikley to conflict with anything.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Mon, 15 Jul 2002 13:01:34 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: More DROP COLUMN
> > We could name the fields "________dropped_x" sort of thing perhaps????
>
> In practice that would certainly work, especially if we increase
> NAMEDATALEN to 128 or so, as has been proposed repeatedly.

Well, x is just an integer anyway, so even with 32 it's not a problem...

In case anyone was wondering btw, if a column named 'dropped_1' already
exists when you drop column 1 in the table, it will be renamed like this:

dropped1_1

And if that also exists, it will become

dropped2_1

etc.  I put that extra number after dropped and not at the end so prevent it
being off the end of a 32 character name.

> Alternatively, we could invest a lot of work to make it possible for
> attname to be NULL, but I don't see the payoff...

Yeah, I think a weird name should be good enough...

Chris


Mon, 15 Jul 2002 12:06:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: More DROP COLUMN 
> > etc.  I put that extra number after dropped and not at the end
> so prevent it
> > being off the end of a 32 character name.
> >
> > > Alternatively, we could invest a lot of work to make it possible for
> > > attname to be NULL, but I don't see the payoff...
> >
> > Yeah, I think a weird name should be good enough...
>
> perhaps starting it with spaces instead of _ would make it even harder
> to write by accident, so tha name could be
>    "         dropped 0000000001"
>
> or to make it even more self documenting store the drop time,
> " col001 dropped@020715.101427"
> --------------------------------

Well, are there characters that are illegal in column names that I could
use?  I did a quick check and couldn't find any!

Chris


Mon, 15 Jul 2002 15:20:08 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: More DROP COLUMN
On Mon, 2002-07-15 at 06:06, Christopher Kings-Lynne wrote:
> > > We could name the fields "________dropped_x" sort of thing perhaps????
> >
> > In practice that would certainly work, especially if we increase
> > NAMEDATALEN to 128 or so, as has been proposed repeatedly.
> 
> Well, x is just an integer anyway, so even with 32 it's not a problem...
> 
> In case anyone was wondering btw, if a column named 'dropped_1' already
> exists when you drop column 1 in the table, it will be renamed like this:
> 
> dropped1_1
> 
> And if that also exists, it will become
> 
> dropped2_1
> 
> etc.  I put that extra number after dropped and not at the end so prevent it
> being off the end of a 32 character name.
> 
> > Alternatively, we could invest a lot of work to make it possible for
> > attname to be NULL, but I don't see the payoff...
> 
> Yeah, I think a weird name should be good enough...

perhaps starting it with spaces instead of _ would make it even harder
to write by accident, so tha name could be
   "         dropped 0000000001"

or to make it even more self documenting store the drop time, 
" col001 dropped@020715.101427"
--------------------------------

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


15 Jul 2002 10:17:52 +0200
Hannu Krosing <hannu@tm.ee>
Re: More DROP COLUMN
On Mon, 2002-07-15 at 09:20, Christopher Kings-Lynne wrote:
> > > etc.  I put that extra number after dropped and not at the end
> > so prevent it
> > > being off the end of a 32 character name.
> > >
> > > > Alternatively, we could invest a lot of work to make it possible for
> > > > attname to be NULL, but I don't see the payoff...
> > >
> > > Yeah, I think a weird name should be good enough...
> >
> > perhaps starting it with spaces instead of _ would make it even harder
> > to write by accident, so tha name could be
> >    "         dropped 0000000001"
> >
> > or to make it even more self documenting store the drop time,
> > " col001 dropped@020715.101427"
> > --------------------------------
> 
> Well, are there characters that are illegal in column names that I could
> use?  I did a quick check and couldn't find any!

I guess that \0 would be unusable (not sure if its illegal)

\r \n and \t (and others < 0x20) are probably quite unlikely too.

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


15 Jul 2002 12:17:11 +0200
Hannu Krosing <hannu@tm.ee>
Re: More DROP COLUMN
Hannu Krosing <hannu@tm.ee> writes:
> or to make it even more self documenting store the drop time,
> " col001 dropped@020715.101427"

I'm not at all excited about trying to store times, random numbers,
etc in dropped column names.  We are not trying to do cryptography
here, only invent an improbable name.  I do not believe that injecting
pseudo-randomness will help.  I'd prefer to keep the names of dropped
columns predictable.

> I guess that \0 would be unusable (not sure if its illegal)

You can NOT use \0, and I don't think other nonprinting characters would
be a good idea either.  I think a bunch of leading spaces or underscores
would be fine.

			regards, tom lane

Mon, 15 Jul 2002 08:44:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More DROP COLUMN 

chris, have you looked at how sapdb (http://www.sapdb.org)
does this ?

/sergio
ps: IANAL


""Christopher Kings-Lynne"" <chriskl@familyhealth.com.au> escribi� en el
mensaje news:GNELIHDDFBOCMGBFGEFOAECECDAA.chriskl@familyhealth.com.au...
> OK,
>
> DROP COLUMN now seems to work perfectly.  All the old test cases that
failed
> now work fine.
>
> However, I'm not happy with the way dropped columns are renamed.  I want
to
> give them a name that no-one would ever want to use as a legit column
name.
> I don't like this behaviour:
>
> test=# create table test (a int4, b int4);
> CREATE TABLE
> test=# alter table test drop a;
> ALTER TABLE
> test=# select dropped_1 from test;
> ERROR:  Attribute "dropped_1" not found
> test=# alter table test add dropped_1 int4;
> ERROR:  ALTER TABLE: column name "dropped_1" already exists in table
"test"
>
> It's a bit confusing, hey?
>
> What should we do about it?
>
> Maybe I could make ADD COLUMN give this message instead for dropped
columns?
>
> ERROR:  ALTER TABLE: column name "dropped_1" is a dropped column in table
> "test" ... or something ...
>
> We could name the fields "________dropped_x" sort of thing perhaps????
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Mon, 15 Jul 2002 14:45:40 -0300
"Sergio A. Kessler" <sak@ksb.com.ar>
Re: More DROP COLUMN