Обсуждение: 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  image
>
> 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.

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
timescaledbversion 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: https://www.postgresql.org/message-id/2121219.1644607692%40sss.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/openingattachments.
 
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  image
>
> 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.

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