Re: Temporary table already exists

Dmitriy Igrishin wrote
> 2014-01-31 Albe Laurenz <

> laurenz(dot)albe(at)(dot)gv

> >:
>
>> 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.

Is it possible that it is read-uncommitted transaction isolation level?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5789896.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.