Обсуждение: question for serial types with CHECK conditions

Hey ya,
  Found a behaviour that caught my attention. While it's not a real
problem, and definetely not serious, I realize that it may be good to
write about it, cause I couldn't find previous posts on the matter.
 Basically, I am preparing a presentation for an install party, and
while I was checking the documentation for certain bullets I arrived
to the CHECK stuff.
 Therefore, I went into my psql and threw the following, without
paying too much attention:
  CREATE TABLE checksss (a serial CHECK (a > 1 and a < 10), b varchar);

followed by a :
  INSERT INTO checksss (b) values ('asdf');
  Obviously, postgresql replied with a big : "new row ...violates
check constraint ..."
  Now, my question is: Shouldn't postgresql avoid the creation of the
table while a serial type contains a check condition? A serial type
represents a sequence, but is not the same as creating the sequence
per se, with special conditions from start.
  I may be radically wrong, and this is probably the case, but I
couldn't resist and choose to ask.

Kind regards,
-- 
Guido Barosio
-----------------------
http://www.globant.com
guido.barosio@globant.com

Thanks for your reply Michael.
  My point was to step on the asumption that the implicit "serial"
call for a type represents the fact that the sequence will start
allways in the same place, unless inmediatelly after your "create
table" you plan to modify that, which makes no sense when we go back
to what the CREATE SEQUENCE represents for the case.
  For what I saw, straight foward what I did is wrong, but the server
allowed me to proceed. So yes, my fault, but with a bit of help,
right?
  After all I am just being boggus on a silly point. The range of
potential DBA's which may come to this situation is pretty small for
further discussions :)

Best wishes,
g.-

On 4/28/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Apr 28, 2007, at 10:30 , Guido Barosio wrote:
>
> >   Now, my question is: Shouldn't postgresql avoid the creation of the
> > table while a serial type contains a check condition?
>
> My question to you is why should it? "a" SERIAL is a shorthand for
> creating an INTEGER column "a",  a sequence ("a_seq") with a
> dependency, and DEFAULT nextval(a_seq). There may be a valid reason
> someone wants to put additional constraints on the column, and I'm
> not sure why the server should second guess the DBA in this case. If
> the CHECK constraint isn't what you want, then don't include it: and
> in this case the server helpfully gave you an error which let you
> know that the CHECK constraint was not doing what you expected.
>
> Also, the server doesn't have the smarts to look into the CHECK
> constraint and decide if it makes sense in your case. For example,
> perhaps you want to have CHECK (a > 0), which won't really do
> anything for a default sequence. However, if the sequence is changed,
> it may return negative integers, which you may not want, so in some
> cases, CHECK (a > 0) may be a valid constraint *in your case*.
>
> The crux of the issue is that there may be valid reasons to have a
> CHECK constraint on a INTEGER (SERIAL) column, and the server is not
> (and will probably never be) smart enough to know your particular
> business rules without you telling it specifically.
>
> Does this help clarify the situation?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>


-- 
Guido Barosio
-----------------------
http://www.globant.com
guido.barosio@globant.com

On Apr 28, 2007, at 10:30 , Guido Barosio wrote:

>   Now, my question is: Shouldn't postgresql avoid the creation of the
> table while a serial type contains a check condition?

My question to you is why should it? "a" SERIAL is a shorthand for  
creating an INTEGER column "a",  a sequence ("a_seq") with a  
dependency, and DEFAULT nextval(a_seq). There may be a valid reason  
someone wants to put additional constraints on the column, and I'm  
not sure why the server should second guess the DBA in this case. If  
the CHECK constraint isn't what you want, then don't include it: and  
in this case the server helpfully gave you an error which let you  
know that the CHECK constraint was not doing what you expected.

Also, the server doesn't have the smarts to look into the CHECK  
constraint and decide if it makes sense in your case. For example,  
perhaps you want to have CHECK (a > 0), which won't really do  
anything for a default sequence. However, if the sequence is changed,  
it may return negative integers, which you may not want, so in some  
cases, CHECK (a > 0) may be a valid constraint *in your case*.

The crux of the issue is that there may be valid reasons to have a  
CHECK constraint on a INTEGER (SERIAL) column, and the server is not  
(and will probably never be) smart enough to know your particular  
business rules without you telling it specifically.

Does this help clarify the situation?

Michael Glaesemann
grzm seespotcode net

On Sat, Apr 28, 2007 at 01:34:15PM -0300, Guido Barosio wrote:
>   My point was to step on the asumption that the implicit "serial"
> call for a type represents the fact that the sequence will start
> allways in the same place, unless inmediatelly after your "create
> table" you plan to modify that, which makes no sense when we go back
> to what the CREATE SEQUENCE represents for the case.

I don't think we need to go second guessing what the user may or may
not have wanted. If the user wanted this, we provide it. If the user
didn't they'll notice soon enough. The most important thing is that it
does exactly what it's documented to do.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.