Generated diff has incorrect syntax

[pematt]

Generated diff has incorrect syntax

[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!