Re: [HACKERS] LIKE optimization in UTF-8 and locale-C
ITAGAKI Takahiro skrev:
I guess it works well for % but not for _ , the latter has to know, how
many bytes the current (multibyte) character covers.
Yes, % is not used in trailing bytes for all encodings, but _ is
used in some of them. I think we can use the optimization for all
of the server encodings except JOHAB.
The problem with the like pattern _ is that it has to know how long the
single caracter is that it should pass over. Say you have a UTF-8 string
with 2 characters encoded in 3 bytes ('ÖA'). Where the first character
is 2 bytes:
0xC3 0x96 'A'
and now you want to match that with the LIKE pattern:
'_A'
How would that work in the C locale?
Maybe one should simply write a special version of LIKE for the UTF-8
encoding since it's probably the most used encoding today. But I don't
think you can use the C locale and that it would work for UTF-8.
/Dennis
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Money type todos?
Tom Lane skrev: The money type is considered deprecated. I was also under the impression it would be eventually removed. Why are we accumulating TODOs for it? Because doing the TODOs would remove the reasons for deprecating it. Whether it is actually ever going to disappear is not agreed upon. What is the reason to keep it? The arguments I've seen is that numeric is too slow to use when you have a lot of money calculations to perform. But with that argument we should instead make a general artitmetic type that is fast and useful to more things than just money. Just drop the currency from money and we have one such type. Would we accept other money-like types, with other units? Like kilogram, liter, yards, square meters, and so on? And what use is the unit in money? It's not like it will do currency conversion or anything like that. I think money should go away and the database should provide more general types. /Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OT: IRC nick to real world mapping
Tom Lane skrev: Is there any cross-check on the correctness of this list? As have been said, there is a registration service that makes it harder to steal nicks. There is no guarantee that anyone who claim to be this or that really is who he say he is. On the other hand, a lot of us have been there most every day the last 5 years or so and after a while you do get to know the guy (or girl) behind the nick. (Hint: if someone shows up in IRC claiming to be me, he's more than likely lying.) In the special case of you I'm pretty sure we would spot it very fast if someone is pretending to be you. And if someone can fool us he would be just as good as having the real thing in the channel :-) /Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] savepoint improvements
Merlin Moncure skrev: The missing piece of the puzzle is the ability to recover a failed transaction without issuing a full commit/rollback. This could be a new flavor of the savepoint command, commit command, or a new command. As a bonus, upon recovering the transaction you could snap an sql statement...this would be great for scripting: BEGIN; SAVEPOINT X; COMMIT ON ERRORS SELECT FOO(); --or-- BEGIN; SAVEPOINT x; SAVEPOINT y ON ERRORS SELECT FOO; -- (or ROLLBACK TO SAVEPOINT x); COMMIT; comments? fast track to todo list? :-) Isn't the problem that you try to use psql for scripting and it doesn't have usual scripting power like branching (if) or looping (while,for) that most scripting languages have. If there was say an \if command in psql you could do things like this: BEGIN; INSERT INTO foo VALUES (42); SAVEPOINT X; INSERT INTO foo VALUES (NULL); \if errorcode 0 ROLLBACK TO SAVEPOINT X; INSERT INTO foo VALUES (666); \endif COMMIT; I'm not sure you want to extend psql to be a full scripting engine, but maybe. It would be useful to me if it had an \if command like above. An other alternative is to use some other language to write scripts in that already have branching, looping, expression evaluation and what else. /Dennis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] setseed() doc
Tom Lane skrev: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. Ok. What about the return value? The doc didn't say anything about it. /Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
David Fetter skrev: As far as big missing features go, here's a short list: * Windowing functions If we are to wish for things the window functions and a proper collation/locale support is what I miss the most. /Dennis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] max(*)
Shouldn't SELECT max(*) FROM foo; give an error? Instead it's executed like SELECT max(1) FROM foo; Just like count(*) is executed as count(1). Something for the TODO or is it a feature? ps. I know it's not an important case since no one sane would try to calculate max(*), but still. /Dennis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] BEGIN inside transaction should be an error
Peter Eisentraut skrev: Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout: You want to make a GUC that makes: BEGIN; BEGIN; Leave you with an aborted transaction? That seems like a singularly useless feature... If a command doesn't do what it is supposed to do, then it should be an error. That seems like a throroughly useful feature to me. And it would follow sql99 that demand an error. I'm surprised everyone seems to ignore that part (except maybe Peter who is the one I happend to reply to :-). A guc that people can turn off if they have old broken code, that would work for me. /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] BEGIN inside transaction should be an error
Tom Lane skrev: The SQL99 spec does say (in describing START TRANSACTION, which is the standard spelling of BEGIN) 1) If a start transaction statement statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state - active SQL-transaction. *However*, they are almost certainly expecting that that condition only causes the START command to be ignored; not that it should bounce the whole transaction. What is the definition of an exception condition? I thought that it ment that a transaction should fail and that completion condition are used for warnings that doesn't abort transactions. As an example I looked up division by zero in sql99 and it say this: If the value of a divisor is zero, then an exception condition is raised: data exception - division by zero. Do you mean that some exception conditions fail transactions and some doesn't? /Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] BEGIN inside transaction should be an error
Hi Yesterday I helped a guy on irc with a locking problem, he thought that locking in postgresql was broken. It turned out that he had a PHP function that he called inside his transaction and the function did BEGIN and COMMIT. Since BEGIN inside a transaction is just a warning what happend was that the inner COMMIT ended the transaction and released the locks. The rest of his commands ran with autocommit and no locks and he got broken data into the database. Could we make BEGIN fail when we already are in a transaction? Looking it up in the sql99 standard I find this: If a start transaction statement statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state - active SQL-transaction. /Dennis ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] -X flag in pg_dump
What use is the -X flag to pg_dump. The code say that if one add a setting feature to -X then there should also be a flag --feature. So we have for example: -X disable-triggers and --disable-triggers If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? -- /Dennis Björklund ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SHOW ALL output too wide
On Sat, 26 Nov 2005, Bruce Momjian wrote: See the discussion or really solo request by me for more feedback when this change was made for 8.1: http://archives.postgresql.org/pgsql-patches/2005-06/msg00295.php Where were you when I asked? I do not work with postgresql like you do and can't follow everything, that's just how life is. I've not used 8.1 much until recently, and that's when I noticed the new behaviour. I spend about 10 hours per week helping the pg project, mostly by giving support in the #postgresql irc channel. That's about all the time I am prepared to give. The main use I have of the SHOW ALL command it to tell people to run it to show me what settings they have. Didn't you notice it in the release notes that have been online for months? * Make SHOW ALL include variable descriptions (Matthias Schmidt) And you except me to understand from that line that the output from SHOW ALL is much harder for me to read now then it was before? I brought it up as soon as I knew about it. I don't think it's fair of you to demand that I keep my mouth shut just because I can't follow the development of pg as close as you can. But of course, if someone wants to pay my for working full time on pg I would be very happy :-) It is unlikely this will be changed in any 8.1.X release. If you want it modified for 8.2, we will need to see more than one person complaining about it, because until now no one has complained about the documented 8.1 change. Then I hope that when people notice it they will also speak up (if they dare to do so now given that they missed the discussion you speak of above). I agree that for 8.1.x we only want bug fixes (as always, unless there are very, very many complaints). If you can find a sufficient number of people who like the VERBOSE idea I don't like the verbose idea. I think commands like that don't belong in the server at all. Different clients have different needs. For example I prefer that the \d command is implemented by psql and not by a DESCRIBE command. I don't want a lot of different server commands that present things in different ways for different clients. SHOW need to stay because there have always been a SHOW command, but do we really want to build in more things like that? What I would want is one of: * Revert to the old SHOW ALL command * Implement a \show in psql that can do more then a server command if we want it to. My motivation is only for the good of postgresql. If the majority want something else then that's what should happen (of course). I'm just stating my view, nothing less and nothing more. -- /Dennis Björklund ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SHOW ALL output too wide
On Fri, 25 Nov 2005, Bruce Momjian wrote: OTOH, what's the relationship between \show and Martijn's wide output patch? Maybe the problem can be solved in a different way. What does \show do that SHOW does not? It could do several things. For example \show could omit the description column and \show+ can include it. One could let \show support a pattern just like we do for table names in \d. Other then that I don't know right now what it would do. Maybe if you show a single variable it could show the maximum and minimum value if such exist. My main problem with SHOW as it is in 8.1 is that the output is so wide that it's very hard to read the output. Is there any use for SHOW except in interactive psql sessions? -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SHOW ALL output too wide
On Fri, 25 Nov 2005, Bruce Momjian wrote: Is there any use for SHOW except in interactive psql sessions? There certainly is. Imagine querying for timezone. Also remember that pgadmin is a client application that is _not_ psql. I should have written SHOW ALL, that's the command output that is too wide to fit in a normal terminal window. Do pgadmin use SHOW ALL? I would expect pgadmin to query pg_settings so that it get all info about the variable that is stored and show it in the gui in some way. But I don't use pgadmin so I don't know how it show the list of server variables. SHOW ALL really is an interactive command line interface command that is implemented by the server. Nothing that can't be done by querying pg_settings. True, so we have pg_settings and SHOW. I see no reason for a third, \show. The same can be said about all slash-commands in psql. By the way, we have yet another way. We have the functions that fetch server variable settings, like pg_show_all_settings(). Is your suggestion that we keep SHOW ALL as is? Or is that we extend it to something like SHOW ALL NO DESC? If we keep it as is then I would like to ask who is the target user it's designed for? It's probably not for psql users since the output isn't readable by anyone but those that have terminal windows 165 characters wide. It can't be for computer clients since those don't need the description column. So who is it designed for? -- /Dennis Björklund ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NULL safe equality operator
On Thu, 24 Nov 2005, Tom Lane wrote: NOT (x IS DISTINCT FROM y) would be the standard-compliant way of spelling that. That's the sql99 way. In sql2003 (but not in pg) one can also do X IS NOT DISTINCT FROM y -- /Dennis Björklund ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Web page down (ad server)
Something is wrong with the web site for me. I look at: http://www.postgresql.org/developer/ then this is what I see: http://www.zigo.dhs.org/~dennis/tmp/dev.png (everything is there except the main content that is not). Turns out it's related to the ads, so if I just adblock the ad server I can see the page just fine. Kind of bad it's needed however :-) I also tried to show the page in opera and it looks the same as in firefox. ps. The cvs server also seems to be down (postgresql.org). -- /Dennis Björklund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Web page down (ad server)
On Tue, 22 Nov 2005, Magnus Hagander wrote: Turns out it's related to the ads, so if I just adblock the ad server I can see the page just fine. Kind of bad it's needed however :-) I also tried to show the page in opera and it looks the same as in firefox. Intersting. It works fine for me. Did you try multiple times? (Since you tried a different browser, I assume you did). Of course. Maybe it's was some dns problem (all the adresses seemed to resolve, but maybe some adress have been updated but not propagated to all dns servers, or something. What do I know?). As I said, I could solve it just fine by using adblock so it's not a problem for me. I just wanted to tell someone about it. I did try like 50 times during 30 minutes. Every time it got stuck waiting on 200.46.208.156. And guess what, now that I remove the adblock to try some more it seems to work again. Aaarg, computers drive me crazy :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Web page down (ad server)
On Tue, 22 Nov 2005, Dennis Bjorklund wrote: ps. The cvs server also seems to be down (postgresql.org). Forgot to say in the last mail, but this also works now. Seems like I should have waited some more before sending the mail. I waited 30 minutes but I should have waited 40... -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] SHOW ALL output too wide
I've noticed that in 8.1 the output of SHOW ALL includes a description column. This makes the output very wide which makes it hard to use from psql (I need to make the terminal window 164 characters wide to not get any line wrapping). I wish I would have noticed this before 8.0 was out and then I would have voted no. Also, how come it's not implemented by a \show command in psql that queries pg_settings. Then it would work like most other commands. And one could have a \show+ command that include the description. Actually, I'm going to implement a \show command and send to -patches and then SHOW can even be deprecated (if we want). SHOW is just a command line client command, that is implemented in the server. That is not how we normally do things in pg (for example, we have \d instead of a server DESCRIBE command). -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MERGE vs REPLACE
On Thu, 17 Nov 2005, Bruce Momjian wrote: Unless you have a table lock, INSERT has to be before UPDATE, think UPDATE, UPDATE (both fail), INSERT, INSERT. No matter what operation you start with you need a loop that try insert/update until one of them succeed like in this example: http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Without a loop you might not get to execute neither the insert nor the update. Why? Think about this example: BEGIN INSERT - fail because there is a row already - before we manage to do the update someone delete the row (which we can see in the default transaction isolation level) UPDATE - fail because there is no row so we will loop and try the insert again - before we manage to do the insert someone else does an insert INSERT - fail because there is a row already - before we manage to do the update someone delete the row You might need to loop any number of times before you manage to perform one of the two operations. Which operation you should start with depends on which of the two cases is the common one. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.0 - 8.1 dump duplicate key problem?
On Thu, 10 Nov 2005, Tom Lane wrote: When the dump gets to the point where the indexes/keys are built, the primary key fails to build due to duplicate key constraint failure. That's pretty bizarre. What's the datatype of the key column(s)? There was one guy on IRC that had the same problem. We didn't get that much info about it and suggested that he check the original database for corruption (rebuilding indexes) to make sure it's in a proper state. Either this is the same guy or/and we might have a bigger problem. The guy on irc also had an integer PK. -- /Dennis Björklund ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PG Killed by OOM Condition
On Mon, 3 Oct 2005, Jeff Davis wrote: involved, but I could be wrong. Is it possible to be hit by the OOM killer if no applications use fork()? Sure, whenever the system is out of mem and the os can't find a free page then it kills a process. If you check the kernel log you can see if the oom killer have been doing some work. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] roundoff problem in time datatype
On Sun, 25 Sep 2005, Tom Lane wrote: Alternatively: why are we forbidding the value 24:00:00 anyway? Is there a reason not to allow the hours field to exceed 23? One reason is because it's what the standard demand. Another is that it isn't a proper time, just like feb 31 isn't a proper date. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] roundoff problem in time datatype
On Mon, 26 Sep 2005, Tom Lane wrote: Actually, I think there is a case where 24:00 is a proper time. Isn't it used for adding leap seconds ? No, I think the usual notation for a leap-second is '23:59:60'. We do allow 60 in the seconds field for this purpose. Yes, and it can go up to 23:59:60.99 (depending on how many fractional seconds one want). I suppose there's another possible approach, which is to special-case the output of this value to look like '23:59:60' instead of '24:00:00'. You would get the same problem with 23:59:60.9 which I guess you want to round up. One solution is to round '23:59:59.9'::time(0) up to '00:00:00'. That is normally the next following time value after all. I know why you might not want to round it up to 00:00:00, but it's one logical solution. By the way, here is another example of the same problem: # SELECT time '23:59:59.9' + interval '0.1'; ?column? -- 24:00:00 # SELECT time '23:59:59.9' + interval '0.11'; ?column? - 00:00:00.01 (1 rad) -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] roundoff problem in time datatype
On Mon, 26 Sep 2005, Tom Lane wrote: Yes, and it can go up to 23:59:60.99 (depending on how many fractional seconds one want). That's an urban legend. There never have been, and never will be, two leap seconds instituted in the same minute. We really should reject anything larger than '23:59:60'. The above is still just one leap second. The time continues to tick until it wraps over to 00:00:00. So for example a time value of 23:59:60.42 exists if we allow just one leap second. One solution is to round '23:59:59.9'::time(0) up to '00:00:00'. 7.2 did that, and we concluded it was broken. Doesn't mean that it necissary was a correct conclusion (and I'm not stating that it was wrong, I would like to think about it for a while before I claim something like that). Do the sql standard say anything on the matter? -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] roundoff problem in time datatype
On Mon, 26 Sep 2005, Tom Lane wrote: b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with implementation-defined rounding or truncation if necessary. So it's implementation-defined what we do. Truncation would avoid the problem but of course loses some of the info. So, what are the alternatives: * Truncation. * Rounding and let it wrap when rounding up towards midnight. * Rounding and never let it wrap. The cases that would wrap goes to 23:59:59 (or 23:59:59.9 and so on for other precisions) or to 23:59:60 (or 23:59.60.9 and so on) if one start with a leap second time. Are there any more viable cases? -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Attention PL authors: want to be listed in template
On Thu, 8 Sep 2005, Tom Lane wrote: I've committed the changes to have a system catalog in place of the hard-wired table. In the initial commit, I listed only the languages included in the core distribution. If I understand this correct you have created a system table that contain the settings for some of the languages. It basicly have one row for each language? And in this row there is for example a column that say what the validator function is called, right? Or what do the current solution look like? Doesn't this make it hard for distributions to package up a language in a rpm (or some other system) and have it just work? If the per language info was simply stored in a file then this file can be included in a package and you can install a new language in a simple way. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] On hardcoded type aliases and typmod for user types
On Thu, 1 Sep 2005, Martijn van Oosterhout wrote: Err, well. My thought was a certain group of type-suffix options would be permitted (only zero or one at a time), for example: WITH TIME ZONE WITHOUT TIME ZONE CHARACTER SET xxx String types have 3 modifiers, the length, the charset and the collation. The syntax of these are defined by the standard so at least that syntax ought to be allowed (even if there are more work to actually do anything with charset and collation info). -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Call for 7.5 feature completion
On Mon, 29 Aug 2005, Christopher Kings-Lynne wrote: Oh, and 'select rowid, * from table' which returns special rowid column that just incrementally numbers each row. In sql2003 there is a window function called ROW_NUMBER() that can be used to get numbers like that (one also need to specify the window to be the full table in this case). I think it can look like this (based on me reading the standard, i've not tested it in one of the other databases that support window functions): SELECT ROW_NUMBER() OVER (ORDER BY id), * FROM table; The over part specify that the whole result set is the window and that the row numbers should be assigned to the result in that order. In practice you want that order to be the same as the whole order I guess SELECT ROW_NUMBER() OVER (ORDER BY id), * FROM table ORDER BY id; Based on some googeling DB2 seems to allow OVER () while oracle does not and you need to specify the ORDER BY (or some other window definition) in the OVER part. Anyway, I just want to point out that row numbers are possible to get in sql2003, even if a simpler syntax like the above can also be useful. Maybe one can just extend sql2003 and let the OVER part be optional all together, and use SELECT ROW_NUMBER(), * FROM table; ps. A window is similar to group by, but you keep all rows in the result set. With group by you get one row from each group in the result set, -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Call for 7.5 feature completion
On Thu, 25 Aug 2005, Josh Berkus wrote: SavePoints be able to use within functions. ( I think this involves making procedures that execute outside of a transaction) Nope, supported in 8.0 for PL/pgSQL. Not sure about other languages. You can't use savepoints, you can trap errors which is implemented using savepoints. You still might want to write code like this: BEGIN SAVEPOINT foo; IF SOME_ERROR_CODE = 1234 THEN ROLLBACK TO SAVEPOINT foo; END ... You can write code like this if you issue each command from the client, say using libpq, but not in pl/pgsql. -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS]
On Wed, 10 Aug 2005, elein wrote: implementation of the SQL-3 standard for multi-value NULLs for PostgreSQL? SQL-3, that's what became sql99, isn't it? Anyway, there is nothing like what you explained in sql 99 nor sql 2003. The boolean type have a third value called UNKNOWN that is just an alias of NULL, that's about all the changes as I can recall in the NULL area. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] #escape_string_warning = off
On Mon, 1 Aug 2005, Jeff Davis wrote: Does the SQL standard provide no way to have a NULL character in a string constant? Is single-quote the only special character? I don't think it forbids you from using the null character. It's not like the strings are zero terminated. Some encodings might not allow the null character, but that's different. ps. null character does not have anything to do with the sql NULL. I'm sure there is someone somewhere that need this info. -- /Dennis Björklund ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] #escape_string_warning = off
On Tue, 2 Aug 2005, Jeff Davis wrote: Does the SQL standard provide no way to have a NULL character in a string constant? Is single-quote the only special character? I don't think it forbids you from using the null character. It's not like the strings are zero terminated. Some encodings might not allow the null character, but that's different. But doesn't PostgreSQL forbid us from using the NULL character in a query at all? Don't we always have to escape or encode it in some way? Pg does not allow \0 in strings at all. Try SELECT 'abc\0def'; in the current version of pg. The sql standard doesn't forbid null values in strings as far as I know and that's all I talked about. To have a sql standard string with null inside you just insert the 0 byte (for normal single byte encodings), no escaping needed. Internally pg handles strings as \0-terminated entities which is a bit unfortunate but that's what we have. That's why 'abc\0def' became the string 'abc'. Most character sets forbid \0 in strings anyway. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #1745: Unable to delete data from the
On Sun, 10 Jul 2005, Sivaraman K.G wrote: The error in the log file is as follows : ERROR : xlog flush request 0/D17B00 is not satisfied --- flushed only to 0/C31ED0 CONTEXT : writing block 0 of relation 17231/17232/17249 WARNING : could not write block 0 of 17231/17232/17249 DETAIL : Multiple failures --- write error may be permanent. Check your kernel log for disk errors. It could very well be that your disk is about to die (or already have). -- /Dennis Björklund ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SQL99 - Nested Tables
On Wed, 6 Jul 2005, Darren Alcorn wrote: I was interested as to if there were plans to develop SQL99 nested tables. Could you give an example of SQL99 nested tables? It might help us who don't know what the term stand for understand the issue. I've browsed through (bur not fully read) sql99 more then once in my life and I don't recall any nested tables. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] LGPL
On Wed, 15 Jun 2005, Bruce Momjian wrote: K, that's what confused me as I got the impression it was ok to require LGPL libraries but not GPL. I think the answer isn't clear on that one. If that is not clear then what is the difference between a LGPL lib and a GPL one? To copy code from said lib into pg could never be allowed, but just linking to it surely can not be a problem. LGPL libs are used all over by all kinds of closed sorce applications and that's the whole idea of making things (like glib) into LGPL instead of GPL. For example Acrobat Reader 7 for unix uses GTK+ and it is LGPL. Acrobat Reader surely do require GTK+. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Backslash handling in strings
On Tue, 31 May 2005, Tom Lane wrote:
The case that convinced me we need to keep some sort of backslash
capability is this: suppose you want to put a string including a tab
into your database. Try to do it with psql:
t= insert into foo values ('TAB
Guess what: you won't get anywhere, at least not unless you disable
readline. So it's nice to be able to use \t.
To insert a tab using readline you can press ESC followed by TAB. This
works as least in readline as it is setup in redhat/fedora (and readline
can be setup in 1000 different ways so who knows how portable this is).
--
/Dennis Björklund
---(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] Backslash handling in strings
On Tue, 31 May 2005, Tom Ivar Helbekkmo wrote: ...or ^V followed by TAB, as per age-old tradition. :-) Right, I forgot about that one. One can also do other control characters instead of TAB by pressing CTRL-J and similar. Well, I just wanted to point out that it's possible. The main problem is still to make sure that old dumps work and can be imported. I don't see how that can work without a GUC variable in addition to the E'foo' stuff (but that's not so bad as it can be phased in to support old pg_dumps and phased out again in pg 10 or something). -- /Dennis Björklund ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] SO_KEEPALIVE
How come we don't set SO_KEEPALIVE in libpq? Is there any reason why we wouldn't want it on? -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SO_KEEPALIVE
On Mon, 16 May 2005, Tom Lane wrote: How come we don't set SO_KEEPALIVE in libpq? Is there any reason why we wouldn't want it on? Is there any reason we *would* want it on? The server-side keepalive should be sufficient to get whatever useful impact it might have. Wouldn't the client also want to know that the server is not there anymore? I talked to Gaetano Mendola (I think, but you never know on irc :-) and he had some clients that had been hanging around for 3 days after the server had been down and later up again (stuck in recv). Server-side keepalive is enough for the server to clean up when clients disapears, but this do nothing to help clients detect that the server is gone. So I don't see what server side keepalive has to do with it. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] SO_KEEPALIVE
On Mon, 16 May 2005, Tom Lane wrote: On the other hand, it seems to me a client-side SO_KEEPALIVE would only be interesting for completely passive clients (perhaps one that sits waiting for NOTIFY messages?) A normal client will try to issue some kind of database command once in awhile At least some of the clients was psql. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] inclusions WAS: Increased company involvement
On Wed, 4 May 2005, Marc G. Fournier wrote: Just curious here ... but do any of the version control systems provide per directory user restrictions? Where I could give CVS access to Joshua, for instance, just to the plphp directory? Just how many incidents where people change the wrong files do you except. Maybe it's just easier to handle one such case every third year than to set up some system to prevent it. -- /Dennis Björklund ---(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] inclusions WAS: Increased company involvement
On Wed, 4 May 2005, Joshua D. Drake wrote: Just how many incidents where people change the wrong files do you except. Maybe it's just easier to handle one such case every third year than to set up some system to prevent it. The number of incidents isn't the issue, the fact that it could happen at all is. This isn't a web browser. Du you have anything against browsers? :-) This is a system that companies, very - very big companies rely on. We must have a controlled, documented process for comitters. And? If you tell someone he/she is just allowed to commit in the pl/foo subproject then that's probably more then enough. The nice thing with cvs is that old things are not lost and all the commits are sent out on a mailinglist. I don't see how this is any different just because some very - very big companies are involved. If it's easy to do, fine. I just don't see it as a very important thing. Anyway. I think it's a good thing that postgresql do as little as possible and stuff that can be handled separately are. -- /Dennis Björklund ---(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] Feature freeze date for 8.1
On Mon, 2 May 2005, Tom Lane wrote: #1 Defend against loss of connectivity to client I claim that if you have a problem with #1 you ought to go discuss it with some TCP hackers: you basically want to second-guess the TCP stack's ideas about appropriate timeouts. Maybe you know what you are doing or maybe not, but it's not a database-level issue. Different applications can have different needs here. For some it's okay to wait a long time, for others it is not. The tcp hackers have provided an api for clients to set these values per socket (setsockopt with TCP_KEEPIDLE and similar (in linux at least)). My problem with the above setting is that some operations can be in progress for a long time on the server without generating any tcp/ip traffic to the client (a non verbose vacuum I guess is such a case). Such an operation would look like it's idle. There is an overlap with session and transaction timeouts, most applications work fine with any of these. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Feature freeze date for 8.1
On Sun, 1 May 2005, Alvaro Herrera wrote: Well, if process A loses the connection to the client, then the transaction will be rolled back and other processes will be able to continue. If the other end of a tcp/ip connection just disapears, for example if the network cable is cut off then in linux it can take up to 2 hours as default for it to close the connection. Normally if a client application dies then the client OS cleans up and closes the socket so that the server knows about it. There are some settings that one can alter to change the time it waits before probing and killing the connection, ie tcp_keepalive_time in /proc/sys/net/ipv4/. It's documented in man tcp that say that it will take 2h11m as default to kill of such a connection. Pg could of course also implement some pinging protocl that should be done every now and then by the client so that the server knows that it is alive. For now you just have to lower the global settings as the one above if you want it to handle it better. -- /Dennis Björklund ---(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] Feature freeze date for 8.1
On Sun, 1 May 2005 [EMAIL PROTECTED] wrote: If a database wants to get bigger on the usage these settings like this must be implemented. Lucky thing that postgresql is open source so you or anyone else that need it can implement or sponsor it. Postgresql gets as good as we make it and nothing happens unless someone that need a feature sit down and implement it. First, a database must have real database features, not extreme features. Different people have different needs. For me this have not even once been a problem, so it's not something that I personally will lose any sleep over. It doesn't mean I wouldn't welcome that someone else work on it. -- /Dennis Björklund ---(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] Help - Urgent
On Fri, 15 Apr 2005, ElayaRaja S wrote: Hi, I am unable to restart the PostgreSQL. I am using redhat Linux 9 with postgresql 7.4.5. Unexpectedly due to ups problem my server was shutdown once. After that i am unable to restart the server. DETAIL: The data directory was initialized by PostgreSQL version 7.3, which is not compatible with this version 7.4.5. pg_ctl: cannot start postmaster Sounds like you have upgraded from 7.3 to 7.4, and first now when you rebooted it tried to use the new installation. The binary data files for 7.3 does not work with 7.4. To upgrade one need to dump the old and restore into the new. The solution is most likely to downgrade to the previous version, the one that created those data files (7.3.x). -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Should we still require RETURN in plpgsql?
On Tue, 5 Apr 2005, Tom Lane wrote: CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; RETURN; END; $$ LANGUAGE plpgsql; The RETURN statement is kinda useless in this example, but it is still required, because we don't allow control to fall off the end of a plpgsql function without causing an error. I am thinking we should allow exit by falling off the end of the function when (a) it has output parameter(s), or (b) it is declared RETURNS void. Comments? The above code example do not have any RETURNS clause, does that mean that it defaults to RETURNS void? I don't see what (a) has to do with anything. The return value is independent of in/out:ness of the parameters, isn't it? -- /Dennis Björklund ---(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] [BUGS] We are not following the spec for HAVING without
On 14 Mar 2005, Greg Stark wrote: select distinct on (x) x,y,z order by x,y,z You can do the equivalent: select x, first(y), first(z) order by x,y,z group by x But you can also handle the more general case like: select x, first(y), first(z), avg(a), sum(s) order by x,y,z group by x I don't really care one way or the other about the first function per se. The standard (sql2003) have what is called windows where one can do these things and much more. A window is like a group by, but you keep all rows in the result. This can be used to for example enumrate the rows within a window partition using ROW_NUMBER(). It can later can be used in a WHERE to select the top 3 rows in each window, or something like that. Here is an example that calculate the avg and sum for each window. It return all the rows (x values) in the window together with a row number (within the window) and the 2 aggregate results. In this case the aggregates will be the same for all rows in the partition but one can also get it to do a kind of of sliding window aggregate (for example the avarage of the row before and the row after the current row): SELECT ROW_NUMBER() OVER bar AS num, x, avg(a) OVER bar, sum (a) OVER bar FROM foo WINDOW bar AS PARTITION BY x ORDER BY x, y, z; and then one can put that whole thing as a subselect and just select the rows with num = 1. This doesn't mean that we don't want functions like first() and last(), they are also be useful. I just wanted to inform that with sql2003 one can write queries with the same effect as the above (but much more complicated, of course :-). ps. All I know about the window functions is from what I've read in the draft of the sql2003 standard. It's not the perfect way to learn about new features so I wont bet my life on that the above example works as is. If someone knows better I'd like to hear about it. ps2. I'd love to read a book that discusses the sql2003 (or even sql99) that explain features, give examples, and so on. But i guess the market don't want books that explain things that no database have implemented yet (Oracle have window functions but i've never used that). -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] We are not following the spec for HAVING without
On 14 Mar 2005, Greg Stark wrote: SELECT ROW_NUMBER() OVER bar AS num, x, avg(a) OVER bar, sum (a) OVER bar FROM foo WINDOW bar AS PARTITION BY x ORDER BY x, y, z; Note that as you said, this returns just as many records as are in the original table. The OLAP functions here are just regular functions, not aggregate functions They are aggregate functions, the avg() is a window aggregate function according to the standard. It runs over all values in the same partition. -- albeit functions that use data from other records other than the one being output. Yes, and not just one other record, but a number of them. Isn't that what aggregate functions are? Anyway, I just wanted to point to this area in the standard to tell others what are possible using standard constructs. It doesn't really help anything in this specific case. Pg will not have any of this implemented in the nearest future (I guess). There is always a chance that someone see the mail, get interested, learn about it and then implements it :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
On 17 Feb 2005, Greg Stark wrote: Gaetano Mendola [EMAIL PROTECTED] writes: We do ~4000 txn/minute so in 6 month you are screewd up... Sure, but if you ran without vacuuming for 6 months, wouldn't you notice the huge slowdowns from all those dead tuples before that? Most people that we have seen on irc that run into the problem do vacuum some tables, but forget to vacuum all. Then their tables work fine but suddenly some system tables like pg_databases and pg_shadow become empty since they never was vacuumed... -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] French site with postgresql name
Some french guy on IRC showed the site http://www.postgresql.fr/ that does not contain anything about postgresql. I don't speak french so I can't tell what the page is about. It looks linux related. This doesn't really belong on the -hackers list but I don't know where to send the info or even if someone cares about it at all. --- some time later --- It turns out that the to me unknown french guy I was chatting with, was in fact Gaetano Mendola but with a new irc nick (and I've cc:ed him). -- /Dennis Björklund ---(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] adding and compiling new code
On Sun, 7 Nov 2004, Martha Chronopoulou wrote: recompile the new source code that I added in some .c files. I suspect that there must be a quicker way to compile those files than executing the commands: ./configure, gmake, gmake install... . Could I use only some Makefiles (the correspodent ones) that there are in each directory? If yes, how should I use them? After one have made the first compile and install, it's usually enough to just compile and install a part of pg. For example if you have changed something in psql then you run make followed by make install in the psql subdirectory of the source. Some changes demand a full new compile (make clean and sometimes make distclean) and some changes demand that you do initdb again, but in most cases one can just compile a part of pg. Then install that part and restart the server. I have a fairly slow computer (800 MHz) and I use ccache which make it much faster to rebuild pg then without. A big difference. I still want a faster computer, but we don't get all we want I'm afraid :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] charset/collation in values
On Tue, 2 Nov 2004, Peter Eisentraut wrote: A collation implies a character set, so you only need to store one piece of information anyway. No, a collation implies a character repertoire like UCS (unicode), it can apply to several character sets like UTF8 and UTF16. One can enumerate all combinations if one want to, as suggested previously. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] charset/collation in values
On Tue, 2 Nov 2004, Peter Eisentraut wrote: For the theoretical specification of a collation, it might suffice to know the character repertoire. But I think in practice, the implementation of a collation will require knowing the specific character encoding. The named entity that is called a collation works for a character repertoire. It would need to handle different charsets for that repertoire of course. So there would be one collation called say ucs_sv and not utf8_sv, utf16_sv, utf32_sv. Anyway, this is not a problem. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] charset/collation in values
On Mon, 1 Nov 2004, Tom Lane wrote: I think the number of charset/collation combinations will be relatively few so perhaps it would be space efficient to maintain a table where each combination is given an oid and have string values store that rather than two separate oid's? In fact, we should do our best to get the overhead down to 1 or 2 bytes. Two OIDs (8 bytes) is ridiculous. Just to be clear, we don't want to store it on disk no matter what since it should be enough to store it once for each column. As a first solution we could store it just to keep it simple until we have tried it out. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: and now we could add functions that work with this charset CREATE FUNCTION bar (x VARCHAR(255) CHARACTER SET foo) What we are saying is that we don't want to be able to do this? Not sure we want to add above. Is it something defined in the standard? The syntax in sql2003 do allow it. The exact semantics is difficult to get a clear picture of from the spec (as always). I thought this question was the whole argument. We can't have something like the above with the pg overloading since then the resolving process will be too hard according to Tom. 2) make shared tables such as pg_database and pg_shadow can handle multiple charsets. this is necessary because database names and user names could be repsented in different charsets Shouldn't we just define the charset for user names and database names? Either one fixed or one that's set during initdb. You don't mean that we want different user numes to be defined using different charsets? The rest of the points looks good to me. The main problem is still what to do with the function definitions as above. Is it something we want or not? Is the charset something that makes two text types different or not? -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: I wonder what is the intention to allow such that syntax. It seems it's just useless since we could make a function bar() which accepts any charsets. One could override the behaviour of functions by adding a charset and a adding new definition of an old function name for that charset. Like adding a new collation and define a new cmp() function for that collation that works different then some old definitons of cmp(). The whole discussion came because I start to look at problems from what is in the specification and try to fit that into pg. Not everything will fit, it's just my starting point when discussing. Tom starts at the other end and then it looks like a big controversy. About the explosion of the number of functions needed. It's not obvious to me that there will be an explosion if one manage to allow both full types that include charset and more generic functions that work on any text type. It seems to me that there are not that many interesting combinations anyway. Most applications will use one charset and define functions that work with just that charset. Anyway, the only way to see what problems would arise is to try. I was hoping that the step A and B in the plan was something that we wanted no matter of how the locale problem was later solved. With those in place it would be easier to experiment. -- /Dennis Björklund ---(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] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote:
I don't understand your point. Today we already use one length()
function for any charsets as Tom has already pointed out.
We have one length function that inside do different things depending on
the charset. If you want to add a charset and implement the length
function for that charset, how do you do that?
The length of a utf-8 string is not calculated the same way as the length
of a latin1 string. Each charset (encoding) have its own way of
calculating the length.
And by the way, today our databases just work with one charset at all and
what length do is decided by a global variable. The difference we talk
about here is the one between
length(latin1) ...
length(utf-8) ...
length(ascii) ...
and
length(x)
{
if charset(x) == latin1 then
,,,
else if charset(x) = utf-8 then
,,,
}
The question in your approach is how you could handle the coercibility
property. It's a transient and on memory property thus will not fit
into the function declaration. No?
No, it's not part of the function signature. Coercibility is a way to
decide what collation to use. Depending on where the value comes from it
can have different coercibility and when one do operations that involves
different collations the coercibility decide how ambiguities are resolved
(which value will be coerced).
If one would want function signatures with charsets in them and where the
charset information is stored, it doesn't have to be opposit of each
other.
I've currently been thinking that one can avoid storing the charset in the
value by handling types like that. I even though that there was no way
that anyone in the pg project would ever accept to enlarge the string
values, obviously a wrong assumption :-)
Even when one do store the charset in the value one might want to have
function overloading to depend on the charset of the string (when
specified).
That's the same opinion that if I declare a function
foo (x varchar(5))
begin
...
end
then I expect to get strings that are max 5 chars long. Why do we allow
the (5) if it's just droped? If I define a column as varchar(5) then the
column values are relly max 5 chars long, but it does not work for
functions like that.
Let us simply agree that we do store the charset/collation/... in the
(memory) values. On disk we don't want that since the column type do
decide it totally, do we agree on that?
--
/Dennis Björklund
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] charset/collation in values
I've looked into storing charset/collation in the string values. This means that we change varchar/text/BpChar to be structures that have a charset oid field and a collation oid field, the rest of the Datum is the string data. Coercability I think one don't need to put in the Datum and it can be stored in the Nodes. Charset/Collation need to be in the Datum since we send that into functions as arguments. Since we are changing what's stored in the Datum and the normal code saves that on disk then we will end up with charset/collation stored on disk for each value. If we want to avoid storing charset/collation both in the column type and in each row, we would need an extra layer that transforms the Datums before they are stored. As a first implementation it's easier to just store everything. For each type we need to have convertion functions to and from strings. Any suggestion of how to represent these as strings now when it's a string plus two oid's? This is a though one.. I have more comments/questions later on, but these are enough for one mail. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Charset/collate support and function parameters
I have a long term plan to implement charset support in pg and now when I have dropped the work on the timestamps, I've been looking into this subject. Today we store the max length of a string in the typmod field, but that has to be extended so we also store the charset and the collation of the string. That's simple but we need functions that take a string of a specific charset and collation as an input and give that as a result. Currently all information we have about function arguments are the OID of the type. The function argument OID's are stored in an array in pg_proc and I suggest that we instead of this array have a table pg_parameters that is much like http://www.postgresql.org/docs/7.4/static/infoschema-parameters.html Notice how there are a lot of columns describing the dynamic parts of a type, like character_maximum_length, character_set_name, datetime_precision. We would of course not store the name of a charset, but the oid (and so on). Most of these are NULL since they only apply to a specific type, but that's okay since NULL values are stored in a bitmap so the row width will still be small. Before one start to work on charset/collation support I think it would be good of one can make the above change with just the old properties. As a result we could write functions like foo (bar varchar(5)) We probably won't write functions like that very often. but as a first step this is what we want. Changing this is a lot of work, especially when one look in pg_proc.h and realize that one need to alter 3000 lines of DATA(insert OID = 2238 ( bit_and PGNSP PGUID 12 t f f f i 1 23 23 _null_ aggregate_dummy - _null_)); DESCR(bitwise-and integer aggregate); into another form. The 23 should be pulled out and it would become a row in the pg_parameters table. Maybe some job for a script :-) Sometimes I wish that (at least part of) the bootstrap was in a higher level and that the above was just normal sql statements: CREATE FUNCTION bit_and ( ) AS ... In addition to the function arguments we also need to treat the function return value in a similar way. The natural solution is to extend pg_proc with many of the same columns as in the pg_parameters table. One could also reuse the pg_parameters table and store a parameter with ordinal number 0 to be the return value. But then there would be some columns that do not apply to return values. My current plan is A) Implement a pg_parameters table and let everything else work as today. Also, the return values have to be taken care of in a similar way. B) Change function overloading so we can have functions with the same name but different properties. For example for strings that means different max lengths are used to resolve overloading. C) Work on charset / collation. All of these will probably not happen for 8.1 but I hope to finish A and B. It all depends on how much trouble I run into and how much time I can put into it. The function overload parts in pg are far from trivial, but I will not worry about that until I get that far. Any comments about this plan? -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Charset/collate support and function parameters
On Sat, 30 Oct 2004, Tom Lane wrote: Why would we not keep this information right in the string values? We could, but then we would need to parse it every time. Storing it in a structured way seems like the database solution and at least as a user from the client side it makes sense. Are you worried about performance or is it the smaller change that you want? I can't tell how much, if any, is gained in speed by having an array of strings instead of another system table. -- /Dennis Björklund ---(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] Charset/collate support and function parameters
On Sat, 30 Oct 2004, Tom Lane wrote:
Are you worried about performance or is it the smaller change that you
want?
I'm worried about the fact that instead of, say, one length(text)
function, we would now have to have a different one for every
characterset/collation.
This is not about how the parameter information is stored, but let's
discuss that anyway. It's important issues.
I was hoping that we could implement functions where one didn't have to
specify the charset and collation (but could if we want to).
For some functions one really want different ones depending on the
charset. For example the length function, then we will need to calculate
the length differently for each charset. We can never have one length
function that works for every possible charset. We could have one pg
function that do N different things inside depending on the charset, but
that's not really a simplification.
For functions where one have not specified the charset of an argument then
we need to be able to pass on that type information to where ever we use
that argument. Variables already have a type and if we have a (pseudo
code) function like
foo (a varchar) returns int
{
select length(a);
}
and call it with
foo ('foo' charset latin1)
then we need to make sure that variable a inside the function body of foo
get the type from the caller and then the function call to length(a) will
work out since it would select the length function for latin1. I think it
should work but an implementation is the only way to know.
Every string do in the end need to know what charset and what collation it
is in. Otherwise it can not be used for anything, not even to compare it
with another string.
I could even imagine to have different functions for each
charset/collation. It's not that many functions built in that are affected
and not all of them need to work with every collation. The user just need
to call them with the correct one. I don't expect any functions like
foo (a varchar collation sv_SE,
b varchar collation en_US)
or any other combination of a and b. If any then a and be will be the same
type. So there would not be arbitrary many combinations (but still a lot).
The alternative is storing the charset and collation inside each string.
That seems like a too big price to pay, it belong in the type.
Not to mention one for every possible N in varchar(N).
This doesn't matter since one can always implement functions to take
varchar arguments without any limit and then any shorter string can be
implictly casted up to that type. Or one can treat the length exactly like
the charset above.
Of course you do not want one length function for each length.
--
/Dennis Björklund
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Charset/collate support and function parameters
On Sat, 30 Oct 2004, Tom Lane wrote: The alternative is storing the charset and collation inside each string. That seems like a too big price to pay, it belong in the type. No, the alternative you're proposing is too big a price to pay. So you want to expand every string with 8 bytes (two oid's)? Or special case the storing in tables so that you only store the text and not the charset/collation info? -- /Dennis Björklund ---(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] Charset/collate support and function parameters
On Sun, 31 Oct 2004, Tatsuo Ishii wrote: So you want to expand every string with 8 bytes (two oid's)? For me that seems to be the right way. I'm not sure if two oids are the right solution but we need to store extra info in varlena structure to support charset/collation anyway. In my understanding TOAST has already done in similar way. Other than charset/collation we also need coercibility info to meet with the SQL standard. This could only be represented in each text, not by function parameters. Arn't we limiting ourself in how we can use charsets when we remove it from the type. The reason why I started to look at the function parameters is because in the standard one can do this: CREATE CHARACTER SET foo GET Latin1; and now we could add functions that work with this charset CREATE FUNCTION bar (x VARCHAR(255) CHARACTER SET foo) What we are saying is that we don't want to be able to do this? I just want to understand all the implications of simplifying the types. Same thing if the user wants to create new collations using CREATE COLLATION. How can we override functions for these new charsets and collations if all we can define are functions like foo(x VARCHAR)? Maybe one wants the information in both place. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] making pdf of docs
Is there something wrong that makes it impossible to build the doc as a pdf? I started a build 4 hours ago, and it has still not finished (stuck at 100% CPU on my old 800Mhz 1G RAM machine). I know that openjade is very slow so for the first 3 hours I didn't worry. Now I'm starting to think that it will never finish. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: Hmmm ... well, does the spec specifically prohibit DST, or just leave it out? It doesn't discuss it. According to the spec a timestamp with time zone is a UTC value + a HH:MM offset from GMT. And intervals in the spec is either a year-month value or a day-time value. One can only compare year-month values with each other and day-time values with each other. So they avoid the problem of the how many days is a month by not allowing it. The spec is not a full solution, it's also not a useless solution. I'm happy as long as the spec is a subset of what pg implements. If not then I would like to be able to have both but with different names or something similar (but I think that should not be needed). -- /Dennis Björklund ---(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] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: Dennis, It doesn't discuss it. According to the spec a timestamp with time zone is a UTC value + a HH:MM offset from GMT. And intervals in the spec is either a year-month value or a day-time value. One can only compare year-month values with each other and day-time values with each other. So they avoid the problem of the how many days is a month by not allowing it. That's not what Tom and I were talking about. You wanted to know what the standard said, and I told what I knew. The issue is that the spec defines Days/Weeks as being an agglomeration of hours and not an atomic entity like Months/Years are. I don't know what you mean with this. The standard does treat them as year month day hour minute second (with fractions) There is no weeks there, if that is what you mean. This leads to some wierd and calendar-breaking behavior when combined with DST, for example: template1= select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL template1- ; ?column? 2004-11-23 09:00:00-08 (1 row) Because of the DST shift, you get an hour shift which is most decidely not anything real human beings would expect from a calendar. I don't see how the above can be caused by the representation of an interval. The above timestamp is 2004-10-09 10:00 PDT which in the standard would be 2004-10-09 10:00 -07 and after the additon would be 2004-11-23 10:00:00-07 Here the time zone is wrong since the standard does not know about named zones and dst. An implementation like the one Tom (and I) want would start with 2004-10-09 10:00 PDT and then after the addition one would get 2004-11-23 10:00:00 PST At least that's my understanding of what we want and what we can get (plus that we also need to support HH:MM tz values since those also exist in the world, check this emails header for example). It's possible that you discuss something else, but that has been lost on me so far. -- /Dennis Björklund ---(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] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: Hour/Minute/Second/ms Day/Week Month/Year And just when I pressed send on the previous mail I got the problem :-) -- /Dennis Björklund ---(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] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: Hour/Minute/Second/ms Day/Week Month/Year This is embarrasing. I'm still a bit confused :-) The standard treat days as a separate entry, it does not assume that a day is 24 hours. It restricts the hour field to the interval 0-23 so one can never have something like 25 hours. So it does not need to worry about how many days that translate to. And why do we need weeks also? Well, this is the last mail I send before I've been thinking about this for a while more :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Tom Lane wrote: There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that in- cludes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an ex- press or implied interval precision that can include any fields other than YEAR or MONTH. AFAICS the reason for this rule is that they expect all Y/M intervals to be comparable (which they are) and they also expect all D/H/M/S intervals to be comparable, which you can only do by assuming that 1 D == 24 H. I said I was not going to send any more mails, but here we go again :-) The standard restrict the hour field to the interval 0-23, so there can never be any compare between for example '1 day 1 hour' and '25 hours'. This means that one can not add two intervals together to get a bigger one but that it would still work to do timestamp+interval+interval. It seems to me though that we can store days separately and do interval comparisons with the assumption 1 D == 24 H, and be perfectly SQL-compatible as far as that goes, and still make good use of the separate day info when adding to a timestamptz that has a DST-aware timezone. In a non-DST-aware timezone the addition will act the same as if we weren't distinguishing days from h/m/s. Therefore, an application using only the spec-defined features (ie, only fixed-numeric-offset timezones) will see no deviation from the spec behavior. I agree with this. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: The standard restrict the hour field to the interval 0-23, so there can never be any compare between for example '1 day 1 hour' and '25 hours'. This means that one can not add two intervals together to get a bigger one but that it would still work to do timestamp+interval+interval. Hour field of the timestamp, or hour field of interval? There a world of difference. Hour field of an interval can be 0-23 according to the spec (doesn't say that we need that restriction, but we do need to understand what the spec say). -- /Dennis Björklund ---(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] Proposed TODO: CREATE .... WITH OWNER;
On Sat, 23 Oct 2004, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: Dennis and I are hashing this out on IRC. The second option would be to simply put SET SESSION AUTHORIZATION statements before each and every statement in the pg_dump. This would make each statement atomic as far as user ownership is concerned, with less changes than WITH OWNER would entail. Uh, isn't that how we did it before? Why is that better? I havn't looked at what pg_dump do in the code. Josh showed some code generated by pg_dump that contains SET SESSSION ... and then some statement and a RESET SESSION AUTHORIZATION. When I saw that I simply asked; why do it issue the RESET at all? Wouldn't it be enough to just set the user whenever needed? Especially since Josh said that pg_dump got the resets wrong. In the extreme one could set the user before every statement but a better way is that pg_dump keeps track of who is the current user and then just issue a SET SESSION AUTH when needed. This sounds like what I though pg_dump were doing already, but probably wasn't since it got it wrong and Josh had a database where the owners after restore was messed up. Another observation is that SET SESSION AUTHORIZATION postgres; and RESET SESSION AUTHORIZATION; would be the same when postgres is the superuser. By not using the name of the superuser one get the benefit that one can restore as another superuser (but see the part about acl's below). Well, hopefully this is not a problem in 8.0 as you say. When discussing this, _another issue_ came up that made me thinking. Let me ask about that: When you alter the owner of an table with ALTER TABLE ... OWNER TO ... then it looks like it just sets the owner but does not alter the acl string at all (at least in 7.4 where I tested). So after one have altered the owner it's possible that the new owner does not have any rights set for the object. and (worse) that the old owner still have rights set. It's also more complicated since in some cases the acl is set to NULL which means that it has the default priviledges. And the default privileges always include all privileges for the owner. So if the acl is NULL then the old owner looses its privileges and the new gets them. Here we have a different semantics based on an implementation detail that's not very visible to the user. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Daylight saving time
I found a nice page about daylight saving time that I want to share: http://timeanddate.com/time/aboutdst.html Here are some fun quotes from the page: Sometimes DST is used for longer periods than just one summer, as in the United States during World War II. From 3 Feb 1942 to 30 Sep 1945 most of United States had DST all year, it was called War Time. many countries change the transition days/principles every year because of special happenings or conditions that has happened or will happen. Also notice the current list of DST changes for this fall 2004: http://timeanddate.com/time/dst2004b.html I can understand why they did not try to formalize that in the sql spec. ps. This letter does not mean that I think it's bad to handle time zone names, just that it's even more difficult then I first thought. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1290: Default value and ALTER...TYPE
On Sun, 24 Oct 2004, Neil Conway wrote: (1) Accept the default's raw parsetree from the parser (2) Convert it to a cooked parsetree via transformExpr() (3) Add a coercion to the table's column type Can't we save the cooked parsetree that we produced in #2? One could even save the string as it was before parsning if one wants to (could make it easier to edit in a graphical client like pgadmin3). -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] BUG #1290: Default value and ALTER...TYPE
On Sun, 24 Oct 2004, Tom Lane wrote: (1) Accept the default's raw parsetree from the parser (2) Convert it to a cooked parsetree via transformExpr() (3) Add a coercion to the table's column type Can't we save the cooked parsetree that we produced in #2? Not without an initdb (to have another column to put it in). And it would produce exactly the same result anyway, because the only way there could be implicit coercion steps at the top of the expression is because step 3 put them there. Yes, and he suggested to not perform step 3. Instead one need to do that when the default value is used. -- /Dennis Björklund ---(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] timestamp with time zone a la sql99
On Fri, 22 Oct 2004, Tom Lane wrote: behavior. The spec says you can put a numeric-GMT-offset zone in and get a numeric-GMT-offset zone out. We can do that and also support named, possibly DST-aware zones. So if I understand you correctly you are planning to extend the current timestamp type to work with both named time zones and HH:MM ones? I didn't think you wanted the last one since your plan was to store a UTC+OID where the OID pointed to a named time zone. And I guess that you don't plan to add 00:00, 00:01, 00:02, ... as named zones with an OID. -- /Dennis Björklund ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] cvs problem
I'm having problems with cvs: cvs diff: failed to create lock directory for `/cvsroot/pgsql-server' (/cvsroot/pgsql-server/#cvs.lock): No such file or directory cvs diff: failed to obtain dir lock in repository `/cvsroot/pgsql-server' cvs [diff aborted]: read lock failed - giving up -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] cvs problem
On Fri, 22 Oct 2004, Heikki Linnakangas wrote: The repository path was changed from /cvsroot/pgsql-server to /cvsroot/pgsql some time ago. You'll have to re-checkout or fix the CVS/Repository file in each subdirectory in your checked-out copy. Oh. Thanks! I should have tried, but in the past it have always been errors on the server... :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] SET SESSION SESSION AUTHORIZATION
Is it just me or is this syntax very ugly? SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT so the parser accepts SET SESSION SESSION AUTHORIZATION DEFAULT; I know the SESSION/LOCAL part should be the same as the other SET commands, but still. It hurt my eyes... The standard command is SET SESSION AUTHORIZATION and affects the session. Couldn't we then have this syntax instead SET [ SESSION | LOCAL ] AUTHORIZATION username SET [ SESSION | LOCAL ] AUTHORIZATION DEFAULT so one can do either SET SESSION AUTHORIZATION DEFAULT; or SET LOCAL AUTHORIZATION DEFAULT; -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] timestamp with time zone a la sql99
On Fri, 22 Oct 2004, Tom Lane wrote: As far as I can tell, Dennis is planning slavish adherence to the spec, which will mean that the datatype is unable to cope effectively with daylight-savings issues. So I'm unconvinced that it will be very helpful to you for remembering local time in addition to true (universal) time. And exactly what issues is it that you see? The only thing I can think of is if you have a timestamp and then add an interval to it so we jump past the daylight saving time change date. Then the new timestamp will keep the old timezone data of say +01 even though we now have jumped into the daylight saving period of +02. If you are just storing actual timestamps then the standard definition works just fine. If I store '2004-10-22 16:20:04 +02' then that's exactly what I get back. No problem what so ever. There is no DST problem with that. It's possible that I will introduce some daylight saving bit or something like that, I'm not sure yet and I will not commit to anything until I've thought it over. I don't think there are that much of a problem as you claim however. Could you give a concret example where it will be a problem? My current thinking is that storing the time zone value as HH:MM is just fine and you avoid all the problems with political changes of when the DST is in effect or not. -- /Dennis Björklund ---(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] SET SESSION SESSION AUTHORIZATION
On Fri, 22 Oct 2004, Tom Lane wrote: backwards incompatibility for. Two major releases ago, we could have considered it... Of course you shouldn't break backward compability over it. I thought it was new stuff in 8.0 hence my comment. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] timestamp with time zone a la sql99
On Fri, 22 Oct 2004, Tom Lane wrote: At bottom, what I want to be able to do is say '2004-10-22 10:50:16.916003 America/New_York' Yes, that's what we said in the last mail and I think there is a value in having something like this. universal time and not the timezone spec. Why should I be satisfied if it stores only the GMT offset and not the knowledge of which timezone this really is? You don't need to be satisfied with it. I think a type like the above would be fine to have. It should however not be called TIMESTAMP WITH TIME ZONE because there is already a definition of that type. We can not hijack standard types. I would not mind a type like TIMESTAMP WITH TIME ZONE NAME (or some other name). I could even imagine that I could implement something like that one day. My current thinking is that storing the time zone value as HH:MM is just fine and you avoid all the problems with political changes of when the DST is in effect or not. This is fundamentally misguided. Time zones *are* political whether you like it or not, and people *do* expect DST-awareness whether you like it or not. And I never said that time zones are not political, just that HH:MM is a usable approximation that works fairly well. But storing a fixed GMT offset is going to be a step backwards compared to existing functionality. It's not a step backwards since you can do everything you can do with the current type plus a little bit more. It's however not a step to the datatype discussed above. One way to do this would be to create a system catalog with entries for all known timezones, and then represent timestamptz values as universal time plus an OID from that catalog. There are other ways that small integer codes could be mapped to timezones of course. This is just fine. You try to make it sound like I am against such a datatype, I am not. It's however not the datatype that we can expect applications and other databases to use. So why should we settle for only that type. Just because you can make a perfect datatype it doesn't mean that the standard datatype should just be ignored. What would you store when the user supplies a timestamp like '2004-10-22 17:21:00 +0200'. Should you reject that because you don't know the time zone name? So your datatype will not work for applications that try to be compatable with many databases by using the standard? Maybe one could make a datatype called TIMESTAMP WITH TIME ZONE that can accept both HH:MM and TimeZoneName. Whenever you store values with HH:MM time zones you will get the same problem when you add an interval as the standard type has. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] timestamp with time zone a la sql99
On Fri, 22 Oct 2004, Tom Lane wrote: than having two different types (the idea of a GUC variable to choose which one is selected by a given type name is just horrid). That is needed no matter what change you do if you want old programs that use the current timestamp with time zone to work. Today you don't get back the same time zone as you insert, programs might depend on that. We are not fully there yet (can't do AT TIME ZONE conversions with all zones yet, for instance) Why is that? When one start with a utc value, performing a AT TIME ZONE operation doesn't look so complicated. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] timestamp with time zone a la sql99
I've made a partial implementation of a datatype timestamp with time
zone as described in the sql standard. The current type timestamptz
does not store the time zone as a standard one should do. So I've made a
new type I've called timestampstdtz that does store the time zone as the
standard demands.
Let me show a bit of what currently works in my implementation:
dennis=# CREATE TABLE foo (
a timestampstdtz,
primary key (a)
);
dennis=# INSERT INTO foo VALUES ('1993-02-04 13:00 UTC');
dennis=# INSERT INTO foo VALUES ('1999-06-01 14:00 CET');
dennis=# INSERT INTO foo VALUES ('2003-08-21 15:00 PST');
dennis=# SELECT a FROM foo;
a
1993-02-04 13:00:00+00
1999-06-01 14:00:00+01
2003-08-21 15:00:00-08
dennis=# SELECT a AT TIME ZONE 'CET' FROM foo;
timezone
1993-02-04 14:00:00+01
1999-06-01 14:00:00+01
2003-08-22 00:00:00+01
My plan is to make a GUC variable so that one can tell PG that constructs
like timestamp with time zone will map to timestampstdtz instead of
timestamptz (some old databases might need the old so unless we want to
break old code this is the easiest solution I can find).
I've made an implicit cast from timestampstdtz to timestamptz that just
forgets about the time zone. In the other direction I've made an
assignment cast that make a timestamp with time zone 0 (that's what a
timestamptz is anyway). Would it be possible to make it implicit in both
directions? I currently don't think that you want that, but is it
possible?
With the implicit cast in place I assume it would be safe to change
functions like now() to return a timestampstdtz? I've not tried yet but I
will. As far as I can tell the cast would make old code that use now() to
still work as before.
Any comments before I invest more time into this subject?
--
/Dennis Björklund
---(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] timestamp with time zone a la sql99
On Thu, 21 Oct 2004, Tom Lane wrote: I've made a partial implementation of a datatype timestamp with time zone as described in the sql standard. The current type timestamptz does not store the time zone as a standard one should do. I'm aware that there are aspects of the spec behavior that appear to require that, but is it really an improvement over the implementation we have? Improvement and improvement. The actual time value is of course the same (the utc part of a timestamp) and the only thing extra you get is that the time zone is stored. The extra information you do have now, when stored in this way, is that you store both a utc time and a local time. Will any application ever need that? Who knows? I think it makes sense and is an easier model to think about then what pg uses today. So I would use it even if it means using 2 bytes more storage then what timestamptz do Just that it is standard also makes it useful. The more things of the standard we support the easier it is to move between databases. This is important to me. I also want to make a general statement that I think that whenever we use standard syntax we should give it a standard semantics. I don't mind extensions at all, but as much as we can we should make sure that they don't clash with standard syntax and semantics. This is an area in which the standard is pretty brain-dead --- the entire concept of a time with time zone datatype is rather suspect, for instance. I havn't look that much at time with time zone yet, just timestamps. I can't see why time with time zone should not also be supported. I can't really imagine it being used without a date, but if someone wants to store timestamps as a date+time with time zone, then why not. It would be extra work tu is it instead of a timestamp (especially for cases where the time wraps over to the prev/next day), but hey. In particular, I wonder how you will handle daylight-savings issues. The spec definition seems to preclude doing anything intelligent with DST, as they equate a timezone with a fixed offset from UTC. That's not how it works in (large parts of) the real world. The tz in the standard is a offset from utc, yes. So when you store a value you tell it what offset you use. If you are using daylight-savings time it might be +02 and if not dst it might be +01. What else would you want to do with it? It's not like you can do anything else with it in pg as of today, can you? The stored tz does not say what region of the globe you are in, it says the distance away from utc in minutes that you are. I could imagine another datatype that stores the time zone as name, but that's not what timestamp with time zone does. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #1290: Default value and ALTER...TYPE
On Thu, 21 Oct 2004, Tom Lane wrote: Would it be possible to check the compatibility of a default value for the associated column? I think that would introduce as many problems as it would fix. AFAICS the only way to make such a check is to evaluate the expression and see what happens. The problem in the reported case was that the default value was a string and pg infered that the default value should be a varchar(2) because that was the column type. The problem I assume is only when the type is unknown for pg, for values with a known type I hope that is the type that is used. The typecast to the column type should be added on when we use the default value and not before. Maybe one could come up with some method to give the default value the most general type. For a string we could give it the type text, for a number we could give it say the smallest int type that fits it (when it is an integer). If one have a default value like '2' it would get the type text but that would still work even if the column is a int column, wouldn't it? I assume here that we must give the default value a real type. Values of type unknown are hard to handle. -- /Dennis Björklund ---(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] Should libpq set close-on-exec flag on its socket?
On Thu, 21 Oct 2004, Tom Lane wrote: It was suggested to me off-list that libpq should do fcntl(fd, F_SETFD, FD_CLOEXEC) on the socket connecting to the server. This would prevent any child program from accidentally or maliciously interfering with the connection. Either way that the lib sets it, the client can alter the setting itself by issuing a new SETFD command. I would not have expected it to be set but it is probably a good idea for most clients (and for most file descriptors). -- /Dennis Björklund ---(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] Get rid of Money
On Sun, 26 Sep 2004, Tom Lane wrote: Wouldn't pg 8.0 be a good release to get rid of the type Money? (1) there is not actually consensus to get rid of it. The doc seems to be clear about it. What does it provide that numeric doesn't? The only extra thing I know is a currency symbol that is broken in a lot of locales. Also, people (you?) have been talking about money being buggy, having round off problems. I don't know any details about that. (2) we are already long past the point where we will force initdb for noncritical problems. Clear for 8.0. -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Use of zlib
On Sat, 25 Sep 2004, Bruce Momjian wrote: I am confused. I just checked my backend binary and I don't see any unusual libs required: $ ldd postgres libz.so = /usr/lib/libz.so (0x2833f000) libncurses.so.5 = /shlib/libncurses.so.5 (0x2834e000) libdl.so = /shlib/libdl.so (0x2838c000) libm.so = /shlib/libm.so.0.0 (0x2838f000) libgcc.so.1 = /shlib/libgcc.so.1 (0x283a) libc.so.2 = /shlib/libc.so.2 (0x283ac000) The libncurses is a little unusual but I don't see libreadline in there even though it is on the link line. Are some OS's adding libs that aren't required by the binary? This is the output in Fedora Core 2, and it includes readline: $ ldd /usr/bin/postgres linux-gate.so.1 = (0x0024) libpam.so.0 = /lib/libpam.so.0 (0x00cf1000) libssl.so.4 = /lib/libssl.so.4 (0x0014e000) libcrypto.so.4 = /lib/libcrypto.so.4 (0x00241000) libkrb5.so.3 = /usr/lib/libkrb5.so.3 (0x0052a000) libcom_err.so.2 = /lib/libcom_err.so.2 (0x00e56000) libz.so.1 = /usr/lib/libz.so.1 (0x00aca000) libreadline.so.4 = /usr/lib/libreadline.so.4 (0x00111000) libtermcap.so.2 = /lib/libtermcap.so.2 (0x00f25000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x00406000) libresolv.so.2 = /lib/libresolv.so.2 (0x00f8a000) libnsl.so.1 = /lib/libnsl.so.1 (0x00215000) libdl.so.2 = /lib/libdl.so.2 (0x00908000) libm.so.6 = /lib/tls/libm.so.6 (0x008a5000) libc.so.6 = /lib/tls/libc.so.6 (0x00592000) libgssapi_krb5.so.2 = /usr/lib/libgssapi_krb5.so.2 (0x00a57000) libk5crypto.so.3 = /usr/lib/libk5crypto.so.3 (0x00ccd000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x004ca000) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Get rid of Money
Wouldn't pg 8.0 be a good release to get rid of the type Money? Maybe there is also other deprecated things that are suitable to get rid of in a .0 release. -- /Dennis Björklund ---(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] How to add locale support for each column?
On 19 Sep 2004, Greg Stark wrote: don't think that's an argument for postgres to reimplement portions of the OS. If the OS locale handling is slow on some OS's then postgres should just warn its users that using locales on those OS's will be slow. In any case I suspect more than just the glibc implementation cache locales in memory at this point. It seems like just too obvious a feature and more and more applications require locale switching to be fast anyways. Still, we want the final solution to be what the sql standard specify. A function as the proposed is however useful until a standard sql solution is implemented. I've used a similar function for some projects and it have worked well for me also. I think such a function fits perfect in contrib. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How to add locale support for each column?
On 19 Sep 2004, Greg Stark wrote: I've seen people describe here I think the standard behaviour will be nigh useless anyway. From what I understand the standard has you declare a locale per column. Yes, you can define a default collation for a column if you want to but more important you can set the charset and encoding for a column. That would mean the entire column can only store strings from a single locale. You store strings in a charset/encoding. The same charset can use different collations when you compare strings later on. So in my application I would need to declare a new set of columns in every table every time I localize it for a new language. Not at all, you can just tell it to use another collation in your query. The collation for the column is just the default. In your query you can do something like SELECT ... ORDER BY foo COLLATE sv_SE I'm not 100% sure of the syntax of the language identifier. but something like that. I actually would prefer an interface like he describes. And I think it's enlightening that everyone that tries their hand at this seems to come up with an interface much like this. You say you did for example, and I did also when I needed a strxfrm interface. This is just because functions is the way you extend postgresql as a user. It's much easier then to add language constructs. The standard is clear (as clear as an sql standard ever is :-) and most of the other databases implements it. I dont think we should make up our own system and have that as the implementation goal. Until we have the standard solution, a simple function like the discussed is useful and putting it in contrib is what I prefer. I would use it. -- /Dennis Björklund ---(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] subtransaction assert failure
On Thu, 16 Sep 2004, Gavin Sherry wrote: Interestingly, I *cannot* recreate on the single CPU system and I cannot I've benn trying to reproduce it on a single cpu system with fedora 2, but no luck. If someone wants else to try, don't forget the configure flag --enable-cassert. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] subtransaction assert failure
On Thu, 16 Sep 2004, Dennis Bjorklund wrote: Interestingly, I *cannot* recreate on the single CPU system and I cannot I've been trying to reproduce it on a single cpu system with fedora 2, but no luck. I spook too soon. After having run it even more times I finally got a failure as well. -- /Dennis Björklund ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Is select a transaction starting statement?
Is select a transaction starting statement according to the the sql specification? In the specification (sql99) there is a list of things that are and a list of things that are not, but I can't figure out which list a select query belongs to. Isn't that great :-) In postgresql it is implemented as one. A guy on irc informed me that in oracle only SELECT ... FOR UPDATE is a transaction starting command. Now I'm trying to figure out which is the correct way. I'm afraid that no one knows this and I will have to spend a full day reading the spec, just to stop my curiosity. Please help me save a day! -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Is select a transaction starting statement?
On Tue, 14 Sep 2004, Alvaro Herrera wrote: -- The following SQL-data statements: [...] direct select statement: multiple rows. [...] The direct select statement: multiple rows is in time defined as cursor specification, which in turn is a query expression, which is our SELECT statement. A lot of jumps in the grammar, but it's there. Nice. In sql99 there is only direct select statement: single row which is SELECT .. INTO .. But maybe some of the other includes queries after 4-5 jumps or so. Or maybe they simply forgot that one. Just my luck to be reading sql99. Thanks. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Is select a transaction starting statement?
On Tue, 14 Sep 2004, Peter Eisentraut wrote: It's the same in SQL 99. I think you missed that most of the direct SQL is specified in part 5 instead of part 2. In SQL 2003 they have merged these parts. The relevant section for you in SQL 99 is 4.6.3 in part 5. Good, that explains it all. It never occured to me that this could be in part 5 Host Languge Bindings. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] translations
I've been going over the translation again and found 2 places that was broken. In libpq there was some files that was not scanned for translated strings and in scan.l there was a call to gettext() missing which made error messages into a mix of english and swedish (in my case). Normally I just commit the swedish translations, but now I commited these source fixes as well so we get fully translated error messages like: dennis=# SEL; FEL: syntaxfel vid eller nära SEL vid tecken 1 RAD 1: SEL; instead of the previous dennis=# SEL; FEL: syntax error vid eller nära SEL at character 1 RAD 1: SEL; which just looks stupid (in both english and swedish). If there are complains I can revert it and send it to -patches. Overall the translation seems to work fairly well. I have still not got a total translation, but it's getting there. Maybe for 8.1. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] translations
On Thu, 9 Sep 2004, Alvaro Herrera wrote: In libpq there was some files that was not scanned for translated strings and in scan.l there was a call to gettext() missing which made error messages into a mix of english and swedish (in my case). I see this problem too. I was about to complain. Not sure if this is the best fix, but it certainly 'needs fixed'. Since the parser calls yyerror() with strings we can't do the gettext call beforehand, which only leaves it to be done inside the yyerror() function. xgettext sees yyerror as a markup functions, so the strings generated by the parser are all in the po file (but was not used since there was no call to gettext). -- /Dennis Björklund ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
