PostgreSQL Insert Problem - Prepared Statement - TZQuery

[jwhitten]

Hello, I recently upgraded Lazarus to 1.3 (from SVN) and Zeoslib to 7.1.2-stable, and I've started having problems with inserting new records. My tables all have SERIAL datatype primary keys and I have been using TZQueries for interacting with the database. My setup is very simple. I use a TZConnection to connect to the database (on another server, plain jane setup, nothing fancy-- been working fine for several months). I use TZQuery to pull a record, DB-aware controls/forms to manipulate the data, and then do a query Post to write it. However, ever since I upgraded to 7.1.2-stable, I have been having errors inserting new records. Updating existing ones is fine. When I use the event logger, I see the problem seems to be in the Prepare Statement portion. It almost prepares the statement correctly, but it doesn't omit the primary key-- the SERIAL datatype key I mentioned above. So when it goes to insert the record, its getting a "Duplicate Key" error.

Either this used to work before, or else I was getting lucky with the keys or something. Have you all made changes?
Here is a (sanitized) exerpt from the logs:

Code: Select all

2013-10-29 17:13:55 cat: Execute prepared, proto: postgresql-9, msg: Statement 1
2013-10-29 17:14:00 cat: Execute, proto: postgresql-9, msg: SELECT false
2013-10-29 17:14:00 cat: Prepare, proto: postgresql-9, msg: PREPARE 'INSERT INTO mydatabase.mytable (myrecord_id,"name",[...fields omitted...],fgcolor,bgcolor) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)'
2013-10-29 17:14:00 cat: Execute prepared, proto: postgresql-9, msg: INSERT INTO mydatabase.mytable (myrecord_id,"name",[...fields omitted...],fgcolor,bgcolor) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?), errcode: 0, error: ERROR: duplicate key value violates unique constraint "pk_mytable"
DETAIL: Key (myrecord_id)=(5) already exists.

If you'll notice, it doesn't omit the key ('myrecord_id') from the query, so it tries to insert the record with a primary key value intact.

Now, the first logical question that should come to mind is, If this is a NEW record, where is it getting that key value from anyway? And then the second question is, why is it including the key for the insert when its a SERIAL key?

I've tried it many times and as near as I can tell, the value of the myrecord_id is either random, or else generated internally by the TZQuery component. But the numbers it has come up with are not always consecutive, though they are also not widely spread out either. So I don't know if there is some sort of pattern to it or not.

Everything seems to work in all other respects except for this one. I can retrieve data, modify data, and put it back (update data) without any problems. It is only when I try to insert a new record that I have a problem. (I haven't tried deleting any, thus far).

I'm sure I could hand-craft the Insert query myself, but I'd rather not. The queries are all simple and I'm pretty sure this used to work-- or else I was getting lucky, as I speculated above.

I opened a ticket on this at Sourceforge [https://sourceforge.net/p/zeoslib/tickets/53/]. I'm not sure if this is a bug in the software or a misunderstanding on my part for how to use it.

Any thoughts?

Thanks,

John Whitten

[EgonHugeist]

@jwhitten

Zeos silently determines the next serial-id and adds them to the Paramaters. First i was thinking the column "name" makes trouble because it's never a good idea to use keywords as identifers.

Actually all approches to reproduce your issue do fail. Dos it happen allways or is there a multi-user environment with uncommited changes in another session?

[jwhitten]

Thanks for the reply.

I figured out what the problem was last night. I was pulling my hair out trying to figure out why it wasn't working. I went through the source back and forth-- and then following it line by line as I was stepping through with the debugger. I saw where it has a field to provide the name of the sequence and I filled that in-- and that's when it dawned on me what the issue really was-- while I was setting up the database table, I didn't want to mess with the "real" data so I set up a copy of the table. Then when I was happy, I copied everything from the test table to the real table-- and of course a straight copy doesn't update the sequence values. So I manually adjusted the sequence to point to the next highest value in the table and-- Voila! Lightbulb moment... everything started working correctly after that. :-)

So note to self... always remember to check the sequence next values!!

You can close this ticket as resolved. It turned out to be an "Operator Headspace" error

Thanks,

John