Re: data dump help

On Mon, Jan 18, 2010 at 05:49:32PM -0600, Terry wrote:
> On Mon, Jan 18, 2010 at 5:07 PM, Terry <td3201(at)gmail(dot)com> wrote:
> > On Mon, Jan 18, 2010 at 4:48 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:
> >> On 1/18/2010 4:08 PM, Terry wrote:
> >>>
> >>> Hello,
> >>>
> >>> Sorry for the poor subject. ?Not sure how to describe what I need
> >>> here. ?I have an application that logs to a single table in pgsql.
> >>> In order for me to get into our log management, I need to dump it out
> >>> to a file on a periodic basis to get new logs. ?I am not sure how to
> >>> tackle this. ?I thought about doing a date calculation and just
> >>> grabbing the previous 6 hours of logs and writing that to a new log
> >>> file and setting up a rotation like that. ?Unfortunately, the log
> >>> management solution can't go into pgsql directly. ?Thoughts?
> >>>
> >>> Thanks!
> >>>
> >>
> >> How about a flag in the db, like: dumped.
> >>
> >> inside one transactions you'd be safe doing:
> >>
> >> begin
> >> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> >> select * from log where dumped = 0;
> >> -- app code to format/write/etc
> >> update log set dumped = 1 where dumped = 0;
> >> commit;
> >>
> >> Even if other transactions insert new records, you're existing transaction
> >> wont see them, and the update wont touch them.
> >>
> >> -Andy
> >>
> >
> > I like your thinking but I shouldn't add a new column to this
> > database. ?It's a 3rd party application.
> >
>
> Although. I really like your idea so I might create another table
> where I will log whether the data has been dumped or not. I just need
> to come up with a query to check this with the other table.

Isn't this just over-engineering? Why not let the database do
the work, and add the column with a default value of 0, so that
you don't have to modify whatever 3rd-party app dumps the data:

ALTER TABLE log ADD COLUMN dumped boolean DEFAULT FALSE

(I don't do much ALTER TABLE, so that syntax may be all foobar'ed)

- Bret