Horton
Menu

Thread

  1. is ErrorResponse possible on Sync?

    Andrei Matei <andreimatei1@gmail.com> — 2022-01-12T17:52:50Z

    Hello Postgres friends,

I've got a question about the wire protocol; the relevant text in the docs
seems a bit ambiguous to me. If the processing of a Sync message fails
(e.g. because the commit of the current transaction fails), is the backend
allowed to respond with an ErrorResponse, in addition to the ReadyForQuery
message? Or, does the backend swallow the error, and return only the
ReadyForQuery (I hope not).

The docs
<https://www.postgresql.org/docs/14/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY>
 say:
"""
At completion of each series of extended-query messages, the frontend
should issue a Sync message. This parameterless message causes the backend
to close the current transaction if it's not inside a BEGIN/COMMIT
transaction block (“close” meaning to commit if no error, or roll back if
error). Then a ReadyForQuery response is issued. The purpose of Sync is to
provide a resynchronization point for error recovery. When an error is
detected while processing any extended-query message, the backend issues
ErrorResponse, then reads and discards messages until a Sync is reached,
then issues ReadyForQuery and returns to normal message processing. (But
note that no skipping occurs if an error is detected while processing Sync
— this ensures that there is one and only one ReadyForQuery sent for each
Sync.)
"""

This paragraph acknowledges that an error can be "detected" while
processing a Sync, but one reading of it might suggest that the only
response from a Sync is a single ReadyForQueryMessage.

Thanks!

- Andrei

  2. Re: is ErrorResponse possible on Sync?

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2022-01-12T18:03:17Z

    >Or, does the backend swallow the error, and return only the ReadyForQuery
(I hope not).

What is your backend version?

Here's a well-known case when the backend did swallow the error:
"Error on failed COMMIT"
https://www.postgresql.org/message-id/b9fb50dc-0f6e-15fb-6555-8ddb86f4aa71%40postgresfriends.org

I don't remember if the behavior has been fixed or not.
The expected behavior was "commit" returned "rollback" status without any
error.

Vladimir

  3. Re: is ErrorResponse possible on Sync?

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-01-12T18:05:04Z

    Andrei Matei <andreimatei1@gmail.com> writes:
> I've got a question about the wire protocol; the relevant text in the docs
> seems a bit ambiguous to me. If the processing of a Sync message fails
> (e.g. because the commit of the current transaction fails), is the backend
> allowed to respond with an ErrorResponse, in addition to the ReadyForQuery
> message? Or, does the backend swallow the error, and return only the
> ReadyForQuery (I hope not).

Uh ... I don't think Sync itself can fail.  Any ErrorResponse you see
there is really from failure of some prior command.  The Sync is really
delimiting how much stuff you'd like to skip in case of a failure.
Basically this is to allow pipelining of commands, with the ability to
discard later commands if an earlier one fails.

But in any case, no, Sync would not suppress an error message if
one is needed.

			regards, tom lane

  4. Re: is ErrorResponse possible on Sync?

    Andrei Matei <andreimatei1@gmail.com> — 2022-01-12T18:18:53Z

    Thanks!

I work on CockroachDB - which is wire-compatible with Postgres - so I'm
interested in what the server can and cannot do.


> Uh ... I don't think Sync itself can fail.  Any ErrorResponse you see
> there is really from failure of some prior command.


Hmm, this got me curious. If Sync itself cannot fail, then what is this
sentence really saying: "This parameterless message (ed. Sync) causes the
backend to close the current transaction if it's not inside a BEGIN/COMMIT
transaction block (“close” meaning to commit if no error, or roll back if
error)." ?
This seems to say that, outside of BEGIN/END, the transaction is committed
at Sync time (i.e. if the Sync is never sent, nothing is committed).
Presumably, committing a transaction can fail even if no
previous command/statement failed, right?



>   The Sync is really
> delimiting how much stuff you'd like to skip in case of a failure.
> Basically this is to allow pipelining of commands, with the ability to
> discard later commands if an earlier one fails.
>
> But in any case, no, Sync would not suppress an error message if
> one is needed.
>
>                         regards, tom lane
>

  5. Re: is ErrorResponse possible on Sync?

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2022-01-12T19:39:20Z

    On 2022-Jan-12, Andrei Matei wrote:

> If Sync itself cannot fail, then what is this
> sentence really saying: "This parameterless message (ed. Sync) causes the
> backend to close the current transaction if it's not inside a BEGIN/COMMIT
> transaction block (“close” meaning to commit if no error, or roll back if
> error)." ?
> This seems to say that, outside of BEGIN/END, the transaction is committed
> at Sync time (i.e. if the Sync is never sent, nothing is committed).
> Presumably, committing a transaction can fail even if no
> previous command/statement failed, right?

A deferred trigger can cause a failure at COMMIT time for which no
previous error was reported.

alvherre=# create table t (a int unique deferrable initially deferred);
CREATE TABLE
alvherre=# insert into t values (1);
INSERT 0 1
alvherre=# begin;
BEGIN
alvherre=*# insert into t values (1);
INSERT 0 1
alvherre=*# commit;
ERROR:  duplicate key value violates unique constraint "t_a_key"
DETALLE:  Key (a)=(1) already exists.

I'm not sure if you can cause this to explode with just a Sync message, though.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/

  6. Re: is ErrorResponse possible on Sync?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2022-01-12T23:51:01Z

    > Hmm, this got me curious. If Sync itself cannot fail, then what is this
> sentence really saying: "This parameterless message (ed. Sync) causes the
> backend to close the current transaction if it's not inside a BEGIN/COMMIT
> transaction block (“close” meaning to commit if no error, or roll back if
> error)." ?
> This seems to say that, outside of BEGIN/END, the transaction is committed
> at Sync time (i.e. if the Sync is never sent, nothing is committed).

Yes, if you do not send Sync and terminate the session, then the
transaction will not be committed.

FE=> Parse(stmt="", query="INSERT INTO t1 VALUES(2)")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Terminate

After this, I don't see the row (2) in table t1.

> Presumably, committing a transaction can fail even if no
> previous command/statement failed, right?

Right. Alvaro gave an excellent example.

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

  7. Re: is ErrorResponse possible on Sync?

    Rafi Shamim <rafiss@gmail.com> — 2022-01-18T23:33:20Z

    I used your example and tried it with prepared statements. I captured the
traffic with
Wireshark. My client sent Bind/Execute/Sync messages, and PostgreSQL 14 sent
back BindComplete/CommandComplete/ErrorResponse messages, followed by
ReadyForQuery after that.

So yes, it looks like ErrorResponse is a valid response for Sync.

On Tue, Jan 18, 2022 at 6:11 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2022-Jan-12, Andrei Matei wrote:
>
> > If Sync itself cannot fail, then what is this
> > sentence really saying: "This parameterless message (ed. Sync) causes the
> > backend to close the current transaction if it's not inside a
> BEGIN/COMMIT
> > transaction block (“close” meaning to commit if no error, or roll back if
> > error)." ?
> > This seems to say that, outside of BEGIN/END, the transaction is
> committed
> > at Sync time (i.e. if the Sync is never sent, nothing is committed).
> > Presumably, committing a transaction can fail even if no
> > previous command/statement failed, right?
>
> A deferred trigger can cause a failure at COMMIT time for which no
> previous error was reported.
>
> alvherre=# create table t (a int unique deferrable initially deferred);
> CREATE TABLE
> alvherre=# insert into t values (1);
> INSERT 0 1
> alvherre=# begin;
> BEGIN
> alvherre=*# insert into t values (1);
> INSERT 0 1
> alvherre=*# commit;
> ERROR:  duplicate key value violates unique constraint "t_a_key"
> DETALLE:  Key (a)=(1) already exists.
>
> I'm not sure if you can cause this to explode with just a Sync message,
> though.
>
> --
> Álvaro Herrera              Valdivia, Chile  —
> https://www.EnterpriseDB.com/
>
>
>
>
>