SPI_execp() failed in RI_FKey_cascade_del() 
Author Message
 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sat, 25 Sep 2004 21:27:08 GMT
 SPI_execp() failed in RI_FKey_cascade_del()

Quote:

> 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



Sat, 25 Sep 2004 21:47:38 GMT
 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!

Quote:
----- Original Message -----


Cc: <>
Sent: Tuesday, April 09, 2002 3:42 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()


| > 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sun, 26 Sep 2004 00:33:33 GMT
 SPI_execp() failed in RI_FKey_cascade_del()
Hi,
Quote:
----- Original Message -----



Sent: Tuesday, April 09, 2002 8:11 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()

|

|
| > 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!

--

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 26 Sep 2004 14:08:05 GMT
 SPI_execp() failed in RI_FKey_cascade_del()
Hello,
Quote:
----- Original Message -----



Sent: Wednesday, April 10, 2002 5:41 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()


|
| > | 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:

--

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Mon, 27 Sep 2004 19:57:54 GMT
 SPI_execp() failed in RI_FKey_cascade_del()

Quote:

> Hello,
> ----- Original Message -----



> Sent: Wednesday, April 10, 2002 5:41 PM
> Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()


> |
> | > | 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.

Quote:
> 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...

- Show quoted text -

Quote:
> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 27 Sep 2004 23:47:11 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. DTS Package call another, when child fails, parent fails

2. Failed to boot Server: listeners have failed

3. SQL 2000 DE Install fails - Locked Connectivity Files Check fails

4. Merge Agent Fails - sp_MSupdategenhistory call fails

5. snap-in failed to initialize and Application failed to initialize

6. Connection Fail after installation - dbnmpntw write() connection fail

7. SQL - NT failed install - dies with failed login

8. physical restore failed - restore reserved pages failed

9. tcpip conn fails fails with 22001

10. xp_sendmail fails, then succeeds, then fails

11. Connection to cube fails using Excel 2000, Brio fails

12. snap-in failed to initialize and Application failed to initialize(0xc0000142)


 
Powered by phpBB® Forum Software