Re: [HACKERS] Problem with site doc search
No, it's still on the TODO. Gevik has also been looking a bit at it (I think - at least he's indicated that he is), and he recently got some nwe parser code to look at to see if we can use to fix it. //Magnus On Wed, Mar 05, 2008 at 10:23:28PM -0500, Bruce Momjian wrote: Has this been addressed? --- Oleg Bartunov wrote: On Tue, 5 Feb 2008, Magnus Hagander wrote: No. It's on the list, but other things around the release haev priority. I just returned from my Europe trip and have many things to do :) //Magnus On Mon, Feb 04, 2008 at 06:43:09PM -0800, Gurjeet Singh wrote: Hi guys any updates on this? Pinging you just so that we do not forget it in the heap of mails in our inboxes. Best regards, On Feb 3, 2008 8:40 AM, Magnus Hagander [EMAIL PROTECTED] wrote: Oleg Bartunov wrote: On Sat, 2 Feb 2008, Gurjeet Singh wrote: Hi All, I just noticed a minor bug in our search results. Searching for is_insteadbool in 8.3 docs returns the following page: http://www.postgresql.org/docs/8.3/static/catalog-pg-rewrite.html is_instead is a column, and bool is the datatype, both mentioned in different columns. I know it is based on postgres' own full text search, but am not sure about the method how docs are read by the search engine. It seems that the problem lies in the the way the doc was read and fed to the index builder, because the initial search results show these two words combined. Also, is it possible to teach our search engine to *not* treat _ (underscore) as a word separator? This would be great help and would result much better results. Sure, there are many ways to do this. Magnus ? Which way would you recommend? You're the tsearch master ;-) //Magnus -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17? 29' 34.37N, 78? 30' 59.76E - Hyderabad 18? 32' 57.25N, 73? 56' 25.42E - Pune 37? 47' 19.72N, 122? 24' 1.69 W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate 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 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] 8.3.0 Core with concurrent vacuum fulls
On Wed, Mar 5, 2008 at 9:29 PM, Tom Lane [EMAIL PROTECTED] wrote: [ thinks some more... ] I guess we could use a flag array dimensioned MaxHeapTuplesPerPage to mark already-processed tuples, so that you wouldn't need to search the existing arrays but just index into the flag array with the tuple's offsetnumber. We can actually combine this and the page copying ideas. Instead of copying the entire page, we can just copy the line pointers array and work on the copy. ISTM that the only place where we change the tuple contents is when we collapse the redirected line pointers and that we can do at the end, on the original page. The last step which we run inside a critical section would then be just like invoking heap_xlog_clean with the information collected in the first pass. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
[HACKERS] Behaviour of to_tsquery(stopwords only)
I'm not sure what value a tsquery has if it's composed from stopwords only, but it doesn't seem to be null or equal to itself. That strikes me as ... unintuitive, although I'm happy to be re-educated on this. I think it's because CompareTSQ (tsquery_op.c, line 142) doesn't have a case to handle query sizes of zero. That's what seems to be returned from tsearch/to_tsany.c lines ~ 345-350. SELECT qid,words,query, (query is null) AS isnull, (query = to_tsquery(words)) as issame FROM util.queries ORDER BY qid DESC LIMIT 5; NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored qid | words | query| isnull | issame --+--+++ 1000 | to || f | f 999 | or || f | f 998 | requests | 'request' | f | t 997 | site | 'site' | f | t 996 | document | 'document' | f | t (5 rows) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Behaviour of to_tsquery(stopwords only)
Further tsquery comparison fun: = SELECT q.qid, q.query, count(*) FROM doc.documents d, util.queries q WHERE d.words @@ q.query AND (q.query::text=$$'tender'$$) GROUP BY q.qid, q.query ; qid | query | count -+--+--- 195 | 'tender' | 374 248 | 'tender' | 374 257 | 'tender' | 374 332 | 'tender' | 374 401 | 'tender' | 374 409 | 'tender' | 374 519 | 'tender' | 374 557 | 'tender' | 374 736 | 'tender' | 374 749 | 'tender' | 374 869 | 'tender' | 374 879 | 'tender' | 374 926 | 'tender' | 374 (13 rows) = SELECT q.query, count(*) FROM doc.documents d, util.queries q WHERE d.words @@ q.query AND (q.query::text=$$'tender'$$) GROUP BY q.query ; query | count --+--- 'tender' | 1870 'tender' | 1496 'tender' | 1496 (3 rows) It seems to be that the tsquery is remembering the shape of the original query, even though it's been trimmed. = SELECT q.query, min(qid), max(qid), count(*) FROM doc.documents d, util.queries q WHERE d.words @@ q.query AND (q.query::text=$$'tender'$$) GROUP BY q.query ; query | min | max | count --+-+-+--- 'tender' | 736 | 926 | 1870 (5 rows aggregated) 'tender' | 401 | 557 | 1496 (4 rows aggregated) 'tender' | 195 | 332 | 1496 (4 rows aggregated) (3 rows) = SELECT * FROM util.queries WHERE qid IN (195,248, 257, 332, 401,409,519,557,736,749,869,879,926) ORDER BY qid; qid |words| query -+-+-- 195 | can of tenders | 'tender' (3 clauses) 248 | tender the this | 'tender' (3 clauses) 257 | have tender for | 'tender' (3 clauses) 332 | for tenders of | 'tender' (3 clauses) 401 | tender with | 'tender' (2 clauses) 409 | tenders to| 'tender' (2 clauses) 519 | tender to | 'tender' (2 clauses) 557 | tenders be| 'tender' (2 clauses) 736 | tenderer| 'tender' (1 clause) 749 | tender | 'tender' (1 clause) 869 | tender | 'tender' (1 clause) 879 | tender | 'tender' (1 clause) 926 | tender | 'tender' (1 clause) (13 rows) So - is this a bug, feature, feature? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Problem with site doc search
No, it's still on the TODO. Gevik has also been looking a bit at it (I think - at least he's indicated that he is), and he recently got some new parser code to look at to see if we can use to fix it. I have the new parser code. Next week I have some time reserved to look at it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [DOCS] [HACKERS] bgwriter_lru_multiplier blurbs inconsistent
Tom Lane wrote:
Greg Smith [EMAIL PROTECTED] writes:
On Sun, 20 Jan 2008, Tom Lane wrote:
I think the main problem is the qualifying clause up front in a place
of prominence. Here's a V3 try
That one looks good to me. These are small details but better to get it
right now.
OK, committed. Back to Alvaro's original concern: is the short
description in guc.c all right, or can we improve that?
I have tried to improve the GUC description for
bgwriter_lru_multiplier; applied to CVS HEAD.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/misc/guc.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.432
diff -c -c -r1.432 guc.c
*** src/backend/utils/misc/guc.c 30 Jan 2008 18:35:55 - 1.432
--- src/backend/utils/misc/guc.c 6 Mar 2008 16:22:44 -
***
*** 1841,1847
{
{bgwriter_lru_multiplier, PGC_SIGHUP, RESOURCES,
! gettext_noop(Background writer multiplier on average buffers to scan per round.),
NULL
},
bgwriter_lru_multiplier,
--- 1841,1847
{
{bgwriter_lru_multiplier, PGC_SIGHUP, RESOURCES,
! gettext_noop(Multiple of the average buffer usage to free per round.),
NULL
},
bgwriter_lru_multiplier,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
[HACKERS] Intended behaviour of SET search_path with SQL functions?
= SHOW search_path; search_path - beta (1 row) = CREATE OR REPLACE FUNCTION func_b() RETURNS SETOF int AS $$ SELECT id FROM table_a; $$ LANGUAGE sql SET search_path = alpha; ERROR: relation table_a does not exist CONTEXT: SQL function func_b = \d table_a Did not find any relation named table_a. = \d alpha.table_a Table alpha.table_a Column | Type | Modifiers +-+--- id | integer | If I temporarily create a beta.table_a then I get to create the function and afterwards it does the right thing. It also works fine with a pl/pgsql function - presumably it's all down to context on the initial parse. I can't think of a way to exploit this maliciously, or do anything other than cause a little confusion, but I'm not sure it's intentional. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Some notes about the index-functions security vulnerability
Added to TODO: * Prevent malicious functions from being executed with the permissions of unsuspecting users Index functions are safe, so VACUUM and ANALYZE are safe too. Triggers, CHECK and DEFAULT expressions, and rules are still vulnerable. http://archives.postgresql.org/pgsql-hackers/2008-01/msg00268.php --- Tom Lane wrote: Now that the dust has settled, I want to post some notes about CVE-2007-6600, which is to my mind the most important of the five security problems fixed in our recent security updates. There are some unfinished issues here. Itagaki Takahiro originally identified the issue. The crux of it is that VACUUM FULL and ANALYZE need to execute functions in index definitions (both expression index columns and partial index predicates). Up to now this has just happened without any special steps being taken, which means that such functions were executed with the privileges of whoever is doing VACUUM/ANALYZE, who is very likely to be a superuser. Now CREATE INDEX requires such functions to be marked IMMUTABLE, which makes them unable to write anything, so the damage is seemingly limited; but it's easy to get around that. Hence, a nefarious user need only put some trojan-horse code into a PL-language function, use the function in an index on one of his tables, and wait for the next routine vacuuming in order to get his code executed as superuser. There are a whole bunch of related scenarios involving trojan-horse code in triggers, view definitions, etc. pgsql-core wasted quite a lot of time (months, actually :-() trying to devise an all-encompassing solution for all of them. However, those other scenarios have been publicly known for years, and haven't seemed to cause a lot of problems in practice, in part because it requires intentional use of a table or view in order to expose yourself to subversion. The index function attack is more nasty than these because it can subvert a superuser during required routine maintenance (including autovacuum). Moreover we couldn't find any way to deal with these other issues that doesn't involve nontrivial semantic incompatibilities, which wouldn't be suitable for back-patching. So the decision was to deal with only the index function problem as a security exercise, and after that try to get people to think some more about plugging those other holes in a future release. Takahiro-san's initial suggestion for fixing this was to try to make the marking of a function as IMMUTABLE into an air-tight guarantee that it couldn't modify the database. Right now it is not air-tight for a number of reasons: you can alter the volatility marking of a function after-the-fact, you can call a volatile function from an immutable one, etc. I originally argued against this fix on the grounds that making a planner hint into a security classification was a bad idea, since people routinely want to lie to the planner, and often have good reasons for it. But there is a better argument: even if you guarantee that a function can't write the database, it'll still be able to read the database and thereby read data the user shouldn't be able to get at. At that point you are reduced to hoping that the user cannot think of any covert channel by which to transmit the interesting info; and there are *always* covert channels, eg timing or CPU usage. We'd have to try to restrict IMMUTABLE functions so that they could not read the DB either, which seems impractical, as well as likely to break a lot of existing applications. So the direction we've pursued instead is to arrange for index expressions to be evaluated as if they were being executed by the table owner, that is, there's an implicit SECURITY DEFINER property attached to them. Up to now I think we've always thought of SECURITY DEFINER functions as being a mechanism for increasing one's privilege level. However, in this context we want to use them as a mechanism for *decreasing* privilege level, and if we want to use them that way then the privilege loss has to be air-tight. The problem there is that so far it's been possible for a SECURITY DEFINER function to execute SET SESSION AUTHORIZATION or SET ROLE and thereby regain whatever privileges are held at the outermost level. The patch as applied disallows both these operations inside a security-definer context. One reason for doing this restrictive fix is that GUC currently isn't being told about fmgr_security_definer's manipulations of CurrentUserId. There was actually a separate bug here: if you did SET ROLE inside a sec-def function and then exited without any error, SHOW ROLE continued to report the SET value as the current role, even though in reality the session had reverted to the previous CurrentUserId. Worse yet, a subsequent ABORT could cause GUC's idea of
Re: [HACKERS] Behaviour of to_tsquery(stopwords only)
= SELECT * FROM util.queries WHERE qid IN (195,248, 257, 332, 401,409,519,557,736,749,869,879,926) ORDER BY qid; qid |words| query -+-+-- 195 | can of tenders | 'tender' (3 clauses) 248 | tender the this | 'tender' (3 clauses) 257 | have tender for | 'tender' (3 clauses) 332 | for tenders of | 'tender' (3 clauses) 401 | tender with | 'tender' (2 clauses) 409 | tenders to| 'tender' (2 clauses) 519 | tender to | 'tender' (2 clauses) 557 | tenders be| 'tender' (2 clauses) 736 | tenderer| 'tender' (1 clause) 749 | tender | 'tender' (1 clause) 869 | tender | 'tender' (1 clause) 879 | tender | 'tender' (1 clause) 926 | tender | 'tender' (1 clause) (13 rows) So - is this a bug, feature, feature? It's definitely a bug: select count(*), query from queries group by query; count | query ---+-- 3 | 'tender' 4 | 'tender' 4 | 'tender' (3 rows) Will fix it soon. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Psql command-line completion bug
Added to TODO:
o Prevent escape string warnings when object names have
backslashes
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00227.php
---
Gregory Stark wrote:
If you hit tab on a table name containing a \ you get spammed with a series of
WARNINGS and HINTS about nonstandard use of \\ in a string literal:
postgres=# select * from bar\bazTAB
WARNING: nonstandard use of \\ in a string literal
LINE 1: ... substring(pg_catalog.quote_ident(c.relname),1,7)='bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 3: ...ing(pg_catalog.quote_ident(n.nspname) || '.',1,7)='bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 3: ...alog.quote_ident(nspname) || '.',1,7) = substring('bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ... || '.' || pg_catalog.quote_ident(c.relname),1,7)='bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ...og.quote_ident(n.nspname) || '.',1,7) = substring('bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING: nonstandard use of \\ in a string literal
LINE 5: ...alog.quote_ident(nspname) || '.',1,7) = substring('bar\\baz'...
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
There are a few options here:
1) Use E'' in all the psql completion queries. This means they won't work on
older versions of postgres (but they don't in general do so anyways). It would
also break anybody who set standard_conforming_string = 'on'. Ideally we would
want to use E'' and then pass false directly to PQEscapeStringInternal but
that's a static function.
2) Use $$%s$$ style quoting. Then we don't need to escape the strings at all.
We would probably have to move all the quoting outside the C strings and
borrow the function from pg_dump to generate the quoting as part of sprintf
parameter substitution.
3) set standards_conforming_strings=on for psql tab-completion queries and
then reset it afterwards. That way we can just use plain standard-conforming
'' and not get any warnings.
4) Replace PQExec with PQExecParam in tab-complete.c
Personally I think (4) is the best long-term option but at this point that
doesn't seem feasible. (3) or (2) seems the next best option.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] 8.3.0 Core with concurrent vacuum fulls
Pavan Deolasee [EMAIL PROTECTED] writes: On Wed, Mar 5, 2008 at 9:29 PM, Tom Lane [EMAIL PROTECTED] wrote: [ thinks some more... ] I guess we could use a flag array dimensioned MaxHeapTuplesPerPage to mark already-processed tuples, so that you wouldn't need to search the existing arrays but just index into the flag array with the tuple's offsetnumber. We can actually combine this and the page copying ideas. Instead of copying the entire page, we can just copy the line pointers array and work on the copy. I think that just makes things more complex and fragile. I like Heikki's idea, in part because it makes the normal path and the WAL recovery path guaranteed to work alike. I'll attach my work-in-progress patch for this --- it doesn't do anything about the invalidation semantics problem but it does fix the critical-section-too-big problem. regards, tom lane binANmrwsdjqk.bin Description: heap_prune_refactor.patch.gz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Intended behaviour of SET search_path with SQL functions?
Richard Huxton [EMAIL PROTECTED] writes: = CREATE OR REPLACE FUNCTION func_b() RETURNS SETOF int AS $$ SELECT id FROM table_a; $$ LANGUAGE sql SET search_path = alpha; ERROR: relation table_a does not exist Hmmm, I'll bet the validator forgets to apply the parameter modification. In plpgsql we had to dumb down the validator to do only a bare syntax check and not any semantic validation. Perhaps SQL function validation should act the same? You can certainly think of plenty of other reasons why a full semantics check might fail at function definition time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Behaviour of to_tsquery(stopwords only)
Teodor Sigaev wrote: So - is this a bug, feature, feature? It's definitely a bug: select count(*), query from queries group by query; count | query ---+-- 3 | 'tender' 4 | 'tender' 4 | 'tender' (3 rows) Will fix it soon. Ah, smashing. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] 8.3.0 Core with concurrent vacuum fulls
Tom Lane wrote: Pavan Deolasee [EMAIL PROTECTED] writes: On Wed, Mar 5, 2008 at 9:29 PM, Tom Lane [EMAIL PROTECTED] wrote: [ thinks some more... ] I guess we could use a flag array dimensioned MaxHeapTuplesPerPage to mark already-processed tuples, so that you wouldn't need to search the existing arrays but just index into the flag array with the tuple's offsetnumber. We can actually combine this and the page copying ideas. Instead of copying the entire page, we can just copy the line pointers array and work on the copy. I think that just makes things more complex and fragile. I like Heikki's idea, in part because it makes the normal path and the WAL recovery path guaranteed to work alike. I'll attach my work-in-progress patch for this --- it doesn't do anything about the invalidation semantics problem but it does fix the critical-section-too-big problem. FWIW, the patch looks fine to me. By inspection; I didn't test it. I'm glad we got away with a single marked array. I was afraid we would need to consult the unused/redirected/dead arrays separately. Do you have a plan for the invalidation problem? I think we could just not remove the redirection line pointers in catalog tables. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] 8.3.0 Core with concurrent vacuum fulls
Heikki Linnakangas [EMAIL PROTECTED] writes: I'm glad we got away with a single marked array. I was afraid we would need to consult the unused/redirected/dead arrays separately. Yeah, I was worried about that too. The fundamental reason why it's okay seems to be that redirects can only be the heads of HOT chains. Therefore, a newly marked tuple cannot be a member of any chain we'll need to scan later, no matter whether it is marked as newly redirected, dead, or unused. And so we can just ignore marked tuples in all cases. I set up the code to mark the redirection target too, but that is just a minor optimization AFAICS --- if we reach the redirection target later in the outer scan loop, we'd decide not to process it anyway. Do you have a plan for the invalidation problem? I think we could just not remove the redirection line pointers in catalog tables. Still thinking about it, but I agree that I'd rather make a small modification to VACUUM FULL than try to redesign cache invalidation. The trick with not removing redirect pointers would be to ensure we don't remove the redirection target. While the redirection target was presumably not DEAD when heap_page_prune looked at it, it seems possible that it is DEAD by the time vacuum.c looks. Another risk factor is trying to move the redirection target down to a lower page. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] 8.3 / 8.2.6 restore comparison
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 3 Mar 2008 20:40:02 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: As a closing note on this from my side. When I ran the restore with 90 connections (as a stress test) it only took 20 minutes longer than the 24 connections. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH0E3IATb/zqfZUUQRAjn3AJ9d0IpBn9pd16AYFWT3qs2frS51lQCfcOIN uQvwc263TjLMZOYzx7Ktbpo= =6xOV -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] dblink doesn't honor interrupts while waiting a result
On Mon, Feb 25, 2008 at 04:45:43AM +0100, Florian G. Pflug wrote:
dblink in 8.3 blocks without any possibility of interrupting it while
waiting for an answer from the remote server. Here is a strace
[pid 27607] rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_IGN}, 8) = 0
[pid 27607] sendto(56, Q\0\0\0008lock table travelhit.booking_code in
exclusive mode\0, 57, 0, NULL, 0) = 57
[pid 27607] rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_IGN}, 8) = 0
[pid 27607] poll([{fd=56, events=POLLIN|POLLERR}], 1, -1) = ?
ERESTART_RESTARTBLOCK (To be restarted)
[pid 27607] --- SIGTERM (Terminated) @ 0 (0) ---
[pid 27607] rt_sigreturn(0xf) = -1 EINTR (Interrupted system call)
[pid 27607] poll(
As you can see I'm trying to lock the table travelhit.booking_code,
which blocks because someone else is already holding that lock. When
I send a SIGTERM to the backend, the poll() syscalll is interruped -
but immediatly restarted.
I'm not sure how a proper fix for this could look like, since the
blocking actually happens inside libpq - but this certainly makes
working with dblink painfull...
FWIW, I've had problems with dblink getting wedged somewhere in it's
communication with another server. I'm not sure at what stage this
happens (getting results, sending a query, etc). The only way I've found
to clear it is to restart the database the connection was coming from.
Dunno if this is related or not...
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
pgpRNqTi40q09.pgp
Description: PGP signature
Re: [HACKERS] [PATCHES] Updatable views
Where are on updatable views? --- Bernd Helmle wrote: --On Freitag, September 01, 2006 11:41:16 -0400 Tom Lane [EMAIL PROTECTED] wrote: So in other words, views on serial columns don't work? I don't think that's going to be acceptable. They work in such a case that someone isn't allowed to put a volatile function in an update query Not really worse than what the rewriter is doing already --- in fact, I think it's isomorphic to what would happen to the rule qual expressions in your existing patch. Currently you don't have to rewrite the rule conditions itself every time you apply them to the query tree since they are stored in pg_rewrite matching all various (reversed) varattno's, resno's and whatever. If i understand correctly you need to do that with constraints every time you fire an update query on a view for each underlying relation I'm about to propose that we should try to go beta next week (see forthcoming message). If you can strip down your patch to avoid the multi-eval problems in the next couple of days, I'm still willing to Depends on how many days couple of days are.if you mean the next three days then definitely not, since i'm afk for the whole upcoming weekend. Bad timing, but it's not deferrable :( consider it, but at the moment I'm assuming that it needs to be held for 8.3. Well, i'll see what i can do next weekit's a little bit disappointing that these problems raises so late, but that's no one's fault since there are many side effects of the rewriting system involved Many thanks for your comments. Bernd ---(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 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Grouped Index Tuples
Heikki, are you going to revise this for 8.4? --- Heikki Linnakangas wrote: I've brought the GIT patch up-to-date with CVS head. The latest version can be found at http://community.enterprisedb.com/git/ I also reran the CPU bound test cases with the latest patch. I want this in 8.3 in some form, and I have the time to do any required changes. If someone wants to see more tests, I can arrange that as well. The patch is pretty big at the moment. I think the best way to proceed with this is to extract some smaller, incremental patches from it that just refactor the current b-tree code. After that, the final patch that implements GIT should be much smaller and more readable. And there's still a bunch of todo items there as well... But before I start doing that, I need some review and general agreement on the design. What I don't want to happen is that three days after the feature freeze, someone finally looks at it and finds a major issue or just thinks it's an unreadable mess, and we no longer have the time to fix it. One question that I'm sure someone will ask is do we need this if we have bitmap indexes? Both aim at having a smaller index, after all. The use cases are quite different; GIT is effective whenever you have a table that's reasonably well-clustered. Unlike the bitmap indexam, GIT's effectiveness doesn't depend on the number of distinct values, in particular it works well with unique indexes. GIT is comparable to clustered indexes in other DBMSs (in fact we might want to call GIT that in the end). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Stream bitmaps
Heikki, are you going to submit this for 8.4? --- Heikki Linnakangas wrote: Hi all, I'd like to see the indexam API changes needed by the bitmap indexam to be committed soon. Has anyone looked at the proposed API in the latest patch? Any thoughts? I'm quite happy with it myself, with a few reservations: - All the getbitmap implementations except the new bitmap indexam are just boilerplate. How about making getbitmap-function optional, and having a generic implementation that fills in a hash bitmap using the traditional getnext function? - getbitmap is passed an existing bitmap as argument, and the implementation needs to OR the existing bitmap with new tuples. How about AND? An indexam could be smart about ANDing with an existing bitmap, for example skipping to the first set bit in the existing bitmap and starting the scan from there. - I'd like to have support to return candidate matches with both getbitmap and getnext. A simple flag per page of results would be enough for getbitmap, I think. - StreamBitmap and HashBitmap are separate node types, but OpStream is not. opaque-field in the StreamBitmap struct is not really that opaque, it needs to be a StreamNode. I drew a UML sketch of what I think the class-hierarchy is (http://community.enterprisedb.com/streambitmaps.png). This is object-oriented programming, we're just implementing classes and inheritance with structs and function pointers. The current patch mixes different techniques, and that needs to be cleaned up. I'd like to see a separate patch that contains just the API changes. Gavin, could you extract an API-only patch from the bitmap index patch? I can work on it as well, but I don't want to step on your toes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Group Commit
Should we remove these now that we have async commit? #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 They seem unfixable. --- Simon Riggs wrote: On Tue, 2007-04-10 at 11:40 +0100, Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I've been working on the patch to enhance our group commit behavior. The patch is a dirty hack at the moment, but I'm settled on the algorithm I'm going to use and I know the issues involved. One question that just came to mind is whether Simon's no-commit-wait patch doesn't fundamentally alter the context of discussion for this. I was certainly intending that it would. Aside from the prospect that people won't really care about group commit if they can just use the periodic-WAL-sync approach, ISTM that one way to get group commit is to just make everybody wait for the dedicated WAL writer to write their commit record. With a sufficiently short delay between write/fsync attempts in the background process, won't that net out at about the same place as a complicated group-commit patch? Possibly. To get efficient group commit there would need to be some kind of signaling between the WAL writer and normal backends. I think there is some in the patch, but I'm not sure if it gives efficient group commit. A constant delay will just give us something similar to commit_delay. Agreed. I've refrained from spending time on group commit until the commit-no-wait patch lands, because it's going to conflict anyway. I'm starting to feel we should not try to rush group commit into 8.3, unless it somehow falls out of the commit-no-wait patch by accident, given that we're past feature freeze and coming up with a proper group commit algorithm would need a lot of research and testing. Better do it for 8.4 with more time, we've got enough features on plate for 8.3 anyway. My feeling was that I couldn't get both done for 8.3, and that including the WAL Writer in 8.3 would make the dev path clearer for a later attempt upon group commit. I think it was worth exploring whether it would be easy, but I think we can see it'll take a lot of work to make it fly right. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] [PATCHES] Updatable views
--On Donnerstag, März 06, 2008 17:03:10 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: Where are on updatable views? I really want to have this one ready for 8.4, but i have nothing appliable at the moment. Considering the amount of rework that needs to be done, i hope i can provide an updated patch version during next commit fest. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-hackers
Re: [HACKERS] Idle idea for a feature
Tom Lane wrote: psql's \d command tells you about outgoing foreign key constraints (ie, ones referencing another table from this one). It doesn't tell you about incoming ones (ie, ones where another table references this one). ISTM it'd be a good idea if it did, as are there any incoming foreign keys seems to be a question we constantly ask when solving update-performance problems, and there isn't any easy way to check for such. I'm not real sure what the printout should look like, though. Added to TODO: o Have \d show foreign keys that reference a table's primary key http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idle idea for a feature
Jim Nasby wrote: On Apr 10, 2007, at 9:48 AM, Stephen Frost wrote: Referenced by: loc_base_clin_loc_base_id_fkey FOREIGN KEY (loc_base_id) BY wdm_networx.loc_base_clin(loc_base_id) /| \/|\ Referenced column(s) in *this* table | |--- column(s) in referencing table +1, and I also like Nikhils' idea of \d reporting if a table inherits or is inherited from. Bruce, can we get a TODO? Added to TODO: o Have \d show child tables that inherit from the specified parent We already show the parent table for inherited children. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idle idea for a feature
Bruce Momjian wrote: Tom Lane wrote: Added to TODO: o Have \d show foreign keys that reference a table's primary key http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php We have a patch for this: http://archives.postgresql.org/pgsql-patches/2008-03/msg5.php -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Group Commit
On Thu, 6 Mar 2008, Bruce Momjian wrote: Should we remove these now that we have async commit? #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 They seem unfixable. commit_delay offers a small but not insignificant improvement for some people using the feature under bursty, high client loads. The useful tuning seems to be siblings[10-20] and a small setting for the delay; I usually just set it to 1 which gives the minimum the OS is capable of resolving. That wasn't the feature's original intention I think, but that's what it's useful for regardless. As async commit is only applicable in cases where it's OK to expand the window for transaction loss, removing commit_delay will cause a small performance regression for users who have tuned it usefully right now. I actually have a paper design for something that builds a little model for how likely it is another commit will be coming soon that essentially turns this into something that can be tuned automatically, better than any person can do it. No idea if I'll actually build that thing, but I hope it's obvious that there's some possibility to improve this area for applications that can't use async commit. If you're going to dump the feature, I'd suggest at least waiting until later in the 8.4 cycle to see if something better comes along first. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Group Commit
Greg Smith [EMAIL PROTECTED] writes: I actually have a paper design for something that builds a little model for how likely it is another commit will be coming soon that essentially turns this into something that can be tuned automatically, better than any person can do it. No idea if I'll actually build that thing, but I hope it's obvious that there's some possibility to improve this area for applications that can't use async commit. If you're going to dump the feature, I'd suggest at least waiting until later in the 8.4 cycle to see if something better comes along first. What about the other idea of just having committers wait for the next walwriter-cycle flush before reporting commit? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idle idea for a feature
Alvaro Herrera wrote: Bruce Momjian wrote: Tom Lane wrote: Added to TODO: o Have \d show foreign keys that reference a table's primary key http://archives.postgresql.org/pgsql-hackers/2007-04/msg00424.php We have a patch for this: http://archives.postgresql.org/pgsql-patches/2008-03/msg5.php Oh, it is in the patches queue already --- nice. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Terminating a backend
I have an idea for this TODO item: * Allow administrators to safely terminate individual sessions either via an SQL function or SIGTERM Lock table corruption following SIGTERM of an individual backend has been reported in 8.0. A possible cause was fixed in 8.1, but it is unknown whether other problems exist. This item mostly requires additional testing rather than of writing any new code. http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php When we get the termination signal, why can't we just set a global boolean, do a query cancel, and in the setjmp() code block check the global and exit --- at that stage we know we have released all locks and can exit cleanly. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Terminating a backend
Bruce Momjian [EMAIL PROTECTED] writes: I have an idea for this TODO item: ... When we get the termination signal, why can't we just set a global boolean, do a query cancel, and in the setjmp() code block check the global and exit --- at that stage we know we have released all locks and can exit cleanly. How does that differ from what happens now? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Terminating a backend
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I have an idea for this TODO item: ... When we get the termination signal, why can't we just set a global boolean, do a query cancel, and in the setjmp() code block check the global and exit --- at that stage we know we have released all locks and can exit cleanly. How does that differ from what happens now? We would _terminate_/exit, not just cancel the query. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Group Commit
On Thu, 6 Mar 2008, Tom Lane wrote: What about the other idea of just having committers wait for the next walwriter-cycle flush before reporting commit? I haven't considered that too much yet; it may very well be superior to anything I was thinking of. The only point I was trying to make today is that I'd prefer not to see commit_delay excised until there's a superior replacement for it that's suitable even for synchronous write situations. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Updatable views
On Thu, Mar 6, 2008 at 6:35 PM, Bernd Helmle [EMAIL PROTECTED] wrote: --On Donnerstag, März 06, 2008 17:03:10 -0500 Bruce Momjian [EMAIL PROTECTED] wrote: Where are on updatable views? I really want to have this one ready for 8.4, but i have nothing appliable at the moment. Considering the amount of rework that needs to be done, i hope i can provide an updated patch version during next commit fest. i will be waiting for it! maybe you can update the wiki with actual state or with the latest patch you have even if it doesn't apply... just to see what needs to be done... -- regards, Jaime Casanova -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Doubt in heap_release_fetch
Hello,
What do the following lines mean :
/* Tuple failed time qual, but maybe caller wants to see it anyway. */
if (keep_buf)
*userbuf = buffer;
else
{
ReleaseBuffer(buffer);
*userbuf = InvalidBuffer;
}
What is the time qualification check ?
Thanks,
Suresh
-
Never miss a thing. Make Yahoo your homepage.
Re: [HACKERS] Doubt in heap_release_fetch
Suresh [EMAIL PROTECTED] writes: What is the time qualification check ? HeapTupleSatisfiesVisibility(). See src/include/utils/tqual.h src/backend/utils/time/tqual.c and if none of this is making any sense maybe you need to start here: http://developer.postgresql.org/pgdocs/postgres/mvcc.html regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
