Hi,

I'm using Postgresql 8.1.3.

Recently I facing one problem, when the connection for postgresql grow up
to 45 or more, when I trigger a statement from WebApp
this statement will stuck forever.
I try to kill this transaction and then trigger the same statement again
but it still the same.
But this time I leave the transaction there and try to kill other
connections that is not in use.
It's weird that after I kill around 5-10 unused connections, the statement
start to run and finish.

Could anyone give me some idea how could this be?
Is it a bug of PostgreSQL?

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history where
organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

Thanks!


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

<kah_hang_ang@toray.com.my> wrote
>
> Recently I facing one problem, when the connection for postgresql grow
> up
> to 45 or more, when I trigger a statement from WebApp
> this statement will stuck forever.
> I try to kill this transaction and then trigger the same statement
> again
> but it still the same.
> But this time I leave the transaction there and try to kill other
> connections that is not in use.
> It's weird that after I kill around 5-10 unused connections, the
> statement
> start to run and finish.
>

I can hardly believe that's Postgres's problem. Are you sure the query
was processing by the server? Try to do:

ps -auxw|grep postgres

to see if you can see the query was stuck there.

Regards,
Qingqing

Hi

Newbie when it comes down to postgre.

OS: Windows XP SP2 Pro Dutch

When I run a DMS (Xinco) that uses postgre (I only use postgre for this), a
service is started. Xinco uses a separate limited Xinco User account.

After starting the service I see several postgre.exe services running of
various sizes. That's fine.

After an hour or so I see several postgre.exe services running (up to 200
and still increasing) and it is the postgre.exe 76kb that is in that huge
number (even when nobody uses Xinco, it still continous).

When I stop the PostgreSQL service then the large kb postgre.exe disappears
from the task manager ...but all those 76kb postgre.exe remain in memory.

Any ideas?

Stefan


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

This is the statement which having problem:
select count(distinct empno) as counter1 from pay_master_history
where empno in (select empno from pay_batch_basic_history where
organizationid like '015003%')
and processyear = '2006'
and processmonth = '05'
and processbatch = '1'

SELECT COUNT (*) FROM (
SELECT empno as counter1 from pay_master_history as a
INNER JOIN (select empno from pay_batch_basic_history where organizationid
like '015003%' and processyear = '2006'
and processmonth = '05'
and processbatch = '1') as b
ON b.empno = a.empno ) as count_result
-----------------------------
or just create the view and use inner join then count :b