| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, jian he <jian(dot)universality(at)gmail(dot)com> |
| Subject: | Rework: Enable fast default for domains with non-volatile constraints |
| Date: | 2026-11-17 02:28:32 |
| Message-ID: | DE69B65E-4621-4AF4-9A00-6A042E565C90@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
# Background
A PG19 feature, “[a0b6ef29a] Enable fast default for domains with non-volatile constraints”, was reverted; see discussion [1]. This is a rework of the feature.
In short, the problem was that:
```
evantest=# create domain d_div as int check (1 / (value - 1) > 0);
CREATE DOMAIN
evantest=# create table t (a int);
CREATE TABLE
evantest=# alter table t add column b d_div default 1;
ERROR: division by zero
```
The “alter table” fails even when the table is empty because it evaluates the domain constraints against the default value, and that evaluation fails. But “create table” succeeds because it doesn’t perform this evaluation:
```
create table t2 (a int, b d_div default 1);
```
Tom pointed out that this behavior is inconsistent. He suggested adding a new TAM method to determine whether a relation is empty, see [2]. If it is, we can skip evaluating the default expression.
# Implementation
This patch follows that approach:
* 0001 adds a new TAM method, relation_is_empty.
* 0002 reimplements the feature “Enable fast default for domains with non-volatile constraints” using the new TAM method.
In discussion [1], Jian also pointed out another inconsistency for non-domain types:
```
create table t2 (a numeric default (1::numeric/0.0::float4)); -- ok
alter table t2 add column b numeric default ((1::numeric/0.0::float4)); -- error
```
This patch fixes that case as well.
In [1], it was discussed that the heap TAM’s relation_is_empty() could simply check “RelationGetNumberOfBlocks() == 0”, which does not provide an exact answer. For example, it cannot cover a case like:
```
begin;
delete from t; -- now the table is empty, but RelationGetNumberOfBlocks() != 0
alter table t add column …
```
But Tom considered that this would not be a major issue; see [2], where he said:
```
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.
```
While implementing this patch, I thought that, once relation_is_empty() is introduced as a generic method, some other callers may want an exact answer. So I added a boolean argument, fast_decision, to it. For the heap TAM, if fast_decision is true, it only checks “RelationGetNumberOfBlocks() == 0”; otherwise, it scans heap pages looking for a live tuple.
0002 includes all regression tests from the reverted commit a0b6ef29a, Jian as the author of that commit, so I listed him as an author in 0002.
# Open Discussion
1. Do we really need the fast_decision parameter for the new relation_is_empty() method?
I searched the source tree for uses of “RelationGetNumberOfBlocks() == 0”, but couldn’t find an existing check suitable for replacement with the new method. I also don’t have another concrete caller that needs an exact answer. So, on further thought, I feel we might not really this parameter.
2. Should we use an exact check for this ALTER TABLE case?
Compared with the cost of rewriting the table when a rewrite is needed, scanning for a live tuple seems acceptable. For nonempty tables, the scan can stop at the first live tuple. It could be more expensive if many rows have been deleted, but deleting many rows and then adding a column seems like a relatively uncommon use case, so I expect the overall impact to be limited. I think the more consistent behavior is worth the potential scanning cost. So I would favor always using an exact check here.
Comments and feedback on these questions would be greatly appreciated.
[1] https://www.postgresql.org/message-id/7033D663-DDB4-4B35-922C-F33DE53B1502@gmail.com
[2] https://www.postgresql.org/message-id/36611.1780777185@sss.pgh.pa.us
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Add-a-table-AM-method-to-check-whether-a-relation.patch | application/octet-stream | 10.8 KB |
| v1-0002-Allow-fast-defaults-for-nonvolatile-domains-and-s.patch | application/octet-stream | 14.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Haibo Yan | 2026-11-17 02:35:51 | [PATCH v1] pg_stash_advice: Reject embedded NUL bytes in persistence files |
| Previous Message | Denis Smirnov | 2026-11-17 02:26:23 | Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE |