Serial/Autoincrement is jumping up by 2 every time on PgSQL

vfclists · Post by **vfclists** » 13.10.2010, 15:38

I am using ZQuery with ZUpdateSQL components and I realize that the serial autoincrement value is jumping up by 2 everytime. I suspect it may be due to some interaction between the ZQuery and PostgrSQL database.

I suspect it is an issue that has come before.

Any pointers?

/vfclists

mrLion · Post by **mrLion** » 13.10.2010, 16:42

vfclists, may be this link between Query and UpdateSQL use 2-stage transaction Commit??? Then serial field (generator) after 2 commit command really "jumping" ut to 2?
Check you code for update in EMC PostgreSQL Manager. Use for it tools that catch incoming SQL queries...

PS: I`m never use UpdateSQL.
PPS. Sory for my bad English

Wild_Pointer · Post by **Wild_Pointer** » 14.10.2010, 07:04

Hello, vfclists,

I've been using zeos + postgres for several years. I use ZUpdateSQL too, but I have never observed the behaviour you describe... I would suggest using TZSQLMonitor and see what statements are executed. Maybe you issue nextval('seq_name') manualy?

Also check if increment of the sequence is 1 in your DB.

Good Luck!

vfclists · Post by **vfclists** » 14.10.2010, 15:44

I was able to work out why.

It appears that zeoslib inspects the database and when it realizes that the column is a serial, it increments the nextval and copies it into the field.

but because my update query did not insert the serial field, expecting it to be set by the server, serial got incremented by again by the nextval.

Naturally the one generated by insert in zeoslib got discarded.

I worked around it by including the serial in the tzupdatesql insert statement and it got fixed.