pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedChanging Column Order (Was Re: MySQL vs PostgreSQL.)
12+ messages / 10 participants
[nested] [flat]
* Changing Column Order (Was Re: MySQL vs PostgreSQL.)
@ 2002-10-12 08:37 Gavin Sherry <swm@linuxworld.com.au>
2002-10-12 09:43 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Antti Haapala <antti.haapala@iki.fi>
2002-10-12 09:54 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Jeff Davis <list-pgsql-hackers@empires.org>
2002-10-12 10:50 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
0 siblings, 4 replies; 12+ messages in thread
From: Gavin Sherry @ 2002-10-12 08:37 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-hackers
On 12 Oct 2002, Hannu Krosing wrote:
> Alvaro Herrera kirjutas L, 12.10.2002 kell 04:16:
> > On Fri, Oct 11, 2002 at 07:08:18PM -0700, Jeff Davis wrote:
> >
> > > And it really is a minor matter of convenience. I end up dropping and
> > > recreating all my tables a lot in the early stages of development, which is
> > > mildly annoying. Certainly not as bad, I suppose, as if you're led to believe
> > > that a feature does something safely, and it kills all your data.
> >
> > Now that ALTER TABLE DROP COLUMN is implemented, there probably isn't
> > any more the need to do such frequent drop/create of tables.
>
> Did attlognum's (for changing column order) get implemented for 7.2 ?
I cannot think of any reason why changing column order should be
implemented in Postgres. Seems like a waste of time/more code bloat for
something which is strictly asthetic.
Regardless, I do have collegues/clients who ask when such a feature will
be implemented. Why is this useful?
Gavin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
@ 2002-10-12 09:43 ` Antti Haapala <antti.haapala@iki.fi>
2002-10-12 15:45 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruno Wolff III <bruno@wolff.to>
3 siblings, 1 reply; 12+ messages in thread
From: Antti Haapala @ 2002-10-12 09:43 UTC (permalink / raw)
To: Gavin Sherry <swm@linuxworld.com.au>; +Cc: Hannu Krosing <hannu@tm.ee>; pgsql-hackers
> I cannot think of any reason why changing column order should be
> implemented in Postgres. Seems like a waste of time/more code bloat for
> something which is strictly asthetic.
What about copy? AFAIK, copy doesn't allow column names being specified,
so it's not purely aesthetic...
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-12 09:43 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Antti Haapala <antti.haapala@iki.fi>
@ 2002-10-12 15:45 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 12+ messages in thread
From: Bruno Wolff III @ 2002-10-12 15:45 UTC (permalink / raw)
To: Antti Haapala <antti.haapala@iki.fi>; +Cc: Gavin Sherry <swm@linuxworld.com.au>; Hannu Krosing <hannu@tm.ee>; pgsql-hackers
On Sat, Oct 12, 2002 at 12:43:37 +0300,
Antti Haapala <antti.haapala@iki.fi> wrote:
>
> > I cannot think of any reason why changing column order should be
> > implemented in Postgres. Seems like a waste of time/more code bloat for
> > something which is strictly asthetic.
>
> What about copy? AFAIK, copy doesn't allow column names being specified,
> so it's not purely aesthetic...
The SQL COPY command does (at least in 7.3). The \copy psql command
doesn't seem to allow this though.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
@ 2002-10-12 09:54 ` Jeff Davis <list-pgsql-hackers@empires.org>
3 siblings, 0 replies; 12+ messages in thread
From: Jeff Davis @ 2002-10-12 09:54 UTC (permalink / raw)
To: Gavin Sherry <swm@linuxworld.com.au>; +Cc: pgsql-hackers
> >
> > Did attlognum's (for changing column order) get implemented for 7.2 ?
>
> I cannot think of any reason why changing column order should be
> implemented in Postgres. Seems like a waste of time/more code bloat for
> something which is strictly asthetic.
>
> Regardless, I do have collegues/clients who ask when such a feature will
> be implemented. Why is this useful?
>
I think even "asthetic" might go too far. It seems mostly irrelevent except
for people who are obsessive compulsive and operate in interactive psql a
lot. It's marginally simpler to get the columns ordered the way you want so
that you can just do "SELECT * ..." rather than "SELECT att0,att1,... ..." at
the interactive psql prompt, and still get the columns in your favorite
order.
As far as I can tell, the order the attributes are returned makes no
difference in a client application, unless you're referencing attributes by
number. All applications that I've made or seen all use the name instead, and
I've never heard otherwise, or heard any advantage to using numbers to
reference columns.
When someone asks, ask them "why?". I'd be interested to know if they have
some other reason. I would think that if they absolutely wanted to fine-tune
the order of columns they'd use a view (seems a little easier than
continually changing order around by individual SQL statements).
Regards,
Jeff
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
@ 2002-10-12 10:50 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
3 siblings, 0 replies; 12+ messages in thread
From: Shridhar Daithankar @ 2002-10-12 10:50 UTC (permalink / raw)
To: pgsql-hackers
On 12 Oct 2002 at 2:54, Jeff Davis wrote:
> As far as I can tell, the order the attributes are returned makes no
> difference in a client application, unless you're referencing attributes by
> number. All applications that I've made or seen all use the name instead, and
> I've never heard otherwise, or heard any advantage to using numbers to
> reference columns.
Even in that case you can obtain field number for a given name and vise versa..
> When someone asks, ask them "why?". I'd be interested to know if they have
> some other reason. I would think that if they absolutely wanted to fine-tune
> the order of columns they'd use a view (seems a little easier than
> continually changing order around by individual SQL statements).
Sounds fine but what is about that "continually changing"? A view needs a
change only if it alters fields selected/tables to select from/selection
criteria. Field order does not figure in there..
Bye
Shridhar
--
QOTD: "A child of 5 could understand this! Fetch me a child of 5."
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
@ 2002-10-14 13:39 ` Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 1 reply; 12+ messages in thread
From: Alessio Bragadini @ 2002-10-14 13:39 UTC (permalink / raw)
To: pgsql-hackers
On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
> I cannot think of any reason why changing column order should be
> implemented in Postgres. Seems like a waste of time/more code bloat for
> something which is strictly asthetic.
>
> Regardless, I do have collegues/clients who ask when such a feature will
> be implemented. Why is this useful?
Has column ordering any effect on the physical tuple disposition? I've
heard discussions about keeping fixed-size fields at the beginning of
the tuple and similar.
Sorry for the lame question. :-)
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-22-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
@ 2002-10-14 15:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-14 15:15 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alvaro Herrera <alvherre@dcc.uchile.cl>
2002-10-15 14:37 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 2 replies; 12+ messages in thread
From: Bruce Momjian @ 2002-10-14 15:04 UTC (permalink / raw)
To: Alessio Bragadini <alessio@albourne.com>; +Cc: pgsql-hackers
Alessio Bragadini wrote:
> On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
>
> > I cannot think of any reason why changing column order should be
> > implemented in Postgres. Seems like a waste of time/more code bloat for
> > something which is strictly asthetic.
> >
> > Regardless, I do have collegues/clients who ask when such a feature will
> > be implemented. Why is this useful?
>
> Has column ordering any effect on the physical tuple disposition? I've
> heard discussions about keeping fixed-size fields at the beginning of
> the tuple and similar.
>
> Sorry for the lame question. :-)
Yes, column ordering matches physical column ordering in the file, and
yes, there is a small penalty for accessing any columns after the first
variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
length column, but with TOAST (large offline storage) it became variable
length too. I don't think there is much of a performance hit, though.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-10-14 15:15 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
1 sibling, 0 replies; 12+ messages in thread
From: Alvaro Herrera @ 2002-10-14 15:15 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Alessio Bragadini <alessio@albourne.com>; pgsql-hackers
On Mon, Oct 14, 2002 at 11:04:07AM -0400, Bruce Momjian wrote:
> Alessio Bragadini wrote:
> > On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
> >
> > > I cannot think of any reason why changing column order should be
> > > implemented in Postgres. Seems like a waste of time/more code bloat for
> > > something which is strictly asthetic.
> >
> > Has column ordering any effect on the physical tuple disposition? I've
> > heard discussions about keeping fixed-size fields at the beginning of
> > the tuple and similar.
>
> Yes, column ordering matches physical column ordering in the file, and
> yes, there is a small penalty for accessing any columns after the first
> variable-length column (pg_type.typlen < 0).
And note that if column ordering was to be implemented through the use
of attlognum or something similar, the physical ordering would not be
affected. The only way to physically reoder the columns would be to
completely rebuild the table.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Aprende a avergonzarte mas ante ti que ante los demas" (Democrito)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-10-15 14:37 ` Jan Wieck <JanWieck@Yahoo.com>
2002-10-16 01:18 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Jan Wieck @ 2002-10-15 14:37 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Alessio Bragadini <alessio@albourne.com>; pgsql-hackers
Bruce Momjian wrote:
>
> Alessio Bragadini wrote:
> > On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
> >
> > > I cannot think of any reason why changing column order should be
> > > implemented in Postgres. Seems like a waste of time/more code bloat for
> > > something which is strictly asthetic.
> > >
> > > Regardless, I do have collegues/clients who ask when such a feature will
> > > be implemented. Why is this useful?
> >
> > Has column ordering any effect on the physical tuple disposition? I've
> > heard discussions about keeping fixed-size fields at the beginning of
> > the tuple and similar.
> >
> > Sorry for the lame question. :-)
>
> Yes, column ordering matches physical column ordering in the file, and
> yes, there is a small penalty for accessing any columns after the first
> variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
> length column, but with TOAST (large offline storage) it became variable
> length too. I don't think there is much of a performance hit, though.
When was char() fixed size? We had fixed size things like char, char2,
char4 ... char16. But char() is internally bpchar() and has allways been
variable-length.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-15 14:37 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Jan Wieck <JanWieck@Yahoo.com>
@ 2002-10-16 01:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-16 03:15 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 2002-10-16 01:18 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Alessio Bragadini <alessio@albourne.com>; pgsql-hackers
Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Alessio Bragadini wrote:
> > > On Sat, 2002-10-12 at 11:37, Gavin Sherry wrote:
> > >
> > > > I cannot think of any reason why changing column order should be
> > > > implemented in Postgres. Seems like a waste of time/more code bloat for
> > > > something which is strictly asthetic.
> > > >
> > > > Regardless, I do have collegues/clients who ask when such a feature will
> > > > be implemented. Why is this useful?
> > >
> > > Has column ordering any effect on the physical tuple disposition? I've
> > > heard discussions about keeping fixed-size fields at the beginning of
> > > the tuple and similar.
> > >
> > > Sorry for the lame question. :-)
> >
> > Yes, column ordering matches physical column ordering in the file, and
> > yes, there is a small penalty for accessing any columns after the first
> > variable-length column (pg_type.typlen < 0). CHAR() used to be a fixed
> > length column, but with TOAST (large offline storage) it became variable
> > length too. I don't think there is much of a performance hit, though.
>
> When was char() fixed size? We had fixed size things like char, char2,
> char4 ... char16. But char() is internally bpchar() and has allways been
> variable-length.
char() was fixed size only in that you could cache the column offsets
for char() becuase it was always the same width on disk before TOAST.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-15 14:37 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Jan Wieck <JanWieck@Yahoo.com>
2002-10-16 01:18 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-10-16 03:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-16 03:19 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2002-10-16 03:15 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Alessio Bragadini <alessio@albourne.com>; pgsql-hackers
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Jan Wieck wrote:
>> When was char() fixed size?
> char() was fixed size only in that you could cache the column offsets
> for char() becuase it was always the same width on disk before TOAST.
But that was already broken by MULTIBYTE.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.)
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-14 13:39 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-15 14:37 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Jan Wieck <JanWieck@Yahoo.com>
2002-10-16 01:18 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-16 03:15 ` Re: Changing Column Order (Was Re: MySQL vs PostgreSQL.) Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-10-16 03:19 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2002-10-16 03:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Alessio Bragadini <alessio@albourne.com>; pgsql-hackers
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Jan Wieck wrote:
> >> When was char() fixed size?
>
> > char() was fixed size only in that you could cache the column offsets
> > for char() becuase it was always the same width on disk before TOAST.
>
> But that was already broken by MULTIBYTE.
Yes, I think there was conditional code that had the optimization only
for non-multibyte servers. Of course, now multibyte is default.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2002-10-16 03:19 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-12 08:37 Changing Column Order (Was Re: MySQL vs PostgreSQL.) Gavin Sherry <swm@linuxworld.com.au>
2002-10-12 09:43 ` Antti Haapala <antti.haapala@iki.fi>
2002-10-12 15:45 ` Bruno Wolff III <bruno@wolff.to>
2002-10-12 09:54 ` Jeff Davis <list-pgsql-hackers@empires.org>
2002-10-12 10:50 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2002-10-14 13:39 ` Alessio Bragadini <alessio@albourne.com>
2002-10-14 15:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-14 15:15 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2002-10-15 14:37 ` Jan Wieck <JanWieck@Yahoo.com>
2002-10-16 01:18 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-10-16 03:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-16 03:19 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox