Thread: updating a view

hi,

i would like to allow updates to a view, but pgsql crashes,
presummably from an infinite loop.

i add a rule like so:

   CREATE RULE update_myview AS ON UPDATE TO myview DO INSTEAD SELECT updater(OLD, NEW);

where updater is a plperl function.  the function updater does a few
comparisons off OLD and NEW (to see what has changed) and then, if
necessary, updates one of the tables that makes up myview:

   spi_exec_query("UPDATE mytable SET somefield = somevalue WHERE somecondition");

i can supply the exact code if you wish, but i think the problem lies
more with my approach than the syntax.  what i think is happening is
that the "update mytable set blah blah" statement is executed, but
that causes an update to myview, which causes my rule to run, which
runs updater etc etc.

i'm not sure how to get around this problem though.  how can i do a
UPDATE myview SET foo = bar, and get that to work without using a
rule, that ends up updating a table that updates myview?

thank you for any help!

matt

m.c.wilkins@massey.ac.nz writes:
> i would like to allow updates to a view, but pgsql crashes,
> presummably from an infinite loop.

If it crashes, that's a bug, regardless of whether the code is doing
what you want.  However, if the thing is indeed into an infinite
recursion, you might be running out of stack space, in which case the
bug is an incorrect setting of max_stack_depth.  (PG 8.2 should be able
to set max_stack_depth for itself on most platforms, but older releases
rely on you to get it right.)  If you reduce max_stack_depth, do you get
an error message instead of a crash?  What are the exact crash symptoms,
anyway?  And which PG version is this?

            regards, tom lane

hi tom,

so maybe i'm using the word crash too liberally.  this is the error
message i get:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:  terminating connection because of crash of another
serverprocess 
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because
anotherserver process exited abnormally and possibly corrupted shared memory. 
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
Failed.
!>
!>

a ps auwx | grep sql shows that postgres is running though, so maybe
not a complete crash?

version is 8.2.0, just downloaded a few days ago.  the max_stack_depth
is 2MB.

matt


On Thu, Dec 14, 2006 at 08:41:28PM -0500, Tom Lane wrote:
> m.c.wilkins@massey.ac.nz writes:
> > i would like to allow updates to a view, but pgsql crashes,
> > presummably from an infinite loop.
>
> If it crashes, that's a bug, regardless of whether the code is doing
> what you want.  However, if the thing is indeed into an infinite
> recursion, you might be running out of stack space, in which case the
> bug is an incorrect setting of max_stack_depth.  (PG 8.2 should be able
> to set max_stack_depth for itself on most platforms, but older releases
> rely on you to get it right.)  If you reduce max_stack_depth, do you get
> an error message instead of a crash?  What are the exact crash symptoms,
> anyway?  And which PG version is this?
>
>             regards, tom lane

m.c.wilkins@massey.ac.nz writes:
> so maybe i'm using the word crash too liberally.  this is the error
> message i get:

> server closed the connection unexpectedly

Looks like a crash to me.

> version is 8.2.0, just downloaded a few days ago.

There's a known bug in 8.2.0 having to do with failing out of a
subtransaction, which would include a plperlu spi_exec() to a query
that gets an error.  So I'm thinking that might be what you're hitting.
If you built from source, you could apply this patch:
http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php

            regards, tom lane

ya'da man tom!

applied the patch, rebuilt, and postgres doesn't crash anymore.  fixed
my syntax error, and all is working properly.

phew, my first experience with sql....

thanks!

matt

On Thu, Dec 14, 2006 at 09:23:40PM -0500, Tom Lane wrote:
> m.c.wilkins@massey.ac.nz writes:
> > so maybe i'm using the word crash too liberally.  this is the error
> > message i get:
>
> > server closed the connection unexpectedly
>
> Looks like a crash to me.
>
> > version is 8.2.0, just downloaded a few days ago.
>
> There's a known bug in 8.2.0 having to do with failing out of a
> subtransaction, which would include a plperlu spi_exec() to a query
> that gets an error.  So I'm thinking that might be what you're hitting.
> If you built from source, you could apply this patch:
> http://archives.postgresql.org/pgsql-committers/2006-12/msg00063.php
>
>             regards, tom lane