|
Greetings, We’ve a hanging query which creates a temp table. Some context:
Our problem in summary:
The query is not generated by our own "mapper" tool that we wrote. We don't use any third party product here (except JOOQ library for communicating to DB) When we try to create a (temporary) table with one statement, and that query takes forever (even after >24h the query is still there). In the Postgres Database we
see three identical active PIDs for the same query (with same starttimestamp). But we only sent the statement
once to the DB. See screenshot below. The query hangs when creating of temporary tables (see below) and also creating normal tables. The query hangs using our go-to library for Database queries (JOOQ) and also when we do it manually (we rewrote the implementation using java JDBC) -> so it is not
dependent on that library The query hangs also after updating the default JDBC drivers to newest version (42.2.19) The query does NOT hang when running on local development environment (not containerized) -> we see 3 PIDs in database (which is strange), but the query finishes
after some seconds The query does NOT hang when running directly on the database using DBeaver, i.e. not using java -> we see 3 PIDs in database (which is strange), but the query finishes
after some seconds This doesn't happen every time we create a table. For source tables that have no new data (or are empty) the query completes. Otherwise it doesn’t seem to be volume
dependent (small vs large vs huge volume) It does consistently happen when creating temp table selecting on specific tables that have some volume. In all environments (ours or clients) the Postgres DB are on own VM and not containerized. On local dev environment (noncontainerized) the query does not hang. It only hangs when using a container environment (local docker or openshift) The process of a delta load is as follows (for each source/target table combination): 1) get max "lastupdate" timestamp from target table 2) delete data on target that has been replaced (i.e. data that was marked for deletion since "lastupdate") 3) create temporary_table with new data from the source table (new means greater than "lastupdate"; this is defined in the subselect) 4) insert into target table as select * from temporary_table The query on step3 is the one that hangs Content of the query: The "create table as select from" has a small to large resultset (can be 0 or millions of rows). The select statement has 2 joins with the same table (a code table
to resolve some code values for later WHERE clauses) and an additional condition in the WHERE clause with a subselect inside (the subselect selects from the same table as the parent select. It checks if this record is in a list of "to be updated" records.
i.e. for a "delta" load). Postgres version: PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit Postgres JDBC driver version: 42.2.19 Java version: 11.0.9.1
Attachments:
Any help is appreciated, Ivan
|
pid | client_addr | state | wait_event_type | backend_start | xact_start | state_change | query
--------+-------------+--------+-----------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30 | | | Activity | 2021-04-01 13:14:17.262872+02 | | |
32 | | | Activity | 2021-04-01 13:14:17.263749+02 | | |
373883 | | | | 2021-04-07 14:04:37.551133+02 | 2021-04-07 08:49:40.152171+02 | |
373884 | | | | 2021-04-07 14:04:37.551576+02 | 2021-04-07 08:49:40.152171+02 | |
28 | | | Activity | 2021-04-01 13:14:17.262323+02 | | |
27 | | | Activity | 2021-04-01 13:14:17.262752+02 | | |
29 | | | Activity | 2021-04-01 13:14:17.262753+02 | | |
224406 | 10.37.1.216 | idle | Client | 2021-04-06 16:31:56.921563+02 | | 2021-04-07 14:04:37.37835+02 |
373309 | 10.37.2.66 | idle | Client | 2021-04-07 14:03:23.608589+02 | | 2021-04-07 14:03:23.613093+02 | SET application_name = 'PostgreSQL JDBC Driver'
40 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.015224+02 | | 2021-04-07 14:02:37.628649+02 | COMMIT
372004 | 10.37.2.66 | idle | Client | 2021-04-07 14:00:35.253338+02 | | 2021-04-07 14:00:35.257102+02 | SET application_name = 'PostgreSQL JDBC Driver'
371383 | 10.37.2.66 | idle | Client | 2021-04-07 13:59:15.547588+02 | | 2021-04-07 13:59:15.551386+02 | SET application_name = 'PostgreSQL JDBC Driver'
367152 | 10.37.2.66 | idle | Client | 2021-04-07 13:50:11.790021+02 | | 2021-04-07 13:50:11.794277+02 | SET application_name = 'PostgreSQL JDBC Driver'
365608 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:53.135327+02 | | 2021-04-07 13:46:53.140432+02 | SET application_name = 'PostgreSQL JDBC Driver'
365280 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:11.134051+02 | | 2021-04-07 13:46:11.140538+02 | SET application_name = 'PostgreSQL JDBC Driver'
365259 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:08.697428+02 | | 2021-04-07 13:46:08.701173+02 | SET application_name = 'PostgreSQL JDBC Driver'
364928 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:26.021603+02 | | 2021-04-07 13:45:26.026252+02 | SET application_name = 'PostgreSQL JDBC Driver'
364841 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:15.036377+02 | | 2021-04-07 13:45:15.045453+02 | SET application_name = 'PostgreSQL JDBC Driver'
225902 | | active | IPC | 2021-04-07 08:49:40.197802+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.238357+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225901 | | active | IPC | 2021-04-07 08:49:40.197379+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.243261+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225598 | 10.37.2.66 | active | IO | 2021-04-07 08:20:29.59069+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.15374+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
41 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.036269+02 | | 2021-04-06 17:14:49.372727+02 | select ta.attname, ia.attnum, ic.relname, n.nspname, NULL from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_namespace n where ic.relname = 'forecastkrit_pkey' AND n.nspname = 'dataapi_lz' AND ic.oid = i.indexrelid AND n.oid = ic.relnamespace AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) order by ia.attnum
37 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.638695+02 | | 2021-04-01 13:15:16.86598+02 | DEALLOCATE "_PLAN0x7f46fc35eed0"
38 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.691856+02 | | 2021-04-01 13:15:16.69662+02 | show max_identifier_length
(24 rows)
Attachment:
deltatranformation_hanging_query.sql
Description: deltatranformation_hanging_query.sql
