Обсуждение: BUG #4113: server closed the connection unexpectedly

The following bug has been logged online:

Bug reference:      4113
Logged by:          Javier Hernandez
Email address:      dba@loopone.com
PostgreSQL version: 8.3.1
Operating system:   Linux version 2.6.9-11.ELsmp; Red Hat 3.4.3-22; gcc
version 3.4.3
Description:        server closed the connection unexpectedly
Details:

The following script generates an unexpected loss of connection.


begin;

\echo "Creating a table to hold numeric values only"
create table numeric_col_table (
num_col numeric(6,0) primary key not null
);

\echo "Creating a table to hold integer values only"
create table int_col_table (
int_col integer primary key not null
);


\echo "Inserting data into our numeric-only table"
insert into numeric_col_table (num_col) values (1), (2), (3);

\echo "Inserting data into our integer-only table"
insert into int_col_table (int_col) values (1), (2), (3);

\echo "Show contents of the numeric table"
select * from numeric_col_table;

\echo "Show contents of the numeric table"
select * from int_col_table;

\echo "Select everything from the integer table where int_col =
numeric_col"
select * from int_col_table where int_col in (select num_col from
numeric_col_table);

\echo "Select everything from the numeric table where numeric_col =
int_col"
select * from numeric_col_table where num_col in (select int_col from
int_col_table);

rollback;

Ouch, crash confirmed --- studying the cause now.

---------------------------------------------------------------------------

Javier Hernandez wrote:
>
> The following bug has been logged online:
>
> Bug reference:      4113
> Logged by:          Javier Hernandez
> Email address:      dba@loopone.com
> PostgreSQL version: 8.3.1
> Operating system:   Linux version 2.6.9-11.ELsmp; Red Hat 3.4.3-22; gcc
> version 3.4.3
> Description:        server closed the connection unexpectedly
> Details:
>
> The following script generates an unexpected loss of connection.
>
>
> begin;
>
> \echo "Creating a table to hold numeric values only"
> create table numeric_col_table (
> num_col numeric(6,0) primary key not null
> );
>
> \echo "Creating a table to hold integer values only"
> create table int_col_table (
> int_col integer primary key not null
> );
>
>
> \echo "Inserting data into our numeric-only table"
> insert into numeric_col_table (num_col) values (1), (2), (3);
>
> \echo "Inserting data into our integer-only table"
> insert into int_col_table (int_col) values (1), (2), (3);
>
> \echo "Show contents of the numeric table"
> select * from numeric_col_table;
>
> \echo "Show contents of the numeric table"
> select * from int_col_table;
>
> \echo "Select everything from the integer table where int_col =
> numeric_col"
> select * from int_col_table where int_col in (select num_col from
> numeric_col_table);
>
> \echo "Select everything from the numeric table where numeric_col =
> int_col"
> select * from numeric_col_table where num_col in (select int_col from
> int_col_table);
>
> rollback;
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Javier Hernandez wrote:

> select * from numeric_col_table where num_col in (select int_col from
> int_col_table);

#0  0x0000000000752368 in pg_detoast_datum (datum=0x1)
    at /pgsql/source/00head/src/backend/utils/fmgr/fmgr.c:2101
#1  0x00000000006bf8bc in hash_numeric (fcinfo=0x7fffc5140550)
    at /pgsql/source/00head/src/backend/utils/adt/numeric.c:1163
#2  0x000000000075081f in FunctionCall1 (flinfo=0xc65688, arg1=1)
    at /pgsql/source/00head/src/backend/utils/fmgr/fmgr.c:1250
#3  0x00000000005898c1 in TupleHashTableHash (key=0x7fffc51409f0, keysize=8)
    at /pgsql/source/00head/src/backend/executor/execGrouping.c:513
#4  0x00000000007550ee in hash_search (hashp=0xc6aec8, keyPtr=0x7fffc51409f0, action=HASH_ENTER,
    foundPtr=0x7fffc51409ef "") at /pgsql/source/00head/src/backend/utils/hash/dynahash.c:791
#5  0x0000000000589635 in LookupTupleHashEntry (hashtable=0xc68eb8, slot=0xc63078,
    isnew=0x7fffc5140a6f "") at /pgsql/source/00head/src/backend/executor/execGrouping.c:368
#6  0x000000000059e2e3 in lookup_hash_entry (aggstate=0xc64490, inputslot=0xc63138)
    at /pgsql/source/00head/src/backend/executor/nodeAgg.c:777
#7  0x000000000059e701 in agg_fill_hash_table (aggstate=0xc64490)
    at /pgsql/source/00head/src/backend/executor/nodeAgg.c:1028
#8  0x000000000059e35e in ExecAgg (node=0xc64490)
    at /pgsql/source/00head/src/backend/executor/nodeAgg.c:812


So what's happening here is that it's writing the hash table using the
wrong datatype ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Javier Hernandez wrote:
>> select * from numeric_col_table where num_col in (select int_col from
>> int_col_table);

> So what's happening here is that it's writing the hash table using the
> wrong datatype ...

Yeah, the planner is at fault here --- it should be coercing the value
to numeric before hashing.  I think this is wrong all the way back,
but pre-8.3 you'd have silently gotten wrong answers instead of a crash,
because the executor made up its own mind about how to unique-ify the
subquery outputs, and it looked directly at their actual data type
and chose some default equality operator for that.  This can be
demonstrated to be the Wrong Thing when the conversion to the IN
operator's datatype is lossy, as in this variant example:

create table numeric_col_table (
num_col numeric
);

create table float_col_table (
float_col float8
);

insert into numeric_col_table values (1), (1.000000000000000000001), (2), (3);

insert into float_col_table values (1), (2), (3);

select * from numeric_col_table;

select * from float_col_table;

select * from float_col_table where float_col in (select num_col from
numeric_col_table);

In 8.2 I get bogus results like

 float_col
-----------
         1
         1
         2
         3
(4 rows)

because 1 and 1.000000000000000000001 are perfectly distinct numeric
values, but not so much after they've been coerced to float.

In 8.3/HEAD I think this can be fixed by coercing the Vars that are put
into the InClauseInfo entry for the IN join.  Not sure how far back it
will be practical to apply that fix, though.

            regards, tom lane

"Javier Hernandez" <dba@loopone.com> writes:
> The following script generates an unexpected loss of connection.

I've applied a patch for this.  Thanks for the report!

            regards, tom lane