Обсуждение: Accessing a database via AJAX scripts
I am not sure where my issue lies - apache, perl or postgresql, but as they say, one has to start somewhere. My goal is to have a perl cgi script (that is accessed using AJAX) perform some operations in a database using DBI. Some of the actions are likely to take a while so my intent was to have a table that the backend process periodically writes status messages into. The front end web page then uses another AJAX script to watch this table. I am coming unstuck, the monitoring script works but the backend processing doesn't. My first attempts would not show anything at the front end as the initial response from the backend was not getting through the system until the backend completely finished (even having set no buffering on STDOUT). After googling abit, it would appear that the way around this is to fork the back-end to allow apache to complete the initial response and then carry on the processing in the forked child process. I have opted to use Proc::Daemon to do this. Now I am getting a Pg error "could not receive data from server: Bad file descriptor". I have attempted re-opening STDOUT and STDERR in the forked process but that didn't make any difference. I am obviously missing something here. If anyone has done something similar and has it working I'd appreciate any help I can get. Glen -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 4446 Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015 "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations"
Glen Eustace <geustace@godzone.net.nz> writes:
> My goal is to have a perl cgi script (that is accessed using AJAX)
> perform some operations in a database using DBI. Some of the actions
> are likely to take a while so my intent was to have a table that the
> backend process periodically writes status messages into. The front end
> web page then uses another AJAX script to watch this table.
Your note is awfully short of concrete details, but I'm guessing the
basic reason why this wasn't working for you was you were doing all the
operations as a single transaction. The results of that transaction
wouldn't be visible to another one until it commits; so in particular
whatever changes it made in the status table wouldn't be visible.
You'd need to break the processing into something like
begin;
insert into status values ('working');
commit;
begin;
... do the useful stuff here ...
commit;
begin;
insert into status values ('done');
commit;
regards, tom lane
Hi Tom,
On 08/09/2010 03:33 AM, Tom Lane wrote:
> Your note is awfully short of concrete details, but I'm guessing the
> basic reason why this wasn't working for you was you were doing all the
> operations as a single transaction. The results of that transaction
> wouldn't be visible to another one until it commits; so in particular
> whatever changes it made in the status table wouldn't be visible.
> You'd need to break the processing into something like
> begin;
> insert into status values ('working');
> commit;
> begin;
> ... do the useful stuff here ...
> commit;
> begin;
> insert into status values ('done');
> commit;
Yes, it was a bit vague but I didn't want to go into too much detail as
I am still not sure I am even dealing with a postgresql issue.
Unfortunately your comments don't shed any light on the error I am getting;
"could not receive data from server: Bad file descriptor".
This suggests to me that I might have lost the connection in my forked
perl process to the back-end. Can you confirm or deny ? This might
give me some idea of where to keep looking.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446
Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
Glen Eustace <geustace@godzone.net.nz> writes:
> Unfortunately your comments don't shed any light on the error I am getting;
> "could not receive data from server: Bad file descriptor".
> This suggests to me that I might have lost the connection in my forked
> perl process to the back-end. Can you confirm or deny ?
Not on that much evidence; but trying to pass a libpq connection across
a fork is usually risky. The trouble is that both parent and child
processes now hold copies of the open socket connection, but only one of
them can safely work with it. It could be that you've closed the libpq
connection in one process and that killed its usability in the other.
regards, tom lane
> Not on that much evidence; but trying to pass a libpq connection across > a fork is usually risky. The trouble is that both parent and child > processes now hold copies of the open socket connection, but only one of > them can safely work with it. It could be that you've closed the libpq > connection in one process and that killed its usability in the other. Thanks Tom, that is pretty much the conclusion I came to. I think I need to close the db connection prior to the fork and then re-open in the new child. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Glen and Rosanne Eustace GodZone Internet Services, a division of AGRE Enterprises Ltd. P.O. Box 8020, Palmerston North, New Zealand 4446. Ph: +64 6 357 8168, Fax +64 6 357 8165, Mob: +64 27 542 4015 http://www.godzone.net.nz "A Ministry specialising in providing low-cost Internet Services to NZ Christian Churches, Ministries and Organisations."
The solution to my problems involved both of the issues Tom identified.
1. Proc::Daemon::Init() call closed the libpq connection in the parent.
Solution: deliberately close connection before call and open in the
child after the call.
2. The status updates were being written inside a transaction
Solution: open a second connection in the child process and use for
the status updates to allow the monitor script to select
them.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446
Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
On Sun, Aug 8, 2010 at 5:08 PM, Glen Eustace <geustace@godzone.net.nz> wrote: > Thanks Tom, that is pretty much the conclusion I came to. I think I need to > close the db connection prior to the fork and then re-open in the new child. > Yes, you pretty much have to do this. I usually do the close immediately after fork in the child, but set the DBI attribute InactiveDestroy on the handle so it doesn't destroy the parent's handle.