Thread: plperl function fails to "fire" Slony trigger
We have a replication set up between 2 servers using Slony; both are runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are made to a replicated table, the replication does not occur; apparently this is due to spi_exec somehow not allowing/causing the slony trigger function to fire. The following 2 queries achieve the same result (one in SQL and the other in plperl) however only the one in SQL ends up being replicated: create or replace function test_insert(int) returns text as $func$ my $query = "insert into inter_rootdecks(id,npanxx,carrier,inter) select $_[0],npanxx,carrier,inter from inter_rootdecks where id = 20;"; $rv = spi_exec_query($query); return "done"; $func$ LANGUAGE plperl; create function test_insert(int) RETURNS text as $func$ INSERT INTO inter_rootdecks (id,npanxx,carrier,inter) select $1,npanxx,carrier,inter from inter_rootdecks where id = 20; SELECT 'done'::text; $func$ LANGUAGE SQL; Both are very basic insert statements, the difference being that the one written in SQL ends up being replicated, the one written in plperl utilizing spi_exec_query does not. In both cases the insert succesfully completes on the source server. Is there anything else that needs to happen in a plperl function such that applicable triggers fire? Sven
Sven Willenberger <sven@dmv.com> writes:
> We have a replication set up between 2 servers using Slony; both are
> runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> made to a replicated table, the replication does not occur; apparently
> this is due to spi_exec somehow not allowing/causing the slony trigger
> function to fire.
Yuck :-(. The only idea that comes to mind is that 8.0 changed the
timing of trigger firing --- the triggers are probably firing while your
function still has control, whereas in earlier releases they'd only fire
after it returns. Could this be breaking some assumption Slony makes
about the order of operations?
regards, tom lane
On 4/22/2005 2:08 PM, Tom Lane wrote: > Sven Willenberger <sven@dmv.com> writes: >> We have a replication set up between 2 servers using Slony; both are >> runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are >> made to a replicated table, the replication does not occur; apparently >> this is due to spi_exec somehow not allowing/causing the slony trigger >> function to fire. > > Yuck :-(. The only idea that comes to mind is that 8.0 changed the > timing of trigger firing --- the triggers are probably firing while your > function still has control, whereas in earlier releases they'd only fire > after it returns. Could this be breaking some assumption Slony makes > about the order of operations? > > regards, tom lane Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to insert the log row. The only way that could possibly be suppressed is by bypassing the executor and doing direct heap_ access. So how does plperl manage that? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote: > On 4/22/2005 2:08 PM, Tom Lane wrote: > > >Sven Willenberger <sven@dmv.com> writes: > >>We have a replication set up between 2 servers using Slony; both are > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are > >>made to a replicated table, the replication does not occur; apparently > >>this is due to spi_exec somehow not allowing/causing the slony trigger > >>function to fire. > > > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the > >timing of trigger firing --- the triggers are probably firing while your > >function still has control, whereas in earlier releases they'd only fire > >after it returns. Could this be breaking some assumption Slony makes > >about the order of operations? > > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to > insert the log row. The only way that could possibly be suppressed is by > bypassing the executor and doing direct heap_ access. > > So how does plperl manage that? It doesn't; it only uses SPI. I guess we would need the original function to see what is really happening. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Limítate a mirar... y algun día veras"
On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > On 4/22/2005 2:08 PM, Tom Lane wrote:
> >
> > >Sven Willenberger <sven@dmv.com> writes:
> > >>We have a replication set up between 2 servers using Slony; both are
> > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > >>made to a replicated table, the replication does not occur; apparently
> > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > >>function to fire.
> > >
> > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the
> > >timing of trigger firing --- the triggers are probably firing while your
> > >function still has control, whereas in earlier releases they'd only fire
> > >after it returns. Could this be breaking some assumption Slony makes
> > >about the order of operations?
> >
> > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to
> > insert the log row. The only way that could possibly be suppressed is by
> > bypassing the executor and doing direct heap_ access.
> >
> > So how does plperl manage that?
>
> It doesn't; it only uses SPI. I guess we would need the original
> function to see what is really happening.
>
If by the "original function" you mean the plperl function, it was in
the thread parent; in essense it runs an "insert into tablename (cols)
select vals from <other table>" query. The Slony trigger is:
"_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')
If by "orginal function" you mean the logtrigger (slony) function it
would appear from a cursory glance at the source that logtrigger
accesses the heap (HeapTuple). It would appear the same manipulation is
utilized by plperl's SPI calls. How this all interacts with the
"executor" is, admittedly, beyond the scope of my comprehension at this
point ...
Sven
On Fri, Apr 22, 2005 at 03:09:13PM -0400, Sven Willenberger wrote:
> On Fri, 2005-04-22 at 14:43 -0400, Alvaro Herrera wrote:
> > On Fri, Apr 22, 2005 at 02:24:57PM -0400, Jan Wieck wrote:
> > > On 4/22/2005 2:08 PM, Tom Lane wrote:
> > >
> > > >Sven Willenberger <sven@dmv.com> writes:
> > > >>We have a replication set up between 2 servers using Slony; both are
> > > >>runnind PostgreSQL 8.0.1. The issue is that when updates/inserts are
> > > >>made to a replicated table, the replication does not occur; apparently
> > > >>this is due to spi_exec somehow not allowing/causing the slony trigger
> > > >>function to fire.
> > > >
> > > >Yuck :-(. The only idea that comes to mind is that 8.0 changed the
> > > >timing of trigger firing --- the triggers are probably firing while your
> > > >function still has control, whereas in earlier releases they'd only fire
> > > >after it returns. Could this be breaking some assumption Slony makes
> > > >about the order of operations?
> > >
> > > Slony triggers are AFTER ROW triggers. All they do is one SPI_execp() to
> > > insert the log row. The only way that could possibly be suppressed is by
> > > bypassing the executor and doing direct heap_ access.
> > >
> > > So how does plperl manage that?
> >
> > It doesn't; it only uses SPI. I guess we would need the original
> > function to see what is really happening.
> >
>
> If by the "original function" you mean the plperl function, it was in
> the thread parent; in essense it runs an "insert into tablename (cols)
> select vals from <other table>" query. The Slony trigger is:
> "_T2_logtrigger_10" AFTER INSERT OR DELETE OR UPDATE ON inter_rootdecks
> FOR EACH ROW EXECUTE PROCEDURE "_T2".logtrigger('_T2', '10', 'kkkv')
>
> If by "orginal function" you mean the logtrigger (slony) function it
> would appear from a cursory glance at the source that logtrigger
> accesses the heap (HeapTuple). It would appear the same manipulation is
> utilized by plperl's SPI calls. How this all interacts with the
> "executor" is, admittedly, beyond the scope of my comprehension at this
> point ...
Hmm. There are no direct heap manipulations that I can see in plperl.
The HeapTuple functions that it uses are merely to check the catalog in
lookup of the function, AFAICS.
I don't have Slony installed here, so I can't run a simple test
directly, but I think the problem lies elsewhere.
The only idea that comes to mind is that the new tuples somehow fail the
snapshot test of the Slony trigger ... not sure if that makes any sense,
because the trigger should affect all new tuples, I imagine.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)