Too many range table entries error
Hello. Please could you help debug the error "too many range table entries”? This error occurs when querying a view that is dependent on many other views (i.e. a view high up in the pyramid of views that we've constructed). I get this error when running select * on the view, or when running an explain analyse on the select. Views that use a total of more than around 4 table references (in the complete tree considering all the dependent views recursively) don't work, but it works with 2 table references. What is the maximum number of table references possible? Can I increase this number somehow? Perhaps relevant: Postgres docs, what is range table https://www.postgresql.org/docs/current/static/querytree.html postgres src, error message https://github.com/postgres/postgres/blob/master/src/backend/optimizer/plan/setrefs.c Postgres version 10.3 from official docker image. Thanks Akshaya
Re: Too many range table entries error
On Mon, 25 Jun 2018 at 13:40, Andres Freund wrote: > Hi, > > On 2018-06-25 13:02:37 +0530, Akshaya Acharya wrote: > > Hello. > > > > > > Please could you help debug the error "too many range table entries”? > > > > > > This error occurs when querying a view that is dependent on many other > > views (i.e. a view high up in the pyramid of views that we've > constructed). > > > > > > I get this error when running select * on the view, or when running an > > explain analyse on the select. > > > > > > Views that use a total of more than around 4 table references (in the > > complete tree considering all the dependent views recursively) don't > work, > > but it works with 2 table references. What is the maximum number of > > table references possible? > > Why are you doing this? I can't imagine queries with that many table > references ever being something useful? I'm pretty sure there's better > solutions for what you're doing. > Our entire application—all our business logic—is built as layers of views inside the database. The ref counts sort of multiple at each layer, hence the large number. > > > > Can I increase this number somehow? > > It's not impossible, it's not entirely trivial either. The relevant > variables currently are 16bit wide, and the limit is close to the max > for that. > I understand. At slide 25 of this presentation a patch is indicated. Is this relevant to our situation? https://www.slideshare.net/hansjurgenschonig/postgresql-joining-1-million-tables Alternatively we will have to optimize our views or change the architecture of our application? Is there any other way to resolve this situation? > > Greetings, > > Andres Freund >
Re: Too many range table entries error
Thank you very much for your guidance on this. I was speaking with a friend about this, and he said something to the effect of "keep it aside and do it later" and then a solution hit me. Since we can allow this data to be stale in our case, replacing some key views in the hierarchy of views with materialized views has worked for us. With regards to the architecture/design, I am still learning as I go along, I will articulate my thoughts and post later. In the long term, we will redesign the system with this new knowledge in mind. Regards Akshaya On Tue, 26 Jun 2018 at 06:08, Tom Lane wrote: > Andres Freund writes: > > On 2018-06-25 13:46:06 +0530, Akshaya Acharya wrote: > >> Our entire application-all our business logic-is built as layers of > views > >> inside the database. The ref counts sort of multiple at each layer, > hence > >> the large number. > > > That still doesn't explain how you realistically get to 40k references, > > and how that's a reasonable design. > > The short answer here is that even if the system accepted queries with > that many tables, it's really unlikely to perform acceptably --- in fact, > I'm a bit astonished that you even found a way to reach this error without > having waited a few hours beforehand. And we are *not* going to promise > to fix all the performance issues you will hit with a schema design like > this. Redesign. Please. > > regards, tom lane >
Re: Refresh materialized view deadlocking?
Hi Xiao From https://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html For concurrently option " Even with this option only one REFRESH at a time may run against any one materialized view. " I would guess yes. Regards, Akshaya On Tue, 10 Jul 2018 at 23:24, Xiao Ba wrote: > I have a materialized view that gets refreshed concurrently every 10 > seconds (b/c it contains time sensitive stuff). At times it appears to get > backed up so there are multiple refreshes queued and then gets stuck > waiting for a lock? Does anybody know if this is expected behavior? Am I > supposed to be manually managing to ensure that only one refresh is being > requested at a time? > > Thanks, > Xiao >
