Re: Plpgsql search_path issue going from 9.3 to 9.6

On 11/13/18 5:17 AM, George Woodring wrote:
> We are having an issue with one of our plpgsql functions after migrating 
> from 9.3 to 9.6.  The function works fine until you change the search path.
> 
> psql (9.6.10)
> Type "help" for help.
> 
> woody=> select ticket_summary(8154);
>                            ticket_summary
> -------------------------------------------------------------------
>   {58451,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
> (1 row)
> 
> woody=> set search_path to "iss-hackers", public;
> SET
> woody=> select ticket_summary(8154);
> ERROR:  invalid input syntax for type timestamp with time zone: "woodring"

Well the above is the base of the issue, the string 'woodring' is being 
used as a timestamp entry. Somewhere wires are getting crossed.

In the function below you have:

  SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;

This is the only place where you do not schema qualify a table.

Is there more then tickets table?


> CONTEXT:  PL/pgSQL function ticket_summary(integer) line 11 at SQL statement
> 
> It is confused which column is which after the change.
> 
> The tables used by the function are:
> public.tickets - A table with 3 child tables
> iss-hackers.tickets - A view of public.tickets with a where clause.
> public.followups - A table with 3 child tables.
> 
> CREATE OR REPLACE FUNCTION ticket_summary(int4) RETURNS int8[] AS $$
>     DECLARE
>        tid ALIAS FOR $1;
>        cstate public.followups.state%TYPE := 1;
>        ticket public.tickets%ROWTYPE;
>        followup public.followups%ROWTYPE;
>        summary int8[] := '{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}';
>        lastdate public.followups.date%TYPE;
>     BEGIN
>        SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;
>        IF NOT FOUND THEN
>           return summary;
>        END IF;
>        lastdate := ticket.opendate;
>        FOR followup IN SELECT * FROM public.followups WHERE 
> ticketsid=tid AND state IS NOT NULL ORDER BY date LOOP
>           summary[cstate] := summary[cstate] + extract( EPOCH FROM 
> (followup.date - lastdate))::int;
>           cstate := followup.state;
>           lastdate := followup.date;
>        END LOOP;
>        summary[cstate] := summary[cstate] + extract( EPOCH FROM 
> (current_timestamp - lastdate))::int;
>        RETURN summary;
>     END;
> $$ LANGUAGE plpgsql;
> 
> I assume I can fix this by putting the function into each of the 
> schemas, but I thought I would ask opinions before doing so.
> 
>   Thanks,
> George Woodring
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>


-- 
Adrian Klaver
adrian.klaver@aklaver.com