Changing PostgreSQL schema only active after reload of Spring Boot application

Question

In my spring boot application, the first migration creates a schema for the current user and switches to this schema. Subsequent migrations are properly executed on this schema. However, after migration is complete, these tables are not found by the application until the application is reloaded.

application.yml

spring:
  r2dbc:
    url: r2dbc:postgresql://127.0.0.1:5432/pickle_db
    username: rick
    password: morty

r2dbc:
  migrate:
    migrations-schema: public
    migrations-table: fun_migrations
    migrations-lock-table: fun_migrations_lock
    resources-paths:
      - classpath:/db/migration/*.sql

V1__schema.sql

CREATE SCHEMA IF NOT EXISTS fun;
ALTER ROLE current_user SET search_path TO 'fun';
SET search_path TO 'fun';

V2__tables.sql

CREATE TABLE TREE(id int, name varchar(64));

Migration runs successfully and creates following tables.

fun.tree
public.fun_migration
public.fun_migration_lock

2021-06-17 19:58:51.845  INFO 4400 --- [  restartedMain] n.n.r.m.a.R2dbcMigrateAutoConfiguration  : Starting R2DBC migration
2021-06-17 19:58:51.847  INFO 4400 --- [  restartedMain] n.n.r2dbc.migrate.core.R2dbcMigrate      : Configured with R2dbcMigrateProperties{enable=true, connectionMaxRetries=500, resourcesPaths=[classpath:/db/migration/*.sql], chunkSize=1000, dialect=null, validationQuery='select '42' as result', validationQueryExpectedResultValue='42', validationQueryTimeout=PT5S, validationRetryDelay=PT1S, acquireLockRetryDelay=PT1S, acquireLockMaxRetries=100, fileCharset=UTF-8, waitForDatabase=true, migrationsSchema='public', migrationsTable='fun_migrations', migrationsLockTable='fun_migrations_lock'}
2021-06-17 19:58:51.909  INFO 4400 --- [  restartedMain] n.n.r2dbc.migrate.core.R2dbcMigrate      : Creating new test connection
2021-06-17 19:58:52.523  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Comparing expected value '42' with provided result '42'
2021-06-17 19:58:52.525  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Closing test connection
2021-06-17 19:58:52.532  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Successfully got result '42' of test query
2021-06-17 19:58:52.678  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Making internal tables' 1 rows updated
2021-06-17 19:58:52.692  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Acquiring lock' 1 rows updated
2021-06-17 19:58:52.702  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Database version is 0
2021-06-17 19:58:52.723  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=1, description='schema', splitByLine=false, transactional=true}
2021-06-17 19:58:52.750  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=1, description='schema', splitByLine=false, transactional=true}' 0 rows updated
2021-06-17 19:58:52.793  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Writing metadata version 1' 1 rows updated
2021-06-17 19:58:52.800  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=2, description='tables', splitByLine=false, transactional=true}
2021-06-17 19:58:52.814  WARN 4400 --- [actor-tcp-nio-1] i.r.p.client.ReactorNettyClient          : Notice: SEVERITY_LOCALIZED=NOTICE, SEVERITY_NON_LOCALIZED=NOTICE, CODE=00000, MESSAGE=table "tree" does not exist, skipping, FILE=tablecmds.c, LINE=1217, ROUTINE=DropErrorMsgNonExistent
2021-06-17 19:58:52.986  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=2, description='tables', splitByLine=false, transactional=true}' 0 rows updated
2021-06-17 19:58:53.027  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Writing metadata version 2' 1 rows updated
2021-06-17 19:58:53.036  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=3, description='data', splitByLine=false, transactional=true}
2021-06-17 19:58:53.058  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=3, description='data', splitByLine=false, transactional=true}' 94 rows updated
2021-06-17 19:58:53.072  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Writing metadata version 3' 1 rows updated
2021-06-17 19:58:53.084  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Releasing lock' 1 rows updated
2021-06-17 19:58:53.090  INFO 4400 --- [  restartedMain] n.n.r.m.a.R2dbcMigrateAutoConfiguration  : End of R2DBC migration

Once I connect to the application, I get following error.

postgresql log

database_1  | 2021-06-17 17:56:29.903 UTC [1] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
database_1  | 2021-06-17 17:56:29.910 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
database_1  | 2021-06-17 17:56:29.910 UTC [1] LOG:  listening on IPv6 address "::", port 5432
database_1  | 2021-06-17 17:56:29.939 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
database_1  | 2021-06-17 17:56:29.960 UTC [51] LOG:  database system was shut down at 2021-06-17 17:56:29 UTC
database_1  | 2021-06-17 17:56:29.972 UTC [1] LOG:  database system is ready to accept connections
database_1  | 2021-06-17 18:03:52.818 UTC [65] ERROR:  relation "tree" does not exist at character 15
database_1  | 2021-06-17 18:03:52.818 UTC [65] STATEMENT:  SELECT * FROM TREE

After restarting the spring boot application, everything works perfectly fine. I assume, public.tree, which does not exist, is selected before restart. Once the application is restarted, fun.tree is selected. So, this happens only after this very first migration. How can I make the search_path which is used during migration persistent? Alternatively, how would I reload the connection after the migration, such that the role defined search_path is used?

Update 2021-06-18

I have found the reason for this issue. spring-boot-starter-data-r2dbc pulls in io.r2dbc:r2dbc-pool. Which creates 10 connections before ALTER ROLE current_user SET search_path TO 'fun'; is executed. SET search_path TO 'fun'; is only valid for the one session in which the migration runs.

So the question comes down to, how can I refresh all connections of the pool?

score 0 · Answer 1 · answered Jun 17 '21 at 19:20

0

Please try set LOCAL option, After the set command as the following example:

SET LOCAL search_path TO 'fun';

Specifies that the command takes effect for only the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again. Note that SET LOCAL will appear to have no effect if it is executed outside a BEGIN block since the transaction will end immediately. For more detail: https://www.postgresql.org/docs/9.1/sql-set.html

answered Jun 17 '21 at 19:20

Meysam Karimi

97
8

Thanks for your answer, I have tried it. With this, tree table is created in public. I want to have it in fun. Once I restart the application fun.tree cannot be found. – conspicillatus Jun 18 '21 at 04:28

Changing PostgreSQL schema only active after reload of Spring Boot application

1 Answers1