MIME-Version: 1.0
References: 
 <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
 <CANxoLDffrZGRTGpW_sPQ-hPEYs0hgjaFgJQh3PJFpPu5Zsbgvg@mail.gmail.com>
 <177997571870.313758.10720313850275742354.pgcf@coridan.postgresql.org>
 <CANxoLDe7xiCWY-UEmzoK_uQdKh3PPNcGXJ3qdzFy3c0o_F+P_Q@mail.gmail.com>
 <SY7PR01MB1092118DD149062E230159D89B6162@SY7PR01MB10921.ausprd01.prod.outlook.com>
 <CAF0whuefRgV3_xgMhPoKaid1LN34CTLhTU-r5dgfFyEGVAFrPg@mail.gmail.com>
 <CANxoLDfBQn+q32DKo5yvb63eD+965umMi8zxqqV8GcsxdN6Trg@mail.gmail.com>
 <CANxoLDf80gXFLSqWM7d-idA=Grot49HsmP9YXZw49nCFe+Mi+g@mail.gmail.com>
 <CANxoLDeqR6wVCoXjsEWv4M_mhng-GCtt1KxFdfM-8PKwushUCA@mail.gmail.com>
 <CANxoLDcQyd=ojcmLCmU5zFT8j475AbXHpjSWHoVP4a5-vdY3Jw@mail.gmail.com>
 <CAF0whue4v=bH8beH0sBxu9UoFKRcZHTW-zH0qeVEt0jW2x7JiQ@mail.gmail.com>
 <CANxoLDd+Pfi=Do-+1ECG-eZg4=DXfxuUK+yX1KhjAMnQzyidwQ@mail.gmail.com>
 <CAF0whuc22Tdf-V=1g21jgmKG0YiGk3ZhLoO7Ttog6tD5P91a2Q@mail.gmail.com>
 <CANxoLDfUeoh+X1AeazXAQjYL312nTa6bY+=iPNJjmN4bXwSKWw@mail.gmail.com>
In-Reply-To: 
 <CANxoLDfUeoh+X1AeazXAQjYL312nTa6bY+=iPNJjmN4bXwSKWw@mail.gmail.com>
From: Rui Zhao <zhaorui126@gmail.com>
Date: Thu, 9 Jul 2026 00:58:10 +0800
Message-ID: 
 <CAHWVJhEpUPQkR0rkeTz88Z06aLxfxrv8-OiAt6Tmhd2q+HTBrA@mail.gmail.com>
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE
 POLICY statement
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: solai v <solai.cdac@gmail.com>, Ilmar Y <tanswis42@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHWVJhEpUPQkR0rkeTz88Z06aLxfxrv8-OiAt6Tmhd2q%2BHTBrA%40mail.gmail.com>
Precedence: bulk

Hi Akshay,

I tested v15 on current master (57f93af36f): builds, make check passes, and
basic reconstruction, pretty mode, NULL handling, and default-clause omission
all look good. Two things.

1) Back in the v3/v4 discussion you decided the ON <table> name should always
be schema-qualified for safety (the pg_get_triggerdef_worker thread with
jian he), and that pretty here only controls formatting, not schema (Phil's
point). That reasoning didn't reach the USING / WITH CHECK expressions,
though: object references inside them are still qualified only by the caller's
search_path, so they can lose their schema and rebind to a different object
when the DDL is replayed elsewhere:

    CREATE FUNCTION s1.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 > 0';
    CREATE FUNCTION s2.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 < 0';
    CREATE POLICY pf ON t2 USING (s1.f(a));

    SET search_path = public, s1;
    SELECT ddl FROM pg_get_policy_ddl('t2', 'pf') AS ddl;
    --  CREATE POLICY pf ON public.t2 USING (f(a));      -- s1. dropped

    SET search_path = public, s2;
    CREATE POLICY pf ON t2 USING (f(a));                 -- now s2.f,
opposite meaning

So within one statement the ON clause is always qualified but the expression
body isn't -- and as Phil noted, the pretty flag can't fix this, since
pg_get_expr qualifies by search_path visibility regardless of pretty.

Worth settling the contract, given where this function sits. The old
pg_get_viewdef / ruledef / indexdef functions are search_path-aware and leave
qualification to the caller (pg_dump sets search_path empty around them so the
output is portable). The new pg_get_*_ddl functions committed so far (role,
database, tablespace) are on global, schemaless objects, so the question never
came up -- pg_get_policy_ddl is the first of the family whose output embeds
schema-qualifiable references. Your own pg_get_table_ddl patch already hit
this and deparses under a controlled search_path (narrowed to pg_catalog), so
the most consistent fix is to do the same here (NewGUCNestLevel + set_config);
an empty search_path already yields the fully-qualified s1.f(a). If instead
the intent is to follow the caller's search_path, that's fine too, but it
should be documented (and SET search_path = '' noted as the way to get
portable DDL).

2) The doc calls the second parameter policy_name, but the actual argument is
policyname, so the documented named-argument call fails:

    SELECT * FROM pg_get_policy_ddl("table" => 't'::regclass,
policy_name => 'p_all');
    ERROR:  function pg_get_policy_ddl(table => regclass, policy_name
=> unknown) does not exist

Regards,
Rui