Shulou(Shulou.com)06/01 Report--

This section provides a brief introduction to the type conversion implementation in PostgreSQL.

First, let's take a look at a few sample SQL:

Testdb=# testdb=# drop table if exists tweak convincing t_conv where id = '1The verbose select * from t_conv where id >' 13TROP TABLEtestdb=# create table t_conv (id int); CREATE TABLEtestdb=# testdb=# insert into t_conv values (1); INSERT 0 1testdb=# insert into t_conv values (2); INSERT 0 1testdb=#

The query condition is id = '1century 1' converted to int for comparison.

Testdb=# explain verbose select * from t_conv where id ='1' QUERY PLAN-Seq Scan on public.t_conv (cost=0.00..41.88 rows=13 width=4) Output: id Filter: (t_conv.id = 1) (3 rows)

The query condition is id = '1.1, error (1.1 cannot be converted to integer)

Testdb=# explain verbose select * from t_conv where id = '1.1 PSQL: ERROR: invalid input syntax for type integer: "1.1" LINE 1: explain verbose select * from t_conv where id =' 1.1; ^

Query condition is id = '1'::text, error (text and int4 have no translation rules)

Testdb=# explain verbose select * from t_conv where id = '1mistress operator does not exist text No operator matches the given name and argument types psql: ERROR: operator does not exist: integer = textLINE 1: explain verbose select * from t_conv where id =' 1bicycle rider text; ^ HINT: text. You might need to add explicit type casts.testdb=#

The query condition is id > '13'::text, converted to numeric for comparison

Testdb=# explain verbose select * from t_conv where id > '13'::text QUERY PLAN-Seq Scan on public.t_conv (cost=0.00..48.25 rows=850 width=4) Output: id Filter: ((t_conv.id):: numeric > '13'::numeric) (3 rows) testdb=#

System conversion rules are defined, and the ">" operator has a comparison that defines text&int, while the "=" operator is not defined, so the "select * from t_conv where id = '1'::text" statement will make an error.

Testdb=# select oid,typname from pg_type where typname in ('int4','text'); oid | typname-+-23 | int4 25 | text (2 rows) testdb=# select oid,oprname,oprleft,oprright,oprresult,oprcode from pg_operator where oprname =' > 'and oprleft = 23 and oprright = 25 Oid | oprname | oprleft | oprright | oprresult | oprcode-+-16407 | > | 23 | 25 | 16 | int_greater_text (1 row) testdb=# select oid,oprname,oprleft,oprright,oprresult Oprcode from pg_operator where oprname ='= 'and oprleft = 23 and oprright = 25 Oid | oprname | oprleft | oprright | oprresult | oprcode-+-(0 rows)

To sum up:

Parse expressions when different data types are involved:

1. If there is a corresponding type of Operator definition (pg_operator), try to convert the type, otherwise an error will be reported.

two。 If there is a corresponding type of conversion rules, convert to the target type and parse, otherwise an error will be reported.

I. data structure

Form_pg_operator

The definition in pg_operator, in which the code converts the definition to a FormData_pg_operator structure

/ *-* pg_operator definition. Cpp turns this into * typedef struct FormData_pg_operator *-* / CATALOG (pg_operator,2617,OperatorRelationId) {Oid oid; / * oid * / / * name of operator * / NameData oprname; / * OID of namespace containing this oper * / Oid oprnamespace BKI_DEFAULT (PGNSP); / * operator owner * / Oid oprowner BKI_DEFAULT (PGUID) / * can be used in hash join, 'ritual, or 'b' * / char oprkind BKI_DEFAULT (b); / * can be used in merge join? * / bool oprcanmerge BKI_DEFAULT (f); / * can be used in hash join? * / bool oprcanhash BKI_DEFAULT (f); / * left arg type, or 0 if 'l' oprkind * / Oid oprleft BKI_LOOKUP (pg_type) / * right arg type, or 0 if 'r'oprkind * / Oid oprright BKI_LOOKUP (pg_type); / * result datatype * / Oid oprresult BKI_LOOKUP (pg_type); / * OID of commutator oper, or 0 if none * / Oid oprcom BKI_DEFAULT (0) BKI_LOOKUP (pg_operator); / * OID of negator oper, or 0 if none * / Oid oprnegate BKI_DEFAULT (0) BKI_LOOKUP (pg_operator) / * OID of underlying function * / regproc oprcode BKI_LOOKUP (pg_proc); / * OID of restriction estimator, or 0 * / regproc oprrest BKI_DEFAULT (-) BKI_LOOKUP (pg_proc); / * OID of join estimator, or 0 * / regproc oprjoin BKI_DEFAULT (-) BKI_LOOKUP (pg_proc);} FormData_pg_operator / *-* Form_pg_operator corresponds to a pointer to a tuple with * the format of pg_operator relation. *-* / typedef FormData_pg_operator * Form_pg_operator; II. Source code interpretation

Make_op

Transform operator expressions to ensure type compatibility, there will be some type conversions.

/ * * make_op () * Operator expression construction. * * Transform operator expression ensuring type compatibility. * This is where some type conversion happens. * transform operator expressions to ensure type compatibility, there will be some type conversions. * * last_srf should be a copy of pstate- > p_last_srf from just before we * started transforming the operator's arguments; this is used for nested-SRF * detection. If the caller will throw an error anyway for a set-returning * expression, it's okay to cheat and just pass pstate- > p_last_srf. * last_srf should be a copy of pstate- > p_last_srf. * / Expr * make_op (ParseState * pstate, List * opname, Node * ltree, Node * rtree, Node * last_srf, int location) {Oid ltypeId,// left operator type OID rtypeId;// right operator type OID Operator tup;// operator form (see pg_operator) Oid actual_arg_types [2]; / / actual parameter type Oid declared_arg_types [2] / / the parameter type declared by the operator int nargs;// parameter format List * args;// parameter list Oid rettype;// returns the result type OpExpr * result;// result / * Select the operator * / / selection operator if (rtree = = NULL) {/ * right operator * / the right tree is NULL, such as the factorial operator "!": 10! LtypeId = exprType (ltree); rtypeId = InvalidOid; tup = right_oper (pstate, opname, ltypeId, false, location);} else if (ltree = = NULL) {/ * left operator * / / left tree is empty, for example, bitwise NOT operator: "~ 21" rtypeId = exprType (rtree); ltypeId = InvalidOid; tup = left_oper (pstate, opname, rtypeId, false, location) } else {/ * otherwise, binary operator * / / binary operator ltypeId = exprType (ltree); rtypeId = exprType (rtree); tup = oper (pstate, opname, ltypeId, rtypeId, false, location);} / / get operator opform = (Form_pg_operator) GETSTRUCT (tup) / * Check it's not a shell * / if (! RegProcedureIsValid (opform- > oprcode)) ereport (ERROR, (errcode (ERRCODE_UNDEFINED_FUNCTION), errmsg ("operator is only a shell:% s", op_signature_string (opname, opform- > oprkind, opform- > oprleft, opform- > oprright)), parser_errposition (pstate Location) / * Do typecasting and build the expression tree * / / perform type conversion and build expression tree if (rtree = = NULL) {/ * right operator * / args = list_make1 (ltree); / / Parameter actual_arg_types [0] = ltypeId;// left actual parameter type declared_arg_types [0] = opform- > oprleft;// declaration type nargs = 1 / / number of parameters} else if (ltree = = NULL) {/ * left operator * / args = list_make1 (rtree); actual_arg_types [0] = rtypeId; declared_arg_types [0] = opform- > oprright; nargs = 1;} else {/ * otherwise, binary operator * / args = list_make2 (ltree, rtree); actual_arg_types [0] = ltypeId; actual_arg_types [1] = rtypeId Declared_arg_types [0] = opform- > oprleft; declared_arg_types [1] = opform- > oprright; nargs = 2 } / * * enforce consistency with polymorphic argument and return types, * possibly adjusting return type or declared_arg_types (which will be * used as the cast destination by make_fn_arguments) * force polymorphic parameters to be consistent with the return type, and may adjust the return type or declared_arg_types * (which may be used as the conversion target type in the function make_fn_arguments) * / rettype = enforce_generic_type_consistency (actual_arg_types) Declared_arg_types, nargs, opform- > oprresult, false) / * perform the necessary typecasting of arguments * / / Parameter type conversion make_fn_arguments (pstate, args, actual_arg_types, declared_arg_types); / * and build the expression node * / / build expression node result = makeNode (OpExpr); result- > opno = oprid (tup); result- > opfuncid = opform- > oprcode; result- > opresulttype = rettype; result- > opretset = get_func_retset (opform- > oprcode) / * opcollid and inputcollid will be set by parse_collate.c * / result- > args = args; result- > location = location; / * if it returns a set, check that's OK * / if (result- > opretset) {check_srf_call_placement (pstate, last_srf, location); / *. And remember it for error checks at higher levels * / pstate- > p_last_srf = (Node *) result;} ReleaseSysCache (tup); return (Expr *) result;}

Make_fn_arguments

Given the actual parameter expression and the expected input type, perform the necessary type conversion for the expression tree.

/ * * make_fn_arguments () * * Given the actual argument expressions for a function, and the desired * input types for the function, add any necessary typecasting to the * expression tree. Caller should already have verified that casting is * allowed. * perform the necessary type conversion for the expression tree given the actual parameter expression and the expected input type. * the caller has verified that the conversion is feasible. * * Caution: given argument list is modified in-place. * convert parameter types in place. * As with coerce_type, pstate may be NULL if no special unknown-Param * processing is wanted. * / voidmake_fn_arguments (ParseState * pstate, List * fargs, Oid * actual_arg_types, Oid * declared_arg_types) {ListCell * current_fargs; int I = 0; foreach (current_fargs, fargs) {/ * types don't match? Then force coercion using a function call... * / / types do not match? If (actual_arg_ types [I]! = declared_arg_ types [I]) {/ / get node Node * node = (Node *) lfirst (current_fargs); / * If arg is a NamedArgExpr, coerce its input expr instead-we * want the NamedArgExpr to stay at the top level of the list. * cast to input expression * / if (IsA (node, NamedArgExpr)) {NamedArgExpr * na = (NamedArgExpr *) node if the parameter is NamedArgExpr Node = coerce_type (pstate, (Node *) na- > arg, actual_arg_types [I], declared_arg_types [I],-1, COERCION_IMPLICIT, COERCE_IMPLICIT_CAST,-1); na- > arg = (Expr *) node } else {/ / execute the transformation node = coerce_type (pstate, node, actual_arg_types [I], declared_arg_types [I],-1, COERCION_IMPLICIT, COERCE_IMPLICIT_CAST,-1) Lfirst (current_fargs) = node;}} iTunes;}} III. Tracking analysis

SQL script

Testdb=# select * from t_conv where id ='1'

Tracking and analysis

(gdb) b make_opBreakpoint 2 at 0x619cdb: file parse_oper.c, line 762. (gdb) cContinuing.Breakpoint 2, make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30) at parse_oper.c:762762 if (rtree= = NULL) (gdb)

Call stack information

(gdb) bt#0 make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30) at parse_oper.c:859#1 0x000000000060df9c in transformAExprOp (pstate=0x22d6e30, a=0x22d6ba0) at parse_expr.c:1010#2 0x000000000060c55b in transformExprRecurse (pstate=0x22d6e30, expr=0x22d6ba0) at parse_expr.c:216#3 0x000000000060c298 in transformExpr (pstate=0x22d6e30, expr=0x22d6ba0, exprKind=EXPR_KIND_WHERE) at parse_expr.c:155#4 0x0000000000602164 in transformWhereClause (pstate=0x22d6e30, clause=0x22d6ba0, exprKind=EXPR_KIND_WHERE ConstructName=0xb458a7 "WHERE") at parse_clause.c:1691#5 0x00000000005c7eb5 in transformSelectStmt (pstate=0x22d6e30, stmt=0x22d6c80) at analyze.c:1239#6 0x00000000005c6392 in transformStmt (pstate=0x22d6e30, parseTree=0x22d6c80) at analyze.c:301#7 0x00000000005c626d in transformOptionalSelectInto (pstate=0x22d6e30, parseTree=0x22d6c80) at analyze.c:246#8 0x00000000005c612b in transformTopLevelStmt (pstate=0x22d6e30, parseTree=0x22d6d98) at analyze.c:196#9 0x00000000005c5f83 in parse_analyze (parseTree=0x22d6d98, sourceText=0x22d5e08 "select * from t_conv where id ='1' ", paramTypes=0x0, numParams=0, queryEnv=0x0) at analyze.c:116#10 0x00000000008e78d9 in pg_analyze_and_rewrite (parsetree=0x22d6d98, query_string=0x22d5e08" select * from t_conv where id ='1'; ", paramTypes=0x0, numParams=0, queryEnv=0x0) at postgres.c:695#11 0x00000000008e7f23 in exec_simple_query (query_string=0x22d5e08" select * from t_conv where id ='1' At postgres.c:1140#12 0x00000000008ec3a0 in PostgresMain (argc=1, argv=0x22ffdb8, dbname=0x22ffc00 "testdb", username=0x22d2a68 "pg12") at postgres.c:4249#13 0x0000000000843650 in BackendRun (port=0x22f7be0) at postmaster.c:4431#14 0x0000000000842e2e in BackendStartup (port=0x22f7be0) at postmaster.c:4122#15 0x000000000083f06a in ServerLoop () at postmaster.c:1704#16 0x000000000083e920 in PostmasterMain (argc=1, argv=0x22d0a20) at postmaster.c:1377#17 0x000000000075f834 in main (argc=1, argv=0x22d0a20) at main.c:228

Input parameters

The operator is "="; ltree is a field column with type int4 (vartype = 23); rtree is constant and type is unknown (consttype = 705); last_srf is NULL; the operator has an offset of 30. 0 in the string.

(gdb) p * pstate$7 = {parentParseState = 0x0, p_sourcetext = 0x22d5e08 "select * from t_conv where id ='1' , p_rtable = 0x22d72a0, p_joinexprs = 0x0, p_joinlist = 0x22d73a8, p_namespace = 0x22d7328, p_lateral_active = false, p_ctenamespace = 0x0, p_future_ctes = 0x0, p_parent_cte = 0x0, p_target_relation = 0x0, p_target_rangetblentry = 0x0, p_is_insert = false, p_windowdefs = 0x0, p_expr_kind = EXPR_KIND_WHERE, p_next_resno = 2, p_multiassign_exprs = 0x0, p_locking_clause = 0x0 P_locked_from_parent = false, p_resolve_unknowns = true, p_queryEnv = 0x0, p_hasAggs = false, p_hasWindowFuncs = false, p_hasTargetSRFs = false, p_hasSubLinks = false, p_hasModifyingCTE = false, p_last_srf = 0x0, p_pre_columnref_hook = 0x0, p_post_columnref_hook = 0x0, p_paramref_hook = 0x0, p_coerce_param_hook = 0x0, p_ref_hook_state = 0x0} (gdb) p * opname$8 = {type = T_List, length = 1 Head = 0x22d6c20, tail = 0x22d6c20} (gdb) p * (Node *) opname- > head- > data.ptr_value$9 = {type = T_String} (gdb) p * (Value *) opname- > head- > data.ptr_value$14 = {type = T_String, val = {ival = 12298942, str = 0xbbaabe "="} (gdb) p * ltree$15 = {type = T_Var} (gdb) p * (Var *) ltree$16 = {xpr = {type = T_Var}, varno = 1, varattno = 1, vartype = 23, vartypmod =-1, vartypmod = 0 Varlevelsup = 0, varnoold = 1, varoattno = 1, location = 27} (gdb) p * (Const *) rtree$18 = {xpr = {type = T_Const}, consttype = 705, consttypmod =-1, constcollid = 0, constlen =-2, constvalue = 36531016, constisnull = false, constbyval = false, location = 32} # # testdb=# select typname from pg_type where oid in (23705) Typname-int4 unknown (2 rows) #

Determine operator (binary operator)

(gdb) n769 else if (ltree = = NULL) (gdb) 779 ltypeId = exprType (ltree); (gdb) 780 rtypeId = exprType (rtree); (gdb) 781 tup = oper (pstate, opname, ltypeId, rtypeId, false, location); (gdb) p ltypeId$19 = 23 (gdb) p rtypeId$20 = 705 (gdb)

Get operator (corresponding to pg_operator)

(gdb) n784 opform = (Form_pg_operator) GETSTRUCT (tup); (gdb) 787 if (! RegProcedureIsValid (opform- > oprcode)) (gdb) p * opform$21 = {oid = 96, oprname = {data = "=",'\ 000'}, oprnamespace = 11, oprowner = 10, oprkind = 98 'baked, oprcanmerge = true, oprcanhash = true, oprleft = 23, oprright = 23, oprresult = 16, oprcom = 96, oprnegate = 518, oprcode = 65, oprrest = 101, oprjoin = 105} (gdb)

Perform type conversions and build the expression tree.

The actual type is 23percent 705, and the operator declares the operation type as 23percent 23.

(gdb) n798 if (rtree = = NULL) (gdb) 806 else if (ltree = = NULL) (gdb) 817 args = list_make2 (ltree, rtree); (gdb) 818 actual_arg_types [0] = ltypeId; (gdb) 819 actual_arg_types [1] = rtypeId; (gdb) 820 declared_arg_types [0] = opform- > oprleft; (gdb) 821 declared_arg_types [1] = opform- > oprright; (gdb) 822 nargs = 2 (gdb) p opform- > oprleft$22 = 23 (gdb) p opform- > oprright$23 = 23 (gdb) n830 rettype = enforce_generic_type_consistency (actual_arg_types, (gdb)

Enter the function make_fn_arguments

(gdb) 837 make_fn_arguments (pstate, args, actual_arg_types, declared_arg_types); (gdb) nBreakpoint 1, make_fn_arguments (pstate=0x22d6e30, fargs=0x22d76f8, actual_arg_types=0x7ffda1b2af80, declared_arg_types=0x7ffda1b2af70) at parse_func.c:18351835 int I = 0; (gdb)

Call the coerce_type function to perform the transformation, unknown-> int4

(gdb) n1837 foreach (current_fargs, fargs) (gdb) 1840 if (actual_arg_ types [I]! = declared_arg_ types [I]) (gdb) 1873 itypes + (gdb) 1837 foreach (current_fargs, fargs) (gdb) 1840 if (actual_arg_ types [I]! = declared_arg_ types [I]) (gdb) p actual_arg_types [I] $24 = 705 (gdb) p declared_arg_types [I] $25 = 23 (gdb) n1842 Node * node = (Node *) lfirst (current_fargs) (gdb) n1848 if (IsA (node, NamedArgExpr)) (gdb) 1866 declared_arg_types [I],-1, (gdb) 1863 node = coerce_type (pstate, (gdb) 1865 actual_arg_types [I], (gdb) 1863 node = coerce_type (pstate, (gdb) 1870 lfirst (current_fargs) = node (gdb) p * node$26 = {type = T_Const} (gdb) p * (Const *) node$27 = {xpr = {type = T_Const}, consttype = 23, consttypmod =-1, constcollid = 0, constlen = 4, constvalue = 1, constisnull = false, constbyval = true, location = 32} (gdb)

Complete the call

(gdb) n1873 iTunes; (gdb) 1837 foreach (current_fargs, fargs) (gdb) 1875} (gdb) make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30) at parse_oper.c:840840 result = makeNode (OpExpr)

Go back to make_op and construct the resulting result structure

(gdb) 841 result- > opno = oprid (tup); (gdb) 842 result- > opfuncid = opform- > oprcode; (gdb) 843 result- > opresulttype = rettype; (gdb) 844 result- > opretset = get_func_retset (opform- > oprcode); (gdb) 846 result- > args = args; (gdb) 847 result- > location = location; (gdb) 850 if (result- > opretset) (gdb) 857 ReleaseSysCache (tup); (gdb) 859 gdb (gdb *) (gdb) p * (Expr *) result$28 = {type = T_OpExpr} (gdb) p * (OpExpr *) result$29 = {xpr = {type = T_OpExpr}, opno = 96, opfuncid = 65, opresulttype = 16, opretset = false, opcollid = 0, inputcollid = 0, args = 0x22d76f8, location = 30} (gdb)

DONE!

The implementation function coerce_type is introduced in the next section.

IV. Reference materials

PostgreSQL Type Conversion

PostgreSQL data type conversion rule # 1

PostgreSQL data type conversion rule # 2

PostgreSQL data type conversion rule # 3

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.