Syntax error on a correct query

[tarkhil]

Running POWA 5, I've got an error

ERROR: SyntaxError('syntax error at or near "AND"\nLINE 3: WHERE main_calculationtariffrule.company = ? AND main_calcul...\n                                                     ^\n')

The request is long but straightforward

SELECT main_calculationtariffrule.id AS main_calculationtariffrule_id,
  main_calculationtariffrule.company AS main_calculationtariffrule_company,
  main_calculationtariffrule.tariff AS main_calculationtariffrule_tariff,
  main_calculationtariffrule.tariff_in_ua AS main_calculationtariffrule_tariff_in_ua,
  main_calculationtariffrule.comment AS main_calculationtariffrule_comment,
  main_calculationtariffrule.def_multiplication AS main_calculationtariffrule_def_multiplication,
  main_calculationtariffrule.def_addition AS main_calculationtariffrule_def_addition,
  main_calculationtariffrule.multiplication AS main_calculationtariffrule_multiplication,
  main_calculationtariffrule.addition AS main_calculationtariffrule_addition,
  main_calculationtariffrule.min_limit_weight AS main_calculationtariffrule_min_limit_weight,
  main_calculationtariffrule.max_limit_weight AS main_calculationtariffrule_max_limit_weight,
  main_calculationtariffrule.min_limit_volume AS main_calculationtariffrule_min_limit_volume,
  main_calculationtariffrule.max_limit_volume AS main_calculationtariffrule_max_limit_volume,
  main_calculationtariffrule.delivery_type AS main_calculationtariffrule_delivery_type,
  main_calculationtariffrule.multi_cargo AS main_calculationtariffrule_multi_cargo,
  main_calculationtariffrule.express_type AS main_calculationtariffrule_express_type,
  main_calculationtariffrule.today_get AS main_calculationtariffrule_today_get,
  main_calculationtariffrule.today_get_time AS main_calculationtariffrule_today_get_time,
  main_calculationtariffrule.pickup_time_required AS main_calculationtariffrule_pickup_time_required,
  main_calculationtariffrule.pickup_time_interval AS main_calculationtariffrule_pickup_time_interval,
  main_calculationtariffrule.pickup_time_step AS main_calculationtariffrule_pickup_time_step,
  main_calculationtariffrule.pickup_time_start AS main_calculationtariffrule_pickup_time_start,
  main_calculationtariffrule.pickup_time_end AS main_calculationtariffrule_pickup_time_end,
  main_calculationtariffrule.display_delivery_time AS main_calculationtariffrule_display_delivery_time,
  main_calculationtariffrule.coincides_pickup_time AS main_calculationtariffrule_coincides_pickup_time,
  main_calculationtariffrule.delivery_date_differs_pickup_date AS main_calculationtariffrule_delivery_date_differs_pickup_d_1,
  main_calculationtariffrule.enable_loading AS main_calculationtariffrule_enable_loading,
  main_calculationtariffrule.enable_unloading AS main_calculationtariffrule_enable_unloading,
  main_calculationtariffrule.enable_crating AS main_calculationtariffrule_enable_crating,
  main_calculationtariffrule.enable_cod AS main_calculationtariffrule_enable_cod,
  main_calculationtariffrule.enable_np AS main_calculationtariffrule_enable_np,
  main_calculationtariffrule.enable_byhand AS main_calculationtariffrule_enable_byhand,
  main_calculationtariffrule.enable_returndoc AS main_calculationtariffrule_enable_returndoc,
  main_calculationtariffrule.enable_insurance AS main_calculationtariffrule_enable_insurance,
  main_calculationtariffrule.enable_hazardous AS main_calculationtariffrule_enable_hazardous,
  main_calculationtariffrule.enable_scan_request AS main_calculationtariffrule_enable_scan_request,
  main_calculationtariffrule.enable_inventory_request AS main_calculationtariffrule_enable_inventory_request,
  main_calculationtariffrule.enable_to_door AS main_calculationtariffrule_enable_to_door,
  main_calculationtariffrule.enable_thermobag AS main_calculationtariffrule_enable_thermobag,
  main_calculationtariffrule.enable_cargo_loaders AS main_calculationtariffrule_enable_cargo_loaders,
  main_calculationtariffrule.enable_jewels AS main_calculationtariffrule_enable_jewels,
  main_calculationtariffrule.enable_change_log AS main_calculationtariffrule_enable_change_log,
  main_calculationtariffrule.jewels_multiplication AS main_calculationtariffrule_jewels_multiplication,
  main_calculationtariffrule.jewels_addition AS main_calculationtariffrule_jewels_addition,
  main_calculationtariffrule.returndoc_multiplication AS main_calculationtariffrule_returndoc_multiplication,
  main_calculationtariffrule.returndoc_addition AS main_calculationtariffrule_returndoc_addition,
  main_calculationtariffrule.by_hand_addition AS main_calculationtariffrule_by_hand_addition,
  main_calculationtariffrule.cod_multiplication AS main_calculationtariffrule_cod_multiplication,
  main_calculationtariffrule.cod_addition AS main_calculationtariffrule_cod_addition,
  main_calculationtariffrule.np_multiplication AS main_calculationtariffrule_np_multiplication,
  main_calculationtariffrule.np_addition AS main_calculationtariffrule_np_addition,
  main_calculationtariffrule.hazardous_multiplication AS main_calculationtariffrule_hazardous_multiplication,
  main_calculationtariffrule.hazardous_addition AS main_calculationtariffrule_hazardous_addition,
  main_calculationtariffrule.scan_request_multiplication AS main_calculationtariffrule_scan_request_multiplication,
  main_calculationtariffrule.scan_request_addition AS main_calculationtariffrule_scan_request_addition,
  main_calculationtariffrule.inventory_request_multiplication AS main_calculationtariffrule_inventory_request_multiplicati_2,
  main_calculationtariffrule.inventory_request_addition AS main_calculationtariffrule_inventory_request_addition,
  main_calculationtariffrule.to_door_addition AS main_calculationtariffrule_to_door_addition,
  main_calculationtariffrule.cargo_loaders_addition AS main_calculationtariffrule_cargo_loaders_addition,
  main_calculationtariffrule.service_discount AS main_calculationtariffrule_service_discount,
  main_calculationtariffrule.created_by_calculation AS main_calculationtariffrule_created_by_calculation,
  main_calculationtariffrule.exclusive AS main_calculationtariffrule_exclusive,
  main_calculationtariffrule.enabled AS main_calculationtariffrule_enabled,
  main_calculationtariffrule.enable_b2b AS main_calculationtariffrule_enable_b2b,
  main_calculationtariffrule.enable_b2c AS main_calculationtariffrule_enable_b2c,
  main_calculationtariffrule.city_times_group_id AS main_calculationtariffrule_city_times_group_id,
  main_calculationtariffrule.geo_times_group_id AS main_calculationtariffrule_geo_times_group_id,
  main_calculationtariffrule.is_simplified_shipment_receipt AS main_calculationtariffrule_is_simplified_shipment_receipt,
  main_calculationtariffrule.today_get_weight_limit AS main_calculationtariffrule_today_get_weight_limit,
  main_calculationtariffrule_directions_1.calculationtariffrule_id AS main_calculationtariffrule_directions_1_calculationtariff_3,
  main_calculationtariffrule_directions_1.calculationtariffruledirection_id AS main_calculationtariffrule_directions_1_calculationtariff_4,
  main_calculationtariffrule_cargo_types_1.calculationtariffrule_id AS main_calculationtariffrule_cargo_types_1_calculationtarif_5,
  main_calculationtariffrule_cargo_types_1.cargotype_id AS main_calculationtariffrule_cargo_types_1_cargotype_id,
  main_calculationtariffrule_shipping_types_1.calculationtariffrule_id AS main_calculationtariffrule_shipping_types_1_calculationta_6,
  main_calculationtariffrule_shipping_types_1.calculationtariffruleshippingtype_id AS main_calculationtariffrule_shipping_types_1_calculationta_7,
  main_calculationtariffcityallow_1.id AS main_calculationtariffcityallow_1_id,
  main_calculationtariffcityallow_1.direction AS main_calculationtariffcityallow_1_direction,
  main_calculationtariffcityallow_1.action AS main_calculationtariffcityallow_1_action,
  main_calculationtariffcityallow_1.tariff_id AS main_calculationtariffcityallow_1_tariff_id,
  main_calculationtariffcityallow_1.city_id AS main_calculationtariffcityallow_1_city_id,
  main_calculationtariffcityallow_1.locality AS main_calculationtariffcityallow_1_locality,
  main_calculationtariffcityallow_1.region1 AS main_calculationtariffcityallow_1_region1,
  main_calculationtariffcityallow_1.region2 AS main_calculationtariffcityallow_1_region2,
  main_calculationtariffcityallow_1.iso AS main_calculationtariffcityallow_1_iso
FROM main_calculationtariffrule
  LEFT OUTER JOIN main_calculationtariffrule_directions AS main_calculationtariffrule_directions_1 ON main_calculationtariffrule.id = main_calculationtariffrule_directions_1.calculationtariffrule_id
  LEFT OUTER JOIN main_calculationtariffrule_cargo_types AS main_calculationtariffrule_cargo_types_1 ON main_calculationtariffrule.id = main_calculationtariffrule_cargo_types_1.calculationtariffrule_id
  LEFT OUTER JOIN main_calculationtariffrule_shipping_types AS main_calculationtariffrule_shipping_types_1 ON main_calculationtariffrule.id = main_calculationtariffrule_shipping_types_1.calculationtariffrule_id
  LEFT OUTER JOIN main_calculationtariffcityallow AS main_calculationtariffcityallow_1 ON main_calculationtariffrule.id = main_calculationtariffcityallow_1.tariff_id
WHERE main_calculationtariffrule.company = ?
  AND main_calculationtariffrule.enabled = ?
  AND main_calculationtariffrule.min_limit_weight <= ?
  AND main_calculationtariffrule.max_limit_weight > ?
  AND main_calculationtariffrule.min_limit_volume <= ?
  AND main_calculationtariffrule.max_limit_volume > ?
  AND main_calculationtariffrule.tariff IN (?, ?, ?)
ORDER BY exclusive desc

The request looks correct and is executed without a problem

[rjuju]

Hi,

On which widget(s) do you get this error?

[tarkhil]

On Predicates, in Example values
Looks like explain gets request with placeholder

[rjuju]

I see. What it means is that powa couldn't find a stored predicate for that query in the given time interval, and had a fallback on just using the raw pg_stat_statements query. On postgres 16 and above the solution would be to rely on the GENERIC_PLAN option, but I don't think that you have such a version.

[tarkhil]

Postgres 16 is here

psql (16.6 (Debian 16.6-1.pgdg110+1))

But for some reason I don't see any stored predicates at all

[tarkhil]

Can I check something to clarify the situation?

[rjuju]

Well, it's not unexpected to not see any predicate for a given query, that depends on your workload and how pg_qualstats is configured (by default it samples 1/max_connections query, so if the queyr is not executed often enough it just won't be sampled). Unless you mean there is nothing stored in the powa catalog for pg_qualstats, then it's probably a configuration issue.

[tarkhil]

Okay, I need to check pg_qualstats and find out if I'm doing something wrong and nothing is stored there?

Any ideas how to check why GENERIC_PLAN is not used?

[tarkhil]

GOT IT!!!

Some statements are recorded as "select * from table where field is ?"

GENERIC_PLAN fails on it, expecting "select * from table where field is $1"

So what should I do next?

[rjuju]

Any ideas how to check why GENERIC_PLAN is not used?

Because we didn't implement it. That's on the list but with the recent version 5 we have been busy fixing the bugs introduced in the v4 -> v5. Once everything is stable we will start again adding new feature, such as this one

Some statements are recorded as "select * from table where field is ?"

I think that the statements are recorded as $X, but we change them to ? in powa-web (https://github.com/powa-team/powa-web/blob/master/powa/sql/__init__.py#L21 I think). It was done a very long time ago, back when pg_stat_statements was also using ? when removing constants and before GENERIC_PLAN was introduced. We will have to change that, and go through the history to see what can still issue ? until which version, and try to change that back to parameters.

[tarkhil]

Okay, can I do something with this?

[rjuju]

All contributions are welcome. I think that some big improvements are needed on those widgets though. The initial idea was to be able to compare execution plan with different "kind of predicates" (most filtering, less filtering and so on), as a way to see if it impacts decision on indexes or something. For instance if the most filtering predicates could benefit from an index but not the least filtering, but if the least filtering is also the less frequent or is just a single value then you can maybe create a partial index or something like that. The per-predicate statistic page is also meant to help for that. Anyway, if we can't get the specific kind of predicate, it's not really useful to still try to get an explain plan since it doesn't bring information, and instead we should return explanation about the issue (no qual found, other problem or something).