Tim Uckun wrote: >> Technically you *can* disable triggers, including RI checks, but it's VERY >> unwise and almost completely defeats the purpose of having the checks. In >> most such situations you're much better off dropping the constraints then >> adding them again at the end of the load. > > > I know that the SQL server bulk loader defaults to not firing the > triggers and I was hoping there would be an option on the COPY command > to accomplish the same thing. > > pg_dump has a --disable-triggers option too. pg_restore? Yes, it does. However, it knows that the data you're loading came from a PostgreSQL database where those triggers have already fired when the data was originally inserted. It can trust that the data is OK. I don't personally think that COPY should make it easy to disable triggers. You can do it if you want to (see the manual for how) but in almost all cases its much wiser to drop triggers and constraints instead. Rather than disabling RI constraints, it'd be desirable for COPY to have an option that *deferred* RI constraint checking, then re-checked the constraints for all rows at once and rolled back the COPY if any failed. That'd be a lot faster for many uses, but (importantly) would preserve referential integrity. While COPY doesn't offer an easy way to do that, you can emulate the behavior by: - Beginning a transaction - Disabling RI constraints - Running COPY - Re-enabling RI constraints - Re-checking the RI constraints and raising an exception to abort the transaction if the checks fail. Unfortunately you can't (yet) do this with deferrable RI constraints, because Pg isn't clever enough to notice when large numbers of individual checks have built up and merge them into a single re-check query that verifies the whole constraint in one pass. So you have to do that yourself. > It seems to me that the COPY FROM should have an option that bypasses > the triggers as a convience. I, for one, would loudly and firmly resist the addition of such a feature. Almost-as-fast options such as intelligent re-checking of deferred constraints would solve the problem better and much more safely. If you really want the dangerous way you can already get it, it's just a bit more work to disable the triggers yourself, and by doing so you're saying "I understand what I am doing and take responsibility for the dangers inherent in doing so". If you really want to do that, look at the manual for how to disable triggers, but understand that you are throwing away the database's data integrity protection by doing it. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
