| Q: | Under what license PGNP is published? | |
| A: | PGNP (PostgreSQL Native Provider) is published under commercial software product license. It is a closed code proprietary software. It is not freeware, shareware, or open source software. In order to use it you would need to purchase a license. | |
| Q: | Which versions of PostgreSQL are compatible to / supported by the Provider? |
|
| A: | We are testing PGNP Provider with PostgreSQL 8.2 and later (8.X to 13.X). Some of our customers reported that they successfully used the Provider with versions 8.0 and 8.1. However, please make sure that provider works with your version of PostgreSQL before the purchase. | |
| Q: | How can I disable trial limitations in the trial version of the provider? Do I need to purchase a license for it? | |
| A: | There is no way to deactivate or disable trial limitations in the trial version of the provider. If you need non trial version you would need to purchase a license and you would get non trial version from us. | |
| Q: | What kind of limitations do you have in the trial version? | |
| A: |
The trial version has the following limitations: |
|
| Q: | My application is written in C#, I am using .NET provider to access my data, what would I get if I switch to PGNP instead? | |
| A: | Any .NET provider supports only subset of OLEDB interfaces. There is no way for example to use .NET providers for DTS/SSIS transformations. Also many reporting and management tools can only use OLEDB providers to access data in databases. .NET providers do not support server side cursors. .NET providers are written in managed code and can not outperform hand optimized native code. Obviously if you need to access PostgreSQL from native code (C++, Delphi, VB) then PGNP would be the only choice. | |
| Q: | Do you support DTS/SSIS transformation from X to Y database? | |
| A: | We have tested many databases transformations from SQL Server and Oracle to PostgreSQL. We believe this represents the most common scenario for DTS/SSIS. We have tested transformations from PostgreSQL to SQL Server and Oracle as well, but not as extensively. Since DTS/SSIS transformations rely on OLEDB data types mapping PGNP should work with any DB with properly written OLEDB provider. | |
| Q: | What do you map SQL Server GUID data type to? | |
| A: | After extensive research we decided to map it to Base64 encoded fixed-length (22) character field. Starting with version 1.2.8 and PostgreSQL 8.3 GUID is mapped to UUID field. | |
| Q: | What are the System Requirements? | |
| A: | PGNP provider is compatible with Windows Servers 2003 SP2 - 2019, and Windows XP Windows 1X. We recommend MDAC 2.8 or later to be installed. Since provider does not consume any significant amount of memory or CPU cycles, the specific hardware requirements should be defined by an application used. For example, replicatrion of a large MS SQL Server database may require 8GB or more memory on the server. | |
| Q: | Any implementation details? | |
| A: | PGNP is written using MS C++ (VS 2019) with key functions implemented in hand optimized assembler. COM implemented using ATL and SQL is handled by state of the art SQL parser. For a fee more details can be obtained under NDA including purchase of the source code. | |
| Q: | My application cannot open connection to a database using PGNP. What is the reason? | |
| A: |
First, check if the server is accepting connections on the standard port: 5432. If non-standard port is used then specify it in the connection string: Extended Properties="PORT=7656". Secondly, in case PostgreSQL server is running on a remote computer, check firewall settings and configuration in pg_hba.conf (to enable remote connections). See PostgreSQL documentation for more details. Thirdly, run PGNProfiler as Administrator and collect trace. The trace usually contains additional details about reason of an issue. We usually recommend using a UDL file for testing connectivity via PGNP provider. For this, create an empty file test.udl on Desktop. Double click on the file and, in Data Link Properties dialog, select "PostgreSQL Native Provider" and the parameters of connection in second tab. Click "Test Connection" button. If connection test fails please check Postgres logs, and see if connection attempt was made, and why it was refused. |
|
| Q: |
Why a simple query on a Linked Server table with 100 million rows results in unusually long execution time? Here is query example: SELECT TOP 20 * FROM [LINKSVR]...[bigtable] |
|
| A: | Please rewrite the query using openquery syntax: SELECT * FROM openquery(LINKSVR, "SELECT TOP 10 * FROM bigtable") The original query with "4-part identifiers" caches all rows from a table. The openquery is much more efficient. For stored procedures and SQL commands consider using Exec ... At... syntax. |
|
| Q: | Why updating a very large table via Linked Server results in Timeout error? Here is command example: SELECT 1 FROM OPENQUERY(LINKSVR,'UPDATE "bigtable" SET "IsActive"=True WHERE "UserId"=960 AND "Client"=True AND "IsActive"= False and "ris"=True and "rwcl"=True') OLE DB provider "PGNP" for linked server "LINKSVR" returned message "timeout expired". |
|
| A: | The issue is caused by default command timeout that is equal to 30 seconds. Please add COMMAND_TIMEOUT=0 parameter to the Extended Properties in connection string. | |
| Q: |
Is OLE DB driver caching the schema for tables/views? If so how do we make the driver reload the schema to get the new column? |
|
| A: | Yes, the provider is caching the schema. The easiest way to recycle the cache is to restart the application. Alternatively, you can connect to the same DB, and execute statement “NOTIFY pgnp_rtx”, or via an OLEDB connection execute the internal stored procedure pgnp_refreshmetadata('','table'). Details are available in the Developer’s Manual. Here is a sample VBs that automates the cache reset (refreshmetadata.vbs): option explicit '' recycle internal metadata cache in the PGNP OLEDB Provider Dim oCon, sCon, cmdObj, rsObj If WScript.Arguments.Count <> 1 Then WScript.Echo "Expecting exactly 1 parameter - table name." WScript.Quit End If '' create connection & command objects set oCon = CreateObject("ADODB.Connection") sCon = "Provider=PGNP.1;Persist Security Info=True;Data Source=localhost;" & _ "Initial Catalog=postgres;User ID=postgres;Password=12345;" & _ "Extended Properties=""PORT=5432;""" '' connect oCon.Open sCon WScript.Echo "Refreshing metadata for: " & WScript.Arguments.Item(0) Set cmdObj = CreateObject("ADODB.Command") Set cmdObj.ActiveConnection = oCon cmdObj.CommandText = "pgnp_refreshmetadata('','" & WScript.Arguments.Item(0) & "');" cmdObj.Execute WScript.Echo "Done." It can be executed from command line: cscript refreshmetadata.vbs tblname |
|
| Q: | What’s the difference between server edition vs desktop edition? | |
| A: | The difference is described in the Developer's Manual. Basically, the Server Edition supports additional OLE DB interfaces (for the Two Phase Commit, FastLoad, etc.), and is intended for work with SSIS, SSAS, SSRS, IIS and other types of servers. Server Edition allows better integration with SSIS, better scalability and performance. | |