Forum OpenACS Q&A: Weirdness with composite primary keys

1: Weirdness with composite primary keys

Posted by Roberto Mello on 04/09/01 03:35 PM

Hi all,

I made a little app to record attendance to a db-backed websites
workshop I am teaching here. The problem I am seeing is that when I
try to insert a bunch of rows in a table with a composite primary_key
(attendee_id,session_id), PG returns saying I can't insert duplicate
rows in a unique index.

But I am not, and I am positive of that. The error in the log says I
can't insert (4,3) in the attendance table. But if I go to psql and
try the exact same Insert I am doing from the page, it goes through
just fine.

Could that be a driver issue? I am running PG 7.1 RC2.

2: Response to Weirdness with composite primary keys (response to 1)

Posted by Don Baccus on 04/09/01 04:19 PM

That's very weird, Roberto.

Are you using the new ns_pg_bind call to do the insert or just a plain, vanilla ns_db dml call?

3: Response to Weirdness with composite primary keys (response to 1)

Posted by Roberto Mello on 04/09/01 04:28 PM

Plain ns_db dml as I running this under OpenACS 3.2.x. At first I thought this could be a PG issue, but then it goes ok under psql. I am going to do some extra testing, but it was really annoying. You know the insertion is correct but it says it's not.

One thing here... I was inserting all rows (about 30) under a transaction. In psql I did outside a transaction.

4: Response to Weirdness with composite primary keys (response to 1)

Posted by Dan Wickstrom on 04/09/01 05:59 PM

If you did it inside a transaction, then checking in psql doesn't really tell you if the key already exists in the db. When you get the duplicate key insertion, the whole transaction is rolled back, so you wouldn't be able to tell if a duplicate was inserted. You need to look at all thirty inserts and make sure that you don't have a duplicate in the transaction. A good way to check is to run aolserver inside shell mode of emacs and search backwards for the duplicate insert.

5: Response to Weirdness with composite primary keys (response to 1)

Posted by Roberto Mello on 04/09/01 06:55 PM

I know I didn't have _any_ duplicate keys because I was inserting the results of another query, and I know the exact results of that query (one handle was getrow'ing and another was inserting). I looked at all the rows returned and there were no duplicate keys.

6: Response to Weirdness with composite primary keys (response to 1)

Posted by Dan Wickstrom on 04/09/01 07:55 PM

The best thing to do would be to try and create a stand-alone test case that reproduces the problem and post it here. I couldn't really suggest anything else based on the information that you've given so far.

7: Response to Weirdness with composite primary keys (response to 1)

Posted by Don Baccus on 04/09/01 08:01 PM

In particular, the driver uses the same client library that PSQL uses, and just passes the raw string to it if you're using vanilla ns_db dml. The bindvar emulator mangles the string in order to insert Tcl variable values, etc, which is why I asked if you were using the new form.

So, given that the driver just passes the querystring through the same client library that PSQL passes your querystring to, it *should* be possible to recreate the problem in PSQL...

It *should* be ... :)