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
540
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
600
What's Missing for Postgres Monitoring
lfittl
0
210
A Map For Monitoring PostgreSQL
lfittl
2
370
Monitoring Postgres at Scale
lfittl
1
390
Monitoring PostgreSQL at Scale
lfittl
4
230
Postgres Performance for App Developers
lfittl
2
270
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
570
PostgreSQL at a Web Startup
lfittl
3
580
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
740
Other Decks in Programming
See All in Programming
Visual StudioのGitHub Copilotでいろいろやってみる
tomokusaba
1
200
お前もAI鬼にならないか?👹Bolt & Cursor & Supabase & Vercelで人間をやめるぞ、ジョジョー!👺
taishiyade
7
4.1k
Domain-Driven Transformation
hschwentner
2
1.9k
プログラミング言語学習のススメ / why-do-i-learn-programming-language
yashi8484
0
150
Djangoアプリケーション 運用のリアル 〜問題発生から可視化、最適化への道〜 #pyconshizu
kashewnuts
1
260
Ça bouge du côté des animations CSS !
goetter
2
130
Amazon Q Developer Proで効率化するAPI開発入門
seike460
PRO
0
120
dbt Pythonモデルで実現するSnowflake活用術
trsnium
0
240
1年目の私に伝えたい!テストコードを怖がらなくなるためのヒント/Tips for not being afraid of test code
push_gawa
1
490
昭和の職場からアジャイルの世界へ
kumagoro95
1
410
Boost Performance and Developer Productivity with Jakarta EE 11
ivargrimstad
0
640
技術を改善し続ける
gumioji
0
110
Featured
See All Featured
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
233
17k
The Invisible Side of Design
smashingmag
299
50k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
Building Your Own Lightsaber
phodgson
104
6.2k
Bootstrapping a Software Product
garrettdimon
PRO
306
110k
How STYLIGHT went responsive
nonsquared
98
5.4k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.1k
4 Signs Your Business is Dying
shpigford
182
22k
Code Reviewing Like a Champion
maltzj
521
39k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
Product Roadmaps are Hard
iamctodd
PRO
50
11k
Fashionably flexible responsive web design (full day workshop)
malarkey
406
66k
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