david.g.johnston@xxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: I explained that the bug doesn't allow a short testcase because there are many degrees of freedom and you don't know, before trying them all, what pattern will emerge. It would be different if I could read a clear statement of expected behavior. But you've already said that there is none. The account of my GitHub issue includes a preliminary test that shows that there's something to investigate further. I copied it below. Please tell me if it meets your succinctness criteria. If it does, then I'll submit it to the psql-bugs list as you suggest. Notice, though, that, counting from « the testcase proper », it's ~70 lines long. Moreover, it uses five different non-super users, all of whom can use and create in a schema whose name doesn't matter but that must be first in each user's search_path. That prelude (« drop and re-create the "bryn" database and the five users that the testcase needs ») is ~45 lines long. It produces this output: current_role, table, operation, v: client, masters, INSERT, Mary current_role, table, operation, v: client, details, INSERT, shampoo current_role, table, operation, v: client, details, INSERT, soap current_role, table, operation, v: client, details, DELETE, soap current_role, table, operation, v: client, masters, DELETE, Mary current_role, table, operation, v: d_owner, details, DELETE, shampoo I've attached it as "t.zip". It unzips to the single file "t.sql". This can be run and re-run time and again. I've proved to myself (again) that I can send from my "bryn@xxxxxxxxxxxx" to other users with various email domains (like "icloud.com" and "gmail.com"). But I've done nothing to try to solve why my attachments don't make it to "pgsql-general@xxxxxxxxxxxxxxxxxxxx". Please tell me if you get it at your "gmail.com" address. |
<<attachment: t.zip>>
-------------------------------------------------------------------------------- \c postgres postgres set client_min_messages = warning; drop database if exists bryn; create database bryn owner postgres; \c bryn postgres set client_min_messages = warning; revoke all on database bryn from public; drop schema public cascade; create schema s authorization postgres; create procedure s.create_role(name in text, can_create in boolean = false) security invoker set search_path = pg_catalog, pg_temp language plpgsql as $body$ begin begin execute format('drop owned by %I cascade', name); exception when undefined_object then null; end; execute format('drop role if exists %I', name); execute format('create role %I login password ''p''', name); execute format('grant connect on database bryn to %I', name); execute format('grant usage on schema s to %I', name); execute format('alter user %I set search_path = s, pg_catalog, pg_temp', name); if can_create then execute format('grant create on database bryn to %I', name); execute format('grant create on schema s to %I', name); end if; end; $body$; call s.create_role('m_owner', true); call s.create_role('d_owner', true); call s.create_role('trg_functions', true); call s.create_role('trg_creator', true); call s.create_role('client' ); -------------------------------------------------------------------------------- -- THE TESTCASE PROPER. \c bryn m_owner create table masters( mk serial primary key, v text not null unique); grant all on table masters to public; grant all on sequence masters_mk_seq to public; \c bryn d_owner create table details( mk int, dk serial, v text not null unique, constraint details_pk primary key(mk, dk), constraint details_fk foreign key(mk) references masters(mk) on delete cascade); grant all on table details to public; grant all on sequence details_dk_seq to public; \c bryn trg_functions create function trg_fn() returns trigger security invoker set search_path = s, pg_catalog, pg_temp language plpgsql as $body$ declare vv constant text not null := case tg_op when 'INSERT' then new.v when 'DELETE' then old.v end; begin raise info 'current_role, table, operation, v: %, %, %, %', current_role, tg_table_name, tg_op, vv; return case tg_op when 'INSERT' then new when 'DELETE' then old end; end; $body$; grant all on function trg_fn() to public; \c bryn trg_creator create trigger masters_trg before insert or delete on masters for each row execute function trg_fn(); create trigger details_trg before insert or delete on details for each row execute function trg_fn(); \c bryn client do $body$ declare new_mk int not null := 0; begin insert into masters(v) values('Mary') returning mk into new_mk; insert into details(mk, v) values(new_mk, 'shampoo'); insert into details(mk, v) values(new_mk, 'soap'); end; $body$; delete from details where v = 'soap'; delete from masters where v = 'Mary'; |
