Обсуждение: exceeded MAX_ALLOCATED_DESCS while trying to open file

Hi,

Using postgres 9.2, I created a FOREIGN TABLE on a csv file. It works.

But I need to change the structure of the data and I created several sql select using union, like

select .... from .foreign table... where...

union

select .... from .foreign table... where...

union

...

It works for the first 10 select but then I get
ERROR:  exceeded MAX_ALLOCATED_DESCS while trying to open file "/srv/www/data/antidote/registre.csv"

What is it MAX_ALLOCATED_DESCS? and how can I modify it without changing the code and re-compile? I tried using postgref.conf but this parameters is not recognize.
thanks
steve

On Tue, May 28, 2013 at 11:28:02AM -0400, Steve.Toutant@inspq.qc.ca wrote:
> Hi,
> Using postgres 9.2, I created a FOREIGN TABLE on a csv file. It works.
> But I need to change the structure of the data and I created several sql
> select using union, like
> select .... from .foreign table... where...
> union
> select .... from .foreign table... where...
> union
> ...
> It works for the first 10 select but then I get
> ERROR:  exceeded MAX_ALLOCATED_DESCS while trying to open file
> "/srv/www/data/antidote/registre.csv"
>
> What is it MAX_ALLOCATED_DESCS? and how can I modify it without changing
> the code and re-compile? I tried using postgref.conf but this parameters
> is not recognize.
> thanks
> steve

Hi Steve,

Each SELECT opens your FOREIGN TABLE so you are opening it 10 times. This is
hardcoded by a #define in ./src/backend/storage/file/fd.c during the build so
you would need to recompile the software yourself to change it. Can you re-
write your query using a single SELECT or possibly read the data into a
temporary table for processing?

Regards,
Ken

>
> Hi Steve,
>
> Each SELECT opens your FOREIGN TABLE so you are opening it 10 times.
> This is hardcoded by a #define in ./src/backend/storage/file/fd.c
> during the build so you would need to recompile the software yourself
> to change it. Can you re- write your query using a single SELECT or
> possibly read the data into a temporary table for processing?
>
> Regards,
> Ken
>

Hi Ken,

So, it means that number of (different) foreign tables joined in single select cannot exceed 10, right?

Regards,
Igor Neyman

Perhaps the solution is to automate the process to copy the csv in a table.....

ktm@rice.edu wrote:

> Each SELECT opens your FOREIGN TABLE so you are opening it 10 times. This is
> hardcoded by a #define in ./src/backend/storage/file/fd.c during the build so
> you would need to recompile the software yourself to change it. Can you re-
> write your query using a single SELECT or possibly read the data into a
> temporary table for processing?

To me this sounds like a bug in whatever FDW is being used.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> ktm@rice.edu wrote:
>> Each SELECT opens your FOREIGN TABLE so you are opening it 10 times. This is
>> hardcoded by a #define in ./src/backend/storage/file/fd.c during the build so
>> you would need to recompile the software yourself to change it. Can you re-
>> write your query using a single SELECT or possibly read the data into a
>> temporary table for processing?

> To me this sounds like a bug in whatever FDW is being used.

Yeah, we need to fix file_fdw so that it's using virtual rather than
physical file descriptors ...

            regards, tom lane

Steve.Toutant@inspq.qc.ca writes:
> Using postgres 9.2, I created a FOREIGN TABLE on a csv file. It works.
> But I need to change the structure of the data and I created several sql
> select using union, like
> select .... from .foreign table... where...
> union
> select .... from .foreign table... where...
> union
> ...
> It works for the first 10 select but then I get
> ERROR:  exceeded MAX_ALLOCATED_DESCS while trying to open file
> "/srv/www/data/antidote/registre.csv"

FYI, I've committed some changes that will relax this limit in future
releases.

            regards, tom lane