Re: [HACKERS] DB Tuning Notes for comment...
Robert Treat wrote:
On Sun, 2002-12-08 at 09:41, Philip Warner wrote:
Any comments or suggestions would be welcome.
first and foremost, this is really excellent work! We need to look into
getting this info into the standard documentation and/or Bruce's tuning
guide.
Seconded!
Tuning
==
1. max_fsm_relations
First of all, the free space manager is useless at managing free space if
it can not map all relations (including system relations and toast
relations). The following query should give the correct ballpark:
select count(*) from pg_class where not relkind in ('i','v');
It should be noted that if you have multiple databases, you'll need to
get the number of relations minus the system tables for each database,
then add in the number of system tables.
Set max_fsm_relations to a number greater than this. Add extra to deal with
any tables you will create etc. It costs 40 bytes per table, so be
generous - if it is set too low, you will get bizarre space usage.
2. VACUUM Frequency
---
Ideally VACUUM should run constantly; a future version will support
something like it. But for now, vacuum should be run when a significant
amount of data has been inserted, updated or deleted. The definition of
'significant' is not immediately obvious.
I don't think this is entirely true. On tables that have large numbers
of inserts, but no updates or deletes, you do not need to run vacuum. It
might be helpful to run analyze on these tables if your inserting enough
data to change the statistical relationships, but vacuum itself is not
needed.
In my experience I've seen tables with numerous indexes continue to
benefit greatly from vacuum/vacuum full operations when large volumes of
inserts are performed. This is true even when the update/delete activity
on the base table itself is manageable. While dropping and recreating
the index after loading is possible in some cases, my general comment is
that index maintenance is an issue you should keep in mind when
designing your vacuum strategy.
Most tables will *not* be updated frequently in most databases; such tables
can be vacuumed irregularly, or vacuumed when the more frequently updated
tables are vacuumed.
In our specific case we have one table that has a few rows ( 1000), but it
is updated as many as 3 times per second. In this case, we chose a 5 minute
interval, which results in at worst 1000 'dead' rows in the table as a
result of the updates. Since it was such a small table, we saw no reason to
vacuum every minute, or even constantly.
I have some similar tables in my system, with between 250 and 3500 rows.
These tables turn over at least every 15 minutes, so I have decided on a
10 minute vacuum interval. As with Phillip's, since they are small
tables, more frequent vacuuming seemed excessive.
For larger or more complex tables, the output of VACUUM ANALYZE must be used.
snip
again, great work Philip.
Robert Treat
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
ss
Scott Shattuck
Technical Pursuit Inc.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] DB Tuning Notes for comment...
Tom Lane wrote: Scott Shattuck [EMAIL PROTECTED] writes: Robert Treat wrote: I don't think this is entirely true. On tables that have large numbers of inserts, but no updates or deletes, you do not need to run vacuum. In my experience I've seen tables with numerous indexes continue to benefit greatly from vacuum/vacuum full operations when large volumes of inserts are performed. This is true even when the update/delete activity on the base table itself is manageable. This is hard to believe, as VACUUM does not even touch the indexes unless it has found deletable tuples --- and I am quite certain that btree indexes, at least, do not do any VACUUM-time reorganization beyond deleting deletable entries. (I wouldn't swear to it one way or the other for GiST though.) Robert's opinion coincides with what I know of the code. Willing to learn here but skipping a vacuum full has caused some issues for us. Here's some data from a recent 3 day test run that was done with regular vacuums but not vacuum fulls. When running with vacuum full the indexes remain in line: nsuite-10=# select relname, relpages, reltuples from pg_class where relname not like 'pg_%' order by reltuples desc; -[ RECORD 1 ]-- relname | directory_fullpath_ix relpages | 96012 reltuples | 1.38114e+06 -[ RECORD 2 ]-- relname | directory_pkey relpages | 16618 reltuples | 1.38114e+06 -[ RECORD 3 ]-- relname | directory relpages | 23924 reltuples | 59578 snip Needless to say, the system performance was pathetic but the test did serve to highlight this index issue. Anyone want to give a quick summary of index maintenance or give me a pointer into the codebase where someone who's not a C expert might still get a sense of what's being done? I'd really like to understand how an index can get so completely out of whack after a weekend of testing. It seems you're telling me that the data here proves there's an update or delete going on somewhere in the system, even though this test is of a database initialization driven by a stored procedure with no update or delete operations targeting the directory table. There may be some operations being done external to that process that I've not been made aware of but I'm still curious to learn more about indexing behavior so I know why something like this happens in the first place. ss ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DB Tuning Notes for comment...
Tom Lane wrote: Scott Shattuck [EMAIL PROTECTED] writes: Willing to learn here but skipping a vacuum full has caused some issues for us. Here's some data from a recent 3 day test run that was done with regular vacuums but not vacuum fulls. When running with vacuum full the indexes remain in line: nsuite-10=# select relname, relpages, reltuples from pg_class where relname not like 'pg_%' order by reltuples desc; -[ RECORD 1 ]-- relname | directory_fullpath_ix relpages | 96012 reltuples | 1.38114e+06 -[ RECORD 2 ]-- relname | directory_pkey relpages | 16618 reltuples | 1.38114e+06 -[ RECORD 3 ]-- relname | directory relpages | 23924 reltuples | 59578 snip blink There's no way that the index and table tuple counts should get that far out of line; in the absence of any concurrent updates, they should be *equal* (or index table, if you have a partial index, which I assume these are not). I would credit the recorded index count exceeding the recorded table count by the number of tuples inserted/ updated while a (plain) VACUUM is in process on that table --- but this doesn't look like it meets that situation. There was a bug a long time ago wherein vacuum would forget to update pg_class.reltuples for indexes in some cases, but according to the CVS logs that was fixed before 7.2 release. What version are you running exactly? test=# select version(); version - PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) test=# In any case, you seem to be dealing with some kind of bug here. It might be helpful to look at the output of vacuum verbose directory if you still have it available. NOTICE: --Relation directory-- NOTICE: Index directory_pkey: Pages 15628; Tuples 4988848: Deleted 35407. CPU 0.73s/3.00u sec elapsed 40.53 sec. NOTICE: Index directory_fullpath_ix: Pages 80808; Tuples 4989317: Deleted 35407. CPU 4.84s/3.91u sec elapsed 275.66 sec. NOTICE: Removed 35407 tuples in 786 pages. CPU 0.13s/0.11u sec elapsed 1.80 sec. NOTICE: Pages 80156: Changed 18, Empty 0; Tup 4988787: Vac 35407, Keep 4977704, UnUsed 348422. Total CPU 7.85s/7.58u sec elapsed 343.84 sec. regards, tom lane Thanks for any insight you can offer here. ss ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] transactions and stored procedures
Hi, Just trying to confirm my understanding of how PG manages transactions with respect to stored procedures, in particular, stored procedures which invoke other procedures and their attendant SQL statements. Assuming the following description of a set of procedures: procA consists of calls to procB, procC, and procD. procB, procC, and procD invoke procE and procF. procs B,C,D,E, and F invoke INSERT/UPDATE/SELECT's My understanding is that since A) PG doesn't currently support nested transactions, B) procedures can't currently define transactional elements within their body, and C) there's at least an implicit transaction of single statement granularity at the outermost level via: select procA(); that all INSERT/UPDATE/SELECT invocations within all nested procedures operate within a single transactional context, that being the context in which the procA() call is made. Is that correct? If so, what is the lifetime of any locks which are acquired by the INSERT/UPDATE/SELECT statements within the transaction? Is it, as I believe, the lifetime of the procA invocation? I'm currently working with a system that makes extremely heavy use of nested pl/pgsql procedures to encode application logic and I'm concerned that certain design patterns may dramatically degrade concurrency if this transactional analysis is correct. Any insight into patterns of development that would avoid locking or concurrency issues would be helpful. Thanks in advance! ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] create or replace view
. Sure, and if we said you can't drop the function because triggers might break then it'd parallel what we're saying here -- in effect we know better than you do what you want. Or to use M$ terminology we know where you want to go today ;). Now, if I've misunderstood the problem here I just spent a lot of time on a non-issue and wasted a lot of time, for which I apologize. But I think the overall philosophy is reusable in any event. I bring it up here because I've gotten a distinct sense of disrepect in some of the replies on this thread and it disturbs me. If we have any goals for the Postgres community they should include: A. We want the programmer/DBA to have an easier time getting their job done and anything we do to that end that is compatible with existing and emerging standards is a good thing. If PG is easier to use it'll get used more. B. We want to treat people who are interested in PostgreSQL with respect at all times, keeping in mind that we communicate with them not only through this forum, but through the code we write for them. As a personal note, any time I see a response to my posts consisting of Why would you want to do that? I automatically assume the author simply left off the implied suffix of you idiot. It's not a question that I feel treats me with respect. I'm sure I'm not alone. /soapbox ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Cause of can't wait without a PROC structure
On Wed, 2002-09-25 at 09:52, Tom Lane wrote: I've identified the reason for the occasional can't wait without a PROC structure failures we've seen reported. I had been thinking that this must occur during backend startup, before MyProc is initialized ... but I was mistaken. Actually, it happens during backend shutdown, and the reason is that ProcKill (which releases the PGPROC structure and resets MyProc to NULL) is called before ShutdownBufferPoolAccess. But the latter tries to acquire the bufmgr LWLock. If it has to wait, kaboom. Great news that you've identified the problem. We continue to see this every few days and it's the only thing that takes our servers down over weeks of pounding. The ordering of these shutdown hooks is the reverse of the ordering of the startup initialization of the modules. It looks like we'll need to rejigger the startup ordering ... and it also looks like that's going to be a rather ticklish issue. (See comments in BaseInit and InitPostgres.) Any thoughts on how to do it? Sorry I can't add any insight at this level...but I can say that it would be significant to my customer(s) and my ability to recommend PG to future ex-Oracle users ;) to see a fix make it into the 7.3 final. ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] locking of referenced table during constraint construction
Hi, Under what conditions would the following statement cause the USERS table to lock out selects? alter table my_coupons add constraint FK_mc_user_id FOREIGN KEY (mc_frn_user_id) REFERENCES users(user_ID); ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] locking of referenced table during constraint
On Wed, 2002-09-04 at 15:51, Stephan Szabo wrote: On 4 Sep 2002, Scott Shattuck wrote: Under what conditions would the following statement cause the USERS table to lock out selects? alter table my_coupons add constraint FK_mc_user_id FOREIGN KEY (mc_frn_user_id) REFERENCES users(user_ID); If I'm reading code correctly, an exclusive lock on the pk table is grabbed which will block selects as well. You're effectively altering both tables (you need to add triggers to both tables) and both get locked. Ok, if I understand things correctly the USERS table gets a constraint that says don't delete/update the USER_ID in any way that would orphan a row in the MY_COUPONS table. The MY_COUPONS table gets one that says don't insert/update MC_FRN_USER_ID such that it isn't found in USERS.USER_ID. But... There are no rows in the my_coupons table so it's not possible to orphan a row there -- were it even the case that an update or delete were running...which they aren't. Even if there were rows in the referring table I don't understand why an exclusive table-level lock is being taken out to add a trigger. If I add user-level triggers to do the same task they go in without a hitch but cause other problems in 7.2 since I can't control their order of execution yet (thanks Tom for the 7.3 patch! :)). ss ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] locking of referenced table during constraint
On Wed, 2002-09-04 at 22:49, Tom Lane wrote: Scott Shattuck [EMAIL PROTECTED] writes: ... I don't understand why an exclusive table-level lock is being taken out to add a trigger. Well, that's a schema change; it makes sense to me to forbid access while we're changing a table's schema. No. In my book a schema change would alter the data a query would see -- as in drop column, or add column, etc. This is simply a don't let a delete/update get past this trigger from this point forward. That's not a bar-the-gates kind of scenario to me. More like for any DML operating after the current version stamp make sure this trigger runs. Why lock anything? One scenario I can see. A delete starting at T0 doesn't see a trigger. The alter occurs at T1 but, due to ACID, the delete doesn't see it. The delete tries to commit at T2. Unfortunately, in that scenario you can envision an issue since it would seem the delete should fail since the alter is done, but the delete's transaction shouldn't be able to be affected by things starting after it does. So, a conflict. But only for a delete or update. Selects already have transaction isolation levels...why don't they allow the selects to read through adding a constraint? I have other serious issues with locking and FK constraints as it is. They often cause us serious performance problems. Sadly, the longer I use PG and get hammered by locking issues surrounding the FK constraint implementation the less I find myself likely to suggest PG for similar customers in the future. I think this discussion may just be a miscommunication: it's not clear to me whether you're complaining about adding a trigger or just firing a trigger. The former is not a time-critical task in my book ... It becomes time critical when the table has 3 million user account entries and the lock blocks people from having their login name verified, causing what's supposed to be a 24x7 e-commerce site to essentially go offline to users for 5 minutes or more just so you can add a constraint to a new table with no rows. Sorry, but that sucks. ss ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] LWLockAcquire problems
On Tue, 2002-08-13 at 22:42, Tom Lane wrote: Scott Shattuck [EMAIL PROTECTED] writes: I'm seeing the following error about once a week or so: 2002-08-13 12:37:28 [24313] FATAL 1: LWLockAcquire: can't wait without a PROC structure Oh? I'd love to see what makes this happen. Can you give more context? I haven't been able to get any detailed correlation on what causes this over the past week and it's not happening often enough for me to turn on heavy logging to catch it a second time. The system details I can provide are: Solaris 8 running on a 4 CPU box with 4GB main memory. Postgres 7.2.1 built with optimization flags on and max backends at 512. Our postgresql.conf file changes are: shared_buffers = 121600 # 2*max_connections, min 16 max_fsm_relations = 512 # min 10, fsm is free space map max_fsm_pages = 65536 # min 1000, fsm is free space map max_locks_per_transaction = 256 # min 10 wal_buffers = 1600 # min 4 sort_mem = 4096 # min 32 vacuum_mem = 65536 # min 1024 wal_files = 32 # range 0-64 Because we're still in tuning mode we also changed: stats_command_string = true stats_row_level = true stats_block_level = true Fsync is true at the moment although we're considering turning that off based on performance and what appears to be high IO overhead. The average number of connections during normal operation is fairly low, roughly 30-50, although lock contention due to foreign key constraints can cause bottlenecks that push the connection count much higher while requests queue up waiting for locks to clear. We run Java-based application servers that do connection pooling and these seem to be operating properly although it might be possible that some interaction between PG and the appserver connection pools may be involved here. I don't have enough understanding of the *darn* little that happens before MyProc gets set to say :). Sorry I don't have more data but the activity count is high enough that logging all queries waiting for a crash to happen over a number of days can create log files that are untenable in our current environment. Again, any insight or assistance would be greatly appreciated. This is a high-volume E-commerce application and other than this bug PG has been rock solid. Eliminating this would get our uptime record where we need it for long term comfort. ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Admin nice-to-have's
A couple of admin nice-to-have's based on the last few weeks of 24x7 operation are: Allow DBA/Database Owner to log in even when max_connections has been reached so they can determine which queries are hung via pg_stat_activity etc. and perform any other needed work to restore stability. Log offending query during DEBUG logging for syntax errors, missing tables/attributes, etc. so that the offending queries can be located in the codebase. ss ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] LWLockAcquire problems
I posted this earlier but it didn't seem to go through. I apologize in advance if you've gotten this twice. Since I posted earlier this error happened 2 additional times. It's now a critical issue for our site: I'm seeing the following error about once a week or so: 2002-08-13 12:37:28 [24313] FATAL 1: LWLockAcquire: can't wait without a PROC structure It's usually preceded by these: 2002-08-13 12:37:28 [24313] FATAL 1: Database template0 is not currently accepting connections And immediately followed by this: 2002-08-13 12:37:28 [12532] DEBUG: server process (pid 24313) exited with exit code 1 2002-08-13 12:37:28 [12532] DEBUG: terminating any other active server processes All active database processes then immediately do the following: 2002-08-13 12:37:28 [24311] NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. Ouch! Any idea what's going on here? Is the LWLockAcquire related to something like the size of the lock table or something? Any help on eliminating this error would be appreciated. Thanks! ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] strange performance anomalies
Hi, We recently put up a new 7.2.1 installation on Solaris 8 that serves a 24x7 e-commerce site. The system seems to run pretty well most of the time but we see a consistent form of performance anomaly. Watching pg_stat_activity the system spends most of it's time running smoothly with queries clearing through sub-second. We have a production job we run which immediately sent the site into a tailspin though. Starting that job caused hundreds of select statements to queue up in the pg_stat_activity view. This seems odd since MVCC would lead us to believe that shouldn't happen. Readers shouldn't block wholesale like that unless we're using DDL on the table or doing a vacuum per the online docs at http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/locking-tables.html Nevertheless, turning off foreign key constraint checking via: update pg_class set reltriggers = 0 where relname = tablename cleared up the problem, load fell off to normal in seconds. So how is it that fk contraints apparently cause what look like table-level locks? Or, at the very least, cause a heck of a lot of select statements to go into a holding pattern for some reason? (On a side note, it seems the current locking behavior might also violate the I in ACID by causing an independent delete transaction to actually see or be affected by the content of the transaction using FK constraint locking before it's clear that the transaction will commit). At any rate, being somewhat new to tuning at this load level for PG I'm not sure if I'm supposed to be tinkering with max_lock_per_transaction here. Could this be evidence of a lock starvation issue or something? Guessing here and any input would be appreciated. Thanks in advance! ss ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Roadmap for a Win32 port
How about a SOAP interface and a web-based front end that provides the cross platform support? My company's TIBET framework would provide a solid foundation for this kind of admin suite. In fact, we're already in the planning stages on doing just that. ss Scott Shattuck Technical Pursuit Inc. - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: Bruce Momjian [EMAIL PROTECTED] Cc: PostgreSQL-development [EMAIL PROTECTED] Sent: Friday, June 07, 2002 11:42 AM Subject: Re: [HACKERS] Roadmap for a Win32 port Bruce Momjian writes: GUI --- pgAdmin2 http://pgadmin.postgresql.org/pgadmin2.php?ContentID=1 pgaccesshttp://pgaccess.org/ Java admin (to be written) Dev-C++ admin (to be written) http://sourceforge.net/projects/dev-cpp/ Surely Unix folks would like a GUI as well? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] How can we help?
Hi all! Sorry for the length of this but I'm trying to get an idea of where my company can contribute to the best effect so I have a number of questions. To begin with my team and I have some energy/time/$ over the coming months to put directly into PostgreSQL-related development work. Technical Pursuit, has made a commitment to PostgreSQL for both our internal projects and our customer projects. We've been evangelizing PostgreSQL for a while now (I did a talk on it at the Database Discovery cruise last June in Alaska -- a lone voice in literally a sea of Oracle folks) and have started doing Oracle-to-PostgreSQL conversions for customers wishing to transition away from Oracle. We're also getting ready to ship a beta release of our TIBET product that uses PG as the backend source code repository among other things. Areas we have customer/business needs in include replication, backup/recovery, monitoring/control, XML support, HTTP/HTTPS protocol support for postmaster, pl/pgperl, possible pl/jython, and possible compile-time inclusion/configuration of time-travel (--with-time-travel ?). On the process side, is there an IRC or other chat-based system in place for the PG team to coordinate their efforts? If not, would an IRC system hosted by TPI be something folks would be interested in using? We'd be willing to start hosting a set of IRC channels if that would assist the team and the community in support issues etc. For XML support I've contacted John Gray who did the current XML contrib but has since ceased development and he's granted me permission to pick up where he left off to improve XML support as it relates to his contrib module. Is there any move underway to integrate XML with PG at any other level? If we were to contribute to replication/backup/recovery solutions who would we coordinate that effort with? If that's something the core team is on top of, can we get an idea of what is expected by August so we can advise our customers and plan accordingly? What is the planned status of Java support in the engine? Is there anyone working on JVM integration at this stage and if not, how could we best integrate with the team to take on this task? We're looking seriously at the idea of a pl/jython that would leverage the Jython language to provide scriptable Java procedures in the engine. Any feedback on that idea? For several scenarios we see value in having the postmaster respond to http(s) protocols and relay to internal functions for processing of web traffic. We've got a simple perl bridge that performs this task now and the performance is more than adequate but we're sure it would be even better if we could avoid having the separate perl component. Is there any interest in this elsewhere? Any feedback on how/where we should start other than hacking postmaster? ;) What is the current thinking on re-introducing time-travel support, perhaps as a compile-time feature ala --with-time-travel? This is a feature our customers would get significant financial benefit from. I've seen recent notes that this might be possible to do. We're *extremely* interested in this direction given the potential for differentiation from other products in the marketplace. It would strengthen PG significantly in our mind. Finally, we're starting to do a lot of work in pl/pgperl(u) and are wondering whether that's an area we can again contribute in. If so, how do we get involved? PG/Financials anyone? Again, sorry for the length of this and the raft of questions. I hope you understand we're in a somewhat interesting position with some time and $ to focus on PG, particularly as it relates to replication/recovery issues. Any guidance on how we can put that to work for the community would be appreciated. Thanks. ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How can we help?
First of all thanks for the feedback! There's #postgresql on efnet and irc.openprojects.net, but it's mostly used for user support. The offer for development coordination channel(s) stands if other folks are interested. ...snip... I can't speak to the backup/recovery efforts -- since there seems to be less activity in this area, perhaps this would be an appropriate place for Technical Pursuit to focus on? We'd be happy to if others agree. I'll post a separate message trying to summarize what I understand of the current backup/recovery items on the TODO list and looking for input. What is the planned status of Java support in the engine? Is there anyone working on JVM integration at this stage and if not, how could we best integrate with the team to take on this task? http://pljava.sourceforge.net/ is the only project that I'm aware of. I hadn't found this. Thanks. For several scenarios we see value in having the postmaster respond to http(s) protocols and relay to internal functions for processing of web traffic. Perhaps you could elaborate on this? It sounds like bloatware, but maybe I'm just cynical. OK. But remember you asked for it :). Given a market which seems bent on HTTP(S)-based XML/SOAP access to data and XSchema/XML output it seems natural to consider putting these into PG. The buzzword seems to be XML Databases. I'm not a big subscriber to that concept so don't get me wrong, I'm not looking to go that route, but I do see value in unifying the protocols for data access so PG can be a fully qualified player in the game. In one sense, we're trying to use PG as we think it was designed, not as a database server so much, but as an application server. Smart databases don't need app servers -- they are app servers. The problem is, web apps need HTTP(S) support. So, we're thinking we'd create new listeners for PG that add alternative protocol support. We've done a simple HTTP listener in Perl that hands off to the postmaster process and while I hesitate to publish any raw data at this point let's just say that even with the extra overhead of the Perl the results are enlightening.Web servers aren't the only things built to scale under load and our tests show that the team working on PG has done a great job. Our business case is simple. We want to avoid having to ship a combination of Apache, Tomcat, and PostgreSQL to our customers. While a lot of products need a database and web access do they really need to ship with a manual that tells the customer to configure Apache, Tomcat, and PG and make sure they all start up and stay up? We'd like to reduce that complexity. The complexity of today's web designs is what I'd define as bloatware. But, rather than referring to a single product, my definition applies to the combination of technologies currently required just so we can put a web face on our database. Web server, servlet container, J2EE server, database. That's bloat. Why use PG at all if we're not going to use it for what it was designed from day one to do? Namely, support writing applications directly in the database itself. Given current web architecture I'm sure some might say I'm crazy to consider such a thing and I have two words for them -- Oracle Financials. Oracle has made billions off the design I'm talking about. Oracle Financials isn't written in Java and it doesn't need 3 servers and 500 jar files from the Jakarta project (although with 9i who knows ;)). It's written in plsql. If stored procs can do all that in a database that wasn't even designed to be extended for application support they can certainly parse a GET/POST request. I just need the postmaster to listen for HTTP so it can figure out which proc to call on my way to replacing 5 years of web bloatware ;). Finally, we're starting to do a lot of work in pl/pgperl(u) and are wondering whether that's an area we can again contribute in. If you mean plperl, then I don't see why not ; if you're talking about a new procedural language based upon some unholy union of pl/pgsql and perl, I'd be skeptical :-) Allowing perl programmers to think in perl full time and use interfaces they're familiar with is more our goal. Something like a DBI module that would function as if you were external to PG even though you aren't. Write a stored proc as if it were going to run outside of the database. Install it inside when it makes sense. No code change. We should be able to say if you know perl/DBI you know how to write stored procs for PG. Same for pl/python. Same for Jython. I don't know if we can get there from here but it's a goal we're going to work hard for. ss Scott Shattuck Technical Pursuit Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
