subject:"Re\: \[HACKERS\] logical column ordering"

On 23.3.2015 19:52, Peter Geoghegan wrote:
 On Mon, Mar 23, 2015 at 11:50 AM, Tomas Vondra
 tomas.von...@2ndquadrant.com wrote:
 Not sure how that's supposed to improve the situation? Also, when you
 change the status to 'stalled', it only makes it more difficult to
 identify why it was stalled (was it waiting for author or a review?).

 What might be done is tracking time since last patch/review, but
 I really don't know how we're going to identify that considering
 the problems with identifying which messages are patches.
 
 
 Perhaps I explained myself poorly. I am proposing having a totally 
 automated/mechanical way of highlighting no actual change in status
 in the CF app. So I think we are in agreement here, or close enough.
 I was just talking about a somewhat arbitrary point at which patches
 are considered to have stalled within the CF app.

Oh, right. Yes, tracking time since the last status change like this
might be useful, although my experience is that many patches are stuck
at some status yet there was a long discussion on the list ...  Not sure
if that counts as 'stalled'.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-23 Thread Josh Berkus

On 03/23/2015 02:32 PM, Tomas Vondra wrote:
 Oh, right. Yes, tracking time since the last status change like this
 might be useful, although my experience is that many patches are stuck
 at some status yet there was a long discussion on the list ...  Not sure
 if that counts as 'stalled'.

Time since last email maybe.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 23.3.2015 18:30, Andres Freund wrote:

 I think it fairly can be marked as returned with feedback for
 now?

That will eventually be the end result, yes. If it's time to do that
now, or leave the patch in the CF and only bounce it at the end, I don't
know.


 ... which means that no useful feedback was received at all in
 this round for this patch. (There was lots of feedback, mind you,
 but as far as I can see it was all on the subject of how the patch
 is going to be summarily rejected unless user-visible controls are
 offered -- and you already know my opinion on that matter.)
 
 To me the actual blocker seems to be the implementation. Which
 doesn't look like it's going to be ready for 9.5; there seems to be
 loads of work left to do. It's hard to provide non flame-bait
 feedback if the patch isn't ready. I'm not sure what review you'd
 like to see at this stage?

The version I posted at the end of February is certainly incomplete (and
some of the regression tests fail), but it seemed reasonably complete to
get some feedback. That is not to say parts of the patch are probably
wrong / need rework.

 I think your approach of concentrating on the technical parts is
 sane, and I'd continue going that way.

I do work in that direction. OTOH I think it's useful to provide some
sort of minimum usable API so that people can actually use it without
messing with catalogs directly. It certainly won't have all the bells
and whistles, though.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 23.3.2015 22:53, Jeff Janes wrote:
 On Mon, Mar 23, 2015 at 11:52 AM, Tomas Vondra
 
 Sorry, the 23/2 one is the one I meant.  I got confused over which of
 the emails listed as having an attachment but no patch was the one that
 actually had a patch.  (If the commitfest app can't correctly deal with
 more than one attachment, it needs to at least give an indication that
 this condition may exist).
 
 But I am still getting a lot of errors during make check.
 
 60 of 153 tests failed
 
 Some of them look like maybe a change in the expected output file didn't
 get included in the patch, but at least one was a coredump.

Yes, there were two coredumps (as noted in the message with the patch).

Not sure of the other errors - it certainly is possible I forgot to
include something in the patch. Thanks for noticing this, will look into
that.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-23 Thread Jeff Janes

On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote:

 On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
  However, there's a difference between making a query silently given
  different results, and breaking it completely forcing the user to
  re-study how to write it.  I think the latter is better.  In that light
  we should just drop attnum as a column name, and use something else:
  maybe (attidnum, attlognum, attphysnum).  So all queries in the wild
  would be forced to be updated, but we would not silently change
  semantics instead.

 +1 for that approach.  Much better to break all of the third-party
 code out there definitively than to bet on which attribute people are
 going to want to use most commonly.

 I'm a little confused as to the status of this patch.  It's marked as
 Waiting on Author in the CommitFest application, and the last patch
 version was posted in December.


There was a patch here, which in the commit fest is hidden behind other
non-attachments in the same email:

Attachment (randomize.sql
http://www.postgresql.org/message-id/attachment/37076/randomize.sql)
at 2015-02-27
19:10:21
http://www.postgresql.org/message-id/54f0c11d.7000...@2ndquadrant.com/ from
Tomas Vondra tomas.vondra at 2ndquadrant.com

But that patch failed the majority of make check checks in my hands.  So
I also don't know what the status is.

Cheers,

Jeff

Re: [HACKERS] logical column ordering

2015-03-23 Thread Peter Geoghegan

On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote:
 I'm a little confused as to the status of this patch.  It's marked as
 Waiting on Author in the CommitFest application, and the last patch
 version was posted in December.  The fact that the new CommitFest
 application encourages people to blindly move things to the next CF
 instead of forcing patch authors to reopen the record when they update
 the patch is, IMHO, not good.  It's just going to lead to the CF
 application filling up with things that the authors aren't really
 working on.  We've got enough work to do with the patches that are
 actually under active development.

Maybe there should be a stalled patch status summary, that
highlights patches that have not had their status change in (say) 2
weeks. Although it wouldn't really be a status summary, since that
they're mutually exclusive with each other in the CF app (e.g. a patch
cannot be both Waiting on Author and Ready for Committer).


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-23 Thread Alvaro Herrera

Andres Freund wrote:
 On 2015-03-23 13:01:48 -0400, Robert Haas wrote:
  I'm a little confused as to the status of this patch.  It's marked as
  Waiting on Author in the CommitFest application, and the last patch
  version was posted in December.
 
 I think it fairly can be marked as returned with feedback for now?

... which means that no useful feedback was received at all in this
round for this patch.  (There was lots of feedback, mind you, but as far
as I can see it was all on the subject of how the patch is going to be
summarily rejected unless user-visible controls are offered -- and you
already know my opinion on that matter.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Hi,

On 23.3.2015 18:01, Robert Haas wrote:
 On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
 However, there's a difference between making a query silently given
 different results, and breaking it completely forcing the user to
 re-study how to write it.  I think the latter is better.  In that light
 we should just drop attnum as a column name, and use something else:
 maybe (attidnum, attlognum, attphysnum).  So all queries in the wild
 would be forced to be updated, but we would not silently change
 semantics instead.
 
 +1 for that approach.  Much better to break all of the third-party
 code out there definitively than to bet on which attribute people are
 going to want to use most commonly.
 
 I'm a little confused as to the status of this patch.  It's marked as
 Waiting on Author in the CommitFest application, and the last patch
 version was posted in December.  The fact that the new CommitFest
 application encourages people to blindly move things to the next CF
 instead of forcing patch authors to reopen the record when they update
 the patch is, IMHO, not good.  It's just going to lead to the CF
 application filling up with things that the authors aren't really
 working on.  We've got enough work to do with the patches that are
 actually under active development.

The last version of the patch was submitted on 24/2 by me. Not sure why
it's not listed in the CF app, but it's here:

http://www.postgresql.org/message-id/54ebb312.7090...@2ndquadrant.com

I'm working on a new version of the patch, based on the ideas that were
mentioned in this thread. I plan to post a new version within a few
days, hopefully.

Anyway, it's obvious this patch won't make it into 9.5 - it's a lot of
subtle changes on many places, so it's not suitable for the last
commitfest. But the feedback is welcome, of course.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Hi,

On 23.3.2015 18:07, Peter Geoghegan wrote:
 On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote:
 I'm a little confused as to the status of this patch.  It's marked as
 Waiting on Author in the CommitFest application, and the last patch
 version was posted in December.  The fact that the new CommitFest
 application encourages people to blindly move things to the next CF
 instead of forcing patch authors to reopen the record when they update
 the patch is, IMHO, not good.  It's just going to lead to the CF
 application filling up with things that the authors aren't really
 working on.  We've got enough work to do with the patches that are
 actually under active development.
 
 Maybe there should be a stalled patch status summary, that
 highlights patches that have not had their status change in (say) 2
 weeks. Although it wouldn't really be a status summary, since that
 they're mutually exclusive with each other in the CF app (e.g. a patch
 cannot be both Waiting on Author and Ready for Committer).

Not sure how that's supposed to improve the situation? Also, when you
change the status to 'stalled', it only makes it more difficult to
identify why it was stalled (was it waiting for author or a review?).

What might be done is tracking time since last patch/review, but I
really don't know how we're going to identify that considering the
problems with identifying which messages are patches.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-23 Thread Peter Geoghegan

On Mon, Mar 23, 2015 at 11:50 AM, Tomas Vondra
tomas.von...@2ndquadrant.com wrote:
 Not sure how that's supposed to improve the situation? Also, when you
 change the status to 'stalled', it only makes it more difficult to
 identify why it was stalled (was it waiting for author or a review?).

 What might be done is tracking time since last patch/review, but I
 really don't know how we're going to identify that considering the
 problems with identifying which messages are patches.


Perhaps I explained myself poorly. I am proposing having a totally
automated/mechanical way of highlighting no actual change in status in
the CF app. So I think we are in agreement here, or close enough. I
was just talking about a somewhat arbitrary point at which patches are
considered to have stalled within the CF app.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 23.3.2015 18:08, Jeff Janes wrote:
 On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com
 mailto:robertmh...@gmail.com wrote:
 
 There was a patch here, which in the commit fest is hidden behind
 other non-attachments in the same email:
 
 Attachment (randomize.sql
 http://www.postgresql.org/message-id/attachment/37076/randomize.sql)
 at 2015-02-27 19:10:21
 http://www.postgresql.org/message-id/54f0c11d.7000...@2ndquadrant.com/ from
 Tomas Vondra tomas.vondra at 2ndquadrant.com http://2ndquadrant.com
 
 But that patch failed the majority of make check checks in my hands. 
 So I also don't know what the status is.

Ummm, that's not a patch but a testing script ...

There was a patch submitted on 23/2, and I believe that passes most make
check tests, except for two IIRC. But it's not perfect - it was the
first version that mostly worked, and was somehow suitable for getting
feedback.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-13 Thread Jeff Janes

On Mon, Feb 23, 2015 at 3:09 PM, Tomas Vondra tomas.von...@2ndquadrant.com
wrote:

 Hi,

 attached is the result of my first attempt to make the logical column
 ordering patch work. This touches a lot of code in the executor that is
 mostly new to me, so if you see something that looks like an obvious
 bug, it probably is (so let me know).


There is an apply conflict in src/backend/parser/parse_relation.c in the
comments for scanRTEForColumn.

It seems like it should be easy to ignore, but when I ignore it I get make
check failing all over the place.

(The first patch posted in this thread seems to work for me, I did some
testing on it before I realized it was obsolete.)

Cheers,

Jeff

Re: [HACKERS] logical column ordering

2015-03-12 Thread Andres Freund

Hi,

On 2015-03-11 22:16:52 -0400, Tom Lane wrote:
 I agree though that it's worth considering defining pg_attribute.attnum as
 the logical column position so as to minimize the effects on client-side
 code.

I actually wonder if it'd not make more sense to define it as the
physical column number. That'd reduce the invasiveness and risk of the
patch considerably. It means that most existing code doesn't have to be
changed and can just continue to refer to attnum like today. There's
much less risk of it being wrongly used to refer to the physical offset
instead of creation order.  Queries against attnum would still give a
somewhat sane response.

It would make some ALTER TABLE commands a bit more complex if we want to
allow reordering the physical order. But that seems like a much more
localized complexity than previous patches in this thread (although I've
not looked at the last version).

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Alvaro Herrera

Tomas Vondra wrote:
 On 12.3.2015 03:16, Tom Lane wrote:

  I agree though that it's worth considering defining 
  pg_attribute.attnum as the logical column position so as to minimize 
  the effects on client-side code. I doubt there is much stuff 
  client-side that cares about column creation order, but there is 
  plenty that cares about logical column order. OTOH this would 
  introduce confusion into the backend code, since Alvaro's definition 
  of attnum is what most of the backend should care about.
 
 IMHO reusing attnum for logical column order would actually make it more
 complex, especially if we allow users to modify the logical order using
 ALTER TABLE. Because if you change it, you have to walk through all the
 places where it might be referenced and update those too (say, columns
 referenced in indexes and such). Keeping attnum immutable makes this
 much easier and simpler.

I think you're misunderstanding.  The suggestion, as I understand it, is
to rename the attnum column to something else (maybe, say, attidnum),
and rename attlognum to attnum.  That preserves the existing property
that ORDER BY attnum gives you the correct view of the table from the
point of view of the user.  That's very useful because it means clients
looking at pg_attribute need less changes, or maybe none at all.

I think this wouldn't be too difficult to implement, because there
aren't that many places that refer to the column-identity attribute by
name; most of them just grab the TupleDesc-attrs array in whatever
order is appropriate and scan that in a loop.  Only a few of these use
att-attnum inside the loop --- that's what would need to be changed,
and it should be pretty mechanical.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Tom Lane

Alvaro Herrera alvhe...@2ndquadrant.com writes:
 However, there's a difference between making a query silently given
 different results, and breaking it completely forcing the user to
 re-study how to write it.  I think the latter is better.  In that light
 we should just drop attnum as a column name, and use something else:
 maybe (attidnum, attlognum, attphysnum).  So all queries in the wild
 would be forced to be updated, but we would not silently change
 semantics instead.

Hm.  I'm on board with renaming like that inside the backend, but
I'm very much less excited about forcibly breaking client queries.
I think there is little if any client-side code that will care about
either attidnum or attphysnum, so forcing people to think about it
will just create make-work.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Tomas Vondra

On 12.3.2015 14:17, Alvaro Herrera wrote:
Tomas Vondra wrote:
On 12.3.2015 03:16, Tom Lane wrote:

I agree though that it's worth considering defining
pg_attribute.attnum as the logical column position so as to minimize
the effects on client-side code. I doubt there is much stuff
client-side that cares about column creation order, but there is
plenty that cares about logical column order. OTOH this would
introduce confusion into the backend code, since Alvaro's definition
of attnum is what most of the backend should care about.

IMHO reusing attnum for logical column order would actually make it more
complex, especially if we allow users to modify the logical order using
ALTER TABLE. Because if you change it, you have to walk through all the
places where it might be referenced and update those too (say, columns
referenced in indexes and such). Keeping attnum immutable makes this
much easier and simpler.

I think you're misunderstanding. The suggestion, as I understand it,
is to rename the attnum column to something else (maybe, say,
attidnum), and rename attlognum to attnum. That preserves the
existing property that ORDER BY attnum gives you the correct view
of the table from the point of view of the user. That's very useful
because it means clients looking at pg_attribute need less changes,
or maybe none at all.

Hmm ... I understood it as a suggestion to drop attlognum and just
define (attnum, attphysnum).

I think this wouldn't be too difficult to implement, because there
aren't that many places that refer to the column-identity attribute
by name; most of them just grab the TupleDesc-attrs array in
whatever order is appropriate and scan that in a loop. Only a few of
these use att-attnum inside the loop --- that's what would need to
be changed, and it should be pretty mechanical.

I think it's way more complicated. We may fix all the pieces of the
code, but that's not all - attnum is referenced in various system views,
catalogs and such. For example pg_stats view does this:

FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE NOT attisdropped
AND has_column_privilege(c.oid, a.attnum, 'select');

information_schema also uses attnum on many places too.

I see the catalogs as a kind of public API, and redefining the meaning
of an existing column this way seems tricky, especially when we
reference it from other catalogs - I'm pretty sure there's plenty of SQL
queries in various tools that rely on this. Just google for pg_indexes
indkeys unnest and you'll find posts like this one from Craig:

http://stackoverflow.com/questions/18121103/how-to-get-the-index-column-orderasc-desc-nulls-first-from-postgresql

specifically tell people to do this:

SELECT
...
FROM (
SELECT
pg_class.relname,
...
unnest(pg_index.indkey) AS k
FROM pg_index
INNER JOIN pg_class ON pg_index.indexrelid = pg_class.oid
) i
...
INNER JOIN pg_attribute ON (pg_attribute.attrelid = i.indrelid
AND pg_attribute.attnum = k);

which specifically tells people to match attnum vs. indkeys. If we
redefine the meaning of attnum, and instead match indkeys against a
different column (say, attidnum), all those queries will be broken.

Which actually breaks the catalog definition as specified here:

http://www.postgresql.org/docs/devel/static/catalog-pg-index.html

which explicitly says that indkey references pg_attribute.attnum.

But maybe we don't really care about breaking this API and it is a good
approach - I need to think about it and try it.

--
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Alvaro Herrera

Tomas Vondra wrote:
 On 12.3.2015 14:17, Alvaro Herrera wrote:
  Tomas Vondra wrote:
  On 12.3.2015 03:16, Tom Lane wrote:
  
  I agree though that it's worth considering defining 
  pg_attribute.attnum as the logical column position so as to minimize 
  the effects on client-side code. I doubt there is much stuff 
  client-side that cares about column creation order, but there is 
  plenty that cares about logical column order. OTOH this would 
  introduce confusion into the backend code, since Alvaro's definition 
  of attnum is what most of the backend should care about.
 
  IMHO reusing attnum for logical column order would actually make it more
  complex, especially if we allow users to modify the logical order using
  ALTER TABLE. Because if you change it, you have to walk through all the
  places where it might be referenced and update those too (say, columns
  referenced in indexes and such). Keeping attnum immutable makes this
  much easier and simpler.
  
  I think you're misunderstanding. The suggestion, as I understand it,
  is to rename the attnum column to something else (maybe, say,
  attidnum), and rename attlognum to attnum. That preserves the
  existing property that ORDER BY attnum gives you the correct view
  of the table from the point of view of the user. That's very useful
  because it means clients looking at pg_attribute need less changes,
  or maybe none at all.
 
 Hmm ... I understood it as a suggestion to drop attlognum and just
 define (attnum, attphysnum).

Pretty sure it wasn't that.

  I think this wouldn't be too difficult to implement, because there 
  aren't that many places that refer to the column-identity attribute
  by name; most of them just grab the TupleDesc-attrs array in
  whatever order is appropriate and scan that in a loop. Only a few of
  these use att-attnum inside the loop --- that's what would need to
  be changed, and it should be pretty mechanical.
 
 I think it's way more complicated. We may fix all the pieces of the
 code, but that's not all - attnum is referenced in various system views,
 catalogs and such. For example pg_stats view does this:
 
   FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid)
 JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum)
   LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
   WHERE NOT attisdropped
 AND has_column_privilege(c.oid, a.attnum, 'select');
 
 information_schema also uses attnum on many places too.

Those can be fixed with relative ease to refer to attidnum instead.

 I see the catalogs as a kind of public API, and redefining the meaning
 of an existing column this way seems tricky, especially when we
 reference it from other catalogs - I'm pretty sure there's plenty of SQL
 queries in various tools that rely on this.

That's true, but then we've never promised that system catalogs remain
unchanged forever.  That would essentially stop development.

However, there's a difference between making a query silently given
different results, and breaking it completely forcing the user to
re-study how to write it.  I think the latter is better.  In that light
we should just drop attnum as a column name, and use something else:
maybe (attidnum, attlognum, attphysnum).  So all queries in the wild
would be forced to be updated, but we would not silently change
semantics instead.

 Which actually breaks the catalog definition as specified here:
 
   http://www.postgresql.org/docs/devel/static/catalog-pg-index.html
 
 which explicitly says that indkey references pg_attribute.attnum.

That's a simple doc fix.

 But maybe we don't really care about breaking this API and it is a good
 approach - I need to think about it and try it.

Yeah, thanks.


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Peter Eisentraut

On 3/12/15 10:07 AM, Andres Freund wrote:
 I actually wonder if it'd not make more sense to define it as the
 physical column number. That'd reduce the invasiveness and risk of the
 patch considerably.

Clearly, the number of places where attnum has to be changed to
something else is not zero, and so it doesn't matter if a lot or a few
have to be changed.  They all have to be looked at and considered.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Peter Eisentraut

On 3/11/15 10:16 PM, Tom Lane wrote:

 I think using an OID would break more stuff than it fixes in dependency
 tracking; in particular you would now need an explicit dependency link
 from each column to its table, because the sub-object knowledge would
 no longer work.

That might not be a bad thing, but ...

 In any case this patch is going to be plenty big enough
 already without saddling it with a major rewrite of the dependency system.

... is true.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-12 Thread Tomas Vondra

On 12.3.2015 03:16, Tom Lane wrote:
 Peter Eisentraut pete...@gmx.net writes:
 Side idea: Let attnum be the logical number, introduce attphysnum 
 as the storage position, and add an oid to pg_attribute as the 
 eternal identifier.
 
 That way you avoid breaking pretty much all user code that looks at
 pg_attribute, which will probably do something like ORDER BY 
 attnum.
 
 Also, one could get rid of all sorts of ugly code that works around
 the lack of an oid in pg_attribute, such as in the dependency
 tracking.
 
 I think using an OID would break more stuff than it fixes in 
 dependency tracking; in particular you would now need an explicit 
 dependency link from each column to its table, because the 
 sub-object knowledge would no longer work. In any case this patch 
 is going to be plenty big enough already without saddling it with a 
 major rewrite of the dependency system.

Exactly. I believe Alvaro considered that option in the past.

 I agree though that it's worth considering defining 
 pg_attribute.attnum as the logical column position so as to minimize 
 the effects on client-side code. I doubt there is much stuff 
 client-side that cares about column creation order, but there is 
 plenty that cares about logical column order. OTOH this would 
 introduce confusion into the backend code, since Alvaro's definition 
 of attnum is what most of the backend should care about.

IMHO reusing attnum for logical column order would actually make it more
complex, especially if we allow users to modify the logical order using
ALTER TABLE. Because if you change it, you have to walk through all the
places where it might be referenced and update those too (say, columns
referenced in indexes and such). Keeping attnum immutable makes this
much easier and simpler.

regards

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-11 Thread Peter Eisentraut

On 12/9/14 12:41 PM, Alvaro Herrera wrote:
 To recap, this is based on the idea of having three numbers for each
 attribute rather than a single attnum; the first of these is attnum (a
 number that uniquely identifies an attribute since its inception and may
 or may not have any relationship to its storage position and the place
 it expands to through user interaction).  The second is attphysnum,
 which indicates where it is stored in the physical structure.  The third
 is attlognum, which indicates where it expands in *, where must its
 values be placed in COPY or VALUES lists, etc --- the logical position
 as the user sees it.

Side idea:  Let attnum be the logical number, introduce attphysnum as
the storage position, and add an oid to pg_attribute as the eternal
identifier.

That way you avoid breaking pretty much all user code that looks at
pg_attribute, which will probably do something like ORDER BY attnum.

Also, one could get rid of all sorts of ugly code that works around the
lack of an oid in pg_attribute, such as in the dependency tracking.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-11 Thread Tom Lane

Peter Eisentraut pete...@gmx.net writes:
 Side idea:  Let attnum be the logical number, introduce attphysnum as
 the storage position, and add an oid to pg_attribute as the eternal
 identifier.

 That way you avoid breaking pretty much all user code that looks at
 pg_attribute, which will probably do something like ORDER BY attnum.

 Also, one could get rid of all sorts of ugly code that works around the
 lack of an oid in pg_attribute, such as in the dependency tracking.

I think using an OID would break more stuff than it fixes in dependency
tracking; in particular you would now need an explicit dependency link
from each column to its table, because the sub-object knowledge would
no longer work.  In any case this patch is going to be plenty big enough
already without saddling it with a major rewrite of the dependency system.

I agree though that it's worth considering defining pg_attribute.attnum as
the logical column position so as to minimize the effects on client-side
code.  I doubt there is much stuff client-side that cares about column
creation order, but there is plenty that cares about logical column order.
OTOH this would introduce confusion into the backend code, since Alvaro's
definition of attnum is what most of the backend should care about.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-03 Thread Bruce Momjian

On Thu, Feb 26, 2015 at 01:55:44PM -0800, Josh Berkus wrote:
 On 02/26/2015 01:54 PM, Alvaro Herrera wrote:
  This patch decouples these three things so that they
  can changed freely -- but provides no user interface to do so.  I think
  that trying to only decouple the thing we currently have in two pieces,
  and then have a subsequent patch to decouple again, is additional
  conceptual complexity for no gain.
 
 Oh, I didn't realize there weren't commands to change the LCO.  Without
 at least SQL syntax for LCO, I don't see why we'd take it; this sounds
 more like a WIP patch.

FYI, pg_upgrade is going to need pg_dump --binary-upgrade to output the
columns in physical order with some logical ordering information, i.e.
pg_upgrade cannot be passed only logical ordering from pg_dump.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-03 Thread Jim Nasby


On 3/3/15 11:23 AM, Bruce Momjian wrote:

On Thu, Feb 26, 2015 at 01:55:44PM -0800, Josh Berkus wrote:

On 02/26/2015 01:54 PM, Alvaro Herrera wrote:

This patch decouples these three things so that they
can changed freely -- but provides no user interface to do so.  I think
that trying to only decouple the thing we currently have in two pieces,
and then have a subsequent patch to decouple again, is additional
conceptual complexity for no gain.


Oh, I didn't realize there weren't commands to change the LCO.  Without
at least SQL syntax for LCO, I don't see why we'd take it; this sounds
more like a WIP patch.


FYI, pg_upgrade is going to need pg_dump --binary-upgrade to output the
columns in physical order with some logical ordering information, i.e.
pg_upgrade cannot be passed only logical ordering from pg_dump.


Wouldn't it need attno info too, so all 3 orderings?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-03 Thread Bruce Momjian

On Tue, Mar  3, 2015 at 11:24:38AM -0600, Jim Nasby wrote:
 On 3/3/15 11:23 AM, Bruce Momjian wrote:
 On Thu, Feb 26, 2015 at 01:55:44PM -0800, Josh Berkus wrote:
 On 02/26/2015 01:54 PM, Alvaro Herrera wrote:
 This patch decouples these three things so that they
 can changed freely -- but provides no user interface to do so.  I think
 that trying to only decouple the thing we currently have in two pieces,
 and then have a subsequent patch to decouple again, is additional
 conceptual complexity for no gain.
 
 Oh, I didn't realize there weren't commands to change the LCO.  Without
 at least SQL syntax for LCO, I don't see why we'd take it; this sounds
 more like a WIP patch.
 
 FYI, pg_upgrade is going to need pg_dump --binary-upgrade to output the
 columns in physical order with some logical ordering information, i.e.
 pg_upgrade cannot be passed only logical ordering from pg_dump.
 
 Wouldn't it need attno info too, so all 3 orderings?

Uh, what is the third ordering?  Physical, logical, and ?  It already
gets information about dropped columns, if that is the third one.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-03 Thread Bruce Momjian

On Tue, Mar  3, 2015 at 11:41:20AM -0600, Jim Nasby wrote:
 Wouldn't it need attno info too, so all 3 orderings?
 
 Uh, what is the third ordering?  Physical, logical, and ?  It already
 gets information about dropped columns, if that is the third one.
 
 attnum; used in other catalogs to reference columns.
 
 If you're shuffling everything though pg_dump anyway then maybe it's
 not needed...

Yes, all those attno system table links are done with pg_dump SQL
commands.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-03 Thread Jim Nasby


On 3/3/15 11:26 AM, Bruce Momjian wrote:

On Tue, Mar  3, 2015 at 11:24:38AM -0600, Jim Nasby wrote:

On 3/3/15 11:23 AM, Bruce Momjian wrote:

On Thu, Feb 26, 2015 at 01:55:44PM -0800, Josh Berkus wrote:

On 02/26/2015 01:54 PM, Alvaro Herrera wrote:

This patch decouples these three things so that they
can changed freely -- but provides no user interface to do so.  I think
that trying to only decouple the thing we currently have in two pieces,
and then have a subsequent patch to decouple again, is additional
conceptual complexity for no gain.


Oh, I didn't realize there weren't commands to change the LCO.  Without
at least SQL syntax for LCO, I don't see why we'd take it; this sounds
more like a WIP patch.


FYI, pg_upgrade is going to need pg_dump --binary-upgrade to output the
columns in physical order with some logical ordering information, i.e.
pg_upgrade cannot be passed only logical ordering from pg_dump.


Wouldn't it need attno info too, so all 3 orderings?


Uh, what is the third ordering?  Physical, logical, and ?  It already
gets information about dropped columns, if that is the third one.


attnum; used in other catalogs to reference columns.

If you're shuffling everything though pg_dump anyway then maybe it's not 
needed...

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-01 Thread Alvaro Herrera

Jim Nasby wrote:
 On 2/27/15 2:37 PM, Gavin Flower wrote:
 Might be an idea to allow lists of columns and their starting position.
 
 ALTER TABLE customer ALTER COLUMN (job_id, type_id, account_num) SET
 ORDER 3;
 
 I would certainly want something along those lines because it would be *way*
 less verbose (and presumably a lot faster) than a slew of ALTER TABLE
 statements.

You know you can issue multiple subcommands in one ALTER TABLE
statement, right?  There's no reason to do more than one table rewrite.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-01 Thread Alvaro Herrera

Jim Nasby wrote:
 On 2/27/15 2:49 PM, Alvaro Herrera wrote:
 Tomas Vondra wrote:
 
 1) change the order of columns in SELECT *
 
 - display columns so that related ones grouped together
   (irrespectedly whether they were added later, etc.)
 
 FWIW, I find the ordering more important for things like \d than SELECT *.

Logical column ordering is (or should be) used in all places where
column lists are expanded user-visibly.  \d would be one of those.
Think column order in the output of a JOIN also, for instance -- they
must follow logical column order.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-03-01 Thread Alvaro Herrera

Tomas Vondra wrote:

  We need an API for physical column reordering, even if it's just pg_
  functions.  The reason is that we want to enable people writing their
  own physical column re-ordering tools, so that our users can figure out
  for us what the best reordering algorithm is.
 
 I doubt that. For example, do you realize you can only do that while the
 table is completely empty, and in that case you can just do a CREATE
 TABLE with the proper order?

Not if you have views or constraints depending on the table definition
-- it's not trivial to drop/recreate the table in that case, but you can
of course think about truncating it, then reorder columns, then
repopulate.

Even better you can cause a full table rewrite if needed.

 But if we want to allow users to define this, I'd say let's make that
 part of CREATE TABLE, i.e. the order of columns defines logical order,
 and you use something like 'AFTER' to specify physical order.
 
 CREATE TABLE test (
 a INT AFTER b,-- attlognum = 1, attphysnum = 2
 b INT -- attlognum = 2, attphysnum = 1
 );

Surely you want an ALTER command as a minimum; perhaps that is enough
and there is no need for options in CREATE.

 It might get tricky because of cycles, though.

If there's a cycle, just raise an error.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-02-27 Thread Jim Nasby


On 2/27/15 2:37 PM, Gavin Flower wrote:

Might be an idea to allow lists of columns and their starting position.

ALTER TABLE customer ALTER COLUMN (job_id, type_id, account_num) SET
ORDER 3;


I would certainly want something along those lines because it would be 
*way* less verbose (and presumably a lot faster) than a slew of ALTER 
TABLE statements.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering


On 28/02/15 18:34, Jim Nasby wrote:

On 2/27/15 2:49 PM, Alvaro Herrera wrote:

Tomas Vondra wrote:


1) change the order of columns in SELECT *

- display columns so that related ones grouped together
  (irrespectedly whether they were added later, etc.)


FWIW, I find the ordering more important for things like \d than 
SELECT *.


Hey, after we get this done the next step is allowing different 
logical ordering for different uses! ;P

[...]

How about CREATE COLUMN SELECTION my_col_sel (a, g, b, e) FROM TABLE 
my_table;


Notes:

1. The column names must match those of the table

2. The COLUMN SELECTION is associated with the specified table

3. If a column gets renamed, then the COLUMN SELECTION effectively gets
   updated to use the new column name
   (This can probably be done automatically, by virtue of storing
   references to the appropriate column definitions)

4. Allow fewer columns in the COLUMN SELECTION than the original table

5. Allow the the same column to be duplicated
   (trivial, simply don't raise an error for duplicates)

6. Allow the COLUMN SELECTION name to appear instead of the list of
   columns after the SELECT key word
   (SELECT COLUMN SELECTION my_col_sel FROM my_table WHERE ... - must
   match table in FROM clause)

If several tables are defined in the FROM clause, and 2 different tables 
have COLUMN SELECTION with identical names, then the COLUMN SELECTION 
names in the SELECT must be prefixed either the table name or its alias.



Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering


On 28/02/15 12:21, Josh Berkus wrote:

On 02/27/2015 03:06 PM, Tomas Vondra wrote:

On 27.2.2015 23:48, Josh Berkus wrote:

Actually, I'm going to go back on what I said.

We need an API for physical column reordering, even if it's just pg_
functions.  The reason is that we want to enable people writing their
own physical column re-ordering tools, so that our users can figure out
for us what the best reordering algorithm is.

I doubt that. For example, do you realize you can only do that while the
table is completely empty, and in that case you can just do a CREATE
TABLE with the proper order?

Well, you could recreate the table as the de-facto API, although as you
point out below that still requires new syntax.

But I was thinking of something which would re-write the table, just
like ADD COLUMN x DEFAULT '' does now.


I also doubt the users will be able to optimize the order better than
users, who usually have on idea of how this actually works internally.

We have a lot of power users, including a lot of the people on this
mailing list.

Among the things we don't know about ordering optimization:

* How important is it for index performance to keep key columns adjacent?

* How important is it to pack values  4 bytes, as opposed to packing
values which are non-nullable?

* How important is it to pack values of the same size, as opposed to
packing values which are non-nullable?


But if we want to allow users to define this, I'd say let's make that
part of CREATE TABLE, i.e. the order of columns defines logical order,
and you use something like 'AFTER' to specify physical order.

 CREATE TABLE test (
 a INT AFTER b,-- attlognum = 1, attphysnum = 2
 b INT -- attlognum = 2, attphysnum = 1
 );

It might get tricky because of cycles, though.

It would be a lot easier to allow the user to specific a scalar.

CREATE TABLE test (
a INT NOT NULL WITH ( lognum 1, physnum 2 )
b INT WITH ( lognum 2, physnum 1 )

... and just throw an error if the user creates duplicates or gaps.


I thinks gaps should be okay.

Remember BASIC?  Lines numbers tended to be in 10's so you could easily 
slot new lines in between the existing ones - essential when using the 
Teletype input/output device.


Though the difference here is that you would NOT want to remember the 
original order numbers (at least I don't think that would be worth the 
coding effort  space).  However, the key is the actual order, not the 
numbering.  However, that might require a WARNING message to say that 
the columns would be essentially renumbered from 1 onwards when the 
table was actually created - if gaps had been left.



Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-02-27 Thread Jim Nasby


On 2/27/15 2:49 PM, Alvaro Herrera wrote:

Tomas Vondra wrote:


1) change the order of columns in SELECT *

- display columns so that related ones grouped together
  (irrespectedly whether they were added later, etc.)


FWIW, I find the ordering more important for things like \d than SELECT *.

Hey, after we get this done the next step is allowing different logical 
ordering for different uses! ;P



- keep columns synced with COPY

- requires user interface (ALTER TABLE _ ALTER COLUMN _ SET ORDER _)


Not sure about the ORDER # syntax.  In ALTER ENUM we have AFTER
value and such .. I'd consider that instead.


+1. See also Gavin's suggestion of ALTER COLUMN (a, b, c) SET ORDER ...


2) optimization of physical order (efficient storage / tuple deforming)

- more efficient order for storage (deforming)

- may be done manually by reordering columns in CREATE TABLE

- should be done automatically (no user interface required)


A large part of it can be done automatically: for instance, not-nullable
fixed length types ought to come first, because that enables the


I would think that eliminating wasted space due to alignment would be 
more important than optimizing attcacheoff, at least for a database that 
doesn't fit in memory. Even if it does fit in memory I suspect memory 
bandwidth is more important than clock cycles.

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-02-27 Thread Matt Kelly


 Even if it does fit in memory I suspect memory bandwidth is more important
 than clock cycles.


http://people.freebsd.org/~lstewart/articles/cpumemory.pdf

This paper is old but the ratios should still be pretty accurate.  Main
memory is 240 clock cycles away and L1d is only 3.  If the experiments in
this paper still hold true loading the 8K block into L1d is far more
expensive than the CPU processing done once the block is in cache.

When one adds in NUMA to the contention on this shared resource, its not
that hard for a 40 core machine to starve for memory bandwidth, and for
cores to sit idle waiting for main memory.  Eliminating wasted space seems
far more important even when everything could fit in memory already.

Re: [HACKERS] logical column ordering

On 26.2.2015 23:36, Tom Lane wrote:
 Jim Nasby jim.na...@bluetreble.com writes:
 On 2/26/15 4:01 PM, Alvaro Herrera wrote:
 Josh Berkus wrote:
 Oh, I didn't realize there weren't commands to change the LCO.  Without
 at least SQL syntax for LCO, I don't see why we'd take it; this sounds
 more like a WIP patch.
 
 The reason for doing it this way is that changing the underlying
 architecture is really hard, without having to bear an endless hackers
 bike shed discussion about the best userland syntax to use.  It seems a
 much better approach to do the actually difficult part first, then let
 the rest to be argued to death by others and let those others do the
 easy part (and take all the credit along with that); that way, that
 discussion does not kill other possible uses that the new architecture
 allows.
 
 +1. This patch is already several years old; lets not delay it further.
 
 I tend to agree with that, but how are we going to test things if there's
 no mechanism to create a table in which the orderings are different?

Physical or logical orderings?

Physical ordering is still determined by the CREATE TABLE command, so
you can do either

CREATE TABLE order_1 (
a INT,
b INT
);

or (to get the reverse order)

CREATE TABLE order_2 (
b INT,
a INT
);

Logical ordering may be updated directly in pg_attribute catalog, by
tweaking the attlognum column

UPDATE pg_attribute SET attlognum = 10
 WHERE attrelid = 'order_1'::regclass AND attname = 'a';

Of course, this does not handle duplicities, ranges and such, so that
needs to be done manually. But I think inventing something like

ALTER TABLE order_1 ALTER COLUMN a SET lognum = 11;

should be straightforward. But IMHO getting the internals working
properly first is more important.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 27.2.2015 19:23, Alvaro Herrera wrote:
 Tomas Vondra wrote:
 
 Physical ordering is still determined by the CREATE TABLE command,
 
 In theory, you should be able to UPDATE attphysnum in pg_attribute
 too when the table is empty, and new tuples inserted would follow
 the specified ordering.

Good idea - that'd give you descriptors with

(attnum != attphysnum)

which might trigger some bugs.

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Tomas Vondra wrote:
 On 26.2.2015 23:42, Kevin Grittner wrote:

  One use case is to be able to suppress default display of columns 
  that are used for internal purposes. For example, incremental 
  maintenance of materialized views will require storing a count(t) 
  column, and sometimes state information for aggregate columns, in 
  addition to what the users explicitly request. At the developers' 
  meeting there was discussion of whether and how to avoid displaying 
  these by default, and it was felt that when we have this logical 
  column ordering it would be good to have a way tosuppress default
  display. Perhaps this could be as simple as a special value for
  logical position.
 
 I don't see how hiding columns is related to this patch at all. That's
 completely unrelated thing, and it certainly is not part of this patch.

It's not directly related to the patch, but I think the intent is that
once we have this patch it will be possible to apply other
transformations, such as having columns that are effectively hidden --
consider for example the idea that attlognum be set to a negative
number.  (For instance, consider the idea that system columns may all
have -1 as attlognum, which would just be an indicator that they are
never present in logical column expansion.  That makes sense to me; what
reason do we have to keep them using the current attnums they have?)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 26.2.2015 23:42, Kevin Grittner wrote:
 Tomas Vondra tomas.von...@2ndquadrant.com wrote:
 
 Over the time I've heard various use cases for this patch, but in 
 most cases it was quite speculative. If you have an idea where
 this might be useful, can you explain it here, or maybe point me to
 a place where it's described?
 
 
 One use case is to be able to suppress default display of columns 
 that are used for internal purposes. For example, incremental 
 maintenance of materialized views will require storing a count(t) 
 column, and sometimes state information for aggregate columns, in 
 addition to what the users explicitly request. At the developers' 
 meeting there was discussion of whether and how to avoid displaying 
 these by default, and it was felt that when we have this logical 
 column ordering it would be good to have a way tosuppress default
 display. Perhaps this could be as simple as a special value for
 logical position.

I don't see how hiding columns is related to this patch at all. That's
completely unrelated thing, and it certainly is not part of this patch.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Tomas Vondra wrote:

 Physical ordering is still determined by the CREATE TABLE command,

In theory, you should be able to UPDATE attphysnum in pg_attribute too
when the table is empty, and new tuples inserted would follow the
specified ordering.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Arthur Silva wrote:

 Sorry to intrude, I've been following this post and I was wondering if it
 would allow (in the currently planed form or in the future) a wider set of
 non-rewriting DDLs to Postgres. For example, drop a column without
 rewriting the table.

Perhaps.  But dropping a column already does not rewrite the table, only
marks the column as dropped in system catalogs, so do you have a better
example.

One obvious example is that you have 

CREATE TABLE t (
   t1 int,
   t3 int
);

and later want to add t2 in the middle, the only way currently is to
drop the table and start again (re-creating all dependant views, FKs,
etc).  With the patch you will be able to add the column at the right
place.  If no default value is supplied for the new column, no table
rewrite is necessary at all.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 27.2.2015 20:34, Alvaro Herrera wrote:
 Tomas Vondra wrote:
 
 I think we could calls to the randomization functions into some of the
 regression tests (say 'create_tables.sql'), but that makes regression
 tests ... well, random, and I'm not convinced that's a good thing.

 Also, this makes regression tests harder to think, because SELECT *
 does different things depending on the attlognum order.
 
 No, that approach doesn't seem very useful.  Rather, randomize the
 columns in the CREATE TABLE statement, and then fix up the attlognums so
 that the SELECT * expansion is the same as it would be with the
 not-randomized CREATE TABLE.

Yes, that's a possible approach too - possibly a better one for
regression tests as it fixes the 'SELECT *' but it effectively uses
fixed 'attlognum' and 'attnum' values (it's difficult to randomize
those, as they may be referenced in other catalogs).

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

OK, so let me summarize here (the other posts in this subthread are
discussing the user interface, not the use cases, so I'll respond here).


There are two main use cases:

1) change the order of columns in SELECT *

   - display columns so that related ones grouped together
 (irrespectedly whether they were added later, etc.)

   - keep columns synced with COPY

   - requires user interface (ALTER TABLE _ ALTER COLUMN _ SET ORDER _)


2) optimization of physical order (efficient storage / tuple deforming)

   - more efficient order for storage (deforming)

   - may be done manually by reordering columns in CREATE TABLE

   - should be done automatically (no user interface required)

   - seems useful both for on-disk physical tuples, and virtual tuples


Anything else?

-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 27.2.2015 19:50, Alvaro Herrera wrote:
 Tomas Vondra wrote:
 On 26.2.2015 23:42, Kevin Grittner wrote:
 
 One use case is to be able to suppress default display of columns 
 that are used for internal purposes. For example, incremental 
 maintenance of materialized views will require storing a count(t) 
 column, and sometimes state information for aggregate columns, in 
 addition to what the users explicitly request. At the developers' 
 meeting there was discussion of whether and how to avoid displaying 
 these by default, and it was felt that when we have this logical 
 column ordering it would be good to have a way tosuppress default
 display. Perhaps this could be as simple as a special value for
 logical position.

 I don't see how hiding columns is related to this patch at all. That's
 completely unrelated thing, and it certainly is not part of this patch.
 
 It's not directly related to the patch, but I think the intent is that
 once we have this patch it will be possible to apply other
 transformations, such as having columns that are effectively hidden --
 consider for example the idea that attlognum be set to a negative
 number.  (For instance, consider the idea that system columns may all
 have -1 as attlognum, which would just be an indicator that they are
 never present in logical column expansion.  That makes sense to me; what
 reason do we have to keep them using the current attnums they have?)

My vote is against reusing attlognums in this way - I see that as a
misuse, making the code needlessly complex. A better way to achieve this
is to introduce a simple 'is hidden' flag into pg_attribute, and
something as simple as

   ALTER TABLE t ALTER COLUMN a SHOW;
   ALTER TABLE t ALTER COLUMN a HIDE;

or something along the lines. Not only that's cleaner, but it's also a
better interface for the users than 'you have to set attlognum to a
negative value.'


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 26.2.2015 23:34, Andres Freund wrote:
 On 2015-02-26 16:16:54 -0600, Jim Nasby wrote:
 On 2/26/15 4:01 PM, Alvaro Herrera wrote:
 The reason for doing it this way is that changing the underlying
 architecture is really hard, without having to bear an endless hackers
 bike shed discussion about the best userland syntax to use.  It seems a
 much better approach to do the actually difficult part first, then let
 the rest to be argued to death by others and let those others do the
 easy part (and take all the credit along with that); that way, that
 discussion does not kill other possible uses that the new architecture
 allows.
 
 I agree that it's a sane order of developing things. But: I don't
 think it makes sense to commit it without the capability to change
 the order. Not so much because it'll not serve a purpose at that
 point, but rather because it'll essentially untestable. And this is a
 patch that needs a fair amount of changes, both automated, and
 manual.

I agree that committing something without a code that actually uses it
in practice is not the best approach. That's one of the reasons why I
was asking for the use cases we expect from this.


 At least during development I'd even add a function that randomizes
 the physical order of columns, and keeps the logical one. Running
 the regression tests that way seems likely to catch a fair number of
 bugs.

That's not all that difficult, and can be done in 10 lines of PL/pgSQL -
see the attached file. Should be sufficient for development testing (and
in production there's not much use for that anyway).

 +1. This patch is already several years old; lets not delay it further.

 Plus, I suspect that you could hack the catalog directly if you
 really wanted to change LCO. Worst case you could create a C
 function to do it.
 
 I don't think that's sufficient for testing purposes. Not only for
 the patch itself, but also for getting it right in new code.

I think we could calls to the randomization functions into some of the
regression tests (say 'create_tables.sql'), but that makes regression
tests ... well, random, and I'm not convinced that's a good thing.

Also, this makes regression tests harder to think, because SELECT *
does different things depending on the attlognum order.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


randomize.sql
Description: application/sql

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering


On 28/02/15 09:09, Josh Berkus wrote:

Tomas,

So for an API, 100% of the use cases I have for this feature would be
satisfied by:

ALTER TABLE __ ALTER COLUMN _ SET ORDER #

and:

ALTER TABLE _ ADD COLUMN colname coltype ORDER #

If that's infeasible, a function would be less optimal, but would work:

SELECT pg_column_order(schemaname, tablename, colname, attnum)

If you set the order # to one where a column already exists, other
column attnums would get bumped down, closing up any gaps in the
process.  Obviously, this would require some kind of exclusive lock, but
then ALTER TABLE usually does, doesn't it?


Might be an idea to allow lists of columns and their starting position.

ALTER TABLE customer ALTER COLUMN (job_id, type_id, account_num) SET 
ORDER 3;


So in a table with fields:

1. id
2. *account_num*
3. dob
4. start_date
5. *type_id*
6. preferred_status
7. */job_id/*
8. comment


would end up like:

1. id
2. dob
3. *job_id*
4. *type_id*
5. *account_num*
6. start_date
7. preferred_status
8. comment

Am assuming positions are numbered from 1 onwards.


Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

On 27.2.2015 21:42, Josh Berkus wrote:
 On 02/27/2015 12:25 PM, Tomas Vondra wrote:
 On 27.2.2015 21:09, Josh Berkus wrote:
 Tomas,

 So for an API, 100% of the use cases I have for this feature would be
 satisfied by:

 ALTER TABLE __ ALTER COLUMN _ SET ORDER #

 and:

 ALTER TABLE _ ADD COLUMN colname coltype ORDER #

 Yes, I imagined an interface like that. Just to be clear, you're
 talking about logical order (and not a physical one), right?
 
 Correct. The only reason to rearrange the physical columns is in
 order to optimize, which presumably would be carried out by a utility
 command, e.g. VACUUM FULL OPTIMIZE.

I was thinking more about CREATE TABLE at this moment, but yeah, VACUUM
FULL OPTIMIZE might do the same thing.

 If we ignore the system columns, the current implementation assumes
 that the values in each of the three columns (attnum, attlognum
 and attphysnum) are distinct and within 1..natts. So there are no
 gaps and you'll always set the value to an existing one (so yes,
 shuffling is necessary).

 And yes, that certainly requires an exclusive lock on the
 pg_attribute (I don't think we need a lock on the table itself).
 
 If MVCC on pg_attribute is good enough to not lock against concurrent
 SELECT *, then that would be lovely.

Yeah, I think this will need a bit more thought. We certainly don't want
blocking queries on the table, but we need a consistent list of column
definitions from pg_attribute.


-- 
Tomas Vondrahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Tomas Vondra wrote:

 1) change the order of columns in SELECT *
 
- display columns so that related ones grouped together
  (irrespectedly whether they were added later, etc.)
 
- keep columns synced with COPY
 
- requires user interface (ALTER TABLE _ ALTER COLUMN _ SET ORDER _)

Not sure about the ORDER # syntax.  In ALTER ENUM we have AFTER
value and such .. I'd consider that instead.

 2) optimization of physical order (efficient storage / tuple deforming)
 
- more efficient order for storage (deforming)
 
- may be done manually by reordering columns in CREATE TABLE
 
- should be done automatically (no user interface required)

A large part of it can be done automatically: for instance, not-nullable
fixed length types ought to come first, because that enables the
attcacheoff optimizations in heaptuple.c to fire for more columns.  But
what column comes next?  The offset of the column immediately after them
can also be cached, and so it would be faster to obtain than other
attributes.  Which one to choose here is going to be a coin toss in most
cases, but I suppose there are cases out there which can benefit from
having a particular column there.


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering


On 28/02/15 09:49, Alvaro Herrera wrote:

Tomas Vondra wrote:


1) change the order of columns in SELECT *

- display columns so that related ones grouped together
  (irrespectedly whether they were added later, etc.)

- keep columns synced with COPY

- requires user interface (ALTER TABLE _ ALTER COLUMN _ SET ORDER _)

Not sure about the ORDER # syntax.  In ALTER ENUM we have AFTER
value and such .. I'd consider that instead.


2) optimization of physical order (efficient storage / tuple deforming)

- more efficient order for storage (deforming)

- may be done manually by reordering columns in CREATE TABLE

- should be done automatically (no user interface required)

A large part of it can be done automatically: for instance, not-nullable
fixed length types ought to come first, because that enables the
attcacheoff optimizations in heaptuple.c to fire for more columns.  But
what column comes next?  The offset of the column immediately after them
can also be cached, and so it would be faster to obtain than other
attributes.  Which one to choose here is going to be a coin toss in most
cases, but I suppose there are cases out there which can benefit from
having a particular column there.


Possible, if there is no obvious (to the system) way of deciding the 
order of 2 columns, then the logical order should be used?


As either the order does not really matter, or an expert DBA might know 
which is more efficient.



Cheers,
Gavin


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-02-27 Thread Arthur Silva

On Fri, Feb 27, 2015 at 4:44 PM, Tomas Vondra tomas.von...@2ndquadrant.com
wrote:

 On 27.2.2015 20:34, Alvaro Herrera wrote:
  Tomas Vondra wrote:
 
  I think we could calls to the randomization functions into some of the
  regression tests (say 'create_tables.sql'), but that makes regression
  tests ... well, random, and I'm not convinced that's a good thing.
 
  Also, this makes regression tests harder to think, because SELECT *
  does different things depending on the attlognum order.
 
  No, that approach doesn't seem very useful.  Rather, randomize the
  columns in the CREATE TABLE statement, and then fix up the attlognums so
  that the SELECT * expansion is the same as it would be with the
  not-randomized CREATE TABLE.

 Yes, that's a possible approach too - possibly a better one for
 regression tests as it fixes the 'SELECT *' but it effectively uses
 fixed 'attlognum' and 'attnum' values (it's difficult to randomize
 those, as they may be referenced in other catalogs).

 --
 Tomas Vondrahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


Sorry to intrude, I've been following this post and I was wondering if it
would allow (in the currently planed form or in the future) a wider set of
non-rewriting DDLs to Postgres. For example, drop a column without
rewriting the table.

Re: [HACKERS] logical column ordering

2015-02-27 Thread Josh Berkus

On 02/27/2015 12:25 PM, Tomas Vondra wrote:
 On 27.2.2015 21:09, Josh Berkus wrote:
 Tomas,

 So for an API, 100% of the use cases I have for this feature would be
 satisfied by:

 ALTER TABLE __ ALTER COLUMN _ SET ORDER #

 and:

 ALTER TABLE _ ADD COLUMN colname coltype ORDER #
 
 Yes, I imagined an interface like that. Just to be clear, you're talking
 about logical order (and not a physical one), right?

Correct.  The only reason to rearrange the physical columns is in order
to optimize, which presumably would be carried out by a utility command,
e.g. VACUUM FULL OPTIMIZE.

 If we ignore the system columns, the current implementation assumes that
 the values in each of the three columns (attnum, attlognum and
 attphysnum) are distinct and within 1..natts. So there are no gaps and
 you'll always set the value to an existing one (so yes, shuffling is
 necessary).
 
 And yes, that certainly requires an exclusive lock on the pg_attribute
 (I don't think we need a lock on the table itself).

If MVCC on pg_attribute is good enough to not lock against concurrent
SELECT *, then that would be lovely.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Tomas Vondra wrote:

 I think we could calls to the randomization functions into some of the
 regression tests (say 'create_tables.sql'), but that makes regression
 tests ... well, random, and I'm not convinced that's a good thing.
 
 Also, this makes regression tests harder to think, because SELECT *
 does different things depending on the attlognum order.

No, that approach doesn't seem very useful.  Rather, randomize the
columns in the CREATE TABLE statement, and then fix up the attlognums so
that the SELECT * expansion is the same as it would be with the
not-randomized CREATE TABLE.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

Tomas Vondra wrote:
 On 27.2.2015 20:34, Alvaro Herrera wrote:
  Tomas Vondra wrote:
  
  I think we could calls to the randomization functions into some of the
  regression tests (say 'create_tables.sql'), but that makes regression
  tests ... well, random, and I'm not convinced that's a good thing.
 
  Also, this makes regression tests harder to think, because SELECT *
  does different things depending on the attlognum order.
  
  No, that approach doesn't seem very useful.  Rather, randomize the
  columns in the CREATE TABLE statement, and then fix up the attlognums so
  that the SELECT * expansion is the same as it would be with the
  not-randomized CREATE TABLE.
 
 Yes, that's a possible approach too - possibly a better one for
 regression tests as it fixes the 'SELECT *' but it effectively uses
 fixed 'attlognum' and 'attnum' values (it's difficult to randomize
 those, as they may be referenced in other catalogs).

Why would you care what values are used as attnum?  If anything
misbehaves, surely that would be a bug in the patch.  (Of course, you
can't just change the numbers too much later after the fact, because the
attnum values could have propagated into other tables via foreign keys
and such; it needs to be done during executing CREATE TABLE or
immediately thereafter.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering

2015-02-27 Thread Arthur Silva

On Feb 27, 2015 5:02 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:

 Arthur Silva wrote:

  Sorry to intrude, I've been following this post and I was wondering if
it
  would allow (in the currently planed form or in the future) a wider set
of
  non-rewriting DDLs to Postgres. For example, drop a column without
  rewriting the table.

 Perhaps.  But dropping a column already does not rewrite the table, only
 marks the column as dropped in system catalogs, so do you have a better
 example.

 One obvious example is that you have

 CREATE TABLE t (
t1 int,
t3 int
 );

 and later want to add t2 in the middle, the only way currently is to
 drop the table and start again (re-creating all dependant views, FKs,
 etc).  With the patch you will be able to add the column at the right
 place.  If no default value is supplied for the new column, no table
 rewrite is necessary at all.

 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services

Cool! I didn't know I could drop stuff without rewriting.

Ya, that's another example, people do these from GUI tools. That's a nice
side effect. Cool (again)!

Re: [HACKERS] logical column ordering

2015-02-27 Thread Josh Berkus

On 02/27/2015 12:48 PM, Tomas Vondra wrote:
 On 27.2.2015 21:42, Josh Berkus wrote:
 On 02/27/2015 12:25 PM, Tomas Vondra wrote:
 On 27.2.2015 21:09, Josh Berkus wrote:
 Tomas,

 So for an API, 100% of the use cases I have for this feature would be
 satisfied by:

 ALTER TABLE __ ALTER COLUMN _ SET ORDER #

 and:

 ALTER TABLE _ ADD COLUMN colname coltype ORDER #

 Yes, I imagined an interface like that. Just to be clear, you're
 talking about logical order (and not a physical one), right?

 Correct. The only reason to rearrange the physical columns is in
 order to optimize, which presumably would be carried out by a utility
 command, e.g. VACUUM FULL OPTIMIZE.
 
 I was thinking more about CREATE TABLE at this moment, but yeah, VACUUM
 FULL OPTIMIZE might do the same thing.

Actually, I'm going to go back on what I said.

We need an API for physical column reordering, even if it's just pg_
functions.  The reason is that we want to enable people writing their
own physical column re-ordering tools, so that our users can figure out
for us what the best reordering algorithm is.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical column ordering