Thread: dbmirror - migration to 8.3 from 7.4

Hi,
we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years, 
and now it is the time to upgrade to 8.3.
We have called our approach "Conditional row grained + FK dependency oriented lazy replication", that is,
any FK dependencies of a row are tranfered only when needed, and each remote slave has a subset of the
master DB. This is applied to a uucp network of postgresql installations that communicate over satelite
dialup connections. That is why we cannot follow any officially supported replication platform.
Now back to my issue,
In the code, i do some SELECTs from the pg_catalog.pg_index, pg_catalog.pg_constraint c,pg_catalog.pg_class,
pg_catalog.pg_attribute and i would like to have your opinion on wether some semantics have changed or
added to the new pg_catalog tables.

The way i find the primary key of a table is:SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID;
i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption
Should i include any of them (e.g. indisvalid) in the where clause above?

The way i find the FK of a table is:SELECT c.confrelid,c.conkey,c.confkey,f.relname FROM pg_catalog.pg_constraint
c,pg_catalog.pg_classf     WHERE c.contype = 'f' AND c.confrelid = f.oid AND c.conrelid= TABLEOID;
 
I noticed that some columns have been added to pg_constraint: conpfeqop,conppeqop,conffeqop
Should i change something to the above query?

Finally, the way i find the name of a column is:SELECT attname FROM pg_attribute WHERE attrelid=TABLEOID and
attnum=ATTNUM;

Also, i had to change any int2vector code, since now int2vector are implemented like varlenas,
and also i had to add PG_DETOAST_DATUM on any array Datum.

The code seems to work however i'd like your comments if i miss something.
Thanks a lot.
-- 
Achilleas Mantzios

> we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years,
> and now it is the time to upgrade to 8.3.
>
> The way i find the primary key of a table is:
>        SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID;
> i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption
> Should i include any of them (e.g. indisvalid) in the where clause above?

Do you use oid? We had an issue where old code relied on oid and when
we tested 8.2 we had issues with oid being used but not available. The
fix was easy and only required a id-column of type serial.

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

>> The way i find the primary key of a table is:
>>        SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID;
>> i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption
>> Should i include any of them (e.g. indisvalid) in the where clause above?
>
> Do you use oid? We had an issue where old code relied on oid and when
> we tested 8.2 we had issues with oid being used but not available. The
> fix was easy and only required a id-column of type serial.

... with oid being used [in our webcode] but not available.

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

Στις Wednesday 07 May 2008 14:49:31 ο/η Claus Guttesen έγραψε:
> > we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years,
> > and now it is the time to upgrade to 8.3.
> >
> > The way i find the primary key of a table is:
> >        SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID;
> > i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption
> > Should i include any of them (e.g. indisvalid) in the where clause above?
>
> Do you use oid? We had an issue where old code relied on oid and when
> we tested 8.2 we had issues with oid being used but not available. The
> fix was easy and only required a id-column of type serial.
>
No, we dont use OID as primary keys.


--
Achilleas Mantzios