Heureka! thanks so much for your help, patience and the right hunch. Actually I 
am glad now I ran into that stack issue (and you) 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 have 
understand that note the wrong way. 
What's your take on that operator being deprecated? 

regards, matt bannert



________________________________________
From: Tom Lane [t...@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" <bann...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to