Re: [HACKERS] union vs. sort
On Wed, Apr 07, 2004 at 02:20:55PM -0400, Tom Lane wrote: I've committed changes to do the right thing in CVS tip. Thanks man! Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] locale
On Wed, Apr 07, 2004 at 03:40:57PM -0400, Tom Lane wrote: In practice, we know that we have seen index failures from altering the locale settings (back before we installed the code that locks down LC_COLLATE/LC_CTYPE at initdb time). I do not recall having heard any Cannot the same failure happen if one upgrades their glibc / locales and the new version implements the locale differently? Perhaps fixing previous bug, or simply producing different results for strcoll / strxfrm? If PostgreSQL depends on external locale information for something as important as indexes, shouldn't it make elementary checks (upon startup, perhaps) that the current locale settings and the current locale version produces results compatible with the existing indexes? And if it does not, reindex? -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] locale
On Thu, 8 Apr 2004, Tatsuo Ishii wrote: The tables in template1 in encoding E1 are compied into the new database in encoding E2. Not all encodings are compatable, so you can't even convert from E1 to E2. In this case you just set your terminal encoding to E1, then SELECT the table. Point is you do not use set client_encoding or \encoding command. This will work as long as both E1 and E2 are single byte encodings. That is not a solution. As you said, it does not even work for all encodings. If the database is in Latin1 I'd expect that the strings in the table are just latin1 and not something else. And for some multibyte encodings that something else might not just be the wrong characters but an invalid string (think utf-8). I can also imagine the indexes being wrong when you keep the encoding of tables when you create a new database. Since the same character can be represented differently, the sort order also changes if you try to interpret something with another encoding then what the compare operator think it is. That makes the index invalid. It's simply broken if you ask me. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] locale
Are you talking about the sort order? Then there's no problem with encoding itself. The tables in template1 in encoding E1 are compied into the new database in encoding E2. Not all encodings are compatable, so you can't even convert from E1 to E2. In this case you just set your terminal encoding to E1, then SELECT the table. Point is you do not use set client_encoding or \encoding command. This will work as long as both E1 and E2 are single byte encodings. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] idle in transaction with JDBC interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm facing a problem with the unfamous: idle in transaction problem. I'm using the JDBC driver. Mainly the problem is that the JDBC interface doesn't provide the method begin() for a transaction, of course this is not a JDBC postgres interface problem. Let me explain what happen using the JDBC interface Client Side|Server Side - --- 1) Open a connection|Connection accepted ~| - Connection Idle 2) set autocommit false |begin; ~| - Idle in transaction 3) select now();|select now(); ~| - Idle in transaction 4) commit; |commit; begin; ~| - Idle in transaction 5) select now();|select now(); ~| - Idle in transaction 6) rollback;|rollback; begin; ~| - Idle in transaction as you can easily understand there is no window time larger enough with a connection idle, I thin that the JDBC behaviour ( with the server I mean ) is not really correct. This is what I think it's better: Client Side|Server Side - --- 1) Open a connection|Connection accepted ~| - Connection Idle 2) set autocommit false | ~| - Connection Idle 3) select now();|begin; select now(); ~| - Idle in transaction 4) commit; |commit; ~| - Connection Idle 5) select now();|begin; select now(); ~| - Idle in transaction 6) select now();|select now(); ~| - Idle in transaction 7) rollback;|rollback; ~| - Connection Idle AS you can see the JDBC driver must do a begin only before the first statement. Am I missing something ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAdTxl7UpzwH2SGd4RAkPOAJwNgUsfkMpd9m5R4que7PxuFnrZvgCePbI9 hdCLD4fAI6vRnr224e9r0lk= =gEQe -END PGP SIGNATURE- ---(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] PostgreSQL configuration
About a year or two ago I submitted a configuration patch that allowed PostgreSQL to be fully configured by postgresql.conf -- enabling data and configuration to be in separate locations. The idea was that, like most UNIX systems, that the configuration file could be stored in the /etc directory (or /etc/postgres or /usr/etc or whatever) and it could contain all the various system directory and file locations, like pg_hba, and so on. There was a lot of debate about it, and I don't recall many arguments against this sort of configuration strategy, only that there was a dislike of my patch because it wasn't an all encompassing re-write of the configuration system. I have been maintaining it for the various versions of PostgreSQL since that time for my own use, can we re-open this debate? It has been a good deal of time with no progress, and I don't think anyone can deny that a more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL configuration
On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote: more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. Why is it important and wouldn't it just make it harder to have several database clusters (for example with different locale) or several versions of pg installed at the same time? I guess I should search the archive for the old discussion. If someone have a link please post :-) -- /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] PostgreSQL configuration
On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote: more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. Why is it important and wouldn't it just make it harder to have several database clusters (for example with different locale) or several versions of pg installed at the same time? My patch did not remove any functionality, it merely augmented it. To say that it would make it more difficult to deploy multiple databases is misleading for (2) reasons. (1) It need not do that, because the configuration system would seem unchanged for those who do not wish to use it in this way. (2) I would bet that *most* deployments of PostgreSQL only use one database environment per server, so I'm not even sure that it would be an issue for the majority of current or prospective users. It is all well and good to say our way is better, (with which I do not agree) but there are, more or less, if not standards, standard concepts from which good software design follows. Besides PostgreSQL, name one popular open source project that is widely used that stores its configuration information inside its data repository. From the new user perspective, configuration within the data directory is an alien concept. From a sysadmin perspective, having configuration in a standard location makes sense. It makes these things easy to backup, archive, and put under version control. (Many sysadmins put machine configuration under version control to see what changes are made over time.) Finally, I'm not suggesting removing any functionality, I am suggesting that configuration can and should be able to be located in a standard location and the the configuration be able to point to the data volume. How many systems have you been asked to inspect for problems? It is one of the things I do for a living. On many systems, I can just look in the '/etc' directory for most of what I need. If they are running PostgreSQL, I have to look around and figure out where the database is located. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] locale
Dennis Bjorklund [EMAIL PROTECTED] writes: I can also imagine the indexes being wrong when you keep the encoding of tables when you create a new database. Since the same character can be represented differently, the sort order also changes if you try to interpret something with another encoding then what the compare operator think it is. That makes the index invalid. See my previous point: the index does not actually fail, in our current implementation, because strcoll() is unaffected by the database's encoding setting. You'd be likely to have trouble with I/O translation and with other encoding-dependent operations like upper()/lower() ... but not with indexes. It's simply broken if you ask me. It's certainly ungood, but I don't think we can materially improve things without a fundamental rewrite along the lines of Peter's proposal to support per-column locale/encoding. Database-level settings are simply the wrong tool for this. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] locale
On Thu, 8 Apr 2004, Tom Lane wrote: See my previous point: the index does not actually fail, in our current implementation, because strcoll() is unaffected by the database's encoding setting. How can it be? If I have a utf-8 template1 and a table with an index sorted according to the utf-8 characters in some locale. Then this table and index is copied into a Latin1 database. When I interpret these bytes as Latin1 in the index, the ordering does not have to be the same as it was before and the index can not be used. I don't understand what you mean when you say that strcoll() is unaffected by the database's encoding setting. It interprets characters, how can it not be? If it works it must be something more going on that I don't know/understand yet. If I am I would be happy to be corrected, if not we have a more broken system then we expected before. The objection to a per database locale is that we can not copy a table from the template into the database since the index would not be valid anymore. To me that is solvable by just reindexing. The current problem with encodings does not look solvable at all to me (except to not copy tables when we can not reencode the strings). -- /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] PostgreSQL configuration
Dennis Bjorklund [EMAIL PROTECTED] writes: On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote: more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. Why is it important and wouldn't it just make it harder to have several database clusters (for example with different locale) or several versions of pg installed at the same time? My recollection of the arguments against were first that and second reliability --- there was concern about getting config and data of multiple installations mixed up if they weren't kept together. In the worst case you could conceivably bollix an installation unrecoverably that way. (Right now I do not think there is anything quite that critical in postgresql.conf, but someday there might be. My very vague recollection is that the proposed patch changed things so that WAL and DATA directories would be separately specified in the config file; if correct, mismatching them definitely would be a great chance to shoot oneself in the foot.) I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] locale
Dennis Bjorklund [EMAIL PROTECTED] writes: On Thu, 8 Apr 2004, Tom Lane wrote: See my previous point: the index does not actually fail, in our current implementation, because strcoll() is unaffected by the database's encoding setting. How can it be? If I have a utf-8 template1 and a table with an index sorted according to the utf-8 characters in some locale. Then this table and index is copied into a Latin1 database. When I interpret these bytes as Latin1 in the index, the ordering does not have to be the same as it was before and the index can not be used. No, the ordering *will* be the same as it was before, because strcoll() is still functioning the same. You'd get the same answer from a sort operation since it depends on the same operators. Now, you will probably complain that the sort order doesn't appear correct according to your Latin1 interpretation --- and you're right. But the index is not corrupt, it is still consistent in its own terms. I don't understand what you mean when you say that strcoll() is unaffected by the database's encoding setting. It interprets characters, how can it not be? It interprets them according to LC_CTYPE, which does not change. regards, tom lane ---(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] make == as = ?
Fabien COELHO wrote: This would help me, at least, write correct and portable SQL. :) Added to TODO: * Add a session mode to warn about non-standard SQL usage So it seems that having C-like operators would hurt a lot;-) So you want to generate warnings for SERIAL, TEXT and a bunch of other types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of pg_catalog instead of information_schema (I may be wrong in the list, I don't have the standard at hand, but I think I'm right in the spirit)... This is going to be noisy;-) Yep, it sure is going to be noisy. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL configuration
On Thu, Apr 08, 2004 at 10:31:44AM -0400, Tom Lane wrote: I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. A counterexample of Apache shows that you can easily use -f or another command line option to point the server to alternate master config file (which I believe is the same with MySQL). From that config files, another files can be included, making it easy to share pieces of configuration, or separate them in any way. -- Honza Pazdziora | [EMAIL PROTECTED] | http://www.fi.muni.cz/~adelton/ .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ... Only self-confident people can be simple. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL configuration
I have the file location discussion in my 7.4 hold mailbox: http:/momjian.postgresql.org/cgi-bin/pgpatches2 I am going to revisit it the next month and see if I can get all the opinions merged into a plan everyone can agree on. I think it can be done. --- Tom Lane wrote: Dennis Bjorklund [EMAIL PROTECTED] writes: On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote: more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. Why is it important and wouldn't it just make it harder to have several database clusters (for example with different locale) or several versions of pg installed at the same time? My recollection of the arguments against were first that and second reliability --- there was concern about getting config and data of multiple installations mixed up if they weren't kept together. In the worst case you could conceivably bollix an installation unrecoverably that way. (Right now I do not think there is anything quite that critical in postgresql.conf, but someday there might be. My very vague recollection is that the proposed patch changed things so that WAL and DATA directories would be separately specified in the config file; if correct, mismatching them definitely would be a great chance to shoot oneself in the foot.) I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL configuration
Tom Lane wrote: I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. regards, tom lane It seems to me that this is a packaging problem and not a postgresql problem. If someone wants to package PostgreSQL so that there's a symlink to a config file in /etc/pgsql or vice versa for the main database they're welcome to do that, and why not? As for test databases, there's already a -D for the datadir, why not add a -C for the config file as many software packages allow. Then packagers could put the config file anywhere they wanted. I would certainly welcome this feature as it would allow for easy tweaking/benchmarking. I agree that we should avoid the viral-like MySQL configuration plague. As to pgsql AT mohawksoft.com requested, here are a few widely used software packages that keep configuration close to the data, some in /var, some in /usr: Mailman OpenSSL Cyrus-IMAP Apache I believe doesn't install anything to /etc/ when you build from source. ---(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] locale
On Thu, 8 Apr 2004, Tom Lane wrote: No, the ordering *will* be the same as it was before, because strcoll() is still functioning the same. You'd get the same answer from a sort operation since it depends on the same operators. It interprets them according to LC_CTYPE, which does not change. I'm afraid that I don't understand you yet, and would like to have it explained in more detail if possible. While I feel a bit stupid to not understand what you are stating, but I'm sure there are more then me who feels like that :-) Maybe we can look at an example. Let us take some utf-8 strings correctly ordered in swedish Åke Ära now, since these are utf-8 they are encoded as c3 85 6b 65(Åke) c3 84 72 61(Ära) and that is the order they have in the index. Now, this index is copied into a new database where the encoding is Latin1. Now we want to in the above table lookup the string that in Latin1 is represented as c3 84 72 61 So we look in the index and see that the first row in the index is not the same. But, now when we compare these strings as latin1 strings it's no longer the case that c3 84 72 61 c3 85 6b 65. As latin1 strings we compare each character and c3 = c3, and then 84 85 (in latin1 84 and 85 are some control characters). Se, we will not find this string in the index since we think it should have been before the first entry. We might even insert a new copy of this string in another position in the index. So, my question is. a) What have we gained by copying this table into the latin1 database. It looks broken to me. As far as I understand we have to rebuild the index to get something that works at least a little. b) Maybe one should not just reindex but reencode. In some cases that works and produces good result. For example from latin1 to utf-8. c) if we are going to reindex anyway, then why not do that and solve the per database locale also. This is an independent point from a) and b) that I still want to understand the first two points even if we don't talk about per database locale. -- /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] PostgreSQL configuration
Honza Pazdziora [EMAIL PROTECTED] writes: On Thu, Apr 08, 2004 at 10:31:44AM -0400, Tom Lane wrote: It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. A counterexample of Apache shows that you can easily use -f or another command line option to point the server to alternate master config file (which I believe is the same with MySQL). According to http://www.mysql.com/documentation/mysql/bychapter/manual_Using_MySQL_Programs.html#Option_files /etc/my.cnf will be read if it exists, no matter what you say on the command line. So AFAICS the only way to make a private installation is to make sure that you have overridden each and every setting in /etc/my.cnf in a private config file that you do control. This is tedious and breakage-prone, of course. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] locale
Dennis Bjorklund [EMAIL PROTECTED] writes: On Thu, 8 Apr 2004, Tom Lane wrote: No, the ordering *will* be the same as it was before, because strcoll() is still functioning the same. You'd get the same answer from a sort operation since it depends on the same operators. But, now when we compare these strings as latin1 strings it's no longer the case that c3 84 72 61 c3 85 6b 65. As latin1 strings we compare each character and c3 = c3, and then 84 85 (in latin1 84 and 85 are some control characters). You're missing the point: strcoll() is not going to compare them as latin1 strings. It's going to interpret the bytes as utf-8 strings, because that's what LC_CTYPE will tell it to do. So the sort ordering of any particular byte string remains the same as it was before, and the index does not become corrupt. Whether the index is delivering answers that you find useful is a whole different question ;-). For example, if you do a WHERE col = 'foo' type of query, you'll be presenting the latin1 encoding of 'foo', which may well not equal the utf-8 encoding of 'foo', meaning you won't find that row even if it exists. However this would be true whether you used the index or not --- it's really a data failure and not an index failure. a) What have we gained by copying this table into the latin1 database. It looks broken to me. It looks broken to me too, in terms of user functionality. I was simply responding to your assertion that the indexes will be corrupt. They won't be. AFAICS, to support per-database encoding and locale correctly, CREATE DATABASE would have to be prepared to re-encode *and* re-index every textual column in the copied database. I don't really foresee us going to that much work in order to have a solution that's still half-baked and non-spec-compliant. It's much more likely that per-column locale and encoding will get done instead. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] make == as = ?
On Thu, 8 Apr 2004, Bruce Momjian wrote: Fabien COELHO wrote: This would help me, at least, write correct and portable SQL. :) Added to TODO: * Add a session mode to warn about non-standard SQL usage So it seems that having C-like operators would hurt a lot;-) So you want to generate warnings for SERIAL, TEXT and a bunch of other types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of pg_catalog instead of information_schema (I may be wrong in the list, I don't have the standard at hand, but I think I'm right in the spirit)... This is going to be noisy;-) Yep, it sure is going to be noisy. Could we consider a three (or more) way setting, for what to do? Something like: sql_noncompliance_mode = error; sql_noncompliance_mode = warn / notice; sql_noncompliance_mode = ignore; Just wondering... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL configuration
Dennis Bjorklund [EMAIL PROTECTED] writes: On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote: more flexable configuration based on the idea that configuration and data are in SEPARATE locations is important. Why is it important and wouldn't it just make it harder to have several database clusters (for example with different locale) or several versions of pg installed at the same time? My recollection of the arguments against were first that and second reliability --- there was concern about getting config and data of multiple installations mixed up if they weren't kept together. In the worst case you could conceivably bollix an installation unrecoverably that way. (Right now I do not think there is anything quite that critical in postgresql.conf, but someday there might be. My very vague recollection is that the proposed patch changed things so that WAL and DATA directories would be separately specified in the config file; if correct, mismatching them definitely would be a great chance to shoot oneself in the foot.) The patch I had kept the directory layout as one single setting, just that postgresql,conf was able to contain the location of pg_hba.conf, pg_ident.conf, and the data directory. Thus, one could start PostgreSQL as: postmaster -C /etc/postgres/webdb.conf Which would allow full configuration from that one file. I've recently had some very unpleasant experiences trying to install test versions of MySQL on machines that already had older versions installed normally. It seems that MySQL *will* read /etc/my.cnf if it exists, whether it's appropriate or not, and so it's impossible to have a truly independent test installation, even though you can configure it to build/install into nonstandard directories. Let's not emulate that bit of brain damage. MySQL is, in general, unpleasent, but that is more or less a packaging issue. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] make == as = ?
scott.marlowe wrote: On Thu, 8 Apr 2004, Bruce Momjian wrote: Fabien COELHO wrote: This would help me, at least, write correct and portable SQL. :) Added to TODO: * Add a session mode to warn about non-standard SQL usage So it seems that having C-like operators would hurt a lot;-) So you want to generate warnings for SERIAL, TEXT and a bunch of other types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of pg_catalog instead of information_schema (I may be wrong in the list, I don't have the standard at hand, but I think I'm right in the spirit)... This is going to be noisy;-) Yep, it sure is going to be noisy. Could we consider a three (or more) way setting, for what to do? Something like: sql_noncompliance_mode = error; sql_noncompliance_mode = warn / notice; sql_noncompliance_mode = ignore; I think a boolean that turns on warnings would be enough. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function to kill backend
Bruce Momjian [EMAIL PROTECTED] writes: On first glance, I don't see anything dangerous about SIGTERM. You haven't thought about it very hard :-( The major difference I see is that elog(FATAL) will call proc_exit directly from elog, rather than longjmp'ing back to PostgresMain. The case that we have confidence in involves elog(ERROR) returning to PostgresMain and then calling proc_exit from there (in the path where we get EOF from the client). This leaves me with a couple of concerns: * Notice all that cleanup/reset stuff in the if (sigsetjmp()) block in PostgresMain. SIGTERM will cause proc_exit to be entered without any of that being done first. Does it work reliably? Shouldn't this be refactored to ensure the same things happen in both cases? * There are various places, especially in the PLs, that try to hook into error recovery by manipulating Warn_restart. Will any of them have problems if their error recovery code doesn't get called during SIGTERM exit? One possible refactoring is for elog(FATAL) to go ahead and longjmp back to PostgresMain, and at the end of the error recovery block check a flag and do proc_exit() if we're fataling. However I am not sure that this doesn't break the design for coping with elog's during proc_exit. Alvaro's nested-transaction work is another thing that's got to be thought about before touching this code. I have not yet seen any design for error recovery in the nested xact case, but I am sure it's going to need some changes right around here. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] rotatelogs integration in pg_ctl
Tom Lane wrote: Andrew Hammond [EMAIL PROTECTED] writes: I've attached a patch for pg_ctl which integrates the Apache project's rotatelogs for logging. Why bother? You just pipe pg_ctl's output to rotatelogs and you're done. It's not difficult to do, once you know how and once you know that there aren't any gotchas. However, the question comes up often enough it's clear that not everybody knows how. This provides a simple, clean, standardized way of using rotatelog. The patch is simple, low risk, and limited impact. So, why not? Drew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] locale
On Thu, 8 Apr 2004, Tom Lane wrote: You're missing the point: strcoll() is not going to compare them as latin1 strings. It's going to interpret the bytes as utf-8 strings, because that's what LC_CTYPE will tell it to do. My current understanding of what you are saying now is that LC_CTYPE is always UTF-8 and all comparisons in the new database are going to be wrong. This since all strings will be compared as if they where UTF-8. LC_CTYPE is per cluster and not per database as some of the other LC_. Yes, this actually makes sense. I really hope that this is the way it work because I think I can understand this. I don't like it, but I can understand what pg currently do, which is good (unless pg does something else :-) Thanks for the explanation. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] restore of large databases failing--any ideas?
Hi all, We're using pg_dump to backup our databases. The actual pg_dump appears to work fine. On smaller ( approx. 100 Meg) data sets, the restore also works, but on larger data sets the restore process consistently fails. Other facts that may be of interest: * We're running Postgres 7.2.3 on a more-or-less stock Red Hat 7.3 platform. * Backup is done with pg_dump -c -U postgres, then gzip * Restore is via cat archive_file | gunzip | psql The particular file I'm wrestling with at the moment is ~2.2 Gig unzipped. If you try to restore using pg_restore, the process immediately fails with the following: pg_restore: [archiver] could not open input file: File too large When the data file is gzip'd, you can at least get the restore process started with the following: cat archive_file.gz | gunzip | psql dbname The above command line starts OK, but eventually fails with: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Function to kill backend
Otherwise, I'll stick by my assertion that idle connection management should
be done in the middleware and NOT by psql.
Perhaps it should be, but as PostgreSQL picks up more and more vendor
applications this is difficult for the person administrating the
database.
Consider a 3rd party application which has the issue but the support
contract is such that you cannot affect the application itself (their
support staff deals with it).
If you need connections in the database for other applications and this
3rd party program is idling on several slots...
--
Rod Taylor rbt [at] rbt [dot] ca
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
signature.asc
Description: This is a digitally signed message part
[HACKERS] using index on comparison with bit-operation?
hi, is it possible to use an index on the expression '(table_1.field table_2.field)::int 0' ? here's the whole query: SELECT COUNT(*) FROM users AS users JOIN search_profile AS search_profile ON (search_profile.bin_matching_field_0 users.bin_matching_field_0)::int 0 WHERE users.id = 190 best regards michael ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] The Tomb of the Unknown Type?
We have encountered a pretty oddball situation involving an unknown type. mydb=# select version(); version -- PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-24) (1 row) mydb=# \d redact_current24248 Table public.redact_current24248 Column | Type| Modifiers -+---+--- n_posted_transaction_id | integer | n_year_u| unknown | n_month_u | unknown | n_breakdown_config_id | integer | n_amount| numeric | We'd like to turn those unknown values into plain integers (e.g. - years and months); apparently it's not so simple... mydb=# select n_year_u::integer, n_month_u::integer from redact_current24248 limit 10; ERROR: failed to find conversion function from unknown to integer How this was generated was with Perl code where the prepared query looks something like the following: CREATE TEMP TABLE $tableName AS . SELECT a.id as n_posted_transaction_id, . ? as n_year_u, . ? as n_month_u, . c.id as n_breakdown_config_id, . calc_revenue( various_parameters ) as n_amount . FROM . transactions_posted a, . items b, . transaction_breakdown_config c; ; I wasn't aware of there being an unknown type, and it's rather bizarre that this is happening. I imagine that specifying SELECT a.id as n_posted_transaction_id, . ?::integer as n_year_u, . ?::integer as n_month_u, . would likely clear this up, but where unknown came from is something of a mystery. The source types shouldn't be any mystery. -- cbbrowne,@,cbbrowne.com http://cbbrowne.com/info/x.html str-str_pok |= SP_FBM; /* deep magic */ s = (unsigned char*)(str-str_ptr); /* deeper magic */ -- Larry Wall in util.c from the perl source code ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] postgres/pgtcl windows
Dear folks, I have a Tcl/Tk application which runs on Unix using either MySql or Postgres - this works. The same Tcl/Tk application runs on Windows using MySql. Does anyone have any recommendations for places to download Postgres and pgtcl libraries to enable running my Tcl/Tk application on Windows using Postgres? Any help would be greatly appreciated. I have had little or no success in this arena. Ron Regards, Ronald C. Nolte Sr. Software Engineer, SAIC 1710 SAIC Drive, Suite 300 McLean, VA 22102 [EMAIL PROTECTED] Office: (703) 676-2018 Fax:(703) 893-3084 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Small suggestion on build script
-Original Message- From: Dann Corbit Sent: Wednesday, April 07, 2004 10:34 AM To: Tom Lane Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Small suggestion on build script -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 10:55 PM To: Dann Corbit Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Small suggestion on build script Dann Corbit [EMAIL PROTECTED] writes: I would like to suggest changing the symlinks to copy commands: That would cause make to fail to handle update dependencies on the linked files. An unconditional copy will cause no breakage. It's not a big deal, I can always edit the files by hand. Or I can write a sed script that fixes it automatically. The reason is that under Mingw, I get random failures with the symlinks This is the worst sort of Microsoft imperialism that I hoped we'd not be succumbing to by having a Windows port :-(. If it doesn't work on Windows then the rest of you have to give up functionality. How about filing a bug report against the Mingw breakage, instead of expecting us to try to work around it? The problem has nothing to do with Microsoft imperialism. Microsoft has no connection to Mingw, it is an independent tool set. Filing the bug report is a good idea. The response from the Mingw team: Comment By: Earnie Boyd (earnie) Date: 2004-04-08 07:33 Message: Logged In: YES user_id=15438 Symbolic links to files and directories do not work on Win32 in general. Support for symlink operation is limited to the source directory or file existing and being able to copy the source to the destination. You will need to modify the package to do the appropriate thing for the environment. Earnie ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL configuration
On Thu, 2004-04-08 at 09:49, [EMAIL PROTECTED] wrote:
On Thu, 8 Apr 2004 [EMAIL PROTECTED] wrote:
more flexable configuration based on the idea that configuration and
data
are in SEPARATE locations is important.
Why is it important and wouldn't it just make it harder to have several
database clusters (for example with different locale) or several versions
of pg installed at the same time?
My patch did not remove any functionality, it merely augmented it.
To say that it would make it more difficult to deploy multiple databases
is misleading for (2) reasons.
(1) It need not do that, because the configuration system would seem
unchanged for those who do not wish to use it in this way.
True, but it is more difficult to deal with multiple databases if one
configures there system in the fashion... debian packages their
installations this way via symlinks so i've experience the difficulty
first hand. .
(2) I would bet that *most* deployments of PostgreSQL only use one
database environment per server, so I'm not even sure that it would be an
issue for the majority of current or prospective users.
except that when doing major version upgrades, i find it far better
practice to install multiple versions on the machine whenever possible,
even if you only intend to run a single version.
It is all well and good to say our way is better, (with which I do not
agree) but there are, more or less, if not standards, standard
concepts from which good software design follows. Besides PostgreSQL,
name one popular open source project that is widely used that stores its
configuration information inside its data repository. From the new user
perspective, configuration within the data directory is an alien concept.
i remember refuting this last time and i have to say something again
because this is equally misleading... apache does things this way if you
build from source, and there are others as well.
From a sysadmin perspective, having configuration in a standard location
makes sense. It makes these things easy to backup, archive, and put under
version control. (Many sysadmins put machine configuration under version
control to see what changes are made over time.)
and i would say that right now the way postgresql does it is much
easier. when you first get on a machine and need to find the webroot of
an apache install, theres no telling where it could be simply because a
lot of packagers do package things up differently.
Finally, I'm not suggesting removing any functionality, I am suggesting
that configuration can and should be able to be located in a standard
location and the the configuration be able to point to the data volume.
IIRC part of the problem with the initial patch/proposal is that it had
implementation issues following a couple of OS guidelines/specs, and
there was an issue with the pid.
One potential bonus I would see to this type of functionality is that on
some servers I have multiple postgresql.confs on a server tuned to
specific tasks at hand... ie one for a pg_restore vs. one for normal
operations... it would be nice to point the db at a specific one rather
than having to copy files back and forth.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] using index on comparison with bit-operation?
On Wed, Apr 07, 2004 at 06:01:03 -0700, Michael Groth [EMAIL PROTECTED] wrote: hi, is it possible to use an index on the expression '(table_1.field table_2.field)::int 0' ? here's the whole query: SELECT COUNT(*) FROM users AS users JOIN search_profile AS search_profile ON (search_profile.bin_matching_field_0 users.bin_matching_field_0)::int 0 WHERE users.id = 190 In 7.4 you can create indexes on expressions. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] using index on comparison with bit-operation?
Michael Groth wrote: hi, is it possible to use an index on the expression '(table_1.field table_2.field)::int 0' ? here's the whole query: SELECT COUNT(*) FROM users AS users JOIN search_profile AS search_profile ON (search_profile.bin_matching_field_0 users.bin_matching_field_0)::int 0 WHERE users.id = 190 No, there is no way to use an index because the columns are in different tables. It is like saying: tab1.col1 = tab2.col2 Now, you want them both to be true, so it is really: tab1.col1 AND tab2.col2 and that can be indexed by separate indexes on col1 and col2. Of course, if many rows are true, the index will not be used because it is faster to just look at all the rows with a sequential scan. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] using index on comparison with bit-operation?
Bruno Wolff III wrote: On Wed, Apr 07, 2004 at 06:01:03 -0700, Michael Groth [EMAIL PROTECTED] wrote: hi, is it possible to use an index on the expression '(table_1.field table_2.field)::int 0' ? here's the whole query: SELECT COUNT(*) FROM users AS users JOIN search_profile AS search_profile ON (search_profile.bin_matching_field_0 users.bin_matching_field_0)::int 0 WHERE users.id = 190 In 7.4 you can create indexes on expressions. But it uses two different tables. You can't mix tables in an index, can you? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] Function to kill backend
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Not having a way to kill backends is like having no way to kill a process except rebooting the server. Some people think that making a database hard to kill is a good thing. Sure. But we're not talking about taking down the whole database, we're talking about taking down a connection. Killing the database is the equivalent of killing the OS. It should be hard to do either. But it should be easy to kill a process on an OS if you have the right permissions, and similiarly it should be easy to kill a connection to the database if you have the right permissions. With respect to nested transactions and other things that might make properly shutting down difficult, it seems to me that the SIGINT case is actually a harder case to deal with. Why? Because for the SIGTERM case, you basically have to do whatever is done whenever the connection itself drops. If we can't handle the connection itself dropping out arbitrarily then we have more serious problems than just how to handle SIGTERM. :-) But for SIGINT you have to decide whether to just abort the innermost transaction or the outermost one, and if it's the outermost one you have to abort then you have to provide the mechanism for it -- something that you might not have to deal with otherwise. So it seems that handling SIGTERM might actually be easy: you have the signal handler close the backend's side of the connection and let the connection-dropping logic kick in automatically, no? Thoughts? Am I completely off my rocker here? :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] locale
Dennis Bjorklund [EMAIL PROTECTED] writes: LC_CTYPE is per cluster and not per database as some of the other LC_. Yup, exactly. If we did not force both LC_COLLATE and LC_CTYPE to have the same values cluster-wide, then we *would* have index corruption issues. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] restore of large databases failing--any ideas?
[EMAIL PROTECTED] (S. Hawkins) writes: * We're running Postgres 7.2.3 on a more-or-less stock Red Hat 7.3 platform. Both the database and the platform are seriously obsolete :-( The particular file I'm wrestling with at the moment is ~2.2 Gig unzipped. If you try to restore using pg_restore, the process immediately fails with the following: pg_restore: [archiver] could not open input file: File too large It appears that you're working with a pg_restore binary that doesn't support access to files larger than 2G. This is mostly an issue of what the platform's libc can handle; and on many platforms it depends on build or link options. I no longer recall whether RH 7.3 supported largefile access at all, let alone what build-time pushups were needed to make it happen if it could happen. My recommendation would be to get hold of a current PG version, dump using the current version's pg_dump, then install and reload into the current version. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] postgres/pgtcl windows
Ron, --- Nolte, Ronald C. [EMAIL PROTECTED] wrote: Does anyone have any recommendations for places to download Postgres and pgtcl libraries to enable running my Tcl/Tk application on Windows using Postgres? You can grab one here: http://www.bschwarz.com/projects/pgaccess/ It's the first link. I have only tested with WinXP and Win98 for PG7.4 and PG7.3...and only light testing...but should work fine. It has libpq statically linked in, so you don't have to mess around getting libpq compiled/installed. It was built with PG7.4 libs, and Tcl 8.2. It should work with any Tcl version = 8.2 HTH, --brett __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL configuration
Robert Treat [EMAIL PROTECTED] writes: On Thu, 2004-04-08 at 09:49, [EMAIL PROTECTED] wrote: (2) I would bet that *most* deployments of PostgreSQL only use one database environment per server, so I'm not even sure that it would be an issue for the majority of current or prospective users. except that when doing major version upgrades, i find it far better practice to install multiple versions on the machine whenever possible, even if you only intend to run a single version. In any case, you will never get such a proposal past the core developers, because we all run multiple PG installs per machine. My primary development machine currently has six postmasters alive on it (7.0, 7.1, ..., 7.4 + CVS tip); my alternate machine has five installations on it, though not all are alive since I've not had reason to restart them all since last reboot; even the laptop I'm physically typing on right now has more than one Postgres installation on it. And practically any time someone allows me access to a machine of theirs to check out some kind of portability issue, I'll build a test installation in my guest-account home directory, rather than muck with their live server. So, don't bother proposing anything that makes it even slightly harder to run multiple servers per machine. It will not happen. End of discussion. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] The Tomb of the Unknown Type?
Chris Browne [EMAIL PROTECTED] writes: We have encountered a pretty oddball situation involving an unknown type. The way you get this sort of thing is with CREATE VIEW foo AS SELECT ... , 'literal', ... The undecorated literal is initially of type UNKNOWN, and there's nothing to cause it to get coerced to some more-specific type, so UNKNOWN ends up actually showing in the view's column type. The CREATE command will bleat ineffectually about this, but create the view anyway. The cure is to cast the literal to some specific type when you do the CREATE. One could perhaps argue that we should default to assuming that TEXT type was meant, as we do in some similar cases such as UNION. But it's not done at the moment. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Small suggestion on build script
Dann Corbit [EMAIL PROTECTED] writes: The response from the Mingw team: Symbolic links to files and directories do not work on Win32 in general. Support for symlink operation is limited to the source directory or file existing and being able to copy the source to the destination. The source does exist, and they should be able to copy it to the destination, and they do manage to do so much of the time. I think you failed to convey the critical point, which is that they randomly fail to do what they claim to be able to do. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] locale
On Thu, 8 Apr 2004, Tom Lane wrote: Yup, exactly. If we did not force both LC_COLLATE and LC_CTYPE to have the same values cluster-wide, then we *would* have index corruption issues. We really show warn people that using another encoding in a database then what the cluster uses, breaks sorting. I was under the impression that as long as I've set the right locale when doing initdb I could then create different databases with different encodings and it all works, but it does not. I simply trust pg too much (not without reason since it is an amazing project). -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
