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/[email protected]
[2]  https://www.postgresql.org/message-id/[email protected]

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/




Attachment: v1-0001-Add-a-table-AM-method-to-check-whether-a-relation.patch
Description: Binary data

Attachment: v1-0002-Allow-fast-defaults-for-nonvolatile-domains-and-s.patch
Description: Binary data

Reply via email to