Re: Create temp table query hangs

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.