Re: A problem with sequences...

On Thursday 20 Feb 2003 2:56 pm, Dima Tkach wrote:
> >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.
>
> Yeah... I understand this.
> In this case, the id is *never* specified explicitly. Java app either
> knows the id or it does not.
> If it knows it, it does the update, otherwise, it does an insert, with
> *no* id specified, and gets the new id back from that rule, so that time
> it will
> know the id and end up doing update...
>
> I was referring to the situation in general when somehow (like data
> migration) the id is specified, I just don't want that rule to barf. It
> is definitely not what's causing my problem. So, let's get into those
> 'obscure possibilities' now :-)

Well, in that case you need to setval() the sequence to something bigger than
any used numbers after the import.

OK - let's look at the rule:

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;

Well - you're going to have problems if you do something like:

INSERT INTO answer (data) (SELECT d FROM foo);

I daresay you're not, but something to bear in mind.

To see why you're getting problems with duplicate ID numbers, open two psql
windows and do:

1> SELECT nextval('answer_id_seq');
2> SELECT nextval('answer_id_seq');
1> SELECT last_value FROM answer_id_seq;
2> SELECT last_value FROM answer_id_seq;

As you'll see, last_value isn't concurrent-safe like currval() and nextval()
are.

So - if you want to keep your rule, you'll want to rewrite it to use currval()
as you mentioned.

Personally, I'd write a function to insert into the table and make the app use
that, or create a view and have the app insert via that. Getting a result-set
back from an insert would spook me if I wasn't expecting it.
--
  Richard Huxton