Forum OpenACS Development: PostgreSQL sequences are broken?
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?
"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.