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
550
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
620
What's Missing for Postgres Monitoring
lfittl
0
220
A Map For Monitoring PostgreSQL
lfittl
2
370
Monitoring Postgres at Scale
lfittl
1
400
Monitoring PostgreSQL at Scale
lfittl
4
240
Postgres Performance for App Developers
lfittl
2
280
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
750
Other Decks in Programming
See All in Programming
SQL Server ベクトル検索
odashinsuke
0
150
DomainException と Result 型で作る型安全なエラーハンドリング
karszawa
0
860
AI Agents with JavaScript
slobodan
0
200
私の愛したLaravel 〜レールを超えたその先へ〜
kentaroutakeda
12
3.7k
ベクトル検索システムの気持ち
monochromegane
30
9.6k
いまさら聞けない生成AI入門: 「生成AIを高速キャッチアップ」
soh9834
14
4.2k
パスキーのすべて / 20250324 iddance Lesson.5
kuralab
0
140
Vibe Codingをせずに Clineを使っている
watany
15
5k
データベースエンジニアの仕事を楽にする。PgAssistantの紹介
nnaka2992
9
4.4k
AWSで雰囲気でつくる! VRChatの写真変換ピタゴラスイッチ
anatofuz
0
120
custom_lintで始めるチームルール管理
akaboshinit
0
200
新卒から4年間、20年もののWebサービスと 向き合って学んだソフトウェア考古学
oguri
8
7.1k
Featured
See All Featured
Rails Girls Zürich Keynote
gr2m
94
13k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
4
500
Designing for Performance
lara
606
69k
Writing Fast Ruby
sferik
628
61k
The Invisible Side of Design
smashingmag
299
50k
Reflections from 52 weeks, 52 projects
jeffersonlam
349
20k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
GitHub's CSS Performance
jonrohan
1030
460k
Typedesign – Prime Four
hannesfritz
41
2.6k
Faster Mobile Websites
deanohume
306
31k
YesSQL, Process and Tooling at Scale
rocio
172
14k
Done Done
chrislema
183
16k
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