Search code examples
postgresqlplpgsqlpostgresql-copy

Repeated execution of PL/pgSQL function triggers error "could not open relation with OID ..."

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.

Solution

  • About the reported error

    Not related to the length of the input string. The problem was with saved query plans. Not for the COPY command but for the subsequent SELECT & DELETE.

    PL/pgSQL can save query plans for nested SQL statements. Since the function body of a PL/pgSQL function is stored as plain string, no dependencies are registered. If you drop and recreate a table used in a saved plan, that can lead to the error message you reported. See:

    Better function

    CREATE OR REPLACE FUNCTION pg_temp.swipl_rpc(IN json_in jsonb, OUT json_out jsonb)
  LANGUAGE plpgsql STRICT AS
$func$
BEGIN
   CREATE TEMPORARY TABLE IF NOT EXISTS swipl_rpc_tmp (json_tmp jsonb);

   EXECUTE format('COPY pg_temp.swipl_rpc_tmp FROM PROGRAM %L;
                   SELECT json_tmp FROM pg_temp.swipl_rpc_tmp;'
                , 'ggp-json ' || quote_literal(json_in))
   INTO json_out;

   EXECUTE 'DELETE FROM pg_temp.swipl_rpc_tmp';
END
$func$;

    A temporary table is cheaper and more appropriate for your purpose.
    Plus, your current solution is open to SQL injection.

    The core problem in your question is solved by executing commands involving the (temporary) table dynamically (with EXECUTE), which never uses saved query plans. So dropping and re-creating the table can't invalidate saved plans.

    EXECUTE can take multiple SQL statements at once. Put the SELECT command right after COPY. The INTO clause moves from SELECT to the EXECUTE command, SELECT must be the final command.

    (Since I don't drop the temp table, the reported error should not normally happen any more anyway.)

    Use a more specific table name, so that conflicts are unlikely. And schema-qualify explicitly, so it can't be resolved to another table of the same name in the search path by accident if the temp table is missing (pg_temp.swipl_rpc_tmp). Both good practice.

    The temp table only ever has a single row, so use DELETE without WHERE. Cheaper.

    SQL injection?

    There are 4 levels of nested quoting in this piece of code:

    1. The literal representing the JSON value json_in must be quoted like any string. (Escaping any nested single quotes!)

    2. Said string literal is argument to the program ggp-json, and the two are nested in single quotes within the COPY command.

    3. The COPY command, plus the subsequent SELECT form a string as argument to EXECUTE.

    4. The EXECUTE command is nested in the function body of the PL/pgSQL function - another quoted string.

    Your solution misses level 1 and pastes json_in with the format specifier %s. Any nested single quotes are not escaped and trigger an error (in the best case) or can be abused for SQL injection (in the worst case).

    I fixed that by first quoting json_in with quote_literal(). Then concatenate with your program name ggp-json and quote for the next level by using the format specifier %L to format().

    See:

    Different approach?

    (Ab-)using COPY to execute an external program is only a workaround. A proper C function would be (much) more efficient and could return directly without a (temp) staging table.

    Or a function with PL/Perl or PL/Python? (If you have one of those installed.) See: