Re: Moving to postgresql and some ignorant questions

On 8/14/07, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:

> 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).

Then you need to define a lookup table, and have both of your tables
reference it by foreign key. You can create an update trigger on one
of the child tables to put a row into the lookup table if it doesn't
exist. If I'm not clear, let me know.

> 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?

In pgsql, views are actually empty tables that are defined by a SQL
statement and fired by rules when you select from the view. I.e.
create view abc as select * from xyz becomes an empty table abc which
has a rule for selects that runs select * from xyz when you access it.
The performance of select * from abc will be almost exactly the same
as select * from xyz, except for some very small overhead from the
rules engine.

The real uses for views are to allow you to reduce query complexity in
the client. Suppose you have a query that joins and / or unions a
dozen tables with really complex join logic. you can just wrap it in
a view, and when you select from the view, postgresql will execute the
real query behind it as though you passed it in.

> 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.

I think you're talking about updatable views, which you can build with
postgresql. Rules can let you do this pretty easily.

> I just wanted to know what the usual thoughts on this are. Are views
> updated as soon as its underlying table(s) are updated?

Yep, because views are just enclosed queries.

Note that you CAN do materialized views with pgsql. Once you've
gotten familiar with regular postgresql stuff, look up materialized
views for postgresql again on google. they're not that hard really,
but most the time you really don't need them.

> 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!

nope, but no great loss either.