Thread: Temporary table already exists
Hi there, in my database I'm using several stored_functions that take advantage of temporary table. The application that is connected to Postgres is a Java Web Application in a Glassfish Application Server: it is connected by a JDBC Connection Pool provided by Glassfish with this settings: Resource type -> javax.sql.ConnectionPoolDataSouce Dataset Classname -> org.postgresql.ds.PGConnectionPoolDataSource Transaction Isolation -> read-uncommitted The problem is that in a concurrent execution of a function, I received error of relation already exists. The relation that caused issue is exactly my temporary table. My question is: what is the reason for which I take this type of error? Is there a way to follow to avoid this situation? Thanks in advance. Mephysto -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
mephysto wrote: > in my database I'm using several stored_functions that take advantage of > temporary table. The application that is connected to Postgres is a Java Web > Application in a Glassfish Application Server: it is connected by a JDBC > Connection Pool provided by Glassfish with this settings: > > Resource type -> javax.sql.ConnectionPoolDataSouce > Dataset Classname -> org.postgresql.ds.PGConnectionPoolDataSource > Transaction Isolation -> read-uncommitted > > > The problem is that in a concurrent execution of a function, I received > error of relation already exists. The relation that caused issue is exactly > my temporary table. > > My question is: what is the reason for which I take this type of error? Is > there a way to follow to avoid this situation? You probably have a connection pool that reuses a connection in which you already created the temporary table. I see two options: - Explicitly drop the temporary table when you are done. - Create the table with ON COMMIT DROP and put your work into a transaction. Yours, Laurenz Albe
Hi Albe, this is code of my stored function:
View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types( p_id_deck BIGINT)RETURNS BIGINT[] AS$$DECLARE l_id_user BIGINT; l_cards_number INTEGER; l_deck_type BIGINT; l_result BIGINT[];BEGIN SELECT INTO STRICT l_id_user id_user FROM ccg_schema.decks_per_user WHERE id = p_id_deck; 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; SELECT INTO l_cards_number COUNT(*) FROM deck_types; FOR l_deck_type IN SELECT DISTINCT unnest(deck_type_ids) FROM deck_types LOOP IF (l_cards_number = (SELECT COUNT(*) FROM (SELECT unnest(deck_type_ids) AS id FROM deck_types) T0 WHERE id = l_deck_type)) THEN l_result := array_append(l_result, l_deck_type); END IF; END LOOP; RETURN l_result;END;$$LANGUAGE PLPGSQL VOLATILE;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? Many thanks. Mephysto
View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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. Yours, Laurenz Albe
I had a similar problem once. The pool is reusing connections and the temporary tables are still there. Now I always create new temporary tables with a unique name like this: tmpTableId = "TMP" + Math.abs(generateUUID().hashCode()); if (tmpTableId.length() > 15) tmpTableId = tmpTableId.substring(tmpTableId.length() - 15, tmpTableId.length()); conn.setAutoCommit(true); tableStmt = conn.createStatement(); try { // create global temporary tables tableStmt.executeUpdate("create temporary table TABLE_ANME_" + tmpTableId + "( ... ) oncommit preserve rows"); etc. Then you have to add the tmpTableId to every statement in your code but it should work fine. Gesendet: Freitag, 31. Januar 2014 um 12:04 Uhr Von: mephysto <mephystoonhell@gmail.com> An: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Temporary table already exists Hi Albe, this is code of my stored function: CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types ( p_id_deck BIGINT ) RETURNS BIGINT[] AS $$ DECLARE l_id_user BIGINT; l_cards_number INTEGER; l_deck_type BIGINT; l_result BIGINT[]; BEGIN SELECT INTO STRICT l_id_user id_user FROM ccg_schema.decks_per_user WHERE id = p_id_deck; 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; SELECT INTO l_cards_number COUNT(*) FROM deck_types; FOR l_deck_type IN SELECT DISTINCT unnest(deck_type_ids) FROM deck_types LOOP IF (l_cards_number = (SELECT COUNT(*) FROM (SELECT unnest(deck_type_ids) AS id FROM deck_types) T0 WHERE id =l_deck_type)) THEN l_result := array_append(l_result, l_deck_type); END IF; END LOOP; RETURN l_result; END; $$ LANGUAGE PLPGSQL VOLATILE; ConnectionPool reuse connections, of course, but how you can see from my code, the temporary table deck_types are alreadydefined with ON COMMIT DROP clause, so I think that my work is not in transaction. Am I true? If so, how can I putmy code in transaction? Many thanks. Mephysto View this message in context: Re: Temporary table already exists Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thank you Felix, but I would to create temporary table from stored procedure, non from application code. Thanks again. Meph -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5789877.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
2014-01-31 Albe Laurenz <laurenz.albe@wien.gv.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;Hmm, unless you explicitly use the SQL statements BEGIN (or START TRANSACTION)
> 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?
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.
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.
On 01/31/2014 06:49 AM, mephysto wrote: > Dmitriy Igrishin wrote >> 2014-01-31 Albe Laurenz < > >> laurenz.albe@.gv > >>> >>> 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? No http://www.postgresql.org/docs/9.3/interactive/transaction-iso.html#XACT-READ-COMMITTED In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only three distinct isolation levels, which correspond to the levels Read Committed, Repeatable Read, and Serializable. When you select the level Read Uncommitted you really get Read Committed... The issue would seem to be here from you initial post: "The problem is that in a concurrent execution of a function, I received error of relation already exists." Per a previous post you will need to crank up the logging and see exactly how your statements are being sent to the back end. > > > -- Adrian Klaver adrian.klaver@gmail.com
mephysto wrote: > Is it possible that it is read-uncommitted transaction isolation level? No; there is no such thing in PostgreSQL. The lowest isolation level is READ COMMITTED. Yours, Laurenz Albe
Hello newly, this is my error log: Thanks in advance. Meph -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790682.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/05/2014 07:19 AM, mephysto wrote: > Hello newly, > this is my error log: > > > > Thanks in advance. Seems problem is solved:) > > Meph > -- Adrian Klaver adrian.klaver@gmail.com
Ehm no, at a few line befor end you can read this: ERROR: relation "deck_types" already exists So, the error persists. :( -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790688.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/05/2014 07:36 AM, mephysto wrote: > Ehm no, > at a few line befor end you can read this: > > ERROR: relation "deck_types" already exists I should have been clearer. There is no error log posted in your previous message. > > > So, the error persists. > > :( > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790688.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
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>: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1 DETAIL: parameters: $1 = '1016' LOG: execute <unnamed>: SET application_name = '' LOG: execute <unnamed>: SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid DETAIL: parameters: $1 = '1016' LOG: execute <unnamed>: INSERT INTO admin.logs VALUES ('', '2014-02-05 16:15:13.249', 'it.redevogames.redevolib.classes.PgStoredExecutor', 'DEBUG', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?)$$, 'PgStoredExecutor.java:215', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?) $$) LOG: execute <unnamed>: SELECT * FROM stored_functions_v0.get_deck_from_id_user($1) DETAIL: parameters: $1 = '51' LOG: execute <unnamed>: SET application_name = '' 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) ERROR: current transaction is aborted, commands ignored until end of transaction block
Thanks in advance.
Mephysto
Il 05/feb/2014 17:21 "Adrian Klaver" <adrian.klaver@gmail.com> ha scritto:
On 02/05/2014 07:36 AM, mephysto wrote:
> Ehm no,
> at a few line befor end you can read this:
>
> ERROR: relation "deck_types" already exists
I should have been clearer. There is no error log posted in your
previous message.
>
>
> So, the error persists.
>
> :(
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790688.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
[hidden email]
--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalIf you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790697.html
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.
On 02/05/2014 12:19 PM, Mephysto 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>: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1 > DETAIL: parameters: $1 = '1016' > LOG: execute <unnamed>: SET application_name = '' > LOG: execute <unnamed>: SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem= e.oid > DETAIL: parameters: $1 = '1016' > LOG: execute <unnamed>: INSERT INTO admin.logs VALUES ('', '2014-02-05 16:15:13.249', 'it.redevogames.redevolib.classes.PgStoredExecutor','DEBUG', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?)$$,'PgStoredExecutor.java:215', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?) > $$) > LOG: execute <unnamed>: SELECT * FROM stored_functions_v0.get_deck_from_id_user($1) > DETAIL: parameters: $1 = '51' > LOG: execute <unnamed>: SET application_name = '' > 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) > ERROR: current transaction is aborted, commands ignored until end of transaction block Not sure where I am going at this point, just trying to understand. If I follow correct CREATE LOCAL TEMPORARY TABLE deck_types is inside the function stored_functions_v0.get_deck_types() which in turn is being called by stored_functions_v0.get_deck_master_properties(). Is this correct or not? Also why in the data being logged to admin.logs are the $$Executing SELECT * statements repeated, are there really simultaneous SELECTs or is that an artifact of the logging? > > > > Thanks in advance. > > > Mephysto > -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian,
it is not an artifact. This log comes from a multiplayer game, and this is an specific test to replicate the error. Practically, there are two users that execute the same operation, so you can see the simultaneous selects.
My opinion was every session was isolated from others and temporary table was atomic for every session (transaction).
But I think that I'm not true.
Are The two selects in the same session in my case? Why?
Thanks in advance.
Mephysto
On 6 February 2014 04:40, Adrian Klaver-3 [via PostgreSQL] <[hidden email]> wrote:
On 02/05/2014 12:19 PM, Mephysto 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>: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
> DETAIL: parameters: $1 = '1016'
> LOG: execute <unnamed>: SET application_name = ''
> LOG: execute <unnamed>: SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid
> DETAIL: parameters: $1 = '1016'
> LOG: execute <unnamed>: INSERT INTO admin.logs VALUES ('', '2014-02-05 16:15:13.249', 'it.redevogames.redevolib.classes.PgStoredExecutor', 'DEBUG', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?)$$, 'PgStoredExecutor.java:215', $$Executing SELECT * FROM stored_functions_v0.get_deck_from_id_user(?)
> $$)
> LOG: execute <unnamed>: SELECT * FROM stored_functions_v0.get_deck_from_id_user($1)
> DETAIL: parameters: $1 = '51'
> LOG: execute <unnamed>: SET application_name = ''
> 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)
> ERROR: current transaction is aborted, commands ignored until end of transaction blockNot sure where I am going at this point, just trying to understand.[hidden email]
If I follow correct CREATE LOCAL TEMPORARY TABLE deck_types is inside
the function stored_functions_v0.get_deck_types() which in turn is being
called by stored_functions_v0.get_deck_master_properties().
Is this correct or not?
Also why in the data being logged to admin.logs are the $$Executing
SELECT * statements repeated, are there really simultaneous SELECTs or
is that an artifact of the logging?
>
>
>
> Thanks in advance.
>
>
> Mephysto
>
--
Adrian Klaver
--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalIf you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790784.html
View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
@mephysto I think you are trying to solve the wrong type of problem. Creation of tables (temporary or not) are not supposed to run concurrently. So, this is not an issue of PostgreSQL but design. There are two ways to solve the problem. a) You could use the sessionID (provided The Glassfish server) to create unique names for the temporary table, if the temporary table is unique per session. If not, since you are talking about multiplayer game the temporary table name could include the userID, so it is unique per user. In that sense, it could be persistent per user, or temporary depending on your needs. In that case you will need EXECUTE command to CREATE the table in your pgsql code, since the table name must be a variable in your functions. b) I highly suspect that this temporary table is used for either caching or local sorting / ordering. In this case Postgres is again the wrong tool. You can use an embedded DB like SQLite, HSQLDB for the local database which may be used for this type of operations, which 'syncs' to the global PostgreSQL DB when connecting or disconnecting. Every client will have a separate copy of this DB, so no overhead to the server. Either way you will have solved your issue. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790806.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I don't need local sorting, I only had to retrieve some objects from db belongs to user.
A this point is it better unlogged tables or postgres object arrays?
Il 06/feb/2014 09:35 "alexandros_e [via PostgreSQL]" <[hidden email]> ha scritto:
@mephysto I think you are trying to solve the wrong type of problem. Creation of tables (temporary or not) are not supposed to run concurrently. So, this is not an issue of PostgreSQL but design. There are two ways to solve the problem.
a) You could use the sessionID (provided The Glassfish server) to create unique names for the temporary table, if the temporary table is unique per session. If not, since you are talking about multiplayer game the temporary table name could include the userID, so it is unique per user. In that sense, it could be persistent per user, or temporary depending on your needs. In that case you will need EXECUTE command to CREATE the table in your pgsql code, since the table name must be a variable in your functions.
b) I highly suspect that this temporary table is used for either caching or local sorting / ordering. In this case Postgres is again the wrong tool. You can use an embedded DB like SQLite, HSQLDB for the local database which may be used for this type of operations, which 'syncs' to the global PostgreSQL DB when connecting or disconnecting. Every client will have a separate copy of this DB, so no overhead to the server.
Either way you will have solved your issue.If you reply to this email, your message will be added to the discussion below:http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790806.html
View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/06/2014 12:09 AM, mephysto wrote: > Hi Adrian, > it is not an artifact. This log comes from a multiplayer game, and this > is an specific test to replicate the error. Practically, there are two > users that execute the same operation, so you can see the simultaneous > selects. > > My opinion was every session was isolated from others and temporary > table was atomic for every session (transaction). Well sessions and transactions are not the same thing. A simple explanation: A session is a specific connection to a database. A transaction is a unit of work. It can be explicitly bounded by BEGIN/COMMIT(ROLLBACK). In Postgres if you do not supply the BEGIN each statement gets one, so each statement is in a transaction. So a session can have one transaction or multiple. > > But I think that I'm not true. > > Are The two selects in the same session in my case? Why? The issue to me at least, assuming Postgres is working properly, is that you have two selects in the same transaction. The ON COMMIT DROP should drop the table at the end of the transaction. Now as Alban wrote it is possible that the table is being cached somehow. One suggestion I on that line of thought is to use dynamic commands: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN It is also possible, as mentioned previously, that the Java code is misbehaving. That there are threads interfering with each other. I am not a Java programmer so I can not be of any help there. > > Thanks in advance. > > Mephysto > > > -- Adrian Klaver adrian.klaver@gmail.com