Re: Re : Re : Re : 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.

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);
>
>
>
>