Обсуждение: 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