Обсуждение: plpgsql replication stored procedure

Hi,

I'm trying to write a stored procedure that can capture all the changes
to a table and record the changes based on the table's primary key.

I can almost get there but the sticking point is being able to access
the primary key field of the NEW/OLD record in the trigger stored
procedure without knowing it's name.

The stored procedure is below and what I'm trying to do is find a way
to get the column from the NEW record that has the name
constraintColName. If I could do that I would replace NEW.oid with the
equivalent of NEW[constraintColName] and remove the need for oid's on
the table.

create or replace function replicate() returns trigger as
$$

 declare
  constraintName varchar;
  constraintColName varchar;
  keyId varchar;
  slaves record;

 begin
    select into constraintName constraint_name from
information_schema.table_constraints where table_name = TG_RELNAME and
constraint_type = 'PRIMARY KEY';
    select into constraintColName column_name from
information_schema.key_column_usage where constraint_name =
constraintName;
    -- execute 'select ' || constraintColName || ' from ' || NEW into
keyId;

    for slaves in
     select slaveid from replicationslaves
    loop
     insert into replicationentries values(default, slaves.slaveid,
TG_OP, TG_RELNAME , NEW.oid, default);
    end loop;
    return NULL;
end;$$
language 'plpgsql';

Aaron

aaron.clauson@gmail.com writes:
> I'm trying to write a stored procedure that can capture all the changes
> to a table and record the changes based on the table's primary key.
> I can almost get there but the sticking point is being able to access
> the primary key field of the NEW/OLD record in the trigger stored
> procedure without knowing it's name.

There is no way to write such a generic trigger in plpgsql (and even if
you could, its performance would suck :-().  You could do it in C if
you're sufficiently determined.

            regards, tom lane

aaron.clauson@gmail.com wrote:
> Hi,
>
> I'm trying to write a stored procedure that can capture all the changes
> to a table and record the changes based on the table's primary key.
>
> I can almost get there but the sticking point is being able to access
> the primary key field of the NEW/OLD record in the trigger stored
> procedure without knowing it's name.
>
> The stored procedure is below and what I'm trying to do is find a way
> to get the column from the NEW record that has the name
> constraintColName. If I could do that I would replace NEW.oid with the
> equivalent of NEW[constraintColName] and remove the need for oid's on
> the table.
>
> create or replace function replicate() returns trigger as
> $$
>
>  declare
>   constraintName varchar;
>   constraintColName varchar;
>   keyId varchar;
>   slaves record;
>
>  begin
>     select into constraintName constraint_name from
> information_schema.table_constraints where table_name = TG_RELNAME and
> constraint_type = 'PRIMARY KEY';
>     select into constraintColName column_name from
> information_schema.key_column_usage where constraint_name =
> constraintName;
>     -- execute 'select ' || constraintColName || ' from ' || NEW into
> keyId;
>
>     for slaves in
>      select slaveid from replicationslaves
>     loop
>      insert into replicationentries values(default, slaves.slaveid,
> TG_OP, TG_RELNAME , NEW.oid, default);
>     end loop;
>     return NULL;
> end;$$
> language 'plpgsql';
>
> Aaron
>
Why not use or adapt the 'C' function in the dbmirror implementation shipped
with Postgres? The Perl script to replicate to the slave database is very
inefficient but the trigger function itself is very fast.

I've also got a C++ implementation of the dbmirror replication perl script as
well if it's any use

Pete
--
www.whitebeam.org
www.yellowhawk.co.uk
-------