Re: WIP: Covering + unique indexes.

25.01.2016 03:32, Jeff Janes:
> On Fri, Jan 22, 2016 at 7:19 AM, Anastasia Lubennikova
> wrote:
>> Done. I hope that my patch is close to the commit too.
>>
> Thanks for the update.
>
> I've run into this problem:
>
> create table foobar (x text, w text);
> create unique index foobar_pkey on foobar (x) including (w);
> alter table foobar add constraint foobar_pkey primary key using index
> foobar_pkey;
>
> ERROR: index "foobar_pkey" does not have default sorting behavior
> LINE 1: alter table foobar add constraint foobar_pkey primary key us...
> ^
> DETAIL: Cannot create a primary key or unique constraint using such an index.
> Time: 1.577 ms
>
>
> If I instead define the table as
> create table foobar (x int, w xml);
>
> Then I can create the index and then the primary key the first time I
> do this in a session. But then if I drop the table and repeat the
> process, I get "does not have default sorting behavior" error even for
> this index that previously succeeded, so I think there is some kind of
> problem with the backend syscache or catcache.
>
> create table foobar (x int, w xml);
> create unique index foobar_pkey on foobar (x) including (w);
> alter table foobar add constraint foobar_pkey primary key using index
> foobar_pkey;
> drop table foobar ;
> create table foobar (x int, w xml);
> create unique index foobar_pkey on foobar (x) including (w);
> alter table foobar add constraint foobar_pkey primary key using index
> foobar_pkey;
> ERROR: index "foobar_pkey" does not have default sorting behavior
> LINE 1: alter table foobar add constraint foobar_pkey primary key us...
> ^
> DETAIL: Cannot create a primary key or unique constraint using such an index.

Great, I've fixed that. Thank you for the tip about cache.

I've also found and fixed related bug in copying tables with indexes:
create table tbl2 (like tbl including all);
And there's one more tiny fix in get_pkey_attnames in dblink module.

including_columns_3.0 is the latest version of patch.
And changes regarding the previous version are attached in a separate
patch. Just to ease the review and debug.

I've changed size of pg_index.indclass array. It contains indnkeyatts
elements now.
While pg_index.indkey still contains all attributes. And this query
Retrieve primary key columns
<https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns> provides
pretty non-obvious result. Is it a normal behavior here or some changes
are required? Do you know any similar queries?

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