Thread: referential integrity constraints not checked inside PL/pgSQL functions?

Hello,

I came across the following problem with integrity constraints and
PL/pgSQL (PostgreSQL version used: 7.4.2):

I defined the following tables, constraints and data:

    create table a (n integer);
    create table b (n integer);
    alter table a add primary key (n);
    alter table b add foreign key (n) references a(n);
    insert into a values (1);
    insert into b values (1);

When trying to execute
    delete from a;
this is denied, since the integrity constraint would be violated.

So far, so good.

Now I defined the following function:

    create function f () returns void as '
    begin
      delete from a;
      delete from b;
      return;
    end;
    ' language plpgsql;

I would expect that

    select f();

yields an error message about constraint violation when executing
'delete from a;'.

However, the function is executed without errors, and the tables a and b
are empty after this operation.

It seems that the validity of (integrity) constraints is not checked
inside a function, only after executing a function. Is this a bug or a
feature?

Thanks in advance for your advice,

--
Dr. Christian Rank
Rechenzentrum Universität Passau
Innstr. 33
D-94032 Passau
GERMANY
Tel.: 0851/509-1838
Fax:  0851/509-1802
PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank

On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote:
>     create function f () returns void as '
>     begin
>       delete from a;
>       delete from b;
>       return;
>     end;
>     ' language plpgsql;
>
> I would expect that
>
>     select f();
>
> yields an error message about constraint violation when executing
> 'delete from a;'.

Off the top of my head, the constraints would be checked when the
transaction ends, i.e., after both the "delete from a" and "delete from b"
happened. Split into 2 transactions?

Cheers,

Patrick

Patrick Welche wrote:

> On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote:
>
>>    create function f () returns void as '
>>    begin
>>      delete from a;
>>      delete from b;
>>      return;
>>    end;
>>    ' language plpgsql;
>>
>>I would expect that
>>
>>    select f();
>>
>>yields an error message about constraint violation when executing
>>'delete from a;'.
>
>
> Off the top of my head, the constraints would be checked when the
> transaction ends, i.e., after both the "delete from a" and "delete from b"
> happened. Split into 2 transactions?

Thanks for this suggestion, but I think this does not solve the issue,
since according to the docs, the validity of a constraint should be
checked after each statement unless this behaviour is altered with a SET
CONSTRAINTS statement.

Anyway, the select f(); is in my case not executed in transactional
context (not embraced by START TRANSACTION; ... COMMIT;).

Regards,
    Christian

--
Dr. Christian Rank
Rechenzentrum Universität Passau
Innstr. 33
D-94032 Passau
GERMANY
Tel.: 0851/509-1838
Fax:  0851/509-1802
PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank

Supposing you drop the "delete from b;" from the function, you'll find
that the function fails with much the same error message you had
before.

Evidently that foreign key check gets _deferred_ in the context of the
stored procedure.  It is indeed checked; just not at the point you
expect it to be checked at.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/advocacy.html
Rules  of  the  Evil  Overlord  #89.  "After  I  captures  the  hero's
superweapon, I  will not immediately  disband my legions and  relax my
guard because I believe whoever holds the weapon is unstoppable. After
all,   the  hero  held   the  weapon   and  I   took  it   from  him."
<http://www.eviloverlord.com/>

Christian Rank <christian.rank@rz.uni-passau.de> writes:
> ... according to the docs, the validity of a constraint should be
> checked after each statement unless this behaviour is altered with a SET
> CONSTRAINTS statement.

"Statement" means "interactive command" in that context --- in other
words, the constraints won't be checked until after control returns from
your function.

            regards, tom lane

Tom Lane wrote:

> Christian Rank <christian.rank@rz.uni-passau.de> writes:
>
>>... according to the docs, the validity of a constraint should be
>>checked after each statement unless this behaviour is altered with a SET
>>CONSTRAINTS statement.
>
>
> "Statement" means "interactive command" in that context --- in other
> words, the constraints won't be checked until after control returns from
> your function.

Thanks very much, that clarifies this behaviour of PostgreSQL -> it's
definitively a feature, not a bug :-)

--
Dr. Christian Rank
Rechenzentrum Universität Passau
Innstr. 33
D-94032 Passau
GERMANY
Tel.: 0851/509-1838
Fax:  0851/509-1802
PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank

Christian Rank wrote:
>
>     create table a (n integer);
>     create table b (n integer);
>     alter table a add primary key (n);
>     alter table b add foreign key (n) references a(n);
>
Have you considered using "on delete cascade" in table b?

--
jimoe at sohnen-moe dot com