Thread: Temporary table already exists

Temporary table already exists

From
mephysto
Date:
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.


Re: Temporary table already exists

From
Albe Laurenz
Date:
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

Re: Temporary table already exists

From
mephysto
Date:
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 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.

Re: Temporary table already exists

From
Albe Laurenz
Date:
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



Re: Temporary table already exists

From
"Felix Kunde"
Date:
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.

Re: Temporary table already exists

From
mephysto
Date:
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.


Re: Temporary table already exists

From
Dmitriy Igrishin
Date:



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;

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

Re: Temporary table already exists

From
mephysto
Date:
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.


Re: Temporary table already exists

From
Adrian Klaver
Date:
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


Re: Temporary table already exists

From
Albe Laurenz
Date:
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

Re: Temporary table already exists

From
mephysto
Date:
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.


Re: Temporary table already exists

From
Adrian Klaver
Date:
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


Re: Temporary table already exists

From
mephysto
Date:
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.


Re: Temporary table already exists

From
Adrian Klaver
Date:
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


Re: Temporary table already exists

From
Mephysto
Date:

​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-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.
NAML

Re: Temporary table already exists

From
Alban Hertroys
Date:
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.



Re: Temporary table already exists

From
Adrian Klaver
Date:
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


Re: Temporary table already exists

From
mephysto
Date:
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 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]


--
Sent via pgsql-general mailing list ([hidden email])
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.
NAML



View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Temporary table already exists

From
alexandros_e
Date:
@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.


Re: Temporary table already exists

From
mephysto
Date:

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
To unsubscribe from Temporary table already exists, click here.
NAML


View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Temporary table already exists

From
Adrian Klaver
Date:
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