From: Robert Voinea <robert.voinea@topex.ro>
Organization: Topex
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Server crash when using dblink_build_sql_* after alter table
Date: Mon, 14 Jun 2010 14:15:09 +0300
User-Agent: KMail/1.13.2 (Linux/2.6.32-22-generic; KDE/4.4.2; i686; ; )
Cc: pgsql-admin@postgresql.org
References: <201006111551.23382.robert.voinea@topex.ro>
	<7091.1276266088@sss.pgh.pa.us>
In-Reply-To: <7091.1276266088@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_98gFMfQb8WlUVh1"
Message-Id: <201006141415.09471.robert.voinea@topex.ro>

--Boundary-00=_98gFMfQb8WlUVh1
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit

On Friday 11 June 2010 17:21:28 Tom Lane wrote:
> Robert Voinea <robert.voinea@topex.ro> writes:
> > I have a development database that is altered a lot and a set of
> > functions that make use of dblink_build_sql_*.
> > The problem is that after I update a table whenever I use a function that
> > makes use of dblink_build_sql the server crashes.
> 
> This is a bug, but you have not provided sufficient information to let
> someone else reproduce it.  If you're unclear on what a complete bug
> report requires, see
> http://www.postgresql.org/docs/8.4/static/bug-reporting.html
> 
> 			regards, tom lane

Sorry about that. I wrote the mail in a hurry.

OS: Linux
Postgres Versions: 8.4.2 -- 8.4.4

Kernels
Linux vr 2.6.32-22-generic #36-Ubuntu SMP Thu Jun 3 22:02:19 UTC 2010 i686 
GNU/Linux
PostgreSQL 8.4.4

Linux topex_server 2.6.31-gentoo-r2 #1 SMP Tue Oct 13 15:48:58 EEST 2009 i686 
Intel(R) Xeon(R) CPU E5504 @ 2.00GHz GenuineIntel GNU/Linux
PostgreSQL 8.4.4

I attached the server log (for the crash case) and a test case with logs.
For that test case this is the error I get, no server crash.

ERROR:  invalid memory alloc request size 4294967293

The scenario is this:
Create a table, drop a column, any column then add a varchar column and an int 
column. Then use dblink_build_sql_update.

I am yet to identify the crash scenario...

I hope this helps...

-- 
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro

--Boundary-00=_98gFMfQb8WlUVh1
Content-Type: text/x-log; charset="UTF-8";
	name="postgresql-2010-06-14_110014.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="postgresql-2010-06-14_110014.log"

LOG:  00000: database system was shut down at 2010-06-14 11:00:13 EEST
LOCATION:  StartupXLOG, xlog.c:5255
LOG:  00000: autovacuum launcher started
LOCATION:  AutoVacLauncherMain, autovacuum.c:529
LOG:  00000: database system is ready to accept connections
LOCATION:  reaper, postmaster.c:2326
LOG:  00000: connection received: host=[local]
LOCATION:  BackendInitialize, postmaster.c:3261
LOG:  00000: connection authorized: user=postgres database=postgres
LOCATION:  BackendInitialize, postmaster.c:3339
LOG:  00000: disconnection: session time: 0:00:00.004 user=postgres database=postgres host=[local]
LOCATION:  log_disconnections, postgres.c:4024
LOG:  00000: connection received: host=[local]
LOCATION:  BackendInitialize, postmaster.c:3261
LOG:  00000: connection authorized: user=softswitch database=softswitch
LOCATION:  BackendInitialize, postmaster.c:3339
LOG:  00000: statement: select * from dblink_build_sql_update ('ChannelsTable', '1', 1, ARRAY[3::TEXT], ARRAY[3::TEXT]);
LOCATION:  exec_simple_query, postgres.c:834
LOG:  00000: server process (PID 6086) was terminated by signal 11: Segmentation fault
LOCATION:  LogChildExit, postmaster.c:2727
LOG:  00000: terminating any other active server processes
LOCATION:  HandleChildCrash, postmaster.c:2554
LOG:  00000: all server processes terminated; reinitializing
LOCATION:  PostmasterStateMachine, postmaster.c:2912
LOG:  00000: database system was interrupted; last known up at 2010-06-14 11:00:14 EEST
LOCATION:  StartupXLOG, xlog.c:5275
LOG:  00000: database system was not properly shut down; automatic recovery in progress
LOCATION:  StartupXLOG, xlog.c:5449
LOG:  00000: connection received: host=[local]
LOCATION:  BackendInitialize, postmaster.c:3261
FATAL:  57P03: the database system is in recovery mode
LOCATION:  ProcessStartupPacket, postmaster.c:1774
LOG:  00000: record with zero length at 0/6C1EA0F0
LOCATION:  ReadRecord, xlog.c:3571
LOG:  00000: redo is not required
LOCATION:  StartupXLOG, xlog.c:5675
LOG:  00000: checkpoint starting: end-of-recovery immediate
LOCATION:  LogCheckpointStart, xlog.c:6288
LOG:  00000: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.000 s
LOCATION:  LogCheckpointEnd, xlog.c:6334
LOG:  00000: autovacuum launcher started
LOCATION:  AutoVacLauncherMain, autovacuum.c:529
LOG:  00000: database system is ready to accept connections
LOCATION:  reaper, postmaster.c:2326

--Boundary-00=_98gFMfQb8WlUVh1
Content-Type: text/x-log;
  charset="UTF-8";
  name="dblink_test.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="dblink_test.log"

psql:./dblink_test.sql:2: LOG:  00000: duration: 5.058 ms  statement: DROP TABLE IF EXISTS dblink_test;
LOCATION:  exec_simple_query, postgres.c:1068
DROP TABLE
psql:./dblink_test.sql:8: NOTICE:  00000: CREATE TABLE will create implicit sequence "dblink_test_id_seq" for serial column "dblink_test.id"
LOCATION:  transformColumnDefinition, parse_utilcmd.c:341
psql:./dblink_test.sql:8: NOTICE:  00000: CREATE TABLE / PRIMARY KEY will create implicit index "dblink_test_pkey" for table "dblink_test"
LOCATION:  DefineIndex, indexcmds.c:438
psql:./dblink_test.sql:8: LOG:  00000: duration: 11.932 ms  statement: CREATE TABLE dblink_test
(
	id SERIAL PRIMARY KEY,
	col1 INT NOT NULL DEFAULT 0,
	col1b INT NOT NULL DEFAULT 0
);
LOCATION:  exec_simple_query, postgres.c:1068
CREATE TABLE
psql:./dblink_test.sql:10: LOG:  00000: duration: 1.160 ms  statement: INSERT INTO dblink_test VALUES(default);
LOCATION:  exec_simple_query, postgres.c:1068
INSERT 0 1
psql:./dblink_test.sql:15: LOG:  00000: duration: 8.336 ms  statement: ALTER TABLE dblink_test
	DROP COLUMN col1,
	ADD COLUMN col2 VARCHAR(10) NOT NULL DEFAULT '',
	ADD COLUMN col3 INT NOT NULL DEFAULT 0;
LOCATION:  exec_simple_query, postgres.c:1068
ALTER TABLE
psql:./dblink_test.sql:18: ERROR:  XX000: invalid memory alloc request size 4294967293
LOCATION:  MemoryContextAlloc, mcxt.c:510

--Boundary-00=_98gFMfQb8WlUVh1
Content-Type: text/x-log;
  charset="UTF-8";
  name="dblink_test_server.log"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="dblink_test_server.log"

2010-06-14 14:12:25 EEST LOG:  00000: duration: 5.058 ms  statement: DROP TABLE IF EXISTS dblink_test;
2010-06-14 14:12:25 EEST LOCATION:  exec_simple_query, postgres.c:1068
2010-06-14 14:12:25 EEST LOG:  00000: duration: 11.932 ms  statement: CREATE TABLE dblink_test
        (
                id SERIAL PRIMARY KEY,
                col1 INT NOT NULL DEFAULT 0,
                col1b INT NOT NULL DEFAULT 0
        );
2010-06-14 14:12:25 EEST LOCATION:  exec_simple_query, postgres.c:1068
2010-06-14 14:12:25 EEST LOG:  00000: duration: 1.160 ms  statement: INSERT INTO dblink_test VALUES(default);
2010-06-14 14:12:25 EEST LOCATION:  exec_simple_query, postgres.c:1068
2010-06-14 14:12:25 EEST LOG:  00000: duration: 8.336 ms  statement: ALTER TABLE dblink_test
                DROP COLUMN col1,
                ADD COLUMN col2 VARCHAR(10) NOT NULL DEFAULT '',
                ADD COLUMN col3 INT NOT NULL DEFAULT 0;
2010-06-14 14:12:25 EEST LOCATION:  exec_simple_query, postgres.c:1068
2010-06-14 14:12:25 EEST ERROR:  XX000: invalid memory alloc request size 4294967293
2010-06-14 14:12:25 EEST LOCATION:  MemoryContextAlloc, mcxt.c:510
2010-06-14 14:12:25 EEST STATEMENT:  SELECT * FROM
                dblink_build_sql_update('dblink_test', '1', 1, ARRAY[1::TEXT], ARRAY[1::TEXT]);
--Boundary-00=_98gFMfQb8WlUVh1
Content-Type: text/x-sql;
  charset="UTF-8";
  name="dblink_test.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="dblink_test.sql"

\set VERBOSITY verbose
DROP TABLE IF EXISTS dblink_test;
CREATE TABLE dblink_test
(
	id SERIAL PRIMARY KEY,
	col1 INT NOT NULL DEFAULT 0,
	col1b INT NOT NULL DEFAULT 0
);

INSERT INTO dblink_test VALUES(default);

ALTER TABLE dblink_test
	DROP COLUMN col1,
	ADD COLUMN col2 VARCHAR(10) NOT NULL DEFAULT '',
	ADD COLUMN col3 INT NOT NULL DEFAULT 0;

SELECT * FROM
	dblink_build_sql_update('dblink_test', '1', 1, ARRAY[1::TEXT], ARRAY[1::TEXT]);

--Boundary-00=_98gFMfQb8WlUVh1--