[Pgpool-general] Pgpool-II and Slony-I : query lag, sequences and stored procedures need help

[Andrea Moretto]

Thanks for replying!

I do not use autoincrement or serial because the application AS IS  
doesn't use it.
Although I share your approach, the project I am currently managing  
uses its own approach
to assign primary keys.

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

How many of you are facing the same problem?

Andrea

On 16/set/08, at 16:29, Itamar - IspBrasil wrote:

> why you don't use autoincrement / serial ?
>
>
> On 9/16/2008 10:46 AM, Andrea Moretto wrote:
>> 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.
>>
>> Thanks in advance!
>>
>> Regards,
>>
>>    Andrea Moretto
>>
>> Andrea Moretto
>> moretto.andrea at 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.
>> -------------------------------------------------------
>>
>> _______________________________________________
>> Pgpool-general mailing list
>> Pgpool-general at pgfoundry.org
>> http://pgfoundry.org/mailman/listinfo/pgpool-general
>>
>>
>>
>
>

Andrea Moretto
moretto.andrea at 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.
-------------------------------------------------------