Postgresql ODBC problems

[Jeff Davis]

Very odd - for some reason this just quit working.

We have a small database we use to track workorders. It has suddenly
quit responding to certain queries, and I'm not sure how to trace this.

It's running postgresql-7.1-1 on a redhat 6.2 (I know, time to update)
box. Queries outside of coldfusion work fine, but from within the cf app
they just hang and jump the server load to over 4.00.

At this point it smells like an odbc driver problem, but not quite sure
how to debug it. I've got postgres syslog turned on, but nothing shows
up in the log. CF server logs show nothing that sticks out either.

I've read some postings elsewhere re utf-8 being a potential issue, but
no luck on that end so far.

Can someone point me as to where to luck so I can resolve this?

Thanks

[Jochem van Dieten - TMM]

Jeff Davis wrote:
> Very odd - for some reason this just quit working.
>
> We have a small database we use to track workorders. It has suddenly
> quit responding to certain queries, and I'm not sure how to trace this.
>
> It's running postgresql-7.1-1 on a redhat 6.2 (I know, time to update)
> box. Queries outside of coldfusion work fine, but from within the cf app
> they just hang and jump the server load to over 4.00.
>
> At this point it smells like an odbc driver problem, but not quite sure
> how to debug it. I've got postgres syslog turned on, but nothing shows
> up in the log. CF server logs show nothing that sticks out either.

Does select * from pg_stat_activity show any connections from CF?
If so, what queries are they running?

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[Jeff Davis]

Thanks for the reply,

Please excuse my ignorance - but what database should I connect to in
order to run that query?

Most are select * from <db_name>. I kinda suspect some bad data, but
not sure what to do about it. Tailing logs doesn't really show anything.

[Jochem van Dieten - TMM]

Jeff Davis wrote:
>
> Please excuse my ignorance - but what database should I connect to in
> order to run that query?

Doesn't matter. It shows all current connections to the database,
and if you are a superuser and you have turned on logging you can
even see which queries are being executed.

> Most are select * from <db_name>. I kinda suspect some bad data, but
> not sure what to do about it.

Do you get errors when you do a VACUUM FULL ANALYZE?

[altimage]

Ive been using the Postgres jdbc driver for quite a while with no problems. Maybe give that a shot:
http://jdbc.postgresql.org

AlImage

[Jeff Davis]

How does one implement this w/coldfusion 5? I'd certainly be willing to
give it a shot....

[Jochem van Dieten - TMM]

Jeff Davis wrote:
> altimage wrote:
>
>> Ive been using the Postgres jdbc driver for quite a while with no
>> problems. Maybe give that a shot:
>> http://jdbc.postgresql.org
>>
>> AlImage
>
> How does one implement this w/coldfusion 5?

MX and 7 can use JDBC, 5 can not.

[Jeff Davis]

Jochem van Dieten - TMM wrote:

> Jeff Davis wrote:
>
>>
>> Please excuse my ignorance - but what database should I connect to in
>> order to run that query?
>
>
> Doesn't matter. It shows all current connections to the database, and if
> you are a superuser and you have turned on logging you can even see
> which queries are being executed.
>
>
>> Most are select * from <db_name>. I kinda suspect some bad data, but
>> not sure what to do about it.
>
>
>
> Do you get errors when you do a VACUUM FULL ANALYZE?
>
> Jochem
>

Some additional info... When running queries whose select statements
are as follows:

SELECT * from wor_order where <condition>

The query times out...

however, if I do the following:

SELECT (full field list) from wor_order where <condition>

no problem. The SELECT * problem only seems to exist from within CF - I
can use PGADMIN and SELECT * all day long with no problems...

Strange...

[Jeff Davis]

Jochem van Dieten - TMM wrote:

> Jeff Davis wrote:
>
>> altimage wrote:
>>
>>> Ive been using the Postgres jdbc driver for quite a while with no
>>> problems. Maybe give that a shot:
>>> http://jdbc.postgresql.org
>>>
>>> AlImage
>>
>>
>> How does one implement this w/coldfusion 5?
>
>
> MX and 7 can use JDBC, 5 can not.
>
> Jochem
>
>

That's what I thought...

[Jochem van Dieten - TMM]

Jeff Davis wrote:
>
> Some additional info... When running queries whose select statements
> are as follows:
>
> SELECT * from wor_order where <condition>
>
> The query times out...
>
> however, if I do the following:
>
> SELECT (full field list) from wor_order where <condition>
>
> no problem. The SELECT * problem only seems to exist from within CF - I
> can use PGADMIN and SELECT * all day long with no problems...

That is probably an ODBC problem. I have had similar problems
with the earlier ODBC drivers and cfqueryparam, so I assume it is
something in the area of not being able to identify the datatypes
or column names.
I'm afraid that if updating your ODBC driver doesn't fix it
writing the full field list is your only other option. If you
have any plans for moving to MX or 7, now is the time. I have
never had any such issues with the JDBC drivers.