[postgis-users] Versioning

[Paul Ramsey]

On Wed, May 13, 2009 at 7:11 PM, Ben Madin <ben at remoteinformation.com.au> wrote:
> Thanks Paul,
>
> This certainly looks like a more efficient system for 'versioning'... are
> you suggesting the history table also has a copy of the 'current' record?

Yes, that's what I'm suggesting. You do all your work against
yourtable, but the triggers maintain a fully versioned copy in
yourtable_history

> Alternatively, is there likely to be much of a performance hit if I create a
> view unioning the 'current' table and the 'history' table to be able to
> achieve this for reporting purposes.

There's no disadvantage to having your _history table just as current
as your working table. (Well, I guess size.) I think Leo's approach
avoid that, but probably has the performance penalty associated with
putting things back together on the fly.

P

> cheers
>
> Ben
>
>
>
>
> On 14/05/2009, at 5:43 AM, Paul Ramsey wrote:
>
>> Then you should be able to get by with a pretty simple system.
>> For each table you want to retain history for, add a second table,
>> table_history that adds two columns (date_added, date_deleted).
>> The put some insert/update/delete triggers on your main tables.
>> - on insert of row, simultaneously insert a copy into the _history
>> table with an appropriate date_added
>> - on delete of row update the date_deleted field in the equivalent
>> _history table row
>> - on update of row, flag the current copy of the row as deleted, and
>> add a new copy of the row with the current date as the added date
>>
>> Now a snapshot of "historical state" as of date_query is as simple as
>> querying the history table for "date_added < date_query and
>> (date_deleted > date_query or date_deleted is null)"
>>
>> The thrifty among us will say "hey, I can just have one table with
>> date_added and date_deleted columns" and "current state is just all
>> records with "date_deleted is null". However, your table will grow
>> over time, and eventually things will get slower than you would like.
>> Having a "working table" that is just current also removes the need
>> for client applications to understand the "history concept", they can
>> just work on the table as normal and history is maintained for them
>> transparently.
>>
>> P.
>>
>>
>> 2009/5/13 Juan Pedro Pérez Alcántara <jp.alcantara at geo-st.com>:
>>>
>>> Sure, you right. I'm refering versioning in it's first meaning, that is,
>>> history tracking.
>>>
>>> Thanks,
>>>
>>> Juan Pedro Pérez Alcántara
>>>
>>> On Wed, 2009-05-13 at 12:33 -0700, Paul Ramsey wrote:
>>>>
>>>> Juan Pedro,
>>>>
>>>> What do you mean by "versioning"?
>>>>
>>>> History tracking? (Every state of the data is one version, but there
>>>> is only one branch of the data.)
>>>> Branching and merging? (People can take a copy of the data (a version)
>>>> and work on it, then merge it back into the main database.)
>>>>
>>>> Versioning is an overloaded word. Describe your actual use case.
>>>>
>>>> P.
>>>>
>>>> 2009/5/13 Juan Pedro Pérez Alcántara <jp.alcantara at geo-st.com>:
>>>>>
>>>>> Hello all,
>>>>>
>>>>> soon we will face a project which will need a really strong
>>>
>>> versioning
>>>>>
>>>>> mechanism, including geometry. I'm sure anybody here have already
>>>>> addressed this sort of problem. I wonder if you can drop a line or
>>>
>>> two
>>>>>
>>>>> about this issue, for I consider it hard to implement. Sure the
>>>>> solutions will vary wildly depending on the scenario, the data, the
>>>>> versioning schedule, etc., but I'll be glad to hear about your
>>>>> experiences in that field.
>>>>>
>>>>> Greetings,
>>>>>
>>>>> Juan Pedro Pérez Alcántara
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>
>>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> --
>
> Ben Madin
> REMOTE INFORMATION
>
> t : +61 8 9192 5455
> f : +61 8 9192 5535
> m : 0448 887 220
> Broome   WA   6725
>
> ben at remoteinformation.com.au
>
>
>
>                                                        Out here, it pays to
> know...
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>