"Chris Burtenshaw" <cburtenshaw at qinetiq-tim.com> writes:
> I've spent some time looking at this, and I'm wondering if it's a bug in
> how slony handles tables with foreign keys that require an added
> primarykey (using table add key).
>
> Basically, I have a table with two fields, midstindex and alertindex
> (both bigint) which I want to replicate.  Alertindex is a foreign key on
> another table.
>
> For various reasons I can't add a unique constraint on the table to use
> as a key, so I've added in a slony index for testing purposes (using
> table add key).  However, it appears that if either database is not
> "fresh" i.e has previously had a slony instance on it that has been
> uninstalled and replaced, I get the following error (there is no data in
> either my master or slave copies of the involved tables, but there is
> stuff in other tables):
>  
> 2005-10-21 10:51:03 BST ERROR  remoteWorkerThread_1: "select
> "_mirror_test".truncateTable('"public"."alertindexes"'); copy
> "public"."alertindexes"
> ("midstindex","alertindex","."."......pg.dropped.3........","_Slony-I_mi
> rror_test_rowID") from stdin; " ERROR:  syntax error at or near "." at
> character 128
>  ERROR:  syntax error at or near "." at character 128
>
> If I remove slony from my master and slave, dump the data to a file
> using pg_dump, drop the databases and start from scratch, set up slony
> to replicate the databases and put the data back in, it works fine. -
> perplexing!
>
> I've had a look at the FAQs etc, and I've not come across this issue
> before, but its entirely possible that I've missed something...

Which version are you running?  1.1.1?

There's a leetle bit of a bug I introduced to 1.1.1 where dropped
columns aren't omitted when building the COPY field list.  The
function that is relevant is copyFields(); in 1.1.1, the "and
a.attisdropped=false" clause was left out.

If you run the query...

  select a.attname from _mirror_test.sl_table t,
  pg_catalog.pg_attribute a where t.tab_id = $1 and t.tab_reloid =
  a.attrelid and a.attnum > 0 and a.attisdropped=false order by
  attnum;

you will probably get something like:

          attname
------------------------------
 "midstindex"
 "alertindex"
 "."
 "."
 "......pg.dropped.3........"
(5 rows)

The new version of the function:

create or replace function @NAMESPACE at .copyFields(integer) 
returns text
as '
declare
	result text;
	prefix text;
	prec record;
begin
	result := '''';
	prefix := ''('';   -- Initially, prefix is the opening paren

	for prec in select @NAMESPACE at .slon_quote_input(a.attname) as column from @NAMESPACE at .sl_table t, pg_catalog.pg_attribute a where t.tab_id = $1 and t.tab_reloid = a.attrelid and a.attnum > 0 and a.attisdropped=false order by attnum
	loop
		result := result || prefix || prec.column;
		prefix := '','';   -- Subsequently, prepend columns with commas
	end loop;
	result := result || '')'';
	return result;
end;
' language plpgsql;
-- 
(reverse (concatenate 'string "ofni.sailifa.ac" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)