Обсуждение: Object Relational, Foreign Keys and Triggers

I am facing the classic pgsql ORDBMS problem:

create sequence entity_id_seq;
create table entity (
  entity_id int not null default nextval('entity_id_seq'),
  primary key (entity_id)
);

create table person (
  first_name varchar(32) not null,
  last_name varchar(32) not null,
  primary key (entity_id)
) inherits (entity);

create sequence entity_phone_id_seq;
create table entity_phone (
  entity_phone_id int not null default nextval('entity_phone_id_seq'),
  entity_id int not null,
  phone varchar(32),
  constraint entity_phones_entity_id_fk foreign key (entity_id)
    references entity on delete cascade,
  primary key (entity_phone_id)
);

-- Insert the person
insert into person (first_name,last_name) values ('Alex','Turner');
-- Attempt to insert phone
insert into entity_phone select max(entity_id), '610 495 5000' from person;


Insert fails with a foreign key constraint error because entity_phone
points to entity, not person, and the rows aren't physicaly in entity,
they are in person.

Two questions:
1) Why can't we make this work the 'right' way - not doing so either
breaks OO or brakes RDBMS.  1)a) Whats the point of an RDBMS if you
can't specify foreign keys that work because you choose to use OO
features (I somewhat appreciate that there is a trigger inheritance
problem, can't we just define the rules and order of precident and
solve it)?

2) Whats the best way to manage this with triggers.  Obviously one can
create a trigger on entity and on person for delete so that it removes
corresponding rows in entity_phone.  But whats the best way to create
a trigger that ensures that entity_ids that are used in entity_phone
exist in entity and it's subtables thats fast.  You could do:

select into foo where entity_id=NEW.entity_id from entity;
if not found then
  raise exception 'Value for entity_id not found in entity';
endif;
-- is this the fastest way?

currently using 7.4.5 on RHEL3, but will be upgrading to 8.0 this week.

Alex Turner
NetEconomist

On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> I am facing the classic pgsql ORDBMS problem:

<snip>

Why are you using MAX()? That won't work at all. Perhaps you need to
look up the documentation for nextval and currval. In particular, that
second query should be:

insert into entity_phone select currval('entity_id_seq'),'610 495 5000';

Also, I'm not sure if inheritance works quite the way you think in the
example you give, though other people may correct me on that.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Actualy max() works just fine.  It's not the solution I use in the
middle tier, but it works for a functional example.  both max() and
currval() are bad because they can cause a race condition where the
sequence has been incremented by another thread.  It's always better
to get nextval('sequence') and store it in a local var, then use it in
the main insert and corresponding sub-inserts.

The example I give has been tested, and works, it's not fake.

Alex Turner
NetEconomist

On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
<kleptog@svana.org> wrote:
> On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> > I am facing the classic pgsql ORDBMS problem:
>
> <snip>
>
> Why are you using MAX()? That won't work at all. Perhaps you need to
> look up the documentation for nextval and currval. In particular, that
> second query should be:
>
> insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
>
> Also, I'm not sure if inheritance works quite the way you think in the
> example you give, though other people may correct me on that.
>
> Hope this helps,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
>

On Mon, 24 Jan 2005, Alex Turner wrote:

> Insert fails with a foreign key constraint error because entity_phone
> points to entity, not person, and the rows aren't physicaly in entity,
> they are in person.
>
> Two questions:
> 1) Why can't we make this work the 'right' way - not doing so either
> breaks OO or brakes RDBMS.  1)a) Whats the point of an RDBMS if you
> can't specify foreign keys that work because you choose to use OO
> features (I somewhat appreciate that there is a trigger inheritance
> problem, can't we just define the rules and order of precident and
> solve it)?

There are multiple problems involved mostly due to the fact that
inheritance really need alot of work. For example, the actual
implementation of the schema you gave has no interlock to prevent
duplicate rows in person and entity (or another entity subclass). The
primary key implementation also only guarantees local uniqueness.
Inheritance really needs some developers who care strongly about it.

> 2) Whats the best way to manage this with triggers.  Obviously one can
> create a trigger on entity and on person for delete so that it removes
> corresponding rows in entity_phone.  But whats the best way to create
> a trigger that ensures that entity_ids that are used in entity_phone
> exist in entity and it's subtables thats fast.  You could do:

There's been discussion about this in the past, so you can get details
from the archives, but using a separate table to store the ids with
triggers between entity and person and the new table which manage the id
list has been proposed as a workaround.

On Tue, Jan 25, 2005 at 08:36:53AM -0500, Alex Turner wrote:
> Actualy max() works just fine.  It's not the solution I use in the
> middle tier, but it works for a functional example.  both max() and
> currval() are bad because they can cause a race condition where the
> sequence has been incremented by another thread.  It's always better
> to get nextval('sequence') and store it in a local var, then use it in
> the main insert and corresponding sub-inserts.

Like I said, read the docs. currval was explicitly created to avoid the
race condition. It gives you the last number handed out in *this*
connection. It's also a lot faster than max. So different connections
get a different currval() and you get an error if you've not called
nextval() in the current connection (it works across transactions).

Storing in a var works too, but currval is totally safe.

Have a nice day,

> On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
> <kleptog@svana.org> wrote:
> > On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> > > I am facing the classic pgsql ORDBMS problem:
> >
> > <snip>
> >
> > Why are you using MAX()? That won't work at all. Perhaps you need to
> > look up the documentation for nextval and currval. In particular, that
> > second query should be:
> >
> > insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
> >
> > Also, I'm not sure if inheritance works quite the way you think in the
> > example you give, though other people may correct me on that.
> >
> > Hope this helps,
> > --
> > Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Am Dienstag, den 25.01.2005, 08:36 -0500 schrieb Alex Turner:
> Actualy max() works just fine.  It's not the solution I use in the
> middle tier, but it works for a functional example.  both max() and
> currval() are bad because they can cause a race condition where the
> sequence has been incremented by another thread.  It's always better
> to get nextval('sequence') and store it in a local var, then use it in
> the main insert and corresponding sub-inserts.

No, thats wrong. If you read the documentation again on that matter, you
will see. currval() works on at least one nextval() in the connection
you are running and therefore keeps showing the last result of nextval()
in this very connection - no matter what other connections/sessions do.
Thats the whole point of sequences anyway.

HTH
Tino