Error 28 "Out of Stack Space" executing a huge query with VB6 & ADO 2.8

Scenario:
Executing an SQL command from a Visual Basic 6 application using ADO Connection.Execute method through PostgreSQL OLEDB Provider to a PostgreSQL 9.2 database.

Query:
It's a simple EXECUTE prepared_statement_name (x, y, z), though it involves a PostGIS geometry type, thus it becomes something like:

EXECUTE prepared_statement_name (1, ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 900001));

Problem:
When the geometry is a huge and complex MULTIPOLYGON which contains many vertexes, the query becomes very lengthy (a few thousands characters) and the Connection.Execute method causes a Error 28: "Out Of Stack Space".

There is no recursion or nested loops involved in the process, it's quite clear the error is due to the excessive length of the query.

I think I could avoid the error if I passed the huge query in "chunks" to the provider before executing it, but this is just an idea and I don't know wether it is possible or not and how.

I have no clue, any help is appreciated.

Solution

Since it sounds like this is a VB6 level problem and you're already on current Pg versions I fear you may have to use some specacularly ugly workarounds.

If at all possible, try to find a way to increase the VB6 query buffer size, send the query in chunks via the VB6 ODBC interface, etc. Consider the following an absolute last resort.

Maybe this will give you some saner clues. I don't speak VB6 (thankfully) so I can't evaulate it: http://www.mrexcel.com/forum/excel-questions/61340-error-28-out-stack-space.html

Use the following only as a last resort if all else fails:

Create a TEMPORARY table like CREATE TEMPORARY TABLE my_query(id integer, text querychunk).
INSERT INTO the temporary table your statement, chunk by chunk, using parameterised queries to avoid quoting issues.
Create a wrapper PL/PgSQL function that does a RETURN QUERY EXECUTE format('EXECUTE stm_name(...)', passing the string_agg of the temp table as a parameter. Yes, that's amazingly ugly.

Here's a demo, some of the most horrible code I've ever written:

CREATE TABLE real_table (blah text);

PREPARE test_stm2(text) AS INSERT INTO real_table VALUES ($1);

CREATE TEMPORARY TABLE data_chunks(datord integer, datchunk text);

PREPARE chunk_insert(integer, text) AS INSERT INTO data_chunks(datord,datchunk) VALUES ($1,$2);

-- You'll really want to do this via proper parameterised statements
-- to avoid quoting nightmares; I'm using dollar-quoting as a workaround
EXECUTE chunk_insert(0, $val$POLYGON((0 0, 0 1, 1 1,$val$);
EXECUTE chunk_insert(1, $val$ 1, 1 0, 0 0))$val$);

DO
$$
BEGIN
    EXECUTE 'EXECUTE test_stm2($1);'
        USING 
        (SELECT string_agg(datchunk,'' ORDER BY datord) FROM data_chunks);
END;
$$ LANGUAGE plpgsql;

Result:

regress=> SELECT * FROM real_table ;
                 blah                  
---------------------------------------
 POLYGON((0 0, 0 1, 1 1, 1, 1 0, 0 0))
(1 row)

A similar approach is possible for SELECT. You'd use RETURN QUERY EXECUTE within a function defined by CREATE OR REPLACE FUNCTION since DO blocks can't return a result. For example, for a query that returns a SETOF INTEGER you might write:

CREATE OR REPLACE FUNCTION test_wrapper_func() RETURNS SETOF integer AS $$
BEGIN
    RETURN QUERY EXECUTE format('EXECUTE test_stm(%L);', (SELECT string_agg(datchunk,'' ORDER BY datord) FROM data_chunks));
END;
$$ LANGUAGE plpgsql;

You'll note the two-level EXECUTE. That's because the PL/PgSQL EXECUTE is quite a different statement to SQL-level EXECUTE. PL/PgSQL EXECUTE runs a string as dynamic SQL, wheras the SQL EXECUTE runs a prepared statement. Here we're running a prepared statement via dynamic SQL. Ick.

Wondering why I'm using PL/PgSQL? Because you can't use a subquery as an EXECUTE parameter. You can avoid the PL/PgSQL wrapper for the query if you don't run it as a prepared statement.

regress=> EXECUTE test_stm2( (SELECT string_agg(datchunk,'' ORDER BY datord) FROM data_chunks) );
ERROR:  cannot use subquery in EXECUTE parameter