Download - Writeable CTEs: The Next Big Thing
![Page 2: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/2.jpg)
Current CTEsWITH [RECURSIVE] t1 [(column type,…)] AS( [SELECT | VALUES][UNION [ALL] [SELECT]),t2 AS…tn AS…SELECT…
![Page 3: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/3.jpg)
![Page 4: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/4.jpg)
Travelling Salesman ProblemGiven a number of cities and the costs of travelling from any city to any other city, what is the least-cost round-trip route that visits each city exactly once and then returns to the starting city?
![Page 5: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/5.jpg)
OBTW
With CTE and Windowing, SQL is Turing Complete.
![Page 6: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/6.jpg)
What Didn't the Old Syntax Do?
![Page 7: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/7.jpg)
WRITE!WITH [RECURSIVE] t1 [(column type,…)] AS( [SELECT | VALUES | (INSERT | UPDATE | DELETE) [RETURNING]][UNION [ALL] [SELECT | VALUES | (INSERT | UPDATE | DELETE) [RETURNING]])(SELECT | INSERT | UPDATE | DELETE) …
![Page 8: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/8.jpg)
For 8.5:Simple Partition Management
CREATE TABLE log ( ts TIMESTAMPTZ NOT NULL, msg TEXT);
![Page 9: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/9.jpg)
CREATE TABLE log_200901 () INHERITS(log);
ALTER TABLE log_200901 ADD CONSTRAINT right_month CHECK( ts >= '2009-01-01' AND ts < '2009-02-01');
For 8.5:Simple Partition Management
![Page 10: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/10.jpg)
For 8.5:Simple Partition Management
johto@postgres:54321=# WITHt1 AS (DELETE FROM ONLY log WHERE ts < '2009-02-01' RETURNING *),INSERT INTO log_200901 SELECT * FROM t1;INSERT 0 83240
![Page 11: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/11.jpg)
What you'll be able to do:WITH t AS ( DELETE FROM ONLY log WHERE ts >= '2009-01-01' AND ts < '2009-02-01' RETURNING *)INSERT INTO log_200901SELECT * FROM t;
QUERY PLAN --------------------------------------------------------- Insert (cost=27.40..27.52 rows=83240 width=40) -> CTE Scan on t (cost=27.40..27.52 rows=83240 width=40) CTE t -> Delete (cost=0.00..27.40 rows=83240 width=6) -> Seq Scan on log (cost=0.00..27.40 rows=83240 width=6) Filter: (..)(6 rows)
![Page 12: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/12.jpg)
What you can do now:Partition Management
johto@postgres:54321=# WITHt1 AS (DELETE FROM ONLY log WHERE ts < '2009-02-01' RETURNING *),t2 AS (INSERT INTO log_200901 SELECT * FROM t1)SELECT min(ts), max(ts), count(*) FROM t1; min │ max │ count ───────────────── | ──────────────── | ──── 2009-01-01 00:00:01.6416-08 │ 2009-01-30 23:58:38.6976-08 │ 83240(1 row)
![Page 13: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/13.jpg)
Query Clustering:I/O Minimization
CREATE TABLE person ( id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, CHECK (CASE WHEN first_name IS NULL THEN 0 ELSE 1 END + CASE WHEN last_name IS NULL THEN 0 ELSE 1 END >= 1) birthdate DATE NOT NULL, gender TEXT);
![Page 14: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/14.jpg)
Query Clustering:I/O Minimization
CREATE TABLE im ( id SERIAL PRIMARY KEY, provider TEXT NOT NULL, /* should be fk */ handle TEXT NOT NULL);
![Page 15: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/15.jpg)
Query Clustering:I/O Minimization
CREATE TABLE phone ( id SERIAL PRIMARY KEY, country_code TEXT NOT NULL, phone_number TEXT NOT NULL, extension TEXT);
![Page 16: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/16.jpg)
Query Clustering:I/O Minimization
CREATE TABLE street ( id SERIAL PRIMARY KEY, street1 TEXT NOT NULL, street2 TEXT, street3 TEXT, city TEXT NOT NULL, state TEXT, country TEXT NOT NULL, post_code TEXT);
![Page 17: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/17.jpg)
Query Clustering:I/O Minimization
CREATE TABLE person_im ( person_id INTEGER NOT NULL REFERENCES person (id), im_id INTEGER NOT NULL REFERENCES im (id), UNIQUE (person_id, im_id));
CREATE TABLE person_phone ( person_id INTEGER NOT NULL REFERENCES person (id), phone_id INTEGER NOT NULL REFERENCES phone (id), UNIQUE (person_id, phone_id));
CREATE TABLE person_street ( person_id INTEGER NOT NULL REFERENCES person (id), street_id INTEGER NOT NULL REFERENCES street (id), UNIQUE (person_id, street_id));
![Page 18: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/18.jpg)
Query Clustering:I/O Minimization
WITH t_person AS ( INSERT INTO person (first_name, last_name) VALUES ('David', 'Fetter') RETURNING id),
![Page 19: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/19.jpg)
Query Clustering:I/O Minimization
t_im AS ( INSERT INTO im (provider, handle) VALUES ('Yahoo!', 'dfetter'), ('AIM', 'dfetter666'), ('XMPP', '[email protected]') RETURNING id),t_person_im AS ( INSERT INTO person_im SELECT * FROM t_person CROSS JOIN t_im),
![Page 20: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/20.jpg)
Query Clustering:I/O Minimization
t_phone (phone_id) AS ( INSERT INTO phone (country_code, phone_number) VALUES ('+1','415 235 3778'), ('+1','510 893 6100') RETURNING id),t_person_phone AS ( INSERT INTO person_phone SELECT * FROM t_person CROSS JOIN t_phone),
![Page 21: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/21.jpg)
Query Clustering:I/O Minimization
t_street AS ( INSERT INTO street (street1, city, state, country, post_code) VALUES ('2500B Magnolia Street', 'Oakland','California','USA','94607-2410'), ('2166 Hayes Street Suite 200', 'San Francisco','California','USA','94117')),t_person_street AS ( INSERT INTO person_street SELECT * FROM t_person CROSS JOIN t_street)
![Page 22: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/22.jpg)
Query Clustering:I/O Minimization
VALUES(true);
![Page 23: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/23.jpg)
Query Clustering:Transaction Management
CREATE TABLE foo ( id SERIAL PRIMARY KEY, bar_id INTEGER NOT NULL);
CREATE TABLE bar ( id SERIAL PRIMARY KEY, foo_id INTEGER NOT NULL REFERENCES foo(id) ON DELETE CASCADE INITIALLY DEFERRED);
ALTER TABLE foo ADD FOREIGN KEY (bar_id) REFERENCES bar(id) ON DELETE CASCADE INITIALLY DEFERRED;
![Page 24: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/24.jpg)
Query Clustering:Transaction Management
WITH t AS ( INSERT INTO foo(id, bar_id) VALUES( DEFAULT, nextval(pg_get_serial_sequence('bar', 'id')) ) RETURNING id AS foo_id, bar_id)INSERT INTO bar(id,foo_id) SELECT bar_id, foo_id FROM t RETURNING *;
![Page 25: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/25.jpg)
How'd He Do That?!?
First try: David digs into the grammar and gets cut a few times.
![Page 26: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/26.jpg)
How'd He Do That?!?
First try: Marko reworks the planner. It needs to know when it creates a ModifyTable node. These used to have another name.
![Page 27: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/27.jpg)
How'd He Do That?!?
First try: Marko reworks the executor. It needs new nodes. Mmmm...nodes.
![Page 28: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/28.jpg)
How'd He Do That?!?
Marko reworks the executor, Part II:Copy & Paste. Now it's getting ugly...
![Page 29: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/29.jpg)
How'd He Do That?!?
Jaime Casanova, Tom Lane, and Robert Haas look at the reworked executor.
D'oh!
![Page 30: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/30.jpg)
How'd He Do That?!?
FAIL!Way too much code copying from toplevel to the new nodes.
![Page 31: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/31.jpg)
How'd He Do That?!?
Planner changes for ModifyTable node (a few)
![Page 32: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/32.jpg)
How'd He Do That?!?
Executor changes: ONE new node called ModifyTable
![Page 33: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/33.jpg)
How'd He Do That?!?
Johto restructures the whole code base for the ModifyTable node. "The usual stuff," (he said casually) for
new nodes.
![Page 34: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/34.jpg)
How'd He Do That?!?
WIN!
![Page 35: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/35.jpg)
Next Steps
INSERT, UPDATE and DELETE on the top level.RECURSIVEOptimization
![Page 36: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/36.jpg)
How'd He Do That?!?
Questions?Comments?
![Page 37: Writeable CTEs: The Next Big Thing](https://reader034.vdocuments.us/reader034/viewer/2022051209/5495b0a0b479596a4d8b4dba/html5/thumbnails/37.jpg)
Thank You!Copyright © 2009David Fetter [email protected] Rights Reserved
http://2009.pgday.eu/feedback