Re: pg_attribute.attnum - wrong column ordinal?

Today I was contacted by a Microsoft (!) developer Kamil who was working on
issues in Linked Servers to PostgreSQL. He brought the following scenario:
if a column is dropped then ordinal positions of remaining columns are
reported incorrectly.

Here is test scenario:
1) create a table in PGAdmin:

create table ms_tst
(
col1 varchar(50),
col2 varchar(50)
);

2) Add col3 and drop col2:

alter table ms_tst add column col3 varchar(50);
alter table ms_tst drop column col2;

3) Use the following query to retrieve columns information (this query is
generated by the OLEDB provider):

select * from
(select T.schemaname as "TABLE_SCHEMA", T.tablename as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION",
A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE",
TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL",
A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as
"COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_tables T on (C.relname=T.tablename)
inner join pg_namespace NS on (NS.oid=C.relnamespace and
NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f'
union select T.schemaname as "TABLE_SCHEMA", T.viewname as "TABLE_NAME",
A.attname as "COLUMN_NAME", A.attnum as "ORDINAL_POSITION",
A.atthasdef as "COLUMN_HASDEFAULT", A.atttypid as "DATA_TYPE",
TY.typname as "TYPNAME", A.attnotnull as "NOT_NULL",
A.attlen as "FIELD_LENGTH", A.atttypmod as "FIELD_MOD", D.adsrc as
"COLUMN_DEFAULT"
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_views T on (C.relname=T.viewname)
inner join pg_namespace NS on (NS.oid=C.relnamespace and
NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f') s
where "TABLE_SCHEMA"='public' and "TABLE_NAME"='ms_tst'
order by "TABLE_SCHEMA", "TABLE_NAME", "ORDINAL_POSITION"

4) Note that ORDINAL_POSITIONs of col1 and col3 are 1 and 3 (expected 1 and
2).

My question: can pg_attribute.attnum be used to determine the sequential
ordinal positions of columns in a table? What is a right way to get the
ordinal numbers?

Please help!

Konstantin

On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com> wrote:

> My question: can pg_attribute.attnum be used to determine the sequential
> ordinal positions of columns in a table? What is a right way to get the
> ordinal numbers?

You could use something like:

row_number() over (partition by T.schemaname,T.viewname order by
attnum) as "ORDINAL_POSITION"

If you just stick this in there in place of attnum it'll cause an
extra sort. It should be possible with enough clever rearranging of
the query to do the whole query with a single sort since that's the
same sort order that the results are ordered in.

Incidentally you probably want UNION ALL rather than UNION in the
original query.

--
greg

Greg,
this is brilliant - thank you very much!

Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find
compatibility information. It works fine with PG 8.3/8.4 and Greenplum 3.3
thou.

Konstantin

On Tue, Nov 24, 2009 at 6:30 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:

> On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com>
> wrote:
>
> > My question: can pg_attribute.attnum be used to determine the sequential
> > ordinal positions of columns in a table? What is a right way to get the
> > ordinal numbers?
>
> You could use something like:
>
> row_number() over (partition by T.schemaname,T.viewname order by
> attnum) as "ORDINAL_POSITION"
>
> If you just stick this in there in place of attnum it'll cause an
> extra sort. It should be possible with enough clever rearranging of
> the query to do the whole query with a single sort since that's the
> same sort order that the results are ordered in.
>
> Incidentally you probably want UNION ALL rather than UNION in the
> original query.
>
> --
> greg
>

On Wed, Nov 25, 2009 at 2:00 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com> wrote:
> Greg,
> this is brilliant - thank you very much!
>
> Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find
> compatibility information. It works fine with PG 8.3/8.4 and Greenplum 3.3
> thou.

It's 8.4 only.

You could also just generate them on the client since the ordering the
query requests is the right order for it.

--
greg

Oops, the server that I thought was 8.3 was recently upgraded to 8.4.

I'm going to generate the ordinals on the client then. The nature of the
query is that it can request columns information for all or several tables
as well. The ordinals generation algorithm can reset counter to 1 each time
the table name changes.

Thank you tons!

On Tue, Nov 24, 2009 at 8:04 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:

> On Wed, Nov 25, 2009 at 2:00 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com>
> wrote:
> > Greg,
> > this is brilliant - thank you very much!
> >
> > Is "partition by" compatible to PostgreSQL 8.0/8.2? I could not find
> > compatibility information. It works fine with PG 8.3/8.4 and Greenplum
> 3.3
> > thou.
>
> It's 8.4 only.
>
> You could also just generate them on the client since the ordering the
> query requests is the right order for it.
>
> --
> greg
>

Greg Stark escribió:
> On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com> wrote:
>
> > My question: can pg_attribute.attnum be used to determine the sequential
> > ordinal positions of columns in a table? What is a right way to get the
> > ordinal numbers?
>
> You could use something like:
>
> row_number() over (partition by T.schemaname,T.viewname order by
> attnum) as "ORDINAL_POSITION"

Should we recast the attributes and columns views in information_schema?
I notice they still use attnum.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Should we recast the attributes and columns views in information_schema?
> I notice they still use attnum.

I'd vote against it, at least until we have something better than a
row_number solution. That would create another huge performance penalty
on views that are already ungodly slow.

When and if we get around to separating physical from logical column
position, the issue might go away "for free".

regards, tom lane

On tor, 2009-12-03 at 10:09 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Should we recast the attributes and columns views in information_schema?
> > I notice they still use attnum.
>
> I'd vote against it, at least until we have something better than a
> row_number solution. That would create another huge performance penalty
> on views that are already ungodly slow.

Should be easy to test the performance impact of this, since the limit
for columns per table is 1600.