referential integrity constraints not checked inside PL/pgSQL functions?

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/&gt;

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