Обсуждение: 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.
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:
Any other advice would be greatly appreciated.
Thanks. I will look into this and see how it goes.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.
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.