From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: petrica.leuca@gmail.com
Subject: BUG #18642: Creating a materialized view on top of ts_stat succeeds on 16.4, but it fails on 17.0
Date: Mon, 30 Sep 2024 09:06:15 +0000
Message-ID: <18642-3fb95066fee227e9@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18642
Logged by: create materialized view breaks on 17.0, but works on 16.4
Email address: petrica.leuca@gmail.com
PostgreSQL version: 17.0
Operating system: Debian
Description:
Hi all,
On PostgreSQL16 one can create a materialized view using ts_stat, but it
breaks on PostgreSQL17 due to no finding the table from the ts_stat query
(while the table does exist), please see below a test example.
PostgreSQL16.4:
# psql -U postgres -d postgres
psql (16.4 (Debian 16.4-1.pgdg120+2))
Type "help" for help.
postgres=# create table test_mat_view(a text);
insert into test_mat_view(a) values ('this is only a test');
select ts_stat('select to_tsvector(a) from test_mat_view');
create materialized view test_mat_view_v as select word, nentry, ndoc from
ts_stat('select to_tsvector(a) from test_mat_view');
select * from test_mat_view_v;
CREATE TABLE
INSERT 0 1
ts_stat
------------
(test,1,1)
(1 row)
SELECT 1
word | nentry | ndoc
------+--------+------
test | 1 | 1
(1 row)
PostgreSQL17.0:
# psql -U postgres -d postgres
psql (17.0 (Debian 17.0-1.pgdg120+1))
Type "help" for help.
postgres=# create table test_mat_view(a text);
insert into test_mat_view(a) values ('this is only a test');
select ts_stat('select to_tsvector(a) from test_mat_view');
CREATE TABLE
INSERT 0 1
ts_stat
------------
(test,1,1)
(1 row)
postgres=# \set VERBOSITY verbose
postgres=# create materialized view test_mat_view_v as select word, nentry,
ndoc from ts_stat('select to_tsvector(a) from test_mat_view');
ERROR: 42P01: relation "test_mat_view" does not exist
LINE 1: select to_tsvector(a) from test_mat_view
^
QUERY: select to_tsvector(a) from test_mat_view
LOCATION: parserOpenTable, parse_relation.c:1449
postgres=#
The above examples are executed with docker, by using the arm image:
docker run -it --name postgres-16-4 -e POSTGRES_PASSWORD=mysecretpassword -d
postgres:16.4
docker run -it --name postgres-17-0 -e POSTGRES_PASSWORD=mysecretpassword -d
postgres:17.0
Kind regards,
Petrica
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, petrica.leuca@gmail.com
Subject: Re: BUG #18642: Creating a materialized view on top of ts_stat succeeds on 16.4, but it fails on 17.0
In-Reply-To: <18642-3fb95066fee227e9@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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