New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostgreSQL: clean doesn't unlink referenced LOBs in pg_largeobject #1934

Open
zinnchen opened this Issue Feb 26, 2018 · 5 comments

Comments

Projects
None yet
2 participants
@zinnchen

zinnchen commented Feb 26, 2018

Which version and edition of Flyway are you using?

5.0.2

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)
Don't know. I can't use 5.0.7 because there is another issue where some classes can't be found.

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using (type & version)?

Postgres 9.6

Which operating system are you using?

Windows

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)
I have two schemas in postgres database. First the default one 'postgres' and separate schema for application tables ('app_schema'). I store blobs in my application schema which are stored as large object. These large objects are stored in table 'pg_largeobject' in catalog 'pg_catalog'.

I run the following Java code test preparation:
// prepare database
Schema schema;
try (Database database = DatabaseFactory.createDatabase(migrator, false)) {
schema = database.getMainConnection().getSchema("app_schema");
if (schema.exists()) {
schema.clean();
} else {
schema.create();
}
}

What did you expect to see?

I expect that schema.clean() does clean the 'app_schema' AND the referenced entries in 'pg_catalog' catalog.
In my case I expect that the entries in 'pg_largeobject' which are referenced by entries in 'app_schema' will be deleted.

What did you see instead?

I see remaining entries in catalog 'pg_catalog'.'pg_largeobject' which not referenced anymore.

@zinnchen zinnchen changed the title from schema.clean() doesn't clean entries in other schema to schema.clean() doesn't clean referenced entries in other schema Feb 26, 2018

@axelfontaine

This comment has been minimized.

Contributor

axelfontaine commented Feb 26, 2018

Hmmm I don't see an easy solution to this. Your table will most likely only contain OIDs. We would have to check whether these belong to large objects and if they do, unlink them. But that's not necessarily safe as these LOBs may still be referenced from a different schema that the user doesn't want cleaned.

Suggestions welcome.

Until this is resolved, I'll document it as a limitation. You can workaround this by using callbacks and doing the work there.

P.S.: Please report the other issue you face with 5.0.7 as well.

@axelfontaine axelfontaine changed the title from schema.clean() doesn't clean referenced entries in other schema to schema.clean() doesn't clean referenced LOBs in pg_largeobject Feb 26, 2018

@axelfontaine axelfontaine changed the title from schema.clean() doesn't clean referenced LOBs in pg_largeobject to PostgreSQL: clean doesn't unlink referenced LOBs in pg_largeobject Feb 26, 2018

@zinnchen

This comment has been minimized.

zinnchen commented Feb 27, 2018

I updated the error descriptions since the table 'pg_largeobject' isn't located in schema 'postgres'. It is located in catalog 'pg_catalog'

@axelfontaine

This comment has been minimized.

Contributor

axelfontaine commented Feb 27, 2018

Any suggestion on how to resolve this in a safe and generic manner?

@zinnchen

This comment has been minimized.

zinnchen commented Feb 27, 2018

After some research in documentation of postgresql I found the build in utility 'vacuumlo' which removes orphaned large objects. May be it is possible to call this function during clean.

vacuumlo is a simple utility program that will remove any "orphaned" large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database.

see: https://www.postgresql.org/docs/9.6/static/vacuumlo.html

@axelfontaine

This comment has been minimized.

Contributor

axelfontaine commented Feb 27, 2018

That has a number of issues. First of all we couldn't assume the CLI tool is installed, second it is challenging on RDS: https://dba.stackexchange.com/questions/174663/how-to-vacuumlo-an-rds-postgresql-database#174664

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment