RE: automatic restore point

Hi. Thanks for comments.

Explanation of the background of the function proposal was inadequate.
So, I explain again.

I assume the following situation.
User needs to make a quick, seemingly simple fix to an important production database. User composes the query, gives it an once-over, and lets it run. Seconds later user realizes that user forgot the WHERE clause, dropped the wrong table, or made another serious mistake, and interrupts the query, but the damage has been done.
Also user did not record the time and did not look at a lsn position.

Certainly, I thought about reducing the possibility of executing the wrong command, but I thought that the possibility could not be completely eliminated.
So I proposed the “automatic restore point”.
With this function, user can recover quickly and reliably even if you perform a failure operation.

> I'd rather spend effort making the initial execution of said commands less likely.
I think that the function to prohibit DELETE and UPDATE without a WHERE clause in the later response is good way.
But I think that it is impossible to completely eliminate the failure of the other commands.
For example, drop the wrong table.

-----
Naoki Yotsunaga

-----Original Message-----
From: Michael Paquier [mailto:michael(at)paquier(dot)xyz]
Sent: Tuesday, June 26, 2018 2:16 PM
To: Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; Yotsunaga, Naoki/四ツ永 直輝 <yotsunaga(dot)naoki(at)jp(dot)fujitsu(dot)com>; Postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: automatic restore point

On Mon, Jun 25, 2018 at 11:01:06PM -0400, Isaac Morland wrote:
> I think an optional setting making DELETE and UPDATE without a WHERE
> clause illegal would be handy. Obviously this would have to be
> optional for backward compatibility. Perhaps even just a GUC setting,
> with the intent being that one would set it in .psqlrc so that
> omitting the WHERE clause at the command line would just be a syntax
> error. If one actually does need to affect the whole table one can
> just say WHERE TRUE. For applications, which presumably have their SQL
> queries tightly controlled and pre-written anyway, this would most likely not be particularly useful.

There was a patch doing exactly that which was discussed last year:
https://commitfest.postgresql.org/13/948/
https://www.postgresql.org/message-id/20160721045746.GA25043@fetter.org
What was proposed was rather limiting though, see my messages on the thread. Using a hook, that's simple enough to develop an extension which does that.
--
Michael