Christopher Browne cbbrowne
Thu Jul 6 10:00:40 PDT 2006
In response to the issues observed with nullable columns found in
columns used for would-be candidate primary keys, I have added the
following logic to the setAddTable_int() function:

    v_pkcand_nn := ''f'';
    for v_prec in select attname from "pg_catalog".pg_attribute where
attrelid =
                        (select oid from "pg_catalog".pg_class where oid
= v_tab_reloid)
                    and attname in (select attname from
"pg_catalog".pg_attribute where
                                    attrelid = (select oid from
"pg_catalog".pg_class PGC,
                                    "pg_catalog".pg_index PGX where
                                    PGC.relname = p_tab_idxname and
PGX.indexrelid=PGC.oid and
                                    PGX.indrelid = v_tab_reloid)) and
attnotnull <> ''t''
    loop
        raise notice ''Slony-I: setAddTable_int: table % PK column %
nullable'', p_fqname, v_prec.attname;
        v_pkcand_nn := ''t'';
    end loop;
    if v_pkcand_nn then
        raise exception ''Slony-I: setAddTable_int: table % not
replicable!'', p_fqname;
    end if;

Existing regression tests work fine, as they have perfectly good
candidate primary keys.

The following table doesn't:

-- Add schema for table4 that hasn't got a good candidate PK

create table table4 (
  id serial NOT NULL,
  id2 integer
);

create unique index no_good_candidate_pk on table4 (id, id2);

-- Slonik to try to replicate this bad table:
set add table (id=4, set id=1, origin=1, fully qualified name =
'public.table4', key = 'no_good_candidate_pk');

Log output when this is submitted:

cbbrowne at dba2:~/Slony-I/slony1-HEAD-pristine/tests> more
/tmp/slony-regress.v17146/slonik.log                              
Thursday 16:53:34
<stdin>:10: NOTICE:  Slony-I: setAddTable_int: table public.table4 PK
column id2 nullable
CONTEXT:  SQL statement "SELECT  "_slony_regress1".setAddTable_int( $1
,  $2 ,  $3 ,  $4 ,  $5 )"
PL/pgSQL function "setaddtable" line 37 at perform
<stdin>:10: PGRES_FATAL_ERROR select "_slony_regress1".setAddTable(1, 4,
'public.table4', 'no_good_candidate_pk', 'public.table4');  - ERROR: 
Slony-I: setAddTable_int: table public.table4 not replicable!
CONTEXT:  SQL statement "SELECT  "_slony_regress1".setAddTable_int( $1
,  $2 ,  $3 ,  $4 ,  $5 )"
PL/pgSQL function "setaddtable" line 37 at perform
<stdin>:4: Possible unsupported PostgreSQL version (0) 0.0, defaulting
to 8.0 support

That should suitably prevent adding tables to replication that have
nullable "candidate primary key" columns...



More information about the Slony1-general mailing list