Re: SERIAL datatype column skipping values.

Andreas Karlsson <andreas(at)proxel(dot)se> writes:
> On 3/11/20 11:15 AM, Prabhat Sahu wrote:
>> Is this an expected behavior?

> Curious, it seems like DEFAULT expressions of a table are executed an
> extra time if a set returning function is used like in your example. And
> the SERIAL type is implemented using DEFAULT.

Yeah, it's the same as if you do

regression=# select generate_series(1,2), test_default();
NOTICE: Ran test_default()
NOTICE: Ran test_default()
NOTICE: Ran test_default()
generate_series | test_default
-----------------+--------------
1 | 42
2 | 42
(2 rows)

The generated plan is

regression=# explain verbose select generate_series(1,2), test_default();
QUERY PLAN
-------------------------------------------------
ProjectSet (cost=0.00..0.28 rows=2 width=8)
Output: generate_series(1, 2), test_default()
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)

and if you read nodeProjectSet.c you'll see that it needs to evaluate
the target list three times. On the third iteration, generate_series()
returns isdone == ExprEndResult indicating that it has no more results,
so we don't emit an output tuple --- but we still run test_default()
while scanning the tlist.

Possibly the planner should try to avoid putting volatile expressions
into ProjectSet's tlist. On the other hand, it's worked this way for
an awfully long time, so I wonder if anyone is relying on the behavior.
Even in versions before we used ProjectSet nodes, you still see three
calls to the volatile function.

Anyway, to get back to the OP's implied question, no you should never
assume that a SERIAL column's values won't have holes in the sequence.
Rolled-back transactions will have that effect in any case.

regards, tom lane