Обсуждение: BUG #11902: PostgreSQL 9.5 crashes on alter table in function

The following bug has been logged on the website:

Bug reference:      11902
Logged by:          Regina Obe
Email address:      lr@pcorp.us
PostgreSQL version: Unsupported/Unknown
Operating system:   Debian and Windows MingW-w64 (both 64 and 32-bit)
Description:

I wanted to pick 9.5 in PostgreSQL version, but that is not listed.  Anyway
for the past couple of weeks regressing against latest snapshots of 9.5 has
been crashing our PostGIS topology tests.

Related ticket here:
http://trac.osgeo.org/postgis/ticket/2982

I was able to crash without PostGIS help with this script:

CREATE OR REPLACE FUNCTION crash_test(table_name varchar, column_name
varchar)
  RETURNS bigint AS
$$
DECLARE
 var_result bigint;
BEGIN
       EXECUTE 'ALTER TABLE ' || quote_ident(table_name) || ' ADD COLUMN '
|| quote_ident(column_name) || ' text;';
       var_result = (random()*100000)::bigint;
       RETURN var_result;
END
$$
  LANGUAGE plpgsql VOLATILE
  COST 100;

DROP TABLE IF EXISTS test_1;
CREATE TABLE test_1(id serial primary key);
DROP TABLE IF EXISTS test_crash;
CREATE TEMP TABLE test_crash AS
SELECT 1 As id, crash_test('test_1', 'lyr1') As layer;

INSERT INTO test_crash(id, layer)
SELECT id + 1, crash_test('test_1', 'lyr' || (id + 1)::text)
FROM test_crash
WHERE id = 1;


Our buildbot runs Debian 64-bit, and I also tested this on my Mingw64
Windows 7 both (64-bit and 32-bit with same issue)

Issue seems to be around here according to gdb trace:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 5568.0x15c4]
0x000000000054fbc9 in AfterTriggerPendingOnRel (relid=17984) at
trigger.c:4781
4781                    for_each_event_chunk(event, chunk,
afterTriggers.query_stack[depth])
(gdb) bt
#0  0x000000000054fbc9 in AfterTriggerPendingOnRel (relid=17984) at
trigger.c:4781
#1  0x000000000053e05c in CheckTableNotInUse (rel=0xe650dc0, stmt=0x8ab69a
<__func__.77621+8091> "ALTER TABLE") at tablecmds.c:2652
#2  0x0000000000545315 in AlterTable (relid=relid@entry=17984,
lockmode=lockmode@entry=8, stmt=0xe32b580) at tablecmds.c:2724

Hi Regina,

> I wanted to pick 9.5 in PostgreSQL version, but that is not listed.  Anyway
> for the past couple of weeks regressing against latest snapshots of 9.5 has
> been crashing our PostGIS topology tests.

> I was able to crash without PostGIS help with this script:

Thanks for the report. Very useful. Can you tell us the exact git
checkout this is based upon?

This is very likely caused by 2781b4b. There was one fix shortly
afterwards 85bb81de.

Greetings,

Andres Freund

Note sure.  It was built from snapshot here
http://www.postgresql.org/ftp/snapshot/dev/ and as far as I could reckon the
issue started happening about 12-15 days ago.

The last one I built to test with from there I think was dated around
11/4/2014.

I can pull from latest git and then run the tests again if you think the
issue is fixed.

Thanks,
Regina

-----Original Message-----
From: Andres Freund [mailto:andres@2ndquadrant.com]
Sent: Thursday, November 06, 2014 1:06 PM
To: lr@pcorp.us; Robert Haas
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #11902: PostgreSQL 9.5 crashes on alter table in
function

Hi Regina,

> I wanted to pick 9.5 in PostgreSQL version, but that is not listed.
> Anyway for the past couple of weeks regressing against latest
> snapshots of 9.5 has been crashing our PostGIS topology tests.

> I was able to crash without PostGIS help with this script:

Thanks for the report. Very useful. Can you tell us the exact git checkout
this is based upon?

This is very likely caused by 2781b4b. There was one fix shortly afterwards
85bb81de.

Greetings,

Andres Freund

"Paragon Corporation" <lr@pcorp.us> writes:
> I can pull from latest git and then run the tests again if you think the
> issue is fixed.

No, the given test case crashes HEAD just fine.  Robert seems to have
changed the invariants around the trigger query stack in some inconsistent
fashion.

            regards, tom lane

On 2014-11-06 16:15:15 -0500, Tom Lane wrote:
> "Paragon Corporation" <lr@pcorp.us> writes:
> > I can pull from latest git and then run the tests again if you think the
> > issue is fixed.
>
> No, the given test case crashes HEAD just fine.  Robert seems to have
> changed the invariants around the trigger query stack in some inconsistent
> fashion.

I think it's just a missing

if (depth < afterTriggers.maxquerydepth)
   continue;
or similar in AfterTriggerPendingOnRel, inside the second loop.

Alternatively we could make the for something like
depth = 0; depth <= Min(afterTriggers.query_depth, afterTriggers.query_depth - 1)
but that doesn't seem prettier.

Robert?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

"'Andres Freund'" <andres@2ndquadrant.com> writes:
> On 2014-11-06 16:15:15 -0500, Tom Lane wrote:
>> No, the given test case crashes HEAD just fine.  Robert seems to have
>> changed the invariants around the trigger query stack in some inconsistent
>> fashion.

> I think it's just a missing
> if (depth < afterTriggers.maxquerydepth)
>    continue;
> or similar in AfterTriggerPendingOnRel, inside the second loop.

No such test was required before.  If one's needed now, then I'd wonder
what other places have new bugs of the same ilk.

            regards, tom lane