Обсуждение: Setof-returning function create script bug

Dear Sir or Madam,<br />I am writing to report a bug in pgadmin III 1.22.0-beta1 running on Windows 7 x64 connected to
PostgreSQL9.5beta2 64 bit.<br />To reproduce the bug create this function using SQL query editor:<br /><br /><font
face="CourierNew">create or replace function afunc() returns setof record as<br />$$<br />begin<br /> return next (1,
2,3);<br /> return next (2, 3, 4);<br />end;<br />$$ language plpgsql;</font><br /><br />The function works fine when
invokedwith a query like this one<br /><br /><font face="Courier New">select * from afunc() as x(a integer, b integer,
cinteger);</font><br /><br />However when the function is selected in Object browser this is what appears in the SQL
pane:<br/><br /><font face="Courier New">-- Function: public.afunc()<br /><br />-- DROP FUNCTION public.afunc();<br
/><br/>CREATE OR REPLACE FUNCTION public.afunc()<br />  RETURNS SETOF SETOF record AS<br />$BODY$<br />begin<br
/>returnnext (1, 2, 3);<br />return next (2,3,4);<br />end;<br />$BODY$<br />  LANGUAGE plpgsql VOLATILE<br />  COST
100<br/>  ROWS 1000;<br />ALTER FUNCTION public.afunc()<br />  OWNER TO postgres;</font><br /><br />There are two
SETOF-safter the RETURNS declaration and this makes the code invalid. Rightclick-Scripts-Create script produces the
same.<br/>The bug appeared on pgadmin III 1.18 running on windows xp connected to 32-bit Postgresql 9.2 as well.<br
/>Itis not really a show stopper yet annoying.<br /><br />Thank you for the great job you are doing.<br />Sincerely,
Stefan

Hello Stefan,

this bug is (supposed to be) fixed in git, however beta1 binaries were 
released before the fix. Therefore, you can either wait for beta2 (or 
whatever release happens next), or rebuild new binaries yourself from 
git (although building windows binaries from scratch involves quite a 
lot of burden)

Regards,
Nikolai

26.11.2015 22:18, Stefan Stefanov:
> Dear Sir or Madam,
> I am writing to report a bug in pgadmin III 1.22.0-beta1 running on
> Windows 7 x64 connected to PostgreSQL 9.5beta2 64 bit.
> To reproduce the bug create this function using SQL query editor:
>
> create or replace function afunc() returns setof record as
> $$
> begin
> return next (1, 2, 3);
> return next (2, 3, 4);
> end;
> $$ language plpgsql;
>
> The function works fine when invoked with a query like this one
>
> select * from afunc() as x(a integer, b integer, c integer);
>
> However when the function is selected in Object browser this is what
> appears in the SQL pane:
>
> -- Function: public.afunc()
>
> -- DROP FUNCTION public.afunc();
>
> CREATE OR REPLACE FUNCTION public.afunc()
> RETURNS SETOF SETOF record AS
> $BODY$
> begin
> return next (1, 2, 3);
> return next (2,3,4);
> end;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100
> ROWS 1000;
> ALTER FUNCTION public.afunc()
> OWNER TO postgres;
>
> There are two SETOF-s after the RETURNS declaration and this makes the
> code invalid. Rightclick-Scripts-Create script produces the same.
> The bug appeared on pgadmin III 1.18 running on windows xp connected to
> 32-bit Postgresql 9.2 as well.
> It is not really a show stopper yet annoying.
>
> Thank you for the great job you are doing.
> Sincerely, Stefan

The intriguing error message here is "could not find block containing chunk 0000000001176B70", which is coming from either the server or more likely, the debugger plugin. 

Korry - you're far more familiar with that code than me; any idea what could be causing this?

On Sat, Feb 13, 2016 at 2:13 PM, Stefan Stefanov <stefanov.sm@abv.bg> wrote:

Hi Dave, this part of pg_log/postgresql-2016-02-13_000000.log might help.

2016-02-13 16:05:23 EET ERROR:  select() failed waiting for target
2016-02-13 16:05:23 EET STATEMENT:  SELECT
        p.func, p.targetName, p.linenumber,
        pldbg_get_source($1::INTEGER, p.func) AS src,
        (SELECT
            s.args
         FROM pldbg_get_stack($1::INTEGER) s
         WHERE s.func = p.func) AS args
    FROM pldbg_step_over($1::INTEGER) p
2016-02-13 16:05:26 EET WARNING:  unrecognized message
   
2016-02-13 16:05:26 EET CONTEXT:  PL/pgSQL function playground.dbg_test() line 8 at RAISE
2016-02-13 16:05:26 EET ERROR:  could not find block containing chunk 0000000001176B70
2016-02-13 16:05:26 EET CONTEXT:  PL/pgSQL function playground.dbg_test() line 8 at RAISE
2016-02-13 16:05:26 EET STATEMENT:  SELECT * FROM playground.dbg_test()
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET ERROR:  debugger connection(client side) terminated
2016-02-13 16:06:02 EET STATEMENT:  SELECT * FROM pldbg_abort_target(1)
2016-02-13 16:06:02 EET LOG:  could not send data to client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET STATEMENT:  SELECT * FROM pldbg_abort_target(1)
2016-02-13 16:06:02 EET FATAL:  connection to client lost
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   

Sincerely, Stefan



>-------- Оригинално писмо --------
>От: Dave Page dpage@pgadmin.org
>Относно: Re: [pgadmin-support] Debugger freeze
>До: Stefan Stefanov <stefanov.sm@abv.bg>
>Изпратено на: 12.02.2016 15:45

Hi

On Thu, Feb 11, 2016 at 6:36 PM, Stefan Stefanov <stefanov.sm@abv.bg> wrote:

Dear Sir or Madam,
I am writing to report a bug in pgadmin III 1.22.0 running on Windows 7 x64 connected to PostgreSQL 9.5.0 VC++ build 1800, 64 bit.
To reproduce the bug create this trivial function using SQL query editor. The example is in "playground" schema.

create function playground.dbg_test() returns void as
$$
declare
    i integer;
    t text;
begin
    for i in 1 .. 100 loop
        t := 'XYZ' || to_char(i, '999');
        raise notice 'Round %', t;
    end loop;
end;
$$ language plpgsql;

Right-click the function in Object browser then debug. Step a few times through the loop then take some time doing other things in pgadmin iii (browse data, run queries etc.) then return to the debug window and step again a few times. The debug window usually freezes (debug.png).
Two locks remain (server status.png). The server may be cleared by cancelling the query of the global listener (pid 7748 in the example) but then pgadmin windows  stop responding.

This is preventing me from using the debugger and I need it badly. 

I've tried to reproduce this but am unable, even letting my machine go into powersave mode while the function is running.

Can anyone else reproduce?

Stefan, could it be that you're suffering from network disconnections? pgAdmin 3 really doesn't like them, and unfortunately it's a hard problem to fix given the current code structure (it's one of the things we're fixing in pgAdmin 4 which is completely new). 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

The intriguing error message here is "could not find block containing chunk 0000000001176B70", which is coming from either the server or more likely, the debugger plugin. 

Korry - you're far more familiar with that code than me; any idea what could be causing this?

On Sat, Feb 13, 2016 at 2:13 PM, Stefan Stefanov <stefanov.sm@abv.bg> wrote:

Hi Dave, this part of pg_log/postgresql-2016-02-13_000000.log might help.

2016-02-13 16:05:23 EET ERROR:  select() failed waiting for target
2016-02-13 16:05:23 EET STATEMENT:  SELECT
        p.func, p.targetName, p.linenumber,
        pldbg_get_source($1::INTEGER, p.func) AS src,
        (SELECT
            s.args
         FROM pldbg_get_stack($1::INTEGER) s
         WHERE s.func = p.func) AS args
    FROM pldbg_step_over($1::INTEGER) p
2016-02-13 16:05:26 EET WARNING:  unrecognized message
   
2016-02-13 16:05:26 EET CONTEXT:  PL/pgSQL function playground.dbg_test() line 8 at RAISE
2016-02-13 16:05:26 EET ERROR:  could not find block containing chunk 0000000001176B70
2016-02-13 16:05:26 EET CONTEXT:  PL/pgSQL function playground.dbg_test() line 8 at RAISE
2016-02-13 16:05:26 EET STATEMENT:  SELECT * FROM playground.dbg_test()
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET ERROR:  debugger connection(client side) terminated
2016-02-13 16:06:02 EET STATEMENT:  SELECT * FROM pldbg_abort_target(1)
2016-02-13 16:06:02 EET LOG:  could not send data to client: No connection could be made because the target machine actively refused it.

   
2016-02-13 16:06:02 EET STATEMENT:  SELECT * FROM pldbg_abort_target(1)
2016-02-13 16:06:02 EET FATAL:  connection to client lost
2016-02-13 16:06:02 EET LOG:  could not receive data from client: No connection could be made because the target machine actively refused it.

   

Sincerely, Stefan



>-------- Оригинално писмо --------
>От: Dave Page dpage@pgadmin.org
>Относно: Re: [pgadmin-support] Debugger freeze
>До: Stefan Stefanov <stefanov.sm@abv.bg>
>Изпратено на: 12.02.2016 15:45

Hi

On Thu, Feb 11, 2016 at 6:36 PM, Stefan Stefanov <stefanov.sm@abv.bg> wrote:

Dear Sir or Madam,
I am writing to report a bug in pgadmin III 1.22.0 running on Windows 7 x64 connected to PostgreSQL 9.5.0 VC++ build 1800, 64 bit.
To reproduce the bug create this trivial function using SQL query editor. The example is in "playground" schema.

create function playground.dbg_test() returns void as
$$
declare
    i integer;
    t text;
begin
    for i in 1 .. 100 loop
        t := 'XYZ' || to_char(i, '999');
        raise notice 'Round %', t;
    end loop;
end;
$$ language plpgsql;

Right-click the function in Object browser then debug. Step a few times through the loop then take some time doing other things in pgadmin iii (browse data, run queries etc.) then return to the debug window and step again a few times. The debug window usually freezes (debug.png).
Two locks remain (server status.png). The server may be cleared by cancelling the query of the global listener (pid 7748 in the example) but then pgadmin windows  stop responding.

This is preventing me from using the debugger and I need it badly. 

I've tried to reproduce this but am unable, even letting my machine go into powersave mode while the function is running.

Can anyone else reproduce?

Stefan, could it be that you're suffering from network disconnections? pgAdmin 3 really doesn't like them, and unfortunately it's a hard problem to fix given the current code structure (it's one of the things we're fixing in pgAdmin 4 which is completely new). 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company