Forum OpenACS Q&A: oid - inconsistencies between PG 7.1 and 7.2?

Collapse
1: oid - inconsistencies between PG 7.1 and 7.2?
Posted by Ola Hansson on 12/30/03 05:00 PM
When reloading an ancient OpenACS 4.5b/PG 7.1.3 dump into a fresh PG 7.2.4 db (after having loaded postgresql.sql) I get an unexpected error (apart from the usual "blah already exists" errors and the "error at or near select" that causes party_approved_member_map, etc., to not be created, which is treated by loading the affected views explicitly - as described here by Roberto: https://openacs.org/doc/misc/openacs-pg-migration.txt ).

The error I get is: 

psql:test.dmp:768: ERROR:  No such attribute or function 'oid'
Line 768 in the dump is this statement: 
CREATE VIEW "user_col_comments" as SELECT upper(text(c.relname)) AS table_name,\ upper(text(a.attname)) AS column_name, d.description AS comments FROM pg_class\ c, (pg_attribute a LEFT JOIN pg_description d ON ((a.oid = d.objoid))) WHERE (\(c.oid = a.attrelid) AND (a.attnum > 0));
The view was created despite the error, but can I safely assume it will work properly?

Thanks.

BTW, Roberto's doc was very hard to find (I was lucky enough to find it by searching the forums) and it would be nice if we could link to it from the current docs, in the "upgrading" section perhaps ...

/Ola

Collapse
2: Re: oid - inconsistencies between PG 7.1 and 7.2? (response to 1)
Posted by Ola Hansson on 12/31/03 05:14 PM
This compilation may better show what the database looked like before and after the migration. 
7.1:

test=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

test=# \d user_col_comments
   View "user_col_comments"
  Attribute  | Type | Modifier
-------------+------+----------
 table_name  | text |
 column_name | text |
 comments    | text |
View definition: SELECT upper(text(c.relname)) AS table_name, upper(text(a.attname)) AS column_name, d.description AS comments FROM pg_class c, (pg_attribute a LEFT JOIN pg_description d ON ((a.oid = d.objoid))) WHERE ((c.oid = a.attrelid) AND (a.attnum > 0));

test=# select count(*) from user_col_comments;
 count
-------
  2212
(1 row)
 
test=# select * from user_col_comments limit 5;
 table_name  | column_name | comments
-------------+-------------+----------
 PG_XACTLOCK | XACTLOCKFOO |
 PG_ATTRDEF  | ADRELID     |
 PG_ATTRDEF  | ADNUM       |
 PG_ATTRDEF  | ADBIN       |
 PG_ATTRDEF  | ADSRC       |
(5 rows)





7.2:

test=# select version();
                                     version                                                                                 
---------------------------------------------------------------------------------
 PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 (Debian)(1 row)

test=# \d user_col_comments
    View "user_col_comments"
   Column    | Type | Modifiers
-------------+------+-----------
 table_name  | text |
 column_name | text |
 comments    | text |
View definition: SELECT upper(text(c.relname)) AS table_name, upper(text(a.attname)) AS column_name, col_description(a.attrelid, int4(a.attnum)) AS comments FROM (pg_class c LEFT JOIN pg_attribute a ON ((a.attrelid = c.oid))) WHERE (a.attnum > 0);

test=# select count(*) from user_col_comments;
 count
-------
  2443
(1 row)
 
test=# select * from user_col_comments limit 5;
 table_name  | column_name | comments
-------------+-------------+----------
 PG_XACTLOCK | XACTLOCKFOO |
 PG_TYPE     | TYPNAME     |
 PG_TYPE     | TYPOWNER    |
 PG_TYPE     | TYPLEN      |
 PG_TYPE     | TYPPRTLEN   |
(5 rows)
Thoughts are appreciated.

Cheers, and happy new year!

/Ola