Re: Temporary table already exists

[mephysto <mephystoonhell@xxxxxxxxx>]

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.