Horton

Re: WIP: Covering + unique indexes.

Jeff Janes <jeff.janes@gmail.com>

View thread View on postgr.es
From: Jeff Janes <jeff.janes@gmail.com>
To: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2016-02-25T18:39:38Z
Lists: pgsql-hackers 
On Thu, Feb 11, 2016 at 8:46 AM, Anastasia Lubennikova
<a.lubennikova@postgrespro.ru> wrote:
> 02.02.2016 15:50, Anastasia Lubennikova:

>
> As promised, here's the new version of the patch "including_columns_4.0".
> I fixed all issues except some points mentioned below.

Thanks for the update patch.  I get a compiler warning:

genam.c: In function 'BuildIndexValueDescription':
genam.c:259: warning: unused variable 'tupdesc'

Also, I can't create a primary key INCLUDING columns directly:

jjanes=# create table foobar (a int, b int, c int);
jjanes=# alter table foobar add constraint foobar_pkey primary key
(a,b) including (c);
ERROR:  syntax error at or near "including"

But I can get there using a circuitous route:

jjanes=# create unique index on foobar (a,b) including (c);
jjanes=# alter table foobar add constraint foobar_pkey primary key
using index foobar_a_b_c_idx;

The description of the table's index knows to include the including column:

jjanes=# \d foobar
    Table "public.foobar"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
 b      | integer | not null
 c      | integer |
Indexes:
    "foobar_pkey" PRIMARY KEY, btree (a, b) INCLUDING (c)


Since the machinery appears to all be in place to have primary keys
with INCLUDING columns, it would be nice if the syntax for adding
primary keys allowed one to implement them directly.

Is this something or future expansion, or could it be added at the
same time as the main patch?

I think this is something it would be pretty frustrating for the user
to be unable to do right from the start.

Cheers,

Jeff

Commits

  1. Adjust INCLUDE index truncation comments and code.

  2. Add commentary explaining why MaxIndexTuplesPerPage calculation is safe.

  3. Indexes with INCLUDE columns and their support in B-tree

  4. Add amcheck verification of heap relations belonging to btree indexes.

  5. Doc: move info for btree opclass implementors into main documentation.

  6. Doc: mention that you can't PREPARE TRANSACTION after NOTIFY.

  7. Remove dedicated B-tree root-split record types.

  8. Restructure index access method API to hide most of it at the C level.

  9. Split _bt_insertonpg to two functions.

  10. Major overhaul of btree index code. Eliminate special BTP_CHAIN logic for