Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
>> b...@yugabyte.com writes:
>> 
>>  get stacked diagnostics msg = message_text;
>>  if msg != 'relation "pg_temp.flag" does not exist' then
> 
> This is pretty fragile --- eg, it will fall over with translated messages. I 
> think you could presume that if the error condition name is undefined_table 
> then you know what to do.

Mea culpa. I should have stressed that my code was meant to be a sketch rather 
than the real thing. So my use of "on commit delete rows" suits the "hard shell 
paradigm" that I described here:

https://www.postgresql.org/message-id/f0a23614-749d-4a89-84c5-119d4000f...@yugabyte.com

where the client code does:

  check out connection
  call a user-defined API subprogram
  release connection

and where I therefore want automatic check-out-duration session state.

In a different use case, I'd want session-duration session state. There. I'd 
use "on commit preserve rows".

About testing what "message_text" from "get stacked diagnostics msg" returns, 
yes… of course its sensitivity to the current choice of national language is a 
non-starter. I don't like to assume more than I have to. So I might say this:

  if msg !~ '"pg_temp.flag"' then

But, then again, I might decide that it's just too fussy.

I've seen this pattern in use:

  create temp table if not exists pg_temp.flag(val boolean not null) on commit 
delete rows;
  insert into pg_temp.flag(val) values(true);

But doing a DDL before every use of the session-state representation felt 
heavier than assuming that it's there and creating the table only if it isn't. 
But I haven't done any timing tests. Is the "create… if not exists" so 
lightweight when the to-be-created object does exist that I'm fussing over 
nothing?

Re: Example code Re: Singleton SELECT inside cursor loop

[Ron]

On 10/1/22 15:42, Ron wrote:

On 10/1/22 14:54, Christoph Moench-Tegeder wrote:

## Ron (ronljohnso...@gmail.com):


The question then is "why am I just now seeing the problem?"  We've been
using v12 for two years, and it just happened.

The only recent change is that I upgraded it from RDS 12.10 to 12.11 a
couple of weeks ago.

That's correlation, but no proof for causality.


Right.  But it is an important change which happened between job runs (the 
22nd of each month).



Now that you've confirmed that you have indeed a mismatch between generic
and custom plan, you could compare those (EXPLAIN EXECUTE) and see
where the generic plan goes wrong.


I'll rerun the EXPLAIN EXECUTE with and without "plan_cache_mode = 
force_custom_plan", and attach them in a reply some time soon.


Attached are explain outputs from: , and then

TASK001785639_explain_output_custom.log: a "first five" fast execution
TASK001785639_explain_output_generic.log: "the sixth" (generic) plan when it 
took 6 minutes.


Next are similar plans except where default_statistic_target = 1000, and the 
table is reanalyzed.  It didn't help with this query.


TASK001785639_explain_output_custom_def_stats_1000.log
TASK001785639_explain_output_generic_def_stats_1000.log

Finally is the explain output from "plan_cache_mode = force_custom_plan":
TASK001785639_explain_output_force_custom_def_stats_1000.log

According to meld diff, custom_def_stats_1000 and 
force_custom_def_stats_1000 have surprisingly similar plans (the only 
difference being that in the forced custom plan, 2 workers were launched, 
and so they filtered out some rows.  Execution time was about 460ms in both.



[snip]

If would help if you could compare execution plans with plans from
before the update, but I guess you didn't document those?


So, since ANALYZE did not help.


EDIT: *No***, since ANALYZE did not help.

--
Angular momentum makes the world go 'round.  QUERY PLAN   
---
 Aggregate  (cost=176420.04..176420.05 rows=1 width=8) (actual time=457.035..457.974 rows=1 loops=1)
   ->  Gather  (cost=120083.10..176417.85 rows=125 width=45) (actual time=311.639..457.225 rows=499 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 ->  Parallel Hash Anti Join  (cost=119083.10..175405.35 rows=52 width=45) (actual time=309.084..452.471 rows=166 loops=3)
   Hash Cond: ((sire.reqte_endpoint_id = sire2.reqte_endpoint_id) AND (sire.reqtde_endpoint_id = sire2.reqtde_endpoint_id) AND ((sire.record_guid)::text = (sire2.record_guid)::text))
   Join Filter: ((sire2.separation_request_id <> sire.separation_request_id) AND (sire2.reqt_processing_completed_dts < sire.reqt_processing_completed_dts))
   Rows Removed by Join Filter: 167
   ->  Parallel Index Scan using xif11sep_info_report_extract on sep_info_report_extract sire  (cost=0.56..56321.62 rows=52 width=60) (actual time=1.275..144.492 rows=166 loops=3)
 Index Cond: ((reqt_processing_completed_dts >= '2022-08-01 00:00:00'::timestamp without time zone) AND (reqt_processing_completed_dts <= '2022-08-31 23:59:59'::timestamp without time zone))
 Filter: (((reqt_failed_ind)::text <> '1'::text) AND ((reqxt_failed_ind IS NULL) OR ((reqxt_failed_ind)::text <> '1'::text)) AND ((resxt_failed_ind IS NULL) OR ((resxt_failed_ind)::text <> '1'::text)) AND ((reqtde_organization_type)::text <> 'SEW_EMPLOYER'::text) AND (req_valid_ind = '1'::numeric) AND (reqte_endpoint_id = '1'::numeric(38,0)) AND (reqtde_endpoint_id = '507650'::numeric(38,0)) AND (((res_valid_ind = 

Re: Example code Re: Singleton SELECT inside cursor loop

[Ron]

On 10/1/22 14:54, Christoph Moench-Tegeder wrote:

## Ron (ronljohnso...@gmail.com):


The question then is "why am I just now seeing the problem?"  We've been
using v12 for two years, and it just happened.

The only recent change is that I upgraded it from RDS 12.10 to 12.11 a
couple of weeks ago.

That's correlation, but no proof for causality.


Right.  But it is an important change which happened between job runs (the 
22nd of each month).



Now that you've confirmed that you have indeed a mismatch between generic
and custom plan, you could compare those (EXPLAIN EXECUTE) and see
where the generic plan goes wrong.


I'll rerun the EXPLAIN EXECUTE with and without "plan_cache_mode = 
force_custom_plan", and attach them in a reply some time soon.



  Otherwise, prime suspects are bad
statistics (Were stats accurate before the update? How about now?
Ran VACUUM ANALYZE recently?


VACUUM yesterday at 04:00, and ANALYZE at 09:00, both from a cron job.  
Manually ran ANALYZE last night during the dig into the problem.



  If not, does it improve matters?)


Nope.  Not one bit.


If would help if you could compare execution plans with plans from
before the update, but I guess you didn't document those?


So, since ANALYZE did not help.

--
Angular momentum makes the world go 'round.

Re: Example code Re: Singleton SELECT inside cursor loop

[Christoph Moench-Tegeder]

## Ron (ronljohnso...@gmail.com):

> The question then is "why am I just now seeing the problem?"  We've been 
> using v12 for two years, and it just happened.
> 
> The only recent change is that I upgraded it from RDS 12.10 to 12.11 a 
> couple of weeks ago.

That's correlation, but no proof for causality.
Now that you've confirmed that you have indeed a mismatch between generic
and custom plan, you could compare those (EXPLAIN EXECUTE) and see
where the generic plan goes wrong. Otherwise, prime suspects are bad
statistics (Were stats accurate before the update? How about now?
Ran VACUUM ANALYZE recently? If not, does it improve matters?)
If would help if you could compare execution plans with plans from
before the update, but I guess you didn't document those?

Regards,
Christoph

-- 
Spare Space

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Tom Lane]

Bryn Llewellyn  writes:
>   get stacked diagnostics msg = message_text;
>   if msg != 'relation "pg_temp.flag" does not exist' then

This is pretty fragile --- eg, it will fall over with translated
messages.  I think you could presume that if the error condition
name is undefined_table then you know what to do.

regards, tom lane

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Bryn Llewellyn]

> t...@sss.pgh.pa.us wrote:
> 
> Up to now, there's been an intentional policy of not documenting
> 
> «
> 20.16. Customized Options
> https://www.postgresql.org/docs/14/runtime-config-custom.html 
> 
> »
> 
> very prominently[*], because doing so would encourage people to abuse such 
> variables as application state variables. I say "abuse" because the code 
> supporting such variables isn't really designed to support lots of them.

I hinted at a different approach in an earlier turn in this thread:

https://www.postgresql.org/message-id/35254b0b-6501-4cf6-a13f-76d03756c...@yugabyte.com

I sketched only how you might handle the case where the session state is just a 
single value—by using a one-row, one-column temporary table with "on commit 
delete rows". But the general approach is to use a two column temporary table 
for key-value pairs. This approach is what the PG doc sketches here:

«
43.13. Porting from Oracle PL/SQL
https://www.postgresql.org/docs/current/plpgsql-porting.html
Since there are no packages, there are no package-level variables either. This 
is somewhat annoying. You can keep per-session state in temporary tables 
instead.
»

(That article of faith, "there are no packages and there never, ever will be", 
saddens me.)

Because PG has no event trigger that fires on session creation (why is this?), 
I've settled on this optimistic pattern:

begin
  insert into pg_temp.flag(val) values(true);
exception when undefined_table then
  get stacked diagnostics msg = message_text;
  if msg != 'relation "pg_temp.flag" does not exist' then
raise;
  else
create temp table pg_temp.flag(val boolean not null) on commit delete rows;
insert into pg_temp.flag(val) values(true);
  end if;
end;

The code would need to be more elaborate (and use "upsert") for key-value 
pairs. But that's easy to do.

Do the experts on this list disapprove of this pattern and prefer (for a future 
regime) something like the Pavel Stehule scheme that Tom mentioned?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

[*] I didn't know that there was a PG doc policy sometimes not to call out a 
bad practice but, rather, to hide away (in an obscure backwater in the docs) 
the account of a feature that’s considered to be better avoided except in 
special cases. This effectively hides it from Google search (and similar) too 
because of the circular notion that few people find it, and fewer still publish 
pages that include the link,… and so on.

I suppose that calling the thing an "option" while the doc for the "set" SQL 
statement uses the term of art "run-time parameter" is another “bad practice 
admonition by obscurity” notion. (I've referred to the thing as a "user-defined 
run-time parameter" in informal emails to colleagues. But that is a lot of 
syllables.)

Re: Example code Re: Singleton SELECT inside cursor loop

[Ron]

On 10/1/22 07:21, Christoph Moench-Tegeder wrote:

## Ron (ronljohnso...@gmail.com):


Note how quickly it runs the first five times, but takes 780x longer the
sixth time I run it.  Exiting psql and entering again causes the same
slowness the sixth time it's run.

Tanks at the sixth time? That rings a bell: "The current rule for this
is that the first five executions are done with custom plans[...]" from
https://www.postgresql.org/docs/12/sql-prepare.html


The question then is "why am I just now seeing the problem?"  We've been 
using v12 for two years, and it just happened.


The only recent change is that I upgraded it from RDS 12.10 to 12.11 a 
couple of weeks ago.



And your functions create prepared statements under the hood:
https://www.postgresql.org/docs/12/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING


The same thing happens when I put the SELECT in a prepared statement, so 
that seems the likely cause.



I guess you should visit
https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-PLAN-CACHE_MODE
and try if plan_cache_mode = force_custom_plan helps here.


That solved the problem.  Thank you!

--
Angular momentum makes the world go 'round.

Re: Example code Re: Singleton SELECT inside cursor loop

[Ron]

On 10/1/22 07:20, Peter J. Holzer wrote:

On 2022-10-01 02:22:22 -0500, Ron wrote:

I extracted the singleton into a function, and that helped me isolate the
problem.

I don't know what a singleton select is (duckduckgo tells me it has
something to do with DB2 and/or COBOL, or maybe it's a whisky), but:


A query which returns one row.  Typically SELECT COUNT/MAX/etc without a 
GROUP BY clause.


--
Angular momentum makes the world go 'round.

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2022-10-01 20:24:21 +0800, Julien Rouhaud wrote:
>> This is intentional, and documented at
>> https://www.postgresql.org/docs/14/runtime-config-custom.html.

> So it's there but hard to find (I did read the table of contents at
> https://www.postgresql.org/docs/14/runtime-config.html but I guess after
> all the subchapters about specific configuration parameters I didn't
> notice the one generic subchapter near the end).
> I think referring to that chapter from
> https://www.postgresql.org/docs/14/sql-set.html and possibly other
> places would help.

Up to now, there's been an intentional policy of not documenting
this very prominently, because doing so would encourage people
to abuse such variables as application state variables.  I say
"abuse" because the code supporting such variables isn't really
designed to support lots of them.

There has been a patch in the works for a long time to provide a
better mechanism for application state variables [1].  Separately,
I did some work recently towards alleviating the performance
problems with lots of custom parameters [2].  It appears from
the discussion in [2] that we're probably going to stick to the
policy of not encouraging use of custom parameters as application
state, although if [1] keeps failing to land maybe that will change?

regards, tom lane


[1] 
https://www.postgresql.org/message-id/flat/CAFj8pRD053CY_N4=6SvPe7ke6xPbh=k50luaowjc3jm8me9...@mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/2982579.1662416...@sss.pgh.pa.us

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Peter J. Holzer]

On 2022-10-01 20:24:21 +0800, Julien Rouhaud wrote:
> On Sat, Oct 01, 2022 at 02:05:53PM +0200, Peter J. Holzer wrote:
> > On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
> > > set rls.tenant_id=42;
> >
> > This works because there is a "." in the name. Without the "."
> > PostgreSQL complains:
> >
> > hjp=> set rls_tenant_id=42;
> > ERROR:  unrecognized configuration parameter "rls_tenant_id"
> >
> > I think I sort of knew this but had forgotten about it, so checked the
> > manual for the exact rules. Unfortunately I couldn't find them
[...]
> > From the documentation I get the impression that you can only set
> > existing parameters, not your own.
> >
> > I therefore suggest adding something like this to the section
> > "configuration_parameter" in
> > https://www.postgresql.org/docs/14/sql-set.html:
> >
[...]
> > (This of course assumes that the behaviour is intentional and not a
> > bug.)
> 
> This is intentional, and documented at
> https://www.postgresql.org/docs/14/runtime-config-custom.html.

So it's there but hard to find (I did read the table of contents at
https://www.postgresql.org/docs/14/runtime-config.html but I guess after
all the subchapters about specific configuration parameters I didn't
notice the one generic subchapter near the end).

I think referring to that chapter from
https://www.postgresql.org/docs/14/sql-set.html and possibly other
places would help.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Julien Rouhaud]

Hi,

On Sat, Oct 01, 2022 at 02:05:53PM +0200, Peter J. Holzer wrote:
> On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
> > set rls.tenant_id=42;
>
> This works because there is a "." in the name. Without the "."
> PostgreSQL complains:
>
> hjp=> set rls_tenant_id=42;
> ERROR:  unrecognized configuration parameter "rls_tenant_id"
>
> I think I sort of knew this but had forgotten about it, so checked the
> manual for the exact rules. Unfortunately I couldn't find them (I
> checked https://www.postgresql.org/docs/14/sql-set.html,
> https://www.postgresql.org/docs/14/config-setting.html,
> https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADMIN-SET,
> and browser the table of content and the index).
>
> From the documentation I get the impression that you can only set
> existing parameters, not your own.
>
> I therefore suggest adding something like this to the section
> "configuration_parameter" in
> https://www.postgresql.org/docs/14/sql-set.html:
>
> | In addition, any name including a single dot (".") can be set. This
> | allows an application to define its own run-time parameters. Using the
> | application name as a prefix reduces the probability of name conflicts
> | with other applications or extensions.
>
> (This of course assumes that the behaviour is intentional and not a
> bug.)

This is intentional, and documented at
https://www.postgresql.org/docs/14/runtime-config-custom.html.

Re: Example code Re: Singleton SELECT inside cursor loop

[Christoph Moench-Tegeder]

## Ron (ronljohnso...@gmail.com):

> Note how quickly it runs the first five times, but takes 780x longer the 
> sixth time I run it.  Exiting psql and entering again causes the same 
> slowness the sixth time it's run.

Tanks at the sixth time? That rings a bell: "The current rule for this
is that the first five executions are done with custom plans[...]" from
https://www.postgresql.org/docs/12/sql-prepare.html
And your functions create prepared statements under the hood:
https://www.postgresql.org/docs/12/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
I guess you should visit
https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-PLAN-CACHE_MODE
and try if plan_cache_mode = force_custom_plan helps here.

Regards,
Christoph

-- 
Spare Space

Re: Example code Re: Singleton SELECT inside cursor loop

[Peter J. Holzer]

On 2022-10-01 02:22:22 -0500, Ron wrote:
> I extracted the singleton into a function, and that helped me isolate the
> problem.

I don't know what a singleton select is (duckduckgo tells me it has
something to do with DB2 and/or COBOL, or maybe it's a whisky), but:


> Note how quickly it runs the first five times, but takes 780x longer the
> sixth time I run it.  Exiting psql and entering again causes the same
> slowness the sixth time it's run.

PostgresSQL evaluates the exact plan the first 5 times it encounters the
same prepared query. After that it switches to a generic plan if it
thinks that the generic plan isn't worse than the specialized plans.

If the plan suddenly gets worse after 5 executions, you've probably run
into a case where the generic plan is worse although the cost computed
by the planner isn't.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

[Peter J. Holzer]

On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
> set rls.tenant_id=42;

This works because there is a "." in the name. Without the "."
PostgreSQL complains:

hjp=> set rls_tenant_id=42;
ERROR:  unrecognized configuration parameter "rls_tenant_id"


I think I sort of knew this but had forgotten about it, so checked the
manual for the exact rules. Unfortunately I couldn't find them (I
checked https://www.postgresql.org/docs/14/sql-set.html,
https://www.postgresql.org/docs/14/config-setting.html,
https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADMIN-SET,
and browser the table of content and the index).

From the documentation I get the impression that you can only set
existing parameters, not your own.

I therefore suggest adding something like this to the section
"configuration_parameter" in
https://www.postgresql.org/docs/14/sql-set.html:

| In addition, any name including a single dot (".") can be set. This
| allows an application to define its own run-time parameters. Using the
| application name as a prefix reduces the probability of name conflicts
| with other applications or extensions.

(This of course assumes that the behaviour is intentional and not a
bug.)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

[Peter J. Holzer]

On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote:
> hjp-pg...@hjp.at wrote:
> b...@yugabyte.com wrote:
> Paraphrasing Peter, the design of the application's RDBMS backend has
> to implement its own notions of roles and privileges as a new layer on
> top of whatever the native RDBMS mechanisms provide. Some RDBMSs have
> native primitives that help the implementation of this next,
> application-specific, roles-and-privileges regime.
> 
> Can you provide a link to documentation (hopefully high-level and concise)
> of such a system? I'm having a hard time imagining one which I wouldn't
> either classify as "database roles by another name" or "just data".
> 
> 
> I fear that we might be about to start another round of mutual
> misunderstanding. I’m not confident that I understand the question.
> 
> I believe that you want x-refs to accounts of native RDBMS features that let
> you implement application-level specific notions of security on top of the
> basic roles and privileges features and that are oriented to the case where a
> single RDBMS role, "client", must expose differentiated functionality to
> different human end-users—where these principals are identified by, and
> authorized by, system(s) outside of the RDBMS in question.

I the context of this thread, I don't "want" anything (what I actually
want may change from project to project). I am not familiar with the
"native primitives" you mentioned, so I would like to read up on them.
So if you can just drop a few names I can feed them to my favourite
search engine. An overview article which explains the concept and how
the primitives are used would be better of course, but if you don't have
any at hand, no problem.

Background: I have used Oracle, MySQL/MariaDB and PostgreSQL enough that
I consider myself to be quite familiar with their capabilities (my
Oracle skills are getting rusty, though). I have occasionally used
and/or read about other RDBMSs, but my knowledge of those is very
spotty.


> Franck's PG RLS  policies are created like this:
> 
> create policy... on ... for... using(tenant_id=current_setting
> ('rls.tenant_id')::int);

So, I think the intermediate concept here which is neither role nor data
is the use of a run-time parameter.

He's not using a database role and he's not using a parameter which has
to be supplied to every query by the application programmer, but a
run-time parameter which would presumably be set once at the beginning
of a session or transaction (depending on whether you use connection
pooling). That's clever. Not sure if I would actually use it but it's
certainly something I'll add to my bag of tools.

> I'm afraid that I don't know what you mean by « classify as "database roles by
> another name" or "just data" ». For me, the RLS scheme is simply the native PG
> feature that you use to get the result that you want.

I meant what do you use to identify the user. The mechanism in
PostgreSQL intended for this are roles. You can use roles in RLS (in
fact I don't think I've ever seen an example which didn't use roles).

In contrast to this many systems (e.g. Django, Typo3, several I've
designed myself) use a table to keep their own list of users (and
possibly groups), and then check for every access whether the user
logged into the application has access by joining with that table. That
join may be explicit in the application code or it may be hidden behind
a view or a function. But the database doesn't know about that user.
It's just another parameter passed in by the application - "just data".

Using a run-time parameter is somewhere in between. The database still
doesn't know what it means (so in some sense it's still "just data"),
but it will keep the value for the duration of the transaction or
session, so the application gets sort of an "authorize once, then forget
about it" abstraction which puts it closer to the "roles by another
name" camp.


> Platform Multitenant Architecture
> https://architect.salesforce.com/fundamentals/platform-multitenant-architecture

That's *too* high-level for me. There's any number of techniques which
could be used to implement something like that and I don't see how they
actually did it (maybe I missed it - I admit I only skimmed the
article).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Example code Re: Singleton SELECT inside cursor loop

[Ron]

I extracted the singleton into a function, and that helped me isolate the 
problem.


Note how quickly it runs the first five times, but takes 780x longer the 
sixth time I run it.  Exiting psql and entering again causes the same 
slowness the sixth time it's run.


sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

sides=> \timing
Timing is on.

sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

Time: 457.325 ms

sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

Time: 459.724 ms

sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

Time: 461.155 ms

sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---
   498
(1 row)

Time: 358426.159 ms (05:58.426)


On 10/1/22 01:29, Ron wrote:

AWS RDS Postgresql 12.11

It runs quickly, no matter what the fetched cursor values are, as long as 
the cursor returns less than 8 rows.  After that, the singleton SELECT's 
performance tanks.


We've got plenty of other procedures which have a similar "Singleton 
SELECT inside a cursor" design (many even query the same table that the 
problematic query does), but only this procedure's singleton SELECT tanks 
if the cursor returns more than 8 rows.


It only runs once a month, so we've only just noticed the problem.

Why would that be?  (We recently updated from 12.10 to 12.11, if that's 
relevant.)




--
Angular momentum makes the world go 'round.

TASK001785639_function.sql
Description: application/sql

Singleton SELECT inside cursor loop

[Ron]

AWS RDS Postgresql 12.11

It runs quickly, no matter what the fetched cursor values are, as long as 
the cursor returns less than 8 rows.  After that, the singleton SELECT's 
performance tanks.


We've got plenty of other procedures which have a similar "Singleton SELECT 
inside a cursor" design (many even query the same table that the problematic 
query does), but only this procedure's singleton SELECT tanks if the cursor 
returns more than 8 rows.


It only runs once a month, so we've only just noticed the problem.

Why would that be?  (We recently updated from 12.10 to 12.11, if that's 
relevant.)


--
Angular momentum makes the world go 'round.