Forum OpenACS Q&A: Response to Postgresql duplicate Key problem
Ok, I've pounded away at this problem and here are MORE details:
I go into /parameters/myserver.tcl and comment out
# 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','firstname.lastname@example.org','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 126.96.36.199)
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?