Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway wrote: Since the feature freeze is only about a week off, I wanted to post this patch even though it is not yet ready to be applied. Sorry -- I just realized that two new files for ValuesScan didn't make it into the patch posted earlier. Here they are now -- please untar in your postgres sourcetree root in addition to applying the patch. (I thought cvs diff -cN should have included the new files, since I had earlier done cvs add on them, but it didn't work. I could swear that worked for me in the past...) Thanks, Joe multi-insert-r6a.new.tar.gz Description: GNU Zip compressed data ---(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: [PATCHES] Mark change-on-restart-only values in postgresql.conf
Peter Eisentraut wrote: Am Dienstag, 18. Juli 2006 23:44 schrieb Zdenek Kotala: I added additional comments marked setting which need server restart to take effect. I use (!RSR!) tag for it, however if anybody have different idea, let me know and I will change it. It seems that people didn't like introducing secret codes into the configuration file, so I adapted your patch and changed the marker to (change requires restart). OK, I going to change. Is there any limit to char per line? Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Allow commenting of variables in postgresql.conf to - try 4
Zdenek, On Fri, Jul 14, 2006 at 12:17:55AM +0200, Zdenek Kotala wrote: There is last version of patch with following changes/improvements: 1) I divide set_config_option to more smaller functions without backside effect. I did not check the changes you have done to set_config_option and the like but tested the commenting / uncommenting / changing of guc variables and the behavior and log output. The general idea (at least my idea) is that whenever a SIGHUP is received and there is some difference between the config file and the active value that the server is using, a notice message is written to the log. That way, at every moment you can see if the active values coincide with the configuration file by sending a SIGHUP and if there are no such messages the admin can stop and restart the server and be sure that the settings will be the same after a restart. While testing, I specified a bunch of test cases that I attach below. I also renamed the GUC_JUST_RELOAD to GUC_IN_CONFFILE because I did not really understand what GUC_JUST_RELOAD should mean. GUC_IN_CONFFILE means that the variable does show up in the configuration file and is active there, i.e. is not commented. Please check my changes, I'm pretty sure it can be cleaned up further. Joachim Test cases for guc falls back to default: guc_context = PGC_POSTMASTER (shared_buffers is an example, Default: 1000) Commented value gets un-commented (value != default) = message every time a sighup is received Example: #shared_buffers = 3301 START shared_buffers = 3301 HUP Output: LOG: parameter shared_buffers cannot be changed after server start; configuration file change ignored Value gets changed (to != initial). = message every time a sighup is received Example: shared_buffers = 3301 START shared_buffers = 3302 HUP Output: LOG: parameter max_prepared_transactions cannot be changed after server start; configuration file change ignored Value gets commented (initial != default). = message every time a sighup is received Example: shared_buffers = 3301 START #shared_buffers = 3301 HUP Output: LOG: parameter max_prepared_transactions cannot be changed (commented) after server start; configuration file change ignored Commented value (not applied) gets changed back to initial setting: = no more messages after SIGHUP Example: shared_buffers = 3301 START #shared_buffers = 3301 HUP (value does not get applied) shared_buffers = 3301 HUP Output: None Commented value (not applied) gets changed to != initial: = message every time a SIGHUP is received Example: shared_buffers = 3301 START #shared_buffers = 3301 HUP shared_buffers = 3302 HUP Output: LOG: parameter shared_buffers cannot be changed after server start; configuration file change ignored guc_context = PGC_SIGHUP set (fsync is an example, Default: true) Value (== default) gets commented = nothing happens Example: fsync = true START #fsync = true HUP Output: None Value (!= default) gets commented = falls back to default on first HUP that is received) Example: fsync = false START fsync = true HUP (subsequent HUPs do not show output anymore) Output: LOG: configuration option fsync falls back to default value Commented value gets un-commented (value != default) Example: #fsync = false START fsync = false HUP Output: None Commented value gets un-commented (value == default) Example: #fsync = true START fsync = true HUP Output: None diff -cr cvs/pgsql/src/backend/utils/misc/guc.c cvs.build/pgsql/src/backend/utils/misc/guc.c *** cvs/pgsql/src/backend/utils/misc/guc.c 2006-07-14 22:19:46.0 +0200 --- cvs.build/pgsql/src/backend/utils/misc/guc.c2006-07-24 12:22:55.0 +0200 *** *** 2667,2705 struct config_bool *conf = (struct config_bool *) gconf; if (conf-assign_hook) ! if (!(*conf-assign_hook) (conf-reset_val, true, PGC_S_DEFAULT)) elog(FATAL, failed to initialize %s to %d, ! conf-gen.name, (int) conf-reset_val); ! *conf-variable = conf-reset_val; break; }
Re: [PATCHES] pgstattuple extension for indexes
ITAGAKI Takahiro wrote: Tom Lane [EMAIL PROTECTED] wrote: Also, I added an experimental feature for btree indexes. It checks fragmentation factor of indexes. The really serious problem with reporting this info via NOTICE is that there's no way for a program to get its hands on the info. The output tuple format needs to be extended instead. Ok, I added 'fragmented_percent' field to the output tuple. This information will help us to decide when to do REINDEX. However, it is only avaliable for btree index presently. Other indexes should have equivalent information, but I don't know how to determine it. BTW while you're handling this, why not change the function to use OUT parameters instead of having to CREATE TYPE to handle the return type? I think it is easier to handle ... One question I had was: in the percentage of fragmentation, is higher better or lower better? (I'm guessing lower is better, but this should be mentioned in the docs) BTW, should we change VACUUM VERBOSE in the same way? If we do so, autovacuum can handle the reports of VACUUM VERBOSE and plan when to do VACUUM FULL, REINDEX and/or CLUSTER using the information. Is this worth doing? You mean having VACUUM VERBOSE return a result set? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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: [PATCHES] LDAP patch feature freeze
Albe Laurenz [EMAIL PROTECTED] writes: Any chance that my LDAP patch http://momjian.us/mhonarc/patches/msg0.html will get reviewed before the feature freeze? Feature freeze is the deadline for patch submission, not patch application. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Allow commenting of variables in postgresql.conf to - try 4
Joachim Wieland [EMAIL PROTECTED] writes: I did not check the changes you have done to set_config_option and the like but tested the commenting / uncommenting / changing of guc variables and the behavior and log output. The general idea (at least my idea) is that whenever a SIGHUP is received and there is some difference between the config file and the active value that the server is using, a notice message is written to the log. Notice message? Where did that come from? The behavior I thought people were after was just that variables previously defined by the file would revert to reset values if not any longer defined by the file. From a reviewer's point of view, it'd be nice if the patch did not contain so many useless changes of whitespace. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] patch implementing the multi-argument aggregates (SOC project)
Sergey E. Koposov [EMAIL PROTECTED] writes: Since the feature freeze is in a few days, I'm sending the first iteration of my patch implementing the multi-argument aggregates (PolyArgAgg) (SOC project) This patch is nowhere near ready for submission :-(. Most of the comments seem to be I don't know what to do here ... A general hint on the polymorphic stuff is that you should be able to exactly duplicate what's done for polymorphic functions --- or even better, get rid of the separate code for aggregates and just invoke the existing logic for functions. (You might need to refactor code a little bit to separate out the common functionality.) Instead of copying data inside advance_transition_function, it might be better for the caller to store the values into the right fields of a temporary FunctionCallInfoData struct, and just pass that to advance_transition_function. The names for the new aggregates seem a bit, how to say, terse and unfriendly. SQL generally tends to a more verbose style of naming. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] patch implementing the multi-argument aggregates (SOC
On Mon, 24 Jul 2006, Tom Lane wrote: Sergey E. Koposov [EMAIL PROTECTED] writes: Since the feature freeze is in a few days, I'm sending the first iteration of my patch implementing the multi-argument aggregates (PolyArgAgg) (SOC project) This patch is nowhere near ready for submission :-(. Most of the :-( But now at least I know that... comments seem to be I don't know what to do here ... No that's not quite true... I have only ~ 2-3 such comments, all others just express that I marked the places where I've had any little doubts and which I'll check additionally... A general hint on the polymorphic stuff is that you should be able to exactly duplicate what's done for polymorphic functions --- or even better, get rid of the separate code for aggregates and just invoke the existing logic for functions. (You might need to refactor code a little bit to separate out the common functionality.) Instead of copying data inside advance_transition_function, it might be better for the caller to store the values into the right fields of a temporary FunctionCallInfoData struct, and just pass that to advance_transition_function. Thank you for the hints, I'll think about them... The names for the new aggregates seem a bit, how to say, terse and unfriendly. SQL generally tends to a more verbose style of naming. The names for the functions came from SQL 2003 standart... Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: There are basically two ways you could go about this: 1. Make a new jointree leaf node type to represent a VALUES construct, and dangle the list of lists of expressions off that. 2. Make a new RangeTblEntry type to represent a VALUES construct, and just put a RangeTblRef to it into the jointree. The expressions dangle off the RangeTblEntry. You seem to have done *both*, which is certainly not what I had in mind. I'd drop the RangeTblEntry changes, I think. Good feedback -- thanks! But without the RTE, how would VALUES in the FROM clause work? Or should I just drop that part and focus on just the InsertStmt case? Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES] [HACKERS] 8.2 features?)
Joe Conway [EMAIL PROTECTED] writes: Good feedback -- thanks! But without the RTE, how would VALUES in the FROM clause work? Is it different from INSERT? I'm just imagining a Values node in the jointree and nothing in the rangetable. If I'm reading the spec correctly, VALUES is exactly parallel to SELECT in the grammar, which means that to use it in FROM you would need parentheses and an alias: SELECT ... FROM (SELECT ...) AS foo SELECT ... FROM (VALUES ...) AS foo ISTM that this should be represented using an RTE_SUBQUERY node in the outer query; the alias attaches to that node, not to the VALUES itself. So I don't think you need that alias field in the jointree entry either. If we stick with the plan of representing VALUES as if it were SELECT * FROM (valuesnode), then this approach would make the second query above have a structure like Query .rtable -RTE_SUBQUERY .subquery - Query .jointree - Values (leaving out a ton of detail of course, but those are the key nodes). To get this to reverse-list in the expected form, we'd need a small kluge in ruleutils.c that short-circuits the display of SELECT ... FROM etc when it sees a Values node at the top of the jointree. This seems like a fairly small price to pay for keeping Query in approximately its present form, though. One thought is that we might allow Query.jointree to point to either a FromExpr or a Values node, and disallow Values from appearing further down in the jointree (except perhaps after flattening of subqueries in the planner). The alternative is that there's a FromExpr atop the Values node in the jointree even in the simple case; which seems uglier but it might avoid breaking some code that expects the top level to always be FromExpr. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Resurrecting per-page cleaner for btree
ITAGAKI Takahiro [EMAIL PROTECTED] writes: This is a revised patch originated by Junji TERAMOTO for HEAD. [BTree vacuum before page splitting] http://archives.postgresql.org/pgsql-patches/2006-01/msg00301.php I think we can resurrect his idea because we will scan btree pages at-atime now; the missing-restarting-point problem went away. Have I missed something? Comments welcome. I think the only serious objection to this would be that it'd mean that tuples that should have an index entry might not have one. The current form of VACUUM does not care, but people keep raising the idea of doing retail vacuuming that operates by looking up index entries explicitly. You could certainly make a retail vacuumer do nothing if it fails to find the expected index entry, but ISTM that'd be a rather serious loss of consistency checking --- you could not tell the someone-already- deleted-it case apart from a bug in the vacuumer's index value computation or lookup. Personally I don't think retail vacuuming in that form will ever fly anyway, so I have no problem with installing the proposed patch, but I thought I'd better throw this comment out to see if anyone thinks it's a big deal. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Allow commenting of variables in postgresql.conf to - try 4
Am Montag, 24. Juli 2006 16:55 schrieb Stephen Frost: #2: That variable can *not* be changed by a reload. Notice-level message is sent to the log notifying the admin that the change requested could not be performed. This already happens. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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: [PATCHES] Allow commenting of variables in postgresql.conf to - try 4
On Mon, Jul 24, 2006 at 10:55:47AM -0400, Stephen Frost wrote: #2: That variable can *not* be changed by a reload. Notice-level message is sent to the log notifying the admin that the change requested could not be performed. This change could be either a revert to reset-value if it was removed/commented-out or an explicit change request to a different value. Right. And what I am voting for is to not only issue such a message once but every time a SIGHUP is received as long as the actively-used value differs from the value in the configuration file. One of the reasons for having this fall-back-to-default-value stuff is to make sure that an admin can restart a server and be sure that it will behave in the same way as when it was shut down. Moreover it's just clearer to send the notice message every time a SIGHUP is received since every reload is the admin's request to apply all of the values in the configuration file independently of what has happened in the past. Joachim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Allow commenting of variables in postgresql.conf to - try 4
Joachim Wieland wrote: On Mon, Jul 24, 2006 at 07:09:17PM +0200, Peter Eisentraut wrote: Am Montag, 24. Juli 2006 16:55 schrieb Stephen Frost: #2: That variable can *not* be changed by a reload. Notice-level message is sent to the log notifying the admin that the change requested could not be performed. This already happens. Not if the option gets commented/deleted, i.e.: shared_buffers = 8000 START #shared_buffers = 8000 HUP This does not issue a message at the moment. Because at the moment, the above does not change the value of shared_buffers. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] The vacuum-ignore-vacuum patch
Alvaro Herrera [EMAIL PROTECTED] writes: Hannu Krossing asked me about his patch to ignore transactions running VACUUM LAZY in other vacuum transactions. I attach a version of the patch updated to the current sources. nonInVacuumXmin seems useless ... perhaps a vestige of some earlier version of the computation? In general, it seems to me that a transaction running lazy vacuum could be ignored for every purpose except truncating clog/subtrans. Since it will never insert its own XID into the database (note: VACUUM ANALYZE is run as two separate transactions, hence the pg_statistic rows inserted by ANALYZE are not a counterexample), there's no need for anyone to include it as running in their snapshots. So unless I'm missing something, this is a safe change for lazy vacuum, but perhaps not for full vacuum, which *does* put its XID into the database. A possible objection to this is that it would foreclose running VACUUM and ANALYZE as a single transaction, exactly because of the point that we couldn't insert pg_statistic rows using a lazy vacuum's XID. I think there was some discussion of doing that in connection with enlarging ANALYZE's sample greatly --- if ANALYZE goes back to being a full scan or nearly so, it'd sure be nice to combine it with the VACUUM scan. However maybe we should just accept that as the price of not having multiple vacuums interfere with each other. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] The vacuum-ignore-vacuum patch
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hannu Krossing asked me about his patch to ignore transactions running VACUUM LAZY in other vacuum transactions. I attach a version of the patch updated to the current sources. nonInVacuumXmin seems useless ... perhaps a vestige of some earlier version of the computation? Hmm ... I remember removing a now-useless variable somewhere, but maybe this one escaped me. I don't have the code handy -- will check. In general, it seems to me that a transaction running lazy vacuum could be ignored for every purpose except truncating clog/subtrans. Since it will never insert its own XID into the database (note: VACUUM ANALYZE is run as two separate transactions, hence the pg_statistic rows inserted by ANALYZE are not a counterexample), there's no need for anyone to include it as running in their snapshots. So unless I'm missing something, this is a safe change for lazy vacuum, but perhaps not for full vacuum, which *does* put its XID into the database. But keep in mind that in the current code, clog truncation takes relminxid (actually datminxid) into account, not running transactions, so AFAICS this should affect anything. Subtrans truncation is different and it certainly should consider lazy vacuum's Xids. A possible objection to this is that it would foreclose running VACUUM and ANALYZE as a single transaction, exactly because of the point that we couldn't insert pg_statistic rows using a lazy vacuum's XID. I think there was some discussion of doing that in connection with enlarging ANALYZE's sample greatly --- if ANALYZE goes back to being a full scan or nearly so, it'd sure be nice to combine it with the VACUUM scan. However maybe we should just accept that as the price of not having multiple vacuums interfere with each other. Hmm, what about having a single scan for both, and then starting a normal transaction just for the sake of inserting the pg_statistics tuple? I think the interactions of Xids and vacuum and other stuff are starting to get complex; IMHO it warrants having a README.vacuum, or something. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] The vacuum-ignore-vacuum patch
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: A possible objection to this is that it would foreclose running VACUUM and ANALYZE as a single transaction, exactly because of the point that we couldn't insert pg_statistic rows using a lazy vacuum's XID. Hmm, what about having a single scan for both, and then starting a normal transaction just for the sake of inserting the pg_statistics tuple? We could, but I think memory consumption would be the issue. VACUUM wants a lotta memory for the dead-TIDs array, ANALYZE wants a lot for its statistics gathering ... even more if it's trying to take a larger sample than before. (This is probably why we kept them separate in the last rewrite.) I think the interactions of Xids and vacuum and other stuff are starting to get complex; IMHO it warrants having a README.vacuum, or something. Go for it ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: ISTM that this should be represented using an RTE_SUBQUERY node in the outer query; the alias attaches to that node, not to the VALUES itself. So I don't think you need that alias field in the jointree entry either. If we stick with the plan of representing VALUES as if it were SELECT * FROM (valuesnode), then this approach would make the second query above have a structure like Query .rtable - RTE_SUBQUERY .subquery - Query .jointree - Values (leaving out a ton of detail of course, but those are the key nodes). OK, I'll go try to wrap my mind around that this evening and see where it takes me. Thanks, Joe ---(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: [PATCHES] The vacuum-ignore-vacuum patch
Cc: pgsql-hackers removed, as this mail contains a patch.
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Hannu Krossing asked me about his patch to ignore transactions running
VACUUM LAZY in other vacuum transactions. I attach a version of the
patch updated to the current sources.
nonInVacuumXmin seems useless ... perhaps a vestige of some earlier
version of the computation?
Yup -- I checked that code and found out that nonInVacuumXmin can be
taken out as it's not used anywhere. One upside of this is that taking
it out means we can remove all diffs to GetSnapshotData. New patch
attached; it's a bit smaller than the last one.
I'm currently testing it. Since it appears there are no further
objections, I intend to commit it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/access/transam/twophase.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.21
diff -c -r1.21 twophase.c
*** src/backend/access/transam/twophase.c 14 Jul 2006 14:52:17 -
1.21
--- src/backend/access/transam/twophase.c 24 Jul 2006 22:16:35 -
***
*** 279,284
--- 279,285
gxact-proc.pid = 0;
gxact-proc.databaseId = databaseid;
gxact-proc.roleId = owner;
+ gxact-proc.inVacuum = false;
gxact-proc.lwWaiting = false;
gxact-proc.lwExclusive = false;
gxact-proc.lwWaitLink = NULL;
Index: src/backend/access/transam/xact.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.224
diff -c -r1.224 xact.c
*** src/backend/access/transam/xact.c 24 Jul 2006 16:32:44 - 1.224
--- src/backend/access/transam/xact.c 24 Jul 2006 22:16:35 -
***
*** 1529,1534
--- 1529,1535
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with
xid/xmin */
/* Clear the subtransaction-XID cache too while holding the
lock */
MyProc-subxids.nxids = 0;
***
*** 1764,1769
--- 1765,1771
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with xid/xmin */
/* Clear the subtransaction-XID cache too while holding the lock */
MyProc-subxids.nxids = 0;
***
*** 1927,1932
--- 1929,1935
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with
xid/xmin */
/* Clear the subtransaction-XID cache too while holding the
lock */
MyProc-subxids.nxids = 0;
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.244
diff -c -r1.244 xlog.c
*** src/backend/access/transam/xlog.c 14 Jul 2006 14:52:17 - 1.244
--- src/backend/access/transam/xlog.c 24 Jul 2006 22:16:35 -
***
*** 5413,5419
* StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true));
if (!shutdown)
ereport(DEBUG2,
--- 5413,5419
* StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true, false));
if (!shutdown)
ereport(DEBUG2,
Index: src/backend/catalog/index.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.269
diff -c -r1.269 index.c
*** src/backend/catalog/index.c 13 Jul 2006 16:49:13 - 1.269
--- src/backend/catalog/index.c 24 Jul 2006 22:16:35 -
***
*** 1367,1373
else
{
snapshot = SnapshotAny;
! OldestXmin = GetOldestXmin(heapRelation-rd_rel-relisshared);
}
scan = heap_beginscan(heapRelation, /* relation */
--- 1367,1374
else
{
snapshot = SnapshotAny;
! /* okay to ignore lazy VACUUMs here */
! OldestXmin = GetOldestXmin(heapRelation-rd_rel-relisshared,
true);
}
scan = heap_beginscan(heapRelation, /* relation */
Index: src/backend/commands/vacuum.c
Re: [PATCHES] Resurrecting per-page cleaner for btree
Tom Lane wrote: ITAGAKI Takahiro [EMAIL PROTECTED] writes: This is a revised patch originated by Junji TERAMOTO for HEAD. [BTree vacuum before page splitting] http://archives.postgresql.org/pgsql-patches/2006-01/msg00301.php I think we can resurrect his idea because we will scan btree pages at-atime now; the missing-restarting-point problem went away. Have I missed something? Comments welcome. I think the only serious objection to this would be that it'd mean that tuples that should have an index entry might not have one. The current form of VACUUM does not care, but people keep raising the idea of doing retail vacuuming that operates by looking up index entries explicitly. You could certainly make a retail vacuumer do nothing if it fails to find the expected index entry, but ISTM that'd be a rather serious loss of consistency checking --- you could not tell the someone-already- deleted-it case apart from a bug in the vacuumer's index value computation or lookup. Personally I don't think retail vacuuming in that form will ever fly anyway, so I have no problem with installing the proposed patch, but I thought I'd better throw this comment out to see if anyone thinks it's a big deal. Agreed. Reverse lookup of index entries will always be too slow. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] pgstattuple extension for indexes
Satoshi Nagayasu wrote:
Hi,
I'm working on an utility for b-tree index, called `pgstatindex`.
It reports b-tree index statistics like a pgstattuple as below.
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey1');
-[ RECORD 1 ]--+-
version| 2
tree_level | 2
index_size | 17956864
root_block_no | 361
internal_pages | 8
leaf_pages | 2184
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 90.07
leaf_fragmentation | 0
pgbench=#
I want to make this to contrib module like a pgstattuple,
and to make this open to public in a few days.
Do you think this is useful?
Yes, for performance debugging, I think.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] LDAP patch feature freeze
Tom Lane wrote: Albe Laurenz [EMAIL PROTECTED] writes: Any chance that my LDAP patch http://momjian.us/mhonarc/patches/msg0.html will get reviewed before the feature freeze? Feature freeze is the deadline for patch submission, not patch application. Right, and it will be applied this week, I think. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] pgstattuple extension for indexes
Satoshi Nagayasu wrote: Hi, I'm working on an utility for b-tree index, called `pgstatindex`. Does it make sense to merge the pgstatindex stuff with pgstattuple, and have the fragmentation report into pgstatindex instead of pgstattuple itself? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Time zone definitions to config files
Joachim Wieland [EMAIL PROTECTED] writes: Here's the patch that generalizes the australian_timezones hack by moving the compiled-in time zone definitions into a text file. The text file to use is chosen via a guc. Applied with some revisions --- mostly, that I didn't like restricting timezone_abbreviations to be changed only via postgresql.conf. The old australian_timezones setting was always USERSET, and I think people would have had a legitimate gripe about loss of flexibility if its replacement wasn't. Fortunately this wasn't too hard to change. I also editorialized a bit on the file placement and the parsing code. The documentation is still in need of help ... in particular, Table B-4 (timezone names) is now out of sync with reality. I am not sure whether to try to fix it, or just remove it and tell people to look at the pg_timezonenames view. Thoughts? If you want to fix it, please send a patch. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pgstattuple extension for indexes
Alvaro Herrera [EMAIL PROTECTED] wrote: Satoshi Nagayasu wrote: I'm working on an utility for b-tree index, called `pgstatindex`. Does it make sense to merge the pgstatindex stuff with pgstattuple, and have the fragmentation report into pgstatindex instead of pgstattuple itself? It sounds good. We will have two separate commands: - pgstattuple: returns tuple-level information - pgstatindex: returns page-level information We can use tuple-level info to check LP_DELETE flags on index tuples, and use page-level info to check needs for REINDEX. Do we add pgstatindex as a new contrib module, or merge it into contrib/pgstattuple? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[Fwd: Re: [PATCHES] Patch for - Change LIMIT/OFFSET to use int8]
I sent this patch already.
Can somebody verify this patch?
Thanks
Dhanaraj
---BeginMessage---
I have made the changes appropriately. The regression tests passed.
Since I do not have enough resources, I could not test for a large number.
It works for a small table. If anybody tests for int8 value, it is
appreciated.
Also, it gives the following error msg, when the input exceeds the int8
limit.
ERROR: bigint out of range
I attach the patch. Pl. check it.
Thanks
Dhanaraj
Tom Lane wrote:
Dhanaraj M [EMAIL PROTECTED] writes:
I attach the patch for the following TODO item.
SQL COMMAND
* Change LIMIT/OFFSET to use int8
This can't possibly be correct. It doesn't even change the field types
in struct LimitState, for example. You've missed half a dozen places
in the planner that would need work, too.
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
*** ./src/backend/executor/nodeLimit.c.orig Tue Jul 11 22:31:51 2006
--- ./src/backend/executor/nodeLimit.c Wed Jul 12 00:46:11 2006
***
*** 23,28
--- 23,29
#include executor/executor.h
#include executor/nodeLimit.h
+ #include catalog/pg_type.h
static void recompute_limits(LimitState *node);
***
*** 226,239
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
- if (node-limitOffset)
- {
- node-offset =
-
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
-
econtext,
-
isNull,
-
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
--- 227,251
{
ExprContext *econtext = node-ps.ps_ExprContext;
boolisNull;
+ Oid type;
+
+ if (node-limitOffset)
+ {
+ type = ((Const *) node-limitOffset-expr)-consttype;
+
+ if(type == INT8OID)
+ node-offset =
+
DatumGetInt64(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
+ else
+ node-offset =
+
DatumGetInt32(ExecEvalExprSwitchContext(node-limitOffset,
+
econtext,
+
isNull,
+
NULL));
/* Interpret NULL offset as no offset */
if (isNull)
node-offset = 0;
***
*** 249,259
if (node-limitCount)
{
node-noCount = false;
! node-count =
!
DatumGetInt32(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
node-noCount = true;
--- 261,282
if (node-limitCount)
{
node-noCount = false;
! type = ((Const *) node-limitCount-expr)-consttype;
!
! if(type == INT8OID)
! node-count =
!
DatumGetInt64(ExecEvalExprSwitchContext(node-limitCount,
!
econtext,
!
isNull,
!
NULL));
! else
!
Re: [PATCHES] pgstattuple extension for indexes
ITAGAKI Takahiro [EMAIL PROTECTED] writes: Do we add pgstatindex as a new contrib module, or merge it into contrib/pgstattuple? I believe Alvaro was suggesting that you should add it as an additional SQL function within contrib/pgstattuple. That'd be my advice too --- I don't see a reason to break this out as a separate contrib module. regards, tom lane ---(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
