Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Dennis Bjorklund said: On Sun, 25 Apr 2004, Andrew Dunstan wrote: Why do you want two names? Just keep the original casing, and a boolean saying if it's quoted or not. Sorry - brain malfunction - yes, original casing plus boolean would work. In effect you could derive the canonical form from those two. Dennis, Ideas still swirling a bit, but I was thinking that there would be a per database flag (which could indeed be set at db creation time) which would specify the flavor of canonical names being used - upper, or lower, or we could also consider exact (i.e. full case sensitivity, which I seem to recall is a mode that SQLServer allows, possibly even the default, but my memory could be rusty). The canonical form of an unquoted name is dictated by this setting, while the canonical form of a quoted name is the name as supplied. Two names clash if their canonical forms are identical, quoted or not. Assuming that we have a database with the flag set to use upper case canonical names, as per the standard, then ... Say that you have this in the table with the identifier name quoted -- Foo False Now you want to add the name FOO FOO True should you be allowed or is it a clash with the above? It's a clash. The canonical for of both is FOO What if you also add foo foo True No clash - FOO foo One of these two should be forbidden. And what about a quoted FOO: FOO False FOO True clash This case says it is not enough with an expressional unique index on (upper(name), quoted). It would be easier to enforce uniqueness if one store both the converted name and the original name: The constraint would in effect be on CASE WHEN quoted THEN name ELSE upper (name) END. The advantage of using a boolean is that a lot less work would need to be done to use whatever flag was being used for the DB. Possibly a reindex after the files are copied. It might fail on some highly pathological cases, but should never fail on our standard template databases. name orig_name - FOO NULL -- quoted one FOO FOO -- unquoted one and the first case FOO Foo -- unquoted FOO NULL -- clashes with the first, good foo NULL -- no clash, works fine With this one can always use upper case translation as per sql spec and psql can optionally show all unquoted identifiers as upper, lower or mixed case. My thought was that there would be a user setting that would allow resultset labels to use either canonical or literal names. Then we also have the INFORMATION_SCHEMA that should show the names in UPPER CASE when not quoted, this since applications that are written for the standard might depend on that (probably no application do today but it would be a valid case of use of the information schema). I see 2 possibilities: either use the upper case canonical setting I envisioned above, or change the information schema setup to force upper case labels via AS clauses in the views. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
On Mon, 26 Apr 2004, Andrew Dunstan wrote: Ideas still swirling a bit Sure, I'm thinking in public as well. Not something you want to do if you are afraid of being wrong and showing it :-) But I'm not. The constraint would in effect be on CASE WHEN quoted THEN name ELSE upper (name) END. That's simple enough (and pretty straight forward). -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
- PL/Java is well and interesting, but requires a barrel of non-free software, which makes installation and configuration anything but slick. I think it's a bit unfair to say that Pl/Java requires a barrel of non-free software. Pl/Java doesn't require any software besides the Java Runtime Environment and those who have an interest in Java is *very* likely to have a JRE installed already. I'm currently working on getting Pl/Java running with GNU's GCJ. Aside from a bug in gcj (reported to be fixed by the gcj people), it runs fine with GCJ. At present it requires the HEAD version and the GCJ 3.4 branch from GNU (not released yet). Releases of both are imminent. If PostgreSQL was equipped with an installer that modules like Pl/Java could hook into, I'd be more than willing to adhere to whatever requirements such an installer would impose. With the present lack of such semantics, I dare say that the installation is pretty slick anyway. Read this and comment http://gborg.postgresql.org/project/pljava/genpage.php?readme. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Christopher Browne [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted: Is anyone really ready for this sort of commitment? By that, I presume you mean... Are people prepared to stop working on the doubtless useful things that they are working on in favor of spending their time instead on this set of marketing-oriented systems integration tasks? I think the answer to that is likely a pretty clear NO. Perhaps more people should be attracted to the project? People that are not just purely technical? I concur with the original posting. Not because of the marketing aspects, but because I think PostgreSQL could benefit greatly if it was more modularized and had a stronger long-term planning. It would ease up a great deal for people like myself who really want to contribute. No grand restructuring is needed but more thoughts need to go into modularization and how various things plugs in to PostgreSQL, not just from a technical how to write the code perspective, but also from an assembly, deploy, and maintain angle. PostgreSQL has a vast amount of users and the customer base is growing. There is a demand that should not be neglected just because it's Market Driven. That's not fair the developers that does an excellent job with the product. They really deserve all goodwill they can get from the users. PostgreSQL runs on numerous systems, and in order to do what you are proposing, it would be necessary to play system favorites. - Configuration systems are inherently platform-specific. This is true. But a large part of a configuration system could be made platform independent. If a good modular configuration system was designed and maintained by the PostgreSQL community, I'm pretty sure the platform independent parts of it would see the light of day pretty quickly. 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: [HACKERS] btbulkdelete
On Sun, 2004-04-25 at 22:34, Manfred Koizar wrote: On -performance we have been discussing a configuration where a bulk delete run takes almost a day (and this is not due to crappy hardware or apparent misconfiguration). Unless I misinterpreted the numbers, btbulkdelete() processes 85 index pages per second, while lazy vacuum is able to clean up 620 heap pages per second. Is there a special reason for scanning the leaf pages in *logical* order, i.e. by following the opaque-btpo_next links? Now that FSM covers free btree index pages this access pattern might be highly nonsequential. I had considered implementing a mode where the index doesn't keep trying to reuse space that was freed by earlier deletes. For many situations where you are processing bulk inserts and bulk deletes, reusing space via the FSM ends up weaving the logical sequence into a very unsorted physical sequence. i.e. my thinking was about a way to keep logical looking more like physical, in certain situations. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
[EMAIL PROTECTED] wrote: PostgreSQL. Maybe it is in the form of a web server like Samba's SWAT utility, I don't know (A SWAT type utility could run as the PostgreSQL I've found webmin to be pretty good swat type tool...it's lacking some things to be a full postgres administration system, but I think they have the right idea... (4) Blessed projects, lets play favorites. Lets find good and meaningful extensions on gborg and ./contrib and work with the authors and make them part of the PostgreSQL environment. Projects like, replication, .NET service provider, ODBC, pgAdmin, etc. are important and users need to find them close to PostgreSQL's main location. (5) Programming languages. We need to make a programming language standard in PostgreSQL. plpgsql is good, but isn't someone working on a Java language. That would be pretty slick. Personally, I like the idea of plphp better. I haven't used either, though. plgsql will always have value in converting oracle deployments, so it is truly hard to standardize on a favorite here. I think the latest versions of pgAdmin are just awesome. I never used to like it very much, but it really is great software. I think any effort expended on beefing that project up is not wasted. Merlin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] FW: getting a crash during initdb
[sorry for the repost...lists down? Problem may or may not be on win32 version only] The catalog number was updated, so it was time to run initdb. Sometime over the weekend (or since the last initdb I ran, about a week), the source was updated which caused an AV CRASH during initdb...specifically during pg_depend step. Also, after initdb fails, the files are removed but the folders are not, causing subsequent attempts to initdb not to work...is this the expected hehavior? Here is the log: H:\initdb The files belonging to this database system will be owned by user Merlin.Moncure. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory c:/postgres/data ... ok creating directory c:/postgres/data/global ... ok creating directory c:/postgres/data/pg_xlog ... ok creating directory c:/postgres/data/pg_clog ... ok creating directory c:/postgres/data/base ... ok creating directory c:/postgres/data/base/1 ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in c:/postgres/data/base/1 ... ok initializing pg_shadow ... ok enabling unlimited row size for system tables ... ok initializing pg_depend [CRASH]... initdb: child process was terminated by signal 5 initdb: failed initdb: removing contents of data directory c:/postgres/data Deleted file - c:\postgres\data\pg_hba.conf [...] Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] btbulkdelete
On Mon, Apr 26, 2004 at 02:29:58PM +0100, Simon Riggs wrote: On Sun, 2004-04-25 at 22:34, Manfred Koizar wrote: Is there a special reason for scanning the leaf pages in *logical* order, i.e. by following the opaque-btpo_next links? Now that FSM covers free btree index pages this access pattern might be highly nonsequential. I had considered implementing a mode where the index doesn't keep trying to reuse space that was freed by earlier deletes. For many situations where you are processing bulk inserts and bulk deletes, reusing space via the FSM ends up weaving the logical sequence into a very unsorted physical sequence. i.e. my thinking was about a way to keep logical looking more like physical, in certain situations. See this: @inproceedings{DBLP:conf/sigmod/ZouS96, author= {Chendong Zou and Betty Salzberg}, editor= {H. V. Jagadish and Inderpal Singh Mumick}, title = {On-line Reorganization of Sparsely-populated B+trees}, booktitle = {Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data, Montreal, Quebec, Canada, June 4-6, 1996}, publisher = {ACM Press}, year = {1996}, pages = {115-124}, bibsource = {DBLP, \url{http://dblp.uni-trier.de}} } Maybe it can be useful. When I tried to implement it, there was no free-pages code, so first I had to do that (Tom Lane beat me to it though). Then I had to choose a different project. Maybe now it can be done. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] FW: getting a crash during initdb
Merlin Moncure wrote: [sorry for the repost...lists down? Problem may or may not be on win32 version only] The catalog number was updated, so it was time to run initdb. Sometime over the weekend (or since the last initdb I ran, about a week), the source was updated which caused an AV CRASH during initdb...specifically during pg_depend step. Also, after initdb fails, the files are removed but the folders are not, causing subsequent attempts to initdb not to work...is this the expected hehavior? No. The code currently does this on Windows: snprintf(buf, sizeof(buf), %s /s /q \%s\, rmtopdir ? rmdir : del, path); system(buf); Originally I had a builtin routine (see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/bin/initdb/initdb.c?rev=1.1content-type=text/x-cvsweb-markup ) but Bruce felt shelling out to rm/rmdir/del was easier. I guess this needs tweaking a bit. I have no idea what caused the pg_depend stuff to crash. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Is this possible? concatenating results from a subquery
Perhaps this helps: CREATE AGGREGATE concat ( BASETYPE = text, SFUNC= textcat, -- is function of operator 'text || text' STYPE= text, INITCOND = '' ); SELECT P.personid, P.name, concat( N.note ) AS allnotesbythisperson FROM tblperson AS P INNER JOIN tblnotes AS N ON N.personid=P.personid WHERE P.personid=34 GROUP BY P.personid, P.name; Regards, Janko Erwin Moller wrote: Hi! I face the following problem: 2 tables: tblperson and tblnotes tblperson: colums: personid (PK), name tblnotes: colums: noteid(PK), personid(references tblperson(personid)), note tblnotes has notes stored written by a person from tblperson identified (FK) by its personid. I make a select on one table with certain criteria and want to have a concatenation on a subquery results. Something like this: SELECT P.personid, P.name, concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) ) AS allnotesbythisperson FROM tblperson AS P WHERE (P.personid=34); The concat word I use is pure fantasy. Is this at all possible? I know I can easily circumvent te problem by my scriptinglanguage (PHP), but that will result in many extra queries. How do I proceed? TIA!! Regards, Erwin Moller ---(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
[HACKERS] PostgreSQL (7.3) on SMB/CIFS Shares on FreeBSD 5.1
[Previously posted to General list] I have an embedded system running FreeBSD (5.1) that does not have any local (rotating) storage (i.e. disk drives). PostgreSQL (7.3.2.1) also runs on this box and (at this point) has two tables. It is an extremely simple PostgreSQL configuration with the tables having less than 20 fields each, and no relations between the tables. However, because there isn't any (substantial) local storage available on the Compact Flash, and the tables have the potential to grow fairly large; Windows-based shares are being used (via PostgreSQL's 'initlocation') as the backing store for the tables. Moreover, setting the system up consisted of: 1. Setting and exporting PGDATA2=/var/nsg/dbNSG in ~/.profile 2. mount_smbfs [EMAIL PROTECTED]/share /var/nsg/dbNSG 3. initdb (default location (/usr/local/pgsql/data) (on the flash)) 4. start PostgreSQL 5. createuser -A -D nsg 6. initlocation -D PGDATA2 7. createdb -D PGDATA2 -O nsg nsg 8. (create tables) This all completes successfully, the problems begin while attempting to populate the tables. It seems that attempts to add specific records causes my C/libpq application to forever block on 'postgres'. My app is blocked on select(2) (via pg_exec('INSERT...')) and PostgreSQL is blocked on a semaphore. And for the record, the application and PostgreSQL perform flawlessly if step 2 (above) is skipped. In other words, there seems to be problem when SMB/CIFS is layered in. Does anyone have any information that might shed a little light? Anyone use SMB/CIFS as the backing store for PostgreSQL? Is there possible locking/deadlocking problems associated with using a SMB/CIFS supporting PostgreSQL. Thanks in advance! robo attachment: winmail.dat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is this possible? concatenating results from a subquery
Thanks Janko! I was hoping for a query-only solution (SQL only), but this will work just great. :-) If I switch database this code will give me some trouble. I guess I'll have to stick to Postgresql. ;-) Thanks. Regards, Erwin Moller Janko Richter wrote: Perhaps this helps: CREATE AGGREGATE concat ( BASETYPE = text, SFUNC= textcat, -- is function of operator 'text || text' STYPE= text, INITCOND = '' ); SELECT P.personid, P.name, concat( N.note ) AS allnotesbythisperson FROM tblperson AS P INNER JOIN tblnotes AS N ON N.personid=P.personid WHERE P.personid=34 GROUP BY P.personid, P.name; Regards, Janko Erwin Moller wrote: Hi! I face the following problem: 2 tables: tblperson and tblnotes tblperson: colums: personid (PK), name tblnotes: colums: noteid(PK), personid(references tblperson(personid)), note tblnotes has notes stored written by a person from tblperson identified (FK) by its personid. I make a select on one table with certain criteria and want to have a concatenation on a subquery results. Something like this: SELECT P.personid, P.name, concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) ) AS allnotesbythisperson FROM tblperson AS P WHERE (P.personid=34); The concat word I use is pure fantasy. Is this at all possible? I know I can easily circumvent te problem by my scriptinglanguage (PHP), but that will result in many extra queries. How do I proceed? TIA!! Regards, Erwin Moller ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] contrib vs. gborg/pgfoundry for replication solutions
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Tom Lane) wrote: Joshua D. Drake [EMAIL PROTECTED] writes: My personal opinion is that contrib should be removed entirely. That's not real workable for code that is tightly tied to the backend, such as the various GIST index extensions presently in contrib. It's just easier to maintain that code when it's in with the backend. However the replication modules don't seem to have such a linkage, so I have no objection to moving them out. I'll point out one fly in ointment that has been noticed; on AIX, there are compilation tools that are difficult to live without, namely mkldexport.sh, that lives pretty deep in the source tree. Maybe the answer is to replicate ;-) that into the code base for code that uses it. Alternatively, perhaps there needs to be a make-all-build-tools target in the main makefile. A challenge seems to be to have this play well with Linux and BSD package systems; building packages that can automatically go to sources (ala Ports or Source RPMs or auto-built .debs) for contrib software is sure to be somewhat painful; doing the same for outside code that also requires a PG source build is painful to think about. -- cbbrowne,@,ntlug.org http://www.ntlug.org/~cbbrowne/finances.html Rules of the Evil Overlord #209. I will not, under any circumstances, marry a woman I know to be a faithless, conniving, back-stabbing witch simply because I am absolutely desperate to perpetuate my family line. Of course, we can still date. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] License question
Hi Haim, Is the PostgreSQL license (http://www.postgresql.org/licence.html) LGPL compatible? I have adapted some code (for jdate manipulation) into the OLE DB project (LGPL). I have copied over the copyright notice, but now I'm thinking that this may not be enough. On the front page of the site (http://www.postgresql.org/) it says *Licence http://www.postgresql.org/licence.html* PostgreSQL is distributed under the flexible BSD licence. but the license is not the BSD license proper. It seems close, though not identical, to the X11 license (http://www.x.org/Downloads_terms.html), which the FSF define as GPL compatible (http://www.fsf.org/licenses/license-list.html). Yet, the X11 license requires an inclusion of a certain paragraph, which is not mandated by the LGPL, and which gives permissions not granted by the GPL. I'm confused. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PITR Phase 1 - Test results
I've now completed the coding of Phase 1 of PITR. This allows a backup to be recovered and then rolled forward (all the way) on transaction logs. This proves the code and the design works, but also validates a lot of the earlier assumptions that were the subject of much earlier debate. As noted in the previous designs, PostgreSQL talks to an external archiver using the XLogArchive API. I've now completed: - changes to PostgreSQL - written a simple archiving utility, pg_arch Using both of these together, I have successfully: - started pg_arch - started postgres - taken a backup using tar - ran pgbench for an extended period, so that the transaction logs taken at the start have long since been recycled - killed postmaster - wait for completion - rm -R $PGDATA - restore using tar - restore xlogs from archive directory - start postmaster and watch it recover to end of logs This has been tested through a number of times on non-trivial tests and I've sat and watch the beast at work to make sure nothing wierd was happening on timing. At this stage: Missing Functions - - recovery does NOT yet stop at a specified point-in-time (that was always planned for Phase 2) - few more log messages required to report progress - debug mode required to allow most to be turned off Wrinkles - code is system testable, but not as cute as it could be - input from committers is now sought to complete the work - you are strongly advised not to treat any of the patches as usable in any real world situation YET - that bit comes next Bugs - two bugs currently occur during some tests: 1. the notification mechanism as originally designed causes ALL backends to report that a log file has closed. That works most of the time, though does give rise to occaisional timing errors - nothing too serious, but this inexactness could lead to later errors. 2. After restore, the notification system doesn't recover fully - this is a straightforward one I'm building a full patchset for this code and will upload this soon. As you might expect over the time its taken me to develop this, some bitrot has set in, so I'm rebuilding it against the latest dev version now, and will complete fixes for the two bugs mentioned above. I'm sure some will say no words, show me the code... I thought you all would appreciate some advance warning of this, to plan time to investigate and comment upon the coding. Best Regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(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: [HACKERS] pl/j looking for alpha testers
[EMAIL PROTECTED] (Dave Cramer) writes: Pl/J is a java procedural language for postgres. We are looking for alpha testers to help us find bugs, and get feedback. The project can be found at http://plj.codehaus.org/ Bugs can be reported at http://jira.codehaus.org/secure/BrowseProject.jspa?id=10430 Shouldn't PL/J be an embedding of Ken Iverson's J into PG? :-) http://www.jsoftware.com/ (Many moons ago, I embedded READLINE into J, which made it vastly more usable on Unix-like systems...) [Oh, the languages I'd like as PL/whatever... PL/I could be an embedding of Icon; PL/J could be pretty neat; PL/CL... :-)] -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://cbbrowne.com/info/lisp.html Rules of the Evil Overlord #96. My door mechanisms will be designed so that blasting the control panel on the outside seals the door and blasting the control panel on the inside opens the door, not vice versa. http://www.eviloverlord.com/ ---(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: [HACKERS] pg_autovacuum crashes when query fails for temp
[EMAIL PROTECTED] (Matthew T. O'Connor) wrote: Bruce Momjian wrote: Should pg_autovacuum be vacuuming temporary tables? This is a good question, and I would like some opinions from some other people more informed than I. Secondly, why would a temporary table for another session be visible to pg_autovacuum? I know these may sound like stupid questions, but I'm a little shocked that it found a temp table. Did someone make a permanent table, delete it, and pg_autovacuum found it in between those operations? I will look into this when I have time, it certainly would be possible that pg_autovacuum could be tripped up by a sequence of events like you describe above. The more general problem is that it shouldn't crash even if it's vacuuming tables it shouldn't. Well, there's an entry in pg_class even for temporary tables; that means that even though a separate session has no ability to vacuum the table, there is still a way to get at its name. I would think that temp tables are TERRIBLE candidates for auto-vacuuming; they are likely to be created via INSERT or SELECT INTO, and if there is a need to analyze such a table, it is likely needful to do so under strict application control. -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://www3.sympatico.ca/cbbrowne/advocacy.html Rules of the Evil Overlord #196. I will hire an expert marksman to stand by the entrance to my fortress. His job will be to shoot anyone who rides up to challenge me. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: getting a crash during initdb
I have no idea what caused the pg_depend stuff to crash. The AV is in postgres.exe following the first SQL call in setup_depend(). The problem is not in initdb (it hasn't changed) but something in the backend. Changing the SQL statement made no difference: I'd venture a guess that postgres.exe crashes when *any* statement is sent to it. About 20 files have changed since my last initdb; I have a list. Forced initdb + initdb crash = nasty bug, even if it's just the cvs version (specific only to windows?). I'll check why the rmdir command is not working as expected. Merlin ---(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
[HACKERS] Is this possible? concatenating results from a subquery
Hi! I face the following problem: 2 tables: tblperson and tblnotes tblperson: colums: personid (PK), name tblnotes: colums: noteid(PK), personid(references tblperson(personid)), note tblnotes has notes stored written by a person from tblperson identified (FK) by its personid. I make a select on one table with certain criteria and want to have a concatenation on a subquery results. Something like this: SELECT P.personid, P.name, concat(SELECT N.note FROM tblnotes AS N WHERE (N.personid=P.personid) ) AS allnotesbythisperson FROM tblperson AS P WHERE (P.personid=34); The concat word I use is pure fantasy. Is this at all possible? I know I can easily circumvent te problem by my scriptinglanguage (PHP), but that will result in many extra queries. How do I proceed? TIA!! Regards, Erwin Moller ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Thread code not vpath-safe
The thread testing program is not nice to a vpath build. I see this error: checking for gmake... gmake checking thread safety of required library functions... Makefile:13: ../../../src/Makefile.global: No such file or directory gmake: *** No rule to make target `../../../src/Makefile.global'. Stop. configure: error: Can not clean thread test directory. (Basically what I'm doing is create an empty directory, cd to it and then call ../sourcedir/configure --enable-thread-safety) -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Por suerte hoy explotó el califont porque si no me habría muerto de aburrido (Papelucho) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Is there any method to keep table in memory at startup
Hi I am working on a project in postgres..in which i designed customized data type and operations on it.it requires a look up table.. I have three options regarding this table... 1. Every time a query is executed it creates table assigns values and after execution destroys it...which is overhead.. 2. store table on disk in database and access it whenever required but it degrades the performance 3. whenever psql starts it can load the table in memory from database which is efficient way to do but i don't know how to load table in memory when psql starts up ... please guide me.. thanks in advance regards vinay --- This mail is sent through IMP: http://horde.org/imp/ Used as the Webmail Interface at C-DAC, Mumbai: http://www.ncst.ernet.in ---(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: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
On Apr 23, 2004, at 8:35 AM, Christopher Kings-Lynne wrote: My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Questions I have are: I have already told Bruce at length about the single most common complaint in the phpPgAdmin lists and in the IRC channel: the inability to change column types. I think we should listen to the punters on that one. Also, how about a new section in the manual: PostgreSQL for MySQL users and PostgreSQL for Oracle users? Hello Bruce, Chris and everyone, So far I have offered free PHP5/ PostgreSQL hosting to around 800 developers that signed up on dotgeek.org I gathered a number of feedback. Overall many PHP developers are extremely impressed by PostgreSQL but they never had the chance/found a reason to try it. The issues are related mainly to the syntax. Here MySQL, by using non standards systems, is making the move not that easy to many developers. Marketing is an important point, so is being able to let the highest number of people to try PostgreSQL and see the difference. Another problem is, as far as I can say, their easier to search and more user friendly manual. I know that Alexey is working on that so I will think about a way to contribute directly. Users (and monitored) comments are a must IMHO. Cheers David Costa Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib vs. gborg/pgfoundry for replication solutions
The specific details aren't especially relevant to this thread, though. What is relevant is that we agree to a commitment that we will make it easy to build modules outside the current Postgres build environment, and that we will have an ongoing commitment to make sure that that keeps working. Maybe you try to mimic apache apxs script behavior? It allows to compile, install, configure new modules into apache. pg_config is not convincing at the time: shell pg_config --includedir-server /usr/local/pg750a/include/postgresql/server shell ls /usr/local/pg750a/include/postgresql/server ls: /usr/local/pg750a/include/postgresql/server: No such file or directory Too bad, server headers are not installed by default:-( -- Fabien. ---(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: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Bruce Momjian wrote: Peter Eisentraut wrote: Rob wrote: But I think there is room to go further, I don't see any reason why that default install can't include example DBs, One reason is that a useful example database would likely have a download footprint of 10 MB or more. Having this in the default download would not be appreciated by many people. Of course having some example database available at all would be a good idea, but then as a separate download. Here is a little psql script I wrote to populate a table with random data. [snip] Right, I have done the same in the past using random character data (it even had random lengths of strings in the different fields) and in other cases random dictionary words. I was thinking something with more structure, like an customer/product/invoice db with random records that link up to each other properly. I will work on something but am wondering if there are any freely available schemas around (for any system, I know Sybase has a book publishing one that they use in their example queries and is provided with their install, pubs2 I believe) that might be good for use in a more extended sample db. Are there any platforms (outside of MS Windows) that don't include a word list or dictionary these days? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Bruno Wolff III wrote: On Fri, Apr 23, 2004 at 16:36:57 -0400, [EMAIL PROTECTED] wrote: Ease of use is VERY important, but few suggestions that address this are ever really accepted. Yes, focusing on the functionality is the primary concern, but how you set it up and deploy it is VERY important. You guys need to remember, people are coming from a world where MySQL, Oracle, and MSSQL all have nice setup programs. nice must be in the eye of the beholder. I have used Oracle's installer to install a client and was not amused by it need hundreds of megabtyes to do a client install. I have to agree, I've installed DB2, Sybase, Oracle, Informix, BerkeleyDB, mySQL, postgreSQL and others. IIRC, I believe postgreSQL was the shortest from download to running system (when compiling the OS ones from scratch) and seemed to do the most thorough testing of itself. Oracle doesn't seem to give you the option to not install the hundreds of megs of documentation on the Nth machine where you just needed the damn client lib - less of an issue now than in the smaller disk/partition days. But I think there is room to go further, I don't see any reason why that default install can't include example DBs, sample maintenance scripts, etc. One nice thing to have would be a sample DB with the scripts necessary to spin up a test/demo DB with a size of X megs. Whenever I started with a new DB system, I wished I didn't have to ramp up on a bunch of topics before I was able to build a set of scripts to generate and populate a sizable testing db. There is a big psychological factor if you can install something, type one command and have a db with 250,000 records to start playing with. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Bruno Wolff III wrote: On Fri, Apr 23, 2004 at 16:36:57 -0400, [EMAIL PROTECTED] wrote: Ease of use is VERY important, but few suggestions that address this are ever really accepted. Yes, focusing on the functionality is the primary concern, but how you set it up and deploy it is VERY important. You guys need to remember, people are coming from a world where MySQL, Oracle, and MSSQL all have nice setup programs. nice must be in the eye of the beholder. I have used Oracle's installer to install a client and was not amused by it need hundreds of megabtyes to do a client install. I second that. I have not found *anybody* who has used Oracle's installer to install the actual database server on Linux or Solaris who has described their installation proceedure as either nice or easy. In fact even reading the installation isntructions is enough to give you second thoughts MS SQL does have a nice installer, however, as do most binary open source products for Windows. I am completely confident that PostgreSQL for Windows, when it arrives, will have a nice GUI-based installer. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted: Is anyone really ready for this sort of commitment? By that, I presume you mean... Are people prepared to stop working on the doubtless useful things that they are working on in favor of spending their time instead on this set of marketing-oriented systems integration tasks? I think the answer to that is likely a pretty clear NO. PostgreSQL runs on numerous systems, and in order to do what you are proposing, it would be necessary to play system favorites. - Configuration systems are inherently platform-specific. - PL/Java is well and interesting, but requires a barrel of non-free software, which makes installation and configuration anything but slick. There is _not_ going to be a Product Management group any time soon that will turn around PostgreSQL into being a marketing-driven project. I suggest that you instead find projects that are worth doing in their own right without having to make some mandate for a Grand Political Restructuring. Doing individual things that are worthwhile is, obviously, worthwhile. If some point some people in directions that, after some number of such projects, resemble some Grand Political Restructuring, then you'll have succeeded at the latter. Much as you can't push on a rope (unless it's wet and frozen :-)), and can't herd cats (although EDS did a SuperBowl commercial claiming they could), you can't push for a Grand Political Restructuring. The best you can do is lure people in your directions, one cat at a time... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://cbbrowne.com/info/spreadsheets.html The theoreticians have proven that this is unsolvable, but there's three of us, and we're smart... -- Arthur Norman ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
On Sun, Apr 25, 2004 at 05:15:19PM -0400, [EMAIL PROTECTED] wrote: (5) Programming languages. We need to make a programming language standard in PostgreSQL. plpgsql is good, but isn't someone working on a Java language. That would be pretty slick. If there's going to be a single standard language, I strongly believe it should be plpgsql. Any other language means that you have to find something that someone else knows or is willing to learn, whereas anyone using a database already knows SQL. plpgsql is simply an extension of SQL, and is trivial for anyone who's worked with any other database procedural languages to pickup. Asking a DBA to learn java or perl or PHP is asking a lot. If anything I'd like to see more features brought into plpgsql, like packages (ala Oracle). Sorry, by standard, I meant installed by default, not to the exclusion of all else. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
When all is said and done, I think the PostgreSQL project lacks a Product Management group which steers the public perception and defines usability. This is something *all* other systems have, including MySQL. Well, NO, not a chance. As one of the de-facto heads of our Advocacy group, let me say you've got to be kidding. I don't think so, let me explain below. MySQL is a private closed-shop software manufacturer who uses the GPL as one of their methods of distribution. They are not a real open source project -- they are a private, commercial, for-profit software company. I agree, I don't like MySQL all that much. We are not. We are a open, 100% voluntary community of contributing developers and support volunteers. Nobody is going to take orders from a Product Manager, that's a position for paid software products departments. It would be horribly inappropriate for PostgreSQL, and would destroy everything that has made us successful to date. You can't give orders to volunteers. It depends on the volunteers. Some are useless at taking orders, this is true, others, however, are very welcoming to direction. It depends in the individual. Lastly, Bruce, Tom, Peter, and others are very didicated to PostgreSQL. If a real case can be made for a feature, I'm sure they are reasonable enough to see that and grudgingly implement it. Someone, however, has to keep an eye on that ball. You're statement that all other systems have a Product Manager is also wildly inaccurate. I think you're thinking of MySQL and Mozilla only. Heck, even OpenOffice.org doesn't have a Product Manager, and that is a Sun-sponsored project. Let alone, say, emacs. Or Linux. Linux has Linus, he has a very good eye in the market forces. Emacs, is a non-entity outside UNIX hard core UNIX guys. OpenOffice is very much managed by Sun. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
FW: [HACKERS] Do we prefer software that works or software that looks good?
Tom Lane wrote: Personally I don't think that this is a transitional issue and we will someday all be happy in upper-case-only-land. Upper-case-only sucks, by every known measure of readability, and I don't want to have to put up with a database that forces that 1960s-vintage-hardware mindset on me. I think the SQL standard is screwy here on at least two levels. Not only is upper case fuggly (we all seem to agree on that point), but I think case folding is a Bad Idea in general. I think the only time you should have to quote a DB identifier is when it conflicts with a reserved word. Either be case sensative or don't. I'm all for the (ignore but preserve case) way of doing things. But it IS the standard, and as such, as much as we all seem to dislike it, I believe it is better to follow it. You can't just go around picking and choosing the standards you'll adhere to. Like Microsoft. If it bothers you that much, put some effort into changing it. Attain world domination and then force the world to bend to The Right Way. Get rich and pay off enough members of the standards body to get it changed. But until then, live with it. Now, I am all for configurability, and lots of it. By all means, allow us to choose how we'd like case folding to be carried out, or whether case folding (blech) is done at all. While you're at it, allow us to choose whether NULL is treated as zero/blank/empty or as SQL standard NULL. Allow us to force the DB to do case-insensative comparisons on all character data. Allow us, as DB admins, to f*** with the standard behavior until we have a working mimic of MySQL or MS-SQL :-) But I think the default behavior should adhere to the SQL standard as closely as possible, even when we all hate it with a passion. Just my $.02 Glen Parker ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] FW: getting a crash during initdb
Merlin Moncure wrote: I'll check why the rmdir command is not working as expected. I just poked around and couldn't figure out the cause. Initdb should either remove the directory if it created it, or remove everything _in_ the directory if the directory already existed. I tried the rmdir/del /s /q commands in a CMD window and it worked fine. Problem is backslash issue :) initdb is issuing del /s /q c:/postgres/data. Is there a library command to properly resolve slashes? Uh, I just testd this using CMD and it worked: rmdir /s /q C:/TMP/TMP The quotes are required, but are in the code: snprintf(buf, sizeof(buf), %s /s /q \%s\, rmtopdir ? rmdir : del, path); However, I have no problem with pulling his rmdir C code back out of CVS and implementing it. I think it might come in as part of tablespaces anyway so I was just waiting to see how things shook out. If we do it, it would go into /port along with copdir/dp for directories. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] transmitted: Is anyone really ready for this sort of commitment? By that, I presume you mean... Are people prepared to stop working on the doubtless useful things that they are working on in favor of spending their time instead on this set of marketing-oriented systems integration tasks? I think the answer to that is likely a pretty clear NO. That's sort of the question, isn't it? Bruce asked what can we learn from MySQL. Well, I've put forth an argument, right or wrong, its my opinion. If there is a concensus that the PostgreSQL team wants to increase user numbers, it is obvious, as evidenced by MySQL, it isn't only about features. The marketing-oriented systems integration tasks are arguably *more* important than minor improvements to attain this goal. PostgreSQL runs on numerous systems, and in order to do what you are proposing, it would be necessary to play system favorites. - Configuration systems are inherently platform-specific. Not all that much, java setup programs or a Samba SWAT like program could go a LONG way to improving configurability without suffering platform snobbery. - PL/Java is well and interesting, but requires a barrel of non-free software, which makes installation and configuration anything but slick. There is _not_ going to be a Product Management group any time soon that will turn around PostgreSQL into being a marketing-driven project. No one is saying Market-driven project, but you must admit, some market savvy would be helpful if you want to get more users. [snip] You know, there were some questions put out to the group wondering how to get more users. You may disagree with my assessment, that's fine, I don't want to have any hard feelings, but I think your tone speaks volumes about why PostgreSQL regularly gets ignored. While it is better than MySQL in almost every metric, it has fewer users than it should. The question is why. I took a bit of time and effort to explain why I think this is so, outline a number of steps that may help. If getting more users is something that we want to do. Then we need to think about what it is we need to do. We need to understand that we need to do things we may not feel like doing for success. I may be completely wrong in every sentence I wrote, but lets discuss why I may be wrong, lets discuss what is the right way to do it, or lastly, lets decide that it isn't important to get more users, but don't just say you can't. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Shachar, I've been giving this some more thought. Here are my contributions: 1. Setting should be on a per-database level. A per-server option is not good enough, and a per-session option is too difficult to implement, with no apparent justifiable return. I disagree with this. I think doing case-folding per database would be preposterously difficult, and that per-server is adequate. Per database settings bring up a whole raft of logical conflicts, particularly around the system catalogs and dblink, that aren't necessarily worth navigating. I also didn't follow the discussion of why a client-side implementation was technically impossible; this seems like the most obvious course to me, and to have *considerable* benefit.It's also consistent with our other statement variables, such as datestyle, which are all client-side, per-session settings. A server-side implementation would possibly reqire touching every single source code file in Postgres, something that would justify a lot of effort to avoid. 2. Old applications already working with PG's lowercase folding should have an option to continue working unmodified for the foreseeable future. Si. 1. Tri-state. Folder upper, if failes, fold lower, if succeeds, warn. Can't see this being possible. 2. Dual state. Fold lower or upper. Break if client is broken. Best, I think. But it should be client-side. 3. Create a database conversion tool to change existing case. No thanks. -- Josh Berkus Aglio Database Solutions San Francisco ---(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: [HACKERS] FW: getting a crash during initdb
I'll check why the rmdir command is not working as expected. I just poked around and couldn't figure out the cause. Initdb should either remove the directory if it created it, or remove everything _in_ the directory if the directory already existed. I tried the rmdir/del /s /q commands in a CMD window and it worked fine. Problem is backslash issue :) initdb is issuing del /s /q c:/postgres/data. Is there a library command to properly resolve slashes? Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
On Sun, Apr 25, 2004 at 05:15:19PM -0400, [EMAIL PROTECTED] wrote: (5) Programming languages. We need to make a programming language standard in PostgreSQL. plpgsql is good, but isn't someone working on a Java language. That would be pretty slick. If there's going to be a single standard language, I strongly believe it should be plpgsql. Any other language means that you have to find something that someone else knows or is willing to learn, whereas anyone using a database already knows SQL. plpgsql is simply an extension of SQL, and is trivial for anyone who's worked with any other database procedural languages to pickup. Asking a DBA to learn java or perl or PHP is asking a lot. If anything I'd like to see more features brought into plpgsql, like packages (ala Oracle). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib vs. gborg/pgfoundry for replication solutions
On Wed, Apr 21, 2004 at 10:25:29PM -0400, Christopher Browne wrote: I'll point out one fly in ointment that has been noticed; on AIX, there are compilation tools that are difficult to live without, namely mkldexport.sh, that lives pretty deep in the source tree. That's just a problem to do with autoconf. All we gots to do is find an autoconf genius. I don't think it entails that we need a complete built source tree for everything. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Thread code not vpath-safe
On Mon, Apr 26, 2004 at 01:15:13PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: The thread testing program is not nice to a vpath build. I see this error: (Basically what I'm doing is create an empty directory, cd to it and then call ../sourcedir/configure --enable-thread-safety) I tried and couldn't get it to fail. Whoa, seems I'm the irreproducible-bug-reporter now :-( Do you have this line at the bottom of configure.in: if ! $srcdir/src/tools/thread/thread_test 5 Yes: if ! $srcdir/src/tools/thread/thread_test 5 thenrm -f $srcdir/src/Makefile.global echo no echo $srcdir/src/tools/thread/thread_test echo AC_MSG_ERROR([Thread test program failed. Your platform is not thread-safe.]) fi I tried it with: mkdir /tmp/thr cd /tmp/thr ~/CVS/pgsql/source/00orig/configure --enable-thread-safety [lots of output, and then:] checking for gmake... gmake checking thread safety of required library functions... Makefile:13: ../../../src/Makefile.global: No such file or directory gmake: *** No rule to make target `../../../src/Makefile.global'. Stop. configure: error: Can not clean thread test directory. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No necesitamos banderas No reconocemos fronteras (Jorge González) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] FW: getting a crash during initdb
Merlin Moncure wrote: I have no idea what caused the pg_depend stuff to crash. The AV is in postgres.exe following the first SQL call in setup_depend(). The problem is not in initdb (it hasn't changed) but something in the backend. Changing the SQL statement made no difference: I'd venture a guess that postgres.exe crashes when *any* statement is sent to it. About 20 files have changed since my last initdb; I have a list. Forced initdb + initdb crash = nasty bug, even if it's just the cvs version (specific only to windows?). I'll check why the rmdir command is not working as expected. I just poked around and couldn't figure out the cause. Initdb should either remove the directory if it created it, or remove everything _in_ the directory if the directory already existed. I tried the rmdir/del /s /q commands in a CMD window and it worked fine. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] FW: getting a crash during initdb
Alvaro Herrera wrote: The AV is in postgres.exe following the first SQL call in setup_depend(). The problem is not in initdb (it hasn't changed) but something in the backend. Changing the SQL statement made no difference: I'd venture a guess that postgres.exe crashes when *any* statement is sent to it. About 20 files have changed since my last initdb; I have a list. Forced initdb + initdb crash = nasty bug, even if it's just the cvs version (specific only to windows?). I've initdb'd more than twenty times with CVS tip code, so it would seem to be Windows-specific. Thought as much. I posted to the win32 list but it hasn't shown up yet. Are you sure you're using a clean build and a really current checkout? Yes, 100%. Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] What can we learn from MySQL?
I'm certain you guys could do a far better installer than the one Oracle has, which is very, very fragile. There's all kinds of wonkiness to try and get it to work on a non-supported linux distro (gentoo in my case), and from talking to people who've dealt with it on redhat it's no better. Also, if possible, I think an installer that plays nice with package management systems would be important. Many users want to use their OS's package system to handle install and upgrade rather than some other installer. On Sat, Apr 24, 2004 at 12:10:01PM -0500, Bruno Wolff III wrote: On Fri, Apr 23, 2004 at 16:36:57 -0400, [EMAIL PROTECTED] wrote: Ease of use is VERY important, but few suggestions that address this are ever really accepted. Yes, focusing on the functionality is the primary concern, but how you set it up and deploy it is VERY important. You guys need to remember, people are coming from a world where MySQL, Oracle, and MSSQL all have nice setup programs. nice must be in the eye of the beholder. I have used Oracle's installer to install a client and was not amused by it need hundreds of megabtyes to do a client install. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-advocacy] Do we prefer software that works or software
Josh Berkus wrote: Shachar, Now, I'm intending to do the best I can on my end. This does have a pretty heavy cost. It means that the OLE DB driver will parse in details each query, and perform replacements on the query text. This is bug prone, difficult, hurts performance, and just plain wrong from a software design perspective. The current drift of wind, however, means that the PostgreSQL steering commite seems to prefer having a lesser quality driver to seeing ugly uppercase. Hey, now wait a minute. As far as I can tell, you've heard only from Tom Lane on the steering committee (I may have missed some, though, I've been sick) Exactly. Of the people I heard from, the wind was against. Unless the 5 of us take a vote, Tom Lane speaks for Tom Lane, not for Core.Also, usually this list or Patches determines by consensus what gets in; the Core only gets involved in very unusual cases. That's why we are holding an open thread on the how in hackers. I'm assuming that once the how is sufficiently resolved, and the implications understood, everyone can make a better decision on the do we at all. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [pgsql-advocacy] Do we prefer software that works or software that looks good?
Shachar, Now, I'm intending to do the best I can on my end. This does have a pretty heavy cost. It means that the OLE DB driver will parse in details each query, and perform replacements on the query text. This is bug prone, difficult, hurts performance, and just plain wrong from a software design perspective. The current drift of wind, however, means that the PostgreSQL steering commite seems to prefer having a lesser quality driver to seeing ugly uppercase. Hey, now wait a minute. As far as I can tell, you've heard only from Tom Lane on the steering committee (I may have missed some, though, I've been sick) Unless the 5 of us take a vote, Tom Lane speaks for Tom Lane, not for Core.Also, usually this list or Patches determines by consensus what gets in; the Core only gets involved in very unusual cases. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thread code not vpath-safe
Alvaro Herrera wrote: The thread testing program is not nice to a vpath build. I see this error: checking for gmake... gmake checking thread safety of required library functions... Makefile:13: ../../../src/Makefile.global: No such file or directory gmake: *** No rule to make target `../../../src/Makefile.global'. Stop. configure: error: Can not clean thread test directory. (Basically what I'm doing is create an empty directory, cd to it and then call ../sourcedir/configure --enable-thread-safety) I tried and couldn't get it to fail. Do you have this line at the bottom of configure.in: if ! $srcdir/src/tools/thread/thread_test 5 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
When all is said and done, I think the PostgreSQL project lacks a Product Management group which steers the public perception and defines usability. This is something *all* other systems have, including MySQL. Well, NO, not a chance. As one of the de-facto heads of our Advocacy group, let me say you've got to be kidding. MySQL is a private closed-shop software manufacturer who uses the GPL as one of their methods of distribution. They are not a real open source project -- they are a private, commercial, for-profit software company. We are not. We are a open, 100% voluntary community of contributing developers and support volunteers. Nobody is going to take orders from a Product Manager, that's a position for paid software products departments. It would be horribly inappropriate for PostgreSQL, and would destroy everything that has made us successful to date. You can't give orders to volunteers. You're statement that all other systems have a Product Manager is also wildly inaccurate. I think you're thinking of MySQL and Mozilla only. Heck, even OpenOffice.org doesn't have a Product Manager, and that is a Sun-sponsored project. Let alone, say, emacs. Or Linux. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FW: getting a crash during initdb
On Mon, Apr 26, 2004 at 11:37:37AM -0400, Merlin Moncure wrote: I have no idea what caused the pg_depend stuff to crash. The AV is in postgres.exe following the first SQL call in setup_depend(). The problem is not in initdb (it hasn't changed) but something in the backend. Changing the SQL statement made no difference: I'd venture a guess that postgres.exe crashes when *any* statement is sent to it. About 20 files have changed since my last initdb; I have a list. Forced initdb + initdb crash = nasty bug, even if it's just the cvs version (specific only to windows?). I've initdb'd more than twenty times with CVS tip code, so it would seem to be Windows-specific. Are you sure you're using a clean build and a really current checkout? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El miedo atento y previsor es la madre de la seguridad (E. Burke) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 1 - Test results
Simon Riggs wrote: Well, I guess I was fairly happy too :-) YES! I'd be more comfortable if I'd found more bugs though, but I'm sure the kind folk on this list will see that wish of mine comes true! The code is in a needs more polishing state - which is just the right time for some last discussions before everything sets too solid. Once we see the patch, we will be able to eyeball all the code paths and interface to existing code and will be able to spot a lot of stuff, I am sure. It might take a few passes over it but you will get all the support and ideas we have. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] PITR Phase 2 - Design Planning
Since Phase1 is functioning and should hopefully soon complete, we can now start thinking about Phase 2: full recovery to a point-in-time. Previous thinking was that a command line switch would be used to specify recover to a given point in time, rather than the default, which will be recover all the way to end of (available) xlogs. Recovering to a specific point in time forces us to consider what the granularity is of time. We could recover: 1.to end of a full transaction log file 2.to end of a full transaction Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. To make (2) work we would have to have a timestamp associated with each transaction. This could be in one of two places: 1. the transaction record in the clog 2. the log record in the xlog We would then recover the xlog record by record, until we found a record that had a timestamp desired point-in-time. Currently, neither of these places have a timestamp. H. We can't use pg_control because we are assuming that it needs recovery... I can't see any general way of adding a timestamp in any less than 2 bytes. We don't need a timezone. The timestamp could refer to a number of seconds since last checkpoint; since this is limited already by a GUC to force checkpoints every so often. Although code avoids a checkpoint if no updates have taken place, we wouldn't be too remiss to use a forced checkpoint every 32,000 seconds (9 hours). Assuming that accuracy of the point-in-time was of the order of seconds?? If we went to 0.1 second accuracy, we could checkpoint (force) every 40 minutes or so. All of that seems too restrictive. If we went to milliseconds, then we could use a 4 byte value and use a checkpoint (force) every 284 hours or 1.5 weeks. Thoughts? Clog uses 2 bits per transaction, so even 2 bytes extra per transaction will make the clog 9 times larger than originally intended. This could well cause it to segment quicker, but I'm sure no one would be happy with that. So, lets not add anything to the clog. The alternative is to make the last part of the XlogHeader record a timestamp value, increasing each xlog write. It might be possible to make this part of the header optional depending upon whether or not PITR was required, but then my preference is against such dynamic coding. So, I propose: - appending 8 byte date/time data into xlog file header record - appending 4 bytes of time offset onto each xlog record - altering the recovery logic to compare the calculated time of each xlog record (file header + offset) against the desired point-in-time, delivered to it by GUC. Input is sought from anybody with detailed NTP knowledge, since the working of NTP drift correction may have some subtle interplay with this proposal. Also, while that code is being altered, some additional log records need to be added when recovery of each new xlog starts, with timing, to allow DBAs watching a recovery to calculate expected completion times for the recovery, which is essential for long recovery situations. I am also considering any changes that may be required to prepare the way for a future implementation of parallel redo recovery. Best regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Phase 1 - Test results
I want to come hug you --- where do you live? !!! :-) --- Simon Riggs wrote: I've now completed the coding of Phase 1 of PITR. This allows a backup to be recovered and then rolled forward (all the way) on transaction logs. This proves the code and the design works, but also validates a lot of the earlier assumptions that were the subject of much earlier debate. As noted in the previous designs, PostgreSQL talks to an external archiver using the XLogArchive API. I've now completed: - changes to PostgreSQL - written a simple archiving utility, pg_arch Using both of these together, I have successfully: - started pg_arch - started postgres - taken a backup using tar - ran pgbench for an extended period, so that the transaction logs taken at the start have long since been recycled - killed postmaster - wait for completion - rm -R $PGDATA - restore using tar - restore xlogs from archive directory - start postmaster and watch it recover to end of logs This has been tested through a number of times on non-trivial tests and I've sat and watch the beast at work to make sure nothing wierd was happening on timing. At this stage: Missing Functions - - recovery does NOT yet stop at a specified point-in-time (that was always planned for Phase 2) - few more log messages required to report progress - debug mode required to allow most to be turned off Wrinkles - code is system testable, but not as cute as it could be - input from committers is now sought to complete the work - you are strongly advised not to treat any of the patches as usable in any real world situation YET - that bit comes next Bugs - two bugs currently occur during some tests: 1. the notification mechanism as originally designed causes ALL backends to report that a log file has closed. That works most of the time, though does give rise to occaisional timing errors - nothing too serious, but this inexactness could lead to later errors. 2. After restore, the notification system doesn't recover fully - this is a straightforward one I'm building a full patchset for this code and will upload this soon. As you might expect over the time its taken me to develop this, some bitrot has set in, so I'm rebuilding it against the latest dev version now, and will complete fixes for the two bugs mentioned above. I'm sure some will say no words, show me the code... I thought you all would appreciate some advance warning of this, to plan time to investigate and comment upon the coding. Best Regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Phase 1 - Test results
Well, I guess I was fairly happy too :-) I'd be more comfortable if I'd found more bugs though, but I'm sure the kind folk on this list will see that wish of mine comes true! The code is in a needs more polishing state - which is just the right time for some last discussions before everything sets too solid. Regards, Simon On Mon, 2004-04-26 at 17:48, Bruce Momjian wrote: I want to come hug you --- where do you live? !!! :-) --- Simon Riggs wrote: I've now completed the coding of Phase 1 of PITR. This allows a backup to be recovered and then rolled forward (all the way) on transaction logs. This proves the code and the design works, but also validates a lot of the earlier assumptions that were the subject of much earlier debate. As noted in the previous designs, PostgreSQL talks to an external archiver using the XLogArchive API. I've now completed: - changes to PostgreSQL - written a simple archiving utility, pg_arch Using both of these together, I have successfully: - started pg_arch - started postgres - taken a backup using tar - ran pgbench for an extended period, so that the transaction logs taken at the start have long since been recycled - killed postmaster - wait for completion - rm -R $PGDATA - restore using tar - restore xlogs from archive directory - start postmaster and watch it recover to end of logs This has been tested through a number of times on non-trivial tests and I've sat and watch the beast at work to make sure nothing wierd was happening on timing. At this stage: Missing Functions - - recovery does NOT yet stop at a specified point-in-time (that was always planned for Phase 2) - few more log messages required to report progress - debug mode required to allow most to be turned off Wrinkles - code is system testable, but not as cute as it could be - input from committers is now sought to complete the work - you are strongly advised not to treat any of the patches as usable in any real world situation YET - that bit comes next Bugs - two bugs currently occur during some tests: 1. the notification mechanism as originally designed causes ALL backends to report that a log file has closed. That works most of the time, though does give rise to occaisional timing errors - nothing too serious, but this inexactness could lead to later errors. 2. After restore, the notification system doesn't recover fully - this is a straightforward one I'm building a full patchset for this code and will upload this soon. As you might expect over the time its taken me to develop this, some bitrot has set in, so I'm rebuilding it against the latest dev version now, and will complete fixes for the two bugs mentioned above. I'm sure some will say no words, show me the code... I thought you all would appreciate some advance warning of this, to plan time to investigate and comment upon the coding. Best Regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pl/j looking for alpha testers
The reason we are using pl/j instead of pl/java is that the word java is protected by sun. We chose J, who knew there was a programming language called J :( --dc-- On Tue, 2004-04-20 at 15:19, Chris Browne wrote: [EMAIL PROTECTED] (Dave Cramer) writes: Pl/J is a java procedural language for postgres. We are looking for alpha testers to help us find bugs, and get feedback. The project can be found at http://plj.codehaus.org/ Bugs can be reported at http://jira.codehaus.org/secure/BrowseProject.jspa?id=10430 Shouldn't PL/J be an embedding of Ken Iverson's J into PG? :-) http://www.jsoftware.com/ (Many moons ago, I embedded READLINE into J, which made it vastly more usable on Unix-like systems...) [Oh, the languages I'd like as PL/whatever... PL/I could be an embedding of Icon; PL/J could be pretty neat; PL/CL... :-)] -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(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: [HACKERS] btbulkdelete
On Mon, 26 Apr 2004 14:29:58 +0100, Simon Riggs [EMAIL PROTECTED] wrote: Now that FSM covers free btree index pages this access pattern might be highly nonsequential. I had considered implementing a mode where the index doesn't keep trying to reuse space that was freed by earlier deletes. Or maybe an FSM function a la Give me a free page near this one? Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Josh Berkus wrote: I also didn't follow the discussion of why a client-side implementation was technically impossible; this seems like the most obvious course to me, and to have *considerable* benefit.It's also consistent with our other statement variables, such as datestyle, which are all client-side, per-session settings. But they are not client side, are they? The date is formatted by the server. The client is simply receiving whatever datestyle itselected. That is, assuming I understand the behaviour correctly. I would catagorize it as a server side per-session configuration. If that's what you mean, we thought it was too insane because it doesn't explain how you are supposed to handle all the catalog and other stuff where you are implictly assuming quoting is not necessary. I'l reiterate the example. Do you really expect to have to write select upper(field) from table and should it be upper or UPPER? A server-side implementation would possibly reqire touching every single source code file in Postgres, something that would justify a lot of effort to avoid. I think the concensus was that the runtime part was aprox. four lines where the case folding currently takes place. Obviously, you would have to get a var, and propogate that var to that place, but not actually change program flow. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. To make (2) work we would have to have a timestamp associated with each transaction. This could be in one of two places: 1. the transaction record in the clog 2. the log record in the xlog We would then recover the xlog record by record, until we found a record that had a timestamp desired point-in-time. Currently, neither of these places have a timestamp. H. We can't use pg_control because we are assuming that it needs recovery... I can't see any general way of adding a timestamp in any less than 2 bytes. We don't need a timezone. The timestamp could refer to a number of seconds since last checkpoint; since this is limited already by a GUC to force checkpoints every so often. Although code avoids a checkpoint if no updates have taken place, we wouldn't be too remiss to use a forced checkpoint every 32,000 seconds (9 hours). Assuming that accuracy of the point-in-time was of the order of seconds?? If we went to 0.1 second accuracy, we could checkpoint (force) every 40 minutes or so. All of that seems too restrictive. If we went to milliseconds, then we could use a 4 byte value and use a checkpoint (force) every 284 hours or 1.5 weeks. Thoughts? I was thinking --- how would someone know the time to use for restore? Certainly they will not know subsecond accuracy? Probably second-level accuracty is enough, _except_ when they want everything restored up to a DROP TABLE transaction or some major problem. Is there a way to give users a list of transactions on a log backup? Can we show them the username, database, or commands or something? Would they be able to restore up to a specific transaction in that case? Basically, we could give them sub-second recovery, but what value would it be? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Shachar, I think the concensus was that the runtime part was aprox. four lines where the case folding currently takes place. Obviously, you would have to get a var, and propogate that var to that place, but not actually change program flow. That's only if you ignore the system catalogs entirely, which maybe you're prepared to do. If you want to change case folding for the system catalogs, though, you'll need to update code in thousands of places, becuase the back-end code is expecting lower-case identifiers -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 1 - Test results
I want to come hug you --- where do you live? !!! You're not the only one. But we don't want to smother the poor guy, at least not before he completes his work :-) ---(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: [HACKERS] Bringing PostgreSQL torwards the standard regarding case folding
Josh Berkus wrote: Shachar, I think the concensus was that the runtime part was aprox. four lines where the case folding currently takes place. Obviously, you would have to get a var, and propogate that var to that place, but not actually change program flow. That's only if you ignore the system catalogs entirely, which maybe you're prepared to do. If you want to change case folding for the system catalogs, though, you'll need to update code in thousands of places, becuase the back-end code is expecting lower-case identifiers IF you want per session setting, yes. If you want per database setting, you only need to worry about the shared catalogs If you want server wide setting, you just create the catalogs with the correct name, and get it over with. That's why I said that per-session setting seems like too much trouble. -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] PITR Phase 1 - Code Overview (1)
On Mon, 2004-04-26 at 16:37, Simon Riggs wrote: I've now completed the coding of Phase 1 of PITR. This allows a backup to be recovered and then rolled forward (all the way) on transaction logs. This proves the code and the design works, but also validates a lot of the earlier assumptions that were the subject of much earlier debate. As noted in the previous designs, PostgreSQL talks to an external archiver using the XLogArchive API. I've now completed: - changes to PostgreSQL - written a simple archiving utility, pg_arch This will be on HACKERS not PATCHES for a while... OVERVIEW : Various code changes. Not all included here...but I want to prove this is real, rather than have you waiting for my patch release skills to improve. PostgreSQL changes include: - guc.c New GUC called wal_archive to control archival logging/not. - xlog.h GUC added here - xlog.c The most critical parts of the code live here. The way things currently work can be thought of as a circular set of logs, with the current log position sweeping around the circle like a clock. In order to archive an xlog, you must start just AFTER the file has been closed and BEFORE the pointer sweeps round again. The code here tries to spot the right moment to notify the archive that its time to archive. That point is critical, too early and the archive may yet be incomplete, too late and a window of failure creeps into the system. Finding that point is more complicated than it seems because every backend has the same file open and decides to close it at different times - nearly the same time if you're running pgbench, but could vary considerably otherwise. That timing difference is the source of Bug#1. My solution is to use the piece of code that first updates pg_control, since there is a similar need to only-do-it-once. My understanding is that the other backends eventually discover they are supposed to be looking at a different file now and reset themselves - so that the xlog gets fsynced only once. It's taken me a week to consider the alternatives...this point is critical, so please suggest if you know/think differently. When the pointer sweeps round again, if we are still archiving, we simply increase the number of logs in the cycle to defer when we can recycle the xlog. The code doesn't yet handle a failure condition we discussed previously: running out of disk space and how we handle that (there was detailed debate, noted for future implementation). New utility aimed at being located in src/bin/pg_arch === - pg_arch.c The idea of pg_arch is that it is a functioning archival tool and at the same time is the reference implementation of the XLogArchive API. The API is all wrapped up in the same file currently, to make it easier to implement, but I envisage separating these out into two parts after it passes initial inspection - shouldn't take too much work given that was its design goal. This will then allow the API to be used for wider applications that want to backup PostgreSQL. - src/bin/Makefile has been updated to include pg_arch, so that this then gets made as part of the full system rather than an add-on. I'm sure somebody has feelings on this...my thinking was that it ought to be available without too much effort. What's NOT included (YET!) == -changes to initdb -changes to postgresql.conf -changes to wal_debug -related changes -user documentation - changes to initdb XLogArchive API implementation relies on the existence of $PGDATA/pg_rlog That would be relatively simple to add to initdb, but its also a no brainer to add without it, so I thought I'd leave it for discussion in case anybody has good reasons to put elsewhere/rename it etc. More importantly, this effects the security model used by XLogArchive. The way I had originally envisaged this, the directory permissions would be opened up for group level read/write thus: pg_xlog rwxr-x--- pg_rlog rwxrwx--- though this of course relies on $PGDATA being opened up also. That then would allow the archiving tool to be in its own account also, yet with a shared group. (Thinking that a standard Legato install (for instance) is unlikely to recommend sharing a UNIX userid with PostgreSQL). I was unaware that PostgreSQL checks the permissions of PGDATA before it starts and does not allow you to proceed if group permissions exist. We have two options:-related changes -user documentation i) alter all things that rely on security being userlevel-only - initdb - startup - most other security features? ii) encourage (i.e. force) people using XLogArchive API to run as the PostgreSQL owning-user (postgres). I've avoided this issue in the general implementation, thinking that there'll be some strong feelings either way, or an alternative that I haven't thought of yet (please...) -changes to postgresql.conf The parameter setting
Re: [HACKERS] What can we learn from MySQL?
Jean-Michel POURE wrote: [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Dear Bruce, Taking the example of pgAdmin III, which reached nearly one million hits in December (http://www.pgadmin.org/stats/webalizer), nothing seems impossible for PostgreSQL. Why not create an all-in-one bundle offering PostgreSQL, Apache, Php and PhpPgAdmin for Win32 and ... mass-release it. There is no need to create a complete installer. There could be a single installer executing other installers (like it is sometimes the case in the Win32 world). So that installers remain different. A single web page like http://win.postgresql.org; in 40 languages is enough to mass-release PostgreSQL. With an installer and a single web page, PostgreSQL Win32 could quickly reach one million downloads every month. There is no need to look for complicated strategies. Every month, there can be 10% more downloads. In the end, people will even forget the name of MySQL. That seems like a good idea. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] What can we learn from MySQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Dear Bruce, Taking the example of pgAdmin III, which reached nearly one million hits in December (http://www.pgadmin.org/stats/webalizer), nothing seems impossible for PostgreSQL. Why not create an all-in-one bundle offering PostgreSQL, Apache, Php and PhpPgAdmin for Win32 and ... mass-release it. There is no need to create a complete installer. There could be a single installer executing other installers (like it is sometimes the case in the Win32 world). So that installers remain different. A single web page like http://win.postgresql.org; in 40 languages is enough to mass-release PostgreSQL. With an installer and a single web page, PostgreSQL Win32 could quickly reach one million downloads every month. There is no need to look for complicated strategies. Every month, there can be 10% more downloads. In the end, people will even forget the name of MySQL. Cheers, Jean-Michel -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAjW11extoHHj2YFMRAggVAJ0e/W4D/tnm/AtMK0nbjfDROtv/fwCfQ/eC KAnaz5T3PCceVlVS6zirsqg= =N1NM -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: getting a crash during initdb
make clean appears to have fixed the initdb crash :) sorry to bother... :) Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR Phase 2 - Design Planning
On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote: Simon Riggs wrote: Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. I was thinking --- how would someone know the time to use for restore? I think there should be a way to get a TransactionId and restore up to that point. It'd be cool, but not required, if the system showed what valid TransactionIds there are, and roughly what they did (the xlog code already has describers everywhere AFAICS). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El realista sabe lo que quiere; el idealista quiere lo que sabe (Anónimo) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Hi, Mark, Yes, I've seen your e-mails around. You should use a sig, though, they're easy to create. I think I am talking about something different. In a company, the core team would be the CTO. I think some entity, one or more people, needs to define the product. Typically this is marketing and product management. But, as Peter reminds me all the time, we're not a company. ;-) The why is that there is no real entity doing so. 2) what this person would be doing that's not already covered by existing groups; All the groups, with the exception of advocacy, are here's what we are building and here's a bug groups. There is planning on hackers, but it is almost purely technical. Marketing features do no often get a reasonable hearing. That's not an argument for not using existing apparatus. What you've persuaded me is that you should: a) join the Advocacy group; b) galvanize people around developing a coherent marketing plan; c) Lead a crew of volunteers and follow through on that process until the plan is ready for comments by Core and Hackers, d) stick around for the arguments and revisions That's what we *need*. We don't need a volunteer with a title who might or might not do any of the above. I'm particulary struck by the fact that you chose to inaugurate this discussion on Hackers, instead of Advocacy where it would have been more appropriate and where more of the *existing* marketing volunteers would have participated. At this point, I'd have to forward the whole thing to transfer it ... I think that a talented manager could make the case for certain features. So? So could any community member with a good grasp of database engineering and an ability to write persuasive e-mails. 4) who this person would be. We recrute like a company does. Um, and pay them with what? Cowrie shells? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
[EMAIL PROTECTED] wrote: If we want to make PostgreSQL a wildly popular product, there will be some pain. There should be a Product Management group. The leader(s) of this group should be chosen carefully, as he (they) must be free to define what PostgreSQL is. They must have a good feel for product development and understanding of the underlying technology, but not be so techie that we don't address the issues intended. They must be able to rally the troops and direct development efforts. Lastly, he (they) must have the confidence of the core hackers, as it is likely that there will be disagreements with the direction of PostgreSQL, and it wouldn't work if Product Management couldn't actually manage what the product was because nobody listened. I agree with this, more or less. The lack of leadership that coordinates all activities actively is really missing. Unfortunately, I believe we are already in a state of fragmentation where setting up something like this is no longer possible. What the end user sees as a PostgreSQL system is brought to them by nearly a dozen different groups nowadays. And the server group can no longer count on having a stronger position to pull them all together. The only option to achieve what you want soon is to market your own product. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Hey, First of all, who is this? I don't recognize the e-mail, and you haven't been signing any of your posts. I've been posting on hackers on and off for a few years. My name is Mark. true, others, however, are very welcoming to direction. AFAIK, this includes none of our major code contributors. So all you're really talking about is manipulating the TODO list. You can't tell programmers what to code unless you're paying them. Yes and no. People can and do what's needed when it is clearly articulated. What is lacking is a clear direction WRT marketing. It depends in the individual. Lastly, Bruce, Tom, Peter, and others are very didicated to PostgreSQL. If a real case can be made for a feature, I'm sure they are reasonable enough to see that and grudgingly implement it. Someone, however, has to keep an eye on that ball. Yes, but they don't need a title to do so. Nor is there any reason for this to be one person. In fact, you've just described one of the reason for the Core's existance -- and even the Core defers to the consensus of decision on this forum about which features to implement and how. I think I am talking about something different. In a company, the core team would be the CTO. I think some entity, one or more people, needs to define the product. Typically this is marketing and product management. Now, if you're arguing that we could use a more cohesive, readable roadmap? Sure! Want to prepare one? I can even help you find out what's under development and what's not likely any time soon. Absolutely, but it would be meaningless if no body listens. Linux has Linus, he has a very good eye in the market forces. Uh-huh. So? That still doesn't make him a product manager. Maybe I've overstated my case, by management I mean the small 'm' not the big 'M' OpenOffice is very much managed by Sun. I used to be a Project Lead for OpenOffice.org. Very cool. It is a great project/product. I think the amount of consensus and compromise, and the extent to which the Community Council and the Project Leads govern the project, would surprise you. No it wouldn't. Overall, I've not seen you present any coherent arguments as to: 1) why we need a new person with a title for marketing stuff; The why is that there is no real entity doing so. 2) what this person would be doing that's not already covered by existing groups; All the groups, with the exception of advocacy, are here's what we are building and here's a bug groups. There is planning on hackers, but it is almost purely technical. Marketing features do no often get a reasonable hearing. 3) how this person would be able to accomplish their job; I think that a talented manager could make the case for certain features. 4) who this person would be. We recrute like a company does. As far as I'm concerned, we need use titles here only if it lends the entitled some kind of authority with the outside world that helps them on their volunteer projects (Robert Bernier, Business Intelligence Analyst, is a good example of a good use of titles -- that one convinces companies that he approaches about case studies that he's for real). Titles are not at all useful *inside* the community, we don't need them. I'm not trying to change the dynamic significantly, but I think, again if increasing usership is important, that some market driven lessons need to be learned. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] signal 11 on AIX: 7.4.2
Has this been resolved? --- Andrew Sullivan wrote: On Mon, Apr 19, 2004 at 11:18:07AM -0400, Tom Lane wrote: What you'd need to do is determine which system headers are being #include'd by that config test, and then look through them to find struct addrinfo. Well, I have this in /usr/include/netdb.h: struct addrinfo { int ai_flags; /* AI_PASSIVE, AI_CANONNAME, AI_NUMERICH OST */ int ai_family; /* PF_xxx */ int ai_socktype; /* SOCK_xxx */ int ai_protocol; /* 0 or IPPROTO_xxx */ size_t ai_addrlen;/* length of ai_addr */ char*ai_canonname; /* canonical name for hostname */ struct sockaddr *ai_addr; /* binary address */ struct addrinfo *ai_next; /* next structure in list */ }; Using the cpp trick that Alvaro Herrera suggested, I see that file mentioned in the output, and this a little way along: struct addrinfo { int ai_flags; int ai_family; int ai_socktype; int ai_protocol; size_t ai_addrlen; char*ai_canonname; struct sockaddr *ai_addr; struct addrinfo *ai_next; }; So it looks like that must be the one. Dunno if this helps. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Hey, First of all, who is this? I don't recognize the e-mail, and you haven't been signing any of your posts. true, others, however, are very welcoming to direction. AFAIK, this includes none of our major code contributors. So all you're really talking about is manipulating the TODO list. You can't tell programmers what to code unless you're paying them. It depends in the individual. Lastly, Bruce, Tom, Peter, and others are very didicated to PostgreSQL. If a real case can be made for a feature, I'm sure they are reasonable enough to see that and grudgingly implement it. Someone, however, has to keep an eye on that ball. Yes, but they don't need a title to do so. Nor is there any reason for this to be one person. In fact, you've just described one of the reason for the Core's existance -- and even the Core defers to the consensus of decision on this forum about which features to implement and how. Now, if you're arguing that we could use a more cohesive, readable roadmap? Sure! Want to prepare one? I can even help you find out what's under development and what's not likely any time soon. Linux has Linus, he has a very good eye in the market forces. Uh-huh. So? That still doesn't make him a product manager. OpenOffice is very much managed by Sun. I used to be a Project Lead for OpenOffice.org. I think the amount of consensus and compromise, and the extent to which the Community Council and the Project Leads govern the project, would surprise you. Overall, I've not seen you present any coherent arguments as to: 1) why we need a new person with a title for marketing stuff; 2) what this person would be doing that's not already covered by existing groups; 3) how this person would be able to accomplish their job; and 4) who this person would be. As far as I'm concerned, we need use titles here only if it lends the entitled some kind of authority with the outside world that helps them on their volunteer projects (Robert Bernier, Business Intelligence Analyst, is a good example of a good use of titles -- that one convinces companies that he approaches about case studies that he's for real). Titles are not at all useful *inside* the community, we don't need them. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: getting a crash during initdb
Bruce Momjian wrote: Merlin Moncure wrote: I'll check why the rmdir command is not working as expected. I just poked around and couldn't figure out the cause. Initdb should either remove the directory if it created it, or remove everything _in_ the directory if the directory already existed. I tried the rmdir/del /s /q commands in a CMD window and it worked fine. Problem is backslash issue :) initdb is issuing del /s /q c:/postgres/data. Is there a library command to properly resolve slashes? Uh, I just testd this using CMD and it worked: Yes, you are correct, I was jumping to conclusions. What's really strange is now initdb is properly cleaning up the folders. I examined my previous logs and apparently the value of rmtopdir parameter to rmtree was wrong... Note my previous initdb log said removing contents of ... (now says removing ...) Some more checking turns out it is all hooking on the return val of check_data_dir...perhaps the condition was hooking if I had the folder open in another window...so no worries there. Still having the problems with initdb. Merlin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Thread code not vpath-safe
OK, I worked with Alvaro via IM and it is fixed now. --- Alvaro Herrera wrote: On Mon, Apr 26, 2004 at 01:15:13PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: The thread testing program is not nice to a vpath build. I see this error: (Basically what I'm doing is create an empty directory, cd to it and then call ../sourcedir/configure --enable-thread-safety) I tried and couldn't get it to fail. Whoa, seems I'm the irreproducible-bug-reporter now :-( Do you have this line at the bottom of configure.in: if ! $srcdir/src/tools/thread/thread_test 5 Yes: if ! $srcdir/src/tools/thread/thread_test 5 thenrm -f $srcdir/src/Makefile.global echo no echo $srcdir/src/tools/thread/thread_test echo AC_MSG_ERROR([Thread test program failed. Your platform is not thread-safe.]) fi I tried it with: mkdir /tmp/thr cd /tmp/thr ~/CVS/pgsql/source/00orig/configure --enable-thread-safety [lots of output, and then:] checking for gmake... gmake checking thread safety of required library functions... Makefile:13: ../../../src/Makefile.global: No such file or directory gmake: *** No rule to make target `../../../src/Makefile.global'. Stop. configure: error: Can not clean thread test directory. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No necesitamos banderas No reconocemos fronteras (Jorge Gonz?lez) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [HACKERS] PITR Phase 2 - Design Planning
On Mon, 2004-04-26 at 22:05, Bruce Momjian wrote: Simon Riggs wrote: Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. To make (2) work we would have to have a timestamp associated with each transaction. This could be in one of two places: 1. the transaction record in the clog 2. the log record in the xlog We would then recover the xlog record by record, until we found a record that had a timestamp desired point-in-time. Currently, neither of these places have a timestamp. H. We can't use pg_control because we are assuming that it needs recovery... I can't see any general way of adding a timestamp in any less than 2 bytes. We don't need a timezone. The timestamp could refer to a number of seconds since last checkpoint; since this is limited already by a GUC to force checkpoints every so often. Although code avoids a checkpoint if no updates have taken place, we wouldn't be too remiss to use a forced checkpoint every 32,000 seconds (9 hours). Assuming that accuracy of the point-in-time was of the order of seconds?? If we went to 0.1 second accuracy, we could checkpoint (force) every 40 minutes or so. All of that seems too restrictive. If we went to milliseconds, then we could use a 4 byte value and use a checkpoint (force) every 284 hours or 1.5 weeks. Thoughts? I was thinking --- how would someone know the time to use for restore? Certainly they will not know subsecond accuracy? Probably second-level accuracty is enough, _except_ when they want everything restored up to a DROP TABLE transaction or some major problem. Is there a way to give users a list of transactions on a log backup? Can we show them the username, database, or commands or something? Would they be able to restore up to a specific transaction in that case? Basically, we could give them sub-second recovery, but what value would it be? Yes, you remind me of a whole train of thought... There should be a switch to allow you to specify the txnid you wish to recover up until as well. You raise the point of how you know what time to recover to. That is in fact the very hardest part of recovery for a DBA. That's a good reason for being able to list xlog contents, as you can with Oracle. Sounds like we need an XlogMiner utility... Can we show them the username, database, or commands or something? Yes, that sounds fairly straightforward possible using a modification of the ReadRecord functions at the bottom of xlog.c - which is why security of the xlogs is important. It's also a good reason for being able to pause and restart recovery, so you can see what it's like before continuing further. Usually you are trying to sync up the contents of the database with all of the other things that were being updated too. Often these will define either the required transaction contents, or give a time to use. Whatever level of time accuracy you choose, we would always need to handle the case where multiple transactions have been committed with exactly the same time (after rounding) and yet we may wish to split them. Rolling forward to a txnid would help there. Best regards, Simon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] FW: getting a crash during initdb
Merlin Moncure wrote: make clean appears to have fixed the initdb crash :) sorry to bother... :) Are you sure you're using a clean build and a really current checkout? Yes, 100%. Care to update that percentage? :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 2 - Design Planning
On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote: On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote: Simon Riggs wrote: Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. I was thinking --- how would someone know the time to use for restore? I think there should be a way to get a TransactionId and restore up to that point. It'd be cool, but not required, if the system showed what valid TransactionIds there are, and roughly what they did (the xlog code already has describers everywhere AFAICS). You're right, I think we should start by implementing the rollforward to a txnid before we consider the rollforward to a specified point-in-time. All the hooks for that are already there... Best regards, Simon Riggs ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote: On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote: Simon Riggs wrote: Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. I was thinking --- how would someone know the time to use for restore? I think there should be a way to get a TransactionId and restore up to that point. It'd be cool, but not required, if the system showed what valid TransactionIds there are, and roughly what they did (the xlog code already has describers everywhere AFAICS). You're right, I think we should start by implementing the rollforward to a txnid before we consider the rollforward to a specified point-in-time. All the hooks for that are already there... Yep, sounds like a plan. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR Phase 1 - Test results
On Mon, 2004-04-26 at 18:08, Bruce Momjian wrote: Simon Riggs wrote: Well, I guess I was fairly happy too :-) YES! I'd be more comfortable if I'd found more bugs though, but I'm sure the kind folk on this list will see that wish of mine comes true! The code is in a needs more polishing state - which is just the right time for some last discussions before everything sets too solid. Once we see the patch, we will be able to eyeball all the code paths and interface to existing code and will be able to spot a lot of stuff, I am sure. It might take a few passes over it but you will get all the support and ideas we have. Thanks very much. Code will be there in full tomorrow now (oh it is tomorrow...) Fixed the bugs that I spoke of earlier though. They all make sense when you try to tell someone else about them... Best Regards, Simon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Broken Catalog? -- 7.4.2
The function format_type() fails only for interval when used on the interval type template1=# select format_type(oid, typlen) from pg_type; ERROR: invalid INTERVAL typmod: 0xc template1=# select format_type(oid, typlen) from pg_type where typname != 'interval'; -- Many results ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Thread code not vpath-safe
On Mon, Apr 26, 2004 at 03:09:48PM -0400, Bruce Momjian wrote: OK, I worked with Alvaro via IM and it is fixed now. It worked cleanly for me. Thanks. Also, the thread flags look correct, but then I'm not a threaded person. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Tiene valor aquel que admite que es un cobarde (Fernandel) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Broken Catalog? -- 7.4.2
On Mon, Apr 26, 2004 at 09:36:26PM -0400, Rod Taylor wrote: The function format_type() fails only for interval when used on the interval type template1=# select format_type(oid, typlen) from pg_type; select format_type(oid, typtypmod) from pg_type; -- works In fact, I believe this is the correct answer from timestamp types, for example. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Having your biases confirmed independently is how scientific progress is made, and hence made our great society what it is today (Mary Gardiner) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Reporting a security hole
[EMAIL PROTECTED] wrote: I work at Coverity where we make a static analysis tool to find bugs in software at compile time. I think I found a security hole in postgresql-7.4.1, but I don't want to just report it to a public list. I sent email to [EMAIL PROTECTED], hoping that the address existed, but I got no response. So where can I report a potential security hole? I have replied to the detailed message on the core list and the security list (not sure who that is). We are researching it. From my initial review, it is something that needs cleaning up, but is not a major security issue, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Pl/Java and GCJ
Thomas Hallgren wrote: Hi, I've made some very encouraging tests using The GNU version of Java known as GCJ together with my Pl/Java implementation . At present I use GCJ just like any other JVM, i.e. as an interpreter. This is not very optimal since GCJ can compile all Java code into shared libraries just like it would compile C or C++ code. Putting it short, there's a tradeoff between adhering to the proposed standard for SQL/Java mapping and using precompiled shared objects. Pre-loaded modules loaded by the postmaster for instance, can never be standard although it will help boost performance a great deal. I guess that extending the proposed functionality is OK as long as attempts are made to follow the standard whenever possible. To do this, I'd like some advice concerning loading of shared libraries that are the result of a jar file gcj compilation. Today, using a normal JVM, I can install modules in the form of jar files into the database. The modules can then be used dynamically and on demand by Pl/Java. Using GCJ, I'd like to have the same semantics from a user perspective (since they are modelled from the standard proposal) but behind the scene the jar file should be compiled into a shared library which then is made available to postgres. Question is, where do I store the shared object, and how do I load it? Ideally, I'd like it to be stored in the database and subject to normal grant/revoke rights etc. but dlopen() will hardly look there. So instead, I'd like to store it somewhere in the filesystem on the server where postmaster runs. Is PostgreSQL doing something similar in other places today (i.e. install a shared library on the server using SQL commands issued from the client)? Any thoughts and/or ideas on this are greatly appreciated. It seems this would be handled just like we handle C functions today, that is you create a shared object file, it sits in the file system, and you LOAD the object into your backend, or you record it via CREATE FUNCTION and specify the pathname. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] case folding and postgres
Hello, I have a project I'm moving from mysql to postgresql. It has both a fair amount of code and a moderate amount of data. In MySQL the identifiers are all MixedCase, but the query strings are never quoted. I would like to change the default behaviour of postgresql to not fold the case to lower. If I change scansup.c 's downcase_truncate_identifier() to not lowercase identifiers, will I break anything (other than case insensitivity?) Furthermore, is there any way I could package this patch such that it would be accepted? A suggestion I received from #postgresql was to implement upper casing, lower casing, and leave-it-alone casing and to have a per-db setting for that. Another approach I wouldn't mind adding is a start-time option. Thank you for CCing me, as I am not subbed to postgresql-hackers list. -- nothing can happen inside a sphere that you could not inscribe upon it. ~mindlacehttp://mindlace.net ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] TPC H data
Shalu Gupta wrote: Hello, We are trying to import the TPC-H data into postgresql using the COPY command and for the larger files we get an error due to insufficient memory space. We are using a linux system with Postgresql-7.3.4 Is it that Postgresql cannot handle such large files or is there some other possible reason. Thanks Shalu Gupta NC State University. Shalu, I loaded the largest TPC-H table (lineitem, roughly 6 million rows) the other day into a completely untuned 7.5devel PostgreSQL instance running on RH 9, and it didn't raise a sweat. I delayed creating the indexes until after the load. Data load took roughly 10 minutes, index creation took a further 35 minutes (there are 13 of them). HTH. (I'm just down the road from NCSU, would be happy to help out) cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])