You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@pekko.apache.org by "patsta32 (via GitHub)" <gi...@apache.org> on 2025/03/19 13:45:03 UTC
[I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
patsta32 opened a new issue, #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275
Given:
- Pekko Persistence schema is created in postgres with schema changed (f.e. pekko)
- Application is configured for durable state actors
When:
Changing schemaName in .conf file for jdbc-durable-state-store to f.e. "pekko" leads to error:
```
org.apache.pekko.persistence.typed.state.internal.DurableStateStoreException: Failed to persist state with sequence number [1] for persistenceId [X]
...
Caused by: org.postgresql.util.PSQLException: ERROR: relation "durable_state" does not exist
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "patsta32 (via GitHub)" <gi...@apache.org>.
patsta32 commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740776000
@pjfanning Hey, i am sorry and i think there is a miscommunication on my side, thanks for your patience with the PR.
So i like your idea to make things clear first.
I am currently setting up a production project where i have to use a different schema for the persistence plugin in a PostgresDB so i noticed this bug. I would have to contribute to this project or create a fork and build the bug free version myself.
## What is the/my goal
As its my only use case currently, fixing the bug for postgres which lead to errors if the durable state is used with a schema name (which is not "public") set in the application.conf file.
### A schema name is and should be not required at any point if the plugin is used, it is and should be optional.
---
To just fix the bugs, there are only three changes needed which are already present in my PR:
1. adding the ```def schemaAndTable``` method to DurableStateTableConfiguration in
[PekkoPersistenceConfig.scala](https://github.com/apache/pekko-persistence-jdbc/blob/488d78a4f99e75633432658772c188a2c96681bd/core/src/main/scala/org/apache/pekko/persistence/jdbc/config/PekkoPersistenceConfig.scala), so when needed the schema and table name combination can be used through the config.
2. updating ```def insertDbWithDurableState``` and ```def updateDbWithDurableState```in [DurableStateQueries.scala](https://github.com/apache/pekko-persistence-jdbc/blob/488d78a4f99e75633432658772c188a2c96681bd/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/DurableStateQueries.scala) to use the ```schemaAndTable``` method in the sqlu statements.
3. updating ```def getSequenceNextValueExpr``` in [SequenceNextValUpdater.scala](https://github.com/apache/pekko-persistence-jdbc/blob/488d78a4f99e75633432658772c188a2c96681bd/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/SequenceNextValUpdater.scala) to also use the ```schemaAndTable``` method in the sqlu statement.
With this changes, the tests will succeed without problems and there are no changes needed for the default configurations. A user who would like to use the reference.conf, postgres-create-schema.sql and postgres-drop-schema.sql could without any problems.
Like you mentioned above:
> The main thing if you view the scripts in that way is that we should ensure that prod users who do choose to require a schema and who don't want to make that schema the default for their db user account - that they can set the schema name in the conf file and that that works.
---
#### Why the other files and script changes:
To test if the plugin would be fully functional with schema set for jdbc-journal, jdbc-snapshot-store and jdbc-durable-state-store in the .conf file, the creation or the movement of all tables would be necessary in/to a different schema (which i named "pekko") for testing purposes.
I was also testing the plugin without a schema set in configuration and the default files. This setup was present in the first version of my PR. Thats why i created new files and the SchemaType, so both versions (schema set and schema not configure / public) would be tested.
With the current state of the PR it is possible to copy the postgres-create-schema.sql and reference.conf files to use them as a starting point for a production setup. There is no need to set the schema in the configuration or the sql files, this is just a option, with the bug resolved.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740798622
Can you remove the sql changes and have a unit test with its own setup that creates a schema called `pekko` and installs the tables in that schema? I don't mind duplicating what is in the existing scripts. Seems safer not to adjust them and I don't think we need the additional `pekko` schema equivalent sql to be in the main/resources dir.
Maybe you only even need the durable state table for your test.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740033533
is this only an issue for postgres users or does it affect other db users?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740453992
The thing about the scripts, they are really more designed for use to set up test runs and to act as an editable template for people who want to set up a production environment. In the prod case, you can edit the scripts to have the schema you want.
The main thing if you view the scripts in that way is that we should ensure that prod users who do choose to require a schema and who don't want to make that schema the default for their db user account - that they can set the schema name in the conf file and that that works.
Also, even with prod users who want to associate the schema with the db user account, does our pekko-persistence-jdbc code facilitate this by not prefixing table names in JDBC commands with schema names.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740805106
I must admit that I have not set up a schema in a db for many years. I tend to create a db instance and create multiple databases in that instance with each database having a very specific role - so that worrying about schemas is not an issue - each db has just one schema.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740081425
This is the reference.conf
```
durable_state {
## The table and column names are not always read and used in SQL statements. If you change
## these values you may need to edit some source code
```
We basically just forked `akka-persistence-jdbc` and didn't think too much about fixing anything.
Certainly, as things stand, it would seem like a bad idea for anyone to set the schema name unless they were willing to fork the project and fix the code to use the schema name.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #275:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275#issuecomment-2740425435
@patsta32 Hear me out. I'm not trying to shoot down #276 and I'm willing to collaborate on that PR.
But: Since we are already in a bit of a mess here with schema support, I'm wondering if the first step is to see if we can document our way out of the problem.
So with Postgres DB, you can define a default schema for a user.
```
ALTER USER your-user set SEARCH_PATH = 'schema_name';
```
Would you be in a position to try that out?
The sql scripts in https://github.com/apache/pekko-persistence-jdbc/tree/main/core/src/main/resources/schema/postgres could theoretically be modified to have the `public.` part of the commands removed. Or for the scripts to be modified to use `?` as a placeholder for the schema name. We would then run the commands in the scripts as preparedStatements and when executing them, we would provide the schemaName.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] jdbc-durable-state-store for postgres schemaName is ignored / leads to errors [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning closed issue #275: jdbc-durable-state-store for postgres schemaName is ignored / leads to errors
URL: https://github.com/apache/pekko-persistence-jdbc/issues/275
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org