Re: Temporary table already exists - Mailing list pgsql-general

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.