Re: WIP: Covering + unique indexes.

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: http://www.postgrespro.com
The Russian Postgres Company