PostgreSQL Question posted in
The official documentation can be found here.

Does Postgresql's COPY FROM have a length limit to the string it can read?

I’ve hit a snag with the following PL/pgSQL function I wrote

CREATE OR REPLACE FUNCTION swipl_rpc(IN json_in JSONB, OUT json_out JSONB)
LANGUAGE plpgsql
AS $code_block$
BEGIN
CREATE TEMPORARY TABLE tmp (json_tmp JSONB);
EXECUTE format($bash$ COPY tmp FROM PROGRAM 'ggp-json ''%s''' $bash$, json_in);
SELECT json_tmp FROM tmp INTO json_out;
DROP TABLE tmp;
END;
$code_block$;

It works fine if the returned JSON is a couple of thousand characters long. But a 28618 byte JSON return string resulted in:

ERROR:  could not open relation with OID 2948131

It seems to be related to the input string size, but I’m a bit in the dark here.

Tags:

2

Answers


  1. 0

    The maximum query length in postgres is 2.147.483.648 characters.

    See the answer of https://stackoverflow.com/a/4937695/11690682

    Login or Signup to reply.
  2. 0

    The problem is that when that function is compiled (on first use) the reference to table tmp is compiled too (OID 2948131). The second time you call it (with the same session) it will create a new temporary table for you but the compiled SELECT statement points still to the old table.

    You have a number of options:

    1. Use a different procedural language that doesn’t compile its statements.
    2. Avoid the use of temporary tables.
    3. Use dynamic SQL (with EXECUTE) for all the statements involved with the temporary table.

    I’d probably go with #3 if the function is really that short.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search