Thread: pgsql: When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan

pgsql: When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan

From
tgl@postgresql.org (Tom Lane)
Date:
28 October 2009, 11:56:02
Log Message:
-----------
When FOR UPDATE/SHARE is used with LIMIT, put the LockRows plan node
underneath the Limit node, not atop it.  This fixes the old problem that such
a query might unexpectedly return fewer rows than the LIMIT says, due to
LockRows discarding updated rows.

There is a related problem that LockRows might destroy the sort ordering
produced by earlier steps; but fixing that by pushing LockRows below Sort
would create serious performance problems that are unjustified in many
real-world applications, as well as potential deadlock problems from locking
many more rows than expected.  Instead, keep the present semantics of applying
FOR UPDATE after ORDER BY within a single query level; but allow the user to
specify the other way by writing FOR UPDATE in a sub-select.  To make that
work, track whether FOR UPDATE appeared explicitly in sub-selects or got
pushed down from the parent, and don't flatten a sub-select that contained an
explicit FOR UPDATE.

Modified Files:
--------------
    pgsql/doc/src/sgml/ref:
        select.sgml (r1.127 -> r1.128)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/select.sgml?r1=1.127&r2=1.128)
    pgsql/src/backend/nodes:
        copyfuncs.c (r1.449 -> r1.450)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/copyfuncs.c?r1=1.449&r2=1.450)
        equalfuncs.c (r1.371 -> r1.372)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/equalfuncs.c?r1=1.371&r2=1.372)
        outfuncs.c (r1.370 -> r1.371)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/outfuncs.c?r1=1.370&r2=1.371)
        readfuncs.c (r1.226 -> r1.227)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/nodes/readfuncs.c?r1=1.226&r2=1.227)
    pgsql/src/backend/optimizer/plan:
        planner.c (r1.260 -> r1.261)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/planner.c?r1=1.260&r2=1.261)
    pgsql/src/backend/optimizer/prep:
        prepjointree.c (r1.68 -> r1.69)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/prep/prepjointree.c?r1=1.68&r2=1.69)
    pgsql/src/backend/parser:
        analyze.c (r1.394 -> r1.395)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/parser/analyze.c?r1=1.394&r2=1.395)
    pgsql/src/backend/rewrite:
        rewriteHandler.c (r1.189 -> r1.190)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/rewrite/rewriteHandler.c?r1=1.189&r2=1.190)
    pgsql/src/backend/utils/adt:
        ruleutils.c (r1.310 -> r1.311)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c?r1=1.310&r2=1.311)
    pgsql/src/include/catalog:
        catversion.h (r1.547 -> r1.548)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/catversion.h?r1=1.547&r2=1.548)
    pgsql/src/include/nodes:
        parsenodes.h (r1.411 -> r1.412)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/parsenodes.h?r1=1.411&r2=1.412)
    pgsql/src/include/parser:
        analyze.h (r1.42 -> r1.43)
        (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/parser/analyze.h?r1=1.42&r2=1.43)