Re: Temporary table already exists

["Felix Kunde" <felix-kunde@xxxxxx>]

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 + "( ... ) on commit 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@xxxxxxxxx>
An: pgsql-general@xxxxxxxxxxxxxx
Betreff: Re: 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 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.