headers
Duane Winner | 5 Mar 2004 15:34
Picon
Favicon

unixodbc - postgresql: broken pipes, malloc, ksqo and other errors

Hello all. I am posting this on unixodbc-dev and pgsql-odbc as well as
r-help to make sure all of my bases are covered (and because I'm not
sure which one of these is the source of the issue.)

Unixodbc with Postgresql seems to be working, but I am getting some
errors that I don't understand and want to know why I am getting these
errors before I put everything into production.

I am running:
FreeBSD 4.9-RELEASE
unixODBC-2.2.8 (installed from FreeBSD port)
postgresql-7.3.5_1 (installed from FreeBSD port)
R 1.8.1 (installed from R-letter FreeBSD port)
RODBC (installed from CRAN)

Here is what is happening:

When I run "isql":
# isql mydb -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> q
Broken pipe
(Continue reading)

Permalink | Reply | 2 comments |
headers
Duane Winner | 8 Mar 2004 21:14
Picon
Favicon

compiling psqlodbc for unixodbc -- problems

Can anybody provide me with some help on this issue?

I'm attempting to build psqlodbc-07.03.0200 in an attempt to use this
driver with unixodbc.

I first configure:
# ./configure --with-unixodbc
I don't see any errors when I do this.

Then I "make":
#make

And this is what I get:

make  all-am
source='info.c' object='info.lo' libtool=yes  depfile='.deps/info.Plo'
tmpdepfile='.deps/info.TPlo'  depmode=gcc /usr/local/bin/bash ./depcomp 
/usr/local/bin/bash ./libtool --mode=compile gcc -DHAVE_CONFIG_H -I. -I.
-I.      -g -O2 -c -o info.lo `test -f 'info.c' || echo './'`info.c
gcc -DHAVE_CONFIG_H -I. -I. -I. -g -O2 -c info.c
-Wp,-MD,.deps/info.TPlo  -fPIC -DPIC -o info.lo
In file included from info.c:19:
psqlodbc.h:41: sql.h: No such file or directory
psqlodbc.h:42: sqlext.h: No such file or directory
psqlodbc.h:52: odbcinst.h: No such file or directory
*** Error code 1

Stop in /root/psqlodbc-07.03.0200.
*** Error code 1
(Continue reading)

Permalink | Reply | 2 comments |
headers
Ray Aspeitia | 8 Mar 2004 22:09

Re: compiling psqlodbc for unixodbc -- problems

I had this problem on OSX,

it has to do with the compiler finding the similarly named file 
"sql.h" in the psql includes directory instead of your unixodbc 
includes directory.

What happened is that somehow the /usr/local/psql/includes directory 
comes before /usr/local/includes in the compiler string and it finds 
the psql version of sql.h first.

For me it was as simple as renaming the 
/usr/local/psql/includes/sql.h file to something else (BEWARE! your 
mileage may vary!) I must not have used some of those flags in that 
file (many define parameters in that file), so that when the compiler 
searched the directory /usr/local/psql/includes and didn't find 
sql.h, it continued on to /usr/local/includes.

You should check the HACKERS list for further information on the 
progress of this. I believe it is in discussion.

Ray A.

>Can anybody provide me with some help on this issue?
>
>I'm attempting to build psqlodbc-07.03.0200 in an attempt to use this
>driver with unixodbc.
>
>I first configure:
># ./configure --with-unixodbc
>I don't see any errors when I do this.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tom Lane | 8 Mar 2004 22:31
Picon

Re: compiling psqlodbc for unixodbc -- problems

Duane Winner <duanewinner <at> att.net> writes:
> I am running FreeBSD-4.9-RELEASE-p3
> Postgresql-7.3.5_1 (installed from ports)
> unixODBC-2.2.8 (installed from ports)

I think you're missing unixODBC-devel.

(It would seem that psqlodbc's configure --with-unixodbc should check
for availability of header files as well as library files ...)

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Permalink | Reply | 0 comments |
headers
Duane Winner | 9 Mar 2004 15:30
Picon
Favicon

compiling psqlodbc for unixodbc -- still having probs

Ok, I'm getting closer (I think) to getting this sucker compiled.

I copied the following files from /usr/local/include to my
psqlodbc-07.03.0200 source directory:
sql.h, sqlext.h, odbcinst.h, sqltypes.h., sqlucode.h

I then tried "make" again, and it proceeded merrily along for a little
while, but then I got this:

/usr/libexec/elf/ld: cannot find -lodbcinst
*** Error code 1

Stop in /root/psqlodbc-07.03.0200.
*** Error code 1

Stop in /root/psqlodbc-07.03.0200.

Now I'm completely at a loss.

Hints, anybody?

Again, I configured with "--with-unixodbc".
I'm trying to build this on FreeBSD-4.9-RELEASE-p3
Postgresql-7.3.5_1 (installed from ports)
unixODBC-2.2.8 (installed from ports)

Thanks,
DW

---------------------------(end of broadcast)---------------------------
(Continue reading)

Permalink | Reply | 0 comments |
headers
Stergios Zissakis | 9 Mar 2004 15:24
Picon
Picon
Favicon

Mutli-threading and performance of ODBC

Dear pgsql-odbc support

I am currently working on a pilot for porting an application to
PostgreSQL. In the pilot I am examining the two interfaces ODBC and
ECPG, and evaluating their performance. I am aware that there is a
performance penalty for using ODBC, and I would like to pose a few
questions, hoping that I can further improve the performace using ODBC:

1. Studing the postgreSQL log, it is apparent that the the ODBC driver
switches off the Genetic Query Optimiser (geqo) and tries to switch on
another optimiser called (ksqo) which fails. What is the ksqo optimiser?
Why does it switch to a different optimiser? And why does the switch to
the ksqo optimiser fail?

2. Also from the postgreSQL log, it is apparent that with the ODBC
driver, client threads tend to get "serialised", while with ECPG threads
execute more concurrently (context switches occur more often). I have
created two test harnesses (see the attached .cc files) one using ODBC
via OTL4 and the other using ECPG. Specifically, ECPG tends to
"multitask" threads to the degree that some threads get to be active
twice, inserting some rows the first time and the rest the second time.
This is more noticable for a small number of threads with say 10 inserts
each; in such case thread activity with ODBC is strictly serialised,
while with ECPG multitasking is still evident. Does this serialisation
have any performance implications? (Is this maybe related to the
switched-off query optimiser mentioned above?).

3. Also, using ODBC, in the log file of PostgreSQL, it is seen that a
lot of blank statements are generated. Can these blank statements affect
the performance?
(Continue reading)

Permalink | Reply | 1 comment |
headers
Duane Winner | 9 Mar 2004 16:17
Picon
Favicon

chunk is already free error

I've finally managed to get psqlodbc compiled for unixodbc without
errors.

But it isn't working.

When I modify my .odbc.ini file to use the psqlodbc driver instead of
the postgresql driver provided by unixodbc, I get this:

-su-2.05b$ isql TestDB
isql in free(): warning: chunk is already free
[ISQL]ERROR: Could not SQLConnect

Here is my odbc.ini file:
[ODBC Data Sources]
TestDB = PostgreSQL native driver

[TestDB]
Driver     = /usr/local/lib/psqlodbc.so
#Driver    = /usr/local/lib/libodbcpsql.so
Host       = localhost
Server     = localhost
ServerName = localhost
Database   = mydb
UserName   = pgsql
UID        = pgsql
Port       = 5432

(Note: If I use the other driver - libodbcpsql.so - I can actually
connect to the database. But I get the same "chunk is already free" in
other applications using unixodbc, such as R).
(Continue reading)

Permalink | Reply | 0 comments |
headers
Tom Lane | 9 Mar 2004 16:56
Picon

Re: Mutli-threading and performance of ODBC

"Stergios Zissakis" <szis <at> intranet.gr> writes:
> 1. Studing the postgreSQL log, it is apparent that the the ODBC driver
> switches off the Genetic Query Optimiser (geqo) and tries to switch on
> another optimiser called (ksqo) which fails. What is the ksqo optimiser?

ksqo was a hack that disappeared several releases back.  I do not know
why ODBC still has a reference to it (or why it tried to force it on
in the first place; IMHO a driver has no business making that kind of
decision).

I would recommend removing both of those SETs from the driver code.

> 3. Also, using ODBC, in the log file of PostgreSQL, it is seen that a
> lot of blank statements are generated. Can these blank statements affect
> the performance?

The backend won't spend much time on a blank statement, but if a
separate network round trip is being incurred for each one, that could
get a tad expensive ...

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Permalink | Reply | 0 comments |
headers
Dave Page | 9 Mar 2004 17:21
Picon

Re: Mutli-threading and performance of ODBC


> -----Original Message-----
> From: Tom Lane [mailto:tgl <at> sss.pgh.pa.us] 
> Sent: 09 March 2004 15:57
> To: Stergios Zissakis
> Cc: pgsql-odbc <at> postgresql.org; Kostas Lykiardopoulos; 
> Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC 
> 
> "Stergios Zissakis" <szis <at> intranet.gr> writes:
> > 1. Studing the postgreSQL log, it is apparent that the the 
> ODBC driver 
> > switches off the Genetic Query Optimiser (geqo) and tries 
> to switch on 
> > another optimiser called (ksqo) which fails. What is the 
> ksqo optimiser?
> 
> ksqo was a hack that disappeared several releases back.  I do 
> not know why ODBC still has a reference to it (or why it 
> tried to force it on in the first place; IMHO a driver has no 
> business making that kind of decision).
> 
> I would recommend removing both of those SETs from the driver code.

I removed KSQO long ago for servers >= 7.3 - for < 7.3 it is a config
option. GEQO is set via a config option - it is not forced on.

> > 3. Also, using ODBC, in the log file of PostgreSQL, it is 
> seen that a 
> > lot of blank statements are generated. Can these blank statements
(Continue reading)

Permalink | Reply | 1 comment |
headers
Dave Page | 9 Mar 2004 17:45
Picon

Re: Mutli-threading and performance of ODBC


> -----Original Message-----
> From: Tom Lane [mailto:tgl <at> sss.pgh.pa.us] 
> Sent: 09 March 2004 16:35
> To: Dave Page
> Cc: Stergios Zissakis; pgsql-odbc <at> postgresql.org; Kostas 
> Lykiardopoulos; Dimitris Pantermalis
> Subject: Re: [ODBC] Mutli-threading and performance of ODBC 
> 
> "Dave Page" <dpage <at> vale-housing.co.uk> writes:
> > I removed KSQO long ago for servers >= 7.3 - for < 7.3 it 
> is a config 
> > option. GEQO is set via a config option - it is not forced on.
> 
> I just looked through the unixODBC 2.2.8 sources, and I can't 
> see anywhere that the behavior for KSQO is made 
> version-dependent.  It seems to be there and default to ON 
> regardless of backend version.

It's not in unixODBC, it's in psqlODBC (and the above should have read
7.1).

From:
http://gborg.postgresql.org/project/psqlodbc/cvs/co.php/psqlodbc/connect
ion.c?r=1.67

	/* KSQO (not applicable to 7.1+ - DJP 21/06/2002) */
	if (ci->drivers.ksqo && PG_VERSION_LT(self, 7.1))
	{
		result = PGAPI_ExecDirect(hstmt, "set ksqo to 'ON'",
(Continue reading)

Permalink | Reply | 1 comment |

Gmane