Re: [HACKERS] [PATCHES] COPY view
On Tue, Aug 22, 2006 at 01:11:22PM -0400, Andrew Dunstan wrote: There's nothing hidden (unless it's also hidden from me ;-) ) I take it that when you talk about we did this you are referring to the patch from Karel Zak. Hans has been original author of COPY VIEW idea and I've wrote it for his customer (yes, it was sponsored work). Karel -- Karel Zak [EMAIL PROTECTED] ---(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
Leaving... (was: Re: [HACKERS] [PATCHES] COPY view)
Hi all, seriously... I don't have time to work on PostgreSQL. It's time to say that I'm leaving this project. So, if you found some my broken code or whatever in PostgreSQL you should go and fix it. It's community-driven project. It's about collaboration -- don't ask why should I help -- go and help! It was nice time and really big experience, but in the world is more projects and many of them need more help than already stable (do you remember PostgreSQL 6.5? :-) and very reliable PostgreSQL. Good bye! Karel On Tue, Aug 22, 2006 at 11:12:21PM -0400, Tom Lane wrote: The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] to_char and i18n
On Sun, 2005-12-25 at 17:56 -0300, Euler Taveira de Oliveira wrote:
--- Euler Taveira de Oliveira [EMAIL PROTECTED] escreveu:
I have a patch like this. But this was for 7.4.x. I have to take a
look
at it.
The patch is attached. It implements day and month i18n. I fixed a few
misspelling comments. Docs is attached too.
template1=# select to_char(now(), 'Day, DD Month ');
to_char
--
Sunday , 25 December 2005
(1 registro)
template1=# select to_char(now(), 'TMDay, DD TMMonth ');
to_char
---
Domingo, 25 Dezembro 2005
(1 registro)
template1=#
Comments?
I think it looks like a good patch. There's small problem that the
current to_char() output is possible use as argument for to_timestamp()
or to_date() function. It means you should implement vice-versa
conversion from string with TMMonth/TMDay to timestamp.
to_timestamp('Domingo, 25 Dezembro 2005', 'TMDay, DD TMMonth ')
Or.. at least describe in the docs that this way is unsupported
for 'TM' prefix.
Karel
--
Karel Zak [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char and i18n
On Wed, 2005-12-21 at 23:50 -0500, Tom Lane wrote: Manuel Sugawara masm@fciencias.unam.mx writes: Tom Lane [EMAIL PROTECTED] writes: I thought to_char already had i18n behavior. What exactly are you thinking of changing? The modifiers that are suitable to localize. Month and day names comes to mind and maybe others, I'm not sure what the state of the code is, but I can say that, at least, the 'month' and 'day' modifiers does not behave in a localized way. The names for months and days are hardcoded to to_char code and it's in English only. Can we spell the names differently but keep to the same field widths? That's important point. How resolve this problem Oracle? Maybe we can say (in docs) that with non-English locales it works with days/months names as in FM (fill) mode. # select length( to_char(now(), 'Day') ) as Normal, length( to_char(now(), 'FMDay') ) as FM; normal | fm + 9 | 8 It means 'FM' uses variable size of Day/Month field -- without FM is the size fixed to 9 chars. I think that for backward compatibility the locale sensitive to_char() should be implemented as separate call to_char(datetime, format, locale) or we should add new modifiers to the current to_char, something like to_char(datetime, LCMonth) or both. I don't have any time to work on to_char(), I can help to review patches only. Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: R: [HACKERS] feature proposal ...
On Wed, 2005-09-21 at 11:31 -0400, Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Paolo Magnoli wrote: Can't you just use a view? no because a new is not a heap ... I think Paolo's idea is much better than munging the syntax of COPY, though. Fixing COPY so that you *could* copy from a view would provide all the desired functionality without any syntactic warts. Well, I will probably help Juergen with the implementation. It seems that fetch data from VIEW is possible by portal stuff. Tom, do you think that there's any other (better) way how we can implement it? Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] 'now' runtime
Why is there so different Total runtime for ('now'::text)::date) and
'2005-04-06'::date ?
I think both is constant during execution.
CREATE TABLE test (
_time timestamp with time zone,
_platform character(5),
_tld character(5)
) WITHOUT OIDS;
explain analyze SELECT count(*) from test where
_time::date=CURRENT_DATE;
QUERY PLAN
Aggregate (cost=0.01..0.01 rows=1 width=0) (actual time=0.026..0.029
rows=1 loops=1)
- Seq Scan on test (cost=0.00..0.00 rows=1 width=0) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((_time)::date = ('now'::text)::date)
Total runtime: 24.034 ms
explain analyze SELECT count(*) from test where
_time::date='2005-04-06'::date;
QUERY PLAN
Aggregate (cost=0.01..0.01 rows=1 width=0) (actual time=0.015..0.018
rows=1 loops=1)
- Seq Scan on test (cost=0.00..0.00 rows=1 width=0) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((_time)::date = '2005-04-06'::date)
Total runtime: 0.065 ms
Karel
--
Karel Zak [EMAIL PROTECTED]
---(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] Bug 1500
On Sat, 2005-03-26 at 15:56 -0800, Josh Berkus wrote: Alvaro, On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote: SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min'; 2600 min Hmm, what if you wanted more than one literal string? Say 1 mon 3 days ... your concatenation idea wouldn't work. ISTM the format string should allow unconverted literals, so you would use SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' ); Hmmm, good point. Question: how does to_char tell the difference between a code (MI) and a code which is also part of a word? (MIN). It's pretty simple. to_char(..., 'MI min'). It's already supported by to_char() format parser. I think to_char(interval) should be support split interval to more items, like: to_char(INTERVAL '1d 3h 65s', 'HHh MIm SSs') --- '27h 1m 5s' Well, I'm going to check how difficult will be implement correct to_char (interval). Karel -- Karel Zak [EMAIL PROTECTED] ---(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] Bug 1500
On Sun, 2005-03-27 at 12:03 +0200, Karel Zak wrote: On Sat, 2005-03-26 at 15:56 -0800, Josh Berkus wrote: Alvaro, On Sat, Mar 26, 2005 at 02:04:14PM -0800, Josh Berkus wrote: SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI' ) || ' min'; 2600 min Hmm, what if you wanted more than one literal string? Say 1 mon 3 days ... your concatenation idea wouldn't work. ISTM the format string should allow unconverted literals, so you would use SELECT to_char( INTERVAL '43 hours 20 minutes', 'MI min' ); Well, I'm going to check how difficult will be implement correct to_char (interval). Hmm, if we want to support conversion like: '43 hours 20 minutes' -- 'MI min' how we should work with calendar INTERVAL units? For example 'month'? '1 month 1 day' -- 'D days' I think answer should be error message: missing calendar unit 'month' in output format Karel -- Karel Zak [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
Re: [HACKERS] Upcoming 8.0.2 Release
http://archives.postgresql.org/pgsql-patches/2005-03/msg00176.php Add it to 8.0.2 or 8.1? Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming 8.0.2 Release
On Fri, 2005-03-25 at 03:29 -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: http://archives.postgresql.org/pgsql-patches/2005-03/msg00176.php I intend to look at that tomorrow. Meanwhile, have you got a fix for bug#1500? http://archives.postgresql.org/pgsql-bugs/2005-02/msg00226.php Sorry. Not yet. I haven't time today. Maybe next week :-( Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming 8.0.2 Release
On Fri, 2005-03-25 at 14:08 -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: On Fri, 2005-03-25 at 03:29 -0500, Tom Lane wrote: I intend to look at that tomorrow. Meanwhile, have you got a fix for bug#1500? http://archives.postgresql.org/pgsql-bugs/2005-02/msg00226.php Sorry. Not yet. I haven't time today. Maybe next week :-( I looked at this and found the problem is that dch_date() isn't defending itself against the possibility that tm-tm_mon is zero, as it well might be for an interval. What do you think about just adding case DCH_MONTH: + if (!tm-tm_mon) + return 0; and similarly in each of the other case arms that use tm_mon? Yes, I think you're right. It's because original code was for non- interval 'tm' struct where is no problem with zeros. This would case MON to convert to a null string for intervals, which is probably as good as we can do. Yes. The final solution will be remove all to_char(interval) stuff in 8.1. Thanks Tom, Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bug 1500
On Fri, 2005-03-25 at 15:33 -0500, 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. Exactly. We had many discussions about it. Well, short summary: the current to_char(interval) is: interval - struct tm - string and it's definitely bad. You can't formatting interval as date/time string and you can't use calendar practices in particular case. The right solution is conversion: interval - interval-string and it means definitely other (new) code for to_char(interval). I think useful for to_char(interval) is only format parser from formatting.c, it's 5% of all to_char() code :-( I don't think we want to maintain useless code in PG and answer every month in PG lists questions why doesn't work it?. It's better remove it and wait for someone who write better implementation. BTW, I have started work on formatting library: http://people.redhat.com/kzak/libfmt/ contributors, volunteers? :-) Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bug 1500
On Fri, 2005-03-25 at 20:03 -0500, Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] writes: 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. Yeah. Karel Zak, who wrote that code, is convinced we should remove it, but I don't think anyone else is ... I think I was Peter and Josh Berkus who convinced me that the code is bed. we should remove... is opinion only... http://groups- beta.google.com/group/comp.databases.postgresql.hackers/browse_frm/thread/a43f02de8017cabb/c290bc55d5e1e6b2?q=to_char(interval)+donernum=1#c290bc55d5e1e6b2 -- Karel Zak [EMAIL PROTECTED] ---(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] Bug 1500
On Sat, 2005-03-26 at 02:32 +0100, Karel Zak wrote: On Fri, 2005-03-25 at 20:03 -0500, Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] writes: 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. Yeah. Karel Zak, who wrote that code, is convinced we should remove it, but I don't think anyone else is ... I think I was Peter and Josh Berkus who convinced me that the code is bed. we should remove... is opinion only... s/bed/bad/ :-) .. but my body dreams about bed, good night (morning?), Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] change palloc to malloc
On Tue, 2005-03-15 at 18:29 -0500, Bruce Momjian wrote: Nguyen Hai wrote: Hi, Is it possible to convert palloc back to malloc? If so, what should I do to make the change? You can, but palloc is automatically freed at the end of a query, while malloc has to be freed in the code. Why do you want to make the switch? Right. And it's definitely faster use palloc() than malloc(). I think the current code is really based on memory contexts and I'm sure that (strange:-) s/palloc/malloc/ is not enough. Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] invalidating cached plans
On Thu, 2005-03-17 at 16:11 +1100, Neil Conway wrote: Neil Conway wrote: Do we want to share plans between call sites? After thinking about this a little more, I think the answer is no -- it doesn't really buy us much, and introduces some extra complications (e.g. resource management). It was already implemented as experiment and I think better is keep plans separate. karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UTF8 or Unicode
On Thu, 2005-02-24 at 23:51 -0500, Bruce Momjian wrote: Tatsuo Ishii wrote: I do not object the changing UNICODE-UTF-8, but all these discussions sound a little bit funny to me. If you want to blame UNICODE, you should blame LATIN1 etc. as well. LATIN1(ISO-8859-1) is actually a character set name, not an encoding name. ISO-8859-1 can be encoded in 8-bit single byte stream. But it can be encoded in 7-bit too. So when we refer to LATIN1(ISO-8859-1), it's not clear if it's encoded in 7/8-bit. Wow, Tatsuo has a point here. Looking at encnames.c, I see: UNICODE, PG_UTF8 but also: WIN, PG_WIN1251 LATIN1, PG_LATIN1 so I see what he is saying. We are not consistent in favoring the official names vs. the common names. Yes. I said already. For example WIN is extremely bad alias. It all is heritage from old versions. I will work on a patch that people can review and test. Thanks. Karel -- Karel Zak [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
Re: [HACKERS] UTF8 or Unicode
On Tue, 2005-02-15 at 14:33 +0100, Peter Eisentraut wrote: Am Dienstag, 15. Februar 2005 10:22 schrieb Karel Zak: in PG: unicode = utf8 = utf-8 Our internal routines in src/backend/utils/mb/encnames.c accept all synonyms. The official internal PG name for UTF-8 is UNICODE :-( I think in the SQL standard the official name is UTF8. If someone wants to verify that this is the case and is exactly the encoding we offer (perhaps modulo the 0x1 issue), then it might make sense to change the canonical form to UTF8. Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. Karel -- Karel Zak [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
Re: [HACKERS] UTF8 or Unicode
On Sat, 2005-02-19 at 00:27 +1300, Oliver Jowett wrote: Karel Zak wrote: Yes, I think we should fix it and remove UNICODE and WIN encoding names from PG code. The JDBC driver asks for a UNICODE client encoding before it knows the server version it is talking to. How do you avoid breaking this? Fix JDBC driver as soon as possible. Add to 8.1 release notes: encoding names 'UNICODE' and 'WIN' are deprecated and it will removed in next release. Please, use correct names UTF-8 and WIN1215. 8.2: remove it. OK? Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] UTF8 or Unicode
On Mon, 2005-02-14 at 22:05 -0500, Bruce Momjian wrote: Abhijit Menon-Sen wrote: At 2005-02-14 21:14:54 -0500, pgman@candle.pha.pa.us wrote: Should our multi-byte encoding be referred to as UTF8 or Unicode? The *encoding* should certainly be referred to as UTF-8. Unicode is a character set, not an encoding; Unicode characters may be encoded with UTF-8, among other things. (One might think of a charset as being a set of integers representing characters, and an encoding as specifying how those integers may be converted to bytes.) I know UTF8 is a type of unicode but do we need to rename anything from Unicode to UTF8? I don't know. I'll go through the documentation to see if I can find anything that needs changing. I looked at encoding.sgml and that mentions Unicode, and then UTF8 as an acronym. I am wondering if we need to make UTF8 first and Unicode second. Does initdb accept UTF8 as an encoding? in PG: unicode = utf8 = utf-8 Our internal routines in src/backend/utils/mb/encnames.c accept all synonyms. The official internal PG name for UTF-8 is UNICODE :-( It's historical reason that UTF8 = UNICODE, because there was UNICODE first. It's same like WIN for WIN1251 (in sources it's marked as _dirty_ alias)... I think initdb uses pg_char_to_encoding() from src/backend/utils/mb/encnames.c and it should be accept all aliases. Karel -- Karel Zak [EMAIL PROTECTED] ---(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] Inline MemoryContextSwitchTo?
On Sun, 2005-02-06 at 18:05 -0500, Tom Lane wrote: Can anyone think of a reason we aren't inlining MemoryContextSwitchTo() in GCC builds, similarly to the way list_head() et al are handled? It wouldn't be a huge gain, but I consistently see MemoryContextSwitchTo eating a percent or three of most profiles. Sounds good. I think we can inlining all MemoryContext functions which check memory context header and call context-metods-...() only. An example MemoryContextAlloc() that is very often called from code too. Karel -- Karel Zak [EMAIL PROTECTED] ---(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] to_char/to_number loses sign
On Sat, 2004-10-23 at 17:25 -0400, Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
SELECT to_number('485-', '999S');
to_number
---
485
Is this a bug or intentional?
Tracing through this, it looks like the problem is that NUM_processor()
has no switch case for NUM_S (nor does the default case raise an error,
which seems a risky practice to me).
Karel, can you verify this and submit a fix?
Yes, you're right. It strange, but NUM_S missing there. The conversion
from string to number is less stable part of formatting.c...
I have already 2000 lines of code of new generation of to_..()
functions. But all will available in 8.1.
The patch is in the attachment.
Karel
--
Karel Zak
http://home.zf.jcu.cz/~zakkr
--- pgsql/src/backend/utils/adt/formatting.c.num_s 2004-10-25 13:51:58.009789928 +0200
+++ pgsql/src/backend/utils/adt/formatting.c 2004-10-25 15:23:09.315025104 +0200
@@ -3625,7 +3625,7 @@
{
#ifdef DEBUG_TO_FROM_CHAR
- elog(DEBUG_elog_output, --- scan start --- );
+ elog(DEBUG_elog_output, --- scan start --- %s, Np-number);
#endif
if (*Np-inout_p == ' ')
@@ -3642,7 +3642,7 @@
/*
* read sign
*/
- if (*Np-number == ' ' (id == NUM_0 || id == NUM_9 || NUM_S))
+ if (*Np-number == ' ' (id == NUM_0 || id == NUM_9 || id == NUM_S))
{
#ifdef DEBUG_TO_FROM_CHAR
@@ -4138,6 +4138,7 @@
case NUM_0:
case NUM_DEC:
case NUM_D:
+case NUM_S:
if (Np-type == TO_CHAR)
{
NUM_numpart_to_char(Np, n-key-id);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tweaking MemSet() performance - 7.4.5
On Sat, 2004-09-25 at 23:23 +0200, Manfred Spraul wrote: [EMAIL PROTECTED] wrote: If the memset bypasses the cache then the following access will cause a cache line miss, which can be so slow that using the faster memset can result in a net performance loss. Could you suggest some structs to test? If I get your meaning, I would make a loop that sets then reads from the structure. Read the sources and the cpu specs. Benchmarking such problems is virtually impossible. I don't have OS-X, thus I checked the Linux-kernel sources: It seems that the power architecture doesn't have the same problem as x86. There is a special clear cacheline instruction for large memsets and the rest is done through carefully optimized store byte/halfword/word/double word sequences. Thus I'd check what happens if you memset not perfectly aligned buffers. That's another point where over-optimized functions sometimes break down. If there is no slowdown, then I'd replace the postgres function with the OS provided function. I'd add some __builtin_constant_p() optimizations, but I guess Tom won't like gcc hacks ;-) I think it cannot be problem if you write it to some .h file (in port directory?) as macro with #ifdef GCC. The other thing is real advantage of hacks like this in practical PG usage :-) Karel -- Karel Zak http://home.zf.jcu.cz/~zakkr ---(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] 8.0 Open Items
On Fri, Aug 20, 2004 at 11:44:28PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Darcy Buskermolen wrote: * remove to_char(interval) if we initdb or mention removal I vote just to mention it's removal at this time, Agreed. Done. While I don't care that much one way or the other --- what is the difference between this and the prior state? Karel already said in the 7.4 docs that to_char(interval) would be removed in the next release. Why would the people who ignored the warning last time believe it this time round? I think that 8.0 is a more appropriate release number in which to be taking backwards-compatibility hits than 8.1. So if we're gonna do it at all, I would vote for doing it now. I agree with Tom. The function to_char(interval) is useless, bad, unsupported and without future (if you want to know why you can found answer in lists archive). I think 8.0 is really better time for some cleanups and back compatibility changes than some other release. BTW, I'm going to start fulltime job for RH next week. Note RH constitute new team of developers in Czech Republic. Maybe I will have again more time for PostgreSQL (or maybe not if they assign me to some other project -- but PostgreSQL is my wish :-) So to_char() familly will again better maintained. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] monetary bug
On Sun, Aug 22, 2004 at 04:07:17PM -0400, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: We keep hoping someone will step up to the plate and rewrite it, instead. Per previous discussion, the type really ought to be a thin layer over numeric, with most likely no operations of its own except I/O conversion. And what would it do with it? Add a currency symbol? Which one? Which form? Where? Format the numbers some way? I'm sure you found answers in struct lconv (locale.h), but you're right (IMHO) better is if datetypes are independent on format. What support some argument definition in column definition, something like: CREATE TABLE tab ( -- curret built-in datetype in/out format numnumeric, -- default user defined format num2 numeric WITH FORMAT, -- column specific format payme numeric WITH FORMAT='L 999D99', temperaturenumeric WITH FORMAT='999D999 C', ); where format producer for datetype is possible (re)define by: CREATE FORMATTER FOR numeric DEFAULT format INPUT funcname OUTPUT funcname; The columns without FORMAT options will use standard (current) in/out methods. Outputs without connection to some table column can be formated by: -- curret built-in datetype in/out format SELECT expr; -- default defined format SELECT FORMAT( expr ); -- specific format SELECT FORMAT( expr AS 'DD/MM/' ); I think it's pretty extendable solution in contrast to the current hardcoded in/out datetypes functions. Comments? Is there any plan for locale-per-column setting syntax? The idea behind the money type is to format per the lc_monetary locale setting, which seems perfectly reasonable to me. Further down the road Why money and why not others things like temperature, speed, weight, ...? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] monetary bug
On Mon, Aug 23, 2004 at 02:52:44PM +0200, Dennis Bjorklund wrote: On Mon, 23 Aug 2004, Karel Zak wrote: I think it's pretty extendable solution in contrast to the current hardcoded in/out datetypes functions. Who are we formatting for? If the client wants the data in a specific format then they can do SELECT to_char(...), or do the formatting in the client all together. Yes. But some people call for datetypes with integrated formatting (for example money). I think we should support _common_ way how do formatting (not only for money) _OR_ we should reject types like money and do formatting only by extra functions like to_char(). The database should manage data, presenting it to the user in different ways are the job of a client. Sure, do you want to read data in binary format that PostgreSQL uses in db files or do you expect it in some nice string? PostgreSQL already does data formating almost for all datetypes -- see sources and datetypes in/out functions. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] 8.0 libpq: missing get_home_path()
Hi, I tried compile some program with latest CVS libpq: gcc -O2 -O2 -g -pipe -march=i386 -mcpu=i686 -I/usr/include/httpd -Wall -I/usr/lib/postgresql/include -DLIBAPMOM_FUNCALL=1 -Wall -Wmissing-prototypes -Wmissing-declarations `xml2-config --cflags` `Wand-config --cppflags` -o jsn-importer main.o file.o db.o md5.o -L/usr/lib/postgresql/lib -lpq `xml2-config --libs` -lMagick -lWand /usr/lib/postgresql/lib/libpq.so: undefined reference to `get_home_path' collect2: ld returned 1 exit status make: *** [jsn-importer] Error 1 It looks like port/path.c (libpgport) isn't compiled in PostgreSQL client libs, but the get_home_path() is used there. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char() and negative intervals
On Fri, Aug 13, 2004 at 12:24:28PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Someone on IRC just reported this to_char() failure with negative intervals: I think Karel wanted to remove to_char(interval) altogether. Yes, the notice about it is already in 7.4 docs. Maybe we can remove it in 7.5/8.0. Comments? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Assisting developers
On Tue, Jul 13, 2004 at 06:29:51PM +0200, Peter Eisentraut wrote: Bruce Momjian wrote: I am not sure what can be done to solve this in the future. There are only a limited number of us who have the experience and time to review and comment on very complex patches. The issue as I see it is not reviewing patches, but defining features. You're right. The other problem is that about some features nobody wants to talk over, because a feature is out of main stream interest or almost nobody really understand a problem. In this case all start discussion if something is already done and they try use it. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] windows encodings
Hi, in Czech database conference we talk about windows PostgreSQL port and windows encodings now. The current code expect use win1250 as FE encoding only. That's right for non-windows systems, but native windows knows work with this encoding. The encodings separation (FE|BE) is defined in the include/mb/pg_wchar.h file and I think we can change it a little by #ifdef WIN32 and define windows encodings as backend encodings for windows port. Pavel Stehule [EMAIL PROTECTED] already test win1250 as backend encoding for windows port and it works. There is more windows encodings like Big5 and Shift-JIS which are defined as FE only. I haven't windows so I can't help with this work, but I think people who work on windows port should test and fix it, because support windows encodigs in native windows port is well-founded idea. I think :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Slony-I goes BETA
On Fri, Jun 04, 2004 at 01:01:19AM -0400, Jan Wieck wrote: Yes, Slonik's, it't true. After nearly a year the Slony-I project is entering the BETA phase for the 1.0 release. Please visit http://gborg.postgresql.org/project/slony1/news/newsfull.php?news_id=174 Jan, the link http://postgresql.org/~wieck/slony1/Slony-I-concept.pdf that is used on project pages doesn't work :-( Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Weird prepared stmt behavior
On Sun, May 02, 2004 at 10:08:50AM +1200, Oliver Jowett wrote: If PREPARE can roll back, the driver must maintain a set of all statements that were sucessfully PREPAREd in the current transaction, and fix up the corresponding query object state whenever a transaction rolls back. From that point of view, it's much simpler to keep PREPARE (or at least Parse) as it currently is. I suspect the same argument applies to any interface layer that uses PREPARE or Parse automatically. Exactly. Tom, will work these two scenarios: 1/ I have web application that uses persistent connetions to PostgreSQL backend. After the connection opening the application prepares all queries and the rest of the application code uses EXECUTE statement only. It means the EXECUTE statemens are used in next arbitrary transactions. 2/ The other way which my application uses is prepare query first time when some code needs it -- and it's independend on actual transaction of course. I use this way now, beacuse it's more effective for me than prepare all queries after the connection startup. If I good understand your idea the case 1/ will work, but case 2/ not. I have no care about BEGIN; CREATE TABLE xxx (id serial); PREPARE q AS SELECT * FROM xxx; ABORT; EXECUTE q; ERROR: relation with OID 38242 does not exist because I can detect it by error message and it's too academic problem for me. I don't change DB schema in stable and production server and I think ALTER/DROP/CREATE is nothing often in running and good designed databases. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] What can we learn from MySQL?
On Mon, Apr 26, 2004 at 04:41:35PM -0400, Bruce Momjian wrote: Jean-Michel POURE wrote: [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. Dear Bruce, Taking the example of pgAdmin III, which reached nearly one million hits in December (http://www.pgadmin.org/stats/webalizer), nothing seems impossible for PostgreSQL. Why not create an all-in-one bundle offering PostgreSQL, Apache, Php and PhpPgAdmin for Win32 and ... mass-release it. There is no need to create a complete installer. There could be a single installer executing other installers (like it is sometimes the case in the Win32 world). So that installers remain different. A single web page like http://win.postgresql.org; in 40 languages is enough to mass-release PostgreSQL. With an installer and a single web page, PostgreSQL Win32 could quickly reach one million downloads every month. There is no need to look for complicated strategies. Every month, there can be 10% more downloads. In the end, people will even forget the name of MySQL. That seems like a good idea. Agree. The page should be describe basic PostgreSQL features and step-by-step introduction from download to a first user's SELECT ... FROM. Do you expect translate PostgreSQL-win installer to foreign languages? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] What can we learn from MySQL?
On Fri, Apr 23, 2004 at 01:05:21PM +0700, David Garamond wrote: So in my opinion, as long as the general awareness about RDBMS (on what tasks/responsibilities it should do, what features it generally has to have, etc) is low, people will be looking at MySQL as good enough and will not be motivated to look around for something better. As a comparison, I'm always amazed by people who use Windows 95/98/Me. They find it normal/good enough that the system crashes every now and then, has to be rebooted every few hours (or every time they install something). They don't know of anything better. Agree. People don't know that an RDBMS can be more better. A lot of users think speed is the most important thing. And they check the performance of SQL server by time mysql -e SELECT... but they don't know something about concurrency or locking. BTW, is the current MySQL target (replication, transactions, ..etc) what typical MySQL users expect? I think they will lost users who love classic, fast and simple MySQL. The trade with advanced SQL servers is pretty full. I don't understand why MySQL developers want to leave their current possition and want to fight with PostgreSQL, Oracle, DB2 .. etc. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] contrib vs. gborg/pgfoundry for replication solutions
On Thu, Apr 22, 2004 at 12:41:28AM +0400, Oleg Bartunov wrote: The problem with moving all contribs to gborg is that sometimes it's required to change many modules, for example, because of changing GiST interface. Tom saves a lot of working for contrib authors, when he change code in core. I'm not sure, gborg would provide easy access for such kind of things. tsearch2, particularly, is maintained in pgsql CVS. Agree. The basic argue for removing something from contrib should be that nobody maintain a module or that maintain it in the contrib is difficult. Other problem -- now maintainers of distribution PostgreSQL packages (Debian/RH/...) make packages from the contrib tree. Are you sure they will search something on sourceforge/gborg and make separate packeges for each small script? How they will detect what is good for packaging? The contrib tree is basic selection of interesting small thigs from PostgreSQL world. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_encoding not needed anymore
On Mon, Apr 19, 2004 at 08:41:18PM -0400, Bruce Momjian wrote: With initdb written now in C, we don't need a pg_encoding binary anymore. By the way, what change the name of initdb to pg_initdb. The current name is really too common (like some others things in pgsql/src/bin) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_encoding not needed anymore
On Tue, Apr 20, 2004 at 08:59:20AM -0400, Bruce Momjian wrote: Karel Zak wrote: On Mon, Apr 19, 2004 at 08:41:18PM -0400, Bruce Momjian wrote: With initdb written now in C, we don't need a pg_encoding binary anymore. By the way, what change the name of initdb to pg_initdb. The current name is really too common (like some others things in pgsql/src/bin) Uh, that would be pretty major. No one has complained about it in the past. I think createuser is much worse. :-) Sure. Maybe is needful wait for some other project like PostgreSQL that will use same clever names... But maybe we will never see a problem, because the others are less ignorant... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] union vs. sort
On Wed, Apr 07, 2004 at 02:20:55PM -0400, Tom Lane wrote: I've committed changes to do the right thing in CVS tip. Thanks man! Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] union vs. sort
On Tue, Apr 06, 2004 at 10:33:25AM -0400, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: I'm surprise with query plan that PostgreSQL planner prepare for selects with ORDER BY if all data are from sub-select that is already sorted. This isn't simply a matter of omitting the sort. Even if the inputs are sorted, their concatenation (Append result) isn't sorted: 1 2 3 4 and 1 3 7 9 are sorted, but 1 2 3 4 1 3 7 9 isn't. I didn't talk about Append result, but about Unique result. The ORDER BY in UNION query works with final concanated data -- that's right. My question is why a result from this ORDER BY is again sorted: # explain select data from (select data from addr union select data from addr2 order by data) as x order by x.data; --- (1) Sort Sort Key: data - Subquery Scan x (2) - Sort Sort Key: data - Unique (3) - Sort Sort Key: data - Append - Subquery Scan *SELECT* 1 - Seq Scan on addr - Subquery Scan *SELECT* 2 - Seq Scan on addr2 I see three sorts with same data. To do what you're thinking about, we'd have to build a variant implementation of Unique that merges two presorted inputs --- and it wouldn't work for more than two inputs (at least not without a lot of pain ... Append is a messy special case in many ways, and we'd have to duplicate most of that cruft to make an N-input version of Unique). I think it is not needful touch Append, but it should detect redundant sorts. Why select data from (select data from addr order by data) as x order by x.data use only one sort? This is possible, without doubt, but I'm not excited about expending that much time on it. You haven't shown any evidence that this would be an important optimization in practice. It's nothing important for me. It's from Czech databases mailing list where some PostgreSQL users was surprised with EXPLAIN result of UNION and speed of these queries. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] union vs. sort
I'm surprise with query plan that PostgreSQL planner prepare for selects with ORDER BY if all data are from sub-select that is already sorted. # explain select data from (select distinct data from addr) as x order by x.data; - Subquery Scan x - Unique - Sort Sort Key: data - Seq Scan on addr This is right -- the main of query doesn't use Sort for ORDER BY, because subselect is sorted by Unique. And almost same query, but in the subselect is union: # explain select data from (select data from addr union select data from addr2) as x order by x.data; - Sort Sort Key: data - Subquery Scan x - Unique - Sort Sort Key: data - Append - Subquery Scan *SELECT* 1 - Seq Scan on addr - Subquery Scan *SELECT* 2 - Seq Scan on addr2 I think it's bad, because there is used extra sort for ORDER BY for already by Unique sorted data. If I add ORDER BY to subselect: # explain select data from (select data from addr union select data from addr2 order by data) as x order by x.data; --- Sort Sort Key: data - Subquery Scan x - Sort Sort Key: data - Unique - Sort Sort Key: data - Append - Subquery Scan *SELECT* 1 - Seq Scan on addr - Subquery Scan *SELECT* 2 - Seq Scan on addr2 I see two unnecessary sorts for unique and already sorted data. The core of problem is probbaly UNION, because if I use simple query without subselect it still sort already sorderd data: # explain select data from addr union select data from addr2 order by data; --- Sort Sort Key: data - Unique - Sort Sort Key: data - Append - Subquery Scan *SELECT* 1 - Seq Scan on addr - Subquery Scan *SELECT* 2 - Seq Scan on addr2 Or order of data which returns unique is for UNION diffrent that data from DISTINCT? (see first example). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] Per database users/admins, handy for database virtual hosting...
On Thu, Mar 25, 2004 at 08:24:59PM -0800, Sean Chittenden wrote: You can't think that allowing the same name to appear globally and locally is a good idea. Actually, I do think it is a good idea. If I say GRANT TO foo, who am I granting privileges to? SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than CLUSTER GRANT TO foo; SET username_precedence TO GLOBAL,LOCAL; GRANT TO foo; Yes, it possible, but I not sure if this commands dependence is something wanted and nice. You can use GRANT TO LOCAL foo rather than connect more commands together. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] linked list rewrite
On Tue, Mar 23, 2004 at 01:16:51PM -0500, Bruce Momjian wrote: I agree a renaming of list functions is good. If we had kept the original Berkeley code as-is, we would have a lot fewer developers today. :-) Making drastic cleanups is often worthwile. I write backend code and still can't remember which list function does what, so clearer naming would help me a lot, and I am sure others too. Maybe I already ask, is there any coding style recommendation for _new_ written things? I agree with Bruce. The list functions and a lot of other functions can be candidates for rename. I read Mono docs last night and it's perfect if they can write to docs about functions names: mono_type_action. I think sometime are people too much focus on the shortest way to target and forgot that with code work others people and not CPU only. (sorry of this pontification:-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_autovacuum next steps
On Mon, Mar 22, 2004 at 02:35:37AM -0500, Matthew T. O'Connor wrote: On Sun, 2004-03-21 at 23:00, Bruce Momjian wrote: C) Most importantly, I'm not backend hacker. If someone wants to do the initial work of getting it running as a backend process, I can take it from there. A while ago, Bruce offered to help me with any backend issues I might have, so perhaps with a little help I can take a run at it. I'd be happy to help you out. Agreed. Ok, thanks for the offer to help, but I think I understated things above when I said I'll need a little help :-) I have a few big picture questions. Once pg_autovacuum is launched as a postmaster sub-process, what changes? All. It's important do it as backend process. Because libpq has very, very limited and slow resources for work with backend stuff. The base should be the standard backend with different main loop that will instead socket checks some shared information about tables and calls directly vacuum stuff. In this case you can omit work with connections, parser etc. I thought about it in last days and I found perfect Tom's idea about FSM tables usage: What I had in the back of my mind was: each backend counts attempted insertions and deletions in its relcache entries (an update adds to both counts). At transaction commit or abort, we know which of these two counts represents the number of dead tuples added to each relation, so while we scan the relcache for post-xact cleanup (which we will be doing anyway) we can transfer the correct count into the shared FSM entry for the relation. This gives us a reasonably accurate count in shared memory of all the tuple obsoletions since bootup, at least for heavily-used tables. (The FSM might choose to forget about lightly-used tables.) The auto vacuumer could look at the FSM numbers to decide which tables are highest priority to vacuum. (2002-09-03 08:10:32) I looked at the code and I think extend FSM tables will pretty simple, but I unsure how relcache counters Tom thought. Tom? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] COPY formatting
On Fri, Mar 19, 2004 at 09:54:37AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: The problem with CSV is that it will correctly work with new protocol only. Because old versions of clients are newline sensitive. Why? The client-side code doesn't have any real say over the meaning of the data, at least not in psql-class clients. I suppose a client app that tries to interpret the data could get confused, but psql sure doesn't do that. libpq, fe-protocol2.c, pqGetCopyData2() and pqGetline2() cut string behind '\n'. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] COPY formatting
On Thu, Mar 18, 2004 at 10:16:36AM -0500, Tom Lane wrote: Passing in a relation OID is probably a bad idea anyway, as it ties this API to the assumption that COPY is only for complete relations. There's been talk before of allowing a SELECT result to be presented via the COPY protocol, for instance. What might be a more usable API is COPY OUT: function formatter_out(text[]) returns text COPY IN: function formatter_in(text) returns text[] where the text array is either the results of or the input to the per-column datatype I/O routines. This makes it explicit that the formatter's job is solely to determine the column-level wrapping and unwrapping of the data. I'm assuming here that there is no good reason for the formatter to care about the specific datatypes involved; can you give a counterexample? The idea was put maximum information about tuple to formatter, and what will formatter do with this information is a formatter problem. It's pity that main idea of current COPY is based on separated lines and it is not more common interface for streaming data between FE and BE. Yeah, that was another concern I had. This API would let the formatter control line-level layout but it would not eliminate the hard-wired significance of newline. What's worse, there isn't any clean way to deal with reading quoted newlines --- the formatter can't really replace the default quoting rules if the low-level code is going to decide whether a newline is quoted or not. I think latest protocol version works with blocks of data and no with lines and client PQputCopyData() returns a block -- only docs says that it is row of table. We could possibly solve that by specifying that the text output or input (respectively) is the complete line sent to or from the client, including newline or whatever other line-level formatting you are using. This still leaves the problem of how the low-level COPY IN code knows what is a complete line to pass off to the formatter_in routine. We could possibly fix this by adding a second input-control routine function formatter_linelength(text) returns integer which is defined to return -1 if the input isn't a complete line yet But formatter_linelength() will need some context information I think. The others words some struct with formatter specific internal data. And for more difficult formats like XML you need some others context data (parser data) too. Maybe there can be some global exported struct (like for triggers) and functions that is written in C can use it. It means for simple formats like CSV you can use non-C functions and for formats like XML you can use C functions. And if it will intereting for PL developers they can add support for access to this structs to their languages. (i.e., read some more data, append to the buffer, and try again), or = 0 to indicate that the first N bytes of the buffer represent a complete line to be passed off to formatter_in. I don't see a way to combine formatter_in and formatter_linelength into a single function without relying on out parameters, which would again confine the feature to format functions written in C. It's a tad annoying that we need two functions for input. One way that we could still keep the COPY option syntax to be just FORMAT csv is to create an arbitrary difference in the signatures of the input functions. Then we could have coexisting functions csv(text[]) returns text csv(text) returns text[] csv(text, ...) returns int that are referenced by FORMAT csv. It sounds good, but I think we both not full sure about it now, right? CSV support will probably better add by DELIMITER extension. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] COPY formatting
On Fri, Mar 19, 2004 at 09:39:58AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: It's pity that main idea of current COPY is based on separated lines and it is not more common interface for streaming data between FE and BE. Yeah, that was another concern I had. This API would let the formatter control line-level layout but it would not eliminate the hard-wired significance of newline. What's worse, there isn't any clean way to deal with reading quoted newlines --- the formatter can't really replace the default quoting rules if the low-level code is going to decide whether a newline is quoted or not. I think latest protocol version works with blocks of data and no with lines and client PQputCopyData() returns a block -- only docs says that it is row of table. But you can't assume that the client will send blocks that are semantically significant. For instance, if psql is reading a file to send with \copy, how's it going to know how the file is formatted? And what \n in attibutes data in CSV? I think CSV format doesn't use some escape for newline char. It means psql with \copy cannot be sure with CSV. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] COPY formatting
On Thu, Mar 18, 2004 at 07:48:40AM +0100, Karel Zak wrote: On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: The formatting function API can be pretty simple: text *my_copy_format(text *attrdata, int direction, int nattrs, int attr, oid attrtype, oid relation) This seems like it could only reasonably be implemented as a C function. Why? I said it's pseudo code. It should use standard fmgr API like every other PostgreSQL function or is it problem and I overlook something? It must to support arbitrary programming language and not C only. Well, I look over the COPY code and best will start with hardcoded version, but make it modular in code and if all will right we can think about some interface for others formats definition. OK? It's pity that main idea of current COPY is based on separated lines and it is not more common interface for streaming data between FE and BE. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY formatting
On Thu, Mar 18, 2004 at 09:29:03AM +, Lee Kindness wrote: To be honest this idea strikes me as overkill - over engineering. It was suggestion, maybe you're right :-) While i have done a lot of messing around reading/writing the binary format (and been stung by changes in that format) if you are using this format then you're 99% likely to be in control of the incoming/outgoing data and thus able to format to your wishes outwith COPY. I partly agree. But.. there is possible write directly final file by backend without data transfer to client. If we want to support this feature we need control output format by server... And.. I can image format that is use for BE/FE data transfer only and not for some final data presentation. For example compression of data stream from/to BE without PostgreSQL protocol change. Something else in the TODO regarding COPY is XML import/export, and for this to be supported in your proposed implementation the function would need to be passed in a heap more information. Yes, very probably some struct with all COPY information and format specific stuff. Tom was right that in this case it will C functions only. As I said I will try implement it without user defined function call for format conversion, but I will do it modular and in future we can create some interface for user defined formats. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] COPY formatting
Hi,
in TODO is item: * Allow dump/load of CSV format. I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction,
int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Comments?
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(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] COPY formatting
On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: The formatting function API can be pretty simple: text *my_copy_format(text *attrdata, int direction, int nattrs, int attr, oid attrtype, oid relation) This seems like it could only reasonably be implemented as a C function. Why? I said it's pseudo code. It should use standard fmgr API like every other PostgreSQL function or is it problem and I overlook something? It must to support arbitrary programming language and not C only. I can't really imagine the average user of COPY wanting to write C in preference to, say, an external perl script. What's the real use-case for the feature? Don't hardcode any format to PostgreSQL, be open for others formats. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] ORDER BY different locales
On Thu, Feb 26, 2004 at 09:16:03AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: I think possible solution is special function used ORDER BY clause which knows to switch by safe way to wanted locales, convert string by strxfrm() and switch back to backend locales. This function breaks the whole backend if an elog() failure occurs while Fixed by sigsetjmp(Warn_restart..). I hope it's more safe now. it's got the wrong locale set. I believe it would also be remarkably slow --- doesn't setlocale() involve reading a new locale definition You're right, it's slow. But sometimes is more important that it works and not all queries work with thousands records like my test below. I think the ultimate solution to our multi-locale problems will have to involve abandoning the C library's support functions and writing locale support that allows multiple locale-defining structures referenced by Agree. But as you said it's huge task and I think if it won't implement in 7.5 we can add nls_string() to the contrib tree. BTW, nls_string() is product of Czech database list where Oracle users have still problems with PostgreSQL ;-) Latest version: ftp://ftp2.zf.jcu.cz/users/zakkr/pg/postgresql-nls-string-0.52.tar.gz Note, I add CC: to pgsql-general, maybe it's interesting for some normal users too. Tests: # SELECT count(*) FROM nlstest; count 10 # SELECT data FROM nlstest ORDER BY upper(data) DESC LIMIT 1; Time: 1213.87 ms # SELECT data FROM nlstest ORDER BY nls_string(data, 'en_US') LIMIT 1; Time: 4269.00 ms Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ORDER BY different locales
Hi,
a lot of people sometimes need order same data in same DB by more
different locales. For example multi-language web application with DB
in UTF-8. It's problem in PostgreSQL, because PostgreSQL require set
LC_COLLATE by initdb.
I think possible solution is special function used ORDER BY clause
which knows to switch by safe way to wanted locales, convert string by
strxfrm() and switch back to backend locales.
Is this function interesting for PostgreSQL contrib or main tree? I
think it's very useful for a lot of users. I can prepare a patch.
Note, the original idea and patch is from
Honza Pazdziora [EMAIL PROTECTED].
For example, the Czech alphabet has between 'h' and 'i' letter 'ch':
# SHOW LC_COLLATE;
lc_collate
C
# SELECT data FROM str ORDER BY nls_string(data,'en_US');
data
---
chccc
# SELECT data FROM str ORDER BY nls_string(data,'cs_CZ');
data
---
chccc
The function returns result encoded in unsigned octal:
# SELECT nls_string('pg','en_US');
nls_string
--
033022001010010001002002
Source:
static char *lc_collate_cache = NULL;
PG_FUNCTION_INFO_V1(nls_string);
Datum
nls_string(PG_FUNCTION_ARGS)
{
text *locale = PG_GETARG_TEXT_P(1);
char *locale_str;
int locale_len;
text *txt = PG_GETARG_TEXT_P(0);
char *txt_str;
int txt_len;
text *txt_out;
char *txt_tmp;
size_t size = 0;
size_t rest = 0;
int i;
if ((VARSIZE(locale) - VARHDRSZ) = 0 || (VARSIZE(txt) - VARHDRSZ) = 0)
PG_RETURN_NULL();
/*
* Save original locale setting
*/
if (!lc_collate_cache)
{
if ((lc_collate_cache = setlocale(LC_COLLATE, NULL)))
/* cached independent on PostgreSQL mmgr */
lc_collate_cache = strdup(lc_collate_cache);
}
if (!lc_collate_cache)
elog(ERROR, invalid system LC_COLLATE setting);
/*
* Conversion to standard strings
*/
locale_len = VARSIZE(locale) - VARHDRSZ;
locale_str = palloc(locale_len + 1);
memcpy(locale_str, VARDATA(locale), locale_len);
*(locale_str + locale_len) = '\0';
txt_len = VARSIZE(txt) - VARHDRSZ;
txt_str = palloc(txt_len + 1);
memcpy(txt_str, VARDATA(txt), txt_len);
*(txt_str + txt_len) = '\0';
/*
* Set wanted locale
*/
if (!setlocale(LC_COLLATE, locale_str))
{
setlocale(LC_COLLATE, lc_collate_cache);/* paranoid? */
elog(ERROR, invalid LC_COLLATE setting: %s, locale_str);
}
pfree(locale_str);
/*
* Text transformation
*/
size = txt_len * 2;
txt_tmp = palloc(size);
memset(txt_tmp, 0, size);
rest = strxfrm(txt_tmp, txt_str, size) + 1;
if (rest = size)
{
pfree(txt_tmp);
txt_tmp = palloc(rest);
memset(txt_tmp, 0, rest);
rest = strxfrm(txt_tmp, txt_str, rest);
}
/*
* Transformation to unsigned octal
*/
txt_out = (text *) palloc(3 * rest + VARHDRSZ);
memset(txt_out, 0, 3 * rest + VARHDRSZ);
for (i = 0; i rest; i++)
{
sprintf(VARDATA(txt_out) + 3 * i, %03o,
(int)(unsigned char)*(txt_tmp + i));
}
pfree(txt_tmp);
VARATT_SIZEP(txt_out) = 3 * rest + VARHDRSZ;
/*
* Set original locale
*/
if (!setlocale(LC_COLLATE, lc_collate_cache))
elog(ERROR, invalid LC_COLLATE setting: %s, lc_collate_cache);
PG_RETURN_TEXT_P(txt_out);
}
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ORDER BY different locales
On Thu, Feb 26, 2004 at 09:16:03AM -0500, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: I think possible solution is special function used ORDER BY clause which knows to switch by safe way to wanted locales, convert string by strxfrm() and switch back to backend locales. This function breaks the whole backend if an elog() failure occurs while I don't think so. There is setlocale() to original locales before elog(). But important is idea of this function. We can rewrite it to fix some minor problems... it's got the wrong locale set. I believe it would also be remarkably slow --- doesn't setlocale() involve reading a new locale definition file from whereever those are stored? Yes, speed can be problem. I will test it. But I hope libc read locales one time only. The common usage is with SELECT where you apply same locales to all lines of result. I think the ultimate solution to our multi-locale problems will have to involve abandoning the C library's support functions and writing locale Yes, but I think nls_string() is nice solution for now. Butter than say no way... :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] msg translation into sk_SK, Docs: SGML - XML
On Fri, Jan 30, 2004 at 06:33:09PM +0100, BARTKO, Zoltan wrote: Having googled around I found this about Docbook to PDF conversion: http://www.linuxfocus.org/English/May2000/article152.shtml no sign of fop. I hope it helps and that I am not mistaken when I claim the format is docbook. Yes, it's simular jade way that we use for SGML now. The fop advantage is direct conversion without huge TeX stuff. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(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] msg translation into sk_SK, Docs: SGML - XML
On Thu, Jan 29, 2004 at 07:02:12PM +0100, Peter Eisentraut wrote: Peter E. works on XML docbook version of docs. I don't know if he wants to move directly to XML or uses on the fly conversion to XML by osx (sgml2xml). This conversion you try use by make testxml stuff in the doc/src/sgml/Makefile. I tested it and it works -- but for example conversion from XML to PDF by fop failed, but it's might be fop problem. The general consensus is that FOP is simply not there yet. Do you how idea how convert XML to fo or directly to pdf? Peter, what's your planns? Well, when people feel good about it, we can switch the stored format to XML. I'm not in a hurry, though, because what is there right now is I feel good about it. The important thing is support in Makefiles XML conversion to other formats like HTML, PDF and man. It's strange if after release almost nobody has idea how create PDF docs. almost equivalent. I've also tried out the po2xml thingy and it seems to work, but translating about 1 strings seems to be a daunting task. Be optimistic! ;-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ Information from NOD32 This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com ---(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] msg translation into sk_SK, Docs: SGML - XML
On Thu, Jan 29, 2004 at 08:29:14AM +0100, BARTKO, Zoltan wrote: I would like to take up the translation of pgsql msg strings into slovak (sk_SK). It is fairly similar to czech (cz_CZ), so it should go quite fast. Please stand up, if you have any objections. If you don't want to waste time do it if the release 7.5 will at least in beta, preferably in messages freeze state and nobody will modify PostgreSQL messages. I made the translation of parts of the 7.2 manual in slovak (tutorial, users manual, admin's manual almost ready) that time I thought I would publish it, but Bruce Momjian's book appeared in the bookstores a few weeks ago. I decided I would go on with the translation and use the sgml files (my translation was formatted as a LyX document) and a message translating program. I wanted to use KBabel, but that understands .po(t) files only. To have a .pot file I could use xml2po, but I need xml. I can convert sgml to xml myself, but it would be easier to have it done centrally - perhaps other languages would want their docs in their native language too. Once it is ready, the changes could be easily incorporated before the respective releases. Peter E. works on XML docbook version of docs. I don't know if he wants to move directly to XML or uses on the fly conversion to XML by osx (sgml2xml). This conversion you try use by make testxml stuff in the doc/src/sgml/Makefile. I tested it and it works -- but for example conversion from XML to PDF by fop failed, but it's might be fop problem. Peter, what's your planns? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] cache in plpgsql
On Fri, Jan 02, 2004 at 12:21:22PM -0500, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Tom Lane wrote: Another little problem is that plpgsql doesn't really have any mechanism for invalidating cached stuff at all; it will leak memory like there's no tomorrow if we start dropping cached subplans. Everyone seems to look at it as a PL/pgSQL specific problem. It is not! No, of course not, but plpgsql has issues of its own that (IMHO) should be solved along with the SPI-level problem. My original PREPARE/EXECUTE patch contained SPI_saveplan() version that save plan to query cache. I think it's pretty bad idea use for same things more separate solutions. For example see RI stuff (triggeres) -- it's perfect adept for PREPARE/EXECUTE query cache instead the current RI solution that save plans in own hash table. I think we can add support for work with query cache to SPI and use it in more places (RI, PL, etc.), something like SPI_saveplan_bykey(). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] time format
On Sat, Jan 03, 2004 at 10:20:57PM +0100, ivan wrote: ok, bat each time where i want to do select .. a nie tu use to_char, but it should be in function timestamp_out to convert time to string it would be easer and faster. I don't think it will too much faster :-) BTW, for example the Oracle allows to define default date/time output format by same way as for to_char(). TODO (?): SET TIMESTAMP_FORMAT = 'MM/DD/ HH24:MI:SS'; SELECT 'now'::timestamp; timestamp 01/05/2004 10:25:01 But it require check (be sure) that defined format is possible without problems convert back from string to timestamp. For this Thomas didn't like this idea. I think dynamic timestamp format is final solution of all problems with PostgreSQL date/time formats. Comments? Karel On Sat, 3 Jan 2004, Kurt Roeckx wrote: On Sat, Jan 03, 2004 at 09:25:14AM +0100, ivan wrote: but what about default style ? first time when i saw DateStyle i thought that i can use it like C/C++ function strftime. I would be not bad idea to have custom data style :) Use to_char() function to put it in any format you want. Kurt ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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 -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Dates BC.
On Thu, Dec 18, 2003 at 02:11:20PM +0100, Kurt Roeckx wrote:
I find this a little strange:
select date_part('year', '0002-01-01 BC'::date);
date_part
---
-1
It seems 1 BC and 0 are the same year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
It this normal or a bug?
I think this code is OK, butg is somethere in extract (date_part) code.
test=# select to_date('0020-01-10 BC'::text, '-MM-DD BC');
to_date
---
0020-01-10 BC
(1 dka)
test=# select to_date('0020-01-10 AD'::text, '-MM-DD BC');
to_date
0020-01-10
test=# select to_char('0020-01-10 BC'::date, '-MM-DD AD');
to_char
---
0020-01-10 BC
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Dates BC.
On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:
There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal,
not cardinal.
I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.
Is there connection between formatting.c and date_part() ?
I don't think so...
In backend/utils/adt/formatting.c:
if (tmfc.bc)
{
if (tm-tm_year 0)
tm-tm_year = -(tm-tm_year - 1);
... tm-tm_year = -(tm-tm_year - 1) is used for:
# select to_timestamp('0001/01/01 BC', '/MM/DD AD');
to_timestamp
0001-01-01 00:00:00 BC
and it's OK.
I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:
# select to_char('0001-01-01 BC'::date, '/MM/DD AD');
to_char
---
/01/01 AD
# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
---
0
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(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
[HACKERS] PL/SQL packages
Hi, in our TODO is the item: Add PL/PgSQL packages. I thought about it and for example Oracle's CREATE PACKAGE / PACKAGE BODY seems really interesting and modular. IMHO it's interesting item in PostgreSQL TODO, but I think there a is small collision between the schemas and possible package contents referencing: SELECT xyz.funcname(); ^^^ Is it some function in schema 'xyz' or some function in package 'xyz' in the current schema? Or is needful check both alternatives? Do you have any experience with it? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PL/SQL packages
On Fri, Nov 28, 2003 at 02:35:11PM +0100, Peter Eisentraut wrote: Karel Zak writes: in our TODO is the item: Add PL/PgSQL packages. I think the interesting part are the package-global variables. The name hierarchy seems completely redundant with schemas. Agree, but there is more advantage than package-global variables only. The Oracle PACKAGE: - package is interface definition only and the real functions are defined in PACKAGE BODY, it allows you change internal logic, but interface is still same. - package-global cursors - package-global variables - ?? Maybe PACKAGE can be in PostgreSQL implemented as special a little extended schema (like sequence are special table) and we needn't think about something new. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp convert function
On Thu, Nov 27, 2003 at 09:49:22AM +0100, Nhan NGO DINH wrote:
Hi,
This is really not a problem, may be in other circumstances...
This output has been obtained from a PostgreSQL 7.3.4
===
testdb=# select to_timestamp('23:20:30.123456', 'HH24:MI:SS.US')::time;
to_timestamp
-
23:20:30.123459
(1 row)
testdb=# select to_timestamp('23:20:30', 'HH24:MI:SS.US')::time;
to_timestamp
--
23:20:30
(1 row)
testdb=# select to_timestamp('23:20:30', 'HH24:MI:SS.US')::time;
to_timestamp
-
23:20:30.123459
(1 row)
I think it's fixed in 7.4.
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
---(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
[HACKERS] XML Docbook
Hi, what use pure XML Docbook (non-SGML) for 7.5 PostgreSQL docs? XML advantage: - more clean and simple conversion into printable formats by FO (Formatting Objects), - needn't huge TeX stuff (!), - Java based XSLT/FO processors like FOP (support PDF, PCL, PS, SVG, Print, AWT, MIF and TXT), - conversion to HTML/MAN/etc by xsltproc, - XML based reference docs is usable for example in clients programs as standard help, because integrate XML parser into program is more simple and usual than SGML parser. I think the current docs build system is usable for normal user only if he wants to generate HTML docs, but for example it's useless if he wants PDF output... For example you can compare our current doc/src/sgml/Makefile with following code for XML conversion: pdf: fop -xsl /path/to/stylesheet/xsl/fo/docbook.xsl -xml book.xml -pdf book.pdf ps: fop -xsl /path/to/stylesheet/xsl/fo/docbook.xsl -xml book.xml -ps book.ps html: xsltproc -o htmldir/ /path/to/stylesheet/html/chunk.xsl book.xml man: xsltproc /path/to/stylesheet/manpages/docbook.xsl ref.xml Comments? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML Docbook
On Fri, Nov 14, 2003 at 10:32:10AM +0100, Peter Eisentraut wrote: XML disadvantage: - no arbitrary parameter entities I unsure if I understand, can you show some example of this problem? I think there is a lot of XML Docbook docs in a lot of projects and I will wonder if in the PostgreSQL docs is something special what disable use XML instead SGML. Follow-up to [EMAIL PROTECTED] please. Hmm.. I must subscribe first :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] UPPER()/LOWER() and UTF-8
On Tue, Nov 04, 2003 at 04:52:33PM -0500, Tom Lane wrote: Alexey Mahotkin [EMAIL PROTECTED] writes: I'm running Postgresql 7.3.4 with ru_RU.UTF-8 locale (with UNICODE database encoding), and all is almost well, except that UPPER() and LOWER() seem to ignore locale. upper/lower aren't going to work desirably in any multi-byte character set encoding. I think Peter E. is looking into what it would take to It's a PostgreSQL and no UTF problem, because standard PostgreSQL text functions doesn't know something about arguments encoding and for this functions cannot use another (an example UTF's lower/upper) method for a work with strings. Maybe a little extend internal text datatype and like VARSIZE() use VARENCODING(). Maybe Peter already has some better idea. fix this for 7.5, but at present you are going to need to use a single-byte encoding within the server. (Nothing to stop you from using UTF-8 on the client side though.) You can use mutibyte on server side too, but you must to use for example convert() function for upper/lower arguments. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Unicode upper() bug still present
On Mon, Oct 20, 2003 at 10:58:00PM +0200, Peter Eisentraut wrote: (Note that I say Unicode a lot here because those people do a lot of research and standardization in this area, which is available for free, but this does not constrain the result to work only with the Unicode character set.) Why cannot do PostgreSQL as 100% pure Unicode system? We can do conversion from/to others encodings as client/server communication extension, but internaly in BE we can use only pure Unicode data. I think a lot of things will more simple... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] lower and upper not UTF-8 safe
On Mon, Aug 04, 2003 at 05:03:02PM -0400, Tom Lane wrote: Julian Satchell [EMAIL PROTECTED] writes: The implementations of lower and upper in src/backend/utils/adt/oracle_compat.c use the single byte macros from ctype.h to alter individual bytes in the text string. If the text is UTF-8 encoded this is totally wrong, and will result in an invalid string that is no longer UTF-8. Only if you use a locale that is assuming a character set that is not UTF8 but does have characters with the high bit set. I'm not sure that we can do anything to defend against locale/charset mismatch. We can try detect typical locale charset and compare it with actual charset used in DB and send NOTICE to FE if it's mismatched. The problem is portability of charset detection code, because there is differences between OS. The best it's if libc support nl_langinfo(CODESET) call. The complete code of charset detection you can found in libcharset or glib (I use simplification of these codes and it's 300 lines:-). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] [PATCHES] Czech NLS
On Wed, Jul 23, 2003 at 12:42:36PM +0200, Peter Eisentraut wrote: Karel Zak writes: Thanks. What time will freeze backend messages? I think after beta release. Typically (meaning in other large projects that care about these things), a string freeze is called much later during the release process, about 2/3 of the way between what we call start of beta and the final release. Translation updates are then accepted up to about the time of the first release candidates. OK. What I can tell you is that I just committed a batch of such message changes (affecting all message catalogs except libpq and backend), and I hope that that will be the last major such change in the affected programs until release. Most of the changes were in punctuation, so you don't have to start all over. I think gettext system is good designed for strings updates and if somebody use cool tool like poedit it's simple found changes and fix updated .po files. BTW, small suggestion - maybe will good add to your NLS page information about ispell .po files checking by pospell tool from spellutils package. pospell -n file.po -p ispell -- -d langname %f Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] CVS: compilation failed
./configure --prefix=/usr/lib/postgresql --enable-nls gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include -c -o miscinit.o miscinit.c -MMD miscinit.c: In function `SetCharSet': miscinit.c:205: error: structure has no member named `in' make[3]: *** [miscinit.o] Error 1 It's problem with: MyProcPort-raddr.in.sin_addr.s_addr ^^ Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CVS: compilation failed
On Tue, Jul 22, 2003 at 10:31:36AM -0400, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: ./configure --prefix=/usr/lib/postgresql --enable-nls I think you must have done --enable-recode too. Oops.. you're right :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] NLS: czech
Hi, I start translate rest of all non-translated PostgreSQL's LC_MESSAGES from http://developer.postgresql.org/~petere/nls.php. Please, if someone other will do this work connect me first. I want to prevent duplicated work... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] NLS: czech
On Mon, Jul 14, 2003 at 10:37:24AM -0400, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: I start translate rest of all non-translated PostgreSQL's LC_MESSAGES from http://developer.postgresql.org/~petere/nls.php. Please, if someone other will do this work connect me first. The backend messages will be getting edited over the course of the next week, so don't waste your time by doing anything with them until that dust settles. You could make progress on the frontend and client messages though. Yes. I don't want to translate something for BE until beta version will release. I have done pg_controldata, libpq, pg_resetxlog and I work on pgscripts now. Maybe I will work on pgadmin too. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TO_CHAR SO SLOW???
On Mon, Jun 23, 2003 at 06:08:19PM -0700, Maksim Likharev wrote: Hi, I have some SQL function, just regular function selects data by using 4 joins nothing fancy, but one thing pretty noticeable, I have to display 3 different columns with same date formatted differently, here are 3 different snippets: 1. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/') FROM ( SELECT x, y, dt FROM ) AS t ... 2. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/'), TO_CHAR(t.dt, 'Mon-') FROM ( SELECT x, y, dt FROM ) AS t .. 3. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/'), TO_CHAR(t.dt, 'Mon-'), TO_CHAR(t.dt, '') FROM ( SELECT x, y, dt FROM ) AS t ... # 1: 15000 rows, I getting data for 130 sec # 2: 15000 rows, I getting data for 160 sec # 3: 15000 rows, I getting data for 220 sec adding different fields into output change query time only marginally but adding or removing to_char, just heavily knocks performance. is it TO_CHAR so slow?? I don't think to_char() is so slow. What happen with performance if you use t.dt without formatting or if try some other function an example extract()? SELECT t.x, t.y, t.dt FROM ( SELECT x, y, dt FROM ) AS t; SELECT t.x, t.y, EXTRACT(year from t.dt) FROM ( SELECT x, y, dt FROM ) AS t; Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Automatic detection of client encoding
; } if (charset) *charset = (char *) codeset; if (strcasecmp(codeset, UTF8)==0 || strcasecmp(codeset, UTF-8)==0) return TRUE; return FALSE; } autoconf part: - AC_DEFUN(jm_LANGINFO_CODESET, [ AC_CHECK_HEADERS(langinfo.h) AC_CHECK_FUNCS(nl_langinfo) AC_CACHE_CHECK([for nl_langinfo and CODESET], jm_cv_langinfo_codeset, [AC_TRY_LINK([#include langinfo.h], [char* cs = nl_langinfo(CODESET);], jm_cv_langinfo_codeset=yes, jm_cv_langinfo_codeset=no) ]) if test $jm_cv_langinfo_codeset = yes; then AC_DEFINE(HAVE_LANGINFO_CODESET, 1, [Define if you have langinfo.h and nl_langinfo(CODESET).]) fi ]) -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] to_char(interval) --- done?
On Fri, Mar 21, 2003 at 03:32:11PM -0800, Josh Berkus wrote: Folks, Some months ago we agreed on this list that the functioning of to_char(interval) was not particularly useful the way it works in current stable versions. I don't see a to_char fix on the TODO list, though; does that mean it's already been fixed in 7.4? No. There was short discussion about it last week. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] to_char(interval) --- done?
On Mon, Mar 24, 2003 at 09:56:53AM -0400, Alvaro Herrera wrote: I volunteered to look into it, but got a sorta negative reply from Peter_E, but no response to my request for suggestions. I think what Peter was saying is to research some ways to manage intervals and other time related data types within what the SQL standard defines. to_char() and the like are only Oracle compatibility functions and should not be taken as serious ways to do things. The to_char() knows formatting numbers, time/date to almost arbitrary string. I unsure if SQL standard knows something like this and write something for interval only is not good idea (IMHO) if there is a lot of code which already know formatting data to string. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] actual cvs: compile error
gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/interfaces/libpq -I../../../src/include -c -o common.o common.c -MMD common.c:184: conflicting types for `cancelConn' common.h:30: previous declaration of `cancelConn' make[3]: *** [common.o] Error 1 make[3]: Leaving directory `/var/home/PG_DEVEL/pgsql/src/bin/psql' make[2]: *** [all] Error 2 The cancelConn has defined to the file common.c as static and in the common.h as extern. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] One SQL to access two databases.
On Mon, Dec 02, 2002 at 08:56:41AM -0800, Joe Conway wrote: Karel Zak wrote: On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: As I said, this is all very preliminary; comments, suggestions, requests are all welcome. Only idea/dream: what implement dblink as virtual schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Yeah, something along these lines is in my long term vision, but I don't think it will happen for 7.4. I'd like one more contrib/dblink release for the code to mature, and to solidify the features and understand the common usage issues. Agree. This expect load a lot of information about remote tables to BE for correct planner executor running. BTW, do you think is possible load this information also from non-PostgreSQL servers (Oracle, DB2...)? The problem with multiple client-SQL libs in BE is only a small part of transparent DBLINK imlementetion. Hopefully for the release *after* 7.4 I'll be ready to make a proposal to integrate dblink into the backend, get it accepted, and get it implemented. If you want to (a lot) use client library in backend it will need real and better memory managemnt for FE libs -- for example same mmgr as use BE. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Mon, Dec 02, 2002 at 12:48:38PM -0800, Christopher Kings-Lynne wrote: But if there is, then the sum/count(*) is nonsensical anyway. You must to use it in SERIALIZABLE transaction isolation. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] [GENERAL] One SQL to access two databases.
On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: As I said, this is all very preliminary; comments, suggestions, requests are all welcome. Only idea/dream: what implement dblink as virtual schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(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] 7.4 Wishlist
On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote: Hi guys, Just out of interest, if someone was going to pay you to hack on Postgres for 6 months, what would you like to code for 7.4? This is interesting discussion.. my wish: * error codes. It's very interesting that nobody other wants it... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] protocol change in 7.4
On Mon, Nov 04, 2002 at 07:22:54PM -0500, Neil Conway wrote:
(1) Add an optional textual message to NOTIFY
(2) Remove the hard-coded limits on database and user names
(SM_USER, SM_DATABASE), replace them with variable-length
fields.
(3) Remove some legacy elements in the startup packet
('unused' can go -- perhaps 'tty' as well). I think the
'length' field of the password packet is also not used,
but I'll need to double-check that.
(4) Fix the COPY protocol (Tom?)
(5) Fix the Fastpath protocol (Tom?)
(6) Protocol-level support for prepared queries, in order to
bypass the parser (and maybe be more compatible with the
implementation of prepared queries in other databases).
(7) Include the current transaction status, since it's
difficult for the client app to determine it for certain
(Tom/Bruce?)
(8) Error codes (maybe needn't change protocol)
- without this is PostgreSQL useless in real DB aplication
(9) Think about full dynamic charset encoding (add new encoding on
the fly)
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] OO future
Hi,
I read a presentation about Object-Oriented features in relation DBs.
The nice are UDT (user defined type):
CREATE TABLE person (
name varchar(32),
address ROW( street varchar(32),
town varchar(32)),
age int
);
INSERT INTO person VALUES ('Bill', ('Somestreet', 'Sometown'), 33);
SELECT name, address.town FROM person;
We have composite types in PostgreSQL and I think we can use it for this:
CREATE TYPE addr AS (street varchar(32), town varchar(32));
CREATE TABLE person (
name varchar(32),
address addr,
age int
);
Comments? I nothinig found about OO in the current TODO. BTW, my
examples are only small part of possible OO features, the others
ideas are for example define PRIVATE/PUBLIC attributes in composite
types and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100;
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OO future
On Wed, Oct 30, 2002 at 08:35:09AM +0200, Hannu Krosing wrote: Comments? I nothinig found about OO in the current TODO. I'm writing a small proposal for evoving inheritance and other OO features in 7.4 and beyond. Will post once 7.3 is out. Good! I look forward. BTW, my examples are only small part of possible OO features, the others ideas are for example define PRIVATE/PUBLIC attributes in composite types At least the Third Manifesto by Date et.al. claims that PRIVATE/PUBLIC is better left to standard access control mechanisms (GRANT/REVOKE). I agree to that. Yes, but it expect access control pre-column and for per composite type attribute. I understand PRIVATE as some internal data for methods and it needn't a speciffic access control, because control must be define for methods, and other way access PRIVATE data is not possible. IMHO it's better. The other important thing is possibility create table from type: CREATE TABLE adresses AS address_t; and methods, SELECT p.name FROM person p WHERE p.pay-tax() 100; The methods will probably have problems with syntax clashes with existing stuff. Hmm, p.pay.tax() ? Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] idle connection timeout ...
On Fri, Oct 25, 2002 at 03:31:22PM -0400, Mike Mascari wrote: Bruce Momjian wrote: Andrew Sullivan wrote: On Fri, Oct 25, 2002 at 11:02:48AM -0400, Tom Lane wrote: So? If it hits the installation-wide limit, you'll have the same problem; and at that point the (presumably runaway) app would have sucked up all the connections, denying service to other apps using other databases. I think Marc's point here is to limit his exposure to misbehavior of any one client app, in a database server that is serving multiple clients using multiple databases. That would indeed be a useful item. The only way to avoid such exposure right now is to run another back end. Added to TODO: * Allow limits on per-db/user connections Could I suggest that such a feature falls under the category of resource limits, and that the TODO should read something like: Implement the equivalent of Oracle PROFILEs. Yes! Please it's better than all discussions about some ugly variables. The PROFILE is better extendable and it's user specific and in the system with ROLEs it really cool and simple set user's system options. I talked about it more times, but is still ignore :-) I don't want to maintain my databases by SET command. profname session_per_user cpu_per_session cpu_per_call connect_time idle_time logical_reads_per_session logical_reads_per_call ... and a lot of others things in future. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PREPARE / EXECUTE
On Wed, Oct 23, 2002 at 11:02:14AM -0400, Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: I wonder if there is a way to store a parsed/rewritten/planned query in a table so that it can be loaded again. The original version of the PREPARE patch used a shared-across-backends cache for PREPAREd statements. We rejected that for a number of reasons, one being the increased difficulty of keeping such a cache up to date. I think actually storing the plans on disk would have all the same problems, but worse. Right. There's solution: persisten backend (for example like classic apache). This solve problem with lifetime of all persistent caches. It's already in TODO. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] Memory leaks
On Tue, Oct 22, 2002 at 11:28:23PM -0400, Tom Lane wrote: I then moved on to psql, again, just for fun. Here, I'm thinking that I started to find some other leaks...but again, I've not spent any real time on it. So again, I'm not really sure it they are meaningful at this point. psql might well have some internal leaks; the backend memory-context design doesn't apply to it. But why? In the Mape project is used mmgr based on PostgreSQL's mmgr and it's used for BE and FE. There is not problem with it (BTW backend is multithread:-). IMHO use memory-context design for FE is good idea if FE a lot works with memory. I already long time think about shared lib with PostgreSQL mmgr... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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
[HACKERS] index theory
Hi, I have SQL query: SELECT * FROM ii WHERE i1='a' AND i2='b'; There're indexes on i1 and i2. I know best solution is use one index on both (i1, i2). The EXPLAIN command show that optimalizer wants to use one index: test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b'; QUERY PLAN - Index Scan using i1 on ii (cost=0.00..4.83 rows=1 width=24) Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying)) It's right and I undererstand why not use both indexes. But I talked about it with one Oracle user and he said me Oracle knows use both indexes and results from both index scans are mergeted to final result -- this is maybe used if full access to table (too big rows?) is more expensive than 2x index scan and final merge. Is in PG possible something like this? And within query/table? I know about it in JOIN (and subselect maybe) only, but in the standard WHERE? test=# explain SELECT * FROM ii a JOIN ii b ON a.i1=b.i2; QUERY PLAN -- Merge Join (cost=0.00..171.50 rows=5000 width=48) Merge Cond: (outer.i1 = inner.i2) - Index Scan using i1 on ii a (cost=0.00..52.00 rows=1000 width=24) - Index Scan using i2 on ii b (cost=0.00..52.00 rows=1000 width=24) Thanks, Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] index theory
On Wed, Oct 16, 2002 at 09:25:37AM -0400, Rod Taylor wrote: On Wed, 2002-10-16 at 09:19, Karel Zak wrote: Hi, I have SQL query: SELECT * FROM ii WHERE i1='a' AND i2='b'; There're indexes on i1 and i2. I know best solution is use one index on both (i1, i2). The EXPLAIN command show that optimalizer wants to use one index: test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b'; QUERY PLAN - Index Scan using i1 on ii (cost=0.00..4.83 rows=1 width=24) Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying)) I think you typo'd. i1='a' AND i1='b' turns into 'a' = 'b' which certainly isn't true in any alphabets I know of. Oh... sorry, right is: test=# explain SELECT * FROM ii WHERE i1='a' AND i2='b'; QUERY PLAN --- Index Scan using i2 on ii (cost=0.00..17.08 rows=1 width=24) Index Cond: (i2 = 'b'::character varying) Filter: (i1 = 'a'::character varying) The query is not important ... it's dummy example only. I think about two indexes on one table for access to table. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 06:31:15PM -0400, Tom Lane wrote:
The middle part of that boils down (as of today) to
regression=# select to_date('402002', 'WW');
to_date
2002-10-01
(1 row)
and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to
represent Week 40 of 2002, it's probably related to the fact that Week 1
of 2002 is converted to
regression=# select to_date('012002', 'WW');
to_date
2002-01-01
(1 row)
which was a Tuesday.
Offhand this seems kinda inconsistent to me --- I'd expect
regression=# select extract(week from date '2002-09-30');
date_part
---
40
(1 row)
to produce 39, not 40, on the grounds that the first day of Week 40
is tomorrow not today. Alternatively, if today is the first day of
Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date
expression should produce today not tomorrow.
I notice that 2001-12-31 is considered part of the first week of 2002,
which is also pretty surprising:
regression=# select extract(week from date '2001-12-31');
date_part
---
1
(1 row)
Anyone able to check this stuff on Oracle? What exactly are the
boundary points for EXTRACT(week), and does to_date() agree?
Please, read docs -- to_() functions know two versions of number of
week
IW = iso-week
WW = oracle week
test=# select to_date('402002', 'WW');
to_date
2002-10-01
(1 row)
test=# select to_date('402002', 'IW');
to_date
2002-09-30
(1 row)
test=# select to_date('012002', 'WW');
to_date
2002-01-01
(1 row)
test=# select to_date('012002', 'IW');
to_date
2001-12-31
(1 row)
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql likes Tuesday...
On Mon, Sep 30, 2002 at 05:37:47PM -0400, Rod Taylor wrote:
select to_char(
to_date(
CAST(extract(week from CURRENT_TIMESTAMP) as text)
|| CAST(extract(year from CURRENT_TIMESTAMP) as text)
, 'WW')
, 'FMDay, D');
to_char
Tuesday, 3
(1 row)
The PostgreSQL not loves Thuesday, but WW for year 2002 loves it. Why?
Because 'WW' = (day_of_year - 1) / 7 + 1, other words this year
start on Thuesday (see 01-JAN-2002) and WW start weeks each 7 days
after this first day of year.
If you need human week you must use IW (iso-week) that start every
Monday.
I know there're countries where week start on Sunday, but it's not supported
-- the problem is with 'D' it returns day-of-week for Sunday-based-week.
Your example (I use to_xxx () only, it's more readable):
If you need correct for Sunday-based-week:
select to_char( to_date(to_char(now(), 'IW'), 'IW')-'1d'::interval, 'FMDay,
D');
to_char
---
Sunday, 1
If you need Monday-based-week (ISO week):
test=# select to_char( to_date(to_char(now(), 'IW'), 'IW'), 'FMDay, D');
to_char
---
Monday, 2
'2' is problem -- maybe add to to_xxx() functions 'ID' as day-of-isoweek.
It's really small change I think we can do it for 7.3 too.
What think about it our Toms?
In the Oracle it's same (means WW vs. IW vs. D)
SVRMGR select to_char(to_date('30-SEP-02'), 'WW IW Day D') from dual;
TO_CHAR(TO_DATE('
-
39 40 Monday2
test=# select to_char('30-SEP-02'::date, 'WW IW Day D');
to_char
---
39 40 Monday2
SVRMGR select to_char(to_date('29-SEP-02'), 'WW IW Day D') from dual;
TO_CHAR(TO_DATE('
-
39 39 Sunday1
test=# select to_char('29-SEP-02'::date, 'WW IW Day D');
to_char
---
39 39 Sunday1
Karel
--
Karel Zak [EMAIL PROTECTED]
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] regression test failure in CVS HEAD
On Fri, Sep 20, 2002 at 01:12:17PM -0400, Bruce Momjian wrote: Tom has fixed it. Sorry I didn't test earlier. Thanks. Neil Conway wrote: It seems the 'numeric' and 'int8' tests are failing in CVS HEAD. The culprit seems to be the recent to_char() change made by Karel, but I haven't verified that. The diff follows. You're right. Sorry. SELECT '' AS to_char_14, to_char(q2, 'FM.999') FROM INT8_TBL; to_char_14 | to_char ! + ! | 456. ! | 4567890123456789. ! | 123. ! | 4567890123456789. ! | -4567890123456789. The results like this are right. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] [PATCHES] to_char(FM9.9) bug fix
On Fri, Sep 20, 2002 at 09:24:00PM +0200, Peter Eisentraut wrote: Karel Zak writes: test=# select to_char(0,'FM9.9'); to_char - 0. (1 row) test=# select to_char(1,'FM9.9'); to_char - 1. (1 row) I find this highly bizzare. The FM modifier means to omit unnecessary In the code it's commented as terrible Ora format :-) trailing stuff. There is no reasonable business or scientific custom to leave a trailing point after a number. I think so. I don't know who can use format number like '1.' or '.0'. Can somebody explain why Oracle implement it, who use it? Or perhaps a more pragmatic question is, how would I print a number without the trailing point? Don't use FM or use FM9.0 Examples: 'SVRMGR' = Oracle8 Release 8.0.5.0.0 'test=#' = PostgreSQL 7.3b1 test=# select to_char(1, 'FM9.9'); to_char - 1. SVRMGR select to_char(1, 'FM9.9') from dual; TO_C 1. test=# select to_char(1, '9.9'); to_char - 1.0 SVRMGR select to_char(1, '9.9') from dual; TO_C 1.0 test=# select to_char(1, 'FM9.0'); to_char - 1.0 SVRMGR select to_char(1, 'FM9.0') from dual; TO_C 1.0 -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] PREPARE code notes
On Mon, Sep 09, 2002 at 11:51:08AM -0400, Tom Lane wrote: Karel Zak [EMAIL PROTECTED] writes: 1/ ExecuteQuery() (line 110). Why is needful use copyObject()? The PostgreSQL executor modify query planns? Yes, and yes. Unfortunately. Hmm, it's bad. Is there any way to fix executor? Maybe in far future we will save to cache all planns and copyObject() is not performance winning. 2/ Lines 236 -- 245. Why do you check for pre-existing entry of same name if you create hash table? I think better is use else for this block of code and check it only if hash table already exist. The code reads more cleanly as-is; changing it as you suggest would create an unnecessary interdependency between two logically distinct concerns. I don't believe :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PREPARE code notes
Probably nothing important, but I saw it in src/backend/commands/prepare.c: 1/ ExecuteQuery() (line 110). Why is needful use copyObject()? The PostgreSQL executor modify query planns? I think copyObject() is expensive call. 2/ Lines 236 -- 245. Why do you check for pre-existing entry of same name if you create hash table? I think better is use else for this block of code and check it only if hash table already exist. 3/ Last is cosmetic: see line 404, what happen if memory context is not valid? :-) (maybe use some elog() call) Thanks Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Memory management question
On Tue, Sep 03, 2002 at 12:28:37PM +0100, Nigel J. Andrews wrote: It's probably a pretty basic question explained in some document I haven't seen but...if I do something like a CreateTupleDescCopy() how do I know my memory context owns everything allocated without following the code all the way through until it returns to me? If some code doesn't call MemoryContextSwitchTo() all is allocated in current memory context. You can check if CurrentMemoryContext is same before and after call that is important for you - but this check say nothing, bacuse some code can switch to other context and after usage switch back to your context. IMHO is not common way how check it. (Ok, maybe check all contexts size before/after call...) Suggestion: add to memory managment counter that handle number of MemoryContextSwitchTo() calls. IMHO it can be compile only if MEMORY_CONTEXT_CHECKING is define. But I think there is not to much places which switching between contexts and all are good commented (I hope, I wish :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] PREPARE, FK's and VIEWs
On Wed, Aug 28, 2002 at 02:53:46PM +0800, Christopher Kings-Lynne wrote: Now that we have prepared statements, should the foreign key code be changed to use them? I think it's highly likely that they will be reused in a connection. Might be an idea. No FK, but more common is integrate query plan cache to SPI (saveplan). I don't how it's in the current implementation. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] Database Caching
On Sun, Aug 25, 2002 at 09:35:24PM -0400, J. R. Nield wrote: I'm not sure about query result caching or 'relation caching', since the first would seem to run into problems with concurrent updates, and the second is sort-of what the buffer cache does. Query plan caching sounds like a really good idea though. Neil Conway's PREPARE patch already does this for an individual backend. Do you think it would be hard to make it use shared memory, and check if a query has already been prepared by another backend? Maybe it could use something like a whitespace insensitive checksum for a shared hash key. The original version of query plan cache allows exactly this. But after some discussion the shared memory usage in qcache was remove. I think better and more robus solution is store cached planns in backend memory and allows to run backend as persistent (means not startup/stop for each client connection). Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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] Locale number format confusion
On Thu, Aug 08, 2002 at 11:19:04PM +0200, Peter Eisentraut wrote: It seems we need a smart plan for handling the decimal point vs. comma issue. Observe: (lc_numeric = de_DE) ^^ It seems like hellish toy... :-) Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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
