Thread: "Number of columns exceed limit" on a hierarchy of views
Hello all, I'm struggling with a database query that under some circumstances returns the error "ERROR: number of columns (2053) exceedslimit (1664)". Confusingly, though, no table is that wide. The problem seems to be my use of views. The largest table in the database is 500 columns wide. However there are a 3 viewslayered on top of it that implement business logic and denormalisation (again, the views are about 500 columns wide) - the first implements rules on how some columns can inherit values from other tables - the second hides values that are considered 'invalid' for some reason (usually based on the value in some other column,sometimes some other table) - the final view handles denormalisation, replacing integer keys with string descriptions taken from other tables. No table or view has more that 500 columns, but I'm suspicious that the building views on top of views is somehow producingan intermediate query that is somehow very wide. It's as if joins to another view don't just involve the columnsdefined by that view but instead involve all the columns in all the joins in that view. Intriguingly, it's only "select * from top_view" that produces this error. "select column_name from top_view" is fine. Does anyone have any ideas as to how I could avoid this error? I've hit the same problem on both Postgres 8.1 and 8.3. Thanks, David
David Waller <daw138a-postgres@yahoo.co.uk> writes:
> I'm struggling with a database query that under some circumstances returns the error "ERROR: number of columns
(2053)exceeds limit (1664)". Confusingly, though, no table is that wide.
This limit would be enforced against the output rows of any intermediate
join step. Without looking at EXPLAIN plans it's hard to say exactly
what's biting you, but it doesn't surprise me a a whole lot that joining
multiple 500-column tables would get you into trouble. I'd suggest
reconsidering your table schemas. Array columns might help.
regards, tom lane
Tom Lane wrote: > David Waller <daw138a-postgres@yahoo.co.uk> writes: > >> I'm struggling with a database query that under some circumstances returns the error "ERROR: number of columns (2053)exceeds limit (1664)". Confusingly, though, no table is that wide. >> > > This limit would be enforced against the output rows of any intermediate > join step. Without looking at EXPLAIN plans it's hard to say exactly > what's biting you, but it doesn't surprise me a a whole lot that joining > multiple 500-column tables would get you into trouble. I'd suggest > reconsidering your table schemas. Array columns might help. > > regards, tom lane > > I have a question may be relevant, if a little tangential, to this problem. When using views that perform joins, will the join order be enforced by the groupings created by the views, or does the planner decide the join order just as if there were no views and it all was written as a single query? In other words, if you have create view C select * from A join B on (A.foo = B.foo); create view D select * from C join E on (C.foo= E.foo); and you execute some select query on D, does it necessarily join A and B before joining the result to E, or might it decide to join B with E first before joining the result to A? Eric
Eric Schwarzenbach wrote: > In other words, if you have > > create view C select * from A join B on (A.foo = B.foo); > create view D select * from C join E on (C.foo= E.foo); > and you execute some select query on D, does it necessarily join A and B > before joining the result to E, or might it decide to join B with E > first before joining the result to A? It may switch join order, yes. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.