Versions Compared

Old Version 10

changes.mady.by.user Kalpana Malladi

Saved on

compared with

New Version 11

changes.mady.by.user Kalpana Malladi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel6
outlinefalse
stylenone
typelist
printabletrue

FAQ for the PostgreSQL JDBC Driver Upgrade

Postgres

  • The same minor changes apply as for AlloyDB, regarding printed value types and typo fixes.

  • Adjustments to the third listed change in the doc (regarding the scenario of “


    • Screenshot 2024-05-13 at 2.39.51 PM.pngImage Added

    • All scenarios affected by this change are going to have errors roughly in that format - Batch entry 0 <query> was aborted: ERROR: <scenario-specific message> Position: <number> Call getNextException to see other errors in the batch., <error code and SQL state info>, next exception: <same error description and position as before>

    • Scenarios that could see these kinds of error message changes include (but are not limited to):

      • When the input value provided for a column is invalid for the column type.

      • When performing a delete that references a non-existent column.

      • When using an operator that does not exist or does not match given argument types.

      • When the query references some relation (schema, table, etc.) that does not exist.

      • When the query references a column that doesn’t exist

What is happening?

We plan to upgrade the JDBC driver for the PostgreSQL Snap Pack from v9.4.1207 to v42.7.2 (Java 8). This upgrade will be part of the GA release on August 14, 2024 (Stable release). As a part of this upgrade, the 42.7.2 JDBC driver is bundled with the PostgreSQL Snap Pack as the default JDBC driver.

...

Use the postgres 9.4.1207.jar from v9.4.1207 to maintain the same behavior as before the driver upgrade.

Behavior Changes

...

  • Changes in the query in the error messages:

    • Some error messages will now include the data type for certain values, for example:

      • Old: "...where (badId = 1) was aborted:..."

      • New: "...where (badId = ('1'::numeric)) was aborted:..."

  • Improvisations in error messages:

    • There are some minor wording changes to some error messages, for example:

      • Old: "Hint: No operator matches the given name and argument types."

      • New: "Hint: No operator matches the given name and argument type(s)."

The following table lists the additional behavior changes in the Snap Pack:

PostgreSQL Snaps

Scenario

Error/Output from the old (v9.4.1207) JDBC Driver

Error/Output from the new (v42.7.2 (Java 8)) JDBC Driver

PostgreSQL-Execute

When you provide multiple statements in the SQL Statement field, the Snap fails.

Status: 0

Output message: Success

Status: -3

Error message: Batch operation failed

PostgreSQL-Select

PostgreSQL-Lookup

PostgreSQL-Execute

Table columns of the Array type now output the data as a list instead of a string.

Output as a string:

{array_col_value={a, b, c}} 

Output message is now in an array:

{array_col_value=[a, b, c]}

PostgreSQL-Delete

PostgreSQL-Execute

PostgreSQL-Insert

PostgreSQL-Update

When the input value provided for a column is invalid for the column type.

Error Message: SQL operation failed

Reason: ERROR: invalid input syntax for type "DataType": "Invalid input" Position: <number>, error code: 0, SQL state: 22007

 

Error Message: Batch operation failed

Reason: Batch entry 0 <query> was aborted: ERROR: invalid input syntax for type 'Col DataType' : "invalid input" Position: <number> Call getNextException to see other errors in the batch., error code: 0, SQL state: 22007, next exception: ERROR: invalid input syntax for type 'Col DataType': "invalid input" Position: <number>

PostgreSQL-Select

 

When the metadata is returned from the Snap’s second output view.

The original number of columns in the input data is displayed in the output.

In Select Snap, the second output view, some new columns are introduced.
For example, IS_GENERATEDCOLUMN , REF_GENERATION, SELF_REFERENCING_COL_NAME, TYPE_CAT, TYPE_NAME, TYPE_SCHEM

PostgreSQL-Delete

 

When the delete condition has an unterminated quote string.

Error message: SQL operation failed
Reason : ERROR: unterminated quoted string at or near "' )"\n Position: <number>, error code: 0, SQL state: 42601

Error message: SQL operation failed
Reason: "SQL [<query>]; Unterminated string literal started at position <number> in SQL <query>. Expected char"

PostgreSQL-Execute

When you provide the following query:

"create table <name> as <query>"

Status : 0
Message : Success

Status: 5
The <number> for Status indicates the number of rows used from the source to create the new table.
Message: Success

All PostgreSQL Snaps

When the Dynamic Account has incorrect credentials.

Error : SQL operation failed
Reason : "java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true, Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true"

Error message: SQL operation failed
Reason : "Failed to retrieve a database connection."

...