Pgpool-II and Slony-I : query lag, sequences and stored procedures need help - Mailing list pgsql-general

Hello there!

I am currently evaluating the best solution for a high-availability,
load balanced cluster.
Currently I am testing this environment : postgres 8.3.1, pgpool-II
2.1, Slony-I 1.2.14.
I've setup a master server to replicate all tables with Slony-I to a
slave, and a frontend with pgpool
that load-balances the queries (load_balance=true,
replication_mode=false, parallel_query=false,
enable_query_cache=false).

There is a problem : a stored procedure that returns a sequence value
(a global unique ID, used as primary key in INSERTs)
returns the same values in successive calls, under certain conditions.
This is clearly due to the replication lag led by Slony-I.
I would call it a race condition.

Now the question : is there a way to force pgpool to redirect all
queries that involves a specific sequence or a stored procedure
to a specific backend? I tried to setup query partitioning, but only
tables are supported so far.

I know that all queries belonging to a single transaction should be
redirected to the same backend, but I would like to
find out a solution working on the backend, avoiding to check a huge
amount of code that works. ;)
I also know that using the pgpool replication mode will solve the
issue, but it can lead to downtime when adding new backends,
since a synch operation is required. Slony-I implement replication in
a more suitable way from this point of view.

I do not use autoincrement or serial primary key because the
application AS IS doesn't use it.

For the sake of clarity I write down the stored procedure and the
sequence instantiation code:

-- begin code excerpt

CREATE SEQUENCE numgen
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 165024182
  CACHE 1;
ALTER TABLE numgen OWNER TO root;

CREATE OR REPLACE FUNCTION pr_next_id(OUT next_number character varying)
  RETURNS character varying AS
$BODY$
declare  myyear char(4);
declare  ii integer;
declare  tmp1 varchar(10);
declare  tmp2 char(10);
BEGIN
    MYYEAR = CAST(EXTRACT(YEAR FROM LOCALTIMESTAMP) AS CHAR(4));
    select CAST(nextval('NUMGEN') AS varchar(10)) into TMP1;
    TMP2 = '0000000000';
    II = length(TMP1);
    NEXT_NUMBER = SSUBSTR(MYYEAR,3,4) || SSUBSTR(TMP2,1,10 - II) ||
TMP1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION pr_next_id() OWNER TO root;

-- end code excerpt
The pr_next_id returns the following values when called on the top of
pgpool repeatedly (WRONG RESULT):

080165024184
080165024185
080165024184  [DUPLICATED VALUE]
080165024185  [DUPLICATED VALUE]
080165024186
080165024187
080165024188

If I call the pr_next_id repeatedly directly on the postgres engine, I
get the following (CORRECT RESULT):

080165024112
080165024113
080165024114
080165024115
080165024116
080165024117
080165024118
080165024119
080165024120

Even this post could be off topic here, I think that some people could
have same needs as mine. I already posted in the pgpool mailing
list, but so far no answer.

Thanks in advance!

Regards,

  Andrea Moretto

Andrea Moretto
moretto.andrea@gmail.com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------