dropPrimaryKey doesnt work with PGSQL

General Discussion
1

I tried drop a primary key without declare the constraint name and I received this error:

Caused by: liquibase.exception.DatabaseException: ERROR: constraint “num_liquidation_pkey” of relation “num_liquidation” does not exist
Where: SQL statement “alter table public.num_liquidation drop constraint NUM_LIQUIDATION_pkey”
PL/pgSQL function inline_code_block line 7 at EXECUTE [Failed SQL: (0) DO $$ DECLARE constraint_name varchar;
BEGIN
SELECT tc.CONSTRAINT_NAME into strict constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND TABLE_NAME = ‘num_liquidation’ AND TABLE_SCHEMA = ‘public’;
EXECUTE 'alter table public.num_liquidation drop constraint ’ || constraint_name;

I noticed that an error in SQL statment, the constraint name is not in quotes. Is this a bug or I configurate wrong?

Yaml:
- dropPrimaryKey:
dropIndex: true
schemaName: public
tableName: NUM_LIQUIDATION

Liquibase Version: 4.4.0
Liquibase Community 4.4.0 by Datical

2

Hi @vagner.montenegro ,

Would you be able to help us with the steps to reproduce this issue at our end? What exactly should be done to reproduce it?

Thanks!
Rakhi Agrawal

3

My project is Java Spring Boot.

my pom:

	<dependency>
		<groupId>org.liquibase</groupId>
		<artifactId>liquibase-core</artifactId>
		<version>4.4.0</version>
	</dependency>
	<dependency>
		<groupId>org.postgresql</groupId>
		<artifactId>postgresql</artifactId>
		<version>42.2.22</version>
	</dependency>

My changelog:

Yaml:

  • dropPrimaryKey:
    dropIndex: true
    schemaName: public
    tableName: NUM_LIQUIDATION

I run the follow command: mvn spring-boot:run

And on startup the app, the erro was showed. As show in the error the follow SQL is run:

SELECT tc.CONSTRAINT_NAME into strict constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND TABLE_NAME = ‘num_liquidation’ AND TABLE_SCHEMA = ‘public’;
EXECUTE 'alter table public.num_liquidation drop constraint ’ || constraint_name;

I executed this SQL in my PgAdmin and I have the same error, but when I replace the constraint_name for name in quotes, the SQL run normally

4

Hi @vagner.montenegro
You need to provide the name of the primary key that you want to drop. Just like you said when you replace the constraint_name with the name it will run normally.
dropPrimaryKey:
dropIndex:true
schemaName:public
tableName:NUM_LIQUIDATION
constraintName: theprimarykeyconstraintname

And you need to add the right amount of tabs for the yaml section

5

but this parameter is not mandatory, including liquibase makes a query in the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table to get the name of constraint.

If you look at the error, you’ll see that liquibase performs a function just for that.

6

Does this work with Liquibase 4.3.5? If so it’s a bug we introduced in 4.4.x. Please create an issue on our Github site.

If it still fails, then we can look at more options.

7

Hi @vagner.montenegro
Did you manage to resolve the issue
or have you created a ticket on it?
10X in advance, Itai