Thread: exceeded MAX_ALLOCATED_DESCS while trying to open file

exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Steve.Toutant@inspq.qc.ca
Date:

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

Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
"ktm@rice.edu"
Date:
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


Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Igor Neyman
Date:
>
> 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


Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Steve.Toutant@inspq.qc.ca
Date:

The csv has 34 columns and I need to create a crosstab (using 8 columns so I can't use built in crosstab functions). That is why I need to make several sql select.


I'm using foreign table so my client can update is csv file without having to run the ETL to update a table in postgres.
Using temporary table means I got to know when the csv is updated...unless there is something I misunderstood.

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




"ktm@rice.edu" <ktm

2013-05-28 11:48

A
Steve.Toutant@inspq.qc.ca
cc
pgsql-admin@postgresql.org
Objet
Re: [ADMIN] exceeded MAX_ALLOCATED_DESCS while trying to open file







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



Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Alvaro Herrera
Date:
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


Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Tom Lane
Date:
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


Re: exceeded MAX_ALLOCATED_DESCS while trying to open file

From
Tom Lane
Date:
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