Re: Strange phenomenon

On 10/15/2010 03:36 PM, Martial Braem wrote:

> 2) On day one, I store data in my application data tables and in the
> logging table (transactional data). At the end of the day, I extract the
> data from the database for daily reporting (the ultimate proof that the
> transactions are actually committed). No problem there.

Is this extraction done via pg_dump? psql? Via a separate java program?
Or could it be happening using the same connection used to insert the
data initially?

> 4) On day six, there’s no trace of any data from day two to day five,
> except that the sequence, used in the application has not been reset.

That's exactly what I'd expect to see if all the work for those days was
done within a single transaction, which was then rolled back after a
tcp/ip connection timeout caused the backend to disconnect or something
like that.

That cannot be the case if you retrieved the data via another
connection, since uncommitted data is never visible to other
transactions in PostgreSQL. If you don't know for sure that you
retrieved it using a different connection than you inserted it with,
though, then this is a possibility. Anything within the same jvm
instance could potentially be using the same connection, depending on
how your environment is set up.

--
Craig Ringer