Thread: 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

On 11/13/18 6:27 AM, George Woodring wrote:
> 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.

That is because you have a table tickets in the public schema and a view 
tickets in the iss-hackers schema.

Is that what you really want?


> 
> \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 <http://www.iglass.net>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/13/18 6:27 AM, George Woodring wrote:
> 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.
> 

> 
> 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.

Or change this:

  SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;

to:

  SELECT * INTO ticket FROM public.tickets WHERE ticketsid=tid;

This will match the ROWTYPE:

ticket public.tickets%ROWTYPE;

> 
> George
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/13/18 6:54 AM, George Woodring wrote:
> The tickets view restricts which tickets can be seen by the schema.
> 
> 9.3 must have created the view in the same column order as the table 
> (which is the case looking at one of our 9.3 databases which we have not 
> updated yet), which is why we never saw the issue before.

What is the view definition?

> 
> George
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

>
> I apologize for top posting,  Google hid all of the other stuff.
>

It is only me who thinks that when it comes to destroying email as a communication tool, no one did a better job than
effinggmail.