public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron <[email protected]>
To: [email protected]
Subject: Re: Create temp table query hangs
Date: Tue, 13 Apr 2021 14:18:52 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On 4/13/21 1:26 PM, Novak Ivan wrote:
>
> Greetings,
>
> We’ve a hanging query which creates a temp table.
>
> Some context:
>
> * The application transforms from one data model to another one (to a
> canonical datamodel). 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).
>
> Our problem in summary:
>
> * a query for creating temporary table on target schema is hanging
> forever and we are out of ideas why.
>
> 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.
>
Something similar happens to us when running a long script full of DDL from
JDBC on Postgresql 9.6.6.
--
Angular momentum makes the world go 'round.
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Create temp table query hangs
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox