Re: [HACKERS] [PATCH] Lazy xid assingment V2
Tom Lane wrote: There's also the plan B of scanning pg_class to decide which relfilenode values are legit. IIRC Bruce did up a patch for this about a year ago, which I vetoed because I was afraid of the consequences if it removed data that someone really needed. I posted a patch like that, 2-3 years ago I think. IIRC, the consensus back then was to just write a log message of the stale files, so an admin can go and delete them manually. That's safer than just deleting them, and we'll get an idea of how much of a problem this is in practice; at the moment a DBA has no way to know if there's some leaked space, except doing a manual compare of pg_class and filesystem. If it turns out to be reliable enough, and the problem big enough, we might start deleting the files automatically in future releases. I never got around to fixing the issues with the patch, but it's been tickling me a bit for all these years. Someone just mentioned doing the same thing but pushing the unreferenced files into a trash directory instead of actually deleting them. While that answers the risk-of-data-loss objection, I'm not sure it does much for the goal of avoiding useless space consumption: how many DBAs will faithfully examine and clean out that trash directory? That sounds like a good idea to me. If you DBA finds himself running out of disk space unexpectedly, he'll start looking around. Doing a rm trash/* surely seems easier and safer than deleting individual files from base. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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
[HACKERS] \dF wrt text search
Section 12.1.3. Configurations of the developer docs notes that Fortunately,
PostgreSQL comes with predefined configurations for many languages. (psql's
\dF shows all predefined configurations.) but alas it doesn't seem to.
Welcome to psql 8.3devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \dF
Did not find any relation named F.
postgres=# select version();
version
---
PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3
(Ubuntu 4.0.3-1ubuntu5)
(1 row)
Is this information just wrong, or is it floating around on someone's TODO
list? If it needs to be I could take a whack at it (though perhaps things are
still to in flux to worry about this yet?)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] \dF wrt text search
Robert Treat wrote: Section 12.1.3. Configurations of the developer docs notes that Fortunately, PostgreSQL comes with predefined configurations for many languages. (psql's \dF shows all predefined configurations.) but alas it doesn't seem to. Welcome to psql 8.3devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \dF Did not find any relation named F. postgres=# select version(); version --- PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5) (1 row) Is this information just wrong, or is it floating around on someone's TODO list? If it needs to be I could take a whack at it (though perhaps things are still to in flux to worry about this yet?) works for me(expect for tab complete support but I already posted a patch for that): postgres=# select version(); version --- PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) (1 row) postgres=# \dF List of text search configurations Schema |Name| Description ++--- pg_catalog | danish | Configuration for danish language pg_catalog | dutch | Configuration for dutch language pg_catalog | english| Configuration for english language pg_catalog | finnish| Configuration for finnish language pg_catalog | french | Configuration for french language pg_catalog | german | Configuration for german language pg_catalog | hungarian | Configuration for hungarian language pg_catalog | italian| Configuration for italian language pg_catalog | norwegian | Configuration for norwegian language pg_catalog | portuguese | Configuration for portuguese language pg_catalog | romanian | Configuration for romanian language pg_catalog | russian| Configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish| Configuration for spanish language pg_catalog | swedish| Configuration for swedish language pg_catalog | turkish| Configuration for turkish language (16 rows) Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCH] Lazy xid assingment V2
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Someone just mentioned doing the same thing but pushing the unreferenced files into a trash directory instead of actually deleting them. That sounds like a good idea to me. It suddenly strikes me that there's lots of precedent for it: fsck moves unreferenced files into lost+found, instead of just deleting them. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] \dF wrt text search
Robert Treat [EMAIL PROTECTED] writes: postgres=# \dF Did not find any relation named F. Works for me. When did you last recompile psql? regression=# \dF List of text search configurations Schema |Name| Description ++--- pg_catalog | danish | Configuration for danish language pg_catalog | dutch | Configuration for dutch language pg_catalog | english| Configuration for english language pg_catalog | finnish| Configuration for finnish language pg_catalog | french | Configuration for french language pg_catalog | german | Configuration for german language pg_catalog | hungarian | Configuration for hungarian language pg_catalog | italian| Configuration for italian language pg_catalog | norwegian | Configuration for norwegian language pg_catalog | portuguese | Configuration for portuguese language pg_catalog | romanian | Configuration for romanian language pg_catalog | russian| Configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish| Configuration for spanish language pg_catalog | swedish| Configuration for swedish language pg_catalog | turkish| Configuration for turkish language (16 rows) regression=# (BTW, now that I look at this: any objection to de-capitalizing the descriptions? Most other built-in object descriptions don't have any caps.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCH] Lazy xid assingment V2
Tom Lane wrote: There's also the plan B of scanning pg_class to decide which relfilenode values are legit. IIRC Bruce did up a patch for this about a year ago, which I vetoed because I was afraid of the consequences if it removed data that someone really needed. Someone just mentioned doing the same thing but pushing the unreferenced files into a trash directory instead of actually deleting them. While that answers the risk-of-data-loss objection, I'm not sure it does much for the goal of avoiding useless space consumption: how many DBAs will faithfully examine and clean out that trash directory? For the admin who for some reason deletes critical input data before seeing a COMMIT return from postgresql they can probably keep the files. The thing is, the leak occurs in situation where a COMMIT hasn't returned to the user, so we are trying to guarantee no data-loss even when the user doesn't see a successful commit? That's a tall order obviously and hopefully people design their apps to attend to transaction success / failure. Plan B certainly won't take more space, and is probably the easiest to cleanup. ---(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] GIST and GIN indexes on varchar[] aren't working in CVS.
Gregory Maxwell [EMAIL PROTECTED] writes: There seems to be some behavior change in current CVS with respect to gist and gin indexes on varchar[]. Some side effect of the tsearch2 merge? I think more likely I broke it during the opfamily rewrite :-(. Looks like I left out entries for _varchar (and _cidr too) thinking that the binary-compatible functions in the same opfamily for _text and _inet would serve ... but they won't, because arrays are never really binary compatible (you have to at least substitute the other element type OID). Will fix. regards, tom lane ---(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] Per-function search_path = per-function GUC settings
On 9/2/07, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: On 9/1/07, Tom Lane [EMAIL PROTECTED] wrote: One problem is that we'd have to make CURRENT a reserved word to make it work exactly like that. Can anyone think of a variant syntax that doesn't need a new reserved word? SET var FROM CURRENT SESSION Seems a little verbose, but maybe we could do SET var FROM CURRENT or SET var FROM SESSION? I'd prefer FROM SESSION then. FROM CURRENT seems unclear. One point worth noting here is that this'd more or less automatically apply to ALTER USER SET and ALTER DATABASE SET as well ... not sure how much use-case there is for those, but it'd fall out ... Does not seem to be a problem. -- marko ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Per-function GUC settings: trickier than it looked
Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: We could perhaps get away with defining that as being the behavior, but it doubtless will surprise someone sometime. What *should* these interactions be like, and has anyone got an idea how to implement their suggestion? What will happen if you have two functions, foo and bar, were the search-path is overridden for foo, and foo calls bar. I guess bar would be executed with foo's overridden searchpath. Thats seems a bit surprising - I think it's correct; if bar doesn't SET a search_path then it should use the caller's. I thought a bit more about this and there are at least some cases we can probably agree on without trouble: * If a transaction or subtransaction aborts, all GUC changes made within it disappear, whether they're from per-function GUC attributes or SET commands. This seems clearly correct. So we need only consider cases where no error occurs. * A regular SET (without LOCAL) propagates clear out to the top level and becomes the session setting, if not aborted. Hence it must/will override any per-function settings, either in its own function or callers. So it seems that only SET LOCAL within a function with per-function GUC settings is at issue. I think that there is a pretty strong use-case for saying that if you have a per-function setting of a particular variable foo, then any SET LOCAL foo within the function ought to vanish at function end --- for instance a function could want to try a few different search_path settings and automatically revert to the caller's setting on exit. The question is what about SET LOCAL on a variable that *hasn't* been explicitly SET by the function definition. Either approach we take with it could be surprising, but probably having it revert at function end is more surprising... I notice BTW that we have never updated the SET reference page since subtransactions were introduced --- it still says only that SET LOCAL is local to the current transaction, without a word about subtransactions. So we have a documentation problem anyway. I recall that we had some discussion during the 8.0 dev cycle about whether having SET LOCAL's effects end at the end of the current subtransaction was really a good idea, given that subtransactions aren't the conceptual model the SQL spec defines, but nothing was ever done about changing the implementation. In fact, our current recommendation for implementing secure SECURITY DEFINER functions (use SET LOCAL to change search_path) really depends on that nowhere-documented behavior ... so it's probably too late to consider changing it now. But this would be the time, if we ever are going to reconsider it. Comments? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Per-function search_path = per-function GUC settings
Marko Kreen [EMAIL PROTECTED] writes: On 9/2/07, Tom Lane [EMAIL PROTECTED] wrote: Seems a little verbose, but maybe we could do SET var FROM CURRENT or SET var FROM SESSION? I'd prefer FROM SESSION then. FROM CURRENT seems unclear. Actually, I think FROM SESSION is unclear, as it opens the question whether the value to be applied is the session-wide setting or the currently active one. Inside a transaction that has done SET LOCAL, these are different things. I think we pretty clearly want to have it take the currently active setting, and I'd vote for FROM CURRENT as the best way of expressing that. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Per-function search_path = per-function GUC settings
On Sun, 2007-09-02 at 12:11 -0400, Tom Lane wrote: I think we pretty clearly want to have it take the currently active setting, and I'd vote for FROM CURRENT as the best way of expressing that. FROM CURRENT sounds good to me. Another idea (just brainstorming): SET var AS CURRENT. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Per-function search_path = per-function GUC settings
On Sat, 2007-09-01 at 15:03 -0400, Tom Lane wrote: ALTER FUNCTION func(args) SET var TO CURRENT; Hmmm ... that's certainly do-able, though I'm not sure how much it helps the use-case you suggest. The search path still has to be set at the top of the module script, no? It gives some better options for module authors and people installing those modules: 1. Set it at the top of the file, in one place 2. Connect as the user whose schema you want to install into, i.e.: $ psql my_db jdavis module_install.sql 3. prepend the SET search_path=foo to the input of psql, i.e.: $ echo SET search_path=foo; | cat module_install.sql | psql my_db ..or $ psql my_db = SET search_path=foo; = \i module_install.sql Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Hash index todo list item
Dear PostgreSQL Hackers: After following the hackers mailing list for quite a while, I am going to start investigating what will need to be done to improve hash index performance. Below are the pieces of this project that I am currently considering: 1. Characterize the current hash index implementation against the BTree index, with a focus on space utilization and lookup performance against a collection of test data. This will give a baseline performance test to evaluate the impact of changes. I initially do not plan to bench the hash creation process since my initial focus will be on lookup performance. 2. Evaluate the performance of different hash index implementations and/or changes to the current implementation. My current plan is to keep the implementation as simple as possible and still provide the desired performance. Several hash index suggestions deal with changing the layout of the keys on a page to improve lookup performance, including reducing the bucket size to a fraction of a page or only storing the hash value on the page, instead of the index value itself. My goal in this phase is to produce one or more versions with better performance than the current BTree. 3. Look at build time and concurrency issues with the addition of some additional tests to the test bed. (1) 4. Repeat as needed. This is the rough plan. Does anyone see anything critical that is missing at this point? Please send me any suggestions for test data and various performance test ideas, since I will be working on that first. Regards, Ken Marshall ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Per-function search_path = per-function GUC settings
On Sat, 2007-09-01 at 13:55 -0400, Tom Lane wrote: You already have that issue with respect to the default public execute permissions on the function. The standard solution is to do it in a transaction block --- then no one can even see the function until you commit. It might be a good idea to have the ability to revoke privileges at CREATE FUNCTION time also. That could clutter up the CREATE FUNCTION syntax, but would offer an opportunity to document the danger of default public execute in a SECURITY DEFINER function. Something like: CREATE FUNCTION ... LANGUAGE plpgsql SECURITY DEFINER REVOKE EXECUTE FROM PUBLIC; Even if we don't do that, we should at least document your standard solution here: http://www.postgresql.org/docs/8.2/static/sql-createfunction.html It is already documented here: http://www.postgresql.org/docs/8.2/static/sql-grant.html But the CREATE FUNCTION page has a section titled Writing SECURITY DEFINER Functions Safely, so I think it's useful to have it there, too. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Attempt to stop dead instance can stop a random process?
On Fri, Aug 31, 2007 at 3:10 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Hmm. Do I correctly grasp the picture that you've got several Postgres installations on the machine and they're all booted by startup scripts? In this situation, it's actually not a bad idea to run each one under a separate userid. The problem is that in successive reboots, each postmaster will typically get almost but not exactly the same PID as last time, since the number of processes launched earlier in system startup is mostly but not completely deterministic. If you start all the postmasters together, as you probably do, then there will be occasions when one gets a PID that another one had in the previous boot cycle. That can lead to refusal to start up: if a postmaster sees a postmaster lock file in its data directory, containing a PID that belongs to another live process owned by the same userid, it has to assume that that's a conflicting postmaster and it must respect the lock file. You can prevent that problem if each postmaster (data directory) belongs to a different userid. I was thinking of submitting a patch to add a recommendation to this effect to section 16.1 (The PostgreSQL User Account) in the documentation. Does that seem appropriate to all? I'm not sure whether it would be worth changing 16.2 (Creating a Database Cluster) to say while logged into the PostgreSQL user account which you have chosen for the cluster. (Some people prefer to fix this by having a startup script that forcibly removes all the lockfiles before launching the postmasters. I think that's kinda risky, although if it's done in a separate script that you'd have no reason to run by hand, it's probably OK. Clueless folks put the action right in the postgresql start script, meaning that a thoughtless service postgresql start blows away the lock file...) Would it be a good idea to mention pid file cleanup strategies in section 16.3 (Starting the Database Server) where pid files are discussed, or isn't that something we should get into in the docs? Is there anywhere in the documentation to describe common causes and solutions for messages such as these (from the log file)?: [2007-09-02 11:47:14.697 CDT] 7910 FATAL: lock file postmaster.pid already exists [2007-09-02 11:47:14.697 CDT] 7910 HINT: Is another postmaster (PID 7760) running in data directory /var/pgsql/data/county/dunn/data? [2007-09-02 14:45:28.541 CDT] 21735 FATAL: lock file /tmp/.s.PGSQL.5417.lock already exists [2007-09-02 14:45:28.541 CDT] 21735 HINT: Is another postmaster (PID 7760) using socket file /tmp/.s.PGSQL.5417? -Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] tsearch filenames unlikes special symbols and numbers
Hello I am found small bug postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell, DictFile= 'cs_czutf'); ERROR: invalid text search configuration file name cs_czutf postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell, DictFile= 'csczutf8'); ERROR: invalid text search configuration file name csczutf8 postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell, DictFile= csczutf8); ERROR: invalid text search configuration file name csczutf8 postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell, DictFile= cs_czutf); ERROR: invalid text search configuration file name cs_czutf postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell, DictFile= csczutf); ERROR: could not open dictionary file /usr/local/pgsql/share/tsearch_data/csczutf.dict: není souborem ani adresářem regards Pavel Stehule ---(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] synchronous_commit: Developer's View
On Thu, 2007-08-30 at 21:00 -0400, Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: ... So at least for the pl/pgsql case, it seems easy enough to temporarily change GUCs already. For other PLs, things might be different though - I wouldn't know, I have never really used them... It's definitely possible, but it's inconvenient and slow (slow because you have to run a subtransaction, which ain't cheap). I think Simon might have a good point about generalizing the proposed set the search path facility to instead be set any GUC for the duration of this function. He's definitely all wet about the usefulness of that for synchronous_commit, but as Greg pointed out, there are other GUCs besides search_path that can break a function's expectations. Not too sure what all wet means, but the imagery is great. :-) As I said, I'm looking for a way to decorate a specific transaction without changing application code. Setting it on a function works fine as long as the function was invoked as a top-level procedure call in its own implicit transaction, which is common usage. Clearly, this doesn't really make sense for non-procedural functions such as md5(), though it does for things like record_vehicle_position() or ad_impression(). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch filenames unlikes special symbols and numbers
I just tried on CVS HEAD and seems something is broken postgres=# CREATE TEXT SEARCH DICTIONARY ru_ispell ( TEMPLATE = ispell, DictFile = russian-utf8.dict, AffFile = russian-utf8.aff, StopWords = russian ); ERROR: syntax error at or near - LINE 3: DictFile = russian-utf8.dict, postgres=# CREATE TEXT SEARCH DICTIONARY ru_ispell ( TEMPLATE = ispell, DictFile = 'russian-utf8.dict', AffFile = 'russian-utf8.aff', StopWords = russian ); ERROR: invalid text search configuration file name russian-utf8.dict Honestly speaking, I have no time to follow constantly changed syntax, but documentation http://momjian.us/main/writings/pgsql/sgml/sql-createtsdictionary.html doesn't make clear what's wrong. Also, I'm wondering do we really need to show all schemas without text search configurations defined ? Looks rather stranger. postgres=# \dF List of text search configurations Schema |Name| Description ++--- information_schema || pg_catalog | danish | Configuration for danish language pg_catalog | dutch | Configuration for dutch language pg_catalog | english| Configuration for english language pg_catalog | finnish| Configuration for finnish language pg_catalog | french | Configuration for french language pg_catalog | german | Configuration for german language pg_catalog | hungarian | Configuration for hungarian language pg_catalog | italian| Configuration for italian language pg_catalog | norwegian | Configuration for norwegian language pg_catalog | portuguese | Configuration for portuguese language pg_catalog | romanian | Configuration for romanian language pg_catalog | russian| Configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish| Configuration for spanish language pg_catalog | swedish| Configuration for swedish language pg_catalog | turkish| Configuration for turkish language pg_temp_1 || pg_toast || pg_toast_temp_1|| public || (21 rows) Another problem I see are broken examples of dictionary and parser in documentation: http://momjian.us/main/writings/pgsql/sgml/textsearch-rule-dictionary-example.html http://momjian.us/main/writings/pgsql/sgml/textsearch-parser-example.html Include files in dictionary example are now in tsearch directory: #include tsearch/ts_locale.h #include tsearch/ts_public.h #include tsearch/ts_utils.h I didn't test parser example. Oleg PS. Sorry, I miss last syntax changes, but I really don't understand parenthesis and commas usage in SQL. It's so strange. I remember Peter raised an objections at the very beginning. On Sun, 2 Sep 2007, Pavel Stehule wrote: Hello I am found small bug postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell,DictFile= 'cs_czutf');ERROR: invalid text search configuration file name cs_czutfpostgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell,DictFile= 'csczutf8');ERROR: invalid text search configuration file name csczutf8postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell,DictFile= csczutf8);ERROR: invalid text search configuration file name csczutf8postgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell,DictFile= cs_czutf);ERROR: invalid text search configuration file name cs_czutfpostgres=# CREATE TEXT SEARCH DICTIONARY cz1(TEMPLATE = ispell,DictFile= csczutf);ERROR: could not open dictionary file/usr/local/pgsql/share/tsearch_data/csczutf.dict: nen? souborem aniadres??em regardsPavel Stehule Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] tsearch filenames unlikes special symbols and numbers
Oleg Bartunov [EMAIL PROTECTED] writes: postgres=# CREATE TEXT SEARCH DICTIONARY ru_ispell ( TEMPLATE = ispell, DictFile = 'russian-utf8.dict', AffFile = 'russian-utf8.aff', StopWords = russian ); ERROR: invalid text search configuration file name russian-utf8.dict I made it reject all but latin letters, which is the same restriction that's in place for timezone set filenames. That might be overly strong, but we definitely have to forbid . and / (and \ on Windows). Do we want to restrict it to letters, digits, underscore? Or does it need to be weaker than that? Also, I'm wondering do we really need to show all schemas without text search configurations defined ? Looks rather stranger. Um ... I don't see that; I get regression=# \dF List of text search configurations Schema |Name| Description ++--- pg_catalog | danish | Configuration for danish language pg_catalog | dutch | Configuration for dutch language pg_catalog | english| Configuration for english language pg_catalog | finnish| Configuration for finnish language pg_catalog | french | Configuration for french language pg_catalog | german | Configuration for german language pg_catalog | hungarian | Configuration for hungarian language pg_catalog | italian| Configuration for italian language pg_catalog | norwegian | Configuration for norwegian language pg_catalog | portuguese | Configuration for portuguese language pg_catalog | romanian | Configuration for romanian language pg_catalog | russian| Configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish| Configuration for spanish language pg_catalog | swedish| Configuration for swedish language pg_catalog | turkish| Configuration for turkish language (16 rows) Are you sure you're using CVS-head psql? Another problem I see are broken examples of dictionary and parser in documentation: http://momjian.us/main/writings/pgsql/sgml/textsearch-rule-dictionary-example.html http://momjian.us/main/writings/pgsql/sgml/textsearch-parser-example.html Yeah, I wanted to discuss that with you. Code examples in sgml docs are a bad idea: they're impossible to use as actual templates, because of all the weird markup changes, and there's no easy way to notice if they're broken. It would be better to remove these from the docs and set them up as contrib modules. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix brain fade in DefineIndex(): it was continuing to access the
[EMAIL PROTECTED] wrote: Hi Tom and Andrew, On Thu, 30 Aug 2007, Tom Lane wrote: While waiting for my application for another animal, I made some tests and was surprised that cluster test failed with an ordering error. This is running with CLOBBER_CACHE_ALWAYS set, right? I think it is quite possible that an autovacuum came and processed the catalog, leading to different ordering. Maybe an ORDER BY is missing in the test query. Yeah, an ORDER BY should be enough. SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname - clstr_tst_pkey clstr_tst_con (2 rows) SELECT relname, relkind, --- 252,259 SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass; conname clstr_tst_con + clstr_tst_pkey (2 rows) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch filenames unlikes special symbols and numbers
Tom Lane [EMAIL PROTECTED] writes: Oleg Bartunov [EMAIL PROTECTED] writes: postgres=# CREATE TEXT SEARCH DICTIONARY ru_ispell ( TEMPLATE = ispell, DictFile = 'russian-utf8.dict', AffFile = 'russian-utf8.aff', StopWords = russian ); ERROR: invalid text search configuration file name russian-utf8.dict I made it reject all but latin letters, which is the same restriction that's in place for timezone set filenames. That might be overly strong, but we definitely have to forbid . and / (and \ on Windows). Do we want to restrict it to letters, digits, underscore? Or does it need to be weaker than that? What's the problem with .? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch filenames unlikes special symbols and numbers
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: I made it reject all but latin letters, which is the same restriction that's in place for timezone set filenames. That might be overly strong, but we definitely have to forbid . and / (and \ on Windows). Do we want to restrict it to letters, digits, underscore? Or does it need to be weaker than that? What's the problem with .? ../../../../etc/passwd Possibly we could allow '.' as long as we forbade /, but the other trouble with allowing . is that it encourages people to try to specify the filetype suffix (as indeed Oleg was doing). I'd prefer to keep the suffixes out of the SQL object definitions, with an eye to possibly someday migrating all the configuration data inside the database. There's a reasonable argument for restricting the names used for these things in the SQL definitions to be valid SQL identifiers, so that that will work nicely... regards, tom lane ---(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] Re: [COMMITTERS] pgsql: Fix brain fade in DefineIndex(): it was continuing to access the
Alvaro Herrera [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: While waiting for my application for another animal, I made some tests and was surprised that cluster test failed with an ordering error. This is running with CLOBBER_CACHE_ALWAYS set, right? I think it is quite possible that an autovacuum came and processed the catalog, leading to different ordering. I've seen this exact ordering difference once or twice before but hadn't got round to looking into the cause. I think Alvaro is right though, because what I see in pg_constraint after a typical serial regression test is ctid |conname +--- (0,1) | cardinal_number_domain_check (0,5) | check_con (0,6) | sequence_con (0,7) | insert_con (0,8) | insert_tbl_check (0,9) | rule_and_refint_t1_pkey (0,10) | rule_and_refint_t2_pkey (0,11) | rule_and_refint_t3_pkey (0,12) | rule_and_refint_t3_id3a_fkey (0,13) | rule_and_refint_t3_id3a_fkey1 (1,1) | copy_con (1,10) | foo (1,11) | inhx_pkey (3,4) | clstr_tst_s_pkey (3,5) | clstr_tst_pkey (3,6) | clstr_tst_con (3,26) | con_check (4,2) | str_domain2_check (4,3) | pos_int_check (19 rows) The planner seems to prefer to do the query at issue by seqscan, regardless of whether pg_constraint has been vacuumed/analyzed lately. So the result will depend on where these two rows get dropped. As you can see, page 2 is entirely empty, so we could see the reported result if clstr_tst_pkey went into page 3 and then an autovacuum reported page 2 as having free space before the clstr_tst_con row was inserted. This is a sufficiently narrow window to be unlikely, but not impossible; and it's easy to believe that CLOBBER_CACHE_ALWAYS could widen the window. ORDER BY added, as suggested by Alvaro. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] \dF wrt text search
On Sunday 02 September 2007 10:29, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
postgres=# \dF
Did not find any relation named F.
Works for me. When did you last recompile psql?
Blah I compiled last night, using the latest snapshot in the
postgresql/dev/ directory in ftp, which, as I look now, has a date listed of
2007-08-10, and looking in the source the catversion is 200702251, which is
before the tsearch bits hit the tree. So, I think my problems lie in the
snapshot no longer being updated :-\ CCing www in case someone wants to fix
it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] \dF wrt text search
Robert Treat [EMAIL PROTECTED] writes: Blah I compiled last night, using the latest snapshot in the postgresql/dev/ directory in ftp, which, as I look now, has a date listed of 2007-08-10, and looking in the source the catversion is 200702251, which is before the tsearch bits hit the tree. So, I think my problems lie in the snapshot no longer being updated :-\ CCing www in case someone wants to fix it. I'll bet a dollar it got broken in the master-CVS-server move. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-www] [HACKERS] \dF wrt text search
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Should be fixed now, running a manual run of it right now, give it about 15
minutes or so ...
- --On Sunday, September 02, 2007 21:48:36 -0400 Robert Treat
[EMAIL PROTECTED] wrote:
On Sunday 02 September 2007 10:29, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
postgres=# \dF
Did not find any relation named F.
Works for me. When did you last recompile psql?
Blah I compiled last night, using the latest snapshot in the
postgresql/dev/ directory in ftp, which, as I look now, has a date listed of
2007-08-10, and looking in the source the catversion is 200702251, which is
before the tsearch bits hit the tree. So, I think my problems lie in the
snapshot no longer being updated :-\ CCing www in case someone wants to fix
it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
-
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED]
Yahoo . yscrappy Skype: hub.orgICQ . 7615664
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.4 (FreeBSD)
iD8DBQFG228F4QvfyHIvDvMRAoYrAJ0b3cg+Jp0lKI6dw2nkQuwoIpE6XwCfQYEG
6EZdIJghysELcvIrdtoisV8=
=EXzk
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hash index todo list item
Kenneth Marshall [EMAIL PROTECTED] writes: ... This is the rough plan. Does anyone see anything critical that is missing at this point? Sounds pretty good. Let me brain-dump one item on you: one thing that hash currently has over btree is the ability to handle index items up to a full page. Now, if you go with a scheme that only stores hash codes and not the underlying data, you can not only handle that but improve on it; but if you reduce the bucket size and don't remove the data, it'd be a step backward. The idea I had about dealing with that was to only reduce the size of primary buckets --- if it's necessary to add overflow space to a bucket, the overflow units are still full pages. So an index tuple up to a page in size can always be accommodated by adding an overflow page to the bucket. Just a thought, but AFAIR it's not in the archives anywhere. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
