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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

mike g <mike (AT) thegodshalls (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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:


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Frank Kurzawa <fkurzawa (AT) topazsoftware (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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:

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Frank Kurzawa <fkurzawa (AT) topazsoftware (DOT) com> writes:
"idle in transaction" would be the usual state in such a case.

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

Tom, Frank,

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

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

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)