On Wed, May 14, 2003 at 10:48:45AM -0400, Eric Sammer wrote:
> Not really a problem (yet), but out of curiousity...
>
> I'm using Apache::DBI with postgres and while not serving requests, all
> the postgres processes are listed as "idle in transaction." Obviously,
> the DBI connect statements are configured with AutoCommit => 0. It
> doesn't seem to cause any real problems, but it does concern me that
> postgres considers itself in a transaction with only a connect (i.e.
> prior to a $dbh->prepare()). I threw some SELECTs at it and it's fine,
> but I worry about transaction blocking under heavier load and with
> INSERT, DELETE, and UPDATE statements. ...or maybe I'm just thinking too
> Oracle-ish about it.
I noticed an interesting problem with regards to dates and times. If
you have columns that you let pgsql fill in, frex:
Column | Type | Modifiers
------------------+---------------------+-----------------------------------------------
order_date | date | default date('now'::text)
order_time | time with time zone | default ('now'::text)::time(6) with time zone
Those dates and times will tend to be off, because pgsql uses the
exact date/time at the _start_ of the transaction. And with Apache::DBI
the transaction could have been started quite some time ago, unless your
server is heavily loaded and/or you do lots of SELECT queries before
adding new rows (thus making sure you end up with a relatively fresh
transaction).
Just to illustrate this, I first get the current time on my server:
dev=> SELECT current_time;
timetz
--------------------
20:11:20.787832-04
Then I go to my mod_perl application and add a new account. The
resulting row has:
order_date | order_time
------------+--------------------
2003-06-01 | 20:10:46.349862-04
And order_time in this row is chronologically before, even though I
added the account _after_ typing 'SELECT current_time' in my psql
console...
The date can be off too, if Apache::DBI used an old, stale transaction
that started the previous day (this is very possible if a new record is
added alightly after midnight...)
