dropAll fails on RedShift

General Discussion
1

Hi,


I would like to execute dropAll on our RedShift DB, and I get this strange error. Unfortunatelly I don’t even see from the debug output what SQL statement is causeing the problem (see below). On one line it states " … 23 more



For more information, use the --logLevel flag


2

It appears to be a problem with the postgres driver. Liquibase is calling out to MetaData.getImportedKeys() which calls https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java#L46-59 and the version that redshift says it is doesn’t match the SQL reshift supports.


It is in the logic to get foreign keys, but redshift doesn’t really support foreign keys, right? Could the whole “get foreign keys” logic be no-op’ed out?

Nathan

3

The diff uses the snapshot logic to find objects to drop. I committed a change to the extension to make foreign key snapshots a no-op. It fixes the dropAll but it makes diffs between redshift and databases that do support foreign keys think that redshift has no FKs. Probably all we can do until we can figure out how to pull the foreign key info in redshift.


Can you try the code in liquibase-redshift master?

Nathan

4

It is built with maven, so it uses the pom.xml file in the root of the directory.


I heard from someone else that the fix seems to work, so I created a new build and published it as 1.0 snapshot #5. 


Nathan

5

I think I see the problem. The old foreign key logic was still being called because of a limitation in the extension API in liquibase 3.0.7 which the extension was built on.


I created https://github.com/liquibase/liquibase-redshift/releases/tag/snapshot-6 that updates support to liquibase 3.1.1 and should replace the standard foreign key snapshot logic.


Could you try the new version?

Nathan

6

Good, thanks for the update. 


Nathan

7

Yes, foreign keys are informal, only to help the optimizer. So as we’re talking about dropAll, anyway all the tables will be deleted, so,it doesn’t matter in which order you delete them: redshift won’t stop you, as foreign keys are not enforced, and the result will be consistent, when we finish to delete all the tables. You can ignore them IMHO

8

Nathan,


how am I supposed to compile the code? I don’t see any build.gradle or similar config, just the java files.

Until now I only added the liquibase-redshift-1.0-SNAPSHOT.jar to our lib/ directory.

9

It didn’t work:


DEBUG 2/17/14 6:27 PM:liquibase: Connected to innotest@jdbc:postgresql://blabla.redshift.amazonaws.com:5439/test

DEBUG 2/17/14 6:27 PM:liquibase: Setting auto commit to false from true

DEBUG 2/17/14 6:27 PM:liquibase: Executing QUERY database command: select count(*) from public.databasechangeloglock

DEBUG 2/17/14 6:27 PM:liquibase: Executing QUERY database command: SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1

DEBUG 2/17/14 6:27 PM:liquibase: Lock Database

DEBUG 2/17/14 6:27 PM:liquibase: Executing UPDATE database command: UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = ‘galaxy (fe80:0:0:0:800:27ff:fe00:0%4)’, LOCKGRANTED = ‘2014-02-17 18:27:20.394’ WHERE ID = 1 AND LOCKED = FALSE

INFO 2/17/14 6:27 PM:liquibase: Successfully acquired change log lock

INFO 2/17/14 6:27 PM:liquibase: Dropping Database Objects in schema: test.public

DEBUG 2/17/14 6:27 PM:liquibase: Executing QUERY database command: SELECT MD5SUM FROM public.databasechangelog WHERE MD5SUM IS NOT NULL

DEBUG 2/17/14 6:27 PM:liquibase: Executing QUERY database command: select count(*) from public.databasechangeloglock

DEBUG 2/17/14 6:27 PM:liquibase: Executing QUERY database command: SELECT relname AS SEQUENCE_NAME FROM pg_class, pg_namespace WHERE relkind=‘S’ AND pg_class.relnamespace = pg_namespace.oid AND nspname = ‘public’ AND ‘nextval(’‘public.’||relname||’’’::regclass)’ not in (select adsrc from pg_attrdef where adsrc is not null) AND ‘nextval(’‘public."’||relname||’"’’::regclass)’ not in (select adsrc from pg_attrdef where adsrc is not null) AND ‘nextval(’’’||relname||’’’::regclass)’ not in (select adsrc from pg_attrdef where adsrc is not null)AND ‘nextval(’’"’||relname||’"’’::regclass)’ not in (select adsrc from pg_attrdef where adsrc is not null)

DEBUG 2/17/14 6:27 PM:liquibase: Release Database Lock

DEBUG 2/17/14 6:27 PM:liquibase: Executing UPDATE database command: UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1

INFO 2/17/14 6:27 PM:liquibase: Successfully released change log lock

Liquibase dropAll Failed: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: Unable to determine a value for MaxIndexKeys due to missing system catalog data.

SEVERE 2/17/14 6:27 PM:liquibase: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: Unable to determine a value for MaxIndexKeys due to missing system catalog data.

liquibase.exception.DatabaseException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: Unable to determine a value for MaxIndexKeys due to missing system catalog data.

… 23 more


10

it works now, thanks