Thread: how to create materialized view in postgresql 8.3
is it possible to created materialized view in postgresql 8.3 if yes please provide some sample.
On 08/04/13 18:58, Zahid Quadri wrote:
8.3 is no longer supportedp { margin: 0; }
is it possible to created materialized view in postgresql 8.3 if yes please provide some sample.
On Mon, Apr 8, 2013 at 4:57 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
-- 8.3 is no longer supportedOn 08/04/13 18:58, Zahid Quadri wrote:
is it possible to created materialized view in postgresql 8.3 if yes please provide some sample.
Zahid, I think that you have mistaken 8.3 and 9.3. Materialized views have just been implemented and will be available in postgres 9.3 whose release is planned this year.
For your example. documentation is your friend:
http://www.postgresql.org/docs/devel/static/sql-creatematerializedview.html
For your example. documentation is your friend:
http://www.postgresql.org/docs/devel/static/sql-creatematerializedview.html
Michael
On 4/7/2013 11:58 PM, Zahid Quadri wrote:
p { margin: 0; }
is it possible to created materialized view in postgresql 8.3 if yes please provide some sample.
in older versions, the best you could do was to create a table and populate it with your 'view', then drop it when you're done (or truncate and repopulate it to update the 'view').
-- john r pierce 37N 122W somewhere on the middle of the left coast
This is the number one requested feature on Uservoice:
On Mon, Apr 8, 2013 at 9:27 AM, John R Pierce <pierce@hogranch.com> wrote:
On 4/7/2013 11:58 PM, Zahid Quadri wrote:
is it possible to created materialized view in postgresql 8.3 if yes please provide some sample.
in older versions, the best you could do was to create a table and populate it with your 'view', then drop it when you're done (or truncate and repopulate it to update the 'view').-- john r pierce 37N 122W somewhere on the middle of the left coast
Le lundi 08 avril 2013 à 09:36 -0700, Mike Christensen a écrit : > This is the number one requested feature on Uservoice: > > > http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views > > I find this rather surprising, considering the fact that a properly tuned database will return queries over millions of rows and multiple joins in milliseconds, given the proper hardware. I can see how a datawharehouse with terrabytes of data can make use of them, but that is hardly a common situation. It seems to me many of these people clamouring for materialized views would be better off simply using a proprer data structure (and taking a few SQL courses). Am I misguided? -- Salutations, Vincent Veyron http://gdlc.fr/logiciels Applications de gestion des sinistres assurance et des contentieux juridiques
On Wed, Apr 10, 2013 at 10:33 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
-- I find this rather surprising, considering the fact that a properly
tuned database will return queries over millions of rows and multiple
joins in milliseconds, given the proper hardware.
I can see how a datawharehouse with terrabytes of data can make use of
them, but that is hardly a common situation. It seems to me many of
these people clamouring for materialized views would be better off
simply using a proprer data structure (and taking a few SQL courses).
Am I misguided?
A use case of materialized views is cache for web application where you could refresh them with complicated join queries running in background. You cannot do that with a view as it would be necessary to reprocess the query each time, and it is difficult to do that with only tables as this could incredibly complicate your database schema.
Michael
On 10/04/13 23:33, Vincent Veyron wrote: > Le lundi 08 avril 2013 à 09:36 -0700, Mike Christensen a écrit : >> This is the number one requested feature on Uservoice: >> >> >> http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views >> >> > > I find this rather surprising, considering the fact that a properly > tuned database will return queries over millions of rows and multiple > joins in milliseconds, given the proper hardware. > > I can see how a datawharehouse with terrabytes of data can make use of > them, but that is hardly a common situation. It seems to me many of > these people clamouring for materialized views would be better off > simply using a proprer data structure (and taking a few SQL courses). > > Am I misguided? > Theres database and application systems (literally everywhere on the web IMO) where people think that throwing extra hardware at a problem will solve what proper caching solutions would achieve with no upgrades at all. IMO, for most things "web", data is retrieved more than it is set or modified. MV's will always perform better caching a query result, than a query (VIEW) and MV's and tablespaces seem to be made for each other. As for proper data structures, for whatever reason (migrating, upgrading, maintaining) really bad query code exists (including mine). Jules.