[Bucardo-general] empty integer values

[Computerisms Corporation]

Hi Greg,

Thank you for the reply, much appreciated.

The application in question is davical, and I don't have the direct 
create statement for this particular table.  However, maybe this will work:

davical=# \d grants
                                Table "public.grants"
     Column     |  Type   |                        Modifiers 

---------------+---------+----------------------------------------------------------
  by_principal  | bigint  |
  by_collection | bigint  |
  to_principal  | bigint  |
  privileges    | bit(24) |
  is_group      | boolean |
  repl_id       | integer | not null default 
nextval('grants_repl_id_seq'::regclass)
Indexes:
     "grants_pk1" UNIQUE, btree (by_principal, to_principal)
     "grants_pk2" UNIQUE, btree (by_collection, to_principal)
Foreign-key constraints:
     "grants_by_collection_fkey" FOREIGN KEY (by_collection) REFERENCES 
collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
     "grants_by_principal_fkey" FOREIGN KEY (by_principal) REFERENCES 
principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
     "grants_to_principal_fkey" FOREIGN KEY (to_principal) REFERENCES 
principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Triggers:
     bucardo_delta AFTER INSERT OR DELETE OR UPDATE ON grants FOR EACH 
ROW EXECUTE PROCEDURE bucardo.delta_public_grants()
     bucardo_kick_davical_sync AFTER INSERT OR DELETE OR UPDATE OR 
TRUNCATE ON grants FOR EACH STATEMENT EXECUTE PROCEDURE 
bucardo.bucardo_kick_davical_sync()
     bucardo_note_trunc_davical_sync AFTER TRUNCATE ON grants FOR EACH 
STATEMENT EXECUTE PROCEDURE bucardo.bucardo_note_truncation('davical_sync')
     grants_modified AFTER INSERT OR UPDATE ON grants FOR EACH ROW 
EXECUTE PROCEDURE grants_modified()



Some of Davical's tables do not have unique identifiers, so I put the 
repl_id column in on my own and it is present throughout most of the 
database tables. However, I don't think it is causing any problems.

The issue appears to be that by_principal and by_collection columns only 
populate one of the cells in any given row.  This creates the 
('','1234') scenario in some of the entries, which also fail when run 
directly on the cli in postgres.  I took the following line from the 
postgres logs and ran it with the following error:

davical=# DELETE FROM public.grants WHERE 
("by_collection","to_principal") IN ( 
('41835','105971'),('13606','8025'),('13606','13604'),('22938','34250'),('13606','41547'),('22938','41834'),('22938','105971'),('41549','41547'),('22938','32691'),('105981','41834'),('','13701'));
ERROR:  invalid input syntax for integer: ""
LINE 1: ...9','41547'),('22938','32691'),('105981','41834'),('','13701'...
                                                              ^
Any suggestions would be most welcome.



On 2021-08-11 6:52 a.m., Greg Sabino Mullane wrote:
> Column 2 cannot have an empty string if it is truly a bigint - can you 
> share the DDL (i.e. CREATE TABLE statement) for that table? Bucardo 
> should have no problem with bigint columns. Could be some sort of edge 
> case but seeing the exact table could help us narrow things down.
> 
> Cheers,
> Greg
>