Postgres Pro
Downloads
Home   >   mailing lists

Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view - Mailing list pgsql-novice

From Aditya Rastogi
Subject Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view
Date
Msg-id BAY176-W37F28B75E21E95BCF9FBF9C5860@phx.gbl
Whole thread Raw
In response to Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
Tree view

Thanks Tom, I'll try rewriting the query, with the distinct list of pairs. But I am still curious to know two things and would really appreciate if you could give me some pointers to help me understand them:
     
1. How does the stack depth come into play while evaluating this query ? What part of the query makes recursive calls ?
2. What would be the recommended approach to make such queries ? I tried putting the pairs in a temporary table and then joined it against the view to get the relevant tuples, which seemed to work.

Thanks,
Aditya

> From: tgl@sss.pgh.pa.us
> To: adirastogi@outlook.com
> CC: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] getting ERROR: stack depth limit exceeded on a WHERE IN query on a view
> Date: Mon, 24 Feb 2014 11:40:06 -0500
>
> Aditya Rastogi <adirastogi@outlook.com> writes:
> > The query is similar to the following query:
> > select count(*) from gui_die_summary where (x_coord, y_coord) in ((25,5),(41,13),(25,7),(28,3),(25,8),(34,7),(26,6),(21,10)); ,
> > only that the list of pairs specified in the in clause is pretty large - around 5000-4000 pairs and that's when I get the stack depth limit exceed error.
>
> Even without the stack depth issue, that would perform pretty horridly for
> so many pairs.
>
> Do you know that the pairs are all distinct, so that you don't really need
> the duplicate-elimination behavior of IN? If so, you could recast this
> like so:
>
> select count(*) from
> gui_die_summary,
> (values (25,5),(41,13),(25,7),(28,3),(25,8),(34,7),(26,6),(21,10)) v(vx,vy)
> where (x_coord, y_coord) = (vx, vy);
>
> which should work better for large numbers of pairs.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view
Next
From: Tom Lane
Date:
Subject: Re: getting ERROR: stack depth limit exceeded on a WHERE IN query on a view