Обсуждение: Server crash when using dblink_build_sql_* after alter table

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 <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

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

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

Robert Voinea <robert.voinea@topex.ro> 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

Robert Voinea <robert.voinea@topex.ro> 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

On Tuesday 15 June 2010 23:31:26 Tom Lane wrote:
> Robert Voinea <robert.voinea@topex.ro> 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 <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