Forum OpenACS Development: PostgreSQL sequences are broken?

Posted by Andrew Piskorski on
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?

Posted by Malte Sussdorff on
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.