On 03/05/2014 10:22 AM, Israel Brewster wrote:
I have a Postgresql 9.2.4 database containing real-time tracking data
for our aircraft for the past week (theoretically). It is populated by
two different processes: one that runs every few minutes, retrieving
data from a number of sources and storing it in the DB, and one that has
an "always on" connection to the DB streaming data into the database in
realtime (often several records per second). To keep the database size
manageable I have a cron job that runs every evening to delete all
records that are more than a week old, after archiving a subset of them
in permanent storage.
This morning my boss e-mailed me, complaining that only a couple of
aircraft were showing up in the list (SELECT distinct(tail) FROM data
being the command that populates the list). Upon looking at the data I
saw that it only went back to 4am this morning, rather than the week I
was expecting. My first thought was "Oh, I must have a typo in my
cleanup routine, such that it is deleting all records rather than only
those a week old, and it's just that no one has noticed until now". So I
looked at that, but changing the delete to a select appeared to produce
the proper results, in that no records were selected:
Well it would, if the records only go back to 4 AM this morning. In
other words if no records exist before 4 AM today, no records exist
before 7 days ago also or am I missing something?
DELETE FROM data WHERE pointtime<now() AT TIME ZONE 'UTC'-interval '7 days';
Then I noticed something even more odd. My database has an id column,
which is defined as a SERIAL. As we all know, a serial is a
monotonically increasing number that is not affected by deletes.
However, the oldest record in my database, from 4am this morning, had an
id of 1. Even though I KNOW there was data in the system yesterday. Even
if my DELETE command was wrong and deleted ALL records, that shouldn't
have reset the SERIAL column to 1! I also know that I have not been in
the database mucking around with the sequence value - to be completely
honest, I don't even know the exact command to reset it - I'd have to
google it if I wanted to.
A sequence is just a special table.
So what does SELECT * from the sequence show?
Also odd is that my cleanup script runs at 1am. I have records of there
being new data in the database up to 3:51am, but the oldest record
currently in the DB is from 4:45am (as specified by the default of now()
on the column). So I know records were added after my delete command
ran, but before this reset occurred.
I am not sure what you are calling the 'reset'?
Did something happen between 3:51 AM and 4:45 AM?
Also not sure why you call the 4:45 AM record the oldest, when you say
you can identify records from 3:51 AM?
So my question is, aside from someone going in and mucking about in the
wee hours of the morning, what could possibly cause this behavior? What
sort of event could cause all data to be deleted from the table, and the
sequence to be reset? Especially while there is an active connection?
Thanks for any ideas, however wild or off the wall :-)
What is in the Postgres/system logs for the time period(s) you mention?
-----------------------------------------------
Israel Brewster
Computer Support Technician II
Era Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x7293
-----------------------------------------------
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
