On 8/14/07, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
