From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] [WIP] Effective storage of duplicates in B-tree index. |
Date: | 2019-07-11 17:20:17 |
Message-ID: | CAH2-Wz=7WU6O1dv941LaKLwoOS4k2G8xVCZG6gEHuYbp65E3mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Jul 11, 2019 at 7:30 AM Bruce Momjian <bruce(at)momjian(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2019-07-11 17:27:11 | Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status) |
Previous Message | Robert Haas | 2019-07-11 17:16:11 | Re: accounting for memory used for BufFile during hash joins |