Обсуждение: 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

        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;

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.

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

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

@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?

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