Page MenuHomeSoftware Heritage
Paste P242

[fixed] Hanging 'task reading' function on filtering period without data
ActivePublic
Actions

Authored by ardumont on Mar 22 2018, 12:07 PM.
begin;
create type task_record as (
task_id bigint,
task_policy task_policy,
task_status task_status,
task_run_id bigint,
arguments jsonb,
type text,
backend_id text,
metadata jsonb,
scheduled timestamptz,
started timestamptz,
ended timestamptz
);
create or replace function swh_scheduler_task_to_archive(
ts timestamptz, last_id bigint default -1, lim bigint default 10)
returns setof task_record
language sql
as $$
select t.id as task_id, t.policy as task_policy,
t.status as task_status, tr.id as task_run_id,
t.arguments, t.type, tr.backend_id, tr.metadata,
tr.scheduled, tr.started, tr.ended
from task t inner join task_run tr on t.id=tr.task
where ((t.policy = 'oneshot' and t.status ='completed') or
(t.policy = 'recurring' and t.status ='disabled')) and
tr.ended < ts and
t.id > last_id
order by t.id
limit lim;
$$;
select * from swh_scheduler_task_to_archive('2016-02-23'::timestamptz); -- fine
select * from swh_scheduler_task_to_archive('2016-02-22'::timestamptz); -- hangs
select t.id as task_id, t.policy as task_policy,
t.status as task_status, tr.id as task_run_id,
t.arguments, t.type, tr.backend_id, tr.metadata,
tr.scheduled, tr.started, tr.ended
from task t inner join task_run tr on t.id=tr.task
where ((t.policy = 'oneshot' and t.status ='completed') or
(t.policy = 'recurring' and t.status ='disabled')) and
tr.ended < '2016-02-22' and
t.id > -1
order by t.id
limit 10; -- fine, no data
select t.id as task_id, t.policy as task_policy,
t.status as task_status, tr.id as task_run_id,
t.arguments, t.type, tr.backend_id, tr.metadata,
tr.scheduled, tr.started, tr.ended
from task t inner join task_run tr on t.id=tr.task
where ((t.policy = 'oneshot' and t.status ='completed') or
(t.policy = 'recurring' and t.status ='disabled')) and
tr.ended < '2016-02-23' and
t.id > -1
order by t.id
limit 10; -- fine
rollback;

Event Timeline

ardumont created this paste.Mar 22 2018, 12:07 PM
ardumont added a subscriber: olasd.Mar 22 2018, 12:20 PM

as @olasd pointed out, changing P242$19 from language sql to language sql stable makes the behavior of the function match the inline query.
And we can make it stable because it has no side-effect.

source:

12:03:49 ardumont | olasd: did you already have an sql function hanging while the equivalent inline query (same input, same dataset) is working fine?
12:04:36    olasd | what's in the function?
12:05:12 ardumont | select with join on 2 tables with filtering
12:05:23 ardumont | the hanging occurs on period when there is no data
12:05:29 ardumont | that match
12:07:51 ardumont | https://forge.softwareheritage.org/P242
12:10:09    olasd | try marking the function as stable in the declaration
12:10:40    olasd | volatile functions turn off a bunch of optimizer stuff
12:10:47 ardumont | ohhh
12:11:32    olasd | (it'll also allow the optimizer to inline the function call)
12:11:38 ardumont | you rock
12:11:39 ardumont | ;)
12:12:06    olasd | (most "read only" functions should be marked as stable)
12:12:30 ardumont | ok
12:13:01    olasd | (which tells the optimizer that their output is constant in the same transaction, and that (if the function is plain sql) it can inline the queries)

Related T986

ardumont changed the title of this paste from hanging function on filtering task reading sql function to Hanging 'task reading' function on filtering period without data.Mar 22 2018, 12:23 PM
ardumont updated the paste's language from autodetect to sql.
ardumont added a project: Scheduling utilities.
ardumont changed the title of this paste from Hanging 'task reading' function on filtering period without data to [fixed] Hanging 'task reading' function on filtering period without data.
About · Developer information · Legal