search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
710 stars 103 forks source link

enable_self_join_removal in 17 breaks regression tests that use self-joins #166

Closed samimseih closed 10 months ago

samimseih commented 11 months ago

Commit d3d55ce5 introduces a new ON by default GUC called enable_self_join_removal which could replace self-joins with single scans. This breaks hint enforcement in test cases that are implemented with self-joins. Rather than remove these tests, disable this feature to continue testing the hints.

Please see the attached fix.

0001-v1-Set-enable_self_join_removal-to-OFF-for-hint-testing.patch

michaelpq commented 10 months ago 
+ALTER SYSTEM SET enable_self_join_removal TO off;

This is a bad idea because it would remain on the cluster permanently, Even if I agree that such instances would be thrown away, I think that this is a bad practice that we should eradicate.

This is not the fault of this patch as the regression tests have been relying on non-portable commands for ages, but I don't agree with adding more of this stuff. So, I've implemented as replacement a set of ALTER DATABASE commands built from a database name retrieved with a \gset, that has the same effect as the original version, while leaving a running cluster alone. A worse workaround was to hack directly the catalog pg_db_role_setting, but it is possible to use ALTER USER with the \gset trick. At the end the user manipulation was not necessary at all, so I've simplified it to use database-level GUC enforcements.