Re: [HACKERS] MERGE vs REPLACE
Martijn, Here is a quick test (Oracle 10.1.0.3/Linux): SQL> select banner from v$version; BANNER Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod PL/SQL Release 10.1.0.3.0 - Production CORE10.1.0.3.0 Production TNS for Linux: Version 10.1.0.3.0 - Production NLSRTL Version 10.1.0.3.0 - Production SQL> select * from merge_test_1; ID NAME -- 1 aaa 2 bbb 3 ccc 4 ddd 5 eee 1 xxx 6 rows selected. SQL> select * from merge_test_2; ID NAME -- 1 AAA 2 BBB 6 FFF SQL> select index_name from user_indexes where table_name like 'merge_test%'; no rows selected SQL> merge into merge_test_1 a1 2 using merge_test_2 a2 3 on (a1.id = a2.id) 4 when matched then 5 update set a1.name = a2.name 6 when not matched then 7 insert (id, name) values (a2.id, a2.name); 4 rows merged. SQL> select * from merge_test_1; ID NAME -- 1 AAA 2 BBB 3 ccc 4 ddd 5 eee 1 AAA 6 FFF 7 rows selected. Regards, Lubomir Petrov Martijn van Oosterhout wrote: On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote: Btw about that keys, oracle gives error on many-to-one or many-to-many relationship between the source and target tables. The standard has something called a "cardinality violation" if the to-be-merged table doesn't match 1-1 with the rest of the statement. If I had access to an Oracle I'd run two tests on MERGE: 1. Does the joining column have to have an index? For example, make a column that's full of unique values but no unique index. According to my reading of the the standard, this should still work (just slower). 2. Additionally, only the rows involved in the MERGE need to be uniquely referenced, so if you add duplicate values but add a WHERE clause to exclude those, it should also work. My feeling is that requiring an index will limit it's usefulness as a general tool. Have a nice day, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug 1500
Tom Lane wrote:
Lyubomir Petrov <[EMAIL PROTECTED]> writes:
I have found what is causing the crash described in Bug 1500. Now I
would like to fix it, but need opinions about what is the correct behaviour.
Yeah, I just came to the same conclusion a little while ago:
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00908.php
Also the general to_char() Interval formatting seems broken anyway.
Karel Zak has stated repeatedly that interval_to_char is fundamentally
wrong and should be removed. I'm not sure it's quite as bad as that,
but it does seem that a different set of formatting codes is needed for
intervals as opposed to timestamps. Textual 'MON' doesn't even make any
sense for intervals really, AFAICS. I could see displaying an interval
in terms of '4 months', but 'April' makes no sense.
Does Oracle have to_char for intervals, and if so how do they define it?
Anyway, even if we think it's broken enough to remove going forward,
we need some kind of stopgap fix to prevent the coredump in existing
releases.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
.
Tom,
Well, I can see how the to_char functionality can be very useful for
intervals - one can get the interval in days only, months and days, etc.
But I think that the format specifications that convert to strings
should be disallowed for intervals (Mon, Month, etc...).
If we decide just to ignore the non-supported format code we can
1) make dch_date aware that it is called for interval and limit the
choices (ignore the attempt to show textual name representation for
example)
2) just ignore the attempt to show month name on invalid value in struct
pg_tm.
In the second case we'll need to change only this file several times
using something like (this is good to be there anyway because of the
array indexing):
case DCH_Mon:
+if (tm->tm_mon > 0) {
+strcpy(inout, months[tm->tm_mon - 1]);
+return 2;
+}
+return -1;
+
-strcpy(inout, months[tm->tm_mon - 1]);
-return 2;
The first case will probably have more impact. I think we can go with 2)
for 8.0.2 and 1) for 8.1.
Oracle has to_char() on intervals, but generally does not allow fancy
formatting (limited format specifications only - FF, TZD, TZH, TZM, and
TZR - which are not very useful anyway).
Regards,
Lyubomir Petrov
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bug 1500
Steve Crawford wrote: So this bug actually brings the issue of interval to_char() formatting. Opinions? In digging around I discovered that it appears a decision was made to remove to_char(interval) at the 8.1 release but I've been unable to find the replacement for this functionality. This alarms me. Given the messages I've seen regarding to_char(interval), it's clearly a function that is used. As an example, in our telephony systems there is a column for start_time and for end_time. Billing involves a sum(end_time-start_time) for the appropriate project/client/period. Naturally, that interval needs to be displayed appropriately. The most common request I've seen (and it would be very helpful for me as well) is the ability to fill the largest displayed time increment with all remaining time in the interval. In other words when the total increment is 7 days, 7 hours, 28 minutes, 12 seconds the desired output would be 10528 minutes 12 seconds. Think phone-billing, race times, mission clocks, etc. So... 1) Is there really a plan to eliminate to_char(interval)? 2) If so, what is the replacement? 3) If there isn't a replacement and it's just scheduled for elimination, what harm was to_char(interval) causing to require its removal and what's the best way to lobby for its retention and improvement? Cheers, Steve . Steve, I am with you on this. The interval functionality is very useful and it will be bad if it gets eliminated. I believe that the best course of action is to keep the to_char(interval) but restrict the available format specifications (the textual representation specificators like Mon/Months). Regards, Lyubomir Petrov ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Bug 1500
Hi, I have found what is causing the crash described in Bug 1500. Now I would like to fix it, but need opinions about what is the correct behaviour. The bug can be easily duplicated when formatting interval in to_char() using 'Mon' or 'Month' in the format string. select to_char(now() - '20011001'::date, 'MonDD'); (server process crash follows) What happens: 1. The formatting function used is dch_date() (src/backend/utils/adt/formatting.c) and it works on struct pg_tm. 2. The interval2tm() (src/backend/utils/adt/timestamp.c) is used to convert the interval into pg_tm struct. 2a. If the Interval parameter has month != 0, then month and year are filled in pg_tm 2b. If not -> they are set to 0 and only days, hours, minutes, seconds are filled (this is the case when the bug appears). 3. dch_date() expects the struct pg_tm to have valid 1-based month index and directly references the months/months_full arrays using (tm->month - 1) as index to get the short/full name of the month. 4. SIGSEGV in the server process This could be easily by not allowing the bad array indexing, but it raises a bigger problem: How is supposed the to_char() function to format interval datatype? What is the correct output? Should we: 1) Try to fill the missing data (years, months) using the days (but how many days are in one month? hardcode 30/31? how many days in 1 year then...) and fix the formatting function to ignore string based formatting for intervals 2) Fail the entire statement (do not support interval formatting with to_char()) Also the general to_char() Interval formatting seems broken anyway. Note that the following (and similar) works now, but the result doesn't seem to be correct: test=> select to_char(now() - '20011001'::date, 'DD'); to_char - 000112 (1 row) test=> select now() - '20011001'::date; ?column? --- 1271 days 12:48:18.1216260046 (1 row) So this bug actually brings the issue of interval to_char() formatting. Opinions? Regards, Lyubomir Petrov ---(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
