Re: RFC: User reviews of PostgreSQL RI functionality

On 1 Dec 2000, at 12:00, Ed Loehr wrote:

> How well is the recently-added PostgreSQL functionality supporting
> referential integrity (RI) working.  Any serious bugs?  Any major
> hinderances?  Other impressions?
>
> I'd be particularly interested in hearing from people who've
> implemented larger schemas using RI (say, more than 30 tables
and 50
> foreign keys).

We've been using a database w/85 tables, with about 60 pkey/fkey
restraints in place. The db has 8 users plus supports a dynamic web
site (having ~10 users at a time on the site). None of the tables are
very large (avg ~400 rows) except 3, which have ~65000 rows.

I haven't come across any real problems particular to RI in pgsql.
The constraints always seem to work; dumping and restoring works
fine (for RI), etc.

You can do some things that defeat RI--most importantly, if you
TRUNCATE a table, RI checks are never performed. However, this is
(IMHO) a good thing, as TRUNCATE is intended solely for DBA use,
and for DBAs, this means I can truncate a table, while ignoring any
related records, and reimport (via COPY or INSERT) the data, all w/o
disturbing any child relationships. This allows me to reconfigure a
table, delete columns, add other constraints, etc., in a database
w/o a full dump and restore. When pgsql has all the ALTER TABLE
DROP COLUMN, ALTER TABLE ALTER COLUMN commands finished,
this may be less important.

The current "stable" ODBC driver for Windows doesn't work perfectly
w/RI -- it doesn't report RI errors as an error. The RI rule is still
obeyed, however, to the ODBC client program, no error is reported,
so your user may never know that something went wrong. (This is
fixed in the CVS versions of the ODBC driver, and you can download
a binary compile from my site at www.scw.org/pgaccess.)

Be away, though, that the way RI is handled internally by pgsql that
when you dump a database and examine the dump, the RI
statements are now triggers and not nice clean REFERENCES tblFoo
ON fieldFOO DDL statements. It reimports perfectly, but is less self-
documenting.

In the tiny-wishlist department, I would like it if there were an easy
way to change the referential integrity behavior (delete, block,
etc.) for an existing relationship.


--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)