Re: Problems with unconstrained join - Mailing list pgsql-general

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