"Number of columns exceed limit" on a hierarchy of views

From: David Waller <daw138a-postgres(at)yahoo(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: "Number of columns exceed limit" on a hierarchy of views
Date: 2009-08-21 12:16:50
Message-ID: 799417.79283.qm@web86502.mail.ird.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

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.

The problem seems to be my use of views. The largest table in the database is 500 columns wide. However there are a 3 views layered 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 producing an intermediate query that is somehow very wide. It's as if joins to another view don't just involve the columns defined 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Sergio Borgonovo 2009-08-21 12:23:38 Re: Temp table or normal table for performance?
Previous Message John DeSoi 2009-08-21 12:16:48 Re: join from array or cursor