Re: Idempotent DDL Updates

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(dot)klaver(at)aklaver(dot)com