Issues invoking within function - One operand supposed to be column another constant

cyga / www_fdw

fdw extension for postgres

123 stars 21 forks source link

I ran into an issue that cost me some small amount of sanity. :)

I wrapped my fdw server in another function that supported local temporal caching in order to minimize the number of repeat service invocations. It would work for some invocations and then it would start throwing a "One operand supposed to be column another constant" error. At first I thought it was data related, but then could reproduce it both working and not working with the exact same service invocation parameters. Recreating the proc would immediately restore the functionality for a time. In my proc I was invoking as:

select result into var1 from www_fdw_myservice where api_key = var2 and user_id = var3;

I'm new to pgsql, but I'm assuming that the above is legitimate for pgsql. My hypothesis is that the procedure is being optimized in such a way that it begins to violate the constraint checking within the fdw service. Explicitly defining the wrapping proc as volatile had no effect (as expected). However, switching to a dynamic execution seems to have eliminated the problem e.g.:

execute 'select result from www_fdw_myservice where api_key = $1 and user_id = $2' into var1 using var2, var3;

If it's not feasible to correct the issue, then there should probably be a note in the documentation so that others can avoid it.

Yep, and as a result the extension is nearly useless in anything aside from toy code :(

The problem can be easily reproduced with the following code snippet:

DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSION www_fdw;
CREATE SERVER www_fdw_server_google_search FOREIGN DATA WRAPPER www_fdw
    OPTIONS (uri 'https://ajax.googleapis.com/ajax/services/search/web?v=1.0');
CREATE USER MAPPING FOR current_user SERVER www_fdw_server_google_search;
CREATE FOREIGN TABLE www_fdw_google_search (
    /* parameters used in request */
    q text,

    /* fields in response */
    GsearchResultClass text,
    unescapedUrl text,
    url text,
    visibleUrl text,
    cacheUrl text,
    title text,
    titleNoFormatting text,
    content text
) SERVER www_fdw_server_google_search;

CREATE OR REPLACE FUNCTION google(text) 
 RETURNS TABLE (url text, field1 text, field2 text) AS $$ 
  select url,substring(title,1,25)||'...',substring(content,1,25)||'...' 
  from www_fdw_google_search where q=$1  
 $$ LANGUAGE SQL VOLATILE STRICT;

select * from google('postgres');

RESULT:

   ERROR:  One operand supposed to be column another constant
   CONTEXT:  SQL function "google" statement 1

cyga / www_fdw

Issues invoking within function - One operand supposed to be column another constant #15