A problem with sequences... - Mailing list pgsql-general
From | Dmitry Tkach |
---|---|
Subject | A problem with sequences... |
Date | |
Msg-id | b30ssj$1nr6$1@news.hub.org Whole thread Raw |
Responses |
Re: A problem with sequences...
|
List | pgsql-general |
Hi, everybody! I am experiencing some weird problem that I was hoping you could shed some light on... This is a little complicated, and, I bet the first thing I am going to hear from you is "change your schema" :-) I will, most probably, end up doing that, but before I get into it, I would like to understand completely what is going onhere, so, please, bear with me for a while. The background is, that I have some tables, that are being populated by a java application through jdbc (don't stop readingbecause of that - it is very unlikely to matter that the stuff goes through jdbc!) The java app wants to get back the id (pk) of the rows it inserts, and it does not want to make a separate query for that. We use rules to achive that. For example: create table answer ( id serial primary key, data text ); create rule answer_id_seq as on insert to answer do select coalesce (new.id, last_value) as id from answer_id_seq; (for those who wonders why I did it this way, and not using currval, I'll explain it in the end). I was surprised to find out that at the time rule is executed, the sequence is already advanced, but the new.id is stillnull (looks like the defaults just never make it into the new.* at all), but apparently, that is the case. So, with this setup my java app is able to execute a statement that inserts a new row, get back a ResultSet and fetch anid from it. And it works just fine most of the time. *However*, every now and then (and I was never able to reproduce it on purpose, so I don't know what exactly the circumstancesare) I get a weird exception from java, complaining about at attempt to insert a duplicate into answer_pkey after executing astatement like insert into answer (data) values ('blah'); So, it looks like my rule somehow manages to screw up the sequence. Does it make sense to anyone? How could it happen? I understand that this rule is no good, and I need to fix it (because it could return an incorrect valueif two connections happen to insert into the same table simultaneously), but still - I want to understand how does it manageto screw up that sequence just by *looking* at it??? The reason I want to understand what is going on here is to be sure that, by fixing this rule, I will really get this problemgo away (it shows up pretty rarely, and I cannot reproduce it on purpose, so I have no way to verify that, other than figuring outexactly what is going on). Does all this make any sense to any of you? Can you imagine some situation when with a setup like this an insert statementwould get a value from the sequence that was already used? I would greatly appreciate any help. Thanks a lot! Dima. P.S. As I promised, the reason that rule is not using currval is just that it may or may not be set for a given insert statement- if the id is explicitly specified, a call to currval would fail (or even return a wrong value if the sequence was accessedpreviously in the same session)... Perhaps, I could work around that, by creating two rules - with 'where new.id is null' and 'where new.id is not null', andhaving the first one use currval, and the other one just return new.id, but it just happened to be done this way... As I said above, I understand that there are all kinds of problems with this rule, and I am going to get rid of it eitherway (I am thinking, about just making a change on the java side and appending ';select currval(..)' to those inserts). However, I would still love to understand what exactly goes on with those duplicate ids, right now. Thanks again!
pgsql-general by date: