search

perseas / Pyrseas

Provides utilities for Postgres database schema versioning.
https://perseas.github.io/
BSD 3-Clause "New" or "Revised" License
395 stars 67 forks source link

"PARTITION BY" not recognized as diff if added to existing table. #200

Open Glupschauge opened 5 years ago

Glupschauge commented 5 years ago

When comparing following schemas, "PARTITION BY" is not recognized as a diff if it is added to an already existing table.

schema a: CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );

schema b: CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);

Following commands shows no difference. dbtoyaml schema_a -o schema_a.yaml yamltodb schema_b schema_a.yaml

I know this is a special case, however I would be grateful if this problem could be fixed.

jmafc commented 5 years ago

This is somewhat related to the limited support of PARTITION BY RANGE (#178), but it's probably (easily) doable by adding a check when diffing tables.

jmafc commented 5 years ago

Now that I started researching this issue, and in particular the ALTER TABLE statement, I cannot find a clause to remove a PARTITION BY specification (or for that matter, a clause to add one). So it appears that the only behavior that yamltodb could implement is:

DROP TABLE sch.measurement;
CREATE TABLE sch.measurement (...);

Is that what you want? It seems to me that this opens a whole can of worms because in a realistic production scenario, table measurement (with a PARTITION BY spec) wouldn't just exist by itself, but would have various partitions, so perhaps they would first have to be DETACHed, and then? What happens with the data in those partitions? And of course, there's the converse scenario: the table is not partitioned in the database, but the input YAML says it is.