{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"1"},"maxLevel":{"value":"6"},"outline":{"value":"false"},"style":{"value":"disc"},"type":{"value":"list"},"printable":{"value":"true"}},"macroMetadata":{"macroId":{"value":"5ec47238-e592-46bf-8fa7-791631ec8a34"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"0c117b75-b8d4-4c7b-8825-5a73d7214814"}},{"type":"heading","attrs":{"level":1},"content":[{"text":"FAQs for the PostgreSQL JDBC Driver Upgrade","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"What is happening?","type":"text"}]},{"type":"paragraph","content":[{"text":"We have upgraded the JDBC driver for the","type":"text"},{"text":" PostgreSQL Snap Pack ","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438096"}}]},{"text":"from v9.4.1207 to ","type":"text"},{"text":"v42.7.2 (Java 8)","type":"text","marks":[{"type":"link","attrs":{"href":"https://jdbc.postgresql.org/download/"}}]},{"text":". This upgrade will be part of the GA release on August 14, 2024 (","type":"text"},{"text":"Stable release","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/336626088#SnapLogicReleaseProcess-SnapPackDistributionRules"}}]},{"text":"). As a part of this upgrade, the 42.7.2 JDBC driver is bundled with the PostgreSQL Snap Pack as the default JDBC driver.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Why are we upgrading the PostgreSQL driver?","type":"text"}]},{"type":"paragraph","content":[{"text":"To improve security, performance, stability, and reliability.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"What is the timeline for this upgrade?","type":"text"}]},{"type":"paragraph","content":[{"text":"This upgrade is available with the Snap Pack version ","type":"text"},{"text":"437patches27172","type":"text","marks":[{"type":"code"}]},{"text":" for PostgreSQL. This driver upgrade will be part of the GA release on August 14, 2024 (","type":"text"},{"text":"Stable release","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/336626088#SnapLogicReleaseProcess-SnapPackDistributionRules"}}]},{"text":").","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Will I be impacted?","type":"text"}]},{"type":"paragraph","content":[{"text":"Yes, you will be impacted if you use the default version. The impact might be minimal if you use the latest driver or a higher version than the v42.7.2 (Java 8) JDBC driver.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To check the version of the current JAR file that you are using, refer to ","type":"text"},{"text":"PostgreSQL's latest versions","type":"text","marks":[{"type":"link","attrs":{"href":"https://jdbc.postgresql.org/download/"}}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To verify the JAR file that is in use, follow these steps:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Open the JAR file using either a ZIP utility or a JAR viewer tool.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Look for the ","type":"text"},{"text":"META-INF/MANIFEST.MF","type":"text","marks":[{"type":"code"}]},{"text":" file in the JAR and check the version.","type":"text"}]}]}]}]}]},{"type":"panel","attrs":{"panelType":"note"},"content":[{"type":"paragraph","content":[{"text":"If you provide your own custom driver you will not be impacted.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Do I need to reconfigure my accounts or drivers?","type":"text"}]},{"type":"paragraph","content":[{"text":"No, you do not have to reconfigure your account settings or drivers; the account can remain as-is to work with the latest driver. However, if you currently use the default version and want to revert to the previous version behavior, we recommend you use the ","type":"text"},{"text":"postgres 9.4.1207.jar","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]},{"type":"panel","attrs":{"panelType":"note"},"content":[{"type":"paragraph","content":[{"text":"We recommend you investigate your pipelines and check the Snaps downstream of the PostgreSQL Snaps for any of the affected scenarios. Update any dependencies that the downstream Snaps might have with respect to the changing error and outputs (especially in expressions) to conform to the new expected errors and outputs. Learn more about the change in ","type":"text"},{"text":"errors and outputs for the new (v42.7.2) JDBC driver","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/edit-v2/3153789126#Behavior-Changes"}}]},{"text":".","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"How can I verify that the changes work?","type":"text"}]},{"type":"paragraph","content":[{"text":"In a production Org","type":"text","marks":[{"type":"strong"}]}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you have a production Org to validate connections to your PostgreSQL instances, upload a PostgreSQL JDBC driver JAR compatible with the ","type":"text"},{"text":"postgres 9.4.1207.jar","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Upload that JAR file to your Org’s shared folder and specify it in the ","type":"text"},{"text":"PostgreSQL Account","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/855835119"}}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Validate the account.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"After you verify that the updated JDBC driver works, remove it.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"How do you override the behavior changes?","type":"text"}]},{"type":"paragraph","content":[{"text":"Use the ","type":"text"},{"text":"postgres 9.4.1207.jar","type":"text","marks":[{"type":"code"}]},{"text":" from v9.4.1207 to maintain the same behavior as before the driver upgrade.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Behavior Changes for the PostgreSQL JDBC Driver Upgrade","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Changes in the query in the error messages:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Some error messages now include the data type for specific values, for example:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Old: ","type":"text"},{"text":"\"...where (badId = 1) was aborted:...\"","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"New: ","type":"text"},{"text":"\"...where (badId = ('1'::numeric)) was aborted:...\"","type":"text","marks":[{"type":"code"}]}]}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Improvements in text for few of the error messages, for example:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Old","type":"text"},{"text":": ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#36b37e"}}]},{"text":"\"Hint: No operator matches the given name and argument types.\"","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"New","type":"text"},{"text":": ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#36b37e"}}]},{"text":"\"Hint: No operator matches the given name and argument type(s).\"","type":"text","marks":[{"type":"code"}]}]}]}]}]}]},{"type":"paragraph","content":[{"text":"The following table lists the additional behavior changes in the Snap Pack:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"b26cec03-242b-443e-80cf-49f45483b688"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL Snaps","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"Scenario","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Error/Output from the old JDBC Driver v9.4.1207","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f4f5f7","rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Error/Output from the new JDBC Driver v42.7.2 (Java 8)","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Execute","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438884"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"When you provide multiple statements in the ","type":"text"},{"text":"SQL Statement ","type":"text","marks":[{"type":"strong"}]},{"text":"field, the Snap fails.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Status:","type":"text"},{"text":" 0","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"Output message: ","type":"text"},{"text":"Success","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Status: ","type":"text"},{"text":"-3","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"Error message: ","type":"text"},{"text":"Batch operation failed","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Select","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438970"}}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL-Lookup","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438928"}}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL-Execute","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438884"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"Table columns of the ","type":"text"},{"text":"Array ","type":"text","marks":[{"type":"code"}]},{"text":"type now output the data as a list instead of a string.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Output as a string: ","type":"text"}]},{"type":"paragraph","content":[{"text":"{array_col_value={a, b, c}} ","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Output message is now in an array: ","type":"text"}]},{"type":"paragraph","content":[{"text":"{array_col_value=[a, b, c]}","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Delete","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438845"}}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL-Execute","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438884"}}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL-Insert","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438880"}}]}]},{"type":"paragraph","content":[{"text":"PostgreSQL-Update","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438937"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"The various invalid SQL operation scenarios that result in failure include (but are not limited to) the following:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When the input value you provide for a column is invalid for the column type.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When you perform a delete operation that references a non-existent column.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When you use an operator that does not exist or does not match the specific argument types.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When the query references some relation (schema, table, etc.) that does not exist.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When the query references a column that does not exist.","type":"text"}]}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Error: ","type":"text"},{"text":"SQL operation failed","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"Reason: ","type":"text"},{"text":"ERROR: Position: , ","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"For example: ","type":"text"},{"text":"column \"unknown\" of relation \"insertTests\" does not exist Position: 55, error code: 0, SQL state: 42703","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Error: ","type":"text"},{"text":"Batch operation failed","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"Batch entry 0 was aborted: ERROR: Position: Call getNextException to see other errors in the batch., , next exception: ERROR: ","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"For example: ","type":"text"},{"text":"Batch entry 0 insert into \"snapteam1\".\"insertTests\" (\"id\", \"c_int\", \"unknown\") values (('1'::int4), ('1'::int4), ('99'::numeric)) was aborted: ERROR: column \"unknown\" of relation \"insertTests\" does not exist Position: 55 Call getNextException to see other errors in the batch., error code: 0, SQL state: 42703, next exception: ERROR: column \"unknown\" of relation \"insertTests\" does not exist Position: 55","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Select","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438970"}}]}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"When the metadata is returned from the Snap’s second output view.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"The original number of columns in the input data is displayed in the output.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"In the Select Snap, the second output view, some new columns are introduced,","type":"text"},{"type":"hardBreak"},{"text":"for example:","type":"text"}]},{"type":"paragraph","content":[{"text":"IS_GENERATEDCOLUMN , REF_GENERATION, SELF_REFERENCING_COL_NAME, TYPE_CAT, TYPE_NAME, TYPE_SCHEM","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Delete","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438845"}}]}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"When the delete condition has an unterminated quote string.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Error message: ","type":"text"},{"text":"SQL operation failed","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"Reason : ","type":"text"},{"text":"ERROR: unterminated quoted string at or near \"' )\"\\n Position: , error code: 0, SQL state: 42601","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Error message: ","type":"text"},{"text":"SQL operation failed","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"Reason: ","type":"text"},{"text":"\"SQL []; Unterminated string literal started at position in SQL . Expected char\"","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL-Execute","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1438884"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"When you provide the following query:","type":"text"}]},{"type":"paragraph","content":[{"text":"\"create table as \"","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Status : 0","type":"text"},{"type":"hardBreak"},{"text":"Message : ","type":"text"},{"text":"Success","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Status: 5 ","type":"text"},{"type":"hardBreak"},{"text":"The for Status indicates the number of rows used from the source to create the new table.","type":"text"},{"type":"hardBreak"},{"text":"Message: ","type":"text"},{"text":"Success","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[252.0]},"content":[{"type":"paragraph","content":[{"text":"All PostgreSQL Snaps","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[323.0]},"content":[{"type":"paragraph","content":[{"text":"When the Dynamic Account has incorrect credentials.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[492.0]},"content":[{"type":"paragraph","content":[{"text":"Error : ","type":"text"},{"text":"SQL operation failed","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"Reason : ","type":"text"},{"text":"\"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\"","type":"text","marks":[{"type":"code"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[733.0]},"content":[{"type":"paragraph","content":[{"text":"Error message: ","type":"text"},{"text":"SQL operation failed","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"Reason : ","type":"text"},{"text":"\"Failed to retrieve a database connection.\"","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"}]}]}]}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]}],"version":1}