opt: only set volatile for scans that perform locking

This commit makes a minor change to the logical properties for scan
operators. The volatile property is now only set for a scan that takes
locks, rather than any scan with non-default locking.

Epic: None

Release note: None

opt: set volatility for locking operators

This commit sets the volatile property for operators that perform locking,
other than Scan, which was already handled. In most cases there is already
a locking scan below these operators, so there are few plan changes. This
is necessary for a following commit, which will cause Lock operators to
no longer be considered mutation operators (which was previously causing
volatility to be set).

Epic: None

Release note: None

opt: remove Mutation tag from lock operators

This commit removes the Mutation tag from the optimizer lock operator.
As a result, errors that result from checking opt.IsMutationOp or
planFlagContainsMutation will no longer incorrectly occur for plans
that contain lock operators.

Fixes #141961

Release note: None

plpgsql: don't make cursors with locking holdable

This commit changes the close_cursors_at_commit setting to be more
Oracle-compatible. Now, disabling the setting will cause cursors to
remain open after txn commit only for cursors without locking.

This commit also removes the Mutation tag from the optimizer Lock
operator. This is necessary to prevent PL/pgSQL cursors with locking
from returning a must not contain data-modifying statements error
under configurations like read-committed which use lock operators.
Lock operators are equivalent to lookup joins with Locking set, so
the change is correct.

Informs #77101

Release note: None