On 12/3/2004 5:14 PM, David Parker wrote:

> After successfully putting it off for a while, I'm finally having to confront the need to replicate a database that has large objects in it. In one of our schemas, we support application objects that are essentially software agents, which get represented in the database as some metadata plus an oid field that stores binary content, e.g., a shared library .so file.
> 
> I know there's no support in slony for replicating large objects, primarily, as I understand it, because it's not possible to create triggers on the pg_largeobject catalog object (there may well be other issues).
> 
> First of all, has anybody been thinking about this? Has any experimenting been done?
> 
> Given a table like:
> 
> create table agent
> {
>    id  int,
>    name varchar(64),
>    description varchar(255),
>    ...
>    data oid
> }
> 
> I was thinking something like this might work:
> 
> 1) remote_worker.c sync_event, for a given table insert/update, detects the 
> presence of an oid field. This would require inspecting more schema metadata
> about the tables in the set, of course. I'm already on shaky ground, here, because
> I don't fully understand yet how this sync_event works.
> 
> 2) for the given oid field, the worker reads that lob from the provider db
> 
> 3) writes the lob into the receiver db
> 
> 4) swizzles the oid in the replicated data from the providers local oid to the
> receivers local oid.

Two problems with this.

a) An existing LOB can be opened and modified without the referencing 
row containing its oid being upated at all. So if you just select the 
oid and lo_open, lo_write, lo_close, there's nothing that will trigger.

b) Deferring the read of the LOB until the subscriber actually processes 
the SYNC bears the risk that if transaction x updates the LOB, then a 
SYNC happens, then transaction y deletes the LOB before that SYNC in the 
middle gets processed by the subscribers, there's no data for the update 
procedure left, leading to an inconsistent, visible state in the subscriber.

Without triggers on pg_largeobject, it is impossible to make LO 
replication transparent for Slony-I.

A crufty workaround would be that the application must call some special 
function after doing lo_close or lo_delete but before commit, which 
records the entire LOB content at that time in the replication log. 
Needless to say that that application then will be responsible to never 
forget to call this function, and that it will break at the moment you 
don't have slony installed in that DB.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #