RE: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list - Mailing list pgsql-hackers

From Jean Landercy - BEEODIVERSITY
Subject RE: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list
Date
Msg-id VI1PR09MB438227C977FEF79DC87DE212E7A59@VI1PR09MB4382.eurprd09.prod.outlook.com
Whole thread Raw
In response to Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: Sudden database error with COUNT(*) making Query Planner crashes: variable not found in subplan target list
List pgsql-hackers
Dear David,

Thank you for taking time on this issue.

Here is the detail of the table (I have anonymized it on SO, this is its real name):

\d logistic_site
                                          Table « public.logistic_site »
   Colonne   |           Type           | Collationnement | NULL-able |                Par défaut

-------------+--------------------------+-----------------+-----------+-------------------------------------------
 id          | bigint                   |                 | not null  | nextval('logistic_site_id_seq'::regclass)
 key         | character varying(32)    |                 | not null  |
 name        | character varying(128)   |                 |           |
 created     | timestamp with time zone |                 | not null  |
 updated     | timestamp with time zone |                 | not null  |
 archived    | timestamp with time zone |                 |           |
 geom        | geometry(Polygon,4326)   |                 |           |
 location    | geometry(Point,4326)     |                 |           |
 notes       | text                     |                 |           |
 country_id  | bigint                   |                 |           |
 customer_id | bigint                   |                 |           |

Index :
    "logistic_site_pkey" PRIMARY KEY, btree (id)
    "logistic_site_country_id_9a696481" btree (country_id)
    "logistic_site_customer_id_a2c8a74a" btree (customer_id)
    "logistic_site_geom_105a08da_id" gist (geom)
    "logistic_site_key_2e791173_like" btree (key varchar_pattern_ops)
    "logistic_site_key_key" UNIQUE CONSTRAINT, btree (key)
    "logistic_site_location_54ae0166_id" gist (location)
Contraintes de clés étrangères :
    "logistic_site_country_id_9a696481_fk_logistic_country_id" FOREIGN KEY (country_id) REFERENCES logistic_country(id)
DEFERRABLEINITIALLY DEFERRED
 
    "logistic_site_customer_id_a2c8a74a_fk_logistic_customer_id" FOREIGN KEY (customer_id) REFERENCES
logistic_customer(id)DEFERRABLE INITIALLY DEFERRED
 
Référencé par :
    TABLE "logistic_hive" CONSTRAINT "logistic_hive_site_id_50c29dd8_fk_logistic_site_id" FOREIGN KEY (site_id)
REFERENCESlogistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "logistic_packorder" CONSTRAINT "logistic_packorder_site_id_16e1a41a_fk_logistic_site_id" FOREIGN KEY
(site_id)REFERENCES logistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "logistic_projectsite" CONSTRAINT "logistic_projectsite_site_id_522bf74b_fk_logistic_site_id" FOREIGN KEY
(site_id)REFERENCES logistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "scientific_identification" CONSTRAINT "scientific_identification_site_id_d9e79149_fk_logistic_site_id"
FOREIGNKEY (site_id) REFERENCES logistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "scientific_inventory" CONSTRAINT "scientific_inventory_site_id_72521353_fk_logistic_site_id" FOREIGN KEY
(site_id)REFERENCES logistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "scientific_result" CONSTRAINT "scientific_result_site_id_af6c815d_fk_logistic_site_id" FOREIGN KEY (site_id)
REFERENCESlogistic_site(id) DEFERRABLE INITIALLY DEFERRED
 
    TABLE "scientific_selection" CONSTRAINT "scientific_selection_site_id_88d69cab_fk_logistic_site_id" FOREIGN KEY
(site_id)REFERENCES logistic_site(id) DEFERRABLE INITIALLY DEFERRED
 

And the output of the related query:

SELECT
    attname, atttypid::regtype, attnum,atthasdef, atthasmissing, attgenerated, attisdropped
FROM
    pg_attribute 
WHERE
    attrelid = 'logistic_site'::regclass
ORDER BY
    attnum;

   attname   |         atttypid         | attnum | atthasdef | atthasmissing | attgenerated | attisdropped
-------------+--------------------------+--------+-----------+---------------+--------------+--------------
 tableoid    | oid                      |     -6 | f         | f             |              | f
 cmax        | cid                      |     -5 | f         | f             |              | f
 xmax        | xid                      |     -4 | f         | f             |              | f
 cmin        | cid                      |     -3 | f         | f             |              | f
 xmin        | xid                      |     -2 | f         | f             |              | f
 ctid        | tid                      |     -1 | f         | f             |              | f
 id          | bigint                   |      1 | t         | f             |              | f
 key         | character varying        |      2 | f         | f             |              | f
 name        | character varying        |      3 | f         | f             |              | f
 created     | timestamp with time zone |      4 | f         | f             |              | f
 updated     | timestamp with time zone |      5 | f         | f             |              | f
 archived    | timestamp with time zone |      6 | f         | f             |              | f
 geom        | geometry                 |      7 | f         | f             |              | f
 location    | geometry                 |      8 | f         | f             |              | f
 notes       | text                     |      9 | f         | f             |              | f
 country_id  | bigint                   |     10 | f         | f             |              | f
 customer_id | bigint                   |     11 | f         | f             |              | f
(17 lignes)

Additional information:
When trying to read the SQL related query for this table in PgAdmin4 I also have the error message popping up and the I
getno SQL. So maybe the problem resides in a deeper function the Query Planner and SQL generator functions rely on. 
 

Don't hesitate to ask for more information.

Best regards,

Jean

pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: pg_rewind: warn when checkpoint hasn't happened after promotion
Next
From: Kyotaro Horiguchi
Date:
Subject: Re: Inconvenience of pg_read_binary_file()