Обсуждение: 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