OIDs missing in pg_attribute?

On Thu, 6 Dec 2001, Marc G. Fournier wrote:

Well, just spend the past few days banging my head against a brick
wall trying to figure out why OpenACS 4.x won't work with PgSQL v7.2b3,
and just figured it out, or, at least, figured out part of it ...

v7.2b3 no longer has an OID on pg_attribute?

I believe so. My guess would be that it cut down the OID usage per
table greatly.

The following works great in v7.1.3, but fails in v7.b3:

select upper(c.relname) as table_name,
upper(a.attname) as column_name,
d.description as comments
from pg_class c,
pg_attribute a
left outer join pg_description d on (a.oid = d.objoid)
where c.oid = a.attrelid
and a.attnum > 0;

I think the test would now be d.objoid=c.oid and d.objsubid=a.attnum
So,
select upper(c.relname) as table_name,
upper(a.attname) as column_name,
d.description as comments
from (pg_class c join pg_attribute a on (c.oid=a.attrelid) left outer join
pg_description d on (d.objsubid=a.attnum and d.objoid=c.\
oid)) where a.attnum>0;

[2001-12-06 21:47] Marc G. Fournier said:
|
| Morning all ...
|
| Well, just spend the past few days banging my head against a brick
| wall trying to figure out why OpenACS 4.x won't work with PgSQL v7.2b3,
| and just figured it out, or, at least, figured out part of it ...
|
| v7.2b3 no longer has an OID on pg_attribute?

nope. It appears to have been removed around 10 Aug 2001.

| The following works great in v7.1.3, but fails in v7.b3:
|
| select upper(c.relname) as table_name,
| upper(a.attname) as column_name,
| d.description as comments
| from pg_class c,
| pg_attribute a
| left outer join pg_description d on (a.oid = d.objoid)
| where c.oid = a.attrelid
| and a.attnum > 0;

see if this does what you need. Notice the col_description() function
that obviates the need for pg_attribute.oid...

SELECT upper(c.relname) as table_name,
upper(a.attname) as column_name,
col_description(a.attrelid, a.attnum) as comments
FROM pg_class c
LEFT JOIN pg_attribute a
ON a.attrelid = c.oid
WHERE a.attnum > 0;

cheers.
brent

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman

"Marc G. Fournier" <scrappy@hub.org> writes:

v7.2b3 no longer has an OID on pg_attribute?

Yup.

Is this intentional? :(

Yup.

The following works great in v7.1.3, but fails in v7.b3:

select upper(c.relname) as table_name,
upper(a.attname) as column_name,
d.description as comments
from pg_class c,
pg_attribute a
left outer join pg_description d on (a.oid = d.objoid)
where c.oid = a.attrelid
and a.attnum > 0;

This would not work anyway in 7.2, since the primary key of
pg_description is now (objoid,classoid,objsubid) not just (objoid).
I'd recommend using col_description(a.attrelid, a.attnum) rather
than the explicit join against pg_description.

regards, tom lane

On Fri, Dec 07, 2001 at 10:42:24AM -0500,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'd recommend using col_description(a.attrelid, a.attnum) rather
than the explicit join against pg_description.

I couldn't find any documentation for this function in the function
section of the development docs or using a search with google.