Re: Strange phenomenon

Hi,

I am a Java developer, using PostgreSQL as a database.
Recently I observed a strange phenomenon:
1) The database has some tables where I store my application data.
I have an additional table, with no relation to any other table, just
for logging purposes. In the database, a sequence is defined too.
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
3) On day two, three, four and five, similar actions are taken
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. Except for the sequence, it looks like the database was reverted
to the state of day one.
Does this sound absurd or is this in any way possible? My application
does not contain any delete statements, and because of the reporting, I
know that the data was once in the database.
Can anyone explain this phenomenon?

Thanks

On 15 October 2010 09:36, Martial Braem <Martial(dot)Braem(at)abboss(dot)be> wrote:

> Hi,
>
>
>
> I am a Java developer, using PostgreSQL as a database.
>
> Recently I observed a strange phenomenon:
>
> 1) The database has some tables where I store my application data. I
> have an additional table, with no relation to any other table, just for
> logging purposes. In the database, a sequence is defined too.
>
> 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
>
> 3) On day two, three, four and five, similar actions are taken
>
> 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. Except
> for the sequence, it looks like the database was reverted to the state of
> day one.
>
> Does this sound absurd or is this in any way possible? My application does
> not contain any delete statements, and because of the reporting, I know that
> the data was once in the database.
>
> Can anyone explain this phenomenon?
>
> * *
>
> *Thanks*
>

What do you have in logs, any drops, deletes or truncates?

regards
Szymon

"Martial Braem" <Martial(dot)Braem(at)abboss(dot)be> writes:
> Recently I observed a strange phenomenon:
> 1) The database has some tables where I store my application data.
> I have an additional table, with no relation to any other table, just
> for logging purposes. In the database, a sequence is defined too.
> 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
> 3) On day two, three, four and five, similar actions are taken
> 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. Except for the sequence, it looks like the database was reverted
> to the state of day one.
> Does this sound absurd or is this in any way possible?

I'd look around for a cron job or some other periodic task that thinks
it's supposed to reload the database or something like that. Postgres
doesn't forget stuff that easily ... unless it's told to.

regards, tom lane

On Fri, Oct 15, 2010 at 12:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I'd look around for a cron job or some other periodic task that thinks
> it's supposed to reload the database or something like that.  Postgres
> doesn't forget stuff that easily ... unless it's told to.

Had a search engine eat an entire database one night by clicking on
the all the "delete" links.

On Friday 15 October 2010, Scott Marlowe elucidated thus:
> On Fri, Oct 15, 2010 at 12:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I'd look around for a cron job or some other periodic task that
> > thinks it's supposed to reload the database or something like that.
> >  Postgres doesn't forget stuff that easily ... unless it's told to.
>
> Had a search engine eat an entire database one night by clicking on
> the all the "delete" links.

And that was the night you learned about this part of RFC 2616 (HTTP
1.1), right? :)

"In particular, the convention has been established that the GET and
HEAD methods SHOULD NOT have the significance of taking an action other
than retrieval...Methods can also have the property of "idempotence" in
that (aside from error or expiration issues) the side-effects of N > 0
identical requests is the same as for a single request. The methods
GET, HEAD, PUT and DELETE share this property."

:)

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com - Fairbanks, AK
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A

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