Re: Re: useability of apache, PHP, Postgres for real business apps - Mailing list pgsql-general

On Thursday, June 28, 2001, at 08:54  AM, Calvin Dodge wrote:
> pvissenb@csc.com (Pieter Vissenberg) wrote in message
> news:<7ab85fc.0106272359.7bbbfbf2@posting.google.com>...
>> All,
>> Does anyone have experience with the use of PostgreSQL in combination
>> with Apache and PHP in a real life business environment?
> We're using those three for simple workflow docs right now (tracking
> tasks and customer installs, as well as comments about those
> operations), and planning on using it for customer data entry (once I
> figure out the "best" way to translate our Access-based software to a
> web-based format).

I'm managing over $600K (USD) with this combo per month, across
400+ websites, for one of my clients, on one server, with weekly
data drops, pgp'd credit card transactions, weekly data
submission from 200 locations, etc. etc.... (Yikes. I could be
rich... er... uhm....nevermind. :-)  )

> PHP 4 session management makes it really easy to create and use
> session variables, so data filtering information (for example:
> selecting only unfinished tasks) persists until the user actually
> changes it ("select ALL tasks"), or until the browser is closed.

FWIW: We don't use this, because of performance reasons. We
wrote our own session handling. Runs faster, and smoother, at
the cost of more programming hours. (It's a YMMV thing,
depending on server load and cost per programming hour.)

Some tips on using PG/PHP/Apache in a high-dollar business environment:
1. Treat it like any other accounting program. Separate user
entered data from system recorded data. Separate tables.
2. Use transactions. This will save time, money, and headaches.
3. Tune and optimize your code for doing as much as possible in
one pg_exec() statement. Multiple statements should be avoided
whenever possible. (let the db engine do the work, not PHP).
4. Decide upon numeric precision ahead of time (how many
decimals?) and code for that. Always. Without fail.
5. pgdump as frequently as you desire, and copy it off. Not
because it goes down (I had a MySQL-centric programmer ask if
the db had gone down, and was confused for a bit...because pg
*hasn't* gone down.), but because you always want to be able to
isolate a database at given breakpoints.
6. Did I mention having a backup server? It doesn't matter how
good the app is, lightning will nuke the best of 'em.
7. If you're on linux, recompile/adjust your kernel for massive
shm/sem space. Otherwise, you'll run out of backends when apache
needs 'em. the pg_pconnect php function needs a backend for each
apache process. 50 apache processes? 50 postmaster backends.
(One way around this: set your MaxRequest counter low to "rotate
the pool")
8. Investigate AOLserver. Good things coming from that camp on
connection pooling. (which takes care of the above issue)
9. If you're porting old PHP code, get the latest CVS copy or
 >=4.0.6 which _should_ have the row grabbing option in pg
functions, which makes life much easier. It's barely documented,
but it works like mysql now.
10. A note about oid/currval/nextval: Ignore my PHP site
comments if you're using a recent version of pg.
11. If you haven't done accounting apps before, hire an
accountant/programmer. Pricey, until you learn the ropes, but
after that, the knowledge is invaluable. Double entry good.
12. Did I mention backups? Even inside of pg? Set up development
only tables, or a dev-only machine. Standard professional
development for monetary issues, but I figured I'd mention it.
13. Watch out for binary math. Many systems use it, so
compensate (and/or cast) accordingly.
14. Watch out for bad data. Never, ever, under any
circumstances, grant the web-server user too many rights ... or,
even worse, put an SQL statement in a GET. Filter, filter,
filter. Then filter again.
15. Force small data fields. (See above).
16. Timestamp every record. And never use "update", just insert
a reversing record. (See #11.)
17. Vaccum often. Daily.

HTH,
-Ronabop

--2D426F70|759328624|00101101010000100110111101110000
ron@opus1.com, 520-326-6109, http://www.opus1.com/ron/
The opinions expressed in this email are not necessarily those
of myself,
my employers, or any of the other little voices in my head.