Hi,
I’m new to liquibase, and just trying to get a grasp of its capabilities.
I’ve hit an issue when trying to generate a changelog that includes data.
I’m using a postgres database, I’ve been able to generate changelogs with the default set of diffTypes, but If I try to generate the changelog including the “data” difftype I get an error.
Unexpected error running Liquibase: ERROR: current transaction is aborted, commands ignored until end of transaction block
- Caused by: ERROR: CASE types text and "char" cannot be matched
Position: 939
In order to eliminate anything strange with our schema/data, I tried this with a completely blank new database, and got no error but obviously no data in the output file.
I then created a single table with a single integer column - still no rows in the database.
I get the same error.
The command I’m using is:
liquibase --username=user --password=pass --url=jdbc:postgresql://localhost:5434/r2 --changelog-file new_db.xml --diffTypes="data" generate-changelog
If I increase the log level, I get the following:
[2022-11-22 10:25:50] FINE [liquibase.executor] Executing with the 'jdbc' executor
[2022-11-22 10:25:50] FINE [liquibase.diff] Cannot get object dependencies: Error executing SQL WITH RECURSIVE preference AS (
SELECT 10 AS max_depth -- The deeper the recursion goes, the slower it performs.
, 16384 AS min_oid -- user objects only
, '^(londiste|pgq|pg_toast)'::text AS schema_exclusion
, '^pg_(conversion|language|ts_(dict|template))'::text AS class_exclusion
, '{"SCHEMA":"00", "TABLE":"01", "CONSTRAINT":"02", "DEFAULT":"03",
"INDEX":"05", "SEQUENCE":"06", "TRIGGER":"07", "FUNCTION":"08",
"VIEW":"10", "MVIEW":"11", "FOREIGN":"12"}'::json AS type_ranks),
dependency_pair AS (
WITH relation_object AS ( SELECT oid, oid::regclass::text AS object_name FROM pg_class )
SELECT DISTINCT substring(pg_identify_object(classid, objid, 0)::text, E'(\\w+?)\\.') as referenced_schema_name, CASE classid
WHEN 'pg_constraint'::regclass THEN (SELECT CONTYPE FROM pg_constraint WHERE oid = objid)
ELSE objid::text
END AS CONTYPE,
CASE classid
WHEN 'pg_attrdef'::regclass THEN (SELECT attname FROM pg_attrdef d JOIN pg_attribute c ON (c.attrelid,c.attnum)=(d.adrelid,d.adnum) WHERE d.oid = objid)
WHEN 'pg_cast'::regclass THEN (SELECT concat(castsource::regtype::text, ' AS ', casttarget::regtype::text,' WITH ', castfunc::regprocedure::text) FROM pg_cast WHERE oid = objid)
WHEN 'pg_class'::regclass THEN rel.object_name
WHEN 'pg_constraint'::regclass THEN (SELECT conname FROM pg_constraint WHERE oid = objid)
WHEN 'pg_extension'::regclass THEN (SELECT extname FROM pg_extension WHERE oid = objid)
WHEN 'pg_namespace'::regclass THEN (SELECT nspname FROM pg_namespace WHERE oid = objid)
WHEN 'pg_opclass'::regclass THEN (SELECT opcname FROM pg_opclass WHERE oid = objid)
WHEN 'pg_operator'::regclass THEN (SELECT oprname FROM pg_operator WHERE oid = objid)
WHEN 'pg_opfamily'::regclass THEN (SELECT opfname FROM pg_opfamily WHERE oid = objid)
WHEN 'pg_proc'::regclass THEN objid::regprocedure::text
WHEN 'pg_rewrite'::regclass THEN (SELECT ev_class::regclass::text FROM pg_rewrite WHERE oid = objid)
WHEN 'pg_trigger'::regclass THEN (SELECT tgname FROM pg_trigger WHERE oid = objid)
WHEN 'pg_type'::regclass THEN objid::regtype::text
ELSE objid::text
END AS REFERENCED_NAME,
substring(pg_identify_object(refclassid, refobjid, 0)::text, E'(\\w+?)\\.') as referencing_schema_name, CASE refclassid
WHEN 'pg_namespace'::regclass THEN (SELECT nspname FROM pg_namespace WHERE oid = refobjid)
WHEN 'pg_class'::regclass THEN rrel.object_name
WHEN 'pg_opfamily'::regclass THEN (SELECT opfname FROM pg_opfamily WHERE oid = refobjid)
WHEN 'pg_proc'::regclass THEN refobjid::regprocedure::text
WHEN 'pg_type'::regclass THEN refobjid::regtype::text
ELSE refobjid::text
END AS REFERENCING_NAME
FROM pg_depend dep
LEFT JOIN relation_object rel ON rel.oid = dep.objid
LEFT JOIN relation_object rrel ON rrel.oid = dep.refobjid, preference
WHERE deptype = ANY('{n,a}')
AND objid >= preference.min_oid
AND (refobjid >= preference.min_oid OR refobjid = 2200) -- need public schema as root node
AND classid::regclass::text !~ preference.class_exclusion
AND refclassid::regclass::text !~ preference.class_exclusion
AND COALESCE(SUBSTRING(objid::regclass::text, E'^(\\\\w+)\\\\.'),'') !~ preference.schema_exclusion
AND COALESCE(SUBSTRING(refobjid::regclass::text, E'^(\\\\w+)\\\\.'),'') !~ preference.schema_exclusion
GROUP BY classid, objid, refclassid, refobjid, deptype, rel.object_name, rrel.object_name
)
select referenced_schema_name,
(CASE
WHEN position('.' in referenced_name) >0 THEN substring(referenced_name from position('.' in referenced_name)+1 for length(referenced_name))
ELSE referenced_name
END) AS referenced_name,
referencing_schema_name,
(CASE
WHEN position('.' in referencing_name) >0 THEN substring(referencing_name from position('.' in referencing_name)+1 for length(referencing_name))
ELSE referencing_name
END) AS referencing_name from dependency_pair where REFERENCED_NAME != REFERENCING_NAME AND ( REFERENCED_NAME like 'public.%' OR REFERENCED_NAME NOT LIKE '%.%')
AND (CONTYPE::text != 'p' AND CONTYPE::text != 'f')
AND referencing_schema_name is not null and referencing_name is not null: ERROR: CASE types text and "char" cannot be matched
Position: 939
Any help would be appreciated.
Thanks
Jas