Re: Temporary table already exists

mephysto wrote:
> Hi Albe, this is code of my stored function:
>         CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types

[...]
>       BEGIN
[...]

>               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;

[...]
>       END;

> ConnectionPool reuse connections, of course, but how you can see from my code, the temporary table
> deck_types are already defined with ON COMMIT DROP clause, so I think that my work is not in
> transaction. Am I true? If so, how can I put my code in transaction?

Hmm, unless you explicitly use the SQL statements BEGIN (or START TRANSACTION)
and COMMIT, PostgreSQL would execute each statement in its own connection.

In this case, the statement that contains the function call would be in
its own connection, and you should be fine.

There are two things I can think of:
- The function is called more than once in one SQL statement.
- You use longer transactions without being aware of it (something in
  your stack does it unbeknownst to you).

You could try to set log_statement to "all" and see what SQL actually
gets sent to the database.

You could also include "EXECUTE 'DROP TABLE deck_types';" in your function.