Skip to content

Instantly share code, notes, and snippets.

@ringerc

ringerc/gist:7413509

Last active December 28, 2015 00:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ringerc/7413509 to your computer and use it in GitHub Desktop.
Save ringerc/7413509 to your computer and use it in GitHub Desktop.
Download ZIP
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
Raw
gistfile1.txt
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