Message-ID: <12802538-383a-4464-9a7d-de1d98bd6d08@dunslane.net>
Date: Mon, 8 Jun 2026 11:40:27 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Fix domain fast defaults on empty tables
To: Chao Li <li.evan.chao@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, pgsql-hackers@lists.postgresql.org,
 jian he <jian.universality@gmail.com>
References: <7033D663-DDB4-4B35-922C-F33DE53B1502@gmail.com>
 <219843CF-9B49-404A-838D-88D51902B978@iki.fi>
 <3590551.1780668509@sss.pgh.pa.us>
 <203a49c2-52e4-49ab-b644-ef6044266145@dunslane.net>
 <36611.1780777185@sss.pgh.pa.us>
 <801E695B-F659-4D21-95E8-02F9EE505AF8@gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <801E695B-F659-4D21-95E8-02F9EE505AF8@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/12802538-383a-4464-9a7d-de1d98bd6d08%40dunslane.net>
Precedence: bulk


On 2026-06-06 Sa 10:09 PM, Chao Li wrote:
>
>> On Jun 7, 2026, at 04:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Andrew Dunstan <andrew@dunslane.net> writes:
>>> On 2026-06-05 Fr 10:08 AM, Tom Lane wrote:
>>>> Concretely, I'm pretty sure it is a hazard for pg_dump, which thinks
>>>> it can freely transform bits of CREATE operations into ALTERs.
>>>> I didn't try to make an example case, but I suspect it is now possible
>>>> to create a database that will fail dump/restore because of this
>>>> inconsistency.
>>> Seems reasonable. So which of Chao's solutions do you prefer? I think
>>> both will meet the pg_dump issue, not sure how much we care about the
>>> case where we have deleted all the rows but not truncated the table.
>> [ studies it a bit ...]  TBH, I don't like either of these,
>> nor do I like a0b6ef29a to begin with.  The fundamental problem
>> with this whole mess is that it treats certain kinds of
>> default-expression evaluation error (i.e., domain CHECK failures)
>> differently from others.  There is no way that that leads to a
>> consistent user experience.  The current complaint is one
>> manifestation of that, but I'm sure there are others, eg having
>> to do with domain coercions lower down in a default expression.
>>
>> It could work if ExecPrepareExprWithContext were capable of
>> soft-trapping essentially all execution errors, but that's
>> not true today and I strongly doubt it ever will be true.
>>
>> I think Chao's v1-s2-0001 points the way towards what could be a
>> workable solution: if we see that the table is known empty (after
>> we already have exclusive lock on it!), we could skip both the table
>> rewrite and the insertion of an attmissingval, and thereby not need
>> to evaluate the default at all during ALTER TABLE.  As Chao says,
>> simplistic versions of "known empty" would expose some user-visible
>> behavioral inconsistencies, but I'm not sure how much that matters.
>> For pg_dump and similar applications we would get the behavior we
>> needed even with just an is-physically-empty check, since all their
>> CREATE/ALTER DDL happens before we ever insert any data.  You could
>> imagine going further and scanning the table for live tuples, but
>> I don't know that that's going to be worth the cycles.
>>
>> However, I doubt that a bare "RelationGetNumberOfBlocks() == 0" check
>> is acceptable: we probably need to let the table access method have
>> control of this.  heapam could do "RelationGetNumberOfBlocks() == 0",
>> but other TAMs might need to do something else.  So that means that
>> this path requires a new TableAmRoutine method, and that probably
>> puts it in the too-late-for-v19 category.
>>
>> My recommendation: we ought to revert a0b6ef29a for now and
>> redesign the optimization for v20.
>>
>> If you don't like that answer, I'd be firmly against v1-s1-0001
>> in any case.  It repeats the classic mistake of supposing that
>> we can PG_CATCH random errors and not invoke transaction cleanup.
>>
>> BTW, I do not like the fact that a0b6ef29a removed the comment
>> stanza explaining why we need this fake default expression at all.
>> That's still largely applicable AFAICS.
>>
>> regards, tom lane
> Thanks, Tom, for the detailed explanation.
>
> I'll hold off here and wait for Andrew's decision on how to proceed. Sounds like reverting a0b6ef29a may be the preferred option for now.
>
> For v1-s2, I’m not ware of a better way to decide whether a table is known empty. I ever considered a count(*)-style scan or a LIMIT 1 scan, but those seem worse, they would add a new table scan inside ALTER TABLE just to decide whether to evaluate the default.
>

Given Tom's reasonable suggestion that we really need a new Table AM 
callback to do this sensibly, I agree we should revert it. Will do.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com