Re: Temporary table already exists

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(at)n5(dot)nabble(dot)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
> > 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] <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.