Re: [PATCHES] [COMMITTERS] pgsql: Remove QueryOperand-istrue flag, it was used only in cover
This change introduced a compiler warning. Here is a fix for it. Remove QueryOperand-istrue flag, it was used only in cover ranking (ts_rank_cd). Use palloc'ed array in ranking instead of flag. Thank you, committed -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] HOT documentation README
On 9/12/07, Pavan Deolasee [EMAIL PROTECTED] wrote: One change that is worh mentioning and discussing is that we don't follow HOT chains while fetching tuples during autoanalyze and autoanalyze would consider all such tuples as DEAD. In the worst case when all the tuples in the table are reachable via redirected line pointers, this would confuse autoanalyze since it would consider all tuples in the table as DEAD. This is all crap. I was under the impression that heap_release_fetch() would never fetch a HOT tuple directly, but thats not true. analyze fetches all tuples in the chosen block, so it would ultimately fetch the visible tuple. A tuple is counted DEAD only if its t_data is set to non-NULL. For redirected line pointer heap_release_fetch() will set t_data to NULL and hence these line pointers are (rightly) not counted as DEAD. This is the right thing to do because lazy vacuum can not remove redirected line pointers. I think we should change this to follow HOT chain in analyze. We need not follow the chain, but we should check for redirect-dead line pointers and count them as dead rows. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] HOT patch - version 15
Pavan Deolasee [EMAIL PROTECTED] writes: Please see the revised patches attached. I'm curious how much the WAL-recovery aspects of this patch have been tested, because heap_xlog_clean seems quite broken. You have apparently decided to redefine the WAL record format as using one-based rather than zero-based item offsets, which would be fine if any of the rest of the code had been changed to agree ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] HOT patch - version 15
Pavan Deolasee wrote: On 9/13/07, Tom Lane [EMAIL PROTECTED] wrote: You have apparently decided to redefine the WAL record format as using one-based rather than zero-based item offsets, which would be fine if any of the rest of the code had been changed to agree ... I know Heikki changed that when he did the initial refactoring, but not sure why. May be he wanted to make it more consistent. Yeah, I just checked the work-in-progress patch I sent you back in July. I refactored it to use one-based offsets for consistency, since I modified log_heap_clean quite heavily anyway. It's possible that I broke it in the process, I was only interested in testing the performance characteristics of the simplified pruning scheme. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] HOT patch - version 15
On 9/13/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Yeah, I just checked the work-in-progress patch I sent you back in July. I refactored it to use one-based offsets for consistency, since I modified log_heap_clean quite heavily anyway. It's possible that I broke it in the process, I was only interested in testing the performance characteristics of the simplified pruning scheme. I don't think so -- atleast I couldn't figure out why its broken. But I would take Tom's comment seriously and look more into it tomorrow. Or we can just revert it back to zero-based offsets. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] HOT patch - version 15
Pavan Deolasee [EMAIL PROTECTED] writes: On 9/13/07, Tom Lane [EMAIL PROTECTED] wrote: You have apparently decided to redefine the WAL record format as using one-based rather than zero-based item offsets, which would be fine if any of the rest of the code had been changed to agree ... I know Heikki changed that when he did the initial refactoring, but not sure why. May be he wanted to make it more consistent. But I don't think its broken because we collect the offsets in one-based format in PageRepairFragmentation, WAL log in that format and redo the same way. Am I missing something ? Hmm, I had been thinking that vacuum.c and vacuumlazy.c worked directly with that info, but it looks like you're right, only PageRepairFragmentation touches that array. Never mind ... though my suspicions would probably not have been aroused if anyone had bothered to fix the comments. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] HOT patch - version 15
On 9/13/07, Tom Lane [EMAIL PROTECTED] wrote: Never mind ... though my suspicions would probably not have been aroused if anyone had bothered to fix the comments. Yeah, my fault. I should have fixed that. Sorry about that. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] HOT patch - version 15
Okay, something else (a real problem this time ;-)): HeapTupleSatisfiesAbort is bogus because it has failed to track recent changes in tqual.c. Rather than fix it, though, I question why we need it at all. The only use of it is in heap_prune_tuplechain() and ISTM that code is redundant, wrong, or both. In the case of a full-page prune, it's redundant because the tuple would be found anyway by searching its chain from the root tuple. Indeed I suspect that such a tuple is entered into the newly-dead list twice, perhaps risking overflow of that array. In the case of a single-chain prune, it still seems wrong since you'll eliminate only one of what might be a chain with multiple aborted entries. If it's OK to leave those other entries for future collection, then it should be OK to leave this one too. If it's not OK then the approach needs to be redesigned. I'm fairly unclear on what the design intention is for recovering from the case where the last item(s) on a HOT chain are aborted. Please clarify. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WIP: rewrite numeric division
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: Please, let's revisit this, and not postpone it without further discussion. I never knew about the correctness issues in div_var(), but since I know about it, I feel I am just waiting until that problem will hit me or anyone else. Yeah. I was basically waiting to see if anyone could come up with a faster solution. Since no one seems to have an idea how to do it better, I'm inclined to apply the patch for 8.3. regards, tom lane -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Still recommending daily vacuum...
Jim C. Nasby wrote: -- Start of PGP signed section. From http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html : Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (Some installations with extremely high update rates vacuum their busiest tables as often as once every few minutes.) If you have multiple databases in a cluster, don't forget to VACUUM each one; the program vacuumdb might be helpful. Do we still want that to be our formal recommendation? ISTM it would be more logical to recommend a combination of autovac, daily vacuumdb -a if you can afford it and have a quiet period, and frequent manual vacuuming of things like web session tables. I'm happy to come up with a patch, but I figure there should be consensus first... I have applied the following patch to emphasize autovacuum rather than administrator-scheduled vacuums. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/maintenance.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v retrieving revision 1.78 diff -c -c -r1.78 maintenance.sgml *** doc/src/sgml/maintenance.sgml 19 Aug 2007 01:41:24 - 1.78 --- doc/src/sgml/maintenance.sgml 13 Sep 2007 23:37:50 - *** *** 59,66 /indexterm para !productnamePostgreSQL/productname's commandVACUUM/ command !emphasismust/emphasis be run on a regular basis for several reasons: orderedlist listitem --- 59,67 /indexterm para !productnamePostgreSQL/productname's commandVACUUM/ (xref !linkend=sql-vacuum command has to run on a regular basis for several !reasons: orderedlist listitem *** *** 78,91 firsttermtransaction ID wraparound/./simpara /listitem /orderedlist - -The frequency and scope of the commandVACUUM/ operations -performed for each of these reasons will vary depending on the -needs of each site. Therefore, database administrators must -understand these issues and develop an appropriate maintenance -strategy. This section concentrates on explaining the high-level -issues; for details about command syntax and so on, see the xref -linkend=sql-vacuum endterm=sql-vacuum-title reference page. /para para --- 79,84 *** *** 103,115 /para para !An automated mechanism for performing the necessary commandVACUUM/ !operations has been added in productnamePostgreSQL/productname 8.1. !See xref linkend=autovacuum. /para sect2 id=vacuum-for-space-recovery !titleRecovering disk space/title indexterm zone=vacuum-for-space-recovery primarydisk space/primary --- 96,109 /para para !Fortunately, autovacuum (xref linkend=autovacuum) monitors table !activity and performs commandVACUUM/commands when necessary. !Autovacuum works dynamically so it is often better !administration-scheduled vacuuming. /para sect2 id=vacuum-for-space-recovery !titleRecovering Disk Space/title indexterm zone=vacuum-for-space-recovery primarydisk space/primary *** *** 129,145 /para para - Clearly, a table that receives frequent updates or deletes will need - to be vacuumed more often than tables that are seldom updated. It - might be useful to set up periodic applicationcron/ tasks that - commandVACUUM/command only selected tables, skipping tables that are known not to - change often. This is only likely to be helpful if you have both - large heavily-updated tables and large seldom-updated tables mdash; the - extra cost of vacuuming a small table isn't enough to be worth - worrying about. -/para - -para There are two variants of the commandVACUUM/command command. The first form, known as quotelazy vacuum/quote or just commandVACUUM/command, marks dead data in tables and --- 123,128 *** *** 167,196 /para para ! The standard form of commandVACUUM/ is best used with the goal ! of maintaining a fairly level steady-state usage of disk space. If ! you need to return disk space to the operating system, you can use ! commandVACUUM FULL/ mdash; but what's the point of releasing disk ! space that will only have to be allocated again soon? Moderately ! frequent standard commandVACUUM/ runs are a better approach ! than infrequent commandVACUUM FULL/ runs for maintaining ! heavily-updated tables. However, if some heavily-updated tables ! have gone too long with infrequent
Re: [PATCHES] [HACKERS] PAM authentication fails for local UNIX users
Applied: PAM does work authenticating against Unix system authentication because the postgres server is started by a non-root user. In order to enable this functionality, the root user must provide additional permissions to the postgres user (for reading filename/etc/shadow/). --- Dhanaraj M wrote: Hi all, This is the continuation to the discussion that we had in the hacker's list. http://www.postgresql.org/docs/8.2/interactive/auth-methods.html#AUTH-PAM Here, I like to add some details in 20.2.6. PAM authentication section. Can someone review and make changes, if required? Thanks. *** client-auth.sgml.orig Tue Aug 21 16:52:45 2007 --- client-auth.sgmlTue Aug 21 17:02:52 2007 *** *** 987,992 --- 987,1001 and the ulink url=http://www.sun.com/software/solaris/pam/; systemitem class=osnameSolaris/ PAM Page/ulink. /para + +note + para + The local UNIX user authentication is not permitted, + because the postgres server is started by a non-root user. + In order to enable this functionality, the root user must provide + additional permissions to the postgres user (for reading /etc/shadow file). + /para +/note /sect2 /sect1 Zdenek Kotala wrote: The problem what Dhanaraj tries to address is how to secure solve problem with PAM and local user. Other servers (e.g. sshd) allow to run master under root (with limited privileges) and forked process under normal user. But postgresql requires start as non-root user. It limits to used common pattern. There is important question: Is current requirement to run postgresql under non-root OK? If yes, than we must update PAM documentation to explain this situation which will never works secure. Or if we say No, it is stupid limitation (in case when UID 0 says nothing about user's privileges) then we must start discussion about solution. For now I think we should update the docs. You really can't compare postgres with sshd - ssh connections are in effect autonomous. I suspect the changes involved in allowing us to run as root and then give up privileges safely would be huge, and the gain quite small. I'd rather see an HBA fallback mechanism, which I suspect might overcome most of the problems being encountered here. cheers andrew -- Dhanaraj M x40049/+91-9880244950 Solaris RPE, Bangalore, India http://blogs.sun.com/dhanarajm/ ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] HOT patch - version 15
On 9/14/07, Tom Lane [EMAIL PROTECTED] wrote: HeapTupleSatisfiesAbort is bogus because it has failed to track recent changes in tqual.c. Oh. I should have been aware. Rather than fix it, though, I question why we need it at all. The only use of it is in heap_prune_tuplechain() and ISTM that code is redundant, wrong, or both. In the case of a full-page prune, it's redundant because the tuple would be found anyway by searching its chain from the root tuple. Indeed I suspect that such a tuple is entered into the newly-dead list twice, perhaps risking overflow of that array. No, we would never reach an aborted dead tuple from the chain because we would see a live tuple before that. Or the tuple preceding the aborted (first aborted, if there are many) may have been updated again and the chain never reaches to the aborted tuples. Thats the reason why we have HeapTupleSatisfiesAbort to collect any aborted heap-only tuples. In the case of a single-chain prune, it still seems wrong since you'll eliminate only one of what might be a chain with multiple aborted entries. If it's OK to leave those other entries for future collection, then it should be OK to leave this one too. If it's not OK then the approach needs to be redesigned. Again, since we check every heap-only tuple for aborted dead case we shall collect all such tuples. I think one place where you are confusing (or IOW the code might have confused you ;-)) is that we never reach aborted dead heap-only tuples by following the HOT chain from the root tuple and thats why it needs special treatment. I'm fairly unclear on what the design intention is for recovering from the case where the last item(s) on a HOT chain are aborted. Please clarify. We don't do anything special. Such a tuple is never reached during HOT chain following because either we would see a visible tuple before that or the older tuple might have been updated again and the chain had moved in a different direction. We only need some special treatment to prune such tuple and hence the business of HeapTupleSatisfiesAbort Having said that, based on our recent discussion about pruning a chain upto and including the latest DEAD tuple in the chain, ISTM that we can get rid of the giving any special treatment to aborted heap-only tuples. Earlier we could not do so for HOT updated aborted heap-only because we did not know if its a part of any valid HOT chain. Now (assuming we change the pruning code to always prune everything including the latest DEAD tuple) we guarantee that all DEAD tuples in the chain will be pruned, and hence we can collect any DEAD tuple seen while pruning. I am not sure if I explain this well, may be I should post an example. Need to run now. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [PATCHES] tab complete changes
Patch applied. Thanks.
---
Stefan Kaltenbrunner wrote:
the attached patch makes teh following changes to the psql tab-complete
support
* adds a few missing words to some commands (like adding GIN as a valid
index type or OWNED BY for ALTER SEQUENCE,...)
* support for ALTER TABLE foo ENABLE/DISABLE REPLICA TRIGGER/RULE
* autocomplete CREATE DATABASE foo TEMPLATE (mostly done to prevent
conflicts with the TEMPLATE keyword for text search)
* support for ALTER/CREATE/DROP TEXT SEARCH as well as COMMENT ON TEXT
SEARCH and the corresponding psql backslash commands.
This proved a little more difficult than expected due to the fact that
words_after_create[] is used for two purposes - one is to provide a list
of words that follow immediatly after CREATE (or DROP) and the other
purpose is to use it for autocompleting anywhere in the statement if the
word in that struct is found with a query.
Since TEXT SEARCH CONFIGURATION|DICTIONARY|TEMPLATE|PARSER results in 3
words instead of one (as all the other words in that list are) I added a
flag to the struct to tell create_command_generator() to skip that entry
for autocompleting immediatly after CREATE which feels like a dirty
hack (but that holds true for a lot of code in tab-complete.c).
Stefan
Index: src/bin/psql/tab-complete.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.166
diff -c -r1.166 tab-complete.c
*** src/bin/psql/tab-complete.c 3 Jul 2007 01:30:37 - 1.166
--- src/bin/psql/tab-complete.c 25 Aug 2007 11:17:23 -
***
*** 328,333
--- 328,337
AND pg_catalog.quote_ident(relname)='%s' \
AND pg_catalog.pg_table_is_visible(c.oid)
+ #define Query_for_list_of_template_databases \
+ SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database \
+ WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s' and
datistemplate IS TRUE
+
#define Query_for_list_of_databases \
SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database \
WHERE substring(pg_catalog.quote_ident(datname),1,%d)='%s'
***
*** 419,424
--- 423,444
(SELECT tgrelid FROM pg_catalog.pg_trigger \
WHERE pg_catalog.quote_ident(tgname)='%s')
+ #define Query_for_list_of_ts_configurations \
+ SELECT pg_catalog.quote_ident(cfgname) FROM pg_catalog.pg_ts_config \
+ WHERE substring(pg_catalog.quote_ident(cfgname),1,%d)='%s'
+
+ #define Query_for_list_of_ts_dictionaries \
+ SELECT pg_catalog.quote_ident(dictname) FROM pg_catalog.pg_ts_dict \
+ WHERE substring(pg_catalog.quote_ident(dictname),1,%d)='%s'
+
+ #define Query_for_list_of_ts_parsers \
+ SELECT pg_catalog.quote_ident(prsname) FROM pg_catalog.pg_ts_parser \
+ WHERE substring(pg_catalog.quote_ident(prsname),1,%d)='%s'
+
+ #define Query_for_list_of_ts_templates \
+ SELECT pg_catalog.quote_ident(tmplname) FROM pg_catalog.pg_ts_template \
+ WHERE substring(pg_catalog.quote_ident(tmplname),1,%d)='%s'
+
/*
* This is a list of all things in Pgsql, which can show up after CREATE
or
* DROP; and there is also a query to get a list of them.
***
*** 429,434
--- 449,455
const char *name;
const char *query; /* simple query, or NULL */
const SchemaQuery *squery; /* schema query, or NULL */
+ const bool noshow; /* NULL or true if this word should not show up
after CREATE or DROP */
} pgsql_thing_t;
static const pgsql_thing_t words_after_create[] = {
***
*** 440,447
--- 461,470
* CREATE CONSTRAINT TRIGGER is not supported here because it is
designed
* to be used only by pg_dump.
*/
+ {CONFIGURATION, Query_for_list_of_ts_configurations, NULL, true},
{CONVERSION, SELECT pg_catalog.quote_ident(conname) FROM
pg_catalog.pg_conversion WHERE
substring(pg_catalog.quote_ident(conname),1,%d)='%s'},
{DATABASE, Query_for_list_of_databases},
+ {DICTIONARY, Query_for_list_of_ts_dictionaries, NULL, true},
{DOMAIN, NULL, Query_for_list_of_domains},
{FUNCTION, NULL, Query_for_list_of_functions},
{GROUP, Query_for_list_of_roles},
***
*** 449,454
--- 472,478
{INDEX, NULL, Query_for_list_of_indexes},
{OPERATOR, NULL, NULL}, /* Querying for this is probably not
such a
* good idea. */
+ {PARSER, Query_for_list_of_ts_parsers, NULL, true},
{ROLE, Query_for_list_of_roles},
{RULE, SELECT pg_catalog.quote_ident(rulename) FROM
pg_catalog.pg_rules WHERE
substring(pg_catalog.quote_ident(rulename),1,%d)='%s'},
{SCHEMA, Query_for_list_of_schemas},
***
***
Re: [PATCHES] XLogCacheByte is unused
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- ITAGAKI Takahiro wrote: I found XLogCtlData.XLogCacheByte is already unused in CVS HEAD. Should we remove the variable, or reserve it for future use? Index: src/backend/access/transam/xlog.c === --- src/backend/access/transam/xlog.c (revision 1268) +++ src/backend/access/transam/xlog.c (working copy) @@ -317,7 +317,6 @@ */ char *pages; /* buffers for unwritten XLOG pages */ XLogRecPtr *xlblocks; /* 1st byte ptr-s + XLOG_BLCKSZ */ - SizeXLogCacheByte; /* # bytes in xlog buffers */ int XLogCacheBlck; /* highest allocated xlog buffer index */ TimeLineID ThisTimeLineID; @@ -4115,8 +4114,6 @@ * Do basic initialization of XLogCtl shared data. (StartupXLOG will fill * in additional info.) */ - XLogCtl-XLogCacheByte = (Size) XLOG_BLCKSZ *XLOGbuffers; - XLogCtl-XLogCacheBlck = XLOGbuffers - 1; XLogCtl-Insert.currpage = (XLogPageHeader) (XLogCtl-pages); SpinLockInit(XLogCtl-info_lck); Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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
