On 8/27/21 11:19 AM, Miles Elam wrote:
What is the general consensus within the community on idempotent DDL
scripts, ie. consistent usage of IF EXISTS/IF NOT EXISTS/OR REPLACE for
SQL init files that get checked into source control?
My experience has been that it's much easier to manage than an
ever-growing set of migration files, but I'm just a data point of one.
Obviously for other engines that don't support transactional DDL, it's a
non-starter, which leads me toward the notion that its lack of obvious
popularity is due to its limited cross-engine viability. But PG does
have transaction DDL, preventing incomplete DDL updates. However this
may just be my personal bias talking. Yet I cannot seem to discount the
advantages over ever-increasing volumes of Flyway-style migration files
& directories:
* being able to do diffs of changes in source control
* limiting the size of the total SQL over time relative to what's
actually in the DB structure
* much more easily determining the most current version of a
function/procedure (this bit me hard in the past with dozens of
migration files)
* the ability to prune old changes that no longer apply to any
deployed version of the database
* treating database structure as code
The drawbacks I've run across are those areas where the EXISTS/REPLACE
constructs aren't implemented like roles management, domains,
constraints, etc. However those cases seem to be handled with only minor
increases in complexity with judicious use of inline plpgsql.
In others' opinions, has DDL idempotency been viable for maintenance of
PG databases fo you in production?
For me at least you will need to show examples of what you trying to
achieve. I'm not seeing how a migration(change) can happen without a
change of some sort. More to the point how *EXISTS/OR REPLACE helps?
- Miles Elam
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
