Heureka! thanks so much for your help, patience and the right hunch. Actually I am glad now I ran into that stack issue
(andyou) cause the entire thing is also much faster now.
I changed my app to emit strings like you suggested and it works, also with smaller max_stack_depth.
Fwiw, I was not stubbornly insisting on nesting operators. Actually I switched from "=>" to the hstore function cause
a note in the manual said it was deprecated (http://www.postgresql.org/docs/9.0/static/hstore.html). Somehow I must
haveunderstand that note the wrong way.
What's your take on that operator being deprecated?
regards, matt bannert
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: Saturday, April 09, 2016 5:25 PM
To: Bannert Matthias
Cc: Charles Clavadetscher; pgsql-general@postgresql.org
Subject: Re: [GENERAL] max_stack_depth problem though query is substantially smaller
"Bannert Matthias" <bannert@kof.ethz.ch> writes:
> [ very deep stack of parser transformExprRecurse calls ]
> #20137 0x00007fe7fb80ab8c in pg_analyze_and_rewrite (parsetree=parsetree@entry=0x7fe7fffdb2a0,
query_string=query_string@entry=0x7fe7fdf606b0"INSERT INTO ts_updates(ts_key, ts_data, ts_frequency) VALUES
('some_id.sector_all.news_all_d',hstore('1900-01-01','-0.395131869823009')||hstore('1900-01-02','-0.395131869823009')||hstore('1"...,
paramTypes=paramTypes@entry=0x0,numParams=numParams@entry=0) at
/build/postgresql-9.3-G1RSAD/postgresql-9.3-9.3.11/build/../src/backend/tcop/postgres.c:640
The SQL fragment we can see here suggests that your "40K entry hstore" is
getting built up by stringing together 40K hstore concatenation operators.
Don't do that. Even without the parser stack depth issue, it's uselessly
inefficient. I presume you're generating this statement mechanically,
not by hand, so you could equally well have the app emit
'1900-01-01 => -0.395131869823009, 1900-01-02 => -0.395131869823009, ...'::hstore
which would look like a single hstore literal to the parser, and be
processed much more quickly.
If you insist on emitting SQL statements that have operators nested
to such depths, then yes you'll need to increase max_stack_depth to
whatever it takes to allow it.
regards, tom lane
