Server crash when using dblink_build_sql_* after alter table
Server crash when using dblink_build_sql_* after alter table
am 11.06.2010 14:51:23 von Robert Voinea
Hi
I have the following issue:
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.
The ALTER TABLE sequences usually add columns and do not drop any.
This does not happen on every table, just now and then.
This is what I get when running dblink_build_sql_update on a table
(channelstable). I must mention that all id's exist in the table... except for
id=2 that does not exist and dblink reports it as inexistent.
I must say that this happens on more than one table.
All tables have a single field primary key.
The alter table sequence does not affect the primary key.
Any ideas?...
Thanks in advance.
Command log follows:
radu database # cat softswitch_interface.sql | grep
dblink_build_sql_update | grep 'ChannelsTable';
q := q || dblink_build_sql_update('ChannelsTable', '1', 1,
ARRAY[i.pkey::TEXT], ARRAY[i.pkey::TEXT]);
q := q || dblink_build_sql_update('ChannelsTable', '1', 1,
ARRAY[i.pkey::TEXT], ARRAY[i.pkey::TEXT]);
radu database # psql -U softswitch
psql (8.4.2)
Type "help" for help.
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[1::TEXT], ARRAY[1::TEXT]);
ERROR: could not open relation with OID 0
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[2::TEXT], ARRAY[2::TEXT]);
ERROR: source row not found
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[4::TEXT], ARRAY[4::TEXT]);
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
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.
radu database # psql -U softswitch
psql (8.4.2)
Type "help" for help.
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
softswitch=> SELECT * FROM dblink_build_sql_update('ChannelsTable', '1',
1, ARRAY[5::TEXT], ARRAY[5::TEXT]);
ERROR: invalid memory alloc request size 4294967293
--
Robert Voinea
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: Server crash when using dblink_build_sql_* after alter table
am 11.06.2010 16:21:28 von Tom Lane
Robert Voinea 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: Server crash when using dblink_build_sql_* after alter table
am 14.06.2010 13:15:09 von Robert Voinea
--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 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
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
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--Boundary-00=_98gFMfQb8WlUVh1--
Re: Server crash when using dblink_build_sql_* after alter table
am 14.06.2010 19:36:27 von Tom Lane
Robert Voinea writes:
> 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.
Yeah, it looks like dblink is simply wrong about how to count the
columns in a case with dropped columns. Thanks for the report, will
fix.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: Server crash when using dblink_build_sql_* after alter table
am 15.06.2010 22:31:26 von Tom Lane
Robert Voinea writes:
>>> The problem is that after I update a table whenever I use a function that
>>> makes use of dblink_build_sql the server crashes.
FYI, I've committed patches to deal with this and some related issues.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: Server crash when using dblink_build_sql_* after alter table
am 17.06.2010 06:55:18 von Robert Voinea
On Tuesday 15 June 2010 23:31:26 Tom Lane wrote:
> Robert Voinea writes:
> >>> The problem is that after I update a table whenever I use a function
> >>> that makes use of dblink_build_sql the server crashes.
>
> FYI, I've committed patches to deal with this and some related issues.
>
Thank you! :)
--
Robert Voinea
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin