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@xxxxxxxxx>: >> 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); > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
