Thread: Committed updates don't seem to be committed.

We are using postgres as the back-end to our webapp which is built with
Jakarta Tomcat, Struts, and Hibernate.

Most of our database-related activity is very simple and so most of our
selects, updates, etc. are not explicitly wrapped in a transaction on
the webapp side.

We are finding that our updates are not appearing in the database. In
particular:

1. If I update from the web-app, then the results of those updates
appear in the webapp until it is restarted (i.e. the database connection
is terminated). After restart, it is as if the updates did not occur.the

2. If I query the database from another tool right after an update from
the webapp I cannot find these changes. The webapp sees these changes at
this point, presumably because it has it cached.

2. BUT, if I look in the postgres log, I can see that postgres is
handling the updates and appears to be committing them:
DEBUG: StartTransactionCommand
LOG: query: update calendar_abstract_task set parent=1145000001,
userID=241, completed='f', completed_date=null, subject=null,
start_date=null, description='A new task' where CTaskTID=1183000000
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand

3. I thought perhaps that due to some interaction with the webapp, that
when the webapp was stopped that I would find a log entry in which this
update is rolled back, but that is not the case.

4. And I know that the webapp is not simply hoarding these updates and
not passing them on since they are appearing in the postgres log.

So I am confronted with the conundrum that the postgres log seems to be
saying that the update has been successfully committed but the reality
is that it has not been committed.

BTW, our configuration has autocommit=ON.

Thanks in advance,

--
Frank Kurzawa

> 1. If I update from the web-app, then the results of those updates
> appear in the webapp until it is restarted (i.e. the database connection
> is terminated). After restart, it is as if the updates did not occur.the
>
I have worked with one application that stored current data in an array
which is what was displayed on the screen to the user.  Updates, deletes
etc would update the array, send a sql statement to the database, but
repaint the screen with the info stored in the array.  Every once in a
while something would go array (sorry for the bad pun) and what you saw
on the screen was not what you saw in the database until you closed the
application and reopened it.  Then it displayed what was in the database
usually to the dismay of the user and me.

The log below does show postgresql received an update sql statement.
Does CTaskTID of 1183... truly already exist in the database?  Is that
column a text or varchar field by chance that might have some extra
white spaces that are preventing it from finding a match?

Can't tell what language you are using to connect to the db but I
believe the docs have some detail on how one can have postgres return
the number of rows affected after issuing a sql statement.  Kind of
sounds like the app could use a few more error checking routines
perhaps.

If you post more details about how the transaction being executed you
might get more replies from others on the list.  Pretty tough to give a
definitive answer based on the info below.

Mike

> 2. If I query the database from another tool right after an update from
> the webapp I cannot find these changes. The webapp sees these changes at
> this point, presumably because it has it cached.
>
> 2. BUT, if I look in the postgres log, I can see that postgres is
> handling the updates and appears to be committing them:
> DEBUG: StartTransactionCommand
> LOG: query: update calendar_abstract_task set parent=1145000001,
> userID=241, completed='f', completed_date=null, subject=null,
> start_date=null, description='A new task' where CTaskTID=1183000000
> DEBUG: ProcessQuery
> DEBUG: CommitTransactionCommand
>
> 3. I thought perhaps that due to some interaction with the webapp, that
> when the webapp was stopped that I would find a log entry in which this
> update is rolled back, but that is not the case.
>
> 4. And I know that the webapp is not simply hoarding these updates and
> not passing them on since they are appearing in the postgres log.
>
> So I am confronted with the conundrum that the postgres log seems to be
> saying that the update has been successfully committed but the reality
> is that it has not been committed.
>
> BTW, our configuration has autocommit=ON.
>
> Thanks in advance,

mike g <mike@thegodshalls.com> writes:
> If you post more details about how the transaction being executed you
> might get more replies from others on the list.  Pretty tough to give a
> definitive answer based on the info below.

In particular, the quoted log segment only shows that this single
command executed successfully; it very possibly could have been part
of a larger transaction that later failed and was rolled back.

I think Frank is in error to suppose that his updates are "not wrapped
in a transaction".

            regards, tom lane

Thanks Mike,
I'll post more details when I'm back in the office tomorrow. I didn't
put too much detail in my initial question because often the long,
convoluted questions scare off people from answering them. <sorry>
In the meantime:

I'm using Hibernate, which is a java-based Object-Relational mapper
(http://www.hibernate.org). It's a great tool, and becoming very
popular in java-based web app developers.
Hibernate generates the sql queries based on the O/R mapping that I've
defined. It in turn uses the postgres JDBC driver to communicate those
queries to postgres. Hibernate has API's for session management and
transaction management. I currently open one hibernate session for the
duration of a single http request. The session loosely corresponds to a
database connection that is opened when the session opens and is
flushed and closed when the session closes when the http request has
been handled. Although hibernate may cache sequel commands until the
flush and close, I've verified that the flush and close are taking
place and that the commands are being executed by the database (e.g.
the update that I showed you in the log.

Although Hibernate also has a transaction API, we are not using it in
most cases because in most cases our database interactions are already
largely atomic in nature.

You ask some specific questions about this particular update, but let
me point out, that this is not a single isolated instance. It's
happening with most any INSERT or UPDATE, and yes, I have verified that
the commands really are legal in the context of the data in the
database. I.e. the INSERTs are failing as well, and the UPDATEs really
are relating to rows that are actually in the database.

More detail to come tomorrow.

Regards,

Frank

On Jun 22, 2004, at 10:06 PM, mike g wrote:

>
>> 1. If I update from the web-app, then the results of those updates
>> appear in the webapp until it is restarted (i.e. the database
>> connection
>> is terminated). After restart, it is as if the updates did not
>> occur.the
>>
> I have worked with one application that stored current data in an array
> which is what was displayed on the screen to the user.  Updates,
> deletes
> etc would update the array, send a sql statement to the database, but
> repaint the screen with the info stored in the array.  Every once in a
> while something would go array (sorry for the bad pun) and what you saw
> on the screen was not what you saw in the database until you closed the
> application and reopened it.  Then it displayed what was in the
> database
> usually to the dismay of the user and me.
>
> The log below does show postgresql received an update sql statement.
> Does CTaskTID of 1183... truly already exist in the database?  Is that
> column a text or varchar field by chance that might have some extra
> white spaces that are preventing it from finding a match?
>
> Can't tell what language you are using to connect to the db but I
> believe the docs have some detail on how one can have postgres return
> the number of rows affected after issuing a sql statement.  Kind of
> sounds like the app could use a few more error checking routines
> perhaps.
>
> If you post more details about how the transaction being executed you
> might get more replies from others on the list.  Pretty tough to give a
> definitive answer based on the info below.
>
> Mike

Frank Kurzawa <fkurzawa@topazsoftware.com> writes:
> I'm using Hibernate, which is a java-based Object-Relational mapper
> (http://www.hibernate.org). It's a great tool, and becoming very
> popular in java-based web app developers.

You should definitely not ignore the possibility that Hibernate is
broken (or to put it more politely, hasn't fully debugged its PostgreSQL
interface module).  I do not recall hearing from any satisfied users of
Hibernate-on-Postgres before ... in fact I never heard of Hibernate
before ... so call me clueless if you like, but a few rough edges in the
interface don't seem too implausible from here.

            regards, tom lane

Let's assume that there is a bug in hibernate that is causing it to
start a transaction behind my back. I'm trying to understand what
footprints it would leave and I see three possibilities:
a) It later commits the transaction behind my back
b) It later rolls back the transaction behind my back
c) It just leaves the transaction uncommitted/unrolledback/just
dangling out there.

for a): I don't think this could be happening or my data would actually
be in the database
for b): I don't think this could be happening or I would see evidence
of the rollback in the pgsql log

That leaves (c): What would this look like?
If I do a 'ps -efl' should I find a postgres process in a particular
state? ('idle waiting on transaction', 'idle in transaction', or
something else)?
Should there be some other footprints lying around that I can look at?
Some record of the pending transaction in some postgres system tables?

...so call me clueless if you like, because I AM clueless. LOL

Regards,

Frank

On Jun 23, 2004, at 12:20 AM, Tom Lane wrote:
> You should definitely not ignore the possibility that Hibernate is
> broken (or to put it more politely, hasn't fully debugged its
> PostgreSQL
> interface module).  I do not recall hearing from any satisfied users of
> Hibernate-on-Postgres before ... in fact I never heard of Hibernate
> before ... so call me clueless if you like, but a few rough edges in
> the
> interface don't seem too implausible from here.
>
>             regards, tom lane
>

Frank Kurzawa <fkurzawa@topazsoftware.com> writes:
> Let's assume that there is a bug in hibernate that is causing it to
> start a transaction behind my back. I'm trying to understand what
> footprints it would leave and I see three possibilities:
> a) It later commits the transaction behind my back
> b) It later rolls back the transaction behind my back
> c) It just leaves the transaction uncommitted/unrolledback/just
> dangling out there.
> ...
> That leaves (c): What would this look like?
> If I do a 'ps -efl' should I find a postgres process in a particular
> state? ('idle waiting on transaction', 'idle in transaction', or
> something else)?

"idle in transaction" would be the usual state in such a case.

> Should there be some other footprints lying around that I can look at?
> Some record of the pending transaction in some postgres system tables?

pg_locks will show an entry with (IIRC) an ExclusiveLock on a
transaction number for each open transaction.

You could also enable query logging and just look for begin/commit commands.

            regards, tom lane

Tom, Frank,

> I do not recall hearing from any satisfied users of
> Hibernate-on-Postgres before ... in fact I never heard of Hibernate
> before ... so call me clueless if you like, but a few rough edges in the
> interface don't seem too implausible from here.

Actually, I have a client using Hibernate, and they've not had a problem with
it.   I'm no java expert (I hire those), but I was favorably impressed with
the software --and with its level of compatibility with PostgreSQL.

Not so Jakarta.   Every client we've had using Jakarta has required help from
a pg-JDBC expert in debugging and configuration; apparently the way Jakarta
comes "out of the box" is barely usable with PG.  So this is a possible
source of problems.

Frank, from you description of your problems, I can see only 2 possibilities:

1) You are not connecting to the database you think you are.  Possibly you're
still connecting to "test"?  Or different parts of the system have their own
connection info?

2) Some part of your stack, due to configuration issues, is opening
transactions for each batch of DML statements and not closing them.   These
non-committed statements, of course, would be automatically rolled back on a
database restart or after connection termination.   This is a problem I've
seen on *lots* of Java applications.

--
Josh Berkus
Aglio Database Solutions
San Francisco

I just wanted to thank everyone for their help.
I believe that I've tracked down the problem and it basically boils down
to the fact that I was inadvertently engaging in Hibernate abuse. Since
this is a postgres forum, not a hibernate forum, I'll spare you the
details.

I do have one remaining comment/question. In the postgres logs, every
query is followed by a "CommitTransactionCommand", but this is not
really a commit. The only time a commit really takes place is when the
debug log includes something like "query: commit;". What exactly is the
meaning of "CommitTransactionCommand" if it doesn't represent a commit?

Thanks again,

Frank


On Wed, 2004-06-23 at 10:26, Tom Lane wrote:
> Frank Kurzawa <fkurzawa@topazsoftware.com> writes:
> > Let's assume that there is a bug in hibernate that is causing it to
> > start a transaction behind my back. I'm trying to understand what
> > footprints it would leave and I see three possibilities:
> > a) It later commits the transaction behind my back
> > b) It later rolls back the transaction behind my back
> > c) It just leaves the transaction uncommitted/unrolledback/just
> > dangling out there.
> > ...
> > That leaves (c): What would this look like?
> > If I do a 'ps -efl' should I find a postgres process in a particular
> > state? ('idle waiting on transaction', 'idle in transaction', or
> > something else)?
>
> "idle in transaction" would be the usual state in such a case.
>
> > Should there be some other footprints lying around that I can look at?
> > Some record of the pending transaction in some postgres system tables?
>
> pg_locks will show an entry with (IIRC) an ExclusiveLock on a
> transaction number for each open transaction.
>
> You could also enable query logging and just look for begin/commit commands.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Frank Kurzawa

Thanks Tom,

I'm certainly open to the idea that there is a larger transaction
wrapper that I am unaware of, but I will say this:
When I saw that the commands were posted without error to the pgsql
log, one of my first thoughts was that perhaps they were later rolled
back. So I carefully reviewed everything in the log after the lines
that I quoted and I can find nothing that even remotely resembles a
statement or statements that would be related to a rollback. Nor were
there any errors reported in the pgsql log.

I'm not at work now, but will post more specifics from my code tomorrow.

Moreover, my app is reasonably good at reporting any database-related
errors that do occur, and I had none reported.

I'm wondering if it is possibly some other database configuration
issue. I confess to a fair amount of ignorance in this area.

Regards,

Frank

On Jun 22, 2004, at 10:41 PM, Tom Lane wrote:
> In particular, the quoted log segment only shows that this single
> command executed successfully; it very possibly could have been part
> of a larger transaction that later failed and was rolled back.
>
> I think Frank is in error to suppose that his updates are "not wrapped
> in a transaction".
>
>             regards, tom lane
>