GRANT WITH INHERIT ??
Hello. When enter this commands in psql : *sudo -u postgres psql CREATE GROUP arbolone_dev_group; // Create a group CREATE ROLE company_admin LOGIN PASSWORD 'myPassword' CREATEDB IN GROUP arbolone_dev_group CREATEROLE; GRANT arbolone_dev_group TO company_admin WITH INHERIT TRUE;* I get this error: /ERROR: syntax error at or near "INHERIT" LINE 1: GRANT arbolone_dev_group TO company_admin WITH INHERIT TRUE;/*/ /* Is there something I missed? -- */ArbolOne ™/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in progress [ í ]
Re: GRANT WITH INHERIT ??
Resolved. Thanks On 2024-12-06 7:40 a.m., Arbol One wrote: When enter this commands in psql : *sudo -u postgres psql CREATE GROUP arbolone_dev_group; // Create a group CREATE ROLE company_admin LOGIN PASSWORD 'myPassword' CREATEDB IN GROUP arbolone_dev_group CREATEROLE; GRANT arbolone_dev_group TO company_admin WITH INHERIT TRUE;* I get this error: /ERROR: syntax error at or near "INHERIT" LINE 1: GRANT arbolone_dev_group TO company_admin WITH INHERIT TRUE;/ -- */ArbolOne ™/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in progress [ í ]
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
On 12/6/24 13:19, Zacher, Stacy wrote: Hello: We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Foreign data wrappers that use tds_fdw for connections to 2 different SQL Servers. After the OS upgrade, one of the FDWs stopped working for some reason but the other one still works. The error message when trying to run the import statement in psql is as follows (I changed some of the names): using psql: I tested connecting to the server/port via ncat and it returns successfully. When I try sqlcmd or tsql, The 1477 port is static (vs. dynamic) [postgres@rh9server bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d Pdata_db -U PData_Reader -P xx Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746. Some searching mentions that this 'TCP Provider: Error code 0x2746.' can be related to lack of TLS 1.2 support. I don't see your tsql connection attempt. The FDW that is still working is going to a SQL Server v14.x 2017 server and the broken one is going to a SQL Server version 13 2016 server. Are the SQL Server instances running on the same machine? If not what versions of Windows are they using in each case? Also, I recreated the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) and it works there. So I'm puzzled here as to why it's not working on the upgraded server, yet the other FDW on the same server still works. Any help on this issue would be greatly appreciated. Thank you, Stacy szac...@mcw.edu -- Adrian Klaver adrian.kla...@aklaver.com
RE: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
Hi Adrian: Please see my replies below:** Thank you! Stacy -Original Message- From: Adrian Klaver Sent: Friday, December 6, 2024 3:51 PM To: Zacher, Stacy ; pgsql-general@lists.postgresql.org Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed ATTENTION: This email originated from a sender outside of MCW. Use caution when clicking on links or opening attachments. On 12/6/24 13:19, Zacher, Stacy wrote: > Hello: > > We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres > v13 server that has 2 Foreign data wrappers that use tds_fdw for > connections to 2 different SQL Servers. > > After the OS upgrade, one of the FDWs stopped working for some reason > but the other one still works. > > The error message when trying to run the import statement in psql is > as follows (I changed some of the names): > > using psql: > > I tested connecting to the server/port via ncat and it returns > successfully. > > When I try sqlcmd or tsql, The 1477 port is static (vs. dynamic) > > [postgres@rh9server bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d > Pdata_db -U PData_Reader -P xx > > Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: > Error code 0x2746. Some searching mentions that this 'TCP Provider: Error code 0x2746.' can be related to lack of TLS 1.2 support. I don't see your tsql connection attempt. ** Here is the tsql attempt: tsql -H mysqlserver.somedomain.net -p 1477 -U 'PData_Reader' -P 'xx' locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Error 20002 (severity 9): Adaptive Server connection failed Error 20002 (severity 9): Adaptive Server connection failed There was a problem connecting to the server > The FDW that is still working is going to a SQL Server v14.x 2017 > server and the broken one is going to a SQL Server version 13 2016 server. Are the SQL Server instances running on the same machine? ** No, they are two different servers. If not what versions of Windows are they using in each case? ** The broken FDW to the SQL server 2016 is running on Windows 2012 R2 ** The one that works is running on is running SQL Server 2017 on Windows Server 2016 Datacenter > > Also, I recreated the FDW that is broken on another server (Postgres > 16 on Rocky Linux 8) and it works there. > > So I'm puzzled here as to why it's not working on the upgraded server, > yet the other FDW on the same server still works. > > Any help on this issue would be greatly appreciated. > > Thank you, > > Stacy > > szac...@mcw.edu > -- Adrian Klaver adrian.kla...@aklaver.com
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
On 12/6/24 14:10, Zacher, Stacy wrote: Hi Adrian: Please see my replies below:** Thank you! Stacy -Original Message- From: Adrian Klaver Sent: Friday, December 6, 2024 3:51 PM To: Zacher, Stacy ; pgsql-general@lists.postgresql.org Subject: Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed ATTENTION: This email originated from a sender outside of MCW. Use caution when clicking on links or opening attachments. On 12/6/24 13:19, Zacher, Stacy wrote: Hello: We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Foreign data wrappers that use tds_fdw for connections to 2 different SQL Servers. After the OS upgrade, one of the FDWs stopped working for some reason but the other one still works. The error message when trying to run the import statement in psql is as follows (I changed some of the names): using psql: I tested connecting to the server/port via ncat and it returns successfully. When I try sqlcmd or tsql, The 1477 port is static (vs. dynamic) [postgres@rh9server bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d Pdata_db -U PData_Reader -P xx Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746. Some searching mentions that this 'TCP Provider: Error code 0x2746.' can be related to lack of TLS 1.2 support. I don't see your tsql connection attempt. ** Here is the tsql attempt: tsql -H mysqlserver.somedomain.net -p 1477 -U 'PData_Reader' -P 'xx' locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Error 20002 (severity 9): Adaptive Server connection failed Error 20002 (severity 9): Adaptive Server connection failed There was a problem connecting to the server From this: http://software.firstworks.com/2017/04/tlsssl-encryption-with-ms-sql-server.html "Connect to the database from the application server using the FreeTDS-supplied tsql program as follows: tsql -S EXAMPLEDB -U exampleuser -P examplepass If the connection fails, it will most likely fail with: Error 20002 (severity 9): Adaptive Server connection failed Which isn't very helpful. Running tsql with TDSDUMP enabled... TDSDUMP=stdout tsql -S EXAMPLEDB -U exampleuser -P examplepass ...may be slightly more revealing. " The FDW that is still working is going to a SQL Server v14.x 2017 server and the broken one is going to a SQL Server version 13 2016 server. Are the SQL Server instances running on the same machine? ** No, they are two different servers. If not what versions of Windows are they using in each case? ** The broken FDW to the SQL server 2016 is running on Windows 2012 R2 ** The one that works is running on is running SQL Server 2017 on Windows Server 2016 Datacenter Also, I recreated the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) and it works there. So I'm puzzled here as to why it's not working on the upgraded server, yet the other FDW on the same server still works. Any help on this issue would be greatly appreciated. Thank you, Stacy szac...@mcw.edu -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
On 12/6/24 13:19, Zacher, Stacy wrote: Hello: We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Foreign data wrappers that use tds_fdw for connections to 2 different SQL Servers. After the OS upgrade, one of the FDWs stopped working for some reason but the other one still works. Also, I recreated the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) and it works there. What version of tds_fdw are you using in this case? Recreated without doing the tds_fdw upgrade/revert sequence? Thank you, Stacy szac...@mcw.edu -- Adrian Klaver adrian.kla...@aklaver.com
tds_fdw DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed
Hello: We recently did an OS upgrade from Rocky Linux 8 to 9 on a Postgres v13 server that has 2 Foreign data wrappers that use tds_fdw for connections to 2 different SQL Servers. After the OS upgrade, one of the FDWs stopped working for some reason but the other one still works. The error message when trying to run the import statement in psql is as follows (I changed some of the names): using psql: mcwisprod=> IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true'); ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (mysqlserver.somedomain.net), OS #: 0, OS Msg: Success, Level: 9 using DEBUG in the logging: 2024-12-04 19:26:10.568 CST [15175] STATEMENT: IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true'); 2024-12-04 19:26:10.568 CST [15175] DEBUG: tds_fdw: Connecting to server 2024-12-04 19:26:10.568 CST [15175] STATEMENT: IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true'); 2024-12-04 19:26:10.576 CST [15175] DEBUG: Failed to connect using connection string mysqlserver.somedomain.net:1477 with user PData_Reader 2024-12-04 19:26:10.576 CST [15175] STATEMENT: IMPORT FOREIGN SCHEMA dbo from SERVER tds_mssql_mysqlserver into interface_prod OPTIONS (import_default 'true'); 2024-12-04 19:26:10.576 CST [15175] ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (mysqlserver.somedomain.net), OS #: 0, OS Msg: Success, Level: 9 After this would not work, I realized the tds_fdw was now version 2.0.4 so the sysadmin downgraded it back to 2.0.3 (rebooted the server) in case that was the issue. I'm still seeing the same error. I dropped everything with cascade (extension, foreign server, user mappings etc.) and re-created everything. Still seeing the same error. I've worked with the network team to ensure the connections are being made to the MSSQL Server. They saw the traffic, connection and then a TCP Teardown but said it is on my side, not the side of the SQL Server. I tested connecting to the server/port via ncat and it returns successfully. When I try sqlcmd or tsql, The 1477 port is static (vs. dynamic) [postgres@rh9server bin]$ ./sqlcmd -S 141.xxx.x.xxx,1477 -C -d Pdata_db -U PData_Reader -P xx Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2746. Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. Free tds used is version 7.3 No special characters in the password for the sql server user mapping. The FDW that is still working is going to a SQL Server v14.x 2017 server and the broken one is going to a SQL Server version 13 2016 server. Also, I recreated the FDW that is broken on another server (Postgres 16 on Rocky Linux 8) and it works there. So I'm puzzled here as to why it's not working on the upgraded server, yet the other FDW on the same server still works. Any help on this issue would be greatly appreciated. Thank you, Stacy szac...@mcw.edu
Empty query_id in pg_stat_activity
Hey folks, I am running Benchbase and pgbench at the same time just for debugging purposes, and I notice that sometimes query_id is missing from pg_stat_activity. Any clue why this is happening? ``` benchbase=# SELECT query_id, now() - query_start as duration, query FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' ORDER BY duration DESC LIMIT 5; query_id | duration | query -+--+ | 00:00:00.223544 | SELECT + | | s_suppkey, + | | s_name, + | | s_address, + | | s_phone, + | | total_revenue + | | FROM + | | supplier, + | | revenue0 + | | WHERE + | | s_suppkey = supplier_no + | | AND total_revenue = ( + | | SELECT + | | MAX(total_revenue) + | | FROM + | | revenue0 + | | ) + | | ORDER BY + | | s_suppkey + | | 3080582906387216276 | 00:00:00.32 | UPDATE pgbench_branches SET bbalance = bbalance + -4897 WHERE bid = 8; 3080582906387216276 | -00:00:00.000321 | UPDATE pgbench_branches SET bbalance = bbalance + -377 WHERE bid = 6; 2064869707185898531 | -00:00:00.000501 | END; 2064869707185898531 | -00:00:00.000502 | END; (5 rows) ``` Cheers, Costa
Re: Empty query_id in pg_stat_activity
On 2024-12-06 15:32 +0100, Costa Alexoglou wrote: > Hey folks, I am running Benchbase and pgbench at the same time just for > debugging purposes, and I notice that sometimes query_id is missing > from pg_stat_activity. Any clue why this is happening? What's your Postgres version? Could be that query_id is not reported because the session is using the extended query protocol. This has been fixed just recently in releases 14.14/15.9/16.5/17.1. From the 14.14 release notes: > * Report the active query ID for statistics purposes at the start of > processing of Bind and Execute protocol messages (Sami Imseih) > https://postgr.es/c/b36ee879c > > This allows more of the work done in extended query protocol to be > attributed to the correct query. Another possibility is that the session just disabled compute_query_id: https://postgr.es/m/472115375.225506.1683812791906%40office.mailbox.org -- Erik
Insert records in the tavke only if they are not exist
Hi, All, When my application starts up, ot creates some tables and insert records in them. When the app starts for the second time it should check if the tables and the records in them are exist and skip the process. Everything is good, except what if I have a connection from 2 different users? I can run this inside transaction, but will this be enough? Will stating transaction lock the DB and the second user will wait for transaction to complete? Thank you.
Re: Insert records in the tavke only if they are not exist
On Friday, December 6, 2024, Igor Korot wrote: > > When my application starts up, ot creates some tables and insert records > in them. > > When the app starts for the second time it should check if the tables and > the records in them are exist and skip the process. > > Everything is good, except what if I have a connection from 2 different > users? > > I can run this inside transaction, but will this be enough? Will stating > transaction lock the DB and the second user will wait for transaction to > complete? > > Seems like letting the create table fail would be a reliable way to determine what is happening. But this procedure overall just seems better avoided. Can’t you just run an installer/updater separate from running the application? David J.
Re: Insert records in the tavke only if they are not exist
Hi, Dvid, On Fri, Dec 6, 2024, 9:55 PM David G. Johnston wrote: > On Friday, December 6, 2024, Igor Korot wrote: >> >> When my application starts up, ot creates some tables and insert records >> in them. >> >> When the app starts for the second time it should check if the tables and >> the records in them are exist and skip the process. >> >> Everything is good, except what if I have a connection from 2 different >> users? >> >> I can run this inside transaction, but will this be enough? Will stating >> transaction lock the DB and the second user will wait for transaction to >> complete? >> >> > Seems like letting the create table fail would be a reliable way to > determine what is happening. But this procedure overall just seems better > avoided. Can’t you just run an installer/updater separate from running the > application? > Interesting idea bout the installer. Except those tables will contain some additional info about the schema and the application. So if I create/remove new table, either in my app or from psql the record will need to be inserted/deleted. Thank you. > David J. > >
