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


Mon, 15 Jul 2002 23:30:05 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
DROP COLUMN
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.


15 Jul 2002 12:23:45 -0400
Rod Taylor <rbt@zort.ca>
Re: DROP COLUMN
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, Pennsylvania 19026

Mon, 15 Jul 2002 13:17:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
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

Mon, 15 Jul 2002 13:41:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
> 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


Tue, 16 Jul 2002 11:46:30 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN 
"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

Tue, 16 Jul 2002 00:09:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
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, Pennsylvania 19026

Tue, 16 Jul 2002 00:38:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
> 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


Tue, 16 Jul 2002 12:47:59 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN
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, Pennsylvania 19026

Tue, 16 Jul 2002 00:50:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
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

Tue, 16 Jul 2002 00:56:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
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, Pennsylvania 19026

Tue, 16 Jul 2002 00:58:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
> 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


Tue, 16 Jul 2002 13:02:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN
> > 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


Tue, 16 Jul 2002 13:03:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN
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, Pennsylvania 19026

Tue, 16 Jul 2002 01:04:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
"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

Tue, 16 Jul 2002 01:15:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
> -----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


Wed, 17 Jul 2002 01:19:42 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: DROP COLUMN
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, Pennsylvania 19026

Tue, 16 Jul 2002 12:30:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
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


16 Jul 2002 20:29:36 +0200
Hannu Krosing <hannu@tm.ee>
Re: DROP COLUMN
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 08:37:13 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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, Pennsylvania 19026

Tue, 16 Jul 2002 23:15:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: DROP COLUMN
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

Wed, 17 Jul 2002 00:04:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 13:11:42 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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



17 Jul 2002 09:13:09 +0500
Hannu Krosing <hannu@tm.ee>
Re: DROP COLUMN
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 13:41:08 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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



17 Jul 2002 09:44:01 +0500
Hannu Krosing <hannu@tm.ee>
Re: DROP COLUMN
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

Wed, 17 Jul 2002 00:46:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 14:26:18 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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

Wed, 17 Jul 2002 02:26:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
> > 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


Wed, 17 Jul 2002 14:29:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 15:30:23 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 15:48:42 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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 Inoue
	http://w2422.nsk.ne.jp/~inoue/

Wed, 17 Jul 2002 17:24:53 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: DROP COLUMN
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



17 Jul 2002 11:28:17 +0200
Hannu Krosing <hannu@tm.ee>
Re: DROP COLUMN
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.COLUMNS AS C
           LEFT JOIN
             ( DEFINITION_SCHEMA.DATA_TYPE_DESCRIPTOR AS D1
             LEFT JOIN
               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
                 FROM DEFINITION_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

17 Jul 2002 12:15:03 +0200
Hannu Krosing <hannu@tm.ee>
Re: DROP COLUMN
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

Wed, 17 Jul 2002 11:28:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DROP COLUMN 
> 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


Thu, 18 Jul 2002 10:10:18 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: DROP COLUMN 