Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problems with unconstrained join

Problems with unconstrained join

From
Oliver Elphick
Date:
Something rather weird happens if you ask for an unlimited join that
gives too many rows:

bray=# select count(*) from product;
 count
-------
  5482
(1 row)

bray=# select count(*) from stock;
 count
-------
  5482
(1 row)

[ Cartesian product = 30,052,324 rows ]

bray=# select p.id from product as p, stock;
server sent data ("D" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
---[lots of those]...
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent binary data ("B" message) without prior row description ("T" message)
server sent data ("D" message) without prior row description ("T" message)
5002D2DH5002D03D    H5003D003DD5003D4DH5004D04D    H5004D005DD5005D5DH5005D06D    H5006D006DD5006D7DH5007D07D
H5007D008DD5008D8DH5008D09D   H5009D009DD5009D0DH5010D10D    H5010D010DD5011D1DH5011D12D    H5012D012DD5012D3DH5013D13D
  H5013D013DD5014D4DH5014D15D    H5015D015DD5015D6DH5016D16D    H5016D01 
...[quite a lot more of that]...
Cancel request sent
Cancel request sent

I used ctrl-C to cancel, but the query did not terminate.  The backend
was listed as idle, so I guess that psql had choked.  I had to kill it
off.

The PostgreSQL version is 7.2.

Obviously psql or the backend was being asked to cope with too much
data, but is there any way to handle the situation more cleanly?

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "The LORD is my light and my salvation; whom shall I
      fear? the LORD is the strength of my life; of whom
      shall I be afraid?"           Psalms 27:1

Re: Problems with unconstrained join

From
David Griffiths
Date:
Not joining two tables in a where clause can take down a database if the
tables have enough rows or the database server is not overly powerful.

Perhaps if you limit the # of rows back (I think the Postgres command is
LIMIT ## where ## is the number of rows - Oracle uses RowNum).

David.
----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, March 06, 2002 6:21 AM
Subject: [GENERAL] Problems with unconstrained join


> Something rather weird happens if you ask for an unlimited join that
> gives too many rows:
>
> bray=# select count(*) from product;
>  count
> -------
>   5482
> (1 row)
>
> bray=# select count(*) from stock;
>  count
> -------
>   5482
> (1 row)
>
> [ Cartesian product = 30,052,324 rows ]
>
> bray=# select p.id from product as p, stock;
> server sent data ("D" message) without prior row description ("T" message)
> server sent data ("D" message) without prior row description ("T" message)
> server sent data ("D" message) without prior row description ("T" message)
> ---[lots of those]...
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent binary data ("B" message) without prior row description ("T"
message)
> server sent data ("D" message) without prior row description ("T" message)
> 5002D2DH5002D03D H5003D003DD5003D4DH5004D04D H5004D005DD5005D5DH5005D06D
H5006D006DD5006D7DH5007D07D H5007D008DD5008D8DH5008D09D
H5009D009DD5009D0DH5010D10D H5010D010DD5011D1DH5011D12D
H5012D012DD5012D3DH5013D13D H5013D013DD5014D4DH5014D15D
H5015D015DD5015D6DH5016D16D H5016D01
> ...[quite a lot more of that]...
> Cancel request sent
> Cancel request sent
>
> I used ctrl-C to cancel, but the query did not terminate.  The backend
> was listed as idle, so I guess that psql had choked.  I had to kill it
> off.
>
> The PostgreSQL version is 7.2.
>
> Obviously psql or the backend was being asked to cope with too much
> data, but is there any way to handle the situation more cleanly?
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>
>      "The LORD is my light and my salvation; whom shall I
>       fear? the LORD is the strength of my life; of whom
>       shall I be afraid?"           Psalms 27:1
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Problems with unconstrained join

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> [ Cartesian product = 30,052,324 rows ]

> bray=# select p.id from product as p, stock;
> server sent data ("D" message) without prior row description ("T" message)
> server sent data ("D" message) without prior row description ("T" message)

libpq does not respond very gracefully to running out of memory for a
query result.  It doesn't crash exactly, but it loses track of what's
coming in, after which you get all these useless error messages.  Feel
free to fix it ...

            regards, tom lane

Re: Problems with unconstrained join

From
Oliver Elphick
Date:
On Wed, 2002-03-06 at 17:06, Tom Lane wrote:
> libpq does not respond very gracefully to running out of memory for a
> query result.  It doesn't crash exactly, but it loses track of what's
> coming in, after which you get all these useless error messages.  Feel
> free to fix it ...

Oh well, at least I know which end of the connection to look...

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "The LORD is my light and my salvation; whom shall I
      fear? the LORD is the strength of my life; of whom
      shall I be afraid?"           Psalms 27:1