Re: Moving to postgresql and some ignorant questions - Mailing list pgsql-general

On 8/14/07, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

> > *And* you can define compound foreign key constraints,

> Thank you for this detailed explanation Alban. But I want to include
> FK constraints on a table2 on a column in the referenced table1 where
> column values are not unique.
>
> I just want row data to be consistent for the same ID. Yes, this is
> repetitive and enough to rile DB purists, but it has its uses (for
> performance in certain reporting queries).

I'm not sure I understand this.  If the set of values you want to
reference is not unique, what are the semantics of this reference?
What should happen if one of those sets gets deleted, for instance?

Perhaps you mean table1 has columns "id" and "col2", and the "col2"
values are not unique, but "id" and "col2" together are?  In that case
you can simply put a UNIQUE constraint on them together, and use both
columns in a single foreign key constraint from table2.

> 1. Should I explore views for this? I am very skeptical about them
> coming from MySQL as the performance of MySQL views is horrendous.
> Besides, if they are updated everytime, there's little use for a view
> in the first place, I may as well simply query the table -- or is this
> wrong? The UPDATE only locks and commits to the table, and then the
> view gets auto updated?

A view is simply an alternate presentation of data in one or more
tables.  It's a persistent query, and the performance is effectively
the same as the query itself.  (More on "materialized views" below.)

In regard to locking, it sounds like you're thinking in MYISAM terms;
PostgreSQL uses MVCC instead of locking.  Two UPDATEs attempted at the
same time may cause one to wait for the other (if both touch the same
rows), but during this time all readers (e.g. SELECT) will continue to
run without waiting.  If you were running into concurrent performance
issues with MYISAM due to locking, PostgreSQL might surprise you.

> 2. Or, I could do this with triggers, and now I realize also with
> "rules" (CREATE RULE). Which are faster, rules or triggers, are they
> similar in speed? Basically I want the rule/trigger to cascade the
> update to table1.col1 and table1.col2 to similar columns in table2.

They are simply different mechanisms.  Rules rewrite a query during
the parsing phase, and then run the eventual query tree as if you'd
entered it yourself.  Triggers are procedural actions in response to
events.  Normally you pick one based on the semantics of what you want
to do.

If you can use foreign keys as above, ON UPDATE CASCADE will probably
do what you want without having to do anything else.

> I will surely be exploring views, and reading more of this:
> http://www.postgresql.org/docs/8.2/interactive/rules-views.html , but
> I just wanted to know what the usual thoughts on this are. Are views
> updated as soon as its underlying table(s) are updated? Can I control
> the duration or timing of their update? I searched for "materialized
> views", after having seen that word on the performance list, but most
> of the search results and the discussions on that forum are beyond my
> comprehension!!

Yeah, you're confusing standard views with "materialized views".  A
standard view is basically a persistent query, and does not store any
data itself.  PostgreSQL implements them with rules.  When you SELECT
against a view, the query rewriter combines your conditions with the
stored SELECT used to create the view, and runs the final query
against the table(s) you created the view from, just as if you'd
entered it yourself.  If the original query against the table(s) is
complex, a view makes a very nice way to simplify things for
applications.

If you want to create an "updateable view", where applications can
INSERT/UPDATE/DELETE against the view itself, you need to add rules to
it to translate operations on the presented view columns into
operations on the table(s) behind them.

A "materialized view" is basically a view that stores copies of its
data.  This is useful in situations where you need some kind of cache,
possibly because the query behind the view takes a long time to
complete.  PostgreSQL does not have built in support for materialized
views, but rules and triggers can be used to create them.  The
discussions you encountered revolve around the finer points of doing
that.

> Would appreciate any thoughts on performance of views. PGSQL seems to
> treat views just like tables, so I wonder if there's any performance
> gain!

In general, a view is performance neutral: it's just a mechanism for
simplifying presentation of data.

It also has uses for security, since you can create a view and grant
roles access to it while still denying them access to the underlying
tables.  This could be used to hide a particular table column, for
instance.  It doesn't sound like this is of any use for your
application though.