BUG #16290: Unable to run \set AUTOCOMMIT off

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16290: Unable to run \set AUTOCOMMIT off

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      16290
Logged by:          apoorv malik
Email address:      [hidden email]
PostgreSQL version: 11.6
Operating system:   Linux
Description:        

Hi,
I'm using Liquibase and trying to run multiple sql files. In the first sql
file, I want to define '\set AUTOCOMMIT off' so that I can rollback.
This will be used for testing. Unfortunately, I can't use BEGIN; as there
are multiple files and can't be combined to one else Liquibase will not have
separate entries.

Could you please give me equivalent command for \set AUTOCOMMIT off -->  I
have tried all types of escaping but nothing seems to work.

set AUTOCOMMIT = off -->this works fine in Liquibase but fails at DB

Here's the error:

Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or
near "\"
  Position: 1 [Failed SQL: (0) \set AUTOCOMMIT off]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
        at liquibase.Liquibase.update(Liquibase.java:202)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1399)
        at liquibase.integration.commandline.Main.run(Main.java:229)
        at liquibase.integration.commandline.Main.main(Main.java:143)
Caused by: liquibase.exception.DatabaseException: ERROR: syntax error at or
near "\"
  Position: 1 [Failed SQL: (0) \set AUTOCOMMIT off]
        at
liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
        at
liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1274)
        at
liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1256)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
        ... 7 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near
"\"
  Position: 1
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
        at
liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
        ... 12 common frames omitted

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16290: Unable to run \set AUTOCOMMIT off

David G Johnston
On Monday, March 9, 2020, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16290
Logged by:          apoorv malik
Email address:      [hidden email]
PostgreSQL version: 11.6
Operating system:   Linux
Description:       

Hi,
I'm using Liquibase and trying to run multiple sql files. In the first sql
file, I want to define '\set AUTOCOMMIT off' so that I can rollback.
This will be used for testing. Unfortunately, I can't use BEGIN; as there
are multiple files and can't be combined to one else Liquibase will not have
separate entries.

Could you please give me equivalent command for \set AUTOCOMMIT off -->  I
have tried all types of escaping but nothing seems to work.

set AUTOCOMMIT = off -->this works fine in Liquibase but fails at DB

Here's the error:

Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or
near "\"
  Position: 1 [Failed SQL: (0) \set AUTOCOMMIT off]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
        at liquibase.Liquibase.update(Liquibase.java:202)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1399)
        at liquibase.integration.commandline.Main.run(Main.java:229)
        at liquibase.integration.commandline.Main.main(Main.java:143)
Caused by: liquibase.exception.DatabaseException: ERROR: syntax error at or
near "\"
  Position: 1 [Failed SQL: (0) \set AUTOCOMMIT off]
        at
liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
        at
liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1274)
        at
liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1256)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
        ... 7 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near
"\"
  Position: 1
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
        at
liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
        ... 12 common frames omitted


This is not a bug.

Server doesn’t have session-level auto-commit toggling options (as such, you can send sql begin to avoid the default auto-commit) so sending a command to it to control session behavior is not possible.  Clients can provide their own auto-commit mode with client-specific means of controlling it.  In this case \set is the psql client’s configuration command (i.e, it only works if you are using psql). You need to figure out what will work in your environment.  JDBC has a setAutoCommit method on the connection, be a good place to start.  Or the LiquiBase documentation...

David J.