Re: Idempotent DDL Updates

On Sat, Aug 28, 2021 at 2:19 AM Miles Elam <miles(dot)elam(at)productops(dot)com> 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?
> [...]
> 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?

Note that the IF EXISTS / IF NOT EXISTS are *not* idempotent. If you
need to write idempotent schema update scripts, you need to query the
catalogs to check if the specific change you want to apply has already
been applied or not.