Thread: 7.1 bug fix question
One of the listed bug fixes is: Disallow INSERT/UPDATE/DELETE on views Can some one give me a little insight on what this implies? In particular, I currently (pg 7.0.3) have views with trigger functions on insert and update that trap the action and recast it to a table (after doing some checks). Will this still work with 7.1? Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory swampler@noao.edu
Steve Wampler <swampler@noao.edu> writes:
> One of the listed bug fixes is:
> Disallow INSERT/UPDATE/DELETE on views
> Can some one give me a little insight on what this
> implies?
It means that if you try to do INSERT/UPDATE/DELETE on a view that
doesn't have a rule to translate that action to something else, the
system will complain (rather than applying the action to the hidden
table underlying the view, as it used to do with confusing results).
> In particular, I currently (pg 7.0.3) have views with
> trigger functions on insert and update that trap the
> action and recast it to a table (after doing some checks).
> Will this still work with 7.1?
Yes. As long as you have appropriate rules, this change won't affect
you at all.
regards, tom lane
Steve Wampler <swampler@noao.edu> writes:
> Well, it looks like I got bit by this after all. Can someone show
> me a rule to attach to a view ("attributes") to invoke a trigger function
> (insert_or_update)?
> The trigger function maps the update into either an insert or an update
> on the underlying table ("attributes_table") depending on whether the row
> already exists or not.
> This was working under 7.0.3 (naturally, since insert/update/delete were
> allowed on views without explicit rules).
You had a trigger on update attached to a view? It should never have
fired, unless you were allowing tuples to be inserted into the view's
hidden table, which seems pretty wasteful.
But if that's what you want to do, you could emulate this pre-7.1
behavior by using a rule to redirect inserts/updates on the view to some
dummy table that you put triggers on. The dummy table would take the
place of the view's hidden table, which no longer exists in 7.1.
regards, tom lane
Tom Lane wrote:
>
> Steve Wampler <swampler@noao.edu> writes:
> > One of the listed bug fixes is:
> > Disallow INSERT/UPDATE/DELETE on views
> > Can some one give me a little insight on what this
> > implies?
>
> It means that if you try to do INSERT/UPDATE/DELETE on a view that
> doesn't have a rule to translate that action to something else, the
> system will complain (rather than applying the action to the hidden
> table underlying the view, as it used to do with confusing results).
>
> > In particular, I currently (pg 7.0.3) have views with
> > trigger functions on insert and update that trap the
> > action and recast it to a table (after doing some checks).
>
> > Will this still work with 7.1?
>
> Yes. As long as you have appropriate rules, this change won't affect
> you at all.
Well, it looks like I got bit by this after all. Can someone show
me a rule to attach to a view ("attributes") to invoke a trigger function
(insert_or_update)?
The trigger function maps the update into either an insert or an update
on the underlying table ("attributes_table") depending on whether the row
already exists or not.
This was working under 7.0.3 (naturally, since insert/update/delete were
allowed on views without explicit rules).
I guess I'm looking for a rule that restores the behavior from 7.0.3...
I didn't see anything in the programmers guide for 7.1 that helped me
understand how to get the trigger function to get called. (Probably
my lack of understanding of what an "action" can be.)
Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu
Tom Lane wrote:
>
> Steve Wampler <swampler@noao.edu> writes:
> > Well, it looks like I got bit by this after all. Can someone show
> > me a rule to attach to a view ("attributes") to invoke a trigger function
> > (insert_or_update)?
>
> > The trigger function maps the update into either an insert or an update
> > on the underlying table ("attributes_table") depending on whether the row
> > already exists or not.
>
> > This was working under 7.0.3 (naturally, since insert/update/delete were
> > allowed on views without explicit rules).
>
> You had a trigger on update attached to a view? It should never have
> fired, unless you were allowing tuples to be inserted into the view's
> hidden table, which seems pretty wasteful.
Never knew views had hidden tables. See below for what I wanted (and
had working [apparently by chance!] in 7.0.x).
I'm (obviously) a novice sqler so perhaps there's a better way to
provide the functionality I want:
I have a table ("attributes_table" with (say) 2 columns: name and value) where I
want to allow an "insert_or_update" action. That is, if an insert comes along
where the name duplicates that of an existing row's name field, then I want to map
the insert into an update into attributes_table. (The table is acting as a
labelled set.)
I asked the newsgroup about this a year or so ago and
was told to create a view ("attributes") and put a trigger function on the view to
do this mapping. Doing so allowed updates, inserts and queries to operate through
the view (I deliberately disallowed deletes through the view) while still providing
the labelled set behavior.
> But if that's what you want to do, you could emulate this pre-7.1
> behavior by using a rule to redirect inserts/updates on the view to some
> dummy table that you put triggers on. The dummy table would take the
> place of the view's hidden table, which no longer exists in 7.1.
Ok, I'll try that unless someone can suggest a better approach to providing
a "labelled set".
I'd prefer the users (all Java programs, in this case) to not have to
know about two database objects - before, all actions were handled through the
single view and the users never knew "attributes_table" was the real
storage for the attributes.
Hmmm, is there any way to put the triggers directly on "attributes_table" instead
of a dummy table? That would be cleaner [from my perspective], though it looks
to me that would cause a recursive plunge in the trigger function (which does
an insert/update internally after figuring out which to use...).
Thanks!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler@noao.edu
Steve Wampler <swampler@noao.edu> writes:
> I have a table ("attributes_table" with (say) 2 columns: name and
> value) where I want to allow an "insert_or_update" action. That is,
> if an insert comes along where the name duplicates that of an existing
> row's name field, then I want to map the insert into an update into
> attributes_table. (The table is acting as a labelled set.)
Assuming that you want *all* inserts into this table to act that way,
I'd suggest doing it with a trigger directly on that table. The trigger
would be BEFORE INSERT, and it would either allow the insert to happen
(by returning the proposed tuple) or do an UPDATE and prevent the insert
(by returning NULL). No circularity involved.
BTW, you should also have a unique index on the name column, just to
make sure that no one can put duplicate rows into the table (say, by
updating an existing row to have a different/conflicting name).
I'm not sure whether you need a view at all. Perhaps there are other
parts of your requirements that would justify a view, but not this one.
> (I deliberately disallowed deletes through the view)
If you want to disallow deletes to some users, you could do that with
GRANT/REVOKE commands. Again, that doesn't seem like a reason to have
a view.
regards, tom lane