[Bucardo-general] Adding sync fails due to missing custom type definition

[Alex Balashov]

On 11/21/2010 10:16 PM, Greg Sabino Mullane wrote:
>> Now, the fly in the ointment is that we are using a custom third-party
>> operator class for certain telephony-related applications:
> ...
>> failed: ERROR:  type "prefix_range" does not exist
> ...
>> I have imported the 'prefix_range' operator class and type definitions
>> into all three databases in play:  (1) dmm1, (2) dmm2, and (3) the
>> bucardo meta-database itself.  Yet I still get this error.  Any
>> suggestions on how to figure out from where exactly it is that the
>> 'prefix_range' definition is missing, and what to do about it?
>
> The error above is for the source databases: those that have a
> bucardo.bucardo_delta table. So that's the one that need to have
> the custom type installed. Make sure your search paths are set up
> so the user connecting (usually 'bucardo') can see those types.
>

Yeah, I figured it out;  the definition was not being imported into the 
'bucardo' schema of the database.  Once I set the search_path correctly, 
all was fine.

I did run into an additional problem, though:

[Sun Nov 21 00:58:00 2010] KID Kid 6863 exiting at cleanup_kid. Sync
"dmm_sync", Target "dmm_rem" public.ds_map
Reason: DBD::Pg::st execute failed: ERROR: cannot cast type
public.prefix_range to prefix_range LINE 5: ... t.customer_id::int4 =
d.rowid2::int4 AND t.prefix::prefix_r...  ^ at
/usr/local/share/perl/5.10.1/Bucardo.pm line 4717.  main error: none
source error: 7 target error: none States: ?/42846/? Line: 4717

It seems like Bucardo was trying to cast the prefix_range type in the 
public schema to the one in the bucardo schema, without realising they 
are the same type.  The 'prefix' package does not provide a cast 
operator for that, so I solved the problem with this ugly hack:

CREATE OR REPLACE FUNCTION prefix_range_idempotent(public.prefix_range)
RETURNS prefix_range
AS $$
   BEGIN
     RETURN $1;
   END
$$ LANGUAGE 'plpgsql';

CREATE CAST (public.prefix_range AS prefix_range) WITH FUNCTION 
prefix_range_idempotent(public.prefix_range);

Suggestions on more elegant solutions appreciated.  :-)

-- 
Alex Balashov - Principal
Evariste Systems LLC
1170 Peachtree Street NE
12th Floor, Suite 1200
Atlanta, GA 30309
Tel: +1-678-954-0670
Fax: +1-404-961-1892
Web: http://www.evaristesys.com/