Forum OpenACS Q&A: Postgresql duplicate Key problem

Posted by Bob OConnor on

We _may_ have a corruption problem with a recent install of postgresql. For a short while, we had two copies of ACS 3.2.4 running off the same postgresql database. The second copy has been shut off. The error occurs when you log in as a new user on the user-new.tcl (shown as user-login.tcl) Fill in the Security and About You sections and "Register" The error message from the log reads:

[23/Mar/2001...] Error: dbinit:
error(localhost::ercbase,ERROR:  Cannot insert a 
duplicate key into unique index users_preferences_pkey ): 
'insert into users_preferences (user_id) values (53)'

It turns out that "53" IS unique and is not in the table user_preferences. So, thinking index corruption, I drop the index and recreate the index. This doesn't help.

I can manually add a new entry into the users table then manually add an entry with same user_id into the users_preferences table.

Oh, before the manual stuff above, I did do a vacuum and stopped and restarted the database and aolserver. This didn't help.

Any suggestions on where to look next? Thank you.


Posted by Bob OConnor on
I forgot to mention this is postgres 7.0.3.
Posted by Bob OConnor on
Follow up... I dropped the database and reloaded the data model...
Same problem: I can create users from the admin interface
without trouble but I cannot log on as a new user.

Same error as described above....
Maybe I need to start again with a new copy of OPENacs...

Posted by Bob OConnor on

Ok, I've pounded away at this problem and here are MORE details:

I go into /parameters/myserver.tcl and comment out the line:

# what tables to stuff when user registers
#    ns_param RequiredUserTable users_preferences
     ns_param RequiredUserTable users_demographics
     ns_param RequiredUserTable users_contact

Restart server, Now, registration works fine.
Uncomment and go back...Same "Cannot insert a duplicate..."

Ok, try using psql mybase I do the following:

mybase=# insert into users_preferences 
( user_id) values ( 5555);
ERROR:   referential integrity violation - key referenced from users_preferences not found in users

mybase=# insert into users 
( user_id, first_names, last_name, email, password) 
values ( 7777,'finefirst','finelast','x@y.zocks','pass');
INSERT 123045 1

mybase=# insert into users_preferences 
(user_id) values ( 7777);
ERROR:  Cannot insert a duplicate key into unique index users_preferences_pkey

mybase=# select * from users_preferences ;
 user_id | ... | email_type 
    7777 | ... | text/html
(1 rows shown)


First section with u_id 5555 I can't just insert into users_preferencses so I then do the users insert with u_id 7777 then the users_preferences.

The result is, despite the duplicate key message, the data IS inserted into the users_preferences table.

This doesn't happen with the normal registration process because inserting to the Users table and then inserting to the Users Preferences table is done inside a TRANSACTION so it is rolled back....(user-new-2.tcl,v

There seems to be BAD BEHAVIOR going on on the part of Postgres unless there is something I don't understand. I did drop the db and reload the data model as mentioned above.

HELP! any suggestions?

Posted by Dan Wickstrom on
The behavior that you've shown through your example is normal.  The users_preferences table is being updated by the "guess_email_pref_tr" trigger in spam.sql.

This also seems to be the source of your registration problem.  The insert into the users_preferences table conflicts with the trigger.  This is a bug and it should be logged in the sdm.  For now you'll have to modify your config file, so that "users_preferences" is not a required user table.

Posted by Bob OConnor on
Thank you Dan.

I've reported it as Bug 890 in the SDM.