Thread: PGroonga index-only scan problem with yesterday’s PostgreSQL updates
With yesterday’s release of PostgreSQL 11.15, 12.10, and 13.6 (presumably 10.20 and 14.2 as well), Zulip’s test suite started failing with “variable not found in subplan target list” errors from PostgreSQL on a table that has a PGroonga index. I found the following reproduction recipe from a fresh database: psql (11.15 (Debian 11.15-1.pgdg100+1)) Type "help" for help. test=# CREATE EXTENSION pgroonga; CREATE EXTENSION test=# CREATE TABLE t AS SELECT CAST(c AS text) FROM generate_series(1, 10000) AS c; SELECT 10000 test=# CREATE INDEX t_c ON t USING pgroonga (c); CREATE INDEX test=# VACUUM t; VACUUM test=# SELECT COUNT(*) FROM t; ERROR: variable not found in subplan target list I filed https://github.com/pgroonga/pgroonga/issues/203, and confirmed with a Git bisection of PostgreSQL that this error first appears with ec36745217 (REL_11_15~42) “Fix index-only scan plans, take 2.” I’m aware that this likely just exposed a previously hidden PGroonga bug, but I figure PostgreSQL developers might want to know about this anyway and help come up with the right fix. The PGroonga author suggested I start a thread here: https://github.com/pgroonga/pgroonga/issues/203#issuecomment-1036708841 > Thanks for investigating this! > > Our CI is also broken with new PostgreSQL: > https://github.com/pgroonga/pgroonga/runs/5149762901?check_suite_focus=true > > https://www.postgresql.org/message-id/602391641208390%40iva4-92c901fae84c.qloud-c.yandex.net > says partial-retrieval index-only scan but our case is > non-retrievable index-only scan. In non-retrievable index-only scan, > the error is occurred. > > We asked about non-retrievable index-only scan on the PostgreSQL > mailing list in the past: > https://www.postgresql.org/message-id/5148.1584372043%40sss.pgh.pa.us > We thought non-retrievable index-only scan should not be used but > PostgreSQL may use it as a valid plan. So I think that our case > should be supported with postgres/postgres@ec36745 “Fix index-only > scan plans, take 2.” > > Could you ask about this case on the PostgreSQL mailing list > https://www.postgresql.org/list/pgsql-hackers/ ? > > The following patch fixes our case and PostgreSQL's test cases are > still passed but a review by the original author is needed: > > --- postgresql-11.15.orig/src/backend/optimizer/plan/setrefs.c 2022-02-08 06:20:23.000000000 +0900 > +++ postgresql-11.15/src/backend/optimizer/plan/setrefs.c 2022-02-12 07:32:20.355567317 +0900 > @@ -1034,7 +1034,7 @@ > { > TargetEntry *indextle = (TargetEntry *) lfirst(lc); > > - if (!indextle->resjunk) > + if (!indextle->resjunk || indextle->expr->type == T_Var) > stripped_indextlist = lappend(stripped_indextlist, indextle); > } > > ``` Anders
Anders Kaseorg <andersk@mit.edu> writes: > With yesterday’s release of PostgreSQL 11.15, 12.10, and 13.6 > (presumably 10.20 and 14.2 as well), Zulip’s test suite started failing > with “variable not found in subplan target list” errors from PostgreSQL > on a table that has a PGroonga index. Possibly same issue I just fixed? https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e5691cc9170bcd6c684715c2755d919c5a16fea2 regards, tom lane
On 2/11/22 15:57, Tom Lane wrote: > Possibly same issue I just fixed? > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e5691cc9170bcd6c684715c2755d919c5a16fea2 Yeah, that does seem to fix my test cases. Thanks! Any chance this will make it into minor releases sooner than three months from now? I know extra minor releases are unusual, but this regression will be critical at least for self-hosted Zulip users and presumably other PGroonga users. Anders
Anders Kaseorg <andersk@mit.edu> writes: > On 2/11/22 15:57, Tom Lane wrote: >> Possibly same issue I just fixed? >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e5691cc9170bcd6c684715c2755d919c5a16fea2 > Yeah, that does seem to fix my test cases. Thanks! > Any chance this will make it into minor releases sooner than three > months from now? I know extra minor releases are unusual, but this > regression will be critical at least for self-hosted Zulip users and > presumably other PGroonga users. I don't know that we'd go that far ... maybe if another bad problem turns up. In the meantime, though, I do have a modest suggestion: it would not be too hard to put some defenses in place against another such bug. The faulty commit was in our tree for a month and nobody reported a problem, which is annoying. Do you want to think about doing your testing against git branch tips, rather than the last released versions? Making a new build every few days would probably be plenty fast enough. An even slicker answer would be to set up a PG buildfarm machine that, in addition to the basic tests, builds PGroonga against the new PG sources and runs your tests. Andrew's machine "crake" does that for RedisFDW and BlackholeFDW, and the source code for at least the latter module is in the buildfarm client distribution. regards, tom lane
On 2/12/22 09:25, Tom Lane wrote: > I don't know that we'd go that far ... maybe if another bad problem > turns up. In the meantime, though, I do have a modest suggestion: > it would not be too hard to put some defenses in place against another > such bug. The faulty commit was in our tree for a month and nobody > reported a problem, which is annoying. Do you want to think about doing > your testing against git branch tips, rather than the last released > versions? Making a new build every few days would probably be plenty > fast enough. > > An even slicker answer would be to set up a PG buildfarm machine > that, in addition to the basic tests, builds PGroonga against the > new PG sources and runs your tests. Andrew's machine "crake" does > that for RedisFDW and BlackholeFDW, and the source code for at least > the latter module is in the buildfarm client distribution. I’m a Zulip developer, not a PGroonga developer, but this sounds like a good idea to me, so I opened a PR to add the PostgreSQL 15 snapshot build to PGroonga’s test matrix. https://github.com/pgroonga/pgroonga/pull/204 The current snapshot build in the PGDG APT repository is 15~~devel~20220208.0530-1~541.gitba15f16, predating this fix, despite the documentation that they should be built every 6 hours (https://wiki.postgresql.org/wiki/Apt/FAQ#Development_snapshots). But it seems the tests have other failures against this snapshot build that will need to be investigated. Anders
On 2/12/22 12:25, Tom Lane wrote: > > An even slicker answer would be to set up a PG buildfarm machine > that, in addition to the basic tests, builds PGroonga against the > new PG sources and runs your tests. Andrew's machine "crake" does > that for RedisFDW and BlackholeFDW, and the source code for at least > the latter module is in the buildfarm client distribution. It occurred to me that this wasn't very well documented, so I created some docco: <https://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto#Testing_Additional_Software> cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Am Montag, dem 14.02.2022 um 16:02 -0500 schrieb Andrew Dunstan: > It occurred to me that this wasn't very well documented, so I created > some docco: > < > https://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto#Testing_Ad > ditional_Software> Thx Andrew, i wasn't aware of this, too! I'm going to use that for the Informix FDW, since it has got rotten over the time a little and i'd like to get some better code testing for it. Bernd