http://www.slony.info/bugzilla/show_bug.cgi?id=314

           Summary: slony1 don't transmit data via second "set"
           Product: Slony-I
           Version: 2.0
          Platform: PC
        OS/Version: Linux
            Status: NEW
          Severity: enhancement
          Priority: low
         Component: slon
        AssignedTo: slony1-bugs at lists.slony.info
        ReportedBy: sanya-spb at list.ru
                CC: slony1-bugs at lists.slony.info
   Estimated Hours: 0.0


I try to make master-master replication via next schema:
DB1->DB2->[my trigger]->DB2*->DB1*, 
where DB1->DB2 is master-slave replication via "set1" of slony1
and DB2*->DB1* is master-slave replication via "set2" of slony1

but last step (slave*->master*) work only if I do insert in DB2 database and
don't work if datas received from DB1 via slony1.

slonik version 2.1.3
postgres (PostgreSQL) 9.2.4


Please help me find where I have mistaken


--------------------------- BEGIN in POSTGRESQL
--------------------------- on MASTER node
CREATE SCHEMA test_slony;
CREATE TABLE test_slony.master
(
  id int4 NOT NULL,
  some_data integer,
  idslave int4,
  slave_id int4,
  CONSTRAINT master_pkey PRIMARY KEY (id)
);
CREATE TABLE test_slony.slave_a
(
  id int4 NOT NULL,
  some_data integer,
  CONSTRAINT slave_a_pkey PRIMARY KEY (id)
);
CREATE TABLE test_slony.slave_b
(
  id int4 NOT NULL,
  some_data integer,
  CONSTRAINT slave_b_pkey PRIMARY KEY (id)
);
CREATE SEQUENCE test_slony.master_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
CREATE OR REPLACE FUNCTION test_slony.slave_a_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
    f_id    int4;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        delete from test_slony.master where idslave=101 and slave_id=OLD.id;
    ELSIF (TG_OP = 'UPDATE') THEN
        update test_slony.master set
            some_data=NEW.some_data,
            slave_id=NEW.id
        where idslave=101 and slave_id=OLD.id;
    ELSIF (TG_OP = 'INSERT') THEN
        select nextval('test_slony.master_id_seq'::regclass) into f_id;
        insert into test_slony.master (id, some_data, idslave, slave_id)
            VALUES (f_id, NEW.some_data, 101::int4, NEW.id);
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION test_slony.slave_b_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
    f_id    int4;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        delete from test_slony.master where idslave=102 and slave_id=OLD.id;
    ELSIF (TG_OP = 'UPDATE') THEN
        update test_slony.master set
            some_data=NEW.some_data,
            slave_id=NEW.id
        where idslave=102 and slave_id=OLD.id;
    ELSIF (TG_OP = 'INSERT') THEN
        select nextval('test_slony.master_id_seq'::regclass) into f_id;
        insert into test_slony.master (id, some_data, idslave, slave_id)
            VALUES (f_id, NEW.some_data, 102::int4, NEW.id);
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE TRIGGER slave_a_trigger_after
  AFTER INSERT OR UPDATE OR DELETE
  ON test_slony.slave_a
  FOR EACH ROW
  EXECUTE PROCEDURE test_slony.slave_a_trigger();
ALTER TABLE test_slony.slave_a ENABLE REPLICA TRIGGER slave_a_trigger_after;
CREATE TRIGGER slave_b_trigger_after
  AFTER INSERT OR UPDATE OR DELETE
  ON test_slony.slave_b
  FOR EACH ROW
  EXECUTE PROCEDURE test_slony.slave_b_trigger();
ALTER TABLE test_slony.slave_b ENABLE TRIGGER slave_b_trigger_after;

--------------------------- on SLAVE node
CREATE SCHEMA test_slony;

CREATE TABLE test_slony.master
(
  id int4 NOT NULL,
  some_data integer,
  idslave int4,
  slave_id int4,
  CONSTRAINT master_pkey PRIMARY KEY (id)
);

CREATE TABLE test_slony.slave_a
(
  id int4 NOT NULL,
  some_data integer,
  CONSTRAINT slave_a_pkey PRIMARY KEY (id)
);

CREATE TABLE test_slony.slave_b
(
  id int4 NOT NULL,
  some_data integer,
  CONSTRAINT slave_b_pkey PRIMARY KEY (id)
);
--------------------------- END 

$ cat slon_tools.conf 
if ($ENV{"SLONYNODES"}) {
    require $ENV{"SLONYNODES"};
} else {
    $CLUSTER_NAME = 'ARIS';
    $LOGDIR = '/var/log/slony1';
    $MASTERNODE = 100;
    add_node(node     => 100,
             host     => '192.168.1.200',
             dbname   => 'MASTER',
             port     => 5432,
             user     => 'postgres',
             password => '*');
    add_node(node     => 101,
             host     => '192.168.1.247',
             dbname   => 'SLAVE',
             port     => 5432,
             user     => 'postgres',
             password => '*');
}
$SLONY_SETS = {
    "set1" => {
        "set_id"       => 100,
        "table_id"     => 1000,
        "sequence_id"  => 1,
        "pkeyedtables" => [
                'test_slony.master',
        ],
        "keyedtables"  => {},
        "serialtables" => [],
        "sequences"    => [],
    },
    "set2" => {
        "set_id"       => 101,
        "origin"       => 101,
        "table_id"     => 1010,
        "sequence_id"  => 1,
        "pkeyedtables" => [
                'test_slony.slave_a',
        ],
        "keyedtables"  => {},
        "serialtables" => [],
        "sequences"    => [],
    },
};
if ($ENV{"SLONYSET"}) {
    require $ENV{"SLONYSET"};
}
1;

--------------------------- START TEST
$ slonik_init_cluster | slonik
$ <starting slony daemon for node 100>
$ <starting slony daemon for node 101>
$ slonik_create_set 100 | slonik
$ slonik_create_set 101 | slonik
$ slonik_subscribe_set 100 101 | slonik
$ slonik_subscribe_set 101 100 | slonik
$ echo 'insert into test_slony.slave_a values (1,1);' | psql -h 192.168.1.247
-d SLAVE
INSERT 0 1

$ echo 'insert into test_slony.slave_b values (1,1);' | psql -h 192.168.1.200
-d MASTER
INSERT 0 1

$ echo 'select * from test_slony.slave_a;' | psql -h 192.168.1.247 -d SLAVE
 id | some_data 
----+-----------
  1 |         1

$ echo 'select * from test_slony.slave_b;' | psql -h 192.168.1.200 -d MASTER
 id | some_data 
----+-----------
  1 |         1

# check slony1 "forward" replication
$ echo 'select * from test_slony.slave_a;' | psql -h 192.168.1.200 -d MASTER
 id | some_data 
----+-----------
  1 |         1

# check triggers are fired
$ echo 'select * from test_slony.master;' | psql -h 192.168.1.200 -d MASTER
 id | some_data | idslave | slave_id 
----+-----------+---------+----------
  1 |         1 |     101 |        1
  2 |         1 |     102 |        1
(2 rows)

# check slony1 "backward" replication
$ echo 'select * from test_slony.master;' | psql -h 192.168.1.247 -d SLAVE
 id | some_data | idslave | slave_id 
----+-----------+---------+----------
  2 |         1 |     102 |        1
(1 row) WHY??

--------------------------- END TEST

-- 
Configure bugmail: http://www.slony.info/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug.
You are the assignee for the bug.