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
510
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
570
What's Missing for Postgres Monitoring
lfittl
0
190
A Map For Monitoring PostgreSQL
lfittl
2
360
Monitoring Postgres at Scale
lfittl
1
370
Monitoring PostgreSQL at Scale
lfittl
4
230
Postgres Performance for App Developers
lfittl
2
240
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
560
PostgreSQL at a Web Startup
lfittl
3
560
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
720
Other Decks in Programming
See All in Programming
型付き API リクエストを実現するいくつかの手法とその選択 / Typed API Request
euxn23
8
2.2k
Compose 1.7のTextFieldはPOBox Plusで日本語変換できない
tomoya0x00
0
190
cmp.Or に感動した
otakakot
3
200
subpath importsで始めるモック生活
10tera
0
310
Kaigi on Rails 2024 〜運営の裏側〜
krpk1900
1
230
TypeScriptでライブラリとの依存を限定的にする方法
tutinoko
3
690
C++でシェーダを書く
fadis
6
4.1k
Quine, Polyglot, 良いコード
qnighy
4
650
アジャイルを支えるテストアーキテクチャ設計/Test Architecting for Agile
goyoki
9
3.3k
AI時代におけるSRE、 あるいはエンジニアの生存戦略
pyama86
6
1.2k
Amazon Bedrock Agentsを用いてアプリ開発してみた!
har1101
0
340
3 Effective Rules for Using Signals in Angular
manfredsteyer
PRO
0
120
Featured
See All Featured
How To Stay Up To Date on Web Technology
chriscoyier
788
250k
Music & Morning Musume
bryan
46
6.2k
Product Roadmaps are Hard
iamctodd
PRO
49
11k
Building Flexible Design Systems
yeseniaperezcruz
327
38k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
364
24k
Unsuck your backbone
ammeep
668
57k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
16
2.1k
Keith and Marios Guide to Fast Websites
keithpitt
409
22k
Side Projects
sachag
452
42k
Writing Fast Ruby
sferik
627
61k
Designing for Performance
lara
604
68k
Why You Should Never Use an ORM
jnunemaker
PRO
54
9.1k
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