Thread: DROP COLUMN

DROP COLUMN

From
Christopher Kings-Lynne
Date:
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



Re: DROP COLUMN

From
Rod Taylor
Date:
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.



Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> 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



Re: DROP COLUMN

From
Tom Lane
Date:
"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


Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> 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



Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> 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



Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> > 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



Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
Tom Lane
Date:
"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


Re: DROP COLUMN

From
"Hiroshi Inoue"
Date:
> -----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



Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
Hannu Krosing
Date:
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



Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
Bruce Momjian
Date:
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
 


Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
Hannu Krosing
Date:
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




Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> > 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



Re: DROP COLUMN

From
Hannu Krosing
Date:
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




Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
"Dave Page"
Date:

> -----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.


Re: DROP COLUMN

From
Hiroshi Inoue
Date:
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/


Re: DROP COLUMN

From
Hannu Krosing
Date:
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




Re: DROP COLUMN

From
Hannu Krosing
Date:
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


Re: DROP COLUMN

From
Tom Lane
Date:
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


Re: DROP COLUMN

From
"Christopher Kings-Lynne"
Date:
> 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