25.01.2016 03:32, Jeff Janes:
On Fri, Jan 22, 2016 at 7:19 AM, Anastasia Lubennikova
<a.lubennikova@postgrespro.ru> 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 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
