Re: Temporary table already exists

2014-01-31 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:

> 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.
>
I would recommend to use DISCARD ALL before returning the connection to the
pool
anyway. But it's not about current problem. The OP's problem is about "why
ON COMMIT
DROP does not work".

--
// Dmitry.