From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Roman Garcia <yzerno@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17696: Creation of deferrable initially deferred constraint
 breaks before transaction commit
In-reply-to: <20221124222356.qolurdwwcnxkbjt2@alvherre.pgsql>
References: <20221124222356.qolurdwwcnxkbjt2@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Thu, 24 Nov 2022 23:23:56 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3976548.1669344944.1@sss.pgh.pa.us>
Date: Thu, 24 Nov 2022 21:55:44 -0500
Message-ID: <3976549.1669344944@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3976549.1669344944%40sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.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