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(dot)klaver(at)aklaver(dot)com