Обсуждение: OIDs in triggers

Hi folks,

Here's what I have:

create table contact (
    contact_id serial,
... <skipped fields>
    primary key    (contact_id)
);

create table customers (
    customer_id serial,
    shipping_contact_id int4,
    billing_contact_id int4,
... <skipped fields>

    primary key (customer_id)
);

Well, I want to write a trigger on CUSTOMERS that will insert 2 records into table CONTACT and
link it to fields shipping_contact_id and billing_contact_id in CUSTOMERS. But how, if I won't know
oids of inserted rows ? I may remember sequence's nextval in variable and use it in both insert and update
statements, but during many insert in contact table sequence's nextval may be used by
concurrent transactions, so one of them will be rolled back.

I prefer to do it with PL/pgSQL, anyway I can't use C.

Any ideas ?

Thanks,
Max Rudensky.

On Sat, 17 Feb 2001, Max Rudensky wrote:

> Hi folks,
>
> Here's what I have:
>
> create table contact (
>     contact_id serial,
> ... <skipped fields>
>     primary key    (contact_id)
> );
>
> create table customers (
>     customer_id serial,
>     shipping_contact_id int4,
>     billing_contact_id int4,
> ... <skipped fields>
>
>     primary key (customer_id)
> );
>
> Well, I want to write a trigger on CUSTOMERS that will insert 2 records into table CONTACT and
> link it to fields shipping_contact_id and billing_contact_id in CUSTOMERS. But how, if I won't know
> oids of inserted rows ? I may remember sequence's nextval in variable and use it in both insert and update
> statements, but during many insert in contact table sequence's nextval may be used by
> concurrent transactions, so one of them will be rolled back.
>
> I prefer to do it with PL/pgSQL, anyway I can't use C.

You shouldn't ever have two transactions getting the same value from
nextval on the sequence.  It should be guaranteed to be unique, so getting
the currval after each insert or nextval before should work.

Note that serial does not guarantee that the number sequence you get will
not have holes just that they are unique.