Target lists can have at most 1664 entries?
What does this mean and how can it be fixed? We are running Hibernate with PostgreSQL 8.3.x...
Regards,
BTJ
--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen
btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------
Bjørn T Johansen wrote:
What does this mean and how can it be fixed? We are running Hibernate
with PostgreSQL 8.3.x...
What query is Hibernate generating? That's an error from the planner.
--
Richard Huxton
Archonet Ltd
Richard Huxton wrote:
Bjørn T Johansen wrote:
What does this mean and how can it be fixed? We are running Hibernate
with PostgreSQL 8.3.x...What query is Hibernate generating? That's an error from the planner.
It does sound like you're trying to query back more than 1664 columns in
one query. That's a *lot*, but auto generated queries can do the
strangest things :-)
//Magnus
=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could you
possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).
regards, tom lane
On Wed, 02 Jul 2008 09:03:04 +0200
Magnus Hagander <magnus@hagander.net> wrote:
Richard Huxton wrote:
Bjørn T Johansen wrote:
What does this mean and how can it be fixed? We are running Hibernate
with PostgreSQL 8.3.x...What query is Hibernate generating? That's an error from the planner.
It does sound like you're trying to query back more than 1664 columns in
one query. That's a *lot*, but auto generated queries can do the
strangest things :-)//Magnus
That might be the case.... Is it possible to increase this value for PostgreSQL or do we have to look at Hibernate?
BTJ
Magnus Hagander wrote:
Richard Huxton wrote:
Bjørn T Johansen wrote:
What does this mean and how can it be fixed? We are running Hibernate
with PostgreSQL 8.3.x...What query is Hibernate generating? That's an error from the planner.
It does sound like you're trying to query back more than 1664 columns in
one query. That's a *lot*, but auto generated queries can do the
strangest things :-)
I *thought* it was referring to columns, but then thought "no, can't be".
--
Richard Huxton
Archonet Ltd
On Wed, 02 Jul 2008 03:04:04 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could you
possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).regards, tom lane
Well, I do agree but it is not my design and a "fix" in PostgreSQL would be quicker than fixing the design....
BTJ
Tom Lane wrote:
=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could you
possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).
(I'm pretty new to Hibernate, so I can only share my general
understanding, but:)
One possible reason is that sometimes tools like Hibernate like to fetch
records from multiple related tables in the database in one query with
chained left joins. They then scan the results and eliminate duplicates
where appropriate.
It sounds horrifying, but it can actually be very fast where fairly
small data sets are being fetched from highly normalized tables with
appropriate indexes. In other circumstances, however, like when there
are very high row counts or lots of fields being returned, it's a very
bad strategy.
My guess is that they haven't told Hibernate to use an appropriate
fetching strategy (multiple SELECTs) for the data they're trying to
load, and for some reason Hibernate is choosing a left join fetch. If
they apply the appropriate annotations to their Hibernate data model or
adjust their HQL queries to avoid "left join fetch" they might find that
the problem goes away - and performance improves significantly.
--
Craig Ringer
On Wed, 02 Jul 2008 15:24:38 +0800
Craig Ringer <craig@postnewspapers.com.au> wrote:
Tom Lane wrote:
=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could you
possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).(I'm pretty new to Hibernate, so I can only share my general
understanding, but:)One possible reason is that sometimes tools like Hibernate like to fetch
records from multiple related tables in the database in one query with
chained left joins. They then scan the results and eliminate duplicates
where appropriate.It sounds horrifying, but it can actually be very fast where fairly
small data sets are being fetched from highly normalized tables with
appropriate indexes. In other circumstances, however, like when there
are very high row counts or lots of fields being returned, it's a very
bad strategy.My guess is that they haven't told Hibernate to use an appropriate
fetching strategy (multiple SELECTs) for the data they're trying to
load, and for some reason Hibernate is choosing a left join fetch. If
they apply the appropriate annotations to their Hibernate data model or
adjust their HQL queries to avoid "left join fetch" they might find that
the problem goes away - and performance improves significantly.--
Craig Ringer
Ok, guess we have to look at our Hibernate config.... (we are only using default fetching strategy...)
Thx...
BTJ
On Wed, Jul 02, 2008 at 09:22:50AM +0200, Bj�rn T Johansen wrote:
On Wed, 02 Jul 2008 03:04:04 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could
you possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).Well, I do agree but it is not my design and a "fix" in PostgreSQL
would be quicker than fixing the design....
That's where you're badly mistaken. Your application is completely
broken, and trying to adjust everybody else's Postgres to accommodate
*your* broken application is both selfish and short-sighted. It's
selfish because you're asking others to do work they don't need to do
just so you can avoid doing work you need to do, and it's
short-sighted because your application is guaranteed to be broken in
lots of other ways if it's broken this way.
Fix the application, and if you can't, find another job where they're
not being idiots. There are plenty of Postgres-related jobs out
there.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, 5 Jul 2008 08:17:37 -0700
David Fetter <david@fetter.org> wrote:
On Wed, Jul 02, 2008 at 09:22:50AM +0200, Bjørn T Johansen wrote:
On Wed, 02 Jul 2008 03:04:04 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:=?UTF-8?Q?Bj=C3=B8rn?= T Johansen <btj@havleik.no> writes:
What does this mean and how can it be fixed?
Reduce the number of columns in your SELECTs?
This whiffs to me of excruciatingly bad schema design. How could
you possibly need upwards of a thousand columns in a query result?
IMHO reasonable column counts are O(10), not O(bignum).Well, I do agree but it is not my design and a "fix" in PostgreSQL
would be quicker than fixing the design....That's where you're badly mistaken. Your application is completely
broken, and trying to adjust everybody else's Postgres to accommodate
*your* broken application is both selfish and short-sighted. It's
selfish because you're asking others to do work they don't need to do
just so you can avoid doing work you need to do, and it's
short-sighted because your application is guaranteed to be broken in
lots of other ways if it's broken this way.Fix the application, and if you can't, find another job where they're
not being idiots. There are plenty of Postgres-related jobs out
there.Cheers,
David.
Actually, this discussion was finished a long time ago (we are already looking at the Hibernate config and domain modell)..
And btw, I wasn't proposing a change in PostgreSQL, only if there were some config that could be changed to accomodate this...
BTJ