![]() It checks if this record is in a list of "to be updated" records. 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. The "create table as select from" has a small to large resultset (can be 0 or millions of rows). data that was marked for deletion since "lastupdate")ģ) create temporary_table with new data from the source table (new means greater than "lastupdate" this is defined in the subselect)Ĥ) insert into target table as select * from temporary_table The process of a delta load is as follows (for each source/target table combination):ġ) get max "lastupdate" timestamp from target tableĢ) delete data on target that has been replaced (i.e. It only hangs when using a container environment (local docker or openshift) On local dev environment (noncontainerized) the query does not hang. In all environments (ours or clients) the Postgres DB are on own VM and not containerized. It does consistently happen when creating temp table selecting on specific tables that have some volume. ![]() Otherwise it doesn’t seem to be volume dependent (small vs large vs huge volume) For source tables that have no new data (or are empty) the query completes. This doesn't happen every time we create a table. not using java -> 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. 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 hangs also after updating the default JDBC drivers to newest version (42.2.19) 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 when creating of temporary tables (see below) and also creating normal tables. But we only sent the statement once to the DB. In the Postgres Database we see three identical active PIDs for the same query (with same starttimestamp). When we try to create a (temporary) table with one statement, and that query takes forever (even after >24h the query is still there). We don't use any third party product here (except JOOQ library for communicating to DB) The query is not generated by our own "mapper" tool that we wrote. * a query for creating temporary table on target schema is hanging forever and we are out of ideas why. The source tables for these transformations are on one schema of the postgresDB (we call it LandingZone) and the target tables of these transformations are in a different schema of the exact same postgresDB (we call it the CDM schema). * The application transforms from one data model to another one (to a canonical datamodel). We’ve a hanging query which creates a temp table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |