Error when migrating Postgres database: cached plan must not change result type

[Marc Ewert]

Hi,

I'm using the Vert.x Postgres client on Kubernetes. When we deploy a new version containing any database change (applied by Flyway) all the other containers currently using the database run into problems. 

It seems like each time a prepared statement is used afterwards, we get the error: cached plan must not change result type.

There seems to be property for the Postgres driver, which might work around that problem: autosave=conservative. But I don't know how to apply this for the Vert.x Postgres client.

Could anyone please help me regarding that? :-)

Best

Marc Ewert

[Julien Viet]

Hi,

do you know how to reproduce this easily so we can have a look and possibly do something similar ?

Julien

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/0c5e2e77-f687-4eac-87d6-3d5103dbd849%40googlegroups.com.

[Marc Ewert]

thanks for your reply. My setup is just a standard Kubernetes deployment with three containers. Inside of the containers I'm using the RxJava version of the PgClient for prepared statements. When I now deploy a new version of the software changing anything regarding the schema (for example adding a new column). I get in the already running containers the following exception:

io.vertx.pgclient.PgException: cached plan must not change result type

at io.vertx.pgclient.impl.codec.ErrorResponse.toException(ErrorResponse.java:29)

Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:

Error has been observed at the following site(s):

[...]

Stack trace:

at io.vertx.pgclient.impl.codec.ErrorResponse.toException(ErrorResponse.java:29)

at io.vertx.pgclient.impl.codec.QueryCommandBaseCodec.handleErrorResponse(QueryCommandBaseCodec.java:57)

at io.vertx.pgclient.impl.codec.PgDecoder.decodeError(PgDecoder.java:235)

at io.vertx.pgclient.impl.codec.PgDecoder.decodeMessage(PgDecoder.java:123)

at io.vertx.pgclient.impl.codec.PgDecoder.channelRead(PgDecoder.java:103)

at io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:251)

And this doesn't stop until these containers are restarted. So the PgClient is complaining about the schema change for each following statement.

Does that already help, or is it somehow related to how exactly I'm using the PgClient?

Best

Marc

On Thursday, April 2, 2020 at 4:23:10 PM UTC+2, Julien Viet wrote:

Hi,

do you know how to reproduce this easily so we can have a look and possibly do something similar ?

Julien

On 2 Apr 2020, at 15:52, Marc Ewert <marc...@deliveryhero.com> wrote:

Hi,

I'm using the Vert.x Postgres client on Kubernetes. When we deploy a new version containing any database change (applied by Flyway) all the other containers currently using the database run into problems. 

It seems like each time a prepared statement is used afterwards, we get the error: cached plan must not change result type.

There seems to be property for the Postgres driver, which might work around that problem: autosave=conservative. But I don't know how to apply this for the Vert.x Postgres client.

Could anyone please help me regarding that? :-)

Best

Marc Ewert

--
You received this message because you are subscribed to the Google Groups "vert.x" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ve...@googlegroups.com.

[Julien Viet]

It is actually a response sent by the server.

I think in such case we would catch this specific error and trash the cached prepared statement for a new one.

Could you make a simple reproducer in Java for this case, e.g

1/ create a prepared statement and use it succesfully

2/ mutate the schema in a way the error happens

3/ use the prepared statement to run into this error.

that would help a lot.

Julien

To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/a2584a35-9b50-4da5-80f2-2a9145fb6120%40googlegroups.com.

[Marc Ewert]

I have to see, when I will find the time for that. Until then I found a (a bit inconvenient) workaround:

Before actually releasing the database migration, I can release an update of the software, which sets PgConnectOptions.setCachePreparedStatements(false). That's slower, but won't complain about any database change, because server side prepared statements are turned off. After the database migration this change has to be rolled back.

An other solution seems to be not to use "SELECT * ..." at all. If all columns to be selected are named, this problem is said to not occur.

Best

Marc

On Thursday, April 2, 2020 at 5:30:39 PM UTC+2, Julien Viet wrote:

It is actually a response sent by the server.

I think in such case we would catch this specific error and trash the cached prepared statement for a new one.

Could you make a simple reproducer in Java for this case, e.g

1/ create a prepared statement and use it succesfully

2/ mutate the schema in a way the error happens

3/ use the prepared statement to run into this error.

that would help a lot.

Julien

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/a2584a35-9b50-4da5-80f2-2a9145fb6120%40googlegroups.com.

[Julien Viet]

I think that the client could support this if it is able to identify this failure from postgres, i.e on an error that indicates that the prepared statement is invalid then it can remove the prepared statement from the cache and recreate it.

To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/1801efad-ed88-4dd5-9a5f-7583bb9a9b98%40googlegroups.com.