Re: [GENERAL] the future of pljava development
Hi, This sounds great. Sorry for not being responsive on the pgsql-general. I'll read up on this thread during the weekend. Short term, this is what I think needs to be done: 1. Create a PL/Java 1.4 from current CVS. It compiles and runs with PostgreSQL 8.2. 2. Do whatever it takes to make PL/Java run with 8.3. 3. When 8.3 is released, verify that everything is really ok, then a 1.5 release should come out. After that, there are improvements in the Java domain. We should support Java 1.5 for instance. 1.4 is soon starting its End Of Life cycle. Would you like to become committers to the PL/Java project? Regards, Thomas Hallgren Marek Lewczuk wrote: Guy Rouillier pisze: Nothing productive will come of a language debate; let's just say there are things you can do with a complete programming language at your disposal that you can't do with PL/pgsql. I use Java in the database, Exactly - sometimes we need to write something more complicated than simple trigger. and the current PL/Java works very well. I know C and Java well, and would be willing to contribute to keeping PL/Java going. I hadn't heard Great to hear that, please contact with Thomas, lets try to make a roadmap what need to be done. Best wishes, Marek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Invoking java in a trigger
David Potts wrote: Is it possible to invoke a computer language other than postgres sql as the result of a trigger firing, eg something like Java. Dave. http://wiki.tada.se/wiki/display/pljava/Home Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] performace review
Joshua D. Drake wrote: Tomi NA wrote: I was just reading http://www.opencrx.org/faq.htm where RDBMS engines are one of the questions and see pgsql bashed sentence after sentence. Can anyone offer any insight as to weather it's fact or FUD? It is 100% FUD. What would be the incentive for OpenCRX spreading FUD about PostgreSQL? Does anyone know? Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] UUID's as primary keys
I'm building an app where I have several entities that are identified using a UUID (i.e. a 128 bit quantity). My current implementation uses a composite primary key consisting of two int8 values. It's a bit cumbersome and I would much rather have a distinct type. An earlier implementation using Oracle mapped the UUID to a RAW(16) but PostgreSQL doesn't have that and the BYTEA adds extra overhead. What would be the best (as in most efficient) mapping for a 128 bit primary key? Regards, Thomas Hallgren ---(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
Re: [GENERAL] UUID's as primary keys
Martijn van Oosterhout wrote: On Wed, Jun 28, 2006 at 09:01:49AM +0200, Thomas Hallgren wrote: I'm building an app where I have several entities that are identified using a UUID (i.e. a 128 bit quantity). My current implementation uses a composite primary key consisting of two int8 values. It's a bit cumbersome and I would much rather have a distinct type. An earlier implementation using Oracle mapped the UUID to a RAW(16) but PostgreSQL doesn't have that and the BYTEA adds extra overhead. What would be the best (as in most efficient) mapping for a 128 bit primary key? Sounds like something for a custom type. There's one here[1] though I have no idea how good it is. [1] http://gborg.postgresql.org/project/pguuid/projdisplay.php Have a nice day, Thanks. That would of course work but at the same time it increases the complexity of my app. Yet another component to install and keep track of. It's also a bit of an overkill since the only thing I need is an opaque bit storage. Why is it that PostgreSQL lack a fixed length binary type similar to the RAW type in Oracle? ISTM that could be very useful and not very hard to implement. Regards, Thomas Hallgren ---(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
Re: [GENERAL] UUID's as primary keys
Martijn van Oosterhout wrote: On Wed, Jun 28, 2006 at 10:22:45AM +0200, Thomas Hallgren wrote: Thanks. That would of course work but at the same time it increases the complexity of my app. Yet another component to install and keep track of. It's also a bit of an overkill since the only thing I need is an opaque bit storage. Why is it that PostgreSQL lack a fixed length binary type similar to the RAW type in Oracle? ISTM that could be very useful and not very hard to implement. AIUI, it can't be done because of a basic rule of the type system: the typmod can't be necessary to interpret the binary representation of a value. For something like RAW(16) the type would be the oid for raw and the typmod would be 16. However, when reading the value from a disk page, you're not given the typmod, so you have no way of determining the length. OK. I thought you always had a type descriptor handy when reading the binary representation. I've noticed that the typmod is expected in some receive functions (bpcharrecv and numeric_recv for instance). Are you saying that there are times when you don't use that? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UUID's as primary keys
Martijn van Oosterhout wrote: On Wed, Jun 28, 2006 at 12:03:40PM +0200, Thomas Hallgren wrote: OK. I thought you always had a type descriptor handy when reading the binary representation. I've noticed that the typmod is expected in some receive functions (bpcharrecv and numeric_recv for instance). Are you saying that there are times when you don't use that? The input functions get it, the output functions (bpcharout, bpcharsend, etc) don't. Which makes it kind of hard to print a raw value if you don't know how long it's going to be. They used to, but that was removed some time back. It's a security issue IIRC, since any user could call raw_out(field, 2048) and get whatever was in the 2K of data after that field. A user that is trusted with installing a C-function in the backend is free to scan the process memory anyway so in what way did that increase the security? IMHO, the only relevant security in that context is to have trusted people install trusted modules. I'm surprised that something like that made you remove significant functionality. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] create view problem
Chris Velevitch wrote: Why doesn't explain work with create views? There's nothing in the documentation saying it shouldn't. No? The documentation for explain clearly states that the statement that follow the EXPLAIN keyword is Any SELECT, INSERT, UPDATE, DELETE, EXECUTE, or DECLARE statement. I don't see CREATE (or any other ddl statement) in that list. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Java Triggers
John, Thanks for the referral. The link is a bit outdated since all web content has moved to a wiki. Please use: http://wiki.tada.se/display/pljava/Home instead. Kind regards, Thomas Hallgren John Sidney-Woollett wrote: I would think the answer is yes. pljava allows you to create java functions, and triggers invoke functions eg CREATE TRIGGER sometrigger BEFORE UPDATE ON schema.table FOR EACH ROW EXECUTE PROCEDURE yourfunction(); Here's a link for the pljava language. http://gborg.postgresql.org/project/pljava/genpage.php?userguide John Jimbo1 wrote: Hi there, I'm a very competent Oracle developer, but have never used Postgres. There's currently a project taking place where I'm working that is developing on an Oracle database, but could potentially be migrated to an open source database in the future; possibly Postgres. There are two questions currently being asked about Postgres: 1. Can it support triggers? 2. Can it support Java Triggers (Java in the database trigger body)? I know the answer to the first question is a definite Yes. However, I don't know the answer to the second. Please can anybody help? Thanks in advance. James ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] background triggers?
Sim Zacks wrote: If the database supported background triggers, it might be implemented by a daemon or by the Listen/Notify framework, but I really couldn't care less. It is part of the database. Assume the backend would handle this, what would the transaction semantics look like? You can't wait for the background work to complete before you commit your transaction, so what should happen when the background trigger fails? Or what if a number of such triggers where fired and then rolled back? The only thing the database *can* do, is what it does today. It provides the hooks needed for specialized code that can react on the *outcome* of transactions (and then perform its task asynchronously using transactions that are autonomous to the first one). What you're trying to do doesn't belong in triggers and the concept of background triggers doesn't make any sense. Triggers execute (and complete) within a transaction and the work they perform should be rolled back if the transaction is rolled back. Kind regards, Thomas Hallgren ---(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
Re: [GENERAL] background triggers?
Rafal Pietrak wrote: I'd like to propose a 'syntax/semantics' of such trigger: Triggers normally execute inside of a transaction. A COMMIT within a trigger could mean: do a fork: fork-1) return to the main and schedule COMMIT there, fork-2) continue in bacground. And what if fork-1) returns to the main, attempts the COMMIT but instead and rolls back due to a violated constraint? Where does that leave fork-2? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] background triggers?
Rafal Pietrak wrote: On Thu, 2006-05-25 at 10:33 +0200, Thomas Hallgren wrote: Rafal Pietrak wrote: I'd like to propose a 'syntax/semantics' of such trigger: Triggers normally execute inside of a transaction. A COMMIT within a trigger could mean: do a fork: fork-1) return to the main and schedule COMMIT there, fork-2) continue in bacground. And what if fork-1) returns to the main, attempts the COMMIT but instead and rolls back due to a violated constraint? Where does that leave fork-2? Regards, Thomas Hallgren No problem at all (at least in particular case of an application I have in mind :). The precedure that remains within fork-2 just does a time consuming housekeeping. Like a cleanup - always succeeds, even if sometimes is not really necesary (like in case of main rolling-back). A somewhat limited use-case to form generic database functionality on, wouldn't you say? And that's exacly why I thing that it should be 'released to run' by RDBMS *after* the main COMMITS (or ROLLES-BACK). It should be run on COMMITED (visible to the world) changes, not on session trancients. Right, so it's not a trigger. It's another session (another transaction) that reacts on a notification that is sent only if the first transaction succeeds. This is exactly what notify/listen is for. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GUI Interface
Joshua D. Drake wrote: BTW who was the EXPERT that slammed java performance? No clue, but java sucks on the desktop :) No it doesn't (unless you mean when tilting your coffee mug. Hmm, no, then you would need something that actually sucks java). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GUI Interface
Dan Armbrust wrote: You live in an interesting world... meanwhile, I'm here in the real world, using Eclipse - the best IDE I've ever used to develop java applications. Oh, wait, Eclipse is written in Java? I didn't think it was possible to write good apps in java? Certainly better than visual studio (and yes, I have to use both - eclipse is a far better IDE in my opinion). Oh, and I can run eclipse on my linux desktop as well as my windows desktop? Thats just cool. Oh, and my next machine? Definitely a Mac. And it woks there too - just like all of my SWT apps do. I will admit, it is rather easy to write slow java swing applications There are a lot of poor ones out there. Its a shame that Sun botched swing so badly, and have never repaired it properly. Its not impossible to write fast, responsive apps in swing, it just takes skilled developers. And its a pain. However, now with the emergence of SWT and modern JVM's - there is no reason for your java GUI to be any slower than anything else. The only excuse for a slow java app these days is the quality of the code that it is built with. And you can write a bad, slow app in any language. Cool. At least one that actually knows what he's talking about and have real life experience. I'm also using Eclipse and a slew of other Java apps. No complaints whatsoever on performance. Not too happy about all C/C++ apps that crash on illegal memory access though... +1 (or 10) for Java on the desktop. I will not spend time on a war that cannot be fought in this forum. Just wanted to air my opinion this once :-) Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Five reasons why you should never use PostgreSQL -- ever
TJ O'Donnell wrote: Slashdot had this today. http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html Interesting. JDBC is now a 'language'. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Clustered PostgreSQL
If you're using JDBC, the C-JDBC (http://c-jdbc.objectweb.org/) might be a good option. Regards, Thomas Hallgren Devrim GUNDUZ wrote: Hi, On Tue, 14 Mar 2006, Jojo Paderes wrote: Is it possible to cluster PostgreSQL? If yes where can I find the resource information on how to implement it? You can use pgcluster: http://pgcluster.projects.postgresql.org/ (I haven't tried it yet) Of course, you can install any clustering software (like Red Hat Cluster Suite) if you want an active-passive cluster. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] New project launched : PostgreSQL GUI
Tino Wildenhain wrote: Jonah H. Harris schrieb: I had to deal with an installer written in python and several in Java... IMHO, Java would be a better language for this and you could build off some nice OSS installers that already exist (such as IzPack). Just my 2 cents :) Yes! Use Java for ultimate suckiness of the installer ;) I love to install all X11, Java and stuff on a server to be able to install a package with about 1/10 the size ;) How about postponing choice of implementation language until it's clear what it is that should be implemented ;-) Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] New project launched : PostgreSQL GUI
Devrim GUNDUZ wrote: Hi, On Mon, 2006-01-30 at 21:27 -0500, Jonah H. Harris wrote: I had to deal with an installer written in python and several in Java... IMHO, Java would be a better language for this and you could build off some nice OSS installers that already exist (such as IzPack). Just my 2 cents :) Bundling Java is a pain, so we'd better stay away from that. There's always gcj. It's pretty mature by now. I'm not sure about availability compared to Python though, but I find it hard to believe it would be more painful. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
For what it's worth, the next release of PL/Java has support for both RECORD parameters and SETOF RECORD return types. The adventurous can try out the current CVS HEAD. Regards, Thomas Hallgren Tom Lane wrote: Yl Zhou [EMAIL PROTECTED] writes: I want to implement a UDF that can accept a parameter which is a tuple of any table, and returns the number of NULL attributes in this tuple. Different tables may have different schemas. How can I implement this function? Thanks. You could do that in C, but none of the available PLs support it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
Re: [GENERAL] FW: Surrogate keys (Was: enums)
Josh Berkus wrote: Why? I don't find this statement to be self-evident. Why would we have ON UPDATE CASCADE if keys didn't change sometimes? Many times you will have references to a specific row from somewhere outside of your database. Perhaps you have a federation of web services that collaborate or other arbitrary URL's that contain the key. It might be harder to create remote row sets, middle tier caches, and other similar constructs if you cannot trust that the primary key is immutable. Such mechanisms often trust that the primary key can be used to refetch the data and that it has been deleted if it's no longer found. Anyway, my opinion on this, in detail, will be on the ITToolBox blog. You can argue with me there. That will be interesting reading. I didn't find it (yet). Can you please post an URL here? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Graphical modelling tool
Thank you for all good suggestions. Clay will probably be my choice for this project since it indeed is an Eclipse plugin that is developed. Thanks again, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Graphical modelling tool
Hi, I'm about to start a new project where the first task is to design a database. I'm looking for some tool that will allow me to model the tables and relationships graphically, UML or similar, and then let me generate the SQL with PostgreSQL flavor. What's the best tools out there? Are there any open source alternatives? Kind regards, Thomas Hallgren ---(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
Re: [GENERAL] Deep integration of PostgreSQL with Apache
Robin Boerdijk wrote: Hi, Apologies if this has been discussed before, but I was wondering if there have been any efforts in the past to provide a deep integration of PostgreSQL with Apache. What I mean by deep integration is that the PostgreSQL server logic runs inside the Apache server processes, rather than separate processes. In particular, the postmaster server logic would run inside the Apache master process and the postgres server logic would run inside Apache child processes. The main advantage of this approach would be that it avoids the Apache/PostgreSQL context switch when executing SQL requests from the web server. It looks like the Apache server and PostgreSQL server architectures are quite similar to make this feasible. Any thoughts? The PostgreSQL backend is inherently single-threaded and a new process is forked each time you establish a new connection (session) so the integration you ask for is not in anyway possible unless you are content with one single database connection. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Deep integration of PostgreSQL with Apache
Robin Boerdijk wrote: Why would this web/database server be limited to using only one connection? No it wouldn't. I misunderstood your question. It's one process/one connection. If you don't use Apache as a multi-threaded server, then your question makes more sense. Still, it's a lot of work to make it happen and I'm not sure you'd gain anything. My advice would be to consider a multi-threaded server that uses a connection pool and perhaps include some shared caching of data that is more static in nature in the middle tier. That could really save some context switches. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Function call identification
Vishal Kashyap @ [SaiHertz] wrote: Dear all, I was wondering if their exist any function in Plpgsql functions that would allow me to identify which functions has called what function. To make it simple suppose I have a function func_1 ,func_2 and func_3 Now func_1 calls function func_2 for some reason and it gives the results as required now the func_3 also nearly requires same kinda results but with some changes . So is their any function that can help me determine which function called func_2 Something like IF func_call = func_2 THEN do some blah; ELSIF func_cal = func_3 THEN do some more blah; END IF: Why not have the callers call the callee with a boolean parameter? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Returning a RECORD, not SETOF RECORD
Michael, Thanks for your reply on this. CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my RECORD definition? Is it possible at all? Ideally I'd like to write something like this: SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc; but that yields a syntax error. What version of PostgreSQL are you using The latest and greatest from CVS. , and could the function return a specific composite type instead of RECORD? This is not for a specific use-case. I want to provide rich functionality in PL/Java but I don't understand how the actual RECORD type is determined in cases where you don't use the function in a FROM clause where it only makes sense (to me at least) to use a function returning SETOF RECORD. Wouldn't it make sense to be able to define a record in the projection part of a query, similar to what I was attempting with my SELECT? Has this been discussed or is it just considered as not very useful? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Functions returning RECORD
I have a question about syntax and feasibility. I do the following: CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my RECORD definition? Is it possible at all? Ideally I'd like to write something like this: SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc; but that yields a syntax error. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Christopher J. Bottaro wrote: Alvaro Herrera wrote: On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: John DeSoi wrote: On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a timestamp with time zone, whereas casting to timestamp unadorned returns a timestamp without time zone. Try cast(timeofday() as timestamptz) or cast(timeofday() as timestamp with time zone) It may not matter a lot but you may as well be aware of the difference ... Ahh, thanks for the tip. I guess I'll just stick with timeofday()::timestamp...its more concise anyways... Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Scott Marlowe wrote: I think you missed the first part of the conversation... I sure did. Sorry... Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Returning a RECORD, not SETOF RECORD
I just discovered that my previous post concerning this had the same subject line as a discussion that took place in January. I'm not asking the same question though, so here I go again with my question about syntax and feasibility. I do the following: CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my RECORD definition? Is it possible at all? Ideally I'd like to write something like this: SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc; but that yields a syntax error. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL 8.0.2 Now Available
Marc G. Fournier wrote: And, for Bittorrent Users, David has put up the tar packages at: http://bt.postgresql.org The windows installer torrent doesn't work. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL and .NET
Marc G. Fournier wrote: From the Mono web site: Mono can run existing programs targeting the .NET or Java frameworks. does this mean that something written for Java (or .Net) could be compiled without mods? Mono runs Java using IKVM.NET http://www.ikvm.net/. It's not yet fully compliant with Java 1.4. As with GNU GCJ, it uses GNU Classpath and is a couple of years behind mainstream Java, both from a functionality and performance standpoint. Still, IKVM is a very interesting project. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL and .NET
Pavel Stehule wrote: Hello maybe http://gborg.postgresql.org/project/plmono/projdisplay.php but I am not know more Judging from the CVS, that project hasn't had any activity at all the last 14 months or so. Is it still active? Has it been moved? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plPHP in core?
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I'm not convinced that PLs are more tied to the core than say OpenFTS, and if we can't maintain that kind of thing externally, then this whole extension thing sounds like a failure to me. It's *possible* to do it. Whether it's a net savings of effort is questionable. For instance, I've had to hack plperl and plpgsql over the past couple days to support OUT parameters, and the only reason I didn't have to hack the other two standard PLs is that they are a few features shy of a load already. I'm pretty sure pl/r and pl/java will need changes to support this feature too. If they were in core CVS then I'd consider it part of my responsibility to fix 'em ... but they aren't, so it isn't my problem, so it falls on Joe and Thomas to get up to speed on what I've been doing and do likewise. Is that really a win? So far we've been able to keep up with PostgreSQL changes because a) the interfaces are after all pretty well defined, and b) there is always a long enough delay between changes of the interfaces and their official release to make it possible for us to catch up. Cumbersome sure, but still not my primary concern. There's a couple of other reasons why it's bad to be an outsider. a) If skilled core developers from time to time stumbled on compilation errors in PL/Java due to changes made in the backend, then I believe that this would result in some level of code review and perhaps lots of good criticism and ideas of improvement. b) I've been forced to do pull some tricks in PL/Java to work around things that I consider lacking in the interfaces. Having PL/Java in core would make it possible to work together more tightly in order to find good solutions/API's that can benefit all PL's. c) PL/Java would become (optional?) part of the build and the regression tests. It would be great to get early warnings when things change that break PL/Java. d) Bringing PL/Java into core will force a consistent documentation and, I imagine, a chapter of it's own in the main docs. I'm happy to write most of it but English is not my native language. Whatever I put into print will always benefit from a review. e) The article http://www.powerpostgresql.com/5_types describes another serious issue pretty well. While it's easy for an organization to become dependent on the Community based PostgreSQL, it's much more difficult to make such a decision with the Solo based PL/Java. In essence, I'm all for bringing PL/Java into core. While doing so I think it's imperative to maintain good API's between modules and backend. Bringing the PL's into core must be done while retaining good separation of concern. The extension mechanism is a good thing. It should be improved regardless where PL's end up. The point here is really that we keep finding reasons to, if not flat-out change the interface to PLs, at least expand their responsibilities. Not to push it too hard, but we still have only one PL with a validator procedure, which IIRC was your own addition to that API. How come they don't all have validators? For PL/Java, the answer is that we just haven't had the time to implement it. It should be done of course. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] question about 8.1 and stored procedures
Oleg Bartunov wrote: On Sun, 20 Mar 2005, Joshua D. Drake wrote: My understanding is that 8.1 will have a much more mature implementation of stored procedures versus UDFs (Which we have had forever). What's the difference between UDF and stored procedure ? Here are a couple of GGIYF references: http://builder.com.com/5100-6388-1045463.html http://blogs.pingpoet.com/vbguru/archive/2004/04/29/535.aspx They are similar but they offer different functionality. At least in the sense of the other databases. Hmm, the only real difference I see - is that SP are precompiled. I think we should clearly outline what is SP and what is UDF and do we are working on SP or just improving and extending our functions. I always thought that the big difference was that a SP can start and end top level transactions whereas UDFs must execute within the scope of a transaction started by the caller. The above article doesn't mention this at all. Regards, Thomas Hallgren ---(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: [GENERAL] PostgreSQL still for Linux only?
Richard Huxton wrote: It can also be bad - the more time spent supporting Windows, the less time is spent working on PostgreSQL itself. Unless the Windows support attracts more resources. Personally I'd be surprised if that's not the case. That's clearly a decision only you can make. Getting replication working on Windows will happen quicker the more people help. If all you want is an off-machine backup, perhaps look at PITR (see manuals for details). If you're using a Java based client perhaps something like C-JDBC http://c-jdbc.objectweb.org would help. It's known to run well with PostgreSQL. Regards, Thomas Hallgren ---(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: [GENERAL] Loading of native libraries in PLJAVA
Nageshwar, This is not the right forum for PL/Java issues. You should use mailing list at [EMAIL PROTECTED] We are facing 2 problems in loading files from Pljava. 1. Loading Native C library from Pljava trigger. From Pljava trigger, we were trying to load native library, Using System.loadLibrary(library_name), which is specified in the LD_LIBRARY_PATH. The likely cause of this is that this library in turn have dependencies to something not specified in the LD_LIBRARY_PATH or that you have some mismatch in function naming. That in turn should be apparent from the exception message (can't tell since you didn't convey it here). Also specified the library file under /var/lib/pljava where all the pljava specifc *.so files are located, which has an entry in the postgresql.conf - dynamic_library_path for /var/lib/pljava, Both theses cases failed to load native library. we are getting this error in both cases- UnSatisfiedLinkedError The dynamic_library_path is only used by the PostgreSQL backend when it loads a module. It has no effect whatsoever on the System.loadLibrary method. 2. Loading Properties file from trigger. (using Properties.load(properties_name.properties)) We are unable to load properties file from Properties.load(properties_name.properties). we have included properties file in jar file and installed the jar and set the jar file in the classpath using sqlj.set_classpath. To my knowledge there's Properties.load method that takes a String argument. You have to use the Class.getResourceAsString() in order to obtain an InputStream for a resource stored in a jar file. The class in question should be in the same package as the resource (i.e. your properties file). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Help with access check
Hi, From code inside a C-function, given the AclId of a user and the Oid of a Schema, what's the most efficient way to find out if the user has been granted CREATE on the schema? Thanks, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Moving from Sybase to PostgreSQL - Stored Procedures
Andre Schnoor wrote: Hi, I'm moving from Sybase to pgsql but have problems with stored procedures. The typical procedure uses a) named parameters, b) local variable declarations and assignments c) transactions d) cursors, views, etc. I can't seem to find these things in the Postgres function syntax. Procedures can be as long as 20-250 lines, performing heavy data manipulation tasks, running from a few seconds up to several hours. Database size is approx. 20GB. Functions in pgsql are very limited compared to Sybase procedures, so I'll have to find a workaround somehow. Perhaps somebody can point me to examples or hints regarding this issue. Thanks in advance! pgsql is not the only language that you can use when writing PosgreSQL functions. Other languages like Perl and Java will provide a richer functionality. PostgreSQL doesn't have stored procedures and its functions will always run within the transaction that was in effect when the call was issued so your Sybase stored procedures involving several hours of execution must probably be rewritten so that you move the transaction demarcation to a client that issues several more short-lived calls. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] OID of current function
Jim C. Nasby wrote:
Is there an easy way to get the OID of the currently running function?
(IE: the function you're in when you execute the code to see what
function you're in, if that makes any sense).
In what language? In C you can use:
Datum your_function(PG_FUNCTION_ARGS)
{
Oid funcOid = fcinfo-flinfo-fn_oid;
...
}
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 1
David Fetter wrote: It's also on http://bt.postgresql.org/ :) I don't know why, but I always seem to have problems with the torrents at bt.postgresql.org. I click them and my download manager says starting but then nothing happens. I can however download the torrents using wget url of torrent and start them locally on my machine without problems. This might of course be something in my own setup but I don't have this problem with any other torrents on the net. Anyone else experiencing similar problems? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Regexp matching: bug or operator error?
Ken Tanzer wrote:
Using Postgres V. 7.4.1, the following query:
SELECT substring('X12345X' FROM '.*?([0-9]{1,5}).*?');
Returns '1'. I would expect it to return '12345'. Is this a bug, or am
I missing something? Thanks.
The regexp {1,5} is satisfied with the minimum of 1 digit. It looks
ahead and finds your '.*'. That in turn consumes all but the last character.
Perhaps what you want is '[^0-9]+([0-9]{1,5})[^0-9]+'
Translates to at least one non digit followed by 1-5 digits and then at
least 1 non digit.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] null value of type java.sql.Time
phil campaigne wrote: Occasionally I want to store a null value for my java.sql.Time-- Time column in Postgresql. update event set game_clock=null where event_id=1; I can retreive the record with the null value (type Time) if I select on the primary key, select game_clock from event where event_id = 1; but when I try to select on the null column value, I get zero records. select * from event where game_clock=null; Try select * from event where game_clock is null; A null value cannot be used in a comparison since it's undefined. You have to explicitly query for something that has no value, hence the different syntax. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Reasoning behind process instead of thread based
Marco, I mean an entirely event driven server. The trickiest part is to handle N-way. On 1-way, it's quite a clear and well-defined model. You need to clarify this a bit. You say that the scheduler is in user-space, yet there's only one thread per process and one process per CPU. You state that instead of threads, you want it to be completely event driven. In essence that would mean serving one event per CPU from start to end at any given time. What is an event in this case? Where did it come from? How will this system serve concurrent users? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasoning behind process instead of thread based
Martijn, I honestly don't think you could really do a much better job of scheduling than the kernel. The kernel has a much better idea of what processes are waiting on, and more importantly, what other work is happening on the same machine that also needs CPU time. I agree 100% with Martijn. Below is a reply that I sent to Marco some days ago, although for some reason it was never received by the mailing list. Marco, You ask what an event is? An event can be: - input from a connection (usually a new query); - notification that I/O needed by a pending query has completed; - if we don't want a single query starve the server, an alarm of kind (I think this is a corner case, but still possible;) - something else I haven't thought about. Sounds very much like a description of the preemption points that a user-space thread scheduler would use. At any given moment, there are many pending queries. Most of them will be waiting for I/O to complete. That's how the server handles concurrent users. In order to determine from where an event origins, say an I/O complete event, you need to associate some structure with the I/O operation. That structure defines the logical flow of all events for one particular session or query, and as such it's not far from a lightweigth thread. The only difference is that your thread resumes execution in a logical sense (from the event loop) rather than a physical program counter position. The resource consumption/performance would stay more or less the same. (*) They're oriented to general purpose processes. Think of how CPU usage affects relative priorities. In a DB context, there may be other criteria of greater significance. Roughly speaking, the larger the part of the data a single session holds locked, the sooner it should be completed. The kernel has no knowledge of this. To the kernel, big processes are those that are using a lot of CPU. And the policy is to slow them down. To a DB, a big queries are those that force the most serialization (lock a lot), and they should be completed as soon as possible. Criteria based prioritisation is very interesting but I think your model has some flaws: - Since the kernel has no idea your process servers a lot of sessions _it_ will be considered a big process. - If a process/thread will do lots of I/O waits (likely for a big query) it's unlikely that the kernel will consider it a CPU hog. - Most big queries are read-only and hence, do not lock a lot of things. - PostgreSQL uses MVCC which brings the concurrent lock problem down to a minimum, even for queries that are not read-only. - Giving big queries a lot of resources is not the desired behavior in many cases. - Your scheduler is confined to one CPU and cannot react to the system as a whole. I think it is more important that the scheduler can balance _all_ sessions among _all_ available resources on the machine. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Reasoning behind process instead of thread based
Marco, You ask what an event is? An event can be: - input from a connection (usually a new query); - notification that I/O needed by a pending query has completed; - if we don't want a single query starve the server, an alarm of kind (I think this is a corner case, but still possible;) - something else I haven't thought about. Sounds very much like a description of the preemption points that a user-space thread scheduler would use. At any given moment, there are many pending queries. Most of them will be waiting for I/O to complete. That's how the server handles concurrent users. In order to determine from where an event origins, say an I/O complete event, you need to associate some structure with the I/O operation. That structure defines the logical flow of all events for one particular session or query, and as such it's not far from a lightweigth thread. The only difference is that your thread resumes execution in a logical sense (from the event loop) rather than a physical program counter position. The resource consumption/performance would stay more or less the same. (*) They're oriented to general purpose processes. Think of how CPU usage affects relative priorities. In a DB context, there may be other criteria of greater significance. Roughly speaking, the larger the part of the data a single session holds locked, the sooner it should be completed. The kernel has no knowledge of this. To the kernel, big processes are those that are using a lot of CPU. And the policy is to slow them down. To a DB, a big queries are those that force the most serialization (lock a lot), and they should be completed as soon as possible. Criteria based prioritisation is very interesting but I think your model has some flaws: - Since the kernel has no idea your process servers a lot of sessions _it_ will be considered a big process. - If a process/thread will do lots of I/O waits (likely for a big query) it's unlikely that the kernel will consider it a CPU hog. - Most big queries are read-only and hence, do not lock a lot of things. - PostgreSQL uses MVCC which brings the concurrent lock problem down to a minimum, even for queries that are not read-only. - Giving big queries a lot of resources is not the desired behavior in many cases. - Your scheduler is confined to one CPU and cannot react to the system as a whole. I think it is more important that the scheduler can balance _all_ sessions among _all_ available resources on the machine. Regards, Thomas Hallgren ---(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: [GENERAL] Bug or stupidity
Martijn van Oosterhout wrote: Sure, that's what you could do, but it makes the query rather more complex than it needs to be. Do you consider this overly complex? Compare: DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and x.b table.b and table.c = 4) to: DELETE FROM x, table WHERE x.a = table.a and x.b table.b and table.c = 4 In the latter, what is it you are deleting? Is it x or table? I'm not at all in favor of listing several tables in the FROM clause of a DELETE statement (that includes implicitly adding them). transform_equals_null comes to mind. It's a hack to make 'x = NULL' work the way people coming from Oracle expect. It fixes it to be 'x IS NULL'. That is arguably something that could cause unexpected results. I assume you mean transform_null_equals. If so, you just made my point. It's disabled by default. Probably for the reason you mention. It has to be exactly one tuple. If there are zero tuples you get zero output. Cross-joining with an empty table produces no output. You're shipping a product where people expect to be able to add more rows to a table, but you never test that? So how is this relevant to the argument? This is not about the capabilities of an imaginary test framework. It was just an example! As I said before, I don't object to the presence of this option so that people that really knows _why_ they enable it can do so, but I strongly object to having this option enabled by default. I suggest that: 1. Have this option disabled by default. 2. Print WARNING's rather than notifications when tables are added. If you're not seeing NOTICEs now, what makes you think you'll see WARNINGs? It's not totally uncommon for a test framework to trigger on warnings and errors (for obvious reasons). My imaginary test actually did just that (as stated). Every DB interface I've used so far displays the notices where I can see them. This notice is one of the less useful, there are other more useful warnings which are much more handy to see... Right. Useful warnings! Seems you agree that this should be a warning, not a notice. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug or stupidity
Martijn, I realize that the change I'm proposing might be too complex to be added in the upcoming 8.0 release. I do find this discussion interesting though, so please bear with me while I try to tie up some loose ends. UPDATE [ ONLY ] table SET col = expression [, ...] [ FROM fromlist ] [ WHERE condition ] Perfectly reasonable addition, but not strictly SQL standard. Also, the scope is not guessed, it's totally unambiguous. Ok, bad choice of words. It's not guessed, and I agree, this is perfectly reasonable. Anyway, I think there's a confusion in the phrase from clause. There's no confusion. I fully understand the differences. That's why think that the term 'add_missing_from' is misleading. From a strict syntax point of view it implies expansion to the statement we both agreed should be disallowed. The fact that it doesn't actually add a missing from but rather expands the scope for the predicate is somewhat confusing. Hence my suggestion that the variable is renamed. But I guess it comes down to to how strictly you want to follow the SQL standard. I think it's OK to deviate from the standard and add features. My whole argument in this thread is based on the fact that PostgreSQL adds tables to the FROM clause of a SELECT which may produce incorrect results and that this magic is performed by default. My suggestion is that we rename the add_missing_from to: update_delete_autoscope and that this option has no effect on SELECT clauses. It would be more or less harmless to have it enabled by default. As pointed out above, it's not needed to update. And add_missing_from currently has no effect on delete, so your suggested option appears to be merely the inverse of what is already there. What I was trying to say is that: a) since the 'add_missing_from' affects the predicate scope for DELETE's, UPDATE's, and SELECT's, and since those statements have different ways of expressing this scope, the current choice of name is a bit confusing and b) it would be nice if the variable affected DELETE and UPDATE scopes only. Now you point out that an UPDATE can have a FROM clause, so let me revise my suggestion and instead say: 1. Let's add a variable named autoscope_for_delete that is enabled by default and only affects the scope of a DELETE predicate. We do this to maintain backward compatibility. 2. Let's change so that add_missing_from is disabled by default and doesn't affect the DELETE statement at all. 3. The autoscope_for_delete will use generate notices and add_missing_from will generate warnings. DELETE FROM first_table x WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo 4) The SQL standard (what I can find on the web anyway) doesn't allow an alias there, and neither does PostgreSQL. The SQL 2003 draft I have states: delete statement: searched ::= DELETE FROM target table [ [ AS ] correlation name ] [ WHERE search condition ] whereas SQL 3 is more elaborated: table reference ::= table name [ [ AS ] correlation name [ left paren derived column list right paren ] ] | derived table [ AS ] correlation name [ left paren derived column list right paren ] | joined table delete statement: searched ::= DELETE FROM table reference [ WHERE search condition ] Perhaps PostgreSQL should adopt this? Incidently, MS SQL server allows the following syntax: DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON The UPDATE syntax extension I mentioned above is also in MS SQL as far as I can tell (I've never personally used it). Would adding support for a from clause there make a difference to you? I'm happy as long as the 'add_missing_from' is disabled or changed so that it doesn't affect SELECT. And yes, this extension looks good. Perhaps consider changing the second FROM to USING (mimicking MySQL instead of MS SQL server). I think it would lessen the risk of introducing ambiguities in the parser (and it looks better than repeated FROM's). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Question regarding the mailing list.
On several occations I have replied to mails originating from someone on the pgsql-general list. I send the reply to: To: the person in question CC: [EMAIL PROTECTED] The person in question receives the mail but it never shows up in the list archive or in the newsgroup. Has anyone here had similar experiences? What am I doing wrong? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question regarding the mailing list.
Marco, I've seen something similar, but I know it's my fault. I've set a Role up in Pine, that _should_ change the From: to the correct one. This list accepts messages only from subscribed users, and, as you may have guessed, the address I'm subscribed with is different from my main one. The problem is that sometimes Pine does not switch Role (should do that automatically) and I simply forget to check before sending. But the list sends me a 'stalled post' message when that happenes. Thanks for the tip, but that's cannot be the cause of my problem. I've double checked the messages that I sent and the From: field is correct. I don't get any 'stalled post' messages either. It's dead silent. In fact, I have sent 3 mails to you personally on the subject Reasoning behind process instead of thread based. One had a CC to the gmane newsgroup. That one showed up. The other two had a CC to the pgsql-general list. You got at least one of them (since you replied), but the list didn't. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Question regarding the mailing list.
Marc G. Fournier wrote: I'm seeing all of your notes, and am not in the CC either ... You see the posts that doesn't show up in the archives or on the newsgroups ?! If you do, you will see exactly 3 posts on the subject Reasoning behind process instead of thread based that are addressed to Marco. Please note that there's several more posts from me on this particular subject. - thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Question regarding the mailing list.
Hmm, I'm using another SMTP server now that actually informs me when it is unable to deliver. Apparently it bounces intermittently with this message: Your message of Fri, 29 Oct 2004 17:08:35 +0200 entitled: Re: [GENERAL] Question regarding the mailing list. could not be delivered to the following recipient(s): [EMAIL PROTECTED] -- 200.46.204.209 does not like recipient. Remote host said: 550 5.7.1 [EMAIL PROTECTED]... Relaying denied Giving up on 200.46.204.209. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Reasoning behind process instead of thread based
Tom Lane wrote: That argument has zilch to do with the question at hand. If you use a coding style in which these things should be considered recoverable errors, then setting up a signal handler to recover from them works about the same whether the process is multi-threaded or not. The point I was trying to make is that when an unrecognized trap occurs, you have to assume not only that the current thread of execution is a lost cause, but that it may have clobbered any memory it can get its hands on. I'm just arguing that far from all signals are caused by unrecoverable errors and that threads causing them can be killed individually and gracefully. I can go further and say that in some multi-threaded environments you as a developer don't even have the opportunity to corrupt memory. In such environments the recognized traps are the only ones you encounter unless the environment is corrupt in itself. In addition, there are a number of techniques that can be used to make it impossible for the threads to unintentionally interfere with each others memory. I'm not at all contesting the fact that a single-threaded server architecture is more bug-tolerant and in some ways easier to manage. What I'm trying to say is that it is very possible to write even better, yet very reliable servers using a multi-threaded architecture and high quality code. ... The point here is circumscribing how much can go wrong before you realize you're in trouble. Ok now I do follow. With respect to my last comment about speed, I guess it's long overdue to kill this thread now. Let's hope the forum stays intact :-) Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Reasoning behind process instead of thread based
[EMAIL PROTECTED] wrote: So Thomas, you say you like the PostgreSQL process based modell better than the threaded one used by MySQL. But you sound like the opposite. I'd like to know why you like processes more. Ok, let me try and explain why I can be perceived as a scatterbrain :-). PostgreSQL is a very stable and well functioning product. It is one of the few databases out there that has a well documented way of adding plugins written in C and quite a few plugins exists today. You have all the server side languages, (PL/pgsql PL/Perl, PL/Tcl, PL/Java, etc.), and a plethora of custom functions and other utilities. Most of this is beyond the control of the PostgreSQL core team since it's not part of the core product. It would be extremely hard to convert everything into a multi-threaded environment and it would be even harder to maintain the very high quality that would be required. I think PostgreSQL in it's current shape, is ideal for a distributed, Open Source based conglomerate of products. The high quality core firmly controlled by the core team, in conjunction with all surrounding features, brings you DBMS functionality that is otherwise unheard of in the free software market. I believe that this advantage is very much due to the simplicity and bug-resilient single-threaded design of the PostgreSQL. My only regret is that the PL/Java, to which I'm the father, is confined to one connection only. But that too has some advantages in terms of simplicity and reliability. So far PostgreSQL At present, I'm part of a team that develops a very reliable multi-threaded system (a Java VM). In this role, I've learned a lot about how high performance thread based systems can be made. If people on this list wants to dismiss multi-threaded systems, I feel they should do it based on facts. It's more than possible to build a great multi-threaded server. It is my belief that as PostgreSQL get more representation in the high end market where the advantages of multi-threaded solutions get more and more apparent, it will find that the competition from a performance standpoint is sometimes overwhelming. I can't say anything about MySQL robustness because I haven't used it much. Perhaps the code quality is indeed below what is required for a multi-threaded system, perhaps not. I choose PostgreSQL over MySQL because MySQL lacks some of the features that I feel are essential, because it does some things dead wrong, and because it is dual licensed. Hope that cleared up some of the confusion. Regards, Thomas Hallgren ---(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: [GENERAL] Reasoning behind process instead of thread based
Martijn van Oosterhout wrote: Now you've piqued my curiosity. You have two threads of control (either two processes or two threads) which shared a peice of memory. How can the threads syncronise easier than processes, what other feature is there? AFAIK the futexes used by Linux threads is just as applicable and fast between two processes as two threads. All that is required is some shared memory. Agree. On Linux, this is not a big issue. Linux is rather special though, since the whole kernel is built in a way that more or less puts an equal sign between a process and a thread. This is changing though. Don't know what relevance that will have on this issue. Shared Memory and multiple processes have other negative impacts on performance since you force the CPU to jump between different memory spaces. Switching between those address spaces will decrease the CPU cache hits. You might think this is esoteric and irrelevant, but the fact is, cache misses are extremely expensive and the problem is increasing. While CPU speed has increased 152 times or so since the 80's, the speed on memory has only quadrupled. Or are you suggesting the only difference is in switching time (which is not that significant). not that significant all depends on how often you need to switch. On most OS'es, a process switch is significantly slower than switching between threads (again, Linux may be an exception to the rule). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasoning behind process instead of thread based
Marco Colombo wrote: [processes vs threads stuff deleted] In any modern and reasonable Unix-like OS, there's very little difference between the multi-process or the multi-thread model. _Default_ behaviour is different, e.g. memory is shared by default for threads, but processes can share memory as well. There are very few features threads have that processes don't, and vice versa. And if the OS is good enough, there are hardly performance issues. Most servers have a desire to run on Windows-NT and I would consider Solaris a modern and reasonable Unix-like OS. On both, you will find a significant performance difference. I think that's true for Irix as well. Your statement is very true for Linux based OS'es though. I think that it would be interesting to discuss multi(processes/threades) model vs mono (process/thread). Mono as in _one_ single process/thread per CPU, not one per session. That is, moving all the scheduling between sessions entirely to userspace. The server gains almost complete control over the data structures allocated per session, and the resources allocated _to_ sessions. I think what you mean is user space threads. In the Java community known as green threads, Windows call it fibers. That approach has been more or less abandoned by Sun, BEA, and other Java VM manufacturers since a user space scheduler is confined to one CPU, one process, and unable to balance the scheduling with other processes and their threads. A kernel scheduler might be slightly heavier but it does a much better job. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Comment on timezone and interval types
Martijn, I agree. One issue I can think of is that if you store each timestamp as a (seconds,timezone) pair, the storage requirements will balloon, since timezone can be something like Australia/Sydney and this will be repeated for every value in the table. I don't know how to deal easily with this since there is no unique identifier to timezones and no implicit order. The only solution I can think of is have initdb create a pg_timezones table which assigns an OID to each timezone it finds. Then the type can use that. I think this is a good solution actually, any thoughts? Using OID's is a good idea, but I think a canonical list of known timezone to OID mappings must be maintained and shipped with the PostgreSQL core. If OID's are generated at initdb time, there's a great risk that the OID's will differ between databases using different versions of PostgreSQL. That in turn will have some negative implications for data exchange. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Theory
Mayra, I need info on the caracteristics of object relational databases and their advantages as well as disdvantages in comparison to relational databases and OO Databases! Please explain these chacteristics with respect to what Postgresql can and cannot do. Thanks for your assistance. With respect to the IMO very helpful reply you got from Jeff Davis on this topic yesterday, what is it you expect from this list? We won't write an essay for you you know. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Reasoning behind process instead of thread based
[EMAIL PROTECTED] wrote: Two: If a single process in a multi-process application crashes, that process alone dies. The buffer is flushed, and all the other child processes continue happily along. In a multi-threaded environment, when one thread dies, they all die. So this means that if a single connection thread dies in MySQL, all connections die? Seems rather serious. I am doubtful that is how they have implemented it. That all depends on how you define crash. If a thread causes an unhandled signal to be raised such as an illegal memory access or a floating point exception, the process will die, hence killing all threads. But a more advanced multi-threaded environment will install handlers for such signals that will handle the error gracefully. It might not even be necesarry to kill the offending thread. Some conditions are harder to handle than others, such as stack overflow and out of memory, but it can be done. So to state that multi-threaded environments in general kills all threads when one thread chrashes is not true. Having said that, I have no clue as to how advanced MySQL is in this respect. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Reasoning behind process instead of thread based
Dann, I'm not advocating a multi-threaded PostgreSQL server (been there, done that :-). But I still must come to the defense of multi-threaded systems in general. You try to convince us that a single threaded system is better because it is more tolerant to buggy code. That argument is valid and I agree, a multi-threaded environment is more demanding in terms of developer skills and code quality. But what if I don't write crappy code or if I am prepared to take the consequences of my bugs, what then? Maybe I really know what I'm doing and really want to get the absolute best performance out of my server. There are clear advantages to separate process space for servers. 1. Separate threads can stomp on each other's memory space. (e.g. imagine a wild, home-brew C function gone bad). Not all servers allow home-brewed C functions. And even when they do, not all home-brewers will write crappy code. This is only a clear advantage when buggy code is executed. 2. Separate processes can have separate user ids, and [hence] different rights for file access. A threaded server will have to either be started at the level of the highest user who will attach or will have to impersonate the users in threads. Impersonation is very difficult to make portable. Yes, this is true and a valid advantage if you ever want access external and private files. Such access is normally discouraged though, since you are outside of the boundaries of your transaction. 3. Separate processes die when they finish, releasing all resources to the operating system. Imagine a threaded server with a teeny-tiny memory leak, that stays up 24x7. Eventually, you will start using disk for ram, or even use all available disk and simply crash. Sure, but a memory leak is a serious bug and most leaks will have a negative impact on single threaded systems as well. I'm sure you will find memory leak examples that are fatal only in a multi-threaded 24x7 environment but they are probably very few overall. Threaded servers have one main advantate: Threads are lightweight processes and starting a new thread is faster than starting a new executable. A few more from the top of my head: 1. Threads communicate much faster than processes (applies to locking and parallel query processing). 2. All threads in a process can share a common set of optimized query plans. 3. All threads can share lots of data cached in memory (static but frequently accessed tables etc.). 4. In environments built using garbage collection, all threads can share the same heap of garbage collected data. 5. A multi-threaded system can apply in-memory heuristics for self adjusting heaps and other optimizations. 6. And lastly, my favorite; a multi-threaded system can be easily integrated with, and make full use of, a multi-threaded virtual execution environment such as a Java VM. ... Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug or stupidity
Martijn, I realize that the change I'm proposing might be too complex to be added in the upcoming 8.0 release. I do find this discussion interesting though, so please bear with me while I try to tie up some loose ends. UPDATE [ ONLY ] table SET col = expression [, ...] [ FROM fromlist ] [ WHERE condition ] Perfectly reasonable addition, but not strictly SQL standard. Also, the scope is not guessed, it's totally unambiguous. Ok, bad choice of words. It's not guessed, and I agree, this is perfectly reasonable. Anyway, I think there's a confusion in the phrase from clause. There's no confusion. I fully understand the differences. That's why think that the term 'add_missing_from' is misleading. From a strict syntax point of view it implies expansion to the statement we both agreed should be disallowed. The fact that it doesn't actually add a missing from but rather expands the scope for the predicate is somewhat confusing. Hence my suggestion that the variable is renamed. But I guess it comes down to to how strictly you want to follow the SQL standard. I think it's OK to deviate from the standard and add features. My whole argument in this thread is based on the fact that PostgreSQL adds tables to the FROM clause of a SELECT which may produce incorrect results and that this magic is performed by default. My suggestion is that we rename the add_missing_from to: update_delete_autoscope and that this option has no effect on SELECT clauses. It would be more or less harmless to have it enabled by default. As pointed out above, it's not needed to update. And add_missing_from currently has no effect on delete, so your suggested option appears to be merely the inverse of what is already there. What I was trying to say is that: a) since the 'add_missing_from' affects the predicate scope for DELETE's, UPDATE's, and SELECT's, and since those statements have different ways of expressing this scope, the current choice of name is a bit confusing and b) it would be nice if the variable affected DELETE and UPDATE scopes only. Now you point out that an UPDATE can have a FROM clause, so let me revise my suggestion and instead say: 1. Let's add a variable named autoscope_for_delete that is enabled by default and only affects the scope of a DELETE predicate. We do this to maintain backward compatibility. 2. Let's change so that add_missing_from is disabled by default and doesn't affect the DELETE statement at all. 3. The autoscope_for_delete will use generate notices and add_missing_from will generate warnings. DELETE FROM first_table x WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo 4) The SQL standard (what I can find on the web anyway) doesn't allow an alias there, and neither does PostgreSQL. The SQL 2003 draft I have states: delete statement: searched ::= DELETE FROM target table [ [ AS ] correlation name ] [ WHERE search condition ] whereas SQL 3 is more elaborated: table reference ::= table name [ [ AS ] correlation name [ left paren derived column list right paren ] ] | derived table [ AS ] correlation name [ left paren derived column list right paren ] | joined table delete statement: searched ::= DELETE FROM table reference [ WHERE search condition ] Perhaps PostgreSQL should adopt this? Incidently, MS SQL server allows the following syntax: DELETE FROM Table1 FROM Table1 INNER JOIN Table2 ON The UPDATE syntax extension I mentioned above is also in MS SQL as far as I can tell (I've never personally used it). Would adding support for a from clause there make a difference to you? I'm happy as long as the 'add_missing_from' is disabled or changed so that it doesn't affect SELECT. And yes, this extension looks good. Perhaps consider changing the second FROM to USING (mimicking MySQL instead of MS SQL server). I think it would lessen the risk of introducing ambiguities in the parser (and it looks better than repeated FROM's). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Reasoning behind process instead of thread based
Martijn van Oosterhout wrote: A lot of these advantages are due to sharing an address space, right? Well, the processes in PostgreSQL share address space, just not *all* of it. They communicate via this shared memory. Whitch is a different beast altogether. The inter-process mutex handling that you need to synchronize shared memory access is much more expensive than the mechanisms used to synchronize threads. 2. All threads in a process can share a common set of optimized query plans. PostgreSQL could do this too, but I don't think anyone's looked into sharing query plans, probably quite difficult. Perhaps. It depends on the design. If the plans are immutable once generated, it should not be that difficult. But managing the mutable area where the plans are cached again calls for expensive inter-process synchronization. Table data is already shared. If two backends are manipulating the same table, they can lock directly via shared memory rather than some OS primitive. Sure, some functionality can be achieved using shared memory. But it consumes more resources and the mutexes are a lot slower. I think PostgreSQL has nicely combined the benefits of shared memory with the robustness of multiple processes... So do I. I've learned to really like PostgreSQL and the way it's built, and as I said in my previous mail, I'm not advocating a switch. I just react to the unfair bashing of multi-threaded systems. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Reasoning behind process instead of thread based
Tom Lane wrote: Right. Depending on your OS you may be able to catch a signal that would kill a thread and keep it from killing the whole process, but this still leaves you with a process memory space that may or may not be corrupted. Continuing in that situation is not cool, at least not according to the Postgres project's notions of reliable software design. There can't be any may or may not involved. You must of course know what went wrong. It is very common that you either get a null pointer exception (attempt to access address zero), that your stack will hit a write protected page (stack overflow), or that you get some sort of arithemtic exception. These conditions can be trapped and gracefully handled. The signal handler must be able to check the cause of the exception. This usually involves stack unwinding and investingating the state of the CPU at the point where the signal was generated. The process must be terminated if the reason is not a recognized one. Out of memory can be managed using thread local allocation areas (similar to MemoryContext) and killing a thread based on some criteria when no more memory is available. A criteria could be the thread that encountered the problem, the thread that consumes the most memory, the thread that was least recently active, or something else. It should be pointed out that when we get a hard backend crash, Postgres will forcibly terminate all the backends and reinitialize; which means that in terms of letting concurrent sessions keep going, we are not any more forgiving than a single-address-space multithreaded server. The real bottom line here is that we have good prospects of confining the damage done by the failed process: it's unlikely that anything bad will happen to already-committed data on disk or that any other sessions will return wrong answers to their clients before we are able to kill them. It'd be a lot harder to say that with any assurance for a multithreaded server. I'm not sure I follow. You will be able to bring all threads of one process to a halt much faster than you can kill a number of external processes. Killing the multithreaded process is more like pulling the plug. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bug or stupidity
Stephan, Perhaps the 8.0 would be a perfect time since it's a change of the major number. Maybe, but I think it'll be a hard sell without a replacement for the delete form that works when it's off. I'm not sure I understand this. Apparently you want tables to be added to the FROM clause of a DELETE statement. Why? If you have more than one table listed in the FROM clause, how do you know which one that is subject to DELETE? Surely you're not suggesting that the DELETE should affect more than one table? If the WHERE clause that defines the criteria for deletion involves more than one table, then you'd use a sub select and that has a FROM clause of its own. Can you give me an example on what you mean? In addition, until we have a form of delete which allows a from list, there are some queries which are really more naturally written in a form similar to add_missing_from (although from lists would be better). Still, if the query is incorrect, I want to know about it. I don't ever But, is the query incorrect? It does what PostgreSQL says it will. That's not what the spec says it'll do, but the same is true of most of the extensions, and I don't think people generally consider queries using those as incorrect. I haven't seen any other extension that, when enabled, attempts to improve badly written SQL in a way that potentially gives incorrect query results. As I said in another post, this is like having a compiler that instead of complaining about a misspelled variable, adds a new one. I can give you an example why I think this option is bad: Assume that you work with some client software. You write your queries and you test your system. You see some notifications passing by from the server (perhaps) but you don't pay much attention to them. You get notifications all the time for other reasons. Nothing to worry about. In fact, your test system is written to trigger on errors and warnings and ignore notifications. So all your tests run fine. You ship to your customers. The customers starts adding data to tables and finds some strange behavior. It turns out that everything is caused by tables being added to the FROM clause. You didn't see the problem in your test because there, the added table had less than 2 tuples in it. As I said before, I don't object to the presence of this option so that people that really knows _why_ they enable it can do so, but I strongly object to having this option enabled by default. I suggest that: 1. Have this option disabled by default. 2. Print WARNING's rather than notifications when tables are added. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Bug or stupidity
Martijn, Do you have a better suggestion, other than forbidding the currently allowed syntax? Yes I do. We agree that my second example should be disallowed since the semantics of the FROM clause is different for a DELETE so the add_missing_from is actually not adding to a FROM clause, it is guessing the scope for the predicate. I assume the same is true for an UPDATE where there is no FROM at all. My suggestion is that we rename the add_missing_from to: update_delete_autoscope and that this option has no effect on SELECT clauses. It would be more or less harmless to have it enabled by default. Perhaps the add_missing_from should remain but then only affect the SELECT and as disabled by default. Anyway, I think the reasoning so far is, the default stays as it is until someone comes up with a non-confusing way of adding a real FROM clause to DELETEs. SQL already defines a stright forward way to do that. Consider the following PostgreSQL syntax: DELETE FROM first_table WHERE first_table.id = second_table.xid AND second_table.foo 4 in standard SQL this would be: DELETE FROM first_table x WHERE x.id IN (SELECT y.xid FROM second_table y WHERE y.foo 4) The number of characters is almost the same in both statements even for a very simple WHERE clause thanks to aliasing. The benefits of aliasing increases as the WHERE clause gets more complicated. For composite keys or other non key based relationships, the EXISTS clause can be used. Why confuse people with yet another syntax? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Bug or stupidity
Stephan Szabo wrote: It's enabled in large part for backwards compatibility. There's a runtime option that controls the behavior (add_missing_from). IMHO, it would be a more natural choice to have the add_missing_from disabled by default. Why would anyone *ever* want faulty SQL being magically patched up by the dbms? Ok, so some older installations might break when this is changed but the option is still there. Let applications that depend on this somewhat magical behavior enable it rather than have everyone else potentially run into the same problem as Philip. Regards, Thomas Hallgren ---(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: [GENERAL] Bug or stupidity
Stephan, In general, when we add a backwards compatibility option, we give a couple of versions before the default is changed. Perhaps the 8.0 would be a perfect time since it's a change of the major number. In addition, until we have a form of delete which allows a from list, there are some queries which are really more naturally written in a form similar to add_missing_from (although from lists would be better). Still, if the query is incorrect, I want to know about it. I don't ever want an incorrect behavior as a result of some behind the scenes magic. For me, there's no exception to that rule and my guess is that very few people would disagree if they think about it more in depth. This option helps no one. It only adds to the confusion. I think that many people do, even if they don't realize it. If people write incorrect SQL because this looks like the natural way of doing it, don't you think it's fair if they find out about the error ASAP? Catching errors early in the development process is generally considered a good thing. When this option is enabled, errors might be hidden (you get the notification that not everyone will pay attention to, or even see). I consider that a very *bad* thing. It's perhaps OK that the option exists so that old legacy system can keep on running, but to have it enabled by default is not good at all. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Verifying a user.
I'm connected to a database and I want to verify that a username and password for some user is correct. I know I can verify a users existence by doing: select exists(select * from pg_user where usename = $1) but I would like to verify the correctness of the password as well. Is there a way to do that using SQL? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Christopher, It seems to me that some vital components have already been set up, considering: a) pgxs provides a build environment to make it easier to add in third party extensions without each of them having to have its own full PG source tree. b) PGFoundry is getting set up as a hopefully-decent place to host things that would be likely to fit into that second tier of Extensions that ought to be ubiquitous. Those can also play off against each other; for an extension to become ubiquitous, it is only reasonable for its developers to improve the builds to make them play well with pgxs. The way I can see this head is for there to be a significant population of projects on PGFoundry that, by virtue of using pgxs, become as easy to add in as most of the contrib items are now, and perhaps roughly as easy as the average BSD Port. If this whas combined with Jan W. suggestion (community votes to create recommendations) it would be very close to what I had in mind in the first place. A project could be hosted on PGFoundry where the verify process could be explained, i.e. 1. your project must be pgxs compatible. 2. it must be hosted on pgFoundry. 3. it must have automatic regression testing built in (perhaps this is part of #1). 4. documentation must follow some guidelines so that it is easy to combine it with other docs. 5. someone must suggest it as a candidate for inclusion and give a good motivation. 6. there's a voting period and a minimum number of votes. 7. if the votes are in your favor, your project will be part of the supported configurations and you will be asked to participate in the integration work. This project could also host the voting mechanism and the supported configurations. My Concerns: Who is behind PGFoundry? Is performance ok now :-) This project might be perceived as a thirdparty add-on and thus, fail its purpose. The steering committee must stand behind this officially. Will you? What's your opinion about the suggestion? Any ideas what the project should be named? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Marc G. Fournier wrote: 1. your project must be pgxs compatible. 2. it must be hosted on pgFoundry. 3. it must have automatic regression testing built in (perhaps this is part of #1). 4. documentation must follow some guidelines so that it is easy to combine it with other docs. 5. someone must suggest it as a candidate for inclusion and give a good motivation. Now, inclusion into where? The list? The idea is that my suggested project, (I henceforth refer to it as this project) should maintain some number of packaged configurations. So what I mean is inclusion of the candidate project artifacts in some (or all) of those packages. 6. there's a voting period and a minimum number of votes. This one, I would say, will be very difficult ... what if its a one of piece of software, that 2 ppl are using, but its very good at what it does? Or a one of piece of software, that sucks royally but is the only thing available, and 100 ppl are using? You're right. This is not crystal clear. How about this: For the first category, an inclusion could be possible if the software has a potential to reach more users and can make the offering more complete in some respect. If that's not the case, it should be included. Most software that sucks royally will be filtered out in the first 4 steps. If it is not, and if a lot of people vote to get it in, well then it does not suck so bad after all, at least not according to the voters. So it's in provided nothing better exists already. It can still be replaced of course, should something better come along. 7. if the votes are in your favor, your project will be part of the supported configurations and you will be asked to participate in the integration work. Integration work ... where? In two places. Most of it takes place in the candidate project but documentation overviews, composite configurations etc. must be updated in this project to include the artifacts from the new project. Such global changes can be made by the contributor in the form of patches. This project might be perceived as a thirdparty add-on and thus, fail its purpose. The steering committee must stand behind this officially. Will you? What's your opinion about the suggestion? Behind what? A list on pgFoundry of recommended software? Sure ... integrating that list into the physical postgresql.tar.gz file that is the core server distribution? No ... The core server distribution is left untouched by all this. It would be really nice if this project could publish packages using your BitTorrent and ftp mirrors though. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Tom Lane wrote: Enlarging the core committee by the amount you seem to be thinking of would transform it into something quite different than it is now (in particular it would be too large to make decisions effectively, IMHO). I can relate to that. Lean and mean is good. So pehaps the core committee should not be enlarged. Instead, another committee should be established for this purpose that work closely together with the core committee (perhaps cross staffed to some extent). The real issue here is finding someone to do the work --- it's notable that this thread has been going on for some time now and no one has actually stepped up and volunteered to *do* anything. I think if there were someone out there willing to do it, they could and would do it, with or without core's blessing. Given a financial scenario that would make it possible, I'd love to help with this. My current employment however has no connection to PostgreSQL. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Karsten Hilbert wrote: a) More software can make use of your good name and reputation. That's rather dangerous, don't you think ? If PostgreSQL proper (eg the core server) wants to keep its good name it better make sure it is bundled with good add-ons. And that would require precisely the additional workload that several core people have explained they must avoid in order to be able to focus on the core engine to keep its good reputation. So, unless people outside the core team take up the task it won't happen. And if they do - what's the difference to the current state of affairs ? I see you point and I don't disagree. But let me give you a different view. When I look at the current state of affairs, I see a PostgreSQL name that, in spite of very skilled people, great technology, a very vibrant community, and IMO a golden opportunity, is held back by a fear (well grounded, no doubt) that growth will increase the workload and thus make it harder to maintain the core engine. I'm not an advocate of commercial packaging, nor packaging performed by outsiders and I don't think packages bundled with operating systems is a great idea either. I'm a fan of free open source and I think the way to archive growth is to appeal to more sponsors. More sponsors equals more resources. More resources, if handled correctly, could both make PostgreSQL grow *and* lessen the workload for the core developers. I really like Jan Wiecks suggestion. To me that sounds like the way to get started. Recommended add-ons will feel a lot safer to the end users and it will give some perception of growth. I'd like to help out and I'm perhaps able to put in a couple of hours a week. Maybe there's more people out there that would like to help? If not for any other cause, then maybe to promote their own product. In so, we need to be coordinated. Preferably by someone from within. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ANN: PL/Java now supports both PostgreSQL 8.0 and 7.4
I tried this on the pgsql-announce list but for some reason it doesn't show up. The 1.0.0.b4 release of PL/Java is out. It takes full advantage of the new exception handling and custom variables introduced in PostgreSQL 8.0 and a native Windows port is included in the distribution (7.4 still supported with Cygwin on Windows). On Linux, PL/Java 1.0.0.b4 includes binary distributions compiled using GCJ (the GNU Java) to take full advantage of Postgres capability of using preloaded modules. Please visit http://gborg.postgresql.org/project/pljava for more info. Regards, Thomas Hallgren ---(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: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Marc, Since I (and I don't believe anyone else on core) uses Java ... shouldn't it be up to the developer of the PL/J* modules to do this? We can't weigh which one is better then the other, as we don't use it ... Of course the contributors should supply as much of this material as possible. The point I'm trying to make is that there's often no incentive to do so, nor a good place to put it. Also, how does someone support something that they don't use? Again, that is the developer of PL/J*'s job to do, not ours ... Again, I'm not trying to offload work from the contributors onto the members of core. This is about how things are perceived by the PostgreSQL customers. Of course the contributors must continue to support their products. If they don't, I'd expect the supported status to be dropped at some point. At that rate, we'll have to distribute via CD to anyone that wants PostgreSQL ... cause downloading it via FTP won't be a viable option anymore :) In times when people download gigabytes of film and music using BitTorrent, I think that's the least of our problems. But of course, the distribution should be kept at a reasonable size. That's why I'd like a better solution to replace the inferior one and to limit the number of overlaps. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Unsupported 3rd-party solutions (Was: Few questions on postgresql (dblink, 2pc, clustering))
Jim Worke wrote: I don't mean to be rude or anything, but having 3rd-party solution is a scary option for a business enterprise. I know that they're stable and all, but if it's not supported by PostgreSQL themselves (i.e. included in PostgreSQL as a whole package), we're afraid that we have to change our code/design in case the product has stopped progress. For example, pgcluster's patch is for PostgreSQL 7.3.6. It's not in sync with PostgreSQL's current version (I'm not blaming the guy... He's created a very good solution and I'm thankful for that). It's just that for my company (and I guess many other companies too), it's more appealing to have a database solution that comes in a package. Those are very interesting remarks. I'm the author of PL/Java, a module that also could be thought of as not supported by PostgreSQL themselves, and I've made the same reflection as you have. It would be beneficial to have some organisational entity within Postgres where this issue could be addressed (i.e. packaging/synchronization and supported configurations). I think it could give a real boost to PostgreSQL as such. Sure, an open source community does not make support commitments. But the PostgreSQL community is large and that creates (a sense of) safety and continuity. This sense is not automatically transferred to the 3rd-party solutions. From a users perspective and perhaps especially from the decision makers perspective, the fact that you have to download various modules from gborg etc. is indeed scary. Who will support your chosen solution a year from now? IMHO, if PosgreSQL is aiming for larger business acceptance, this has to be resolved. Contributors like myself must be given the opportunity to get things verified and checked in as supported. It would do PostgreSQL an awful lot of good if there where supported configurations including replication, server side language support (Perl, Tcl, Java, etc.), JDBC and ODCB drivers, and other things that you'd normally find in commercial enterprise solutions. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Plperlu and sending emails, is it safe?
Tom Lane wrote: It's a *really* bad idea to expose that to users of the PL. Alvaro Herrera wrote: You want to abort the transaction on the callback? What for? You could have aborted it earlier. Of course, in a function you could save the mails you are going to send and register a callback for the actual send, or discard if the transaction is going to abort. But you have to be very careful not to do cause errors during the sending of the mails ... All in all, I think it's easier to do it with NOTIFY or a cron job. I think I need to explain what I want to do in more detail. What I have in mind would not in any way compromise the transaction code. If the EOXactCallback will have that effect if it fails, then that's not the mechanism that I want (see questions at the end). What I really want are two things. Both of them should, IMHO be completely harmless from postgres point of view: 1. A callback that occurs after the fact, i.e. after *all* transactional code has completed for a specific transaction. Thus, a failure in this code would not have any effect whatsoever on the transaction. Hence, it would be perfectly safe to invoke this callback just as any other function and there would be no danger involved if user code is executed in a Pl/lang module. It would execute very similar to a NOTIFY after transaction commit with the slight difference that no explicit NOTIFY is needed and that there will be a callback invocation assoicated with both commit and abort. The documentation for this callback must of course clearly state that there's no way that the user can change the outcome of the transaction. It's already complete (or aborted). For the email case this would mean that if the sending of the mail fails, the best the callback can do is to log that fact and rely on other mechanisms to clean it up later. No big deal since smtp is far from fail safe anyway. 2. A callback that occurs before the fact, i.e. just before the commit code is entered. It's executed just like any deferred trigger would be and here, an error would cause the transaction to abort. Since it's just like any other trigger, there should be no problem relaying the call to user code executed by a Pl/lang module. So why not abort earlier or use NOTIFY this? Assume that the code executed before the fact does some complex form of integrigy checking that cannot be bound to one specific table (and thus, not to one deferred trigger). It might for instance be based on a RETE Rule executing on a bill of material collected from actions performed throughout the transaction. To rely on that the client will issue a NOTIFY just before the commit in order to maintain integrity is not feasible. If anything, that would be to move the responsabilities to the wrong place. I see the SQL layer as the access API towards my data. Integrity must be maintained behind that API without the need for additional calls that might be forgotten. The email example would probably have limited use of the before the fact callback. Perhaps it could ensure that an SMTP connection exists if emails have been generated (using a fairly short timeout :-) ). Could NOTIFY be used for the after the fact case then? Well, AFAIK there is no NOTIFY equipped with an abort so where would I place the clean-up code? And even if there is, I'd argue that this, again, would be moving responsabilities to the wrong place. As the database designer I'd like the ability to design a system where an attempt will be made to send the emails on commit if some specific changes has been made to the database, no matter what. I don't want to rely on that all clients will issue a NOTIFY. Questions I have now are: 1. I see that some additonal callbacks are executed after the EOXact stuff and that interrupts are disabled during exeuction. So this is probably not the mechanism that I want. Question is, is this something that is in use today? Or could the CallEOXactCallbacks be moved to the end? 2. I guess that the answer to my first question is no, it cannot be moved since that would break backward compatibility. If that's the case, do you see a problem in introducing a more harmless callback mechanism that can be called with a state enum denoting PreCommit, PostCommit, PostAbort? Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [OT] Dilemma about OS - Postgres interaction
Harald Fuchs wrote: In article [EMAIL PROTECTED], Rory Campbell-Lange [EMAIL PROTECTED] writes: I should have mentioned that we need the messages sent very soon after they have landed in the 'inbox'; otherwise cron would definitely be the way to go -- including an @reboot line. This rules out a cronjob. How about writing a pl/perlu function which sends the mail and calling that function by an AFTER INSERT trigger? Use of triggers has one drawback. What happens if the insert is rolled back when the transaction attempts to commit? I've mentioned this before. It would be *very* beneficial if it was possible for a Pl/lang module to subscribe to transactional events. That in turn would enable a solution like this to defer the actual sending until the transaction is succesfully commited and to remove any stray messages should it be rolled back. Awaiting a more full-blown 2 phase commit solution I would settle for the ability to subscribe to four events (actually, this would work pretty good with 2pc as well). 1. Commit issued. This event is sent before any of the normal commit code is executed. In essence, it's similar to any other normal SQL command executed within the transaction itself. An event handler is allowed to make changes to the database. A change will schedule a new Commit issued event to be sent once all event handlers have been called. This process repeats until no event handler makes a change. The mechanism will need a loop counter and a configurable threshold. A commit can be prevented during Commit issued using normal error handling. 2. Commit start. From now on, no changes are permitted. The users view of the data will not change. This is where deferred constraints, transaction wide rule checking etc. can be executed. A handler is allowed to terminate the commit using normal error handling (a normal return from the handler is equivalent to a vote commit in a 2pc environment). 3. Commit complete. The commit is at it's final stage. From the database perspective, the commit is complete and cannot be rolled back. A handler can use this to attempt to send prepared emails etc. A failure will not have any effect on stored data. Normally, the handler would perform a minimum of processing here (since it cannot fail) and do most of the work at Commit start. 4. Rollback complete. This notification tells the handler that everything has been rolled back. AFAICS, only two things are required from the PostgreSQL backend. A simple event sending mechanism to be triggered before and after the (complete) process of committing or rolling back, and the ability to track attempts to make a change to the database. The latter of course to log the fact that a change has occured (for Commit issued) or to prevent it from happening (all other handlers). I'm sure this functionality is there already. I'd be happy to write a patch if someone could point out where I find it. Kind regards, Thomas Hallgren ---(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: [GENERAL] 7.4 windows version?
Tom Allison wrote: Doug McNaught wrote: Jonathan Barnhart [EMAIL PROTECTED] writes: I know that postgres runs under Cygwin. It was announced that 7.4 would have a windows native version or some such. I have found nothing but Cygwin versions however. Native Windows support is slated for 7.5 (which is nearing beta at this point). You mean I'll be able to run this on my windows computer at work instead of the Access? OMG!!! ... I'm so happy ... ---(end of broadcast)--- TIP 8: explain analyze is your friend The Object Management Group (OMG) would be smiling too if they read your post. I wonder if they ever considered the more divine translation :-) Regards, Thomas Hallgren ---(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
[GENERAL] How to tell when postmaster is ready
I'm writing a small test harness. I have two threads. One that starts the postmaster and another that does all the testing and finally stops the postmaster with a pg_ctl stop. At present, the second thread starts with a sleep sufficient to ensure that the postmaster is running. Is there a proper way to test when the postmaster is ready to receive commands? Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] News outage?
Hi, I have not been able to connect to news.postgresql.org over the last couple of days. I've tried from several locations/computers. Is anyone else having the same problem? Kind regards, Thomas Hallgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
