Thread: Select in temporary table

In response to Sylvain Lara <sly_lara@yahoo.com>:

> Hello,
>
> I found some subjects like mine on many forums, but the given solution doesn't work for me.
>
> I'm using PostgreSQL 8.4. I'm working on a C# application.
>
> When connecting to the application, a temporary table myTableTemp is created.
> This table is available for all the application duration, and is deleted when the session is killed, when user closes
theapplication. 
>
> The use should do this, after connnecting to my application :
>
> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and temporary table already exist because
createdwhen launching application) 
> - Menu item 2 : Another PL/PGSQL function selects number of rows in this table and displays it
>
> As I encountered my problem, I have just tried the second function (called by menu item 2), that should return me 0,
becausethe temporary table has not be filled. 
>
> When launching a first time the menu item 2, the function returns 0 ==> good result
> When launching a second time the menu item 2, I've got the following error ==> table myTableTemp does not exist.
>
> Sometimes, the menu item 2 works many times, if I'm waiting a little time between two executions, but at the end, the
sameerror occurs. 
>
>
> I found on some posts solutions talking about using the EXECUTE function, which I tried, but the same result still
occurs.
>
>
> My code is (not EXACTLY my code because just written by memory) :
>
> CREATE OR REPLACE FUNCTION test RETURN integer AS
> DECLARE
>         nbLines    integer;
>        
> BEGIN
>        
>         EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
>        
>         RETURN nbLines;
>        
> END;
>
>
> First execution :
> select test() ==> 0
>
> Second execution :
> select test() ==> table myTableTemp does not exist.
>
> Any ideas ?

I assume you're using CREATE TEMP TABLE to make the table.  Internally,
PostgreSQL automatically drops the table when the session is disconnected.
Also, the temp table is not visible from any other connection.

As a result, my guess is that either:
a) Your application disconnects and reconnects between the two runs
b) Your application establishes multiple connections at some point and
   uses a different one on the second run

It may be deep in the underlying libraries that this is happening.  I have
seen examples of code that establishe dozens of database connections for
a single application, because the code is poorly organized (as an example).

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Not sure about how de C# driver you use to connect, but if you have
multiple connections, then you have multiple sessions.

2010/4/1 Sylvain Lara <sly_lara@yahoo.com>:
>> Hello,
>>
>> I found some subjects like mine on many forums, but the given solution
>> doesn't work for me.
>>
>> I'm using PostgreSQL 8.4. I'm working on a C# application.
>>
>> When connecting to the application, a temporary table myTableTemp is
>> created.
>> This table is available for all the application duration, and is deleted
>> when the session is killed, when user closes the application.
>>
>> The use should do this, after connnecting to my application :
>>
>> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and
>> temporary table already exist because created when launching application)
>> - Menu item 2 : Another PL/PGSQL function selects number of rows in this
>> table and displays it
>>
>> As I encountered my problem, I have just tried the second function (called
>> by menu item 2), that should return me 0, because the temporary table has
>> not be filled.
>>
>> When launching a first time the menu item 2, the function returns 0 ==>
>> good result
>> When launching a second time the menu item 2, I've got the following error
>> ==> table myTableTemp does not exist.
>>
>> Sometimes, the menu item 2 works many times, if I'm waiting a little time
>> between two executions, but at the end, the same error occurs.
>>
>>
>> I found on some posts solutions talking about using the EXECUTE function,
>> which I tried, but the same result still occurs.
>>
>>
>> My code is (not EXACTLY my code because just written by memory) :
>>
>> CREATE OR REPLACE FUNCTION test RETURN integer AS
>> DECLARE
>>         nbLines    integer;
>>
>> BEGIN
>>
>>         EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
>>
>>         RETURN nbLines;
>>
>> END;
>>
>>
>> First execution :
>> select test() ==> 0
>>
>> Second execution :
>> select test() ==> table myTableTemp does not exist.
>>
>> Any ideas ?
>
> I assume you're using CREATE TEMP TABLE to make the table.  Internally,
> PostgreSQL automatically drops the table when the session is disconnected.
> Also, the temp table is not visible from any other connection.
>
> As a result, my guess is that either:
> a) Your application disconnects and reconnects between the two runs
> b) Your application establishes multiple connections at some point and
>   uses a different one on the second run
>
> It may be deep in the underlying libraries that this is happening.  I have
> seen examples of code that establishe dozens of database connections for
> a single application, because the code is poorly organized (as an example).
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> ---------------------------
>
> Hello,
>
> Thank you for answering me.
> You're right, my temporary table is done like that :  CREATE TEMP TABLE
>
> My application doesn't disconnect, between two executions because sometimes,
> it works two, three, four, .. times and I've got the error on the next
> execution.
>
> I have multiple connections that's right, but these connections are in the
> same session.
> If I insert data in my table, juste after creating it (when launching my
> application), the good number of rows is displayed.
> Then sometimes, the same number is displayed on the second execution. etc...
>
> I haven't developed the core, so I don't know exactly how it works.
> But I'm sure there is no disconnection between these two executions.
>
> Thank you very much
>
> ----------------------------
>
> Hello,
>
> Another test has been done, the first thing I'm doing in my PL/PGSQL
> function is :
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
>         raise notice 'function exists...';
> ELSE
>         raise notice 'function not exists...';
> END IF;
>
>
> So my function is like that :
>
> CREATE OR REPLACE FUNCTION test()  RETURNS integer AS
> $BODY$
> DECLARE
>
>     nbLines    integer;
>
> BEGIN
>
>         IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp')
> THEN
>                 raise notice 'function exists...';
>         ELSE
>                 raise notice 'function not exists...';
>         END IF;
>
>         -- Select from myTableTemp
>         SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE
> inst_id IN (select inst_id from myTableTemp);
>
>
>     RETURN nbLines;
>
> EXCEPTION
>     WHEN OTHERS THEN
>         BEGIN
>             raise exception 'Erreur function test() : %', SQLERRM;
>         END;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> My function notices everytime 'function exists', even when I've got the
> error "relation myTableTemp does not exist".
>
> Any ideas ? I'm a little bit disappointed with this problem...
>
> Thanks a lot
>
> ----------------------------
>
> My temporary table is created like that :
>
> CREATE TEMPORARY TABLE myTableTemp (inst_id integer);
>
>
>
>

On 1 Apr 2010, at 10:47, Sylvain Lara wrote:
> De : jose javier parra sanchez <jojapasa@gmail.com>
> Cc : pgsql-general@postgresql.org
> Envoyé le : Jeu 1 avril 2010, 9 h 26 min 27 s
> Objet : Re: Re : Re : Re : [GENERAL] Select in temporary table
>
> Not sure about how de C# driver you use to connect, but if you have
> multiple connections, then you have multiple sessions.
>
> -----------------------------------------------
>
> I've got multiple connections, but when selecting data in my temp table (when it works, once, twice, three times and
morebefore the error), I've got my data when selecting into the temp table. 
>
> Whereas if I connect the same base, at the same time, from another client, it doesn't display the same data, which is
correctfor me. 
>
> Each client has it's own temporary table data.
>
> But my problem is that this temporary table is randomly accessible by the same client.
> And it's seems that if I wait a moment between two executions (I put a breakpoint and browse my NpgSQLConnection
object),I can access my temp table without any problem... 


Sounds like there's a connection pooler in there somewhere and you sometimes get a different connection than the one
yourtemp table lives in. Only the connection that created the temp table will be able to see it. 

Other possibilities I can think of:

Are you rolling back the transaction with which you created the temp table? In that case the temp table is gone after
yourolled back, so if later code is trying to access it it won't find it. 

Is your client multi-threaded? Is it possible that you're trying to access the temp table from another thread before it
finishedcreating? 
That's probably not the case, or you'd either need to serialize your database access to be able to use the same
connection- in which case this situation can't occur - or you're using multiple parallel connections from the same
clientand we're back at the multiple-connection answer. If you'd be using one connection in parallel threads without
serializingthem then you're having more serious issues to deal with... 

It's most probably a problem in your client or your connection library, other people are using temp tables without a
problem.Temp tables are not exactly a new feature either. 

Alban Hertroys

PS. Considering the way you quote your mails you seem to have an absolutely terrible mail client. If you have a choice,
considergetting something decent. 

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb46f1610411569890503!