Re: [HACKERS] PQputCopyData dont signal error

2014-03-29 Thread steve k
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


Re: [HACKERS] PQputCopyData dont signal error

2014-03-31 Thread steve k
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

2014-03-31 Thread steve k
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

2014-03-31 Thread steve k
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

2014-03-31 Thread steve k
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

2014-03-31 Thread steve k
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

2014-04-01 Thread steve k
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