On Thu, Jul 11, 2019 at 7:30 AM Bruce Momjian <bruce@momjian.us> wrote:
> Wow, I never thought of that. The only things I know we lock until
> transaction end are rows we update (against concurrent updates), and
> additions to unique indexes. By definition, indexes with many
> duplicates are not unique, so that doesn't apply.
Right. Another advantage of their approach is that you can make
queries like this work:
UPDATE tab SET unique_col = unique_col + 1
This will not throw a unique violation error on most/all other DB
systems when the updated column (in this case "unique_col") has a
unique constraint/is the primary key. This behavior is actually
required by the SQL standard. An SQL statement is supposed to be
all-or-nothing, which Postgres doesn't quite manage here.
The section "6.6 Interdependencies of Transactional Storage" from the
paper "Architecture of a Database System" provides additional
background information (I should have suggested reading both 6.6 and
6.7 together).
--
Peter Geoghegan