Обсуждение: Mystery SELECT * query
Hello,
I've inherited a web service on a Windows server that connects to a Postgres database. There is a "SELECT * FROM" query below that runs a long time and appears to happen whenever there is a web service update. I'd like to eliminate or limit it but I have to find it first. I have pgBadger set up and logging turned up which is how I found the query.
The developer says the query is not in his code and gave me the source code. It's pasqual. I see the other queries but I don't see the select * either in the code. He also added logging of the queries and the SELECT * FROM is missing. Is it possible another query creates the select * such as the two queries before it shown below?
These are all part of the same connection [2308] and I can see the connection and disconnect each time. Can I safely assume this is all the same connection and must be coming from the same web service code/program?
Any help would be greatly appreciated.
Thanks,
Andrew
Andrew
2013-12-09 00:48:15 EST [2308]: [21-1] user=phoneworks,db=phoneworks LOG: duration: 33.032 ms statement: Update cout4424ord set deliverbyopr='C' Where col_id In (17403)
2013-12-09 00:48:15 EST [2308]: [22-1] user=phoneworks,db=phoneworks LOG: duration: 4.047 ms statement: Select Count(col_id) from cout4424ord Where deliverbyopr<>'C' OR deliverbyopr IS NULL
2013-12-09 00:48:16 EST [2308]: [23-1] user=phoneworks,db=phoneworks LOG: duration: 905.426 ms statement: SELECT * FROM cout4424ord
Andrew Puschak <apuschak@gmail.com> wrote: > I've inherited a web service on a Windows server that connects to > a Postgres database. There is a "SELECT * FROM" query below that > runs a long time and appears to happen whenever there is a web > service update. I'd like to eliminate or limit it but I have to > find it first. The first thing I would look at is whether the software stack on your web server includes some sort of table cache which is being invalidated by each update. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Dec 9, 2013 at 6:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Andrew Puschak <apuschak@gmail.com> wrote:The first thing I would look at is whether the software stack on
> I've inherited a web service on a Windows server that connects to
> a Postgres database. There is a "SELECT * FROM" query below that
> runs a long time and appears to happen whenever there is a web
> service update. I'd like to eliminate or limit it but I have to
> find it first.
your web server includes some sort of table cache which is being
invalidated by each update.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi Kevin,
I have not had access to the web server yet, the owner works on it with the developer but knowledge is limited there and this was setup years ago. I can see if I can get access or ask the developer but this might not happen. I'm told it might be using ODBC to connect, I'm not familiar with Windows, I'm a linux admin. I'll look up table caching and see if we can find it there.
Thanks for your help,
Andrew
On Mon, Dec 9, 2013 at 7:08 PM, Andrew Puschak <apuschak@gmail.com> wrote:
Hi Kevin,On Mon, Dec 9, 2013 at 6:57 PM, Kevin Grittner <kgrittn@ymail.com> wrote:Andrew Puschak <apuschak@gmail.com> wrote:The first thing I would look at is whether the software stack on
> I've inherited a web service on a Windows server that connects to
> a Postgres database. There is a "SELECT * FROM" query below that
> runs a long time and appears to happen whenever there is a web
> service update. I'd like to eliminate or limit it but I have to
> find it first.
your web server includes some sort of table cache which is being
invalidated by each update.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL CompanyI have not had access to the web server yet, the owner works on it with the developer but knowledge is limited there and this was setup years ago. I can see if I can get access or ask the developer but this might not happen. I'm told it might be using ODBC to connect, I'm not familiar with Windows, I'm a linux admin. I'll look up table caching and see if we can find it there.Thanks for your help,Andrew
Hi Everyone,
An update. The original developer worked on this for a while. The last he communicated he thought it had to do with a count query he was sending to ODBC and even though it was limited, the older ODBC connector was performing a SELECT * query and calculating the value itself. I don't really know what he did, he made a number of changes and tests since then but the last change did stop the volume of SELECT * queries from his code. I see in pgBadger that the average select query duration for each day is around 20ms all day now instead of spiking over 50ms. We are processing phone calls and have a lot more traffic to move to this database so this is helpful.
Thanks for your help,
Andrew
Andrew Puschak, 09.12.2013 22:07: > I've inherited a web service on a Windows server that connects to a > Postgres database. There is a "SELECT * FROM" query below that runs a > long time and appears to happen whenever there is a web service > update. I'd like to eliminate or limit it but I have to find it > first. I have pgBadger set up and logging turned up which is how I > found the query. > > The developer says the query is not in his code and gave me the > source code. It's pasqual. I see the other queries but I don't see > the select * either in the code. He also added logging of the queries > and the SELECT * FROM is missing. Is it possible another query > creates the select * such as the two queries before it shown below? Maybe a "clever" ORM that re-selects any row that is touched by a DML statement in order to re-retrieve computed columnsor generated IDs? I don't know if there are any Pascal ORMs around, but I know that some ORMs in the Java world to crazy things like that.
On Mon, Jan 20, 2014 at 9:23 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Andrew Puschak, 09.12.2013 22:07:> I've inherited a web service on a Windows server that connects to aMaybe a "clever" ORM that re-selects any row that is touched by a DML statement in order to re-retrieve computed columns or generated IDs?
> Postgres database. There is a "SELECT * FROM" query below that runs a
> long time and appears to happen whenever there is a web service
> update. I'd like to eliminate or limit it but I have to find it
> first. I have pgBadger set up and logging turned up which is how I
> found the query.
>
> The developer says the query is not in his code and gave me the
> source code. It's pasqual. I see the other queries but I don't see
> the select * either in the code. He also added logging of the queries
> and the SELECT * FROM is missing. Is it possible another query
> creates the select * such as the two queries before it shown below?
I don't know if there are any Pascal ORMs around, but I know that some ORMs in the Java world to crazy things like that.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Sounds plausible, I'm still not sure what he uses to run the code, he just says its ODBC. Hopefully it doesn't have any more problems since its legacy code that hopefully will be retired.
Thanks,
Andrew
Try to set a statement timeout and see which part of code/application fails when this query is timed-out.
Will that be a logical way to figure out from where the query is being fired?
On Mon, Jan 20, 2014 at 10:46 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
Try to set a statement timeout and see which part of code/application fails when this query is timed-out.Will that be a logical way to figure out from where the query is being fired?
Yes, that makes sense, but unfortunately I don't have access to the system to test it. I did test all the queries that were written in the code I was given and none of them created SELECT * FROM queries, so I also suspected it was somewhere in the stack but was unable to learn what the code is being run in besides just being told its pasqual and ODBC. Hopefully it won't be a problem.
Thanks,
Andrew
Andrew