Comments (15)
I took another look. If the user creates the pg_hint_plan
extension, this will not be an issue as the hints
table is created as part of the extension.
It also seems there will be difficulty in actually handling this error and proceeding with the query as the user must rollback the transaction.
At minimum, should this scenario be highlighted in docs [1]?
Thoughts?
[1] https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md#the-hint-table
from pg_hint_plan.
One thing that we could do is a check callback for the GUC to ease the error message when using a SET. Like for check_default_table_access_method, it cannot be perfect: it is not possible to do a catalog lookup to see if the hint table exists or if the extension is installed if not connected to a database and if not within a transaction.
from pg_hint_plan.
One thing that we could do is a check callback for the GUC to ease the error message when using a SET. Like for check_default_table_access_method, it cannot be perfect:
To be sure, you are thinking to check if the pg_hint_plan extension has been created in the guc check hook?
from pg_hint_plan.
To be sure, you are thinking to check if the pg_hint_plan extension has been created in the guc check hook?
Or check that the table exists. Both require a catalog lookup and a transaction state, so that could run under the same constraints. LImited because of these constraints, still perhaps useful for some.
from pg_hint_plan.
such a check would indeed be very limited, and totally useless in some scenarios that I would expect to be common (like using session_preload_libraries). it also wouldn't cover e.g. persistent connections and a later drop extension.
it would probably be better to add a check in get_hints_from_table() that the table exist (like trying to get its oid or something) and raise a clear error message with a hint.
from pg_hint_plan.
Hmm. Is an error message the best choice though? If pg_hint_plan.enable_hint_table is enabled in postgresql.conf, then one would get an error for basically all queries run in the backend. I have been annoyed by that a bit myself. So how about lowering this log's level? WARNING would noisy and bloat the logs, though.
from pg_hint_plan.
On the other hand, if you rely on the extension being installed to not have your production server crash and burn due to incorrect plans, downgrading the message to WARNING would decrease the chances to notice the problem immediately after a maintenance operation.
Ideally we should document that enabling it globally is likely to be a terrible idea and should be done at the database level using ALTER DATABASE SET.
from pg_hint_plan.
On the other hand, if you rely on the extension being installed to not have your production server crash and burn due to incorrect plans, downgrading the message to WARNING would decrease the chances to notice the problem immediately after a maintenance operation.
That's a good point. It's better to error out rather than let query execution go sideways leaving the user confused as to why. Adding more documentation are this behavior is necessary at minimum.
from pg_hint_plan.
Ideally we should document that enabling it globally is likely to be a terrible idea and should be done at the database level using ALTER DATABASE SET.
Yeah, agreed that this looks like a good alternative based on the fact that CREATE EXTENSION needs to happen for each database.
from pg_hint_plan.
I've also wondered if the hints table can be turned into a dynamic-sized hash table; which may be a better way to go long term particularly if we remove the normalized_text and start using queryId. We will have to deal with persisting the data across reboots and crashes.
from pg_hint_plan.
We could do that, but there will still be the fundamental requirement that query identifiers are only guaranteed to be meaningful on a per-database level (unless you have a jumbling extension that only works with object names rather than oid, but we can't assume that by default), so you will need to add the database oid in the hash key and declare them on a per-database basis anyway. Given those requirements I'm not sure that the extra infrastructure to handle it will be really worth it.
from pg_hint_plan.
It may still be worth considering.
There could probably be performance benefits with going down the hash table path, such as the data always being in memory ( a heavily used hints table will probably always be in memory, so that's probably not a major gain), but there is also the additional locking required for a normal table and the extra sql to fetch the hint.
from pg_hint_plan.
Given those requirements I'm not sure that the extra infrastructure to handle it will be really worth it.
I guess that you are also referring here to the part where the data would need to persist across restarts and fill in the hash table when starting without crash recovery, right?
from pg_hint_plan.
I guess that you are also referring here to the part where the data would need to persist across restarts and fill in the hash table when starting without crash recovery, right?
Exactly. And unless we want to do the configuration using some SQL wrappers on top of C functions and declare them manually on each replica, the source of truth will probably stay in some local table, so this would only be a cache so it will also need to handle eviction and refreshing based on the source table, handle the MVCC aspect and so on.
Having such a cache will likely improve performance, as @samimseih pointed, but I don't think it's really trivial to do.
from pg_hint_plan.
Having such a cache will likely improve performance, as @samimseih pointed, but I don't think it's really trivial to do.
I'm biased on the risk vs benefit risk on this one and there are a few other things I'd like to get done around first, so I don't really plan to work on that.
from pg_hint_plan.
Related Issues (20)
- Does pg_hint_plan support "unnamed_subquery" added as part of PG 16 ? HOT 1
- Instability of pg_hint_plan.sql test in PG16 + master on a slow box (or when testing with valgrind). HOT 1
- SIGABRT when the parse_messages parameter equals to error. HOT 1
- Regression with Parallel hints combined with removal of indexes in Index hints HOT 10
- enable_self_join_removal in 17 breaks regression tests that use self-joins HOT 1
- Is it possible to inject each operator's cardinality by hint 'Rows' ? HOT 1
- Scan and Join hints do not work as expected HOT 6
- Installation of pg_hint_plan failed for Enterprise DB 14.x HOT 4
- pg_hint_plan whether support for hints for group by method(hash/sort)? HOT 1
- make hint relname comparison case insensitive HOT 1
- Tolerating preceding single-line comments HOT 3
- Hint to use a Nested Loop Semi Join HOT 1
- Improved repository description? HOT 9
- Cant get normalized text to work with pg plan hint HOT 2
- Maskout script doesn't work on macOS HOT 2
- Hint table doesn't seem to have effect on query plans HOT 7
- Hints do not work on subqueries with DISTINCT
- Next release of pg_hint_plan with PostgreSQL 17 compatibility HOT 27
- Discrepancy in the costs of prepared operators depending on the architecture of the system HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pg_hint_plan.