search

Ninja-Squad / DbSetup

An API for populating a database in unit tests
http://dbsetup.ninja-squad.com/
212 stars 34 forks source link

PSQLException: A result was returned when none was expected. #73

Closed ShajeeOnCloud closed 2 years ago

ShajeeOnCloud commented 2 years ago

Hi,

I am using DBSetup for running test cases. It works fine with Oracle and H2. Recently there is a move to Postgres. Some of the queries are returning below error

Caused by: org.postgresql.util.PSQLException: A result was returned when none was expected. at org.postgresql.jdbc.PgStatement.checkNoResultUpdate(PgStatement.java:276) at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:266) at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119) at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) at com.ninja_squad.dbsetup.operation.SqlOperation.execute(SqlOperation.java:64) at com.ninja_squad.dbsetup.operation.CompositeOperation.execute(CompositeOperation.java:92) at com.ninja_squad.dbsetup.operation.CompositeOperation.execute(CompositeOperation.java:92) at com.ninja_squad.dbsetup.DbSetup.launch(DbSetup.java:107)

Debugging into the stack trace the error is caused when executing a set of sql statements.

Environment (from build.gradle):

testImplementation group: 'com.ninja-squad', name: 'DbSetup', version: '2.1.0' testImplementation group: 'org.postgresql', name: 'postgresql', version: '42.3.6'

I have SQLs that I am passing to DBSetup for execution. First goes a set of delete statements and then insert statements.

Can someone please give some inputs on how to solve this issue?

jnizet commented 2 years ago

Post a minimal example reproducing the issue.

ShajeeOnCloud commented 2 years ago

ok after doing some debugging and investigation I found the issue, it is because of updating sequences in postgres, or rather how the sequences are getting updated SELECT SETVAL('table1_id_seq', COALESCE(MAX(id), 1) ) FROM table1;

using SETVAL is a legitimate way of updating sequences in Postgres apart from the Alter statement, howeever from DbSetup a executeUpdate is fired for all SQLs. Making the above execution fail and resulting in stacktrace as I pasted above.

ShajeeOnCloud commented 2 years ago

is it possible to execute a SELECT query from DbSetup?

jnizet commented 2 years ago

DbSetup is designed to setup your database before tests. So its goal is rather to delete and create stuff in the database than to select stuff from the database. I can understand why you'd want to use a select here, so I see two solutions:

  1. Use alter sequence restart with, as described here: https://www.postgresql.org/docs/current/sql-altersequence.html
  2. Implement your own Operation (http://dbsetup.ninja-squad.com/apidoc/2.1.0/com/ninja_squad/dbsetup/operation/Operation.html) where you're free to do whatever you need to do with the JDBC connection.
ShajeeOnCloud commented 2 years ago

Thank you for the response and the possible alternatives. Option 1 might not work as I need reference to the last/max sequence in a given table. I guess Option 2 it is then.