Open28
PostgreSQLのソースコードを読もう
nnaka2992GWなのでPostgreSQLのソースコードを読んでどのように動いているのかをコードレベルで理解したいです。
といっても数十年開発が続く巨大なシステムなので、目的なく読んでも理解出来なくなるのでpostgresが実行されてからINSERT文が実行されるまでの流れを追います。
SELECT foo FROM bar INSERT INTO bazのパターンは除きます
GitHubにあるコードはミラーなのですが、リンクの貼りやすさなどを考慮してミラーのコードを読んでいきます。
nnaka2992postgresのmain関数はsrc/backend/main/main.cの59-201行目で定義されていました。
main関数の大枠を整理すると以下のようになります。
nnaka2992
StevensのリライアブルなBSD-Style リードライトルーティーンをパクったRWWをパクった
Stevensとは？
nnaka2992ひとまずServerLoopがINSERT文を受け取ってクエリを実行し、イベントループに戻る流れまでを追えた。
PostgresMain()の中でやっている処理を細かく追っていく
nnaka2992postmasterの位置づけがイマイチわからない
postmasterはpostgresの廃止予定の別名とあるけど、コードを見る限りpostmasterがpostgresコマンドの中心に見える
昔はpostgresコマンドがpostmasterコマンドだったってこと？
わからない
nnaka2992一体これはなんの目的で存在しているのだろう？
nnaka2992ソースコードを読んでもparsetreeのクエリがどんな感じなのかのイメージがあまりわかないので、コード内にプリントを仕込んでparsetree_list(parsetree_item)を表示してみる。 の直後に以下のコードを挿入していくらかクエリを実行してみる
printf("Print query_string: \n");
printf("%s", query_string);
printf("\n");
printf("Print parsetree_list: \n");
for (int i = 0; i < list_length(parsetree_list); i++)
{
printf("%s", nodeToString(list_nth(parsetree_list, i)));
printf("\n");
}
printf("\n");
Print query_string:
create table employee (
id integer primary key,
name varchar(256)
);
Print parsetree_list:
{RAWSTMT :stmt {CREATESTMT :relation {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias <> :location 13} :tableElts ({COLUMNDEF :colname id :typeName {TYPENAME :names ("pg_catalog" "int4") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 27} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints ({CONSTRAINT :conname <> :deferrable false :initdeferred false :location 35 :contype PRIMARY_KEY :keys <> :including <> :options <> :indexname <> :indexspace <> :reset_default_tblspc false}) :fdwoptions <> :location 24} {COLUMNDEF :colname name :typeName {TYPENAME :names ("pg_catalog" "varchar") :typeOid 0 :setof false :pct_type false :typmods ({A_CONST :val 256 :location 61}) :typemod -1 :arrayBounds <> :location 53} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints <> :fdwoptions <> :location 48}) :inhRelations <> :partbound <> :partspec <> :ofTypename <> :constraints <> :options <> :oncommit 0 :tablespacename <> :accessMethod <> :if_not_exists false} :stmt_location 0 :stmt_len 67}
Print query_string:
drop table employee;
Print parsetree_list:
{RAWSTMT :stmt {DROPSTMT :objects (("employee")) :removeType 41 :behavior 0 :missing_ok false :concurrent false} :stmt_location 0 :stmt_len 19}
Print query_string:
create table employee (
id integer primary key,
name varchar(256)
,job_title varchar(256)
);
Print parsetree_list:
{RAWSTMT :stmt {CREATESTMT :relation {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias <> :location 13} :tableElts ({COLUMNDEF :colname id :typeName {TYPENAME :names ("pg_catalog" "int4") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 27} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints ({CONSTRAINT :conname <> :deferrable false :initdeferred false :location 35 :contype PRIMARY_KEY :keys <> :including <> :options <> :indexname <> :indexspace <> :reset_default_tblspc false}) :fdwoptions <> :location 24} {COLUMNDEF :colname name :typeName {TYPENAME :names ("pg_catalog" "varchar") :typeOid 0 :setof false :pct_type false :typmods ({A_CONST :val 256 :location 61}) :typemod -1 :arrayBounds <> :location 53} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints <> :fdwoptions <> :location 48} {COLUMNDEF :colname job_title :typeName {TYPENAME :names ("pg_catalog" "varchar") :typeOid 0 :setof false :pct_type false :typmods ({A_CONST :val 256 :location 85}) :typemod -1 :arrayBounds <> :location 77} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints <> :fdwoptions <> :location 67}) :inhRelations <> :partbound <> :partspec <> :ofTypename <> :constraints <> :options <> :oncommit 0 :tablespacename <> :accessMethod <> :if_not_exists false} :stmt_location 0 :stmt_len 91}
Print query_string:
create table job_title (
job_title varchar(256) primary key,
saraly numeric(16,2)
);
Print parsetree_list:
{RAWSTMT :stmt {CREATESTMT :relation {RANGEVAR :catalogname <> :schemaname <> :relname job_title :inh true :relpersistence p :alias <> :location 13} :tableElts ({COLUMNDEF :colname job_title :typeName {TYPENAME :names ("pg_catalog" "varchar") :typeOid 0 :setof false :pct_type false :typmods ({A_CONST :val 256 :location 43}) :typemod -1 :arrayBounds <> :location 35} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints ({CONSTRAINT :conname <> :deferrable false :initdeferred false :location 48 :contype PRIMARY_KEY :keys <> :including <> :options <> :indexname <> :indexspace <> :reset_default_tblspc false}) :fdwoptions <> :location 25} {COLUMNDEF :colname saraly :typeName {TYPENAME :names ("pg_catalog" "numeric") :typeOid 0 :setof false :pct_type false :typmods ({A_CONST :val 16 :location 76} {A_CONST :val 2 :location 79}) :typemod -1 :arrayBounds <> :location 68} :compression <> :inhcount 0 :is_local true :is_not_null false :is_from_type false :storage <> :storage_name <> :raw_default <> :cooked_default <> :identity <> :identitySequence <> :generated <> :collClause <> :collOid 0 :constraints <> :fdwoptions <> :location 61}) :inhRelations <> :partbound <> :partspec <> :ofTypename <> :constraints <> :options <> :oncommit 0 :tablespacename <> :accessMethod <> :if_not_exists false} :stmt_location 0 :stmt_len 83}
Print query_string:
select
e.id, e.name, j.job_title, j.saraly
from employee e
inner join job_title j
on e.job_title=j.job_title;
Print parsetree_list:
{RAWSTMT :stmt {SELECTSTMT :distinctClause <> :intoClause <> :targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "id") :location 7} :location 7} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "name") :location 13} :location 13} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "job_title") :location 21} :location 21} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "saraly") :location 34} :location 34}) :fromClause ({JOINEXPR :jointype 0 :isNatural false :larg {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias {ALIAS :aliasname e :colnames <>} :location 48} :rarg {RANGEVAR :catalogname <> :schemaname <> :relname job_title :inh true :relpersistence p :alias {ALIAS :aliasname j :colnames <>} :location 70} :usingClause <> :join_using_alias <> :quals {A_EXPR :name ("=") :lexpr {COLUMNREF :fields ("e" "job_title") :location 85} :rexpr {COLUMNREF :fields ("j" "job_title") :location 97} :location 96} :alias <> :rtindex 0}) :whereClause <> :groupClause <> :groupDistinct false :havingClause <> :windowClause <> :valuesLists <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <>} :stmt_location 0 :stmt_len 108}
Print query_string:
select
e.id, e.name, j.job_title, j.saraly
from employee e
inner join job_title j
on e.job_title=j.job_title;
Print parsetree_list:
{RAWSTMT :stmt {SELECTSTMT :distinctClause <> :intoClause <> :targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "id") :location 11} :location 11} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "name") :location 17} :location 17} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "job_title") :location 25} :location 25} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "saraly") :location 38} :location 38}) :fromClause ({JOINEXPR :jointype 0 :isNatural false :larg {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias {ALIAS :aliasname e :colnames <>} :location 52} :rarg {RANGEVAR :catalogname <> :schemaname <> :relname job_title :inh true :relpersistence p :alias {ALIAS :aliasname j :colnames <>} :location 74} :usingClause <> :join_using_alias <> :quals {A_EXPR :name ("=") :lexpr {COLUMNREF :fields ("e" "job_title") :location 93} :rexpr {COLUMNREF :fields ("j" "job_title") :location 105} :location 104} :alias <> :rtindex 0}) :whereClause <> :groupClause <> :groupDistinct false :havingClause <2023-05-04 14:08:51.846 JST [190004] LOG: checkpoint starting: time
RawStmtとanalyze後のparsetree、pg_plan_query後のparsetreeをみたいのでprintfを追加して再実行
Print query_string:
select
e.id, e.name, j.job_title, j.saraly
from employee e
inner join job_title j
on e.job_title=j.job_title;
Print parsetree_list:
{RAWSTMT :stmt {SELECTSTMT :distinctClause <> :intoClause <> :targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "id") :location 11} :location 11} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "name") :location 17} :location 17} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "job_title") :location 25} :location 25} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "saraly") :location 38} :location 38}) :fromClause ({JOINEXPR :jointype 0 :isNatural false :larg {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias {ALIAS :aliasname e :colnames <>} :location 52} :rarg {RANGEVAR :catalogname <> :schemaname <> :relname job_title :inh true :relpersistence p :alias {ALIAS :aliasname j :colnames <>} :location 74} :usingClause <> :join_using_alias <> :quals {A_EXPR :name ("=") :lexpr {COLUMNREF :fields ("e" "job_title") :location 93} :rexpr {COLUMNREF :fields ("j" "job_title") :location 105} :location 104} :alias <> :rtindex 0}) :whereClause <> :groupClause <> :groupDistinct false :havingClause <> :windowClause <> :valuesLists <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <>} :stmt_location 0 :stmt_len 116}
Print RawStmt:
{RAWSTMT :stmt {SELECTSTMT :distinctClause <> :intoClause <> :targetList ({RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "id") :location 11} :location 11} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("e" "name") :location 17} :location 17} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "job_title") :location 25} :location 25} {RESTARGET :name <> :indirection <> :val {COLUMNREF :fields ("j" "saraly") :location 38} :location 38}) :fromClause ({JOINEXPR :jointype 0 :isNatural false :larg {RANGEVAR :catalogname <> :schemaname <> :relname employee :inh true :relpersistence p :alias {ALIAS :aliasname e :colnames <>} :location 52} :rarg {RANGEVAR :catalogname <> :schemaname <> :relname job_title :inh true :relpersistence p :alias {ALIAS :aliasname j :colnames <>} :location 74} :usingClause <> :join_using_alias <> :quals {A_EXPR :name ("=") :lexpr {COLUMNREF :fields ("e" "job_title") :location 93} :rexpr {COLUMNREF :fields ("j" "job_title") :location 105} :location 104} :alias <> :rtindex 0}) :whereClause <> :groupClause <> :groupDistinct false :havingClause <> :windowClause <> :valuesLists <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <>} :stmt_location 0 :stmt_len 116}
Print querytree_list:
{QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :isReturn false :cteList <> :rtable ({RANGETBLENTRY :alias {ALIAS :aliasname e :colnames <>} :eref {ALIAS :aliasname e :colnames ("id" "name" "job_title")} :rtekind 0 :relid 16393 :relkind r :rellockmode 1 :tablesample <> :perminfoindex 1 :lateral false :inh true :inFromCl true :securityQuals <>} {RANGETBLENTRY :alias {ALIAS :aliasname j :colnames <>} :eref {ALIAS :aliasname j :colnames ("job_title" "saraly")} :rtekind 0 :relid 16400 :relkind r :rellockmode 1 :tablesample <> :perminfoindex 2 :lateral false :inh true :inFromCl true :securityQuals <>} {RANGETBLENTRY :alias <> :eref {ALIAS :aliasname unnamed_join :colnames ("id" "name" "job_title" "job_title" "saraly")} :rtekind 2 :jointype 0 :joinmergedcols 0 :joinaliasvars ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1} {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location -1} {VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location -1} {VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location -1} {VAR :varno 2 :varattno 2 :vartype 1700 :vartypmod 1048582 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location -1}) :joinleftcols (i 1 2 3) :joinrightcols (i 1 2) :join_using_alias <> :lateral false :inh false :inFromCl true :securityQuals <>}) :rteperminfos ({RTEPERMISSIONINFO :relid 16393 :inh true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9 10) :insertedCols (b) :updatedCols (b)} {RTEPERMISSIONINFO :relid 16400 :inh true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9) :insertedCols (b) :updatedCols (b)}) :jointree {FROMEXPR :fromlist ({JOINEXPR :jointype 0 :isNatural false :larg {RANGETBLREF :rtindex 1} :rarg {RANGETBLREF :rtindex 2} :usingClause <> :join_using_alias <> :quals {OPEXPR :opno 98 :opfuncid 67 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 93} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1} {RELABELTYPE :arg {VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location 105} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}) :location 104} :alias <> :rtindex 3}) :quals <>} :mergeActionList <> :mergeUseOuterJoin false :targetList ({TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 11} :resno 1 :resname id :ressortgroupref 0 :resorigtbl 16393 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 17} :resno 2 :resname name :ressortgroupref 0 :resorigtbl 16393 :resorigcol 2 :resjunk false} {TARGETENTRY :expr {VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location 25} :resno 3 :resname job_title :ressortgroupref 0 :resorigtbl 16400 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 2 :varattno 2 :vartype 1700 :vartypmod 1048582 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 38} :resno 4 :resname saraly :ressortgroupref 0 :resorigtbl 16400 :resorigcol 2 :resjunk false}) :override 0 :onConflict <> :returningList <> :groupClause <> :groupDistinct false :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location 0 :stmt_len 116}
Print querytree_list after pg_plan_queries:
{PLANNEDSTMT :commandType 1 :queryId 0 :hasReturning false :hasModifyingCTE false :canSetTag true :transientPlan false :dependsOnRole false :parallelModeNeeded false :jitFlags 0 :planTree {HASHJOIN :join.plan.startup_cost 11.575 :join.plan.total_cost 24.2 :join.plan.plan_rows 70 :join.plan.plan_width 1054 :join.plan.parallel_aware false :join.plan.parallel_safe true :join.plan.async_capable false :join.plan.plan_node_id 0 :join.plan.targetlist ({TARGETENTRY :expr {VAR :varno -1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 11} :resno 1 :resname id :ressortgroupref 0 :resorigtbl 16393 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno -1 :varattno 2 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 17} :resno 2 :resname name :ressortgroupref 0 :resorigtbl 16393 :resorigcol 2 :resjunk false} {TARGETENTRY :expr {VAR :varno -2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location 25} :resno 3 :resname job_title :ressortgroupref 0 :resorigtbl 16400 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno -2 :varattno 2 :vartype 1700 :vartypmod 1048582 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location 38} :resno 4 :resname saraly :ressortgroupref 0 :resorigtbl 16400 :resorigcol 2 :resjunk false}) :join.plan.qual <> :join.plan.lefttree {SEQSCAN :scan.plan.startup_cost 0 :scan.plan.total_cost 11.4 :scan.plan.plan_rows 140 :scan.plan.plan_width 534 :scan.plan.parallel_aware false :scan.plan.parallel_safe true :scan.plan.async_capable false :scan.plan.plan_node_id 1 :scan.plan.targetlist ({TARGETENTRY :expr {VAR :varno 2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location -1} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno 2 :varattno 2 :vartype 1700 :vartypmod 1048582 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 2 :location -1} :resno 2 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :scan.plan.qual <> :scan.plan.lefttree <> :scan.plan.righttree <> :scan.plan.initPlan <> :scan.plan.extParam (b) :scan.plan.allParam (b) :scan.scanrelid 2} :join.plan.righttree {HASH :plan.startup_cost 10.7 :plan.total_cost 10.7 :plan.plan_rows 70 :plan.plan_width 1036 :plan.parallel_aware false :plan.parallel_safe true :plan.async_capable false :plan.plan_node_id 2 :plan.targetlist ({TARGETENTRY :expr {VAR :varno -2 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location -1} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno -2 :varattno 2 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location -1} :resno 2 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno -2 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location -1} :resno 3 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :plan.qual <> :plan.lefttree {SEQSCAN :scan.plan.startup_cost 0 :scan.plan.total_cost 10.7 :scan.plan.plan_rows 70 :scan.plan.plan_width 1036 :scan.plan.parallel_aware false :scan.plan.parallel_safe true :scan.plan.async_capable false :scan.plan.plan_node_id 3 :scan.plan.targetlist ({TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 11} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 17} :resno 2 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno 1 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 93} :resno 3 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :scan.plan.qual <> :scan.plan.lefttree <> :scan.plan.righttree <> :scan.plan.initPlan <> :scan.plan.extParam (b) :scan.plan.allParam (b) :scan.scanrelid 1} :plan.righttree <> :plan.initPlan <> :plan.extParam (b) :plan.allParam (b) :hashkeys ({RELABELTYPE :arg {VAR :varno -2 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 93} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}) :skewTable 16400 :skewColumn 1 :skewInherit false :rows_total 0} :join.plan.initPlan <> :join.plan.extParam (b) :join.plan.allParam (b) :join.jointype 0 :join.inner_unique false :join.joinqual <> :hashclauses ({OPEXPR :opno 98 :opfuncid 67 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 100 :args ({RELABELTYPE :arg {VAR :varno -2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location 105} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1} {RELABELTYPE :arg {VAR :varno -1 :varattno 3 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 93} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1}) :location -1}) :hashoperators (o 98) :hashcollations (o 100) :hashkeys ({RELABELTYPE :arg {VAR :varno -2 :varattno 1 :vartype 1043 :vartypmod 260 :varcollid 100 :varnullingrels (b) :varlevelsup 0 :varnosyn 2 :varattnosyn 1 :location 105} :resulttype 25 :resulttypmod -1 :resultcollid 100 :relabelformat 2 :location -1})} :partPruneInfos <> :rtable ({RANGETBLENTRY :alias {ALIAS :aliasname e :colnames <>} :eref {ALIAS :aliasname e :colnames ("id" "name" "job_title")} :rtekind 0 :relid 16393 :relkind r :rellockmode 1 :tablesample <> :perminfoindex 1 :lateral false :inh false :inFromCl true :securityQuals <>} {RANGETBLENTRY :alias {ALIAS :aliasname j :colnames <>} :eref {ALIAS :aliasname j :colnames ("job_title" "saraly")} :rtekind 0 :relid 16400 :relkind r :rellockmode 1 :tablesample <> :perminfoindex 2 :lateral false :inh false :inFromCl true :securityQuals <>} {RANGETBLENTRY :alias <> :eref {ALIAS :aliasname unnamed_join :colnames ("id" "name" "job_title" "job_title" "saraly")} :rtekind 2 :jointype 0 :joinmergedcols 0 :joinaliasvars <> :joinleftcols <> :joinrightcols <> :join_using_alias <> :lateral false :inh false :inFromCl true :securityQuals <>}) :permInfos ({RTEPERMISSIONINFO :relid 16393 :inh true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9 10) :insertedCols (b) :updatedCols (b)} {RTEPERMISSIONINFO :relid 16400 :inh true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9) :insertedCols (b) :updatedCols (b)}) :resultRelations <> :appendRelations <> :subplans <> :rewindPlanIDs (b) :rowMarks <> :relationOids (o 16393 16400) :invalItems <> :paramExecTypes <> :utilityStmt <> :stmt_location 0 :stmt_len 116}
saralyじゃなくてsalaryでは？
nnaka2992RawStmtはlfirst_nodeでListからRawStmtにcastしてるだけだからparsetree_list(item)のときと内容の差はないのか
nnaka2992なるほどsrc/backend/nodes/print.cのpprint()とか使えばもっときれいに表示できたのか
そもそもDebug_print_parseとかオンにしておけばこんな小面倒なことしなくてよかったのか
nnaka2992Range Tableがイマイチよくわからない
要するにテーブルだのViewだのリレーショナルなオブジェクトたちのことか
The range table is a list of relations that are used in the query. In a SELECT statement these are the relations given after the FROM key word.
Every range table entry identifies a table or view and tells by which name it is called in the other parts of the query. In the query tree, the range table entries are referenced by number rather than by name, so here it doesn't matter if there are duplicate names as it would in an SQL statement. This can happen after the range tables of rules have been merged in. The examples in this chapter will not have this situation.
nnaka2992insert into employee values (0, 'Jhon Doe');したときのquerytreeでrtableがinsert先のテーブルになってるしこの理解でいったんは問題なさそう
(
{QUERY
:commandType 3
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 1
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable (
{RANGETBLENTRY
:alias <>
:eref
{ALIAS
:aliasname employee
:colnames ("id" "name" "job_title")
}
:rtekind 0
:relid 16393
:relkind r
:rellockmode 3
:tablesample <>
:perminfoindex 1
:lateral false
:inh false
:inFromCl false
:securityQuals <>
}
)
:rteperminfos (
{RTEPERMISSIONINFO
:relid 16393
:inh false
:requiredPerms 1
:checkAsUser 0
:selectedCols (b)
:insertedCols (b 8 9)
:updatedCols (b)
}
)
:jointree
{FROMEXPR
:fromlist <>
:quals <>
}
:mergeActionList <>
:mergeUseOuterJoin false
:targetList (
{TARGETENTRY
:expr
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 29
:constvalue 4 [ 0 0 0 0 0 0 0 0 ]
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{FUNCEXPR
:funcid 669
:funcresulttype 1043
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 100
:inputcollid 100
:args (
{CONST
:consttype 1043
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 32
:constvalue 12 [ 48 0 0 0 74 104 111 110 32 68 111 101 ]
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 4 1 0 0 0 0 0 0 ]
}
{CONST
:consttype 16
:consttypmod -1
:constcollid 0
:constlen 1
:constbyval true
:constisnull false
:location -1
:constvalue 1 [ 0 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:resno 2
:resname name
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 43
}
)
{QUERY
:commandType 3
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 1
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable (
{RANGETBLENTRY
:alias <>
:eref
{ALIAS
:aliasname employee
:colnames ("id" "name" "job_title")
}
:rtekind 0
:relid 16393
:relkind r
:rellockmode 3
:tablesample <>
:perminfoindex 1
:lateral false
:inh false
:inFromCl false
:securityQuals <>
}
)
:rteperminfos (
{RTEPERMISSIONINFO
:relid 16393
:inh false
:requiredPerms 1
:checkAsUser 0
:selectedCols (b)
:insertedCols (b 8 9)
:updatedCols (b)
}
)
:jointree
{FROMEXPR
:fromlist <>
:quals <>
}
:mergeActionList <>
:mergeUseOuterJoin false
:targetList (
{TARGETENTRY
:expr
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 29
:constvalue 4 [ 0 0 0 0 0 0 0 0 ]
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{FUNCEXPR
:funcid 669
:funcresulttype 1043
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 100
:inputcollid 100
:args (
{CONST
:consttype 1043
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 32
:constvalue 12 [ 48 0 0 0 74 104 111 110 32 68 111 101 ]
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 4 1 0 0 0 0 0 0 ]
}
{CONST
:consttype 16
:consttypmod -1
:constcollid 0
:constlen 1
:constbyval true
:constisnull false
:location -1
:constvalue 1 [ 0 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:resno 2
:resname name
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 43
}
)
The concept is that sometimes PostgreSQL needs to keep track of per-tuple information that isn’t part of the query output. It might be a sort key that’s not part of the select list, an intermediate result from a subquery that’s used as a filter then discarded, or it might be an internal column like ctid that isn’t exposed to users.
nnaka2992RewriteQuery()前後の比較
parsetree-targetList before rewrite:
(
{TARGETENTRY
:expr
{VAR
:varno 2
:varattno 1
:vartype 1043
:vartypmod 260
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varnosyn 2
:varattnosyn 1
:location -1
}
:resno 1
:resname job_title
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 2
:varattno 2
:vartype 1700
:vartypmod 1048582
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varnosyn 2
:varattnosyn 2
:location -1
}
:resno 2
:resname saraly
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
parsetree-targetList after rewrite:
(
{TARGETENTRY
:expr
{VAR
:varno 2
:varattno 1
:vartype 1043
:vartypmod 260
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varnosyn 2
:varattnosyn 1
:location -1
}
:resno 1
:resname job_title
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 2
:varattno 2
:vartype 1700
:vartypmod 1048582
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varnosyn 2
:varattnosyn 2
:location -1
}
:resno 2
:resname saraly
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{FUNCEXPR
:funcid 669
:funcresulttype 1043
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 100
:inputcollid 100
:args (
{CONST
:consttype 1043
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location -1
:constvalue 9 [ 36 0 0 0 115 116 117 102 102 ]
}
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location -1
:constvalue 4 [ 4 1 0 0 0 0 0 0 ]
}
{CONST
:consttype 16
:consttypmod -1
:constcollid 0
:constlen 1
:constbyval true
:constisnull false
:location -1
:constvalue 1 [ 0 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:resno 3
:resname level
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
これぐらい違うらしい
43a44,94
> {TARGETENTRY
> :expr
> {FUNCEXPR
> :funcid 669
> :funcresulttype 1043
> :funcretset false
> :funcvariadic false
> :funcformat 2
> :funccollid 100
> :inputcollid 100
> :args (
> {CONST
> :consttype 1043
> :consttypmod -1
> :constcollid 100
> :constlen -1
> :constbyval false
> :constisnull false
> :location -1
> :constvalue 9 [ 36 0 0 0 115 116 117 102 102 ]
> }
> {CONST
> :consttype 23
> :consttypmod -1
> :constcollid 0
> :constlen 4
> :constbyval true
> :constisnull false
> :location -1
> :constvalue 4 [ 4 1 0 0 0 0 0 0 ]
> }
> {CONST
> :consttype 16
> :consttypmod -1
> :constcollid 0
> :constlen 1
> :constbyval true
> :constisnull false
> :location -1
> :constvalue 1 [ 0 0 0 0 0 0 0 0 ]
> }
> )
> :location -1
> }
> :resno 3
> :resname level
> :ressortgroupref 0
> :resorigtbl 0
> :resorigcol 0
> :resjunk false
> }
mutex的なやつじゃないロックが存在するらしい
nnaka2992このコメントのreally "locks"がいわゆるlockでRuleLockはただの名前ということ？
/*
* RuleLock -
* all rules that apply to a particular relation. Even though we only
* have the rewrite rule system left and these are not really "locks",
* the name is kept for historical reasons.
*/
おそらくRuleLock=Rewrite Ruleの認識で問題なさそう
nnaka2992なるほど知らなかった
PostgreSQLのルールシステムを使用すると、データベーステーブルに対する挿入、更新、削除時に本来の操作の代替として実行するアクションを定義できます。
nnaka2992なるほど、Cにtry chatchがないからPG_TRY/PG_CATCHみたいなマクロを作ってるのか
nnaka2992
ChoosePoratlStrategyはシンプルなINSERTならPORTAL_MULTI_QUERYを返すみたい
nnaka2992PORTAL_MULTI_QUERY
(
{PLANNEDSTMT
:commandType 3
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:jitFlags 0
:planTree
{MODIFYTABLE
:plan.startup_cost 0
:plan.total_cost 0.01
:plan.plan_rows 0
:plan.plan_width 0
:plan.parallel_aware false
:plan.parallel_safe false
:plan.async_capable false
:plan.plan_node_id 0
:plan.targetlist <>
:plan.qual <>
:plan.lefttree
{RESULT
:plan.startup_cost 0
:plan.total_cost 0.01
:plan.plan_rows 1
:plan.plan_width 552
:plan.parallel_aware false
:plan.parallel_safe false
:plan.async_capable false
:plan.plan_node_id 1
:plan.targetlist (
{TARGETENTRY
:expr
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 29
:constvalue 4 [ 0 0 0 0 0 0 0 0 ]
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{CONST
:consttype 1043
:consttypmod 260
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location -1
:constvalue 12 [ 48 0 0 0 106 111 104 110 32 100 111 101 ]
}
:resno 2
:resname name
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{CONST
:consttype 1043
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull true
:location -1
:constvalue <>
}
:resno 3
:resname job_title
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:plan.qual <>
:plan.lefttree <>
:plan.righttree <>
:plan.initPlan <>
:plan.extParam (b)
:plan.allParam (b)
:resconstantqual <>
}
:plan.righttree <>
:plan.initPlan <>
:plan.extParam (b)
:plan.allParam (b)
:operation 3
:canSetTag true
:nominalRelation 1
:rootRelation 0
:partColsUpdated false
:resultRelations (i 1)
:updateColnosLists <>
:withCheckOptionLists <>
:returningLists <>
:fdwPrivLists (<>)
:fdwDirectModifyPlans (b)
:rowMarks <>
:epqParam 0
:onConflictAction 0
:arbiterIndexes <>
:onConflictSet <>
:onConflictCols <>
:onConflictWhere <>
:exclRelRTI 0
:exclRelTlist <>
:mergeActionLists <>
}
:partPruneInfos <>
:rtable (
{RANGETBLENTRY
:alias <>
:eref
{ALIAS
:aliasname employee
:colnames ("id" "name" "job_title")
}
:rtekind 0
:relid 16393
:relkind r
:rellockmode 3
:tablesample <>
:perminfoindex 1
:lateral false
:inh false
:inFromCl false
:securityQuals <>
}
{RANGETBLENTRY
:alias <>
:eref
{ALIAS
:aliasname *RESULT*
:colnames <>
}
:rtekind 8
:lateral false
:inh false
:inFromCl false
:securityQuals <>
}
)
:permInfos (
{RTEPERMISSIONINFO
:relid 16393
:inh false
:requiredPerms 1
:checkAsUser 0
:selectedCols (b)
:insertedCols (b 8 9)
:updatedCols (b)
}
)
:resultRelations (i 1)
:appendRelations <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 16393)
:invalItems <>
:paramExecTypes (o 0)
:utilityStmt <>
:stmt_location 0
:stmt_len 43
}
)
なんかprintの動きが変なときあるなと思ってたらcはfflushなるものがあるのか