Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant
On Tue, 2005-07-19 at 22:24 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Short patch enclosed to turn off writing of commit-status hint bits. Doesn't this entirely destroy the ability to truncate clog, and therefore the ability to survive XID wraparound? I hope not for all our sakes, since the hint bits are not WAL logged and anything that relies upon them would be fragile. If we were going to move a table to WORM storage, then we'd have done VACUUM FREEZE first anyway, which would be required to avoid XID wraparound. This patch would then guarantee that no further system-initiated writes take place. Not AFAICS comments from vacuum.c included: /* * Now scan all the pages that we moved tuples onto and update tuple * status bits. This is not really necessary, but will save time for * future transactions examining these tuples. */ update_hint_bits(onerel, fraged_pages, num_fraged_pages, last_move_dest_block, num_moved); then again on the function itself... /* * update_hint_bits() -- update hint bits in destination pages * * Scan all the pages that we moved tuples onto and update tuple status bits. * This is normally not really necessary, but it will save time for future * transactions examining these tuples. * * * For the non-freeze case, one wonders whether it wouldn't be better to skip * this work entirely, and let the tuple status updates happen someplace * that's not holding an exclusive lock on the relation. */ It probably also breaks subxact and multixact logging, but I haven't looked closely... AFAIK this has nothing to do with that, since those locks are not persistent across a crash,,,but conceivably something in 2PC might be effected. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Problems compiling Postgresql 8.0.3 on 10.4
Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -dynamiclib -install_name /usr/local/pgsql/lib/libpq.4.dylib -compatibility_version 4 -current_version 4.0 -multiply_defined suppress fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../src/port -lresolv -o libpq.4.0.dylib /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) make[3]: *** [libpq.4.0.dylib] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 ---(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] [HACKERS] Problems compiling Postgresql 8.0.3 on 10.4
Jamie, That's strange, I have 8.03 building fine on 10.4 ? Did you simply do a make without a make clean and run configure again ? Dave On 20-Jul-05, at 3:37 AM, Jamie Deppeler wrote: Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wendif-labels -fno-strict-aliasing -dynamiclib -install_name /usr/ local/pgsql/lib/libpq.4.dylib -compatibility_version 4 - current_version 4.0 -multiply_defined suppress fe-auth.o fe- connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o -L../../../ src/port -lresolv -o libpq.4.0.dylib /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) make[3]: *** [libpq.4.0.dylib] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all] Error 2 make: *** [all] Error 2 ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Problems compiling Postgresql 8.0.3 on 10.4
On Jul 20, 2005, at 5:58 AM, Dave Cramer wrote: Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make /usr/bin/libtool: for architecture: cputype (16777234) cpusubtype (0) file: -lSystem is not an object file (not allowed in a library) Install the new Xcode. that should fix it. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2005-07-19 at 22:24 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Short patch enclosed to turn off writing of commit-status hint bits. Doesn't this entirely destroy the ability to truncate clog, and therefore the ability to survive XID wraparound? I hope not for all our sakes, since the hint bits are not WAL logged and anything that relies upon them would be fragile. We don't rely on any one write of them to work, but that doesn't mean that we can indefinitely postpone writing them. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant
On Wed, 2005-07-20 at 09:24 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2005-07-19 at 22:24 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Short patch enclosed to turn off writing of commit-status hint bits. Doesn't this entirely destroy the ability to truncate clog, and therefore the ability to survive XID wraparound? I hope not for all our sakes, since the hint bits are not WAL logged and anything that relies upon them would be fragile. We don't rely on any one write of them to work, but that doesn't mean that we can indefinitely postpone writing them. OK, I think I understand where you're coming from now. A table can't be migrated to read-only media until all of its tuples have an xmin of FrozenTransactionId; I said the following, which was wrong: This should allow migration of older child tables to hierarchical storage when using a large historical table design. The patch doesn't directly contribute to that goal, though is of value in a large historical table design with many read only child tables (and other situations). But that comment wasn't the only inspiration for the patch. When VACUUM freezes the xid, it *does* make sense at that point to update the hint bits as a performance optimization. That isn't required though, and *can* be indefinitely postponed, AFAICS. All of the tqual routines will still work just as well without the hint bits set. If you know different, I'll need a deeper explanation before I understand. IMHO, the direction the patch is going in is still worthwhile because of these issues: 1. Any block read may attempt to set hint bits, which dirties the block and must be written out. So *reads* can result in heavier write activity at checkpoint time. That effects both OLTP and DW systems: Random read transactions against a large table will be worst effected, since we may end up writing the block once for each read. 2. A lazy vacuum may also dirty a block, even when it has done nothing else useful to that block. Worse, if we vacuum a table that is bigger than shared_buffers (or close), then we will end up having to evict dirty buffers that the vacuum itself has written in order to continue the vacuum. Since Vacuum is two-pass, we may end up writing a block *twice*, once where we set the hint bits and then again later where we remove the tuples and re-write. So this patch will allow a normal VACUUM to perform better on larger tables. (1) is a pain, but there's no point solving it without also solving (2). The patch would fail an Assert test during a VACUUM, since the info bits are not actually set if cache_txn_status_with_data = false during VACUUM. As a result of (2), perhaps we should remove all of the SetBufferCommitInfoNeedsSave calls in HeapTupleSatisfiesVacuum, and add a call to SetBufferCommitInfoNeedsSave that overrides cache_txn_status_with_data when we actually freeze a row. (Or perhaps that should be a VACUUM FAST command?) That way we would set the hint bits *only* when we freeze a row and not at any other time. If we further reduced the number of times we dirty the block *at all* on the first pass of a VACUUM, we would reduce the chance of writing twice. We could save the setting of frozen transactions until the second phase, i.e. only dirty the block if (pgchanged vacrelstats-num_dead_tuples prev_dead_count) The patch sets cache_txn_status_with_data as a USERSET, with the intention that particular read-only users would not wish to have their read-only transactions turn into write transactions. There was no intention to prevent VACUUM, not to avoid the optimisation of writing hint bits on a VACUUM FREEZE nor to set full read only status - which we discussed previously but is a much longer project. Sorry for any confusion caused in my initial patch submission. Does my longer explanation make sense of what the patch is trying to achieve. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Interval-day patch
I have applied this patch with significant adjustments. I changed your
simplify function into two new functions, justify_hours() and
justify_days(), to handle the adjustment of interval values to hours
24 and days 30. Do we want to separate functions?
I used date2j and j2date to add days to the interval value (you used a
comment as a place-holder). I also went through all the Interval
mentions and made sure everything was handling the new 'day' field
properly.
SELECT '2005-04-03 00:00:00'::timestamp WITH TIME ZONE + '1 day';
?column?
2005-04-04 00:00:00-04
SELECT '2005-04-03 00:00:00'::timestamp WITH TIME ZONE + '24 hours';
?column?
2005-04-04 01:00:00-04
This looks a little strange:
SELECT '2005-04-04 00:00:00'::timestamp with time zone - '2005-04-03
00:00:00'::timestamp with time zone;
--
23:00:00
(1 row)
SELECT '2005-04-04 01:00:00'::timestamp with time zone - '2005-04-03
00:00:00'::timestamp with time zone;
?column?
--
1 day
When you subtract two timestamps, do we return the hours or days of
difference? What happens now is the difference is in hours/time, and
hours are rolled up into days. Is this what we want?
We have this TODO item:
o Allow TIMESTAMP WITH TIME ZONE to store the original timezone
information, either zone name or offset from UTC [timezone]
If the TIMESTAMP value is stored with a time zone name, interval
computations should adjust based on the time zone rules.
It was originally added so we could distinguish 24 hours from 1 day. Do
we still need this TODO?
---
Michael Glaesemann wrote:
Please find attached a patch which adds a day field to the interval
struct so that we can treat INTERVAL '1 day' differently from
INTERVAL '24 hours' in DST-aware situations. It also includes a
function called interval_simplify() which takes an interval argument
and returns an interval where hours over 24 are promoted to days, e.g.,
template1=# select interval_simplify('3 months -11 days 79 hours 2
minutes'::interval);
interval_simplify
--
3 mons -7 days -16:58:00
(1 row)
If anyone has better ideas for the name of this function, please let
me know.
I've modified the regression tests, but still need to add additional
tests for the interval_simplify function, and I want to add a few
more tests for the new interval behavior. Also, the docs will need to
be updated to mention the new behavior. I plan on doing this in over
the next couple of days.
This is some of the first C I've hacked, and the first patch I've
submitted that's more than a documentation or a simple one-liner (and
even that one got worked over pretty good :) ), so I fully expect
some mistakes to be found. Please let me know and I'll do my best to
fix them.
In timestamp.c, I suspect that AdjustIntervalForTypmod,
interval_scale will need some modifications, though I'm not quite
sure what this code is doing. I've left them as-is. I've made some
changes to interval2tm, but believe that the changes I've made may
not be adequate. Given sufficient instruction, I'll be happy to make
the necessary changes to these functions.
A few things I noticed while I was working:
In interval_mul and interval_div, I'm wondering whether 30.0 and 24.0
shouldn't be substituted for 30 and 24 in the non-integer-timestamp
code path, as these are floats. Perhaps it doesn't make a difference
for multiplication, but I see similar usage in interval_cmp_interval.
I've left the code as-is.
In the deconstruct_array calls in interval_accum and interval_avg,
the size of interval is passed as a magic number (16). I think this
could be abstracted out, such as #define SIZEOF_INTERVAL 16 to make
the code a bit more robust (albeit just a little). Is this a
reasonable change?
Michael Glaesemann
grzm myrealbox com
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 3: 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| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.267
diff -c -c -r1.267 func.sgml
***
Re: [PATCHES] Writing Commit Status hint bits (was Re: [HACKERS] Constant WAL replay)
Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2005-07-20 at 09:24 -0400, Tom Lane wrote: We don't rely on any one write of them to work, but that doesn't mean that we can indefinitely postpone writing them. OK, I think I understand where you're coming from now. Apparently not :-( When VACUUM freezes the xid, it *does* make sense at that point to update the hint bits as a performance optimization. The hint bits are not really relevant when xmin = FrozenTransactionId, since any examiner of the tuple would consider that XID committed anyway. Besides, the hint bit is guaranteed set in that scenario; note the Assert where vacuum is setting it: HeapTupleHeaderSetXmin(tuple.t_data, FrozenTransactionId); /* infomask should be okay already */ Assert(tuple.t_data-t_infomask HEAP_XMIN_COMMITTED); The scenario in which the hint bit *must* be set is where it is for an XID for which we have deleted the relevant section of pg_clog, which we are willing to do well before freeze occurs, if we know that all the relevant XIDs have been hinted. See TruncateCLOG. Your patch breaks that logic by not considering hint-bit updates as changes that must be flushed to disk by checkpoint. 1. Any block read may attempt to set hint bits, which dirties the block and must be written out. So *reads* can result in heavier write activity at checkpoint time. Sure, but the alternative is heavier activity in repeated checks of pg_clog to find out commit state that a previous examiner of the tuple already found out. The patch supposes that one write is worse than N reads, which is clearly a loss at some not-exceedingly-large value of N. If we thought that was a good tradeoff, we might as well not have the hint bits at all. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Interval-day patch
Bruce Momjian pgman@candle.pha.pa.us writes: We have this TODO item: o Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC [timezone] If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules. It was originally added so we could distinguish 24 hours from 1 day. Do we still need this TODO? That's a completely separate TODO item. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Interval-day patch
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: We have this TODO item: o Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC [timezone] If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules. It was originally added so we could distinguish 24 hours from 1 day. Do we still need this TODO? That's a completely separate TODO item. OK. Is it clear enough? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Changes for AIX buildfarm
Tom Lane wrote: Fair enough. But I'm concerned about the proposed patch because it seems to revert a deliberate change made some time ago: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/Makefile.shlib#r ev1.65 http://archives.postgresql.org/pgsql-committers/2002-10/msg00054.php so I think we need more eyeballs on the problem before deciding this is a good fix. A potentially more correct solution would be to set SHLIB_LINK in the makefiles of the affected modules only. Certainly we don't want to link with all libraries all the time. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] [COMMITTERS] pgsql: Add 'day' field to INTERVAL so 1 day interval
Kris Jurka wrote: Bruce Momjian wrote: Log Message: --- Add 'day' field to INTERVAL so 1 day interval can be distinguished from 24 hours. This is very helpful for daylight savings time: Seems to have broken the contrib/btree_gist interval regression tests http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=dragonflydt=2005-07-20%2017:30:00 I have applied a patch which adds the 'date' value for interval to the code. However, the regression still does not pass. Here is a query: SELECT count(*) FROM intervaltmp WHERE a = '199 days 21:21:23'::interval; count --- 1 (1 row) that should return 1, but returns 0 in the current code. Does anyone have any ideas on a fix? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [COMMITTERS] pgsql: Add 'day' field to INTERVAL so 1 day interval
This is also broken for make check with --enable-integer-datetimes on kookaburra (AIX). It looks like the other members of buildfarm that fail on contrib aren't using integer datetimes. -rocco -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: Wednesday, July 20, 2005 2:21 PM To: Kris Jurka Cc: PostgreSQL-patches Subject: Re: [PATCHES] [COMMITTERS] pgsql: Add 'day' field to INTERVAL so 1 day interval Kris Jurka wrote: Bruce Momjian wrote: Log Message: --- Add 'day' field to INTERVAL so 1 day interval can be distinguished from 24 hours. This is very helpful for daylight savings time: Seems to have broken the contrib/btree_gist interval regression tests http://pgbuildfarm.org/cgi-bin/show_log.pl?nm=dragonflydt=200 5-07-20%2017:30:00 I have applied a patch which adds the 'date' value for interval to the code. However, the regression still does not pass. Here is a query: SELECT count(*) FROM intervaltmp WHERE a = '199 days 21:21:23'::interval; count --- 1 (1 row) that should return 1, but returns 0 in the current code. Does anyone have any ideas on a fix? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
