Re: Committed updates don't seem to be committed. - Mailing list pgsql-novice

> 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,