Re: [HACKERS] backwards-compat problem?
Got it. --- Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Can we ensue that this is listed in the release notes clearly then? Bruce hasn't made up the incompatibilities to note section yet, but when he does, it should certainly be mentioned. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] set constraints docs page
Kevin Brown wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I object to creating gratuitous incompatibilities with the SQL standard, which will obstruct legitimate features down the road. The SQL standard says it is schema.constraint. Is there a case for enforcing uniqueness on constraint names, then? Other than SQL92 says so? Very little. This seems to me to be a design error in the spec. Per-table constraint names are easier to work with --- if they're global across a schema then you have a serious problem avoiding collisions. I assume that SQL99 and later don't specify anything different than what SQL92 calls for in this regard? Without any meaningful guidance from the spec, the best we can do is support per-table constraint names and provide optional (via a GUC variable) support for SQL92-compliant constraint names. Let the DBA decide which (if not both) is best for his situation. Inasmuch as one of our selling points is our compliance with the SQL spec, I see little reason to entirely avoid compliance with the spec on this issue -- just make it possible to do something else when/if necessary. The two approaches aren't necessarily mutually exclusive (though SQL99 compliance on constraint names would obviously make it unnecessary to specify a tablename along with a constraint name), so I see little problem here. But the current arrangement is obviously untenable, because it allows you to create a situation (multiple constraints by the same name) that you can't reasonably extricate yourself from. Well, it seems if we want to continue to allow the same constraint name to be used by different tables in the same schema, we have to print the tablename in the error message. Would someone actually be looking for a standards-compliant error string? We have already extended the standard --- either we revert that, or we have to go the entire way and print the table name. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] NOTICE vs WARNING
Surely a WARNING is a problem that you should probably fix? Or at least pay attention to. My thought is that you could turn of NOTICES and not worry. (Which is what I sometimes do during restore, etc.) Chris - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: PostgreSQL Development [EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 12:44 AM Subject: [HACKERS] NOTICE vs WARNING Can someone explain in succinct and general terms what the difference between a NOTICE and a WARNING is? I'm currently examining the validity of notice and warning messages throughout the backend, but I find these categories to be applied inconsistently. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I am back
That really should be up to you. Conferences are a great way to lift the project's profile, and PostgreSQL talks are very popular and well attended. However, as your email seems to suggest, they're pretty time consuming and generally pretty far away from home. I was definitely feeling this last year. I don't mind the trips. I usually look forward to it, then a few days before, I dread it because my life has to stop while I travel, then when I return I realize how valuable the trip was, and I catch up on my email. The only downside is my being away from email for a week every month. I read some email while I am away, but I don't have the time to read most of it because the trips are usually busy times for me. I'll do the trips - unfortunately it'll cost me $A2000 each time to fly to the states economy :P Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Need Documentation(information) about PostgreSql's structure
Help me, please. I need to add any funtions to PostgreSql's source v. 7.32. It is too hard for a newbie himself to know what each function does. I try to understand PostgreSql structure through readme files, postgres tutorial and commentaries in postgresql source but along time i can't understand postgresql structure. -- I need to grab sql queries sometimes: before parser, after parser, after optimizer, after rewriter (and before exccuter). Besides, i need to work with views. -- I know it take a lot of time to explain all i need. Tell me, please, where i can find necessary information. Thank you very much. âÅÚÙÍÑÎÎÙÊ.bmp Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] NOTICE vs WARNING
Peter Eisentraut [EMAIL PROTECTED] writes: Can someone explain in succinct and general terms what the difference between a NOTICE and a WARNING is? I'm currently examining the validity of notice and warning messages throughout the backend, but I find these categories to be applied inconsistently. I'm sure they are :-(. WARNING was invented much later than NOTICE, and I don't believe anyone tried to come up with a hard-and-fast distinction, much less go through the code line-by-line to apply it consistently to existing NOTICEs. I'd say that WARNING means this is probably wrong, you'd better take a close look while NOTICE is something that's probably OK but we want to tell the user what we're doing. Dunno whether that's precise enough though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Date input changed in 7.4 ?
Hi all, I noticed that some date are not anymore accepted: Postgres 7.3.3: test=# select '18/03/71'::date; date 1971-03-18 (1 row) Postgres 7.4beta1: test=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 is this the indendeed behaviour ? Regards Gaetano ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Date input changed in 7.4 ?
Mendola Gaetano wrote: Hi all, I noticed that some date are not anymore accepted: Postgres 7.3.3: test=# select '18/03/71'::date; date 1971-03-18 (1 row) Postgres 7.4beta1: test=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 is this the indendeed behaviour ? Yes, we now honor datestyle to determine how to deal with dates where the year is at the end. You can set your date style to 'euopean' or the new 'DMY' value to allow this. This highlights the fact I need to get the compatibility section written for the history file soon. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Date input changed in 7.4 ?
Mendola Gaetano [EMAIL PROTECTED] writes: I noticed that some date are not anymore accepted: test=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 is this the indendeed behaviour ? If it does not match your DateStyle setting, then yes. regression=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 regression=# show DateStyle ; DateStyle --- ISO, MDY (1 row) regression=# set datestyle = dmy; SET regression=# select '18/03/71'::date; date 1971-03-18 (1 row) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Date input changed in 7.4 ?
Bruce Momjian [EMAIL PROTECTED] wrote: Mendola Gaetano wrote: Hi all, I noticed that some date are not anymore accepted: Postgres 7.3.3: test=# select '18/03/71'::date; date 1971-03-18 (1 row) Postgres 7.4beta1: test=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 is this the indendeed behaviour ? Yes, we now honor datestyle to determine how to deal with dates where the year is at the end. You can set your date style to 'euopean' or the new 'DMY' value to allow this. This highlights the fact I need to get the compatibility section written for the history file soon. May I also suggest to change in date.c the 3 generic error: invalid input syntax for date: with more comprensive messages ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Date input changed in 7.4 ?
Tom Lane [EMAIL PROTECTED] wrote: Mendola Gaetano [EMAIL PROTECTED] writes: I noticed that some date are not anymore accepted: test=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 is this the indendeed behaviour ? If it does not match your DateStyle setting, then yes. regression=# select '18/03/71'::date; ERROR: invalid input syntax for date: 18/03/71 regression=# show DateStyle ; DateStyle --- ISO, MDY (1 row) regression=# set datestyle = dmy; SET regression=# select '18/03/71'::date; date 1971-03-18 (1 row) Great. How I already suggest to Bruce I think is better give an hint on the error reported just to avoid to be overloaded of emails like mine. My cent. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Date input changed in 7.4 ?
Mendola Gaetano [EMAIL PROTECTED] writes: May I also suggest to change in date.c the 3 generic error: invalid input syntax for date: with more comprensive messages ? That's easier said than done; there are enough different valid syntaxes that it's not always obvious what the user's intent was. (Indeed, the reason for this change was exactly that the code was guessing wrong too much of the time.) See also the thread at http://archives.postgresql.org/pgsql-bugs/2003-08/msg00113.php I'm currently thinking about reporting invalid syntax if ParseDateTime() fails and invalid field value if the various Decode() routines fail, but I'm quite unsure that that will be helpful ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Date input changed in 7.4 ?
How about reporting the current DateStyle in such messages? Then it should be clear if the date parse failed because of a mismatch. andrew Tom Lane wrote: Mendola Gaetano [EMAIL PROTECTED] writes: May I also suggest to change in date.c the 3 generic error: invalid input syntax for date: with more comprensive messages ? That's easier said than done; there are enough different valid syntaxes that it's not always obvious what the user's intent was. (Indeed, the reason for this change was exactly that the code was guessing wrong too much of the time.) See also the thread at http://archives.postgresql.org/pgsql-bugs/2003-08/msg00113.php I'm currently thinking about reporting invalid syntax if ParseDateTime() fails and invalid field value if the various Decode() routines fail, but I'm quite unsure that that will be helpful ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] table-level and row-level locks.
if table and page are locked in src/backend/storage/lmgr/lmgr.c by LockRelation() and LockPage respectively, in which file and by which function is a row locked? thanks Jenny From: Jenny - [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [HACKERS] table-level and row-level locks. Date: Sun, 24 Aug 2003 11:30:41 -0700 A row lock is represented by storing the locking transaction's ID in xmax and setting the HEAP_MARKED_FOR_UPDATE infomask bit. Where is 'xmax' found? is it at code level or on disk? thanks Jenny From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [HACKERS] table-level and row-level locks. Date: Wed, 20 Aug 2003 14:45:23 -0400 Koichi Suzuki [EMAIL PROTECTED] writes: I need to know where such lock marks are stored in the source level. A row lock is represented by storing the locking transaction's ID in xmax and setting the HEAP_MARKED_FOR_UPDATE infomask bit. The bit is needed to distinguish this from the case where the transaction is deleting the tuple. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html _ Get MSN 8 and help protect your children with advanced parental controls. http://join.msn.com/?page=features/parental ---(end of broadcast)--- TIP 8: explain analyze is your friend _ MSN 8: Get 6 months for $9.95/month. http://join.msn.com/?page=dept/dialup ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] WHERE order
It would appear that in 7.4 the order of clauses in WHERE affects the execution time (not output if AND). I would think that a simple optimization would be to push off evaluation of a subplan whenever possible by re-arranging AND statements. In the below example, it gives an order of magnitude speed increase on a small dataset. EXPLAIN ANALYZE SELECT ss.service_id FROM service.service AS ss JOIN account.account AS a ON (ss.account_id = a.parent_account_id) WHERE NOT EXISTS (SELECT subservice_id FROM service.combination JOIN service.service using (service_id) WHERE account_id = a.account_id AND subservice_id = ss.service_id) AND (account_id_exposed_to IS NULL OR account_id_exposed_to = a.account_id) AND a.account_id = 8219; QUERY PLAN Hash Join (cost=3.17..5755.34 rows=22 width=4) (actual time=46.88..4552.81 rows=15 loops=1) Hash Cond: (outer.account_id = inner.parent_account_id) Join Filter: ((NOT (subplan)) AND ((outer.account_id_exposed_to IS NULL) OR (outer.account_id_exposed_to = inner.account_id))) - Seq Scan on service ss (cost=0.00..46.16 rows=916 width=12) (actual time=0.20..11.53 rows=916 loops=1) - Hash (cost=3.16..3.16 rows=1 width=8) (actual time=0.12..0.12 rows=0 loops=1) - Index Scan using account_pkey on account a (cost=0.00..3.16 rows=1 width=8) (actual time=0.06..0.08 rows=1 loops=1) Index Cond: (account_id = 8219) SubPlan - Nested Loop (cost=0.00..259.12 rows=3 width=4) (actual time=12.93..12.93 rows=0 loops=350) - Seq Scan on combination (cost=0.00..155.21 rows=26 width=8) (actual time=6.13..12.66 rows=4 loops=350) Filter: (subservice_id = $1) - Index Scan using service_pkey on service (cost=0.00..3.98 rows=1 width=4) (actual time=0.04..0.04 rows=0 loops=1553) Index Cond: (outer.service_id = service.service_id) Filter: (account_id = $0) Total runtime: 4553.59 msec (15 rows) QUERY PLAN Hash Join (cost=3.17..5755.34 rows=22 width=4) (actual time=17.27..449.76 rows=15 loops=1) Hash Cond: (outer.account_id = inner.parent_account_id) Join Filter: (((outer.account_id_exposed_to IS NULL) OR (outer.account_id_exposed_to = inner.account_id)) AND (NOT (subplan))) - Seq Scan on service ss (cost=0.00..46.16 rows=916 width=12) (actual time=0.20..9.65 rows=916 loops=1) - Hash (cost=3.16..3.16 rows=1 width=8) (actual time=0.12..0.12 rows=0 loops=1) - Index Scan using account_pkey on account a (cost=0.00..3.16 rows=1 width=8) (actual time=0.06..0.08 rows=1 loops=1) Index Cond: (account_id = 8219) SubPlan - Nested Loop (cost=0.00..259.12 rows=3 width=4) (actual time=5.83..5.83 rows=1 loops=74) - Seq Scan on combination (cost=0.00..155.21 rows=26 width=8) (actual time=4.81..5.42 rows=7 loops=74) Filter: (subservice_id = $1) - Index Scan using service_pkey on service (cost=0.00..3.98 rows=1 width=4) (actual time=0.04..0.04 rows=0 loops=554) Index Cond: (outer.service_id = service.service_id) Filter: (account_id = $0) Total runtime: 450.54 msec (15 rows) EXPLAIN ANALYZE SELECT ss.service_id FROM service.service AS ss JOIN account.account AS a ON (ss.account_id = a.parent_account_id) WHERE (account_id_exposed_to IS NULL OR account_id_exposed_to = a.account_id) AND NOT EXISTS (SELECT subservice_id FROM service.combination JOIN service.service using (service_id) WHERE account_id = a.account_id AND subservice_id = ss.service_id) AND a.account_id = 8219; signature.asc Description: This is a digitally signed message part
Re: [HACKERS] NOTICE vs WARNING
Christopher Kings-Lynne writes: Surely a WARNING is a problem that you should probably fix? How are should and probably defined? Or at least pay attention to. If it were in fact the characteristic of a NOTICE that you need not pay attention to them, why do we have them? My thought is that you could turn of NOTICES and not worry. Well, there are plenty of NOTICE instances that carry a definite need to worry, such as identifier truncation, implicitly added FROM items, implicit changes to types specified as opaque, unsupported and ignored syntax clauses. I have a slight feeling that these two categories cannot usefully be distinguished, but I'm interested to hear other opinions. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Networking in 7.4?
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Do we have full Rendezvous and IPv6 support in 7.4? That is, sufficient to shout about in our PR materials? The IPv6 support does everything you could want AFAIK, and is certainly worth a bullet point. I'm not sure how full the Rendezvous support is; we have some Rendezvous-specific code in the postmaster now, but I have little idea what it does or whether there is more stuff that Rendezvous users might wish for. Might be wise to avoid the word full in this context. Unless there is someone on the list who knows Rendezvous well enough to offer an opinion? The Rendezvous patch submitter doesn't believe there is anything more to add --- he says it just works. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 7: don't forget to increase your free space map settings
[HACKERS] 2-phase commit
Hi, As the 7.4 beta rolls on, I thought now would be a good time to start talking about the future. I have a potential need in the future for distributed transactions (XA). To get that from Postgres, I'd need two-phase commit, I think. There is someone working on such a project (http://snaga.org/pgsql/), but last time it was discussed here, it received a rather lukewarm reception (see, e.g., the thread starting at http://archives.postgresql.org/pgsql-hackers/2003-06/msg00752.php). While at OSCON, I had a discussion with Joe Conway, Bruce Momjian, and Greg Sabino Mullane about 2PC. Various people expressed various opinions on the topic, but I think we agreed on the following. The relevant folks can correct me if I'm wrong: Two-phase commit has theoretical problems, but it is implemented in several enterprise RDBMS. 2PC is something needed by certain kinds of clients (especially those with transaction managers), so if PostgreSQL doesn't have it, PostgreSQL just won't get supported in that arena. Someone is already working on 2PC, but may feel unwanted due to the reactions last heard on the topic, and may not continue working unless he gets some support. What is a necessary condition for such support is to get some idea of what compromises 2PC might impose, and thereafter to try to determine which such compromises, if any, are acceptable ones. I think the idea here is that, while in most cases a pretty-good implementation of a desirable feature might get included in the source on the grounds that it can always be improved upon later, something like 2PC has the potential to do great harm to an otherwise reliable transaction manager. So the arguments about what to do need to be aired in advance. I (perhaps foolishly) volunteered to undertake to collect the arguments in various directions, on the grounds that I can contribute no code, but have skin made of asbestos. I thought I'd try to collect some information about what people think the problems and potentially acceptable compromises are, to see if there is some way to understand what can and cannot be contemplated for 2PC. I'll include in any such outline the remarks found in the -hackers thread referenced above. Any objections? A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] NOTICE vs WARNING
Peter Eisentraut [EMAIL PROTECTED] writes: Well, there are plenty of NOTICE instances that carry a definite need to worry, such as identifier truncation, implicitly added FROM items, implicit changes to types specified as opaque, unsupported and ignored syntax clauses. Of course, some of those may be misclassified... I have a slight feeling that these two categories cannot usefully be distinguished, but I'm interested to hear other opinions. I would say that NOTICEs are things that are routine in certain contexts. We would not bother with the NOTICE at all if we thought it held no interest, but often it doesn't have any. Ignored syntax clauses probably ought to be WARNINGs, since the message is telling you that what you asked for isn't going to be done. The other examples you give seem appropriate as NOTICEs. In particular, the notices about changing opaque types to something else are a routine occurrence in upgrading old schemas, and so I think it's reasonable for them to be NOTICEs. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12
On Mon, 25 Aug 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 21 Aug 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Wait, he's in australia, what if he's getting the edge case the other way. I'm inclined to fix to_date by decomposing the code differently --- it should avoid the coercion to timestamp, which is a waste of cycles anyway. But is to_timestamp (and more generally timestamp's input converter) broken? If so, how can we do better? I don't think we can entirely avoid the problem of a transition between local and GMT time. Yes. Timestamp with timezone is broken on the same boundaries in general. I'm not really sure how to do better without some work, it seems we end up with multiple different input values getting the same internal representation so we can differentiate which version of the input was used to get there (whether the user said 1901-12-13 23:00 or 1901-12-14). I've fixed to_date() along the above lines, but the general problem of how timestamp I/O should behave remains. I've come to the conclusion that there isn't any really consistent behavior if we want to stick with the current definition that timestamps outside the Unix date range are always UTC. If we do that, then there is a set of timestamps at one end of the date range that are ambiguous (they could be taken as either UTC or local), while at the other end of the range there is a set of timestamps that can't be validly converted as either one. This is essentially the same problem we have during daylight-savings transition hours: when you spring forward there is no local time 02:30, and when you fall back there are two of 'em. The solution we've adopted for DST transitions is to interpret invalid or ambiguous local times as always standard time. We could possibly do the same for the questionable times at the ends of the Unix date range, ie, always interpret them as UTC (although I've been fooling with the code for a couple hours now trying to get it to do that, without much success). Yeah, it seemed like the rules involved in doing that might be complicated to get right. Plan B would be to get rid of the discontinuity by abandoning the rule that timestamps outside the Unix range are UTC. We could instead say that the local time zone offset that mktime() reports for the first date of the Unix range applies to all prior dates, and similarly the offset for the last date of the range applies to all later dates. I'm unsure which of these is a better answer. Any thoughts? Generally, I think B is best since it keeps the values more continuous and doesn't require complicated trickery, although I'm not sure if that might change the observable behavior for people using timestamps outside the boundaries currently. I'm not one of them, so maybe we should continue on -general? ---(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
Re: [HACKERS] [BUGS] postgresql 7.3.2 bug on date '1901-12-13' and '1901-12
Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 21 Aug 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Wait, he's in australia, what if he's getting the edge case the other way. I'm inclined to fix to_date by decomposing the code differently --- it should avoid the coercion to timestamp, which is a waste of cycles anyway. But is to_timestamp (and more generally timestamp's input converter) broken? If so, how can we do better? I don't think we can entirely avoid the problem of a transition between local and GMT time. Yes. Timestamp with timezone is broken on the same boundaries in general. I'm not really sure how to do better without some work, it seems we end up with multiple different input values getting the same internal representation so we can differentiate which version of the input was used to get there (whether the user said 1901-12-13 23:00 or 1901-12-14). I've fixed to_date() along the above lines, but the general problem of how timestamp I/O should behave remains. I've come to the conclusion that there isn't any really consistent behavior if we want to stick with the current definition that timestamps outside the Unix date range are always UTC. If we do that, then there is a set of timestamps at one end of the date range that are ambiguous (they could be taken as either UTC or local), while at the other end of the range there is a set of timestamps that can't be validly converted as either one. This is essentially the same problem we have during daylight-savings transition hours: when you spring forward there is no local time 02:30, and when you fall back there are two of 'em. The solution we've adopted for DST transitions is to interpret invalid or ambiguous local times as always standard time. We could possibly do the same for the questionable times at the ends of the Unix date range, ie, always interpret them as UTC (although I've been fooling with the code for a couple hours now trying to get it to do that, without much success). Plan B would be to get rid of the discontinuity by abandoning the rule that timestamps outside the Unix range are UTC. We could instead say that the local time zone offset that mktime() reports for the first date of the Unix range applies to all prior dates, and similarly the offset for the last date of the range applies to all later dates. I'm unsure which of these is a better answer. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] row level locks
How do we acquire row level locks in postgresql and is there any feild in LOCK or PROCLOCK datastructes (src/include/storage/lock.h) that tells us the lock is row-level? thanks _ Enter for your chance to IM with Bon Jovi, Seal, Bow Wow, or Mary J Blige using MSN Messenger http://entertainment.msn.com/imastar ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Decent VACUUM (was: Buglist)
On Thu, 21 Aug 2003, Tom Lane wrote: We have had some people looking at improved buffer management algorithms; LRU-2 or something smarter would help. I dunno whether we can dissuade the kernel from flushing its cache though. Using open/read/write system calls, you can't. You can always use an OS like Solaris that should detect the sequential read, however, and avoid blowing out the buffer cache. Most operating systems support the madvise system call, which does let you do things like say, I'm accessing this sequentially and I don't need this to be buffered any more, though how much attention most operating systems pay to this advice varies with the OS. However, it turns out to be non-trivial to get postgres to use mmap for data blocks, since you can't actually write any data to a mmmap'd block until you've confirmed that the log entry has been written, because once you've touched data in an mmaped block you have no way of stopping it from being written to the disk right away. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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
Re: [HACKERS] Decent VACUUM (was: Buglist)
Manfred Koizar [EMAIL PROTECTED] writes: better. AFAICS Vivek's problem is that it is hard enough to hold a good part of the working set in the cache, and still his disks are saturated. Now a VACUUM not only adds one more process to disk I/O contention, but also makes sure that the working set pages are *not* in memory which leads to higher I/O rates after the VACUUM. We have had some people looking at improved buffer management algorithms; LRU-2 or something smarter would help. I dunno whether we can dissuade the kernel from flushing its cache though. If we teach VACUUM to not read pages that don't contain any dead tuples, this could be a significant improvement. I'm envisioning a data structure (reclaimable space map, RSM) similar to the FSM. Whenever a backend encounters a dead tuple it inserts a reference to its page into the RSM. This assumes that backends will visit dead tuples with significant probability. I doubt that assumption is tenable; it's certainly not if you assume that no backend is doing seqscans. (And if they are, then VACUUM is not the only I/O culprit...) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Decent VACUUM (was: Buglist)
Manfred Koizar [EMAIL PROTECTED] writes: Good point. What about: Whenever a backend *deletes* a tuple it inserts a reference to its page into the RSM? Then an entry in the RSM doesn't necessarily mean that the referenced page has reclaimable space, but it would still be valueable information. That might work if the RSM were lossless, but in practice I think it'd have to be lossy, like the FSM. Which would mean that you'd still have to do full-scan vacuums fairly regularly to make sure you hadn't forgotten any freeable tuples. Conceivably it could be a win, though, if you could do frequent vacuum decents and only a full-scan vacuum once in awhile (once a day maybe). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
