Thread: SPI_execp() failed in RI_FKey_cascade_del()

SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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



Re: SPI_execp() failed in RI_FKey_cascade_del()

From
Tom Lane
Date:
"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

Re: SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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


Re: SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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



Re: SPI_execp() failed in RI_FKey_cascade_del()

From
Stephan Szabo
Date:
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.



Re: SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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


Re: SPI_execp() failed in RI_FKey_cascade_del()

From
Stephan Szabo
Date:
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.



Re: SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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


Re: SPI_execp() failed in RI_FKey_cascade_del()

From
Stephan Szabo
Date:
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.


Re: SPI_execp() failed in RI_FKey_cascade_del()

From
"Papp, Gyozo"
Date:
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




Re: SPI_execp() failed in RI_FKey_cascade_del()

From
Stephan Szabo
Date:
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.