0

I'm running multiple queries (in diffent sessions) which use the following code, some times in paralell, and I was getting errors saying the temp table already existed. I was under the impression the temp tables would just be using some sort of random name in the background processing so these don't interfiere with each other:

select country_id, product_category_id, sum(units) as total_units
into temp totals
from schema.table
group by country_id, product_category_id;

I'm getting this:

Query failed: ERROR: relation "totals" already exists.

Postgres version is 9.4 and this only happens when using pgbouncer port

Am I doing something wrong? Should I use create temp table instead?

Thanks,

Matias
  • 539
  • 5
  • 28
  • 2
    Maybe you have a regular table named `totals`? Btw: it is recommended to use the standard compliant `create table .. as select` rather than the Postgres specific `select .. into ... from ..` –  Sep 25 '20 at 11:16
  • 1
    Unrelated to your problem, but: Postgres 9.4 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  Sep 25 '20 at 11:17
  • I checked and the table totals only exists on pg_temp_NNNN schemas – Matias Sep 25 '20 at 11:27
  • 2
    Are you maybe running that statement more than once in a session? The default is to keep temp tables until the session (=connection) is closed. –  Sep 25 '20 at 12:05
  • I just noticed this happens only when using the pgbouncer port, I'll add this to the querstion – Matias Sep 25 '20 at 13:44

1 Answers1

0

It was the pgbouncer set up, we had the pool_mode as transaction, once we changed it to session it worked OK

Matias
  • 539
  • 5
  • 28