Lists: | pgsql-bugspgsql-hackers |
---|
From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Bug related to out of memory condition |
Date: | 2006-10-24 02:03:07 |
Message-ID: | 1161655387.18892.29.camel@dogma.v10.wvs |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
This behavior exists in 8.1.4 and CVS HEAD.
I list below my preexisting schema, a set of commands that behave as I
expect (and result in an ERROR), and a similar set of commands that do
not behave as I expect (and result in a PANIC). Note the position of
"BEGIN" in each.
This is quite finicky behavior. Apparently the columns in the "crashme"
table have a significant effect on the results. If you have trouble
reproducing this, I can give more system details. However, it behaves
this way on a fresh install of CVS HEAD, and it's 100% reproducible (for
me, anyway).
Regards,
Jeff Davis
------- EXISTING SCHEMA -------------
CREATE TABLE r1( i INT PRIMARY KEY );
INSERT INTO r1 VALUES(1);
CREATE TABLE r2( i INT PRIMARY KEY );
INSERT INTO r2 VALUES(1);
CREATE TABLE r3( i INT PRIMARY KEY );
INSERT INTO r3 VALUES(1);
CREATE TABLE r4( i INT PRIMARY KEY );
INSERT INTO r4 VALUES(1);
First, here is the correct behavior:
-------- CORRECT BEHAVIOR -----------
crashme=> CREATE TABLE crashme (
crashme(> attr1 SERIAL8 PRIMARY KEY,
crashme(> attr2 TIMESTAMPTZ DEFAULT NOW(),
crashme(> attr3 TIMESTAMPTZ,
crashme(> attr4 INT REFERENCES r1(i),
crashme(> attr5 INT REFERENCES r2(i),
crashme(> attr6 INT REFERENCES r3(i),
crashme(> attr7 INT REFERENCES r4(i),
crashme(> attr8 TEXT
crashme(> );
NOTICE: CREATE TABLE will create implicit sequence "crashme_attr1_seq"
for serial column "crashme.attr1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"crashme_pkey" for table "crashme"
CREATE TABLE
crashme=> BEGIN;
BEGIN
crashme=> INSERT INTO crashme
(attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8) SELECT NEXTVAL
('crashme_attr1_seq'),NOW(),NOW(),1,1,1,1,'t1' FROM generate_series
(1,5000000);
ERROR: out of memory
DETAIL: Failed on request of size 32.
crashme=>
----------- UNEXPECTED BEHAVIOR ------------------
crashme=> BEGIN;
BEGIN
crashme=> CREATE TABLE crashme (
crashme(> attr1 SERIAL8 PRIMARY KEY,
crashme(> attr2 TIMESTAMPTZ DEFAULT NOW(),
crashme(> attr3 TIMESTAMPTZ,
crashme(> attr4 INT REFERENCES r1(i),
crashme(> attr5 INT REFERENCES r2(i),
crashme(> attr6 INT REFERENCES r3(i),
crashme(> attr7 INT REFERENCES r4(i),
crashme(> attr8 TEXT
crashme(> );
NOTICE: CREATE TABLE will create implicit sequence "crashme_attr1_seq"
for serial column "crashme.attr1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"crashme_pkey" for table "crashme"
CREATE TABLE
crashme=> INSERT INTO crashme
(attr1,attr2,attr3,attr4,attr5,attr6,attr7,attr8) SELECT NEXTVAL
('crashme_attr1_seq'),NOW(),NOW(),1,1,1,1,'t1' FROM generate_series
(1,5000000);
WARNING: AbortTransaction while in ABORT state
WARNING: AbortTransaction while in ABORT state
WARNING: AbortTransaction while in ABORT state
ERROR: out of memory
DETAIL: Failed on request of size 32.
ERROR: out of memory
DETAIL: Failed on request of size 27.
ERROR: out of memory
DETAIL: Failed on request of size 27.
ERROR: out of memory
DETAIL: Failed on request of size 27.
ERROR: out of memory
DETAIL: Failed on request of size 24.
PANIC: ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug related to out of memory condition (more information) |
Date: | 2006-10-24 22:32:05 |
Message-ID: | 1161729125.31124.20.camel@dogma.v10.wvs |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
I have made a clearer example of the bug I reported to -hackers
yesterday:
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01252.php
The following example shows a simple case that fails on 8.0+ (including
CVS HEAD), but works fine on 7.4. There are two almost identical
situations, and one causes an ERROR and the other a PANIC. The only
difference is the column type: INT versus TEXT, respectively.
I am on FreeBSD. An OOM condition must be caused to see this bug. In
7.4, an OOM condition is not even caused for the query, so perhaps it
has a the same bug, but handles foreign keys differently. Incidently,
foreign keys are all AFTER triggers, even in 7.4, but I don't understand
why 7.4 doesn't exhaust itself of memory collecting the trigger events,
as is described in the following mailing list post:
http://archives.postgresql.org/pgsql-bugs/2006-05/msg00036.php
Also, and this is pure conjecture, this bug may be related to the
following change in the 8.0 release notes:
"Nondeferred AFTER triggers are now fired immediately after completion
of the triggering query, rather than upon finishing the current
interactive command. This makes a difference when the triggering query
occurred within a function: the trigger is invoked before the function
proceeds to its next operation. For example, if a function inserts a new
row into a table, any nondeferred foreign key checks occur before
proceeding with the function."
Regards,
Jeff Davis
Step 1: Create 4 tables
-----------------------------------------
CREATE TABLE r1( i INT PRIMARY KEY );
INSERT INTO r1 VALUES(1);
CREATE TABLE r2( i INT PRIMARY KEY );
INSERT INTO r2 VALUES(1);
CREATE TABLE r3( i INT PRIMARY KEY );
INSERT INTO r3 VALUES(1);
CREATE TABLE r4( i INT PRIMARY KEY );
INSERT INTO r4 VALUES(1);
Step 2: Cause an out of memory condition. The result is an ERROR, as
expected.
-----------------------------------------
BEGIN;
CREATE TABLE crashme (
attr1 INT REFERENCES r1(i),
attr2 INT REFERENCES r2(i),
attr3 INT REFERENCES r3(i),
attr4 INT REFERENCES r4(i),
attr5 TEXT
);
INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,'t'
FROM generate_series(1,5000000);
Step 3: Do almost exacly the same thing, except attr5 is INT and not
TEXT type. This causes a PANIC instead of an ERROR. The bug is that this
should be only an ERROR, since everything is the same except the column
type for attr5.
---------------------------------------------------
BEGIN;
CREATE TABLE crashme (
attr1 INT REFERENCES r1(i),
attr2 INT REFERENCES r2(i),
attr3 INT REFERENCES r3(i),
attr4 INT REFERENCES r4(i),
attr5 INT
);
INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,1 FROM
generate_series(1,5000000);