Re: [GENERAL] subselect in CHECK constraint?

2000-09-04 Thread Alfred Perlstein 

* Ian Turner [EMAIL PROTECTED] [000903 22:37] wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 When I try to do this:
 
 CREATE TABLE test (
   a Integer,
   b Integer, 
   CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b)  1000)
 ); 
 
 INSERT INTO test (a, b) VALUES (100, 2);
 
 I get this error on the second query:
 
 ERROR:  ExecEvalExpr: unknown expression type 108
 
 I'm guessing this means I can't do subselects in CHECK statements.

Two things:

1) i'm pretty sure this subselect can be rewritten as:
SELECT SUM(t.a)  1000 FROM test t WHERE t.b = b
to return a boolean.

2) you can probably get away with using a plpgsql function
that has more logic in it.

I'm not saying that subselects do or do not work, just offering
some alternative advice.

-Alfred

RE: [GENERAL] subselect in CHECK constraint?

2000-09-04 Thread Hiroshi Inoue 

 -Original Message-
 From: Ian Turner
 
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 When I try to do this:
 
 CREATE TABLE test (
   a Integer,
   b Integer, 
   CHECK ((SELECT SUM(t.a) FROM test t WHERE t.b = b)  1000)
 ); 
 
 INSERT INTO test (a, b) VALUES (100, 2);
 
 I get this error on the second query:
 
 ERROR:  ExecEvalExpr: unknown expression type 108
 
 I'm guessing this means I can't do subselects in CHECK statements.
 

Yes.
It would be very difficult to implement constraints other than column
constraints. There seems to be 2 reasons at least.
1) We have to check the constraint not only for the row itself which is
about to be insert/update/deleted but also for other related rows.
As for your case,if b is updated the constraints not only for new b
but also for old b should be checked. If the WHERE clause is more
complicated what kind of check should we do ? 
2) The implementation is very difficult without acquiring a table level
locking.  As for your case I couldn't think of any standard way to
prevent the following other than acquiring a table level locking.

When there's no row which satisfies b = 2,two backends insert values
(500, 2) at the same time.

Regards.

Hiroshi Inoue

Re: [GENERAL] subselect in CHECK constraint?

2000-09-04 Thread Ian Turner 

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

   CHECK (testconstraint(a, b))

Uhhh. I get no errors, but it dosen't work, either. Consider:

CREATE FUNCTION testconstraint(int,int) RETURNS bool AS '
BEGIN
RETURN (select sum(a) FROM test WHERE b = $2)  1000;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE test (a int, b int, CHECK (testconstraint(a,b)));

INSERT INTO test (a,b) VALUES (1100, 1);

SELECT * FROM test;

Yielding:

  a   | b
- --+---
 1100 | 1
(1 row)

which clearly does not satisfy the constraint.

Ian
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5s8/6fn9ub9ZE1xoRAuiRAKCHh/wWSl7uYzhJGWnc7kc0OxqZogCgpMCN
MdTBSXm7w0C4R4Ghh77+8ok=
=nik7
-END PGP SIGNATURE-

Re: [GENERAL] subselect in CHECK constraint?

2000-09-04 Thread Ian Turner 

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

 Also, as several other people already pointed out, a constraint
 involving a select could be violated in many ways including alteration
 or removal of tuples in other tables.  We only evaluate check
 constraints when we insert/update tuples in the table they are attached
 to...

OK.

Is this something that could be accomplished with triggers? :o

Also, is it possible to have a foreign key constraint across multiple
columns? :o

Ian
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5s9xxfn9ub9ZE1xoRAo9WAJ0blihjzEQFo+3clEGRsySjkUzrqgCdEIhe
8VumU6bICMN6jUHCdq0WSYM=
=niuY
-END PGP SIGNATURE-