Re: Temporary table already exists - Mailing list pgsql-general

On 05 Feb 2014, at 21:19, Mephysto <mephystoonhell@gmail.com> wrote:

> ​I posted my last message via Nabble, so I think that the log is not shown in email.
>
>
> I try to repost my log via email:
>
> DEBUG:  building index "pg_toast_148085_index" on table "pg_toast_148085"
> CONTEXT:  SQL statement "CREATE LOCAL TEMPORARY TABLE deck_types
>             ON COMMIT DROP
>             AS
>             SELECT stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
>             FROM ccg_schema.deck_composition T0
>             ,ccg_schema.cards_per_user T1
>             WHERE id_deck = p_id_deck
>               AND t1.id_owner = l_id_user
>               AND t0.id_card = t1.id_card"
>         PL/pgSQL function stored_functions_v0.get_deck_types(bigint) line 12 at SQL statement
> STATEMENT:  SELECT * FROM stored_functions_v0.get_deck_master_properties($1)
...
> LOG:  execute <unnamed>: INSERT INTO admin.logs VALUES ('', '2014-02-05 16:15:13.258',
'it.redevogames.redevolib.classes.PgStoredExecutor','DEBUG', $$Executing SELECT * FROM
stored_functions_v0.get_deck_master_properties(?)$$,'PgStoredExecutor.java:215', $$Executing SELECT * FROM
stored_functions_v0.get_deck_master_properties(?)
>         $$)
> LOG:  execute <unnamed>: SELECT * FROM stored_functions_v0.get_deck_master_properties($1)
> DETAIL:  parameters: $1 = '1'
> ERROR:  relation "deck_types" already exists
> CONTEXT:  SQL statement "CREATE LOCAL TEMPORARY TABLE deck_types
>             ON COMMIT DROP
>             AS
>             SELECT stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
>             FROM ccg_schema.deck_composition T0
>             ,ccg_schema.cards_per_user T1
>             WHERE id_deck = p_id_deck
>               AND t1.id_owner = l_id_user
>               AND t0.id_card = t1.id_card"
>         PL/pgSQL function stored_functions_v0.get_deck_types(bigint) line 12 at SQL statement
> STATEMENT:  SELECT * FROM stored_functions_v0.get_deck_master_properties($1)

You aren’t calling that function recursively? Or from multiple parallel threads using the same connection object?

If not, it looks like you’re running some kind of auditing system as well; perhaps that’s accidentally re-executing the
function?

You’re almost certainly executing the multiple times in the same session, it’s mostly a matter of figuring out how that
happens.

There is also a remote possibility that the temp table hasn’t finished clearing out before another session attempts to
createthe same table; I seem to recall reading on this list that such was possible in old versions of Postgres. I
stronglydoubt that though. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.