Re: PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

On 27/07/16 06:11, David Fetter wrote:
> On Wed, Jul 27, 2016 at 03:24:28AM +0200, Vik Fearing wrote:
>> On 27/07/16 03:15, Peter Eisentraut wrote:
>>> On 7/26/16 6:14 PM, Vik Fearing wrote:
>>>> As mentioned elsewhere in the thread, you can just do WHERE true
>>>> to get around it, so why on Earth have it PGC_SUSET?
>>>
>>> I'm not sure whether it's supposed to guard against typos and
>>> possibly buggy SQL string concatenation in application code. So
>>> it would help against accidental mistakes, whereas putting a WHERE
>>> TRUE in there would be an intentional override.
>>
>> If buggy SQL string concatenation in application code is your
>> argument, quite a lot of them add "WHERE true" so that they can just
>> append a bunch of "AND ..." clauses without worrying if it's the
>> first (or last, whatever), so I'm not sure this is protecting
>> anything.
>
> I am sure that I'm not the only one who's been asked for this feature
> because people other than me have piped up on this thread to that very
> effect.

Sure. I'm just saying that I think it is poorly designed. I think it
would be far better to error out if the command affects x rows, or an
estimated y% of the table.

Doing that, and also allowing the user to turn it off, would solve the
problem as I understand your presentation of it.

> I understand that there may well be lots of really meticulous people
> on this list, people who would never accidentally do an unqualified
> DELETE on a table in production, but I can't claim to be one of them
> because I have, and not just once. It's under once a decade, but even
> that's too many.

That doesn't mean that requiring a WHERE clause -- without even looking
at what's in it -- is a good idea.

Why not start by turning off autocommit, for example?

> I'm not proposing to make this feature default, or even available by
> default, but I am totally certain that this is the kind of feature
> people would really appreciate, even if it doesn't prevent every
> catastrophe.

This kind of feature, why not. This feature, no.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support