"Chris Burtenshaw" <[EMAIL PROTECTED]> 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 @[EMAIL PROTECTED](integer)
returns text
as '
declare
result text;
prefix text;
prec record;
begin
result := '''';
prefix := ''(''; -- Initially, prefix is the opening paren
for prec in select @[EMAIL PROTECTED](a.attname) as column from @[EMAIL
PROTECTED] 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)
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
