Thread: BUG #18642: Creating a materialized view on top of ts_stat succeeds on 16.4, but it fails on 17.0

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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.

This is expected: you'll need to qualify the table name.  See the
very first compatibility entry in the v17 release notes.

            regards, tom lane