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
420
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
490
What's Missing for Postgres Monitoring
lfittl
0
160
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
220
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
MetricKitで予期せぬ終了を検知する話 / Detect unexpected termination with MetricKit
nekowen
1
180
はてなにおける CSS Modules、及び CSS Modules に足りないもの / CSS Modules in Hatena, and CSS Modules missing parts
mizdra
7
930
⼤規模⾔語モデルの拡張(RAG)が 終わったかも知れない件について
nearme_tech
23
15k
ゆるい個人開発のススメ
kuroppe1819
10
990
Goのエラースタックトレースの歴史と今後
sonatard
7
1.3k
1BRC--Nerd Sniping the Java Community
gunnarmorling
0
340
エンターテイメント業界で利用されるAWS
demuyan
0
210
try! Swift Tokyo 初参加報告LT
hinakko2
0
220
雑に思考を整理する技術と効能
konifar
59
29k
Scalable Customer Journey Orchestration (CJO)
lewuathe
0
270
DMMプラットフォームがTiDB Cloudを採用した背景
pospome
8
4.1k
CA.swift19 恋するAIアプリ開発の裏側
oskmr
0
360
Featured
See All Featured
Teambox: Starting and Learning
jrom
128
8.4k
Principles of Awesome APIs and How to Build Them.
keavy
121
16k
KATA
mclloyd
15
12k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
274
13k
Creatively Recalculating Your Daily Design Routine
revolveconf
210
11k
[RailsConf 2023] Rails as a piece of cake
palkan
23
3.9k
Atom: Resistance is Futile
akmur
259
25k
Why You Should Never Use an ORM
jnunemaker
PRO
51
8.6k
Navigating Team Friction
lara
178
13k
Fantastic passwords and where to find them - at NoRuKo
philnash
37
2.5k
Statistics for Hackers
jakevdp
789
220k
Web Components: a chance to create the future
zenorocha
305
41k
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