[BUGS] BUG: PLPGSQL function causes PgSQL process to die when inserting into a view
Hi all; I have two tables which have different security considerations wrapped into a view. Inserting into the view is done using a simple rule which inserts into both tables (tables have a 1:1 relationship and reflect the user-defined and admin-defined portions of a user profile). From psql, inserts into the table work as expected. However from a PLPGSQL function, any attempt to insert into the table causes the following error: FATAL: SPI: improper call to spi_dest_setup The query is a simple insert and the rule merely splits it into two inserts. The workaround is simply to insert into both tables in the view independently within the plpgsql function. But this seems like odd behavior that should not be occurring. Best Wishes, Chris Travers _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG: PLPGSQL function causes PgSQL process to die when inserting into
Sorry, forgot the version information: 7.3.2
Full schema and example:
CREATE TABLE owners_admin (
owner_id integer DEFAULT nextval('"owners_owner_id_seq"'::text) NOT
NULL,
first_name character varying(15) DEFAULT '',
last_name character varying(15) DEFAULT '',
login character varying(12) NOT NULL,
title text DEFAULT '',
email character varying(35) DEFAULT '',
time_added timestamp with time zone DEFAULT ('now'::text)::timestamp(6)
with time zone,
admin boolean DEFAULT false,
disabled integer DEFAULT '0',
manager integer
);
CREATE TABLE owners_user (
owner_id integer,
expertise text,
home_form character varying(20),
form_count integer,
cal_min_hr smallint,
cal_max_hr smallint,
query_max integer
);
CREATE VIEW owners
AS
SELECT a.owner_id, a.first_name, a.last_name, a.login, a.title,
u.expertise,
a.email, a.time_added, u.home_form, u.form_count, u.cal_min_hr,
u.cal_max_hr, a.admin, a.disabled, u.query_max, a.manager
FROM owners_user u, owners_admin a
WHERE a.owner_id = u.owner_id;
CREATE RULE view_insert AS ON INSERT TO owners
DO INSTEAD
(
INSERT INTO owners_admin
(first_name, last_name, login, title, email, time_added, admin,
disabled)
VALUES
(COALESCE(new.first_name, ''), COALESCE(new.last_name, ''),
new.login, COALESCE(new.title, ''), COALESCE(new.email, ''),
CURRENT_TIMESTAMP, COALESCE(new.admin, FALSE),
COALESCE(new.disabled, '0'));
INSERT INTO owners_user
(owner_id, expertise, home_form, form_count, cal_min_hr,
cal_max_hr, query_max)
VALUES
((SELECT owner_id FROM owners_admin WHERE login = new.login),
new.expertise, new.home_form, new.form_count,
COALESCE(new.cal_min_hr, '9'), COALESCE(new.cal_max_hr,
'16'),
new.query_max)
);
CREATE OR REPLACE FUNCTION test_view()
RETURNS INT AS '
DECLARE
BEGIN
EXECUTE ''INSERT INTO owners (first_name, last_name, email,
login, title, expertise)
VALUES
(Test,Person,
[EMAIL PROTECTED], test,
Tester, Testing)'';
END;
' LANGUAGE PLPGSQL;
When you call test_view() you get:
Error is:
FATAL: SPI: improper call to spi_dest_setup
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: Succeeded.
Note that inserts work fine from the command line.
_
Need more e-mail storage? Get 10MB with Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] BUG: PLPGSQL function causes PgSQL process to die when inserting into
My bad; As Tom has pointed out this is a known issue. Thanks. I wonder why my first search didn't find it (probably a typo on my part) :( Best Wishes. _ Try MSN Messenger 6.0 with integrated webcam functionality! http://www.msnmessenger-download.com/tracking/reach_webcam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[BUGS] Behavior at odds with documentation (CREATE USER and SQL language functions)
Hi all; I came across another piece of strange behavior that I wanted to report to you. This appears to be related to previous email disucssions (see http://archives.postgresql.org/pgsql-bugs/2003-04/msg00091.php). Utility statements do not work from SQL language functions. Presumably this is because SQL only does argument substitution and that these do not work with utility commands. The workaround is to do this in plpgsql with an EXECUTE statement. If this is indeed the expected behavior (as I understand from the previous thread), then it would be very nice to see a note to this effect in the documentation on SQL language functions, as that might save others much troubleshooting time as well. Also, is there any chance that this will be changed in the near future? It would be nice to have the option to use arguments for utility commands in SQL language (and for that matter plpgsql language) functions? Best Wishes, Chris Travers _ Express yourself with MSN Messenger 6.0 -- download now! http://www.msnmessenger-download.com/tracking/reach_general ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
