Does postgresql statement_timeout also apply to a blocked query

Does postgresql statement_timeout also apply to a blocked query (version 9.6)?

From my tests this does not seem to be the case. I created a blocking query using the following example taken from https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/

CREATE TABLE items (   key text primary key,   value jsonb );  BEGIN; ALTER TABLE items ADD COLUMN last_update timestamptz;

In a separate SQL session I do the following

set statement_timeout to 10000; commit; select * from items;

Note there is no global timeout set at the database level.

This query does not return with a timeout after 10 seconds. The timeout documentation states "The timeout is measured from the time a command arrives at the server until it is completed by the server." hence I would expect it to apply to blocked queries as well.

Utilising an existing page while using the “s” query parameter

I’m trying to load/utilise an existing page template (templates/results.php) while making use of the "s" (search) query parameter. For example: example.com/results?s=lorem

Currently, this results in a 404.

I’ve got a filter using template_includes, however it appears that the page has already been "decided" before that hook/filter (is_search(), is_page('results') are both false) before loading the template. I could just load the template files based on some other parameters, but then I’d have to set the title and other things away from the 404 page.

The pagename and s fields are recognised in the global $ wp_query‘s raw query vars, so it’d just be a case of "deciding" what the query actually is before the template is hit.

Usecase for this being able to show a filtered set of posts for a custom post type "listings". The Listings archive already shows listings, automatically filtered for "open" ones, and optionally filtered for its taxonomy and "s" for any searches. I’d have the results page do the same thing, being able to pick up the same optional query parameters as its archive counterpart.

How can I load a page, and utilise an existing page template while still using the in-built s query parameter?

This answer’s solution 1 mentions replacing the default ‘s’ query var with a custom one, aiming to avoid that and hopefully filter/hook in before WordPress decides to search the results page for the ‘s’ string

SQL Dynamic Query based on user input in PowerBI

I am pulling data from inSQL using the SQL Query below:

SET QUOTED_IDENTIFIER OFF  SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [X],[Y], [Z], [A]  FROM WideHistory  WHERE wwRetrievalMode = 'Cyclic'  AND wwResolution = 1000  AND wwVersion = 'Latest'  AND DateTime >= '20210601 20:02:33.000'  AND DateTime <= '20210607 20:02:34.000'")

I’m using this table in PowerBI and want [X],[Y], [Z], [A] & the DateTime values to dynamically change based on user input.

Any advice on this would be greatly appreciated. I am new to PowerBI and SQL.

One-row postgres query as CTE/subquery much slower when passing subquery field into function / maybe related to inlining?

I’m using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).

I can’t figure out why Query2 below is so much slower than Query1 (they should basically be almost exactly the same, maybe a few ms difference at most)…

Query1: This query takes 49 seconds:

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count FROM t1

Query2: This query takes 4 minutes and 30 seconds (only one line difference):

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count,             -- the line below is the only difference to Query1:             util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage FROM t1

Before this point, and with some extra columns in the outer the query (which should have made almost zero difference), the whole query was insanely slow, like 25 minutes, which I think was due to inlining maybe? Hence the OFFSET 0 being added into both queries (which does help a lot).
I’ve also been swapping between using the above CTEs vs subqueries, but with the OFFSET 0 included it doesn’t seem to make any difference.

Here’s the definitions of the functions being called in Query2:

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $  $   BEGIN     RETURN ROUND($  1::NUMERIC * 100, $  2)::TEXT || '%'; END; $  $   LANGUAGE plpgsql IMMUTABLE;   CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $  $   BEGIN          RETURN CASE          WHEN $  2 > 0 THEN util.ratio_to_percentage_string($  1::FLOAT / $  2::FLOAT, 2)         ELSE 'divide_by_zero'          END         ;  END; $  $   LANGUAGE plpgsql IMMUTABLE;

As you can see it’s a very simple function, which is only being called once, from the single row the whole thing produces… how can this cause such a massive slowdown? And why is it affecting whether postgres inlines the initial subquery/WITH (or whatever else might be going on here)

EXPLAIN ANALYZE outputs:

Query1: https://explain.depesz.com/s/bq7u
Query2: https://explain.depesz.com/s/9w3rY

Force MySQL index on OR query?

I have a query that could be resolved 99% of the time using an index but needs a table scan for a few queries:

SELECT * FROM mytable WHERE idx=123 OR name like "%foo%" limit 1

The execution time is way above the regular index lookup even in the cases when an item is found and EXPLAIN confirms that MySQL 5.7 is always doing a table scan.

I tried to add a FORCE INDEX(primary), but that doesn’t help.

Unfortunately I this needs to be done in a single query.

Any ideas how to speed up the queries where an item is found in the index ?

How to use WP_Query without breaking the main Query

The WP Query class can be used to make almost any imaginable query to the WP database. It’s amazing.

But it’s also used when WordPress is loaded, to run the main query for the current page request.

A common practice is to use wp_reset_query() and/or wp_reset_postdata()

But it’s unclear when to use one or the other, or if they must be used at all. And I don’t want to use them just in case but only when they are really needed.

So I would like to know what is the correct way to use the power of WP Query without breaking the main query.

display text generated via custom get query string

I’m trying to display some text on my page, generating it via get query string, e.g. calling this url: mysite.com/page/?letter=exampletext

If I understand correctly in such cases it is needed to "add them to the public query variables available to WP_Query" (stated here https://developer.wordpress.org/reference/functions/get_query_var/), so I followed directions adding the following code to my page template:

<?php  function queries( $  qvars ) {     $  qvars[] = 'letter';     return $  qvars;     } add_filter( 'query_vars', 'queries' );  $  text = get_query_var( 'letter' ); echo "letter is: ".$  text; ?>

unfortunately this is not working as expected, since it only diplays the exlicit part of the string ("letter is: ").

What am I missing? Thanks everyone

How to make a content management system to load webpages without a PHP loader and its query strings?

I have a MediaWiki 1.35.2 Hebrew website which is principally all core; I host that website on a Apache, Nginx (as reverse proxy), PHP and MySQL hosting environment.

The default behavior of MediaWiki is to load any webpage with load.php and often many query strings;
It often raises errors in Google Pagespeed Insights about too many resources which consume loading time (maybe it’s better to load these informally without query strings) and it also makes my URLs "long" instead "short" (i.e. without using this load.php method) when they are already long because of Right-To-Left language Encoding.

For some reason/s I have a very hard time finding my way in the following MediaWiki guides on how to get shorter URLs without load.php and its query strings (guides that include explicitly outdated parts and/or many warnings and exceptions):

Manual:Short URL
Manual:Short URL/Apache

Run calculation not working in sub query, “does not exist”

PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I have a query that provides the count of 2 things over 2 time periods:

time period of this week, start of the week (Sunday/Monday midnight) -> today
time period of last week, start of last week (Sunday/Monday midnight) -> today but last week

What I would like is to also perform a calculation on the two "car" count ints that are returned and provide this with the query result.

I can of course do this in the consuming app, but I’d love to be able to do it in my SQL.

Working SQL

This is the base SQL and is what I currently have and working:

WITH last_week AS (   SELECT COUNT(car_name) as car_count_last_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')   AND car_time <= CURRENT_DATE - INTERVAL '6 days' ), current_week AS (   SELECT COUNT(car_name) AS car_count_current_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE)   AND car_time <= CURRENT_DATE ) SELECT car_name,   date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,   CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,   date_trunc('week', CURRENT_DATE) AS start_of_current_week,   CURRENT_DATE AS today,   car_count_last_week,   car_count_current_week   FROM car_store   CROSS JOIN last_week, current_week   WHERE car_name = 'awesome'   GROUP BY car_name, car_count_last_week, car_count_current_week   ORDER BY car_name;

Setup DB table

CREATE TABLE IF NOT EXISTS car_store (      car_id INT GENERATED ALWAYS AS IDENTITY,      car_time TIMESTAMP NOT NULL,      car_name VARCHAR(255) NOT NULL,      PRIMARY KEY(car_id)   )  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594'); INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');  INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

Thing that causes error

I would like add the following code – to calculate difference_between_weeks:

ROUND(car_count_current_week/(car_count_last_week/100) - 100)     AS difference_between_weeks

I have tried calculating in the columns, I have tried adding another sub query. But I don’t seem to be able to calculate the difference_between_weeks as it tells me "does not exist" or ERROR: division by zero

Full SQL that errors

An example of the SQL with the added code that errors:

WITH last_week AS (   SELECT COUNT(car_time) as car_count_last_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')   AND car_time <= CURRENT_DATE - INTERVAL '6 days' ), current_week AS (   SELECT COUNT(car_time) AS car_count_current_week   FROM car_store   WHERE car_name = 'awesome'   AND car_time >= date_trunc('week', CURRENT_DATE)   AND car_time <= CURRENT_DATE ) SELECT car_name,   date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,   CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,   date_trunc('week', CURRENT_DATE) AS start_of_current_week,   CURRENT_DATE AS today,   car_count_last_week,   car_count_current_week,   ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks   FROM car_store   CROSS JOIN last_week, current_week   WHERE car_name = 'awesome'   GROUP BY car_name, car_count_last_week, car_count_current_week   ORDER BY car_name;

Returned error

I get the following error:

ERROR:  division by zero SQL state: 22012

I feel like I am close but I also feel this may not be possible? Any pointers to what I am missing would be hugely appreciated.

Please let me know if I have missed something?

Thanks

How to catch the search query when the user comes from a search engine to the web site?

Is it possible to transfer a search query to the url or to catch it somehow in order to show more relevant similar products on the landing page ? For ex.

if the search query is "red tiles" we would like to show other variants of red tiles
if the search query is "Versace tiles" we would like to show other variants from the same brand