Обсуждение: psql swallowed my "BEGIN;" on reset... user beware?

Поиск
Список
Период
Сортировка

psql swallowed my "BEGIN;" on reset... user beware?

От
Ken Tanzer
Дата:
After restarting the server in another window, I was surprised that my command did not run in a transaction:

spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW rent_info; \i create.view.rent_info.sql
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
NOTICE:  view "ptest_mip" does not exist, skipping
DROP VIEW
DROP VIEW
CREATE VIEW
spc_test_scratch=# commit;
WARNING:  there is no transaction in progress
COMMIT

It looks like the behavior makes perfect sense--the "BEGIN;" failed, the server reset, psql continued processing commands.  It seems a little dangerous, though.  In my case I could/should have known, but a user could easily have no way of knowing if their server was reset by an admin.  And of course "BEGIN;" is an unusually likely candidate for first command in a series.

Is this the desired behavior, or would it be better to abort the commands at this point?

Ken



--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: psql swallowed my "BEGIN;" on reset... user beware?

От
Moshe Jacobson
Дата:

On Sun, Sep 29, 2013 at 2:18 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
After restarting the server in another window, I was surprised that my command did not run in a transaction:

spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW rent_info; \i create.view.rent_info.sql
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
NOTICE:  view "ptest_mip" does not exist, skipping
DROP VIEW
DROP VIEW
CREATE VIEW
spc_test_scratch=# commit;
WARNING:  there is no transaction in progress
COMMIT

This seems like a pretty serious issue. My opinion is that psql should refuse to execute further commands on a line of input if any of them fails due to a terminated connection.


Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle

Re: psql swallowed my "BEGIN;" on reset... user beware?

От
Merlin Moncure
Дата:
On Mon, Sep 30, 2013 at 10:49 AM, Moshe Jacobson <moshe@neadwerx.com> wrote:
>
> On Sun, Sep 29, 2013 at 2:18 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>>
>> After restarting the server in another window, I was surprised that my
>> command did not run in a transaction:
>>
>> spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW
>> rent_info; \i create.view.rent_info.sql
>> FATAL:  terminating connection due to administrator command
>> server closed the connection unexpectedly
>>         This probably means the server terminated abnormally
>>         before or while processing the request.
>> The connection to the server was lost. Attempting reset: Succeeded.
>> NOTICE:  view "ptest_mip" does not exist, skipping
>> DROP VIEW
>> DROP VIEW
>> CREATE VIEW
>> spc_test_scratch=# commit;
>> WARNING:  there is no transaction in progress
>> COMMIT
>
>
> This seems like a pretty serious issue. My opinion is that psql should
> refuse to execute further commands on a line of input if any of them fails
> due to a terminated connection.

you can control this with on error stop directive -- add it to your
psqlrc if you want the setting to persist.

merlin

Re: psql swallowed my "BEGIN;" on reset... user beware?

От
Ryan Kelly
Дата:
On Mon, Sep 09/30/13, 2013 at 11:49:29AM -0400, Moshe Jacobson wrote:
> On Sun, Sep 29, 2013 at 2:18 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
> > After restarting the server in another window, I was surprised that my
> > command did not run in a transaction:
> >
> > spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW
> > rent_info; \i create.view.rent_info.sql
> > FATAL:  terminating connection due to administrator command
> > server closed the connection unexpectedly
> >         This probably means the server terminated abnormally
> >         before or while processing the request.
> > The connection to the server was lost. Attempting reset: Succeeded.
> > NOTICE:  view "ptest_mip" does not exist, skipping
> > DROP VIEW
> > DROP VIEW
> > CREATE VIEW
> > spc_test_scratch=# commit;
> > WARNING:  there is no transaction in progress
> > COMMIT
> >
>
> This seems like a pretty serious issue. My opinion is that psql should
> refuse to execute further commands on a line of input if any of them fails
> due to a terminated connection.

FWIW, I often have this same problem. I also agree with the proposed
solution.

The current code seems to have the connection reset logic down inside
the result processing routines. It looks like it would be relatively
straightforward to add two calls to ConnectionUp in ProcessResult and
ProcessResult to resolve this. But it would be a change in behavior.

-Ryan

Re: psql swallowed my "BEGIN;" on reset... user beware?

От
Ryan Kelly
Дата:
On Mon, Sep 09/30/13, 2013 at 11:49:29AM -0400, Moshe Jacobson wrote:
> On Sun, Sep 29, 2013 at 2:18 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
> > After restarting the server in another window, I was surprised that my
> > command did not run in a transaction:
> >
> > spc_test_scratch=# BEGIN; DROP VIEW IF EXISTS ptest_mip ; DROP VIEW
> > rent_info; \i create.view.rent_info.sql
> > FATAL:  terminating connection due to administrator command
> > server closed the connection unexpectedly
> >         This probably means the server terminated abnormally
> >         before or while processing the request.
> > The connection to the server was lost. Attempting reset: Succeeded.
> > NOTICE:  view "ptest_mip" does not exist, skipping
> > DROP VIEW
> > DROP VIEW
> > CREATE VIEW
> > spc_test_scratch=# commit;
> > WARNING:  there is no transaction in progress
> > COMMIT
> >
>
> This seems like a pretty serious issue. My opinion is that psql should
> refuse to execute further commands on a line of input if any of them fails
> due to a terminated connection.

Err, meant SendQuery and PSQLexec in my last mail. Not sure how I typed
ProcessResult twice...

-Ryan

Re: psql swallowed my "BEGIN;" on reset... user beware?

От
Ken Tanzer
Дата:
you can control this with on error stop directive -- add it to your
psqlrc if you want the setting to persist.

I hear what you're saying, but wonder about the implications:

It sounds like you can't trust your BEGIN to actually start a transaction unless on_error_stop is set

The default for on_error_stop seems to be off, so at a minimum this is a default gotcha waiting to happen.  At the very least, perhaps the documentation for BEGIN and ON_ERROR_STOP should mention this?

Should BEGIN be a special case?  The operator clearly wants the subsequent commands to run together or not at all.  I'm having trouble imagining any scenario under which one would want their commands to continue after a failed BEGIN, especially if it happens at random times outside of one's control.

Is your response based on belief that the current behavior is good, or a concern about changing the way psql works, or undesired side effects from changing it?  I could understand any of those--just curious what your thinking is!

Cheers,
Ken