Обсуждение: SPI_execp() failed in RI_FKey_cascade_del()
Hi,
In my database there is a table "request" which is referenced
by a couple of other tables (about 4 or 5). Since quite frequently
I need to delete "request"s with any related data, I formulate
the foreign keys in the following way:
request_id INTEGER
CONSTRAINT n_request_id NOT NULL
CONSTRAINT f_request_id
REFERENCES request ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED,
Here comes the definition of "request":
CREATE TABLE request (
request_id INTEGER
CONSTRAINT d_request_id DEFAULT nextval('s_request_id'::text)
CONSTRAINT p_request_id PRIMARY KEY,
label TEXT CONSTRAINT n_label NOT NULL,
description TEXT ,
announce_date DATE CONSTRAINT d_announce_date DEFAULT CURRENT_DATE,
submit_date DATE CONSTRAINT n_submit_date NOT NULL,
mail_subject TEXT ,
mail_message TEXT ,
organization_id INTEGER
CONSTRAINT n_organization_id NOT NULL
CONSTRAINT f_organization_id REFERENCES organization,
questionnare_id INTEGER
CONSTRAINT n_questionnare_id NOT NULL
CONSTRAINT f_questionnare_id REFERENCES questionnare,
status "char"
CONSTRAINT x_status
CHECK (upper(status) IN ('S','A','O','C','E')),
-- [S]aved, to-[A]nnounce, [O]pen = announced, [C]losed, [E]rror
participated INTEGER CONSTRAINT d_paricipated DEFAULT 0,
asked INTEGER ,
CONSTRAINT x_request_dates CHECK (submit_date > announce_date)
)
WITHOUT OIDS;
and when I execute the query "DELETE FROM request WHERE ..." I got
the following error message:
ERROR: SPI_execp() failed in RI_FKey_cascade_del()
My questions:
- Is it connected to permission somehow?
(delete revoked some of the related tables)
- What to do to get work the cascaded delete?
(with version 7.2.1 of postgres as a database superuser (postgres))
Gyozo Papp
- pgerzson@freestart.hu
"Papp, Gyozo" <pgerzson@freestart.hu> writes:
> and when I execute the query "DELETE FROM request WHERE ..." I got
> the following error message:
> ERROR: SPI_execp() failed in RI_FKey_cascade_del()
I tried to reproduce this without any success. You'll need to provide a
complete example.
regards, tom lane
What must I provide? (complete DB scheme with some data) A complete pg_dump can be found here: http://213.134.22.60/~gerzson/CAF.dump.gz (about 210 kB) it was produced by: "pg_dump -C CAF -Ft -f CAF.dump" command. if anything else is needed, please let me know! ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Papp, Gyozo" <pgerzson@freestart.hu> Cc: <> Sent: Tuesday, April 09, 2002 3:42 PM Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del() | "Papp, Gyozo" <pgerzson@freestart.hu> writes: | > and when I execute the query "DELETE FROM request WHERE ..." I got | > the following error message: | | > ERROR: SPI_execp() failed in RI_FKey_cascade_del() | | I tried to reproduce this without any success. You'll need to provide a | complete example. | | regards, tom lane | | ---------------------------(end of broadcast)--------------------------- | TIP 5: Have you checked our extensive FAQ? | | http://www.postgresql.org/users-lounge/docs/faq.html
Hello,
You can find my minimal db scheme and data which reproduces
this error (after pg_restore, too):
http://213.134.22.60/~gerzson/spi-error.tar.gz
(pg_dump -C Ft -f spi-error.tar and gzipped)
Thanks for your guidance in advance
ps: [to Tom Lane] sorry for the broken link
--
Gyozo Papp - pgerzson@freestart.hu
----- Original Message -----
From: "Papp, Gyozo" <pgerzson@freestart.hu>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, April 09, 2002 4:56 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()
What must I provide? (complete DB scheme with some data)
A complete pg_dump can be found here:
http://213.134.22.60/~gerzson/CAF.dump.gz (about 210 kB)
it was produced by: "pg_dump -C CAF -Ft -f CAF.dump"
command.
if anything else is needed, please let me know!
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Papp, Gyozo" <pgerzson@freestart.hu>
Cc: <>
Sent: Tuesday, April 09, 2002 3:42 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()
| "Papp, Gyozo" <pgerzson@freestart.hu> writes:
| > and when I execute the query "DELETE FROM request WHERE ..." I got
| > the following error message:
|
| > ERROR: SPI_execp() failed in RI_FKey_cascade_del()
|
| I tried to reproduce this without any success. You'll need to provide a
| complete example.
|
| regards, tom lane
|
| ---------------------------(end of broadcast)---------------------------
| TIP 5: Have you checked our extensive FAQ?
|
| http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, 9 Apr 2002, Papp, Gyozo wrote: > Hello, > > You can find my minimal db scheme and data which reproduces > this error (after pg_restore, too): > > http://213.134.22.60/~gerzson/spi-error.tar.gz > (pg_dump -C Ft -f spi-error.tar and gzipped) > > > Thanks for your guidance in advance > > ps: [to Tom Lane] sorry for the broken link I think I see something that might be doing it... There's a rule on answer_summary that forces deletes to be negated which means the trigger sees that the delete wasn't executed which would give the error since the constraint would no longer be satisfied.
Hi, ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Papp, Gyozo" <pgerzson@freestart.hu> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, April 09, 2002 8:11 PM Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del() | | On Tue, 9 Apr 2002, Papp, Gyozo wrote: | | > Hello, | > | > You can find my minimal db scheme and data which reproduces | > this error (after pg_restore, too): | > | > http://213.134.22.60/~gerzson/spi-error.tar.gz | > (pg_dump -C Ft -f spi-error.tar and gzipped) | > | > | > Thanks for your guidance in advance | > | > ps: [to Tom Lane] sorry for the broken link | | I think I see something that might be doing it... | There's a rule on answer_summary that forces deletes to be | negated which means the trigger sees that the delete wasn't | executed which would give the error since the constraint | would no longer be satisfied. So, if I drop these rules and I try to protect my database with a right acl system (grant + revoke), it should work. Thanks for the clarification! -- Gyozo Papp - pgerzson@freestart.hu
On Tue, 9 Apr 2002, Papp, Gyozo wrote: > | I think I see something that might be doing it... > | There's a rule on answer_summary that forces deletes to be > | negated which means the trigger sees that the delete wasn't > | executed which would give the error since the constraint > | would no longer be satisfied. > > So, if I drop these rules and I try to protect my database > with a right acl system (grant + revoke), it should work. Well, it's not going to not delete those rows whatever you do pretty much. It uses the table owner as the deleter of the rows so revoking write from answer_summary won't probably work (if it does, that's a bug). In any case, if it can't delete them, it's a constraint violation and it should error at some point (probably currently with the SPI_execp). I'm guessing the intent was that on insert to answer_summary the row needed to be there but from that point forward you don't care if the row gets deleted in the referenced table? If so, you're probably best off writing a small plpgsql trigger function to do the check, foreign keys have a bunch of semantics you don't want.
Hello,
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Papp, Gyozo" <pgerzson@freestart.hu>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, April 10, 2002 5:41 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()
| On Tue, 9 Apr 2002, Papp, Gyozo wrote:
|
| > | I think I see something that might be doing it...
| > | There's a rule on answer_summary that forces deletes to be
| > | negated which means the trigger sees that the delete wasn't
| > | executed which would give the error since the constraint
| > | would no longer be satisfied.
| >
| > So, if I drop these rules and I try to protect my database
| > with a right acl system (grant + revoke), it should work.
|
| Well, it's not going to not delete those rows whatever you
| do pretty much. It uses the table owner as the deleter
| of the rows so revoking write from answer_summary won't
| probably work (if it does, that's a bug).
So, I must grant delete privilege on each table referencing to
"request" to that user who deletes the rows from "request".
It implies that user can deletes any kind of rows from those
tables at any time. As you guessed below, it is not my original
intention.
BTW, I suspect there might be a bug around here.
I have an "admin" user granted read privilege on "answer" only
and he is able to delete a speficied row from "request".
But, do not trust in it too much! I changed the privileges really
often, so i have to see what the current settings are:
Access privileges for database "CAF"
Table | Access privileges
-------------------------------+------------------------------------------------------
access | {=,gerzson=arwdRxt,target=arw,scheduler=r}
answer | {=,gerzson=arwdRxt,target=ar,admin=r,scheduler=r}
answer_summary | {=,gerzson=arwdRxt,admin=r,scheduler=a}
asked | {=,gerzson=arwdRxt,target=rd,scheduler=arwd}
category | {=,gerzson=arwdRxt,admin=r}
class | {=,gerzson=arwdRxt,admin=r}
classing | {=,gerzson=arwdRxt,admin=arwd}
criterium | {=,gerzson=arwdRxt,target=r,admin=arwd}
notification | {=,gerzson=arwdRxt,admin=ard,scheduler=r}
official | {=,gerzson=arwdRxt,admin=ard,scheduler=r}
organization | {=,gerzson=arwdRxt,admin=arw,scheduler=r}
question | {=,gerzson=arwdRxt,target=r,admin=ar}
questionnare | {=,gerzson=arwdRxt,admin=arw}
questionnare_criterium | {=,gerzson=arwdRxt,target=r,admin=arwd}
questionnare_question | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=r}
request | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=rw}
s_category_id | {=,gerzson=arwdRxt,admin=rwd}
s_class_id | {=,gerzson=arwdRxt,admin=rwd}
s_criterium_id | {=,gerzson=arwdRxt,admin=rwd}
s_official_id | {=,gerzson=arwdRxt,admin=rwd}
s_organization_id | {=,gerzson=arwdRxt,admin=rwd}
s_question_no | {=,gerzson=arwdRxt,admin=rwd}
s_questionnare_id | {=,gerzson=arwdRxt,admin=rwd}
s_request_id | {=,gerzson=arwdRxt,admin=rwd}
scheme_position | {=,gerzson=arwdRxt,admin=r}
task | {=,gerzson=arwdRxt,scheduler=arw}
v_answer_summary_normalized | {=,gerzson=arwdRxt,admin=r}
v_asked_login | {=,gerzson=arwdRxt,target=r,admin=r}
v_questionnare_criterium_list | {=,gerzson=arwdRxt,target=r,admin=r}
v_questionnare_list | {=,gerzson=arwdRxt,admin=r}
v_questionnare_question_list | {=,gerzson=arwdRxt,target=r,admin=r}
v_request_list | {=,gerzson=arwdRxt,admin=r}
(32 rows)
Could you reproduce it? Is it a bug?
May I send a new demo database scheme?
| In any case, if it can't delete them, it's a constraint
| violation and it should error at some point (probably
| currently with the SPI_execp).
|
| I'm guessing the intent was that on insert to answer_summary
| the row needed to be there but from that point forward
| you don't care if the row gets deleted in the referenced
| table? If so, you're probably best off writing a small
| plpgsql trigger function to do the check, foreign keys
| have a bunch of semantics you don't want.
The original goal was to protect "answer" and "answer_summary"
tables against updates and deletes as you guessed. Now, it has
been changed a bit. It must allow to delete a "request" with
all the corresponding answers, summaries, etc.
But, it would be still appreciated to protect the se tables
from any other modifications (undesired updates and deletes).
Anyway, thanks you guidance:
--
Gyozo Papp - pgerzson@freestart.hu
On Thu, 11 Apr 2002, Papp, Gyozo wrote:
> Hello,
> ----- Original Message -----
> From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
> To: "Papp, Gyozo" <pgerzson@freestart.hu>
> Cc: <pgsql-general@postgresql.org>
> Sent: Wednesday, April 10, 2002 5:41 PM
> Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()
>
>
> | On Tue, 9 Apr 2002, Papp, Gyozo wrote:
> |
> | > | I think I see something that might be doing it...
> | > | There's a rule on answer_summary that forces deletes to be
> | > | negated which means the trigger sees that the delete wasn't
> | > | executed which would give the error since the constraint
> | > | would no longer be satisfied.
> | >
> | > So, if I drop these rules and I try to protect my database
> | > with a right acl system (grant + revoke), it should work.
> |
> | Well, it's not going to not delete those rows whatever you
> | do pretty much. It uses the table owner as the deleter
> | of the rows so revoking write from answer_summary won't
> | probably work (if it does, that's a bug).
>
> So, I must grant delete privilege on each table referencing to
> "request" to that user who deletes the rows from "request".
> It implies that user can deletes any kind of rows from those
Well, the code should be using the table owner which means you
shouldn't need to grant delete permission to the user that deletes from
request, however anyone who can delete from request can delete can
delete its associated rows. Someone who can both insert and delete from
request can delete any row.
> tables at any time. As you guessed below, it is not my original
> intention.
>
> BTW, I suspect there might be a bug around here.
> I have an "admin" user granted read privilege on "answer" only
> and he is able to delete a speficied row from "request".
It looks like admin has arwd on request...
> But, do not trust in it too much! I changed the privileges really
> often, so i have to see what the current settings are:
>
> Access privileges for database "CAF"
> Table | Access privileges
> -------------------------------+------------------------------------------------------
> access | {=,gerzson=arwdRxt,target=arw,scheduler=r}
> answer | {=,gerzson=arwdRxt,target=ar,admin=r,scheduler=r}
> answer_summary | {=,gerzson=arwdRxt,admin=r,scheduler=a}
> request | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=rw}
> Could you reproduce it? Is it a bug?
> May I send a new demo database scheme?
>
> | In any case, if it can't delete them, it's a constraint
> | violation and it should error at some point (probably
> | currently with the SPI_execp).
> |
> | I'm guessing the intent was that on insert to answer_summary
> | the row needed to be there but from that point forward
> | you don't care if the row gets deleted in the referenced
> | table? If so, you're probably best off writing a small
> | plpgsql trigger function to do the check, foreign keys
> | have a bunch of semantics you don't want.
>
> The original goal was to protect "answer" and "answer_summary"
> tables against updates and deletes as you guessed. Now, it has
> been changed a bit. It must allow to delete a "request" with
> all the corresponding answers, summaries, etc.
> But, it would be still appreciated to protect the se tables
> from any other modifications (undesired updates and deletes).
I'm not sure I understand, but for cases wher you want
one of the actions or you want the action to fail if a row is referenced
the foreign key is the right thing usually. If you only want an
insert check, foreign keys won't do that because they must always be
satisified which means something has to give when you modify keys or
delete a referenced row.
Hello, | do pretty much. It uses the table owner as the deleter | of the rows so revoking write from answer_summary won't I didn't read carefully enough your previous post, sorry. The table owner's privileges is used when deleting referenced rows. I understood. | It looks like admin has arwd on request... Everything is getting more clear now... (step by step) | I'm not sure I understand, but for cases wher you want | one of the actions or you want the action to fail if a row is referenced | the foreign key is the right thing usually. If you only want an | insert check, foreign keys won't do that because they must always be | satisified which means something has to give when you modify keys or | delete a referenced row. What I really want is the following: - Noone in any circumstances can insert a row into "answer" which refers to a non-existent "request". I think I did good choice with using foreign keys to check if a "request" exists. But... - after a record is inserted into "answer", it must be locked. The table "answer" acts as an archive, no modification is allowed excepted one case. To be more specific: - noone in any circumstances can update any row in "answer", - deleting from "answer" is allowed only when it's triggered by deleting from the referenced "request". It seems to be good solution that foreign key has the "ON DELETE CASCADE" clause. (only admin allowed to perform such an action) sorry for bothering you again, -- Gyozo Papp - pgerzson@freestart.hu
On Thu, 11 Apr 2002, Papp, Gyozo wrote: > | I'm not sure I understand, but for cases wher you want > | one of the actions or you want the action to fail if a row is referenced > | the foreign key is the right thing usually. If you only want an > | insert check, foreign keys won't do that because they must always be > | satisified which means something has to give when you modify keys or > | delete a referenced row. > > What I really want is the following: > > - Noone in any circumstances can insert a row into "answer" which > refers to a non-existent "request". I think I did good choice with > using foreign keys to check if a "request" exists. > > But... > > - after a record is inserted into "answer", it must be locked. The > table "answer" acts as an archive, no modification is allowed > excepted one case. To be more specific: > - noone in any circumstances can update any row in "answer", > - deleting from "answer" is allowed only when it's triggered by > deleting from the referenced "request". It seems to be good > solution that foreign key has the "ON DELETE CASCADE" clause. > (only admin allowed to perform such an action) Okay, yes, that should work with on delete cascade and setting up the permissions appropriately excepting that the table owner can delete from the table directly.