[BioSQL-l] Postgres rules clarification

[Hilmar Lapp]

Hi Rob,

indeed the purpose of the RULE isn't to ensure uniqueness - that's the  
purpose of the unique key constraint.

The RULEs were once motivated by a peculiar difference of transaction  
handling in PostgreSQL compared to other popular RDBMSs, and to allow  
programming INSERTs as efficiently as possible. Specifically, if a  
command fails in an active transaction in PostrgreSQL, the entire  
transaction is doomed, i.e., you can't attempt an INSERT and then do  
an UPDATE instead in PostgreSQL if the INSERT failed, because at that  
point all you have left is to ROLLBACK. So to prevent this situation,  
one would have to test every single INSERT first with a lookup SELECT  
query to ensure that the key doesn't already exist. However, for most  
tables the number of possible keys is "open-ended" (for example,  
sequence accession numbers), so that most lookups are bound to fail.

Meanwhile, this could be programmed much more efficiently without the  
RULEs because PostgreSQL now supports nested transactions. I.e., one  
would open a nested transaction, attempt the INSERT, and if it fails,  
roll it back, and do an UPDATE instead, in a still active transaction.

	-hilmar

On Nov 14, 2011, at 10:24 PM, Rob Syme wrote:

> Hi all,
>
> This may well be a basic query, my SQL experience isn't amazing.
> The BioSQL tables have rules such as (in the ontology table, for  
> example):
>
> CREATE OR REPLACE RULE rule_ontology_i AS
>    ON INSERT TO ontology
>   WHERE (( SELECT ontology.ontology_id
>           FROM ontology
>          WHERE ontology.name::text = new.name::text)) IS NOT NULL DO
> INSTEAD NOTHING;
>
>
> It seems to me that this rule checks to make sure that the name field
> is unique, failing quietly when there is a clash, but the uniqueness
> of the ontology.name is already ensured by the table constraint:
>
> CONSTRAINT ontology_name_key UNIQUE (name)
>
> Have I misunderstood the purpose of the create/replace rule?
>
> -r
>
> Rob Syme
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/biosql-l

-- 
===========================================================
: Hilmar Lapp -:- Durham, NC -:- hlapp at drycafe dot net :
===========================================================