Re: temporal support patch

On Sat, Aug 25, 2012 at 1:30 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> My internals knowledge is basically zero but it would seem that If you
> simply wanted the end-of-transaction result you could just record nothing
> during the transaction and then copy whatever values are present at commit
> to whatever logging mechanism you need.

"Whatever values are present and commit" could be a terabyte of data.
Or it could be a kilobyte of changed data within a terabyte database.
You'd need some way to identify which data actually needs to be
copied, since you surely don't want to copy the whole database. And
even if you can identify it, going back and visiting all those blocks
a second time will be expensive.

> Since these temporal/audit tables are intended to be maintained by the
> system if you do not ask the users to identify themselves but instead take
> the information directly from the environment, you never have to give a "I'm
> sorry Dave" response because Dave is never given the chance to submit a
> proposed value.

Well, the point is that I think many people have requirements that are
(1) different from each other and (2) more complicated than the
simplest case we can come up with. Some people will want to log the
application user (or some other piece of extra data); others won't.
Some people will want to record every change in a transaction; others
won't. Some people will want to log time stamps; others won't; others
still may want a "range" per row indicating the time that row version
lived. Some people will want to delete history before it fills up the
disk; others will want to keep it forever. Some people will want to
clean up history created by "accidental" changes; others will want to
make sure that the history is as tamper-proof as possible. That's
why, of everything that's been said on this topic, I mostly agree with
what Josh Berkus said upthread:

# If you want something in core which will be useful to a lot of our
# users, it needs to be simple and flexible. Not ornate with lots of
# dependancies. The first version of it should be as simple and minimalist
# as possible.
#
# Personally, I would prefer a tool which just made it simpler to build my
# own triggers, and made it automatic for the history table to track
# changes in the live table. I think anything we build which controls
# what goes into the history table, etc., will only narrow the user base.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company