Re: PQputCopyData dont signal error

From: steve k <steven(dot)c(dot)kohler(at)nasa(dot)gov>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: PQputCopyData dont signal error
Date: 2014-03-28 17:49:45
Message-ID: 1396028985427-5797826.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.050000|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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2014-03-28 17:51:50 Re: Doing better at HINTing an appropriate column within errorMissingColumn()
Previous Message Tom Lane 2014-03-28 16:56:20 Re: Store data in pg_toast for custom type fails (bug?)