http://blog.gmane.org/gmane.comp.db.postgresql.odbc hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9551 <pre>Hi Groups, I'm dealing with periodic backend process segmentation faults. I'm posting to both the bugs and odbc lists as it seems that my application's use of pgsqlODBC triggers a bug in the postgres backend. The environment is: Clients: win32 clients using various version of the psqlODBC driver (mostly using 8.04.0200). The connection string contains "UseDeclareFetch=1" which causes long idle transactions, heavy cursor and savepoint use. Server: Dell dual Xeon x86 48GB RAM (12GB PG shared mem) RHEL system with Postgresql 9.2.4. Note that these same issues occurred on PG9.1 and PG8.4. I've experienced these issues for over a year now. However, during that time several things have changed which may or may not be related: * The schema has been modified with heavier use of triggers to update manually created materialized views (ie not using the 9.3 CREATE MATERIALIZED VIEW). * The number of concurrent users has increased (from perhaps 15 concurrent users two years ago, to 30 concurrent users now). * The PG v</pre> Joshua Berry 2013-05-24T16:55:19 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9543 <pre>I'm using postgresql-odbc-09.01.0200 with postgresl-9.2.4 According to the documention for SQLSetCursorName (http://msdn.microsoft.com/en-us/library/windows/desktop/ms711707%28v=vs.85%29.aspx) I don't really need to use it, and ODBC will use a default name. With unixODBC and a Postgresql connection handle, SQLGetInfo() shows SQL_CA1_POS_UPDATE capability for SQL_STATIC_CURSOR_ATTRIBUTES1, and SQL_KEYSET_CURSOR_ATTRIBUTES1. I tried using both a static and a keyset cursor, by setting SQL_ATTR_CURSOR_TYPE accordingly, on a new statement handle; then executing a "SELECT [..] FOR UPDATE", fetching multiple rows, using SQLSetPos to position on a given row, using SQLGetCursorName to retrieve the cursor's name, then attempting to execute an "UPDATE [..] WHERE CURRENT OF &lt;cursor_name&gt;" on another statement handle. I'm basically following the script given here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms709389%28v=vs.85%29.aspx The original SELECT is a simple select on a table with a</pre> Sam Varshavchik 2013-05-22T22:11:57 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9541 <pre>Hello, I have a very large, long running query that is being run on a CentOS 6 Linux box from an application using unixODBC 2.2.14 and postgresql-odbc 8.4. The target database is a postgreSQL 8.4 DB on another CentOS 6 Linux box. When run, the query runs for about one hour and then dies with the following error: for SQL input object "SQL-AR": SQLNumResultCols: -1/1; S1000/[unixODBC]No query has been executed with that handle I'm no overly familiar with using ODBC and have been researching this as best as I can. I've tried to add UseDeclareFetch to /etc/odbcinst.ini as well, thinking that may be the cause. Note that other queries run fine. I'm not sure if it is timing out, or what is happening. Does anyone know what this error could mean, and if there is anything I can put in my /etc/odbcinst.ini to help resolve the problem. My /etc/odbcinst.ini currently looks like this: [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/li</pre> ter062424 2013-05-22T17:08:47 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9531 <pre>-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 It seems the ODBC driver does not deal well with a WITH clause in a statement: 8&lt;---------------------------------- SQL&gt; select id from generate_series(1,2) as t(id) +------------+ | id | +------------+ | 1 | | 2 | +------------+ SQLRowCount returns -1 2 rows fetched SQL&gt; select w.id from (select id from generate_series(1,2) as t(id)) as w +------------+ | id | +------------+ | 1 | | 2 | +------------+ SQLRowCount returns -1 2 rows fetched SQL&gt; with w as (select id from generate_series(1,2) as t(id)) select w.id from w SQLRowCount returns 0 SQL&gt; select id from generate_series(1,2) as t(id) [ISQL]ERROR: Could not SQLExecute 8&lt;---------------------------------- The last statement fails (according to the logs) because: ERROR: cursor "SQL_CUR0x17c4bd0" already exists At that point the only recovery is ABORT. 8&lt;---------------------------------- SQL&gt; abort SQLRowCount returns 0 SQL&gt; select id from generate_s</pre> Joe Conway 2013-05-21T22:28:06 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9523 <pre>I just tried installing the latest psqlODBC 32-bit drivers compiled from source on a new Windows 7 64-bit Machine using the psqlODBC 32-bit drivers. The install went fine, but when trying to configure a System DSN I got the message "The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem." Going further on an existing System DSN I got "Couldn't load libpq - SSL mode is unavailable", So I guess it is the libpq.dll that needs VC++ 2010 runtimes. I hadn't noticed it before in testing since I had PostgreSQL 9.2.4 installed on the compile machine which has the "Microsoft Visual C++ 2010 Redistributable Package (x86)" package installed by the PostgreSQL Installer. After installing the "Microsoft Visual C++ 2010 Redistributable Package (x86)" package System DSN configuration works fine. I used the download from http://www.microsoft.com/en-us/download/details.aspx?id=5555 &lt;http://www.microsoft.com/en-us/download/details.aspx?id=5555&gt; Can the </pre> ljwilson 2013-05-21T14:33:21 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9515 <pre>Hello, Can you please let me know any mapping document which maps ODBC constructs between sybase and postgres. Also list of ODBC constructs to be used with postgres. Thanks in advance! Regards... </pre> Dev Kumkar 2013-05-20T23:23:20 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9508 <pre>With the latest psqlODBC 09.02.0100 prep, I have to make one change to compile using MS VC++ 2005 Express (32-bit version of drivers): In psqlodbc.h (my addition in bold) /* File:psqlodbc.h * * Description:This file contains defines and declarations that are related to *the entire driver. * * Comments:See "readme.txt" for copyright and license information. */ #ifndef __PSQLODBC_H__ #define __PSQLODBC_H__ /* #define__MS_REPORTS_ANSI_CHAR__ */ #ifndef WIN32 #include "config.h" #else #defineWIN32_LEAN_AND_MEAN #include &lt;windows.h&gt; *#if (_MSC_VER == 1400) /* in case of VC++ 2005 */ #include &lt;winsock2.h&gt; #endif /* _MSC_VER == 1400 */* #endif For a detailed discussion of why we need winsock2.h see: http://stackoverflow.com/questions/1372480/c-redefinition-header-files &lt;http://stackoverflow.com/questions/1372480/c-redefinition-header-files&gt; I'm curious what build environment is currently being used--is there something I can change on my end to not have to edit psqlodbc.h, or can we add </pre> ljwilson 2013-05-19T13:23:34 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9505 <pre>Hi: Currently when we put a blank date into a date/time field in postgres, we get a "Invalid data/time syntax" error. Can you put a patch for the ODBC driver to convert the blank date to null? (Other ODBC drivers we are doing do this and a previous ODBC drive 7.3.260 did support this? Any way to add this to current version? Thanks Barry Bell, IT Department Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell&lt; at &gt;harte-hanks.com&lt;mailto:Barry_Bell&lt; at &gt;harte-hanks.com&gt; </pre> Barry Bell 2013-05-17T17:57:21 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9501 <pre> Does anyone know of an setting in the ODBC driver that will convert a blank date to a null? Thanks Barry </pre> Barry Bell 2013-05-16T20:07:59 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9493 <pre>Hi. I think the vote of the next release number. 1.) 09.01.0300 many patch is a bug-fix support. and, psqlODBC is not intended to be synchronized with postgres version number. 2.) 09.02.0100 new version is desirable because test code that contains. 3.) 09.03.0100 we want to have a postgres release the next number. == I vote to 1.) , I think the major version up, when there is a change of more features and desirable. thanks! Regards, Hiroshi Saito </pre> Hiroshi Saito 2013-05-15T14:51:09 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9486 <pre> Hello,   if you use SQLDriverConnect instead of SQLConnect the connection fails on special characters like ';', '(', ')' and '+'(?) within the password.   According to the documentation ( http://msdn.microsoft.com/en-us/library/windows/desktop/ms715433%28v=vs.85%29.aspx ) []{}(),;?*=!&lt; at &gt;  are special characters.   In order to pass the connection string to the driver intact you have to bracket the whole connection string ( or just the values ? - it looks like my English is not good enough for this article ).   There already had been a patch in 09.00.0100 but this problem obviously persists in psqlODBC 09.01.0100 Unicode. Maybe this has been patched in the ANSI version only? Backslashes are still a problem? I wonder whether standard_conforming_strings has an effect on embedded backslashes.   Could you check, please?   Thank you very much,   Peter </pre> Jan-Peter Seifert 2013-05-13T14:04:57 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9484 <pre></pre> Jan-Peter Seifert 2013-05-13T13:15:49 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9482 <pre>Hello, I hope this is the right mailing list to post questions about pgsql odbc. I am sorry if I made a mistake and it is not. I have a Postgres database (9.1) and an Oracle database (10.2) whose character set is utf8. I installed the 64-bit 9.1 unicode psqlodbc driver along with the Oracle Database Gateway for ODBC (DG4ODBC), version 11.2 because I have the need to access data in the Postgres database from the Oracle one. It works fine, but we have noticed the following. When a query such as this is issued in Oracle: select * from "dps_user"&lt; at &gt;pg where "id" = '32422' The where clause is dropped, and the query that reaches the Postgres database is: select * from "dps_user" So the whole table data are brought from Postgres, and then the where clause is applied in Oracle. This is pretty inefficient with big tables. We have noticed that this only happens when the column in the where clause is of type character varying, due to the fact that the Oracle Database Gateway for ODBC returns all charact</pre> Carlos Muñoz Juste 2013-05-13T09:21:47 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9477 <pre>I've seen conflicting information on this: http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html &lt;http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html&gt; What I see with the latest source tree in my test environment of two windows workstations, each using the latest compiled odbc drivers (09.01.02??) to connect to PostgreSQL 9.2.4 (Windows): 1. With UserDeclareFetch=1 and UseServerSidePrepare=0, works as it always has for me. 2. With UserDeclareFetch=0 and UseServerSidePrepare=1, works as it always has for me. 3. With UserDeclareFetch=1 and UseServerSidePrepare=1, the first workstation that opens the database "wins". Then the second workstation cannot open the database on its own connection until the first workstation exits its program. If I use pgadmin3 to monitor a table--if a workstation with UserDeclareFetch=1 and UseServerSidePrepare=1 enters data into the table, pgadmin3 will not show the new data until the workstation exits. It's li</pre> ljwilson 2013-05-10T18:27:28 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9469 <pre>If everything goes to the plan, PostgreSQL 9.3 beta will be released in the new few weeks, and I just saw a thread on the pgsql-jdbc list on releasing a 9.3 JDBC driver for the beta (http://www.postgresql.org/message-id/1367433610.6768.15.camel&lt; at &gt;lenovo01-laptop03.gunduz.org). I think we should also make a 9.3 release of the ODBC driver. There's been a lot of bug fixes since the last release, so it's time to make a new release anyway. - Heikki </pre> Heikki Linnakangas 2013-05-02T06:54:29 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9458 <pre>I cleaned up and pushed the regression test suite that I wrote a while back. Two of the test cases are currently failing because of the bug discussed here: http://www.postgresql.org/message-id/51479445.7020905&lt; at &gt;vmware.com. Once that's fixed, let's keep it green! I tried to write a nmake makefile for running the tests on Windows, but failed. If someone who has more experience with Windows has the time to do that, that would be great, but a Unix-only suite is already better than nothing. - Heikki </pre> Heikki Linnakangas 2013-04-24T15:15:33 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9446 <pre>The psqlODBC website, at psqlodbc.projects.pgfoundry.org, is a bit outdated. It's missing links to the new git repository (it didn't have a link to the old CVS repo either), the pgfoundry page, and the downloads. Also, it doesn't look modern. I created a little mock-up of what the site would look like using the main PostgreSQL web site's style: http://psqlodbc.mooo.com/ (that domain is just a temporary address for showcasing this - I'm not proposing that we change hosting) The sub-pages, like the howtos and faq, are unchanged. On the front page, I created a new "history" section, and move there the paragraphs talking about history from the introduction section. I created a section for "psqlODBC development", with links to the mailing list archives and the git repository. I also added a link to the downloads page in the introduction - that's the first thing that users look for, so it's good to have it at the top. The important links are also available in the top navigation bar. I think this p</pre> Heikki Linnakangas 2013-04-22T11:38:13 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9444 <pre>I've been upgrading some Windows servers from PostgreSQL 8.3.18 to 9.2.4. Along with that I upgraded psqlODBC for the Windows clients from 8.0x.xxxx to 09.01.0200. All was fine until I had a customer who could consistently get a gpf in psqlodbc35w.dll when doing an export. Getting a copy of their database, I could reproduce as well. Trying different driver versions, I've found no crashes with the 8.0x psqlODBC series, but I can reproduce the gpf starting with 09.00.xxxx through the latest 9.01.02?? that I compiled yesterday (2013/04/20) from the git repository. I've used UseDeclareFetch=1 and Fetch=100 since I started with PostgreSQL back in 2008. Now with the 09.xx.xxxx drivers I don't get a crash if I: 1. Set UseDeclareFetch=0 or 2. Set UseServerSidePrepare=1 I've got a 31MB mylog file (zipped) of an entire client session. I can uploaded somewhere if that would help The bulk of that could be stripped-out; probably just the first and last 1000 lines are relevant. The actual sql is doing an update one</pre> ljwilson 2013-04-21T17:56:05 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9443 <pre>The docs on ODBC configuration options recommend UseServerSidePrepare=1 for server versions 7.4 and above. That's a good recommendation. Given that "7.4 and above" covers all supported versions of PostreSQL, and every reasonable version people might want to use a new driver with, how about we make UseServerSidePrepare=1 the default, so that people don't need to set it manually? - Heikki </pre> Heikki Linnakangas 2013-04-19T19:14:44 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9440 <pre> I'm experimenting with 64 bit windows environment, and before hitting too much the head on the wall, for 32 bit application i have to install the 32 bit version odbc drivers and use the 32 bit version of control panel application to create DSN, right? There's no trouble at all at mixing 32/64 bit postgres odbc driver, right? Thanks. </pre> Marco Gaiarin 2013-04-18T16:05:34 http://comments.gmane.org/gmane.comp.db.postgresql.odbc/9428 <pre>Hello. Our code does a bunch of selects, inserts and updates, including an insert within a transaction that looks like: insert into foo values (?, ?, ?, ?, ?, ?, ?, ?) where the parameters are integers, strings and timestamps. We then use SQLRowCount() to confirm that one row was inserted. With the 9.1.2 driver, if one of the string parameters is five characters long then I get -1 for the affected rows in SQLRowCount(). (I'm not sure that the string length is anything to do with it, but it causes the problem without fail, and it appears to work fine with any other length of string.) I've confirmed via Wireshark that the number of affected rows is indeed 1 and is being sent to the client from the PostgreSQL server. Like Vadim Zeitlin: http://www.postgresql.org/message-id/E1UQdZr-00073b-2A&lt; at &gt;smtp.tt-solutions.com I've concluded that the problem occurs if and only if this change: diff a/statement.h b/statement.h --- a/statement.h +++ b/statement.h0 &lt; at &gt;&lt; at &gt; -440,7 +440,7 &lt; at &gt;&lt; at &gt; do { \ if (PREPARED_TEMPORARI</pre> Geoff Johnstone 2013-04-12T20:15:08 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.odbc