Skip site navigation (1) Skip section navigation (2)

Finding overlapping records

From: Frank Sheiness <frank(at)dough(dot)net>
To: austinpug(at)postgresql(dot)org
Subject: Finding overlapping records
Date: 2009-12-10 08:30:14
Message-ID: 20091210083014.GA31938@forbidden.texas.rr.com (view raw or flat)
Thread:
Lists: austinpug
Hello,

I had a question that I meant to bring up during the meeting but forgot
until the end..

I have these tables representing DHCP leases:

CREATE TABLE ips (
        ip      serial  PRIMARY KEY,
        address inet    NOT NULL
);

CREATE TABLE leases (
        lease   serial          PRIMARY KEY,
	ip      integer         NOT NULL REFERENCES ips (ip) ON UPDATE CASCADE ON DELETE RESTRICT,
        mac     macaddr         NOT NULL,
        starts  timestamp       NOT NULL,
        ends    timestamp       NOT NULL,
        server  integer         NOT NULL,
        UNIQUE (ip, mac, starts, ends)
);

To optimize the number of records, I would like to make it so when I insert a
new lease, it checks to see if there's a relevant overlapping lease for that
mac/ip combination.  If so, I want to update the end time of the old record
with the end time of the new record instead of doing the insert.

Currently, I'm accomplishing this with a trigger that calls this function:

CREATE FUNCTION optimize_lease_fx() RETURNS trigger AS $optimize_lease_fx$
        DECLARE 
                id integer;
        BEGIN   
                SELECT lease INTO id FROM leases WHERE mac = NEW.mac and
                ip = NEW.ip and NEW.starts between starts AND ends AND
                NEW.ends >= ends;
                IF NOT FOUND THEN
                        RETURN NEW;
                ELSE
                        EXECUTE 'UPDATE leases SET ends = '
                                || quote_literal(NEW.ends)
                                || ' where lease = '
                                || quote_literal(id);
                        RETURN NULL;
                END IF;
        END
$optimize_lease_fx$ LANGUAGE plpgsql;

It works, but I'm wondering if there's a better way to do this.  I'll be
flushing data when it's older than say.. 3 months.  Also, keep in mind that
some pre-optimized data may look like this (below).  The new rows will
overlap with each other and/or existing rows.


 lease  |  ip   |        mac        |       starts        |        ends         | server 
--------+-------+-------------------+---------------------+---------------------+---------
 255646 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 04:21:02 | 2009-12-10 06:21:02 |  400001
 256948 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:13:38 | 2009-12-10 08:13:38 |  400001
 256951 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:13:42 | 2009-12-10 08:13:42 |  400001
 256954 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:13:50 | 2009-12-10 08:13:50 |  400001
 257073 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:17:34 | 2009-12-10 08:17:34 |  400001
 257077 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:17:38 | 2009-12-10 08:17:38 |  400001
 257084 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:17:47 | 2009-12-10 08:17:47 |  400001
 257157 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:19:33 | 2009-12-10 08:19:33 |  400001
 257158 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:19:37 | 2009-12-10 08:19:37 |  400001
 257168 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:19:44 | 2009-12-10 08:19:44 |  400001
 257212 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:20:31 | 2009-12-10 08:20:31 |  400001
 257215 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:20:34 | 2009-12-10 08:20:34 |  400001
 257217 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:20:43 | 2009-12-10 08:20:43 |  400001
 257230 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:20:59 | 2009-12-10 08:20:59 |  400001
 257234 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:21:02 | 2009-12-10 08:21:02 |  400001
 257236 | 22625 | 00:0d:36:e8:4b:d2 | 2009-12-10 06:21:03 | 2009-12-10 08:21:03 |  400001



Thanks,
Frank

Responses

austinpug by date

Next:From: Jon ErdmanDate: 2009-12-10 17:12:32
Subject: Re: Finding overlapping records
Previous:From: Mark LehmannDate: 2009-12-09 22:57:33
Subject: Re: AustinPUG meeting, Wednesday Dec. 9th

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group