Re: PostgreSQL Write Performance

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