Thread: psql - -dry-run option
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. 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
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
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
<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>
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
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
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
On 18 December 2015 at 16:50, Shulgin, Oleksandr <oleksandr.shulgin@zalando.de> wrote:
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'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...