Forum OpenACS Development: Nextval and sessions

Collapse
1: Nextval and sessions
Posted by Malte Sussdorff on 07/06/06 11:50 AM
I learned (thanks to many helpful guys here and on #postgresql) that sequences are session specific and not transaction specific. So forget all my ranting about transactions, this was a cure for the symptom, but not the problem. Problem persists that db_nextval acs_object_id_seq returns the same value if two or more people start creating items at the same time.

So I dug a little bit further and doing a db_nextval t_acs_object_id_seq reduced the number of "unique constraint violation" errors by 40%. So apparently the overhead of checking if it is a view or a real sequence has an impact on the return of the sequence.

I know that it should not do this, but all I can say is it does. The only explanation that I have now left is:

Due to the fact that sequences are tracked per session and AOLserver connects with multiple sessions at the same time, having the sequence queried at the same time from multiple sessions provides a problem.

Now my question is:

Would it make sense to create a new Pool with one connection and use the Poolname in db_nextval as the default database? Would this guarantee that nextval commands are only executed through the same session?

Collapse
2: PostgreSQL sequences are broken? (response to 1)
Posted by Andrew Piskorski on 07/07/06 09:43 AM
Malte, what "ranting about transactions" are you referring to? Do you mean your recent posts to the acs_object_id_seq creation speed and content revision new problem threads?

Your statement that PostgreSQL "sequences are session specific" seems strange. If PostgreSQL sequence values are only unique across a single connection to the database, then they are totally useless as primary keys, which seems really weird! Is this some strange new behavior in a recent version of PostgreSQL? Surely, there is some option to tell PostgreSQL to give you a real database-wide sequence, just like Oracle's sequences?

If a database sequence can ever (unexpectedly) return the same value twice, it is definitely broken! And from what your report, it sounds like it is indeed broken on PostgreSQL! So, what's the full story here? When did PostgreSQL break their sequence support, why, and what's the correct way to fix or work-around that lossage for OpenACS?

Collapse
3: Re: PostgreSQL sequences are broken? (response to 2)
Posted by Malte Sussdorff on 07/07/06 10:59 AM
Yes, I was talking about both. According to the documentation of PostgreSQL it should work:

"nextval: Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value."

I should probably just write a logfile with the id's of the sequence when called from db_nextval and be over with it, just to get some more hard proof. But as a matter of fact I reduced my reliance on db_nextval considerably (I use db_string "select nextval(t_acs_object_id_seq) from dual" instead) and the problem is reduced to happen only once every three or four hours (which translates into every 1.000 objects or so).

Therefore I doubt the problem is within PostgreSQL itself, it seems to be in the way db_nextval queries PostgreSQL, and one assumption was that it has to do with the sessions.

Collapse
4: Re: Nextval and sessions (response to 1)
Posted by Malte Sussdorff on 07/07/06 01:24 PM
To shed some more light into this, here is an excerpt from the error log, note that this is all from the same user. Furthermore, the first error is catched and we try afterwards, if the first insert failed, to increase the sequence number and make the insert again. Interestingly the retrieval of the sequence fails...:

[07/Jul/2006:13:14:37][26514.1126636464][-conn:wieners::24] Error: Ns_PgExec: result status: 7 message: ERROR: duplicate key violates unique constraint "acs_objects_pk"
CONTEXT: SQL statement "insert into acs_objects (object_id, object_type, title, package_id, context_id, creation_date, creation_user, creation_ip, security_inherit_p) values ( $1 , $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 )"
PL/pgSQL function "acs_object__new" line 40 at SQL statement
PL/pgSQL function "content_revision__new" line 30 at assignment
PL/pgSQL function "content_revision__new" line 15 at return

[07/Jul/2006:13:14:37][26514.1126636464][-conn:wieners::24] Error: Ns_PgExec: result status: 7 message: ERROR: current transaction is aborted, commands ignored until end of transaction block

[07/Jul/2006:13:14:39][26514.1126636464][-conn:wieners::24] Error: POST http://lektor.wienersundwieners.de/invoices/offer-ae?
referred by "http://lektor.wienersundwieners.de/invoices/offer-ae";
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")

ERROR: current transaction is aborted, commands ignored until end of transaction block

SQL: select nextval('t_acs_object_id_seq') from dual
while executing
"ns_pg_bind 0or1row nsdb0 {select nextval('t_acs_object_id_seq') from dual}"

Collapse
5: Re: Nextval and sessions (response to 1)
Posted by Torben Brosten on 07/08/06 12:10 PM
Hi Malte,

What db version is this? Is it the latest minor release?

How often are you running 'vacuum full'?

The postgresql Bug 2379 'Duplicate pkeys in table' thread discusses a somewhat similar problem. The db has many inserts over a short period of time, resulting in an occasional duplicate key. There is speculation that VACUUM FULL may be breaking[1] (presumably during a busy session).

In my limited understanding, I beleive some of the diagnostic questions and comments in that thread might also help in this case.

1. http://archives.postgresql.org/pgsql-bugs/2006-04/msg00066.php

best wishes,
 Torben