View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000792 | Pgpool-II | Enhancement | public | 2023-03-26 17:07 | 2023-03-27 20:31 |
| Reporter | cobolbaby | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | have not tried |
| Status | assigned | Resolution | open | ||
| Summary | 0000792: The read_only_function_list and write_function_list configurations cannot coexist | ||||
| Description | The read_only_function_list and write_function_list configurations cannot coexist. Once the former is configured, the latter judgment is skipped directly. https://github.com/pgpool/pgpool2/blob/e83e75874c7d3bf3c24ea8d437cf98d748df51b4/src/utils/pool_select_walker.c#L396-L428 | ||||
| Tags | No tags attached. | ||||
|
|
It's not a bug. It's an expected behavior. See the documentation: https://www.pgpool.net/docs/43/en/html/runtime-config-load-balancing.html#RUNTIME-CONFIG-LOAD-BALANCING-SETTINGS |
|
|
However, the logic behind this decision is still up for debate. Let me talk about a user scenario. Most of the functions in the `report` schema use temporary tables, so they need to be routed to the primary node. In order to reduce unnecessary configuration modifications in future operations and maintenance, I have set the `write_function_list` configuration to`nextval,setval,.*\.func_insert_.*,.*\.func_delete_.*,.*\.func_update_.*,.*\.func_nlb_.*,(report)\..*`. However, there are still several functions in this schema that do not use temporary tables and are called frequently. If they are routed to the primary node, it will further burden the primary node. And if I add it to the `read_only_function_list` alone, all functions not in the list will be routed to the primary node, which is terrible. |
|
|
Why don't you just make read_only_function_list and write_function_list empty? From the documentation: "Note: If this parameter and write_function_list is empty string, function's volatile proper will be checked. If the property is volatile, the function is regarded as a writing function. This is convenient and recommended way. " |
|
|
> function's volatile proper will be checked. I tried this solution before, but basically all custom function queries will be routed to the primary node. For example, the logic of the function is to query a certain table, and then return the statistical results of the latest day. The Function is theoretically volatile, but in fact the function can be routed to slave nodes. |
|
|
> For example, the logic of the function is to query a certain table, and then return the statistical results of the latest day. The Function is theoretically volatile, but in fact the function can be routed to slave nodes. IMO the function should be marked as stable, not volatile. If it's marked as "stable", pgpool routes the query using the function to load balance node. From the PostgreSQL doc: "STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc." |
|
|
May I close this issue? Mantis is the forum for bug reports, not questions or enhancement discussions. If you want to discuss or propose particular feature, please use pgpool-general mailing list. |
|
|
> STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. I haven't found such a description in the official documentation. In the PostgreSQL official documentation https://www.postgresql.org/docs/12/xfunc-volatility.html, The explanation of stable is a bit obscure. > IMO the function should be marked as stable, not volatile. If it's marked as "stable", pgpool routes the query using the function to load balance node. If the internal logic of a function is very complex, it is necessary to use a temporary table to cache intermediate results. But in fact, given the same input parameters, the return value of the function is the same, so the function should be defined as "stable" theoretically. However, if the function is defined as "stable", the query will be routed to the replica node for execution, which may fail due to the use of temporary tables. |
|
|
> May I close this issue? Mantis is the forum for bug reports, not questions or enhancement discussions. > If you want to discuss or propose particular feature, please use pgpool-general mailing list. Ok. |
|
|
> I haven't found such a description in the official documentation. See: https://www.postgresql.org/docs/15/sql-createfunction.html > If the internal logic of a function is very complex, it is necessary to use a temporary table to cache intermediate results. Then the function actually writes to database thus it should be marked as "volatile". |
|
|
> https://www.postgresql.org/docs/15/sql-createfunction.html Thank you. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2023-03-26 17:07 | cobolbaby | New Issue | |
| 2023-03-27 09:32 | t-ishii | Note Added: 0004315 | |
| 2023-03-27 09:33 | t-ishii | Note Edited: 0004315 | |
| 2023-03-27 09:35 | t-ishii | Assigned To | => t-ishii |
| 2023-03-27 09:35 | t-ishii | Status | new => feedback |
| 2023-03-27 10:22 | cobolbaby | Note Added: 0004316 | |
| 2023-03-27 10:22 | cobolbaby | Status | feedback => assigned |
| 2023-03-27 11:36 | t-ishii | Note Added: 0004318 | |
| 2023-03-27 12:23 | cobolbaby | Note Added: 0004320 | |
| 2023-03-27 17:16 | t-ishii | Note Added: 0004327 | |
| 2023-03-27 17:34 | t-ishii | Note Added: 0004328 | |
| 2023-03-27 17:34 | t-ishii | Status | assigned => feedback |
| 2023-03-27 18:05 | cobolbaby | Note Added: 0004329 | |
| 2023-03-27 18:05 | cobolbaby | Status | feedback => assigned |
| 2023-03-27 18:16 | cobolbaby | Note Added: 0004330 | |
| 2023-03-27 19:47 | t-ishii | Note Added: 0004331 | |
| 2023-03-27 20:31 | cobolbaby | Note Added: 0004332 |
