Thread: Re: Good open source mailing list system PHP / Postgresql
Re: Good open source mailing list system PHP / Postgresql
From
"Randolf Richardson, DevNet SysOp 29"
Date:
> An ISP I belong to uses Majordomo for their mailing list system. I'd like
> to encourage them to move to a system that uses a database, preferably
> psql which they already run on their server. Anything out there in Php?
I doubt there's anything in PHP since PHP is a language purely used for
the dynamic generation of web pages (and possibly other types of documents
for any other systems which are able to embed PHP on the server-side in a
similar fashion to Dynamic HTML in the way Apache HTTPd does).
You may have more success if you search on PERL or other languages.
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
Check Majordomo2, which is what we use for the lists ... it has the ability to use database backends now, at least for MySQL, and I know a good portion of code is in place for PostgreSQL also (just not sure to what extent, haven't braved it yet) ... On Wed, 19 Nov 2003, Randolf Richardson, DevNet SysOp 29 wrote: > > An ISP I belong to uses Majordomo for their mailing list system. I'd like > > to encourage them to move to a system that uses a database, preferably > > psql which they already run on their server. Anything out there in Php? > > I doubt there's anything in PHP since PHP is a language purely used for > the dynamic generation of web pages (and possibly other types of documents > for any other systems which are able to embed PHP on the server-side in a > similar fashion to Dynamic HTML in the way Apache HTTPd does). > > You may have more success if you search on PERL or other languages. > > -- > Randolf Richardson - rr@8x.ca > Inter-Corporate Computer & Network Services, Inc. > Vancouver, British Columbia, Canada > http://www.8x.ca/ > > This message originated from within a secure, reliable, > high-performance network ... a Novell NetWare network. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
> > An ISP I belong to uses Majordomo for their mailing list system. I'd like > > to encourage them to move to a system that uses a database, preferably > > psql which they already run on their server. Anything out there in Php? Some possible starting points are mailman and ezmlm-idx. I saw someone claim that mailman version 2.1 was supposed to allow you to use your own database. ezmlm-idx has support for mysql and so may not be too hard to get to work with postgres. It does require qmail for an MTA. Bruce Guenter has just volunteered to take over maintainance from Fred Lindberg so the project should start seeing some activity again.
On Wed, 19 Nov 2003, Randolf Richardson, DevNet SysOp 29 wrote: > > An ISP I belong to uses Majordomo for their mailing list system. I'd like > > to encourage them to move to a system that uses a database, preferably > > psql which they already run on their server. Anything out there in Php? > > I doubt there's anything in PHP since PHP is a language purely used for > the dynamic generation of web pages (and possibly other types of documents > for any other systems which are able to embed PHP on the server-side in a > similar fashion to Dynamic HTML in the way Apache HTTPd does). > > You may have more success if you search on PERL or other languages. This is simply not true. PHP comes in both a web ready embedded version, as well as a CLI version, and is quite capable, even of handling things like streams and such, and can even be used to write a daemon listening on a port quite easily. Just because it (probably) hasn't been used to write such a system doesn't mean you couldn't do it in PHP.
Re: Good open source mailing list system PHP / Postgresql
From
merlyn@stonehenge.com (Randal L. Schwartz)
Date:
>>>>> "scott" == scott marlowe <scott.marlowe@ihs.com> writes: scott> This is simply not true. PHP comes in both a web ready scott> embedded version, as well as a CLI version, and is quite scott> capable, even of handling things like streams and such, and can scott> even be used to write a daemon listening on a port quite scott> easily. But PHP is where Perl was five years ago, and continually plays catchup. If you want real work done, use the right tool. PHP is fine for nifty web pages for smallish sites, but Perl takes over when the real heavy lifting is needed. scott> Just because it (probably) hasn't been used to write such a scott> system doesn't mean you couldn't do it in PHP. You could do it in assembler too. But why? To keep from wasting precious human cycles, you need something with the code density and flexibility of Perl or better. Python, Ruby, that league. Not C, not Java, not PHP. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
We use PHP and Perl in our environments here, and are finding daily that there is less and less need to use Perl for much of anything anymore. At one point, the only holdup was forking stuff. PHP now has that. We have been successfully creating various standalone multi-forking servers and clients in PHP that access Postgresql, the network, and other resources without a problem. There are thousands and thousands of CLI PHP code in production over here. In terms of language denseness... PHP has as much (and much more built in) functionality as Perl, and if I dare to say so, is cleaner (though not as elegent) to code in that Perl. There's not much available at CPAN that is not already in PEAR, or over at PHPCLASSES. In fact, I would say, that the only catching up that PHP has to do, is in having a great resource such as CPAN. PHP has long ago caught up with Perl, and I believe the the OOP features available in PHP 5, will easily leapfrog over Perl. Having said that, we still code a lot of Perl, simply because of inertia and an existing codebase. Best regards, Ericson Smith Developer +-----------------------+----------------------------+ | http://www.did-it.com | "When I'm paid, I always | | eric@did-it.com | follow the job through. | | 516-255-0500 | You know that." -Angel Eyes| +-----------------------+----------------------------+ Randal L. Schwartz wrote: >>>>>>"scott" == scott marlowe <scott.marlowe@ihs.com> writes: >>>>>> >>>>>> > >scott> This is simply not true. PHP comes in both a web ready >scott> embedded version, as well as a CLI version, and is quite >scott> capable, even of handling things like streams and such, and can >scott> even be used to write a daemon listening on a port quite >scott> easily. > >But PHP is where Perl was five years ago, and continually plays >catchup. If you want real work done, use the right tool. PHP is fine >for nifty web pages for smallish sites, but Perl takes over when the >real heavy lifting is needed. > >scott> Just because it (probably) hasn't been used to write such a >scott> system doesn't mean you couldn't do it in PHP. > >You could do it in assembler too. But why? > >To keep from wasting precious human cycles, you need something with >the code density and flexibility of Perl or better. Python, Ruby, >that league. Not C, not Java, not PHP. > > >
On Mon, Dec 01, 2003 at 12:42:39PM -0800, Randal L. Schwartz wrote: > >>>>> "scott" == scott marlowe <scott.marlowe@ihs.com> writes: > > scott> This is simply not true. PHP comes in both a web ready > scott> embedded version, as well as a CLI version, and is quite > scott> capable, even of handling things like streams and such, and can > scott> even be used to write a daemon listening on a port quite > scott> easily. > > But PHP is where Perl was five years ago, and continually plays > catchup. If you want real work done, use the right tool. PHP is fine > for nifty web pages for smallish sites, but Perl takes over when the > real heavy lifting is needed. Someone pointed out on this list some time ago that you can work around the performance issue of starting a Perl interpreter and the compiling phase by using PersistentPerl. I have been using it since for smallish things and kinda like it but have not really had the chance to test it extensively. Apparently it can keep persistent connections reasonably well, for example (though some cruft on sub END is apparently needed ...) What's your opinion on the thing? Have you used it with PostgreSQL? Persistent DBI connections and such? Maybe you could write an article on the subject? :-) (Maybe this belongs to a Perl list, but I'm on none ... any suggestion of a better place?) -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "There was no reply" (Kernel Traffic)
On 1 Dec 2003, Randal L. Schwartz wrote: > >>>>> "scott" == scott marlowe <scott.marlowe@ihs.com> writes: > > scott> This is simply not true. PHP comes in both a web ready > scott> embedded version, as well as a CLI version, and is quite > scott> capable, even of handling things like streams and such, and can > scott> even be used to write a daemon listening on a port quite > scott> easily. > > But PHP is where Perl was five years ago, and continually plays > catchup. If you want real work done, use the right tool. PHP is fine > for nifty web pages for smallish sites, but Perl takes over when the > real heavy lifting is needed. Bold assertion, with little to back it up. Name something Perl is so much better at than PHP, and you'll likely find that PHP now does it and does it well. We quit programming in Perl a couple years ago in my shop, as it was far easier to configure PHP on a server and have it do what we needed. > scott> Just because it (probably) hasn't been used to write such a > scott> system doesn't mean you couldn't do it in PHP. > > You could do it in assembler too. But why? Ignoring your assembler point, as it's a poor comparison, and we both know it... Why do it in PHP: Because it's a good choice for such things, having Perl's easy string handling with C's simple file interface functions. Because there's always somewhere else to go that no one else has thought of, and a different way of doing it. Because it's a good language that has a lot of people who say nebulous bad things about it but have pitiful little real experience with it? I'm not sure. > To keep from wasting precious human cycles, you need something with > the code density and flexibility of Perl or better. Python, Ruby, > that league. Not C, not Java, not PHP. Again, show me an area where PHP is actually deficient here. Something Perl or Ruby does better that would pertain to a mailing list. Don't just wave your hands around, give us a concrete example of its short comings.
On Mon, Dec 01, 2003 at 04:03:02PM -0500, Ericson Smith wrote: > PHP has long ago caught up with Perl, and I believe the the OOP features > available in PHP 5, will easily leapfrog over Perl. Having said that, we > still code a lot of Perl, simply because of inertia and an existing > codebase. Huh, when is PHP 5 due? When is Perl 6 due? Does Parrot have an working implementation? Is it useful for anything? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Jude: I wish humans laid eggs Ringlord: Why would you want humans to lay eggs? Jude: So I can eat them
"scott.marlowe" <scott.marlowe@ihs.com> writes: > Again, show me an area where PHP is actually deficient here. Something > Perl or Ruby does better that would pertain to a mailing list. Don't just > wave your hands around, give us a concrete example of its short comings. Error handling. The lack of exceptions is driving me absolutely insane. But what does all this all this have to do with Postgres? -- greg
On 1 Dec 2003, Greg Stark wrote: > > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > > Again, show me an area where PHP is actually deficient here. Something > > Perl or Ruby does better that would pertain to a mailing list. Don't just > > wave your hands around, give us a concrete example of its short comings. > > Error handling. The lack of exceptions is driving me absolutely insane. Have you had a look at this: http://www.phpclasses.org/mirrors.html?page=%2Fbrowse.html%2Fpackage%2F345.html > But what does all this all this have to do with Postgres? Tangential, tangential. But it appears every time someone mentions using PHP around here he gets bashed for it. It's tiring to listen to.
On Mon, 2003-12-01 at 15:42, Randal L. Schwartz wrote: > But PHP is where Perl was five years ago, and continually plays > catchup. If you want real work done, use the right tool. PHP is fine > for nifty web pages for smallish sites, but Perl takes over when the > real heavy lifting is needed. > > scott> Just because it (probably) hasn't been used to write such a > scott> system doesn't mean you couldn't do it in PHP. > > You could do it in assembler too. But why? > > To keep from wasting precious human cycles, you need something with > the code density and flexibility of Perl or better. Python, Ruby, > that league. Not C, not Java, not PHP. Sorry, but this doesn't match my experiences (well, I agree with C and Java). I built and manage the CMS for www.mcgill.ca. It currenlty has over 10k pages in the system, and has distributed content management to over 1000 people on campus. Previous versions of it were written entirely in Perl, and I was pretty happy with it at the time. However, about two years ago, we re-wrote it in PHP and haven't regretted it one bit. Some of the advantages we noticed: 1) Development time was much faster. 2) It is much easier to find/hire PHP programmers than Perl programmers. 3) Building templates with embedded code is much easier/more intuitive in PHP than Perl. 4) Despite using rigid coding standards, our old PHP code is much easier to read than our old Perl code. 5) When a programmer gets stuck trying to find a solution to a particular problem, often a simple google search finds a hint/solution. This wasn't always the case with Perl. 6) Even now, after using Perl for a number of years longer than PHP, I still find myself opening the camel and/or lama books for reminders. While these books are well written :-), I don't have to do so nearly as often in PHP, and when I do need info on a function -> http://ca.php.net/functionname. Having said that, there are still lots of things in Perl that I love and wish were in PHP. The Perl community seems much better at organizing the language development. PHP has all sorts of really stupid inconsistencies, like string replacement functions that take parameters in different orders (needle, haystack, subject), (haystack, needle, subject), etc.. I suspect that this will improve as the language matures and the community gets better organized. Also, things seem less likely to break when a new version of Perl comes out, whereas often minor versions of PHP break all sorts of tiny things that one wouldn't expect. This is a *BIG* PITA. So, while I still do all of my batch processing and system stuff in Perl, I have no plans on going back to doing web work with it. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017
Re: Good open source mailing list system PHP / Postgresql
From
merlyn@stonehenge.com (Randal L. Schwartz)
Date:
>>>>> "Christopher" == Christopher Murtagh <christopher.murtagh@mcgill.ca> writes: Christopher> 3) Building templates with embedded code is much Christopher> easier/more intuitive in PHP than Perl. Did you look at Apache::Template and Template-Toolkit? The work in that area has really become a PHP killer for me. If you did, and still have the opinion you have, I'd be curious. I agree with your general observation: Raw Perl for people who could or want to code in PHP is probably the wrong solution. But the combination of Perl for the heavy lifting, and the TT2-minilanguage for the "designers" and casual use, is a very hard-to-beat combo, in my observation. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
On Wed, 2003-12-03 at 11:08, Randal L. Schwartz wrote: > >>>>> "Christopher" == Christopher Murtagh <christopher.murtagh@mcgill.ca> writes: > > Christopher> 3) Building templates with embedded code is much > Christopher> easier/more intuitive in PHP than Perl. > > Did you look at Apache::Template and Template-Toolkit? The work in > that area has really become a PHP killer for me. If you did, and still > have the opinion you have, I'd be curious. I did, but that was some time ago, I saw a talk about it when YAPC was at McGill 2+(?) years ago. I was definitely impressed with it, and we had considered moving to it (this was right around the time when we were considering rebuilding in PHP or Perl). Also, at the time one of the big reasons why it seemed interesting was because I hadn't done any PHP programming, and I think version 4 was either still beta or really new (I never would have considered moving from Perl to PHP 3.x). Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, - -- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: > Someone pointed out on this list some time ago that you can work around > the performance issue of starting a Perl interpreter and the compiling > phase by using PersistentPerl. you should use mod_perl, but it is *much* more then "CGI scripting on steroids": http://perl.apache.org/ mod_perl and PostgreSQL is a very good combination for middle to large web applications. With mod_perl you have the *full* power of the Apache web server, e.g. access to the Apache API and callback hooks. Embperl (embedded Perl in HTML with automatic forms, tables, ..., see: http://www.ecos.de/embperl/en/) with PostgreSQL is a very good combination for small web applications. Easyer and much more powerfull then PHP. You may also look here: http://theory.uwinnipeg.ca/CPAN/data/ for thousands easy installable Perl modules (just type "cpan install Module::Name" and wait), including DBI etc. Compare it with PHP ... OK, this was a joke. Ciao Alvar - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html ** ODEM.org-Tour: http://tour.odem.org/ ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (FreeBSD) iD8DBQE/0K0KOndlH63J86wRAjR7AKCCK06FMjON4SnsJ0/DqxcGe/G89wCfcILU AaG7aYZqzFvRMacYa/B6p38= =ky4l -----END PGP SIGNATURE-----
Quoting Alvar Freude <alvar@a-blast.org>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > - -- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: > > > Someone pointed out on this list some time ago that you can work around > > the performance issue of starting a Perl interpreter and the compiling > > phase by using PersistentPerl. > > you should use mod_perl, but it is *much* more then "CGI scripting on > steroids": > > http://perl.apache.org/ > > > mod_perl and PostgreSQL is a very good combination for middle to large web > applications. With mod_perl you have the *full* power of the Apache web > server, e.g. access to the Apache API and callback hooks. > > Embperl (embedded Perl in HTML with automatic forms, tables, ..., see: > http://www.ecos.de/embperl/en/) with PostgreSQL is a very good combination > for small web applications. Easyer and much more powerfull then PHP. > > > You may also look here: http://theory.uwinnipeg.ca/CPAN/data/ for thousands > easy installable Perl modules (just type "cpan install Module::Name" and > wait), including DBI etc. Compare it with PHP ... OK, this was a joke. > > > Ciao > Alvar > > - -- > ** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.org/ > ** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html > ** ODEM.org-Tour: http://tour.odem.org/ > ** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/ > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.3 (FreeBSD) > > iD8DBQE/0K0KOndlH63J86wRAjR7AKCCK06FMjON4SnsJ0/DqxcGe/G89wCfcILU > AaG7aYZqzFvRMacYa/B6p38= > =ky4l > -----END PGP SIGNATURE----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > Not that I want to see a language perpetuate but I just had to chime in here. My standard developement environment is Apache2-SSL, mod_perl and PostgreSQL. It has been quite awhile now. In my opinion, you have most of the functionality of Java in a more mature language with an easier syntax. Couple that with being able to maintain persistant connections to PostgreSQL from Apache on the backend and you have an environment with checking out. If you were turned off by mod_perl 1.0, 2.0 (well 1.99_x) has some new and refined methods. The web site (previously posted) is MUCH, MUCH better than before- more documentation and examples. I would been able to write my PostgreSQL Authentication module in half the time! -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Re: Perl / mod_perl / PostgreSQL was: Good open source mailing list system PHP / Postgresql
From
Alvaro Herrera
Date:
On Fri, Dec 05, 2003 at 05:06:34PM +0100, Alvar Freude wrote: > - -- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: > > > Someone pointed out on this list some time ago that you can work around > > the performance issue of starting a Perl interpreter and the compiling > > phase by using PersistentPerl. > > you should use mod_perl, but it is *much* more then "CGI scripting on > steroids": Well, my applications are not web based at all, so mod_perl is not an option in this case. Though I still don't see why should pick mod_perl over PersistentPerl, if I were to build a web-app? I have used HTML::Template for, well, HTML templates; though it is not exactly pretty, it works as intended. (Smarty templates for PHP appear to be much better, but I don't like PHP.) > Embperl (embedded Perl in HTML with automatic forms, tables, ..., see: > http://www.ecos.de/embperl/en/) with PostgreSQL is a very good combination > for small web applications. Easyer and much more powerfull then PHP. Hm. Depends on what you call small, I guess. I swear I will never put HTML in code (or vice versa) again. Not in PHP nor Perl. Maybe this will be over when I switch to Python. Eventually ... -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Hoy es el primer d�a del resto de mi vida"
Re: Perl / mod_perl / PostgreSQL was: Good open source mailing list system PHP / Postgresql
From
"Keith C. Perry"
Date:
Quoting Alvaro Herrera <alvherre@dcc.uchile.cl>: > On Fri, Dec 05, 2003 at 05:06:34PM +0100, Alvar Freude wrote: > > > - -- Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: > > > > > Someone pointed out on this list some time ago that you can work around > > > the performance issue of starting a Perl interpreter and the compiling > > > phase by using PersistentPerl. > > > > you should use mod_perl, but it is *much* more then "CGI scripting on > > steroids": > > Well, my applications are not web based at all, so mod_perl is not an option > in this case. [sniped] Actually that is not quiet true. You can use Apache as a Perl server. I've never done it before but what I gather from the documentation is that you can have the server run your code. There is a start file I think for all the perl related "stuff" for Apache and in that file you can have a script load. > Though I still don't see why should pick mod_perl over PersistentPerl, if I > were to build a web-app? I have used HTML::Template for, well, HTML > templates; > though it is not exactly pretty, it works as intended. (Smarty templates > for PHP appear to be much better, but I don't like PHP.) You have perl write the template on the fly- no need for anything else really. On the more basic level, you could use put your HTML page in a perl script and replace what you want with variables. On the otherside of the spectrum you can have perl read/send parameters to your users and have pages build dynamically based on that. I generally do this way so I rarely write a complete page of HTML. I just use perl to assemble those pieces based on user input and the required business logic. The EIS is of course PostgreSQL. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
Re: Perl / mod_perl / PostgreSQL was: Good open source mailing list system PHP / Postgresql
From
Harald Fuchs
Date:
In article <20031205173135.GA19630@dcc.uchile.cl>, Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > Though I still don't see why should pick mod_perl over PersistentPerl, if I > were to build a web-app? I have used HTML::Template for, well, HTML templates; > though it is not exactly pretty, it works as intended. Do you mean ``<TMPL_VAR NAME="myvar">''? That's the reason why I switched to the Template Toolkit where you just write ``[%myvar%]''.
Hello,
I want to know whether PostgreSQL support cancel() [cancel
JDBCStatement]. I want to abort a long running query issued/executed by a
java application using JDBC driver to connect to the database. Thanks.
-Prahalad
Hi
I have a function called from a java app (via jdbc) which identifies
images awaiting processing. This is determined by checking the
WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being
processed, 3=complete).
The (jdbc) connection to the database is a standard one so I suspect that
the transaction isolation level is Read uncommitted.
What I need is for the call to GetNextChangedImageHeader() to return the
WDResourceID of the next WPImageHeader record awaiting processing.
The way it is written (I think that) it will either return the ID of a
WPImageHeader record that genuinely is awaiting processing (if one is
available), or will return -1 because it waited on a row lock which was
released by another transaction on the same WPImageHeader record, but
whose WPImageStateID is now no longer 1.
Does this look correct?
Thanks
John Sidney-Woollett
ps The function was converted from Oracle which allows a "select for
update NOWAIT" which meant that the procedure was written very differently
because this doesn't block, and either returns a row, or fails.
CREATE OR REPLACE FUNCTION GetNextChangedImageHeader() RETURNS integer AS '
-- returns the next image header (WDResourceID) awaiting processing
-- and changes the state of the record to being processed
-- Also modifies the state of an unprocessed (child) Image records
-- Either returns a WDResourceID or -1 if no record need processing
DECLARE
vIsLocked boolean := false;
vWDResourceID integer := -1;
vImageStateID integer := null;
BEGIN
-- locate the first (unlocked?) ImageHeader awaiting processing
select WDResourceID, WPImageStateID
into vWDResourceID, vImageStateID
from WPImageHeader
where WPImageStateID = 1
for update
limit 1;
-- check that an image header record is available
if (vWDResourceID is null) then
return -1;
end if;
-- check that the state is really awaiting processing (=1)
if (vImageStateID > 1) then
return -1;
end if;
-- change the state to being processed
update WPImageHeader set WPImageStateID = 2
where WDResourceID = vWDResourceID;
-- mark the (child) image records as being processed too
update WPImage set WPImageStateID = 2
where WPImageStateID = 1
and WDResourceID = vWDResourceID;
return vWDResourceID;
END;
' LANGUAGE 'plpgsql';
On 09/12/2003 11:47 John Sidney-Woollett wrote: > Hi > > I have a function called from a java app (via jdbc) which identifies > images awaiting processing. This is determined by checking the > WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being > processed, 3=complete). > > The (jdbc) connection to the database is a standard one so I suspect that > the transaction isolation level is Read uncommitted. Unlikely as PostgreSQL doesn't support read uncommitted... > > What I need is for the call to GetNextChangedImageHeader() to return the > WDResourceID of the next WPImageHeader record awaiting processing. > > The way it is written (I think that) it will either return the ID of a > WPImageHeader record that genuinely is awaiting processing (if one is > available), or will return -1 because it waited on a row lock which was > released by another transaction on the same WPImageHeader record, but > whose WPImageStateID is now no longer 1. > > Does this look correct? I think you need to play with a couple of psql sessions to sort this out. I think you might have a race condition here. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
> Unlikely as PostgreSQL doesn't support read uncommitted...
You're right - Postgres only offers two levels "PostgreSQL offers the Read
Committed and Serializable isolation levels."
> I think you need to play with a couple of psql sessions to sort this out.
> I think you might have a race condition here.
Following your suggestion, I made a test. In my tests with two PSQL
sessions and 1 row in the WPImageHeader table, the following occured:
Session 1: start transaction;
Session 1: select * from WPImageHeader where WDResourceID=1 for update;
Session 2: select GetNextChangedImageHeader();
{This call (Session 2) blocks until Session 1 either commits, or issues a
rollback}
Session 1: update WPImageHeader set WPImageStateID=2 where WDResourceID=1;
Session 2: {returns} -1
In other words GetNextChangedImageHeader() will block if another thread is
also calling GetNextChangedImageHeader() and they are both trying to
access the same record (reading the uncommitted values).
Is there a way to read the WPImageHeader table in such as way that you
skip any rows which have (any kind of) locks on them?
John Sidney-Woollett
ps I attach the function code again (just in case)
CREATE OR REPLACE FUNCTION GetNextChangedImageHeader() RETURNS integer AS '
-- returns the next image header (WDResourceID) awaiting processing
-- and changes the state of the record to being processed
-- Also modifies the state of an unprocessed (child) Image records
-- Either returns a WDResourceID or -1 if no record need processing
DECLARE
vWDResourceID integer := -1;
vImageStateID integer := null;
BEGIN
-- locate the first (unlocked?) ImageHeader awaiting processing
select WDResourceID, WPImageStateID
into vWDResourceID, vImageStateID
from WPImageHeader
where WPImageStateID = 1
for update
limit 1;
-- check that an image header record is available
if (vWDResourceID is null) then
return -1;
end if;
-- check that the state is really awaiting processing (=1)
if (vImageStateID > 1) then
return -1;
end if;
-- change the state to being processed
update WPImageHeader set WPImageStateID = 2
where WDResourceID = vWDResourceID;
-- mark the (child) image records as being processed too
update WPImage set WPImageStateID = 2
where WPImageStateID = 1
and WDResourceID = vWDResourceID;
return vWDResourceID;
END;
' LANGUAGE 'plpgsql';
On 09/12/2003 14:01 John Sidney-Woollett wrote:
> > Unlikely as PostgreSQL doesn't support read uncommitted...
>
> You're right - Postgres only offers two levels "PostgreSQL offers the
> Read
> Committed and Serializable isolation levels."
>
> > I think you need to play with a couple of psql sessions to sort this
> out.
> > I think you might have a race condition here.
>
> Following your suggestion, I made a test. In my tests with two PSQL
> sessions and 1 row in the WPImageHeader table, the following occured:
>
> Session 1: start transaction;
> Session 1: select * from WPImageHeader where WDResourceID=1 for update;
> Session 2: select GetNextChangedImageHeader();
>
> {This call (Session 2) blocks until Session 1 either commits, or issues a
> rollback}
>
> Session 1: update WPImageHeader set WPImageStateID=2 where
> WDResourceID=1;
> Session 2: {returns} -1
What you don't know is which condition prompted the -1 return. According
to the User Guide section 9.2.1, when session 1 commits, session 2 should
re-evaluate its select and return a different (or no) row. So I'd expect
it to be the first test which triggers the -1 return in which case you
shold be OK. Might be worth checking though...
>
> In other words GetNextChangedImageHeader() will block if another thread
> is
> also calling GetNextChangedImageHeader() and they are both trying to
> access the same record (reading the uncommitted values).
>
> Is there a way to read the WPImageHeader table in such as way that you
> skip any rows which have (any kind of) locks on them?
Not that I know of.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
On 9 Dec 2003, Harald Fuchs wrote:
> > Though I still don't see why should pick mod_perl over
> PersistentPerl, if I > were to build a web-app? I have used
> HTML::Template for, well, HTML templates; > though it is not exactly
> pretty, it works as intended.
>
> Do you mean ``<TMPL_VAR NAME="myvar">''?
>
> That's the reason why I switched to the Template Toolkit where you
> just write ``[%myvar%]''.
I have used both. Template-Toolkit because of recommendations of 'Perl'
people and HTML::Template on a Windows system when I had problems
installing Template-Toolkit. Both add a some functionality that is IMO a
little heavy for most needs.
Oh yeah I'm using the Template-Toolkit where I'm building standard
text-only emails which it seemed to fit quite well and HTML::Template for
... HTML.
Cheers,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
On Tue, 9 Dec 2003, H A Prahalad wrote: > > Hello, > I want to know whether PostgreSQL support cancel() [cancel > JDBCStatement]. I want to abort a long running query issued/executed by a > java application using JDBC driver to connect to the database. Thanks. > The JDBC driver does support Statement.cancel(), but the tricky part is that you need to have access to the Statement object that issued the original query. You can't just open a new connection and cancel another query. Kris Jurka
Thanks. I have access to the Statement object that issued the original query. I used the latest JDBC driver for PostgreSQl7.3 and JDK1.4 and I used threads to achieve it. Thanks once again. prahalad On Thu, 11 Dec 2003, Kris Jurka wrote: > > > On Tue, 9 Dec 2003, H A Prahalad wrote: > > > > > Hello, > > I want to know whether PostgreSQL support cancel() [cancel > > JDBCStatement]. I want to abort a long running query issued/executed by a > > java application using JDBC driver to connect to the database. Thanks. > > > > The JDBC driver does support Statement.cancel(), but the tricky part is > that you need to have access to the Statement object that issued the > original query. You can't just open a new connection and cancel another > query. > > Kris Jurka >