Postgresql Materialized views

Jean-Michel Pouré wrote:

In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs

Unless you are going to *pay* for it - you do realize that the best way
to get it implemented, would be to open up the source code, and give it
a try yourself?

If it was so easy, and such a clear win, I think one of the very
competent people using PostgreSQL today would have already done it?

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

On 12/01/2008, Mark Mielke <mark@mark.mielke.cc> wrote:

Jean-Michel Pouré wrote:

In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs

Unless you are going to *pay* for it - you do realize that the best way
to get it implemented, would be to open up the source code, and give it
a try yourself?

In fairness to Jean-Michel, he has spent hundreds of hours in the past
doing just that and far more for the pgAdmin users in the community -
I'm sure we can excuse him for asking for what many do think would be
a useful feature in the hopes that someone listening might just decide
to pick it up.

In the meantime though - have you tried rolling your own materialised
views with some triggers Jean-Michel? I have good results doing that
in the past.

Regards, Dave

Dave Page wrote:

On 12/01/2008, Mark Mielke <mark@mark.mielke.cc> wrote:

Jean-Michel Pour� wrote:

In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs

Unless you are going to *pay* for it - you do realize that the best way
to get it implemented, would be to open up the source code, and give it
a try yourself?

In fairness to Jean-Michel, he has spent hundreds of hours in the past
doing just that and far more for the pgAdmin users in the community -
I'm sure we can excuse him for asking for what many do think would be
a useful feature in the hopes that someone listening might just decide
to pick it up.

In the meantime though - have you tried rolling your own materialised
views with some triggers Jean-Michel? I have good results doing that
in the past

I'm not good with names - I suppose Jean-Michel should be asking his
benefactors to return the favour then? :-)

In my own case - I use a combination of triggers and application to
maintain materialized views - but the subject does seem complex to me.

The last two uses of materialized views I used:

Counts, because as we all know, PostgreSQL count(*) is slow, and in any
case, my count(*) is not on the whole table, but on a subset. Doing this
in a general way seems complex to me as it would need to be able to
evaluate whether a given INSERT or UPDATE or one of the dependent tables
would impact the WHERE clause for the materialized view, and it still
wouldn't know which rows to add/update/remove without detailed analysis,
so it would either be throwing out the entire materialized view and
recreating it on INSERT or UPDATE (or deferring until the next query?)
in which case it may be very slow, or it may be very complex.

Another one that I use is a complex join of several tables, and merging
1:N tables including aggregate queries into a 1:1 materialized view. I
see this as the same problem where it needs to do dependency analysis,
and it still doesn't know how to INSERT/UPDATE/DELETE materialized rows
without complex analysis forcing a re-build. In my case, it is 1 ms to
query my materialized view and 1500 ms to rebuild the materialized view.
I do NOT want to rebuild this view after every update.

In summary, I don't think materialized views is an easy thing to do.
Perhaps the very simplest of cases - but the simplest of cases can be
easily managed with triggers or application logic.

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

Mark Mielke wrote:

Counts, because as we all know, PostgreSQL count(*) is slow, and in
any case, my count(*) is not on the whole table, but on a subset.
Doing this in a general way seems complex to me as it would need to be
able to evaluate whether a given INSERT or UPDATE or one of the
dependent tables would impact the WHERE clause for the materialized
view, and it still wouldn't know which rows to add/update/remove
without detailed analysis, so it would either be throwing out the
entire materialized view and recreating it on INSERT or UPDATE (or
deferring until the next query?) in which case it may be very slow, or
it may be very complex.

Bah. I forgot to add: The feature I've been wondering about (and not
necessarily looking for somebody else to do, although I don't think I
know the code well enough to do it at this point):

Web applications often make the same queries over and over. While
memcache can be used to cache results, the memcache interface is
different from the web application interfere requiring complex code, and
as well, one loses the transaction guarantees as the memcache results
are not guaranteed to be up-to-date with the database. I see the
greatest overall performance gain for web applications to be for
PostgreSQL to hang on to the results of the previous X queries along
with transactions numbers of each of the dependent tables as of the
snapshot of the table that is used, and if one of them matches, return
the results immediately. I believe MySQL does this (although not sure
how reliable their implementation is). I believe I have seen this
subject talked about on this list in the past. For web applications, I
believe this gives most of the benefits that materialized views would
provide, with less of the costs?

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, 12 Jan 2008 10:26:49 -0500
Mark Mielke <mark@mark.mielke.cc> wrote:

Jean-Michel Pouré wrote:

In my past development projects, I always used the concept of
Materialized VIEW to speed-up SELECTs over INSERTs

Unless you are going to *pay* for it - you do realize that the best
way to get it implemented, would be to open up the source code, and
give it a try yourself?

If it was so easy, and such a clear win, I think one of the very
competent people using PostgreSQL today would have already done it?

No actually, and your reply is offensive. There are a lot of things
PostgreSQL is missing that are "easy" and a clear win, yet people still
don't do them. A simple one is the ridiculous usage of pg_dump and
pg_dumpall. Or that we can't use pg_restore to use the plain text
backup.

I think his email was very well written and a simple request of
discussion of alternatives as well as future plans.

Sincerely,

Joshua D. Drake

Cheers,
mark

- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHiQ4LATb/zqfZUUQRAvEpAKCmLzjPg1+95ddWHBqogK4Ea981AgCfa4FG
2AVhx/5BNwYbCta086Iz1oo=
=ng7C
-----END PGP SIGNATURE-----

On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:

Please pick-up this important issue for developpers. There is no need to
concentrate on complex issues, when handling materialized views could
boost somme web apps. by a factor of 10 or more.

It's more complex than you think, but the main reason was that HOT was a
prerequisite for making summary tables work efficiently, which is only
now just about to go live into 8.3

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Joshua D. Drake wrote:

Unless you are going to *pay* for it - you do realize that the best
way to get it implemented, would be to open up the source code, and
give it a try yourself?

If it was so easy, and such a clear win, I think one of the very
competent people using PostgreSQL today would have already done it?

No actually, and your reply is offensive. There are a lot of things
PostgreSQL is missing that are "easy" and a clear win, yet people still
don't do them. A simple one is the ridiculous usage of pg_dump and
pg_dumpall. Or that we can't use pg_restore to use the plain text
backup.

I think his email was very well written and a simple request of
discussion of alternatives as well as future plans.

Offensive is relative. I find it offensive when people demand things on
one of the many mailing lists I read without providing anything to the
community.

I didn't realize the original poster did not fit this class of person.
For this, I apologize. As for tone - I don't see anything technically
wrong with my response. The best way to get something done *is* to pay
for it, or do it yourself. It's a tried and true practice in the open
source community. Also, I do not think it is as easy as you say - but
feel free to continue the discussion and prove how idiotic I am for
calling the problem "not easy". :-)

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

On Jan 12, 2008 5:31 PM, Mark Mielke <mark@mark.mielke.cc> wrote:

Joshua D. Drake wrote:

I think his email was very well written and a simple request of
discussion of alternatives as well as future plans.

Agreed, JD.

Offensive is relative. I find it offensive when people demand things on one
of the many mailing lists I read without providing anything to the
community.

I have to agree with JD. Your response was quite negative.
Similarly, your statement, "If it was so easy, and such a clear win, I
think one of the very competent people using PostgreSQL today would
have already done it?" is misplaced. Jean-Michel didn't say it was
easy at all, he just explained some of the benefits.

Having used materialized views for years, I can tell you they are an
important feature Postgres currently lacks (in native form). Also, as
I have personally looked into implementing materialized views in
Postgres, I can tell you it's not too difficult. However, as Simon
stated, it's an iterative process.

You don't need to jump on someone for a well-written request with a
specific use-case.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

On Sat, 12 Jan 2008, Jean-Michel Pour� wrote:

You are well aware of Jonathan Gardner preliminary work:
http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html

There's also PostgreSQL::Snapshots ; intro at
http://cunha17.cristianoduarte.pro.br/postgresql/snapshots.en_us.php and
main project page at http://pgfoundry.org/projects/snapshot/

I run a 400.000+ message board using PhpBB 3.0...My database handles 10
to 20 queries every second. There are 100 selects for 1 INSERT. But my
database could well handle over 500 queries a second using materialized
views.

I hope you don't take this the wrong way, but if you can't hit 500
queries/second on that volume of messages I would guess that something is
wrong with either the design scalability of the PhpBB software running
against a PostgreSQL database or some detail of how you've got it setup.
A quick read suggests it's not unusual for people to drop PhpBB and use
something vBulletin instead exactly because of PhpBB's issues handling
larger communities. It's probably out of date but I found the discussion
of query optimization for larger message boards at
http://www.phpbb.com/community/viewtopic.php?t=135383 to be informative on
this topic.

At my level, here are my plans:
1) Publish some long query LOGs from my database, longuer than 30
milliseconds.

Rather than chasing after core product features that are some distance
off, I think what might be more productive for you in the short term is to
collect this information--including EXPLAIN ANALYZE plans--and include it
along with information about your server and how the postgresql.conf is
setup in a message to the performance list. That might get you immediate
suggestions. In addition to "query is badly written/indexed for
PostgreSQL" (which is another potential side to the query issues discussed
in the phpbb forum topic I referenced), problems you might not have caught
that could be nailing you include things like not allocating enough memory
for use by the database and tables not being analyzed frequently enough.

I would love to have materialized views in the core database. But it's a
ways off no matter what, is moderately hard to accomplish, and you can
emulate some of the benefits using things like Gardner's trigger-based
approach. All of that makes it harder to kick off such a project. I
don't think you need to convince anyone that it's important--the
occasional person has been screaming about needing this feature for years
now. The real question is who cares enough about the feature that it's
worth their trouble to fund development, and I'm not sure whether your
personal attempts to rouse demand will impact that.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

In fairness to Jean-Michel, he has spent hundreds of hours in the past
doing just that and far more for the pgAdmin users in the community -
I'm sure we can excuse him for asking for what many do think would be
a useful feature in the hopes that someone listening might just decide
to pick it up.

In the meantime though - have you tried rolling your own materialised
views with some triggers Jean-Michel? I have good results doing that
in the past.

Dear friends,

For of all, thanks for picking up my message and replying.

I agree with Dave Page and others that it is hard to find contributors
for a Free project. When writing 2% of pgAdmin2 code, I noticed that you
could count 10.000 end-users for ONE developper.

This is why I usually offer a bounty for any kind of feature.

Two months ago, my last bounty for a Kdenlive feature (100€), but no-one
aggreed to receive the money after developing the feature, probably
because there were dozens of contibutors (people writing code on top of
someone else code).

But the power of PostgreSQL is to be a real community, like Xorg or
Apache. This makes all the difference. This was the heart of my message.

This being said, our behind the scene needs are:
* We are a non-profit organisation based in France, providing
real-estate listing services.
* We are going to spend 2000€ buying two servers (Phantom 4-die + 8GB 2U
servers), running on Debian: one for PostgreSQL, the other for Apache2.
* We plan to launch the first virtual real-estate agency managed by a
free community.
* We are going to use a mixture of PhpBB 3.0, Drupal and a custom
applications.
* We plan to handle thousands of simultaneous users.

So ...

Based on my current logs, I know that I may have to buy and run a web
farm. Which I do not want, because it implies extra hosting costs.

AND

I know that ONE PostgreSQL database can handle 1000 simultaneous
queries, when using server-side code.

I wrote a small PostgreSQL query optimization HOWTO :
http://area51.phpbb.com/phpBB/viewtopic.php?f=3&amp;t=29292
Pardon me if some information is not accurate.

I tried my best to educate PhpBB developers choosing the right technology.
PostgreSQL... Now, I am going to write the requested server-side code for PhpBB 3.0
Then I will benchmark queries on our large server.

In the end I hope that someone will be willing to pick-up this
MATERIALIZED VIEW issue, so that any applications running
on PostgreSQL can benefit from "lightning speed".

If someone is willing to receive a bounty, please contact me.

Being a non-profit organisation, I can ask money to contributors on our web site.
This will not be any kind of large sum of money, something between 100€ and 500€.

Kind regards and happy new year.
Jean-Michel Pouré

Mark Mielke wrote:

Joshua D. Drake wrote:

Unless you are going to *pay* for it - you do realize that the best
way to get it implemented, would be to open up the source code, and
give it a try yourself?

Because users possibly want to do that - use it? Some of us have better
things to do than go
through the learning curve of how the internals of a non-trivial system
work. Does that really
mean its unreasonable to voice an opinion of what would make the system
more useful?

Offensive is relative. I find it offensive when people demand things
on one of the many mailing lists I read without providing anything to
the community.

If your view of the community is that it should be insular and closed to
those who can't or won't be developers, then fine. But taking that
attitude will pretty much guarantee that your system will never amount
to more than a hill of beans.

One of the major problems with open source as a whole is that you get
this 'fix it yourself or pay for it' business which provides no way to
spread the cost over many users who would all have something to gain -
but none of whom can justify footing the bill for the entire
development. Most of us are in that position as users, even if we do
have skills that would enable us to help - we have our own issues to
deal with. Buying support isn't the answer - its not support that's
needed, after all, so much as an ability to buy a share of influence
over a roadmap..

Do you want ensure that only the very rich in cash or time can have any
influence? You're going the right way about it with your attitude,
which appears deliberately user-hostile.

What do you want?

James

FYI: I don't like being attacked for stating the truth, nor distracting
the mailing list with these emotional discussions. However, there are
things that need to be clarified. Feel free to kill the thread in your
mail browser.

James Mansion wrote:

Mark Mielke wrote:

Joshua D. Drake wrote:

Unless you are going to *pay* for it - you do realize that the best
way to get it implemented, would be to open up the source code, and
give it a try yourself?

Because users possibly want to do that - use it? Some of us have
better things to do than go
through the learning curve of how the internals of a non-trivial
system work. Does that really
mean its unreasonable to voice an opinion of what would make the
system more useful?

It's unreasonable to demand or set a time schedule like "drop everything
and work on materialized views in 2008 because you are wasting your time
on other things - web developers can get 10X improvement today if you do
this one thing". It's not unreasonable to ask for support for an idea.
As it was, I believe I misinterpreted Jean-Michel's message as a demand
(the words seemed demanding), whereas it may have been a plea. I was far
more sympathetic to his second post.

Offensive is relative. I find it offensive when people demand things
on one of the many mailing lists I read without providing anything to
the community.

If your view of the community is that it should be insular and closed
to those who can't or won't be developers, then fine. But taking that
attitude will pretty much guarantee that your system will never amount
to more than a hill of beans.

First point: I don't speak for the developers and it is not my system.
Second point: This has nothing to do with insular vs non-insular
community. Everything I stated was true - you may not like what I
stated, but it was true. It's possible I could have adjusted a word or
two, or added a smiley to get an affect you would find more pleasing,
but there was nothing incorrect about what I stated. The best way to get
a feature in is to pay for somebody to do it, or do it yourself. If you
need reference points look to the last 20 large features that went into
PostgreSQL. See how it progresses. Also, people have been working on
materialized views on and off for years with differing degrees of
success. It is *NOT* the simple problem that people make it out to be.
(Although it seems that some people may have a good grasp of the
problem, and may be verging on a solution)

One of the major problems with open source as a whole is that you get
this 'fix it yourself or pay for it' business which provides no way to
spread the cost over many users who would all have something to gain -
but none of whom can justify footing the bill for the entire
development. Most of us are in that position as users, even if we do
have skills that would enable us to help - we have our own issues to
deal with. Buying support isn't the answer - its not support that's
needed, after all, so much as an ability to buy a share of influence
over a roadmap..

Open source is a double-edged sword for sure. This does not give any
right to demand anything. If you want to work on something *with* other
people, the message is "I am looking at doing tihs, but don't know how
to start - does anybody with similar interest want to help?" This is
*not* the message that was presented.

Do you want ensure that only the very rich in cash or time can have
any influence? You're going the right way about it with your
attitude, which appears deliberately user-hostile.
What do you want?

I want people to respect the few developers we have, because I value
them and respect them.

As for my "attitude" - words in email are hardly capable of showing
emotion, especially something as short as I wrote. You will think what
you wish of my words, and your decision was already made the moment you
ignored the fact that I spoke truth and focused only on the tone.

I have an apology for Jean-Michel. I don't have an apology for people
such as you who took this opportunity to attack me. Pot, kettle, black.

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

James Mansion wrote:

If your view of the community is that it should be insular and closed to
those who can't or won't be developers, then fine. But taking that
attitude will pretty much guarantee that your system will never amount to
more than a hill of beans.

Keep in mind that Mark Mielke is not a PostgreSQL developer.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Jan 12, 2008 4:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:

Please pick-up this important issue for developpers. There is no need to
concentrate on complex issues, when handling materialized views could
boost somme web apps. by a factor of 10 or more.

It's more complex than you think, but the main reason was that HOT was a
prerequisite for making summary tables work efficiently, which is only
now just about to go live into 8.3

+1

If you know how to write triggers, materialization techniques aren't
all that difficult. The real technical limitation was not lack of
materialization techniques (write triggers), but was dealing with the
mvcc penalty. Previously to HOT, for summary tables I would redirect
the trigger to insert to a 'roll up' table and move the data to the
summary on cron or from an application event.

Materialized views are syntax sugar (but still very sweet).

merlin

<sarcasm>Good to see</sarcasm> things haven't changed, and requests for
features and improvements on the pgsql-hackers list can still degenerate
rapidly into a discussion about how to request features and improvements.

As Joshua Drake has pointed out before, most of the core people working on
PostgreSQL don't actually use it for anything themselves. I will expand a
little on that and say that this means that while they are extremely good at
what they do, they really don't have a clue what might be useful to someone
"in the wild". Sort of like automotive engineers who in the 1970's made the
Cadillac's engine so large that you couldn't change the spark plugs without
taking the motor mounts loose and lifting the engine.

The basic question this brings up in the context of this latest flurry of
hurt feelings is whether user demand is officially a driving force in
PostgreSQL development.
If the answer to that question is yes, then the next question is how is that
structured?

I'm not sure that the collective answer to the first question is actually
yes. If it actually is yes, then the next question has barely been touched,
as witnessed by these flurries of electrons on the list, unless the
structure is Anarchy. Which is oxymoronic of course.

There doesn't appear to be an easy way to officially "take the temperature"
of either the developer community, or the user community, and there
certainly is no official way to clearly and easily communicate between the
two in order to effect change. Unfortunately for all of us, the
communications, social, organizational, and people skills/talents necessary
to envision and create the type of social structure that benefits the entire
community are outside the range of experience of everyone on this list. How
do I know that? Because if even one person had those talents/skills it would
have happened already. That is what those type of people do, they can't help
themselves.* I'm not sure that anyone reading this would even be able to
recognize such a person if they met them. Perhaps we should go fishing for
some help from one of those "University Places"? From people outside the
Computer Science department? Maybe even some people in Industrial
Psychology? Somebody probably needs a Master's project....

--
Sean Utt

* Actually I'm being optimistic, while organizers are compulsive, they know
a hopeless cause when they see it, and quietly disappear. I'm operating
under the assumption that the PostgreSQL community is not a hopeless cause
organizationally.

[entire original message deleted for lack of usefulness]

Sean Utt wrote:

<sarcasm>Good to see</sarcasm> things haven't changed, and requests for
features and improvements on the pgsql-hackers list can still degenerate
rapidly into a discussion about how to request features and improvements.

As Joshua Drake has pointed out before, most of the core people working
on PostgreSQL don't actually use it for anything themselves.

<snip>

Unfortunately for all of us,
the communications, social, organizational, and people skills/talents
necessary to envision and create the type of social structure that
benefits the entire community are outside the range of experience of
everyone on this list. How do I know that? Because if even one person
had those talents/skills it would have happened already. That is what
those type of people do, they can't help themselves.* I'm not sure that
anyone reading this would even be able to recognize such a person if
they met them.

I would argue that they know and recognize them but... they don't care.
They are -hackers. They care about code. Some of them in a tertiary
manner care about the entire paragraph above, others not at all.

I think if you look around what you will see is disparate group of
people all organizing the community in their own particular way for the
benefit of the community as a whole.

Consider the following, in the last 2 years we have had a number of
significant community events happen that allow for exactly what you are
stating above. Formation of the Fundraising group and affiliation with
Software in the Public Interest. This allowed us to execute the
Anniversary, PostgreSQL Conference West, as well as the upcoming EAST
conference. It also allowed to to provide help to PgDay.IT for last
years Italian PostgreSQL day. Since that time they have also formed
ITPUG and Italian non-profit for PostgreSQL. Lastly this affiliation has
allowed allowed the community as a whole to sponsor travel for
PostgreSQL speakers to various conferences and support major events such
as OSCON and LinuxWorld.

Perhaps we should go fishing for some help from one of
those "University Places"? From people outside the Computer Science
department? Maybe even some people in Industrial Psychology? Somebody
probably needs a Master's project....

Actually you just need to not be on -hackers :). This belongs over in
-advocacy.

Sincerely,

Joshua D. Drake

Just my two cents on this (rapidly degenerating) thread.

On 1/13/08, Sean Utt <sean@strateja.com> wrote:

<sarcasm>Good to see</sarcasm> things haven't changed, and requests for
features and improvements on the pgsql-hackers list can still degenerate
rapidly into a discussion about how to request features and improvements.

As Joshua Drake has pointed out before, most of the core people working on
PostgreSQL don't actually use it for anything themselves. I will expand a
little on that and say that this means that while they are extremely good at
what they do, they really don't have a clue what might be useful to someone
"in the wild". Sort of like automotive engineers who in the 1970's made the
Cadillac's engine so large that you couldn't change the spark plugs without
taking the motor mounts loose and lifting the engine.

As a very satisfied Postgres "customer", I take exception to the comparison.

<SNIP>

There doesn't appear to be an easy way to officially "take the temperature"
of either the developer community, or the user community, and there
certainly is no official way to clearly and easily communicate between the
two in order to effect change.

Huh? A politely worded feature request generally gets discussed and
then put on the TODO list if there is some consensus about its
usefulness. If there is no consensus, then the requester usually has
to do more work, which might involve prototyping some code etc.
Admittedly, some developers get grumpy sometimes, but, as the man
said, "Let him who is without sin throw the first stone..." There was
an issue with the tone of the request for material views in the
beginning of this thread, but that seems to ironed out among those who
are actually interested in accomplishing something.

May I propose the following: (1) can we put materialized views on the
TODO, perhaps as a library or as core, still subject to a lot of
design work and with no particular deadline? (2) Can we discontinue
this particular flame war about the responsive of the developers (who
in my estimation do a huge amount of work that benefits me immensely
with nary a "thank you")?

Sorry for the meta rant, I just couldn't take it anymore.

"Webb Sprague" <webb.sprague@gmail.com> writes:

May I propose the following: (1) can we put materialized views on the
TODO, perhaps as a library or as core, still subject to a lot of
design work and with no particular deadline?

Actually, I had thought they *were* on the TODO list, because certainly
it's been suggested before. I can't find anything about 'em in the
list though, so +1 for adding the entry. There are plenty of other TODO
items that we have no idea how to do, so the lack of a clear design is
hardly an objection ;-)

regards, tom lane

Sean Utt wrote:

As Joshua Drake has pointed out before, most of the core people
working on PostgreSQL don't actually use it for anything themselves. I
will expand a little on that and say that this means that while they
are extremely good at what they do, they really don't have a clue what
might be useful to someone "in the wild". Sort of like automotive
engineers who in the 1970's made the Cadillac's engine so large that
you couldn't change the spark plugs without taking the motor mounts
loose and lifting the engine.

This is both gratuitously offensive and based on a demonstrably false
premise. The definition of "core people working on PostgreSQL" is
somewhat vague. But if you were to take it as, say, the group of active
committers, then I would say that the majority of us earn our living in
whole or in part using PostgreSQL. Certainly I do (there's a reason I
use an elephant logo for my business).

Many of the things I have contributed to PostgreSQL have been in
response to user requests, and often things I personally have no
immediate need for. The same is true of many contributors. And almost
all of mine have been aimed at increasing usability (e.g. dollar
quoting, CSV import/export). So I rather take offense at the quite
inappropriate spark plug analogy.

As for feature requests, everyone has a right to air an opinion.
Personally, I will pay more attention to people who contribute to the
community than to those who don't. That doesn't just mean coders, though
- contribution comes in many forms. In the case of the present request,
Jean-Michel is a contributor, and any suggestion to the contrary is
right out of line.

The real question for me is whether we want to support Materialized
Views at the grammar level. If we do then it should be worked on. If not
then it can probably be dealt with via a bolt-on module. Personally I'm
inclined to say we should support it via the grammar.

cheers

andrew