Thread: RFC: User reviews of PostgreSQL RI functionality

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).

Regards,
Ed Loehr

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)

> I was wondering if you could tell me where to get the CVS version
of
> the ODBC driver -- I have almost implemented foreign key support
> (SQLForeignKeys), but I did it on the ODBC source available on the
> postgres server.  I browsed the postgres CVS repository via the
web,
> but couldn't find anything relevant....

It's *possible* that if you're programming directly to the ODBC API
that you've always seen the RI stuff. I do my work in VBA apps (like
Access), and I know that they don't see the RI errors.

You can find the ODBC source in the pgsql source, at
src/interfaces/odbc (or something *very* close, no source in front
of me right now, sorry).

If you don't want to grab the whole file just for ODBC stuff, you can
use CVS to just get the contents of the src/interfaces/odbc
directory. The directions say you need to use VisualC++ to compile
this.

Via ftp, that's in the /dev directory.


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