Обсуждение: BUG #18777: Error running unnest function in a two phase commit transaction

The following bug has been logged on the website:

Bug reference:      18777
Logged by:          Sérgio Paulo Samaan Filho
Email address:      spsamaan@gmail.com
PostgreSQL version: 15.10
Operating system:   linux
Description:

I have a query " SELECT * FROM UNNEST(ARRAY[123456]) " running in a Groovy 4
code. That code is running in a weblogic 14 server. The driver I'm using is
postgresql-42.7.3.jar.
When the weblogic datasource is configured as a Two-phase commit I got an
error: 

Caused by: org.postgresql.util.PSQLException: ERROR: function
unnest(unknown) is not unique
  Hint: Could not choose a best candidate function. You might need to add
explicit type casts.
  Position: 250
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
~[postgresql-42.7.3.jar:42.7.3]
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
~[postgresql-42.7.3.jar:42.7.3]
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
~[postgresql-42.7.3.jar:42.7.3]
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:329)
~[postgresql-42.7.3.jar:42.7.3]
        at
org.postgresql.jdbc.PgPreparedStatement.getParameterMetaData(PgPreparedStatement.java:1755)
~[postgresql-42.7.3.jar:42.7.3]
        at
weblogic.jdbc.wrapper.PreparedStatement.getParameterMetaData(PreparedStatement.java:1045)
~[com.bea.core.datasource6.jar:14.1.1.0]
        at groovy.sql.Sql.getParameterMetaDataSafe(Sql.java:4234)
~[groovy-sql-4.0.6.jar:4.0.6]
        at groovy.sql.Sql.setParameters(Sql.java:4216)
~[groovy-sql-4.0.6.jar:4.0.6]
        at groovy.sql.Sql.getPreparedStatement(Sql.java:4519)
~[groovy-sql-4.0.6.jar:4.0.6]
        at groovy.sql.Sql.getPreparedStatement(Sql.java:4607)
~[groovy-sql-4.0.6.jar:4.0.6]
        at groovy.sql.Sql.access$1000(Sql.java:234)
~[groovy-sql-4.0.6.jar:4.0.6]
        at groovy.sql.Sql$PreparedQueryCommand.runQuery(Sql.java:4803)
~[groovy-sql-4.0.6.jar:4.0.6]
        ... 158 more

Disabling Two-phase commit in weblogic (just for testing purpose) thw query
works properly.

PG Bug reporting form <noreply@postgresql.org> writes:
> I have a query " SELECT * FROM UNNEST(ARRAY[123456]) " running in a Groovy 4
> code. That code is running in a weblogic 14 server. The driver I'm using is
> postgresql-42.7.3.jar.
> When the weblogic datasource is configured as a Two-phase commit I got an
> error: 
> Caused by: org.postgresql.util.PSQLException: ERROR: function
> unnest(unknown) is not unique

Presumably what is happening is that the query actually being sent
to the server is something like

    SELECT * FROM UNNEST($1)

with no hint as to what the data type of $1 is, making it impossible
to infer the unnest's result type.  I don't know whether the JDBC
driver or weblogic is more at fault, but this isn't a server-side bug.
Some level on the client side has to take responsibility for
specifying the data type of the parameter.

            regards, tom lane