After creating the index, use the explain command to report an error.

[Liebesleid-z]

-- create test table create table test_st2(id int);
insert into test_st2 select generate_series(1,1000000); -- ceate index select monitor.hypopg_create_index('CREATE INDEX idx_test_st2_id ON public.test_st2 USING btree (id)'); (13741,<13741>btree_test_st2_id) -- execute explain explain (FORMAT JSON) select * from test_st2 where id=9280; ERROR: cache lookup failed for index 13740

[rjuju]

Hi,

Unfortunately I can't reproduce it locally using the latest version:

[16318]rjuju@127.0.0.1:14295) rjuju=# create table test_st2(id int);
CREATE TABLE

[16318]rjuju@127.0.0.1:14295) rjuju=# insert into test_st2 select generate_series(1,1000000);
INSERT 0 1000000

[16318]rjuju@127.0.0.1:14295) rjuju=# create schema monitor;
CREATE SCHEMA

[16318]rjuju@127.0.0.1:14295) rjuju=# create extension hypopg with schema monitor ;
CREATE EXTENSION

[16318]rjuju@127.0.0.1:14295) rjuju=# select monitor.hypopg_create_index('CREATE INDEX idx_test_st2_id ON public.test_st2 USING btree (id)');
       hypopg_create_index
----------------------------------
 (13823,<13823>btree_test_st2_id)
(1 row)

[16318]rjuju@127.0.0.1:14295) rjuju=# explain (FORMAT JSON) select * from test_st2 where id=9280;
                     QUERY PLAN
-----------------------------------------------------
 [                                                  +
   {                                                +
     "Plan": {                                      +
       "Node Type": "Bitmap Heap Scan",             +
       "Parallel Aware": false,                     +
       "Async Capable": false,                      +
       "Relation Name": "test_st2",                 +
       "Alias": "test_st2",                         +
       "Startup Cost": 103.78,                      +
       "Total Cost": 4835.31,                       +
       "Plan Rows": 5642,                           +
       "Plan Width": 4,                             +
       "Recheck Cond": "(id = 9280)",               +
       "Plans": [                                   +
         {                                          +
           "Node Type": "Bitmap Index Scan",        +
           "Parent Relationship": "Outer",          +
           "Parallel Aware": false,                 +
           "Async Capable": false,                  +
           "Index Name": "<13823>btree_test_st2_id",+
           "Startup Cost": 0.00,                    +

I'm assuming you're doing something else to get this error, especially since the reported missing index with oid 13740 isn't the one reported by hypopg, but it's hard to know what exactly.

Can you share a way to reproduce this issue from scratch?

[Liebesleid-z]

I was fine yesterday, but when I executed it again today, I suddenly reported an error.

[rjuju]

Not really. As far as I know the only way to get such an error would be to force a plan using an existing hypothetical index to be cached, then removing this hypothetical index and then try to explain that prepared statement again. That clearly can't be the case since you're referencing a newly created table.

Another way would be to create a table and a hypothetical index, then drop the table and then create a new table long enough after the drop so that enough oid are consumed and your new table gets the same oid as the one that were dropped. But it seems quite unlikely as you either need to have close to 4 billions objects in your database or create close to 4 billions new object before or a mix of those.

Both scenario are somewhat out of scope for this project and more of a "don't do that", as trying to properly implement a cache to make sure that hypothetical indexes are automatically dropped when the underlying table is dropped, but only if the (sub) transaction is committed would require a lot of effort for scenarios that shouldn't happen.

[Liebesleid-z]

I found that this problem will definitely occur after I update the image. I updated and did these operations in the new image.

mkdir -p /usr/lib/postgresql/14/lib/bitcode/pg_qualstats
cp /usr/lib/postgresql/14/lib/bitcode/pg_qualstats.bc  /usr/lib/postgresql/14/lib/bitcode/pg_qualstats/pg_qualstats.bc

[rjuju]

I'm not sure how changing something related to pg_qualstats would matter. Did you really only do that or change other stuff, including related to hypopg?

[Liebesleid-z]

postgres=# drop extension hypopg;
DROP EXTENSION
postgres=# create extension hypopg
postgres-# ;
CREATE EXTENSION
postgres=# drop table test_st2;
DROP TABLE
postgres=# create table test_st2(id int);
CREATE TABLE
postgres=# insert into test_st2 select generate_series(1,1000000);
INSERT 0 1000000
postgres=# select hypopg_create_index('CREATE INDEX idx_test_st2_id ON public.test_st2 USING btree (id)');
       hypopg_create_index
----------------------------------
 (13740,<13740>btree_test_st2_id)
(1 row)

postgres=# explain (FORMAT JSON) select * from test_st2 where id=9280;
ERROR:  cache lookup failed for index 13740
postgres=# create table test_st1(id int);
CREATE TABLE
postgres=# insert into test_st1 select generate_series(1,1000000);
INSERT 0 1000000
postgres=# select hypopg_create_index('CREATE INDEX idx_test_st1_id ON public.test_st1 USING btree (id)');
       hypopg_create_index
----------------------------------
 (13741,<13741>btree_test_st1_id)
(1 row)

postgres=# explain (FORMAT JSON) select * from test_st1 where id=9280;
ERROR:  cache lookup failed for index 13741

[Liebesleid-z]

I'm not sure how changing something related to pg_qualstats would matter. Did you really only do that or change other stuff, including related to hypopg?

I really only changed it here. Is it related to the storage of the Docker environment?

[rjuju]

I don't know much about docker storage.

The .bc file are for the jit feature, so it seems a bit surprising that it would cause such a problem. Still I don't know what would happen if you add a new .bc file while postgres is running. Was all of it done using the same connection or did you open a new session each time?

Can you reproduce after restart the container and/or recreating a proper image?

[Liebesleid-z]

I tried to deploy the old version of the image and the new version of the image in two new environments, both of which can be used normally.

And the same upgrade operation for the old version can also be used normally.

But I can't fix the environment where I reported an error. I'm in pain.

[rjuju]

As far as I know any modification done in the container filesystem (as in not a folder that's behind a docker volume) isn't persistent, so a restart of the container should revert it to it's original state?

[Liebesleid-z]

I'm not modifying the image, I'm building a new image on the basis of the old image.

[rjuju]

I'm not sure what to tell you. It seems to be caused by something you did on a container image more than a bug in hypopg, so I don't think I can help you.