Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Minor |
| Complexity | Unknown |
| Component | Tools - Table Data Editor |
| Version | 15.0 |
Table Data Editor on PostgreSQL should check "show standard_conforming_strings" in ConnectionProperties.requiresEscapeBackslash().
You can use the "show standard_conforming_strings" or "set standard_conforming_strings = on"
From the PostgreSQL documentation :
http://www.postgresql.org/docs/current/static/runtime-config-compatible.html
This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application desires backslashes to be treated as escape characters.
standard_conforming_strings property is now retrieve from the database and used to determine if backslash will get escaped prior to been sent to the database in string values.
I would like to state that the value of standard_conforming_strings can be set at a session. If you set the value at session level and do an execute edit on a query, the table data editor will either use the same session, if connection pooling is set on, or create a new session. If connection pooling is not on and then the value from database will return the configured default database value which is set in the postgresql.conf file.
@ivan: This is the behavior I see
Case 1: Under Server Registration- > Edit Server Properties - > Scripts tab if you enter set standard_conforming_strings = off and Save, it works fine in the Table Data Editor.
Case 2: However, if you have set connection pooling = ON, open a query window and enter set standard_conforming_strings = off and then use Execute Edit in the Query window, the \\ won't appear because we do not share the session. Table Data Editor creates a new session and takes the default standard_conforming_strings which is ON. In this case we need to either update postgresql.conf or we need to use the Server Registration Scripts tab to set it to ON/OFF.
Is this the correct behavior for Case 2 ?
@ivan: This is the behavior I see
Case 1: Under Server Registration- > Edit Server Properties - > Scripts tab if you enter set standard_conforming_strings = off and Save, it works fine in the Table Data Editor.
Case 2: However, if you have set connection pooling = ON, open a query window and enter set standard_conforming_strings = off and then use Execute Edit in the Query window, the \\ won't appear because we do not share the session. Table Data Editor creates a new session and takes the default standard_conforming_strings which is ON. In this case we need to either update postgresql.conf or we need to use the Server Registration Scripts tab to set it to ON/OFF.
Is this the correct behavior for Case 2 ?
@ivan : Instead of using the command "show standard_conforming_strings", you used the query "select setting from pg_settings where name = 'standard_conforming_strings'. The table name is ambiguous. When querying system catalog tables you need to be explicit, so you should use "pg_catalog.pg_settings". Otherwise it could lead to incorrect results.
@tariq : I see a question below that you made, but don't see an answer. Then you resolved the issue. Can you or Ivan clarify on your question?
Otherwise, the implementation works well.
@ivan : Instead of using the command "show standard_conforming_strings", you used the query "select setting from pg_settings where name = 'standard_conforming_strings'. The table name is ambiguous. When querying system catalog tables you need to be explicit, so you should use "pg_catalog.pg_settings". Otherwise it could lead to incorrect results.
@tariq : I see a question below that you made, but don't see an answer. Then you resolved the issue. Can you or Ivan clarify on your question?
Otherwise, the implementation works well.
@niels: I've qualified the system catalog view with pg_catalog owner.
@tariq: Using connection pooling will not work but the user can set the Server Registration to run the script in the scripts tab and that will work for them. This would be in the case the user wishes to change the value only for his work instead of server wide in the postgresql.conf file.
@niels: I've qualified the system catalog view with pg_catalog owner.
@tariq: Using connection pooling will not work but the user can set the Server Registration to run the script in the scripts tab and that will work for them. This would be in the case the user wishes to change the value only for his work instead of server wide in the postgresql.conf file.
Verified the implementation and the SQL Query in ADS v15.0.14-6 1 more time.
Verified the implementation and the SQL Query in ADS v15.0.14-6 1 more time.
Issue #13085 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build v15.0.14-4, v16.0.0-beta-6 |
| No time estimate |
4 issue links |
relates to #13087
Issue #13087Table Data Editor on PostgreSQL type databases may need to check "show standard_conforming_strings" in ConnectionProperties.requiresEscapeBackslash() |
relates to #13116
Issue #13116Importing data to new table in PostgreSQL will open many new connections |
relates to #13810
Issue #13810Export tool for PostgreSQL is not escaping "\" in strings |
breaks #13116
Issue #13116Importing data to new table in PostgreSQL will open many new connections |
standard_conforming_strings property is now retrieve from the database and used to determine if backslash will get escaped prior to been sent to the database in string values.
I would like to state that the value of standard_conforming_strings can be set at a session. If you set the value at session level and do an execute edit on a query, the table data editor will either use the same session, if connection pooling is set on, or create a new session. If connection pooling is not on and then the value from database will return the configured default database value which is set in the postgresql.conf file.