Thread: Are UseDeclareFetch and UseServerSidePrepare mutually exclusive?
I've seen conflicting information on this: http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html <http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html> 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 like the workstation has an exclusive lock on the table when UserDeclareFetch=1 and UseServerSidePrepare=1. I'm not questioning the behavior, but I'd like to understand it and maybe get it documented so a user doesn't accidentally set up the switches such that it takes a while to figure out what is going on. Thanks...jack -- View this message in context: http://postgresql.1045698.n5.nabble.com/Are-UseDeclareFetch-and-UseServerSidePrepare-mutually-exclusive-tp5755080.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Hi Jack, (2013/05/11 3:27), ljwilson wrote: > I've seen conflicting information on this: > http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html > <http://postgresql.1045698.n5.nabble.com/Improve-ODBC-Throughput-tp2189601p2189614.html> > > 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. It seems very strange to me. Do the workstations issue DDL commands or vacuum commands? regards, Hiroshi Inoue
No, just standard select/update statements. I set log_statement = 'ddl' in postgresql.conf and did a reload just to make sure no ddl commands were being executed. Here is a list of the unique sql statements (first few words only) for a test session (extracted with log_statement = 'all'): BEGIN;declare "SQL_CUR05628E20" cursor for SELECT COMMIT INSERT INTO RELEASE _EXEC_SVP_056292B0 SAVEPOINT _EXEC_SVP_056292B0 SELECT UPDATE close "SQL_CUR05628E20";commit declare "SQL_CUR05628E20" cursor with hold for SELECT COUNT(*) FROM delete from Thanks...jack -- View this message in context: http://postgresql.1045698.n5.nabble.com/Are-UseDeclareFetch-and-UseServerSidePrepare-mutually-exclusive-tp5755080p5755243.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
(2013/05/13 23:13), ljwilson wrote: > No, just standard select/update statements. I set log_statement = 'ddl' in > postgresql.conf and did a reload just to make sure no ddl commands were > being executed. > > Here is a list of the unique sql statements (first few words only) for a > test session (extracted with log_statement = 'all'): > > BEGIN;declare "SQL_CUR05628E20" cursor for SELECT > COMMIT > INSERT INTO > RELEASE _EXEC_SVP_056292B0 > SAVEPOINT _EXEC_SVP_056292B0 > SELECT > UPDATE > close "SQL_CUR05628E20";commit > declare "SQL_CUR05628E20" cursor with hold for SELECT COUNT(*) FROM > delete from OK you wrote > 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. ISTM the second workstation is waiting for the return of a query when the database is blocked to be opened. Could you check it? regards, Hiroshi Inoue
So the answer to this thread's question is "No", now that Hiroshi Inoue has committed a patch which lets UseDeclareFetch=1 and UseServerSidePrepare=1 co-exist quite happily in my multi-user Windows application with the latest psqlodbc compiled from source. Many thanks again to Inoue-san for the quick turnaround. ...jack -- View this message in context: http://postgresql.1045698.n5.nabble.com/Are-UseDeclareFetch-and-UseServerSidePrepare-mutually-exclusive-tp5755080p5755435.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Sound good to me:-) Many users seem to be waiting for the new release. I'm working on re-confirmation of some. Thanks! (2013/05/15 0:32), ljwilson wrote: > So the answer to this thread's question is "No", now that Hiroshi Inoue haso me: > committed a patch which lets UseDeclareFetch=1 and UseServerSidePrepare=1 > co-exist quite happily in my multi-user Windows application with the latest > psqlodbc compiled from source. > > Many thanks again to Inoue-san for the quick turnaround. > > ...jack > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Are-UseDeclareFetch-and-UseServerSidePrepare-mutually-exclusive-tp5755080p5755435.html > Sent from the PostgreSQL - odbc mailing list archive at Nabble.com. > >