is ErrorResponse possible on Sync?

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"
/messages/by-id/b9fb50dc-0f6e-15fb-6555-8ddb86f4aa71@postgresfriends.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

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

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?

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/

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

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: