K
K
kav2011-06-18 02:12:38
PostgreSQL
kav, 2011-06-18 02:12:38

pgpool II. After failback on nodes different nextval()?

Initial data
Three servers:
s1. Pgpool-II 3.0.1 + PostgreSQL 8.4
s2. PostgreSQL 8.4
s3. PostgreSQL 8.4
In Pgpool-II, replication and balancing mode is enabled, while only servers s1 and s2 are included in balancing by setting weights.
Online-recovery of the PITR type is configured according to the office. manual ( goo.gl/RC2WG).
Description of the problem
On different nodes, when inserting through Pgpool-II, I get different values ​​in the field with modifier nextval ().
More
Table, column of interest:

<br/>
Table &quot;auth.user_temp&quot;<br/>
 | Column | Type | Modifiers | Storage | <br/>
 | id | integer | not null default nextval('&quot;auth&quot;.user_temp_id_seq'::regclass) | plain |<br/>
...<br/>

Request:
db=# INSERT INTO &quot;auth&quot;.&quot;user_temp&quot; (name, password, last_ip, activation_key, contact_person, id_country, telephone, email, created) VALUES ('user1', '247bad9c4eb5', '127.0.0.1', '228bdcf1', 'FIO', '643', '09090909', '[email protected]', '2011-06-18 01:57:38')<br/>

Initially, only the first node is included in Pgpool-II, the INSERT request is processed, the number in the id field increases by one with each request.
Next, pcp_recovery_node is called to connect the second node (s2) to pgpool. I execute INSERT on Pgpool, I get identical id = 15 (for example) in auth.user_temp, everything is ok.
But as soon as I connect the third node (s3) via recovery, the INSERT result is as follows:
s1. id = 17
s2. id = 16
s3. id = 17
In fact, it turns out that the data is identical only on Primary(s1) and the node that was last synchronized, be it s2 or s3, one of them constantly lags behind in id depending on the recovery order.
This is critical, since further foreign keys are faked and nodes are disconnected from pgpool.
What I tried:
1. I tried to set weight = "0" for s2 and s3 to disable SELECT balancing. Did not help.
2. I read that in pgpool.conf: black_function_list = 'nextval,setval' it is desirable to add 'lastval,currval', but it didn't help.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Albertum, 2011-06-22
@Albertum

Sequences in PostgreSQL are not transactional, i.e. after the transaction is rolled back, the sequence value will remain incremented.
www.postgresql.org/docs/8.4/static/functions-sequence.html

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question