MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
 <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
 <DEFNP3WX7BLX.1X7DS1P9X6JIF@nmfay.com>
 <CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
 <DELPC4B56Y5A.3OBZ3WCH3T4VX@nmfay.com>
 <CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
 <CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
 <CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
 <CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
 <CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
 <CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
 <CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
 <CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
 <CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
 <CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
 <CA+TgmoaZMOikxK=LqS+Jn+835h9S139JLGk-3LyETVXw5W5j=w@mail.gmail.com>
 <1136161.1769654478@sss.pgh.pa.us>
 <CAP53PkwZ1ZTMARKg6iEfAw9qzBhkjBitj-9gr_Jvy7k2AwGgWA@mail.gmail.com>
 <CAMbWs4--NuEUFE_xTo991TRXaZryE29jarJPDnVxoaQOYdt7tA@mail.gmail.com>
 <CA+TgmobzR+XMGbRosVPbjHbSo4+cgJn=qZK6w05aF1sbj=C+9Q@mail.gmail.com>
 <CA+TgmoawzvCoZAwFS85tE5+c8vBkqgcS8ZstQ_ohjXQ9wGT9sw@mail.gmail.com>
 <CA+TgmoYS4ZCVAF2jTce=bMP0Oq_db_srocR4cZyO0OBp9oUoGg@mail.gmail.com>
 <CAK98qZ2RzbgCHrSg4zLkvpzyBam_X6te-KF8w1+_vON9BAVMEw@mail.gmail.com>
 <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
 <CAK98qZ1zWzRB0ABG7ULzTeWKRR5C7-FxLqM-6v8wQDiFM+DNAg@mail.gmail.com>
 <CA+Tgmob7ozJAs5SU6bD2RfAt4w_AmsMGz-aaVA6WeLXHkBypOg@mail.gmail.com>
 <CAK98qZ1J42RoAsHnYWGPPmHziZmzmqE=Lp_O6WJ-9aKK2qjikA@mail.gmail.com>
 <CA+TgmoYjcBA6dw3nwiyfDzPXTCrxTZPXDMrc2TrDJcL1cPK6iA@mail.gmail.com>
 <CA+TgmoYru-vxoTKfwjQby30r2OkTXfb18Km_=VLs6qk8Akr0-g@mail.gmail.com>
 <CAKFQuwYxTMx1e1dvVLd5vs8CO2Xe__YRCW0m4v2UErG=5Lu7kQ@mail.gmail.com>
 <CA+TgmoaPH3rOmH1zw8s10Az3TQKRbDnF3dKYZCAFvx_DgFV5_Q@mail.gmail.com>
 <CAKFQuwZ7CRbvmJ60EjvEUJP8X9hWVCHJz8yJZ0-xRpYwL=fugA@mail.gmail.com>
 <CA+TgmoYsG2pAOG4YYWwnGsaL+QfOZopi_m+f_hZqBZ3meNEkjg@mail.gmail.com>
 <CAKFQuwY4i6xXCRHd=ccrFz0sQOfiWRxhobiBrzyK0rzu1-Y33Q@mail.gmail.com>
 <CA+Tgmobbj_TaCsYmr1grJBTRKaFaxFfotXn1T6LSXs9GQ8_Kyw@mail.gmail.com>
 <CA+TgmoZzM2i+p-Rxdphs4qx7sshn-kzxF91ASQ5duOo0dFRXLQ@mail.gmail.com>
 <CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
In-Reply-To: 
 <CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Wed, 18 Mar 2026 13:07:54 -0400
Message-ID: 
 <CA+TgmoYRJdDh7MPK-cLs4w4gmYqEaWM3Y0o+2-YAKfJ=5rHNyA@mail.gmail.com>
Subject: Re: pg_plan_advice
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoYRJdDh7MPK-cLs4w4gmYqEaWM3Y0o%2B2-YAKfJ%3D5rHNyA%40mail.gmail.com>
Precedence: bulk

Hi,

Coverity complained about the fact that pgpa_planner_feedback_warning
does not do anything to free the memory used by flagbuf or detailbuf,
and Tom asked whether that failure could amount to a significant leak.
The best way to use a lot of memory here is to have a very long advice
string that doesn't do anything useful, so I created this test case:

load 'pg_plan_advice';
select set_config('pg_plan_advice.advice', v.v, false) from (select
string_agg('SEQ_SCAN(hogehogehoge' || g || ')', ' ') v from
generate_series(1,10000) g) v;
set pg_plan_advice.feedback_warnings = true;
select 1;

While this is not the worst case imaginable, it's pretty bad. There's
probably no use case for having 10000 advice items in your advice
string even if they were all valid, and here none of them are valid
for the final query (select 1). So this produces "WARNING:  supplied
plan advice was not enforced" with a 10000 line detail message where
all the lines look like this, but with different numbers:

advice SEQ_SCAN(hogehogehoge7348) feedback is "not matched"

Failure to free the buffers costs us just over 1MB of memory, which is
not wildly disproportionate given that the message itself is over half
a MB long, so I'm not sure that freeing it is all that useful here. I
don't think the context we use for planning normally sticks around for
all that long. If I'm wrong about that and it does, then we should
probably wrap our own shorter-lived context around the stuff this
module is doing rather than trying to free allocations individually.

But this does raise two points which are perhaps worthy of some
further consideration:

1. Maybe we should limit the length of the detail message. In some
other cases, like reportDependentObjects, we limit the detail message
to the first 100 problems found, and then say at the end of that
detail message how many more problems there were afterwards. We could
do that here, too. I'm not 100% sure it's worth the code, but then
again it's not much code.

2. The way the current code works is to transform the advice feedback
into a Node-tree representation which is stashed in the PlannedStmt's
extension_state, and then also passes that Node-tree representation to
pgpa_planner_feedback_warning, which generates the actual warning.
That Node-tree representation is currently used by EXPLAIN to display
the advice feedback, which I think for most people will be a nicer
interface than enabling pg_plan_advice.feedback_warnings, and it can
also be used by other extensions. For instance, we could extend
pg_stash_advice so that it looks at the advice feedback and updates
the shared store, so that users can monitor whether their stashed
advice is doing what they hoped it would.

However, in a case like this, that Node tree is actually quite large:
about 16MB. I guess that's because pgpa_planner_append_feedback() has
to do multiple allocations for every item of feedback: a C string,
DefElem, an Integer, plus whatever lappend() charges us to add to a
List. We could save that memory by adding code here to optimize for
the case where we need to generate warnings but we don't need the Node
tree for anything else. I'm inclined not to do that, because (A) I
don't think temporarily using 16MB when the user specifies 10,000
items of bogus advice is really that bad and (B) complicating the code
has its own costs, such as maybe introducing more bugs. However, maybe
someone else sees it differently.

Another idea is to try to find a more economic Node representation.
For instance, we could jam the flags into the DefElem's location
field, instead of building a separate Integer node, or we could build
the advice feedback as a giant binary blob and wrap it in a varlena
and a Const node and leave consumers to make sense of that as best
they're able, or we could invent a new Node type that's just to the
perfect thing to hold a C string and an integer. I'm inclined to think
that the first two are too hacky and the third is too special-purpose,
but again someone else might see it otherwise.

Thoughts?

-- 
Robert Haas
EDB: http://www.enterprisedb.com