Re: Incremental backups, and backup history

I think somebody already suggested the following solution, which would
solve referential integrity problems:
- create a "history" table with the exact layout of the working table,
but without it's FK constraints, and optionally with an insertion date;
- create a trigger on the working table so that it inserts in the
history table the current state on update/delete/(maybe insert);
Now when you delete a row from the working table, you have all the
benefits of FKs, and the history table provides all the history info you
need.
Recovery would be possible only manually copying back from the history
table, with possible FK hassles, but the data would be there.

Cheers,
Csaba.

On Fri, 2003-06-20 at 18:28, Dennis Gearon wrote:
> that's a good point, ref integrity and 'deleted' items. I'll have to take a look at that as I make my next design. I'm surpirsed that I didn't think of it. But I probably would have experienced it soon, as I am getting ready to put data in the design I'm on now.
>
> One way I know that makes it all easier, is to use surrogate integer keys on all tables, i.e. sequences, as the primary key.
>
> Nigel J. Andrews wrote:
>
> > On Thu, 19 Jun 2003, Matthew Nuzum wrote:
> >
> >
> >>Regarding backup history:
> >>
> >>I have an application designed for novices. Apparently it's easy to hit the
> >>"Delete" button, and then say yes to the "Are you sure you want to delete
> >>this?" question even when they don't want to. Therefore I simply mark a
> >>record as deleted. For example,
> >>UPDATE table SET deleted='t' WHERE something=true;
> >>
> >>Then my application logic pretends it doesn't really exist until two days
> >>later the user decides they want it back.
> >>
> >>It works very well for me.
> >>
> >
> >
> > But are you also taking care of the referential integrity issues, i.e. only
> > disallowing tuples with a deleted = true from being referenced to and ensuring
> > nothing references them at the time they are marked as deleted.
> >
> > It is a useful idea but as I know from a current project it requires
> > reimplementing foreign key functionality. In this case the middleware only uses
> > functions, one per statement, and nothing else, so I have been able to do much
> > of this in those functions but it's still a pain. I even wrote a utility to
> > take some of the leg work out of generating and maintaining quite a few
> > functions but if I'd had time [and thought about these basically being foreign
> > key constraints] I'd have looked at the existing foreign key code and seen if I
> > could copy and amend it or just amend it in place.
> >
> >
> > --
> > Nigel Andrews
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>