Thread: DROP COLUMN
OK, more DROP COLUMN funny business: Assuming that selects, updates and deletes all ignore the dropped column, what happens with things like alter table statements? You can still quite happily set the default for a dropped column, etc. Will I have to add a dropped column check in everywhere that a command is able to target a column. ie. create index, cluster, alter table, etc, etc.? Or is there an easier way? Cheers, Chris
On Mon, 2002-07-15 at 11:30, Christopher Kings-Lynne wrote: > OK, more DROP COLUMN funny business: > > Assuming that selects, updates and deletes all ignore the dropped column, > what happens with things like alter table statements? > > You can still quite happily set the default for a dropped column, etc. > > Will I have to add a dropped column check in everywhere that a command is > able to target a column. ie. create index, cluster, alter table, etc, > etc.? Or is there an easier way? Each utility statement does some kind of a SearchSysCache() to determine the status of the column (whether it exists or not). You may want to write a wrapper function in lsyscache.c that returns the status of the column (dropped or not). Perhaps the att tuple could be fetched through this function (processed on the way out) -- though lsyscache routines tend to return simple items.
Rod Taylor wrote: > On Mon, 2002-07-15 at 11:30, Christopher Kings-Lynne wrote: > > OK, more DROP COLUMN funny business: > > > > Assuming that selects, updates and deletes all ignore the dropped column, > > what happens with things like alter table statements? > > > > You can still quite happily set the default for a dropped column, etc. > > > > Will I have to add a dropped column check in everywhere that a command is > > able to target a column. ie. create index, cluster, alter table, etc, > > etc.? Or is there an easier way? > > Each utility statement does some kind of a SearchSysCache() to determine > the status of the column (whether it exists or not). > > You may want to write a wrapper function in lsyscache.c that returns the > status of the column (dropped or not). Perhaps the att tuple could be > fetched through this function (processed on the way out) -- though > lsyscache routines tend to return simple items. Excellent idea. That's how temp tables worked, by bypassing the syscache. I wonder if you could just prevent dropped columns from being returned by the syscache. That may work just fine. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Excellent idea. That's how temp tables worked, by bypassing the
> syscache. I wonder if you could just prevent dropped columns from being
> returned by the syscache. That may work just fine.
No, it will break all the places that need to see dropped columns.
I agree that a wrapper function is probably an appropriate solution,
but only some of the calls of SearchSysCache should use it.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Excellent idea. That's how temp tables worked, by bypassing the
> > syscache. I wonder if you could just prevent dropped columns from being
> > returned by the syscache. That may work just fine.
>
> No, it will break all the places that need to see dropped columns.
>
> I agree that a wrapper function is probably an appropriate solution,
> but only some of the calls of SearchSysCache should use it.
What like add another parameter to SearchSysCache*?
Another question: How do I fill out the ObjectAddress when trying to drop
related objects?
eg:
object.classId = ??; object.objectId = ??; object.objectSubId = ??;
performDeletion(&object, behavior);
Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> I agree that a wrapper function is probably an appropriate solution,
>> but only some of the calls of SearchSysCache should use it.
> What like add another parameter to SearchSysCache*?
Definitely *not*; I don't want to kluge up every call to SearchSysCache
with a feature that's only relevant to a small number of them.
> Another question: How do I fill out the ObjectAddress when trying to drop
> related objects?
A column would be classId = RelOid_pg_class, objectId = OID of relation,
objectSubId = column's attnum.
BTW, it occurred to me recently that most of the column-specific
AlterTable operations will happily try to alter system columns (eg,
OID). In most cases this makes no sense and should be forbidden.
It definitely makes no sense for DROP COLUMN...
regards, tom lane
Tom Lane wrote: > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > >> I agree that a wrapper function is probably an appropriate solution, > >> but only some of the calls of SearchSysCache should use it. > > > What like add another parameter to SearchSysCache*? > > Definitely *not*; I don't want to kluge up every call to SearchSysCache > with a feature that's only relevant to a small number of them. Uh, then what? The only idea I had was to set a static boolean variable in syscache.c that controls whether droppped columns are returned, and have a enable/disable functions that can turn it on/off. The only problem is that an elog inside a syscache lookup would leave that value set. My only other idea is to make a syscache that is like ATTNAME except that it doesn't return a dropped column. I could probably code that up if you wish. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Uh, then what? The only idea I had was to set a static boolean > variable in > syscache.c that controls whether droppped columns are returned, and have > a enable/disable functions that can turn it on/off. The only problem is > that an elog inside a syscache lookup would leave that value set. > > My only other idea is to make a syscache that is like ATTNAME except > that it doesn't return a dropped column. I could probably code that up > if you wish. That'd be cool. I guess the thing is that either way, I will need to manually change every single instance where a dropped column should be avoided. So, really there's not much difference between me changing the SysCache search to use ATTNAMEUNDROPPED or whatever, or just checking the attisdropped field of the tuple in the same way that you must always check that attnum > 0. In fact, looking at it logically...if all the commands currently are required to check that they're not modifiying a system column, then why not add the requirement that they must also not modify dropped columns? I can do a careful doc search and try to make sure I've touched everything... Chris
Christopher Kings-Lynne wrote: > > Uh, then what? The only idea I had was to set a static boolean > > variable in > > syscache.c that controls whether droppped columns are returned, and have > > a enable/disable functions that can turn it on/off. The only problem is > > that an elog inside a syscache lookup would leave that value set. > > > > My only other idea is to make a syscache that is like ATTNAME except > > that it doesn't return a dropped column. I could probably code that up > > if you wish. > > That'd be cool. > > I guess the thing is that either way, I will need to manually change every > single instance where a dropped column should be avoided. So, really > there's not much difference between me changing the SysCache search to use > ATTNAMEUNDROPPED or whatever, or just checking the attisdropped field of the > tuple in the same way that you must always check that attnum > 0. > > In fact, looking at it logically...if all the commands currently are > required to check that they're not modifiying a system column, then why not > add the requirement that they must also not modify dropped columns? I can > do a careful doc search and try to make sure I've touched everything... Makes sense. Of course, we could make a syscache that didn't return system columns either. Actually, the original argument for negative attno's for dropped columns was exactly for this case, that the system column check would catch dropped columns too, but it causes other problems that are harder to fix so we _dropped_ the idea. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Definitely *not*; I don't want to kluge up every call to SearchSysCache
>> with a feature that's only relevant to a small number of them.
> Uh, then what?
Then we make a wrapper function. Something like
GetUndeletedColumnByName(relid,attname)
replaces SearchSysCache(ATTNAME,...) in those places where you don't
want to see deleted columns. It'd return NULL if it finds a column
tuple but sees it's deleted.
GetUndeletedColumnByNum(relid,attnum)
replaces SearchSysCache(ATTNUM,...) similarly.
> My only other idea is to make a syscache that is like ATTNAME except
> that it doesn't return a dropped column.
That would mean duplicate storage of tuples inside the catcache...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Definitely *not*; I don't want to kluge up every call to SearchSysCache > >> with a feature that's only relevant to a small number of them. > > > Uh, then what? > > Then we make a wrapper function. Something like > > GetUndeletedColumnByName(relid,attname) > > replaces SearchSysCache(ATTNAME,...) in those places where you don't > want to see deleted columns. It'd return NULL if it finds a column > tuple but sees it's deleted. > > GetUndeletedColumnByNum(relid,attnum) > > replaces SearchSysCache(ATTNUM,...) similarly. Good idea. > > My only other idea is to make a syscache that is like ATTNAME except > > that it doesn't return a dropped column. > > That would mean duplicate storage of tuples inside the catcache... No, I was thinking of something that did the normal ATTNAME lookup in the syscache code, then returned NULL on dropped columns; similar to your idea but done inside the syscache code rather than in a separate function. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Actually, the original argument for negative attno's for dropped columns > was exactly for this case, that the system column check would catch > dropped columns too, but it causes other problems that are harder to fix > so we _dropped_ the idea. Well, negative attnums are a good idea and yes, you sort of avoid all these problems. However, the backend is _full_ of stuff like this: if (attnum < 0)elog(ERROR, "Cannot footle system attribute."); But the problem is that we'd have to change all of them anyway in a negative attnum implementation, since they're not system attributes, they're dropped columns. But let's not start another thread about this!! Chris
> > In fact, looking at it logically...if all the commands currently are > > required to check that they're not modifiying a system column, > then why not > > add the requirement that they must also not modify dropped > columns? I can > > do a careful doc search and try to make sure I've touched everything... > > Makes sense. Of course, we could make a syscache that didn't return > system columns either. Actually - are you certain that every command uses a SearchSysCache and not some other weirdness? If we have to do the odd exception, then maybe we should do them all as 'exceptions'? Chris
Christopher Kings-Lynne wrote: > > > In fact, looking at it logically...if all the commands currently are > > > required to check that they're not modifiying a system column, > > then why not > > > add the requirement that they must also not modify dropped > > columns? I can > > > do a careful doc search and try to make sure I've touched everything... > > > > Makes sense. Of course, we could make a syscache that didn't return > > system columns either. > > Actually - are you certain that every command uses a SearchSysCache and not > some other weirdness? If we have to do the odd exception, then maybe we > should do them all as 'exceptions'? I actually don't know. I know all the table name lookups do use syscache or temp tables wouldn't have worked. ;-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Actually - are you certain that every command uses a SearchSysCache and not
> some other weirdness?
They probably don't. You'll have to look closely at places that use the
TupleDesc from a relcache entry.
regards, tom lane
> -----Original Message----- > From: Bruce Momjian > > Christopher Kings-Lynne wrote: > > > Uh, then what? The only idea I had was to set a static boolean > > > variable in > > > syscache.c that controls whether droppped columns are > returned, and have > > > a enable/disable functions that can turn it on/off. The only > problem is > > > that an elog inside a syscache lookup would leave that value set. > > > > > > My only other idea is to make a syscache that is like ATTNAME except > > > that it doesn't return a dropped column. I could probably > code that up > > > if you wish. > > > > That'd be cool. > > > > I guess the thing is that either way, I will need to manually > change every > > single instance where a dropped column should be avoided. So, really > > there's not much difference between me changing the SysCache > search to use > > ATTNAMEUNDROPPED or whatever, or just checking the attisdropped > field of the > > tuple in the same way that you must always check that attnum > 0. > > > > In fact, looking at it logically...if all the commands currently are > > required to check that they're not modifiying a system column, > then why not > > add the requirement that they must also not modify dropped > columns? I can > > do a careful doc search and try to make sure I've touched everything... > > Makes sense. Of course, we could make a syscache that didn't return > system columns either. > > Actually, the original argument for negative attno's for dropped columns > was exactly for this case, that the system column check would catch > dropped columns too, > but it causes other problems that are harder to fix > so we _dropped_ the idea. What does this mean ? BTW would we do nothing for clients after all ? regards, Hiroshi Inoue
Hiroshi Inoue wrote: > > Makes sense. Of course, we could make a syscache that didn't return > > system columns either. > > > > Actually, the original argument for negative attno's for dropped columns > > was exactly for this case, that the system column check would catch > > dropped columns too, > > > but it causes other problems that are harder to fix > > so we _dropped_ the idea. > > What does this mean ? Client programmers prefered the dropped flag rather than negative attno's so we went with that. > BTW would we do nothing for clients after all ? Clients will now need to check that dropped flag. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Tue, 2002-07-16 at 18:30, Bruce Momjian wrote: > Hiroshi Inoue wrote: > > > Makes sense. Of course, we could make a syscache that didn't return > > > system columns either. > > > > > > Actually, the original argument for negative attno's for dropped columns > > > was exactly for this case, that the system column check would catch > > > dropped columns too, > > > > > but it causes other problems that are harder to fix > > > so we _dropped_ the idea. > > > > What does this mean ? > > Client programmers prefered the dropped flag rather than negative > attno's so we went with that. While you are at it,could you add another flag is_system ? <evil grin> ----------- Hannu
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > Makes sense. Of course, we could make a syscache that didn't return > > > system columns either. > > > > > > Actually, the original argument for negative attno's for dropped columns > > > was exactly for this case, that the system column check would catch > > > dropped columns too, > > > > > but it causes other problems that are harder to fix > > > so we _dropped_ the idea. > > > > What does this mean ? > > Client programmers prefered the dropped flag rather than negative > attno's so we went with that. What I asked you is what *harder to fix* means. > > BTW would we do nothing for clients after all ? > > Clients will now need to check that dropped flag. Clients would have to check the flag everywhere pg_attribute appears. Why should clients do such a thing ? regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > > Makes sense. Of course, we could make a syscache that didn't return > > > > system columns either. > > > > > > > > Actually, the original argument for negative attno's for dropped columns > > > > was exactly for this case, that the system column check would catch > > > > dropped columns too, > > > > > > > but it causes other problems that are harder to fix > > > > so we _dropped_ the idea. > > > > > > What does this mean ? > > > > Client programmers prefered the dropped flag rather than negative > > attno's so we went with that. > > What I asked you is what *harder to fix* means. Uh, some said that having attno's like 1,2,3,5,7,8,9 with gaps would cause coding problems in client applications, and that was easier to have the numbers as 1-9 and check a flag if the column is dropped. Why that is easier than having gaps, I don't understand. I voted for the gaps (with negative attno's) but client coders liked the flag, so we went with that. > > > BTW would we do nothing for clients after all ? > > > > Clients will now need to check that dropped flag. > > Clients would have to check the flag everywhere > pg_attribute appears. > Why should clients do such a thing ? Well, good question. They could easily skip the dropped columns if we used negative attno's because they usually already skip system columns. However, they prefered a specific dropped column flag and positive attno's. I don't know why. They would have to explain. From my perspective, when client coders like Dave Page and others say they would prefer the flag to the negative attno's, I don't have to understand. I just take their word for it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> What I asked you is what *harder to fix* means.
> Uh, some said that having attno's like 1,2,3,5,7,8,9 with gaps would
> cause coding problems in client applications, and that was easier to
> have the numbers as 1-9 and check a flag if the column is dropped. Why
> that is easier than having gaps, I don't understand. I voted for the
> gaps (with negative attno's) but client coders liked the flag, so we
> went with that.
It seems to me that the problems Chris is noticing have to do with
gaps in the sequence of valid (positive) attnums. I don't believe that
the negative-attnum approach to marking deleted columns would make those
issues any easier (or harder) to fix. Either way you have a gap.
But since the historical convention is "negative attnum is a system
column", and deleted columns are *not* system columns, I prefer the idea
of using a separate marker for deleted columns. AFAICT the comments
from application coders have also been that they don't want to confuse
these two concepts.
regards, tom lane
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > Hiroshi Inoue wrote: > > > > > BTW would we do nothing for clients after all ? > > > > > > Clients will now need to check that dropped flag. > > > > Clients would have to check the flag everywhere > > pg_attribute appears. > > Why should clients do such a thing ? > > Well, good question. They could easily skip the dropped columns if we > used negative attno's because they usually already skip system columns. > However, they prefered a specific dropped column flag and positive > attno's. I don't know why. They would have to explain. I don't stick to negative attno's but > >From my perspective, when client coders like Dave Page and others say > they would prefer the flag to the negative attno's, I don't have to > understand. I just take their word for it. do they really love to check attisdropped everywhere ? Isn't it the opposite of the encapsulation ? I don't understand why we would do nothing for clients. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> What I asked you is what *harder to fix* means. > > > Uh, some said that having attno's like 1,2,3,5,7,8,9 with gaps would > > cause coding problems in client applications, and that was easier to > > have the numbers as 1-9 and check a flag if the column is dropped. Why > > that is easier than having gaps, I don't understand. I voted for the > > gaps (with negative attno's) but client coders liked the flag, so we > > went with that. > > It seems to me that the problems Chris is noticing have to do with > gaps in the sequence of valid (positive) attnums. I don't believe that > the negative-attnum approach to marking deleted columns would make those > issues any easier (or harder) to fix. Either way you have a gap. Have I ever mentioned that negative attno's is better than the attisdropped flag implemetation in the handling of gaps in attnums ? And I don't object to the attisdropped flag implemetation as long as it doesn't scatter the attisdropped test around client applications. Why would you like to do nothing for clients ? regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Have I ever mentioned that negative attno's is better
> than the attisdropped flag implemetation in the handling
> of gaps in attnums ?
How so? I don't see any improvement ...
regards, tom lane
Tom Lane wrote: > > Hiroshi Inoue <Inoue@tpf.co.jp> writes: > > Have I ever mentioned that negative attno's is better > > than the attisdropped flag implemetation in the handling > > of gaps in attnums ? > > How so? I don't see any improvement ... Sorry please ignore my above words if it has no meanig to you. My comments about this item always seem to be misunderstood. I've never intended to persist that my trial work using negative attno's was better than the attisdropped implementa- tion. I've only intended to guard my work from being evaluated unfairly. In my feeling you evaluated my work unfairly without any verfication twice. I've protected againast you about it each time but never got your explicit reply. Or have I missed your reply ? regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
On Wed, 2002-07-17 at 09:11, Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > From my perspective, when client coders like Dave Page and others say > > they would prefer the flag to the negative attno's, I don't have to > > understand. I just take their word for it. > > do they really love to check attisdropped everywhere ? > Isn't it the opposite of the encapsulation ? > I don't understand why we would do nothing for clients. AFAIK, there is separate work being done on defining SQL99 compatible system views, that most client apps could and should use. But those (few) apps that still need intimate knowledge about postrges' internals will always have to query the original system _tables_. Also, as we have nothing like Oracles ROWNR, I think it will be quite hard to have colnums without gaps in the system views, so we could perhaps have a stopgap solution of adding logical column numbers ( (pg_attribute.attlognum) that will be changed every time a col is added/dropped just for that purpose. ------------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> Also, as we have nothing like Oracles ROWNR, I think it will be quite
> hard to have colnums without gaps in the system views, so we could
> perhaps have a stopgap solution of adding logical column numbers (
> (pg_attribute.attlognum) that will be changed every time a col is
> added/dropped just for that purpose.
[ thinks... ] I don't believe this would make life any easier, really.
Inside the backend it's not much help, because we still have to look
at every single attnum reference to see if it should be logical or
physical attnum. On the client side it seems promising at first sight
... but the client will still break if it tries to correlate the
logical colnum it sees with physical colnums in pg_attrdef and other
system catalogs.
Bottom line AFAICT is that it's a lot of work and a lot of code
to examine either way :-(
regards, tom lane
Hannu Krosing wrote: > > On Wed, 2002-07-17 at 09:11, Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > From my perspective, when client coders like Dave Page and others say > > > they would prefer the flag to the negative attno's, I don't have to > > > understand. I just take their word for it. > > > > do they really love to check attisdropped everywhere ? > > Isn't it the opposite of the encapsulation ? > > I don't understand why we would do nothing for clients. > > AFAIK, there is separate work being done on defining SQL99 compatible > system views, that most client apps could and should use. > > But those (few) apps that still need intimate knowledge about postrges' > internals will always have to query the original system _tables_. > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > hard to have colnums without gaps in the system views, Agreed. However do we have to give up all views which omit dropped columns ? > so we could > perhaps have a stopgap solution of adding logical column numbers ( > (pg_attribute.attlognum) that will be changed every time a col is > added/dropped just for that purpose. > > ------------- > Hannu -- Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
> > But those (few) apps that still need intimate knowledge about postrges' > > internals will always have to query the original system _tables_. > > > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > > hard to have colnums without gaps in the system views, > > Agreed. However do we have to give up all views which omit > dropped columns ? What's Oracle's ROWNR? Chris
On Wed, 2002-07-17 at 11:29, Christopher Kings-Lynne wrote: > > > But those (few) apps that still need intimate knowledge about postrges' > > > internals will always have to query the original system _tables_. > > > > > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > > > hard to have colnums without gaps in the system views, > > > > Agreed. However do we have to give up all views which omit > > dropped columns ? > > What's Oracle's ROWNR? A pseudocolumn that is always the number of row as it is retrieved. so if we had it, we could do something like select ROWNUM as attlognum, attname from ( select attname from pg_attribute where attrelid = XXX and attisdropped order by attnum ) att order by attlognum; and have nice consecutive colnums the internal select is needed because ROWNUM is generated in the executor as the tuple is output, so sorting it later would mess it up ------------- Hannu
I sent a draft by mistake, sorry. Hannu Krosing wrote: > > On Wed, 2002-07-17 at 09:11, Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > From my perspective, when client coders like Dave Page and others say > > > they would prefer the flag to the negative attno's, I don't have to > > > understand. I just take their word for it. > > > > do they really love to check attisdropped everywhere ? > > Isn't it the opposite of the encapsulation ? > > I don't understand why we would do nothing for clients. > > AFAIK, there is separate work being done on defining SQL99 compatible > system views, that most client apps could and should use. > > But those (few) apps that still need intimate knowledge about postrges' > internals will always have to query the original system _tables_. > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > hard to have colnums without gaps in the system views, Agreed. However do we have to give up all views which omit dropped columns ? Logical numbers aren't always needed. I think the system view created by 'CREATE VIEW xxxx as select * from pg_attribute where not attisdropped' has its reason for existing. regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
> -----Original Message----- > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] > Sent: 17 July 2002 05:12 > To: Bruce Momjian > Cc: Christopher Kings-Lynne; Tom Lane; Rod Taylor; > PostgreSQL-development > Subject: Re: [HACKERS] DROP COLUMN > > > > >From my perspective, when client coders like Dave Page and > others say > > they would prefer the flag to the negative attno's, I don't have to > > understand. I just take their word for it. > > do they really love to check attisdropped everywhere ? > Isn't it the opposite of the encapsulation ? > I don't understand why we would do nothing for clients. In pgAdmin's case, this involves one test (maybe 3 lines of code), because all access to column info is made through one class. The reason I voted for attisdropped is that the negative attnum's are assumed by pgAdmin to be 'system columns', not 'any column that doesn't belong to the user'. Coding around a change like that - whilst not necessarily harder - would certainly be messier. Regards, Dave.
Tom Lane wrote: > > Hannu Krosing <hannu@tm.ee> writes: > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > > hard to have colnums without gaps in the system views, so we could > > perhaps have a stopgap solution of adding logical column numbers ( > > (pg_attribute.attlognum) that will be changed every time a col is > > added/dropped just for that purpose. > > [ thinks... ] I don't believe this would make life any easier, really. > Inside the backend it's not much help, because we still have to look > at every single attnum reference to see if it should be logical or > physical attnum. On the client side it seems promising at first sight > ... but the client will still break if it tries to correlate the > logical colnum it sees with physical colnums in pg_attrdef and other > system catalogs. Why do we have to give up all even though we can't handle physical/logical attnums in the same way ? regards, Hiroshi Inouehttp://w2422.nsk.ne.jp/~inoue/
On Wed, 2002-07-17 at 08:26, Tom Lane wrote: > Hannu Krosing <hannu@tm.ee> writes: > > Also, as we have nothing like Oracles ROWNR, I think it will be quite > > hard to have colnums without gaps in the system views, so we could > > perhaps have a stopgap solution of adding logical column numbers ( > > (pg_attribute.attlognum) that will be changed every time a col is > > added/dropped just for that purpose. > > [ thinks... ] I don't believe this would make life any easier, really. > Inside the backend it's not much help, because we still have to look > at every single attnum reference to see if it should be logical or > physical attnum. I meant this as a workaround for missing ROWNR pseudocolumn. All backend functions would still use real attnum's. And I doubt that backend will ever work though system views. Adding them should touch _only_ CREATE TABLE, ADD COLUMN, DROP COLUMN plus the system views and possibly output from SELECT(*), if we allow logical reordering of columns by changing attlognum. Of course we would not need them if we had ROWNR (or was it ROWNUM ;), except for the hypothetical column reordering (which would be useful for ALTER COLUMN CHANGE TYPE too) > On the client side it seems promising at first sight > ... but the client will still break if it tries to correlate the > logical colnum it sees with physical colnums in pg_attrdef and other > system catalogs. One can alway look it up in pg_attribute ;) Just remember to use attlognum _only_ for presentation. > Bottom line AFAICT is that it's a lot of work and a lot of code > to examine either way :-( Yes, I see that it can open another can of worms . -------------- Hannu
On Wed, 2002-07-17 at 08:48, Hiroshi Inoue wrote:
> I sent a draft by mistake, sorry.
>
> Hannu Krosing wrote:
> >
> > On Wed, 2002-07-17 at 09:11, Hiroshi Inoue wrote:
> > > Bruce Momjian wrote:
> > >
> > > > From my perspective, when client coders like Dave Page and others say
> > > > they would prefer the flag to the negative attno's, I don't have to
> > > > understand. I just take their word for it.
> > >
> > > do they really love to check attisdropped everywhere ?
> > > Isn't it the opposite of the encapsulation ?
> > > I don't understand why we would do nothing for clients.
> >
> > AFAIK, there is separate work being done on defining SQL99 compatible
> > system views, that most client apps could and should use.
> >
> > But those (few) apps that still need intimate knowledge about postrges'
> > internals will always have to query the original system _tables_.
> >
> > Also, as we have nothing like Oracles ROWNR, I think it will be quite
> > hard to have colnums without gaps in the system views,
>
> Agreed. However do we have to give up all views which omit
> dropped columns ? Logical numbers aren't always needed.
Of course not. I just proposed it as a solution for getting
ORDINAL_POSITION for ANSI/ISO system view COLUMNS.
The standard view is defined below but we will no doubt have to
implement it differently ;)
CREATE VIEW COLUMNS AS SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, C.COLUMN_NAME,
ORDINAL_POSITION, CASE WHEN EXISTS ( SELECT * FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA ) = (S.CATALOG_NAME, S.SCHEMA_NAME ) AND
( SCHEMA_OWNER IN ( PUBLIC , CURRENT_USER ) OR SCHEMA_OWNER
IN ( SELECT ROLE_NAME FROM ENABLED_ROLES ) ) ) THEN COLUMN_DEFAULT
ELSE NULL END AS COLUMN_DEFAULT, IS_NULLABLE, COALESCE (D1.DATA_TYPE, D2.DATA_TYPE) AS
DATA_TYPE, COALESCE (D1.CHARACTER_MAXIMUM_LENGTH, D2.CHARACTER_MAXIMUM_LENGTH) AS
CHARACTER_MAXIMUM_LENGTH, COALESCE (D1.CHARACTER_OCTET_LENGTH, D2.CHARACTER_OCTET_LENGTH) AS
CHARACTER_OCTET_LENGTH, COALESCE (D1.NUMERIC_PRECISION, D2.NUMERIC_PRECISION) AS NUMERIC_PRECISION,
COALESCE(D1.NUMERIC_PRECISION_RADIX, D2.NUMERIC_PRECISION_RADIX) AS NUMERIC_PRECISION_RADIX, COALESCE
(D1.NUMERIC_SCALE,D2.NUMERIC_SCALE) AS NUMERIC_SCALE, COALESCE (D1.DATETIME_PRECISION,
D2.DATETIME_PRECISION) AS DATETIME_PRECISION, COALESCE (D1.INTERVAL_TYPE, D2.INTERVAL_TYPE)
AS INTERVAL_TYPE, COALESCE (D1.INTERVAL_PRECISION, D2.INTERVAL_PRECISION) AS INTERVAL_PRECISION,
COALESCE(C1.CHARACTER_SET_CATALOG, C2.CHARACTER_SET_CATALOG) AS CHARACTER_SET_CATALOG, COALESCE
(C1.CHARACTER_SET_SCHEMA,C2.CHARACTER_SET_SCHEMA) AS CHARACTER_SET_SCHEMA, COALESCE
(C1.CHARACTER_SET_NAME,C2.CHARACTER_SET_NAME) AS CHARACTER_SET_NAME, COALESCE (D1.COLLATION_CATALOG,
D2.COLLATION_CATALOG) AS COLLATION_CATALOG, COALESCE (D1.COLLATION_SCHEMA, D2.COLLATION_SCHEMA)
AS COLLATION_SCHEMA, COALESCE (D1.COLLATION_NAME, D2.COLLATION_NAME) AS COLLATION_NAME,
DOMAIN_CATALOG,DOMAIN_SCHEMA, DOMAIN_NAME, COALESCE (D1.USER_DEFINED_TYPE_CATALOG,
D2.USER_DEFINED_TYPE_CATALOG) AS UDT_CATALOG, COALESCE (D1.USER_DEFINED_TYPE_SCHEMA,
D2.USER_DEFINED_TYPE_SCHEMA) AS UDT_SCHEMA, COALESCE (D1.USER_DEFINED_TYPE_NAME,
D2.USER_DEFINED_TYPE_NAME) AS UDT_NAME, COALESCE (D1.SCOPE_CATALOG, D2.SCOPE_CATALOG) AS SCOPE_CATALOG,
COALESCE (D1.SCOPE_SCHEMA, D2.SCOPE_SCHEMA) AS SCOPE_SCHEMA, COALESCE (D1.SCOPE_NAME, D2.SCOPE_NAME) AS
SCOPE_NAME, COALESCE (D1.MAXIMUM_CARDINALITY, D2.MAXIMUM_CARDINALITY) AS MAXIMUM_CARDINALITY,
COALESCE(D1.DTD_IDENTIFIER, D2.DTD_IDENTIFIER) AS DTD_IDENTIFIER, IS_SELF_REFERENCING FROM ( (
DEFINITION_SCHEMA.COLUMNSAS C LEFT JOIN ( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
LEFTJOIN DEFINITION_SCHEMA.COLLATIONS AS C1 ON ( ( C1.COLLATION_CATALOG, C1.COLLATION_SCHEMA,
C1.COLLATION_NAME ) = ( D1.COLLATION_CATALOG, D1.COLLATION_SCHEMA,
D1.COLLATION_NAME ) ) ) ON ( ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, 'TABLE',
C.DTD_IDENTIFIER) = ( D1.OBJECT_CATALOG, D1.OBJECT_SCHEMA, D1.OBJECT_NAME,
D1.OBJECT_TYPE,D1.DTD_IDENTIFIER ) ) ) ) LEFT JOIN ( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D2
LEFT JOIN DEFINITION_SCHEMA.COLLATIONS AS C2 ON ( ( C2.COLLATION_CATALOG, C2.COLLATION_SCHEMA,
C2.COLLATION_NAME ) = ( D2.COLLATION_CATALOG, D2.COLLATION_SCHEMA,
D2.COLLATION_NAME) ) ) ON ( ( C.DOMAIN_CATALOG, C.DOMAIN_SCHEMA, C.DOMAIN_NAME, 'DOMAIN',
C.DTD_IDENTIFIER) = ( D2.OBJECT_CATALOG, D2.OBJECT_SCHEMA, D2.OBJECT_NAME, D2.OBJECT_TYPE,
D2.DTD_IDENTIFIER) ) WHERE ( C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROMDEFINITION_SCHEMA.COLUMN_PRIVILEGES WHERE ( SCHEMA_OWNER IN ( 'PUBLIC',
CURRENT_USER) OR SCHEMA_OWNER IN ( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) ) AND C.TABLE_CATALOG = ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );
----------------
Hannu
Hannu Krosing <hannu@tm.ee> writes:
> All backend functions would still use real attnum's. And I doubt that
> backend will ever work though system views.
> Adding them should touch _only_ CREATE TABLE, ADD COLUMN, DROP COLUMN
> plus the system views and possibly output from SELECT(*), if we allow
> logical reordering of columns by changing attlognum.
Hmm. That last point is attractive enough to make it interesting to do.
Christopher, you're the man doing the legwork ... what do you think?
Offhand I'd think that expansion of "SELECT *" and association of
column aliases to specific columns would be the two places that would
need work to support attlognum; but we know they're both broken anyway
by introduction of dropped columns.
regards, tom lane
> Hannu Krosing <hannu@tm.ee> writes: > > All backend functions would still use real attnum's. And I doubt that > > backend will ever work though system views. > > Adding them should touch _only_ CREATE TABLE, ADD COLUMN, DROP COLUMN > > plus the system views and possibly output from SELECT(*), if we allow > > logical reordering of columns by changing attlognum. > > Hmm. That last point is attractive enough to make it interesting to do. > > Christopher, you're the man doing the legwork ... what do you think? > Offhand I'd think that expansion of "SELECT *" and association of > column aliases to specific columns would be the two places that would > need work to support attlognum; but we know they're both broken anyway > by introduction of dropped columns. Sure you don't want me to submit a working patch for DROP COLUMN first and then do it after? It wouldn't even cause any backward compatibility problems would it? Older clients would just order the columns by attnum... Chris