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.klaver@aklaver.com