Обсуждение: Multi-table CHECK constraint

I need to add some complex constraints at the DB.

For example.

Do not allow a line item of inventory to be changed if it does not result in the same number of joints originally shipped.

These will involve several tables.

What is the best approach for this?

Here is what I have been trying.

CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint)
  RETURNS double precision AS
'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi
left join view.generic_item_shipment_id v on v.id=gi.id
left join v_shipment_jts_off vsjo on vsjo."shipmentId"=v.shipment_id
where gi.id=$1;'
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco;

alter table inventory.t_generic_item add constraint check_shipment_original_jts CHECK (numoriginaljts(id)=0);

Does this approach seem reasonable?
This did not work, but it is probably my error.  It actually let me break the constraint, but my constraint kicked in when I tried to correct the problem.
Can someone point me to an example of doing something like this?

The point of this is to never let the total number of original pieces be different than the number originally shipped.

My code has done this occasionally and users can override the inventory.

Basically I would rather the application throw an error than let this number become unbalanced.

-- 
Thank you for your time,

Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.octgsoftware.com
HJBug Founder and President		
http://www.hjbug.com   

On Wed, Dec 10, 2008 at 3:58 PM, Jason Long
<mailing.list@supernovasoftware.com> wrote:

> I need to add some complex constraints at the DB.
> These will involve several tables.
> What is the best approach for this?

Well ANSI-SQL provides the CREATE ASSERTION for this purpose.
However, PostgreSQL doesn't support this feature.  It can be mimiced
by using the PostgreSQL CONSTRAINT TRIGGER extenstion.  IIRC, it works
very much like an ordinary trigger except that you must raise an error
when an DML attempt possibly violates your condition.

> Can someone point me to an example of doing something like this?

http://www.postgresql.org/docs/8.3/interactive/sql-createconstraint.html
http://www.postgresql.org/docs/8.3/interactive/triggers.html

I hope this helps.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Richard Broersma wrote:

On Wed, Dec 10, 2008 at 3:58 PM, Jason Long
<mailing.list@supernovasoftware.com> wrote:
 

I need to add some complex constraints at the DB.
These will involve several tables.
What is the best approach for this?   

Well ANSI-SQL provides the CREATE ASSERTION for this purpose.
However, PostgreSQL doesn't support this feature.  It can be mimiced
by using the PostgreSQL CONSTRAINT TRIGGER extenstion.  IIRC, it works
very much like an ordinary trigger except that you must raise an error
when an DML attempt possibly violates your condition.
 

Can someone point me to an example of doing something like this?   

http://www.postgresql.org/docs/8.3/interactive/sql-createconstraint.html
http://www.postgresql.org/docs/8.3/interactive/triggers.html

I hope this helps.

 

Thanks.  I will look into this and see how it goes.

Any other advice would be greatly appreciated.

On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote:
> I need to add some complex constraints at the DB.
>
> For example.
>
> Do not allow a line item of inventory to be changed if it does not
> result in the same number of joints originally shipped.
>
> These will involve several tables.
>
> What is the best approach for this?

Triggers.

> Here is what I have been trying.
>
> CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint)
>  RETURNS double precision AS
> 'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi
> left join view.generic_item_shipment_id v on v.id=gi.id
> left join v_shipment_jts_off vsjo on vsjo."shipmentId"=v.shipment_id
> where gi.id=$1;'
>  LANGUAGE 'sql' VOLATILE
>  COST 100;
> ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco;
>
> alter table inventory.t_generic_item add constraint
> check_shipment_original_jts CHECK (numoriginaljts(id)=0);
>
> *Does this approach seem reasonable?

Nope.  You're lying to the database by wrapping otherwise disallowed
SQL in a check constraint, and it will get its revenge.

> This did not work, but it is probably my error.  It actually let me
> break the constraint, but my constraint kicked in when I tried to
> correct the problem.  Can someone point me to an example of doing
> something like this?*
>
> The point of this is to never let the total number of original
> pieces be  different than the number originally shipped.
>
> My code has done this occasionally and users can override the
> inventory.
>
> Basically I would rather the application throw an error than let
> this  number become unbalanced.

You might want to talk to people who have done bookkeeping
applications for PostgreSQL, or possibly even buy one of the
proprietary PostgreSQL-based systems for it, as this stuff can be
fiendishly tricky to get right.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote:
> > I need to add some complex constraints at the DB.
> >
> > For example.
> >
> > Do not allow a line item of inventory to be changed if it does not
> > result in the same number of joints originally shipped.
> >
> > These will involve several tables.
> >
> > What is the best approach for this?
>


> You might want to talk to people who have done bookkeeping
> applications for PostgreSQL, or possibly even buy one of the
> proprietary PostgreSQL-based systems for it, as this stuff can be
> fiendishly tricky to get right.
>

As a developer of just such a bookkeeping application, here's (IMHO)
the best way to handle this:  Wrap the dependent operations into one
stored procedure, grant rights to that procedure and not to the
underlying tables.  If an operation (such as shipping product)
requires multiple database queries and updates, wrapped in a
transaction, don't place your trust in every user and/or application
to do that properly.

In your example, don't give the user or application UPDATE permission
to the raw inventory or product tables, that's just asking for trouble.
Instead, create a ship_product() procedure that takes all the steps
required.  (You'll also need others, adding inventory for example)

There are many, many benefits to reap once you've made the commitment
to doing this.  More re-usable code, a stable API, atomic operations,
faster transactions, less traffic over the wire, etc etc.

I would still add critical constraints and triggers as a failsafe so
an admin with rights can't accidentally introduce bad data to the
system, but there's just no substitute for proper encapsulation.
Plus, sometimes it's expensive or impossible to verify after the fact
(in a constraint trigger) whether the transaction was valid, but
just wrapping the stuff in a stored procedure is much simpler.