Thread

Happy column dropping

Peter Eisentraut <peter_e@gmx.net> — 2000-01-23T01:29:42Z

With caveats, it is now possible to drop columns from tables.

The implementation is based on copying the old table to a new one minus
the specified column. This procedure changes the oids of everyone
involved, so I was wondering if
a) this is a good reason to tell people to stop using oids as keys, or
b) if there is some way to keep the oids on both the records and the
pg_class entry.

Is it possible/safe to specify an oid to heap_insert (like tuple->...->oid
= x) if the oid is still in use (in the old table), or would a
heap_delete({from old table}) plus heap_insert({into new table}) work.

Is it possible/safe to change to oid of the new pg_class entry back to the
old one? In that case the trouble of moving over all the constraints, etc.
would be half the work.

Speaking of which, the current implementation loses all constraints,
triggers, rules, comments, etc. (not defaults and notnulls
though). Therefore
1) did I forget anything in the above list
2) how do I find out if the dropped column is referenced in a constraint,
trigger, rule (this is necessary for a correct RESTRICT/CASCADE
implementation)
3) once again, is it possible/safe to do the equivalent of update pg_class
set oid=old where oid=new to save this work?

Oh, btw., heaven help you if you try this on tables that are inherited
from.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T02:01:02Z

Did I miss a discussion here on implementing this, and how?  Sounds to me
like a week and a bit before planned beta, an *incomplete* feature has
been shoved into the source tree with zero forewarning or discussion ...

Okay, my turn here ... I vote for this to be *reverted*!!

On Sun, 23 Jan 2000, Peter Eisentraut wrote:

> With caveats, it is now possible to drop columns from tables.
> 
> The implementation is based on copying the old table to a new one minus
> the specified column. This procedure changes the oids of everyone
> involved, so I was wondering if
> a) this is a good reason to tell people to stop using oids as keys, or
> b) if there is some way to keep the oids on both the records and the
> pg_class entry.
> 
> Is it possible/safe to specify an oid to heap_insert (like tuple->...->oid
> = x) if the oid is still in use (in the old table), or would a
> heap_delete({from old table}) plus heap_insert({into new table}) work.
> 
> Is it possible/safe to change to oid of the new pg_class entry back to the
> old one? In that case the trouble of moving over all the constraints, etc.
> would be half the work.
> 
> Speaking of which, the current implementation loses all constraints,
> triggers, rules, comments, etc. (not defaults and notnulls
> though). Therefore
> 1) did I forget anything in the above list
> 2) how do I find out if the dropped column is referenced in a constraint,
> trigger, rule (this is necessary for a correct RESTRICT/CASCADE
> implementation)
> 3) once again, is it possible/safe to do the equivalent of update pg_class
> set oid=old where oid=new to save this work?
> 
> Oh, btw., heaven help you if you try this on tables that are inherited
> from.
> 
> -- 
> Peter Eisentraut                  Sernanders vg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
> 
> 
> 
> 
> ************
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T03:27:11Z

> Did I miss a discussion here on implementing this, and how?  Sounds to me
> like a week and a bit before planned beta, an *incomplete* feature has
> been shoved into the source tree with zero forewarning or discussion ...
> 
> Okay, my turn here ... I vote for this to be *reverted*!!

I disagree.  First, it is on the TODO list, so it is open game.  Second
it is not throughout all the code, it only gets activated if someone
executes the command.  Third, I don't know of any time limit that
features have to be implemented a certain number of weeks _before_ beta
starts.

Everyone asks for this, and if it does only %70 of the job, that is fine
as long as the manual page says so.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T03:48:15Z

[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> With caveats, it is now possible to drop columns from tables.
> 
> The implementation is based on copying the old table to a new one minus
> the specified column. This procedure changes the oids of everyone
> involved, so I was wondering if
> a) this is a good reason to tell people to stop using oids as keys, or
> b) if there is some way to keep the oids on both the records and the
> pg_class entry.

Actually CLUSTER has the same problem, I think.  It may be even worse
because it drops all indexes.  Can you take a look at that code too. 
Some people have reported problems with it, while others are OK.  There
is a cluster TODO mail file in the source tree.  It shows an actual bug
that still exists, plus some other issues with cluster.



Not sure how to deal with this.  I think our whole OID system is messed
up because you can't update the OID column in a table.  That is very
limiting, and some commands like cluster lose oids.  I think we need a
full OID discussion on how to move forward with them.

Why not allow heap_insert to receive the oid as a parameter.  It may
help with cluster too.  Seems like a great idea!

> 
> Is it possible/safe to change to oid of the new pg_class entry back to the
> old one? In that case the trouble of moving over all the constraints, etc.
> would be half the work.

I don't know.  I don't see any reason we can't handle that.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-23T03:49:42Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Okay, my turn here ... I vote for this to be *reverted*!!

> I disagree.  First, it is on the TODO list, so it is open game.  Second
> it is not throughout all the code, it only gets activated if someone
> executes the command.  Third, I don't know of any time limit that
> features have to be implemented a certain number of weeks _before_ beta
> starts.

I agree with Bruce.  There's no risk of this breaking anything else,
AFAICT, so if it doesn't work there's no harm done.  I hope Peter is
going to clean it up more before beta, but why shouldn't he push out
what he has for review and criticism?

I'm busy committing changes in areas that are considerably more critical
than this is, so if the rule is going to be "no risk taken for N weeks
*before* beta", better tell me about it...

			regards, tom lane

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T04:23:16Z

On Sat, 22 Jan 2000, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Okay, my turn here ... I vote for this to be *reverted*!!
> 
> > I disagree.  First, it is on the TODO list, so it is open game.  Second
> > it is not throughout all the code, it only gets activated if someone
> > executes the command.  Third, I don't know of any time limit that
> > features have to be implemented a certain number of weeks _before_ beta
> > starts.
> 
> I agree with Bruce.  There's no risk of this breaking anything else,
> AFAICT, so if it doesn't work there's no harm done.  I hope Peter is
> going to clean it up more before beta, but why shouldn't he push out
> what he has for review and criticism?

Why didn't I just take Alfred's stuff and shove it into the source tree
and let the bricks fall where they may?  As far as I am/was concerned, the
ramifications of the changes were so great that *some* discussion should
have happened before hand ...

I'm one of those that would love to have an ALTER TABLE ... DROP COLUMN
implementation, but how many applications will get broken because OID are
no longer retained?  If it had been brought up and discussed before being
thrown in, like everyone else has done in the past, maybe a better
solution could have presented itself ...

> I'm busy committing changes in areas that are considerably more critical
> than this is, so if the rule is going to be "no risk taken for N weeks
> *before* beta", better tell me about it...

Its not the *risk* that bothers me, its the lack of discussions ... IMHO,
he should have brought up the "I'm in the process of toasting the reliance
on OIDs" topic *before* implementing it ... give other ppl a chance to
possibly present alternatives ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T04:26:32Z

> [Charset ISO-8859-1 unsupported, filtering to ASCII...]
> > With caveats, it is now possible to drop columns from tables.
> > 
> > The implementation is based on copying the old table to a new one minus
> > the specified column. This procedure changes the oids of everyone
> > involved, so I was wondering if
> > a) this is a good reason to tell people to stop using oids as keys, or
> > b) if there is some way to keep the oids on both the records and the
> > pg_class entry.
> 
> Actually CLUSTER has the same problem, I think.  It may be even worse
> because it drops all indexes.  Can you take a look at that code too. 
> Some people have reported problems with it, while others are OK.  There
> is a cluster TODO mail file in the source tree.  It shows an actual bug
> that still exists, plus some other issues with cluster.
> 

I wonder if we should throw out a NOTICE when we drop some
characteristic of a table?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-23T04:38:11Z

Peter Eisentraut <peter_e@gmx.net> writes:
> With caveats, it is now possible to drop columns from tables.
> The implementation is based on copying the old table to a new one minus
> the specified column. This procedure changes the oids of everyone
> involved, so I was wondering if
> a) this is a good reason to tell people to stop using oids as keys,

AFAIK there is nothing particularly magic about OIDs.  You could
perfectly well create the new table with the same OIDs as are in the
old table (see COPY WITH OIDS if you are wondering how).

User-level stuff like referential integrity triggers might get unhappy,
but you're not going to let triggers see what you're doing, right ;-) ?

> Is it possible/safe to change to oid of the new pg_class entry back to the
> old one? In that case the trouble of moving over all the constraints, etc.
> would be half the work.

Wrong way to think about it.  You should be doing a heap_update of the
catalog tuples that need to change, ISTM.

You could almost get away with doing it like you describe, except that
there is a unique index on pg_class OIDs these days (right, Bruce?)
and that index will kick out an error.  But heap_update on the original
table tuple will work.

I think what we may want here is something comparable to what's been
discussed recently for VACUUM: build the new table as a new heap file
and then rename the physical file into place, without really doing
anything to the pg_class tuple --- except of course you'd need to
heap_update it to adjust the number-of-attributes field.

> 2) how do I find out if the dropped column is referenced in a constraint,
> trigger, rule (this is necessary for a correct RESTRICT/CASCADE
> implementation)

Actually it's worse than that: you need to be prepared to renumber the
columns after the dropped one, too.  Probably what you will need to do
is read in and deparse all the relevant rules and triggers, then reparse
them against the updated table schema.  Ugly.  And no, I have no idea
how you even *find* all the relevant rules.  (Jan?)

> Oh, btw., heaven help you if you try this on tables that are inherited
> from.

The whole thing should be done inside a recursive routine that applies
the same change to all children of the target table.  See ALTER TABLE
ADD COLUMN for an example.  (ADD COLUMN is pretty broken too, since it
doesn't preserve consistency of column numbering across child tables ---
want to reimplement it in this same style?)

			regards, tom lane

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T04:39:14Z

At 10:27 PM 1/22/00 -0500, Bruce Momjian wrote:
>> Did I miss a discussion here on implementing this, and how?  Sounds to me
>> like a week and a bit before planned beta, an *incomplete* feature has
>> been shoved into the source tree with zero forewarning or discussion ...
>> 
>> Okay, my turn here ... I vote for this to be *reverted*!!
>
>I disagree.  First, it is on the TODO list, so it is open game.  Second
>it is not throughout all the code, it only gets activated if someone
>executes the command.  Third, I don't know of any time limit that
>features have to be implemented a certain number of weeks _before_ beta
>starts.
>
>Everyone asks for this, and if it does only %70 of the job, that is fine
>as long as the manual page says so.

I'm sorry, but his current hack results in silent failures.

Personally, I think he should've asked for answers to his questions
first - i.e., how do you find out which relations are subject to
foreign key restraints (implemented via triggers)?

Allowing one to drop columns with silent failure is hardly the 
hallmark of a professional piece of software.  What has impressed me
about the Postgres effort in the year that I've been tracking it (though
sadly not contributing to it), has been the concentration on professional
implementation of features, and the concentration on improving stability
and reliability.

"copy to a table, drop the column, copy back" - heck, I could do that
with a script file to be fed into psql.  An internal implementation has
to be far better to be considered a piece of a professional RDBMS, I'm
afraid.

I'm not saying that this isn't the proper basis for the feature, only
that sure, that's easy to do internally or externally, and that isn't
the problem.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-23T05:27:32Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I wonder if we should throw out a NOTICE when we drop some
> characteristic of a table?

The problem is mostly that the code doesn't even *know* that it's
dropping data.  If we add code to find the info that's getting lost,
it's probably little more work to add code to copy it.

I'm of two minds about this.  Peter is an energetic new contributor
and we'd be really foolish to discourage him (I was there not very
long ago myself).  And a limited DROP COLUMN capability is better
than none at all, so long as its limitations are well-documented.

OTOH, I understand Don Baccus' concern: Postgres is on the cusp of
being considered professional-grade software --- we are competing
against multi-K-dollar commercial offerings --- and we jeopardize
that perception if we add features that are less than fully baked.
This is definitely in the 50%-baked category...

			regards, tom lane

Re: [HACKERS] Happy column dropping

Vince Vielhaber <vev@michvhf.com> — 2000-01-23T05:42:45Z

On 23-Jan-00 Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I wonder if we should throw out a NOTICE when we drop some
>> characteristic of a table?
> 
> The problem is mostly that the code doesn't even *know* that it's
> dropping data.  If we add code to find the info that's getting lost,
> it's probably little more work to add code to copy it.
> 
> I'm of two minds about this.  Peter is an energetic new contributor
> and we'd be really foolish to discourage him (I was there not very
> long ago myself).  And a limited DROP COLUMN capability is better
> than none at all, so long as its limitations are well-documented.
> 
> OTOH, I understand Don Baccus' concern: Postgres is on the cusp of
> being considered professional-grade software --- we are competing
> against multi-K-dollar commercial offerings --- and we jeopardize
> that perception if we add features that are less than fully baked.
> This is definitely in the 50%-baked category...

So why not    ./configure --enable-experimental

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
   128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T05:58:30Z

On Sun, 23 Jan 2000, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I wonder if we should throw out a NOTICE when we drop some
> > characteristic of a table?
> 
> The problem is mostly that the code doesn't even *know* that it's
> dropping data.  If we add code to find the info that's getting lost,
> it's probably little more work to add code to copy it.
> 
> I'm of two minds about this.  Peter is an energetic new contributor
> and we'd be really foolish to discourage him (I was there not very
> long ago myself).  And a limited DROP COLUMN capability is better
> than none at all, so long as its limitations are well-documented.

IMHO, put out a BIG NOTICE if someone issues the DROP COLUMN command:

Do not expect your table to look like what you used to have!!

This has nothing to do with discouraging a contributor ... this has to do
with maintaining QA through peer-review ... it would have taken Peter *as
long* to send his note out 24hrs *before* commiting the changes and would
have at least spur'd on a possible discussion of a better way of dealign
with the whole OID situation ...

Look at the last major patch we threw in from Alfred ... he posted and
asked for comments ... Tom, I believe it was you that send back a few
concerns ... he addressed them and posted for review a *second* time
before we committed it.  After committing, we found a bug ... someone else
wanted to revert that patch, but *at that point* it would have been
inappropriate to do, since it had been reviewed twice and considered good
for inclusion ... if Alfred couldn't have fixed the problem adequately
after a few days, okay, then revert it, but at least give him a chance to
fix that which he wrought ...

In Peter's case, there was no review ... just slap it in and pray ;(

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-23T12:38:49Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Okay, my turn here ... I vote for this to be *reverted*!!
> 
> > I disagree.  First, it is on the TODO list, so it is open game.  Second
> > it is not throughout all the code, it only gets activated if someone
> > executes the command.  Third, I don't know of any time limit that
> > features have to be implemented a certain number of weeks _before_ beta
> > starts.
> 
> I agree with Bruce.  There's no risk of this breaking anything else,
> AFAICT, so if it doesn't work there's no harm done.  I hope Peter is
> going to clean it up more before beta, but why shouldn't he push out
> what he has for review and criticism?
>

I agree with Marc. 
DROP COLUMN feature should be discussed before implementing it.

We can live without DROP COLUMN feature.
All we have to do is to ignore the column.
Why should we have a complicated implementation for the feature
without any discussion ?

Anyway I have 2 basic questions.

1) Is the * 2x disk usage *  implementation really needed ?
2) Why rename() ?
    I don't trust rename() at all in transaction control.
    The first thing we should do it to change relname -> relation file
    name mapping in order to avoid calling rename(). 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T15:26:58Z

At 12:27 AM 1/23/00 -0500, Tom Lane wrote:

>I'm of two minds about this.  Peter is an energetic new contributor
>and we'd be really foolish to discourage him (I was there not very
>long ago myself).  And a limited DROP COLUMN capability is better
>than none at all, so long as its limitations are well-documented.
>
>OTOH, I understand Don Baccus' concern: Postgres is on the cusp of
>being considered professional-grade software --- we are competing
>against multi-K-dollar commercial offerings --- and we jeopardize
>that perception if we add features that are less than fully baked.
>This is definitely in the 50%-baked category...

I certainly don't want to discourage Peter, either, and perhaps
was a bit too harsh.  But release of a feature this half-baked
would fit the stereotype many people have held towards free,
open source software, and postgres in particular.

IMHO, of course.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Hannu Krosing <hannu@tm.ee> — 2000-01-23T18:53:18Z

Don Baccus wrote:
> 
> At 12:27 AM 1/23/00 -0500, Tom Lane wrote:
> 
> >I'm of two minds about this.  Peter is an energetic new contributor
> >and we'd be really foolish to discourage him (I was there not very
> >long ago myself).  And a limited DROP COLUMN capability is better
> >than none at all, so long as its limitations are well-documented.
> >
> >OTOH, I understand Don Baccus' concern: Postgres is on the cusp of
> >being considered professional-grade software --- we are competing
> >against multi-K-dollar commercial offerings --- and we jeopardize
> >that perception if we add features that are less than fully baked.
> >This is definitely in the 50%-baked category...
> 
> I certainly don't want to discourage Peter, either, and perhaps
> was a bit too harsh.  But release of a feature this half-baked

Putting something in a development tree can hardly be called a "release"
I'm sure many people would appreciate it even without "preserving oid-s" 
as OID's are declared deprecated for (AFAIK) >2 years, and they don't give 
any real advantage over primary key with default nextval, as there are 
currently no means for reasonably getting from oid to record.

I agree that it could be #ifedef'ed or usable only when you do:
set TerribleDropColumnCludge to 'ON';

> would fit the stereotype many people have held towards free,
> open source software, and postgres in particular.

What keeps us from discussing the implementation _now_ that we have something 
to discuss. Much of the success of open source software comes from the 
"show me the code" mentality - you discuss what you have, not what you might
do.

The current "(UN)Happy column dropping" discussion, frankly seems to stem much 
from jealousy - hands off my tree, we allow only _purfect_ contributions.

OTOH there are several existing features in postgresql you would not 
expect, unless you have worked with postgresql for many years and read 
most of traffic on hackers list (like running out of almost all resources
doing a seemingly innocent query (or having it done for you by a "smart" 
application), or lack of most common-sense "convenience" optimisations,
like using index for max(), or being able to _partially_ rollback DDL 
statements.

Nobody has demanded removing ORs (or even the optimiser ;)) from postgres 
because they can explode the backend.

So IMHO discouraging small usability improvements is wrong.

> IMHO, of course.

Sure

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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T19:01:41Z

At 08:53 PM 1/23/00 +0200, Hannu Krosing wrote:

>What keeps us from discussing the implementation _now_ that we have
something 
>to discuss.

Nothing.  The argument is simply that perhaps discussion should come FIRST.

> Much of the success of open source software comes from the 
>"show me the code" mentality - you discuss what you have, not what you might
>do.

I see a lot of pre-implementation discussion on this group.  For instance,
recently there was public discussion of "TOAST" large data types.  Details
were ironed out, now Jan will go implement it when he has time.

Likewise his approach to implementing referential integrity was discussed
here beforehand.

Just today, we're seeing discussion of the implementation of a new
stddev aggregate function.

I think this is a good process to follow.

>The current "(UN)Happy column dropping" discussion, frankly seems to stem
much 
>from jealousy - hands off my tree, we allow only _purfect_ contributions.

Are you suggesting that the goal should be anything less than perfection?

I guess this goes to my argument that Postgres is starting to be regarded
as a potential competitor to expensive commercial DBs in certain application
environments.  If the bar is lowered for contributions, Postgres will
quickly re-earn the image of flakiness that the current developers have
worked so hard to shed.

>OTOH there are several existing features in postgresql you would not 
>expect, unless you have worked with postgresql for many years and read 
>most of traffic on hackers list (like running out of almost all resources
>doing a seemingly innocent query (or having it done for you by a "smart" 
>application), or lack of most common-sense "convenience" optimisations,
>like using index for max(), or being able to _partially_ rollback DDL 
>statements.
>
>Nobody has demanded removing ORs (or even the optimiser ;)) from postgres 
>because they can explode the backend.

No ... but fixing ORs seems to be on the list of things to be done.

Pointing to the fact that the inherited code still needs a lot of work
before it's really a solid, commercial-quality database engine in all
regards doesn't convince me that weak implementations of new features
should be added.

My impression is that the current crop of developers are aiming higher...

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-23T19:14:30Z

Hannu Krosing <hannu@tm.ee> writes:
> The current "(UN)Happy column dropping" discussion, frankly seems to
> stem much from jealousy - hands off my tree, we allow only _purfect_
> contributions.

I think this discussion could do without personal attacks, don't you?

> OTOH there are several existing features in postgresql you would not 
> expect, unless you have worked with postgresql for many years and read 
> most of traffic on hackers list (like running out of almost all resources
> doing a seemingly innocent query (or having it done for you by a "smart" 
> application), or lack of most common-sense "convenience" optimisations,
> like using index for max(), or being able to _partially_ rollback DDL 
> statements.

I see it considerably differently.  Quite a few of us have been sweating
blood for a long time to clean up those past half-baked implementation
decisions.  We're all well aware that the list of those problems is
still very long.  Should we be happy about the addition of another such
problem?

I've been generally in favor of letting Peter continue with this
process, but that's only because I expect him to be here for the long
haul and to work on refining his first cut into a robust feature.
If he stops here and leaves it for other folks to clean up, then I'd
vote to remove it as well.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T19:43:23Z

> I certainly don't want to discourage Peter, either, and perhaps
> was a bit too harsh.  But release of a feature this half-baked
> would fit the stereotype many people have held towards free,
> open source software, and postgres in particular.

I again am totally confused by the reaction to this.

Peter has done a lot for 7.0.  Only Tom Lane and Jan have done more work
on 7.0 than Peter.  He has also fixed many TODO items from the list.

We is not releasing tomorrow.  He is putting in what he has and asking
for advise.  Yes, it would have been better if he would have discussed
this first.  However, he is writing _huge_ amounts of PostgreSQL code. 
Look at the new psql or initdb, or the other items he has done.  Maybe
he is too busy coding to bring up issues on the list.

As long as he addresses concerns we have, I can live with some
non-perfect code.  He is relatively new to this.  Honestly, only Tom
Lane and Jan are as energetic about 7.0 as Peter, so why rock the boat.

That doesn't mean I accept bad code, only that I am patient with
people's styles.

I can't remember the last time we all were so harsh, and I am concerned.

Let me see if I can say something everyone will agree on:

---------------------------------------------------------------------------

First, Peter, I hope you have not gotten too upset while reading the
previous messages.

Second, I know you asked for suggestions while working on psql, and
really didn't get much feedback.  You did a great job without much
direction.  Let me suggest you not give up on us helping you in other
projects.  If you get stuck or need a suggestion, just send a note to
hackers, and we can give you ideas.  Often this will make your job
easier.

---------------------------------------------------------------------------

Actually, when I get stuck, I post to the list and put the project down
for a few hours.  An answer to my question usually shows up very soon. 
In the old days, I used to ask over and over again as I learned more
about the problem until someone figured it out.  :-)

I am thinking of calling 7.0 the Lane/Wieck/Eisentraut release.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T20:02:30Z

At 02:43 PM 1/23/00 -0500, Bruce Momjian wrote:
>> I certainly don't want to discourage Peter, either, and perhaps
>> was a bit too harsh.  But release of a feature this half-baked
>> would fit the stereotype many people have held towards free,
>> open source software, and postgres in particular.

>I again am totally confused by the reaction to this.

>Peter has done a lot for 7.0.  Only Tom Lane and Jan have done more work
>on 7.0 than Peter.  He has also fixed many TODO items from the list.

Yes, and that's great.  I'm not commenting about those things, though.
Though I have to say, having just finally built the pre-7.0 sources,
I like the new psql formatting.

That doesn't mean I have to like an "alter ... drop column" that doesn't
really implement "alter ... drop column", though, does it? :)

>We is not releasing tomorrow.  He is putting in what he has and asking
>for advise.

Perhaps I'm suffering from a misperception, then...my understanding
was that his having committed the changes meant they would be showing
up in the upcoming V7.0 Beta release.

> Yes, it would have been better if he would have discussed
>this first.  However, he is writing _huge_ amounts of PostgreSQL code. 
>Look at the new psql or initdb, or the other items he has done.  Maybe
>he is too busy coding to bring up issues on the list.

>As long as he addresses concerns we have, I can live with some
>non-perfect code.  He is relatively new to this.  Honestly, only Tom
>Lane and Jan are as energetic about 7.0 as Peter, so why rock the boat.

This sounds a bit like the old management technique of measuring
productivity by counting lines of code!  I should hope quality counts
for even more than quantity.  Shouldn't it?

(and, no, I'm not implying that Peter's contributions are of poor
quality, I'm just reacting to your point)

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T20:08:46Z

On Sun, 23 Jan 2000, Hannu Krosing wrote:

> The current "(UN)Happy column dropping" discussion, frankly seems to
> stem much from jealousy - hands off my tree, we allow only _purfect_
> contributions.

I don't expect any contribution to be perfect ... I do expect those that
are committing code directly to the source tree to take a few minutes and
think before they do so.  Peter *knew* there were implementation flaws to
what he added, yet he implemented it anyway, without asking anyone else
for comments and/or suggestions on how those flaws could be avoided ... if
Peter didn't have commit access, he would have had to submit those patches
for review before having them applied, just like Alfred recently went
through with his libpq changes ...

> OTOH there are several existing features in postgresql you would not
> expect, unless you have worked with postgresql for many years and read
> most of traffic on hackers list (like running out of almost all
> resources doing a seemingly innocent query (or having it done for you
> by a "smart"  application), or lack of most common-sense "convenience"
> optimisations, like using index for max(), or being able to
> _partially_ rollback DDL statements.

Ah, but, in these cases, they lack of don't break existing applications
*and* the running out of all resources definitely isn't "hidden" in the
background, you find out about it quick ...

The beef I have with how Peter went about implementing this was that the
biggest flaw that he lists is *hidden* in the background ... 

> Nobody has demanded removing ORs (or even the optimiser ;)) from
> postgres because they can explode the backend.

It is because they explode the backend that we don't ... what Peter
implemented silently tromps on the OIDs ...

> So IMHO discouraging small usability improvements is wrong.

Ppl are missing the whole point here ... it isn't the improvement that I
have a beef against, it is the fact that, unlike every other feature
addition (bug fixes are different), there was absolutely no discussion
before implementation ... hell, if the implementation didn't come with a
"caveat" afterwards, it wouldn't have been so bad, but Peter commit'd a
"small usability improvement" followed up by what *I* consider to be one
helluva caveat, without any discussion on how to get around that before
committing ... that, IMHO, is wrong ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T20:11:04Z

On Sun, 23 Jan 2000, Don Baccus wrote:

> I guess this goes to my argument that Postgres is starting to be
> regarded as a potential competitor to expensive commercial DBs in
> certain application environments.  If the bar is lowered for
> contributions, Postgres will quickly re-earn the image of flakiness
> that the current developers have worked so hard to shed.

I'm not so much beef'd about 'lowering any bar', I'm beef'd that the bar
apparently is being set at different hits for contributors vs committers
... 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T20:18:14Z

> At 02:43 PM 1/23/00 -0500, Bruce Momjian wrote:
> >> I certainly don't want to discourage Peter, either, and perhaps
> >> was a bit too harsh.  But release of a feature this half-baked
> >> would fit the stereotype many people have held towards free,
> >> open source software, and postgres in particular.
> 
> >I again am totally confused by the reaction to this.
> 
> >Peter has done a lot for 7.0.  Only Tom Lane and Jan have done more work
> >on 7.0 than Peter.  He has also fixed many TODO items from the list.
> 
> Yes, and that's great.  I'm not commenting about those things, though.
> Though I have to say, having just finally built the pre-7.0 sources,
> I like the new psql formatting.
> 
> That doesn't mean I have to like an "alter ... drop column" that doesn't
> really implement "alter ... drop column", though, does it? :)

But is this really a bad thing.  I think it is acceptible as is.  We
currently tell people in the FAQ that do drop a column, do a SELECT INTO
... ALTER RENAME.  That loses more than Peter's version.

> 
> >We is not releasing tomorrow.  He is putting in what he has and asking
> >for advise.
> 
> Perhaps I'm suffering from a misperception, then...my understanding
> was that his having committed the changes meant they would be showing
> up in the upcoming V7.0 Beta release.

Well, my assumption is that either Peter will add the missing
functionality, or we will vote on whether to enable/disable it in 7.0. 
One line in gram.y and it is disabled.

I can tell you that I certainly will vote for it as enabled, even with
the limitations, because CLUSTER has a bigger problem.  Maybe we just
add a NOTICE to CLUSTER and DROP COLUMN and be done with it.

I can also say I would never have thought about the items Peter asked
about.  I would have just implemented it as he did and maybe never even
considered the limitations.

> 
> > Yes, it would have been better if he would have discussed
> >this first.  However, he is writing _huge_ amounts of PostgreSQL code. 
> >Look at the new psql or initdb, or the other items he has done.  Maybe
> >he is too busy coding to bring up issues on the list.
> 
> >As long as he addresses concerns we have, I can live with some
> >non-perfect code.  He is relatively new to this.  Honestly, only Tom
> >Lane and Jan are as energetic about 7.0 as Peter, so why rock the boat.
> 
> This sounds a bit like the old management technique of measuring
> productivity by counting lines of code!  I should hope quality counts
> for even more than quantity.  Shouldn't it?
> 
> (and, no, I'm not implying that Peter's contributions are of poor
> quality, I'm just reacting to your point)

When I say huge amount of code, I mean huge rewrites of terriblily
unstuctured code into nice neat code.  Look at the new psql vs. the old
code.

We can't expect people to just walk up and produce portable,
style-conforming, totally functional code from day 1 or even year 1.
We work with people and point them in the right direction.

You know why ANALYZE is part of VACUUM?  Because at the time I didn't
know how to scan a table.  Vacuum already did that, so I piggybacked on
that code.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T20:26:38Z

> On Sun, 23 Jan 2000, Hannu Krosing wrote:
> 
> > The current "(UN)Happy column dropping" discussion, frankly seems to
> > stem much from jealousy - hands off my tree, we allow only _purfect_
> > contributions.
> 
> I don't expect any contribution to be perfect ... I do expect those that
> are committing code directly to the source tree to take a few minutes and
> think before they do so.  Peter *knew* there were implementation flaws to
> what he added, yet he implemented it anyway, without asking anyone else
> for comments and/or suggestions on how those flaws could be avoided ... if
> Peter didn't have commit access, he would have had to submit those patches
> for review before having them applied, just like Alfred recently went
> through with his libpq changes ...

But he is not done.  What does it matter if he does 1/2 now and 1/2 in a
week, as long as it is done before beta?  As long as the tree still
compiles, does it matter?

Let him fix 50 TODO items 1/2 way.  If he finishes them by beta, great.
If not, we can vote on a ripout/disable before beta begins.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T20:41:03Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> You know why ANALYZE is part of VACUUM?  Because at the time I didn't
> know how to scan a table.  Vacuum already did that, so I piggybacked on
> that code.

Big difference ... what you did didn't run the risk of breaking existing
applications ... *maybe* there is no choice in this, *maybe* the way that
Peter implemented is  the only way it *could* be implemented ... all I'm
arguing is that there should have been a discussion *before* it was
implemented to hammer that point out ...

... just like we've done for the past 4 years now ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T20:43:22Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> > On Sun, 23 Jan 2000, Hannu Krosing wrote:
> > 
> > > The current "(UN)Happy column dropping" discussion, frankly seems to
> > > stem much from jealousy - hands off my tree, we allow only _purfect_
> > > contributions.
> > 
> > I don't expect any contribution to be perfect ... I do expect those that
> > are committing code directly to the source tree to take a few minutes and
> > think before they do so.  Peter *knew* there were implementation flaws to
> > what he added, yet he implemented it anyway, without asking anyone else
> > for comments and/or suggestions on how those flaws could be avoided ... if
> > Peter didn't have commit access, he would have had to submit those patches
> > for review before having them applied, just like Alfred recently went
> > through with his libpq changes ...
> 
> But he is not done.  What does it matter if he does 1/2 now and 1/2 in a
> week, as long as it is done before beta?  As long as the tree still
> compiles, does it matter?
> 
> Let him fix 50 TODO items 1/2 way.  If he finishes them by beta, great.
> If not, we can vote on a ripout/disable before beta begins.

And if 5 of those 50 that he does finish rely on 45 that he doesn't?  I'm
just saying that 24hr of discussion before implementation might have come
up with a cleaner solution to the problem ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T20:45:18Z

At 03:18 PM 1/23/00 -0500, Bruce Momjian wrote:

>> That doesn't mean I have to like an "alter ... drop column" that doesn't
>> really implement "alter ... drop column", though, does it? :)
>
>But is this really a bad thing.  I think it is acceptible as is.  We
>currently tell people in the FAQ that do drop a column, do a SELECT INTO
>... ALTER RENAME.  That loses more than Peter's version.

Again I'm missing not having Date here, I just called and it will
be mailed to me next week.

But I've been under the assumption that "alter table drop column" is
part of SQL 92.  

If I'm wrong, then I suppose PostgreSQL can do whatever it wants.

If it is part of SQL 92, though, shouldn't there at least be discussion
of what's needed to actually implement the real, live standard semantics?

Isn't the user who picks up PostgreSQL from, say, a Red Hat distribution
going to be a bit surprised that "drop column" drops integrity constraints
for the whole table?

Assuming, of course, the feature as is were to go into release.

>I can also say I would never have thought about the items Peter asked
>about.  I would have just implemented it as he did and maybe never even
>considered the limitations.

Hmmm...if it's part of SQL 92 I certainly would've looked at the 
defined semantics first.  At least, that's what people pay me to do
when I hack compilers...

>We can't expect people to just walk up and produce portable,
>style-conforming, totally functional code from day 1 or even year 1.
>We work with people and point them in the right direction.

And if I get organized to the point of being able to make contributions
I would hope for tough, objective criticism of my efforts.

>You know why ANALYZE is part of VACUUM?  Because at the time I didn't
>know how to scan a table.  Vacuum already did that, so I piggybacked on
>that code.

This doesn't break standard semantics - again, if I'm wrong about
alter table ... drop column being part of SQL 92 then I'll back off
the suggestion that an implementation of standard semantics be
explored.

Maybe we should just drop this thread, I'm certainly not out to make
any enemies.  I've become fond of Postgres, and I guess my expectations
and standards are just very high.  Not that I'm always able to live
up to them! :)

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T20:46:47Z

> > But he is not done.  What does it matter if he does 1/2 now and 1/2 in a
> > week, as long as it is done before beta?  As long as the tree still
> > compiles, does it matter?
> > 
> > Let him fix 50 TODO items 1/2 way.  If he finishes them by beta, great.
> > If not, we can vote on a ripout/disable before beta begins.
> 
> And if 5 of those 50 that he does finish rely on 45 that he doesn't?  I'm
> just saying that 24hr of discussion before implementation might have come
> up with a cleaner solution to the problem ...

Sure, that is preferable.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T20:58:20Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> > > But he is not done.  What does it matter if he does 1/2 now and 1/2 in a
> > > week, as long as it is done before beta?  As long as the tree still
> > > compiles, does it matter?
> > > 
> > > Let him fix 50 TODO items 1/2 way.  If he finishes them by beta, great.
> > > If not, we can vote on a ripout/disable before beta begins.
> > 
> > And if 5 of those 50 that he does finish rely on 45 that he doesn't?  I'm
> > just saying that 24hr of discussion before implementation might have come
> > up with a cleaner solution to the problem ...
> 
> Sure, that is preferable.

*head pounding against a wall* that was my *whole* point :)

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T20:59:38Z

> Maybe we should just drop this thread, I'm certainly not out to make
> any enemies.  I've become fond of Postgres, and I guess my expectations
> and standards are just very high.  Not that I'm always able to live
> up to them! :)

It's like watching hot dogs or legislation being made.  You don't want
to watch.

We get everything working in the end.  It can be ugly getting there and
seeing all the warts along the way.

I think we are spoiled with Tom, Jan, and Vadim who just show up and
produce 100% functional patches the first time.  Some people go at it in
different ways.  Eventually it all gets working.  I can't tell you how
many times I have added a feature or fixed something, and then had Tom
Lane or Vadim come along and fix my fixes.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T21:06:28Z

> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> 
> > > > But he is not done.  What does it matter if he does 1/2 now and 1/2 in a
> > > > week, as long as it is done before beta?  As long as the tree still
> > > > compiles, does it matter?
> > > > 
> > > > Let him fix 50 TODO items 1/2 way.  If he finishes them by beta, great.
> > > > If not, we can vote on a ripout/disable before beta begins.
> > > 
> > > And if 5 of those 50 that he does finish rely on 45 that he doesn't?  I'm
> > > just saying that 24hr of discussion before implementation might have come
> > > up with a cleaner solution to the problem ...
> > 
> > Sure, that is preferable.
> 
> *head pounding against a wall* that was my *whole* point :)

Yes, but if wants to put what he has done so far, and then discuss it,
why give him grief about it?  Maybe that's how he likes to do things.

I tried to get Jan to assist Vince in the way CVS is used for the web
pages.  Vince was happy with his system and my interference in Vince's
system made for hurt feelings all around.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Vince Vielhaber <vev@michvhf.com> — 2000-01-23T21:19:20Z

On 23-Jan-00 Bruce Momjian wrote:
>> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> I tried to get Jan to assist Vince in the way CVS is used for the web
> pages.  Vince was happy with his system and my interference in Vince's
> system made for hurt feelings all around.

Water under the bridge.  Jan and I have down and had a few beers 
together.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
   128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T21:21:11Z

> 
> On 23-Jan-00 Bruce Momjian wrote:
> >> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> > I tried to get Jan to assist Vince in the way CVS is used for the web
> > pages.  Vince was happy with his system and my interference in Vince's
> > system made for hurt feelings all around.
> 
> Water under the bridge.  Jan and I have down and had a few beers 
> together.

Well, that's very good news.  

Now, I guess I can ask abou the new developers page.  It would be nice
to have that for the start of Beta.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T21:22:47Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> I think we are spoiled with Tom, Jan, and Vadim who just show up and
> produce 100% functional patches the first time.  Some people go at it in
> different ways.  Eventually it all gets working.  I can't tell you how
> many times I have added a feature or fixed something, and then had Tom
> Lane or Vadim come along and fix my fixes.

2 points:

	a) 100% functional patches *after* extensive discussion
	b) Peter's change wasn't a fix

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T21:27:00Z

> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> 
> > I think we are spoiled with Tom, Jan, and Vadim who just show up and
> > produce 100% functional patches the first time.  Some people go at it in
> > different ways.  Eventually it all gets working.  I can't tell you how
> > many times I have added a feature or fixed something, and then had Tom
> > Lane or Vadim come along and fix my fixes.
> 
> 2 points:
> 
> 	a) 100% functional patches *after* extensive discussion
> 	b) Peter's change wasn't a fix

Usually some one cleans me up. :-)  Even with discussion, my stuff is
not 100%.

I did code to extend the number of index columns >8, but broke the
system in several ways.  Tom Lane fixed it for me.  I think I even
committed it with a broken initdb, but figured out how to disable it
while Tom and I worked on it.

Yes, but even my feature additions have problems.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T21:42:56Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> > On Sun, 23 Jan 2000, Bruce Momjian wrote:
> > 
> > > I think we are spoiled with Tom, Jan, and Vadim who just show up and
> > > produce 100% functional patches the first time.  Some people go at it in
> > > different ways.  Eventually it all gets working.  I can't tell you how
> > > many times I have added a feature or fixed something, and then had Tom
> > > Lane or Vadim come along and fix my fixes.
> > 
> > 2 points:
> > 
> > 	a) 100% functional patches *after* extensive discussion
> > 	b) Peter's change wasn't a fix
> 
> Usually some one cleans me up. :-)  Even with discussion, my stuff is
> not 100%.
> 
> I did code to extend the number of index columns >8, but broke the
> system in several ways.  Tom Lane fixed it for me.  I think I even
> committed it with a broken initdb, but figured out how to disable it
> while Tom and I worked on it.
> 
> Yes, but even my feature additions have problems.  :-)

There is a key factor involved in all of this ... how many of these
"bugs" were things that ppl would think about before it is
implemented?  

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T21:48:10Z

> > Usually some one cleans me up. :-)  Even with discussion, my stuff is
> > not 100%.
> > 
> > I did code to extend the number of index columns >8, but broke the
> > system in several ways.  Tom Lane fixed it for me.  I think I even
> > committed it with a broken initdb, but figured out how to disable it
> > while Tom and I worked on it.
> > 
> > Yes, but even my feature additions have problems.  :-)
> 
> There is a key factor involved in all of this ... how many of these
> "bugs" were things that ppl would think about before it is
> implemented?  

No idea.  I just know I didn't think of them.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T22:03:55Z

At 12:04 AM 1/24/00 +0200, Hannu Krosing wrote:

>It could possibly be done by marking the column deleted and doing the 
>compression/renumbering during vacuum , or by locking the table and 
>compressing each page in-place if space is a concern or maybe several 
>other ways. 
>The existence of several ways to do it should not discourage people 
>from actually adding the drop column feature to postgres

It should provide even more encouragement to discuss the proposed
implementation *first*.

IMHO, again.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Hannu Krosing <hannu@tm.ee> — 2000-01-23T22:04:39Z

The Hermit Hacker wrote:
> 
> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> 
> > I think we are spoiled with Tom, Jan, and Vadim who just show up and
> > produce 100% functional patches the first time.  Some people go at it in
> > different ways.  Eventually it all gets working.  I can't tell you how
> > many times I have added a feature or fixed something, and then had Tom
> > Lane or Vadim come along and fix my fixes.
> 
> 2 points:
> 
>         a) 100% functional patches *after* extensive discussion

It assumes that you do all your actual coding and code reviewing yourself 
while hiding the code, i.e. the (alledged) *BSD way. 

The "release often, release early" school of OS tells people to show even 
premature code in hope of getting more/faster eyballing.

He could have done it by posting patches for discussion, but I can't see 
the real difference here.

What I think he is doing here is one-to-one move the FAQ recommendation 
for drop column to backend. And then move on from that to cover the areas 
of renumbering colums and keeping related constraints intact the FAQ 
glossed over.

It could possibly be done by marking the column deleted and doing the 
compression/renumbering during vacuum , or by locking the table and 
compressing each page in-place if space is a concern or maybe several 
other ways. 
The existence of several ways to do it should not discourage people 
from actually adding the drop column feature to postgres

>         b) Peter's change wasn't a fix

I was'nt a _bug_ fix, it was a usability fix and likely SQL 92 
compatibility fix.

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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-23T22:17:39Z

At 12:19 AM 1/24/00 +0200, Hannu Krosing wrote:
>Don Baccus wrote:

>> It should provide even more encouragement to discuss the proposed
>> implementation *first*.
>
>Judging from the length of this thread it is much more effective to get 
>a discussion *after* ;)

Not at all...thus far there's no discussion of HOW to implement the
correct semantics at all.  



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Hannu Krosing <hannu@tm.ee> — 2000-01-23T22:19:25Z

Don Baccus wrote:
> 
> At 12:04 AM 1/24/00 +0200, Hannu Krosing wrote:
> 
> >It could possibly be done by marking the column deleted and doing the
> >compression/renumbering during vacuum , or by locking the table and
> >compressing each page in-place if space is a concern or maybe several
> >other ways.
> >The existence of several ways to do it should not discourage people
> >from actually adding the drop column feature to postgres
> 
> It should provide even more encouragement to discuss the proposed
> implementation *first*.

Judging from the length of this thread it is much more effective to get 
a discussion *after* ;)

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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T22:20:04Z

Does anyone know why this thread is called, "Happy column dropping"?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Vince Vielhaber <vev@michvhf.com> — 2000-01-23T22:21:07Z

On Sun, 23 Jan 2000, Bruce Momjian wrote:

> > 
> > On 23-Jan-00 Bruce Momjian wrote:
> > >> On Sun, 23 Jan 2000, Bruce Momjian wrote:
> > > I tried to get Jan to assist Vince in the way CVS is used for the web
> > > pages.  Vince was happy with his system and my interference in Vince's
> > > system made for hurt feelings all around.
> > 
> > Water under the bridge.  Jan and I have down and had a few beers 
> > together.
> 
> Well, that's very good news.  
> 
> Now, I guess I can ask abou the new developers page.  It would be nice
> to have that for the start of Beta.

You volunteering?  :)   When I said earlier that it was on the back
burner, it is because I'm extremely busy right now.  If Jan wants to
do it or if you do, I have no problem with that - but I suspect Jan's
a little on the busy side right now as well.  With luck perhaps I can
have it online by late spring.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
   128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/mo or less at Pop4
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-23T22:27:26Z

On Sun, 23 Jan 2000, Don Baccus wrote:

> At 12:19 AM 1/24/00 +0200, Hannu Krosing wrote:
> >Don Baccus wrote:
> 
> >> It should provide even more encouragement to discuss the proposed
> >> implementation *first*.
> >
> >Judging from the length of this thread it is much more effective to get 
> >a discussion *after* ;)
> 
> Not at all...thus far there's no discussion of HOW to implement the
> correct semantics at all.  

Actually, I like the fact that Hannu suggested a way of implemented that
Bruce and I have been discussing in private :)

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-23T22:30:04Z

> > Now, I guess I can ask abou the new developers page.  It would be nice
> > to have that for the start of Beta.
> 
> You volunteering?  :)   When I said earlier that it was on the back
> burner, it is because I'm extremely busy right now.  If Jan wants to
> do it or if you do, I have no problem with that - but I suspect Jan's
> a little on the busy side right now as well.  With luck perhaps I can
> have it online by late spring.
> 

Jan's version looked done to me.  Can't we just put that up until we
have something better.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Hannu Krosing <hannu@tm.ee> — 2000-01-23T22:37:21Z

Don Baccus wrote:
> 
> At 12:19 AM 1/24/00 +0200, Hannu Krosing wrote:
> >Don Baccus wrote:
> 
> >> It should provide even more encouragement to discuss the proposed
> >> implementation *first*.
> >
> >Judging from the length of this thread it is much more effective to get
> >a discussion *after* ;)
> 
> Not at all...thus far there's no discussion of HOW to implement the
> correct semantics at all.

Such is the human nature. Instead of discussing how to implement it we 
discuss someone else not discussing it at proper time...

Seems to be much more important topic.

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

Re: [HACKERS] Happy column dropping

Lamar Owen <lamar.owen@wgcr.org> — 2000-01-24T03:23:24Z

Bruce Momjian wrote:
> 
> Does anyone know why this thread is called, "Happy column dropping"?

Because Peter thought everyone would be happy being able to drop
columns.

Of course, more discussion should have happened.  I have found out the
hard way about some of this in keeping up the RPM's.  However, I have
found most of the RPM issues settle out from end users -- not from the
development team.  Thus, I am in an 'in between' position -- which is an
ideal place for a packager, anyway.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: [HACKERS] Happy column dropping

Chris Bitmead <chris@bitmead.com> — 2000-01-24T03:50:07Z

Hannu Krosing wrote:

> Putting something in a development tree can hardly be called a "release"
> I'm sure many people would appreciate it even without "preserving oid-s"
> as OID's are declared deprecated for (AFAIK) >2 years, 

I've never seen or heard of any mention of oids being "deprecated". Some
people don't like them much, other people like them. Myself, I think
they will be great when a few issues with them are cleaned up (e.g.
backup with preservation of oids and full INSERT statements).

> and they don't give
> any real advantage over primary key with default nextval, as there are
> currently no means for reasonably getting from oid to record.

Not sure what you mean by "no means". They are queried and indexed like
other fields. No advantage? Well it takes one less pg call to get the
last value, and it takes less storage since they are there anyway. They
will also be very important if and when postgres makes more moves toward
being an ODBMS.

BTW, if someone implememnted INSERT where you may optionally specify the
oid, would this solve the problem, as I take it this patch is all about
implementing drop column in terms of a CREATE/SELECT INTO.

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-24T05:21:08Z

>>>> I think we are spoiled with Tom, Jan, and Vadim who just show up and
>>>> produce 100% functional patches the first time.

Who, me?  Go back and read the CVS logs for all my "oops..." messages.

>> a) 100% functional patches *after* extensive discussion
>
> Usually some one cleans me up. :-)  Even with discussion, my stuff is
> not 100%.

Assuming that Peter is still listening and hasn't given up in disgust,
I think the take-home point here is "discuss first, implement second".
Even the guys who have been working with Postgres the longest are
keenly aware that they don't know all there is to know about the
system.  *That's* why we have developed a tradition of sending proposals
to the list before doing anything major.  Very often there's someone
who knows a little more than you do about one aspect or another, and
will be able to save you from a mistake.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-24T05:27:23Z

> >>>> I think we are spoiled with Tom, Jan, and Vadim who just show up and
> >>>> produce 100% functional patches the first time.
> 
> Who, me?  Go back and read the CVS logs for all my "oops..." messages.

But you fix your own oops.  Sometimes I throw in the towel.

> 
> >> a) 100% functional patches *after* extensive discussion
> >
> > Usually some one cleans me up. :-)  Even with discussion, my stuff is
> > not 100%.
> 
> Assuming that Peter is still listening and hasn't given up in disgust,
> I think the take-home point here is "discuss first, implement second".
> Even the guys who have been working with Postgres the longest are
> keenly aware that they don't know all there is to know about the
> system.  *That's* why we have developed a tradition of sending proposals
> to the list before doing anything major.  Very often there's someone
> who knows a little more than you do about one aspect or another, and
> will be able to save you from a mistake.

Yes.  We certainly have driven that point home.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-24T12:15:27Z

On Sun, 23 Jan 2000, The Hermit Hacker wrote:

> Its not the *risk* that bothers me, its the lack of discussions ... IMHO,
> he should have brought up the "I'm in the process of toasting the reliance
> on OIDs" topic *before* implementing it ... give other ppl a chance to
> possibly present alternatives ...


From: Bruce Momjian <pgman@candle.pha.pa.us> 
To: Alain TESIO <tesio@easynet.fr> 
Subject: Re: [GENERAL] A script which drops a column 
Date: Thu, 25 Nov 1999 23:36:42 -0500 (EST) 

> Hello,
> 
> You may be interested by a script which drops a column as this
> feature isn't supported by Postgresql. I guess it could be easier
> and nice in Perl or something similar but I'm using what I know.
> 
> The parameters are in that order :
> 
> the name of the database
> the table
> the column to drop
> 
> Alain
> 
> #!/bin/sh
> 
> psql -d $1 -c "\d $2" | awk 'BEGIN { keep=1 } /+-/ { keep=1-keep } { if
> (keep) { print } }' | grep -v "\-\-" | grep -v "Table *=" | grep -v " $3
" |
> sed "s/| \([^ ]*\).*/\1/" | tr -s \\012 "," | sed "s/,$//" | sed
> "s/\(.*\)/select \1 into temp tmp_drop_column from $2 ; drop table $2 ;
> select * into $2 from tmp_drop_column;/" > tmp_sql_drop_column
> psql -d $1 -f tmp_sql_drop_column
> rm tmp_sql_drop_column

The fact is that internally this is exactly what we would have to do to
drop a column.  Now that we have temp tables, maybe someone could code
up some C to do this, or just an pg_exec_query_dest() call to do the
job.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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


I think what I did is significantly better than that, and of course it
will be cleaned up by next week.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-24T13:00:58Z

On Mon, 24 Jan 2000, Peter Eisentraut wrote:

> I think what I did is significantly better than that, and of course it
> will be cleaned up by next week.

>From a discussion with Bruce over the past 24hrs, as well as was mentioned
by Hannu on this list ... we effectively do this with VACUUM, so "copying"
that logic should be, I imagine, relatively easy, *and* it preserves OIDs
*and* it doesn't require 2x the space...

I imagine that DROP COLUMN isn't used that often, so the time it takes to
do this isn't an issue ... 

=================
> > 
> > One thing I've never been able to figure out ... why isn't implementing
> > DROP COLUMN a simple matter of "lock table;remove field from pg_*;rebuild
> > table", similar to the way that we do when we vacuum?
> 
> Because the column is still in the table, just invisible after removing
> from pg_attribute.  You need to remove the column from the heap, and
> that requires creating a new version of the table.  Vacuum moves tuples
> but does not make them shorter.

That I understand ... excuse my ignorance, but what would it take to
do that?  The way I envision a table 'on disk':

col1col2col3col4col5|col1col2col3col4col5|col1col2col3col4col5|

Basically, you have X tuples per page, where a page is 8192bytes,
correct?  If you were to remove, let's say, col2 out of the table, why
can't you do:

lock table
read page 1 into memory
rewrite page1 to disk as:
col1col3col4col5|col1col3col4col5|col1col3col4col5|
add one to page and goto 'read page n to memory'
unlock table

I'm making an assumption here ... first one being that each 'tuples' has
some sort of endoftuple marker in the table ...

If we're removing a column, the resultant 'page size' from the modified
page is going to be smaller then the original, so I would think it would
be a relatively simple thing, considering that its a read/re-write from
the same part of the 'on disk file' ...

... and it wouldn't require 2X the space used by the table ...
===================

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Happy column dropping

Hannu Krosing <hannu@tm.ee> — 2000-01-24T22:43:41Z

The Hermit Hacker wrote:
> 
> > Because the column is still in the table, just invisible after removing
> > from pg_attribute.  You need to remove the column from the heap, and
> > that requires creating a new version of the table.  Vacuum moves tuples
> > but does not make them shorter.
> 
> That I understand ... excuse my ignorance, but what would it take to
> do that?  The way I envision a table 'on disk':
> 
> col1col2col3col4col5|col1col2col3col4col5|col1col2col3col4col5|

I recently did a small python script to salvage deleted tuples and I can say 
that current docs on the layout of storage are very incomplete ant partially 
wrong.

> Basically, you have X tuples per page, where a page is 8192bytes,
> correct?  If you were to remove, let's say, col2 out of the table, why
> can't you do:
> 
> lock table
> read page 1 into memory
> rewrite page1 to disk as:
> col1col3col4col5|col1col3col4col5|col1col3col4col5|
> add one to page and goto 'read page n to memory'
> unlock table
> 
> I'm making an assumption here ... first one being that each 'tuples' has
> some sort of endoftuple marker in the table ...

you do have startoftuple/startoffreespace (as a offset inside the page)
but getting at the starts of col2 and col3 is not that easy - you must use 
all the accessor functions from pg_attribute and count null-bits (and 
shift null-bitmap) if present 

That's why I actually like the idea of just hiding the column (and setting 
it to DEFAULT NULL) - you don't automatically reclaim space, but you don't 
need much any extra space either. And it's very fast.

And as a practical person I like the current implementation too, mainly
because 
it's there and it does not break anything, at least when you don't use it ;)

As it is not a feature anyone would use in scripts very often (except Don
Baccus ;)

> If we're removing a column, the resultant 'page size' from the modified
> page is going to be smaller then the original, so I would think it would
> be a relatively simple thing, considering that its a read/re-write from
> the same part of the 'on disk file' ...
> 
> ... and it wouldn't require 2X the space used by the table ...

But a system crash while doing it would do really bad things, not to mention 
the fact that it bypasses storage manager making future changes to storage 
managers very hard.

VACUUMs bypassing of storage manager is understandable as it is a part of 
storage manager and not a general SQL thing - a garbage-collecting
all-in-memory 
signing-while-working storage manager will not need vacuum, analyse it may
need, 
perhaps.

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

RE: [HACKERS] Happy column dropping

Peter Eisentraut <peter_e@gmx.net> — 2000-01-24T23:48:06Z

On 2000-01-23, Hiroshi Inoue mentioned:

> Anyway I have 2 basic questions.
> 
> 1) Is the * 2x disk usage *  implementation really needed ?

Yes, unless you bypass all transaction logic and do a get a row, change
the row, delete the old row, write the new row, and silently hope that no
problems come up down the line. If you do an SQL update of all the rows in
a 10GB table you temporarily need 20GB in case there is a rollback.

> 2) Why rename() ?
>     I don't trust rename() at all in transaction control.
>     The first thing we should do it to change relname -> relation file
>     name mapping in order to avoid calling rename(). 

That's a good point. The alter table / rename code makes free use of this,
which is just waiting to kick somebody in the head. If you think this
could be addressed in the next release, I'm even for dropping my
business and wait for a cleaner solution.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Peter Eisentraut <peter_e@gmx.net> — 2000-01-24T23:48:22Z

On 2000-01-22, Tom Lane mentioned:

> AFAIK there is nothing particularly magic about OIDs.  You could
> perfectly well create the new table with the same OIDs as are in the
> old table (see COPY WITH OIDS if you are wondering how).

Okay, the oids of the user data are safe.

> > Is it possible/safe to change to oid of the new pg_class entry back to the
> > old one? In that case the trouble of moving over all the constraints, etc.
> > would be half the work.
> 
> Wrong way to think about it.  You should be doing a heap_update of the
> catalog tuples that need to change, ISTM.
> 
> You could almost get away with doing it like you describe, except that
> there is a unique index on pg_class OIDs these days (right, Bruce?)
> and that index will kick out an error.  But heap_update on the original
> table tuple will work.
> 
> I think what we may want here is something comparable to what's been
> discussed recently for VACUUM: build the new table as a new heap file
> and then rename the physical file into place, without really doing
> anything to the pg_class tuple --- except of course you'd need to
> heap_update it to adjust the number-of-attributes field.

I've tried to do that but it messed me up big time. If I drop the column
and do a select * from tablename it segfaults. The same happens on vacuum
analyze (but not plain vacuum). For the analyze part I traced it down to
the tuple that gets passed to nocachegetattr() still has t_data->t_natts
set at the old value put the data itself is no longer there, hence
segfault. Vacuum does some Page and MemoryContext things to get that
tuple, so I'm lost there, but I figure select has the same problem. Seems
like not all the buffers have been flushed, but there aren't any more I
could think of. Because when I reconnect, everything is fine. Help!

What more needs to be done?

newrel = heap_create(...);
{ copy data from oldrel to newrel }
{ update pg_class.relnatts }
{ update pg_attribute }
{ update pg_attrdef }
/* get rid of old one */
ReleaseRelationBuffers(oldrel);
smgrunlink(..., oldrelname);
RelationForgetRelation(oldrel_oid);
/* Rename the new one */
FlushRelationBuffers(tempname, (BlockNumber) 0, true);
smgrclose(..., tempname);
heap_close(newrel);
rename(tempname, oldrelname);

This is pretty much what it does. Not enough flushing? Too much flushing?

However, the more severe problem with this approach is this: If for some
reason whatsoever the rename() call fails you've already deleted the
original table on disk and now you're stuck with a heap file that you
can't rename plus a catalog entry to a table that doesn't exist on disk.
Panic. Any reordering of the above suggested? Of course, rename would
normally be able to atomically overwrite the old table, but since the
disk representation might be split over several files we lose. The table
renaming code makes some pretty dangerous assumptions in this regard, I
just noticed. Perhaps we should forbid this kind of messing with big
tables, at least until we come up with a better scheme. And that's leaving
alone the fact that calling rename in the first place is a pretty bad
violation of the storage manager encapsulation. Darn it.

> 
> > 2) how do I find out if the dropped column is referenced in a constraint,
> > trigger, rule (this is necessary for a correct RESTRICT/CASCADE
> > implementation)
> 
> Actually it's worse than that: you need to be prepared to renumber the
> columns after the dropped one, too.  Probably what you will need to do
> is read in and deparse all the relevant rules and triggers, then reparse
> them against the updated table schema.  Ugly.  And no, I have no idea
> how you even *find* all the relevant rules.  (Jan?)

I think this will be postponed. This is not even done on DROP TABLE, so it
will be consistent. ;)

> 
> > Oh, btw., heaven help you if you try this on tables that are inherited
> > from.
> 
> The whole thing should be done inside a recursive routine that applies
> the same change to all children of the target table.  See ALTER TABLE
> ADD COLUMN for an example.  (ADD COLUMN is pretty broken too, since it
> doesn't preserve consistency of column numbering across child tables ---
> want to reimplement it in this same style?)

Yes I saw that. I'm just going to forbid use of this on inheritance trees
until all of this gets fixed in one run.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Peter Eisentraut <peter_e@gmx.net> — 2000-01-24T23:48:47Z

On 2000-01-22, Tom Lane mentioned:

> > 2) how do I find out if the dropped column is referenced in a constraint,
> > trigger, rule (this is necessary for a correct RESTRICT/CASCADE
> > implementation)
> 
> Actually it's worse than that: you need to be prepared to renumber the
> columns after the dropped one, too.  Probably what you will need to do
> is read in and deparse all the relevant rules and triggers, then reparse
> them against the updated table schema.  Ugly.  And no, I have no idea
> how you even *find* all the relevant rules.  (Jan?)

Perhaps their should be a pg_attribute.attisusedbytrigger::oid,
pg_attribute.attisconstrainedbyconstr::oid, etc. Eventually, I think, this
is unavoidable if you want DROP TABLE to do the right thing as well, and
scanning and decoding possibly hundreds of rules, triggers, and
constraints won't get you far.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Peter Eisentraut <peter_e@gmx.net> — 2000-01-24T23:48:59Z

On 2000-01-23, Don Baccus mentioned:

> I certainly don't want to discourage Peter, either, and perhaps
> was a bit too harsh.  But release of a feature this half-baked
> would fit the stereotype many people have held towards free,
> open source software, and postgres in particular.

Who said something of a release? Whatever happened to release early,
release often?


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-24T23:53:37Z

At 12:48 AM 1/25/00 +0100, Peter Eisentraut wrote:
>On 2000-01-23, Don Baccus mentioned:
>
>> I certainly don't want to discourage Peter, either, and perhaps
>> was a bit too harsh.  But release of a feature this half-baked
>> would fit the stereotype many people have held towards free,
>> open source software, and postgres in particular.
>
>Who said something of a release? Whatever happened to release early,
>release often?

Is "release early, release often" why RH 6.1 doesn't seem to recognize
memory > 64 MB on a wide variety of x86 systems, the exact same boxes
on which RH 6.0 properly detected memory?  (kernel 2.2.*)

Sometimes I wonder...it leaves the impression that RH 6.1 left the house
with little QA testing.

I was unaware that other folks had pointed to the copy/rename approach
earlier as being a possible means of implementation.  I thought you'd
pulled that one out of your hat.  Still, more advance discussion would've
perhaps led to other approaches to investigate, just as discussion
now is doing.

I'm going to be blunt: implementation of "drop column" by doing a copy/rename
isn't something one expects of a competitive commercial-quality RDBMS.

Perhaps it's the best we can expect of Postgres, though.  If so, so be it.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T00:00:16Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Perhaps their should be a pg_attribute.attisusedbytrigger::oid,
> pg_attribute.attisconstrainedbyconstr::oid, etc. Eventually, I think, this
> is unavoidable if you want DROP TABLE to do the right thing as well, and
> scanning and decoding possibly hundreds of rules, triggers, and
> constraints won't get you far.

Wouldn't help much: when you do a DROP TRIGGER, do you turn off the
attisusedbytrigger, or not?  You'd still end up scanning everything,
just at a different time.

Maybe if it were a reference count, and not just a bit --- but I'd sure
hate to try to guarantee that we maintain the reference count
accurately.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T00:19:31Z

Peter Eisentraut <peter_e@gmx.net> writes:
>> I certainly don't want to discourage Peter, either, and perhaps
>> was a bit too harsh.  But release of a feature this half-baked
>> would fit the stereotype many people have held towards free,
>> open source software, and postgres in particular.

> Who said something of a release?

Um, you do recall that we are one week from feature-freeze for 7.0 beta,
don't you?  It's mighty late in the cycle to be committing code that you
are not expecting to release in more or less its current form.  The
reason the howls have been so loud is that because of the calendar,
everyone is assuming that you intend to release this code more or less
as it stands.

If that was *not* your intent, perhaps you had better pull the code out
until after we fork the tree for 7.1 development.

> Whatever happened to release early, release often?

The Postgres project has generally adopted a more conservative approach
to releases, because we know that people entrust critical data to
DBMSes.  We don't have anything that corresponds to a development
release series; *all* our releases are supposed to be "stable releases".
Of course we don't always get there, but that's the idea.

Jan has been muttering that we ought to have some means of dealing with
code development that spans multiple release cycles, ie, CVS branches
for work that is not expected to be part of the very next release.
I've found it hard enough to keep track of tip vs. last release branch,
but maybe something like that is needed.  It would let people push code
out for review without implying that they think it's good enough to go
into the next release.

			regards, tom lane

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T00:21:15Z

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter@sd.tpf.co.jp]On Behalf Of Peter
> Eisentraut
>
> On 2000-01-23, Hiroshi Inoue mentioned:
>
> > Anyway I have 2 basic questions.
> >
> > 1) Is the * 2x disk usage *  implementation really needed ?
>
> Yes, unless you bypass all transaction logic and do a get a row, change
> the row, delete the old row, write the new row, and silently hope that no
> problems come up down the line. If you do an SQL update of all the rows in
> a 10GB table you temporarily need 20GB in case there is a rollback.
>

I have already proposed another idea.
It only changes pg_attribute not touching relation files at all.
Probably it isn't the best solution but would be better than 2x disk usage
implementation.

> > 2) Why rename() ?
> >     I don't trust rename() at all in transaction control.
> >     The first thing we should do it to change relname -> relation file
> >     name mapping in order to avoid calling rename().
>
> That's a good point. The alter table / rename code makes free use of this,
> which is just waiting to kick somebody in the head. If you think this
> could be addressed in the next release, I'm even for dropping my
> business and wait for a cleaner solution.
>

I wonder that no one but me has complained about this.
CLUSTER/ALTER TABLE RENAME TO already have the same flaw.
And maybe restructuring VACUUM also.
As far as I see,fixed mapping relname to relation filename is the
problem. This doesn't allow the coexistence of old and new relation
files.  If old and new relation files could coexist we would be able to
update the relation_file_name attribute(we should add this kind of
atribute) of pg_class. Of cource,there must be the standard
mechanism to remove old files after commit.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T00:25:43Z

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> As far as I see,fixed mapping relname to relation filename is the
> problem. This doesn't allow the coexistence of old and new relation
> files.

Yes, and Vadim has proposed using relation OIDs as filenames for
reasons of his own, IIRC.

If we did that we could also solve the problems we have now with
rolling back table deletion/rename inside an aborted transaction.

I've been resisting this idea, but maybe it's time to bite the bullet
and accept that relation filenames can't be the same as the logical
names of the relations.

			regards, tom lane

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T01:58:43Z

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > As far as I see,fixed mapping relname to relation filename is the
> > problem. This doesn't allow the coexistence of old and new relation
> > files.
> 
> Yes, and Vadim has proposed using relation OIDs as filenames for
> reasons of his own, IIRC.
>

Yes,I know it.
But I'm now inclined to generate unique relation file name each time
in order to have different file names for different versions of a same
relation oid.  Without chainging relation oids,we would be to do 
nothing about their attributes/constraints etc.

Anyway this must be decided after sufficient discussion.
It's not the time to do it now.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T02:12:32Z

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> But I'm now inclined to generate unique relation file name each time
> in order to have different file names for different versions of a same
> relation oid.  Without chainging relation oids,we would be to do 
> nothing about their attributes/constraints etc.

I was thinking about adding a "version number" to the pg_class entry
for a relation, and then having its actual filename look like

	RELATIONOID_vVERSION.SEGMENT

Then we have:

	* Table rename: doesn't change the filename at all

	* VACUUM with rebuild or ADD/DROP COLUMN: write new data into
	  files with an incremented version number.  Also heap_update
	  the pg_class tuple with new version number.  At instant of
	  commit, voila: the new files are valid, the old ones aren't.
	  Works for indexes, too.

> Anyway this must be decided after sufficient discussion.
> It's not the time to do it now.

Agreed.  I think we are too close to 7.0 beta to consider doing this.
We can start thinking about it for 7.1 though.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T02:17:49Z

[Charset ISO-8859-1 unsupported, filtering to ASCII...]
> On 2000-01-23, Don Baccus mentioned:
> 
> > I certainly don't want to discourage Peter, either, and perhaps
> > was a bit too harsh.  But release of a feature this half-baked
> > would fit the stereotype many people have held towards free,
> > open source software, and postgres in particular.
> 
> Who said something of a release? Whatever happened to release early,
> release often?

We don't do that here.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T02:41:37Z

At 09:12 PM 1/24/00 -0500, Tom Lane wrote:

>	* VACUUM with rebuild or ADD/DROP COLUMN: write new data into
>	  files with an incremented version number.

Just a reality check for my learning of the internals.  Out of curiousity
I coincidently have spent the last hour looking to see how add column's
implemented.  It doesn't appear to do anything other than the new attribute
to the proper system table.  heap_getattr() just returns null if you ask
for an attribute past the end of the tuple.  

This would appear to be (at least one reason) why you can't add a "not null"
constraint to a column you're adding to an existing relation, or set the
new column to some non-null default value.

Correct?  (again, to see if my eyeballs and brain are working in synch
tonight)

Does your comment imply that it's planned to change this, i.e. actually
add the new column to each tuple in the relation rather than use the
existing, somewhat elegant hack?

Just curious...

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T02:48:46Z

> I was unaware that other folks had pointed to the copy/rename approach
> earlier as being a possible means of implementation.  I thought you'd
> pulled that one out of your hat.  Still, more advance discussion would've
> perhaps led to other approaches to investigate, just as discussion
> now is doing.
> 
> I'm going to be blunt: implementation of "drop column" by doing a copy/rename
> isn't something one expects of a competitive commercial-quality RDBMS.
> 
> Perhaps it's the best we can expect of Postgres, though.  If so, so be it.

Maybe that is true.  Having phantom column all over the code is going to
be a mess, and hardly worth it considering how many developers there are
and how many _big_ items still have to be done.

Messing up code for one feature is rarely worth it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Chris Bitmead <chris@bitmead.com> — 2000-01-25T02:50:30Z

Tom Lane wrote:
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > But I'm now inclined to generate unique relation file name each time
> > in order to have different file names for different versions of a same
> > relation oid.  Without chainging relation oids,we would be to do
> > nothing about their attributes/constraints etc.
> 
> I was thinking about adding a "version number" to the pg_class entry
> for a relation, and then having its actual filename look like

Well, if you are going to re-write the files in place on update, a
version  number seems like overkill. All you need is version "a" and
"b". Every time you do a change it swaps from version RELATIONOID_a to
RELATIONOID_b , or RELATIONOID_b to RELATIONOID_a. Or you could just go
for RELATIONOID and RELATIONOID_new and do a rename (although I guess
you're trying to avoid that).


> 
>         RELATIONOID_vVERSION.SEGMENT
> 
> Then we have:
> 
>         * Table rename: doesn't change the filename at all
> 
>         * VACUUM with rebuild or ADD/DROP COLUMN: write new data into
>           files with an incremented version number.  Also heap_update
>           the pg_class tuple with new version number.  At instant of
>           commit, voila: the new files are valid, the old ones aren't.
>           Works for indexes, too.
> 
> > Anyway this must be decided after sufficient discussion.
> > It's not the time to do it now.
> 
> Agreed.  I think we are too close to 7.0 beta to consider doing this.
> We can start thinking about it for 7.1 though.
> 
>                         regards, tom lane
> 
> ************

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T02:51:51Z

> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > As far as I see,fixed mapping relname to relation filename is the
> > problem. This doesn't allow the coexistence of old and new relation
> > files.
> 
> Yes, and Vadim has proposed using relation OIDs as filenames for
> reasons of his own, IIRC.
> 
> If we did that we could also solve the problems we have now with
> rolling back table deletion/rename inside an aborted transaction.
> 
> I've been resisting this idea, but maybe it's time to bite the bullet
> and accept that relation filenames can't be the same as the logical
> names of the relations.
> 

Yes, that is going to happen, clearly.  New file names will be
table_name+oid.  Solves many problems.

New file name will not be just oid.  Too hard to administer.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T03:18:29Z

Chris Bitmead <chris@bitmead.com> writes:
> Tom Lane wrote:
>> I was thinking about adding a "version number" to the pg_class entry
>> for a relation, and then having its actual filename look like

> Well, if you are going to re-write the files in place on update, a
> version  number seems like overkill. All you need is version "a" and
> "b". Every time you do a change it swaps from version RELATIONOID_a to
> RELATIONOID_b ,

And what happens when I do two (or more) DROP COLUMNs within a single
transaction?  Nope, you need an open-ended counter.

> ... Or you could just go
> for RELATIONOID and RELATIONOID_new and do a rename (although I guess
> you're trying to avoid that).

Precisely.  If we can avoid renaming the files, then we aren't at the
mercy of the OS while moving from "uncommitted" to "committed" state;
the only thing that matters is marking the transaction committed in
pg_log, and that's as atomic as we can make it.

If there's no rename, the worst that can happen is that no-longer-needed
files get left around (if the backend dies between committing and
removing dead files, or if it dies after making the files but before
committing the transaction).  We could arrange for VACUUM to remove such
files.

			regards, tom lane

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T03:22:44Z

> -----Original Message-----
> From: Don Baccus [mailto:dhogaza@pacifier.com]
> 
> At 09:12 PM 1/24/00 -0500, Tom Lane wrote:
> 
> >	* VACUUM with rebuild or ADD/DROP COLUMN: write new data into
> >	  files with an incremented version number.
> 
> Just a reality check for my learning of the internals.  Out of curiousity
> I coincidently have spent the last hour looking to see how add column's
> implemented.  It doesn't appear to do anything other than the new 
> attribute
> to the proper system table.  heap_getattr() just returns null if you ask
> for an attribute past the end of the tuple.  
> 
> This would appear to be (at least one reason) why you can't add a 
> "not null"
> constraint to a column you're adding to an existing relation, or set the
> new column to some non-null default value.
>
> Correct?  (again, to see if my eyeballs and brain are working in synch
> tonight)
>

Probably yes.
Adding NOT NULL constraints to new column needs to apply default
value to existent rows and this would need either implicit updation of
existent rows or to copy into different version of the relation file. .

Do other DBMSs allow such things ?
For example,in Oracle NOT NULL constraint could be specified for new
column only when there's no row in the table AFAIK.

I couldn't judge it's worth the work.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Mike Mascari <mascarm@mascari.com> — 2000-01-25T03:31:37Z

Hiroshi Inoue wrote:
> > Correct?  (again, to see if my eyeballs and brain are working in synch
> > tonight)
> >
> 
> Probably yes.
> Adding NOT NULL constraints to new column needs to apply default
> value to existent rows and this would need either implicit updation of
> existent rows or to copy into different version of the relation file. .
> 
> Do other DBMSs allow such things ?
> For example,in Oracle NOT NULL constraint could be specified for new
> column only when there's no row in the table AFAIK.
> 
> I couldn't judge it's worth the work.
> 
> Regards.
> 
> Hiroshi Inoue
> Inoue@tpf.co.jp
> 

But the times when I've found it would be nice to use ALTER TABLE
ADD COLUMN with NOT NULL constraints is in development mode when
the schema is changing rapidly and there actually isn't any data
yet in the tables. Otherwise, to add a new NOT NULL column during
a development cycle, one has to drop and recreate the table, all
triggers, comments, etc. Its just a real pain. :-(

Mike Mascari

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T03:57:12Z

Don Baccus <dhogaza@pacifier.com> writes:
> Just a reality check for my learning of the internals.  Out of curiousity
> I coincidently have spent the last hour looking to see how add column's
> implemented.  It doesn't appear to do anything other than the new attribute
> to the proper system table.  heap_getattr() just returns null if you ask
> for an attribute past the end of the tuple.  

> This would appear to be (at least one reason) why you can't add a "not null"
> constraint to a column you're adding to an existing relation, or set the
> new column to some non-null default value.

> Correct?  (again, to see if my eyeballs and brain are working in synch
> tonight)

Yup, that's about the size of it.  ADD COLUMN doesn't actually touch the
table itself, so it can only add a column that's initially all NULLs.
And even this depends on some uncomfortable assumptions about the
robustness of heap_getattr().  I have always wondered whether it works
if you ADD COLUMN a 33'rd column (or anything that is just past the
next padding boundary for the null-values bitmap).

Another problem with it is seen when you do a recursive ADD COLUMN in
an inheritance tree.  The added column has the first free column number
in each table, which generally means that it has different numbers in
the children than in the parent.  There are some kluges to make this
sort-of-work for simple cases, but a lot of stuff fails unpleasantly
--- Chris Bitmead can show you some scars from that, IIRC.

> Does your comment imply that it's planned to change this, i.e. actually
> add the new column to each tuple in the relation rather than use the
> existing, somewhat elegant hack?

That's what I would like to see: all the children should have the
same column numbers for all columns that they inherit from the parent.

(Now, this would mean not only physically altering the tuples of
the children, but also renumbering their added columns, which has
implications on stored rules and triggers and so forth.  It'd be
painful, no doubt about it.  Still, I'd rather pay the price in the
seldom-used ADD COLUMN case than try to deal with out-of-sync column
numbers in many other, more commonly exercised, code paths.)

			regards, tom lane

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T04:00:04Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> New file name will not be just oid.  Too hard to administer.

But if it contains the table name, you still have the problem we'd
like to avoid: renaming the file(s) is a critical part of committing
a table-renaming transaction.

The debugging and administrative convenience of using table names as
filenames is exactly why we've resisted this for so long.  But I am
starting to think that we have to give that up.  It's nice, but
having robust DDL commands is nicer.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T04:35:31Z

> The debugging and administrative convenience of using table names as
> filenames is exactly why we've resisted this for so long.  But I am
> starting to think that we have to give that up.  It's nice, but
> having robust DDL commands is nicer.

Because you want to have multiple versions of a table with the same name
and table oid.  We don't have to have the oid equal the oid of the
table.  Just use any oid to append to the table name for versioning.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T04:44:31Z

At 09:48 PM 1/24/00 -0500, Bruce Momjian wrote:

>Maybe that is true.  Having phantom column all over the code is going to
>be a mess, and hardly worth it considering how many developers there are
>and how many _big_ items still have to be done.

Works for Oracle...I guess Postgres is just an obviously more robust,
faster, more reliable, and altogether more brilliant RDBMS than this
loser commercial DB?  It's really hard to understand why Postgres has
had such a poor reputation over the years when faced with such facts,
isn't it?

>Messing up code for one feature is rarely worth it.

Dropping constraints on a table just because you drop a column is
just butt-ugly.

Sorry if you disagree.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T04:46:28Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Because you want to have multiple versions of a table with the same name
> and table oid.  We don't have to have the oid equal the oid of the
> table.  Just use any oid to append to the table name for versioning.

That'd do for versioning, but what about a plain table-renaming
operation?  We aren't making a new file in that case, and we still
want to avoid rename().

			regards, tom lane

RE: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T04:50:03Z

At 12:22 PM 1/25/00 +0900, Hiroshi Inoue wrote:
>> -----Original Message-----
>> From: Don Baccus [mailto:dhogaza@pacifier.com]

>> This would appear to be (at least one reason) why you can't add a 
>> "not null"
>> constraint to a column you're adding to an existing relation, or set the
>> new column to some non-null default value.
>>
>> Correct?  (again, to see if my eyeballs and brain are working in synch
>> tonight)

>Probably yes.
>Adding NOT NULL constraints to new column needs to apply default
>value to existent rows and this would need either implicit updation of
>existent rows or to copy into different version of the relation file. .

Right...and my reading says neither happens.

(and I'm not arguing that either should, I posed my question as a reality
check as to whether or not a pedantic, lame-brain like myself is actually
capable of reading and understanding the code)

>Do other DBMSs allow such things ?

Don't know, haven't researched it.  I think it's a reasonable restriction,
though.

>For example,in Oracle NOT NULL constraint could be specified for new
>column only when there's no row in the table AFAIK.

I think that's right.

>I couldn't judge it's worth the work.

Gosh, and I hope you didn't think I was suggesting it was.  I was simply
responding to Tom's saying that "ADD/DROP column" would require a new
file for the updated relation, and asking if the current situation was
slated for change (because the current situation doesn't require that
the relation be copied to a new file after a "alter table add column",
if I read the code right).

That's all...I just wasn't able to relate Tom's point to how things 
appear to work, and wondered if I was missing something crucial in my
reading of the code.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T05:00:56Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Because you want to have multiple versions of a table with the same name
> > and table oid.  We don't have to have the oid equal the oid of the
> > table.  Just use any oid to append to the table name for versioning.
> 
> That'd do for versioning, but what about a plain table-renaming
> operation?  We aren't making a new file in that case, and we still
> want to avoid rename().

I see.  Hard to see how the advantage of fixing that would be worth
losing table names as files.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T05:01:43Z

At 10:57 PM 1/24/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:

>> Correct?  (again, to see if my eyeballs and brain are working in synch
>> tonight)

>Yup, that's about the size of it.

Phew!  I'm sane!  

>  ADD COLUMN doesn't actually touch the
>table itself, so it can only add a column that's initially all NULLs.
>And even this depends on some uncomfortable assumptions about the
>robustness of heap_getattr().

Yeah...that was my thought...you did notice I included the word
"kludge", yeah?  

>  I have always wondered whether it works
>if you ADD COLUMN a 33'rd column (or anything that is just past the
>next padding boundary for the null-values bitmap).

Way beyond my knowledge of things today, but if you REALLY CARE, I
could probably figure it out :)

>Another problem with it is seen when you do a recursive ADD COLUMN in
>an inheritance tree.  The added column has the first free column number
>in each table, which generally means that it has different numbers in
>the children than in the parent.  There are some kluges to make this
>sort-of-work for simple cases, but a lot of stuff fails unpleasantly
>--- Chris Bitmead can show you some scars from that, IIRC.

I think this probably explains some of the editorial comments about the
code.  There seem to be some added by "XXX" - is that person part of the
current clan of developers?  Comments like "This code is a crock because..."

>> Does your comment imply that it's planned to change this, i.e. actually
>> add the new column to each tuple in the relation rather than use the
>> existing, somewhat elegant hack?
>
>That's what I would like to see: all the children should have the
>same column numbers for all columns that they inherit from the parent.

>(Now, this would mean not only physically altering the tuples of
>the children, but also renumbering their added columns, which has
>implications on stored rules and triggers and so forth.  It'd be
>painful, no doubt about it.  Still, I'd rather pay the price in the
>seldom-used ADD COLUMN case than try to deal with out-of-sync column
>numbers in many other, more commonly exercised, code paths.)

Yeah...though I don't know the code well enough to comment on the pain,
in the wider, more general sense I totally agree.  I don't think 
efficiency of ADD COLUMN (or DROP for that matter) should be of paramount
concern, even though Oracle gives a separate quick DROP option similar to
what's being discussed as an alternative to COPY/RENAME.

My discomfort with the latter has more to do with the dropping of 
constraints (and I've read enough of the code to see that rebuilding
them might be a worthy subject for a Hitchcock film)

Cool...please be patient with me as I work my way through this stuff
in my spare time.  

And, Peter, if you're reading this, please be assured that my criticisms
are meant only in the context of wanting to see this open source database
become an Oracle (or other) slayer in at least a segment of the marketplace.
On its merits, not the price differential.  If I'm harsh on things that I
feel don't quite meet the mark, please don't take it personally.

And if I'm proven wrong, I'm always more than willing to admit it far
and wide, and to apologize in public.  

I just want to see debate over the "howtos" based on merit, that's it.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T05:08:01Z

> At 09:48 PM 1/24/00 -0500, Bruce Momjian wrote:
> 
> >Maybe that is true.  Having phantom column all over the code is going to
> >be a mess, and hardly worth it considering how many developers there are
> >and how many _big_ items still have to be done.
> 
> Works for Oracle...I guess Postgres is just an obviously more robust,
> faster, more reliable, and altogether more brilliant RDBMS than this
> loser commercial DB?  It's really hard to understand why Postgres has
> had such a poor reputation over the years when faced with such facts,
> isn't it?
> 
> >Messing up code for one feature is rarely worth it.
> 
> Dropping constraints on a table just because you drop a column is
> just butt-ugly.

We aren't charging 100k either.  We do what we can, and spend time where
it is most valuable.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T05:17:28Z

At 12:08 AM 1/25/00 -0500, Bruce Momjian wrote:

>> Dropping constraints on a table just because you drop a column is
>> just butt-ugly.

>We aren't charging 100k either.  We do what we can, and spend time where
>it is most valuable.

Perhaps I've misunderstood, then...I'd thought part of the goal was to
compete with the 100K model without charging 100K...

If not, OK.

(BTW, Oracle for development is free and you can actually cruise with
that for some time.  A five-user license for non-internet use costs
$1450 for Linux)

And further BTW...some folks recently went through a little handwringing
upon hearing the Interbase will be released in free, open source form.

And there's a recent, though not current, Sybase version available for
Linux users for free, too...

If the point's not to be competitive with other free or low cost options,
is there any point at all?

Is it time for me to apologize for having high standards?  

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T05:29:20Z

> If the point's not to be competitive with other free or low cost options,
> is there any point at all?
> 
> Is it time for me to apologize for having high standards?  

We have to be realistic.  It is no good to have one command that has 100
fancy capabilities while 100 other commands are broken or are more
important.

Doesn't mean we don't do a great job, but that sometimes it is not worth
it considering the other things we can be doing.

If you are suggesting we don't follow that plan, I have to disagree.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T05:29:26Z

Don Baccus <dhogaza@pacifier.com> writes:
> And, Peter, if you're reading this, please be assured that my criticisms
> are meant only in the context of wanting to see this open source database
> become an Oracle (or other) slayer in at least a segment of the marketplace.
> On its merits, not the price differential.  If I'm harsh on things that I
> feel don't quite meet the mark, please don't take it personally.

Not pretending to speak for Peter, but --- I don't think there's anybody
here who hasn't got the highest standards in mind as an end goal.  Yet
we have many miles to go, and we seldom agree on whether a particular
problem is the most critical next thing to work on.  Each of us is doing
what comes to hand to be done.  Sometimes we can fix a problem properly,
and sometimes we can only put a finger in the dike, or apply triage and
say "that's going to have to go unfixed a while longer".

Yup, it's a little disorganized, but that's both the curse and the
blessing of open-source development.  Anybody who's *really* annoyed
by a particular problem is welcome to come and work on it.

			regards, tom lane

Oh btw, about XXX

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T05:39:14Z

Don Baccus <dhogaza@pacifier.com> writes:
> I think this probably explains some of the editorial comments about the
> code.  There seem to be some added by "XXX" - is that person part of the
> current clan of developers?  Comments like "This code is a crock because..."

XXX isn't a signature, it's a conventional marker for a "Hey! This is
broken! FIX ME!" kind of comment.  I think the original idea was you
could do "grep XXX *.c" if you were idly looking for problems to work
on.  Some projects use "FIXME" in the same way.

The only signatures I've seen in the Postgres code are initials at the
ends of comments.  XXX usually goes at the front of a gripe.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-25T06:39:53Z

On Mon, 24 Jan 2000, Don Baccus wrote:

> At 09:48 PM 1/24/00 -0500, Bruce Momjian wrote:
> 
> >Maybe that is true.  Having phantom column all over the code is going to
> >be a mess, and hardly worth it considering how many developers there are
> >and how many _big_ items still have to be done.
> 
> Works for Oracle...I guess Postgres is just an obviously more robust,
> faster, more reliable, and altogether more brilliant RDBMS than this
> loser commercial DB?  It's really hard to understand why Postgres has
> had such a poor reputation over the years when faced with such facts,
> isn't it?

Woah ... pull back here ... I haven't got a *clue* where this response
came from, but, from what I can tell, it was *totally* uncalled
for.  Oracle makes how many *millions* of dollars a year to sink into
programmers dedicated to it?  We have how many developers in comparison,
who don't get paid and who work on things they feel is important ... and,
there are alot bigger items on the TODO list ...

If we had unlimited (or near so) monetary resourses, fine, but we don't,
we have alot of volunteers who spend alot of *personal* time advancing
this project ... we are *not* trying to compete directly with Oracle, we
are trying to create a product that ppl *trust* and can *rely* on
... Bruce's comment above about "going to be a mess", to me, interpretes
as "there has gotta be a cleaner way of doing it that we haven't come up
with yet" ... 

> >Messing up code for one feature is rarely worth it.
> 
> Dropping constraints on a table just because you drop a column is
> just butt-ugly.
> 
> Sorry if you disagree.

And yes, I do agree here ... I like the solution that one person presented
that had it so that you couldn't drop the column unless as constraints and
requirements were removed first ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T06:46:43Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgresql.org
> [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of Tom Lane
> 
> robustness of heap_getattr().  I have always wondered whether it works
> if you ADD COLUMN a 33'rd column (or anything that is just past the
> next padding boundary for the null-values bitmap).
>

If so,it's very serious.
ADD COLUMN feature is much more important than other ALTER TABLE
options and it's also important to implement it without touching the table
as possible.
It seems to me that each tuple has t_natts and t_hoff for the change of
table definition. 

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Marc G. Fournier <scrappy@hub.org> — 2000-01-25T06:50:57Z

On Mon, 24 Jan 2000, Don Baccus wrote:

> At 12:08 AM 1/25/00 -0500, Bruce Momjian wrote:
> 
> >> Dropping constraints on a table just because you drop a column is
> >> just butt-ugly.
> 
> >We aren't charging 100k either.  We do what we can, and spend time where
> >it is most valuable.
> 
> Perhaps I've misunderstood, then...I'd thought part of the goal was to
> compete with the 100K model without charging 100K...
> 
> If not, OK.
> 
> (BTW, Oracle for development is free and you can actually cruise with
> that for some time.  A five-user license for non-internet use costs
> $1450 for Linux)
> 
> And further BTW...some folks recently went through a little handwringing
> upon hearing the Interbase will be released in free, open source form.
> 
> And there's a recent, though not current, Sybase version available for
> Linux users for free, too...
> 
> If the point's not to be competitive with other free or low cost options,
> is there any point at all?
> 
> Is it time for me to apologize for having high standards?  

Not necessarily ... only for expecting more from volunteers then they are
willing, or have time, to put forth ... 

Where does Sybase make its money?  Oracle?  Inbase?  In Oracle's case, it
is what we work with *heavily* at my *real* job, and I know where they
make their money ... support contracts that we never use ...

PostgreSQL, Inc was created 6+ months ago to provide a means of clients
getting commerical support, and it does enough to keep things afloat, but
rich we are not getting ... my *hope* when forming this was that we could
generate enough revenues to go out and hire someone to deal with features
that are so time-consuming that they are difficult to implement ... its a
slow growth ... maybe in 6mos we'll be able to do this ... we keep coming
up with new ways of increasing revenues and we dedicate a certain
percentage of each to a 'kitty' to be used for PostgreSQL related projects
... that kitty is small, but growing ...

We try with those resources we have ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

RE: [HACKERS] Happy column dropping

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T07:18:22Z

> -----Original Message-----
> From: Mike Mascari [mailto:mascarm@mascari.com]
> 
> > 
> > Do other DBMSs allow such things ?
> > For example,in Oracle NOT NULL constraint could be specified for new
> > column only when there's no row in the table AFAIK.
> > 
> > I couldn't judge it's worth the work.
> > 
> 
> But the times when I've found it would be nice to use ALTER TABLE
> ADD COLUMN with NOT NULL constraints is in development mode when
> the schema is changing rapidly and there actually isn't any data
> yet in the tables. Otherwise, to add a new NOT NULL column during
> a development cycle, one has to drop and recreate the table, all
> triggers, comments, etc. Its just a real pain. :-(
>

I don't know details about constraints.
Probably you could implement it better than me.

Even default is not allowed in ADD COLUMN now. 
There may be other reasons why they aren't allowed.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Mike Mascari <mascarm@mascari.com> — 2000-01-25T07:24:10Z

The Hermit Hacker wrote:
> 
> On Mon, 24 Jan 2000, Don Baccus wrote:
> 
> > At 09:48 PM 1/24/00 -0500, Bruce Momjian wrote:
> >
> > >Maybe that is true.  Having phantom column all over the code is going to
> > >be a mess, and hardly worth it considering how many developers there are
> > >and how many _big_ items still have to be done.
> >
> > Works for Oracle...I guess Postgres is just an obviously more robust,
> > faster, more reliable, and altogether more brilliant RDBMS than this
> > loser commercial DB?  It's really hard to understand why Postgres has
> > had such a poor reputation over the years when faced with such facts,
> > isn't it?
> 
> Woah ... pull back here ... I haven't got a *clue* where this response
> came from, but, from what I can tell, it was *totally* uncalled
> for.  Oracle makes how many *millions* of dollars a year to sink into
                               ^^^^^^^^
Billions. Oracle had 8.82 billion in sales in FY 1999, 1.2
billion in earnings (profit). 

> programmers dedicated to it?  

Oracle has 43,800 employees.

> We have how many developers in comparison,
> who don't get paid and who work on things they feel is important ... and,
> there are alot bigger items on the TODO list ...

The market capitalization of Oracle before the split a week ago
was $147 billion dollars. Larry Ellison owns 24% of the company
and is thus worth $35 billion dollars alone.  The least Don could
do is buy a T-shirt or a key ring or something... ;-)

Mike Mascari

Re: [HACKERS] Oh btw, about XXX

Alfred Perlstein <bright@wintelcom.net> — 2000-01-25T08:42:00Z

* Tom Lane <tgl@sss.pgh.pa.us> [000124 22:10] wrote:
> Don Baccus <dhogaza@pacifier.com> writes:
> > I think this probably explains some of the editorial comments about the
> > code.  There seem to be some added by "XXX" - is that person part of the
> > current clan of developers?  Comments like "This code is a crock because..."
> 
> XXX isn't a signature, it's a conventional marker for a "Hey! This is
> broken! FIX ME!" kind of comment.  I think the original idea was you
> could do "grep XXX *.c" if you were idly looking for problems to work
> on.  Some projects use "FIXME" in the same way.

It can also mean 'blech, this should be cleaner'  such as the use
of 'curproc' in the FreeBSD kernel do figure out which process is
using the current codepath, sometimes you need it and the API
doesn't pass it down to you, hence
   struct proc *p = curproc; /* XXX */ 
which is strewn about the code.

> The only signatures I've seen in the Postgres code are initials at the
> ends of comments.  XXX usually goes at the front of a gripe.

gvim even highlights them in bright inverse yellow. :)

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: [HACKERS] Happy column dropping

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-25T10:38:26Z

On Mon, 24 Jan 2000, Bruce Momjian wrote:

> New file name will not be just oid.  Too hard to administer.

How exactly does this play into administration? You don't actually do a cp
/usr/local/pgsql/data/base/testdb/mytable /some/where to back up? ;)

What did the Unix kernel programmers think when they first introduced
inodes? The wrote the readdir() library call. We have that too, it's
called pg_class.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-25T10:41:55Z

On Mon, 24 Jan 2000, Don Baccus wrote:

> Dropping constraints on a table just because you drop a column is
> just butt-ugly.

Hey, nobody said that this was the final version. Keeping the constraints
was a trivial step from what there was. I guess the lesson I learned was
that around here you Release Late, Release Rarely, and that's fine, but I
didn't know that. Sorry. -> next time ;)

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Happy column adding (was RE: [HACKERS] Happy column dropping)

Peter Eisentraut <e99re41@docs.uu.se> — 2000-01-25T10:55:23Z

On Tue, 25 Jan 2000, Hiroshi Inoue wrote:

> Even default is not allowed in ADD COLUMN now. 
> There may be other reasons why they aren't allowed.

It's not a matter of *allowed*, it's a parsing deficiency. The fact that
there was a default declared gets silently ignored. If y'all allow ;) I
would like to fix that (have already started a bit) by perusing the code
in parse_func.c:transformCreateStmt and do the same for the alter table
add column part. Maybe and add/drop constraint will come out in the end as
well.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T14:14:29Z

> On Mon, 24 Jan 2000, Bruce Momjian wrote:
> 
> > New file name will not be just oid.  Too hard to administer.
> 
> How exactly does this play into administration? You don't actually do a cp
> /usr/local/pgsql/data/base/testdb/mytable /some/where to back up? ;)
> 
> What did the Unix kernel programmers think when they first introduced
> inodes? The wrote the readdir() library call. We have that too, it's
> called pg_class.
> 

Ingres has table names as numbered files.  It is a pain to figure out
which files match which tables.  If you need to restore a table from
tape, the pg_class entry is gone and you have no way to figure out the
right table.  When analyzing disk space, figuring who is using the space
is a pain.

Every Ingre admin I ever talked to agreed file numbers are a pain.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T14:15:04Z

> On Mon, 24 Jan 2000, Don Baccus wrote:
> 
> > Dropping constraints on a table just because you drop a column is
> > just butt-ugly.
> 
> Hey, nobody said that this was the final version. Keeping the constraints
> was a trivial step from what there was. I guess the lesson I learned was
> that around here you Release Late, Release Rarely, and that's fine, but I
> didn't know that. Sorry. -> next time ;)

I probably lead you astray on that one.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Brian E Gallew <geek+@cmu.edu> — 2000-01-25T14:53:34Z

Then <pgman@candle.pha.pa.us> spoke up and said:
> Ingres has table names as numbered files.  It is a pain to figure out
> which files match which tables.  If you need to restore a table from
> tape, the pg_class entry is gone and you have no way to figure out the
> right table.  When analyzing disk space, figuring who is using the space
> is a pain.
> 
> Every Ingre admin I ever talked to agreed file numbers are a pain.

I, too, found the Ingres naming scheme to be a pain, especially since
the numbers change when you drop/recreate a table (but not for
truncation).  In my case, I wrote a fairly trivial script that runs as
part of the backup job that lists all tables and filenames.  This give
me an accurate map of whats out there.  It has the added advantage
that I can use this list to remove tables/database owned by users who
no longer exist (although, in truth, I hesitate to do that
automatically).

On the other hand, I *hate* the Oracle tablespace scheme.  At least
with Ingres, when I update a table, I *don't* have to backup all of
the other tables in that database (barring Oracle's hot-backup mode,
of course).

-- 
=====================================================================
| JAVA must have been developed in the wilds of West Virginia.      |
| After all, why else would it support only single inheritance??    |
=====================================================================
| Finger geek@cmu.edu for my public key.                            |
=====================================================================

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T15:24:32Z

At 12:29 AM 1/25/00 -0500, Tom Lane wrote:

>Not pretending to speak for Peter, but --- I don't think there's anybody
>here who hasn't got the highest standards in mind as an end goal.  Yet
>we have many miles to go, and we seldom agree on whether a particular
>problem is the most critical next thing to work on.  Each of us is doing
>what comes to hand to be done.  Sometimes we can fix a problem properly,
>and sometimes we can only put a finger in the dike, or apply triage and
>say "that's going to have to go unfixed a while longer".

Yes, I know, and I'm probably being a bit over-obnoxious.

>Yup, it's a little disorganized, but that's both the curse and the
>blessing of open-source development.  Anybody who's *really* annoyed
>by a particular problem is welcome to come and work on it.

Well...I'm trying to spend about four hours a week reading code, maybe
someday I'll get there :)  Once we're done porting the arsDigita Community
System from Oracle to Posgres and once I'm done with my current contract,
I'll have more time to play.

I realize that at some point I need to do more than just whine about
things.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T15:35:52Z

At 12:29 AM 1/25/00 -0500, Bruce Momjian wrote:

>We have to be realistic.  It is no good to have one command that has 100
>fancy capabilities while 100 other commands are broken or are more
>important.
>
>Doesn't mean we don't do a great job, but that sometimes it is not worth
>it considering the other things we can be doing.
>
>If you are suggesting we don't follow that plan, I have to disagree.

In general, I don't disagree with this.  But a drop column command that
kills all constraints on a table won't be terribly useful to folks.  I'm
thinking of the fact that this same version will have referential 
integrity constraints, which will be used by many.  These will be dropped,
too, if I understand things correctly.

I think my emotional reaction is mostly to the fact that it was 
put into sources that I presumed were to be released in beta form
just a few days afterwards.  With no prior discussion.

>From Peter's notes, it is clear that his perception of a beta version
might differ somewhat from that which has been traditional with the
postgres group.  And that makes me very nervous, since I'm planning
to try to base further porting work on that beta.  Obviously, I
don't need to use "drop column", but if the release model drifts more
towards the "break often, break early" model then then I'll have to
rethink my usage of Postgres betas.

(no, we won't release our port on the beta, we're just hoping that
the beta will be solid enough that we can work with it, and release
our beta on top of the resulting version of PG).

Now, couple this with all the problems associated with some apparently
below-par changes to libpq - which I absolutely depend on - and I start
worrying that the excellent stability of the 6.5 beta and subsequent
full releases might become an abberration rather than the norm.

I know folks don't want that...

Anyway, this perhaps is mostly a communication problem, as Peter
apparently thought that the Postgres group follows the "release early,
release often" model.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T15:53:53Z

At 11:41 AM 1/25/00 +0100, Peter Eisentraut wrote:
>On Mon, 24 Jan 2000, Don Baccus wrote:
>
>> Dropping constraints on a table just because you drop a column is
>> just butt-ugly.
>
>Hey, nobody said that this was the final version. Keeping the constraints
>was a trivial step from what there was. I guess the lesson I learned was
>that around here you Release Late, Release Rarely, and that's fine, but I
>didn't know that. Sorry. -> next time ;)

Yes, I've thought about this and as I mentioned in another note a few
minutes ago, realize now that you believed that to be the release model.

I now understand why you were willing to dump a change like this into
the sources a few days before a planned beta release.  Perfectly
reasonable under the more agressive release early, release often
model.

I'll back off now.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T16:01:25Z

At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
>On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
>
>> Even default is not allowed in ADD COLUMN now. 
>> There may be other reasons why they aren't allowed.
>
>It's not a matter of *allowed*, it's a parsing deficiency. The fact that
>there was a default declared gets silently ignored. If y'all allow ;) I
>would like to fix that (have already started a bit) by perusing the code
>in parse_func.c:transformCreateStmt and do the same for the alter table
>add column part. Maybe and add/drop constraint will come out in the end as
>well.

However, heap_getattr still won't see the default since it simply
checks to see of the attribute number falls off the end of the
tuple and then returns null.

There's no provision for then pulling out the default value and
returning it instead.

I think this is why Tom was implying that add column should really
alter the table?

A fully-featured "add column" feature would be very nice, indeed.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

RE: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-25T16:06:22Z

> -----Original Message-----
> From: Peter Eisentraut [mailto:e99re41@DoCS.UU.SE]
>
> On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
>
> > Even default is not allowed in ADD COLUMN now.
> > There may be other reasons why they aren't allowed.
>
> It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> there was a default declared gets silently ignored. If y'all allow ;) I
> would like to fix that (have already started a bit) by perusing the code
> in parse_func.c:transformCreateStmt and do the same for the alter table
> add column part. Maybe and add/drop constraint will come out in the end as
> well.
>

IIRC,there were some reason that default for new column had been rejected.
It may be possible now.
Probably Tom knows it.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T16:35:20Z

> At 11:41 AM 1/25/00 +0100, Peter Eisentraut wrote:
> >On Mon, 24 Jan 2000, Don Baccus wrote:
> >
> >> Dropping constraints on a table just because you drop a column is
> >> just butt-ugly.
> >
> >Hey, nobody said that this was the final version. Keeping the constraints
> >was a trivial step from what there was. I guess the lesson I learned was
> >that around here you Release Late, Release Rarely, and that's fine, but I
> >didn't know that. Sorry. -> next time ;)
> 
> Yes, I've thought about this and as I mentioned in another note a few
> minutes ago, realize now that you believed that to be the release model.
> 
> I now understand why you were willing to dump a change like this into
> the sources a few days before a planned beta release.  Perfectly
> reasonable under the more agressive release early, release often
> model.

Maybe I have to get that into the developers FAQ.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T16:36:09Z

> Now, couple this with all the problems associated with some apparently
> below-par changes to libpq - which I absolutely depend on - and I start
> worrying that the excellent stability of the 6.5 beta and subsequent
> full releases might become an abberration rather than the norm.
> 
> I know folks don't want that...
> 
> Anyway, this perhaps is mostly a communication problem, as Peter
> apparently thought that the Postgres group follows the "release early,
> release often" model.

There are no plans to change the way we release -- release rarely,
release stable.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Happy column dropping

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-01-25T16:37:11Z

On Tue, Jan 25, 2000 at 09:14:29AM -0500, Bruce Momjian wrote:
> > On Mon, 24 Jan 2000, Bruce Momjian wrote:
> > 
> > > New file name will not be just oid.  Too hard to administer.
> > 
> 
> Ingres has table names as numbered files.  It is a pain to figure out
> which files match which tables.  If you need to restore a table from
> tape, the pg_class entry is gone and you have no way to figure out the
> right table.  When analyzing disk space, figuring who is using the space
> is a pain.
> 

Hmm, how about a map file, of conventional name, kept in the pgsql/data
dir, that contains filename -> db/tablename mappings? It would be
essentially a pretty printed dump of pg_class. That way, the admin
has access to the mapping even when the postmaster is down. Even to
restore from tape: grab pg_class_map from the tape (you did dump it
to the beginning, right?) and then grab the file you need. I don't see
this as being a particularly large file, in any case, and DDL isn't a
speed critical path, so rewriting the pg_class_map file wouldn't hurt.
And, since it's just for human/admin consumption, so major problem if
it gets out of sync: just regenerate it from pg_class. Could even be
used to sanity check the DBMS on start up: generate a new pg_class_map,
compare it to the old: if they don't match, fire a warning/die?

I'd suggest a format that's easily machine by awk/sed/grep (perl, etc.)
for those db admin tasks Bruce is talking about (space, etc.) Heck, 
it'd be easy to whip up a 'generate a bunch of symlinks' script
to get the tablenames back, if you really need them.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>          Tel. 713-348-6166
NSBRI Research Scientist/Programmer                    Fax  713-348-6182
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T17:23:15Z

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>>>> Even default is not allowed in ADD COLUMN now.
>> 
>> It's not a matter of *allowed*, it's a parsing deficiency. The fact that
>> there was a default declared gets silently ignored.

> IIRC,there were some reason that default for new column had been rejected.

Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
cause every row currently existing in the table to acquire x = 42,
rather than x = NULL?  In fact that would *have* to happen to allow
constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.

The only way to make that happen is for ADD COLUMN to switch over to
an implementation that rewrites all the tuples.  Which I think is the
right way to go ... but per this discussion, it's not a trivial fix.

			regards, tom lane

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T17:39:25Z

Don Baccus <dhogaza@pacifier.com> writes:
> However, heap_getattr still won't see the default since it simply
> checks to see of the attribute number falls off the end of the
> tuple and then returns null.

Right.

> There's no provision for then pulling out the default value and
> returning it instead.

Would it even be possible to do that?  I'm not sure that heap_getattr
has any way to find the default.  It only has a TupleDesc, not a
reference to the relation itself...

			regards, tom lane

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-01-25T17:44:53Z

On Tue, Jan 25, 2000 at 08:01:25AM -0800, Don Baccus wrote:
> At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
> >On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
> >
> >> Even default is not allowed in ADD COLUMN now. 
> >> There may be other reasons why they aren't allowed.
> >
> >It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> >there was a default declared gets silently ignored. If y'all allow ;) I
> >would like to fix that (have already started a bit) by perusing the code
> >in parse_func.c:transformCreateStmt and do the same for the alter table
> >add column part. Maybe and add/drop constraint will come out in the end as
> >well.
> 
> However, heap_getattr still won't see the default since it simply
> checks to see of the attribute number falls off the end of the
> tuple and then returns null.
> 

Right, but that just means that existing tuples would not get the
default, but all new tuples would, right? So, while it does leave the
data inconsistent with the schema definition, it is fixable, and in a
controlled manner. A simple

UPDATE my_table SET new_field='default' WHERE new_field IS NULL;

should do it, right? 

In fact, that's something I liked about the 'make invisible' strategy
for the ALTER DROP COLUMN case: it allows the DBA to control the
backends activity. If the DBA needs to drop a column from a large table,
but doesn't have space for 2X that table, what does she do? With the
invisible column, she could ALTER DROP, then do a series of updates,
similar to what Tom suggested:

UPDATE my_table SET otherfield=otherfield where table_id>0 and table_id<=100;
VACUUM mytable;
UPDATE my_table SET otherfield=otherfield where table_id>100 and table_id<=200;
VACUUM mytable;

etc.

Similarly, the aftermath of the ADD DEFAULT case could be handled in a
controlled manner, without forcing a 2X table size disk usage.  I _like_
implementations that give the user (in this case, the DBA) control over
what happens, and when. I think this may answer Marc Fournier's desire
for a 'rewrite in place' version of these, since it would allow the
DBA, at their option, to update one tuple at a time (well, it'd be a royal
pain, but could be done...)

> There's no provision for then pulling out the default value and
> returning it instead.

Right, we don't want to special case it. What's wrong with returning a
NULL, for any tuple that hasn't been updated yet? 

> 
> I think this is why Tom was implying that add column should really
> alter the table?
> 
> A fully-featured "add column" feature would be very nice, indeed.
> 

I agree, where full-featured means adding constraints. I disagree that
rewriting the entire table is a good idea. This isn't even only an edge 
case for admins with little disk space. I could very easily imagine
a schema chamge on existing data, where one has a 'flatfile' sort of
table in the tens of gigabytes range, and you want to remove a column,
in order to normalize the table (get rid of address2, zip2, state2,
..., for example).  Requiring transient diskspace of 2X the table,
and a complete rewrite, _as each column is dropped_ whould be really
annoying. Yes, I know, dump/restore, but what about 24/7 systems? Hey,
it's dangerous to do surgery on a live system, but sometimes, you have
little choice.

I realize I just slipped over from the ADD COLUMN to the DROP COLUMN
case, but I think fundamentally, they're very similar, and will probably
get implemented with the same mechanism.

So Bruce, would the inviso-columns really be a mess in the source? We've
already got the 'virtual column' case, with the ADD COLUMN aftermath. 

Perhaps Oliver's suggestion of removing the link between logical and
physical field ordering might help. That could lead to some interesting
storage optimizations, as well. Collecting  all variable types to the
end of a tuple, for example, might be a win.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-01-25T18:29:07Z

On Tue, Jan 25, 2000 at 12:23:15PM -0500, Tom Lane wrote:
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> >>>> Even default is not allowed in ADD COLUMN now.
> >> 
> >> It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> >> there was a default declared gets silently ignored.
> 
> > IIRC,there were some reason that default for new column had been rejected.
> 
> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
> cause every row currently existing in the table to acquire x = 42,
> rather than x = NULL?  In fact that would *have* to happen to allow
> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.

Actually, no I wouldn't expect it. That's mixing DDL and DML in one
statement.  I expect the ALTER command to be pure DDL, and the UPDATE
to be pure DML.

For a detailed brain dump as I thought about this, see my reply to Don Baccus,
one message up. ;-)

Yes, I know the standard isn't that pure: SELECT INTO, for example. Hmm,
is SELECT INTO standard? <FX: Ross grovels through the draft standard text...>

Ouch, reading standards always makes my brain hurt. Especially how you
have to read them upside down. Turns out SELECT INTO is in the standard,
but not the way we implement it.

 13.5  <select statement: single row>

         Function

         Retrieve values from a specified row of a table.

         Format

         <select statement: single row> ::=
              SELECT [ <set quantifier> ] <select list>
                INTO <select target list>
                  <table expression>

         <select target list> ::=
              <target specification> [ { <comma> <target specification> }... ]

and in section 6.2:

         <target specification> ::=
                <parameter specification>
              | <variable specification>

         <simple target specification> ::=
                <parameter name>
              | <embedded variable name>

         <parameter specification> ::=
              <parameter name> [ <indicator parameter> ]

and in section 5.4:

         <parameter name> ::= <colon> <identifier>

So, it looks like SELECT INTO is a single row query that fills variables,
either parameters or embedded, not a combined 'create this table and
fill it' command. Oops!

Ross

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T18:49:01Z

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> Perhaps Oliver's suggestion of removing the link between logical and
> physical field ordering might help. That could lead to some interesting
> storage optimizations, as well. Collecting  all variable types to the
> end of a tuple, for example, might be a win.

I recall seeing comments in the source to the effect that this was
considered (but never implemented) long ago.  It does seem like a
very clean solution to some aspects of the add/drop column problem;
but implementation would be a pain in the neck.  We'd have to go through
all of the source code and decide whether each use of an attribute
number should be using logical or physical number.  It'd be a long slog.

It occurs to me that in at least some of the places where attribute
numbers are currently used, we ought to use *neither* logical nor
physical column position, but rather a permanent unique ID --- the
attribute tuple's OID would work, if it's assigned soon enough to be
used for constraints given in CREATE TABLE.  (Otherwise we could assign
"column serial numbers" that count from 1 for each relation, but are
never changed or recycled within the relation.)

In particular, if parsetrees for stored rules and constraints worked
that way, renumbering attributes that follow the added/dropped column
would become a lot less painful.

			regards, tom lane

Re: [HACKERS] Happy column dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T18:55:42Z

At 11:36 AM 1/25/00 -0500, Bruce Momjian wrote:

>There are no plans to change the way we release -- release rarely,
>release stable.

Yes, I understand that now, etc etc.

I'm ready to put this thread to bed, myself.  Peter, I didn't mean
my criticisms to be personal in any way.  My apologies if they
came off that way.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T19:03:15Z

At 12:23 PM 1/25/00 -0500, Tom Lane wrote:

>The only way to make that happen is for ADD COLUMN to switch over to
>an implementation that rewrites all the tuples.  Which I think is the
>right way to go ... but per this discussion, it's not a trivial fix.

Might it be possible to keep the current (ummm) hack for simple
add column name type and only rewrite for fancy cases?  The current
implementation is awfully fast and there's the advantage of not
needing sufficient disk storage to duplicate the table.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T19:05:43Z

At 12:39 PM 1/25/00 -0500, Tom Lane wrote:

>> There's no provision for then pulling out the default value and
>> returning it instead.
>
>Would it even be possible to do that?  I'm not sure that heap_getattr
>has any way to find the default.  It only has a TupleDesc, not a
>reference to the relation itself...

In that case, then presumably not.  You've just pushed me beyond
the limits of my understanding of that code, I'm afraid!




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T19:20:01Z

At 12:29 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
>On Tue, Jan 25, 2000 at 12:23:15PM -0500, Tom Lane wrote:

>> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
>> cause every row currently existing in the table to acquire x = 42,
>> rather than x = NULL?  In fact that would *have* to happen to allow
>> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.

>Actually, no I wouldn't expect it. That's mixing DDL and DML in one
>statement.  I expect the ALTER command to be pure DDL, and the UPDATE
>to be pure DML.

Hmmm...interesting...is alter table in the standard?  Again, my copy
of Date's SQL 92 primer is somewhere 'wteen Boston, MA and Portland, OR,
so I can't look myself.   Since you've got the standard available you
can answer perhaps?

>Ouch, reading standards always makes my brain hurt. Especially how you
>have to read them upside down. Turns out SELECT INTO is in the standard,
>but not the way we implement it.

Scary!!! :) :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T19:30:01Z

At 01:49 PM 1/25/00 -0500, Tom Lane wrote:

>It occurs to me that in at least some of the places where attribute
>numbers are currently used, we ought to use *neither* logical nor
>physical column position, but rather a permanent unique ID --- the
>attribute tuple's OID would work, if it's assigned soon enough to be
>used for constraints given in CREATE TABLE.  (Otherwise we could assign
>"column serial numbers" that count from 1 for each relation, but are
>never changed or recycled within the relation.)

>In particular, if parsetrees for stored rules and constraints worked
>that way, renumbering attributes that follow the added/dropped column
>would become a lot less painful.

Yes...I see what you're driving at.  Very interesting idea.  The stored
rules and constraints would in this case would still refer to the remaining
columns after a drop, right?

As far as moving from phsyical to logical attribute numbering, and
the long slog involved, it might be possible to work towards this
goal in background mode by providing two sets of access macros/functions
with the same semantics, and as folks have time to slowly work through the
code.   This wouldn't break anything.  Nor would it substitute for
an intense push to finish the job before switching to such a scheme,
and of course there would be many errors.

It might slowly lower the barrier towards switching, though.  The cost
would be a confusing and incomplete separation during the transition
period.

Of course, moving along this path would be something to consider only
if it were decided that eventually such a switch will be made.  And I'm
not at all sure I like it.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T20:08:15Z

> At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
> >On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
> >
> >> Even default is not allowed in ADD COLUMN now. 
> >> There may be other reasons why they aren't allowed.
> >
> >It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> >there was a default declared gets silently ignored. If y'all allow ;) I
> >would like to fix that (have already started a bit) by perusing the code
> >in parse_func.c:transformCreateStmt and do the same for the alter table
> >add column part. Maybe and add/drop constraint will come out in the end as
> >well.
> 
> However, heap_getattr still won't see the default since it simply
> checks to see of the attribute number falls off the end of the
> tuple and then returns null.
> 
> There's no provision for then pulling out the default value and
> returning it instead.
> 
> I think this is why Tom was implying that add column should really
> alter the table?
> 
> A fully-featured "add column" feature would be very nice, indeed.

Oh, so that is why ALTER TABLE can't have a NOT NULLL default. Makes
total sense.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: Happy column adding and dropping

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-01-25T20:22:19Z

On Tue, Jan 25, 2000 at 11:20:01AM -0800, Don Baccus wrote:
> At 12:29 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
> >On Tue, Jan 25, 2000 at 12:23:15PM -0500, Tom Lane wrote:
> 
> >> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
> >> cause every row currently existing in the table to acquire x = 42,
> >> rather than x = NULL?  In fact that would *have* to happen to allow
> >> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.
> 
> >Actually, no I wouldn't expect it. That's mixing DDL and DML in one
> >statement.  I expect the ALTER command to be pure DDL, and the UPDATE
> >to be pure DML.
> 
> Hmmm...interesting...is alter table in the standard?  Again, my copy
> of Date's SQL 92 primer is somewhere 'wteen Boston, MA and Portland, OR,
> so I can't look myself.   Since you've got the standard available you
> can answer perhaps?

Gee, Don, that's a good idea, I should have thought of it myself! BTW,
what I have is labelled "Second Informal Review Draft" and is dated July
30, 1992.  I don't know how it differs from the final standard, if at all.
I orginally got it from someone on this list, I forget who. Anyone
want a copy, I'll happily email it to you, or throw it on a website:
it's 1.6M of ASCII, ready for the line printer ;-) (273K gzipped.)

That said, ALTER is in fact described, and the ADD COLUMN case agrees
with Tom's expectations, rather than mine, see General Rule 2, below:

    11.11  <add column definition>

    Function

    Add a column to a table.

    Format

    <add column definition> ::=
         ADD [ COLUMN ] <column definition>


    Syntax Rules

       None.

    Access Rules


       None.

    General Rules

    1) The column defined by the <column definition> is added to T.

    2) Let C be the column added to T. Every value in C is the default
       value for C.

       Note: The default value of a column is defined in Subclause 11.5,
       "<default clause>".

       Note: The addition of a column to a table has no effect on any
       existing <query expression> included in a view descriptor or
       <search condition> included in constraint descriptor because
       any implicit <column reference>s in these clauses are replaced
       by explicit <column reference>s when the clause is originally
       evaluated. See the Syntax Rules of Subclause 7.10, "<query ex-
       pression>".


For what it's worth, here's what it says about DROP COLUMN. Note that
the question of what to do about references to columns: standard says,
throw and error, unless the DBA really means it, with the CASCADE option,
except for VIEWs, which get dropped, unless the DBA is careful to say
RESTRICT.


    11.15  <drop column definition>

    Function

    Destroy a column.

    Format

    <drop column definition> ::=
         DROP [ COLUMN ] <column name> <drop behavior>

    <drop behavior> ::= CASCADE | RESTRICT

    Syntax Rules

    1) Let T be the table identified by the <table name> in the con-
       taining <alter table statement> and let TN be the name of T.

    2) Let C be the column identified by the <column name> CN.

    3) C shall be a column of T and C shall not be the only column of
       T.

    4) If RESTRICT is specified, then C shall not be referenced in
       the <query expression> of any view descriptor or in the <search
       condition> of any constraint descriptor other than a table con-
       straint descriptor that contains references to no other column
       and that is included in the table descriptor of T.

       Note: A <drop column definition> that does not specify CASCADE
       will fail if there are any references to that column resulting
       from the use of CORRESPONDING, NATURAL, SELECT * (except where
       contained in an exists predicate>), or REFERENCES without a
       <reference column list> in its <referenced table and columns>.

       Note: If CASCADE is specified, then any such dependent object
       will be dropped by the execution of the <revoke statement> spec-
       ified in the General Rules of this Subclause.

    Access Rules

       None.

    General Rules

    1) Let A be the current <authorization identifier>. The following
       <revoke statement> is effectively executed with a current <au-
       thorization identifier> of "_SYSTEM" and without further Access
       Rule checking:

         REVOKE INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN
         FROM A CASCADE

    2) Let VN be the name of any view that contains a reference to
       column C of table T. The following <drop view statement> is
       effectively executed with a current <authorization identifier>
       of "_SYSTEM" and without further Access Rule checking:

         DROP VIEW VN CASCADE

    3) If the column is not based on a domain, then its data type de-
       scriptor is destroyed.

    4) The data associated with C is destroyed and the descriptor of C
       is removed from the descriptor of T.

    5) The identified column and its descriptor are destroyed.

    6) The degree of T is reduced by 1. The ordinal position of all
       columns having an ordinal position greater than the ordinal
       position of C is reduced by 1.

Re: Happy column adding and dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T20:42:54Z

At 02:22 PM 1/25/00 -0600, Ross J. Reedstrom wrote:

>Gee, Don, that's a good idea, I should have thought of it myself! BTW,
>what I have is labelled "Second Informal Review Draft" and is dated July
>30, 1992.  I don't know how it differs from the final standard, if at all.

>From the date, it should be quite close.  From my experience, by this
point in the process changes are mostly editorial, not substantial
in the sense of adding or dropping columns...I mean features.  :)

>I orginally got it from someone on this list, I forget who. Anyone
>want a copy, I'll happily email it to you, or throw it on a website:
>it's 1.6M of ASCII, ready for the line printer ;-) (273K gzipped.)

I'd love a copy and have a DSL line, so the size doesn't bother me.

As far as putting it on a web site, Ansi drafts are generally available
for public comment so it might be OK to do so.  Is there a copyright
statement attached?

>That said, ALTER is in fact described, and the ADD COLUMN case agrees
>with Tom's expectations, rather than mine, see General Rule 2, below:

So it goes :)

>For what it's worth, here's what it says about DROP COLUMN. Note that
>the question of what to do about references to columns: standard says,
>throw and error, unless the DBA really means it, with the CASCADE option,
>except for VIEWs, which get dropped, unless the DBA is careful to say
>RESTRICT.

>       Note: A <drop column definition> that does not specify CASCADE
>       will fail if there are any references to that column resulting
>       from the use of CORRESPONDING, NATURAL, SELECT * (except where
>       contained in an exists predicate>), or REFERENCES without a
>       <reference column list> in its <referenced table and columns>.

This is interesting, because it implies that select * can or should
be expanded when a view is created, rather than when a view is queried.
Except for "exists" where the "*" target list can be thought of as being
a dummy as sorts.

Also, the "natural" refers to a "natural join".  A "natural join", you'll
recall, is one where you supply two table names and the tables are joined
by those columns they share in common.   "drop column" is supposed to
catch such cases.

Ditto the form "references tablename" where the foreign key is determined
by inspecting "tablename".

Cool!  They don't expect much, do they? :)

All the above with the caveat "if I'm reading this correctly".

>       Note: If CASCADE is specified, then any such dependent object
>       will be dropped by the execution of the <revoke statement> spec-
>       ified in the General Rules of this Subclause.

...

>    1) Let A be the current <authorization identifier>. The following
>       <revoke statement> is effectively executed with a current <au-
>       thorization identifier> of "_SYSTEM" and without further Access
>       Rule checking:
>
>         REVOKE INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN
>         FROM A CASCADE

I don't know exactly what this sez offhand...

>
>    2) Let VN be the name of any view that contains a reference to
>       column C of table T. The following <drop view statement> is
>       effectively executed with a current <authorization identifier>
>       of "_SYSTEM" and without further Access Rule checking:
>
>         DROP VIEW VN CASCADE

This is clear enough.

...


>    6) The degree of T is reduced by 1. The ordinal position of all
>       columns having an ordinal position greater than the ordinal
>       position of C is reduced by 1.

Does the standard give a way to access the ordinal position of 
columns?

My guess is that any preliminary implementation of drop column won't
be implementing everything spelled out in the standard!




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding and dropping

Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-01-25T21:48:39Z

On Tue, Jan 25, 2000 at 12:42:54PM -0800, Don Baccus wrote:
> At 02:22 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
> 
> I'd love a copy and have a DSL line, so the size doesn't bother me.
> 
> As far as putting it on a web site, Ansi drafts are generally available
> for public comment so it might be OK to do so.  Is there a copyright
> statement attached?

Hmm, the string 'copyright' (cas insensitive search) does not appear in 
the text at all. This draft was done at DEC, according to the title page.

So, grab it from 

       http://wallace.ece.rice.edu/sql1992.txt 
or
       http://wallace.ece.rice.edu/sql1992.txt.gz 

(hmm, Netscape just decompressed that, and showed me the text!)

Perhaps this could go on postgresql.org, in the developers info section,
if, as I believe, we may act in the good-faith belief that the drafts of
the ANSI standards are actually in the public domain. Hmm, I just realized,
this is actually a draft of the ISO standard. s/ANSI/ISO/

> 
> >       Note: If CASCADE is specified, then any such dependent object
> >       will be dropped by the execution of the <revoke statement> spec-
> >       ified in the General Rules of this Subclause.
> 
> ...
> 
> >    1) Let A be the current <authorization identifier>. The following
> >       <revoke statement> is effectively executed with a current <au-
> >       thorization identifier> of "_SYSTEM" and without further Access
> >       Rule checking:
> >
> >         REVOKE INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN
> >         FROM A CASCADE
> 
> I don't know exactly what this sez offhand...

I think it says: make these items disappear, for the current user, by
revoking all privileges for this column (CN :== column name) on this table.

Err, are privileges supposed to be granular to the _column_? The syntax for
GRANT and REVOKE don't imply it, but some of the Rules in each do.

> 
> 
> >    6) The degree of T is reduced by 1. The ordinal position of all
> >       columns having an ordinal position greater than the ordinal
> >       position of C is reduced by 1.
> 
> Does the standard give a way to access the ordinal position of 
> columns?

You mean go from <column name> to <ordinal>? I don't think so, but I
haven't grovelled through the standard enough... O.K., now I have. It's
in the defined system tables, in particular the COLUMNS table, which I
won't duplicate here, since it runs to more than a printed page! It's
in clause 21.3.10, page 580, and defines a table that includes fields

[...]
              TABLE_NAME         INFORMATION_SCHEMA.SQL_IDENTIFIER,
              COLUMN_NAME        INFORMATION_SCHEMA.SQL_IDENTIFIER,
              ORDINAL_POSITION   INFORMATION_SCHEMA.CARDINAL_NUMBER
                CONSTRAINT COLUMN_POSITION_NOT_NULL NOT NULL,
[...]

So, yes, there is a standard way to get from <table name> + <ordinal> to
<column name>, if that's what you meant (since getting at the _contents_
is trivial: SELECT <ordinal> from <table name> Gack, look what you've
done, I'm starting to type in <standard complient markup> ;-)

> 
> My guess is that any preliminary implementation of drop column won't
> be implementing everything spelled out in the standard!
> 

I wouldn't think so! But it might help to plan for future implementation,
or at least be sure not to implement something that makes it harder to
implement the rest in the future.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: Happy column adding and dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T21:57:15Z

At 03:48 PM 1/25/00 -0600, Ross J. Reedstrom wrote:

>       http://wallace.ece.rice.edu/sql1992.txt.gz 

Thanks!  Now I have my own copy...
...

>> Does the standard give a way to access the ordinal position of 
>> columns?
>
>You mean go from <column name> to <ordinal>? I don't think so, but I
>haven't grovelled through the standard enough... O.K., now I have. It's
>in the defined system tables, in particular the COLUMNS table, which I
>won't duplicate here, since it runs to more than a printed page! It's
>in clause 21.3.10, page 580, and defines a table that includes fields
>
>[...]
>              TABLE_NAME         INFORMATION_SCHEMA.SQL_IDENTIFIER,
>              COLUMN_NAME        INFORMATION_SCHEMA.SQL_IDENTIFIER,
>              ORDINAL_POSITION   INFORMATION_SCHEMA.CARDINAL_NUMBER
>                CONSTRAINT COLUMN_POSITION_NOT_NULL NOT NULL,
>[...]
>
>So, yes, there is a standard way to get from <table name> + <ordinal> to
><column name>, if that's what you meant (since getting at the _contents_
>is trivial: SELECT <ordinal> from <table name> Gack, look what you've
>done, I'm starting to type in <standard complient markup> ;-)

OK, I asked because someone earlier said that Postgres should probably
eventually provide SQL92 standard ways to get at table information.

Interesting...




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-25T22:38:22Z

> It occurs to me that in at least some of the places where attribute
> numbers are currently used, we ought to use *neither* logical nor
> physical column position, but rather a permanent unique ID --- the
> attribute tuple's OID would work, if it's assigned soon enough to be
> used for constraints given in CREATE TABLE.  (Otherwise we could assign
> "column serial numbers" that count from 1 for each relation, but are
> never changed or recycled within the relation.)
> 
> In particular, if parsetrees for stored rules and constraints worked
> that way, renumbering attributes that follow the added/dropped column
> would become a lot less painful.

I am going to object to any use of invisible columns just to get a nice
ALTER DROP COLUMN capability.  It doesn't seeem with the added code
complexity.  Our code is complex enough.  Why add more to it just for
one feature.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T23:01:36Z

Don Baccus <dhogaza@pacifier.com> writes:
>> In particular, if parsetrees for stored rules and constraints worked
>> that way, renumbering attributes that follow the added/dropped column
>> would become a lot less painful.

> Yes...I see what you're driving at.  Very interesting idea.  The stored
> rules and constraints would in this case would still refer to the remaining
> columns after a drop, right?

Right.  You'd still need to scan through all the rules/constraints to
look for references to a column-to-be-dropped (and then either drop that
rule/constraint or kick out an error, as appropriate).  But you wouldn't
have to *change* any surviving rules/constraints, because they'd still
be referring to the same permanent IDs of the remaining columns.

Also, inherited ADD COLUMN would become far easier, because it wouldn't
change the rules/constraints of child tables at all --- even though the
new column would change the logical numbering of child-table columns,
it wouldn't change their permanent IDs and thus we wouldn't have to
update rules/constraints.

If we were willing to hardwire the assumption that DROP COLUMN never
physically drops a column, but only hides it and adjusts logical column
numbers, then the physical column numbers could serve as permanent IDs;
so we'd only need two numbers not three.  This might be good, or not.

			regards, tom lane

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-25T23:10:22Z

At 06:01 PM 1/25/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>>> In particular, if parsetrees for stored rules and constraints worked
>>> that way, renumbering attributes that follow the added/dropped column
>>> would become a lot less painful.
>
>> Yes...I see what you're driving at.  Very interesting idea.  The stored
>> rules and constraints would in this case would still refer to the remaining
>> columns after a drop, right?
>
>Right.  You'd still need to scan through all the rules/constraints to
>look for references to a column-to-be-dropped (and then either drop that
>rule/constraint or kick out an error, as appropriate).  But you wouldn't
>have to *change* any surviving rules/constraints, because they'd still
>be referring to the same permanent IDs of the remaining columns.

Good, I understand then.

>Also, inherited ADD COLUMN would become far easier, because it wouldn't
>change the rules/constraints of child tables at all --- even though the
>new column would change the logical numbering of child-table columns,
>it wouldn't change their permanent IDs and thus we wouldn't have to
>update rules/constraints.

Ahhh...yes.  I haven't looked at the inheritance code, yet, but I see
what you're saying.  I think.  Do child-table columns follow parent-table
columns by some chance (in today's absolute column number scheme)?

>If we were willing to hardwire the assumption that DROP COLUMN never
>physically drops a column, but only hides it and adjusts logical column
>numbers, then the physical column numbers could serve as permanent IDs;
>so we'd only need two numbers not three.  This might be good, or not.

Yes.  But if I'm right about how child-table columns are numbered, 
wouldn't add column still cause a problem, i.e. you'd still have to
change their physical column number?

I'm probably misunderstanding here because I've not looked at the 
inheritance mechanism at all.  Maybe I'll do that for a little evening
entertainment.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-25T23:55:59Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am going to object to any use of invisible columns just to get a nice
> ALTER DROP COLUMN capability.  It doesn't seeem with the added code
> complexity.  Our code is complex enough.  Why add more to it just for
> one feature.

I'm not convinced about it either --- but at this point the discussion
is just a gedanken-experiment to see what problems would be solved
or created if we did something like this.  It could be that having
invisible columns (or more likely, separate logical and physical
column numbers) would solve enough nagging problems with ADD COLUMN
and DROP COLUMN and inherited tables that it'd be worth doing.
Or not.  But let's not shut off the discussion before we see where
it leads.

Following on to my comment about logical column numbers, physical
column positions, and permanent column IDs being conceptually
distinct: one thing that needs careful consideration is just how
we identify inherited columns in child tables as being the "same
column" as the original column in the parent.  Right now, because
ADD COLUMN doesn't guarantee to assign the same column number in
each child, the parser/planner handles this by looking for the
same column name in each child table, which it does on-the-fly
while setting up a "SELECT table*" operation.  That's pretty much
of a kluge in my opinion.  My guess is that we ought to use either
logical column number or permanent ID as the key that tells us which
child column is the inherited one.  To use permanent ID, we'd have
to ensure that permanent IDs are inheritable, which would complicate
assignment of permanent IDs considerably --- they'd probably have to
become OIDs, but not the same OIDs as are assigned to the pg_attribute
rows themselves.  Logical column number might work OK for this purpose
though.  It seems a little shaky to me intuitively, but I can't actually
think of a reason that it wouldn't work.

			regards, tom lane

Re: Happy column adding (was RE: [HACKERS] Happy columndropping)

Hannu Krosing <hannu@tm.ee> — 2000-01-26T00:25:13Z

Don Baccus wrote:
> 
> Ahhh...yes.  I haven't looked at the inheritance code, yet, but I see
> what you're saying.  I think.  Do child-table columns follow parent-table
> columns by some chance (in today's absolute column number scheme)?
> 
> >If we were willing to hardwire the assumption that DROP COLUMN never
> >physically drops a column, but only hides it and adjusts logical column
> >numbers, then the physical column numbers could serve as permanent IDs;
> >so we'd only need two numbers not three.  This might be good, or not.
> 
> Yes.  But if I'm right about how child-table columns are numbered,
> wouldn't add column still cause a problem, i.e. you'd still have to
> change their physical column number?

If we allow deleted column as a basic feature of postgres,
it could be like that 

base:    COL1 | COL2 | COL3 
child:   COL1 | COL2 | COL3 | COL4

after add column 5 to base table

base:    COL1 | COL2 | COL3 | del4 | COL5 
child:   COL1 | COL2 | COL3 | COL4 | COL5

after add column 6 to child

base:    COL1 | COL2 | COL3 | del4 | COL5 
child:   COL1 | COL2 | COL3 | COL4 | COL5 | COL6

after drop column 2 from base table

base:    COL1 | del2 | COL3 | del4 | COL5 
child:   COL1 | del2 | COL3 | COL4 | COL5 | COL6

dropping column from child table that is not a deleted column in 
parent is not allowed.

The delN columns are always NULLed on reading tuple and are written as proper 
null columns (taking up space only in NULL bitmask)

multiple inheritance is tricky and _requires_ unique column ids maybe oids
from pg_attribute to be doable.

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

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hannu Krosing <hannu@tm.ee> — 2000-01-26T00:31:34Z

Tom Lane wrote:
> 
> Don Baccus <dhogaza@pacifier.com> writes:
> > However, heap_getattr still won't see the default since it simply
> > checks to see of the attribute number falls off the end of the
> > tuple and then returns null.
> 
> Right.
> 
> > There's no provision for then pulling out the default value and
> > returning it instead.
> 
> Would it even be possible to do that?  I'm not sure that heap_getattr
> has any way to find the default.  It only has a TupleDesc, not a
> reference to the relation itself...

but could it not return a special value, say COLUMN_DEFAULT, that upper 
levels know to replace with the real default. Of course it get's funny if 
if the default is nextval('myseq')  or CURRENT_TIME.

another way to approach the whole problem is to have something like 
generation_id in each tuple that tells which version of TupleDesc applies 
to this tuple, but it seems to complicate things even further.

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

Re: Happy column adding (was RE: [HACKERS] Happy columndropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-26T00:38:04Z

At 02:25 AM 1/26/00 +0200, Hannu Krosing wrote:

>multiple inheritance is tricky and _requires_ unique column ids maybe oids
>from pg_attribute to be doable.

Having worked on a C++ compiler (one of the first, actually) I 
suspect that this might be one of the lesser problems associated
with implementing a multiple inheritance scheme :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding (was RE: [HACKERS] Happycolumndropping)

Hannu Krosing <hannu@tm.ee> — 2000-01-26T01:08:27Z

Don Baccus wrote:
> 
> At 02:25 AM 1/26/00 +0200, Hannu Krosing wrote:
> 
> >multiple inheritance is tricky and _requires_ unique column ids maybe oids
> >from pg_attribute to be doable.
> 
> Having worked on a C++ compiler (one of the first, actually) I
> suspect that this might be one of the lesser problems associated
> with implementing a multiple inheritance scheme :)
>

I was aiming at a more loose way of doing multiple inheritance, something like
is used in python - you don't check too many things at compile time and
dynamically 
lookup them when needed.

for this the most_basic_column_oid scheme might work.

having unique indexes that span multiple tables would of course be tricky too, 
as would triggers and rules and ...

table a (oidA1, oidA2)
table b (oidB1, oidB2)
table c (oidC1) inherits(a,b)
alter table a drop column oidA2
alter table b add column oidB3
-->
table a - (oidA1,delA2)
table b - (oidB1,oidB2,oidB3)
table c - (oidA1,delA2,oidB1,oidB2,oidC1,oidB3)

seems the only sensible way for multiple inheritance to work with ADD/DROP
COLUMN 
is going with column oids for storage.

Hidden columns only would work for single inheritance. 

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

Re: [HACKERS] Re: Happy column adding and dropping

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-01-26T03:03:17Z

> > As far as putting it on a web site, Ansi drafts are generally available
> > for public comment so it might be OK to do so.  Is there a copyright
> > statement attached?
> Hmm, the string 'copyright' (cas insensitive search) does not appear in
> the text at all. This draft was done at DEC, according to the title page.

Right. I picked it up from a late, great DEC ftp site somewhere, and
might have been the one to forward it to you.

afaik, the drafts are in the public domain, or have a copyright which
allows distribution. They make their money from the final version...

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Re: [HACKERS] Re: Happy column adding and dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-26T03:04:17Z

> > > As far as putting it on a web site, Ansi drafts are generally available
> > > for public comment so it might be OK to do so.  Is there a copyright
> > > statement attached?
> > Hmm, the string 'copyright' (cas insensitive search) does not appear in
> > the text at all. This draft was done at DEC, according to the title page.
> 
> Right. I picked it up from a late, great DEC ftp site somewhere, and
> might have been the one to forward it to you.
> 
> afaik, the drafts are in the public domain, or have a copyright which
> allows distribution. They make their money from the final version...

I have to admit, I was hoping for a nice PDF version with bookmarks.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Re: [HACKERS] Re: Happy column adding and dropping

Don Baccus <dhogaza@pacifier.com> — 2000-01-26T04:39:34Z

At 10:04 PM 1/25/00 -0500, Bruce Momjian wrote:

>I have to admit, I was hoping for a nice PDF version with bookmarks.

1992?  Not likely :)  I mean, not in its original form.

We live in a fast-changing world, don't we?



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: [HACKERS] Re: Happy column adding and dropping

Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-26T05:27:39Z

> At 10:04 PM 1/25/00 -0500, Bruce Momjian wrote:
> 
> >I have to admit, I was hoping for a nice PDF version with bookmarks.
> 
> 1992?  Not likely :)  I mean, not in its original form.
> 
> We live in a fast-changing world, don't we?
> 

I was usually a paper manuals guy, but with PDF and bookmarks so you can
jump to the section you want, I like PDF better.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

RE: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-26T05:32:16Z

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Don Baccus
> 
> At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
> >On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
> >
> >> Even default is not allowed in ADD COLUMN now. 
> >> There may be other reasons why they aren't allowed.
> >
> >It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> >there was a default declared gets silently ignored. If y'all allow ;) I
> >would like to fix that (have already started a bit) by perusing the code
> >in parse_func.c:transformCreateStmt and do the same for the alter table
> >add column part. Maybe and add/drop constraint will come out in 
> the end as
> >well.
> 
> However, heap_getattr still won't see the default since it simply
> checks to see of the attribute number falls off the end of the
> tuple and then returns null.
>

Sorry,the following question might be already answered but too
many postings for me.

Do we have to refer default value for already inserted rows ?
Doesn't 'default' have its meaning only when rows are about to be
inserted ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hannu Krosing <hannu@tm.ee> — 2000-01-26T08:45:42Z

Hiroshi Inoue wrote:
> 
> > However, heap_getattr still won't see the default since it simply
> > checks to see of the attribute number falls off the end of the
> > tuple and then returns null.
> >
> 
> Sorry,the following question might be already answered but too
> many postings for me.
> 
> Do we have to refer default value for already inserted rows ?
> Doesn't 'default' have its meaning only when rows are about to be
> inserted ?

I think the case was about adding a NOT NULL column and setting current NULL 
columns to DEFAULT seemed like a natural thing to do.

But, considering the fact that DEFAULT can be something reaaly complex, like
function that does a lot of things, it may be better to have the constraints
checked at the end of transaction, like

BEGIN;
ALTER TABLE T1 ADD COLUMN C1 TEXT NOT NULL;
UPDATE T1 SET C1='MYDEFAULTVALUE';
COMMIT;

only IIRC we have quite poor support for DDL statements inside transactions.

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

RE: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hiroshi Inoue <inoue@tpf.co.jp> — 2000-01-26T11:05:00Z

> -----Original Message-----
> From: hannu@hu.tm.ee [mailto:hannu@hu.tm.ee]On Behalf Of Hannu Krosing
> 
> Hiroshi Inoue wrote:
> > 
> > > However, heap_getattr still won't see the default since it simply
> > > checks to see of the attribute number falls off the end of the
> > > tuple and then returns null.
> > >
> > 
> > Sorry,the following question might be already answered but too
> > many postings for me.
> > 
> > Do we have to refer default value for already inserted rows ?
> > Doesn't 'default' have its meaning only when rows are about to be
> > inserted ?
>

Oh,I found it from Ross's posting.

    General Rules

    1) The column defined by the <column definition> is added to T.

    2) Let C be the column added to T. Every value in C is the default
       value for C.

       Note: The default value of a column is defined in Subclause 11.5,
       "<default clause>"

Is this reasonable ?
. 
> I think the case was about adding a NOT NULL column and setting 
> current NULL 
> columns to DEFAULT seemed like a natural thing to do.
>
> But, considering the fact that DEFAULT can be something reaaly 
> complex, like
> function that does a lot of things, it may be better to have the 
> constraints
> checked at the end of transaction, like
> 
> BEGIN;
> ALTER TABLE T1 ADD COLUMN C1 TEXT NOT NULL;

Isn't 'iNITIALLY DEFERRED' needed ?
ALTER TABLE T1 ADD COLUMN C1 TEXT NOT NULL INITIALLY DEFERRED;

> UPDATE T1 SET C1='MYDEFAULTVALUE';
> COMMIT;
>

It seems more reasonable than standard.
But is it worth breaking SQL standard ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Jose Soares <jose@sferacarta.com> — 2000-01-26T13:24:14Z


Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Don Baccus
> >
> > At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
> > >On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
> > >
> > >> Even default is not allowed in ADD COLUMN now.
> > >> There may be other reasons why they aren't allowed.
> > >
> > >It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> > >there was a default declared gets silently ignored. If y'all allow ;) I
> > >would like to fix that (have already started a bit) by perusing the code
> > >in parse_func.c:transformCreateStmt and do the same for the alter table
> > >add column part. Maybe and add/drop constraint will come out in
> > the end as
> > >well.
> >
> > However, heap_getattr still won't see the default since it simply
> > checks to see of the attribute number falls off the end of the
> > tuple and then returns null.
> >
>
> Sorry,the following question might be already answered but too
> many postings for me.
>
> Do we have to refer default value for already inserted rows ?
> Doesn't 'default' have its meaning only when rows are about to be
> inserted ?
>

Of course yes.
from  "A guide to SQL Standard" page 106:

"ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT DEFAULT -1

this statement adds a new fifth column called DISCOUNT to base table S. All
existing S rows are extended to include a value for new column; that value is
-1 in every such row...."

The problem is when we define a constraint for the column like:

ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT NOT NULL

In such case IMO this should be refused because it violates data integrity,
an less you define also a default value for the column as in:

ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT NOT NULL DEFAULT -1

José






>
> Regards.
>
> Hiroshi Inoue
> Inoue@tpf.co.jp
>
> ************

Re: Happy column adding (was RE: [HACKERS] Happy columndropping)

Jose Soares <jose@sferacarta.com> — 2000-01-26T13:39:19Z


Don Baccus wrote:

> At 12:29 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
> >On Tue, Jan 25, 2000 at 12:23:15PM -0500, Tom Lane wrote:
>
> >> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
> >> cause every row currently existing in the table to acquire x = 42,
> >> rather than x = NULL?  In fact that would *have* to happen to allow
> >> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.
>
> >Actually, no I wouldn't expect it. That's mixing DDL and DML in one
> >statement.  I expect the ALTER command to be pure DDL, and the UPDATE
> >to be pure DML.
>
> Hmmm...interesting...is alter table in the standard?

Yes, of course.
...
         <alter table statement> ::=
              ALTER TABLE <table name> <alter table action>

         <alter table action> ::=
                <add column definition>
              | <alter column definition>
              | <drop column definition>
              | <add table constraint definition>
              | <drop table constraint definition>
...

> Again, my copy
> of Date's SQL 92 primer is somewhere 'wteen Boston, MA and Portland, OR,
> so I can't look myself.   Since you've got the standard available you
> can answer perhaps?

>
> >Ouch, reading standards always makes my brain hurt. Especially how you
> >have to read them upside down. Turns out SELECT INTO is in the standard,
> >but not the way we implement it.
>
> Scary!!! :) :)
>
> - Don Baccus, Portland OR <dhogaza@pacifier.com>
>   Nature photos, on-line guides, Pacific Northwest
>   Rare Bird Alert Service and other goodies at
>   http://donb.photo.net.
>
> ************

José

Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Don Baccus <dhogaza@pacifier.com> — 2000-01-26T18:07:50Z

At 10:45 AM 1/26/00 +0200, Hannu Krosing wrote:

>> Do we have to refer default value for already inserted rows ?
>> Doesn't 'default' have its meaning only when rows are about to be
>> inserted ?
>
>I think the case was about adding a NOT NULL column and setting current NULL 
>columns to DEFAULT seemed like a natural thing to do.

etc...

It depends on whether we want to be SQL92 compliant.  The SQL92 standard
seems to make it clear that "add column ... default" is supposed to 
set the column in ALL the rows in the table to that value.

I think it's actually much more useful this way.  If you set a default
value, it is normal to write application code that depends on its 
existence.  If you're going to write your application to work with
the column set to NULL, then you probably don't need the default
anyway.

And...if you don't want to pay the penalty of having to set default
values for all the rows when you add a column with a default value,
you can always add the column without the default value and use a
trigger to set new rows to a default value.  This would give the
functionality you want, no?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Re: Happy column adding

Peter Eisentraut <peter_e@gmx.net> — 2000-01-26T18:35:28Z

On 2000-01-25, Tom Lane mentioned:

> > IIRC,there were some reason that default for new column had been rejected.
> 
> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
> cause every row currently existing in the table to acquire x = 42,

Sure. But whatever happened to
pg_exec_query_dest("update <tablename> set <newcolname> = <default>")

This is what users would have to execute anyway, right?

Look at this:

=> create table test1 (a text);
=> insert into test1 values ('blah');
=> insert into test1 values ('blah');
=> insert into test1 values ('blah');
=> alter table test1 add column b serial;
=> select * from test1;
  a   | b
------+---
 blah | 1
 blah | 2
 blah | 3
(3 rows)

Not good?

> rather than x = NULL?  In fact that would *have* to happen to allow
> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.

Or how about (continuing the above):

=> alter table test1 add column c int not null;
ERROR:  ALTER TABLE: adding NOT NULL attribute to non-empty table requires
a non-NULL default value       

> The only way to make that happen is for ADD COLUMN to switch over to
> an implementation that rewrites all the tuples.  Which I think is the
> right way to go ... but per this discussion, it's not a trivial fix.

So what's the above doing? Seriously, is there silently creeping heap
corruption I'm not seeing?

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden