Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Hacking PostgreSQL to Gain SQL Parsing Superpowers
Search
Lukas Fittl
April 22, 2016
Programming
1
430
Hacking PostgreSQL to Gain SQL Parsing Superpowers
Talk at Microsoft Reactor / Open Source Show And Tell
https://github.com/lfittl/pg_query
Lukas Fittl
April 22, 2016
Tweet
Share
More Decks by Lukas Fittl
See All by Lukas Fittl
How to Scale Postgres - Automation, Tuning & Sharding
lfittl
0
500
What's Missing for Postgres Monitoring
lfittl
0
170
A Map For Monitoring PostgreSQL
lfittl
2
340
Monitoring Postgres at Scale
lfittl
1
330
Monitoring PostgreSQL at Scale
lfittl
4
220
Postgres Performance for App Developers
lfittl
2
230
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
510
PostgreSQL at a Web Startup
lfittl
3
550
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
710
Other Decks in Programming
See All in Programming
2 週間で Twitter Bot を作ってみた
contour_gara
0
820
初心者のためのRubyKaigi入門/RubyKaigi Introduction
a_matsuda
10
1.8k
Apache Hive 4 on Treasure Data
ryukobayashi
1
460
新宿ダンジョンを可視化してみた
satoshi7190
3
420
Git Rebase
bkuhlmann
11
1.6k
仕様と実装で学ぶOpenTelemetry
drumato
2
150
Effectで作る堅牢でスケーラブルなAPIゲートウェイ / Robust and Scalable API Gateway Built on Effect
yasaichi
5
770
『Railsオワコン』と言われる時代に、なぜブルーモ証券はRailsを選ぶのか
free_world21
2
410
Balkan Ruby 2024 — How and why to run SQLite on Rails in production
fractaledmind
0
110
AppRouter Panel Talk
yosuke_furukawa
PRO
1
510
AmperとFleetを使ったAndroidアプリ
yoppie
0
290
The Cutting Edge Of Versioning (LambdaConf 2024)
chriskrycho
0
230
Featured
See All Featured
Designing for humans not robots
tammielis
247
25k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
275
13k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
34
6.1k
The Invisible Side of Design
smashingmag
294
49k
Clear Off the Table
cherdarchuk
85
310k
How To Stay Up To Date on Web Technology
chriscoyier
782
250k
Building Effective Engineering Teams - LeadDev
addyosmani
32
1.9k
The Language of Interfaces
destraynor
151
23k
5 minutes of I Can Smell Your CMS
philhawksworth
199
19k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
501
140k
10 Git Anti Patterns You Should be Aware of
lemiorhan
649
58k
Fontdeck: Realign not Redesign
paulrobertlloyd
76
4.9k
Transcript
@LukasFittl Hacking PostgreSQL to Gain SQL Parsing Superpowers @LukasFittl
@LukasFittl Parsing SQL
@LukasFittl SELECT * FROM x WHERE z = 1
@LukasFittl http://xkcd.com/208/
@LukasFittl PostgreSQL
@LukasFittl EXPLAIN (PARSETREE TRUE) SELECT * FROM x
WHERE y = 1 Unfortunately doesn’t exist.
@LukasFittl Parse Statement raw_parse(..) pg_catalog Rewrite Query Query Planner Execute
How PostgreSQL runs a Query:
@LukasFittl tree = raw_parser(query_str); str = nodeToString(tree); printf(str); ({SELECT :distinctClause
<> :intoClause <> :targetList ( {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 7} :location 7}) :fromClause ( {RANGEVAR :schemaname <> :relname x :inhOpt 2 :relpersistence p :alias <> :location 14}) :whereClause {AEXPR :name (“=") :lexpr {COLUMNREF :fields ("y") :location 22} :rexpr {PARAMREF :number 0 :location 26} :location 24} :groupClause <> :havingClause <> :windowClause <>
@LukasFittl Parse Statement raw_parse(..) pg_catalog Rewrite Query Query Planner Execute
@LukasFittl PgQuery._raw_parse( “SELECT * FROM x WHERE y = 1”)
({SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ({A_STAR}) :location 7} :location 7}) :fromClause ( {RANGEVAR :schemaname <> :relname x :inhOpt 2 :relpersistence p :alias <> :location 14}) :whereClause {AEXPR :name (“=") :lexpr {COLUMNREF :fields ("y") :location 22} :rexpr {PARAMREF :number 0 :location 26} :location 24} :groupClause <> :havingClause <> :windowClause <> :valuesLists <>
@LukasFittl PgQuery._raw_parse( “SELECT * FROM x WHERE y = 1”)
[{ "SelectStmt": { "targetList": [{ "ResTarget": { "val": { "ColumnRef": { "fields": [{ "A_Star": {} }], "location": 7 } }, "location": 7 } }], "fromClause": [{ "RangeVar": { "relname": "x", "inhOpt": 2, "relpersistence": "p", "location": 14 } }], "whereClause": { "A_Expr": {
@LukasFittl github.com/lfittl/pg_query
@LukasFittl De-Parsing SQL
@LukasFittl SELECT * FROM the_table SELECT * FROM a_better_table
@LukasFittl q = PgQuery.parse(‘SELECT * FROM the_table’) =>
[{"SelectStmt" => { "targetList" =>[…], "fromClause" => [{ "RangeVar" => { "relname" => “the_table", … } }] }}] q.tree[0][‘SelectStmt']['fromClause'][0][‘RangeVar'] ['relname'] = ‘the_other_table' q.deparse => "SELECT * FROM \"the_other_table\"
@LukasFittl Don’t like JSON?
@LukasFittl pg_query_go package main import ( "github.com/lfittl/pg_query_go"
) func main() { tree, err := pg_query.Parse("SELECT 1") if err != nil { panic(err); } … }
@LukasFittl pg_query.ParsetreeList{ Statements: []nodes.Node{
nodes.SelectStmt{ TargetList: []nodes.Node{ nodes.ResTarget{ Val: nodes.A_Const{ Type: "integer", Val: nodes.Value{ Type: nodes.T_Integer, Ival: 1, }, Location: 7, }, Location: 7, }, }, }, }, tree := pg_query.Parse(“SELECT 1")
@LukasFittl Other Languages C/C++: libpg_query Go: pg_query_go Node.js: pg-query-parser Not
yet: Python, Java, [?]
@LukasFittl Shameless Plug: github.com/citusdata/citus Open-source, distributed PostgreSQL
@LukasFittl Thank You! github.com/lfittl/pg_query