[pgpool-general: 4705] Re: In pgpool while using delete query inside postgresql function , it is not deletingdata from all servers
Tatsuo Ishii
ishii at postgresql.org
Tue May 24 14:43:44 JST 2016
> Hi,
>
> I have created following function in pgpool .
>
> CREATE OR REPLACE Function fun1(Id int)
> RETURNS boolean as $executionStatus$
> DECLARE
>
> BEGIN
> DELETE FROM table1 where table1_id = Id ;
> DELETE from table2 where table2_id = Id ;
> DELETE from table3 where table3_id = Id ;
> RETURN true;
> END;
> $executionStatus$ LANGUAGE plpgsql;
>
> I run following command inside the postgres shell of pgpool
>
> select fun1(1);
> It is deleted the data only from master. I tried again then it is deleted
> from different server.So replication fails in this case. But if i use
> delete queries separately then it is working fine.It is deleting data from
> all servers.
>
> DELETE FROM table1 where table1_id = 1 ;DELETE from table2 where table2_id
> = 1 ;DELETE from table3 where table3_id = 1 ;
>
> Please let me know how to fix this issue .
Assuming you are using replication mode (replication_mode = on)、I
guess the SELECT is load balanced and only one of the PostgreSQL
servers receives the command. Solution would be:
1) Add "/*NO LOAD BALANCE*/" commento in front of the SELECT.
2) Add func1 to the black_function_list.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list