Re: Too many range table entries error

On Mon, 25 Jun 2018 at 13:40, Andres Freund <andres(at)anarazel(dot)de> 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 40000 table references (in the
> > complete tree considering all the dependent views recursively) don't
> work,
> > but it works with 20000 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
>