logical column position

I'd like to add a new column to pg_attribute that specifies the
attribute's "logical position" within its relation. The idea here is
to separate the logical order of the columns in a relation from the
on-disk storage of the relation's tuples. This allows us to easily &
quickly change column order, add an additional column before or after
an existing column, etc.

At present, attnum basically does three things: identifies an column
within a relation, indicates which columns are system columns, and
defines the order of a relation's columns. I'd like to move this last
functionality into a separate pg_attribute column named "attpos" (or
"attlogicalpos"):

- when the table is created, attnum == attpos. System columns
have attpos < 0, as with attnum. At no point will two
columns of the same relation have the same attpos.

- when returning output to the client and no column ordering
is implied by the query (e.g. "SELECT * ..."), we sort the
columns in ascending attpos order.

- when storing a tuple on disk, we don't consider attpos

- if we want to change the order of the column's in a
relation, we can do so merely by updating pg_attribute; no
changes to the on-disk storage of the relation should be
necessary

A few notes:

(a) ISTM this should also apply to COPY TO and COPY FROM if the user
didn't supply a column list. Is this reasonable? It would break
dumps of the table's contents, but then again, dumps aren't
guaranteed to remain valid over arbitrary changes to the table's
meta-data.

(b) Using the above scheme that attnum == attpos initially, there
won't be any gaps in the sequence of attpos values. That means
that if, for example, we want to move the column in position 50
to position 1, we'll need to change the position's of all the
columns in positions [1..49] (and suffer the resulting MVCC
bloat in pg_attribute). Changing the column order is hardly a
performance critical operation, so that might be acceptable.

If we want to avoid this, one easy (but arguably unclean) way to
do so would be to make the initial value of attpos == attnum *
1000, and make attpos an int4 rather than an int2. Then, we can
do most column reordering operations with only a single
pg_attribute update -- in the worst-case that enough
re-orderings are done that we overflow the 999 "padding"
positions, we can just fall-back to doing multiple pg_attribute
updates. Is this worth doing, and/or is there a better way to
achieve the same effect?

(c) Do I need to consider inheritance?

Comments are welcome.

-Neil

Neil Conway writes:

> (b) Using the above scheme that attnum == attpos initially, there
> won't be any gaps in the sequence of attpos values. That means
> that if, for example, we want to move the column in position 50
> to position 1, we'll need to change the position's of all the
> columns in positions [1..49] (and suffer the resulting MVCC
> bloat in pg_attribute). Changing the column order is hardly a
> performance critical operation, so that might be acceptable.

I don't think you can speak of "bloat" for pg_attribute. But you can
speak of a problem when you want to do the old col = col + 1 in the
presence of a unique index.

> (c) Do I need to consider inheritance?

Inheritance is based on column names, so initially no, but if there is a
command to alter the column order, then it should have an ONLY option.

--
Peter Eisentraut peter_e(at)gmx(dot)net

On Mon, 17 Nov 2003, Neil Conway wrote:

> I'd like to add a new column to pg_attribute that specifies the
> attribute's "logical position" within its relation. The idea here is
> to separate the logical order of the columns in a relation from the
> on-disk storage of the relation's tuples. This allows us to easily &
> quickly change column order, add an additional column before or after
> an existing column, etc.

That sounds excellent!

> (a) ISTM this should also apply to COPY TO and COPY FROM if the user
> didn't supply a column list. Is this reasonable? It would break
> dumps of the table's contents, but then again, dumps aren't
> guaranteed to remain valid over arbitrary changes to the table's
> meta-data.

You're just saying it'd break old dumps, right? I'd assume COPY FROM would
use attpos ordering when writing out columns, or that every user-visible
interaction with the table pretends the columns are in attpos order. So
dumps would break no more or less than when adding or dropping a column
currently, right?

Jon

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I don't think you can speak of "bloat" for pg_attribute. But you
> can speak of a problem when you want to do the old col = col + 1 in
> the presence of a unique index.

I'm sorry, but I'm not sure what either of these comments mean -- can
you elaborate?

-Neil

Jon Jensen <jon(at)endpoint(dot)com> writes:
> You're just saying it'd break old dumps, right? I'd assume COPY FROM
> would use attpos ordering when writing out columns, or that every
> user-visible interaction with the table pretends the columns are in
> attpos order. So dumps would break no more or less than when adding
> or dropping a column currently, right?

Right -- AFAICS, the only change in COPY compatibility would be if you
COPY TO'd a table and then changed the logical column order in some
fashion, you would no longer be able to restore the dump (unless you
specified a column list for the COPY FROM -- which, btw, pg_dump
does). I don't think it will be a problem, I just thought I'd mention
it.

-Neil

> Right -- AFAICS, the only change in COPY compatibility would be if you
> COPY TO'd a table and then changed the logical column order in some
> fashion, you would no longer be able to restore the dump (unless you
> specified a column list for the COPY FROM -- which, btw, pg_dump
> does). I don't think it will be a problem, I just thought I'd mention
> it.

Well it's the same problem as if you'd dropped a column in the middle of
the table.

BTW, one main consideration is that all the postgres admin apps will now
need to support ORDER BY attlognum for 7.5+.

Chris

Neil Conway wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > I don't think you can speak of "bloat" for pg_attribute. But you
> > can speak of a problem when you want to do the old col = col + 1 in
> > the presence of a unique index.
>
> I'm sorry, but I'm not sure what either of these comments mean -- can
> you elaborate?

Peter is pointing out a problem with our unique indexes that might
cause you a problem. Suppose you have a unique index in attlognum:

test=> create table xx( lognum int);
CREATE TABLE
test=> insert into xx values (1);
INSERT 17145 1
test=> insert into xx values (2);
INSERT 17146 1
test=> update xx set lognum = lognum + 1;
UPDATE 2
test=> create unique index yy on xx (lognum);
CREATE INDEX
test=> update xx set lognum = lognum + 1;
ERROR: duplicate key violates unique constraint "yy"

There is discussion to delay unique constraint failures until commit,
then recheck them to see if they are still valid, sort of like what we
do with deferred triggers. This would fix the problem because on
commit, those values are unique, but aren't while the rows are updated
invidually. If we don't get that working you might want to use the 1000
gap idea because it doesn't cause this problem, and we don't support
>1600 columns, so a 1000 gap shouldn't cause a problem and can be
modified later. If they hit 999 updates, just tell them to dump/reload
the table.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, 2003-11-17 at 20:24, Christopher Kings-Lynne wrote:
> > Right -- AFAICS, the only change in COPY compatibility would be if you
> > COPY TO'd a table and then changed the logical column order in some
> > fashion, you would no longer be able to restore the dump (unless you
> > specified a column list for the COPY FROM -- which, btw, pg_dump
> > does). I don't think it will be a problem, I just thought I'd mention
> > it.
>
> Well it's the same problem as if you'd dropped a column in the middle of
> the table.
>
> BTW, one main consideration is that all the postgres admin apps will now
> need to support ORDER BY attlognum for 7.5+.

Yeah... how about maintaining attnum for the logical attribute number
and create an attphysnum or something for the physical position instead?

This is more intrusive into the source, but you don't need to teach new
tricks to external entities.

Christopher Kings-Lynne writes:

> BTW, one main consideration is that all the postgres admin apps will now
> need to support ORDER BY attlognum for 7.5+.

But that is only really important if they've also used the ALTER TABLE
RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they
need to do another. That seems fair.

--
Peter Eisentraut peter_e(at)gmx(dot)net

>>BTW, one main consideration is that all the postgres admin apps will now
>>need to support ORDER BY attlognum for 7.5+.
>
>
> But that is only really important if they've also used the ALTER TABLE
> RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they
> need to do another. That seems fair.

Good point.

Chris

On Tue, 18 Nov 2003, Peter Eisentraut wrote:

> Christopher Kings-Lynne writes:
>
> > BTW, one main consideration is that all the postgres admin apps will now
> > need to support ORDER BY attlognum for 7.5+.
>
> But that is only really important if they've also used the ALTER TABLE
> RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they
> need to do another. That seems fair.

The ability to reshuffle and to get the correct ordering in a client app
are separate unless we're going to assume that all access goes through
that particular client. If one user uses psql and shuffles them, a
second user using fooclient may not see the new ordering.

Will adding the logical attribute number break all of the external
tools? pg_dump, etc are all dependent on attnum now?

Would it be possible to keep the meaning of attnum the same externally
and add another column internally to represent the physical number?

Dave

Dave Cramer wrote:
> Will adding the logical attribute number break all of the external
> tools? pg_dump, etc are all dependent on attnum now?
>
> Would it be possible to keep the meaning of attnum the same externally
> and add another column internally to represent the physical number?

Interesting idea. It would require a lot of code renaming in the
backend, but it could be done.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

>>Will adding the logical attribute number break all of the external
>>tools? pg_dump, etc are all dependent on attnum now?
>>
>>Would it be possible to keep the meaning of attnum the same externally
>>and add another column internally to represent the physical number?
>
>
> Interesting idea. It would require a lot of code renaming in the
> backend, but it could be done.

Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc
and all the main developers for those lists read all these posts, I
think the massive amount of effort to maintain the external interface
isn't worth it.

I can vouch that it would take me exactly 2 minutes to add support for
attlognums in phpPgAdmin.

Chris

Christopher Kings-Lynne wrote:

>
>>> Will adding the logical attribute number break all of the external
>>> tools? pg_dump, etc are all dependent on attnum now?
>>>
>>> Would it be possible to keep the meaning of attnum the same externally
>>> and add another column internally to represent the physical number?
>>
>>
>>
>> Interesting idea. It would require a lot of code renaming in the
>> backend, but it could be done.
>
>
> Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc
> and all the main developers for those lists read all these posts, I
> think the massive amount of effort to maintain the external interface
> isn't worth it.
>
> I can vouch that it would take me exactly 2 minutes to add support for
> attlognums in phpPgAdmin.

Lesson 1 in "SQL for beginners" says "Don't use SELECT * if you rely on
the order of columns". This discussion is about fixing a problem that
only inexperienced programmers have. It's like an extra set of wheels on
top of your car, just in case you drive wrong way...

What's happening if you simply delete a column? Ordering 1,2,3,5? Insert
another column of the same name, as a previously deleted, will it get
the old position number? And so on. IMHO, way too much effort for
working around situations that should be avoided anyway.

Regards,
Andreas

Andreas,

The point of this is to maintain the column position. I don't think that
an alter of a column type should move the column position. It may be
that programmers should not rely on this, but it happens, and in very
large projects. If we can avoid unexpected side-affects like moving the
columns position, then I think we should.

Dave

On Wed, 2003-11-19 at 12:30, Andreas Pflug wrote:
> Christopher Kings-Lynne wrote:
>
> >
> >>> Will adding the logical attribute number break all of the external
> >>> tools? pg_dump, etc are all dependent on attnum now?
> >>>
> >>> Would it be possible to keep the meaning of attnum the same externally
> >>> and add another column internally to represent the physical number?
> >>
> >>
> >>
> >> Interesting idea. It would require a lot of code renaming in the
> >> backend, but it could be done.
> >
> >
> > Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc
> > and all the main developers for those lists read all these posts, I
> > think the massive amount of effort to maintain the external interface
> > isn't worth it.
> >
> > I can vouch that it would take me exactly 2 minutes to add support for
> > attlognums in phpPgAdmin.
>
> Lesson 1 in "SQL for beginners" says "Don't use SELECT * if you rely on
> the order of columns". This discussion is about fixing a problem that
> only inexperienced programmers have. It's like an extra set of wheels on
> top of your car, just in case you drive wrong way...
>
> What's happening if you simply delete a column? Ordering 1,2,3,5? Insert
> another column of the same name, as a previously deleted, will it get
> the old position number? And so on. IMHO, way too much effort for
> working around situations that should be avoided anyway.
>
> Regards,
> Andreas
>
>
>

Dave Cramer wrote:

>Andreas,
>
>The point of this is to maintain the column position. I don't think that
>an alter of a column type should move the column position.
>
Why should ALTER COLUMN change the column number, i.e. position?

>It may be that programmers should not rely on this, but it happens, and in very
>large projects. If we can avoid unexpected side-affects like moving the
>columns position, then I think we should.
>
>
This is *expected* if behaviour if you delete and add columns; is there
any DB system out there that allows to reshuffle the column ordering?

Instead of some order-ordering facility it would be better to support
all kinds of column type changes, not only binary compatible ones. This
would help everybody, not only maintainers of ill-designed software.

Regards,
Andreas

Andreas,

On Wed, 2003-11-19 at 13:07, Andreas Pflug wrote:
> Dave Cramer wrote:
>
> >Andreas,
> >
> >The point of this is to maintain the column position. I don't think that
> >an alter of a column type should move the column position.
> >
> Why should ALTER COLUMN change the column number, i.e. position?

Rod's current proposed patch does that if you do an alter column alter
type. This is an artifact of the underlying mechanism. (ren old col, add
new col, update newcol=oldcol::newtype). Which is the point of the
logical column number discussion, and the todo item.

>
> >It may be that programmers should not rely on this, but it happens, and in very
> >large projects. If we can avoid unexpected side-affects like moving the
> >columns position, then I think we should.
> >
> >
> This is *expected* if behaviour if you delete and add columns; is there
> any DB system out there that allows to reshuffle the column ordering?

Yes, informix allows you to add the column before|after a column, and
mysql allows for add column after col. those are the only two I know
about.there could be more.
>
> Instead of some order-ordering facility it would be better to support
> all kinds of column type changes, not only binary compatible ones. This
> would help everybody, not only maintainers of ill-designed software.
>
> Regards,
> Andreas
>
>
>
>
>

Dave Cramer wrote:

>Andreas,
>
>
>On Wed, 2003-11-19 at 13:07, Andreas Pflug wrote:
>
>
>>Dave Cramer wrote:
>>
>>
>>
>>>Andreas,
>>>
>>>The point of this is to maintain the column position. I don't think that
>>>an alter of a column type should move the column position.
>>>
>>>
>>>
>>Why should ALTER COLUMN change the column number, i.e. position?
>>
>>
>
>Rod's current proposed patch does that if you do an alter column alter
>type. This is an artifact of the underlying mechanism. (ren old col, add
>new col, update newcol=oldcol::newtype).
>
I must have missed that, can't find it in hackers?!?
In this case the old attnum value should simply be reused, to retain the
original ordering. IMHO this is necessary to prevent problems with any
object referencing a column (index, view, ...) The current proposal
invents the attpos for column ordering purpose only, but
views/indexes/etc will reference attnum, and would need updates.

Actually, a column that changes its attnum is just like a table changing
its oid, i.e. it's not the same object any more. This will provoke
problems in administration tools (at least in pgAdmin3, which will try
to refresh its display with the formerly known oid/attnum af ter
executing a change), and maybe other places too.

To put it differently: a ALTER COLUMN command may never-ever change the
identifier of the column, i.e. attrelid/attnum.

Regards,
Andreas

Andreas Pflug kirjutas K, 19.11.2003 kell 20:45:
> Dave Cramer wrote:
> >>Why should ALTER COLUMN change the column number, i.e. position?
> >
> >Rod's current proposed patch does that if you do an alter column alter
> >type. This is an artifact of the underlying mechanism. (ren old col, add
> >new col, update newcol=oldcol::newtype).
> >
> I must have missed that, can't find it in hackers?!?

Was on [PATCHES] IIRC.

> In this case the old attnum value should simply be reused, to retain the
> original ordering. IMHO this is necessary to prevent problems with any
> object referencing a column (index, view, ...)

Actually these have to be recreaqted, especially when changing column
type.

Rod's patchs does that too ;)

> The current proposal
> invents the attpos for column ordering purpose only,

That's the only place _user_ sees it. The other uses are taken care of
inide database backend.

> but
> views/indexes/etc will reference attnum, and would need updates.

they also "reference" column type, and thus need to be updated anyway
when column type changes.

> Actually, a column that changes its attnum is just like a table changing
> its oid, i.e. it's not the same object any more. This will provoke
> problems in administration tools (at least in pgAdmin3, which will try
> to refresh its display with the formerly known oid/attnum af ter
> executing a change), and maybe other places too.

Sure. _any_ change to database structure could break a client not
(designed to be) aware of that change.

> To put it differently: a ALTER COLUMN command may never-ever change the
> identifier of the column, i.e. attrelid/attnum.

to be even more restirictive: ALTER COLUMN may never-ever change the
type of the column, as this too may break some apps. Nah!

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

Hannu Krosing wrote:

>>To put it differently: a ALTER COLUMN command may never-ever change the
>>identifier of the column, i.e. attrelid/attnum.
>>
>>
>
>to be even more restirictive: ALTER COLUMN may never-ever change the
>type of the column, as this too may break some apps. Nah!
>
>
>
Yeah, and the data should be read only :-)

Seriously: Methinks that only a part of the -patches thread was turned
over to -hackers, some important parts are missing.

First, there are column type changes that don't need any
index/view/constraint recheck or data transformation at all, being of
the very popular class "hell, I need to stuff 12 bytes in my
varchar(10)". Some months ago, this was discussed, and there was
consense that binarily compatible types may be changed with few special
precautions (e.g. varchar(12) -> varchar(10) e.g. needs a check for
len<=10). As a consequence, this kind of column type change is
implemented in pgAdmin3".

Probably a large percentage of real life column type changes are such
binarily compatible ones, so it's senseful to handle them separately.

Second, column type changes needing a nontrivial cast function should be
implemented in a way that preserve attnum. This could be done like this:
- decompile dependent objects, and memorize them for later recreation
- ADD tmpCol, UPDATE tmpCol=col::newtype, DROP old column, cascading to
dependent objects, RENAME tmpCol (known stuff)
- restore old attnum, which is a simple UPDATE to pg_attribute at this stage
- recreate all dependent objects

Voila! No need for an additional attpos.

Regards,
Andreas

> Why should ALTER COLUMN change the column number, i.e. position?

Because it creates a NEW column.

>> It may be that programmers should not rely on this, but it happens,
>> and in very
>> large projects. If we can avoid unexpected side-affects like moving the
>> columns position, then I think we should.
>>
>>
> This is *expected* if behaviour if you delete and add columns; is there
> any DB system out there that allows to reshuffle the column ordering?

MySQL

Chris

Andreas Pflug kirjutas N, 20.11.2003 kell 01:38:

> Second, column type changes needing a nontrivial cast function should be
> implemented in a way that preserve attnum. This could be done like this:
> - decompile dependent objects, and memorize them for later recreation
> - ADD tmpCol, UPDATE tmpCol=col::newtype, DROP old column, cascading to
> dependent objects, RENAME tmpCol (known stuff)
> - restore old attnum, which is a simple UPDATE to pg_attribute at this stage

and suddenly your table is broken, as you can't retrieve the tmpCol when
the attnum points to the dropped old column which has data in the format
for old type ...

the whole point of separating attnum and attpos is that attnum is used
internally to retrieve the data and you can't change it by just
UPDATEing pg_attribute.

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

Hannu Krosing wrote:

>
>attnum is used internally to retrieve the data
>
>
>
Oops...

So if an additional column number is invented, it should not be a
logical column number, but a physical storage number for internal data
retrieval. This way, the "user interface" doesn't change, and all those
"SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
expected result.

Regards,
Andreas

Andreas Pflug kirjutas N, 20.11.2003 kell 11:40:
> Hannu Krosing wrote:
>
> >
> >attnum is used internally to retrieve the data
> >
> >
> >
> Oops...
>
> So if an additional column number is invented, it should not be a
> logical column number, but a physical storage number for internal data
> retrieval.

You are just shifting the interface problems to a place needing way more
changes in the backend. There will be some problems either way.

also, tools needing knowledge should start using information schema as
much as they can, making internal reshufflings less of a problem.

> This way, the "user interface" doesn't change, and all those
> "SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
> expected result.

Depending on what you expect ;)

If you expect the above to give you all active columns as orderd as they
are stored, then it does not give you what you expect.

Btw, most of these concerns (and more) were already iterated when DROP
column was done causing gaps in attnum. There were a lot of doomsday
profecies, but in the end it went quite smoothly. The tools needing
internal knowledge about storage (meaning any tool doing select .. from
pg_...) have always needed some upgrades for new verions.

IMHO, The only behaviour visible to common user we should worry about is
SELECT * , and a special column for solving this is _the_ easiest way to
do it.

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

Which is what started the whole discussion.

Dave
On Thu, 2003-11-20 at 04:40, Andreas Pflug wrote:
> Hannu Krosing wrote:
>
> >
> >attnum is used internally to retrieve the data
> >
> >
> >
> Oops...
>
> So if an additional column number is invented, it should not be a
> logical column number, but a physical storage number for internal data
> retrieval. This way, the "user interface" doesn't change, and all those
> "SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
> expected result.
>
> Regards,
> Andreas
>
>

On Wed, 19 Nov 2003 19:07:23 +0100, Andreas Pflug
<pgadmin(at)pse-consulting(dot)de> wrote:
>is there
>any DB system out there that allows to reshuffle the column ordering?

Firebird:
ALTER TABLE tname ALTER COLUMN cname POSITION 7;

Servus
Manfred

Hannu Krosing wrote:

>
>
>You are just shifting the interface problems to a place needing way more
>changes in the backend. There will be some problems either way.
>
>
Not quite. Certainly, basing internal storage on attstoragenum is more
work in the backend, but less (precisely: zero) work on an unknown
number of frontend tools and apps.

>also, tools needing knowledge should start using information schema as
>much as they can, making internal reshufflings less of a problem.
>
>
We had this discussion. information_schema doesn't deliver enough info
needed for admin tools.

>
>
>>This way, the "user interface" doesn't change, and all those
>>"SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
>>expected result.
>>
>>
>
>Depending on what you expect ;)
>
>
Usually, nobody should care about the column ordering, but for those
unfortunate guys that rely on a specific SELECT * ordering the list of
columns displayed in admin tools must show that ordering; this is what
current admin tools expect from attnum. No SQL user would ever care
about internal storage details/pointers/counters, so any admin tool
would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum
END (and the unique key to pg_attribute, as seen from the tool, changes
from refoid/attnum to refoid/attindex too).

>If you expect the above to give you all active columns as orderd as they
>are stored, then it does not give you what you expect.
>
>Btw, most of these concerns (and more) were already iterated when DROP
>column was done causing gaps in attnum. There were a lot of doomsday
>profecies, but in the end it went quite smoothly.
>
I don't bother about missing attnum values, even 1,2,3,5,6,8 is nicely
ordered.

> The tools needing
>internal knowledge about storage (meaning any tool doing select .. from
>pg_...) have always needed some upgrades for new verions.
>
>
Yes, but changes to pg_... should retain the usual meanings as much as
possible, so older tools continue to work. The discussed change is
problematic because old tools *seem* to work ok, but their attnum
interpretation would be wrong.

>IMHO, The only behaviour visible to common user we should worry about is
>SELECT * , and a special column for solving this is _the_ easiest way to
>do it.
>
>
>
Surely this is the easiest way. But it has the biggest impact on clients
too. I'm just imagining what would happen to pgAdmin3. The column number
would have to display attpos (this is what the user is interested in to
see the ordering), while index, FK and so forth will continue to display
attnum. This seems quite unwanted to me.

---
Are there any comments on the proposed lean way to alter columns for
trivial type changes?

Regards,
Andreas

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> You are just shifting the interface problems to a place needing way more
> changes in the backend. There will be some problems either way.

Exactly. I'm considerably more worried about breaking out-of-the-way
places in the backend than I am about what order someone's admin tool
presents the columns in.

> Btw, most of these concerns (and more) were already iterated when DROP
> column was done causing gaps in attnum. There were a lot of doomsday
> profecies, but in the end it went quite smoothly.

That is a good comparison point. I'm inclined to think that we should
do it in a way that minimizes backend changes. The way to do that is
to keep attnum with its current definition (physical position) and add
a new column for the logical position, which only a small number of
places will need to care about.

regards, tom lane

Tom Lane writes:

> Exactly. I'm considerably more worried about breaking out-of-the-way
> places in the backend than I am about what order someone's admin tool
> presents the columns in.

Clearly, the effort of adding logical column numbers will consist of
making choices between physical and logical numbers in the backend in some
places. So one option is to replace some uses of attnum by attlognum.
The other optionis to replace *all* uses of attnum by attphysnum and then
replace some uses of attphysnum by attnum. To me, this looks like an
equal "risk" as far as the backend goes.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> Exactly. I'm considerably more worried about breaking out-of-the-way
>> places in the backend than I am about what order someone's admin tool
>> presents the columns in.

> Clearly, the effort of adding logical column numbers will consist of
> making choices between physical and logical numbers in the backend in some
> places. So one option is to replace some uses of attnum by attlognum.
> The other optionis to replace *all* uses of attnum by attphysnum and then
> replace some uses of attphysnum by attnum. To me, this looks like an
> equal "risk" as far as the backend goes.

This would be a reasonable assessment if we had our hands on every line
of backend code that exists. But you are neglecting the probability of
breaking user-written C functions, PL languages outside the main distro,
etc.

If we were going to go about this in a way that does not localize the
changes, I'd be inclined to use "attlognum" and "attphysnum" ... that
is, *deliberately* break every use that hasn't been looked at and
updated. Even that would not guarantee catching all the trouble spots;
for example loop indexes and attnums passed as function parameters might
not have names that would be caught by a simplistic search-and-replace
update.

I'm for localizing the changes.

regards, tom lane

Rod Taylor <pg(at)rbt(dot)ca> writes:
> On Mon, 2003-11-17 at 20:24, Christopher Kings-Lynne wrote:
>> BTW, one main consideration is that all the postgres admin apps will now
>> need to support ORDER BY attlognum for 7.5+.

> Yeah... how about maintaining attnum for the logical attribute number
> and create an attphysnum or something for the physical position instead?
> This is more intrusive into the source, but you don't need to teach new
> tricks to external entities.
> [ and similar remarks from other people elsewhere in the thread ]

It's completely fallacious to imagine that we could make this change be
transparent to external applications. To take two examples:

1. How many places do you think know that pg_attribute.attnum links to
pg_attrdef.adnum? pg_dump, psql, and the JDBC driver all appear to
know that, in a quick search of the CVS tree; I haven't even bothered to
look at pgadmin and the other apps that are likely to have such
dependencies.

2. How about linking pg_attribute.attnum to entries in pg_index.indkey?
Lots of apps know about that too.

Unless you are going to change the meanings of pg_index.indkey and
pg_attrdef.adnum, you can't simply redefine attnum as a logical column
position. And if you do make such a change you will break code
elsewhere.

If we add a *new* column attlogpos, without changing the semantics
of attnum, then I think we have a fighting chance of making this work
without an undue amount of effort. I see no prospect that we can
change the meaning of attnum without breaking things far and wide.

regards, tom lane

Tom Lane wrote:

>It's completely fallacious to imagine that we could make this change be
>transparent to external applications. To take two examples:
>
>1. How many places do you think know that pg_attribute.attnum links to
>pg_attrdef.adnum? pg_dump, psql, and the JDBC driver all appear to
>know that, in a quick search of the CVS tree; I haven't even bothered to
>look at pgadmin and the other apps that are likely to have such
>dependencies.
>
>2. How about linking pg_attribute.attnum to entries in pg_index.indkey?
>Lots of apps know about that too.
>
>Unless you are going to change the meanings of pg_index.indkey and
>pg_attrdef.adnum, you can't simply redefine attnum as a logical column
>position. And if you do make such a change you will break code
>elsewhere.
>
>If we add a *new* column attlogpos, without changing the semantics
>of attnum, then I think we have a fighting chance of making this work
>without an undue amount of effort. I see no prospect that we can
>change the meaning of attnum without breaking things far and wide.
>
>
>

I don't quite understand your argumentation.
Currently, attnum is used
1) to determine position (the concern)
2) as part of the unique identifier, as used by index, FK etc
3) as pointer for data retrieval.

If only the retrieval functions would use attstoragenum or however you'd
call it, all other references to attnum can remain untouched. Actual
physical reordering would be hidden almost completely. This is a bit
like abstracting a primary key from the row's physical storage location.

Regards,
Andreas

> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> I don't quite understand your argumentation.

My point is that to change attnum into a logical position without
breaking client apps (which is the ostensible reason for doing it
that way), we would need to redefine all system catalog entries that
reference columns by attnum so that they also store logical rather than
physical position. That has a number of serious problems, one big one
being the difficulty of updating them all correctly during a column
renumbering operation. More, it turns what would otherwise be a
relatively localized patch into a massive and bug-prone backend
modification.

I think it is better to consider attnum as sort of a mini-OID: any one
column has a uniquely assigned attnum that will never change and can
be relied on to identify that column. This is essentially how it is
being used now (remember attnum is part of the PK for pg_attribute)
and the fact that it is also the physical position is really rather
incidental as far as the system catalogs are concerned.

You're quite right that attnum is serving three purposes, but that
doesn't mean that we can choose at random which purpose(s) to decouple.
Abandoning the assumption that attnum is a permanent identifier would
break a lot of things --- probably not only in the backend, either.

regards, tom lane

Tom Lane wrote:

>Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>
>>I don't quite understand your argumentation.
>>
>>
>
>My point is that to change attnum into a logical position without
>breaking client apps (which is the ostensible reason for doing it
>that way), we would need to redefine all system catalog entries that
>reference columns by attnum so that they also store logical rather than
>physical position. That has a number of serious problems, one big one
>being the difficulty of updating them all correctly during a column
>renumbering operation. More, it turns what would otherwise be a
>relatively localized patch into a massive and bug-prone backend
>modification.
>
>I think it is better to consider attnum as sort of a mini-OID: any one
>column has a uniquely assigned attnum that will never change and can
>be relied on to identify that column. This is essentially how it is
>being used now (remember attnum is part of the PK for pg_attribute)
>and the fact that it is also the physical position is really rather
>incidental as far as the system catalogs are concerned.
>
>
I agree considering attrelid/attnum as kind-of OID, but a relation's
pg_class.oid won't change at ALTER TABLE either, I'd expect the same
from ALTER COLUMN.

>You're quite right that attnum is serving three purposes, but that
>doesn't mean that we can choose at random which purpose(s) to decouple.
>Abandoning the assumption that attnum is a permanent identifier would
>break a lot of things --- probably not only in the backend, either.
>
>

Maybe my proposal wasn't clear enough:
Just as an index references a pg_class entry by it's OID, not some value
identifying it's physical storage, all objects might continue
referencing columns by attnum. Only tuple handling functions like
heap_getattr and heap_formtuple need to know how to extract a Datum by
its attnum from a HeapTuple or how to compile a HeapTuple correctly. If
reshuffling columns is done inside of these functions, it would be
transparent to the rest of the backend and the clients. Hopefully, there
are not too much of such functions, or fancy modules bypassing them...

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Maybe my proposal wasn't clear enough:
> Just as an index references a pg_class entry by it's OID, not some value
> identifying it's physical storage, all objects might continue
> referencing columns by attnum.

That's exactly the same thing I am saying. Your mistake is to assume
that this function can be combined with identification of a (changeable)
logical column position. It can't. Changeability and immutability are
just not compatible requirements.

regards, tom lane

Tom Lane wrote:

>Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>
>>Maybe my proposal wasn't clear enough:
>>Just as an index references a pg_class entry by it's OID, not some value
>>identifying it's physical storage, all objects might continue
>>referencing columns by attnum.
>>
>>
>
>That's exactly the same thing I am saying. Your mistake is to assume
>that this function can be combined with identification of a (changeable)
>logical column position. It can't. Changeability and immutability are
>just not compatible requirements.
>
>
In the mind of a programmer, a ALTER COLUMN doesn't create a new column,
but merely changes some attributes of an existing column. In this sense,
changeability and immutability are not controversal.

Digging deeper:

TupDesc contains an array of physical attr descriptions, and to access a
column description attnum is taken as index into that array (taken from
fastgetattr).

return fetchatt(tupleDesc->attrs[attnum-1], ...)

The physical location can easily reordered if there's an additional
array, to translate attnum into the array index.

return fetchatt(tupleDesc->attrs[tupleDesc->attrpos[attnum-1]] ...

For sure, reordering (i.e. changing the attrpos array) may only be
performed as long as the column isn't referenced.

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> To put it differently: a ALTER COLUMN command may never-ever change the
> identifier of the column, i.e. attrelid/attnum.

If the ALTER is changing the column type, it's not really the same
column anymore; I see nothing wrong with assigning a new attnum in that
scenario. It's not like you can simply change the type and not go visit
the references in such a case.

regards, tom lane

Tom Lane wrote:

>If the ALTER is changing the column type, it's not really the same
>column anymore;
>

This doesn't strike. "If the ALTER is changing the number of columns,
it's not really the same table anymore" is as true as your statement.
Still, pg_class.oid remains the same for ADD and DROP column.

> I see nothing wrong with assigning a new attnum in that
>scenario. It's not like you can simply change the type and not go visit
>the references in such a case.
>
>

But this fix is about automatically updating references as well, making
the ALTER COLUMN appear a low-impact change to the user (which obviously
isn't true, unless my proposed shortcut for binary compatible type
changes is implemented).

When dropping and recreating an object, nobody would expect to get the
same identifier. When altering, I *do* expect the identifier to remain
the same.

Regards,
Andreas

Andreas Pflug kirjutas N, 20.11.2003 kell 16:10:
> Hannu Krosing wrote:
>
> >
> >
> >You are just shifting the interface problems to a place needing way more
> >changes in the backend. There will be some problems either way.
> >
> >
> Not quite. Certainly, basing internal storage on attstoragenum is more
> work in the backend, but less (precisely: zero) work on an unknown
> number of frontend tools and apps.

With stress on "unknown number" ;)

> >also, tools needing knowledge should start using information schema as
> >much as they can, making internal reshufflings less of a problem.
> >
> >
> We had this discussion. information_schema doesn't deliver enough info
> needed for admin tools.

It should. This is the sole reason for existance of it. If it is not
enough, then it should be updated.

Updating information_schema would also make developers of other admin
tools happy. Remember - competition is good ;)

Compatibility with old verions of admin tools wont happen anyway, so we
should not let that lock backend into bad development decisions. Look
what happened to DOS-WIN16-WIN32.

> >>This way, the "user interface" doesn't change, and all those
> >>"SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
> >>expected result.
> >>
> >>
> >
> >Depending on what you expect ;)
> >
> >
> Usually, nobody should care about the column ordering, but for those
> unfortunate guys that rely on a specific SELECT * ordering the list of
> columns displayed in admin tools must show that ordering; this is what
> current admin tools expect from attnum. No SQL user would ever care
> about internal storage details/pointers/counters, so any admin tool
> would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum
> END

This won't work anyway if table is missing column attpos . You have to
have different queries for different versions. Add it is preferential to
keep these different queries in information_schema of corresponding
databases not all in frontend tool.

You still need different queries for old databases which did not support
schemas.

> (and the unique key to pg_attribute, as seen from the tool, changes
> from refoid/attnum to refoid/attindex too).

the key needs no change, just the ORDER BY clause.

> >If you expect the above to give you all active columns as orderd as they
> >are stored, then it does not give you what you expect.
> >
> >Btw, most of these concerns (and more) were already iterated when DROP
> >column was done causing gaps in attnum. There were a lot of doomsday
> >profecies, but in the end it went quite smoothly.
> >
> I don't bother about missing attnum values, even 1,2,3,5,6,8 is nicely
> ordered.

Still there were several predictions of all admin tools breaking as a
result of gaps.

> > The tools needing
> >internal knowledge about storage (meaning any tool doing select .. from
> >pg_...) have always needed some upgrades for new verions.
> >
> >
> Yes, but changes to pg_... should retain the usual meanings as much as
> possible, so older tools continue to work.

Can you name one PG version change from A.N to A.M where old admin tools
have not needed any changes ?

> The discussed change is
> problematic because old tools *seem* to work ok, but their attnum
> interpretation would be wrong.

attnum interpretation of pgAdmin3 is already wrong - it claims it to be
Position even when some previous columns are dropped. So you can have a
table which has 1 column with "Position" 3 ;)

> >IMHO, The only behaviour visible to common user we should worry about is
> >SELECT * , and a special column for solving this is _the_ easiest way to
> >do it.
> >
> Surely this is the easiest way. But it has the biggest impact on clients
> too.

Not all clients - just admin tools. And we dont have that many admin
tools. And IMNSHO admin tools should move to using information_schema as
much as possible.

And if information_schema is inadequate then fix it instead of bypassing
it.

I think keeping know-how about retrieving postgresql structure inside of
an application instead of making it readily available in
information_schema is either
a) an egoistic attemt of shutting out competition
b) laziness
or
c) both

<grin>

It is also bad design, as it ties ones tool to backend structure too
tightly.

Backend structure will keep changing and the last thing we want to hold
it back is some frontend tool which thinks it knows better how to
organize data in backend. What if some completely new storage is added
to postgreSQL (ancient Postgres versions had built-in support for
several storages). Should all frontend tools (including ?DBC drivers)
need updating or just information_schema ?

> I'm just imagining what would happen to pgAdmin3. The column number
> would have to display attpos (this is what the user is interested in to
> see the ordering),

No they are interested in position as you mentioned above, they didn't
want to see attnum (i.e 1,2,3,5,6,8 in your example) before either.

I think it is a bug that pgAdmin3 shows attnum instead the real
position.

> while index, FK and so forth will continue to display
> attnum. This seems quite unwanted to me.

Actually the column name is shown (at least I could not find attnum
anywhere in keys or indexes)

> ---
> Are there any comments on the proposed lean way to alter columns for
> trivial type changes?

Sorry, I must have missed it ;( could you give a link to archived copy.

But I think that nobody objected, but nobody didn't volunteer to do the
work either ;)

At least that was the impression i got from an answer to my similar
question on growing varchars and dropping isnull's without forcing
column copies and constraint checks.

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

>>We had this discussion. information_schema doesn't deliver enough info
>>needed for admin tools.
>>
>>
>
>It should. This is the sole reason for existance of it. If it is not
>enough, then it should be updated.
>
>
It can't. ANSI says only objects owned by the user are shown. Admins
might be quite unhappy about that...
pg_catalog views don't help either, just look at pg_tables. It doesn't
even have the oid, how should a table be identified uniquely?
The system views are unusable for hardcore admin purposes, until they
include *. So I'd rather use the tables directly.

>>
>>Usually, nobody should care about the column ordering, but for those
>>unfortunate guys that rely on a specific SELECT * ordering the list of
>>columns displayed in admin tools must show that ordering; this is what
>>current admin tools expect from attnum. No SQL user would ever care
>>about internal storage details/pointers/counters, so any admin tool
>>would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum
>>END
>>
>>
>
>This won't work anyway if table is missing column attpos .
>
Sorry to be not precise enough, this was meant as meta code. Of course
the query must be built version dependent.

>Still there were several predictions of all admin tools breaking as a
>result of gaps.
>
>
wasn' me.

>
>Can you name one PG version change from A.N to A.M where old admin tools
>have not needed any changes ?
>
>
Older tools usually continue to work, they just don't know new features.
Maybe some esotheric features break, so few people notice.

>attnum interpretation of pgAdmin3 is already wrong - it claims it to be
>Position even when some previous columns are dropped. So you can have a
>table which has 1 column with "Position" 3 ;)
>
>
attnum isn't interpreted at all in pgAdmin3, only used for ordering. It
can't be used as key to a column any more, if altering a column would
create a new attnum with old name (and old attpos). In this sense, the
key is attrelid/attpos, because only these don't change for an ALTER
COLUMN statement.

Imagine a sql update to a non-pk column would change the pk of the row,
so you'd have to reread the row by its content to obtain the new pk
value. This wouldn't make you happy, right? Same here.

>
>
>>>IMHO, The only behaviour visible to common user we should worry about is
>>>SELECT * , and a special column for solving this is _the_ easiest way to
>>>do it.
>>>
>>>
>>>
>>Surely this is the easiest way. But it has the biggest impact on clients
>>too.
>>
>>
>
>Not all clients - just admin tools. And we dont have that many admin
>tools. And IMNSHO admin tools should move to using information_schema as
>much as possible.
>
>And if information_schema is inadequate then fix it instead of bypassing
>it.
>
>
It is unfixable. Being ANSI-conformant, it prevents the admin seeing
everything.

>I think keeping know-how about retrieving postgresql structure inside of
>an application instead of making it readily available in
>information_schema is either
> a) an egoistic attemt of shutting out competition
> b) laziness
>or
> c) both
>
><grin>
>
>
>
<comment mode=suppress> ******** </comment>

>It is also bad design, as it ties ones tool to backend structure too
>tightly.
>
>Backend structure will keep changing and the last thing we want to hold
>it back is some frontend tool which thinks it knows better how to
>organize data in backend. What if some completely new storage is added
>to postgreSQL (ancient Postgres versions had built-in support for
>several storages). Should all frontend tools (including ?DBC drivers)
>need updating or just information_schema ?
>
>

Again, I'm not against using information_schema. I tried to use it (for
non-admin tool purposes!) and failed badly. pgAdmin3 is a tool for *all*
PostgreSQL features, not just for some common ANSI stuff.

>>I'm just imagining what would happen to pgAdmin3. The column number
>>would have to display attpos (this is what the user is interested in to
>>see the ordering),
>>
>>
>
>No they are interested in position as you mentioned above, they didn't
>want to see attnum (i.e 1,2,3,5,6,8 in your example) before either.
>
>I think it is a bug that pgAdmin3 shows attnum instead the real
>position.
>
>
>
It shows what's in the db, to identify the column. I can't imagine why I
should show a column number, programmers should address by column name
and nothing else.

>Sorry, I must have missed it ;( could you give a link to archived copy.
>
>
About a week ago, when the message you answered on was posted. Took a
week now to appear...

>But I think that nobody objected, but nobody didn't volunteer to do the
>work either ;)
>
>At least that was the impression i got from an answer to my similar
>question on growing varchars and dropping isnull's without forcing
>column copies and constraint checks.
>
>

Yeah, interesting. For my observations, these trivial changes make 90-95
% of daily column change work, that's why I implemented it in pgAdmin3
(targeting the system tables directly...), so it's worth the effort
handling them separately. I might add it some time to the backend (as an
additional code path to the big version).

Regards,
Andreas