Thread: table inheritance and foreign keys
I'd appreciate some advice regarding the use of foreign keys in
inherited tables. Specifically, here's the schema I'd like:
A S
-------- --------
id serial pkey <---1:1---> id integer references A(id)
name text +-/
|
A1 |
-------- |
(inherits A) <--1:1-+
id serial
C1 text
This doesn't work because the foreign key constraint can only bind to
one table. In other words, I'd like S.id to reference the union of id
keys from A and A1. This is analogous to the union select generated by
'select ... from A'.
Is there a way to achieve the same end by a different design (with
reasonable effort)? The best I can think of is to make A1 a distinct
table joinable on id, and then create a view with insert rule which
separately inserts into A and A1.
I've used table inheritance extensively and find it quite useful.
Extending the OO abstraction to a table's indices would enable even more
concise and expressive designs.
Thanks,
Reece
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
On Thu, 2003-05-15 at 10:07, Reece Hart wrote: > I'd appreciate some advice regarding the use of foreign keys in > inherited tables. Specifically, here's the schema I'd like: Ok, you can't do it with native postgres RI stuff. I'm told that the subject has been debated extensively, but right now it can't be done. I had to do the same thing as you and the way I've gone about it is to write triggers in plpython that raise an exception if RI is not maintained. I have a table that lists all the "x references y" pairs, and it just reads that and does the query to see if it can find a row in the "y" table with the right ID. It was working quite well (within my limited testing) until I hit a but in plpython last night. Said bug is on my todo list to take a look at, but that won't be happening imediately. You'll find you can't write this stuff easily in plpgsql, and I don't recommend plpython right now because I don't trust it at this point. You might have some luck writing similar triggers in C. I'm happy to send you my python triggers if you'd like something to start from, but be warned: they're a bit of a mess. HTH James.
James- Thanks for your advice. I think I'll opt for the following approach instead, but I'm interested to hear of any pitfalls you foresee. Subtables (for which I wanted to use inherit) will instead contain only the subtable-specific columns, and in addition contain a foreign key into the parent table. A view with insert, update, and delete rules can emulate the appearance of a full subtable (ie, inherited and subtable-specific columns). The upside I see over triggers is that the referential integrity is still on postgresql's back. The downside is that I end up with more tables and views to maintain and an extra join in many cases. Again, thanks for your advice. Do you see any reason that the above sketch wouldn't work to emulate foreign keys into inherited tables? -Reece -- Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/ Genentech, Inc. 650/225-6133 (voice), -5389 (fax) Bioinformatics and Protein Engineering 1 DNA Way, MS-93 http://www.in-machina.com/~reece/ South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0
On Sat, 2003-05-17 at 02:23, Reece Hart wrote: > James- > > Thanks for your advice. I think I'll opt for the following approach > instead, but I'm interested to hear of any pitfalls you foresee. > > Subtables (for which I wanted to use inherit) will instead contain only > the subtable-specific columns, and in addition contain a foreign key > into the parent table. A view with insert, update, and delete rules can > emulate the appearance of a full subtable (ie, inherited and > subtable-specific columns). > > The upside I see over triggers is that the referential integrity is > still on postgresql's back. The downside is that I end up with more > tables and views to maintain and an extra join in many cases. Heh, that's quite clever. I must admit I didn't even consider that. You're right about it requiring lots of views, but my solution requires lots of triggers. There's not much difference there, but it's more visible when you forget to make a view - if I forget a trigger I probably won't see it until the next audit of the database. I decided to chase the "real inheritance" angle rather than looking for alternatives such as this because I figured it was likely to be more future compatible - I was hoping that either the postgres developers would add inherited RI functionality or I would get time to implement it myself (which I started doing). Of course, the RI in place atm is done with triggers, so a C module to do it, along with some clever code to actually add the triggers would allow it to live in contribs and not sacrifice too much convenience. > > Again, thanks for your advice. Do you see any reason that the above > sketch wouldn't work to emulate foreign keys into inherited tables? I think it's a good way to proceed with the current version of postgresql. James.
Hi,
Let me re-post an issue that I had posted to pgsql-bugs@postgresl.org almost 2
years ago. I got no response at all to that query, and meanwhile, the issue
has perhaps become more important because of all the furore in the Enterprise
Java world on the best way to persist objects.
Postgres has the ability to make that entire argument go away by providing
true object capability in the data store itself, so there's no need to
"persist" an object in memory. Objects are already persistent. If you write
your entire application logic as stored procedures, then the database itself
becomes your app server! Writing a thin Java layer that maps class attributes
and methods to database columns and procedures is then almost trivial.
Regards,
Ganesh Prasad
Here's the problem (basically that polymorphism isn't implemented):
----------------------------------------------------------------------------------------------
(10 July 2001)
Let's try to use polymorphism in PostgreSQL. This could be quite a useful
feature in many situations.
We create two tables, one inheriting from the other, then define functions
with the same name
on both of them, but with different internal logic. When we call the
function on all records of
the parent class, we want the subclass's variant to be called for the
records corresponding to
the subclass.
However, the syntax of function definitions for a table expects the table
name to be passed to the
function as an argument. Therefore, it does not seem possible to
transparently call different
functions depending on the class of a record.
Is there a way to do this? Is the syntax described in this example wrong?
Create a class "employee" with key "id" and attributes "name" and "salary".
Define a function
"getTax()" that calculates tax as 20% of salary. Insert a record into it.
Now create a subclass of "employee" called "manager" with one extra
attribute "dept", and insert
a record into it. Define a function "getTax()" for "manager" that calculates
tax as 25% of salary.
(Let managers pay more tax ;-)
If we call "getTax()" on all records of "employee", we want regular
employees to be shown taxed at 20%,
but managers taxed at 25%. That is polymorphic behaviour.
However, we are only able to invoke the employee version of getTax() on the
employee table. Application
of the manager version is only possible (explicitly) on the manager table.
STEPS:
1. Run the script "setup.sql". This creates the two tables, populates them
with a record each, and
creates the "getTax()" functions.
2. Run the script "test1.sql". This invokes the "getTax()" method on the
"employee" table for
all records. Only the employee version is called.
gettax
--------
20000
20000
(2 rows)
This is not what we want. We want "test1.sql" to return the following result
(i.e. applying
the manager variant of getTax() to the manager record):
gettax
--------
20000
25000 <-- Manager variant of getTax() should be used here
(2 rows)
3. Run the script "test2.sql". This explicitly invokes the "getTax()" method
on the "manager" table
for all records corresponding to the child class (manager). This calls the
manager variant.
gettax
--------
25000
(1 row)
This is correct, but we shouldn't have to call this variant explicitly.
Polymorphism should
cause it to be called implicitly.
------ Start of scripts --------
setup.sql:
----------
drop function getTax( t_employee );
drop function getTax( t_manager );
drop table t_manager;
drop table t_employee;
/*
Employees have an id (key), a name and a salary.
*/
create table t_employee
(
id int4 primary key,
name varchar(50) not null,
salary float8
);
/*
Managers are employees who manage a department.
*/
create table t_manager
(
dept char(2)
)
inherits (t_employee);
/*
An ordinary employee.
*/
insert into t_employee
values
(
1,
'Joe Bloggs',
100000.0
);
/*
A manager.
*/
insert into t_manager
values
(
2,
'John Doe',
100000.0,
'HR'
);
/*
A "method" defined for the "employee" class,
which should be inherited by the "manager" class.
*/
create function getTax( t_employee ) returns float8 as '
declare
emp alias for $1;
begin
return emp.salary * 0.2;
end;
' language 'plpgsql';
/*
A "method" defined for the "manager" class,
which should override that defined for the
"employee" class.
*/
create function getTax( t_manager ) returns float8 as '
declare
mgr alias for $1;
begin
return mgr.salary * 0.25;
end;
' language 'plpgsql';
test1.sql:
----------
/*
Calculate tax for all employees.
*/
select getTax( t_employee ) from t_employee;
test2.sql:
----------
/*
The "method" for managers has to be explicitly called.
The "method" defined for managers should be implicitly called
even if the record is in the "employee" table.
*/
select getTax( t_manager ) from t_manager;
------ End of scripts --------