Options to remove IF EXISTS (and similar) statements from generated scripts about pg-diff HOT 5 OPEN

@olivierdalang

You have to consider the reason why this tool born.
The main purpose is to help team of developers to make changes on DB (manually or not, like code-first approch).
Each member of the team must always have an updated database version before make any change.
In a multi version and multi tenant application (no matter what kind of application is) is very important to apply patch only on objects that must receive that patch.

However, at the moment i will keep this issue in stand-by for future release (in the end why not, it's up to you set the configuration you need 😄, but the tool should give you all sensible features possible ).

from pg-diff.

Dear @olivierdalang ,

The feature you mention was there on version 1.x and after a thoughtful analysis has been deprecated and removed on version 2.x.
The reason why has been removed that feature is because after a quality test an IDEMPOTENT statement produce better result\usage on every kind of needs or database configuration\state.

Let me analyze deeply your comment 😄.

You said

"For instance, ALTER TABLE IF EXISTS ... would silently fail if the table does not exist, and one could think the migration did correctly apply, while it failed because of a major issue (a missing table !!)"

That should never happens because if pg-diff detect a missing table it will generate a CREATE TABLE IF NOT EXIST statement, more over an ALTER TABLE IF EXISTS will be generated only if a table has been found.

The only way to execute an ALTER TABLE IF EXISTS where the table effectively doesn't exists is to apply a patch generated between diffing two database on a third database which has an inconsistent state.
But every patch should be generated exactly for the target database in order to collect real differences between.
Also, every target databaes where you will apply the patch must apply all patches not yet applied in exactly same order as patch creation date; else is obviously that different target database will have inconsistent state.

In case i didn't convince you about my thought, can you explain me better what really happend in your environement?
Why the table wasn't on target database which was instead expected by my tool ?

from pg-diff.

You're right, in the case the database was correctly managed, that should never happen.

But unfortunately, other cases are not uncommon (e.g. user made manual changes on the DB, or migrating a legacy project, or for whatever bad reason patches were not applied in the correct order, etc.).

(note that in my case, I'm just using the diff part of the tool, not the rest of it about managing migrations, and I'm indeed expecting some of the databases I'm working with to be in an inconsistent state)

from pg-diff.

Dear @olivierdalang,

We must have a shared starting point to reach the target 😄 .
Can we assume that:

1. You run a DIFF between a source db ("the source of truth") and a first target db
2. The source db never change, i mean connection string (at least per domain app)
3. Once patch has been generated, it can executed (no matter how) on first target db, but also on many other target DB

If we can starts from those assumptions, i guess you have to change your strategy, because:

1. Even with NOT IDEMPOTENT statements, at least you will receive an exception executing a patch on target DB different than first target DB. I wanna suggest to create a script (shell, js, whatever you want, etc.) in order to loop between TARGET DBs, for each of them RUN diff tool that will generate a patch specific for TARGET DB.
2. Moreover if statement is NOT IDEMPOTENT, the exception could be rised for any other kind of issue not related to SCHEMA or DATA changes or not dependant to pg-diff at all; in this case will be very difficult to handle error 'cause pg-diff or other error in execution stack
3. If you try to apply patch made for a different target db also every SQL OBJECTS DEPENDENCIES TREE could be wrong (probably it will be), then make no sense to me try to apply a patch and hope it will not encounter error, how should i handle db-regression?
4. If you don't know which patch has been already applied (in case you not use migration and other from pg-diff), the db can be wasted with too many statements executed where should not.
   Please, image a patch 0 which add a column, a patch 1 which remove that column, a patch 2 which re-add the column with same name but different type; how you know execution order and what to not execute because already executed?
   If you applied in mixed order then DOOM!.
   If you apply in correct order but more and more times, you will never have error (also not idempotent statements could play without issue) but you can reset the content of the column every time you execute patches (Maybe not a DOOM but despicable behaviour 😄 ).

Finally, a STATEMENTS that fail not cover all your potential issue.

I have following suggestions:

1. Change your strategy in order to be always consistent with target db
2. If you manually change source db (explained on documentation) you have to patch DATABASE of CHANGES to FINAL DATABASE
3. If you manually change target db on a multi target db env, you have to make a new DIFF every time ( or at least at specific time before execute the patch; maybe as step of your DevOps as we do)

At the moment i don't even enought elements to consider "NOT IDEMPOTENT" as a needed feature.
Do you agree?

from pg-diff.

I agree with 3 assumptions, but then can't really follow on what you mean. To clarify :

• In an ideal world (where your database are always in a consistent, known state) : you don't need "IF EXIST", because you already know if the table exists or not.
• In a less ideal world (where your database's state is not always clearly known) : you don't want "IF EXIST", because it could make you think a migration succeeded, while there was an obvious issue. An SQL error like "Relation XXX doesn't exist" is much preferable.

But as said, I'm only using the SQL DIFF part of the tool, not the rest of your migration workflow, so you may not be interested in supporting that.

from pg-diff.