pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix results of index-only scans on btree_gist char(N) indexes. 6+ messages / 1 participants [nested] [flat]
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/54b1cb7eb784a66680ee18f84e2c4e86fad59e82 Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/043c1e1a73aace8b459b3290ff7a50c9b124fb33 Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/823d4c7e25383cb4056c21615130b2abde17efba Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/3a69d45afcc81fceeb44254f38bc3258fc3a160b Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ REL_11_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/38cbdd22d633851413471d501633a038d36dc649 Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix results of index-only scans on btree_gist char(N) indexes. @ 2022-01-08 19:54 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2022-01-08 19:54 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix results of index-only scans on btree_gist char(N) indexes. If contrib/btree_gist is used to make a GIST index on a char(N) (bpchar) column, and that column is retrieved via an index-only scan, what came out had all trailing spaces removed. Since that doesn't happen in any other kind of table scan, this is clearly a bug. The cause is that gbt_bpchar_compress() strips trailing spaces (using rtrim1) before a new index entry is made. That was probably a good idea when this code was first written, but since we invented index-only scans, it's not so good. One answer could be to mark this opclass as incapable of index-only scans. But to do so, we'd need an extension module version bump, followed by manual action by DBAs to install the updated version of btree_gist. And it's not really a desirable place to end up, anyway. Instead, let's fix the code by removing the unwanted space-stripping action and adjusting the opclass's comparison logic to ignore trailing spaces as bpchar normally does. This will not hinder cases that work today, since index searches with this logic will act the same whether trailing spaces are stored or not. It will not by itself fix the problem of getting space-stripped results from index-only scans, of course. Users who care about that can REINDEX affected indexes after installing this update, to immediately replace all improperly-truncated index entries. Otherwise, it can be expected that the index's behavior will change incrementally as old entries are replaced by new ones. Per report from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/696c995b-b37f-5526-f45d-04abe713179f@gmail.com Branch ------ REL_10_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b2198690847fa562d46b5f1dec2baef985416791 Modified Files -------------- contrib/btree_gist/btree_text.c | 104 ++++++++++++++++++++++++--------- contrib/btree_gist/expected/char.out | 6 +- contrib/btree_gist/expected/char_1.out | 6 +- 3 files changed, 82 insertions(+), 34 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-01-08 19:54 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us> 2022-01-08 19:54 pgsql: Fix results of index-only scans on btree_gist char(N) indexes. Tom Lane <tgl@sss.pgh.pa.us>
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