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?
