agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfunction call
13+ messages / 4 participants
[nested] [flat]
* function call
@ 2014-08-05 11:28 Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 12:56 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-05 11:28 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I have a SET returning function (defined as RETURNS TABLE), it takes 2
parameters and always returns one row with 3 columns. Now when I run it
from pgAdmin it takes around 5 seconds but when I run it from the
application its around 3 minutes (same parameters of course). It shows up
in the postgres' status server and odbc log right away so I believe the
applications has nothing to do with it. Where should I start looking ?
I'm running postgres 9.1
regards
mk
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-05 12:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:05 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2014-08-05 12:56 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; pgsql-sql
On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:
> Hi list,
>
> I have a SET returning function (defined as RETURNS TABLE), it takes 2
> parameters and always returns one row with 3 columns. Now when I run it
> from pgAdmin it takes around 5 seconds but when I run it from the
> application its around 3 minutes (same parameters of course). It shows
> up in the postgres' status server and odbc log right away so I believe
> the applications has nothing to do with it. Where should I start looking ?
More information would be helpful.
For now I am guessing in your application you are using ODBC to connect
to the server, correct? This is an additional step not found in the
pgAdmin route which may be a clue.
More questions:
1) What does the function do?
2) Where are the database and pgAdmin and the application in relation to
each other? In other words are they on the same machine or are they
going across a network?
3) What is the application?
>
> I'm running postgres 9.1
>
>
> regards
> mk
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 12:56 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
@ 2014-08-06 07:05 ` Marcin Krawczyk <jankes.mk@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-06 07:05 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
>
>
> For now I am guessing in your application you are using ODBC to connect to
> the server, correct?
>
Correct.
>
> 1) What does the function do?
>
It calculates a lot of things and issues documents. I have much more
complicated functions and never experienced such problems with this
application.
>
> 2) Where are the database and pgAdmin and the application in relation to
> each other? In other words are they on the same machine or are they going
> across a network?
>
The application and the pgAdmin are on the same client, connecting to
server through LAN.
> 3) What is the application?
>
It's an ERP system.
> 4) What happens if you turn off the ODBC logging?
>
It does not change anything.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-05 13:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2014-08-05 13:01 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; pgsql-sql
On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:
> Hi list,
>
> I have a SET returning function (defined as RETURNS TABLE), it takes 2
> parameters and always returns one row with 3 columns. Now when I run it
> from pgAdmin it takes around 5 seconds but when I run it from the
> application its around 3 minutes (same parameters of course). It shows
> up in the postgres' status server and odbc log right away so I believe
> the applications has nothing to do with it. Where should I start looking ?
Just had another thought.
I found in the past that ODBC logging can slow things down considerably.
What happens if you turn off the ODBC logging?
>
> I'm running postgres 9.1
>
>
> regards
> mk
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
@ 2014-08-06 07:41 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 12:03 ` Re: function call John L. Poole <jlpoole56@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-06 07:41 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
It's not ODBC, I've just tested with simple C# through the same odbc source
and the function takes 4 seconds as well.
regards
mk
2014-08-05 15:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
> On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:
>
>> Hi list,
>>
>> I have a SET returning function (defined as RETURNS TABLE), it takes 2
>> parameters and always returns one row with 3 columns. Now when I run it
>> from pgAdmin it takes around 5 seconds but when I run it from the
>> application its around 3 minutes (same parameters of course). It shows
>> up in the postgres' status server and odbc log right away so I believe
>> the applications has nothing to do with it. Where should I start looking ?
>>
>
> Just had another thought.
>
> I found in the past that ODBC logging can slow things down considerably.
>
> What happens if you turn off the ODBC logging?
>
>
>
>
>> I'm running postgres 9.1
>>
>>
>> regards
>> mk
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-06 12:03 ` John L. Poole <jlpoole56@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: John L. Poole @ 2014-08-06 12:03 UTC (permalink / raw)
To: pgsql-sql
Wouldn't this issue be an excellent candidate to create a test example
and then log a bug with all the details so others can recreate the
issue? It's very difficult to divine what the problem is unless you can
provide a working example that demonstrates it.
John
On 8/6/2014 12:41 AM, Marcin Krawczyk wrote:
> It's not ODBC, I've just tested with simple C# through the same odbc
> source and the function takes 4 seconds as well.
>
>
> regards
> mk
>
>
> 2014-08-05 15:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
> On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:
>
> Hi list,
>
> I have a SET returning function (defined as RETURNS TABLE), it
> takes 2
> parameters and always returns one row with 3 columns. Now when
> I run it
> from pgAdmin it takes around 5 seconds but when I run it from the
> application its around 3 minutes (same parameters of course).
> It shows
> up in the postgres' status server and odbc log right away so I
> believe
> the applications has nothing to do with it. Where should I
> start looking ?
>
>
> Just had another thought.
>
> I found in the past that ODBC logging can slow things down
> considerably.
>
> What happens if you turn off the ODBC logging?
>
>
>
>
> I'm running postgres 9.1
>
>
> regards
> mk
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-06 13:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 09:50 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 11:13 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Adrian Klaver @ 2014-08-06 13:58 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
On 08/06/2014 12:41 AM, Marcin Krawczyk wrote:
> It's not ODBC, I've just tested with simple C# through the same odbc
> source and the function takes 4 seconds as well.
So it would seem that the issue is with ERP application returning the
information.
This probably hinges on what you are defining as returning?
In your function description you note that the function issues documents.
Is that what you are using to determine that the function has completed?
If so, do you return the documents in the pgAdmin case as well?
If not, how are measuring success(returning) in the application case?
>
>
> regards
> mk
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
@ 2014-08-11 09:50 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 19:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-11 09:50 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
I've managed to determine that there's a trigger dependency tree that slows
things down. When I DISABLE one of them the function takes under 30
seconds, still more than from pgAdmin but much quicker than before.
regards
mk
2014-08-06 15:58 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
> On 08/06/2014 12:41 AM, Marcin Krawczyk wrote:
>
>> It's not ODBC, I've just tested with simple C# through the same odbc
>> source and the function takes 4 seconds as well.
>>
>
> So it would seem that the issue is with ERP application returning the
> information.
>
> This probably hinges on what you are defining as returning?
>
> In your function description you note that the function issues documents.
>
> Is that what you are using to determine that the function has completed?
>
> If so, do you return the documents in the pgAdmin case as well?
>
> If not, how are measuring success(returning) in the application case?
>
>
>
>>
>> regards
>> mk
>>
>>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 09:50 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-11 19:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-18 07:57 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2014-08-11 19:01 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
On 08/11/2014 02:50 AM, Marcin Krawczyk wrote:
> I've managed to determine that there's a trigger dependency tree that
> slows things down. When I DISABLE one of them the function takes under
> 30 seconds, still more than from pgAdmin but much quicker than before.
I am with David. I am not sure you are calling the same function in each
case.
Are you sure you have not overloaded the function name and a difference
in search_paths is not causing a different version to be run?
>
> regards
> mk
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 09:50 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 19:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
@ 2014-08-18 07:57 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-18 22:16 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-18 07:57 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
I wish I was doing that :) that's the first thing I checked.
pozdrowienia
mk
2014-08-11 21:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
> On 08/11/2014 02:50 AM, Marcin Krawczyk wrote:
>
>> I've managed to determine that there's a trigger dependency tree that
>> slows things down. When I DISABLE one of them the function takes under
>> 30 seconds, still more than from pgAdmin but much quicker than before.
>>
>
> I am with David. I am not sure you are calling the same function in each
> case.
>
> Are you sure you have not overloaded the function name and a difference in
> search_paths is not causing a different version to be run?
>
>
>
>> regards
>> mk
>>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 09:50 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 19:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-18 07:57 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-18 22:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2014-08-18 22:16 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
On 08/18/2014 12:57 AM, Marcin Krawczyk wrote:
> I wish I was doing that :) that's the first thing I checked.
Hmmm.
So what happens when you run it in the psql client?
Also you say it is only slow when run from your ERP application. Running
it from pgAdmin or directly from a C# program is fast.
So is there a log for that ERP application that might shed some light on
what is going on when the function is run from the application?
>
>
> pozdrowienia
> mk
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
@ 2014-08-11 11:13 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 16:13 ` Re: function call David G Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Marcin Krawczyk @ 2014-08-11 11:13 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
>
> So it would seem that the issue is with ERP application returning the
>> information.
>>
>
> This probably hinges on what you are defining as returning?
>
> In your function description you note that the function issues documents.
>
> Is that what you are using to determine that the function has completed?
>
The function makes some calculations, then INSERTs and UPDATEs data (the
aforementioned documents) then returns 1 row with 3 columns with
information on whether there were any errors or how many documents were
created and so on.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function call
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 13:58 ` Re: function call Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 11:13 ` Re: function call Marcin Krawczyk <jankes.mk@gmail.com>
@ 2014-08-11 16:13 ` David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: David G Johnston @ 2014-08-11 16:13 UTC (permalink / raw)
To: pgsql-sql
Marcin Krawczyk-2 wrote
>>
>> So it would seem that the issue is with ERP application returning the
>>> information.
>>>
>>
>> This probably hinges on what you are defining as returning?
>>
>> In your function description you note that the function issues documents.
>>
>> Is that what you are using to determine that the function has completed?
>>
>
> The function makes some calculations, then INSERTs and UPDATEs data (the
> aforementioned documents) then returns 1 row with 3 columns with
> information on whether there were any errors or how many documents were
> created and so on.
Are you positive the pgAdmin and your application are talking to the same
machine?
Is there any variation in run-time for the two clients?
Can you get both of them to run the same query but using EXPLAIN ANALYZE?
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/function-call-tp5813776p5814428.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2014-08-18 22:16 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-05 11:28 function call Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 12:56 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:05 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-05 13:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-06 07:41 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-06 12:03 ` John L. Poole <jlpoole56@gmail.com>
2014-08-06 13:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 09:50 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 19:01 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-18 07:57 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-18 22:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2014-08-11 11:13 ` Marcin Krawczyk <jankes.mk@gmail.com>
2014-08-11 16:13 ` David G Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox