[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 (
('','13701'))
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
www.computerisms.ca
More information about the Bucardo-general
mailing list