Re: [HACKERS] PANIC: block 463 unfound during REDO after out of
Btw -unfound?? I think the English there might need to be improved :) Chris On 1/11/07, Richard Huxton dev@archonet.com wrote: Warren Guy wrote: Hi everyone Was running a VACUUM on a database on a partition which was running out of disk space. During VACUUM the server process died and failed to restart. Running PostgreSQL 8.1.4 ... Jan 11 15:02:39 marshall postgres[73909]: [5-1] FATAL: the database system is starting up Jan 11 15:02:40 marshall postgres[73888]: [12-1] PANIC: block 463 unfound Jan 11 15:02:41 marshall postgres[67977]: [5-1] LOG: startup process (PID 73888) was terminated by signal 6 Jan 11 15:02:41 marshall postgres[67977]: [6-1] LOG: aborting startup due to startup process failure You say was running out of disk space - does that mean it did run out of disk space? I don't see the error that caused this, just the results. That would suggest to me that something unusual caused this (or you clipped the log fragment too far down :-) In any case, the first thing I'd try is to make your on-disk backups and set it up as though it's PITR recovery you're doing. That way you can stop the recovery before block 463 causes the failure. Oh, assuming you've got the space you need on your partition of course. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Chris Kings-Lynne Director KKL Pty. Ltd. Biz: +61 8 9328 4780 Mob: +61 (0)409 294078 Web: www.kkl.com.au ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Loose ends in PG XML patch
* Shouldn't the xml type support binary I/O? Right now it is the only standard datatype that doesn't. I have no idea whether there is an appropriate representation besides text, but if not we could define the binary representation to be the same as text. There is an effort to develop a binary xml format: http://www.w3.org/TR/xbc-characterization/ ---(end of broadcast)--- TIP 1: 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] Sorry about the GIN docs :(
Waaah! Don't apply that it's completely unfinished!!! And not to mention still has parts copied and pasted from GiST docs without modification. I believe Oleg or Teodor plans to finish it up On 9/5/06, Bruce Momjian [EMAIL PROTECTED] wrote: Patch applied. Thanks. --- Christopher Kings-Lynne wrote: Hi guys, I've attached as much as I've done so far on the GIN docs. It's not a lot, but I'm afraid with the feature freeze in effect, I'm just not going to have the ability to get them done by the RC date. The main problem was I just strugged to fully understand it all :( Anyway, hopefully someone else can pick them up and finish them off for the release. Sorry about that, Chris [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Chris Kings-Lynne BSc (hons) Director KKL Pty. Ltd. 10 Orana Crescent City Beach, Australia 6015 +61 (0)409 294078 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Is this getting through?
Are my mails getting through? Did anyone see my mail about the GIN docs? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Sorry about the GIN docs :(
Hi guys, I've attached as much as I've done so far on the GIN docs. It's not a lot, but I'm afraid with the feature freeze in effect, I'm just not going to have the ability to get them done by the RC date. The main problem was I just strugged to fully understand it all :( Anyway, hopefully someone else can pick them up and finish them off for the release. Sorry about that, Chris gindocs.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] An Idea for planner hints
see. Collecting the statistics thereafter isn't that hard, but there needs to be a way to not collect an exponential volume of statistics on all column combinations. You could collect them on all FK relationships - is that enough? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: BugTracker (Was: Re: [HACKERS] 8.2 features status)
We have three candidates already -- debbugs, RT and Gnats. The first has the advantage that was written by hackers, for hackers, so it doesn't have any of the insane for end users stuff which annoys so many people around here ;-) (On the other hand it does have some web stuff for generating reports, etc). Kill me now if I have to use GNATS :) Have you ever tried submitting a bug to the FreeBSD project? *shudder* That said, I'll live :) I have recently totally falling in love with Trac and its complete subversion integration. I'm not sure it supports PostgreSQL, and converting to subversion is probably a little too hardcore at the moment :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] BugTracker
Trac does support PostgreSQL... The thing I don't understand at this point is what exactly is the nature of the integration with the SCM. I don't see it being likely that there will be a deep integration of the PostgreSQL SCM (whatever the SCM platform) with Trac; that's way too much change to expect quickly... Basically I have it set up like this: * Trac has built-in browsing of the svn via the web * When I commit, the commit message must have a reference to an open ticket in Trac, eg: Rearrange blah to fix bad bug. Fixes #745 * In trac's development timeline, or whatever you can see all the commits against each ticket, and just click on them to see the complete diff for each change set in SVN. * Commit messages can contain full wiki markup, that fully integrates with the wiki that is all thoughout Trac. So, you can have wiki in your commit messages that refers to other bugs, wiki pages, source code files and lines, etc. Basically, Trac is cool. I don't see us adopting it too quickly for PostgreSQL though :P Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Bison Version
What version of Bison is currently required to compile HEAD? 1.75 doesn't seem to work... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Progress bar updates
It would be the most practical way for a DBA to monitor an application. But it's not going to be convenient for clients like pgadmin or psql. Even a web server may want to, for example, stream ajax code updating a progress bar until it has results and then stream the ajax to display the results. Having to get the backend pid before your query and then open a second database connection to monitor your first connection would be extra footwork for nothing. But that said, it CAN be coded and work just fine no? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Strange. Last time I checked I thought MySQL dump used 'multivalue lists in inserts' for dumps, for the same reason that we use COPY ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] 8.2 features?
I did some experimentation just now, and could not get mysql to accept a command longer than about 1 million bytes. It complains about Got a packet bigger than 'max_allowed_packet' bytes which seems a bit odd because max_allowed_packet is allegedly set to 16 million, but anyway I don't think people are going to be loading any million-row tables using single INSERT commands in mysql either. Ah no, I'm mistaken. It's not by default in mysqldump, but it does seem recommended. This is from man mysqldump: -e|--extended-insert Allows utilization of the new, much faster INSERT syntax. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] More nuclear options
I've already added adddepends to pgFoundry (as Old PG Upgrade), since people spoke up for it. I will assign one of them as admin of the project (not sure who yet). How is addepends in any way old pg upgrade?? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Three weeks left until feature freeze
No, I don't believe you can do this with CVS at all. We'd need something like SVN/WebDAV to be able to grant write access just to specific parts of the tree to different people. You just use an on-commit script like cvsacl. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Removing AddDepends; should I bother with a project?
1) Rod Taylor is not interested in maintaining it anymore; 2) It currently throws errors on 8.2 (and probably earlier); 3) With KL's improvements to pg_dump for 8.0, about half of its functionality is no longer necessary. So, speak up if someone thinks there's some reason to save adddepends anywhere other than the CVS and FTP archives. I think it absolutely should be kept on pgFoundry no? I don't see how my improvements to pg_dump help anyone upgrading from 7.2 to later versions? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Removing AddDepends; should I bother with a project?
Think is, 8.1 does a much better job of upgrading 7.2 datatabases than 7.3 or 7.4 did anyway. I just tested using a database created in 7.1 and upgraded to 7.2 which has a baroque and unnecessarily complex schema (legacy production applicaiton) which breaks on 7.4 without adddepends. I was able to upgrade it to 8.2(today) and it worked without adddepends. I'm testing dump, load, dump, load now to see if it still works OK. That's irrelevant - does it actually have FK's is the question. I bet you'll instead have a bunch of CREATE CONSTRAINT TRIGGER statements... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixed length datatypes. WAS [GENERAL] UUID's as
If there is interest - I'm sure Nathan and I would be willing to put it on pgfoundry, and at some point give it up for inclusion into PostgreSQL. One requirement would be that it runs on Windows. Is that something you have tested? In case it influences anyone, MySQL 5 already has built-in UUID support: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#id2899901 Chris ---(end of broadcast)--- TIP 1: 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] GIN index creation extremely slow ?
It looks like we still don't have any docs for GIN in the tree so I don't know if those timings are expected or not ... Ummm my bad. Sorry... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] vacuum, performance, and MVCC
The example is a very active web site, the flow is this: query for session information process HTTP request update session information This happens for EVERY http request. Chances are that you won't have concurrent requests for the same row, but you may have well over 100 HTTP server processes/threads answering queries in your web server farm. You're crazy :) Use memcache, not the DB :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Full Disjunction
What IS this full disjunction business? User Pgstudy wrote: Log Message: --- Some more deformed tuple structures consolidation works. Now even faster but still half way done. Modified Files: -- fd: algutils.c (r1.9 - r1.10) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/fulldisjunction/fd/algutils.c.diff?r1=1.9r2=1.10) odmbfd.c (r1.12 - r1.13) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/fulldisjunction/fd/odmbfd.c.diff?r1=1.12r2=1.13) queuesfuncs.h (r1.8 - r1.9) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/fulldisjunction/fd/queuesfuncs.h.diff?r1=1.8r2=1.9) tset.c (r1.8 - r1.9) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/fulldisjunction/fd/tset.c.diff?r1=1.8r2=1.9) tsetfuncs.h (r1.6 - r1.7) (http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/fulldisjunction/fd/tsetfuncs.h.diff?r1=1.6r2=1.7) ---(end of broadcast)--- TIP 1: 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 -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] sync_file_range()
http://lwn.net/Articles/178199/ Check out the article on sync_file_range(): long sync_file_range(int fd, loff_t offset, loff_t nbytes, int flags); This call will synchronize a file's data to disk, starting at the given offset and proceeding for nbytes bytes (or to the end of the file if nbytes is zero). How the synchronization is done is controlled by flags: * SYNC_FILE_RANGE_WAIT_BEFORE blocks the calling process until any already in-progress writeout of pages (in the given range) completes. * SYNC_FILE_RANGE_WRITE starts writeout of any dirty pages in the given range which are not already under I/O. * SYNC_FILE_RANGE_WAIT_AFTER blocks the calling process until the newly-initiated writes complete. An application which wants to initiate writeback of all dirty pages should provide the first two flags. Providing all three flags guarantees that those pages are actually on disk when the call returns. Is that at all useful for PostgreSQL's purposes? Chris ---(end of broadcast)--- TIP 1: 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] ADD/DROP INHERITS
I forget whether the developer managed to get it working without doing any table rewriting. In theory the table just needs to know that records that are missing that column in the null bitmap should behave as if they have the default value. But I seem to recall some headaches with that approach. The problem is if you then change the default. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Snowball and ispell in tsearch2
We got a lot requests about including stemmers and ispell dictionaries for all accessible languages into tsearch2. I understand that tsearch2 will be closer to end user. But sources of snowball stemmers is about 800kb, each ispell dictionaries will takes about 0.5-2M. All sizes are sized with compression. I am afraid that is too big size... What are opinions? Maybe putting it on pgFoundry? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Snowball and ispell in tsearch2
Perhaps we can put together the source code for all languages modules available and provide scripts to fetch ispell data or to generate the snowball stemmers. A debian package maintainer would have to fetch all the data to generate all language packages. Someone else might just want to download and compile a norwegian snowball stemmer. I'd be willing to help with such a project. I have experience with tsearch2 as well as with gentoo and debian packaging. I can't help with rpm, though. I could help with a FreeBSD package I suppose. ---(end of broadcast)--- TIP 1: 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] Snowball and ispell in tsearch2
I'd be willing to help with such a project. I have experience with tsearch2 as well as with gentoo and debian packaging. I can't help with rpm, though. I could help with a FreeBSD package I suppose. Although I should probably finish up those damn GIN docs first :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LinkedIn
Do any of you guys use linkedin.com and want to add me to your contacts network? Ironically, I don't use LinkedIn, even though they use PostgreSQL (not exclusively, though). Really? How do you know that? Are any of their people part of the community? Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PQescapeIdentifier
Here's a question. I wish to add a function to libpq to escape PostgreSQL identifiers. Will this function be subject to the same security/encoding issues as PQescapeString? Chris -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PQescapeIdentifier
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Here's a question. I wish to add a function to libpq to escape PostgreSQL identifiers. Will this function be subject to the same security/encoding issues as PQescapeString? Is this of any general-purpose use? How many apps are really prepared to let an untrusted user dictate which columns are selected/compared? phpPgAdmin has use for it, I assume pgAdmin would as well. As does PHP's PostgreSQL interface, etc. The PHP sites I work on in my job have some functions to automatically build queries (eg. insert queries), which technically need to escape column names. It seems nice from my point of view as completeness, and will help in the case when we ever change identifier escaping, etc. It might also encourage app writers to escape fields properly...I've seen too many places where they escape strings, but not fields... However, I guess it's still a small minority of apps. But to answer your question, yes, I can certainly imagine encoding-related risks... It's probably out of my league to code safely then I guess, unless it's basically the same coding as for PQescapeStringInternal...? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] LinkedIn
Hi hackers, I sent this to the hackers list, as this is the people I wish to contact. Do any of you guys use linkedin.com and want to add me to your contacts network? I'm keen to get into a network of PostgreSQL-related people - for future jobs/contracts purposes. My linkedin.com email is [EMAIL PROTECTED] Sorry for bothering anyone with this who doesn't care... Cheers, Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Performance Issues
Thank you for your help. I found that an implicit index is created for the primary key in the current version. However, it is not done in 7.x version. It absolutely is created in all 7.x versions of PostgreSQL. ---(end of broadcast)--- TIP 1: 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] String Similarity
Try contrib/pg_trgm... Chris Mark Woodward wrote: I have a side project that needs to intelligently know if two strings are contextually similar. Think about how CDDB information is collected and sorted. It isn't perfect, but there should be enough information to be usable. Think about this: pink floyd - dark side of the moon - money dark side of the moon - pink floyd - money money - dark side of the moon - pink floyd etc. To a human, these strings are almost identical. Similarly: dark floyd of money moon pink side the Is a puzzle to be solved by 13 year old children before the movie starts. My post has three questions: (1) Does anyone know of an efficient and numerically quantified method of detecting these sorts of things? I currently have a fairly inefficient and numerically bogus solution that may be the only non-impossible solution for the problem. (2) Does any one see a need for this feature in PostgreSQL? If so, what kind of interface would be best accepted as a patch? I am currently returning a match liklihood between 0 and 100; (3) Is there also a desire for a Levenshtein distence function for text and varchars? I experimented with it, and was forced to write the function in item #1. ---(end of broadcast)--- TIP 1: 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 -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
And MySQL is much closer to being a competitor now than they were in 4.1. And feature-wise they'll probably equal PostgreSQL in the next release. Will the features be anywhere near as robust or well thought out? No. But in a heck of a lot of companies that doesn't matter. Don't forget that they got nested transactions and PITR both before us. They will also shortly have really nice partitioning before us... ...don't underestimate their development speed. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
We also need better support for non C locales in tsearch. As I was porting mysql's sakila sample database I was reminded just how painful it is when you initdb in a non-supported locale (which is probably the default on the majority of distros out there) In 8.2 tsearch2 supports utf8... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [OT] MySQL is bad, but THIS bad?
If you want to get users to swtich to your software from your competitors, you have to eliminate barriers, and a big one for any database is getting locked into a specific one. People aren't going to take the time to try switching to postgresql if they can't easily make it back to thier former database. It's one of the reasons why PostgreSQL's standards compliance is so important; if you want to swtich to a new database, your best bet is to give PostgreSQL a shot, because even if you don't like it, we're not going to try and trap you into our software with bunches of non-standard knobs. Low barrier to exit == low barrier to entry. Another reason why a tool to export from pgsql to mysql is just as important as the vice versa... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] audit table containing Select statements submitted
Having tinkered a little with PQA, yes, actually. The issue is that the message text can easily be multi-line and contain a vast variety of special characters. The issue is figuring out where the prefix, the tag and the message begin and end. And our text log format makes that a PITA. Try pgfouine... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] BEGIN inside transaction should be an error
We could, but it'd probably break about as many apps as it fixed. I wonder whether php shouldn't be complaining about this, instead --- doesn't php have its own ideas about controlling where the transaction commit points are? All PHP does is when the connection is returned to the pool, if it is still in a transaction, a rollback is issued. The guy needs to do his own tracking of transaction state if he wants to avoid these problems... Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
Yes. There is a patch pending on -patches which fix almost all of these in HEAD. (There are a few tiny things related to perl and NLS that aren't included in it ATM. And I'm just assuming you're seeing the same problems as I was but I didn't base my work off vcproject). I'm also working on a buildscript to convert the Makefiles to visual c++ project files, but that's not quite done yet. The idea with this work is to have the stuff as integrated as possible with main CVS, so the maintenance will be as low as possible - unlike the vcproject project which has been focusing on keeping a separate build environment maintained. The CrystalSpace and PlaneShift projects I'm associated with have automated MSVC generators. Want me to try to grab them for you? They'd be GPL though. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] Build with Visual Studio MSVC
You mean they have a tool that parses GNU Makefiles and generate VC project files? Sure, that might be interesting. I've seen I think two others, and tried, but they fell over badly because the pg build system was too complicated. But I beleive I'm still allowed to loko at GPL stuff and get ideas as long as I don't copy the code :-) http://cvs.sourceforge.net/viewcvs.py/crystal/CS/mk/msvcgen/ I think it actually takes some sort of XML format or something...or just reads in files in directories. I'm not sure. Chris ---(end of broadcast)--- TIP 1: 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] Rethinking locking for database create/drop vs connection
It's slightly annoying to have to read the flat file twice, but for reasonable numbers of databases per installation I don't think this will pose any material performance penalty. The file will certainly still be sitting in kernel disk cache. Dropping a db isn't exactly a common occurrence anyway no? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a black box, or not?
Sure, but there's no reason that would couldn't allow that with a true black-box SERIAL, either. In fact, you can do it today if you want, just by creating a wrapper around nextval(pg_get_serial_sequence()). Or just use lastval() Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Summer of Code idea
For the sake of saying again, I already have a recursive-descent parser for PostgreSQL written in a PCCTS grammar. It's something I started writing years ago, but I'd be willing to consider open sourcing it if the PostgreSQL community will really entertain the thought of switching. Unfortunately, this discussion usually ends up with, why would we want to change what we have now when it already works? Is it faster? How much faster? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Logging pg_autovacuum
Those messages were at LOG level because otherwise it's difficult to be sure from the log that autovac is running at all. OK, so what do we want to do? Clearly outputing something everytime pg_autovacuum touches a database isn't ideal. By default, the server logs should show significant events, which this is not. Do we want something output only the first time autovacuum runs? I've considered several times proposing that I want to be able to turn off or do something about autovacuum log messages. I just always thought it would be rejected. I have it set up so that I get the last few hundred lines of my postgres logs mailed to me each day. However, most of the time I just get a few hundred autovacuum messages. So, I had to much around with grepping out the autovacuum lines, etc. I personally don't see the point of there being s many of those autovacuum log messages... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Logging pg_autovacuum
I suggest that maybe the cleanest solution is to not use log level at all for this, but to invent a separate autovacuum_verbosity setting that controls how many messages autovac tries to log, using the above scale. Anything it does try to log can just come out at LOG message setting. +1 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
What changed between Try 1 and Try 2? Teodor Sigaev wrote: We (me and Oleg) are glad to present GIN to PostgreSQL. If community will agree, we will commit it to HEAD branch. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GIN - Generalized Inverted iNdex. Try 2.
Oh I can't read - ignore me :) Teodor Sigaev wrote: Changes from previous patch: * add support for tsearch2 * add 'fuzzy' limit * fixes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Checking assumptions
I havn't been able to find any more serious issues in the Coverity report, now that they've fixed the ereport() issue. A number of the issues it complains about are things we already Assert() for. For the rest, as long as the following assumptions are true we're done (well, except for ECPG). I think they are true but it's always good to check: Everytime someone does this, we fix everything except ECPG. Surely it's time we fixed ECPG as well? Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Google SoC--Idea Request
I think Martin Oosterhout's nearby email on coverity bug reports might make a good SoC project, but should it also be added to the TODO list? I may as well put up phpPgAdmin for it. We have plenty of projects available in phpPgAdmin... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] phpPgAdmin looking for developers
Hi, The phpPgAdmin project has been pretty quiet for some time now. We have decided to try to build up our developer base again by recruiting some new, interested, PHP developers. The core team is still around to offer guidance, suggestions, releases, etc. however no-one seems to have much time to code anymore. If you are keen to work on a well-designed open-source PHP application, and expand your knowledge of PostgreSQL, please contact me and I'll find something for you to get started with. Regards, Chris Kings-Lynne phpPgAdmin Project Lead ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] phpPgAdmin looking for developers
Oooh. Based on emails I've received I should point out that phpPgAdmin is a FREE, VOLUNTEER project! It's not a paid job offer! Christopher Kings-Lynne wrote: Hi, The phpPgAdmin project has been pretty quiet for some time now. We have decided to try to build up our developer base again by recruiting some new, interested, PHP developers. The core team is still around to offer guidance, suggestions, releases, etc. however no-one seems to have much time to code anymore. If you are keen to work on a well-designed open-source PHP application, and expand your knowledge of PostgreSQL, please contact me and I'll find something for you to get started with. Regards, Chris Kings-Lynne phpPgAdmin Project Lead ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
I need a rownum column, like Oracle. I have searched the mailing lists and I don't see a satisfactory solution, so I was wondering write a UDF to implement it, the requirements are: +1 I would _love_ to see rownums in PostgreSQL :) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] commit callback, request
The only solution I know if is this patch: http://gorda.di.uminho.pt/community/pgsqlhooks/ Chris Pavel Stehule wrote: Hello Is possible make transaction commit trigger without patching code now? I finding way , but all usable interfaces are static. I remember on diskussion about it and about changes in LISTEN/NOTIFY implementation. Is there any progress? I need it for simulation of Oracle dbms_alert.signal function. Whole dbms_alert package is similar our LISTEN/NOTIFY. Difference is dbms_alert is server side solution and L/N is client side. Is any chance so this interface will be in 8.2? Regards Pavel Stehule _ Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixing domain input
I'm glad to see work being done on domains. I'm definitely learning from the discussion. I wonder if we should implement 'GRANT USAGE ON DOMAINS' for spec compliance sometime... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL Bypass for indexes
Martin's proposal at least looks sensible; he just hasn't quite made the case that it's worth doing. If you're running a system that hardly ever crashes, you might be willing to accept index rebuilds during crash recovery, especially for indexes on relatively small, but frequently updated, tables (which should have reasonably short rebuild times). Obviously this would have to be configurable per-index, or at least per-table, and I agree that it likely would never be the default. But it could be a good tradeoff for some cases. My web system hasn't crashed in years, and last time I upgraded the index rebuild time was maybe 30 mins? So, I think a typical web application doesn't _really_ have that much data, and would greatly benefit from cranking the TPS. Chris ---(end of broadcast)--- TIP 1: 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] Static build of psql with readline support
To the GP, adding -lncurses (or rather the static equivalent) to your
link line should solve it. But if you include any other libraries like
ssl or kerberos be prepared to add a lot more.
With -lncurses or -lcurses I still can't get this to work. I add it to
the ${CC} line, right?
Chris
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Static build of psql with readline support
What is the virtue of this in any case? I can see considerable use for a statically linked pg_dump, to help with upgrading, but not too much for statically linked anything else, especially since we are now pretty relocatable on most platforms at least. Upgraded db server to 8.1, but don't want to upgrade client library on 3 webservers to 8.1. Reason being I'll have to end up rebuilding PHP and more downtime and then new version of libtool, autoconf, etc. and anything else FreeBSD ports decides it needs. So, I just put static versions of pg_dump, pg_dumpall and psql on the webservers in /usr/local/bin so that those machines can still usefully talk to the db server from the CLI. In particular, I can restore dumps containing dollar quotes, plus get new psql features and 8.1 dumps. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Worthwhile optimisation of position()?
Is it worth allowing this:
select count(*) from users_users where position('ch' in username) = 0;
To be able to use an index, like:
select count(*) from users_users where username like 'ch%';
At the moment the position() syntax will do a seqscan, but the like
syntax will use an index.
Chris
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Worthwhile optimisation of position()?
Yeah. AFAICS the transformation Chris suggested is valid. I'm really dubious that it's worth expending planner cycles to look for it though. LIKE is something that everybody and his brother uses, but who uses this position()=0 locution? One of our junior developers :) Which is why I noticed it. Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Worthwhile optimisation of position()?
The docs are correct so my initial point was correct. position('ch' in
user) = 0 is equivalent to user NOT LIKE '%ch%' and there's no way
you can index that.
Well = 1 then.
Chris
---(end of broadcast)---
TIP 1: 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] Accessing schema data in information schema
Hm, good point. We could put 'em in pg_sequence, except that most of
the operations on pg_sequence rows will be nontransactional, and that
doesn't seem to square nicely with transactional updates on ACLs.
Maybe we need two catalogs just to separate the transactional and
nontransactional data for a sequence? Ugh.
Is it possible to have an SRF that can peek into the lastval data and
present it, and make no changes to our catalogs at all?
Or can't we use in the schema view something like:
CREATE VIEW sequences AS
SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
CAST(nc.nspname AS sql_identifier) AS sequence_schema,
CAST(c.relname AS sql_identifier) AS sequence_name,
(SELECT seq_info('sequence_name', 'max')) AS maximum_value,
(SELECT seq_info('sequence_name', 'min')) AS minimum_value,
(SELECT seq_info('sequence_name', 'inc')) AS increment,
(SELECT seq_info('sequence_name', 'cycle')) AS cycle_option
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind = 's';
Chris
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] Static build of psql with readline support
Hi guys,
I've been trying to build the cvs checkout of 8.1.3 on my freebsd 4.9
box with a STATIC psql utility. I keep getting failures trying to hook
in libreadline I think:
lreadline -lcrypt -lcompat -lm -lutil -o psql
/usr/lib/libreadline.a(terminal.o): In function `_rl_get_screen_size':
terminal.o(.text+0x84): undefined reference to `tgetnum'
terminal.o(.text+0xdd): undefined reference to `tgetnum'
/usr/lib/libreadline.a(terminal.o): In function `rl_resize_terminal':
terminal.o(.text+0x1ce): undefined reference to `tgetstr'
/usr/lib/libreadline.a(terminal.o): In function `_rl_init_terminal_io':
terminal.o(.text+0x2c6): undefined reference to `tgetent'
terminal.o(.text+0x4a9): undefined reference to `tgetflag'
...more...
It builds fine if I use --disable-readline, but other than that I simply
can't get it to build. I've done gmake distclean, reconfigured, etc.
How do I get this to work? I've basically just added '-static' to the
psql Makefile, eg: 'psql: ${CC} -static ...'
I can build static pg_dump and pg_dumpall just fine (they don't use
readline though of course.)
Chris
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] pg_freespacemap question
The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deleting loid from the database
contrib/vacuumlo perhaps? Michael Fuhr wrote: On Sat, Mar 04, 2006 at 12:08:52PM +0530, Md.Abdul Aziz wrote: I am a presently working on a module which enhances postgre to store audio files,while storing the aduido file in the databese i used liod,now the problem is i am able to unlink but still the data is present in the postgre database.can some one suggest me how to delete (not unlink) large objects from the postgre databse. VACUUM FULL pg_largeobject might be what you're looking for, but if you're going to reload the data then an ordinary VACUUM (without FULL) will free the space for re-use by PostgreSQL without shrinking the file (unless the table has no live tuples, in which case the file size will be zeroed). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char and i18n
E - Era name (like, Japanese Imperial) (kind of pointless) EE - Full era name Some stuff here: http://java.sun.com/javase/6/docs/guide/intl/calendar.doc.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ipcclean in 8.1 broken?
No-one has a comment on this? Christopher Kings-Lynne wrote: I just tried using ipcclean in 8.1.3. It doesn't work when I su to the pgsql user. This part of the script: if [ $USER = 'root' -o $LOGNAME = 'root' ] Always fails because even tho $USER is set to 'pgsql' when su'ed, $LOGNAME is still root. This is on FreeBSD 4.9 Chris ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ipcclean in 8.1 broken?
if [ $USER = 'root' -o $LOGNAME = 'root' ] Always fails because even tho $USER is set to 'pgsql' when su'ed, $LOGNAME is still root. This is on FreeBSD 4.9 It seems to work on Linux; apparently there are different behaviors of su. Do you have a suggestion for resolving this? Well all I did to fix it on FreeBSD was to remove the '-o $LOGNAME = 'root'' bit... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ipcclean in 8.1 broken?
I wonder if there could be a potential problem with using this approach - checking on $USER == root. Although it is a common practice, I think a superuser does not have to be root. Yes, like the 'toor' account in FreeBSD... (disabled by default though) Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] character encoding in StartupMessage
I could not find anything in the Frontend/Backend protocol docs about character encoding in the StartupMessage. Assuming it is legal for a database or user name to have unicode characters, how is this handled when nothing yet has been said about the client encoding? A similar badness is that if you issue CREATE DATABASE from a UTF8 database, the dbname will be stored as UTF8. Then, if you go to a LATIN1 database and create another it will be stored as LATIN1. Then, it's impossible to display both database names on the same screen or webpage as they have different encodings... Not only that but it's impossible to know what encoding it IS in since it's the encoding of the database from where you issued the CREATE DATABASE instruction from, not the encoding of the database itself. Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] ipcclean in 8.1 broken?
I just tried using ipcclean in 8.1.3. It doesn't work when I su to the pgsql user. This part of the script: if [ $USER = 'root' -o $LOGNAME = 'root' ] Always fails because even tho $USER is set to 'pgsql' when su'ed, $LOGNAME is still root. This is on FreeBSD 4.9 Chris ---(end of broadcast)--- TIP 1: 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] character encoding in StartupMessage
I don't see any very nice solution at the moment. Once we get support for per-column locales, it might be possible to declare that the shared catalogs are always in UTF8 encoding and get the necessary conversions to happen automatically. At the very least, could we always convert dbnames and store them as their own encoding? That way at least in HTML you can probably mark them out as having particular encodings or something... Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] User privileges-verification required
In my opinion we should cater for such a situation, and two possible solutions come to my mind for this: I've done exactly this before, and had to use single user mode to recover. Annoying. 1. Place a restriction that there should be more than one superuser before you can issue a NOCREATEUSER command. I agree :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign keys for non-default datatypes
No, there's no need for that. It means that the RI stuff would have to take whatever steps we agree on to determine the exact comparison operator to use, and then be sure to emit SQL that will select exactly that operator --- this involves using the OPERATOR(foo.=) syntax to remove schema-ambiguity and possibly adding explicit type coercions of the operands. This'll make the RI queries noticeably uglier, but they're not meant to be read by humans anyway. I think it wouldn't be any slower, because OPERATOR() syntax will suppress a search-path search that the parser would otherwise make for the operator --- but in any case, since the plan result is cached, a few microseconds here or there won't matter. Incidentally, shouldn't the existing RI queries (eg. SELECT ... FOR SHARE) explicity specify operator(pg_catalog.=)? Or are they safe from that for some other reason? Chris ---(end of broadcast)--- TIP 1: 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] [PERFORM] Need pointers to standard pg database(s) for testing
Not really, but you can check out the sample databases project: http://pgfoundry.org/projects/dbsamples/ Chris Ron wrote: I assume we have such? Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] Need pointers to standard pg database(s) for
Relating to this. If anyone can find govt or other free db's and convert them into pgsql format, I will host them on the dbsamples page. The dbsamples are _really_ popular! Chris Scott Marlowe wrote: On Fri, 2006-02-17 at 10:51, Ron wrote: I assume we have such? Depends on what you wanna do. For transactional systems, look at some of the stuff OSDL has done. For large geospatial type stuff, the government is a good source, like www.usgs.gov or the fcc transmitter database. There are other ones out there. Really depends on what you wanna test. ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 1: 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] qsort again (was Re: [PERFORM] Strange Create Index behaviour)
Ouch! That confirms my problem. I generated the random test case because it was easier than including the dump of my tables, but you can appreciate that tables 20 times the size are basically crippled when it comes to creating an index on them. I have to say that I restored a few gigabyte dump on freebsd the other day, and most of the restore time was in index creation - I didn't think too much of it though at the time. FreeBSD 4.x. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Blog post on EnterpriseDB...maybe off topic
http://www.flamingspork.com/blog/2006/02/16/enterprisedb-where-is-the-source/ Any comments on this? Is he referring to EnterpriseDB extensions that they don't make public? Chris ---(end of broadcast)--- TIP 1: 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] Feature request - Add microsecond as a time unit for
This generalizes to any scale factor you care to use, eg fortnights... so I don't see a pressing need to add microseconds. Perhaps an argument for adding microseconds to interval declarations is that you can extract them using extract()... Those two lists of allowed scales should be the same, no? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming re-releases
[OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] And it's days like these that make me happy to be running Debian. My thanks go to Martin for his excellent work. Heh don't log into #postgresql then - we have all pretty much been convinced after years of newbie support that Debian is the son of the devil when it comes to PostgreSQL :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
This would apply to only a single relation, so would be just as efficient a write to the database as to WAL. The proposed route is to sync to the database, but not to WAL, thus halving the required I/O. Yes, its designed for large data loads. A question - would setting fsync=off while restoring a multi-gig dump (during an upgrade) improve performance? Chris ---(end of broadcast)--- TIP 1: 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] [Bizgres-general] WAL bypass for INSERT, UPDATE and
* Allow WAL logging to be turned off for a table, but the table might be dropped or truncated during crash recovery [walcontrol] Allow tables to bypass WAL writes and just fsync() dirty pages on commit. This should be implemented using ALTER TABLE, e.g. ALTER TABLE PERSISTENCE [ DROP | TRUNCATE | DEFAULT ]. Tables using non-default logging should not use referential integrity with default-logging tables. A table without dirty buffers during a crash could perhaps avoid the drop/truncate. This would be such a sweet feature for website session tables... Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Persistent error
I believe pgAdmin only supports PostgreSQL 7.3 and above. Chris Flavio Caiuby wrote: Dear hackers I have downloaded and instaled pgadim2 (and pgadmin3 corrected for my Windows98 -second edition) .When I try to conect my web server, where I have an AVL program to nurse and inspect an error message comes and I cannot proceed with the inspection. The error is Column datpathdoes not exist . And now? How to proceed ??? Please help me . Flavio Caiuby --São Paulo - Brazil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 8.1.3?
Hey guys, When do you reckon 8.1.3 will be released? That has the massive speedup on GiST index creation, right? I'm planning on a major upgrade soon, but the greatest time in reload is taken up by index creation time, so I'll hang out for 8.1.3. Any ETA? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO-Item: B-tree fillfactor control
If you want it to be dumped by pg_dump (which is debatable IMHO) then it MUST NOT be a syntax extension, it has to be driven by a GUC variable, else we have compatibility problems with the dumps. We just went through this with WITH/WITHOUT OIDS. Compatibility problems? CREATE INDEX isn't an SQL standard command is it? Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
It could read all the SET variables in at startup? Peter Eisentraut wrote: Some time ago, the tab completion code for the SET command was changed to read the list of available settings from the pg_settings table. This means that by the time you're done completing SET TRANSACTION ISOLATION, you've already sent a query and the command will be disallowed. It's not a major issue, but I figured I'd mention it since it confused me a while ago. If someone has an ingenious plan for working around this, let me know. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tab completion of SET TRANSACTION ISOLATION
I believe psql keeps the password in memory. \c seems to be able to change databases without asking for the password again. What if that role has a maximum of one connection, etc.? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Weirdness with =?
I had this code in a script: UPDATE food_foods SET included=true WHERE verification_status = 'I'; UPDATE food_foods SET included=false WHERE verification_status IS NULL; I tried replacing it with: UPDATE food_foods SET included=(verification_status = 'I'); However, that set included to true only where verification_status=I, it didn't set false at all. Why doesn't this work? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Want to add to contrib.... xmldbx
Andrew Dunstan wrote: Michael Fuhr wrote: On Mon, Jan 30, 2006 at 12:20:25PM +0900, Michael Glaesemann wrote: On Jan 30, 2006, at 12:23 , Andrew Dunstan wrote: A nicer idea would be something like a utility could we ship that will download, build and install module foo for you. CPAN modules, Ruby gems, PgFoundry ingots? :) Tusks? (Extensions of the elephant.) Trunks? Dung? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Weird pg_dumpall bug?
I did a dump of a 7.4.11 database using the 8.1.2 pg_dumpall. I got this at the top of the dump: ... ... CREATE ROLE support; ALTER ROLE support WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md5'; ... ... CREATE ROLE support; ALTER ROLE support WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN; ... ... It dumped the support role twice! Any ideas? H...actually. It's because I have a user called 'support' and a group called 'support'. Seems like it needs a fix... Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Cache lookup failed error in tsearch2?
What would be the cause of this error after upgrading from pgsql 7.4 to 8.1?
usatest=# SELECT lexize[1] FROM lexize('en_stem', 'bacon');
ERROR: cache lookup failed for function 861011
Does tsearch2 need to somehow be tweaked after the upgrade?
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Weird pg_dumpall bug?
How about an option to map groups whose names conflict with user names using a prefix mechanism? e.g. --map-conflicting-groups=gr_ Then in Christopher's example his support group would become the role gr_support. No bad, have to change some application code then as well... Chris ---(end of broadcast)--- TIP 1: 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] Surrogate keys (Was: enums)
Yes. Representation of the DNA is probably best. But - that's a lot of data to use as a key in multiple tables. :-) No then you have problems with identical twins :) Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] No heap lookups on index
Oracle does, but you pay in other ways. Instead of keeping dead tuples in the main heap, they shuffle them off to an 'undo log'. This has some downsides: Rollbacks take *forever*, though this usually isn't much of an issue unless you need to abort a really big transaction. It's a good point though. Surely a database should be optimised for the most common operation - commits, rather than rollbacks? Chris ---(end of broadcast)--- TIP 1: 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] Contrib Schemas
Default schema really has to be public to help the newbies out there. All contribs should come with some sort of standard uninstall.sql script though. Chris Mark Kirkwood wrote: David Fetter wrote: Folks, I'm picturing something like this: make install # Option 0 Leave as-is make install --auto_schema [--generate_path_mod] # Option 1 make install --schema=contrib # Option 2 make install --schema=foo # Option 3 What do you think? I like the idea of being able to specify a schema as an option, but would prefer the default schema to be 'public'. i.e. make install [--schema=foo] # Option 4 Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: Minor doc tweak: NOT NULL is
Why? SERIAL implies NOT NULL (although PRIMARY KEY does as well, of course). Ah yes you're right. I mixed up with the fact that SERIAL no longer implies UNIQUE... Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Minor doc tweak: NOT NULL is redundant
I hope you mean 'redundant with PRIMARY KEY in example'... Works out the same way though. Chris Neil Conway wrote: Log Message: --- Minor doc tweak: NOT NULL is redundant with SERIAL in example. Modified Files: -- pgsql/doc/src/sgml/ref: create_domain.sgml (r1.26 - r1.27) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_domain.sgml.diff?r1=1.26r2=1.27) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixing row comparison semantics
I've gotten interested again in the issue of row comparisons, eg (a, b, c) = (1, 2, 3) We've discussed this before, the most comprehensive thread being http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php but nothing's gotten done. Unless someone's already working on this I think I will take it up. Can someone explain to me how: (a, b) (1, 2) is different to a 1 and b 2 ? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixing row comparison semantics
Now, since COLLATE support is still in progress, I'm not sure how much any of this helps you. I'm up to modifying the scankeys but it's hard when you jave to keep rgrepping the tree to work out what is called from where... src/tools/make_ctags is your friend... Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Where are we on this? In general I agree with Tom, but I have no time to do the work. Unless someone has an immediate implementation, I suggest that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt, which is the minimum needed to unbreak Windows builds, while this gets sorted out properly. I had forgotten that the Windows build is broken. I'll see what I can do with throwing together the cleaner-API function. Another question about these encrypted passwords. phpPgAdmin needs to connect to databases that are sometimes on other servers. I use the pg_connect() function to do this. This is passed down to PQconenct() I presume. So, can I specify the password to pg_connect() as 'md5127349123742342344234'? ie. Can I CONNECT using an md5'd password? Also, does this work for non-md5 host lines on the server, and how can I avoid doing it on older (pre-7.2) PostgreSQL?? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
So, can I specify the password to pg_connect() as 'md5127349123742342344234'? Certainly not. We'd hardly be worrying about obscuring the original password if the encrypted version were enough to get in with. AndrewSN can't post at the moment, but asked me to post this for him: Knowing the md5 hash is enough to authenticate via the 'md5' method in pg_hba.conf, even if you don't know the original password. Admittedly you have to modify libpq to do this, but this isn't going to stop an attacker for more than 5 seconds. I'll add my own note that never sending the cleartext password does not necessarily improve PostgreSQL security, but certainly stops someone who sniffs the password from then using that cleartext password to get into other applications. If all they can get is the md5 hash, then all they can get into is PostgreSQL. Chris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Improving planning of outer joins
I'm not sure whether we'd need any additional planner knobs to control this. I think that the existing join_collapse_limit GUC variable should continue to exist, but its effect on left/right joins will be the same as for inner joins. If anyone wants to force join order for outer joins more than for inner joins, we'd need some other control setting, but I don't currently see why that would be very useful. Does this seem like a reasonable agenda, or am I thinking too small? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
