Introducing PostgreSQL SQL Parser- Use of PostgreSQL Parser in other Applications PGCon 2019, Ottawa
2019-05-31
SRA OSS, Inc. JapanBo Peng
pengbo@sraoss.co.jp
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

1

Who am I?
◼ Bo Peng◼ Chinese National, based in Tokyo◼ Organization: SRA OSS◼ Experience in using PostgreSQL since 2016
◼ Current work Open source software technical support• Monitoring software• Clustering software Construction work

◼ Pgpool-II developer Chinese documentation translation
 Committer since Pgpool-II 3.5
 Release management, bug fix, SQL parser
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

2

Outline◼ PostgreSQL Query Processing Parser Analyzer
 Rewriter Planner
 Executor◼ How to port the raw parser into an application?
 Give a concrete example
 Some use cases of raw parser

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

3

Part Ⅰ
PostgreSQL Query Processing

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

4

Query Processing• Parser

Client▪ Check the query string forvalid syntax

query string

Parser• Analyzer

raw parse tree

Analyzer▪ Add detailed info
▪ Database lookups

query tree
YES

• Rewriter▪ Apply rewrite rules• Planner

Utilitycommand?
NO

CREATE, DROP,
COPY, ALTER,VACUUM, etc.

▪ Choose the best plan

• Executor

Rewriterrewritten query tree

Planner
plan tree

Executor

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

5

Parser• Check the query syntax▪ input: query string▪ output: raw parse tree

• Source code: src/backend/parser• Parser is defined in scan.l and gram.y:▪ scan.l:• definition of lexer• recognizing identifiers, the SQL key words etc.
• built using flex

▪ gram.y:• definition of parser• built using bison• No lookups in the system catalogs
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

6

Example: a Simple Select Statement
SELECT * FROM tbl WHERE c1 > 1 AND c2 = 'foo';
simple_select:| SELECT distinct_clause target_list
into_clause from_clause where_clausegroup_clause having_clause window_clause{
SelectStmt *n = makeNode(SelectStmt);n->distinctClause = $2;
n->targetList = $3;
n->intoClause = $4;
n->fromClause = $5;
n->whereClause = $6;
n->groupClause = $7;
n->havingClause = $8;
n->windowClause = $9;$$ = (Node *)n;gram.ySelectStmt
targetList

RESTARGET

whereClause

fromClause

BOOLEXPR
boolop

RANGEVAR

andval
COLUMNREF

AEXPR
lexpr

relnameCOLUMNREF
A_STAR

SELECT col1,
col2
FROM
tbl
WHERE col1 > 1 AND
col2 = 'foo';/* create SELECT node *//* DISTINCT clause*//* TARGET list*//* INTO clause*//* FROM clause*//* WHERE clause*//* GROUP BY clause*//* HAVING clause*//* WINDOW clause*/tbl

fields
C1

AEXPR
rexpr
A_CONST

lexpr
COLUMNREF
fieldsval
1

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

C2

rexpr

A_CONSTvalfoo
7

Example: Raw Parse Tree
SELECT * FROM tbl WHERE c1 > 1 AND c2 = 'foo';
1. Apply patch
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.cindex 44a59e1..4e046f6 100644--- a/src/backend/tcop/postgres.c+++ b/src/backend/tcop/postgres.c@@ -1075,6 +1075,9 @@ exec_simple_query(const char *query_string)
Portalportal;DestReceiver *receiver;
int16
format;++if (Debug_print_parse)+
elog_node_display(LOG, "raw parse tree", parsetree,
Debug_pretty_print);

2. debug_print_parse = on
:stmt
{SELECT:distinctClause <>:intoClause <>:targetList ({RESTARGET:name <>
:indirection <>:val{COLUMNREF:fields ({A_STAR}):location 7}:location 7}):fromClause ({RANGEVAR:schemaname <>:relname tbl:inh true:relpersistence p:alias <>:location 14})

Raw Parse Tree

:whereClause{BOOLEXPR:boolop and:args ({AEXPR:name (">"):lexpr{COLUMNREF:fields ("c1"):location 24}:rexpr{A_CONST:val 1:location 29}:location 27}{AEXPR:name ("="):lexpr{COLUMNREF:fields ("c2"):location 35}:rexpr{A_CONST:val "foo":location 40}:location 38}):location 31}:groupClause <>:havingClause <>(snip)

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

8

Analyzer (1)◼ Analyzer
 input: raw parse tree output: query tree

◼ System catalog lookups
Add detailed info, like:• Table OID• Column name• Type, operator OID◼ Query Tree src/include/nodes/parsenodes.h

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

9

Raw Parse Tree vs. Query Tree
SELECT * FROM tbl WHERE c1 > 1 AND c2 = 'foo';raw parse tree
:targetList ({RESTARGET:name <>
:indirection <>:val{COLUMNREF:fields ({A_STAR}):location 7}:location 7}):fromClause ({RANGEVAR:schemaname <>:relname tbl:inh true:relpersistence p:alias <>:location 14})

query tree

:rtable ({RTE:alias <>:eref{ALIAS:aliasname tbl:colnames ("c1" "c2")}:rtekind 0:relid 16405
table OID
:relkind r:tablesample <>
*
...
}
)
...
Analyzer:targetList ({TARGETENTRY
:expr
{VAR
:varno 1:varattno 1
column type:vartype 23
FROM tbl...
}:resno 1
column name:resname c1:ressortgroupref 0:resorigtbl 16405:resorigcol 1:resjunk false}
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

10Rewriter◼ Rewriterinput: query tree
output: rewritten query tree

◼ Apply RULE to query tree
◼ Rewrite VIEW
Rewrite the client's query to the base table referenced
in the view

◼ Source codesrc/backend/rewrite

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

11Planner◼ Plannerinput: rewritten query tree
output: plan tree

◼ Planner is responsible for creating an optimal
execution plan
1. Create all possible plans
2. Estimate the cost for each plan
3. Choose the lowest cost plan and return it to Executor

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

12

Executor◼ Executor
input: plan tree
output: result◼ Executor executes queries
Step through the plan tree
Retrieve tuples in the way given by the plan tree

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

13

Part Ⅱ
How to Import Parser into an Application.

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

14

Why?
◼ Why import PostgreSQL raw parser for parsing SQL
queries in our own applications.
 Determining if it is a READ query or WRITE query• Accomplish load balancing in applications

 Extracting specific part from query• Extract table name or function name Rewriting or modifying parts of the query string

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

15

How to parse complex SQL queries?
with year_total as (select c_customer_id customer_id,c_first_name customer_first_name,c_last_name customer_last_name,c_preferred_cust_flag customer_preferred_cust_flag,c_birth_country customer_birth_country,c_login customer_login,c_email_address customer_email_address,d_year dyear,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) year_total,'s' sale_typefrom customer,store_sales,date_dim…
)
select…
from year_total t_s_firstyear,year_total t_s_secyear,year_total t_c_firstyear,year_total t_c_secyear,year_total t_w_firstyear,year_total t_w_secyear
where t_s_secyear.customer_id = t_s_firstyear.customer_id
and t_s_firstyear.customer_id = t_c_secyear.customer_id
and t_s_firstyear.customer_id = t_c_firstyear.customer_id
and t_s_firstyear.customer_id = t_w_firstyear.customer_id
and t_s_firstyear.customer_id = t_w_secyear.customer_id
and t_s_secyear.sale_type = 's'and t_c_secyear.sale_type = 'c'and t_s_firstyear.year_total > 0
and t_c_firstyear.year_total > 0
and t_w_firstyear.year_total > 0
and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end> case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else null end
and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / t_c_firstyear.year_total else null end> case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else null end
order by t_s_secyear.customer_id,t_s_secyear.customer_first_name,t_s_secyear.customer_last_name,t_s_secyear.customer_birth_country limit 100;

Using regular expression to parse complex SQL queries is painful.
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

16

How to Port the Raw Parser to an Application- Using Pgpool-II as a concrete example -Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

17

Why Pgpool-II needs to parse SQL queries?
◼ Pgpool-II is a database clustering software.
◼ It provides the following features:
 Load Balancing
 Connection Pooling Automatic failover Replication, etc.
◼ Pgpool-II has imported PostgreSQL parser
 To accurately parse the SQLs, using regular expressions to parsecomplex SQL queries is painful (Load balancing)• Determine if it is a READ query or WRITE query• Find specific function name from SQL query• Find specific patterns from SQL query To rewrite the query (Replication)• Rewrite Date/Time functions to timestamp constant for synchronizing databases
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

18

How to import raw parser into Pgpool-II?
1.

Import the following files to Pgpool-II to create a raw parse tree
src/parser/
src/backend/nodes/copyfuncs.c
src/backend/nodes/list.c
src/backend/nodes/makefuncs.c
src/backend/nodes/nodes.c
src/backend/nodes/value.c
src/backend/parser/gram.ysrc/backend/parser/parser.c
src/backend/parser/scansup.csrc/backend/parser/scan.c
src/backend/lib/stringinfo.c
src/common/keywords.c
src/backend/utils/mb/wchar.c

src/utils/

called by parser

src/backend/utils/mmgr/mcxt.c
src/backend/utils/mmgr/aset.c
src/backend/utils/error/elog.c
src/common/psprintf.c

2.

Process the raw parse tree in Pgpool-II (See examples)3.

Create the following files to convert a raw parse tree to a query string and send the query to
PostgreSQL (new in Pgpool-II)
outfuncs.c
pool_string.c

Generate libsql-parser.a•

Use libsql-parser.a to explain the use case of processing raw parse tree in Pgpool-II.

•

When compiling Pgpool-II source code, a static library libsql-parser.a is generated.

•

Create a main program query_parse.c and link it to the static library.
gcc -o query_parse query_parse.c -L. -lsql-parser
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

19

USE CASE (1) : Find a READ or WRITE query

query

raw parsetree
raw_parser(..)

main(..)# runparser.c
int main(int argc, char **argv){
POOL_DEST
dest;
parsetree_list = raw_parser(query, &error);node = raw_parser2(parsetree_list);/* READ or WRITE query */dest = send_to_where(node, query);if (dest == POOL_EITHER)
printf("query: ¥"%s¥" is a READ query¥n", query);else (dest == POOL_PRIMARY)
printf("query: ¥"%s¥" is a WRITE query¥n", query);

send_to_where(..)

POOL_DEST send_to_where(Node *node, char *query){/* SELECT INTO or SELECT FOR SHARE or UPDATE ? */if (IsA(node, SelectStmt)){...
}/* COPY */else if (IsA(node, CopyStmt)){...
}/* Transaction commands */else if (IsA(node, TransactionStmt)){...
}/* SET */else if (IsA(node, VariableSetStmt)){...
}

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

20

USE CASE (1) : Find a READ or WRITE query – SELECT
raw parsetree
raw_parser(..)

pool_has_insertinto_or_locking_clause(..
)

send_to_where(..)

raw_expression_tree_walker(..,insertinto_or_locking_clause_walker, ..)POOL_DEST send_to_where(Node *node, char *query){/* From 9.5 include/nodes/node.h ("TAGS FOR STATEMENT NODES"part) */
static NodeTag nodemap[] = {T_InsertStmt,
T_UpdateStmt,
T_SelectStmt,
. . .
};

insertinto_or_locking_clause_walker(..)/* Walker function to find intoClause or lockingClause */static boolinsertinto_or_locking_clause_walker(..){

SelectContext ctx;

if (bsearch(&nodeTag(node), nodemap,
sizeof(nodemap) / sizeof(nodemap[0]),sizeof(NodeTag), compare) != NULL){
if (IsA(node, SelectStmt)){/* SELECT INTO or SELECT FOR SHARE or UPDATE ? */if (pool_has_insertinto_or_locking_clause(node))
return POOL_PRIMARY;

if (IsA(node, IntoClause) ||IsA(node, LockingClause)){
ctx.has_insertinto_or_locking_clause = true;
return false;}
return raw_expression_tree_walker(node,

insertinto_or_locking_clause_walker,

return POOL_EITHER;}&ctx);}

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

21

USE CASE (1) : Find a READ or WRITE query - SELECT

Example:⚫ Check if "FOR UPDATE" appears in the SELECT query.
⚫ Consider "SELECT … FOR UPDATE" as a WRITE query and send it to primary node.
$ ./query_parse "SELECT * FROM tbl FOR UPDATE"
query: " SELECT * FROM tbl FOR UPDATE " is a WRITE query⚫ Consider "SELECT … " without " FOR UPDATE " query as a READ query and send it to the loadbalance node.
$ ./query_parse "SELECT * FROM tbl"
query: " SELECT * FROM tbl " is a READ query

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

22

USE CASE (1) : Find a READ or WRITE query - COPY
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }TO { 'filename' | STDOUT }[ [ WITH ] ( option [, ...] ) ]raw parsetree
raw_parser(..)

send_to_where(..)

POOL_DEST send_to_where(Node *node, char *query){(snip)/* COPY */if (IsA(node, CopyStmt)){
if (((CopyStmt *) node)->is_from)return POOL_PRIMARY;else{
if (((CopyStmt *) node)->query == NULL)
return POOL_EITHER;
else
return (IsA(((CopyStmt*) node)->query,

SelectStmt)) ?
POOL_EITHER : POOL_PRIMARY;}}(snip)⚫ Check if a COPY query is a READ query or a WRITE query.
⚫ If a COPY query copies the results of a WRITE query, then it is a WRITE query.
$ ./query_parse "COPY (UPDATE tbl SET i = i + 1 RETURNING *) TO STDOUT;"
query: "COPY (UPDATE test SET i=i+1 RETURNING *) TO STDOUT;" is a WRITE query⚫ If a COPY query copies the results of a SELECT query , then it is a READ query$ ./query_parse "COPY (SELECT * FROM tbl) TO STDOUT;"
query: " COPY (SELECT * FROM test) TO STDOUT;" is a READ query

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

23

USE CASE (1) : Find a READ or WRITE query- SET
SET⚫
⚫

Send "SET … TO… " to both of primary node and load balance node
However, the following "SET TRANSACTION" should be sent to theprimary only.
▪
▪
▪
▪
▪
▪
▪

SET transaction_read_only TO off
SET transaction_isolation TO 'serializable'
SET default_transaction_isolation TO 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLESET SESSION CHARACTERISTICS AS TRANSACTION ISOLATIONLEVEL SERIALIZABLESET TRANSACTION READ WRITESET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

raw parsetree
raw_parser(..)

send_to_where(..)

POOL_DEST send_to_where(Node *node, char *query){(snip)/* COPY */if (IsA(node, VariableSetStmt) ){if (((VariableSetStmt *) node)->kind == VAR_SET_VALUE &&!strcmp(((VariableSetStmt *) node)->name,"transaction_read_only")){(snip)
switch (v->val.type){case T_String:if (!strcasecmp(v->val.val.str, "off") ||!strcasecmp(v->val.val.str, "f") ||!strcasecmp(v->val.val.str, "false"))ret = POOL_PRIMARY;(snip)}
else if (((VariableSetStmt *) node)->kind == VAR_SET_VALUE &&(!strcmp(((VariableSetStmt *) node)->name,"transaction_isolation") ||!strcmp(((VariableSetStmt *) node)->name,"default_transaction_isolation"))){switch (v->val.type){case T_String:if (!strcasecmp(v->val.val.str, "serializable"))return POOL_PRIMARY;(snip)

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

24

USE CASE (1) : Find a READ or WRITE query- SET

Example:# runparser.c
int main(int argc, char **argv){(snip)/* READ or WRITE query */dest = send_to_where(node, query);if (dest == POOL_PRIMARY)
printf("send ¥"%s¥" to primary node, query);else if (dest == POOL_BOTH)
printf("send ¥"%s¥" to primary node and load blance node, query);(snip)⚫ SET parameters$ ./query_parse "set client_encoding to 'utf8';"
send "SET client_encoding TO 'utf8'" to primary node and load balance node

⚫ SET transaction isolation level repeatable read$ ./query_parse "set transaction isolation level repeatable read;"send "SET TRANSACTION ISOLATION LEVEL repeatable read" to primary node and load balancenode⚫ SET transaction isolation level serializable$ ./query_parse "set transaction isolation level serializable;"send "SET TRANSACTION ISOLATION LEVEL serializable" to primary node
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

25

USE CASE (2) : Find functions
raw parsetreemain(..)

pool_has_function_call(..)

raw_parser(..)# runparser.c
int main(int argc, char **argv){
POOL_DEST
dest;

raw_expression_tree_walker(..,function_call_walker, ..)parsetree_list = raw_parser(query, &error);node = raw_parser2(parsetree_list);function_call_walker(..)/* Find functions */pool_has_function_call(node);/* Walker function to find intoClause or lockingClause */static boolfunction_call_walker(Node *node, void *context){
SelectContext *ctx = (SelectContext *) context;
if (IsA(node, FuncCall)){Call*fcall = (FuncCall *) node;char*fname;int
length = list_length(fcall->funcname);if (length > 0){
if (length == 1)/* no schema qualification? */fname = strVal(linitial(fcall->funcname));else/* with schema qualification */fname = strVal(lsecond(fcall->funcname));}printf("function call walker, function name: ¥"%s¥"", fname)(snip)}

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

26

USE CASE (2) : Find functions

Example:# test.sql
WITH customer_total_return AS(
SELECT
sr_customer_sk AS ctr_customer_sk ,sr_store_sk
AS ctr_store_sk ,Sum(sr_fee)
AS ctr_total_return
FROM
store_returns ,date_dim
WHERE
sr_returned_date_sk = d_date_skANDd_year =2000
GROUP BY sr_customer_sk ,sr_store_sk)
SELECTc_customer_id
FROM
customer_total_return ctr1 ,store ,
customer
WHERE
ctr1.ctr_total_return >(SELECT Avg(ctr_total_return)*1.2
FROM
customer_total_return ctr2
WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
ANDs_store_sk = ctr1.ctr_store_skANDs_state = 'TN'AND
ctr1.ctr_customer_sk = c_customer_sk
ORDER BY c_customer_id LIMIT 100;

⚫ find functions and print function names$ ./query_parse test.sql
function call walker, function name: avg
function call walker, function name: sum

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

27

USE CASE (3) : Rewrite Date/Time Functions

Why rewrite Date/Time Functions?
◼ Pgpool-II native replication mode
 Not rely on PostgreSQL streaming replication
 Pgpool-II replicates queries to synchronize backend nodes Local time difference of each node or query execution timedifference could cause inconsistency◼ Date/Time Functions
 CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME (precision) CURRENT_TIMESTAMP (precision)
 LOCALTIME LOCALTIMESTAMP LOCALTIME (precision)
 LOCALTIMESTAMP (precision)
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

28

USE CASE (3) : Rewrite Date/Time Functions
Rewrite Date/Time Functions
1.
2.

Convert Date/Time functions such as CURRENT_DATE, CURRENT_TIME,LOCALTIMESTAMP etc. to "'now'::text::timestamptz" format.
Rewrite "'now'::text::timestamptz" to timestamp constant.

Example:CURRENT_TIMESTAMP -> '2019-04-10 13:59:59.123456+09'::text::timestamptz
raw parsetree
rewrite_timestamp(..)

rewrite_timestamp_update()

raw_expression_tree_walker(..)

static boolrewrite_timestamp_walker(Node *node, void *context){

rewrite_timestamp_walker(..)

switch (nodeTag(node)){case T_SQLValueFunction:{
SQLValueFunction *svf = (SQLValueFunction *) node;}case T_TypeCast:(snip)
return raw_expression_tree_walker(node, rewrite_timestamp_walker,
context);}

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

29

USE CASE (3) : Rewrite Date/Time Functions

Example:◼ Check if a Date/Time function exists
 If a Date/Time function exists$ ./query_parse "INSERT INTO tbl VALUES(1, now(), 2, CURRENT_DATE);"
INSERT INTO "t3" VALUES (1,"pg_catalog"."timestamptz"('2019-04-22
23:48:20.0601+09'::text),2,'2019-04-22 23:48:20.0601+09'::text::date)

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

30

Summary◼ PostgreSQL query processing
ParserAnalyzer
RewriterPlannerExecuter◼ Use PostgreSQL parser in applications to achieve
functions, like:Load balancing
Retrieve specific part from query
Rewrite query◼ Parser processing libraryhttps://github.com/pengbo0328/query_parse
Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

31

Thank you!

Copyright © 2019 SRA OSS, Inc. Japan All rights reserved.

32