pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedtemp tables and pg_pconnect()
6+ messages / 4 participants
[nested] [flat]
* temp tables and pg_pconnect()
@ 2001-10-13 17:14 Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 18:23 ` Re: temp tables and pg_pconnect() Adam Lang <aalang@rutgersinsurance.com>
2001-10-13 19:04 ` Re: temp tables and pg_pconnect() Papp Gyozo <pgerzson@freestart.hu>
0 siblings, 2 replies; 6+ messages in thread
From: Emmanuel SARACCO @ 2001-10-13 17:14 UTC (permalink / raw)
To: pgsql-php
hi,
I am using temp tables in stored procedures (I just can not create
normal tables because I must retreive values from those tables in those
stored procedures with the "execute" instruction).
doing connexions with pg_connect()/pg_close() on each php page all work
fine, but when I try to use pg_pconnect() (wich is much more efficient!)
temp table are not detroyed...
how can I use pg_connect() and continue using temp tables in stored
procedures?
thanks
--
Emmanuel SARACCO
Email: esaracco@noos.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp tables and pg_pconnect()
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
@ 2001-10-13 18:23 ` Adam Lang <aalang@rutgersinsurance.com>
2001-10-13 19:29 ` Re: temp tables and pg_pconnect() Brent R. Matzelle <bmatzelle@yahoo.com>
2001-10-14 08:48 ` Re: temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
1 sibling, 2 replies; 6+ messages in thread
From: Adam Lang @ 2001-10-13 18:23 UTC (permalink / raw)
To: pgsql-php
Are temp tables supposed to be destroyed when the connection is cosed? If
so, then the reason is because with pconnect, the connection really is never
killed as far as Postgresql sees it. The connection remains open.
Can you drop the temp table yourself through the pg_exec comand and just use
DROP TABLE ?
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Emmanuel SARACCO" <esaracco@noos.fr>
To: <pgsql-php@postgresql.org>
Sent: Saturday, October 13, 2001 1:14 PM
Subject: [PHP] temp tables and pg_pconnect()
> hi,
>
> I am using temp tables in stored procedures (I just can not create
> normal tables because I must retreive values from those tables in those
> stored procedures with the "execute" instruction).
>
> doing connexions with pg_connect()/pg_close() on each php page all work
> fine, but when I try to use pg_pconnect() (wich is much more efficient!)
> temp table are not detroyed...
>
> how can I use pg_connect() and continue using temp tables in stored
> procedures?
>
> thanks
>
>
> --
> Emmanuel SARACCO
> Email: esaracco@noos.fr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp tables and pg_pconnect()
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 18:23 ` Re: temp tables and pg_pconnect() Adam Lang <aalang@rutgersinsurance.com>
@ 2001-10-13 19:29 ` Brent R. Matzelle <bmatzelle@yahoo.com>
1 sibling, 0 replies; 6+ messages in thread
From: Brent R. Matzelle @ 2001-10-13 19:29 UTC (permalink / raw)
To: pgsql-php
--- Adam Lang <aalang@rutgersinsurance.com> wrote:
> Are temp tables supposed to be destroyed when the connection
> is cosed? If
> so, then the reason is because with pconnect, the connection
> really is never
> killed as far as Postgresql sees it. The connection remains
> open.
pg_pconnect does keep the existing connection to PostgresSQL so
the temporary table would not be dropped.
> Can you drop the temp table yourself through the pg_exec
> comand and just use
> DROP TABLE ?
That would be a reasonable approach. Or, if you do not need to
drop the table it would be might be more efficient to perform a
TRUNCATE rather than the DROP and CREATE combo. That would
depend upon your system.
Brent
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp tables and pg_pconnect()
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 18:23 ` Re: temp tables and pg_pconnect() Adam Lang <aalang@rutgersinsurance.com>
@ 2001-10-14 08:48 ` Emmanuel SARACCO <esaracco@noos.fr>
1 sibling, 0 replies; 6+ messages in thread
From: Emmanuel SARACCO @ 2001-10-14 08:48 UTC (permalink / raw)
To: pgsql-php
hi adam,
thanks for your answer but here is part of my stored procedure:
------------------
CREATE TEMP TABLE res_tot (tot INTEGER);
sqlreq := '
INSERT INTO
res_tot
SELECT
count(no_dossier)
FROM ' ||
vs_lb
||'
WHERE 1=1 ';
IF surf_min>0 THEN
sqlreq :=sqlreq||' AND surf_hab >= '|| surf_min ||' ';
end if;
IF surf_max>0 THEN
sqlreq :=sqlreq||' AND surf_hab <= '|| surf_max ||' ';
end if;
IF prix_min>0 THEN
IF devise = 'FRANC' THEN
sqlreq :=sqlreq||' AND prix_franc >= '|| prix_min ||' ';
ELSE
sqlreq :=sqlreq||' AND prix_euro >= '|| prix_min ||' ';
END IF;
END IF;
EXECUTE sqlreq;
SELECT INTO resultat tot FROM res_tot;
RETURN resultat;
------------------
as you see, I can not create a normal table because of concurrency
problems. the solution would be to create a normal table with random
generated name but I could not retrieve a value from it with a "execute"
instruction... so I think it is impossible for me to use pg_pconnect() :-(
bye
Adam Lang wrote:
> Are temp tables supposed to be destroyed when the connection is cosed? If
> so, then the reason is because with pconnect, the connection really is never
> killed as far as Postgresql sees it. The connection remains open.
>
> Can you drop the temp table yourself through the pg_exec comand and just use
> DROP TABLE ?
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Emmanuel SARACCO" <esaracco@noos.fr>
> To: <pgsql-php@postgresql.org>
> Sent: Saturday, October 13, 2001 1:14 PM
> Subject: [PHP] temp tables and pg_pconnect()
>
>
>
>>hi,
>>
>>I am using temp tables in stored procedures (I just can not create
>>normal tables because I must retreive values from those tables in those
>>stored procedures with the "execute" instruction).
>>
>>doing connexions with pg_connect()/pg_close() on each php page all work
>>fine, but when I try to use pg_pconnect() (wich is much more efficient!)
>>temp table are not detroyed...
>>
>>how can I use pg_connect() and continue using temp tables in stored
>>procedures?
>>
>>thanks
>>
>>
>>--
>>Emmanuel SARACCO
>>Email: esaracco@noos.fr
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>>
>>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
--
Emmanuel SARACCO
Email: esaracco@noos.fr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp tables and pg_pconnect()
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
@ 2001-10-13 19:04 ` Papp Gyozo <pgerzson@freestart.hu>
2001-10-13 21:04 ` Re: temp tables and pg_pconnect() Adam Lang <aalang@rutgersinsurance.com>
1 sibling, 1 reply; 6+ messages in thread
From: Papp Gyozo @ 2001-10-13 19:04 UTC (permalink / raw)
To: esaracco@noos.fr; pgsql-php
I'm afraid you can't save your temp tables if you use pg_connect,
because the connection opened by pg_connect automagically closed
on the end of the script. So connection closed and temp tables
will be dropped.
----- Original Message -----
From: "Emmanuel SARACCO" <esaracco@noos.fr>
To: <pgsql-php@postgresql.org>
Sent: Saturday, October 13, 2001 7:14 PM
Subject: [PHP] temp tables and pg_pconnect()
> hi,
>
> I am using temp tables in stored procedures (I just can not create
> normal tables because I must retreive values from those tables in those
> stored procedures with the "execute" instruction).
>
> doing connexions with pg_connect()/pg_close() on each php page all work
> fine, but when I try to use pg_pconnect() (wich is much more efficient!)
> temp table are not detroyed...
>
> how can I use pg_connect() and continue using temp tables in stored
> procedures?
>
> thanks
>
>
> --
> Emmanuel SARACCO
> Email: esaracco@noos.fr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: temp tables and pg_pconnect()
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 19:04 ` Re: temp tables and pg_pconnect() Papp Gyozo <pgerzson@freestart.hu>
@ 2001-10-13 21:04 ` Adam Lang <aalang@rutgersinsurance.com>
0 siblings, 0 replies; 6+ messages in thread
From: Adam Lang @ 2001-10-13 21:04 UTC (permalink / raw)
To: pgsql-php
You got it backwards I think. He wants to use pg_pconnect and have the temp
tables dropped.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Papp Gyozo" <pgerzson@freestart.hu>
To: <esaracco@noos.fr>; <pgsql-php@postgresql.org>
Sent: Saturday, October 13, 2001 3:04 PM
Subject: Re: [PHP] temp tables and pg_pconnect()
> I'm afraid you can't save your temp tables if you use pg_connect,
> because the connection opened by pg_connect automagically closed
> on the end of the script. So connection closed and temp tables
> will be dropped.
>
> ----- Original Message -----
> From: "Emmanuel SARACCO" <esaracco@noos.fr>
> To: <pgsql-php@postgresql.org>
> Sent: Saturday, October 13, 2001 7:14 PM
> Subject: [PHP] temp tables and pg_pconnect()
>
>
> > hi,
> >
> > I am using temp tables in stored procedures (I just can not create
> > normal tables because I must retreive values from those tables in those
> > stored procedures with the "execute" instruction).
> >
> > doing connexions with pg_connect()/pg_close() on each php page all work
> > fine, but when I try to use pg_pconnect() (wich is much more efficient!)
> > temp table are not detroyed...
> >
> > how can I use pg_connect() and continue using temp tables in stored
> > procedures?
> >
> > thanks
> >
> >
> > --
> > Emmanuel SARACCO
> > Email: esaracco@noos.fr
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-10-14 08:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-13 17:14 temp tables and pg_pconnect() Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 18:23 ` Adam Lang <aalang@rutgersinsurance.com>
2001-10-13 19:29 ` Brent R. Matzelle <bmatzelle@yahoo.com>
2001-10-14 08:48 ` Emmanuel SARACCO <esaracco@noos.fr>
2001-10-13 19:04 ` Papp Gyozo <pgerzson@freestart.hu>
2001-10-13 21:04 ` Adam Lang <aalang@rutgersinsurance.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox