Re: [HACKERS] temporal support patch

2012-08-28 Thread Jim Nasby
On 8/27/12 12:40 PM, Robert Haas wrote: On Sat, Aug 25, 2012 at 1:30 PM, David Johnstonpol...@yahoo.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

Re: [HACKERS] temporal support patch

2012-08-27 Thread Robert Haas
On Sat, Aug 25, 2012 at 1:30 PM, David Johnston pol...@yahoo.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

Re: [HACKERS] temporal support patch

2012-08-27 Thread Pavel Stehule
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

Re: [HACKERS] temporal support patch

2012-08-27 Thread Robert Haas
On Mon, Aug 27, 2012 at 1:50 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I can't agree - why we need a some simple solution based on tools, that are available now? I don't think we have to be hurry in support own proprietary solutions - when isn't difficult do it just with available tools

Re: [HACKERS] temporal support patch

2012-08-25 Thread Robert Haas
On Mon, Aug 20, 2012 at 7:17 PM, David Johnston pol...@yahoo.com wrote: Ideally the decision of whether to do so could be a client decision. Not storing intra-transaction changes is easier than storing all changes. Not really. If you don't care about suppressing intra-transaction changes, you

Re: [HACKERS] temporal support patch

2012-08-25 Thread David Johnston
-Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Saturday, August 25, 2012 12:46 PM To: David Johnston Cc: Jeff Davis; Vlad Arkhipov; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] temporal support patch On Mon, Aug 20, 2012 at 7:17 PM, David Johnston

Re: [HACKERS] temporal support patch

2012-08-23 Thread Kevin Grittner
Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-08-21 at 17:07 -0500, Kevin Grittner wrote: The fact that it has an unknown sequence number or timestamp for purposes of ordering visibility of transactions doesn't mean you can't show that it completed in an audit log. In other words, I

Re: [HACKERS] temporal support patch

2012-08-22 Thread Jeff Davis
On Tue, 2012-08-21 at 17:07 -0500, Kevin Grittner wrote: The fact that it has an unknown sequence number or timestamp for purposes of ordering visibility of transactions doesn't mean you can't show that it completed in an audit log. In other words, I think the needs for a temporal database

Re: [HACKERS] temporal support patch

2012-08-21 Thread Vlad Arkhipov
On 08/21/2012 01:52 PM, Jeff Davis wrote: On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote: 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

Re: [HACKERS] temporal support patch

2012-08-21 Thread Anssi Kääriäinen
I have written one approach to audit tables, available from https://github.com/akaariai/pgsql_shadow_tables The approach is that every table is backed by a similar audit table + some meta information. The tables and triggers to update the audit tables are managed by plpgsql procedures.

Re: [HACKERS] temporal support patch

2012-08-21 Thread Kevin Grittner
Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-08-20 at 19:32 -0500, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: This is sounding like a completely runaway spec on what should be a simple feature. I hate to contribute to scope creep (or in this case scope screaming

Re: [HACKERS] temporal support patch

2012-08-21 Thread David Fetter
On Mon, Aug 20, 2012 at 09:33:45PM -0700, Jeff Davis wrote: On Mon, 2012-08-20 at 19:17 -0400, David Johnston wrote: Ideally the decision of whether to do so could be a client decision. Not storing intra-transaction changes is easier than storing all changes. At worse you could stage up

Re: [HACKERS] temporal support patch

2012-08-21 Thread Gavin Flower
On 22/08/12 02:16, Kevin Grittner wrote: Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-08-20 at 19:32 -0500, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: This is sounding like a completely runaway spec on what should be a simple feature. I hate to contribute to scope

Re: [HACKERS] temporal support patch

2012-08-21 Thread Alvaro Herrera
Excerpts from Gavin Flower's message of mar ago 21 16:51:57 -0400 2012: On 22/08/12 02:16, Kevin Grittner wrote: So, if you want to allow serializable temporal queries, the timing of a read-write serializable transaction can't be locked down until all overlapping read-write serializable

Re: [HACKERS] temporal support patch

2012-08-21 Thread Kevin Grittner
Gavin Flower gavinflo...@archidevsys.co.nz wrote: So if I understand correctly... If there is a very long running transaction, say 1 hour, then all (or just some? - depending) transactions that nominally start and finish within that time, can not have definitive start times until the very

Re: [HACKERS] temporal support patch

2012-08-21 Thread Kevin Grittner
Alvaro Herrera alvhe...@2ndquadrant.com wrote: I think there would need to be a way to also list transactions which are in progress -- this would include not only live transactions, but also all those transactions that have actually committed but are not yet listed as committed because their

Re: [HACKERS] temporal support patch

2012-08-20 Thread Robert Haas
On Sun, Aug 19, 2012 at 6:28 PM, Jeff Davis pg...@j-davis.com wrote: The other issue is how to handle multiple changes of the same record within the transaction. Should they be stored or not? In a typical audit log, I don't see any reason to. The internals of a transaction should be

Re: [HACKERS] temporal support patch

2012-08-20 Thread David Johnston
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Robert Haas Sent: Monday, August 20, 2012 5:04 PM To: Jeff Davis Cc: Vlad Arkhipov; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] temporal support patch

Re: [HACKERS] temporal support patch

2012-08-20 Thread Josh Berkus
On 8/20/12 4:17 PM, David Johnston wrote: The issue with adding the PostgreSQL role to the database in this way is that you now can never delete that role or reassign it to another entity. I guess with temporal you could do so and basically have the identity-role relationship define over

Re: [HACKERS] temporal support patch

2012-08-20 Thread Kevin Grittner
Josh Berkus j...@agliodbs.com wrote: This is sounding like a completely runaway spec on what should be a simple feature. I hate to contribute to scope creep (or in this case scope screaming down the tracks at full steam), but I've been watching this with a queasy feeling about interaction

Re: [HACKERS] temporal support patch

2012-08-20 Thread Jeff Davis
On Mon, 2012-08-20 at 17:04 -0400, Robert Haas wrote: On Sun, Aug 19, 2012 at 6:28 PM, Jeff Davis pg...@j-davis.com wrote: The other issue is how to handle multiple changes of the same record within the transaction. Should they be stored or not? In a typical audit log, I don't see any

Re: [HACKERS] temporal support patch

2012-08-20 Thread Jeff Davis
On Mon, 2012-08-20 at 19:17 -0400, David Johnston wrote: Ideally the decision of whether to do so could be a client decision. Not storing intra-transaction changes is easier than storing all changes. At worse you could stage up all changed then simply fail to store all intermediate results

Re: [HACKERS] temporal support patch

2012-08-20 Thread Jeff Davis
On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote: This is sounding like a completely runaway spec on what should be a simple feature. My feeling as well. However, we will eventually want to coalesce around some best practices and make it easy and robust for typical cases. Personally, I

Re: [HACKERS] temporal support patch

2012-08-20 Thread Jeff Davis
On Mon, 2012-08-20 at 19:32 -0500, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: This is sounding like a completely runaway spec on what should be a simple feature. I hate to contribute to scope creep (or in this case scope screaming down the tracks at full steam), but

Re: [HACKERS] temporal support patch

2012-08-20 Thread Craig Ringer
On 08/21/2012 12:52 PM, Jeff Davis wrote: On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote: This is sounding like a completely runaway spec on what should be a simple feature. My feeling as well. However, we will eventually want to coalesce around some best practices and make it easy and

Re: [HACKERS] temporal support patch

2012-08-20 Thread Craig Ringer
On 08/21/2012 12:52 PM, Jeff Davis wrote: On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote: This is sounding like a completely runaway spec on what should be a simple feature. My feeling as well. However, we will eventually want to coalesce around some best practices and make it easy and

Re: [HACKERS] temporal support patch

2012-08-19 Thread Jeff Davis
On Mon, 2012-06-25 at 17:46 +0900, Vlad Arkhipov wrote: It's not sufficient to store only a period of validity for a row. If two transactions started in the same time change the same record, you have a problem with TSTZRANGE type because it's normalized to empty interval. That's an

Re: [HACKERS] temporal support patch

2012-08-19 Thread Pavel Stehule
If there is some syntax that offers a convenient shorthand for WHERE, that's fine with me. Or using two tables, one called foo and one called foo_history, is also fine. But I don't want the DML syntax to introduce new mechanisms that aren't available without the fancy syntax (though new DDL

Re: [HACKERS] temporal support patch

2012-06-25 Thread Vlad Arkhipov
On 05/31/2012 11:52 AM, Jeff Davis wrote: On Wed, 2012-05-16 at 23:14 +0200, Miroslav Šimulčík wrote: Hi all, as a part of my master's thesis I have created temporal support patch for PostgreSQL. It enables the creation of special temporal tables with entries versioning. Modifying operations

Re: [HACKERS] temporal support patch

2012-06-18 Thread Vlad Arkhipov
On 06/15/2012 03:59 PM, Jeff Davis wrote: On Wed, 2012-06-13 at 23:10 +0200, Miroslav Šimulčík wrote: I have working patch for postgresql version 9.0.4, but it needs refactoring before i can submit it, because some parts don't meet formatting requirements yet. And yes, changes are large, so it

Re: [HACKERS] temporal support patch

2012-06-18 Thread Jeff Davis
On Mon, 2012-06-18 at 19:34 +0900, Vlad Arkhipov wrote: What's wrong with SPI/timetravel extension for system versioning? http://www.postgresql.org/docs/9.1/static/contrib-spi.html We are heavily using system-versioned and application-time period tables in our enterprise products (most of

Re: [HACKERS] temporal support patch

2012-06-15 Thread Jeff Davis
On Wed, 2012-06-13 at 23:10 +0200, Miroslav Šimulčík wrote: I have working patch for postgresql version 9.0.4, but it needs refactoring before i can submit it, because some parts don't meet formatting requirements yet. And yes, changes are large, so it will be better to discuss design first

Re: [HACKERS] temporal support patch

2012-06-13 Thread Miroslav Šimulčík
There would be no problem to make my solution compatible with SQL 2011, but the standard is not freely available. Can anybody provide me with this standard? 2012/5/20 Pavel Stehule pavel.steh...@gmail.com Hello 2012/5/18 Miroslav Šimulčík simulcik.m...@gmail.com: Hello. SQL 2011

Re: [HACKERS] temporal support patch

2012-06-13 Thread Miroslav Šimulčík
2012/5/30 Jim Nasby j...@nasby.net On 5/18/12 2:06 AM, Miroslav Šimulčík wrote: - no data redundancy - in my extension current versions of entries are stored only once in original table (in table_log - entries are inserted to both original and log table) That's not necessarily a

Re: [HACKERS] temporal support patch

2012-06-13 Thread Miroslav Šimulčík
* I'd very much like to see you make use of Range Types from 9.2; in particular, TSTZRANGE would be much better than holding two timestamps. If a standard requires you to display two timestamps in certain situations, perhaps you could use ranges internally and display the boundaries as

Re: [HACKERS] temporal support patch

2012-06-13 Thread Merlin Moncure
On Wed, Jun 13, 2012 at 4:10 PM, Miroslav Šimulčík simulcik.m...@gmail.com wrote: I have working patch for postgresql version 9.0.4, but it needs refactoring before i can submit it, because some parts don't meet formatting requirements yet. And yes, changes are large, so it will be better to

Re: [HACKERS] temporal support patch

2012-05-30 Thread Jeff Davis
On Wed, 2012-05-16 at 23:14 +0200, Miroslav Šimulčík wrote: Hi all, as a part of my master's thesis I have created temporal support patch for PostgreSQL. It enables the creation of special temporal tables with entries versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these

Re: [HACKERS] temporal support patch

2012-05-29 Thread Jim Nasby
On 5/18/12 2:06 AM, Miroslav Šimulčík wrote: - no data redundancy - in my extension current versions of entries are stored only once in original table (in table_log - entries are inserted to both original and log table) That's not necessarily a benefit... it makes querying for both history

Re: [HACKERS] temporal support patch

2012-05-20 Thread Pavel Stehule
Hello 2012/5/18 Miroslav Šimulčík simulcik.m...@gmail.com: Hello. SQL 2011 standard wasn't available in time I started this project so I built my implementation on older standards TSQL2 and SQL/Temporal, that were only available. None of these were accepted by ANSI/ISO commissions however.

Re: [HACKERS] temporal support patch

2012-05-18 Thread Miroslav Šimulčík
Hello. Basically my implementation serve for the same thing as table_log extension - entry versioning. It also uses history table and triggers to store old versions of entries. After quick review of table_log extension, here is comparison with my solution: tale_log advantages compared to my

Re: [HACKERS] temporal support patch

2012-05-18 Thread Albe Laurenz
Miroslav Šimulcík wrote: as a part of my master's thesis I have created temporal support patch for PostgreSQL. It enables the creation of special temporal tables with entries versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these tables don't cause permanent changes to

Re: [HACKERS] temporal support patch

2012-05-18 Thread Miroslav Šimulčík
Hello. SQL 2011 standard wasn't available in time I started this project so I built my implementation on older standards TSQL2 and SQL/Temporal, that were only available. None of these were accepted by ANSI/ISO commissions however. There is different syntax in SQL 2011 and it looks like one that

Re: [HACKERS] temporal support patch

2012-05-18 Thread Miroslav Šimulčík
Hi, yes you are right, storing old versions of entry to history table can be handled with triggers and my solution also uses triggers to do that. Advantage of my implementation is that user doesn't need to create all necessary objects (triggers, history table, begin/end columns ...) manually. He

Re: [HACKERS] temporal support patch

2012-05-18 Thread Jaime Casanova
On Wed, May 16, 2012 at 4:14 PM, Miroslav Šimulčík simulcik.m...@gmail.com wrote: This is only a illustration of main functionality. Later I can create a document about the design and implementation details, but first I need to know if such temporal features as described here, could be added

[HACKERS] temporal support patch

2012-05-16 Thread Miroslav Šimulčík
Hi all, as a part of my master's thesis I have created temporal support patch for PostgreSQL. It enables the creation of special temporal tables with entries versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these tables don't cause permanent changes to entries, but create new

Re: [HACKERS] temporal support patch

2012-05-16 Thread Pavel Stehule
Hello what is conformance of your solution with temporal extension in ANSI SQL 2011 http://www.slideshare.net/CraigBaumunk/temporal-extensions-tosql20112012010438 Regards Pavel Stehule 2012/5/16 Miroslav Šimulčík simulcik.m...@gmail.com: Hi all, as a part of my master's thesis I have