[Bucardo-general] empty integer values

Computerisms Corporation bob at computerisms.ca
Tue Aug 10 21:30:08 UTC 2021

Hi List,

I have discovered an issue with my bucardo master-master replication. 
It appears that one of the tables that is being synced is using two 
bigint columns, and the design of the table is such that only one of the 
columns has a value on any given row.  IE if column 1 is populated, 
column 2 has an empty string.

Enter Bucardo trying to do a delete statement like the following (pulled 
from the postgres logs):

DELETE FROM public.grants WHERE ("by_collection","to_principal") IN ( 

and I get an error:

ERROR:  invalid input syntax for integer: ""

in chasing this down, it seems that postgres cannot do a statement where 
a set of empty quotes can represent an empty value for any kind of 
integer datatype.  The best suggestion on the web is to change the data 
type for the given column to text, which I tried to do.  Sadly, this 
moves me into an extremely slippery and complicated slope of updating 
data types throughout the database due to foreign key restrictions.

I am thinking I can't be the first bucardo user to encounter this 
problem; I am wondering if anyone has found a way to deal with this from 
the bucardo side, rather than having to restructure the whole database?

Bob Miller
Cell: 867-334-7117
Office: 867-633-3760
Office: 867-322-0362

More information about the Bucardo-general mailing list