Thread: Happy column dropping
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
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 v�g 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
> 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, Pennsylvania19026
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
[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, Pennsylvania19026
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
> [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, Pennsylvania19026
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
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 Serviceand other goodies at http://donb.photo.net.
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
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/moor less at Pop4 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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
> -----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
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 Serviceand other goodies at http://donb.photo.net.
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
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 Serviceand other goodies at http://donb.photo.net.
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
> 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
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
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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
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
> > 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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/moor less at Pop4 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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 discussionb) 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
> > 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, Pennsylvania19026
> 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, Pennsylvania19026
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
> > 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, Pennsylvania19026
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
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 Serviceand other goodies at http://donb.photo.net.
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
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 Serviceand other goodies at http://donb.photo.net.
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, Pennsylvania19026
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/moor less at Pop4 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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
> > 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, Pennsylvania19026
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
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
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.
>>>> 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
> >>>> 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, Pennsylvania19026
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,
Pennsylvania19026
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
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
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
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
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
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
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
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 Serviceand other goodies at http://donb.photo.net.
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
> -----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
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
"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
> -----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
"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
onesaren'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
[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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
> 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, Pennsylvania19026
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 > > ************
> "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, Pennsylvania19026
> -----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
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
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
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
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
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
> 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
> 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, Pennsylvania19026
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
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
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
> -----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
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
> -----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
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
* 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]
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
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
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
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. | =====================================================================
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
> -----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
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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
> 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, Pennsylvania19026
> 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, Pennsylvania19026
"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
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
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
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
<selecttarget 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
"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
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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 Serviceand other goodies at http://donb.photo.net.
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
viewdescriptor or <search condition> included in constraint descriptor because any implicit <column
reference>sin 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
thename 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
inthe <search condition> of any constraint descriptor other than a table con- straint descriptor that
containsreferences 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
columnresulting 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
<revokestatement> 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
witha 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
AccessRule 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
theordinal position of C is reduced by 1.
> 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
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
CONSTRAINTCOLUMN_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
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 Serviceand other goodies at http://donb.photo.net.
> 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, Pennsylvania19026
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
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 Serviceand other goodies at http://donb.photo.net.
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
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
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
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 Serviceand other goodies at http://donb.photo.net.
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
> > 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
> > > 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, Pennsylvania19026
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 Serviceand other goodies at http://donb.photo.net.
> -----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
> 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, Pennsylvania19026
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
> -----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
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 > > ************
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é
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 | 1blah | 2blah | 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
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 Serviceand other goodies at http://donb.photo.net.