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
610
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
機能が複雑化しても 頼りになる FactoryBotの話
tamikof
0
130
From the Wild into the Clouds - Laravel Meetup Talk
neverything
0
140
Jakarta EE meets AI
ivargrimstad
0
320
楽しく向き合う例外対応
okutsu
0
620
Boost Performance and Developer Productivity with Jakarta EE 11
ivargrimstad
0
790
コードを読んで理解するko build
bells17
1
110
Generating OpenAPI schema from serializers throughout the Rails stack - Kyobashi.rb #5
envek
1
380
Rubyで始める関数型ドメインモデリング
shogo_tksk
0
140
技術を改善し続ける
gumioji
0
120
データの整合性を保つ非同期処理アーキテクチャパターン / Async Architecture Patterns
mokuo
54
19k
Djangoアプリケーション 運用のリアル 〜問題発生から可視化、最適化への道〜 #pyconshizu
kashewnuts
1
260
仕様変更に耐えるための"今の"DRY原則を考える
mkmk884
9
3.2k
Featured
See All Featured
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
47
5.2k
The World Runs on Bad Software
bkeepers
PRO
67
11k
StorybookのUI Testing Handbookを読んだ
zakiyama
28
5.5k
Six Lessons from altMBA
skipperchong
27
3.6k
Rails Girls Zürich Keynote
gr2m
94
13k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
30
4.6k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
7
640
Raft: Consensus for Rubyists
vanstee
137
6.8k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
45
9.4k
The Invisible Side of Design
smashingmag
299
50k
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