Re: BUG #17696: Creation of deferrable initially deferred constraint breaks before transaction commit

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> On 2022-Nov-24, David G. Johnston wrote:
>> I don't see any benefit for the effort making it work for DDL.

> Yeah ... I agree that this scenario seems rather pointless, but I don't
> see any hard argument for making it not work, other than development
> effort.

There would need to be a side-eye on whether it breaks pg_dump scripts
being run in single-transaction mode. Verifications of deferred fkey
constraints would all be delayed till the final COMMIT, which at the
very least is not what pg_dump is expecting.

I think it'd pose a problem for parallel restores, in which restoration
of such constraints could no longer be parallelized. I don't think we
have any cases in which a subsequent view creation would actually fail,
but I can believe that a subsequent matview-population query would run
much more slowly than expected because the fkey relationship couldn't
be used yet for plan optimization.

On the whole I share David's guess that the cost/benefit ratio of
making this happen is unattractive. I might feel impelled to do
it anyway if somebody could show that the SQL spec requires it ...
but I bet you'll search for that in vain, because I doubt that the
SQL spec requires transactional DDL at all. (There are too many
influential implementations that would have a problem with that.)

regards, tom lane