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

On Wed, Jul 27, 2016 at 02:59:17PM +0200, Vik Fearing wrote:
> 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.

What else would constitute a good design?

I am a little wary of relying on estimates, at least those provided by
EXPLAIN, because the row counts they produce can be off by several
orders of magnitude.

Are there more accurate ways to estimate?

Would you want x and y to be parameters somewhere?

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

I made it PGC_USERSET in the third patch.

> > 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?

Because that setting is client side, and even more vulnerable to not
being turned on for everyone everywhere.

> > 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.

I would very much value your input into the design of the feature.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate