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.

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

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

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, Pennsylvania 19026

"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, Pennsylvania 19026

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 Inoue
http://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, Pennsylvania 19026

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 Inoue
http://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

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/

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

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 Inoue
http://w2422.nsk.ne.jp/~inoue/

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

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

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/

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/

-----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 Inoue
http://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.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

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