View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000560 | Pgpool-II | Enhancement | public | 2019-11-30 17:49 | 2019-12-10 10:16 |
| Reporter | subi | Assigned To | t-ishii | ||
| Priority | normal | Severity | feature | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 4.1.0 | ||||
| Summary | 0000560: allow to whitelist caching the results of stable functions | ||||
| Description | The project I am currently working with relies on PostgREST (http://postgrest.org) to provide a REST API. I am trying to use pgpool to cache the results of postgrest generated queries. The problem is that pgpool doesn't cache the query results of any non-immutable function, including stable ones, while postgrest heavily depends on the json-related functions to process and produce the results. Quite a few json functions are marked as stable, e.g., array_to_json, json_agg, json_build_array, json_build_object, json_object_agg, json_populate_record, json_to_record, etc.. Hence any result produced by postgrest is not cachable. Is it possible to add a configuration parameter to allow caching whitelisted stable functions? (just like views) | ||||
| Tags | query cache | ||||
|
|
The reason why Pgpool-II does not cache SELECT results including stable functions is, their results may be changed while Pgpool-II does not notice it. Example: SELECT pg_conf_load_time(); One of workarounds for the issue is, creating a wrapper function for the stable function and give immutable property to it, then call the wrapper function. Another idea is, changing the source code so that allow Pgpool-II to cache SELECTs results including stable functions. In src/utils/pool_select_walker.c around line 1047: #define IS_STABLE_FUNCTION_QUERY "SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = '%s' AND p.provolatile = 'i'" Change this to: #define IS_STABLE_FUNCTION_QUERY "SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = '%s' AND (p.provolatile = 'i' OR p.provolatile = 's)" Then Pgpool-II will cache SELECT results including immutable or stable functions. Of course you should care to not shoot yourself in the foot. More sane idea is, introducing a white list including stable functions as you said. But this will take time (at the earliest in 4.2 which is supposed to be released in 2020 fall). |
|
|
OK, I would pick a workround described in your comment, until a white list of stable functions is introduced. Many thanks! |
|
|
May I close this issue? |
|
|
Surely. BTW, will the stable function whitelist be introduced to 4.2? |
|
|
I have posted a proposal for this: https://www.pgpool.net/pipermail/pgpool-hackers/2019-December/003473.html If there's no objection from other pgpool developers, it is likely that it will appear in 4.2. |
|
|
That's awesome. Many thanks! |
|
|
Issue closed. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2019-11-30 17:49 | subi | New Issue | |
| 2019-11-30 17:49 | subi | Tag Attached: query cache | |
| 2019-12-02 15:12 | t-ishii | Note Added: 0002985 | |
| 2019-12-02 15:12 | t-ishii | Assigned To | => t-ishii |
| 2019-12-02 15:12 | t-ishii | Status | new => feedback |
| 2019-12-02 19:27 | subi | Note Added: 0002987 | |
| 2019-12-02 19:27 | subi | Status | feedback => assigned |
| 2019-12-04 09:04 | t-ishii | Status | assigned => feedback |
| 2019-12-04 09:05 | t-ishii | Note Added: 0003000 | |
| 2019-12-05 21:09 | subi | Note Added: 0003003 | |
| 2019-12-05 21:09 | subi | Status | feedback => assigned |
| 2019-12-06 11:33 | t-ishii | Note Added: 0003007 | |
| 2019-12-06 11:50 | subi | Note Added: 0003008 | |
| 2019-12-10 10:16 | t-ishii | Note Added: 0003016 | |
| 2019-12-10 10:16 | t-ishii | Status | assigned => closed |
