Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error

On Fri, Jan 21, 2022 at 4:20 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

>
> Bug reference: 17376
>
> CREATE FUNCTION myfunc() RETURNS TEXT LANGUAGE plpgsql AS $$
> BEGIN
> SELECT r FROM t1;
> RETURN random()::text;
> END $$;
>
> ALTER TABLE t1 ADD COLUMN r TEXT NOT NULL UNIQUE DEFAULT myfunc();
> --
>
> Results in the error:
> ERROR: could not read block 0 in file "base/84505/84705": read only 0 of
> 8192 bytes
>
> The error message content returned is what I suspect of being a bug, not so
> much that this SQL didn't work.
>
>
About the only error improvement I would care to try to emit here would be
the one that says what one can read in the documentation:

"ERROR: Default expressions shall not execute queries."

Because of that prohibition this confusing error should be rare enough that
if someone breaks the rule and cannot figure out that they did so a
bug-report and (hopefully) quick response should suffice; and fare much
better in a cost/benefit analysis.

So, no, the error message is not a bug - we don't make any promises about
what is going to happen when the rules are broken. In the rare case that
breaking the rule may be a good idea the current state at least allows you
to do so while detecting and prohibiting the case at runtime would not...

David J.