A problem with sequences... - Mailing 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!