A problem with sequences...

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 on here,
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 reading because 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 still null (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 an id 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 circumstances are)
I get a weird exception from java, complaining about at attempt to insert a duplicate into answer_pkey after executing a statement 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 value if
two connections happen to insert into the same table simultaneously), but still - I want to understand how does it manage to 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 problem go away
(it shows up pretty rarely, and I cannot reproduce it on purpose, so I have no way to verify that, other than figuring out exactly 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 statement would 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 accessed previously
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', and having
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 either way (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!