Re: Avoid lost result of recursion (src/backend/optimizer/util/inherit.c) - Mailing list pgsql-hackers

On Fri, 23 Dec 2022 at 15:21, Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Thu, Dec 22, 2022 at 5:22 PM David Rowley <dgrowleyml@gmail.com> wrote:
>> I still think we should have a test to ensure this is actually
>> working. Do you want to write one?
>
>
> I agree that we should have a test.  According to the code coverage
> report, the recursion part of this function is never tested.  I will
> have a try to see if I can come up with a proper test case.

I started having a go at writing one yesterday. I only got as far as
the following.
It looks like it'll need a trigger or something added to the foreign
table to hit the code path that would be needed to trigger the issue,
so it'll need more work. It might be a worthy starting point.

CREATE EXTENSION postgres_fdw;

-- this will need to work the same way as it does in postgres_fdw.sql
by using current_database()
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS
(dbname 'postgres', port '5432');

create table t_gc (a int, b int, c int);
create table t_c (b int, c int, a int) partition by list(a);
create table t_tlp (c int, a int, b int) partition by list (a);

CREATE FOREIGN TABLE ft_tlp (
c int,
a int,
b int
) SERVER loopback OPTIONS (schema_name 'public', table_name 't_tlp');


alter table t_c attach partition t_gc for values in (1);
alter table t_tlp attach partition t_c for values in (1);

create role perm_check login;

CREATE USER MAPPING FOR perm_check SERVER loopback OPTIONS (user
'perm_check', password_required 'false');

grant update (b) on t_tlp to perm_check;
grant update (b) on ft_tlp to perm_check;

set session authorization perm_check;

-- should pass
update ft_tlp set b = 1;

-- should fail
update ft_tlp set a = 1;
update ft_tlp set c = 1;

-- cleanup

drop foreign table ft_tlp cascade;
drop table t_tlp cascade;
drop role perm_check;
drop server loopback cascade;
drop extension postgres_fdw;

David