By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,081 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Time varying referential integrity

P: n/a
Hello.

I have time-varying relation variables similar to the ones described in
this Rick Snodgrass article:

http://www.informix.com.ua/articles/tempref/tempref.htm

An example:

CREATE TABLE departments (
department bigint primary key not null,
name text not null,
start_date timestamp not null default now(),
end_date timestamp
);

CREATE TABLE projects (
project bigint primary key not null,
department bigint not null,
name text not null,
start_date timestamp not null default now(),
end_date timestamp
);

I also have views which query only the active rows:

CREATE VIEW active_departments AS
SELECT *
FROM departments
WHERE end_date IS NULL;

The behavior, which I currently achieve laboriously through hand-written
triggers and partial indexes, is:

1) Insertion of an active project requires the existence of an active
department
2) Deactivation of an active department will cascade with a deactivation
of the associated active projects

where "active" means any tuple whose end_date is NULL

I'm thinking of modifying the backend to achieve these results
declaratively, rather than the manner I'm using now. For a small number
of relation variables, custom triggers aren't that bad. However, in the
hundreds it becomes a bit of a bear. In addition, I'd like the
deactivation of a tuple value in a temporal relation to optionally
result in its deletion, if the deactivation failed to cause the
cascading deactivation of other tuples in referencing non-temporal
relations.

If I did write up something to achieve this by modifying the backend,
would it have any chance of being accepted? Or, given the above design
and requirements, is there a way I could achieve the appropriate effects
declaratively that I've missed?

Mike Mascari
ma*****@mascari.com


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Mike Mascari wrote:
Hello.

I have time-varying relation variables similar to the ones described
in this Rick Snodgrass article:

http://www.informix.com.ua/articles/tempref/tempref.htm


I should also point out that apparently there is apparently a
SQL/Temporal section to SQL 3, but I only have old '93 BNF so I haven't
a clue as to what it says...

http://www.dbpd.com/vault/9810snod.html

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2

P: n/a
Mike Mascari wrote:
I should also point out that apparently there is apparently a
SQL/Temporal section to SQL 3, but I only have old '93 BNF so I haven't
a clue as to what it says...


FWIW, you can grab a copy of a late draft of SQL 2003 here:
http://www.wiscorp.com/SQLStandards.html

Joe

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

P: n/a
Joe Conway wrote:
Mike Mascari wrote:
I should also point out that apparently there is apparently a
SQL/Temporal section to SQL 3, but I only have old '93 BNF so I
haven't a clue as to what it says...

FWIW, you can grab a copy of a late draft of SQL 2003 here:
http://www.wiscorp.com/SQLStandards.html


Well, that's a great link and I grabbed the ZIP file for it, but it
appears to be missing SQL/Temporal. It appears to have:

01-Framework
02-Foundation
03-CLI
04-PSM
09-MED
10-OLB
11-Schemata
13-JRT

but not 07-Temporal. Is that right? From a cursory search on temporal
support, it appears that there should be a 07-SQL/Temporal group.

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #4

P: n/a
Mike Mascari wrote:
but not 07-Temporal. Is that right? From a cursory search on temporal
support, it appears that there should be a 07-SQL/Temporal group.


Your correct -- I didn't realize that it was its own spec. A little
googling found me this though:
http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5

P: n/a
Joe Conway wrote:
Mike Mascari wrote:
but not 07-Temporal. Is that right? From a cursory search on temporal
support, it appears that there should be a 07-SQL/Temporal group.

Your correct -- I didn't realize that it was its own spec. A little
googling found me this though:
http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF


Great. Thanks, Joe. It's kind of ironic that a SQL working draft is
archived as a Lotus Notes document. I'll have to have a nice
read-through though. The various SIGMOD Snodgrass articles and
TimeCenter publications lead me to believe that temporal support in SQL
database management systems could really make life simple. Hopefully an
incremental approach for PostgreSQL is possible, because my home-grown
temporal RI system is turning into a bit of a nightmare...

Thanks again,

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #6

P: n/a
Another thing to think about when using start and stop times
like this is an RTREE index over time.

Think of timestamps as points on a line. Intersects, contains
within all apply.

Obviously this does not necessarily solve the RI problem,
but you may find that this does what you need in a practical
sense.

--elein

On Sat, Dec 27, 2003 at 09:21:27PM -0500, Mike Mascari wrote:
Joe Conway wrote:
Mike Mascari wrote:
but not 07-Temporal. Is that right? From a cursory search on temporal
support, it appears that there should be a 07-SQL/Temporal group.

Your correct -- I didn't realize that it was its own spec. A little
googling found me this though:
http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF


Great. Thanks, Joe. It's kind of ironic that a SQL working draft is
archived as a Lotus Notes document. I'll have to have a nice
read-through though. The various SIGMOD Snodgrass articles and
TimeCenter publications lead me to believe that temporal support in SQL
database management systems could really make life simple. Hopefully an
incremental approach for PostgreSQL is possible, because my home-grown
temporal RI system is turning into a bit of a nightmare...

Thanks again,

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #7

P: n/a

On Dec 27, 2003, at 8:06 PM, Joe Conway wrote:
Mike Mascari wrote:
but not 07-Temporal. Is that right? From a cursory search on temporal
support, it appears that there should be a 07-SQL/Temporal group.


Your correct -- I didn't realize that it was its own spec. A little
googling found me this though:
http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/
5A5731749C30132388256A5B0044557A/$FILE/32N0651.PDF


This document is dated 2001-05-29. For some more recent information on
the Temporal-SQL proposal, you can check out Hugh Darwen & C.J. Date's
"Overview and Analysis of TSQL2" (draft dated 2003-09-3)
<http://www.hughdarwen.freeola.com/TheThirdManifesto.web/OnTSQL2.pdf>
Hugh Darwen, of IBM UK, has been an active contributor to SQL
standards, in particular regarding TSQL. According to this "Overview",

"[T]ime ran out toward the end of 2001, when─since no vendor had made
any such move, and the committee had therefore done no further work on
the project to develop Part 7 of the standard─ISO's own bylaws led to
that project being canceled altogether. At the time of writing,
therefore, the working draft document mentioned above ("Part 7:
SQL/Temporal") is in limbo."

Darwen & Date's "Overview" refers to a book they've written, together
with Nikos A. Lorentzos, called "Temporal Data and the Relational
Model" (ISBN 1-55860-855-9) proposing a method of working discrete
point intervals in general (including temporal data in particular).
Hugh Darwen's summary of the book is available at
<http://www.hughdarwen.freeola.com/Th...Manifesto.web/
TemporalData.pdf>

So right now, it appears the field is pretty open for temporal
implementations. It doesn't look like there's much movement on the ISO
front right now. It's potentially an opportunity for PostgreSQL to
implement something pretty cutting edge. I'm interested in this area,
but have no C coding skills. Any recommendations for getting started on
C? I've also thought there might be some crossover between
discrete-point-interval support and work done for GIS, but I haven't
looked at this very closely yet.

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.