On 07/04/2013 03:50 AM, Raghav wrote:
> Hi,
>
> I've three nodes configured using Slony 2.2.0B/PG 9.3beta with One
> master two slaves and one table.
>
>
>          Node1 (Master - 5555)
>                        |
>          --------------------------------
>          |                          |
> Slave1-5556          Slave2- 5557
>
> As per doc, RESUBSCRIBE NODE its says we could change the provider. So,
> planned to move Provider to Slave1-5556 (node 2).

If you want node 3 to receive subscriptions from node 2 then there must 
be a direct path between node 2 and 3.  Your sl_path entries don't show 
a path between   2->3 and 3->2.

Having said that,

   select "_rep220".resubscribeNode(1, 2, 2);

and

 > DETAIL:  Key (sub_provider, sub_receiver)=(2, 2) is not present in table
 > "sl_path".

Is concerning,  I would have expected it to say  (2,3) not (2,2).    If 
you somehow did call resubscribeNode with the provider and receiver the 
same I would have expected you to see the error message : 'Slony-I: 
subscribeSet(): set origin and receiver cannot be identical'

Are you didn't do a search/replace on node id's when pasting that message?





>
> "The RESUBSCRIBE NODE command will change the provider used to receive
> data from for all of the replication sets originating on a given node.
> If a subscriber node is receiving a number of replication sets with a
> particular node as an origin then the RESUBSCRIBE NODE command will
> change the provider node used by that subscriber node for all of the
> replication sets originating on the given origin."
>
> Hence, I have created a script to perform RESUBSCRIBE NODE:
>
> Resub.sh script:
>
> cluster name = rep220;
> node 1 admin conninfo='host=localhost dbname=postgres user=postgres
> port=5555';
> node 2 admin conninfo='host=localhost dbname=postgres user=postgres
> port=5556';
> node 3 admin conninfo='host=localhost dbname=postgres user=postgres
> port=5557';
> resubscribe node ( origin = 1, provider = 2 , receiver = 3);
>
> While executing it gives me error as:
>
> -bash-4.1$ slonik resub_set.sh
> resub_set.sh:8: PGRES_FATAL_ERROR lock table "_rep220".sl_event_lock,
> "_rep220".sl_config_lock;select "_rep220".resubscribeNode(1, 2, 2);  -
> ERROR:  insert or update on table "sl_subscribe" violates foreign key
> constraint "sl_subscribe-sl_path-ref"
> DETAIL:  Key (sub_provider, sub_receiver)=(2, 2) is not present in table
> "sl_path".
> CONTEXT:  SQL statement "update "_rep220".sl_subscribe
>                          set sub_provider = p_sub_provider,
>                                  sub_forward = p_sub_forward
>                          where sub_set = p_sub_set
>                          and sub_receiver = p_sub_receiver"
> PL/pgSQL function
> _rep220.subscribeset_int(integer,integer,integer,boolean,boolean) line
> 35 at SQL statement
> SQL statement "SELECT "_rep220".subscribeSet_int(v_record.sub_set,
>                                  p_provider,
>                                  p_receiver, v_record.sub_forward, false)"
> PL/pgSQL function _rep220.resubscribenode(integer,integer,integer) line
> 75 at PERFORM
> -bash-4.1$
>
> Here's sl_path output from three nodes:
>
> sl_path ouput from 5555 port
>
> postgres=# select * from _rep220.sl_path ;
>   pa_server | pa_client |                      pa_conninfo
>          | pa_connretry
> -----------+-----------+--------------------------------------------------------+--------------
>           2 |         1 | host=localhost dbname=postgres user=postgres
> port=5556 |           10
>           1 |         2 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
>           3 |         1 | host=localhost dbname=postgres user=postgres
> port=5557 |           10
>           1 |         3 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
> (4 rows)
>
> sl_path ouput from 5556 port
>
> -bash-4.1$ psql -p 5556
> psql (9.3beta1)
> Type "help" for help.
>
> postgres=# select * from _rep220.sl_path ;
>   pa_server | pa_client |                      pa_conninfo
>          | pa_connretry
> -----------+-----------+--------------------------------------------------------+--------------
>           1 |         2 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
>           2 |         1 | host=localhost dbname=postgres user=postgres
> port=5556 |           10
>           3 |         1 | host=localhost dbname=postgres user=postgres
> port=5557 |           10
>           1 |         3 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
> (4 rows)
>
> sl_path ouput from 5557 port
>
> -bash-4.1$ psql -p 5557
> psql (9.3beta1)
> Type "help" for help.
>
> postgres=# select * from _rep220.sl_path ;
>   pa_server | pa_client |                      pa_conninfo
>          | pa_connretry
> -----------+-----------+--------------------------------------------------------+--------------
>           2 |         1 | host=localhost dbname=postgres user=postgres
> port=5556 |           10
>           1 |         2 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
>           1 |         3 | host=localhost dbname=postgres user=postgres
> port=5555 |           10
>           3 |         1 | host=localhost dbname=postgres user=postgres
> port=5557 |           10
> (4 rows)
>
> I may be doing something surely wrong here, would you please indicate me
> what mix-up am doing here.
>
> Thanks in advance.
>
> --
> Regards
> Raghav
> Blog: htt://raghavt.blogspot.com/ <http://raghavt.blogspot.com/>
>
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general