persistent db connections in PHP

At least in mySQL Any temporary tables are known only t the connection that
you have created e.g.

/*****connected as root to localhost *********/
mysql> CREATE TEMPORARY TABLE test.TEMP_TABLE (COL VARCHAR(20));
ERROR 1050 (42S01): Table 'temp_table' already exists
mysql> SELECT * from test.TEMP_TABLE;
Empty set (0.00 sec)

mysql> INSERT INTO test.TEMP_TABLE VALUES ('FooBar');
Query OK, 1 row affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.TEMP_TABLE;
+--------+
| COL |
+--------+
| FooBar |
+--------+
1 row in set (0.00 sec)
/**********we see that the TEMP_TABLE exists and has 1 row of Data Inserted
*******/

/************Second connection ****************/
C:\MySQL>.\bin\mysql -u test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> status;
--------------
\bin\mysql Ver 14.12 Distrib 5.0.37, for Win32 (ia32)

Connection id: 26
Current database:
Current user: test@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 2 hours 29 min 21 sec

Threads: 2 Questions: 64 Slow queries: 0 Opens: 25 Flush tables: 1 Open
tab
les: 6 Queries per second avg: 0.007
--------------

mysql> select * from test.TEMP_TABLE;
ERROR 1146 (42S02): Table 'test.temp_table' doesn't exist

/*********this proves that a second connection cannot see temp tables
created by the first connection*****/
I *****assume***** the same is true for Postgres!

HTH
Martin--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: <lawpoop@gmail.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, June 15, 2007 10:52 AM
Subject: [GENERAL] persistent db connections in PHP

Hello all!

I'm working on a PHP site using Postgres as a back-end. I have an
include at the top of each page that runs the pg_connect function.

I'm implementing some temporary tables, which I understand are
destroyed automatically at the end of the session. It seems to me that

- Temp tables are local to a session (ie. a connection)
- Persistent connections in PHP creates a pool of connections. When you
pg_pconnect() you get a connection from the pool. You have no control over
which connection you get, so you don't really know if the temp tables have
been created by a previous connection or not.

So, the best solution is to create the temp tables inside a transaction,
with ON COMMIT DROP, do your work, and let them disappear. This will save
you from getting temp tables already filled by stuff from the previous
webpage which got this connection. For the same reason, pg_pconnect()
should ALWAYS be followed by pg_query( "ROLLBACK" ). If you put rollback
in register_shutdown_function(), be aware that it will NOT be called if
your script is nuked for trying to malloc more than the allowed memory
limit, or for other brutal reasons...

Also note that PHP, being PHP, sucks, and thusly, will not reconnect
persistent connections when they fail. You have to kick it a bit.

On 16/06/2007 16:46, PFC wrote:

Also note that PHP, being PHP, sucks, and thusly, will not reconnect
persistent connections when they fail. You have to kick it a bit.

I've seen similar negative comments before on this list about PHP, and
I'm curious to know what informs them.

I use PHP quite a bit, and though I wouldn't claim to be any sort of an
expert, I like it a lot: IMHO it's powerful, fast and easy to use. Mind
you, I came to PHP from an ASP/VBscript background, so anything would
have been an improvement.

Having said that, the main gripes I would have with PHP are (i)
variables aren't strongly typed, which can bite you unless you're
careful, and (ii) you don't have to declare variables before using them,
which can also cause trouble - in VBScript you have "option expicit"
which forces you to declare your variables; I'd like to see something
similar in PHP.

Apologies if this is off-topic for this list, but I'm curious as to why
others reckon that PHP sucks.....

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

I've seen similar negative comments before on this list about PHP, and
I'm curious to know what informs them.

Maybe the fact that, when I coded a database object/form library, it took
me LONGER to ensure that empty strings / NULLs / zero valued floats and
integers / etc were handled correctly than it took me to code the entire
thing ?

I use PHP quite a bit, and though I wouldn't claim to be any sort of an
expert, I like it a lot: IMHO it's powerful, fast and easy to use. Mind
you, I came to PHP from an ASP/VBscript background, so anything would
have been an improvement.

LOL. Yeah.

Having said that, the main gripes I would have with PHP are (i)
variables aren't strongly typed, which can bite you unless you're
careful, and (ii) you don't have to declare variables before using them,
which can also cause trouble - in VBScript you have "option expicit"
which forces you to declare your variables; I'd like to see something
similar in PHP.

Apologies if this is off-topic for this list, but I'm curious as to why
others reckon that PHP sucks.....

It's a matter of mindset. PHP and Postgres have really opposite mindsets.
Python is a lot more similar to Postgres, for instance :

- Postgres, Python : strongly typed, throws an error rather than doing
funny stuff with your data, your code does what you think it should do.
- PHP : very loosely typed, does whatever it wants, your code will
surprise you...

To stay on topic :

- There is no way to know if your persistent connection is a new
connection (so you might want to setup and issue a few PREPARE statements)
or an already "second hand" connection.
- There is no way to reconnect a dead persistent connection (if you
reboot your database server, you'll have to restart all your
webservers/PHP processes manually)
- pg_query() returns everything as strings (python/psycopg2 converts
postgres types to python types, DATE becomes datetime.datetime, arrays
too, and you can customize it)
- handling of NULL, 0, '', array(), 0.0 is horrendous
- NO DAMN UNICODE SUPPORT WTF ? (python/psycopg does the right thing)
- register_shutdown_function() isn't always called
- the object model is horrible
- no closures or lexical scoping, no first class classes or function,
strings aren't objects
- built by accretion rather than thoughtful design
- etc, etc

Still, I use it, and it works. It is always poised to bite though. You
have to wear chain mail pants with PHP.

Raymond O'Donnell wrote:

On 16/06/2007 16:46, PFC wrote:

Also note that PHP, being PHP, sucks, and thusly, will not
reconnect persistent connections when they fail. You have to kick it a
bit.

I've seen similar negative comments before on this list about PHP, and
I'm curious to know what informs them.

This is likely to be a long thread, but I will break it down very simply.

PHP works.

PHP does not work the way "real" programmers want it to.

Everything else comes down to semantic arguments of what is correct and
what isn't. Most of the arguments will be correct in that:

PHP as a programming language is horrible
PHP does things that any sane programmer would run from

But go back to my first point:

PHP works.

PHP is easy, I say this as a guy that will code Python over PHP any day
of the week but, PHP works.

Sincerely,

Joshua D. Drake

I use PHP quite a bit, and though I wouldn't claim to be any sort of an
expert, I like it a lot: IMHO it's powerful, fast and easy to use. Mind
you, I came to PHP from an ASP/VBscript background, so anything would
have been an improvement.

Having said that, the main gripes I would have with PHP are (i)
variables aren't strongly typed, which can bite you unless you're
careful, and (ii) you don't have to declare variables before using them,
which can also cause trouble - in VBScript you have "option expicit"
which forces you to declare your variables; I'd like to see something
similar in PHP.

Apologies if this is off-topic for this list, but I'm curious as to why
others reckon that PHP sucks.....

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.

On 6/16/07, PFC <lists@peufeu.com> wrote:

"PHP: very loosely typed, does whatever it wants"

yeah php got a life of its own! sure be a lazy programmer and blame
sql injection etc crap on php or try http://www.xdebug.org/ and
others.

bottomline- people who know other tools better gripe because their's
isn't as popular.
jzs

On 06/16/07 11:24, PFC wrote:
[snip]

It's a matter of mindset. PHP and Postgres have really opposite
mindsets. Python is a lot more similar to Postgres, for instance :

- Postgres, Python : strongly typed, throws an error rather than
doing funny stuff with your data, your code does what you think it
should do.

I wouldn't call Python *strongly* typed, but I do know what you
mean. I think.

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

On 6/17/07, John Smith <jayzee.smith@gmail.com> wrote:

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.

That's like saying that BSD or Linux should be more like
Windows because there's more Windows than Linux stuff
to be found on the web ....

The following is a bit out of proportion, but you may get
the idea"
"If the majority of the population had cancer it would still be
an illness and not the norm."

-- Cheers,
Andrej

On Saturday 16 June 2007, John Smith wrote:

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.

Mhhh - what does PHP have to do with Postgresql? Lots of pages just end
in .php, which is why the google results are so high - guess what, the
tool "html" hits 3.2 billion :-)

On 6/16/07, PFC <lists@peufeu.com> wrote:

"PHP: very loosely typed, does whatever it wants"

yeah php got a life of its own! sure be a lazy programmer and blame
sql injection etc crap on php or try http://www.xdebug.org/ and
others.

bottomline- people who know other tools better gripe because their's
isn't as popular.

I doubt popularity has anything to do with it. I've used PHP as it emerged
back in 1990. I liked it back then because it produced faster results than
perl/cgi. The downside was - and IMHO still is - that a lot of application
code is embedded in the pages. Ok, nowadays PHP has advanced to a point where
you don't really have to do that anymore, but still many people do -
resulting in a nice, quickly built but often unmaintainable application.

IMHO there are many other web environments that are much better at separating
the view from the actual code.

In the end, neither of these points has anything to do with postgresql.

Personally I'm more inclined to agree with Joshua on this one...

Uwe

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

On 06/16/07 15:04, Andrej Ricnik-Bay wrote:

On 6/17/07, John Smith <jayzee.smith@gmail.com> wrote:

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.

That's like saying that BSD or Linux should be more like
Windows because there's more Windows than Linux stuff
to be found on the web ....

You've not used KDE lately, have you? :)

And while *ix might not get viruses, it's got it's share of worms
and rootkits.

The following is a bit out of proportion, but you may get
the idea"
"If the majority of the population had cancer it would still be
an illness and not the norm."

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

and that's not how it is?? ever tried ubuntu and saw how it looks a
bit like windows thesedays??

good luck but try getting funding/acceptance with this line "i wanna
design a new tool but i don't want features from that other tool that
work in the market"

let's stop blaming php if you don't know what smarty is.
jzs

On 6/16/07, Uwe C. Schroeder <uwe@oss4u.com> wrote:

Mhhh - what does PHP have to do with Postgresql? Lots of pages just end
in .php, which is why the google results are so high - guess what, the
tool "html" hits 3.2 billion :-)

show me a database that doesn't respect html and i'll show you one i
don't want :).
jzs

I wouldn't call Python *strongly* typed, but I do know what you mean. I
think.

It is strongly typed (string + int = error), just not statically typed
(but you saw what I mean ;)

"PHP: very loosely typed, does whatever it wants"
yeah php got a life of its own! sure be a lazy programmer and blame
sql injection etc crap on php or try http://www.xdebug.org/ and
others.

No need.
I either use pg_query_params() which automagically handles all quoting,
or an ORM which does the same.
There is no reason to include strings in SQL statements except laziness.
MySQL does not have a mysql_query_params() for PHP, so you have to write
one, it's pretty simple.

Python's (and perl) strength in this respect is that they make it easier
to use the safe solution, ie :
query( "sql with ? or $1 or %s", arg, arg, arg )

PEAR::DB is horrendous.

On 06/16/07 15:34, John Smith wrote:

On 6/16/07, Uwe C. Schroeder <uwe@oss4u.com> wrote:

Mhhh - what does PHP have to do with Postgresql? Lots of pages just end
in .php, which is why the google results are so high - guess what, the
tool "html" hits 3.2 billion :-)

show me a database that doesn't respect html and i'll show you one i
don't want :).

Respect html?

WTH does that mean?

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

On 6/17/07, Raymond O'Donnell <rod@iol.ie> wrote:

Having said that, the main gripes I would have with PHP are (i)
variables aren't strongly typed, which can bite you unless you're
careful, and (ii) you don't have to declare variables before using them,
which can also cause trouble - in VBScript you have "option expicit"
which forces you to declare your variables; I'd like to see something
similar in PHP.

PHP offers this functionality already.

error_reporting(E_ALL); // This will throw E_NOTICE and E_WARNING too

E_NOTICE is what will flag when variables are not explicitly declared.

As for strong typing, I see that as a benefit, but that's a whole
different can of worms. How much value you derive from a language
depends on how you use it. After playing for years with Perl, and now
with Python and Ruby, I think PHP is still where it's at. I am not
overly into OOP, I like to use what works best. Our code is very, very
cleanly structured in fast procedural functions, and where possible,
no functions at all (because instantiating a function still takes some
processing power) and we follow something like an MVC model. And we
wouldn't touch PEAR with a borrowed pole, although we do pick out some
classes once in a while.

Apologies if this is off-topic for this list, but I'm curious as to why
others reckon that PHP sucks.....

It doesn't. However, many average joes who find PHP accessible and
therefore usable, do suck rocks.

EP

On 6/17/07, PFC <lists@peufeu.com> wrote:

I either use pg_query_params() which automagically handles all quoting,
or an ORM which does the same.
There is no reason to include strings in SQL statements except laziness.
MySQL does not have a mysql_query_params() for PHP, so you have to write
one, it's pretty simple.

Take your pick:

- Quotemeta
- Addslashes
- Htmlentities($string, 'utf-8')
- Magic_quotes in the INI
- Anti-XSS code (several available online)

Python's (and perl) strength in this respect is that they make it easier
to use the safe solution, ie, query( "sql with ? or $1 or %s", arg, arg, arg )

$sql = '"select column from table where field = '%s';
$sql = sprintf($sql, $submittedvariable);
..

PEAR::DB is horrendous.

And hugely unnecessary.

EP

On Saturday 16. June 2007 23:34, Erick Papadakis wrote:

How much value you derive from a language
depends on how you use it. After playing for years with Perl, and now
with Python and Ruby, I think PHP is still where it's at.

I too have played around with Perl and Python, and use both of them for
special jobs, but I'm writing my dynamic web pages in PHP. In
hindsight, I might have been better off writing them in Perl, but I was
put off by the lousy HTML generated by the CGI module. It doesn't even
close paragraphs. PHP doesn't have anything like the CGI.pm, but I'm
quite content with hand coding where every single HTML tag should go.

I am not
overly into OOP, I like to use what works best. Our code is very, very
cleanly structured in fast procedural functions, and where possible,
no functions at all (because instantiating a function still takes some
processing power) and we follow something like an MVC model. And we
wouldn't touch PEAR with a borrowed pole, although we do pick out some
classes once in a while.

I consider writing database interfaces in OOP as a fundamental
misunderstanding of what RDBMS's are good for. The Java droids and the
Ruby fanbois, for instance, seem to prefer fetching single values from
the db, and then do the entire logic in their own software.

It doesn't. However, many average joes who find PHP accessible and
therefore usable, do suck rocks.

The average Joes program in Java and Ruby nowadays.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On 6/17/07, Ron Johnson <ron.l.johnson@cox.net> wrote:

That's like saying that BSD or Linux should be more like
Windows because there's more Windows than Linux stuff
to be found on the web ....

You've not used KDE lately, have you? :)

That'll be right. I use fluxbox. :}

And while *ix might not get viruses, it's got it's share of worms
and rootkits.

There's are some, but check the numbers ... I'd dare say it would
go the other way round that it went in the PHP <-> Postgres hits,
with Windows being a "winner" by magnitudes.

-- Cheers,
Andrej

On Jun 16, 2007, at 3:38 PM, John Smith wrote:

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.

I would oppose the notion that someone needs to adapt to meet a
lowest common denominator. That's a willing sacrifice of yourself.

I've been around a lot of different languages to different levels of
involvement and have formed a theory that hasn't been disputed in 5
years.

Popular languages have nothing to do with how good they are.
They have to do with the lower entry barrier.

Examples:

Java developers are crawling out of the woodwork. That's because I
can go to the local community college and get a couple classes done.
Run over to some training company like New Horizons, and walk away
with a Java Certified Programmer paper in a month. Now I can walk
into a company and say, "I'm a Java developer, see!". Unfortunately
they also think Java is the only language anything can be programmed in.

Similarly the MCSE (Must Consult Someone Else) is a easy piece of
paper to obtain that says you are Software Engineer. I took the
tests 8 years ago, they're crap.

MySQL is insanely easy to set up. But there are a lot of MySQL users
who really don't understand the concept of a database and tend to
treat it like a flat file or spreadsheet.

I got in a shouting match at a Rails conference because they didn't
see why you should put things like UNIQUE indexes or Referential
Integrity in their database since they have the Rails functions
of .exists? and .unique? (or something like that...) Easy to use,
but it requires smarts to use it well.

Contrary to these examples:

Perl programmers who actually do this for a period of time tend to be
extremely good at general programming concepts. Perl has a high
entry barrier. There is a LOT to learn before you can do much of
significance. Few of them are the "idiots" that the other fast track
certification languages can provide. It's certification by war stories.

C/C++ programmers who do this beyond their BSCSE courses are the same
way. They know their stuff and can be trusted to do it well. Again,
not a low barrier language.

It is my opinion that MySQL and PHP both fall into the category of
low entry barrier applications. They are easy to set up, easy to get
"hello world" and you can find lots of company supported training
programs. But this has nothing to do with a language/application
being good or safe.

But at the same time, I think the communities do not suffer from
having higher entry barriers to them. If everything was as popular
as Windows then there would be a lot of AOL addresses signed up on
this list, something I'm OK with not having.