Re: [HACKERS] PQputCopyData dont signal error
Thanks Robert, I'm already there. Obviously I'm the only one in the room that didn't get the memo. I've had some time to reflect on what might be done differently, just not any time to try it. If I get it to work I'll let everyone know. The code I was working with went away when the Network admins pushed something that forced me to reboot and close all my temp file windows last Friday. Sorry for any troubles I've caused you all and I didn't mean to put everyone on the defensive. It has occurred to me that I may have been examining the wrong results set. One of the things you mentioned is "I often find it necessary to refer to existing examples of code when trying to figure out how to do things correctly". I couldn't agree more. Haven't seen one yet, but found plenty of discussion that tap danced around one or more of the components of the copy, put, end paradigm. Maybe I should have just asked for a sample code snippet but didn't after a day or so of frustration and trying to piece together other people's incomplete samples. It seems that none of the discussion threads I looked at (doesn't mean there aren't any - before everyone gets worked up) where people were having similar questions also never offered a working solution. So I don't know if those folks gave up or figured it out on their own. In the end it comes down to how much time do you have to google, read through a thread, find out that discussion thread really has nothing to do with your topic of interest, repeat, finally try something different, repeat? Again, my apologies for lighting a fire under everyone. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798202.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
David, The code I posted is what I started/finished with. As I tried to figure things out I kept adding PQgetResult, PQresultStatus and PQresultErrorMessage calls and writing the results to the log file. By the time I was done it was so messy that I stripped all that messaging/logging back out and made sure it ran with good data and moved on to something else because I couldn't waste any more time on it and figured I'd look it over again if the insert with multiple values clauses was a bust too (which thankfully it wasn't). When I got the multiple values clause insert replacements going and compared processing times and error handling I dumped all the old debugging version stuff because that's not the kind of thing you commit to version control. In the end I didn't think it mattered because I wasn't going to use any of it since I had no way to know if bad data actually didn't get written by virtue of its being bad data. And no return code ever indicated anything was awry. I'd love to see an actual working example where an executing C++ program was able to in fact determine that copy data containing bad data that was sent by the C++ program was rejected by the server and subsequently the C++ program was able to raise/log/notify specifically which block of data failed and then log information about it. However, all I ever got was PGRES_COMMAND_OK whether or not the data that was sent was rejected as containing bad data or not. Effectively these were silent failures. Sorry I can't provide more information but I do appreciate your time. If you can't get any further with it I understand and don't expect another reply. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798104.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
I started with this: DBInsert_excerpts6_test_cpdlc.cpp <http://postgresql.1045698.n5.nabble.com/file/n5798049/DBInsert_excerpts6_test_cpdlc.cpp> Due to a cut and paste error I was originally querying the wrong sequence which led to me wondering why data wasn't in the table that the log reported as having successfully been written. After I fixed the sequence query error and got done laughing at myself for being so ignorant I realized I better see what happened if there was actually bad data and not just user stupidity. The data is in this log file excerpt.raptor_efb2_excerpts.out <http://postgresql.1045698.n5.nabble.com/file/n5798049/raptor_efb2_excerpts.out> This excerpt is from a run that copied as expected with perfect data. The test was to modify line 36 in the code excerpt from: << "|" << dataRec.getMinute() to: << "|" << dataRec.getMinute() << "a" which put alphabetic data into the data which should have been rejected as nonnumeric. No error was raised and the log indicated that there should be 20 rows of data in the cpdlc table except there weren't. By the time I gave up on this I had so many friggin log statements in these 2 methods that there was more logging than actual useful code. Results never changed though. No errors and no messages when I appended the "a" onto numeric data destined for a numeric field. This was naturally unsettling so I turned to message boards to see if anyone had similar experiences or knew a work around or better way. I'm taking the time to give you all this information because I want to learn from it despite the potential for acute public embarassment. Maybe others will get something too. It's how we all get ahead. At any rate, thanks for your time and I look forward to what you find. Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798049.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
Hi Alvaro, Thanks for the prompt response. PGLoader looks like an awesome project and I especially liked this part: /"Handling PostgreSQL errors Some data will get rejected by PostgreSQL, even after being carefully prepared by the transformation functions you can attach to pgloader. Then pgloader parses the PostgreSQL CONTEXT error message that contains the line number in the batch of where the error did happen. It's then easy enough to *resend the all the rows from the batch that are located before the error, skip and log as rejected the faulty row, and continue*, handling eventual next errors the same way. /" I didn't see anything in the documentation about binary files and that is unfortunately the only thing I have for input currently. They used binary files because that was the fastest way to write the data for each of the frames in the sim without falling out of realtime. We're trying to bring some parts of this project more up to date with the ultimate goal of being able to write directly to the database itself without falling out of realtime and developing a dashboard for researchers to monitor during experiments and simulation runs. Thanks again for the suggestion and I'll definitely keep PGLoader in mind as things unfold here on this project. Best Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798038.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
Am I to understand then that I should expect no error feedback if copy fails because of something like attempting to insert alphabetic into a numeric? I apologize for my ignorance, but all my return codes were always successful (PGRES_COMMAND_OK) even if nothing was copied due to garbage data. Also, calling PQgetResult never returned any information either because everything was always PGRES_COMMAND_OK. If that's what is supposed to happen then I have completely missed the boat and apologize for wasting everyone's time. The exact same garbage data test returned specific error related information if the copy is done with the same sql query from the command line. This is what I was trying to get to happen with the embedded sql so that in the event of bad data, you could stop and determine the source of the bad data, and not go on assuming you were successfully copying only to find later that 1000 recs here and a 1000 records there were never inserted/copied and to have no idea or log messages as to why. In a sense, I have made this work just fine. It compiled, ran, and copied data - as long as all the data was perfect. I was trying set up error handling to notify in case there was invalid data contained within the records being copied. Again, I apologize if I was unclear and further apologize for irritating everyone that has replied thus far. Thank you all for your time and best regards. I am examining other ways to do mass inserts/writes that allow for notification if some of the data contained within for some reason fails to copy/insert so that the cause of the bad data can be examined and remedied as soon as it occurs as well as writing the offending data to a log so that not all of it is lost. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798032.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
<http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_man_excerpt.png> These were my results: <http://postgresql.1045698.n5.nabble.com/file/n5798002/PG_embedded_copy_log_excerpt.png> I'd advise anyone contemplating using this feature to seriously seriously seriously test this and examine your logs after each test run before you move this feature into your baseline. Maybe you'll have better luck than I did. For what it's worth I got very good performance results from using INSERT with multiple values clauses that inserted 1000 records at a time. For example on one error test (of many) purposefully attempting to insert alphabetic data into a numeric field yielded explicit, correct information about the exact line of data causing the error within the 1000 records attempting to be inserted. With this information in hand it would be eminently feasible to go back to the baseline and examine any recent source code updates that might have altered the generation of the data that caused an error like this. Hopefully this helps anyone trying to handle large amounts of data quickly and wondering what a viable solution might be. Best regards to everyone and thank you all for your time, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5798002.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQputCopyData dont signal error
I realize this is an old thread, but seems to be the only discussion I can find on this topic "I have a problem with PQputCopyData function. It doesn't signal some error. " I am using from within a c++ program: PQexec(m_pConn, "COPY... ...FROM stdin"), followed by PQputCopyData(m_pConn, ssQuery.str().c_str(), ssQuery.str().length()), finished with PQputCopyEnd(m_pConn, NULL). Everything that the gentleman that started this thread discussed is still in force in PostGres 9.3. Specifically that if some data anomaly within the data being copied causes the copy to fail, there is no notice, no return code change, and no way to know if this has happened until you examine the actual table being copied to itself and see that in fact the copy didn't work. Does anyone know of a way to find out if a copy went wrong so an error can be posted and the condition noted without having to check the table and count the records and compare the actual count against how many were expected to present after the copy. When one uses copy from the command line you get very useful feedback about the quality of your data such as: postgres=# COPY dr_cpdlc(id_pk, fk_guid_machine_run_info_lk, sim_time, wall_time,... Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 988|4789|1394686027.05|1394686027.049000|ASTOR|RECV|ATOS|NASA02|4|47a|7... >> \. ERROR: invalid input syntax for integer: "47a" CONTEXT: COPY dr_cpdlc, line 1, column minute_of_the_time: "47a" postgres=# Does such error reporting functionality exist for using copy from within a c++ program? Does anyone have any ideas or know if this is being worked on? Am I completely missing something obvious here? If not I feel this is a feature shortcoming that could hopefully be addressed if it hasn't already. It prevents PostGres copy from being reliable for use when writing data during a live simulation run. Imagine getting approximately 100 or so people together including Air Traffic Controllers, Pilots, Researchers, Radio operators, and various other support staff. They spend all day (or days) running through various scenarios which generate say 20 frames a second of data for say a few hundred simulated aircraft plus all the simulated radio traffic and other com related data. After all these people have flown back to where they came from you notice some missing data. ---OOPs! :) Now what? What do you say to these people that put this experiment together?? Sorry? During these simulation runs, things break since each new scenario is testing a new hypothesis, and people expect to have to stop, recalibrate, tune/tweak code/settings, etc before the final run and actual data gathering. A data anomaly suddenly occurring would be a situation that all parties would want to resolve before going further. If you could capture this event (copy failing) and write it to a log, the source of the bad data could be addressed before going further. As things stand currently the vast reams of data might easily hide the fact that a few thousand or 10,000 records were missing until it was too late to do a rerun. Effectively, this precludes the use of PostGres in support of harvesting data during a simulation run unless someone knows a quicker way to jam data into a set of relational tables without falling out of realtime. Sorry to be so negative but I find it amazing that an rdbms engine as robust as PostGres seems to have this gaping hole in its capabilities and potential utilization. Coming from an Oracle world I was delighted to see the full functionality offered by PostGres that seems just as good and perhaps better (no listeners to troubleshoot, no pfile vs. spfile interactions from unusual shutdowns etc...) I haven't tried using inserts yet with multiple "values" clauses but have read this is possible. However, once the planner gets involved, speed drops noticeably. Thanks to everyone for their time or any feedback. Regards, Steve K. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-tp4302340p5797826.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
