Re: PostgreSQL Gotchas

Nikolay Samokhvalov wrote:

>On 08/10/05, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
>
>>On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
>>
>>
>>>http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
>>>
>>>Perhaps I'm wrong with some issues - any comments are welcome.
>>>
>>>
>>The only thing I can comment on is updatable views. You can make
>>updatable views using RULEs. The only thing is that they're not
>>*automatically* updateable.
>>
>>
>
>OK, I'll make this correction. But for me, updatable views are views
>for which DBMS supports insert/update/delete operations as for
>tables. Ideally, people shouldn't distinguish table and view - that's
>what theory stands for (see Date's thoutghs about it:
>http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
>on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm)
>
>
Who do you mean by "people?" Users? DBA's?

Now, obviously the DBA will always be able to distinguish between them.
Otherwise he/she wouldn't be much of a DBA would he/she?

>PostgreSQL doesn't support updates even for simple views such as
>select-with-restriction. What it does support - not updatable views,
>but some kind of INSTEAD OFF triggers (another form of).
>
>
Ok... Your complaint is that PostgreSQL doesn't support automatically
generating insert/update/delete rules for views. Ultimately, however,
this may not be done simply because there are other priorities which
people are devoting their time and energy to instead. I know some
people have put in some work on this item but I don't know what the
status is.

Also, what SQL Server 2003 calles a trigger, we call a rule. What we
call a trigger is different and I am not sure SQL Server 2003 has such
an equivalent.

>
>
>>In theory, if someone came up will a program that from given <view
>>definition> produced the appropriate INSERT, UPDATE and DELETE rules,
>>it might be incorporated. Currently you just have to do it manually...
>>
>>
>
>There is a good theory (Date), but it cannot be implemented for any
>practical DBMS. The cause lies in differences between theory and
>practice. And the major difference is possibility to define tables w/o
>PK (in other words, possible duplicate rows). Nevertheless, all major
>commercial RDMSs support some subset of views that can be updated..
>SQL:2003 defines a quite large subset, but the definition is pretty
>mazy...
>
>

Again, this is likely doable. There are hidden fields that I suppose
could be extended in a view to reference unique rows (maybe ctid since
the whole thing is expanded in a single SQL statement, but I haven't
tried it).

Best Wishes,
Chris Travers
Metatron Technology Consulting