On Fri, 2005-05-13 at 12:16 -0400, Jan Wieck wrote:
> On 5/13/2005 9:30 AM, Hannu Krosing wrote:
> 
> > On R, 2005-05-13 at 08:23 -0400, Jan Wieck wrote:
> >> On 5/13/2005 6:38 AM, Hannu Krosing wrote:
> >> 
> >> The Slony-I log trigger is an AFTER trigger. Only BEFORE triggers can 
> >> modify NEW. Everything that tries to modify a tuple once stored has to 
> >> issue another query, thereby creating new row versions and firing the 
> >> log trigger again.
> >> 
> >> I still have to see a test case where plperl succeeds in confusing the 
> >> trigger manager so much that it forgets to fire AR triggers. That would 
> >> be the only way how what Sven claims could have happened.
> > 
> > We had a similar problem with a BEFORE trigger (in pl/pgsql) that
> > modified NEW after doing a lookup from another table. and then another
> > AFTER trigger modified that row from that another table which had also
> > both BEFORE and AFTER triggers.
> 
> Well, doing an update to a row in an AFTER trigger, and another BEFORE 
> trigger depends on that row ... I have seen simpler methods to get your 
> foot shot, but this certainly works ;-)
> 
> > 
> > Could it be that ion some cases the slony UPDATEs stored inside the same
> > transaction can be stored in different order then they actually
> > happened ?
> 
> Hmmm ... I am not 100% sure about the trigger firing order. If an update 
> to a row fires an AFTER trigger that called before the slony log 
> trigger, and that AFTER trigger now updates something else ... is the 
> log trigger fired for "something else" possibly called before the first 
> slony log trigger fires or not? Gotta check this ...
> 
> > 
> > I'll see if I can come up with a simple test case.
> > 
> > And it was not "reproducable" in the sense that it happened every time,
> > but it did happen several times. (pg 7.4.5 slony 1.0.5)
> > 
> 
> Some test cases require a dropdb, createdb cycle to reproduce. Our 
> regression test might not even succeed without it.
> 
> 
> Jan
> 

I have reproduced the problem again but the steps to reproducing this
are utterly bizarre. First let me clarify that these updates are not
being done via a trigger but rather a select function_name(arg) and this
function performs the insert. Here is how I reproduced this peculiar
problem.

1) ORIGIN:su to root on box and psql -U <psql superuser> <dbname>
2) ORIGIN: create table and it's primary key
3) SUBSCRIBER: create table and it's primary key (using copy/paste from
other terminal)
4) ORIGIN: slonik: create new set, add table to set, subscribe set
5) ORIGIN run perltest(int) function:
------------
my $query = "insert into perltest4(id,prefix,description,carrier,rate)
SELECT ";
        $query .= "$_[0],prefix,description,carrier,rate from
innat_rootdecks ";
        $query .= "where id = 40;";
$rv = spi_exec_query($query);
return "done";
-------------
like : select perltest(45);
6) ORIGIN: select * from "_T2".sl_log_1 shows the inserts
7) SUBSCRIBER: sees the new rows (about 5k).

... so far so good .. I can repeat 5-7 and all works fine .. now it gets
strange
8) ORIGIN: quit psql session and leave root shell such that I am in my
own shell again. 
9) ORIGIN: start a new psql session using the same superuser name as in
step 1.
10) ORIGIN: select perltest(57);
11) ORIGIN verify inserts happening into table BUT: "_T2".sl_log_1 is
empty and this batch insert does not get replicated.
12) SUBSCRIBER: new rows do not appear in the table
... more oddly:
13) ORIGIN: select perltest(74);
14) ORIGIN: verify inserts happen and now "_T2".sl_log_1 has the inserts
but just for this last run (id = 74).
15) SUBSCRIBER: verify that the set for id=74 did replicate. the set for
id = 57 never did replicate and now the two tables are out of sync.


Sven