Thread: OIDs missing in pg_attribute?
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?
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_classc, pg_attribute a left outer join pg_description d on (a.oid = d.objoid) where c.oid =
a.attrelid and a.attnum > 0;
In v7.1.3, it retuns:
table_name | column_name | comments
---------------------------------+-----------------+----------PG_TYPE | TYPNAME
|PG_TYPE | TYPOWNER |PG_TYPE | TYPLEN |PG_TYPE
| TYPPRTLEN |PG_TYPE | TYPBYVAL |PG_TYPE |
TYPTYPE |PG_TYPE | TYPISDEFINED |PG_TYPE | TYPDELIM |
In v7.2b3, it returns:
ERROR: No such attribute or function 'oid'
arthur_acs=#
Is this intentional? :(
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.