> I am running an Apache 1.3.2 server with PHP 3.0.5 and PostgreSQL 6.3
> and am experiencing a weird "backing up/or stalling" problem. I am
> running a fairly lightly loaded, and fast, server. It seems that a
> query will cause other queries to back up behind it.
>
> Any ideas on tuning, or what potential problems are?
>
> PostgresQL doesn't function in a serial mode does it?
>
It sounds like you're using transactions (which is good).
So, with the behavior you are seeing, you must ask yourself what do I
want it to do. If your 1st query is an update, insert, or delete you
want to have others wait until it's finished. If your first query is
simply a select you want other select queries to happily process away
because there is no change in the data.
So, to answer your questions...
tuning - use transactions for logical units of work. And don't
forget those indexes to speed up queries.
potential problems - transactions that alter the same table will
be done in serial (hopefully, it could get messy otherwise).
serial mode - see above about potential problems. Selects should
happen in parallel unless you are explicitly locking the table yourself.
Now that was general information; this is slightly more specific to your
case. Don't start a transaction that isn't finished before the page
loads.
"Why?", you might ask.
"Because!", I might answer.
1st user1 loads page1 which starts a transaction which locks table1 and
select back a row for editing.
2nd user2 loads page1 which just spins waiting for user 1 to finish.
3rd user1 does his editing and submits his changes to page2 which
updates the row in the database and ends the transaction.
4th user2's load of page 1 finally finishes.
I think you get the idea (and that was just 2 users and 2 pages), so
don't do it.
Work around would be:
1st user1 loads page1 which selects back a row to edit (NOTE: no
transaction needed).
2nd user2 loads page1 which selects back the same row to edit.
3rd user1 submits his changes to page2 which reselects the row to see if
it's been changed since the editing started, if not the update the
database
4th user2 submits his changes to page2 which notifies user2 that the row
has changed since editing began then takes appropriate action.
Hope this helps,
-DEJ