Re: [HACKERS] psql - -dry-run option

[Jim Nasby]

On 12/18/15 2:50 AM, Shulgin, Oleksandr wrote:

On Thu, Dec 17, 2015 at 9:13 PM, Tom Lane > 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

Re: [HACKERS] psql - -dry-run option

[Craig Ringer]

On 18 December 2015 at 16:50, Shulgin, Oleksandr <
oleksandr.shul...@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

Re: [HACKERS] psql - -dry-run option

[Shulgin, Oleksandr]

On Thu, Dec 17, 2015 at 9:13 PM, Tom Lane  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: [HACKERS] psql - -dry-run option

[Pavel Stehule]

2015-12-17 20:14 GMT+01:00 Pavel Stehule :

>
>
> 2015-12-17 20:03 GMT+01:00 Tom Lane :
>
>> Pavel Stehule  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: [HACKERS] psql - -dry-run option

[Alvaro Herrera]

Joe Conway wrote:
> On 12/17/2015 11:58 AM, Christopher Browne wrote:
> > On 17 December 2015 at 14:16, Pavel Stehule  > > 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 Herrerahttp://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

Re: [HACKERS] psql - -dry-run option

[Pavel Stehule]

2015-12-17 20:03 GMT+01:00 Tom Lane :

> Pavel Stehule  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: [HACKERS] psql - -dry-run option

[Joe Conway]

On 12/17/2015 11:58 AM, Christopher Browne wrote:
> On 17 December 2015 at 14:16, Pavel Stehule  > 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



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] psql - -dry-run option

[Tom Lane]

Pavel Stehule  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

Re: [HACKERS] psql - -dry-run option

[Christopher Browne]

On 17 December 2015 at 14:16, Pavel Stehule  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?"

Re: [HACKERS] psql - -dry-run option

[Tom Lane]

Christopher Browne  writes:
> On 17 December 2015 at 14:16, Pavel Stehule  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