关于PostgreSQL的简单查询和扩展查询协议

简单查询和扩展查询是PG前端和后端交互的时候，2种不同交互方法。简单查询时，前端发一个包含SQL的包过去，后端执行后返回结果。扩展查询则是把简单查询切分成Parse,Bind,Describe,Execute,Close等几个步骤，以达到执行计划复用的目的。扩展查询还有防止SQL注入，减少通信数据量（使用binary形式传参）的效果。

更详细的描述，可参考手册：



下面是服务端处理简单查询和扩展查询的代码概要

1.简单查询

点击(此处)折叠或打开

exec_simple_query(const char *query_string)
 ->parsetree_list = pg_parse_query(query_string);
 ->foreach(parsetree_item, parsetree_list)
      ->querytree_list = pg_analyze_and_rewrite(parsetree, query_string,NULL, 0);
      ->plantree_list = pg_plan_queries(querytree_list, 0, NULL);
      ->portal = CreatePortal("", true, true);
      ->PortalDefineQuery(portal,NULL,query_string,commandTag,plantree_list,NULL);
      ->PortalStart(portal, NULL, 0, InvalidSnapshot);
      ->PortalRun(portal,FETCH_ALL,isTopLevel,receiver,receiver,completionTag);
      ->PortalDrop(portal, false);

2.扩展查询

parse消息的处理（命名语句）：

exec_parse_message(const char *query_string,const char *stmt_name,Oid *paramTypes,int numParams)
      ->parsetree_list = pg_parse_query(query_string);
      ->raw_parse_tree = (Node *) linitial(parsetree_list);
      ->psrc = CreateCachedPlan(raw_parse_tree, query_string, commandTag);
      ->query = parse_analyze_varparams(raw_parse_tree,query_string,&paramTypes,&numParams);
      ->querytree_list = pg_rewrite_query(query);
      ->CompleteCachedPlan(psrc,querytree_list,unnamed_stmt_context,paramTypes,numParams,NULL,NULL,0,true);
      ->StorePreparedStatement(stmt_name, psrc, false);

bind消息的处理：

exec_bind_message(StringInfo input_message)
      ->portal_name = pq_getmsgstring(input_message);
      ->stmt_name = pq_getmsgstring(input_message);
      ->pstmt = FetchPreparedStatement(stmt_name, true);
      ->psrc = pstmt->plansource;
      ->cplan = GetCachedPlan(psrc, params, false);
      ->portal = CreatePortal(portal_name, true, true);
      ->PortalDefineQuery(portal,saved_stmt_name,query_string,psrc->commandTag,cplan->stmt_list,cplan);
      ->PortalStart(portal, params, 0, InvalidSnapshot);

GetCachedPlan()会决定新创建一个定制的执行计划还是使用之前保存的通用的执行计划。

execute消息的处理：

exec_execute_message(const char *portal_name, long max_rows)
      ->portal = GetPortalByName(portal_name);
      ->completed = PortalRun(portal,max_rows,true,receiver,receiver,completionTag);

close消息的处理：

PostgresMain(int argc, char *argv[],const char *dbname,const char *username)
  case 'C':
      ->DropPreparedStatement(close_target, false);
      or
      ->PortalDrop(portal, false);

3.参考

http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4150218
src/backend/tcop/postgres.c