psql - -dry-run option

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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.

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

--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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