Last active
December 28, 2015 00:29
-
-
Save ringerc/7413509 to your computer and use it in GitHub Desktop.
PostgreSQL parse, rewrite, and plan trees for a `delete ... using statement` in Pg 9.3; see https://wiki.postgresql.org/wiki/Automatically_updatable_security_barrier_views#How_does_updating_a_join_work.3F
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| regress=> DELETE FROM t USING t2 WHERE t.id = t2.id; | |
| LOG: parse tree: | |
| DETAIL: {QUERY | |
| :commandType 4 | |
| :querySource 0 | |
| :canSetTag true | |
| :utilityStmt <> | |
| :resultRelation 1 | |
| :hasAggs false | |
| :hasWindowFuncs false | |
| :hasSubLinks false | |
| :hasDistinctOn false | |
| :hasRecursive false | |
| :hasModifyingCTE false | |
| :hasForUpdate false | |
| :cteList <> | |
| :rtable ( | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16387 | |
| :relkind r | |
| :lateral false | |
| :inh true | |
| :inFromCl false | |
| :requiredPerms 10 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t2 | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16402 | |
| :relkind r | |
| :lateral false | |
| :inh true | |
| :inFromCl true | |
| :requiredPerms 2 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| ) | |
| :jointree | |
| {FROMEXPR | |
| :fromlist ( | |
| {RANGETBLREF | |
| :rtindex 1 | |
| } | |
| {RANGETBLREF | |
| :rtindex 2 | |
| } | |
| ) | |
| :quals | |
| {OPEXPR | |
| :opno 96 | |
| :opfuncid 65 | |
| :opresulttype 16 | |
| :opretset false | |
| :opcollid 0 | |
| :inputcollid 0 | |
| :args ( | |
| {VAR | |
| :varno 1 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno 1 | |
| :location 29 | |
| } | |
| {VAR | |
| :varno 2 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno 1 | |
| :location 36 | |
| } | |
| ) | |
| :location 34 | |
| } | |
| } | |
| :targetList <> | |
| :returningList <> | |
| :groupClause <> | |
| :havingQual <> | |
| :windowClause <> | |
| :distinctClause <> | |
| :sortClause <> | |
| :limitOffset <> | |
| :limitCount <> | |
| :rowMarks <> | |
| :setOperations <> | |
| :constraintDeps <> | |
| } | |
| LOG: rewritten parse tree: | |
| DETAIL: ( | |
| {QUERY | |
| :commandType 4 | |
| :querySource 0 | |
| :canSetTag true | |
| :utilityStmt <> | |
| :resultRelation 1 | |
| :hasAggs false | |
| :hasWindowFuncs false | |
| :hasSubLinks false | |
| :hasDistinctOn false | |
| :hasRecursive false | |
| :hasModifyingCTE false | |
| :hasForUpdate false | |
| :cteList <> | |
| :rtable ( | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16387 | |
| :relkind r | |
| :lateral false | |
| :inh true | |
| :inFromCl false | |
| :requiredPerms 10 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t2 | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16402 | |
| :relkind r | |
| :lateral false | |
| :inh true | |
| :inFromCl true | |
| :requiredPerms 2 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| ) | |
| :jointree | |
| {FROMEXPR | |
| :fromlist ( | |
| {RANGETBLREF | |
| :rtindex 1 | |
| } | |
| {RANGETBLREF | |
| :rtindex 2 | |
| } | |
| ) | |
| :quals | |
| {OPEXPR | |
| :opno 96 | |
| :opfuncid 65 | |
| :opresulttype 16 | |
| :opretset false | |
| :opcollid 0 | |
| :inputcollid 0 | |
| :args ( | |
| {VAR | |
| :varno 1 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno 1 | |
| :location 29 | |
| } | |
| {VAR | |
| :varno 2 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno 1 | |
| :location 36 | |
| } | |
| ) | |
| :location 34 | |
| } | |
| } | |
| :targetList ( | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 1 | |
| :varattno -1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 1 | |
| :resname ctid | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk true | |
| } | |
| ) | |
| :returningList <> | |
| :groupClause <> | |
| :havingQual <> | |
| :windowClause <> | |
| :distinctClause <> | |
| :sortClause <> | |
| :limitOffset <> | |
| :limitCount <> | |
| :rowMarks <> | |
| :setOperations <> | |
| :constraintDeps <> | |
| } | |
| ) | |
| LOG: plan: | |
| DETAIL: {PLANNEDSTMT | |
| :commandType 4 | |
| :queryId 0 | |
| :hasReturning false | |
| :hasModifyingCTE false | |
| :canSetTag true | |
| :transientPlan false | |
| :planTree | |
| {MODIFYTABLE | |
| :startup_cost 1.23 | |
| :total_cost 28.76 | |
| :plan_rows 62 | |
| :plan_width 12 | |
| :targetlist <> | |
| :qual <> | |
| :lefttree <> | |
| :righttree <> | |
| :initPlan <> | |
| :extParam (b) | |
| :allParam (b) | |
| :operation 4 | |
| :canSetTag true | |
| :resultRelations (i 1) | |
| :resultRelIndex 0 | |
| :plans ( | |
| {HASHJOIN | |
| :startup_cost 1.23 | |
| :total_cost 28.76 | |
| :plan_rows 62 | |
| :plan_width 12 | |
| :targetlist ( | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 65000 | |
| :varattno 1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 1 | |
| :resname ctid | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk true | |
| } | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 65001 | |
| :varattno 1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 2 | |
| :resname ctid1 | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk true | |
| } | |
| ) | |
| :qual <> | |
| :lefttree | |
| {SEQSCAN | |
| :startup_cost 0.00 | |
| :total_cost 22.30 | |
| :plan_rows 1230 | |
| :plan_width 10 | |
| :targetlist ( | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 2 | |
| :varattno -1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 1 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 2 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno 1 | |
| :location 36 | |
| } | |
| :resno 2 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| ) | |
| :qual <> | |
| :lefttree <> | |
| :righttree <> | |
| :initPlan <> | |
| :extParam (b 0) | |
| :allParam (b 0) | |
| :scanrelid 2 | |
| } | |
| :righttree | |
| {HASH | |
| :startup_cost 1.10 | |
| :total_cost 1.10 | |
| :plan_rows 10 | |
| :plan_width 10 | |
| :targetlist ( | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 65001 | |
| :varattno 1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 1 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 65001 | |
| :varattno 2 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno 1 | |
| :location -1 | |
| } | |
| :resno 2 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| ) | |
| :qual <> | |
| :lefttree | |
| {SEQSCAN | |
| :startup_cost 0.00 | |
| :total_cost 1.10 | |
| :plan_rows 10 | |
| :plan_width 10 | |
| :targetlist ( | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 1 | |
| :varattno -1 | |
| :vartype 27 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno -1 | |
| :location -1 | |
| } | |
| :resno 1 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| {TARGETENTRY | |
| :expr | |
| {VAR | |
| :varno 1 | |
| :varattno 1 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno 1 | |
| :location 29 | |
| } | |
| :resno 2 | |
| :resname <> | |
| :ressortgroupref 0 | |
| :resorigtbl 0 | |
| :resorigcol 0 | |
| :resjunk false | |
| } | |
| ) | |
| :qual <> | |
| :lefttree <> | |
| :righttree <> | |
| :initPlan <> | |
| :extParam (b 0) | |
| :allParam (b 0) | |
| :scanrelid 1 | |
| } | |
| :righttree <> | |
| :initPlan <> | |
| :extParam (b 0) | |
| :allParam (b 0) | |
| :skewTable 16402 | |
| :skewColumn 1 | |
| :skewInherit false | |
| :skewColType 23 | |
| :skewColTypmod -1 | |
| } | |
| :initPlan <> | |
| :extParam (b 0) | |
| :allParam (b 0) | |
| :jointype 0 | |
| :joinqual <> | |
| :hashclauses ( | |
| {OPEXPR | |
| :opno 96 | |
| :opfuncid 65 | |
| :opresulttype 16 | |
| :opretset false | |
| :opcollid 0 | |
| :inputcollid 0 | |
| :args ( | |
| {VAR | |
| :varno 65001 | |
| :varattno 2 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 2 | |
| :varoattno 1 | |
| :location 36 | |
| } | |
| {VAR | |
| :varno 65000 | |
| :varattno 2 | |
| :vartype 23 | |
| :vartypmod -1 | |
| :varcollid 0 | |
| :varlevelsup 0 | |
| :varnoold 1 | |
| :varoattno 1 | |
| :location 29 | |
| } | |
| ) | |
| :location -1 | |
| } | |
| ) | |
| } | |
| ) | |
| :returningLists <> | |
| :fdwPrivLists (<>) | |
| :rowMarks ( | |
| {PLANROWMARK | |
| :rti 2 | |
| :prti 2 | |
| :rowmarkId 1 | |
| :markType 4 | |
| :noWait false | |
| :isParent false | |
| } | |
| ) | |
| :epqParam 0 | |
| } | |
| :rtable ( | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16387 | |
| :relkind r | |
| :lateral false | |
| :inh false | |
| :inFromCl false | |
| :requiredPerms 10 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| {RTE | |
| :alias <> | |
| :eref | |
| {ALIAS | |
| :aliasname t2 | |
| :colnames ("id" "secret") | |
| } | |
| :rtekind 0 | |
| :relid 16402 | |
| :relkind r | |
| :lateral false | |
| :inh false | |
| :inFromCl true | |
| :requiredPerms 2 | |
| :checkAsUser 0 | |
| :selectedCols (b 9) | |
| :modifiedCols (b) | |
| } | |
| ) | |
| :resultRelations (i 1) | |
| :utilityStmt <> | |
| :subplans <> | |
| :rewindPlanIDs (b) | |
| :rowMarks ( | |
| {PLANROWMARK | |
| :rti 2 | |
| :prti 2 | |
| :rowmarkId 1 | |
| :markType 4 | |
| :noWait false | |
| :isParent false | |
| } | |
| ) | |
| :relationOids (o 16387 16402) | |
| :invalItems <> | |
| :nParamExec 1 | |
| } | |
| DELETE 10 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment