PostgreSQL : Transactional feature is not working when switching off auto-commit in postgres

[Shashwat32]

What happened The transactional feature is not working when switching off auto-commit. When I switch off auto-commit using PostgreSQL and then try to execute any query it is giving me an error: org.postgresql.util.PSQLException: ERROR: syntax error: unexpected $end, expecting TRANSACTION at position 6"

What you expected to happen The user should be able to disable auto-commit and handle the transaction as needed.

How to reproduce it (as minimally and precisely as possible)

1. Create one table in immudb.

CREATE TABLE IF NOT EXISTS users (username VARCHAR[50],email VARCHAR[50],PRIMARY KEY (username));

2. Try inserting one row using immudb4j (Java SDK : 0.9.0.6) and PostgreSQL (Version : 42.2.25). Set Auto-Commit as false.

private final String INSERT_QUERY_USERS = "INSERT INTO USERS (username,email ) VALUES ('"; try { String urlForConnection = "jdbc:postgresql://"+url+":"+port+"/"+databaseName+"?sslmode=allow&preferQueryMode=simple"; var connection = DriverManager.getConnection(urlForConnection,"immudb", "immudb"); var client = connection .getClient(); client.setAutoCommit(false); Statement stmt = client.createStatement(); String sqlStmt = INSERT_QUERY_USERS+usersDTO.getUsername()+"','"+usersDTO.getEmail()+")"; stmt.executeUpdate(sqlStmt); client.commit(); } catch (SQLException throwables) { throwables.printStackTrace(); client.rollback(); } finally { client.close(); }

Environment

# run "immu* 1.2.2" and copy/paste the output here
./immudb -d

immudb 2022/03/18 09:56:25 INFO: SQL Engine ready for database 'defaultdb' {replica = false} immudb 2022/03/18 09:56:25 INFO: pgsl server is running at port 5432 immudb 2022/03/18 09:56:25 INFO: Webconsole enabled: 0.0.0.0:8080 immudb 2022/03/18 09:56:25 INFO: Web API server enabled on 0.0.0.0:8080/api (http) You can now use immuadmin and immuclient CLIs to login with the immudb superadmin user and start using immudb. immudb has been started with PID 211 shashwat32@Eternal007:~$ immudb 2022/03/18 10:23:15 ERROR: unable to write error on wire: write tcp 172.19.66.153:5432->172.19.64.1:53826: write: broken pipe immudb 2022/03/18 10:23:15 ERROR: unable to complete error handling: write tcp 172.19.66.153:5432->172.19.64.1:53826: write: broken pipe immudb 2022/03/18 10:23:15 WARNING: connection is closed immudb 2022/03/18 10:28:46 INFO: 1 transaction/s to be indexed at 'data/defaultdb' immudb 2022/03/18 10:34:31 ERROR: unable to write error on wire: write tcp 172.19.66.153:5432->172.19.64.1:54138: write: broken pipe immudb 2022/03/18 10:34:31 ERROR: unable to complete error handling: write tcp 172.19.66.153:5432->172.19.64.1:54138: write: broken pipe immudb 2022/03/18 10:34:31 WARNING: connection is closed

Additional info (any other context about the problem)

[sibijohn72]

+1 i have the exact same issue :(