Thread: 8.2 pl/pgsql crash bug (WAS: [pgadmin-support] Error craches pgAdmin)

8.2 pl/pgsql crash bug (WAS: [pgadmin-support] Error craches pgAdmin)

From
Dave Page
Date:
The issue below was reported to us as a pgAdmin bug - it can be
recreated in psql on 8.2.0, and results in:

2006-12-12 09:06:50 LOG:  server process (PID 4588) exited with exit
code -1073741819
2006-12-12 09:06:50 LOG:  terminating any other active server processes
2006-12-12 09:06:50 WARNING:  terminating connection because of crash of
another server process
2006-12-12 09:06:50 DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2006-12-12 09:06:50 HINT:  In a moment you should be able to reconnect
to the database and repeat your command.

In 8.1.5, it works as expected (ie. without crashing).

Regards, Dave.

-------- Original Message --------
Subject:     [pgadmin-support] Error craches pgAdmin
Date:     Mon, 11 Dec 2006 20:11:39 +0100
From:     Paolo Saudin <paolo@ecometer.it>
To:     <pgadmin-support@postgresql.org>

Hi,

I found a different pgAdmin behavior  if  I use it against Postgres
8.1.15 or 8.2.0. Here to try it out

I have a table filled with dates :

CREATE TABLE _master_h24  (
   fulldate timestamp without time zone NOT NULL,
   CONSTRAINT _master_h24_pkey PRIMARY KEY (fulldate)
)  WITHOUT OIDS;

I have a function to fulfill it with dates :

CREATE OR REPLACE FUNCTION fillmastertable_h24()
   RETURNS timestamp without time zone AS
$BODY$
declare
     dt_now timestamp;
     dt_last timestamp;
     max_loops INTEGER;
     v INTEGER;
BEGIN

     -- gets the last update
     SELECT fulldate INTO dt_last FROM _master_h24 ORDER BY fulldate DESC
LIMIT 1;

     --RAISE NOTICE 'last : % ', dt_last;
     -- gets the gmt - 1 hour date time
     dt_now := to_timestamp(TIMEZONE('WAT',CURRENT_TIMESTAMP),
'YYYY-MM-DD');

     --RAISE NOTICE 'dt_now : % ', dt_now;
     max_loops := 100;
     v := 0;

     WHILE dt_last < dt_now AND v < max_loops loop
       v := v + 1;
       dt_last := dt_last + '24 HOUR'::INTERVAL;

       /* execute query */
       BEGIN
         RAISE NOTICE 'Dt : % ', dt_last;
         insert into _master_24 (fulldate) VALUES
(dt_last);                        /*  ß <- HERE IS THE TABLE MISSPELLING
(_master_24 ) */

       /* errors check */
       EXCEPTION
       /* in case of any error */
         WHEN OTHERS THEN RAISE NOTICE 'ERROR in fillmastertable_h24';
       END;
     END loop;
     return dt_last;
END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION fillmastertable_h24() OWNER TO postgres;

I then insert the first date to begin with :

insert into _master_h24 (fulldate) VALUES ('2006-12-01'); -> OK

I run the script  :

SELECT fillmastertable_h24();

And on Postgres 8.1.15 I get back ‘ERROR: “relation _master_24” does not
exist -> OK

While on Postgres 8.2.0 I get back only “:” and pgAdmin craches loosing
the connection to the server. If I click on the server node I get the
following message box :

An error has occurred:

Server closed the connection unexpectedly

This probably means the server terminated abnormally before or while
processing the request

I don’t know if depends on pgAdmin or the server itself.


Thanks,

Paolo Saudin

Re: 8.2 pl/pgsql crash bug (WAS: [pgadmin-support] Error craches

From
Heikki Linnakangas
Date:
This bug seems to be introduced by this recent change to avoid memory
leakage:

> Log Message:
> -----------
> Prevent intratransaction memory leak when a subtransaction is aborted
> in the middle of executing a SPI query.  This doesn't entirely fix the
> problem of memory leakage in plpgsql exception handling, but it should
> get rid of the lion's share of leakage.
>
> Modified Files:
> --------------
>     pgsql/src/backend/executor:
>         spi.c (r1.164 -> r1.165)
>         (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/spi.c.diff?r1=1.164&r2=1.165

I don't know that code too well, but somehow the tuptable memory context
gets messed up / not free'd properly etc.

Dave Page wrote:
> The issue below was reported to us as a pgAdmin bug - it can be
> recreated in psql on 8.2.0, and results in:
>
> 2006-12-12 09:06:50 LOG:  server process (PID 4588) exited with exit
> code -1073741819
> 2006-12-12 09:06:50 LOG:  terminating any other active server processes
> 2006-12-12 09:06:50 WARNING:  terminating connection because of crash of
> another server process
> 2006-12-12 09:06:50 DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> 2006-12-12 09:06:50 HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
>
> In 8.1.5, it works as expected (ie. without crashing).
>
> Regards, Dave.
>
> -------- Original Message --------
> Subject:     [pgadmin-support] Error craches pgAdmin
> Date:     Mon, 11 Dec 2006 20:11:39 +0100
> From:     Paolo Saudin <paolo@ecometer.it>
> To:     <pgadmin-support@postgresql.org>
>
> Hi,
>
> I found a different pgAdmin behavior  if  I use it against Postgres
> 8.1.15 or 8.2.0. Here to try it out
>
> I have a table filled with dates :
>
> CREATE TABLE _master_h24  (
>   fulldate timestamp without time zone NOT NULL,
>   CONSTRAINT _master_h24_pkey PRIMARY KEY (fulldate)
> )  WITHOUT OIDS;
>
> I have a function to fulfill it with dates :
>
> CREATE OR REPLACE FUNCTION fillmastertable_h24()
>   RETURNS timestamp without time zone AS
> $BODY$
> declare
>     dt_now timestamp;
>     dt_last timestamp;
>     max_loops INTEGER;
>     v INTEGER;
> BEGIN
>
>     -- gets the last update
>     SELECT fulldate INTO dt_last FROM _master_h24 ORDER BY fulldate DESC
> LIMIT 1;
>
>     --RAISE NOTICE 'last : % ', dt_last;
>     -- gets the gmt - 1 hour date time
>     dt_now := to_timestamp(TIMEZONE('WAT',CURRENT_TIMESTAMP),
> 'YYYY-MM-DD');
>
>     --RAISE NOTICE 'dt_now : % ', dt_now;
>     max_loops := 100;
>     v := 0;
>
>     WHILE dt_last < dt_now AND v < max_loops loop
>       v := v + 1;
>       dt_last := dt_last + '24 HOUR'::INTERVAL;
>
>       /* execute query */
>       BEGIN
>         RAISE NOTICE 'Dt : % ', dt_last;
>         insert into _master_24 (fulldate) VALUES
> (dt_last);                        /*  ß <- HERE IS THE TABLE MISSPELLING
> (_master_24 ) */
>
>       /* errors check */
>       EXCEPTION
>       /* in case of any error */
>         WHEN OTHERS THEN RAISE NOTICE 'ERROR in fillmastertable_h24';
>       END;
>     END loop;
>     return dt_last;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
> ALTER FUNCTION fillmastertable_h24() OWNER TO postgres;
>
> I then insert the first date to begin with :
>
> insert into _master_h24 (fulldate) VALUES ('2006-12-01'); -> OK
>
> I run the script  :
>
> SELECT fillmastertable_h24();
>
> And on Postgres 8.1.15 I get back ‘ERROR: “relation _master_24” does not
> exist -> OK
>
> While on Postgres 8.2.0 I get back only “:” and pgAdmin craches loosing
> the connection to the server. If I click on the server node I get the
> following message box :
>
> An error has occurred:
>
> Server closed the connection unexpectedly
>
> This probably means the server terminated abnormally before or while
> processing the request
>
> I don’t know if depends on pgAdmin or the server itself.
>
>
> Thanks,
>
> Paolo Saudin
--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: 8.2 pl/pgsql crash bug (WAS: [pgadmin-support] Error craches

From
Tom Lane
Date:
Heikki Linnakangas <heikki@enterprisedb.com> writes:
> This bug seems to be introduced by this recent change to avoid memory
> leakage:

I see no crash in CVS tip --- I believe it's same bug fixed here:

2006-12-07 19:40  tgl

    * src/backend/executor/: spi.c (REL8_2_STABLE), spi.c: Avoid double
    free of _SPI_current->tuptable.  AtEOSubXact_SPI() now tries to
    release it in a subtransaction abort, but this neglects possibility
    that someone outside SPI already did.  Fix is for spi.c to forget
    about a tuptable as soon as it's handed it back to the caller.    Per
    bug #2817 from Michael Andreen.

            regards, tom lane