Latest image `postgis:10-2.5-alpine` causing "variable not found in subplan target list"

[d33kayyy]

Hi,

We are using postgis:10-2.5-alpine image to run our CI/CD pipeline, which started to fail after the latest release. (we always pull the latest postgis:10-2.5-alpine image, and the latest that we have is 214a7af1fedc). I tried with an older version and it works just fine. This error is quite strange to me, as I tried to google it but there doesn't seem to be much resource around it.

Any help is appreciated.

../.venv/lib/python3.6/site-packages/django/db/backends/utils.py:84: in _execute
    return self.cursor.execute(sql, params)
E   psycopg2.errors.InternalError_: variable not found in subplan target list
The above exception was the direct cause of the following exception:
...
../.venv/lib/python3.6/site-packages/django/contrib/admin/options.py:748: in get_changelist_instance
    sortable_by,
../.venv/lib/python3.6/site-packages/django/contrib/admin/views/main.py:100: in __init__
    self.get_results(request)
../.venv/lib/python3.6/site-packages/django/contrib/admin/views/main.py:235: in get_results
    result_count = paginator.count
../.venv/lib/python3.6/site-packages/django/utils/functional.py:48: in __get__
    res = instance.__dict__[self.name] = self.func(instance)
../.venv/lib/python3.6/site-packages/django/core/paginator.py:94: in count
    return c()
../.venv/lib/python3.6/site-packages/django/db/models/query.py:411: in count
    return self.query.get_count(using=self.db)
../.venv/lib/python3.6/site-packages/django/db/models/sql/query.py:515: in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
../.venv/lib/python3.6/site-packages/django/db/models/sql/query.py:500: in get_aggregation
    result = compiler.execute_sql(SINGLE)
../.venv/lib/python3.6/site-packages/django/db/models/sql/compiler.py:1154: in execute_sql
    cursor.execute(sql, params)
../.venv/lib/python3.6/site-packages/django/db/backends/utils.py:66: in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
../.venv/lib/python3.6/site-packages/django/db/backends/utils.py:75: in _execute_with_wrappers
    return executor(sql, params, many, context)
../.venv/lib/python3.6/site-packages/django/db/backends/utils.py:84: in _execute
    return self.cursor.execute(sql, params)
../.venv/lib/python3.6/site-packages/django/db/utils.py:90: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
../.venv/lib/python3.6/site-packages/django/db/backends/utils.py:84: in _execute
    return self.cursor.execute(sql, params)
E   django.db.utils.InternalError: variable not found in subplan target list

pg log

2022-02-14 15:49:16.872 GMT [30] STATEMENT:  DROP VIEW IF EXISTS cce_all
2022-02-14 15:49:17.313 GMT [30] ERROR:  variable not found in subplan target list

[osovv]

We're using latest postgis/postgis:13-3.2-alpine and having the same issue. Seems like some PostgreSQL scheduler problem though

[ImreSamu]

"variable not found in subplan target list"

This is similar to your issue: https://www.postgresql.org/message-id/flat/4c347490-d734-5fdd-d613-1327601b4e7e%40mit.edu "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:"

good news:

• in theory it has been fixed; a few days ago; commit: 2022Feb11: https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5691cc9170bcd6c684715c2755d919c5a16fea2

bad news:

• Need more than 3 months to the next minor release. ( May 12th, 2022. )

So this is a PostgreSQL's problem.

• my recommendation : using the "old images"

The previous images Job list can be accessed here: https://github.com/postgis/docker-postgis/actions/runs/1804724878

• select your job like "Build docker image for 12-3.2 variant alpine" and "Push docker image to dockerhub" step
• search for the digest in the log; like
  • 12-3.2-alpine: digest: sha256:22ce834ccf439390556a06342edcf150b03d413d29a1383675111597548a5572
• and with the digest - it is possible to access the "old image" docker pull postgis/postgis:12-3.2-alpine@sha256:22ce834ccf439390556a06342edcf150b03d413d29a1383675111597548a5572

COMMENT:

• if you have similar problems; please use some voting emoji with my comments; So I can "count" the users with this issue; thanks !

EDIT:

• fixed the suggestion based on @osovv comments; thanks;

[osovv]

You can find digest in "Push docker image to dockerhub" step UPD: It worked! Thank you!

[ImreSamu]

the new postgis\postgis images were created with the latest PG 14.3, 13.7, 12.11, 11.16, and 10.21. ( merged PR https://github.com/postgis/docker-postgis/pull/294 )

Please re-test !