-- appropriate key description (or none) in various situations

create table key_desc (a int, b int) partition by list ((a+0));

create table key_desc_1 partition of key_desc for values in (1) partition by range (b);

create usersomeone_else;

grant select (a) on key_desc_1 tosomeone_else;

grant insert on key_desc tosomeone_else;

set rolesomeone_else;

create userregress_insert_other_user;

grant select (a) on key_desc_1 toregress_insert_other_user;

grant insert on key_desc toregress_insert_other_user;

set roleregress_insert_other_user;

-- no key description is shown

insert into key_desc values (1, 1);

ERROR: no partition of relation "key_desc_1" found for row

reset role;

grant select (b) on key_desc_1 tosomeone_else;

set rolesomeone_else;

grant select (b) on key_desc_1 toregress_insert_other_user;

set roleregress_insert_other_user;

-- key description (b)=(1) is now shown

insert into key_desc values (1, 1);

ERROR: no partition of relation "key_desc_1" found for row

insert into key_desc values (2, 1);

ERROR: no partition of relation "key_desc" found for row

reset role;

revoke all on key_desc fromsomeone_else;

revoke all on key_desc_1 fromsomeone_else;

drop rolesomeone_else;

revoke all on key_desc fromregress_insert_other_user;

revoke all on key_desc_1 fromregress_insert_other_user;

drop roleregress_insert_other_user;

drop table key_desc, key_desc_1;

-- test minvalue/maxvalue restrictions

create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);

createtablekey_desc (aint, bint) partition by list ((a+0));

createtablekey_desc_1 partition of key_desc forvaluesin (1) partition by range (b);

createusersomeone_else;

grantselect (a)on key_desc_1 tosomeone_else;

grant inserton key_desc tosomeone_else;

createuserregress_insert_other_user;

grantselect (a)on key_desc_1 toregress_insert_other_user;

grant inserton key_desc toregress_insert_other_user;

set rolesomeone_else;

set roleregress_insert_other_user;

insert into key_descvalues (1,1);

reset role;

grantselect (b)on key_desc_1 tosomeone_else;

set rolesomeone_else;

grantselect (b)on key_desc_1 toregress_insert_other_user;

set roleregress_insert_other_user;

insert into key_descvalues (1,1);

insert into key_descvalues (2,1);

reset role;

revoke allon key_descfromsomeone_else;

revoke allon key_desc_1fromsomeone_else;

drop rolesomeone_else;

revoke allon key_descfromregress_insert_other_user;

revoke allon key_desc_1fromregress_insert_other_user;

drop roleregress_insert_other_user;

droptable key_desc, key_desc_1;