Dmitriy Igrishin wrote > 2014-01-31 Albe Laurenz < > laurenz.albe@.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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
