Re : Re : Re : Re : Select in temporary table

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

----------------------------------------------

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 more before 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 correct for 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...