Hi Pat, You are right: COPY operation, or more formally PQputCopyData() of the PostgreSQL API, returns error only for transmission issues and not for parsing ones. This is documented on a recent pretty heated thread on the pgsql-hackers mailing-list (to me, the following is the key message of that thread):
http://www.postgresql.org/message-id/[email protected] In a recent conversation with Pierre-Yves Maunier about PosgreSQL and COPY, we pretty much concluded it would be nice to add support for multi-values INSERT statements: such support exists for the MySQL plugin but not for the PostgreSQL one since back in times the COPY approach was the only option available. I have this item on my todo list but with no specific priority attached to it. Essentially, if you or anybody else reading is interested into this and thinks it deserves more priority than "nice to have", this is the time to speak. Cheers, Paolo On Tue, Jul 22, 2014 at 03:29:19PM -0700, THE MIGHTY VEXORG wrote: > Hello, > While debugging an issue with nfacctd (I am running nfacctd 1.5.0rc3) I > noticed that the pgsql debug messages are incomplete as they don't show > when a transaction fails. An example: > > Jul 22 12:05:01 INFO ( flows/pgsql ): *** Purging cache - START (PID: > 25911) *** > Jul 22 12:05:01 DEBUG ( flows/pgsql ): COPY acct_bgp (stamp_updated, > stamp_inserted, as_src, as_dst, as_path, peer_ip_src, peer_ip_dst, > packets, bytes) FROM STDIN DELIMITER ',' > Jul 22 12:05:01 DEBUG ( flows/pgsql ): 2014-07-22 12:05:01,2014-07-22 > 12:00:00,0,0,,64.230.15.243,64.230.200.186,2282,3292100 > Jul 22 12:05:01 DEBUG ( flows/pgsql ): 2014-07-22 12:05:01,2014-07-22 > 12:00:00,0,0,,64.230.15.243,64.230.193.172,3533,5096589 > <snip> > Jul 22 12:05:01 DEBUG ( flows/pgsql ): 2014-07-22 12:05:01,2014-07-22 > 12:00:00,2906,5690,5690 5690 5690 5690 5690 5690 5690 5690 5690 5690 > 5690 5690 5690,64.230.15.243,64.230.194.232,1591,2045887 > Jul 22 12:05:01 INFO ( flows/pgsql ): *** Purging cache - END (PID: > 25911, QN: 874, ET: 0) *** > > and from Postgres logs: > > 2014-07-22 12:05:01 EDT ERROR: value too long for type character(50) > 2014-07-22 12:05:01 EDT CONTEXT: COPY acct_bgp, line 559, column > as_path: "5690 5690 5690 5690 5690 5690 5690 5690 5690 5690 5690 5690 5690" > 2014-07-22 12:05:01 EDT STATEMENT: COPY acct_bgp (stamp_updated, > stamp_inserted, as_src, as_dst, as_path, peer_ip_src, peer_ip_dst, > packets, bytes) FROM STDIN DELIMITER ',' > > I fixed the problem by altering the comms and as_path columns to > character_varying but it took me a while to think to check the Postgres > logs as the nfacctd logs for pgsql inserts look the same whether the > insert is successful or fails. Is there a configuration directive for > nfacctd that I am missing that would show these failures in the nfacctd > debug log? > > Pat > > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
