Subject: Re: WIP: Covering + unique indexes.
To: pgsql-hackers@postgresql.org
References: <56167F09.3090101@postgrespro.ru>
 <CAMkU=1wZ8-mNHNMuG3BeMJkXxPyRJFNqbNnc8CaXMGxpciv8aQ@mail.gmail.com>
 <CAKJS1f9W0aB-g7H6yYgNBq7hJsOKF3UwHU7-Q5jobbaTyK9f4g@mail.gmail.com>
 <569530FC.2010900@postgrespro.ru>
 <CAKJS1f_GsQRQGYwUfBkL8H5ar4LW0sQ24cyZBdKAhFpG2jEsdw@mail.gmail.com>
 <56965808.7050004@postgrespro.ru>
 <CAKJS1f8Fbd69Y0qONSkn9q7-2VhzDVhodFXKdNiPDdpRO0p35A@mail.gmail.com>
 <CAKJS1f-scJKh1wTariUfdOwrPNwcr8o1N6Jo1-JsGi0bZiT+0Q@mail.gmail.com>
 <569E6D77.3080700@postgrespro.ru>
 <CAKJS1f9wejUeakn-nD+i1H_t4ZQZxvtuXs2EpeuinykPo-oUeA@mail.gmail.com>
 <56A24885.7050706@postgrespro.ru>
 <CAMkU=1zqOqj_pt73H+bXJaz-wkWpJ=j-ZMmrptoO8o4Mhe_3MA@mail.gmail.com>
 <56A78433.3050303@postgrespro.ru>
 <CAKJS1f__-i3eOc8pD6WFODDFvPrMBhe4o3KE0kokFWy+AQrStQ@mail.gmail.com>
 <56B0A61A.2030808@postgrespro.ru> <56BCBAE0.2080102@postgrespro.ru>
 <CAMkU=1zAr_jDAEk8QoxaMbGM=mdjbqGnhJqJzL+s1tVs5yREpQ@mail.gmail.com>
 <56D46114.9090205@postgrespro.ru>
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Message-ID: <56D5C970.6020002@postgrespro.ru>
Date: Tue, 1 Mar 2016 19:55:12 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.5.1
MIME-Version: 1.0
In-Reply-To: <56D46114.9090205@postgrespro.ru>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


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.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers