Re: WIP: Covering + unique indexes.

From: Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: Covering + unique indexes.
Date: 2016-03-01 17:10:20
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

01.03.2016 19:55, Anastasia Lubennikova:
> 29.02.2016 18:17, Anastasia Lubennikova:
>> 25.02.2016 21:39, Jeff Janes:
>>>> 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'
>> Thank you for the notice, I'll fix it in the next update.
>>> 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?
>> Good point.
>> At quick glance, this looks easy to implement it. The only problem is
>> that there are too many places in code which must be updated.
>> I'll try to do it, and if there would be difficulties, it's fine with
>> me to delay this feature for the future work.
>> I found one more thing to do. Pgdump does not handle included columns
>> now. I will fix it in the next version of the patch.
> As promised, fixed patch is in attachments. It allows to perform
> following statements:
> create table utbl (a int, b box);
> alter table utbl add unique (a) including(b);
> create table ptbl (a int, b box);
> alter table ptbl add primary key (a) including(b);
> And now they can be dumped/restored successfully.
> I used following settings
> pg_dump --verbose -Fc postgres -f pg.dump
> pg_restore -d newdb pg.dump
> It is not the final version, because it breaks pg_dump for previous
> versions. I need some help from hackers here.
> pgdump. line 5466
> if (fout->remoteVersion >= 90400)
> What does 'remoteVersion' mean? And what is the right way to change
> it? Or it changes between releases?
> I guess that 90400 is for 9.4 and 80200 is for 8.2 but is it really
> so? That is totally new to me.
> BTW, While we are on the subject, maybe it's worth to replace these
> magic numbers with some set of macro?
> P.S. I'll update documentation for ALTER TABLE in the next patch.

Sorry for missed attachment. Now it's here.

Anastasia Lubennikova
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
including_columns_5.0.patch text/x-patch 91.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2016-03-01 17:13:28 Re: Confusing with commit time usage in logical decoding
Previous Message Petr Jelinek 2016-03-01 17:09:28 Re: Confusing with commit time usage in logical decoding