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

On Fri, 21 Jan 2022 at 17:22, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17376
> Logged by: akg
> Email address: adrien(dot)gilmore+pg(at)gmail(dot)com
> PostgreSQL version: 13.5
> Operating system: Linux
> Description:
>
> Hello,
>
> SQL demonstrating the issue on 13.5 is below.
>
> --
> BEGIN;
> CREATE TABLE t1 (id SERIAL PRIMARY KEY);
> INSERT INTO t1 VALUES (default);
>
> 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
>

I found that the ALTER TABLE ... ADD COLUMN ... UNIQUE will create a sub-command
to create an index for table, however, it does not create the file on disk,
which leads the above error. The new unique index's oid is 84705 in your
environment. You can try debug it and make a breakpoint at ATExecAddIndex() to
see it.

When calling _SPI_execute_plan() to execute plpgsql code, the SELECT statement
tries to open the index which is create by ALTER command, since it does not
exists, so you get the error like above.

> The error message content returned is what I suspect of being a bug, not so
> much that this SQL didn't work.

+1. The error message makes user confused IMO, maybe we can fix it, but I have
no idea for this. Any suggestion is welcomed.

OTOH, you can use the following code to replace it:

ALTER TABLE t1 ADD COLUMN r TEXT NOT NULL DEFAULT myfunc();
ALTER TABLE t1 ADD UNIQUE (r);

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.