pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
RFC: User reviews of PostgreSQL RI functionality 3+ messages / 2 participants [nested] [flat]
* RFC: User reviews of PostgreSQL RI functionality @ 2000-12-01 18:00 Ed Loehr <eloehr@austin.rr.com> 0 siblings, 1 reply; 3+ messages in thread From: Ed Loehr @ 2000-12-01 18:00 UTC (permalink / raw) To: pggeneral <pgsql-general@postgreSQL.org> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: RFC: User reviews of PostgreSQL RI functionality @ 2000-12-01 20:12 Joel Burton <jburton@scw.org> parent: Ed Loehr <eloehr@austin.rr.com> 0 siblings, 1 reply; 3+ messages in thread From: Joel Burton @ 2000-12-01 20:12 UTC (permalink / raw) To: Ed Loehr <eloehr@austin.rr.com>; +Cc: pgsql-general@postgresql.org 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) ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: RFC: User reviews of PostgreSQL RI functionality @ 2000-12-01 20:52 Joel Burton <jburton@scw.org> parent: Joel Burton <jburton@scw.org> 0 siblings, 0 replies; 3+ messages in thread From: Joel Burton @ 2000-12-01 20:52 UTC (permalink / raw) To: Michael Fork <mfork@toledolink.com>; +Cc: pgsql-general@postgresql.org; pgsql-interfaces > 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) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-12-01 20:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-12-01 18:00 RFC: User reviews of PostgreSQL RI functionality Ed Loehr <eloehr@austin.rr.com> 2000-12-01 20:12 ` Joel Burton <jburton@scw.org> 2000-12-01 20:52 ` Joel Burton <jburton@scw.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox