Generated diff has incorrect syntax

[pematt]

Bug description
When adding a column to a partitioned table and doing a diff then the diff DDL has invalid syntax. Most of the ALTER command appears to be missing.

How to reproduce
test.dbm.zip

1. Open the attached test.dbm model.
2. Install it into an empty database.
3. Add one column called new_column to the test.test table.
4. Generate a diff.

The following invalid diff is generated:

-- Diff code generated with pgModeler (PostgreSQL Database Modeler)
-- pgModeler version: 1.1.0
-- Diff date: 2024-02-12 15:21:47
-- Source model: new_database
-- Database: mpdb
-- PostgreSQL version: 16.0

-- [ Diff summary ]
-- Dropped objects: 0
-- Created objects: 1
-- Changed objects: 0

SET search_path=public,pg_catalog,test;
-- ddl-end --


-- [ Dropped objects ] --
ALTER TABLE test.test DETACH PARTITION test.test_2024;
-- ddl-end --
ALTER TABLE test.test DETACH PARTITION test.test_2025;
-- ddl-end --


-- [ Created objects ] --
	new_column smallint,
ALTER TABLE test.test ATTACH PARTITION test.test_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- ddl-end --
ALTER TABLE test.test ATTACH PARTITION test.test_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- ddl-end --

Expected behavior
The generated diff code should be syntactically correct.

Screenshots

Info about your desktop
OS: Linux Mint
Version: 21.1
Window manager: Cinnamon
pgModeler version: 1.1.0
Qt version: 6.2.4

[rkhaotix]

@pematt The original problem is fixed. However, the generated diff still has a semantic problem that I still need to figure out how to solve.

After applying the patch the generate code was:

-- [ Dropped objects ] --
ALTER TABLE test.test DETACH PARTITION test.test_2024;
-- ddl-end --
ALTER TABLE test.test DETACH PARTITION test.test_2025;
-- ddl-end --


-- [ Created objects ] --
ALTER TABLE test.test ADD COLUMN new_column smallint;
-- ddl-end --
ALTER TABLE test.test ATTACH PARTITION test.test_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- ddl-end --
ALTER TABLE test.test ATTACH PARTITION test.test_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- ddl-end --

But if you run the diff above from pgModeler it'll break on the first ALTER TABLE ... ATTACH that's because since the partitions were detached in the first lines of the script we have to add the new columns on all partitions which is not happening according to the diff code.

The lines where ATTACH and DETACH appear are false-positive codes because they are only generated when the partitioning bounding expressions change. It took me a while to figure out what was happening... but pgModeler compares the partitioning bounding expression from the original model and the one imported by the diff process.

It turns out that PostgreSQL converts FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'); to FOR VALUES FROM ('2025-01-01 00:00:00-03') TO ('2026-01-01 00:00:00-03');. Semantically, they are all the same, but for pgModeler not, and that is causing pgModeler to create the code to detach and (re)attach the partitions. The workaround for this issue is to add the hour (and eventually the timezone) to the partitions' bounding expressions in your database model so the mentioned code stops being generated. Your example, unfortunately, hits one of the limitations of pgModeler: the semantical comparison which isn't available at the moment! :(

[pematt]

Ok I will do that. Thank you very much for the detailed explanation how to solve it!