CopyManager throws wrong exception when setting DateStyle

[apetrelli]

When trying to set DateStyle before a COPY operation through CopyManager, the SQLExceptions that it throws are wrong.
You can try it with the following Maven-based project:
https://www.dropbox.com/s/663132bj1kolqv9/jdbc-bug-poc.zip
Using PG JDBC version 9.3-1101-jdbc41
Using PGSql server version 9.2.4

WrongQueryMain: executes a wrong query for a COPY.
WrongDataMain: executes a correct query but with wrong data.
The table to create is inside src/etc/sql/create.sql

The wrong exceptions are, in particular:

1. if the syntax of the query is wrong, it fails this way:

Exception in thread "main" org.postgresql.util.PSQLException: Database connection failed when starting copy
    at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:730)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:52)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:139)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongQueryMain.main(WrongQueryMain.java:29)
Caused by: java.io.IOException: Unexpected packet type during copy: 83
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1060)
    at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:728)
    ... 4 more

If the data is wrong it fails this way:

Exception in thread "main" org.postgresql.util.PSQLException: Database connection failed when canceling copy operation
    at org.postgresql.core.v3.QueryExecutorImpl.cancelCopy(QueryExecutorImpl.java:798)
    at org.postgresql.core.v3.CopyOperationImpl.cancelCopy(CopyOperationImpl.java:30)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:148)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongDataMain.main(WrongDataMain.java:30)
Caused by: java.io.IOException: Unexpected packet type during copy: 0
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1060)
    at org.postgresql.core.v3.QueryExecutorImpl.cancelCopy(QueryExecutorImpl.java:780)
    ... 4 more

[polobo]

So, what - specifically - do you want changed?

PSQLException is a sub-class of SQLException. That said the interface for copyIn(string, Reader) allows for an IOException so wrapping it in a PSQLException (and by extension an SQLException) could be considered incorrect; though that does not appear to be your complaint anyway.

Getting a pg-specific error from a pg-specific class is something where maybe you can quibble on semantics but given the class-inheritance I'd say you are getting the best of both worlds.

Maybe something other than an IOException should occur but if you agree that the queries are invalid the distinction between the two is irrelevant since you couldn't recover from either in any meaningful way other that rewriting the query.

I don't see changing this to IOException because it really is the higher-level protocol, and not the I/O itself, that is generating the error. And making it explicitly an SQLException seems pointless and possibly harmful since callers would no longer be able to use "getServerErrorMessage()" on the error.

[apetrelli]

As I stated in the title, the problem seems to arise when the DateStyle parameter is set, without it the exception messages are very clear. In fact, the problem is that:

1. it confuses a regular message for an unexpected one, because PGSql resets the DateStyle parameter and informs the client about it;
2. the message is not relevant and it is hard to debug the problem.

If the DateStyle parameter is not modified, the exceptions are very clear. For the first case (wrong query syntax):

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "xxx"
  Posizione: 34
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:966)
    at org.postgresql.core.v3.QueryExecutorImpl.startCopy(QueryExecutorImpl.java:728)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:52)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:139)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongQueryMain.main(WrongQueryMain.java:29)

For the second case:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "xxx1"
  Dove: COPY sample, line 1, column id: "xxx1"
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:966)
    at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:828)
    at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:59)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:145)
    at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:124)
    at sample.WrongDataMain.main(WrongDataMain.java:30)

[apetrelli]

Related pull request: #132

[apetrelli]

Great! Thank you!

[pblh123]

I meet a question,please help me fix it.

Sqoop command arguments :
export
--connect
jdbc:postgresql://ip/test
--username
{user}
--password
{pw}
--table
dealers_target2
--direct
--export-dir
/user/hive/warehouse/path
-m
1

12728 [main] INFO org.apache.hadoop.mapreduce.Job - Job job_1499303596818_281263 running in uber mode : false
12731 [main] INFO org.apache.hadoop.mapreduce.Job - map 0% reduce 0%
18820 [main] INFO org.apache.hadoop.mapreduce.Job - map 100% reduce 0%
18828 [main] INFO org.apache.hadoop.mapreduce.Job - Job job_1499303596818_281263 failed with state FAILED due to: Task failed task_1499303596818_281263_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0

18936 [main] INFO org.apache.hadoop.mapreduce.Job - Counters: 8
Job Counters
Failed map tasks=1
Launched map tasks=1
Data-local map tasks=1
Total time spent by all maps in occupied slots (ms)=14208
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=3552
Total vcore-seconds taken by all map tasks=3552
Total megabyte-seconds taken by all map tasks=14548992
18943 [main] INFO org.apache.sqoop.mapreduce.ExportJobBase - Transferred 0 bytes in 14.2587 seconds (0 bytes/sec)
18945 [main] INFO org.apache.sqoop.mapreduce.ExportJobBase - Exported 0 records.
18947 [main] ERROR org.apache.sqoop.tool.ExportTool - Error during export:

Error: java.io.IOException: org.postgresql.util.PSQLException: Tried to end inactive copy at org.apache.sqoop.mapreduce.postgresql.PostgreSQLCopyExportMapper.cleanup(PostgreSQLCopyExportMapper.java:145) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:148) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1783) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) Caused by: org.postgresql.util.PSQLException: Tried to end inactive copy at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:816) at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:61) at org.apache.sqoop.mapreduce.postgresql.PostgreSQLCopyExportMapper.cleanup(PostgreSQLCopyExportMapper.java:142) ... 9 more

[davecramer]

Not exactly sure what you want us to do with this. Very difficult to figure out what the problem is with the information provided

[Ts686]

@davecramer mr task , extract data from hive to pg , An error is as follows

What are the usual reasons