Re: A problem with sequences...

On Wednesday 19 Feb 2003 11:05 pm, Dmitry Tkach wrote:
> 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;

> *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');

> 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
^^^^^^^^^^^^^^^^^^

Before looking into more obscure possibilities, are you using the sequence to
generate these explicit id's? If not, that's why you're getting duplicates,
the sequence generator doesn't know you've used these numbers.

> 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.

--
Richard Huxton