[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
On Thu, 23 Oct 2003, Christopher Travers wrote: > 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. Can you give version information and a standalone example? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG: PLPGSQL function causes PgSQL process to die when inserting into a view
"Christopher Travers" <[EMAIL PROTECTED]> writes: > FATAL: SPI: improper call to spi_dest_setup I think this is a known and already-fixed bug. What version are you running? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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
"Christopher Travers" <[EMAIL PROTECTED]> writes: > Sorry, forgot the version information: 7.3.2 Update to 7.3.4 --- I see this fix in the CVS logs: 2003-02-14 16:12 tgl * src/backend/executor/spi.c (REL7_3_STABLE): Fix SPI result logic for case where there are multiple statements of the same type in a rule. Per bug report from Pavel Hanak. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
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
[BUGS] my postgreSQL 7.4 beta for windows
hello,... I'm using postgreSQL 7.4 beta for windows, and i use them in my project with delphi and i use DBexpress for connecting to then Postgres, but in other case my postgres runs very slowly ,I compare it with mySQL is faster than mine, By the way my computer spesification is : Intel PIII 600B , 256 MB RAM could you help me, is there any other procedure in the installation to make my Postgres runs fast(maybe my installation is standart); Thank You dedy styawan Indonesia __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.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
[BUGS] currval and nextval in 7.3.4
Hi,
I recently installed 7.3.4 (complete install from scratch) and both 'select
nextval('my_seq') from my_table' and 'select currval('my_seq') from my_table'
return a number of rows equal to the number of rows in the table.
The sequence was created with a SERIAL type if that helps.
In 'psql' the results look like this.
my_db=# select nextval('my_seq') from my_table;
nextval
-
6
7
8
9
(4 rows)
my_db=# select currval('my_seq') from my_table;
currval
-
9
9
9
9
(4 rows)
I get the same results using the JDBC driver so it's not a psql problem.
Any thoughts out there?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] my postgreSQL 7.4 beta for windows
On Wednesday 22 October 2003 05:07, dedy setiawan wrote: > hello,... > I'm using postgreSQL 7.4 beta for windows, and i use > them in my project with delphi and i use DBexpress for > connecting to then Postgres, but in other case my > postgres runs very slowly Could you subscribe to the performance list instead of this one? That's the best place for these sorts of questions. Two points: 1. PG on Windows on Cygwin is almost certainly slower than on *nix. 2. People are going to want to know what tuning you've done in the postgresql.conf file and what type of load you're testing it with. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] ECPG and NULL indicators
POSTGRESQL BUG REPORT
Your name : Edmund Bacon
Your email address : ebacon (at) onesystem (dot) com
System Configuration
-
Architecture : Intel Pentium
Operating System : Linux 2.4.20
PostgreSQL version : PostgreSQL-7.3.3
Compiler used : gcc-3.2.2
Please enter a FULL description of your problem:
ecpg does not correctly set null indicators when storage for the
string is dynamically allocated
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
CREATE TABLE strings (string text);
insert into strings values('able');
insert into strings values(null);
insert into strings values('baker');
insert into strings values(null);
Source for foo.pgc:
#include
#include
#include
EXEC SQL WHENEVER SQLERROR sqlprint;
EXEC SQL INCLUDE sqlca;
int main()
{
int i;
EXEC SQL BEGIN DECLARE SECTION;
char **a_str;
int *a_str_ind;
char str[5][20];
int str_ind[5];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO test;
printf("Test one: alloced string, allocated indicator:\n");
a_str = NULL;
a_str_ind = NULL;
EXEC SQL SELECT string INTO :a_str :a_str_ind FROM strings;
printf("indicator string\n");
for(i = 0; i < sqlca.sqlerrd[2]; i++)
printf("%8d \"%s\"\n", a_str_ind[i], a_str[i]);
free(a_str);
free(a_str_ind);
printf("\nTest two: alloced string, unalloced indicator:\n");
a_str = NULL;
for(i = 0; i < 5; i++) str_ind[i] = 99;
EXEC SQL SELECT string INTO :a_str :str_ind FROM strings;
printf("indicator string\n");
for(i = 0; i < sqlca.sqlerrd[2]; i++)
printf("%8d \"%s\"\n", str_ind[i], a_str[i]);
free(a_str);
printf("\nTest three: unalloced string, alloced indicator:\n");
a_str_ind = NULL;
bzero(str, sizeof(str));
EXEC SQL SELECT string INTO :str :a_str_ind FROM strings;
printf("indicator string\n");
for(i = 0; i < sqlca.sqlerrd[2]; i++)
printf("%8d \"%s\"\n", a_str_ind[i], str[i]);
free(a_str_ind);
printf("\nTest four: unalloced string, unalloced indicator:\n");
bzero(str, sizeof(str));
for(i = 0; i < 5; i++) str_ind[i] = 99;
EXEC SQL SELECT string INTO :str :str_ind FROM strings;
printf("indicator string\n");
for(i = 0; i < sqlca.sqlerrd[2]; i++)
printf("%8d \"%s\"\n", str_ind[i], str[i]);
return 0;
}
==
Output for foo:
==
Test one: alloced string, allocated indicator:
indicator string
-1 "able"
0 ""
0 "baker"
0 ""
Test two: alloced string, unalloced indicator:
indicator string
-1 "able"
99 ""
99 "baker"
99 ""
Test three: unalloced string, alloced indicator:
indicator string
0 "able"
-1 ""
0 "baker"
-1 ""
Test four: unalloced string, unalloced indicator:
indicator string
0 "able"
-1 ""
0 "baker"
-1 ""
==
Note that when the storage for the string is allocated, only the first
element of the indicator array is set. This value is the value of
the indicator for the last string in the string array, which can be
confirmed by using the appropriate ORDER BY clause.
This problem does not arise with allocated integer or float values.
This problem occurs if string is any multi-char type (e.g. TEXT, CHAR(),
or VARCHAR())
---(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] currval and nextval in 7.3.4
On Thu, 23 Oct 2003, Keith Marr wrote:
> Hi,
>
> I recently installed 7.3.4 (complete install from scratch) and both 'select
> nextval('my_seq') from my_table' and 'select currval('my_seq') from my_table'
> return a number of rows equal to the number of rows in the table.
>
> The sequence was created with a SERIAL type if that helps.
> In 'psql' the results look like this.
>
> my_db=# select nextval('my_seq') from my_table;
> nextval
> -
>6
>7
>8
>9
> (4 rows)
This is what you asked for, for each row of my_table, call
nextval('my_seq') and return its value as an output row.
I'd guess that maybe you just want select nextval('myseq');
but I'm not sure.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
