pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
6+ messages / 4 participants
[nested] [flat]
* BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
@ 2022-01-21 09:22 PG Bug reporting form <noreply@postgresql.org>
2022-01-21 17:27 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
2022-01-22 06:33 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2022-01-21 09:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: adrien.gilmore+pg@gmail.com
The following bug has been logged on the website:
Bug reference: 17376
Logged by: akg
Email address: adrien.gilmore+pg@gmail.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
The error message content returned is what I suspect of being a bug, not so
much that this SQL didn't work.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
@ 2022-01-21 17:27 ` Japin Li <japinli@hotmail.com>
2022-01-21 17:36 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Japin Li @ 2022-01-21 17:27 UTC (permalink / raw)
To: adrien.gilmore+pg@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, 21 Jan 2022 at 17:22, PG Bug reporting form <noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17376
> Logged by: akg
> Email address: adrien.gilmore+pg@gmail.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
2022-01-21 17:27 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
@ 2022-01-21 17:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-22 00:18 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-01-21 17:36 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: adrien.gilmore+pg@gmail.com; pgsql-bugs@lists.postgresql.org
Japin Li <japinli@hotmail.com> writes:
> On Fri, 21 Jan 2022 at 17:22, PG Bug reporting form <noreply@postgresql.org> wrote:
>> 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.
Yeah. Ideally we'd throw an error along the lines of "can't access a
table that's in process of being altered". The SELECT inside the function
is not part of the ALTER TABLE machinery and ought to be locked out.
However, I fear we don't have adequate infrastructure to tell which
table accesses *are* part of the ALTER TABLE machinery and which aren't.
Maybe it'd be sufficient to check for an active ALTER TABLE in the
parser, but I'm not sure.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
2022-01-21 17:27 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
2022-01-21 17:36 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-01-22 00:18 ` Japin Li <japinli@hotmail.com>
2022-01-22 04:15 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Japin Li @ 2022-01-22 00:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: adrien.gilmore+pg@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 22 Jan 2022 at 01:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> On Fri, 21 Jan 2022 at 17:22, PG Bug reporting form <noreply@postgresql.org> wrote:
>>> 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.
>
> Yeah. Ideally we'd throw an error along the lines of "can't access a
> table that's in process of being altered". The SELECT inside the function
> is not part of the ALTER TABLE machinery and ought to be locked out.
> However, I fear we don't have adequate infrastructure to tell which
> table accesses *are* part of the ALTER TABLE machinery and which aren't.
Right.
> Maybe it'd be sufficient to check for an active ALTER TABLE in the
> parser, but I'm not sure.
>
Do you mean check the table is accessed by ALTER TABLE when calling SPI to
execute the function? How can we get the table that is be accessed by
ALTER TABLE in parser?
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
2022-01-21 17:27 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
2022-01-21 17:36 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Tom Lane <tgl@sss.pgh.pa.us>
2022-01-22 00:18 ` Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error Japin Li <japinli@hotmail.com>
@ 2022-01-22 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2022-01-22 04:15 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: adrien.gilmore+pg@gmail.com; pgsql-bugs@lists.postgresql.org
Japin Li <japinli@hotmail.com> writes:
> On Sat, 22 Jan 2022 at 01:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> However, I fear we don't have adequate infrastructure to tell which
>> table accesses *are* part of the ALTER TABLE machinery and which aren't.
> Right.
>> Maybe it'd be sufficient to check for an active ALTER TABLE in the
>> parser, but I'm not sure.
> Do you mean check the table is accessed by ALTER TABLE when calling SPI to
> execute the function? How can we get the table that is be accessed by
> ALTER TABLE in parser?
The reason there's not a problem like this with respect to cross-session
accesses is that the exclusive lock acquired by ALTER TABLE prevents
other sessions from touching the table. Conversely, the reason we do
have a problem here is that such locks don't prevent accesses within
our own session. So a rough sketch for fixing this might be "at
the point where we acquire a lock on some table for a new statement,
also check to see if some internal-to-our-session operation has
declared a need for exclusive access". The problem is that we don't
have a well-defined line as to what's part of the ALTER and what
is not. For example, I recall that ALTER TABLE handles index rebuilding
by creating textual ALTER ADD INDEX commands, and parsing and executing
those. How can we distinguish that behavior from a SQL function within
an index or CHECK constraint trying to execute ALTER ADD INDEX?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
@ 2022-01-22 06:33 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2022-01-22 06:33 UTC (permalink / raw)
To: adrien.gilmore+pg@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jan 21, 2022 at 4:20 AM PG Bug reporting form <
noreply@postgresql.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-01-22 06:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-21 09:22 BUG #17376: Adding unique column with a function() default results in "could not read block 0 in file" error PG Bug reporting form <noreply@postgresql.org>
2022-01-21 17:27 ` Japin Li <japinli@hotmail.com>
2022-01-21 17:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-22 00:18 ` Japin Li <japinli@hotmail.com>
2022-01-22 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-22 06:33 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox