Horton

Thread

  1. referential integrity constraints not checked inside PL/pgSQL functions?

    Christian Rank <christian.rank@rz.uni-passau.de> — 2004-05-13T09:41:24Z

    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

  2. Re: referential integrity constraints not checked inside PL/pgSQL functions?

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2004-05-13T10:26:34Z

    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

  3. Re: referential integrity constraints not checked inside

    Christian Rank <christian.rank@rz.uni-passau.de> — 2004-05-13T11:22:24Z

    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

  4. Re: referential integrity constraints not checked inside PL/pgSQL functions?

    Chris Browne <cbbrowne@acm.org> — 2004-05-13T11:59:54Z

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

  5. Re: referential integrity constraints not checked inside

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-13T14:09:28Z

    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

  6. Re: referential integrity constraints not checked inside

    Christian Rank <christian.rank@rz.uni-passau.de> — 2004-05-13T14:19:17Z

    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

  7. Re: referential integrity constraints not checked inside

    James Moe <jimoe@sohnen-moe.com> — 2004-05-13T15:26:37Z

    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