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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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-uncommittedThe 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Albe,this is code of my stored function: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: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5789857.html
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>I had a similar problem once. The pool is reusing connections and the temporary tables are still there.</div>
<div>Now I always create new temporary tables with a unique name like this:</div>
<div> </div>
<div>tmpTableId = "TMP" + Math.abs(generateUUID().hashCode());<br/>
if (tmpTableId.length() > 15)<br/>
tmpTableId = tmpTableId.substring(tmpTableId.length() - 15, tmpTableId.length());<br/>
<br/>
conn.setAutoCommit(true);<br/>
tableStmt = conn.createStatement();<br/>
<br/>
try {<br/>
// create global temporary tables<br/>
tableStmt.executeUpdate("create temporary table TABLE_ANME_" + tmpTableId + "( ... ) on commit preserve rows");</div>
<div> </div>
<div>etc.</div>
<div> </div>
<div>Then you have to add the tmpTableId to every statement in your code but it should work fine.</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Freitag, 31. Januar 2014 um 12:04 Uhr<br/>
<b>Von:</b> mephysto <mephystoonhell@gmail.com><br/>
<b>An:</b> pgsql-general@postgresql.org<br/>
<b>Betreff:</b> Re: [GENERAL] Temporary table already exists</div>
<div name="quoted-content">Hi Albe, this is code of my stored function:
<pre> 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;</pre>
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
<hr align="left" width="300"/> View this message in context: <a href="http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5789857.html" target="_blank">Re: Temporary table already exists</a><br/>
Sent from the <a href="http://postgresql.1045698.n5.nabble.com/PostgreSQL-general-f1843780.html" target="_blank">PostgreSQL - general mailing list archive</a> at Nabble.com.</div>
</div>
</div>
</div></div></body></html>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
SELECTstored_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.
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
SELECTstored_functions_v0.get_card_deck_types(t1.id_master_card) AS
deck_type_idsFROM 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 tabledeck_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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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] <http://user/SendEmail.jtp?type=node&node=5790697&i=0>--
Sent via pgsql-general mailing list ([hidden email]<http://user/SendEmail.jtp?type=node&node=5790697&i=1>)To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general------------------------------
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-tp5789852p5790697.html
To unsubscribe from Temporary table already exists, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5789852&code=bWVwaHlzdG9vbmhlbGxAZ21haWwuY29tfDU3ODk4NTJ8LTkwNDU5Mzg0Ng==>
.
NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
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 create the same table; I seem to recall reading on this list that such was possible in old versions of Postgres. I strongly doubt 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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] <
ml-node+s1045698n5790784h44@n5.nabble.com> 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
SELECTstored_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-0516: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 * FROMstored_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-0516: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 * FROMstored_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
SELECTstored_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
[hidden email] <http://user/SendEmail.jtp?type=node&node=5790784&i=0>--
Sent via pgsql-general mailing list ([hidden email]<http://user/SendEmail.jtp?type=node&node=5790784&i=1>)To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general------------------------------
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-tp5789852p5790784.html
To unsubscribe from Temporary table already exists, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5789852&code=bWVwaHlzdG9vbmhlbGxAZ21haWwuY29tfDU3ODk4NTJ8LTkwNDU5Mzg0Ng==>
.
NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790803.html
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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]" <
ml-node+s1045698n5790806h26@n5.nabble.com> 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
To unsubscribe from Temporary table already exists, click here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5789852&code=bWVwaHlzdG9vbmhlbGxAZ21haWwuY29tfDU3ODk4NTJ8LTkwNDU5Mzg0Ng==>
.
NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790819.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.