Re: automatic restore point

On Mon, 2 Jul 2018 at 20:07, Yotsunaga, Naoki
<yotsunaga(dot)naoki(at)jp(dot)fujitsu(dot)com> wrote:
>
> 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.
>

Thinking on Michael's suggestion of using event triggers, you can
create an event trigger to run pg_create_restore_point() on DROP,
here's a simple example of how that should like:
https://www.postgresql.org/docs/current/static/functions-event-triggers.html

You can also create a normal trigger BEFORE TRUNCATE to create a
restore point just before running the TRUNCATE command.

Those would run *on the background* (you don't need to call them
manually), you can use them right now, won't affect performance for
people not wanting this "functionality".

BTW, Michael's suggestion also included the idea of recording
xid/time/lsn which can be done through triggers too

--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services