Обсуждение: Simple Column reordering
Column storage position is the subject of many long threads in recent times. Solutions proposed for this have been both fairly complex and long enough that nothing seems likely to happen for 8.3. If I'm wrong, then of course this proposal would be superceded. I propose that at CREATE TABLE time, the column ordering is re-ordered so that the table columns are packed more efficiently. This would be a physical re-ordering, so that SELECT * and COPY without explicit column definitions would differ from the original CREATE TABLE statement. This would be an optional feature, off by default, controlled by a USERSET GUCoptimize_column_order = off (default) | on When the full column ordering proposal is implemented, optimize_column_ordering would be set to default to on. The feature would be supported for at least one more release after this to allow bug analysis. The proposed ordering would be: 1. All fixed length columns, arranged so that alignment is efficient 2. All variable length columns All column ordering would stay as close as possible to original order No changes would be made apart from at CREATE TABLE time. The ordering would be repeatable, so that the order would not change on repeated dump/restore of a table with no changes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > > I propose that at CREATE TABLE time, the column ordering is re-ordered > so that the table columns are packed more efficiently. This would be a > physical re-ordering, so that SELECT * and COPY without explicit column > definitions would differ from the original CREATE TABLE statement. > > This would be an optional feature, off by default, controlled by a > USERSET GUC > optimize_column_order = off (default) | on > Umm, you want a GUC setting to enable standards-breaking behaviour and that will be obsolete when we do column ordering right, which is not likely to be more than one release away, and could even still happen in this coming release? I hope I haven't misunderstood. cheers andrew
Andrew Dunstan wrote: > Simon Riggs wrote: > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > so that the table columns are packed more efficiently. This would be a > > physical re-ordering, so that SELECT * and COPY without explicit column > > definitions would differ from the original CREATE TABLE statement. > > > > This would be an optional feature, off by default, controlled by a > > USERSET GUC > > optimize_column_order = off (default) | on > > Umm, you want a GUC setting to enable standards-breaking behaviour and > that will be obsolete when we do column ordering right, which is not > likely to be more than one release away, and could even still happen in > this coming release? Given that we already seem to have a patch implementing a complete solution, or part thereof, this would seem a rather shortsighted proposal. Why not develop the whole thing and be done with it? I don't understand the reluctance to implementing all of it. The most serious objection I've seen, from Andreas IIRC, is that it would make drivers' lives more difficult; but really, drivers have to cope with dropped columns today which is a pain, and ISTM this proposal (not this one here, but the three-column proposal) would make that a bit simpler. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Alvaro Herrera" <alvherre@commandprompt.com> writes: > Given that we already seem to have a patch implementing a complete > solution we do? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Thu, 2007-02-22 at 23:49 -0300, Alvaro Herrera wrote: > Andrew Dunstan wrote: > > Simon Riggs wrote: > > > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > > so that the table columns are packed more efficiently. This would be a > > > physical re-ordering, so that SELECT * and COPY without explicit column > > > definitions would differ from the original CREATE TABLE statement. > > > > > > This would be an optional feature, off by default, controlled by a > > > USERSET GUC > > > optimize_column_order = off (default) | on > > > > Umm, you want a GUC setting to enable standards-breaking behaviour and > > that will be obsolete when we do column ordering right, which is not > > likely to be more than one release away, and could even still happen in > > this coming release? > > Given that we already seem to have a patch implementing a complete > solution, or part thereof, this would seem a rather shortsighted > proposal. Why not develop the whole thing and be done with it? That would be my preference, but if it doesn't happen, I wanted to have a clear secondary proposal documented. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Thu, 2007-02-22 at 20:07 -0600, Andrew Dunstan wrote: > Simon Riggs wrote: > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > so that the table columns are packed more efficiently. This would be a > > physical re-ordering, so that SELECT * and COPY without explicit column > > definitions would differ from the original CREATE TABLE statement. > > > > This would be an optional feature, off by default, controlled by a > > USERSET GUC > > optimize_column_order = off (default) | on > > > > > Umm, you want a GUC setting to enable standards-breaking behaviour and > that will be obsolete when we do column ordering right, which is not > likely to be more than one release away, and could even still happen in > this coming release? If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, nor does CREATE TABLE enforce the ordering to be the same as it has specified, AFAICS. Please correct me and I will withdraw. Practical issues seem far stronger drivers than standards issues here, which is why the parameter would default=off. If the full implementation exists and works, I would welcome it. This proposal is really aimed at what we do if that doesn't occur; we must wait to see if it will. I see that many users would want to get something sooner rather than later. That isn't a commercial perspective, I see that as a PostgreSQL advocacy perspective. I also see that we are forcing change into the on-disk format of heaps in this release. If we defer this to another release then we would be effectively changing the on-disk format again in next release. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > If this is standards-breaking as you say, I would withdraw immediately. > I checked the SQL standard and could not see how this would do so. The > standard states SELECT * would return columns in order; it doesn't say > what that order should be, nor does CREATE TABLE enforce the ordering to > be the same as it has specified, AFAICS. Please correct me and I will > withdraw. Practical issues seem far stronger drivers than standards > issues here, which is why the parameter would default=off. I did not follow the entire thread. I just wanted to point out that IIRC MS SQL Server (and maybe also Sybase) do automatically optimize the internal order of how columns are stored to move fixed length (which also means non NULLable for these two servers) columns to the left. Maybe this will serve as a reference point (not necessarily for standards compliance of course). regards, Lukas
> I don't understand the reluctance to implementing all of it. > The most serious objection I've seen, from Andreas IIRC, is > that it would make drivers' lives more difficult; but really, > drivers have to cope with dropped columns today which is a Yes, I already said, that my objection is probably moot in face of drop column. > pain, and ISTM this proposal (not this one here, but the > three-column proposal) would make that a bit simpler. Do all of them support dropped columns correctly, yet ? Andreas
Hi Simon, On 2/23/07, Simon Riggs <simon@2ndquadrant.com> wrote: > If this is standards-breaking as you say, I would withdraw immediately. > I checked the SQL standard and could not see how this would do so. The > standard states SELECT * would return columns in order; it doesn't say > what that order should be, nor does CREATE TABLE enforce the ordering to > be the same as it has specified, AFAICS. Please correct me and I will > withdraw. Phil Currier seems to have a preliminary proposition so I think we should wait for his patch. AFAICS in what he did, the physical position is not tied to the column ordering and it's better IMHO. It doesn't seem very intrusive and it doesn't change the "visible" behavior of PostgreSQL. Personnaly, I really need my column ordering. When I create my table, I put the columns in a logical order and I need they stay as I created them (for \d for example - on a large table I make semantic groups so I can find the field names more easily). And I think a lot of people do it as well so I'm not sure people would use a GUC which changes that much their habits. -- Guillaume
> > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say Imho the create table order is implied. What other order would they mean with "in order" ? > > what that order should be, nor does CREATE TABLE enforce the ordering > > to be the same as it has specified, AFAICS. Please correct me and I > > will withdraw. Practical issues seem far stronger drivers than > > standards issues here, which is why the parameter would default=off. > > I did not follow the entire thread. I just wanted to point > out that IIRC MS SQL Server (and maybe also Sybase) do > automatically optimize the internal order of how columns are > stored to move fixed length (which also means non NULLable > for these two servers) columns to the left. > Maybe this will serve as a reference point (not necessarily > for standards compliance of course). If you state that, it is imho also important to note that this is not externally visible. select * is not altered. I think we can have this based on Phil's patch. Simon would probably be willing to extend it to choose the best physical order during create table time. And the first step would be done. The next step could then be to "fix" drop column and add "add column before" eighter using a 3rd column or using the existing 2. Andreas
On Fri, 2007-02-23 at 09:46 +0100, Guillaume Smet wrote: > Hi Simon, > > On 2/23/07, Simon Riggs <simon@2ndquadrant.com> wrote: > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. Please correct me and I will > > withdraw. > > Phil Currier seems to have a preliminary proposition so I think we > should wait for his patch. That is exactly what I've said also, so it is good we agree. I had read that Phil had declined to work on it further; I hope he changes his mind on that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: >>> I propose that at CREATE TABLE time, the column ordering is re-ordered >>> so that the table columns are packed more efficiently. This would be a >>> physical re-ordering, so that SELECT * and COPY without explicit column >>> definitions would differ from the original CREATE TABLE statement. How about INSERTs without column names? Wouldn't that also cease to work? In particular, 'pg_dump --format=plain --inserts' would produce unusable output unless --attribute-inserts is also given, right? Yours, Laurenz Albe
On 2/23/07, Simon Riggs <simon@2ndquadrant.com> wrote: > I had read that Phil had declined to work on it further; I hope he > changes his mind on that. IIRC he just said he wasn't interested to work on the visible ordering part (as in MySQL) and I don't think it's a problem as even if it's related it's a different problem. -- Guillaume
Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs: > If this is standards-breaking as you say, I would withdraw immediately. > I checked the SQL standard and could not see how this would do so. The > standard states SELECT * would return columns in order; it doesn't say > what that order should be, b) Otherwise, the <select list> “*” is equivalent to a <value expression> sequence in which each <value expression> is a column reference that references a column of T and each column of T is referenced exactly once. The columns are referenced in the ascending sequence of their ordinal position within T. > nor does CREATE TABLE enforce the ordering to > be the same as it has specified, AFAICS. b) The column descriptors of every column of T, according to the Syntax Rules and General Rules of Subclause 11.4, “<column definition>”, applied to the <column definition>s contained in TEL, in the order in which they were specified. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Fri, 2007-02-23 at 11:25 +0100, Peter Eisentraut wrote: > Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs: > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, > > b) Otherwise, the <select list> “*” is equivalent to a <value expression> > sequence in which each <value expression> is a column reference that > references a column of T and each column of T is referenced exactly once. The > columns are referenced in the ascending sequence of their ordinal position > within T. Which begs the question: what is their ordinal position? If we change the ordinal position at CREATE TABLE time then the SELECT * would still work per standard. So the next point is the important one: > > nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. > > b) The column descriptors of every column of T, according to the Syntax Rules > and General Rules of Subclause 11.4, “<column definition>”, applied to the > <column definition>s contained in TEL, in the order in which they were > specified. I read both of these phrases prior to posting the original suggestion. My reading was that this was about constraints on columns, not the columns themselves, when that phrase was taken in context. I take it you think that reading was wrong? I'm not a lawyer, so I'll not argue too close to the edge. Suggestion withdrawn. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Am Freitag, 23. Februar 2007 12:25 schrieb Simon Riggs: > My reading was that this was about constraints on columns, not the > columns themselves, when that phrase was taken in context. I take it you > think that reading was wrong? I see nothing there that speaks of constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Simon Riggs wrote:
> On Fri, 2007-02-23 at 11:25 +0100, Peter Eisentraut wrote:
>
>> Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs:
>>
>>> If this is standards-breaking as you say, I would withdraw immediately.
>>> I checked the SQL standard and could not see how this would do so. The
>>> standard states SELECT * would return columns in order; it doesn't say
>>> what that order should be,
>>>
>> b) Otherwise, the <select list> “*” is equivalent to a <value expression>
>> sequence in which each <value expression> is a column reference that
>> references a column of T and each column of T is referenced exactly once. The
>> columns are referenced in the ascending sequence of their ordinal position
>> within T.
>>
>
> Which begs the question: what is their ordinal position? If we change
> the ordinal position at CREATE TABLE time then the SELECT * would still
> work per standard.
>
>
That's quite a stretch. Surely "their ordinal position" can't mean
"their ordinal position as arbitrarily determined at CREATE TABLE time
by the implementation".
I really don't think that we can accept under any circumstances a
situation where something as simple as this breaks:
create table foo (x text, y int); insert into foo values ('qwerty',1);
Physical storage optimization must not have any SQL level visibility or
consequences, IMNSHO, regardless of what we do about providing mutable
display order.
If you really want an interim solution, what about a builtin function
that would explicitly mutate the definition and table contents (if any)
along the lines you want? (assuming that's lots less work than just
doing the whole thing right to start with). Or even one which just
*displayed* the optimal order might be sufficient assistance to DBAs who
want to take advantage of this.
cheers
andrew
On Fri, 2007-02-23 at 07:52 -0500, Andrew Dunstan wrote: > I really don't think that we can accept under any circumstances a > situation where something ... breaks: Yes, I've accepted that, in response to Peter earlier today. > If you really want an interim solution, what about a builtin function > that would explicitly mutate the definition and table contents (if any) > along the lines you want? (assuming that's lots less work than just > doing the whole thing right to start with). Or even one which just > *displayed* the optimal order might be sufficient assistance to DBAs who > want to take advantage of this. I think the only interim solution now is to put functionality into PgAdmin et al to optimize the column order. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
> If this is standards-breaking as you say, I would withdraw immediately.
> I checked the SQL standard and could not see how this would do so. The
> standard states SELECT * would return columns in order; it doesn't say
> what that order should be, nor does CREATE TABLE enforce the ordering to
> be the same as it has specified, AFAICS.
SQL92 7.9 <query specification> defines the meaning of SELECT * as
b) Otherwise, the <select list> "*" is equivalent to a <value expression> sequence in which each
<valueexpression> is a <column reference> that references a column of T and each column of T is
referencedexactly once. The columns are ref- erenced in the ascending sequence of their ordinal position
within T.
11.3 <table definition> says
2) The degree of the table being created is initially set to 0; the General Rules of Subclause 11.4,
"<columndefinition>" specify the degree of the table being created during the definition of columns
inthat table.
and 11.4 <column definition> says
4) The degree of the table T being defined in the containing <table definition> or <temporary table
declaration>or altered by the containing <alter table statement> is increased by 1.
5) A column descriptor is created that describes the column being defined. .... The ordinal
positionincluded in the column descriptor is equal to the degree of T.
Now, I will grant you that it doesn't actually say anywhere that the
column definitions in CREATE TABLE must be processed left to right, but
if they meant this behavior to be implementation-dependent they would
have said so. Given the number of places in the spec in which semantics
are directly dependent on ordinal position, I cannot think that that is
intended --- for example, the behavior of <row comparison> becomes
completely undefined if column ordinal positions aren't fixed.
regards, tom lane
On Fri, Feb 23, 2007 at 02:09:55PM +0000, Simon Riggs wrote: > > If you really want an interim solution, what about a builtin function > > that would explicitly mutate the definition and table contents (if any) > > along the lines you want? (assuming that's lots less work than just > > doing the whole thing right to start with). Or even one which just > > *displayed* the optimal order might be sufficient assistance to DBAs who > > want to take advantage of this. > > I think the only interim solution now is to put functionality into > PgAdmin et al to optimize the column order. Well, if it comes to that it would be good to have pgAdmin et all driven by logic in the database, so that people using psql can benefit as well. Perhaps a function that is passed an existing table and re-creates it in optimal order (if it's empty...). Or at least spits out a CREATE TABLE statement for you that's in optimal order. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim C. Nasby wrote: > On Fri, Feb 23, 2007 at 02:09:55PM +0000, Simon Riggs wrote: > > > If you really want an interim solution, what about a builtin function > > > that would explicitly mutate the definition and table contents (if any) > > > along the lines you want? (assuming that's lots less work than just > > > doing the whole thing right to start with). Or even one which just > > > *displayed* the optimal order might be sufficient assistance to DBAs who > > > want to take advantage of this. > > > > I think the only interim solution now is to put functionality into > > PgAdmin et al to optimize the column order. > > Well, if it comes to that it would be good to have pgAdmin et all driven > by logic in the database, so that people using psql can benefit as well. > Perhaps a function that is passed an existing table and re-creates it in > optimal order (if it's empty...). Or at least spits out a CREATE TABLE > statement for you that's in optimal order. That's just working around the fact that the engine is not smart enough to do the right thing (semi-) automatically. We don't support that kind of operation, just like we don't support optimizer hints. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
First, it would absolutely be best if we just got the full blown patch into 8.3 and were done with it. I don't think anyone's arguing against that... it's a question of what we can do if that can't happen (and it does sound like the patch lost it's maintainer when the direction changed towards doing both physical and logical ordering code at the same time). On Fri, Feb 23, 2007 at 01:04:14PM -0300, Alvaro Herrera wrote: > Jim C. Nasby wrote: > > On Fri, Feb 23, 2007 at 02:09:55PM +0000, Simon Riggs wrote: > > > > If you really want an interim solution, what about a builtin function > > > > that would explicitly mutate the definition and table contents (if any) > > > > along the lines you want? (assuming that's lots less work than just > > > > doing the whole thing right to start with). Or even one which just > > > > *displayed* the optimal order might be sufficient assistance to DBAs who > > > > want to take advantage of this. > > > > > > I think the only interim solution now is to put functionality into > > > PgAdmin et al to optimize the column order. > > > > Well, if it comes to that it would be good to have pgAdmin et all driven > > by logic in the database, so that people using psql can benefit as well. > > Perhaps a function that is passed an existing table and re-creates it in > > optimal order (if it's empty...). Or at least spits out a CREATE TABLE > > statement for you that's in optimal order. > > That's just working around the fact that the engine is not smart enough > to do the right thing (semi-) automatically. We don't support that kind > of operation, just like we don't support optimizer hints. Except it's kind of the opposite... in this case, the database actually knows better about what fields have what alignment, etc. At least if users can get what the database says will be the best order they can use that should they choose to. I also don't see why we should restrict that information to users of pgAdmin or other 3rd party tools and not support those that just use psql. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Lukas Kahwe Smith wrote: > Simon Riggs wrote: > > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. Please correct me and I will > > withdraw. Practical issues seem far stronger drivers than standards > > issues here, which is why the parameter would default=off. > > I did not follow the entire thread. I just wanted to point out that IIRC > MS SQL Server (and maybe also Sybase) do automatically optimize the > internal order of how columns are stored to move fixed length (which > also means non NULLable for these two servers) columns to the left. > Maybe this will serve as a reference point (not necessarily for > standards compliance of course). And that optimized ordering shows up with SELECT *? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Lukas Kahwe Smith wrote: >> Simon Riggs wrote: >> >>> If this is standards-breaking as you say, I would withdraw immediately. >>> I checked the SQL standard and could not see how this would do so. The >>> standard states SELECT * would return columns in order; it doesn't say >>> what that order should be, nor does CREATE TABLE enforce the ordering to >>> be the same as it has specified, AFAICS. Please correct me and I will >>> withdraw. Practical issues seem far stronger drivers than standards >>> issues here, which is why the parameter would default=off. >> I did not follow the entire thread. I just wanted to point out that IIRC >> MS SQL Server (and maybe also Sybase) do automatically optimize the >> internal order of how columns are stored to move fixed length (which >> also means non NULLable for these two servers) columns to the left. >> Maybe this will serve as a reference point (not necessarily for >> standards compliance of course). > > And that optimized ordering shows up with SELECT *? no .. like i said its just the internal order .. I think I learned this piece of information from reading "SQL performance tuning" by Peter and Trudy .. dont have the book here right now to give a page number. regards, Lukas
I realized this proposal has been withdrawn, but the fact the proposal even illicited comments exploring it requires me to comment. Folks, how can we entertain ideas that would break SELECT * and no-column-list INSERTs for a small performance boost? If there was no other way to get the performance boost, and the features was rarely used, we might consider such a change, but neither is true in this case. My point is that this proposal is so far away from our acceptable criteria that I am worried about how people are analyzing proposals. --------------------------------------------------------------------------- Simon Riggs wrote: > Column storage position is the subject of many long threads in recent > times. Solutions proposed for this have been both fairly complex and > long enough that nothing seems likely to happen for 8.3. If I'm wrong, > then of course this proposal would be superceded. > > I propose that at CREATE TABLE time, the column ordering is re-ordered > so that the table columns are packed more efficiently. This would be a > physical re-ordering, so that SELECT * and COPY without explicit column > definitions would differ from the original CREATE TABLE statement. > > This would be an optional feature, off by default, controlled by a > USERSET GUC > optimize_column_order = off (default) | on > > When the full column ordering proposal is implemented, > optimize_column_ordering would be set to default to on. The feature > would be supported for at least one more release after this to allow bug > analysis. > > The proposed ordering would be: > 1. All fixed length columns, arranged so that alignment is efficient > 2. All variable length columns > > All column ordering would stay as close as possible to original order > > No changes would be made apart from at CREATE TABLE time. > > The ordering would be repeatable, so that the order would not change on > repeated dump/restore of a table with no changes. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > > Folks, how can we entertain ideas that would break SELECT * and > no-column-list INSERTs for a small performance boost? If there was no > other way to get the performance boost, and the features was rarely > used, we might consider such a change, but neither is true in this case. > > My point is that this proposal is so far away from our acceptable > criteria that I am worried about how people are analyzing proposals. > > In Simon's defense, I think we need to feel free to brainstorm a bit, and propose things that might seem odd. There are plenty of cool heads around to shoot down bad ideas, but we'll only make progress by cherry-picking the good ideas. If one out of ten of my ideas is useful I think I'm doing really well. cheers andrew
Andrew Dunstan wrote: > Bruce Momjian wrote: > > > > Folks, how can we entertain ideas that would break SELECT * and > > no-column-list INSERTs for a small performance boost? If there was no > > other way to get the performance boost, and the features was rarely > > used, we might consider such a change, but neither is true in this case. > > > > My point is that this proposal is so far away from our acceptable > > criteria that I am worried about how people are analyzing proposals. > > > > > > In Simon's defense, I think we need to feel free to brainstorm a bit, > and propose things that might seem odd. There are plenty of cool heads > around to shoot down bad ideas, but we'll only make progress by > cherry-picking the good ideas. If one out of ten of my ideas is useful I > think I'm doing really well. Agreed. I just wanted to make sure we still have the same criteria. The fact the proposal was explored just got me worried. I will go back to not worrying. ;-) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> In Simon's defense, I think we need to feel free to brainstorm a bit,
> and propose things that might seem odd. There are plenty of cool heads
> around to shoot down bad ideas, but we'll only make progress by
> cherry-picking the good ideas. If one out of ten of my ideas is useful I
> think I'm doing really well.
The easiest way to spot genius is to talk to the craziest person in the
room.
Joshua D. Drake
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > I realized this proposal has been withdrawn, but the fact the proposal > even illicited comments exploring it requires me to comment. > > Folks, how can we entertain ideas that would break SELECT * and > no-column-list INSERTs for a small performance boost? If there was no > other way to get the performance boost, and the features was rarely > used, we might consider such a change, but neither is true in this case. > > My point is that this proposal is so far away from our acceptable > criteria that I am worried about how people are analyzing proposals. When suggested, it wasn't clear to me that it did break anything, otherwise I wouldn't have written it up. I read Alvaro's post and wondered why that proposal had been overlooked, so I started a separate thread to ensure that the idea was discussed. That seems very similar to many of your own posts. In the morning light, holding a coffee, its obviously broken and it is clear that I misunderstood what was being proposed. Bottom line is I initiate lots of ideas, not all of which become projects, but many become TODO items. Sometimes I see things others do not, sometimes I make mistakes; sometimes it takes a while for my ideas to be understood and accepted. I don't hold mistakes against anyone, and we all make them. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > > > I realized this proposal has been withdrawn, but the fact the proposal > > even illicited comments exploring it requires me to comment. > > > > Folks, how can we entertain ideas that would break SELECT * and > > no-column-list INSERTs for a small performance boost? If there was no > > other way to get the performance boost, and the features was rarely > > used, we might consider such a change, but neither is true in this case. > > > > My point is that this proposal is so far away from our acceptable > > criteria that I am worried about how people are analyzing proposals. > > When suggested, it wasn't clear to me that it did break anything, > otherwise I wouldn't have written it up. I read Alvaro's post and You mentioned in your own original posting that it broke SELECT * and COPY. > wondered why that proposal had been overlooked, so I started a separate > thread to ensure that the idea was discussed. That seems very similar to > many of your own posts. True, but usually I don't see the breakage. What concerned me is you saw some of the breakage, but still went ahead with the proposal. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Mon, 2007-02-26 at 13:02 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > > > > > I realized this proposal has been withdrawn, but the fact the proposal > > > even illicited comments exploring it requires me to comment. > > > > > > Folks, how can we entertain ideas that would break SELECT * and > > > no-column-list INSERTs for a small performance boost? If there was no > > > other way to get the performance boost, and the features was rarely > > > used, we might consider such a change, but neither is true in this case. > > > > > > My point is that this proposal is so far away from our acceptable > > > criteria that I am worried about how people are analyzing proposals. > > > > When suggested, it wasn't clear to me that it did break anything, > > otherwise I wouldn't have written it up. I read Alvaro's post and > > You mentioned in your own original posting that it broke SELECT * and > COPY. I saw that there was an effect, not breakage; I didn't use that word. I specifically highlighted that there would be a difference because it was an area of possible contention. The order of the columns is *arbitrary* in relational theory; the ordering needs to match to allow DDL to match other SQL that presumes an ordering. Changing the order at CREATE TABLE time seemed acceptable and would be so in many cases, since most applications follow sensible guidelines about not using SELECT * etc. But SQL Standard breakage is not acceptable. My mistake was mis-reading the Standard, which regrettably is not the easiest manual to read, but no excuse. The functionality could still be usefully implemented in a client tool, which was where the discussion left. > > wondered why that proposal had been overlooked, so I started a separate > > thread to ensure that the idea was discussed. That seems very similar to > > many of your own posts. > > True, but usually I don't see the breakage. Sorry, I just meant you summarise ideas that others have made, not that your proposals are broken. > What concerned me is you > saw some of the breakage, but still went ahead with the proposal. I have never and will never propose something I know to be broken. That shouldn't need to be said, but I've had to say that more than once recently for some reason. Why would you even think that the author of PITR would harbour some hidden disrespect for server integrity, or somebody who overhauled the standards compliance documentation, with Troels, has no respect for standards? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Simon Riggs" <simon@2ndquadrant.com> writes:
> The order of the columns is *arbitrary* in relational theory;
SQL is very far from being relational theory...
regards, tom lane
Simon Riggs wrote: > > > wondered why that proposal had been overlooked, so I started a separate > > > thread to ensure that the idea was discussed. That seems very similar to > > > many of your own posts. > > > > True, but usually I don't see the breakage. > > Sorry, I just meant you summarise ideas that others have made, not that > your proposals are broken. My proposals are often broken. > > What concerned me is you > > saw some of the breakage, but still went ahead with the proposal. > > I have never and will never propose something I know to be broken. That > shouldn't need to be said, but I've had to say that more than once > recently for some reason. Why would you even think that the author of > PITR would harbour some hidden disrespect for server integrity, or > somebody who overhauled the standards compliance documentation, with > Troels, has no respect for standards? I was merely trying to focus us on making sure we stay on track. I know you share the same goals. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce, > True, but usually I don't see the breakage. What concerned me is you > saw some of the breakage, but still went ahead with the proposal. That's completely unfair, Bruce. This is a *discussion list*, and hackers are free to propose and discuss even far-out improbable ideas in the hopes that someone else on the list can make them work. -hackers is not a clearinghouse for already-perfected work; that's -patches. For my part, I continue to the interested in this proposal and would like to see some performance benchmarks on it. If there is enough performance gain, I think it would be possible to implement a "logical" order which was different from the "physical" order. Such a feature would also allow us to give the MySQLites the ability to re-order columns they keep asking for. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus wrote: > For my part, I continue to the interested in this proposal and would like to > see some performance benchmarks on it. If there is enough performance gain, > I think it would be possible to implement a "logical" order which was > different from the "physical" order. Such a feature would also allow us to > give the MySQLites the ability to re-order columns they keep asking for. > > That is indeed what has been proposed (see earlier discussion re Tom's 3 numbers idea). Simon's proposal would not have done that, however, but would simply have mutated the (single physical+logical) column order. Anyway, I think we're all (or mostly all) on the same page now. cheers andrew