Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the process of implementing sql in PostgreSQL. I hope you will get something after reading this article. Let's discuss it together.

I. SQL execution process

The process of performing SQL by PG has the following steps:

The first step is to execute SQL Parse according to the input SQL statement, perform lexical and grammatical analysis, and finally generate a parsing tree

In the second step, according to the parsing tree, the query logic / physical optimization and query rewriting are performed, and finally the query tree is generated.

The third step is to generate the execution plan according to the query tree

In the fourth step, the executor executes the SQL according to the execution plan.

II. SQL parsing

As mentioned earlier, the SQL Parse (parsing) process of PG is implemented by the function pg_parse_query and called in the exec_simple_query function.

The code is as follows:

/ * Do raw parsing (only). * A list of parsetrees (RawStmt nodes) is returned, since there might be * multiple commands in the given string. * * NOTE: for interactive queries, it is important to keep this routine * separate from the analysis & rewrite stages. Analysis and rewriting * cannot be done in an aborted transaction, since they require access to * database tables. So, we rely on the raw parser to determine whether * we've seen a COMMIT or ABORT command; when we are in abort state, other * commands are not processed any further than the raw parse stage. * / List * pg_parse_query (const char * query_string) {List * raw_parsetree_list; TRACE_POSTGRESQL_QUERY_PARSE_START (query_string); if (log_parser_stats) ResetUsage (); raw_parsetree_list = raw_parser (query_string); if (log_parser_stats) ShowUsage ("PARSER STATISTICS") # ifdef COPY_PARSE_PLAN_TREES / * Optional debugging check: pass raw parsetrees through copyObject () * / {List * new_list = copyObject (raw_parsetree_list); / * This checks both copyObject () and the equal () routines... * / if (! equal (new_list, raw_parsetree_list)) elog (WARNING, "copyObject () failed to produce an equal raw parsetree"); else raw_parsetree_list = new_list;} # endif TRACE_POSTGRESQL_QUERY_PARSE_DONE (query_string); return raw_parsetree_list;} / * * raw_parser * Given a query in string form, do lexical and grammatical analysis. * * Returns a list of raw (un-analyzed) parse trees. The immediate elements * of the list are always RawStmt nodes. * / List * raw_parser (const char * str) {core_yyscan_t yyscanner; base_yy_extra_type yyextra; int yyresult; / * initialize the flex scanner * / yyscanner = scanner_init (str, & yyextra.core_yy_extra, ScanKeywords, NumScanKeywords); / * base_yylex () only needs this much initialization * / yyextra.have_lookahead = false / * initialize the bison parser * / parser_init (& yyextra); / * Parse! * / yyresult = base_yyparse (yyscanner); / * Clean up (release memory) * / scanner_finish (yyscanner); if (yyresult) / * error * / return NIL; return yyextra.parsetree;}

Important data structure: SelectStmt structure

/ *-* Select Statement * A "simple" SELECT is represented in the output of gram.y by a single * SelectStmt node; so is a VALUES construct. A query containing set * operators (UNION, INTERSECT, EXCEPT) is represented by a tree of SelectStmt * nodes, in which the leaf nodes are component SELECTs and the internal nodes * represent UNION, INTERSECT, or EXCEPT operators. Using the same node * type for both leaf and internal nodes allows gram.y to stick ORDER BY, * LIMIT, etc, clause values into a SELECT statement without worrying * whether it is a simple or compound SELECT. *-* / typedef enum SetOperation {SETOP_NONE = 0, SETOP_UNION, SETOP_INTERSECT, SETOP_EXCEPT} SetOperation; typedef struct SelectStmt {NodeTag type; / * * These fields are used only in "leaf" SelectStmts. * / List * distinctClause; / * NULL, list of DISTINCT ON exprs, or * lcons (NIL,NIL) for all (SELECT DISTINCT) * / IntoClause * intoClause; / * target for SELECT INTO * / List * targetList; / * the target list (of ResTarget) * / List * fromClause; / * the FROM clause * / Node * whereClause / * WHERE qualification * / List * groupClause; / * GROUP BY clauses * / Node * havingClause; / * HAVING conditional-expression * / List * windowClause; / * WINDOW window_name AS (...),... / * In a "leaf" node representing a VALUES list, the above fields are all * null, and instead this field is set. Note that the elements of the * sublists are just expressions, without ResTarget decoration. Also note * that a list element can be DEFAULT (represented as a SetToDefault * node), regardless of the context of the VALUES list. It's up to parse * analysis to reject that where not valid. * / List * valuesLists; / * untransformed list of expression lists * / / * These fields are used in both "leaf" SelectStmts and upper-level * SelectStmts. * / List * sortClause; / * sort clause (a list of SortBy's) * / Node * limitOffset; / * # of result tuples to skip * / Node * limitCount; / * # of result tuples to return * / List * lockingClause; / * FOR UPDATE (list of LockingClause's) * / WithClause * withClause / * WITH clause * / * * These fields are used only in upper-level SelectStmts. * / SetOperation op; / * type of set op * / bool all; / * ALL specified? * / struct SelectStmt * larg; / * left child * / struct SelectStmt * rarg; / * right child * / / * Eventually add fields for CORRESPONDING spec here * /} SelectStmt

Important structure: Value

/ *-* Value node * The same Value struct is used for five node types: T_Integer, * T_Float, T_String, T_BitString, T_Null. * * Integral values are actually represented by a machine integer, * but both floats and strings are represented as strings. * Using T_Float as the node type simply indicates that * the contents of the string look like a valid numeric literal. * * (Before Postgres 7.0,we used a double to represent T_Float, * but that creates loss-of-precision problems when the value is * ultimately destined to be converted to NUMERIC. Since Value nodes * are only used in the parsing process, not for runtime data, it's * better to use the more general representation.) * * Note that an integer-looking string will get lexed as T_Float if * the value is too large to fit in an 'int'. * * Nulls, of course, don't need the value part at all. *-* / typedef struct Value {NodeTag type; / * tag appropriately (eg. T_String) * / union ValUnion {int ival; / * machine integer * / char * str; / * string * /} val;} Value # define intVal (v) ((Value *) (v)-> val.ival) # define floatVal (v) atof (Value *) (v))-> val.str) # define strVal (v) (Value *) (v))-> val.str)

The implementation process in this section is shelved for the time being. The result returned by the function pg_parse_query is the linked list List, where the element is RawStmt. The specific structure needs to be determined according to NodeTag (this practice is similar to the polymorphism of Java/C++).

test data

Testdb=#-- Unit information testdb=# drop table if exists tweedwxwitues ('Y Co., Ltd., '1002 values' Haidian District, Beijing'); insert into t_dwxx (dwmc,dwbh,dwdz) values ('Z Co., Ltd., '1003 values' Wuxiang District, Nanning City, Guangxi); NOTICE: table "t_dwxx" does not exist, skippingDROP TABLEtestdb=# create table t_dwxx (dwmc varchar, dwbh varchar, dwdz varchar) CREATE TABLEtestdb=# testdb=# insert into t_dwxx (dwmc,dwbh,dwdz) values ('X Co., Ltd., '1001 1testdb=# insert into t_dwxx,' Liwan District, Guangzhou City, Guangdong Province'); INSERT 0 1testdb=# insert into t_dwxx (dwmc,dwbh,dwdz) values ('Y Co., Ltd., '1002 Jingling' Haidian District, Beijing'); INSERT 0 1testdb=# insert into t_dwxx (dwmc,dwbh,dwdz) values ('Z Co., Ltd., '1003 Jingling' Wuxiang District, Nanning City, Guangxi') INSERT 0 1testdb=#-- personal information testdb=# drop table if exists tweak grxxx personal information: table "t_grxx" does not exist, skippingDROP TABLEtestdb=# create table t_grxx (dwbh varchar (10), grbh varchar (10), xm varchar (20), nl int); CREATE TABLEinsert into t_grxx (dwbh,grbh,xm,nl) values ('1002, dwbh,grbh,xm,nl, 903, Wang Wu, 43) Testdb=# testdb=# insert into t_grxx (dwbh,grbh,xm,nl) values; INSERT 0 1testdb=# insert into t_grxx (dwbh,grbh,xm,nl) values; INSERT 0 1testdb=# insert into t_grxx (dwbh,grbh,xm,nl) values; INSERT 0 1testdb=#-personal payment information testdb=# drop table if exists t_jfxx NOTICE: table t_jfxx does not exist, skippingDROP TABLEtestdb=# create table t_jfxx (grbh varchar (10), ny varchar (10), je float); CREATE TABLEtestdb=# testdb=# insert into t_jfxx (grbh,ny,je) values (401.30); insert into t_jfxx (grbh,ny,je) values (401.30); insert into t_jfxx (grbh,ny,je) values (401.30) Insert into t_jfxx (grbh,ny,je) values; insert into t_jfxx (grbh,ny,je) values Insert into t_jfxx (grbh,ny,je) values (testdb=# insert into t_jfxx (grbh,ny,je) values); INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values (401.30); INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values (401.30) INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values; INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values; INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values; INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values (372.22) INSERT 0 1testdb=# insert into t_jfxx (grbh,ny,je) values (INSERT 0 1testdb=#-get pidtestdb=# select pg_backend_pid () Pg_backend_pid-1560 (1 row)-- query statement for testing: testdb=# select tweak dwxx.dwmc repertoire tpurgrxx.grbh.requngrxx.xmdfxx.nyPowertfoljfxx.jetestdbtel # from tfoldxx.dwxref = t_grxx.dwbh testdb-# and t_grxx.grbh = t_jfxx.grbhtestdb-# and t_dwxx.dwbh IN ('1001') '1002') testdb-# order by t_grxx.grbhtestdb-# limit 8 Dwmc | grbh | xm | ny | je-+-X Co., Ltd. | 901 | Zhang San | 201801 | 401.3 X Co., Ltd. | 901 | Zhang San | 201802 | 401.3 X Co., Ltd. | 901 | Zhang San | 201803 | 401.3 Y Co., Ltd. | 902 | Li Si | 201801 | 513 | . 1 Y Co., Ltd. | 902 | Li Si | 201802 | 513.3 Y Co., Ltd. | 513.3 | Li Si | 201804 | 513.3 Y Co., Ltd. | 372.22 (8 rows) | Wang Wu | 201801 | 372.22 Y Co., Ltd.

Result analysis

[xdb@localhost] $gdb-p 1560GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-100.el7Copyright (C) 2013 Free Software Foundation, Inc.... (gdb) b pg_parse_queryBreakpoint 1 at 0x84c6c9: file postgres.c, line 615. (gdb) cContinuing.Breakpoint 1, pg_parse_query (query_string=0x1a46ef0 "select tweedwxx.dwmc. T_jfxx.je\ nfrom t_dwxx inner join t_grxx on t_dwxx.dwbh = t_grxx.dwbh\ ninner join t_jfxx on t_grxx.grbh = t_jfxx.grbh\ nwhere t_dwxx.dwbh IN. At postgres.c:615615 if (log_parser_stats) (gdb) n618 raw_parsetree_list = raw_parser (query_string); (gdb) 620 if (log_parser_stats) (gdb) 638 return raw_parsetree_list (gdb) p * (RawStmt *) (raw_parsetree_list- > head.data- > ptr_value) $7 = {type = T_RawStmt, stmt = 0x1a48c00, stmt_location = 0 Stmt_len = 232} (gdb) p * ((RawStmt *) (raw_parsetree_list- > head.data- > ptr_value)-> stmt$8 = {type = T_SelectStmt} # converted to the actual type SelectStmt (gdb) p * (SelectStmt *) ((RawStmt *) (raw_parsetree_list- > head.data- > ptr_value))-> stmt$16 = {type = T_SelectStmt, distinctClause = 0x0, intoClause = 0x0, targetList = 0x1a47b18, fromClause = 0x1a48900, whereClause = 0x1a48b40, groupClause = 0x0, havingClause = 0x0, windowClause = 0x0, valuesLists = SortClause = 0x1afd858, limitOffset = 0x0, limitCount = 0x1afd888, lockingClause = 0x0, withClause = 0x0, op = SETOP_NONE, all = false, larg = 0x0, rarg = 0x0} # set the temporary variable (gdb) set $stmt= (SelectStmt *) ((RawStmt *) (raw_parsetree_list- > head.data- > ptr_value)-> stmt# view the variables in the structure #-> targetList (gdb) p * ($stmt- > targetList) $28 = {type = T_List Length = 5, head = 0x1a47af8, tail = 0x1a48128} # targetList has five elements, corresponding to the first element (gdb) set $restarget= (ResTarget *) ($stmt- > targetList- > head.data- > ptr_value) (gdb) p * $restarget- > val$25 = {type = T_ColumnRef} (gdb) p * (ColumnRef *) $restarget- > val$26 = {type = T_ColumnRef, fields = 0x1a47a08 Location = 7} (gdb) p * ((ColumnRef *) $restarget- > val)-> fields$27 = {type = T_List, length = 2, head = 0x1a47a88, tail = 0x1a479e8} (gdb) p * (Node *) ((ColumnRef *) $restarget- > val)-> fields)-> head.data- > ptr_value$32 = {type = T_String} # fields the first element of the linked list is the data table The second element is the data column (gdb) p * (Value *) (ColumnRef *) $restarget- > val)-> fields)-> head.data- > ptr_value$37 = {type = T_String, val = {ival = 27556248, str = 0x1a47998 "t_dwxx"}} (gdb) p * (Value *) ((ColumnRef *) $restarget- > val)-> fields)-> tail.data- > ptr_value$38 = {type = T_String, val = {ival = 27556272 Str = 0x1a479b0 "dwmc"}} # other similar #-> fromClause (gdb) p * (Node *) ($stmt- > fromClause- > head.data- > ptr_value) $41 = {type = T_JoinExpr} (gdb) set $fromclause= (JoinExpr *) ($stmt- > fromClause- > head.data- > ptr_value) (gdb) p * $fromclause$42 = {type = T_JoinExpr, jointype = JOIN_INNER, isNatural = false, larg = 0x1a484f8, rarg = 0x1a48560, usingClause = 0x0, quals = 0x1a487d0, 0x1a487d0 = 0x1a487d0 Rtindex = 0} #-> whereClause (gdb) p * (Node *) ($stmt- > whereClause) $44 = {type = T_A_Expr} (gdb) p * (FromExpr *) ($stmt- > whereClause) $46 = {type = T_A_Expr, fromlist = 0x1a48bd0 Quals = 0x1a489d0} #-> sortClause (gdb) p * (Node *) ($stmt- > sortClause- > head.data- > ptr_value) $48 = {type = T_SortBy} (gdb) p * (SortBy *) ($stmt- > sortClause- > head.data- > ptr_value) $49 = {type = T_SortBy, node = 0x1a48db0, sortby_dir = SORTBY_DEFAULT, sortby_nulls = SORTBY_NULLS_DEFAULT, useOp = 0x0 Location =-1} #-> limitCount (gdb) p * (Node *) ($stmt- > limitCount) $50 = {type = T_A_Const} (gdb) p * (Const *) ($stmt- > limitCount) $51 = {xpr = {type = T_A_Const}, consttype = 0, consttypmod = 216,constcollid = 0, constlen = 8, constvalue = 231, constisnull = 16, constbyval = false, location = 0} I believe you have a certain understanding of "what is the process of implementing sql in PostgreSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.