Thread: 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
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 |
|
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
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