PostgreSQL with recursive -> prevent deadlocks/recursion

Question

Hi i want to select objects with postgres recursive. But i cannot guarantee that every data in the database is correct. So i'ts not impossible that an object has for example itself as parent id.

Is it possible to stop when an object already exists? What i've tried:

with recursive sumthis(myobjectid,parent_myobjectid) as (
    select myobjectid, parent_myobjectid
    from myobjectentity
    where myobjectid = 243358
    union all
    select C.myobjectid, C.parent_myobjectid
    from sumthis P
    inner join myobjectentity C on P.myobjectid = C.parent_myobjectid AND (P.myobjectid NOT IN (SELECT myobjectid FROM sumthis))

)

SELECT * FROM myobjectentity WHERE myobjectid IN (select myobjectid from sumthis)

AND

with recursive sumthis(myobjectid,parent_myobjectid) as (
    select myobjectid, parent_myobjectid
    from myobjectentity
    where myobjectid = 243358
    union all
    select C.myobjectid, C.parent_myobjectid
    from sumthis P
    inner join myobjectentity C on P.myobjectid = C.parent_myobjectid
    Limit 100

)

SELECT * FROM myobjectentity WHERE myobjectid IN (select myobjectid from sumthis)

This is a special case of non-repeating directed graph traversal. Thankfully as you discovered, recursive CTEs (used right) help with this. — Craig Ringer
– Craig Ringer, Commented Aug 4, 2014 at 15:34

wutzebaer · Accepted Answer · 2014-08-04 15:15:11Z

1

Ok the answer is reasy, i just had to change

union all

to

union

answered Aug 4, 2014 at 15:15

wutzebaer

15k20 gold badges107 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL with recursive -> prevent deadlocks/recursion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related