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
_______________________________________________
Bucardo-general mailing list
[email protected]
https://bucardo.org/mailman/listinfo/bucardo-general
