neues Feature: Range Data Type

Version 9.2 wird wahrscheinlich einen komplett neuen Datentyp mitbringen: Range Types.

Die Grundarbeit daran begann Jeff Davis ( Temporal PostgreSQL Roadmap « Experimental Thoughts ) schon 2010, und es gab da bereits Patches, die einen ‘Temporal’ - Typ bereitstellten. PostgreSQL 9.0 brachten einen weiteren wichtigen Baustein, und zwar
Exclusion Constraints. Wie bei PostgreSQL typisch wurde die gute Idee von Jeff, einen von ihm ‘temporal’ genannten Datentyp bereitzustellen verallgemeinert zu den Range Types, und damit von Datum- bzw. Zeitbereichen auf numerische (INT, FLOAT) Bereiche erweitert.

Wozu braucht man das?

Man stelle sich einen Shop vor, der N Artikel hat. Zu bestimmten Zeitbereichen gibt es unterschiedliche Preise (vermutlich werden Kreuzfahrtreisen demnächst billiger …), wer große Mengen eines Artikels kauft bekommt Rabatt. Dies will man nicht nur effektiv speichern, sondern auch schnell abfragen und möglichst auch noch sicherstellen, daß es keine Überschneidungen gibt. Man stelle sich vor, man bucht im Hotel die Suite und bei Anreise stellt man fest: diese wurde leider doppelt gebucht.

Genug der Worte, laßt uns die Doku lesen: PostgreSQL: Documentation: Manuals: Range Types und das mal praktisch testen!

Ich habe dazu mal was vorbereitet, und zwar diese Tabelle:

test=*# \d preisliste
      Table "public.preisliste"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 artikel | integer       |
 gueltig | daterange     |
 menge   | int4range     |
 preis   | numeric(10,3) |
Indexes:
    "preisliste_artikel_gueltig_menge_excl" EXCLUDE USING gist (artikel WITH =, gueltig WITH &&, menge WITH &&)

Anmerken muß ich, daß man dazu noch eine Extension erstellen muß:

create extension btree_gist;

Aber das ist seit 9.1 ja kein Problem mehr.

Also, mit dieser Tabelle und dem Exclude-Constraint möchte ich sicherstellen, daß es zu einem Artikel (hier nur eine ID) bei einer bestimmten Menge und zu einem bestimmten Kaufdatum nur maximal einen definierten Preis gibt. Ein Test:

test=# INSERT INTO preisliste (artikel, gueltig, menge, preis) VALUES (1, '[2012-01-01,2012-01-31)','[0,10)',10);
INSERT 0 1
test=*# INSERT INTO preisliste (artikel, gueltig, menge, preis) VALUES (1, '[2012-01-01,2012-01-31)','[5,15)',15);
ERROR:  conflicting key value violates exclusion constraint "preisliste_artikel_gueltig_menge_excl"
DETAIL:  Key (artikel, gueltig, menge)=(1, [2012-01-01,2012-01-31), [5,15)) conflicts with existing key (artikel, gueltig, menge)=(1, [2012-01-01,2012-01-31), [0,10)).

Super, denn ich kann nicht für ein und denselben Artikel und Datumsbereich sich überlappende Rabattmengen definieren. Ich lösche die Tabelle und fülle sie mit einigen Testdaten. Hier nutze ich ein weiteres cooles Feature: writeable Common Table Expressions, und einen seltenen Fall, wo ein CROSS JOIN sinnvoll ist:

WITH
  artikel AS (
    SELECT
      a
    FROM
      generate_series(1,500) a
  ),
  gueltig AS (
    SELECT
      daterange(
        ('2012-01-01'::date + s * '1 month'::interval)::date,
        ('2012-01-01'::date + (s + 1) * '1 month'::interval)::date
      ) AS gueltig
    FROM
      generate_series(0,11) s
  ),
  menge AS (
    SELECT
      int4range(0 + m * 10, 9 + m *10) AS m
    FROM
      generate_series(0,10)m
  )

INSERT INTO
  preisliste
SELECT
  artikel.a,
  gueltig.gueltig,
  menge.m,
  random()*1000
FROM
  artikel
  CROSS JOIN gueltig
  CROSS JOIN menge;

Das sind 66000 Datensätze, um mal ein paar anzuzeigen:

test=*# select * from preisliste limit 20 offset 4750;
 artikel |         gueltig         |   menge   |  preis
---------+-------------------------+-----------+---------
      36 | [2012-12-01,2013-01-01) | [90,99)   |  85.753
      36 | [2012-12-01,2013-01-01) | [100,109) |  89.641
      37 | [2012-01-01,2012-02-01) | [0,9)     | 776.763
      37 | [2012-01-01,2012-02-01) | [10,19)   | 324.941
      37 | [2012-01-01,2012-02-01) | [20,29)   | 363.077
      37 | [2012-01-01,2012-02-01) | [30,39)   | 255.377
      37 | [2012-01-01,2012-02-01) | [40,49)   |  66.375
      37 | [2012-01-01,2012-02-01) | [50,59)   | 972.578
      37 | [2012-01-01,2012-02-01) | [60,69)   | 396.493
      37 | [2012-01-01,2012-02-01) | [70,79)   | 123.560
      37 | [2012-01-01,2012-02-01) | [80,89)   | 180.430
      37 | [2012-01-01,2012-02-01) | [90,99)   |  94.849
      37 | [2012-01-01,2012-02-01) | [100,109) | 305.953
      37 | [2012-02-01,2012-03-01) | [0,9)     | 265.531
      37 | [2012-02-01,2012-03-01) | [10,19)   | 822.822
      37 | [2012-02-01,2012-03-01) | [20,29)   | 332.631
      37 | [2012-02-01,2012-03-01) | [30,39)   | 926.855
      37 | [2012-02-01,2012-03-01) | [40,49)   | 531.492
      37 | [2012-02-01,2012-03-01) | [50,59)   | 891.744
      37 | [2012-02-01,2012-03-01) | [60,69)   | 467.688
(20 rows)

Kurzer Test einer Abfrage:

test=*# SELECT * FROM preisliste WHERE artikel = 37 AND '2012-02-25'::date <@ gueltig AND 53 <@ menge;
 artikel |         gueltig         |  menge  |  preis
---------+-------------------------+---------+---------
      37 | [2012-02-01,2012-03-01) | [50,59) | 891.744
(1 row)

Und dazu ein Explain Analayse:

test=*# EXPLAIN ANALYSE SELECT * FROM preisliste WHERE artikel = 37 AND '2012-02-25'::date <@ gueltig AND 53 <@ menge;
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using preisliste_artikel_gueltig_menge_excl on preisliste  (cost=0.00..8.32 rows=1 width=74) (actual time=0.040..0.057 rows=1 loops=1)
   Index Cond: ((artikel = 37) AND ('2012-02-25'::date <@ gueltig) AND (53 <@ menge))
 Total runtime: 0.106 ms
(3 rows)

Fazit: wir haben Datentypen, um Bereiche von Werten definieren zu können, wir haben Constraints um sich überschneidende Bereiche verhindern zu können, wir haben eine Reihe von Operatoren auf solche Bereiche und wir haben effektive Indexe auf solchen Bereichen.


Ich bin echt mal wieder von PostgreSQL begeistert!

Und wer davon nicht begeistert ist, setze sich hin und entwerfe dasselbe ohne Range Types und ohne Exclusion Constraints!


Andreas