Thread: psql - -dry-run option

psql - -dry-run option

From
Pavel Stehule
Date:
Hi

when I read a blog http://www.depesz.com/2015/12/14/waiting-for-9-6-psql-support-multiple-c-and-f-options-and-allow-mixing-them/ where is emulated dry-run mode, I though so we can implement it very simply.

Notices, comments?

Regards

Pavel

Re: psql - -dry-run option

From
Tom Lane
Date:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> when I read a blog
> http://www.depesz.com/2015/12/14/waiting-for-9-6-psql-support-multiple-c-and-f-options-and-allow-mixing-them/
> where is emulated dry-run mode, I though so we can implement it very
> simply.

Not one that is actually reliable.  All a script would have to do is
include its own begin/commit commands, and it would override what you
are talking about.  It's okay, in my opinion, if the -1 switch is just a
half-baked "best effort" solution.  It's not okay to provide a --dry-run
switch that is equally full of holes, because if someone were to actually
rely on it to not execute the script, the possibility of an override would
amount to a security bug.
        regards, tom lane



Re: psql - -dry-run option

From
Pavel Stehule
Date:


2015-12-17 20:03 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> when I read a blog
> http://www.depesz.com/2015/12/14/waiting-for-9-6-psql-support-multiple-c-and-f-options-and-allow-mixing-them/
> where is emulated dry-run mode, I though so we can implement it very
> simply.

Not one that is actually reliable.  All a script would have to do is
include its own begin/commit commands, and it would override what you
are talking about.  It's okay, in my opinion, if the -1 switch is just a
half-baked "best effort" solution.  It's not okay to provide a --dry-run
switch that is equally full of holes, because if someone were to actually
rely on it to not execute the script, the possibility of an override would
amount to a security bug.

My idea was enforce global transaction (-1) option and ensure STOP_ON_ERROR mode (cannot be changed later). Any inner COMMIT or ROLLBACK have to be disallowed (or ignored) - what can be problem :(

and if all statements from input stream was processed, then ROLLBACK is emitted, but result is success.

Pavel
 

                        regards, tom lane

Re: psql - -dry-run option

From
Pavel Stehule
Date:


2015-12-17 20:14 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-17 20:03 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> when I read a blog
> http://www.depesz.com/2015/12/14/waiting-for-9-6-psql-support-multiple-c-and-f-options-and-allow-mixing-them/
> where is emulated dry-run mode, I though so we can implement it very
> simply.

Not one that is actually reliable.  All a script would have to do is
include its own begin/commit commands, and it would override what you
are talking about.  It's okay, in my opinion, if the -1 switch is just a
half-baked "best effort" solution.  It's not okay to provide a --dry-run
switch that is equally full of holes, because if someone were to actually
rely on it to not execute the script, the possibility of an override would
amount to a security bug.

My idea was enforce global transaction (-1) option and ensure STOP_ON_ERROR mode (cannot be changed later). Any inner COMMIT or ROLLBACK have to be disallowed (or ignored) - what can be problem :(

and if all statements from input stream was processed, then ROLLBACK is emitted, but result is success.

Pavel

or different idea - just enforce syntax check without execution.
 
 

                        regards, tom lane


Re: psql - -dry-run option

From
Christopher Browne
Date:
<div dir="ltr">On 17 December 2015 at 14:16, Pavel Stehule <<a
href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>>wrote:<br />> or different idea - just enforce
syntaxcheck without execution.<br /><br />That seems pretty cool...  I'd find "syntax check without execution" to be
prettyuseful to test SQL (and especially DDL).<br /><br />--<br />When confronted by a difficult problem, solve it by
reducingit to the<br />question, "How would the Lone Ranger handle this?"</div> 

Re: psql - -dry-run option

From
Tom Lane
Date:
Christopher Browne <cbbrowne@gmail.com> writes:
> On 17 December 2015 at 14:16, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> or different idea - just enforce syntax check without execution.

> That seems pretty cool...  I'd find "syntax check without execution" to be
> pretty useful to test SQL (and especially DDL).

If it didn't execute the DDL at all, I doubt it would be that useful ---
you could not test any statements that depended on earlier statements.
Moreover, people might get surprised by error checks that they expect
to get reported by the "syntax check" but actually are not made until
runtime.  There's a lot of behavior there that's currently just
implementation detail but would become user-visible, depending on just
where the syntax check stops processing.

So what you want I think is something that *does* execute everything,
but within a single transaction that is guaranteed not to get committed.
A bulletproof version of that would likely need to be implemented on the
server side, not with some psql hack.

Whether we really need a feature like that isn't clear though; it's not
like it's hard to test things that way now.  Stick in a BEGIN with no
COMMIT, you're there.  The problem only comes in if you start expecting
the behavior to be bulletproof.  Maybe I'm being too pessimistic about
what people would believe a --dry-run switch to be good for ... but
I doubt it.
        regards, tom lane



Re: psql - -dry-run option

From
Joe Conway
Date:
On 12/17/2015 11:58 AM, Christopher Browne wrote:
> On 17 December 2015 at 14:16, Pavel Stehule <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>> or different idea - just enforce syntax check without execution.
>
> That seems pretty cool...  I'd find "syntax check without execution" to
> be pretty useful to test SQL (and especially DDL).

Like this?
https://github.com/jconway/pgsynck

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Re: psql - -dry-run option

From
Alvaro Herrera
Date:
Joe Conway wrote:
> On 12/17/2015 11:58 AM, Christopher Browne wrote:
> > On 17 December 2015 at 14:16, Pavel Stehule <pavel.stehule@gmail.com
> > <mailto:pavel.stehule@gmail.com>> wrote:
> >> or different idea - just enforce syntax check without execution.
> > 
> > That seems pretty cool...  I'd find "syntax check without execution" to
> > be pretty useful to test SQL (and especially DDL).
> 
> Like this?
> https://github.com/jconway/pgsynck

I thought the idea was to test the execution of the commands themselves,
not just the syntax.  Something like add a column here and see whether
this other complex UPDATE populates it correctly.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: psql - -dry-run option

From
"Shulgin, Oleksandr"
Date:
On Thu, Dec 17, 2015 at 9:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Whether we really need a feature like that isn't clear though; it's not
like it's hard to test things that way now.  Stick in a BEGIN with no
COMMIT, you're there.  The problem only comes in if you start expecting
the behavior to be bulletproof.  Maybe I'm being too pessimistic about
what people would believe a --dry-run switch to be good for ... but
I doubt it.

I'm on the same line: BEGIN/ROLLBACK requires trivial effort and a --dry-run option might give a false sense of security, but it cannot possibly rollback side-effects of user functions which modify filesystem or interact with the outside world in some other way.

--
Alex

Re: psql - -dry-run option

From
Jim Nasby
Date:
On 12/18/15 2:50 AM, Shulgin, Oleksandr wrote:
> On Thu, Dec 17, 2015 at 9:13 PM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>
>     Whether we really need a feature like that isn't clear though; it's not
>     like it's hard to test things that way now.  Stick in a BEGIN with no
>     COMMIT, you're there.  The problem only comes in if you start expecting
>     the behavior to be bulletproof.  Maybe I'm being too pessimistic about
>     what people would believe a --dry-run switch to be good for ... but
>     I doubt it.
>
>
> I'm on the same line: BEGIN/ROLLBACK requires trivial effort and a
> --dry-run option might give a false sense of security, but it cannot
> possibly rollback side-effects of user functions which modify filesystem
> or interact with the outside world in some other way.

The issue with that is if you're \i'ing files in and one of those 
happens to contain a COMMIT, you're hosed. I can see some use for a 
"must rollback" mode of BEGIN.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com



Re: psql - -dry-run option

From
Craig Ringer
Date:
On 18 December 2015 at 16:50, Shulgin, Oleksandr <oleksandr.shulgin@zalando.de> wrote:
 
I'm on the same line: BEGIN/ROLLBACK requires trivial effort and a --dry-run option might give a false sense of security, but it cannot possibly rollback side-effects of user functions which modify filesystem or interact with the outside world in some other way.

Pretty much. Side effects.

The most glaringly obvious example is nextval(...) and setval(...). You can't make them have no effect, since your script will then fail to run. But you can't roll them back either.

Also, anything that touches the file system, like COPY. Untrusted PLs that can fiddle with the file system. FDWs. All sorts. Oh, and of course psql commands like \o .

I think this idea is completely unworkable. You might work around the filesystem access issues with a new attribute (like LEAKPROOF) that asserts that a function relies strictly on in-database transactional behaviour. But you're not going to be able to do a dry run with sequence setting. You could ignore setval and run nextval as normal, but then your dry-run wouldn't reflect what the real run would do...

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services