Обсуждение: More DROP COLUMN
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
"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
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 alllight. --XTC
> > 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
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
> > 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
> 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: Yes, how about prepending a character that would usually need to be escaped. I like Hannu's proposal with the blanks " col1 dropped@2002-07-17.10:30:00", the underscores are too commonly used. Maybe add two characters, one special and a backspace after the first blank. So it would print nicely, but be very unlikely. I would prefer a simple but highly predictable rule, where you can say "Don't name your columns starting with " \353\010" (blank, greek d, BS) over some random algo that stays out of the way by means of low probability. Andreas
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
On Mon, 15 Jul 2002, Zeugswetter Andreas SB SD wrote: > I would prefer a simple but highly predictable rule, where you can say > "Don't name your columns starting with " \353\010" (blank, greek d, > BS) over some random algo that stays out of the way by means of low > probability. \353 is not a delta in most of the character encodings that I use, and is not valid at all in ASCII. Non-graphic chars are also likely to cause misery because it's not obvious, using normal tools, what they are. (The above example would appear to many people as just a space.) I would suggest it's probably a good idea to stick to ASCII graphic (i.e., non-control, not delete) characters. 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 alllight. --XTC
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
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