Forum OpenACS Q&A: Response to Postgresql duplicate Key problem

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?