Commit should throw an error if it fails

[davecramer]

Currently if you execute the following code

begin;
BEGIN
postgres=*# cause an errror;
ERROR:  syntax error at or near "cause"
LINE 1: cause an errror
        ^
postgres=!# commit;
ROLLBACK

the error is thrown when the error occurs but the commit "succeeds" without an error message.

Here is the discussion on hackers https://www.postgresql.org/message-id/b9fb50dc-0f6e-15fb-6555-8ddb86f4aa71%40postgresfriends.org

Here's an attempt to fix it

https://commitfest.postgresql.org/32/2522/

I still think this behaviour is undesirable. I just don't see any support from the server community.

[jackc]

If the driver layer has a transaction entity instead of the application or ORM directly sending begin and commit queries, then the driver can shield the application by checking the command tag and returning an appropriate error. This is what pgx does:

// Commit commits the transaction.
func (tx *dbTx) Commit(ctx context.Context) error {
	// ...

	commandTag, err := tx.conn.Exec(ctx, "commit")
	// ... handle err
	if commandTag.String() == "ROLLBACK" {
		return ErrTxCommitRollback
	}

	return nil
}

I agree that the current behavior is not ideal, and seems to be breaking the SQL spec. So this change might be reasonable, but it wouldn't affect pgx much either way -- especially since the old way would need to be supported for old servers.

[davecramer]

Yes we do the same thing. The idea here is that these are things that we feel could be improved.