Re: Plpgsql search_path issue going from 9.3 to 9.6

[George Woodring <george.woodring@xxxxxxxxxx>]

I think the issue is that the function is not putting the data into the tickets%ROWTYPE correctly.  When I do \d on public.tickets and iss-hackers.tickets, the columns are in a different order.

\d public.tickets

    Column    |           Type           |                          Modifiers                          

--------------+--------------------------+-------------------------------------------------------------

 ticketsid    | integer                  | not null default nextval('tickets_ticketsid_seq'::regclass)

 opendate     | timestamp with time zone | default now()

 state        | smallint                 | default 1

 opentech     | character varying(50)    | 

 priority     | smallint                 | default 10

 severity     | smallint                 | default 30

 problem      | character varying(300)   | 

 summary      | text                     | 

 parent       | integer                  | 

 remed        | boolean                  | default false

 remed2       | boolean                  | default false

 remed_hstart | timestamp with time zone | 

 autoclean    | boolean                  | default false

 remoteid     | character varying        | 

 remotesync   | timestamp with time zone | 

 sla_time     | interval                 | 

 sla_alarm    | boolean                  | 

\d iss-hackers.tickets

             View "iss-hackers.tickets"

    Column    |           Type           | Modifiers 

--------------+--------------------------+-----------

 ticketsid    | integer                  | 

 opentech     | character varying(50)    | 

 summary      | text                     | 

 parent       | integer                  | 

 opendate     | timestamp with time zone | 

 priority     | smallint                 | 

 problem      | character varying(300)   | 

 autoclean    | boolean                  | 

 state        | smallint                 | 

 severity     | smallint                 | 

 remed        | boolean                  | 

 remed2       | boolean                  | 

 remoteid     | character varying        | 

 remotesync   | timestamp with time zone | 

 sla_time     | interval                 | 

 sla_alarm    | boolean                  | 

 remed_hstart | timestamp with time zone | 

 tableoid     | oid                      | 

The error message is saying column2 is not a timestamp, which the public table is a timestamp for column2.  If I change my SELECT in the function from SELECT * to SELECT opendate  I can fix my issue easily.

George

iGLASS Networks
www.iglass.net

On Tue, Nov 13, 2018 at 8:44 AM Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hi

út 13. 11. 2018 v 14:18 odesílatel George Woodring <george.woodring@xxxxxxxxxx> napsal:

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"

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.

It looks strange. Maybe you have some garbage in iss-hackers schema created in upgrade time.

Hard to say what is wrong without complete schema.

Regards

Pavel

 Thanks,

George Woodring

iGLASS Networks
www.iglass.net