Re: Home-brewed table syncronization

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

>>>>> "Michael" == Michael A Nachbaur <Michael> writes:

Michael> On Wednesday 09 July 2003 02:28 pm, Cliff Wells wrote:
>> On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: > So,
>> I'm looking at syncronizing 4 tables from one master database
>> to > several child databases. I'm thinking of doing the
>> following with > DBD::Multiplex:
>> >
>> > DELETE FROM TableA; > INSERT INTO TableA (..) VALUES (...); >
>> ....
>> >
>> > on all the child databases, but I'm not sure what kind of
>> impact this > would have on my servers. My impression is that
>> this would hammer the > indexes, and might blow any memory
>> optimization out the window. Only a > few records in my
>> dataset will change from time-to-time, but just the > process
>> of determining what is different may take more effort than
>> simply > rebuilding.
>>
>> Keep a timestamp associated with each record. Only update the
>> records with timestamps later than your last sync.

Michael> I'm dealing with an existing database structure that,
Michael> though I can change it, has a lot of impact on the rest
Michael> of my infrastructure. If I can find a way of doing this
Michael> without resorting to timestamps, I'd much rather do it
Michael> that way.

Had the same issue, so I made a separate table to store
meta-information about what records have been changed in my master
tables. Note that I do not store the actual change, just which record
was affected and the nature of the change: delete, insert or update.

The deltas table is called, strangely enough, ``delta'', and here's
the code that manages it (for a table called pdetail (keys: package,
pool, timeslot):

- -- Table pdetail

create or replace function pdetail_update_delta()
returns opaque
as '
declare
mykeyval varchar(1024) ;
upd char(1) ;

begin
if TG_OP = ''UPDATE'' then
upd := ''U'' ;
mykeyval := OLD.package || ''|'' || OLD.pool || ''|''
|| OLD.timeslot ;
end if ;
if TG_OP = ''INSERT'' then
upd := ''I'' ;
mykeyval := NEW.package || ''|'' || NEW.pool || ''|''
|| NEW.timeslot ;
end if ;
if TG_OP = ''DELETE'' then
upd := ''D'' ;
mykeyval := OLD.package || ''|'' || OLD.pool || ''|''
|| OLD.timeslot ;
execute ''delete from delta where relation=''''''
|| TG_RELNAME || '''''' and keyval=''''''
|| mykeyval || '''''';'' ;
end if ;
insert into delta ( relation , keyval , timestamp , what )
values ( ''pdetail'' , mykeyval , now () , upd ) ;
if TG_OP = ''UPDATE'' or TG_OP = ''INSERT'' then
return NEW ;
end if ;
if TG_OP = ''DELETE'' then
return OLD ;
end if ;
end ;
' language plpgsql ;
create trigger pdetail_update_delta_trigger
after update on pdetail
for each row
execute procedure pdetail_update_delta() ;
create trigger pdetail_insert_delta_trigger
after insert on pdetail
for each row
execute procedure pdetail_update_delta() ;
create trigger pdetail_delete_delta_trigger
before delete on pdetail
for each row
execute procedure pdetail_update_delta() ;

Table delta itself looks like this:

create table delta
(
relation varchar(32) , -- Table name to which update was made
keyval varchar(1024) , -- Key value of the updated record
timestamp timestamp without time zone default now() , -- When
what char(1)
check (what = 'U' or what = 'D' or what = 'I') ,

primary key ( relation , keyval , timestamp )
) ;

Not much experienced with PgSQL, so would appreciate any tips the
masters can give for improving the plpgsql code. However, it works
for me as it is at the moment.

You are free to use this code under the terms of the GNU GPL.

Regards,

- -- Raju
- --
Raj Mathur raju(at)kandalaya(dot)org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
It is the mind that moves
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard <http://www.gnupg.org/>

iD8DBQE/DN78yWjQ78xo0X8RAsmXAJ4k1cq7mFiRxUb6EGO0R81MVfAWfgCfdGxN
K7g2SsvUAPedg7RH86OZcTY=
=JkN/
-----END PGP SIGNATURE-----