Selecting large tables gets killed - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Selecting large tables gets killed
Date
Msg-id CAFjFpRftO_ZgUi8Afo_dg_MdXc3_G-mO6nAnwRuBu0cY6MBxWw@mail.gmail.com
Whole thread Raw
Responses Re: Selecting large tables gets killed  (amul sul <sul_amul@yahoo.co.in>)
Re: Selecting large tables gets killed  (Ian Lawrence Barwick <barwick@gmail.com>)
List pgsql-hackers
Hi All,
Here is a strange behaviour with master branch with head at

commit d3c4c471553265e7517be24bae64b81967f6df40
Author: Peter Eisentraut <peter_e@gmx.net>
Date:   Mon Feb 10 21:47:19 2014 -0500

The OS is
[ashutosh@ubuntu repro]uname -a
Linux ubuntu 3.2.0-59-generic #90-Ubuntu SMP Tue Jan 7 22:43:51 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

This is a VM hosted on Mac-OS X 10.7.5

Here's the SQL script

[ashutosh@ubuntu repro]cat big_select_killed.sql
drop table big_tab;
create table big_tab (val int, val2 int, str varchar);

insert into big_tab select x, x, lpad('string', 100, x::text)
    from generate_series(1, 10000000) x;

select * from big_tab;

The last select causes the "Killed" message.

[ashutosh@ubuntu repro]psql -d postgres -f big_select_killed.sql
DROP TABLE
CREATE TABLE
INSERT 0 10000000
Killed

There is a message in server log
FATAL:  connection to client lost
STATEMENT:  select * from big_tab;

Any SELECT selecting all the rows is getting psql killed but not SELECT count(*)

[ashutosh@ubuntu repro]psql -d postgres
psql (9.4devel)
Type "help" for help.

postgres=# select count(*) from big_tab;
  count  
----------
 10000000
(1 row)

postgres=# select * from big_tab;
Killed

[ashutosh@ubuntu repro]psql -d postgres
psql (9.4devel)
Type "help" for help.

Below is the buffer cache size and the relation size (if anyone cares)
postgres=# show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

postgres=# select pg_relation_size('big_tab'::regclass);
 pg_relation_size
------------------
       1412415488
(1 row)

postgres=# select pg_relation_size('big_tab'::regclass)/1024/1024; -- IN MBs to be simple
 ?column?
----------
     1346
(1 row)

There are no changes in default configuration. Using unix sockets
[ashutosh@ubuntu repro]ls /tmp/.s.PGSQL.5432*
/tmp/.s.PGSQL.5432  /tmp/.s.PGSQL.5432.lock

Looks like a bug in psql to me. Does anybody see that behaviour?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

pgsql-hackers by date:

Previous
From: Kyotaro HORIGUCHI
Date:
Subject: Re: inherit support for foreign tables
Next
From: Alexander Korotkov
Date:
Subject: Re: PoC: Partial sort