Cédric Villemain wrote:
> Geoffrey a écrit :
>> Okay, so we have one table that appears to be having a problem in 
>> replication.  The unique thing about this table is that we have a 
>> field that often times is null, yet it has in index as follows:
>>
>> "tract_order_num_key" unique, btree (order_num)
>>
>> So, this application has been running along just fine like this for a 
>> couple of years.  Is it possible that the above index is causing the 
>> problem?
> for me yes. unique contraint does not check the null values. 
> http://www.postgresql.org/docs/current/static/ddl-constraints.html
>>
>> The difference between the data in this table on the primary and slave 
>> does appear to be related to rows where the order_num field is null.
>>
> 
> yes, slony check the first index matching primary key criteria , and use 
> it if none specified.

But this is not the primary key.  There is a primary key specified. 
Here are the indices on this table:

Indexes:

     "tract_pkey" primary key, btree (recid)
     "tract_order_num_key" unique, btree (order_num)
     "tract_assigned" btree (assigned)
     "tract_code" btree (code)
     "tract_comments" btree (comments)
     "tract_compound_1" btree (code, old_order_num)
     "tract_date_avail" btree (date_avail)
     "tract_dest_state" btree (dest_state)
     "tract_dest_zone" btree (dest_zone)
     "tract_driver" btree (driver)
     "tract_orig_state" btree (orig_state)
     "tract_orig_zone" btree (orig_zone)
     "tract_prebooked" btree (prebooked)
     "tract_tractor_num" btree (tractor_num)
     "tract_trailer_num" btree (trailer_num)

Note the primary key is 'tract_pkey'

> You have probably explicitely tell slony the Primary key to use ? and 
> Slony didn't check that you lies :
> 
> 
>      if p_idx_name isnull then
>                select PGXC.relname
>                                into v_idxrow
>                                from "pg_catalog".pg_class PGC,
>                                        "pg_catalog".pg_namespace PGN,
>                                        "pg_catalog".pg_index PGX,
>                                        "pg_catalog".pg_class PGXC
>                                where 
> @NAMESPACE at .slon_quote_brute(PGN.nspname) || ''.'' ||
>                                        
> @NAMESPACE at .slon_quote_brute(PGC.relname) = v_tab_fqname_quoted
>                                        and PGN.oid = PGC.relnamespace
>                                        and PGX.indrelid = PGC.oid
>                                        and PGX.indexrelid = PGXC.oid
>                                        and PGX.indisprimary;
>                if not found then
>                        raise exception ''Slony-I: table % has no primary 
> key'',
>                                        v_tab_fqname_quoted;
>                end if;
>        else
>                select PGXC.relname
>                                into v_idxrow
>                                from "pg_catalog".pg_class PGC,
>                                        "pg_catalog".pg_namespace PGN,
>                                        "pg_catalog".pg_index PGX,
>                                        "pg_catalog".pg_class PGXC
>                                where 
> @NAMESPACE at .slon_quote_brute(PGN.nspname) || ''.'' ||
>                                        
> @NAMESPACE at .slon_quote_brute(PGC.relname) = v_tab_fqname_quoted
>                                        and PGN.oid = PGC.relnamespace
>                                        and PGX.indrelid = PGC.oid
>                                        and PGX.indexrelid = PGXC.oid
>                                        and PGX.indisunique
>                                        and 
> @NAMESPACE at .slon_quote_brute(PGXC.relname) = 
> @NAMESPACE at .slon_quote_input(p_idx_name);
>                if not found then
>                        raise exception ''Slony-I: table % has no unique 
> index %'',
>                                        v_tab_fqname_quoted, p_idx_name;
>                end if;
>        end if;
> 
> 
> in src/backend/slony1_funcs.sql
> 
> see the 'and PGX.indisprimary;' vs 'and PGX.indisunique'
> 
> Perhaps some bugs around ? Slony MUST check it is a PK, not a unique 
> contraint only.
> 


-- 
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
  - Benjamin Franklin