Thread: [HACKERS] \set AUTOROLLBACK ON
Hi hackers, A colleague of mine wondered if there is a way to always run everything you type into psql in a db txn and automatically rollback it as soon as it finish. I couldn't think of any way to do so, but thought it would be a nice feature and probably quite easy to add to psql, so I thought I should suggest it here. The typical use-case is you are doing something in production that you just want to a) test if some query works like expected and then rollback or, b) read-only queries that should not commit any changes anyway, so here the rollback would just be an extra layer of security, since your SELECT might call volatile functions that are actually not read-only Thoughts? /Joel
On Mon, Jun 26, 2017 at 04:00:55PM +0200, Joel Jacobson wrote: > Hi hackers, > > A colleague of mine wondered if there is a way to always run > everything you type into psql in a db txn and automatically rollback > it as soon as it finish. > I couldn't think of any way to do so, but thought it would be a nice > feature and probably quite easy to add to psql, so I thought I should > suggest it here. > > The typical use-case is you are doing something in production that you > just want to > a) test if some query works like expected and then rollback > or, > b) read-only queries that should not commit any changes anyway, so > here the rollback would just be an extra layer of security, since your > SELECT might call volatile functions that are actually not read-only > > Thoughts? Multi-statement transactions: Would flavor of BEGIN TRANSACTION undo the feature? If not, would it auto-munge COMMIT into a ROLLBACK? Side effects: Let's imagine you have a function called ddos_the_entire_internet(message TEXT), or something less drastic which neverthelesshas side effects the DB can't control. How should this mode handle it? Should it try to detect calls to volatile functions, or should it just silently failto do what it's promised to do? 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
On Mon, Jun 26, 2017 at 04:00:55PM +0200, Joel Jacobson wrote:
> Hi hackers,
>
> A colleague of mine wondered if there is a way to always run
> everything you type into psql in a db txn and automatically rollback
> it as soon as it finish.
> I couldn't think of any way to do so, but thought it would be a nice
> feature and probably quite easy to add to psql, so I thought I should
> suggest it here.
>
> The typical use-case is you are doing something in production that you
> just want to
> a) test if some query works like expected and then rollback
> or,
> b) read-only queries that should not commit any changes anyway, so
> here the rollback would just be an extra layer of security, since your
> SELECT might call volatile functions that are actually not read-only
>
> Thoughts?
Multi-statement transactions:
Would flavor of BEGIN TRANSACTION undo the feature?
If not, would it auto-munge COMMIT into a ROLLBACK?
We already have SET TRANSACTION READ ONLY.
If you begin a transaction and do not issue an explicit commit when the session closes the default action is ROLLBACK.
At some point if you want to use SQL features you need to write SQL - not pass command-line arguments to the client.
See also ".psqlrc" and shell functions/aliases.
This doesn't seem like material to build into psql but since the proposal lacks an envisioned usage its hard to say conclusively. Interplay with the various ways to source SQL, and existing arguments, is a prime area of concern.
Side effects:
Let's imagine you have a function called
ddos_the_entire_internet(message TEXT), or something less drastic
which nevertheless has side effects the DB can't control.
How should this mode handle it? Should it try to detect calls to
volatile functions, or should it just silently fail to do what
it's promised to do?
It doesn't need to promise anything more than what happens today if someone manually keys in
BEGIN;
[...]
ROLLBACK;
using psql prompts.
David J.
On Mon, Jun 26, 2017 at 12:35:47PM -0700, David G. Johnston wrote: > On Mon, Jun 26, 2017 at 12:19 PM, David Fetter <david@fetter.org> wrote: > > > On Mon, Jun 26, 2017 at 04:00:55PM +0200, Joel Jacobson wrote: > > > Hi hackers, > > > > > > A colleague of mine wondered if there is a way to always run > > > everything you type into psql in a db txn and automatically rollback > > > it as soon as it finish. > > > I couldn't think of any way to do so, but thought it would be a nice > > > feature and probably quite easy to add to psql, so I thought I should > > > suggest it here. > > > > > > The typical use-case is you are doing something in production that you > > > just want to > > > a) test if some query works like expected and then rollback > > > or, > > > b) read-only queries that should not commit any changes anyway, so > > > here the rollback would just be an extra layer of security, since your > > > SELECT might call volatile functions that are actually not read-only > > > > > > Thoughts? > > > > Multi-statement transactions: > > > > Would flavor of BEGIN TRANSACTION undo the feature? > > If not, would it auto-munge COMMIT into a ROLLBACK? > > > > We already have SET TRANSACTION READ ONLY. Now there's an interesting and potentially fruitful idea. How about exposing GUCs to psql? That way, it'd be possible to put (some transformation of) them in the prompt, etc. > > Side effects: > > > > Let's imagine you have a function called > > ddos_the_entire_internet(message TEXT), or something less drastic > > which nevertheless has side effects the DB can't control. > > > > How should this mode handle it? Should it try to detect calls to > > volatile functions, or should it just silently fail to do what > > it's promised to do? > > > > It doesn't need to promise anything more than what happens today if > someone manually keys in > > BEGIN; > [...] > ROLLBACK; > > using psql prompts. Seems reasonable :) 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
On Mon, Jun 26, 2017 at 9:35 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > We already have SET TRANSACTION READ ONLY. But in my use-case I am OK with the query doing write operations, since sometimes you need to test something in prod (that cannot be tested easily locally) but you want to ROLLBACK the query as quickly as possible to avoid locking things longer than necessary. Currently I'm just manually appending "; rollback;" to the query to make sure its rollbacked. The best thing I can do today is to map some button on the keyboard to automatically type "; rollback; [enter]" instead of hitting [enter] to fire-off the query, but it would be nice if it was built-in psql so you could never commit something by mistake unless you explicitly exit the AUTOROLLBACK mode.