Hi All,

I have given up working on the BETWEEN node.  It got to the stage where I
realised I was really out of my depth!  Rod Taylor has indicated an interest
in the problem and I have sent him my latest patch, so hopefully he'll be
able to crack it.

So instead, I've taken up with the DROP COLUMN crusade.  It seems that the
following are the jobs that need to be done:

* Add attisdropped to pg_attribute
  - Looking for takers for this one, otherwise I'll look into it.
* Fill out AlterTableDropColumn
  - I've done this, with the assumption that attisdropped exists.  It sets
attisdropped to true, drops the column default and renames the column.
(Plus does all other normal ALTER TABLE checks)
* Modify parser and other places to ignore dropped columns
  - This is also up for grabs.
* Modify psql and pg_dump to handle dropped columns
  - I've done this.

Once the above is done, we have a working drop column implementation.

* Modify all other interfaces, JDBC, etc. to handle dropped cols.
  - I think this can be suggested to the relevant developers once the above
is committed!

* Modify VACUUM to add a RECLAIM option to reduce on disk table size.
  - This is out of my league, so it's up for grabs

I have approached a couple of people off-list to see if they're interested
in helping, so please post to the list if you intend to work on something.

It has also occurred to me that once drop column exists, users will be able
to change the type of their columns manually (ie. create a new col, update
all values, drop the old col).  So, there is no reason why this new
attisdropped field shouldn't allow us to implement a full ALTER TABLE/SET
TYPE sort of feature - cool huh?

Chris




Wed, 3 Jul 2002 16:46:55 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
BETWEEN Node & DROP COLUMN
> all values, drop the old col).  So, there is no reason why this new
> attisdropped field shouldn't allow us to implement a full ALTER TABLE/SET
> TYPE sort of feature - cool huh?


I've not looked in a while, but the column rename code did not account
for issues in foreign keys, etc.  Those should be easier to ferret out
soon, but may not be so nice to change yet.

It should also be noted that an ALTER TABLE / SET TYPE implemented with
the above idea with run into the 2x diskspace issue as well as take
quite a while to process.




03 Jul 2002 07:24:50 -0400
Rod Taylor <rbt@zort.ca>
Re: BETWEEN Node & DROP COLUMN
> I've not looked in a while, but the column rename code did not account
> for issues in foreign keys, etc.  Those should be easier to ferret out
> soon, but may not be so nice to change yet.

Which is probably a good reason for us to offer it as an all-in-one command,
rather than expecting them to do it manually...

> It should also be noted that an ALTER TABLE / SET TYPE implemented with
> the above idea with run into the 2x diskspace issue as well as take
> quite a while to process.

I think that if the 'SET TYPE' operation is ever to be rollback-able, it
will need to use 2x diskspace.  If it's overwritten in place, there's no
chance of fallback...  I think that a DBA would choose to use the command
knowing full well what it requires?  Better than not offering them the
choice at all!

Chris





Wed, 3 Jul 2002 20:23:52 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
> > It should also be noted that an ALTER TABLE / SET TYPE implemented with
> > the above idea with run into the 2x diskspace issue as well as take
> > quite a while to process.
> 
> I think that if the 'SET TYPE' operation is ever to be rollback-able, it
> will need to use 2x diskspace.  If it's overwritten in place, there's no
> chance of fallback...  I think that a DBA would choose to use the command
> knowing full well what it requires?  Better than not offering them the
> choice at all!

True, but if we did the multi-version thing in pg_attribute we may be
able to coerce to the right type on the way out making it a high speed
change.




03 Jul 2002 08:32:30 -0400
Rod Taylor <rbt@zort.ca>
Re: BETWEEN Node & DROP COLUMN
On Wed, 2002-07-03 at 14:32, Rod Taylor wrote:
> > > It should also be noted that an ALTER TABLE / SET TYPE implemented with
> > > the above idea with run into the 2x diskspace issue as well as take
> > > quite a while to process.
> > 
> > I think that if the 'SET TYPE' operation is ever to be rollback-able, it
> > will need to use 2x diskspace.  If it's overwritten in place, there's no
> > chance of fallback...  I think that a DBA would choose to use the command
> > knowing full well what it requires?  Better than not offering them the
> > choice at all!
> 
> True, but if we did the multi-version thing in pg_attribute we may be
> able to coerce to the right type on the way out making it a high speed
> change.

If I understand you right, i.e. you want to do the conversion at each
select(), then the change is high speed but all subsequent queries using
it will pay a a speed penalty, not to mention added complexity of the
whole thing.

I don't think that making changes quick autweights added  slowness and
complexity - changes are meant to be slow ;)

The real-life analogue to the proposed scenario would be adding one
extra wheel next to each existing one in a car in order to make it
possible to change tyres while driving - while certainly possible nobody
actually does it.

---------------
Hannu






03 Jul 2002 16:55:38 +0200
Hannu Krosing <hannu@tm.ee>
Re: BETWEEN Node & DROP COLUMN
Christopher Kings-Lynne wrote:
> Hi All,
>
> I have given up working on the BETWEEN node.  It got to the stage where I
> realised I was really out of my depth!  Rod Taylor has indicated an interest
> in the problem and I have sent him my latest patch, so hopefully he'll be
> able to crack it.
>
> So instead, I've taken up with the DROP COLUMN crusade.  It seems that the
> following are the jobs that need to be done:

Great crusade!

> * Add attisdropped to pg_attribute
>   - Looking for takers for this one, otherwise I'll look into it.

I can do this for you.  Just let me know when.

> * Fill out AlterTableDropColumn
>   - I've done this, with the assumption that attisdropped exists.  It sets
> attisdropped to true, drops the column default and renames the column.
> (Plus does all other normal ALTER TABLE checks)
> * Modify parser and other places to ignore dropped columns
>   - This is also up for grabs.

As I remember, Hiroshi's drop column changed the attribute number to a
special negative value, which required lots of changes to track.
Keeping the same number and just marking the column as dropped is a big
win.  This does push the coding out the client though.

> * Modify psql and pg_dump to handle dropped columns
>   - I've done this.
> 
> Once the above is done, we have a working drop column implementation.
> 
> * Modify all other interfaces, JDBC, etc. to handle dropped cols.
>   - I think this can be suggested to the relevant developers once the above
> is committed!
> 
> * Modify VACUUM to add a RECLAIM option to reduce on disk table size.
>   - This is out of my league, so it's up for grabs

Will UPDATE on a row set the deleted column to NULL?  If so, the
disk space used by the column would go away over time.  In fact, a
simple:
	
	UPDATE tab SET col = col;
	VACUUM;

would remove the data stored in the deleted column;  no change to VACUUM
needed.

> I have approached a couple of people off-list to see if they're interested
> in helping, so please post to the list if you intend to work on something.
> 
> It has also occurred to me that once drop column exists, users will be able
> to change the type of their columns manually (ie. create a new col, update
> all values, drop the old col).  So, there is no reason why this new
> attisdropped field shouldn't allow us to implement a full ALTER TABLE/SET
> TYPE sort of feature - cool huh?

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



Wed, 3 Jul 2002 12:18:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Hiroshi Inoue wrote:
> > As I remember, Hiroshi's drop column changed the attribute number to a
> > special negative value, which required lots of changes to track.
> 
> ??? What do you mean by *lots of* ?

Yes, please remind me.  Was your solution renumbering the attno values? 
I think there are fewer cases to fix if we keep the existing attribute
numbering and just mark the column as deleted.  Is this accurate?

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



Wed, 3 Jul 2002 20:25:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN


Bruce Momjian wrote:
> 
> Christopher Kings-Lynne wrote:
> > Hi All,
> >
> > I have given up working on the BETWEEN node.  It got to the stage where I
> > realised I was really out of my depth!  Rod Taylor has indicated an interest
> > in the problem and I have sent him my latest patch, so hopefully he'll be
> > able to crack it.
> >
> > So instead, I've taken up with the DROP COLUMN crusade.  It seems that the
> > following are the jobs that need to be done:
> 
> Great crusade!
> 
> > * Add attisdropped to pg_attribute
> >   - Looking for takers for this one, otherwise I'll look into it.
> 
> I can do this for you.  Just let me know when.
> 
> > * Fill out AlterTableDropColumn
> >   - I've done this, with the assumption that attisdropped exists.  It sets
> > attisdropped to true, drops the column default and renames the column.
> > (Plus does all other normal ALTER TABLE checks)
> > * Modify parser and other places to ignore dropped columns
> >   - This is also up for grabs.
> 
> As I remember, Hiroshi's drop column changed the attribute number to a
> special negative value, which required lots of changes to track.

??? What do you mean by *lots of* ?

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 09:26:01 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > > As I remember, Hiroshi's drop column changed the attribute number to a
> > > special negative value, which required lots of changes to track.
> >
> > ??? What do you mean by *lots of* ?
> 
> Yes, please remind me.  Was your solution renumbering the attno values?

Yes though I don't intend to object to Christopher's proposal.

> I think there are fewer cases to fix if we keep the existing attribute
> numbering and just mark the column as deleted.  Is this accurate?

No. I don't understand why you think so. 

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 09:37:24 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > Hiroshi Inoue wrote:
> > > > As I remember, Hiroshi's drop column changed the attribute number to a
> > > > special negative value, which required lots of changes to track.
> > >
> > > ??? What do you mean by *lots of* ?
> > 
> > Yes, please remind me.  Was your solution renumbering the attno values?
> 
> Yes though I don't intend to object to Christopher's proposal.
> 
> > I think there are fewer cases to fix if we keep the existing attribute
> > numbering and just mark the column as deleted.  Is this accurate?
> 
> No. I don't understand why you think so. 

With the isdropped column, you really only need to deal with '*'
expansion in a few places, and prevent the column from being accessed. 
With renumbering, the backend loops that go through the attnos have to
be dealt with.

Is this correct?  I certainly prefer attno renumbering to isdropped
because it allows us to get DROP COLUMN without any client changes, or
at least with fewer because the dropped column has a negative attno.  Is
this accurate?

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



Wed, 3 Jul 2002 20:38:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN


Bruce Momjian wrote:
> 
> Hiroshi Inoue wrote:
> > Bruce Momjian wrote:
> > >
> > > Hiroshi Inoue wrote:
> > > > > As I remember, Hiroshi's drop column changed the attribute number to a
> > > > > special negative value, which required lots of changes to track.
> > > >
> > > > ??? What do you mean by *lots of* ?
> > >
> > > Yes, please remind me.  Was your solution renumbering the attno values?
> >
> > Yes though I don't intend to object to Christopher's proposal.
> >
> > > I think there are fewer cases to fix if we keep the existing attribute
> > > numbering and just mark the column as deleted.  Is this accurate?
> >
> > No. I don't understand why you think so.
> 
> With the isdropped column, you really only need to deal with '*'
> expansion in a few places, and prevent the column from being accessed.
> With renumbering, the backend loops that go through the attnos have to
> be dealt with.

I used the following macro in my trial implementation.
 #define COLUMN_IS_DROPPED(attribute) ((attribute)->attnum <= 
DROP_COLUMN_OFFSET)
The places where the macro was put are exactly the places
where attisdropped must be checked.

The difference is essentially little. Please don't propagate
a wrong information. 
 
> Is this correct?  I certainly prefer attno renumbering to isdropped
> because it allows us to get DROP COLUMN without any client changes,

Unfortunately many apps rely on the fact that the attnos are
consecutive starting from 1. It was the main reason why Tom
rejected my trial. Nothing has changed about it.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 10:27:15 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
> > > > Yes, please remind me.  Was your solution renumbering the
> attno values?
> > >
> > > Yes though I don't intend to object to Christopher's proposal.

Hiroshi,

I am thinking of rolling back my CVS to see if there's code from your
previous test implementation that we can use.  Apart from the DropColumn
function itself, what other changes did you make?  Did you have
modifications for '*' expansion in the parser, etc.?

Chris




Thu, 4 Jul 2002 10:00:11 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Christopher Kings-Lynne wrote:
> > > > > Yes, please remind me.  Was your solution renumbering the
> > attno values?
> > > >
> > > > Yes though I don't intend to object to Christopher's proposal.
> 
> Hiroshi,
> 
> I am thinking of rolling back my CVS to see if there's code from your
> previous test implementation that we can use.  Apart from the DropColumn
> function itself, what other changes did you make?  Did you have
> modifications for '*' expansion in the parser, etc.?

Yes, please review Hiroshi's work.  It is good work.  Can we have an
analysis of Hiroshi's approach vs the isdropped case.

Is it better to renumber the attno or set a column to isdropped.  The
former may be easier on the clients.

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



Wed, 3 Jul 2002 22:18:27 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
> > I am thinking of rolling back my CVS to see if there's code from your
> > previous test implementation that we can use.  Apart from the DropColumn
> > function itself, what other changes did you make?  Did you have
> > modifications for '*' expansion in the parser, etc.?
>
> Yes, please review Hiroshi's work.  It is good work.  Can we have an
> analysis of Hiroshi's approach vs the isdropped case.

Yes, it is.  I've rolled it back and I'm already incorporating his changes
to the parser into my patch.  I just have to grep all the source code for
'HACK' to find all the changes.  It's all very handy.

> Is it better to renumber the attno or set a column to isdropped.  The
> former may be easier on the clients.

Well, obviously I prefer the attisdropped approach.  I think it's clearer
and there's less confusion.  As a head developer for phpPgAdmin that's what
I'd prefer...  Hiroshi obviously prefers his solution, but doesn't object to
mine/Tom's.  I think that with all the schema-related changes that clients
will have to handle in 7.3, we may as well hit them with the dropped column
stuff in the same go, that way there's fewer rounds of clients scrambling to
keep up with the server.

I intend to email every single postgres client I can find and tell them
about the new changes, well before we release 7.3...

Chris




Thu, 4 Jul 2002 10:40:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Christopher Kings-Lynne wrote:
> > > I am thinking of rolling back my CVS to see if there's code from your
> > > previous test implementation that we can use.  Apart from the DropColumn
> > > function itself, what other changes did you make?  Did you have
> > > modifications for '*' expansion in the parser, etc.?
> >
> > Yes, please review Hiroshi's work.  It is good work.  Can we have an
> > analysis of Hiroshi's approach vs the isdropped case.
> 
> Yes, it is.  I've rolled it back and I'm already incorporating his changes
> to the parser into my patch.  I just have to grep all the source code for
> 'HACK' to find all the changes.  It's all very handy.

Yes.  It should have been accepted long ago, but we were waiting for a
"perfect" solution which we all know now will never come.

> 
> > Is it better to renumber the attno or set a column to isdropped.  The
> > former may be easier on the clients.
> 
> Well, obviously I prefer the attisdropped approach.  I think it's clearer
> and there's less confusion.  As a head developer for phpPgAdmin that's what
> I'd prefer...  Hiroshi obviously prefers his solution, but doesn't object to

OK, can you explain the issues from a server and client perspective,
i.e. renumbering vs isdropped?

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



Wed, 3 Jul 2002 22:43:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Christopher Kings-Lynne wrote:
> 
> > > > > Yes, please remind me.  Was your solution renumbering the
> > attno values?
> > > >
> > > > Yes though I don't intend to object to Christopher's proposal.
> 
> Hiroshi,
> 
> I am thinking of rolling back my CVS to see if there's code from your
> previous test implementation that we can use.  Apart from the DropColumn
> function itself, what other changes did you make?  Did you have
> modifications for '*' expansion in the parser, etc.?

Don't mind my posting.
I'm only correcting a misunderstanding for my work.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 11:44:45 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
> > Well, obviously I prefer the attisdropped approach.  I think
> it's clearer
> > and there's less confusion.  As a head developer for phpPgAdmin
> that's what
> > I'd prefer...  Hiroshi obviously prefers his solution, but
> doesn't object to
>
> OK, can you explain the issues from a server and client perspective,
> i.e. renumbering vs isdropped?

Well in the renumbering case, the client needs to know about missing attnos
and it has to know to ignore negative attnos (which it probably does
already).  ie. psql and pg_dump wouldn't have to be modified in that case.

In the isdropped case, the client needs to know to exclude any column with
'attisdropped' set to true.

So in both cases, the client needs to be updated.  I personally prefer the
explicit 'is dropped' as opposed to the implicit 'negative number', but hey.

*sigh* Now I've gone and made an argument for the renumbering case.  I'm
going to have a good look at Hiroshi's old code and see which one is less
complicated, etc.  So far all I've really need to do is redefine Hiroshi's
COLUMN_DROPPED macro.

I'm sure that both methods could be made to handle a 'ALTER TABLE/SET TYPE'
syntax.

Chris




Thu, 4 Jul 2002 11:01:05 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Christopher Kings-Lynne wrote:
> > > Well, obviously I prefer the attisdropped approach.  I think
> > it's clearer
> > > and there's less confusion.  As a head developer for phpPgAdmin
> > that's what
> > > I'd prefer...  Hiroshi obviously prefers his solution, but
> > doesn't object to
> >
> > OK, can you explain the issues from a server and client perspective,
> > i.e. renumbering vs isdropped?
> 
> Well in the renumbering case, the client needs to know about missing attnos
> and it has to know to ignore negative attnos (which it probably does
> already).  ie. psql and pg_dump wouldn't have to be modified in that case.
> 
> In the isdropped case, the client needs to know to exclude any column with
> 'attisdropped' set to true.
> 
> So in both cases, the client needs to be updated.  I personally prefer the
> explicit 'is dropped' as opposed to the implicit 'negative number', but hey.
> 
> *sigh* Now I've gone and made an argument for the renumbering case.  I'm
> going to have a good look at Hiroshi's old code and see which one is less
> complicated, etc.  So far all I've really need to do is redefine Hiroshi's
> COLUMN_DROPPED macro.
> 
> I'm sure that both methods could be made to handle a 'ALTER TABLE/SET TYPE'
> syntax.

Yes!  This is exactly what I would like investigated.  I am embarrassed
to see that we had Hiroshi's patch all this time and never implemented
it.

I think it underscores that we have drifted too far into the code purity
camp and need a little reality check that users have needs and we should
try to meet them if we want to be successful.  How  many DROP COLUMN
gripes have we heard over the years!  Now I am upset.

OK, I calmed down now.  What I would like to know is which DROP COLUMN
method is easier on the server end, and which is easier on the client
end.  If one is easier in both places, let's use that.

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



Wed, 3 Jul 2002 23:24:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
> Unfortunately many apps rely on the fact that the attnos are
> consecutive starting from 1. It was the main reason why Tom
> rejected my trial. Nothing has changed about it.

OK, I've been looking at Hiroshi's implementation.  It's basically
semantically equivalent to mine from what I can see so far.  The only
difference really is in how the dropped columns are marked.

I've been ruminating on Hiroshi's statement at the top there.  What was the
reasoning for assuming that 'many apps rely on the fact that the attnos are
consecutive'?  Is that true?  phpPgAdmin doesn't.  In fact, phpPgAdmin won't
require any changes with Hiroshi's implementaiton and will require changes
with mine.

Anyway, an app that relies on consecutive attnos is going to have pain
skipping over attisdropped columns anyway???

In fact, I'm now beginning to think that I should just resurrect Hiroshi's
implementation.  I'm prepared to do that if people like...

Chris




Thu, 4 Jul 2002 12:50:22 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> I used the following macro in my trial implementation.
>  #define COLUMN_IS_DROPPED(attribute) ((attribute)->attnum <= 
> DROP_COLUMN_OFFSET)
> The places where the macro was put are exactly the places
> where attisdropped must be checked.

Actually, your trial required column dropped-ness to be checked in
many more places than the proposed approach does.  Since you renumbered
the dropped column, nominal column numbers didn't correspond to physical
order of values in tuples anymore; that meant checking for dropped
columns in many low-level tuple manipulations.

>> Is this correct?  I certainly prefer attno renumbering to isdropped
>> because it allows us to get DROP COLUMN without any client changes,

> Unfortunately many apps rely on the fact that the attnos are
> consecutive starting from 1. It was the main reason why Tom
> rejected my trial. Nothing has changed about it.

I'm still not thrilled about it ... but I don't see a reasonable way
around it, either.  I don't see any good way to do DROP COLUMN
without breaking applications that make such assumptions.  Unless
you have one, we may as well go for the approach that adds the least
complication to the backend.

			regards, tom lane



Thu, 04 Jul 2002 01:04:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Christopher Kings-Lynne wrote:
> > Unfortunately many apps rely on the fact that the attnos are
> > consecutive starting from 1. It was the main reason why Tom
> > rejected my trial. Nothing has changed about it.
> 
> OK, I've been looking at Hiroshi's implementation.  It's basically
> semantically equivalent to mine from what I can see so far.  The only
> difference really is in how the dropped columns are marked.
> 
> I've been ruminating on Hiroshi's statement at the top there.  What was the
> reasoning for assuming that 'many apps rely on the fact that the attnos are
> consecutive'?  Is that true?  phpPgAdmin doesn't.  In fact, phpPgAdmin won't
> require any changes with Hiroshi's implementaiton and will require changes
> with mine.
> 
> Anyway, an app that relies on consecutive attnos is going to have pain
> skipping over attisdropped columns anyway???
> 
> In fact, I'm now beginning to think that I should just resurrect Hiroshi's
> implementation.  I'm prepared to do that if people like...

Well, you have clearly identified that Hiroshi's approach is cleaner for
clients, because most clients don't need any changes.  If the server end
looks equivalent for both approaches, I suggest you get started with
Hiroshi's idea.

When Hiroshi's idea was originally proposed, some didn't like the
uncleanliness of it, and particularly relations that relied on attno
would all have to be adjusted/removed.  We didn't have pg_depend, of
course, so there was this kind of gap in knowing how to remove all
references to the dropped column.

There was also this idea that somehow the fairy software goddess was
going to come down some day and give us a cleaner way to implement DROP
COLUMN.  She still hasn't shown up.  :-)

I just read over TODO.detail/drop and my memory was correct.  It was a
mixure of having no pg_depend coupled with other ideas.  Now that
pg_depend is coming, DROP COLUMN is ripe for a solution.

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



Thu, 4 Jul 2002 01:20:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Tom Lane wrote:
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I used the following macro in my trial implementation.
> >  #define COLUMN_IS_DROPPED(attribute) ((attribute)->attnum <= 
> > DROP_COLUMN_OFFSET)
> > The places where the macro was put are exactly the places
> > where attisdropped must be checked.
> 
> Actually, your trial required column dropped-ness to be checked in
> many more places than the proposed approach does.  Since you renumbered
> the dropped column, nominal column numbers didn't correspond to physical
> order of values in tuples anymore; that meant checking for dropped
> columns in many low-level tuple manipulations.
> 
> >> Is this correct?  I certainly prefer attno renumbering to isdropped
> >> because it allows us to get DROP COLUMN without any client changes,
> 
> > Unfortunately many apps rely on the fact that the attnos are
> > consecutive starting from 1. It was the main reason why Tom
> > rejected my trial. Nothing has changed about it.
> 
> I'm still not thrilled about it ... but I don't see a reasonable way
> around it, either.  I don't see any good way to do DROP COLUMN
> without breaking applications that make such assumptions.  Unless
> you have one, we may as well go for the approach that adds the least
> complication to the backend.

It may turn out to be a choice of client-cleanliness vs. backend
cleanliness.  Seems Hiroshi already wins for client cleanliness.  We
just need to know how many extra places need to be checked in the
backend.

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



Thu, 4 Jul 2002 01:23:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> It may turn out to be a choice of client-cleanliness vs. backend
> cleanliness.  Seems Hiroshi already wins for client cleanliness.

No, he only breaks even for client cleanliness --- either approach
*will* require changes in clients that look at pg_attribute.

			regards, tom lane



Thu, 04 Jul 2002 01:26:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > It may turn out to be a choice of client-cleanliness vs. backend
> > cleanliness.  Seems Hiroshi already wins for client cleanliness.
> 
> No, he only breaks even for client cleanliness --- either approach
> *will* require changes in clients that look at pg_attribute.

Uh, Christopher already indicated three clients, psql, pg_dump, and
another that will not require changes for Hiroshi's approach, but will
require changes for isdropped.  That doesn't seem "break even" to me.

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



Thu, 4 Jul 2002 01:33:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
> Well in the renumbering case, the client needs to know about missing attnos
> and it has to know to ignore negative attnos (which it probably does
> already).  ie. psql and pg_dump wouldn't have to be modified in that case.
> In the isdropped case, the client needs to know to exclude any column with
> 'attisdropped' set to true.
> So in both cases, the client needs to be updated.

How about defining a view (or views) which hides these details? Perhaps
a view which is also defined in SQL99 as one of the information_schema
views which we might like to have anyway?

                    - Thomas



Wed, 03 Jul 2002 22:40:28 -0700
Thomas Lockhart <lockhart@fourpalms.org>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> No, he only breaks even for client cleanliness --- either approach
>> *will* require changes in clients that look at pg_attribute.

> Uh, Christopher already indicated three clients, psql, pg_dump, and
> another that will not require changes for Hiroshi's approach, but will
> require changes for isdropped.

Oh?  If either psql or pg_dump don't break, it's a mere coincidence,
because they certainly depend on attnum.  (It's also pretty much
irrelevant considering they're both under our control and hence easily
fixed.)

I'm fairly certain that Christopher is mistaken, anyhow.  Check the
manipulations of attribute defaults for a counterexample in pg_dump.

			regards, tom lane



Thu, 04 Jul 2002 01:50:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > I used the following macro in my trial implementation.
> >  #define COLUMN_IS_DROPPED(attribute) ((attribute)->attnum <=
> > DROP_COLUMN_OFFSET)
> > The places where the macro was put are exactly the places
> > where attisdropped must be checked.
> 
> Actually, your trial required column dropped-ness to be checked in
> many more places than the proposed approach does.

Have you ever really checked my trial implementation ?

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 14:53:55 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> No, he only breaks even for client cleanliness --- either approach
> >> *will* require changes in clients that look at pg_attribute.
> 
> > Uh, Christopher already indicated three clients, psql, pg_dump, and
> > another that will not require changes for Hiroshi's approach, but will
> > require changes for isdropped.
> 
> Oh?  If either psql or pg_dump don't break, it's a mere coincidence,
> because they certainly depend on attnum.  (It's also pretty much
> irrelevant considering they're both under our control and hence easily
> fixed.)
> 
> I'm fairly certain that Christopher is mistaken, anyhow.  Check the
> manipulations of attribute defaults for a counterexample in pg_dump.

Well, it seems isdropped is going to have to be checked by _any_ client,
while holes in the number will have to be checked by _some_ clients.  Is
that accurate?

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



Thu, 4 Jul 2002 02:00:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Tom Lane wrote:
>> Actually, your trial required column dropped-ness to be checked in
>> many more places than the proposed approach does.

> Have you ever really checked my trial implementation ?

Well, I've certainly stumbled over it in places like relcache.c
and preptlist.c, which IMHO should not have to know about this...
and I have little confidence that there are not more places that
would have needed fixes if the change had gotten any wide use.
You were essentially assuming that it was okay for pg_attribute.attnum
to not agree with indexes into tuple descriptors, which seems very
shaky to me.

			regards, tom lane



Thu, 04 Jul 2002 02:02:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> Actually, your trial required column dropped-ness to be checked in
> >> many more places than the proposed approach does.
> 
> > Have you ever really checked my trial implementation ?
> 
> Well, I've certainly stumbled over it in places like relcache.c
> and preptlist.c, which IMHO should not have to know about this...
> and I have little confidence that there are not more places that
> would have needed fixes if the change had gotten any wide use.
> You were essentially assuming that it was okay for pg_attribute.attnum
> to not agree with indexes into tuple descriptors, which seems very
> shaky to me.

Isn't it only the dropped column that doesn't agree with the descriptor.
The kept columns retain the same numbering, and a NULL sits in the
dropped spot, right?

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



Thu, 4 Jul 2002 02:06:04 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Well, it seems isdropped is going to have to be checked by _any_ client,
> while holes in the number will have to be checked by _some_ clients.  Is
> that accurate?

What's your point?  No client that examines pg_attribute can be trusted
until it's been examined pretty closely (as in, more closely than
Christopher looked at pg_dump).  I'd prefer to see us keep the backend
simple and trustworthy, rather than pursue a largely-illusory idea that
we might be saving some trouble on the client side.  The clients are
less likely to cause unrecoverable data corruption if something is
missed.

If we were willing to remap attnums so that clients would require *no*
changes, it would be worth doing --- but I believe we've already
rejected that approach as unworkable.  I don't think "maybe you don't
need to change, but you'd better study your code very carefully anyway"
is a big selling point.

			regards, tom lane



Thu, 04 Jul 2002 02:11:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Well, it seems isdropped is going to have to be checked by _any_ client,
> > while holes in the number will have to be checked by _some_ clients.  Is
> > that accurate?
> 
> What's your point?  No client that examines pg_attribute can be trusted
> until it's been examined pretty closely (as in, more closely than
> Christopher looked at pg_dump).  I'd prefer to see us keep the backend
> simple and trustworthy, rather than pursue a largely-illusory idea that
> we might be saving some trouble on the client side.  The clients are
> less likely to cause unrecoverable data corruption if something is
> missed.
> 
> If we were willing to remap attnums so that clients would require *no*
> changes, it would be worth doing --- but I believe we've already
> rejected that approach as unworkable.  I don't think "maybe you don't
> need to change, but you'd better study your code very carefully anyway"
> is a big selling point.

It sure is.  If most people don't need to modify their code, that is a
win.  Your logic is that we should make everyone modify their code and
somehow that will be more reliable?  No wonder people think we are more
worried about clean code than making things easier for our users.

I will vote for the option that has the less pain for our users _and_ in
the backend, but if it is close, I will prefer to make things easier on
clients/users.

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



Thu, 4 Jul 2002 02:15:46 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
> > What's your point?  No client that examines pg_attribute can be trusted
> > until it's been examined pretty closely (as in, more closely than
> > Christopher looked at pg_dump).  I'd prefer to see us keep the backend
> > simple and trustworthy, rather than pursue a largely-illusory idea that
> > we might be saving some trouble on the client side.  The clients are
> > less likely to cause unrecoverable data corruption if something is
> > missed.

I'm prepared to admit I didn't look at pg_dump too hard.  I have to say that
I agree with Tom here, but that's personal opinion.  If Tom reckons that
there's places where Hiroshi's implementation needed work and that there
would be messiness, then I'm inclined to believe him.

In all honesty, the amount of changes clients have to make to support
schemas makes checking dropped columns pale in significance.

> > If we were willing to remap attnums so that clients would require *no*
> > changes, it would be worth doing --- but I believe we've already
> > rejected that approach as unworkable.  I don't think "maybe you don't
> > need to change, but you'd better study your code very carefully anyway"
> > is a big selling point.

Exactly.  I like the whole 'explicit' idea of having attisdropped.  There's
no ifs and buts.  It's not a case of, "oh, the attnum is negative, but it's
not an arbitratily negative system column" sort of thing.

> I will vote for the option that has the less pain for our users _and_ in
> the backend, but if it is close, I will prefer to make things easier on
> clients/users.

I will vote for attisdropped.  However, I'm not a main developer and I will
go with the flow.  In the meantime, I'm developing attisdropped but using
some of Hiroshi's implementation...

Chris




Thu, 4 Jul 2002 14:27:40 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Well, it seems isdropped is going to have to be checked by _any_ client,
> > while holes in the number will have to be checked by _some_ clients.  Is
> > that accurate?
> 
> What's your point?  No client that examines pg_attribute can be trusted
> until it's been examined pretty closely (as in, more closely than
> Christopher looked at pg_dump).  I'd prefer to see us keep the backend
> simple and trustworthy, rather than pursue a largely-illusory idea that
> we might be saving some trouble on the client side.

Largely-illusory?  Almost every pg_attribute query will have to be modified
for isdropped, while Hiroshi's approach requires so few changes, we are
having trouble even finding a query that needs to be modified.  That's
pretty clear to me.

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



Thu, 4 Jul 2002 02:27:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> OK, I've been looking at Hiroshi's implementation.  It's basically
> semantically equivalent to mine from what I can see so far.  The only
> difference really is in how the dropped columns are marked.

True enough, but that's not a trivial difference.  The problem with
Hiroshi's implementation is that there's no longer a close tie between
pg_attribute.attnum and physical positions of datums in tuples.  I think
that that's going to affect a lot of low-level code, and that he hasn't
found all of it.

Keeping the attisdropped marker separate from attnum is logically
cleaner, and IMHO much less likely to lead to trouble down the road.

We should not allow ourselves to put too much weight on the fact that
some clients use "attnum > 0" as a filter for attributes that they
(think they) need not pay attention to.  That's only a historical
artifact, and it's far from clear that it will keep those clients
out of trouble anyway.

			regards, tom lane



Thu, 04 Jul 2002 02:28:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
By the way,

What happens if someone drops ALL the columns in a table?  Do you just leave
it there as-is without any columns or should it be forbidden or should it be
interpreted as a drop table?

Chris




Thu, 4 Jul 2002 14:29:34 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
> True enough, but that's not a trivial difference.  The problem with
> Hiroshi's implementation is that there's no longer a close tie between
> pg_attribute.attnum and physical positions of datums in tuples.  I think
> that that's going to affect a lot of low-level code, and that he hasn't
> found all of it.

OK, I can see how that would be a problem actually.  You'd have to regard
attnum as a 'virtual attnum' and keep having to reverse the computation to
figure out what its original attnum was...

> Keeping the attisdropped marker separate from attnum is logically
> cleaner, and IMHO much less likely to lead to trouble down the road.

I'm a purist and I like to think that good, clean, well thought out code
always results in more stable, bug free software.

> We should not allow ourselves to put too much weight on the fact that
> some clients use "attnum > 0" as a filter for attributes that they
> (think they) need not pay attention to.  That's only a historical
> artifact, and it's far from clear that it will keep those clients
> out of trouble anyway.

It's also not 'every client app' that will need to be altered.  Just DB
admin apps, of which there aren't really that many.  And remember, anyone
who uses the catalogs directly always does so at their own risk.  I think
that once we have a proper INFORMATION_SCHEMA anyway, all clients should use
that.  Heck, if INFORMATION_SCHEMA gets in in 7.3, then clients might have a
_heap_ of work to do...

Chris




Thu, 4 Jul 2002 14:36:45 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN 
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> What happens if someone drops ALL the columns in a table?

Good point.  Ideally we should allow that, but in practice I suspect
there are many places that will blow up on zero-length tuples.
Rejecting the situation might be the better part of valor ... anyway
I'm not excited about spending a lot of time searching for such bugs.

			regards, tom lane



Thu, 04 Jul 2002 02:38:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > OK, I've been looking at Hiroshi's implementation.  It's basically
> > semantically equivalent to mine from what I can see so far.  The only
> > difference really is in how the dropped columns are marked.
> 
> True enough, but that's not a trivial difference.  The problem with
> Hiroshi's implementation is that there's no longer a close tie between
> pg_attribute.attnum and physical positions of datums in tuples.  I think
> that that's going to affect a lot of low-level code, and that he hasn't
> found all of it.

Isn't that only for the dropped column.  Don't the remaining columns stay
logically clear as far as the tuple storage is concerned?

> 
> Keeping the attisdropped marker separate from attnum is logically
> cleaner, and IMHO much less likely to lead to trouble down the road.

My problem is that you are pushing the DROP COLUMN check out into almost
every client that uses pg_attribute.  And we are doing this to keep our
backend cleaner.  Seems we should do the work once, in the backend, and
not burden clients will all of this.

> We should not allow ourselves to put too much weight on the fact that
> some clients use "attnum > 0" as a filter for attributes that they
> (think they) need not pay attention to.  That's only a historical
> artifact, and it's far from clear that it will keep those clients
> out of trouble anyway.

Well, why shouldn't we use the fact that most/all clients don't look at
attno < 0, and that we have no intention of changing that requirement. 
We aren't coding in a vacuum.  We have clients, they do that already,
let's use it.

Attno < 0 is not historical.  It is in the current code, and will remain
so for the forseeable future, I think.

I honestly don't understand the priorities we are setting here.

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



Thu, 4 Jul 2002 02:39:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Tom Lane wrote:
> >> Actually, your trial required column dropped-ness to be checked in
> >> many more places than the proposed approach does.
> 
> > Have you ever really checked my trial implementation ?
> 
> Well, I've certainly stumbled over it in places like relcache.c
> and preptlist.c, which IMHO should not have to know about this...
> and I have little confidence that there are not more places that
> would have needed fixes if the change had gotten any wide use.
> You were essentially assuming that it was okay for pg_attribute.attnum
> to not agree with indexes into tuple descriptors, which seems very
> shaky to me.

I already explained it to you once in the thread Re: [HACKERS]
RFC: Restructuring pg_aggregate. How many times should I
explain the same thing ?
My trial implementation is essentially the same as adding
isdropped pg_attribute column. There's no strangeness in
my implementation.
The reason why I adopted negative attnos is as follows.
I also explained it more than twice.

1) It doesn't need initdb. It was very conveneient for
   the TRIAL implementation.
2) It's more sensitive about oversights of modification
   than isdropped column implementation.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 15:53:02 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
> My problem is that you are pushing the DROP COLUMN check out into almost
> every client that uses pg_attribute.  And we are doing this to keep our
> backend cleaner.  Seems we should do the work once, in the backend, and
> not burden clients will all of this.

As a user of Postgres, I found the following more painful:

* Anti-varchar truncation in 7.2
* Making you have to quote "timestamp"(), etc.

People mail the list every day with backwards compatibility problems.  We've
done it before, why not do it again?  In fact, I'm sure there are already
backwards compatibility problems in 7.3.

> > We should not allow ourselves to put too much weight on the fact that
> > some clients use "attnum > 0" as a filter for attributes that they
> > (think they) need not pay attention to.  That's only a historical
> > artifact, and it's far from clear that it will keep those clients
> > out of trouble anyway.
>
> Well, why shouldn't we use the fact that most/all clients don't look at
> attno < 0, and that we have no intention of changing that requirement.
> We aren't coding in a vacuum.  We have clients, they do that already,
> let's use it.
>
> Attno < 0 is not historical.  It is in the current code, and will remain
> so for the forseeable future, I think.

Problem is, the current code actually assumes that attno < 0 means that the
attribute is a system column, NOT a dropped user column.

As an example, I'd have to change all of these in the Postgres source code:

        /* Prevent them from altering a system attribute */
        if (attnum < 0)
                elog(ERROR, "ALTER TABLE: Cannot alter system attribute
\"%s\"",
                         colName);

Who knows how many other things like this are littered through the source?

Chris




Thu, 4 Jul 2002 14:54:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
> > No, he only breaks even for client cleanliness --- either approach
> > *will* require changes in clients that look at pg_attribute.
> 
> Uh, Christopher already indicated three clients, psql, pg_dump, and
> another that will not require changes for Hiroshi's approach, but will
> require changes for isdropped.  That doesn't seem "break even" to me.

And Tom pointed out that I was wrong...

Chris




Thu, 4 Jul 2002 15:07:59 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Tom Lane wrote:
> 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > OK, I've been looking at Hiroshi's implementation.  It's basically
> > semantically equivalent to mine from what I can see so far.  The only
> > difference really is in how the dropped columns are marked.
> 
> True enough, but that's not a trivial difference.

> The problem with
> Hiroshi's implementation is that there's no longer a close tie between
> pg_attribute.attnum and physical positions of datums in tuples. 

?? Where does the above consideration come from ?

BTW there seems a misunderstanding about my posting.
I'm not objecting to add attisdropped pg_attribute column.
They are essentially the same and so I used macros
like COLUMN_IS_DROPPED in my implementation so that
I can easily change the implementation to use isdropped
pg_attribute column.
I'm only correcting the unfair valuation for my
trial work.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/



Thu, 04 Jul 2002 18:21:30 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN
> BTW there seems a misunderstanding about my posting.
> I'm not objecting to add attisdropped pg_attribute column.
> They are essentially the same and so I used macros
> like COLUMN_IS_DROPPED in my implementation so that
> I can easily change the implementation to use isdropped
> pg_attribute column.
> I'm only correcting the unfair valuation for my
> trial work.

Hiroshi, I totally respect your trial work.  In fact, I'm relying on it to
do the attisdropped implementation.  I think everyone's beginning to get a
bit cranky here - I think we should all just calm down.

Chris




Thu, 4 Jul 2002 17:27:18 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
Thomas Lockhart wrote:
> > Well in the renumbering case, the client needs to know about missing attnos
> > and it has to know to ignore negative attnos (which it probably does
> > already).  ie. psql and pg_dump wouldn't have to be modified in that case.
> > In the isdropped case, the client needs to know to exclude any column with
> > 'attisdropped' set to true.
> > So in both cases, the client needs to be updated.
> 
> How about defining a view (or views) which hides these details? Perhaps
> a view which is also defined in SQL99 as one of the information_schema
> views which we might like to have anyway?

We could change pg_attribute to another name, and create a view called
pg_attribute that never returned isdropped columns to the client.  That
would allow clients to work cleanly, and the server to work cleanly.

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



Thu, 4 Jul 2002 08:20:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Largely-illusory?  Almost every pg_attribute query will have to be modified
> for isdropped, while Hiroshi's approach requires so few changes, we are
> having trouble even finding a query that needs to be modified.  That's
> pretty clear to me.

Apparently you didn't think hard about the pg_dump example.  The problem
there isn't the query so much as it is the wired-in assumption that the
retrieved rows will correspond to attnums 1-N in sequence.  That
assumption breaks either way we do it.  The illusion is thinking that
clients won't break.

I suspect it will actually be easier to fix pg_dump if we use the
attisdropped approach --- it could keep the assumption that its array
indexes equal attnums, include attisdropped explicitly in the rows
it stores, and just not output rows that have attisdropped true.
Getting rid of the index == attnum assumption will be a considerably
more subtle, and fragile, patch.

			regards, tom lane



Thu, 04 Jul 2002 12:34:35 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
> We could change pg_attribute to another name, and create a view called
> pg_attribute that never returned isdropped columns to the client.  That
> would allow clients to work cleanly, and the server to work cleanly.

Another case where having an informational schema would eliminate the
whole argument -- as the clients wouldn't need to touch the system
tables.

Any thoughts on that initial commit Peter?




04 Jul 2002 17:29:28 -0400
Rod Taylor <rbt@zort.ca>
Re: BETWEEN Node & DROP COLUMN
Rod Taylor wrote:
> > We could change pg_attribute to another name, and create a view called
> > pg_attribute that never returned isdropped columns to the client.  That
> > would allow clients to work cleanly, and the server to work cleanly.
> 
> Another case where having an informational schema would eliminate the
> whole argument -- as the clients wouldn't need to touch the system
> tables.
> 
> Any thoughts on that initial commit Peter?

 From my new understanding, the client coders _want_ to see the isdropped
row so the attno's are consecutive.

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



Thu, 4 Jul 2002 17:32:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Rod Taylor <rbt@zort.ca> writes:
>> We could change pg_attribute to another name, and create a view called
>> pg_attribute that never returned isdropped columns to the client.  That
>> would allow clients to work cleanly, and the server to work cleanly.

> Another case where having an informational schema would eliminate the
> whole argument -- as the clients wouldn't need to touch the system
> tables.

This is a long-term solution, not a near-term one.  I suspect it's
really unlikely that pg_dump, pgAdmin, etc will ever want to switch
over to the SQL-standard informational schema, because they will want
to be able to look at Postgres-specific features that are not reflected
in the standardized schema.  Certainly there will be no movement in
that direction until the informational schema is complete; a first-cut
implementation won't attract any interest at all :-(

I thought about the idea of a backward-compatible pg_attribute view,
but I don't see any efficient way to generate the consecutively-numbered
attnum column in a view; anyone?

			regards, tom lane



Thu, 04 Jul 2002 19:56:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> >> We could change pg_attribute to another name, and create a view called
> >> pg_attribute that never returned isdropped columns to the client.  That
> >> would allow clients to work cleanly, and the server to work cleanly.
> 
> > Another case where having an informational schema would eliminate the
> > whole argument -- as the clients wouldn't need to touch the system
> > tables.
> 
> This is a long-term solution, not a near-term one.  I suspect it's
> really unlikely that pg_dump, pgAdmin, etc will ever want to switch
> over to the SQL-standard informational schema, because they will want
> to be able to look at Postgres-specific features that are not reflected
> in the standardized schema.  Certainly there will be no movement in
> that direction until the informational schema is complete; a first-cut
> implementation won't attract any interest at all :-(
> 
> I thought about the idea of a backward-compatible pg_attribute view,
> but I don't see any efficient way to generate the consecutively-numbered
> attnum column in a view; anyone?

No, we can't, and because our client coders want consecutive, it is a
dead idea.  Even if we could do it, we would be feeding clients attno
values that are inaccurate, causing problems when attno is joined to
other tables.

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



Thu, 4 Jul 2002 20:03:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: BETWEEN Node & DROP COLUMN
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Even if we could do it, we would be feeding clients attno
> values that are inaccurate, causing problems when attno is joined to
> other tables.

Good point; we'd need similar views replacing pg_attrdef and probably
other places.  Messy indeed :-(

But as Dave already pointed out, it's probably pointless to worry.
The schema support in 7.3 will already de-facto break nearly every
client that inspects the system catalogs, so adding some more work
for DROP COLUMN support isn't going to make much difference.

			regards, tom lane



Thu, 04 Jul 2002 20:15:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN Node & DROP COLUMN 
> > We could change pg_attribute to another name, and create a view called
> > pg_attribute that never returned isdropped columns to the client.  That
> > would allow clients to work cleanly, and the server to work cleanly.
>
> Another case where having an informational schema would eliminate the
> whole argument -- as the clients wouldn't need to touch the system
> tables.

Since postgres has so many features that standard SQL doesn't have (eg.
custom operators), how are they going to be shown in the information schema?

Chris




Fri, 5 Jul 2002 10:07:22 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: BETWEEN Node & DROP COLUMN
On Thu, 2002-07-04 at 22:07, Christopher Kings-Lynne wrote:
> > > We could change pg_attribute to another name, and create a view called
> > > pg_attribute that never returned isdropped columns to the client.  That
> > > would allow clients to work cleanly, and the server to work cleanly.
> >
> > Another case where having an informational schema would eliminate the
> > whole argument -- as the clients wouldn't need to touch the system
> > tables.
> 
> Since postgres has so many features that standard SQL doesn't have (eg.
> custom operators), how are they going to be shown in the information schema?

I would assume we would add pg_TABLE or TABLES.pg_COLUMN as appropriate
and where it wouldn't disturbe normal usage.

If we always put pg columns at the end it shouldn't disturbe programs
which use vectors to pull information out of the DB with a target of *.




04 Jul 2002 22:48:06 -0400
Rod Taylor <rbt@zort.ca>
Re: BETWEEN Node & DROP COLUMN
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > OK, I've been looking at Hiroshi's implementation.  It's basically
> > semantically equivalent to mine from what I can see so far.  The only
> > difference really is in how the dropped columns are marked.
> 
> True enough, but that's not a trivial difference.  The problem with
> Hiroshi's implementation is that there's no longer a close tie between
> pg_attribute.attnum and physical positions of datums in tuples.  I think
> that that's going to affect a lot of low-level code, and that he hasn't
> found all of it.

Please don't propagate an unfair valuation without any verification.

regards,
Hiroshi Inoue



Sun, 7 Jul 2002 00:43:26 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: BETWEEN Node & DROP COLUMN 