Skip site navigation (1) Skip section navigation (2)

Re: By Passed Domain Constraints

From: Robert Perry <rlperry(at)lodestonetechnologies(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: By Passed Domain Constraints
Date: 2005-07-06 15:43:06
Message-ID: B37006A8-8E96-4E45-B01F-11BA87DF383F@lodestonetechnologies.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
     I'm sorry Tom, but I am not certain that I understand how this  
known problem applies to my problem.

select * from employee_passcode_update('system',100000, 'DOGGY');
ERROR:  value for domain d_passcode violates check constraint  
"d_passcode_check"

     I call the same function from pqlib using PQexecParams and the  
next thing I know.

select * from employee_passcode where id = 100000;
    id   | passcode | mod_user |           timestamp
--------+----------+----------+-------------------------------
100000 | DOGGY    | system   | 2005-07-06 11:37:09.926907-04

Where

\df employee_passcode_update
                                        List of functions
Schema |           Name           | Result data type |           
Argument data types
--------+--------------------------+------------------ 
+----------------------------------------
public | employee_passcode_update | d_employee_id    | d_user_name,  
d_employee_id, d_passcode

and

\d employee_passcode
    Table "public.employee_passcode"
   Column   |     Type      | Modifiers
-----------+---------------+-----------
id        | d_employee_id | not null
passcode  | d_passcode    |
mod_user  | d_user_name   |
timestamp | d_timestamp   |
Indexes:
     "employee_passcode_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "employee_passcode_mod_user_fkey" FOREIGN KEY (mod_user)  
REFERENCES employee(user_name)

     The same plpgsql function is being called each time.   
Additionally, I believe that the same checks work from php.

     However, if I cast the D_PASSCODE to a varchar and back in the  
function implementation I do get the expected exception when called  
from PQexecParams.  Which is good.  It means I have a work around,  
though I really do not want to updated hundreds of functions to do  
this.  (I suspect that this will be my short term solution) Just  
casting to a D_PASSCODE does nothing.  I am guessing this is because  
p_passcode, my function parameter in question, is already a D_PASSCODE.

     I have also been bitten by the problem you are describing. But,  
that one is a problem even when called from psql if I am not  
mistaken.  Does psql not use pqlib?  Perhaps it is something about  
PQexecParams that is the problem.  I will test in a little while.

Thanks for you help
Robert Perry

On Jul 6, 2005, at 10:35 AM, Tom Lane wrote:

> Robert Perry <rlperry(at)lodestonetechnologies(dot)com> writes:
>
>>      It looks like the data is not checked when passed to the
>> function and from their on out since it is already the correct type
>> it is not checked again. Has anyone else seen something like this?
>>
>
> IIRC, plpgsql does not know anything about domains and does not  
> enforce
> domain constraints when assigning to local variables or the function
> result.  The same is true of the other PLs, though I think it could  
> only
> matter for the function result in those cases (internal variables  
> aren't
> of SQL types anyway for them).  You could probably work around this by
> writing explicit casts to the domain inside the function, eg
> "RETURN x::domain" not just "RETURN x".
>
> I thought the unfinished work for domains was mentioned on the TODO  
> list
> but I don't see anything about it right now.
>
>             regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to  
> majordomo(at)postgresql(dot)org)
>


In response to

Responses

pgsql-hackers by date

Next:From: Teodor SigaevDate: 2005-07-06 15:53:13
Subject: GiST notice
Previous:From: Tom LaneDate: 2005-07-06 15:26:52
Subject: Re: timezone changes break windows and cygwin

pgsql-interfaces by date

Next:From: Tom LaneDate: 2005-07-06 16:05:58
Subject: Re: By Passed Domain Constraints
Previous:From: Tom LaneDate: 2005-07-06 14:35:37
Subject: Re: By Passed Domain Constraints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group