Tim Uckun wrote: >> I, for one, would loudly and firmly resist the addition of such a >> feature. Almost-as-fast options such as intelligent re-checking of > > Even if it was not the default behavior? Even if it was called COPY (PLEASE BREAK MY DATABASE) FROM ... ... because there are *better* ways to do it that are safe for exposure to normal users, and existing ways to do it the dangerous way if you really want to. > I guess it's a matter of philosophy. I kind of think as the DBA I > should be the final authority in determining what is right and wrong. > It's my data after all. Yes I would expect pg to perform every check I > specify and execute every trigger I write but if I want I should be > able to bypass those things "just this once". > > As you point out I can already do this by manually going through and > disabling every trigger or even dropping the triggers. You could alternately dynamically query pg_catalog and use PL/PgSQL (or SQL generated by your app) to issue the appropriate statements to control the triggers. > Many people > have said I could drop the constraints and re-set them up. The fact > that the COPY command does not have a convenient way for me to do this > doesn't prevent me from "shooting myself in the foot" if I want to. I think that it would be desirable for COPY to provide a convenient way to drop and re-create constraints, or (preferably) just disable them while it ran then re-check them before returning success. Think: COPY (BATCH_RI_CHECKS) FROM ... The thing you might have missed is that dropping and re-creating constraints is different to disabling them (as you're requesting that COPY do). When the constraints are re-created, the creation will *fail* if the constraint is violated, aborting the whole operation if you're sensible enough to do it in a single transaction. At no point can you end up with a constraint in place promising RI that is in fact violated by the data. By contrast, if you disable triggers and constraints, re-enabling them does *not* re-check any constraints. So it's much, MUCH more dangerous, since it can let bad data into the DB silently. So if you disable constraints you MUST re-check them after re-enabling them and abort the transaction if they're violated, or must be utterly certain that the data you inserted/altered/deleted was really safe. > It would just be a flag. If you want you can enable it, if you don't > they no harm no foul. Unfortunately my experience has been that many users (a) often don't read documentation and (b) just try different things until something they do makes the error "go away". They then get five steps down the track and post a question about a query that doesn't work correctly (because they broke their data) and it takes forever to get to the bottom of it. You're clearly a fairly experienced and responsible DBA who'd look something up before using it and would be careful to preserve RI manually in these situations. Some people who use PG aren't (an amazing number of them just installed it to run their poker card counting software!), and I don't think it's wise to make dangerous things *too* obvious. They need to be there and available, but not staring you in the face. I don't advocate the GNOME philosophy of "make it impossible if it's not suitable for a user who's using a computer for the first time" ... but I don't like the "nuke my data" button to be on the default desktop either ;-) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
