| From: | "Felix Kunde" <felix-kunde(at)gmx(dot)de> |
|---|---|
| To: | mephysto <mephystoonhell(at)gmail(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Temporary table already exists |
| Date: | 2014-01-31 13:02:17 |
| Message-ID: | trinity-f3c2d1d4-d350-4883-a123-a3d1bf9185b3-1391173337496@3capp-gmx-bs32 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | 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(at)gmail(dot)com><br/>
<b>An:</b> pgsql-general(at)postgresql(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | mephysto | 2014-01-31 13:48:53 | Re: Temporary table already exists |
| Previous Message | Albe Laurenz | 2014-01-31 12:51:43 | Re: Temporary table already exists |