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
@[EMAIL PROTECTED](PGN.nspname) || ''.'' ||
@[EMAIL PROTECTED](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
@[EMAIL PROTECTED](PGN.nspname) || ''.'' ||
@[EMAIL PROTECTED](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
@[EMAIL PROTECTED](PGXC.relname) =
@[EMAIL PROTECTED](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
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
