Thread: With Recursive / Recursive View question
This select is almost instant:
WITH RECURSIVE pathname(id, parent_id, basename) AS (
SELECT child.id, child.parent_id, child.basename
FROM dirents child
WHERE basename = '10732.emlx'
UNION ALL
SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename)
FROM dirents parent, pathname child
WHERE parent.id = child.parent_id
)
SELECT basename FROM pathname where parent_id IS NULL;
Note that the non-recursive term selects the children and the recursion is “out” towards the ancestors.
This select doesn’t complete before I get impatient:
CREATE RECURSIVE VIEW pathname(id, basename, parent_id, ino, ext, fullpath) AS
SELECT id, basename, parent_id, ino, ext, basename
FROM dirents
WHERE parent_id IS NULL
UNION ALL
SELECT child.id, child.basename, child.parent_id, child.ino, child.ext, CONCAT(parent.fullpath, '/', child.basename)
FROM dirents child, pathname parent
WHERE parent.id = child.parent_id;SELECT * FROM pathname WHERE basename = '10732.emlx’;
In this case, the non-recursive term starts at the top of the directory trees and the recursion works “in” towards the children.
I’m not surprised that the first is fast and the second is very slow. My problem is I currently have a file called recurse.sql which is the top query. I go in and edit that file and then execute it via psql -f recurse.sql. What I’m attempting to do in the second example is to create a view and then use select on the view to select the rows that I’m looking for.
To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors?
Thank you for your time,
Perry
Attachment
> On Aug 20, 2022, at 15:42, Perry Smith <pedz@easesoftware.com> wrote: > > To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors? Assuming that the concern is that you want to parameterize this predicate: WHERE basename = '10732.emlx' ... you might consider an SQL function taking basename as a parameter.
> On Aug 20, 2022, at 19:38, Christophe Pettus <xof@thebuild.com> wrote: > > > >> On Aug 20, 2022, at 15:42, Perry Smith <pedz@easesoftware.com> wrote: >> >> To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors? > > Assuming that the concern is that you want to parameterize this predicate: > > WHERE basename = '10732.emlx' > > ... you might consider an SQL function taking basename as a parameter. Yea. If I did a function, I would just pass in the id. I’ve used functions only rarely. For whatever reason, I’ve alwaysbeen very skittish around them. But perhaps I need to grow up. Thank you again, Perry
Attachment
On Aug 20, 2022, at 19:38, Christophe Pettus <xof@thebuild.com> wrote:On Aug 20, 2022, at 15:42, Perry Smith <pedz@easesoftware.com> wrote:
To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors?
Assuming that the concern is that you want to parameterize this predicate:
WHERE basename = '10732.emlx'
... you might consider an SQL function taking basename as a parameter.
That wasn’t so bad…
CREATE OR REPLACE FUNCTION pathname(in_id bigint)RETURNS character varying AS$$DECLAREfullpath character varying;BEGINWITH RECURSIVE pathname(id, parent_id, basename) AS (SELECT child.id, child.parent_id, child.basenameFROM dirents childWHERE child.id = in_idUNION ALLSELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename)FROM dirents parent, pathname childWHERE parent.id = child.parent_id)SELECT basename INTO fullpath FROM pathname where parent_id IS NULL;RETURN fullpath;END;$$ LANGUAGE plpgsql;SELECT pathname(id) FROM dirents WHERE basename = 'OSX';
Thank you … again! :-)
Perry
Attachment
On 2022-08-20 17:42:27 -0500, Perry Smith wrote: > This select is almost instant: > > > WITH RECURSIVE pathname(id, parent_id, basename) AS ( > SELECT child.id, child.parent_id, child.basename > FROM dirents child > WHERE basename = '10732.emlx' > UNION ALL > SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', > child.basename) > FROM dirents parent, pathname child > WHERE parent.id = child.parent_id > ) > SELECT basename FROM pathname where parent_id IS NULL; > > > Note that the non-recursive term selects the children and the recursion is > “out” towards the ancestors. [...] > To rephrase, is it possible to write a view that would work from the child > terms out towards the ancestors? I see that you also have a solution using a function but I thought I should give it a shot using a view: create view tree as WITH RECURSIVE pathname(id, parent_id, fullpath, leafname) AS ( SELECT child.id, child.parent_id, child.basename, child.basename FROM dirents child UNION ALL SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.fullpath), leafname FROM dirents parent, pathname child WHERE parent.id = child.parent_id ) SELECT * FROM pathname where parent_id is null; This does functionally do what you want (IIUC): hjp=> select * from tree where leafname = '10732.emlx'; ╔════╤═══════════╤════════════════════════════╤════════════╗ ║ id │ parent_id │ fullpath │ leafname ║ ╟────┼───────────┼────────────────────────────┼────────────╢ ║ 5 │ (∅) │ home/alice/Mail/10732.emlx │ 10732.emlx ║ ╚════╧═══════════╧════════════════════════════╧════════════╝ (1 row) hjp=> select * from tree where leafname = 'bin'; ╔════╤═══════════╤══════════════╤══════════╗ ║ id │ parent_id │ fullpath │ leafname ║ ╟────┼───────────┼──────────────┼──────────╢ ║ 1 │ (∅) │ bin │ bin ║ ║ 23 │ (∅) │ usr/bin │ bin ║ ║ 5 │ (∅) │ home/bob/bin │ bin ║ ╚════╧═══════════╧══════════════╧══════════╝ (3 rows) However, to be performant the optimizer would have to recognize that it can push «leafname = ...» all the way down into the initial subquery of the recursive query. That's theoretically possible but I would be surprised if it actually did this. (It didn't in my tests, but my test data set was too small to get it to even use indexes with normal queries). hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"