pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16290: Unable to run \set AUTOCOMMIT off
2+ messages / 2 participants
[nested] [flat]

* BUG #16290: Unable to run \set AUTOCOMMIT off
@ 2020-03-10 05:40 PG Bug reporting form <noreply@postgresql.org>
  2020-03-10 06:26 ` Re: BUG #16290: Unable to run \set AUTOCOMMIT off David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-03-10 05:40 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: apoorvmalik.appy@gmail.com

The following bug has been logged on the website:

Bug reference:      16290
Logged by:          apoorv malik
Email address:      apoorvmalik.appy@gmail.com
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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #16290: Unable to run \set AUTOCOMMIT off
  2020-03-10 05:40 BUG #16290: Unable to run \set AUTOCOMMIT off PG Bug reporting form <noreply@postgresql.org>
@ 2020-03-10 06:26 ` David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David G. Johnston @ 2020-03-10 06:26 UTC (permalink / raw)
  To: apoorvmalik.appy@gmail.com <apoorvmalik.appy@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Monday, March 9, 2020, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16290
> Logged by:          apoorv malik
> Email address:      apoorvmalik.appy@gmail.com
> 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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-03-10 06:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-10 05:40 BUG #16290: Unable to run \set AUTOCOMMIT off PG Bug reporting form <noreply@postgresql.org>
2020-03-10 06:26 ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox