Whole transaction aborted with Protocol=7.4-2 (MS Windows)

[ruiaurelio]

Greetings.
It seems that the expectation about 'Statement(2): Rollback the statement.' (https://odbc.postgresql.org/docs/config.html) is not fulfilled, at least in the MS Windows world.
See his demonstration with Perl script:
`use strict;
use warnings;
use DBI;

my $conn_str =
'Driver={PostgreSQL Unicode(x64)};' .
'Server=localhost;' .
'Port=5432;' .
'Database=pg_crw_local;' .
'B6=1;C0=1;C8=0;' .
'Protocol=7.4-2;'
;

my $user = '';
my $password = '';

my $dbh = DBI->connect(
"dbi:ODBC:$conn_str",
$user,
$password,
{
RaiseError => 1,
PrintError => 0,
AutoCommit => 0,
LongReadLen => 65536,
LongTruncOk => 1, # optional
}
);

print "Connected successfully.\n";

sub print_locks {
my ($dbh, $when) = @_;

print "$when:\n";
my $sql = q{ SELECT pid, locktype, mode, granted, CAST(relation AS regclass) FROM pg_locks WHERE pid = pg_backend_pid(); };
my $sth = $dbh->prepare($sql);
$sth->execute();

while (my @row = $sth->fetchrow_array) {
print join(
    ' | ',
    map { defined $_ ? $_ : '<NULL>' } @row
    ), "\n";
}
$sth->finish();

}

sub print_current_txid {
my ($dbh) = @_;

my $sql = q{ SELECT txid_current(); };
my $sth = $dbh->prepare($sql);
$sth->execute();

while (my @row = $sth->fetchrow_array) {
print "$row[0]\n";
}
$sth->finish();

}

sub lock_table_in_mode {
my ($dbh, $mode) = @_;

my $sql = "LOCK TABLE PROBLEM_PARTITION_RTD_ENV IN $mode MODE;";
eval { $dbh->do($sql); };

print "err=",    ($DBI::err    // '<none>'), "\n";
print "errstr=", ($DBI::errstr // '<none>'), "\n";

}

lock_table_in_mode( $dbh, 'EXCLUSIVE' );
print_locks( $dbh, 'before error' );
print_current_txid( $dbh );
lock_table_in_mode( $dbh, 'POTATOS' );
print_locks( $dbh, 'after error' );
print_current_txid( $dbh );

$dbh->disconnect();

print "Disconnected.\n";
`

Result:
Connected successfully.
err=
errstr=
before error:
27035 | relation | AccessShareLock | 1 | pg_locks
27035 | virtualxid | ExclusiveLock | 1 |
27035 | relation | ExclusiveLock | 1 | problem_partition_rtd_env
285981
err=1
errstr=ERROR: syntax error at or near "POTATOS";
Error while executing the query (SQL-42601)
after error:
27035 | relation | AccessShareLock | 1 | pg_locks
27035 | virtualxid | ExclusiveLock | 1 |
285982
Disconnected.

So, after the wrong SQL:

• the table lock (on my custom table 'problem_partition_rtd_env') was lost;
• and we have a new TX ID.

It's behaving exactly like Protocol=7.4-1.
However if I change to Protocol=7.4-0 then I get "ERROR: current transaction is aborted, commands ignored until end of transaction block" as expected.

I tested this in two PostgreSQL ODBC driver versions: 17.00.0006 and 18.00.0002, same behavior.
PostgreSQL DB version is 17.5.

Bug? Useful tips/comments?

Thanks in advance.

[ruiaurelio]

Adding extra information:

1. (the original situation) if the offending SQL is a construction from a wrong syntax like "ERROR: syntax error at or near "POTATOS"" (error 42601) then:

• TX ID takes a step forward;
• and the table lock already obtained is released - the problem!!

2. if the offending SQL is like "ERROR: relation "test2" does not exist;" (error 42P01) then:

• TX ID takes a step forward;
• but the table lock already obtained is kept - the 'overall' transaction is still going!

So the mechanism seems to 'kind of work', depending on that we throw to the database ... which is kind of a lottey then, I guess ...

Comments?