BUG #18410: SQL Error [XX000]: ERROR: variable not found in subplan target list
The following bug has been logged on the website:
Bug reference: 18410
Logged by: Fernando Asulay
Email address: fernando.asulay@es.abb.com
PostgreSQL version: 16.2
Operating system: TimescaleDB linux Docker image
Description:
The issue was found to happend on timescaledb extension hypertables using
postgres 14 and above. We tested different version of timescaledb and
doesn't seem to take any effect. Only changing pg version from 13 to 14.
Tested using images from
https://hub.docker.com/r/timescale/timescaledb/tags
To reproduce the error:
The following compose was used:
version: "3.5"
services:
works:
image: timescale/timescaledb:2.14.2-pg13
container_name: works
restart: always
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: password
ports:
- "5432:5432"
doesntwork:
image: timescale/timescaledb:2.14.2-pg14
container_name: doesntwork
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: password
ports:
- "5433:5432"
doesntworkeither:
image: timescale/timescaledb:2.14.2-pg16
container_name: doesntworkeither
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: password
ports:
- "5434:5432"
connect to "doesntwork" or "doesntworkeither" containers and run the
following SQL:
-- Create test table
CREATE TABLE public.test1(
id text NOT null,
created timestamptz NOT NULL
);
-- Make the table an timescaledb hypertable
SELECT create_hypertable('public.test1', 'created', chunk_time_interval =>
interval'4 days');
-- Insert some data
INSERT INTO public.test1 VALUES (1, '2024-01-01');
-- Verify data is correclty inserted
SELECT * FROM public.test1;
-- Run the query to trigger the error
DELETE FROM
"public".test1
WHERE
(EXISTS (SELECT 1));
On Wed, 2024-03-27 at 12:42 +0000, PG Bug reporting form wrote:
Bug reference: 18410
PostgreSQL version: 16.2
Operating system: TimescaleDB linux Docker imageThe issue was found to happend on timescaledb extension hypertables using
postgres 14 and above. We tested different version of timescaledb and
doesn't seem to take any effect. Only changing pg version from 13 to 14.
What makes you think that that is a PostgreSQL bug, not a TimescaleDB bug?
Yours,
Laurenz Albe
Well, like I mention we tested old and newer versions of timescaledb both in newer versions of postgres and varying the timescaledb version doesn't provoke the issue.
It only appears when you use timescaledb on postgres version 14 and above.
I really am not sure is a postgres bug, but this error has been reported before as a postgres bug in the past, think here : /messages/by-id/2121219.1644607692@sss.pgh.pa.us
But maybe I'm miss understanding. If you are sure is not a postgres bug I can report it to the timescaleDB team.
—
Fernando Asulay
Principal Software Engineer
-----Mensaje original-----
De: Laurenz Albe <laurenz.albe@cybertec.at>
Enviado el: miércoles, 27 de marzo de 2024 14:02
Para: Fernando Asulay <fernando.asulay@es.abb.com>; pgsql-bugs@lists.postgresql.org
Asunto: Re: BUG #18410: SQL Error [XX000]: ERROR: variable not found in subplan target list
BeSecure! This email comes from outside of ABB. Make sure you verify the sender before clicking any links or downloading/opening attachments.
If this email looks suspicious, report it by clicking 'Report Phishing' button in Outlook or raising a ticket on MyIS.
On Wed, 2024-03-27 at 12:42 +0000, PG Bug reporting form wrote:
Bug reference: 18410
PostgreSQL version: 16.2
Operating system: TimescaleDB linux Docker imageThe issue was found to happend on timescaledb extension hypertables
using postgres 14 and above. We tested different version of
timescaledb and doesn't seem to take any effect. Only changing pg version from 13 to 14.
What makes you think that that is a PostgreSQL bug, not a TimescaleDB bug?
Yours,
Laurenz Albe
Fernando Asulay <fernando.asulay@es.abb.com> writes:
I really am not sure is a postgres bug, but this error has been reported before as a postgres bug in the past,
It's a symptom of something going wrong during planning. But unless
you can reproduce it on stock Postgres, I don't think there is any
reason to believe that it's a Postgres bug. In any case, the
community has no means to investigate it. You need to file it with
the Timescale people; if they conclude there's an underlying Postgres
problem, they can come to us with their conclusions.
regards, tom lane