Re: query causes connection termination
Tomas Vondra writes: > On 11/23/2017 01:25 AM, Neto pr wrote: >> Anyone have any tips on why this occurs? > Attach gdb to the backend, run the query and when it fails get us the > backtrace. So something like More details on that here: https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend regards, tom lane
Re: query causes connection termination
On 11/23/2017 01:25 AM, Neto pr wrote: > Another fact is that when executing the query without the command > EXPLAIN ANALYZE, the result is usually returned after a few minutes. > I do not understand, because when using the EXPLAIN ANALYZE command the > dbms closes the connection. > Anyone have any tips on why this occurs? > Attach gdb to the backend, run the query and when it fails get us the backtrace. So something like 1) select pg_backend_pid() 2) gdb -p $PID 3) run the EXPLAIN ANALYZE again 4) watch the gdb session, when it fails do 'bt' You need to install debuginfo first, so that the backtrace makes sense. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: query causes connection termination
Another fact is that when executing the query without the command EXPLAIN
ANALYZE, the result is usually returned after a few minutes.
I do not understand, because when using the EXPLAIN ANALYZE command the
dbms closes the connection.
Anyone have any tips on why this occurs?
2017-11-22 21:19 GMT-03:00 Neto pr :
> Only complementing
> I use postgresql version 10.
> However the postgresql.conf file has standard settings.
> My server is a 2.8 GHz Xeon (4 core) and SSDs disc.
>
> 2017-11-22 21:12 GMT-03:00 Neto pr :
>
>> Dear all,
>> when executing a query, it causes the database to close the connection.
>> See the error reported by the SQL TOOL DBEAVER tool:
>>
>> - DBEAVER SQL tool-
>> An I / O error occurred while sending to the backend.
>>java.io.EOFException:
>>
>> ---
>>
>> I tried to execute the query in PSQL but the same thing happens. The
>> query is the 19 of the TPC-H Benchmark.
>>
>> ---PSQL Cliente Sql --
>> tpch40gnorssd=# EXPLAIN (ANALYZE) select sum(l_extendedprice* (1 -
>> l_discount)) as revenue
>> tpch40gnorssd-# fromlineitem, part
>>
>> tpch40gnorssd-# where (
>>
>> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(# and part.p_brand = 'Brand#54'
>>
>> tpch40gnorssd(# and part.p_container in ('SM CASE', 'SM
>> BOX', 'SM PACK', 'SM PKG')
>> tpch40gnorssd(# and lineitem.l_quantity >= 4 and
>> lineitem.l_quantity <= 4 + 10
>> tpch40gnorssd(# and part.p_size between 1 and 5
>>
>> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>> tpch40gnorssd(# )
>>
>> tpch40gnorssd-# or
>>
>> tpch40gnorssd-# (
>>
>> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(# and part.p_brand = 'Brand#51'
>>
>> tpch40gnorssd(# and part.p_container in ('MED BAG', 'MED
>> BOX', 'MED PKG', 'MED PACK')
>> tpch40gnorssd(# and lineitem.l_quantity >= 11 and
>> lineitem.l_quantity <= 11 + 10
>> tpch40gnorssd(# and part.p_size between 1 and 10
>>
>> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>> tpch40gnorssd(# )
>>
>> tpch40gnorssd-# or
>>
>> tpch40gnorssd-# (
>>
>> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(# and part.p_brand = 'Brand#21'
>>
>> tpch40gnorssd(# and part.p_container in ('LG CASE', 'LG
>> BOX', 'LG PACK', 'LG PKG')
>> tpch40gnorssd(# and lineitem.l_quantity >= 28 and
>> lineitem.l_quantity <= 28 + 10
>> tpch40gnorssd(# and part.p_size between 1 and 15
>>
>> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>>
>> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>>
>> tpch40gnorssd(# );
>>
>>
>>
>> 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.
>>
>>
>> !>
>>
>>
>> !>
>>
>>
>> However, when executing an Explain query, no error is reported.
>>
>>
>> - EXPLAIN ONLY
>>
>> Finalize Aggregate (cost=280394.81..280394.82 rows=1 width=32)
>>-> Gather (cost=280394.59..280394.80 rows=2 width=32)
>> Workers Planned: 2
>> -> Partial Aggregate (cost=279394.59..279394.60 rows=1
>> width=32)
>>-> Nested Loop (cost=29935.44..279381.95 rows=1685
>> width=12)
>> -> Parallel Bitmap Heap Scan on part
>> (cost=29934.87..48103.87 rows=7853 width=30)
>>Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
>> AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
>> BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
>> 'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND
>> (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED
>> PACK"}'::bpchar[]))) OR ((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15)
>> AND (p_si
>> ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
>> PKG"}'::bpchar[]
>>-> BitmapOr (cost=29934.87..29934.87
>> rows=18861 width=0)
>> -> BitmapAnd (cost=9559.76..9559.76
>> rows=3140 width=0)
>>-> Bitmap Index S
Re: query causes connection termination
Only complementing
I use postgresql version 10.
However the postgresql.conf file has standard settings.
My server is a 2.8 GHz Xeon (4 core) and SSDs disc.
2017-11-22 21:12 GMT-03:00 Neto pr :
> Dear all,
> when executing a query, it causes the database to close the connection.
> See the error reported by the SQL TOOL DBEAVER tool:
>
> - DBEAVER SQL tool-
> An I / O error occurred while sending to the backend.
>java.io.EOFException:
>
> ---
>
> I tried to execute the query in PSQL but the same thing happens. The query
> is the 19 of the TPC-H Benchmark.
>
> ---PSQL Cliente Sql --
> tpch40gnorssd=# EXPLAIN (ANALYZE) select sum(l_extendedprice* (1 -
> l_discount)) as revenue
> tpch40gnorssd-# fromlineitem, part
>
> tpch40gnorssd-# where (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#54'
>
> tpch40gnorssd(# and part.p_container in ('SM CASE', 'SM
> BOX', 'SM PACK', 'SM PKG')
> tpch40gnorssd(# and lineitem.l_quantity >= 4 and
> lineitem.l_quantity <= 4 + 10
> tpch40gnorssd(# and part.p_size between 1 and 5
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(# )
>
> tpch40gnorssd-# or
>
> tpch40gnorssd-# (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#51'
>
> tpch40gnorssd(# and part.p_container in ('MED BAG', 'MED
> BOX', 'MED PKG', 'MED PACK')
> tpch40gnorssd(# and lineitem.l_quantity >= 11 and
> lineitem.l_quantity <= 11 + 10
> tpch40gnorssd(# and part.p_size between 1 and 10
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(# )
>
> tpch40gnorssd-# or
>
> tpch40gnorssd-# (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#21'
>
> tpch40gnorssd(# and part.p_container in ('LG CASE', 'LG
> BOX', 'LG PACK', 'LG PKG')
> tpch40gnorssd(# and lineitem.l_quantity >= 28 and
> lineitem.l_quantity <= 28 + 10
> tpch40gnorssd(# and part.p_size between 1 and 15
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
>
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
>
> tpch40gnorssd(# );
>
>
>
> 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.
>
>
> !>
>
>
> !>
>
>
> However, when executing an Explain query, no error is reported.
>
>
> - EXPLAIN ONLY
>
> Finalize Aggregate (cost=280394.81..280394.82 rows=1 width=32)
>-> Gather (cost=280394.59..280394.80 rows=2 width=32)
> Workers Planned: 2
> -> Partial Aggregate (cost=279394.59..279394.60 rows=1 width=32)
>-> Nested Loop (cost=29935.44..279381.95 rows=1685
> width=12)
> -> Parallel Bitmap Heap Scan on part
> (cost=29934.87..48103.87 rows=7853 width=30)
>Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
> AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
> BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
> 'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND (p_container
> = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))) OR
> ((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_si
> ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
> PKG"}'::bpchar[]
>-> BitmapOr (cost=29934.87..29934.87
> rows=18861 width=0)
> -> BitmapAnd (cost=9559.76..9559.76
> rows=3140 width=0)
>-> Bitmap Index Scan on
> idx_p_brand_p_size (cost=0.00..508.37 rows=31035 width=0)
> Index Cond: ((p_brand =
> 'Brand#54'::bpchar) AND (p_size <= 5) AND (p_size >= 1))
>-> Bitmap Index Scan on
> idx_p_containerpart000 (cost=0.00..9041.72 rows=809333 width=0)
> Index Cond: (p_container =
> ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bp
query causes connection termination
Dear all,
when executing a query, it causes the database to close the connection.
See the error reported by the SQL TOOL DBEAVER tool:
- DBEAVER SQL tool-
An I / O error occurred while sending to the backend.
java.io.EOFException:
---
I tried to execute the query in PSQL but the same thing happens. The query
is the 19 of the TPC-H Benchmark.
---PSQL Cliente Sql --
tpch40gnorssd=# EXPLAIN (ANALYZE) select sum(l_extendedprice* (1 -
l_discount)) as revenue
tpch40gnorssd-# fromlineitem, part
tpch40gnorssd-# where (
tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
tpch40gnorssd(# and part.p_brand = 'Brand#54'
tpch40gnorssd(# and part.p_container in ('SM CASE', 'SM
BOX', 'SM PACK', 'SM PKG')
tpch40gnorssd(# and lineitem.l_quantity >= 4 and
lineitem.l_quantity <= 4 + 10
tpch40gnorssd(# and part.p_size between 1 and 5
tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
REG')
tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'
tpch40gnorssd(# )
tpch40gnorssd-# or
tpch40gnorssd-# (
tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
tpch40gnorssd(# and part.p_brand = 'Brand#51'
tpch40gnorssd(# and part.p_container in ('MED BAG', 'MED
BOX', 'MED PKG', 'MED PACK')
tpch40gnorssd(# and lineitem.l_quantity >= 11 and
lineitem.l_quantity <= 11 + 10
tpch40gnorssd(# and part.p_size between 1 and 10
tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
REG')
tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'
tpch40gnorssd(# )
tpch40gnorssd-# or
tpch40gnorssd-# (
tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
tpch40gnorssd(# and part.p_brand = 'Brand#21'
tpch40gnorssd(# and part.p_container in ('LG CASE', 'LG
BOX', 'LG PACK', 'LG PKG')
tpch40gnorssd(# and lineitem.l_quantity >= 28 and
lineitem.l_quantity <= 28 + 10
tpch40gnorssd(# and part.p_size between 1 and 15
tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
REG')
tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'
tpch40gnorssd(# );
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.
!>
!>
However, when executing an Explain query, no error is reported.
- EXPLAIN ONLY
Finalize Aggregate (cost=280394.81..280394.82 rows=1 width=32)
-> Gather (cost=280394.59..280394.80 rows=2 width=32)
Workers Planned: 2
-> Partial Aggregate (cost=279394.59..279394.60 rows=1 width=32)
-> Nested Loop (cost=29935.44..279381.95 rows=1685
width=12)
-> Parallel Bitmap Heap Scan on part
(cost=29934.87..48103.87 rows=7853 width=30)
Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND (p_container
= ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))) OR
((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_si
ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
PKG"}'::bpchar[]
-> BitmapOr (cost=29934.87..29934.87
rows=18861 width=0)
-> BitmapAnd (cost=9559.76..9559.76
rows=3140 width=0)
-> Bitmap Index Scan on
idx_p_brand_p_size (cost=0.00..508.37 rows=31035 width=0)
Index Cond: ((p_brand =
'Brand#54'::bpchar) AND (p_size <= 5) AND (p_size >= 1))
-> Bitmap Index Scan on
idx_p_containerpart000 (cost=0.00..9041.72 rows=809333 width=0)
Index Cond: (p_container = ANY
('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[]))
-> BitmapAnd (cost=9837.67..9837.67
rows=6022 width=0)
-> Bitmap Index Scan on
idx_p_brand_p_size (cost=0.00..997.27 rows=60947 width=0)
Index Cond: ((p_brand =
'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1))
-> Bitmap Index Scan on
idx_p_
