[HACKERS] Postgres 7.2.2 Segment Error
I just saw this in my logs:
2002-09-18 12:13:10 ERROR: cannot open segment 1 of relation users_sessions
(target block 1342198864): No such file or directory
This query caused it:
DELETE FROM users_sessions WHERE changed ('now'::timestamp - '1440
minutes'::interval) AND name = 'fhnid';
However, I cannot repeat the error now. Is this a bug in postgres
somewhere.
Also, what should I do to fix the table properly. I haven't vacuumed it or
anything yet in case someone wants to analyze it.
Chris
---(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] Proposal for resolving casting issues
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Note that if you write, say, set numericcol = numericcol * 3.14159; my proposal would do the right thing since the constant would be typed as numeric to start with and would stay that way. To do what you want with a float variable, it'd be necessary to write set numericcol = numericcol * float4col::numeric; Yes, that is the case where the new behavior would imho not be good (but you say spec compliant). I loose precision even though there is room to hold it. Lose what precision? It seems silly to imagine that the product of Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? a numeric and a float4 is good to more digits than there are in the float4. This is exactly the spec's point: combining an exact and an approximate input will give you an approximate result. Does it actually say how approximate the result needs to be, or is it simply approximate by nature that one part was only approximate ? Do they really mean, that an approximate calculation with one float4 must be calculated in float4 arithmetic ? If you e.g. calculate in float8 it would still be an approximate result and thus imho conform. (Unless of course the value in the float4 happens to be exact, eg, an integer of not very many digits. But if you are relying on that to be true, why aren't you using an exact format for storing it?) Probably because the approximate is more efficient in storage size, or the designer knew he only wants to store 6 significant digits ? Informix does the calculations in numeric, and then converts the result if no casts are supplied (would do set float4col = float4(float4col::numeric * numericcol)). I am not sure what the argument is for following Informix's lead rather than the standard's lead; especially when Informix evidently doesn't understand numerical analysis ;-) It was only an example of how someone else does it and was why I asked what other db's do. I would e.g. suspect Oracle does it similarily. Please, someone check another db ! Andreas ---(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: AIX compilation problems (was Re: [HACKERS] Proposal ...)
PS: pg snapshot 09/11 does not compile on AIX (large files (don't want _LARGE_FILES), Please provide details. On AIX we would only want to make the large file api visible (_LARGE_FILE_API) which automatically gets defined when xlc is used with -qlonglong. #ifdef _LARGE_FILE_API extern off64_t lseek64(int, off64_t, int); #endif configure somehow thinks it needs to #define _LARGE_FILES though, which then clashes with pg_config.h's _LARGE_FILES. I think the test needs to #include unistd.h . and mb conversions (pg_ascii2mic and pg_mic2ascii not found in the postmaster and not included from elsewhere) shared libs on AIX need to be able to resolve all symbols at linkage time. Those two symbols are in backend/utils/SUBSYS.o but not in the postgres executable. My guess is, that they are eliminated by the linker ? Do they need an extern declaration ? Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] generating postgres core files on debian
Hello, I am trying to debug a problem involving DBD::PgSPI that crashes the backend. It used to work fine util we installed perl-5.8. How can I get a core file of a crashed backend on a debian-linux (unstable) machine? My /etc/security/limits.conf is empty. When I login as root ulimit -c shows a limit of 0. If I set the limit to unlimited and logout/login the limit is back to 0. Is it sufficient to set the proper limit and then restart postgres in the same shell to obtain core files in case the backend crashes? Thanks in advance, cheers, -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] generating postgres core files on debian
On Thu, 2002-09-19 at 11:18, Louis-David Mitterrand wrote: Hello, I am trying to debug a problem involving DBD::PgSPI that crashes the backend. It used to work fine util we installed perl-5.8. How can I get a core file of a crashed backend on a debian-linux (unstable) machine? My /etc/security/limits.conf is empty. When I login as root ulimit -c shows a limit of 0. If I set the limit to unlimited and logout/login the limit is back to 0. I think /etc/security/limits.conf is used to limit what you can set with ulimit rather than dictate the settings. You probably need to put ulimit -c unlimited in ~postgres/.bash_profile. Is it sufficient to set the proper limit and then restart postgres in the same shell to obtain core files in case the backend crashes? Yes. The core file produced by postmaster from the binary package will not be very useful to you, because the binary is stripped. You need to build the package from source and use the binary from the source tree (.../src/backend/postmaster/postmaster), not the one copied into the package tree (.../debian/usr/lib/postgresql/bin/postmaster) since the stripping is done on the package tree after the binaries are installed there. To build the package: cd /usr/local/src apt-get source postgresql # installs in postgresql-7.2.2 apt-get build-dep postgresql # build dependencies apt-get install devscripts fakeroot # needed for building anything cd postgresql-7.2.2 debuild -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Bring ye all the tithes into the storehouse, that there may be meat in mine house, and prove me now herewith, saith the LORD of hosts, if I will not open you the windows of heaven, and pour you out a blessing, that there shall not be room enough to receive it. Malachi 3:10 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] generating postgres core files on debian
On Thu, Sep 19, 2002 at 12:17:15PM +0100, Oliver Elphick wrote: On Thu, 2002-09-19 at 11:18, Louis-David Mitterrand wrote: I am trying to debug a problem involving DBD::PgSPI that crashes the backend. It used to work fine util we installed perl-5.8. How can I get a core file of a crashed backend on a debian-linux (unstable) machine? My /etc/security/limits.conf is empty. When I login as root ulimit -c shows a limit of 0. If I set the limit to unlimited and logout/login the limit is back to 0. I think /etc/security/limits.conf is used to limit what you can set with ulimit rather than dictate the settings. Aha, that makes sense. You probably need to put ulimit -c unlimited in ~postgres/.bash_profile. Hmm, I hadn't thought of that Is it sufficient to set the proper limit and then restart postgres in the same shell to obtain core files in case the backend crashes? Yes. The core file produced by postmaster from the binary package will not be very useful to you, because the binary is stripped. You need to build the package from source and use the binary from the source tree (.../src/backend/postmaster/postmaster), not the one copied into the package tree (.../debian/usr/lib/postgresql/bin/postmaster) since the stripping is done on the package tree after the binaries are installed there. I also suspected that a stripped binary would not help much. Your indications will save me much time. To build the package: cd /usr/local/src apt-get source postgresql # installs in postgresql-7.2.2 apt-get build-dep postgresql # build dependencies apt-get install devscripts fakeroot # needed for building anything cd postgresql-7.2.2 debuild Hey, debuild is nice, didn't know about it until now. Cleaner dpkg-buidpackage -us -uc or fakeroot debian/rules binary ;) Thanks a lot for your help, -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: The TODO List (Was: Re: [HACKERS] Open 7.3 items)
On Thu, 19 Sep 2002, Bruce Momjian wrote: Marc G. Fournier wrote: On Thu, 19 Sep 2002, Bruce Momjian wrote: It is an open issue. It has to be resolved. When it is, I will remove it. I added a question mark to it but it needs to be tracked. I keep having to add and remove it because I have people telling me what to do. It was Peter who told me to add it, and you and Thomas to remove it. It isn't me adding/removing on my own. Right, so you have two telling you to remove it, one telling you to add it, and two that are discussion why/if it *should* be added ... Tom feels it should be added, and I'm clarifing the why of it ... don't re-add it until we've determined *if* it is actually an open issue or not ... stop jumping the gun ... I will make the decision. If you want to maintain your own open items list, go ahead. Ah, okay, so your list doesn't necessarily follow reality, its more for your own use ... k, as long as we have that clarified, we're fine ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Bruce Momjian [EMAIL PROTECTED] writes: Looking at the open item list, I see: fix up function return types on lang/type/trigger creation or loosen opaque restrictions Seems that should be fixed before beta2 because it does effect people loading data. Yeah, we should do something with that. Are people okay with the idea of CREATE LANGUAGE, etc, retroactively changing prorettype from OPAQUE to the correct thing? Are we done with all of these? Add casts: (Tom) assignment-level cast specification inet - text macaddr - text int4 - varchar? int8 - varchar? add param for length check for char()/varchar() All but the inet/macaddr-text change; I backed that out after finding that it induced a bunch of regression-test failures. The tests assume that inet = integer will provoke a failure. Guess what: if both inet and integer have implicit casts to text, the system takes it. On reflection I still feel that we should be getting rid of implicit casts to text rather than adding more. This is still an open bug: http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] PGXLOG variable worthwhile?
On Wed, 2002-09-18 at 22:24, Marc G. Fournier wrote:
On Wed, 18 Sep 2002, Bruce Momjian wrote:
Sorry, I don't see the logic here. Using postgresql.conf, you set it
once and it remains set until you change it again. With -X, you have to
use it every time. I think that's where the votes came from.
Ah, so you are saying that you type out your full command line each and
every time you start up the server? I know, in my case, I have a shell
script setup that I edit my changes in so that I don't have to remember
...
the effort/danger of editing a shell script can't be less than editing
postgresql.conf
You argued that -X and GUC make sense, but why add -X when can get it
done at once in postgresql.conf. Also, consider changing the location
does require moving the WAL files, so you already have this extra step.
Adding to postgresql.conf is easy. I don't think you can just point it
at a random empty directory on startup. Our goal was to reduce params
to postmaster/postgres in favor of GUC, not add to them.
I don't disagree that editing postgresql.conf is easy, but its not
something that ppl would naturally thing of ... if I want to move a
directory with most servers I run, I will generally do a man to find out
what command options are required to do this change, and, if none are
provided, just create a god-forsaken symlink ...
I don't know if I agree with that. Most servers (apache for instance) have
configuration variables on where files are going to live, not command line
options.
The man page for postmaster should have something in it like:
-X directory Specifies an alternate location for WAL files. Superseded
by setting xlog_path in postmaster.conf
Well, as with most (all?) GUC variables, wouldn't you have the option of doing
postmaster -o pgxlog=/dev/null and have the same functionality as -X ?
Hell, if you are going to remove -X because its 'easier to do it in
postmaster.conf', you should be looking at removing *all* command line
args that are better represented in the postmaster.conf file ...
Generally speaking people should be looking to avoid useing command line flags
and useing whats in the postgresql.conf, IMHO.
snip
the GUC value should override the command line option, agreed ... but the
ability to use the command line should not be removed just because some
ppl aren't competent enough to adjust their startup scripts if they change
their system ...
Shouldn't this work the other way around? Use what's in the conf file unless I
explicitly state otherwise? IIRC that's how it works with -i
Robert Treat
--
LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: The TODO List (Was: Re: [HACKERS] Open 7.3 items)
Marc G. Fournier [EMAIL PROTECTED] writes: Who implemented SIMILAR TO in the first place? Thomas. He put in the syntax, but as it stands it's simply syntactic sugar for ~ --- that is, our Posix-compatible regex match operator. Since the spec demands very non-Posix behavior, this is wrong. AFAICS, getting SIMILAR TO to operate per spec would require adding some sort of translation function that converts the spec-style pattern into a Posix pattern that our regex match engine would handle. This would at least require adding ^ and $ around the pattern, converting the escape character if any, and translating % and _ into .* and . respectively. There are probably some differences of detail that we'd need to fix later, but that would get it to a state where we need not be ashamed to release it. We already have a similar mechanism for handling LIKE ... ESCAPE clauses, so it doesn't seem too difficult to do. But I haven't got time for it... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: The TODO List (Was: Re: [HACKERS] Open 7.3 items)
Marc G. Fournier [EMAIL PROTECTED] writes: Right, so you have two telling you to remove it, one telling you to add it, and two that are discussion why/if it *should* be added ... Tom feels it should be added, and I'm clarifing the why of it ... don't re-add it until we've determined *if* it is actually an open issue or not ... stop jumping the gun ... It *is* an open issue, Marc: Peter and I think so, at least. You cannot declare by fiat that it isn't. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] PGXLOG variable worthwhile?
I think Marc made a pretty good case about the use of command line arguments but I think I have to vote with Tom. Many of the command line arguments you seem to be using do sorta make sense to have for easy reference or to help validate your runtime environment for each instance. The other side of that is, I completely agree with Tom in the it's a very dangerous option. It would be begging for people to shoot themselves with it. Besides, just as you can easily parse the command line, you can also parse the config file to out that information. Plus, it really should be a very seldom used option. When it is used, it's doubtful that you'll need the same level of dynamic control that you get by using command line options. As a rule of thumb, if an option is rarely used or is very dangerous if improperly used, I do think it should be in a configuration file to discourage adhoc use. Let's face it, specify XLOG location is hardly something people need to be doing on the fly. My vote is config file it and no command line option! Greg On Wed, 2002-09-18 at 23:50, Bruce Momjian wrote: Marc G. Fournier wrote: On Wed, 18 Sep 2002, Bruce Momjian wrote: Yea, but you aren't going to be needing to know the xlog directory that way, will you? Why not? Who are you to tell me how my scripts work, or how they get their information? I have a script that runs to tell me how much disk space each instance is using up, that parses the ps output for the -D argument ... having -X there would allow me to parse for that as well and, if it was in the ps output, add that appropriately into the calculations ... My point is, the functionality is there, and should be documented properly ... encourage ppl to use the GUC setting in postmaster.conf, but just because you can't grasp that some of us *like* to use command line args, don't remove such functionality ... You ask for a vote and see if you can get votes to add -X. We had that vote once already. We do make decisions on what people should use. If not, we would be as hard to manage as Oracle. -- 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 signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Proposal for resolving casting issues
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Yes, that is the case where the new behavior would imho not be good (but you say spec compliant). I loose precision even though there is room to hold it. Lose what precision? It seems silly to imagine that the product of Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? So? If you are storing one input as float4, then you cannot rationally say that you know the result to better than 6 digits, because you don't know the input to better than 6 digits. Claiming that 1000.001 is a more accurate answer for the product than 1000.0 is simply wishful thinking on your part: nothing to the right of the sixth digit actually means a darn thing, because you don't know whether the input was really exactly 1000, or should have been perhaps 1000.001. Do they really mean, that an approximate calculation with one float4 must be calculated in float4 arithmetic ? If you e.g. calculate in float8 it would still be an approximate result and thus imho conform. And still the output would be illusory: if you think you'd get 16 digits of precision that way, then you are failing to grasp the problem. (Unless of course the value in the float4 happens to be exact, eg, an integer of not very many digits. But if you are relying on that to be true, why aren't you using an exact format for storing it?) Probably because the approximate is more efficient in storage size, or the designer knew he only wants to store 6 significant digits ? Seems an exceedingly uncompelling scenario. The only values that could be expected to be stored exactly in a float4 (without very careful analysis) are integers of up to 6 digits; you might as well store the column as int4 if that's what you plan to keep in it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Postgres 7.2.2 Segment Error
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
I just saw this in my logs:
2002-09-18 12:13:10 ERROR: cannot open segment 1 of relation users_sessions
(target block 1342198864): No such file or directory
This query caused it:
DELETE FROM users_sessions WHERE changed ('now'::timestamp - '1440
minutes'::interval) AND name = 'fhnid';
What does EXPLAIN show as the plan for that query? I'm guessing an
indexscan, and that the error was caused by reading a broken item
pointer from the index. (1342198864 = hex 50005450, which sure looks
like the upper 5 shouldn't be there ... how big is the table, anyway?)
However, I cannot repeat the error now. Is this a bug in postgres
somewhere.
If the broken item pointer were indeed in the index, I'd expect it to be
100% repeatable. I'm wondering about flaky memory or some such. Have
you run any hardware diagnostics?
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] Proposal for resolving casting issues
Yes, that is the case where the new behavior would imho not be good (but you say spec compliant). I loose precision even though there is room to hold it. Lose what precision? It seems silly to imagine that the product of Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? So? If you are storing one input as float4, then you cannot rationally say that you know the result to better than 6 digits, because you don't know the input to better than 6 digits. Claiming that 1000.001 is a more accurate answer for the product than 1000.0 is simply wishful thinking on your part: nothing to the right of the sixth digit actually means a darn thing, because you don't know whether the input was really exactly 1000, or should have been perhaps 1000.001. I still see 1E-10 as a better answer to above calculation than your 0, and my snapshot 9/11 does return that 1E-10. For better understanding the test in pg: create table atab (a decimal(30,20), b float4, c decimal(30,20), d float4); insert into atab values (1.001,10.0,0, 0); update atab set c=a*b-b, d=a*b-b where 1=1; create view av as select a*b-b, 1, b, c,d from atab; \d av View definition: SELECT ((atab.a * numeric(atab.b)) - numeric(atab.b)), atab.a, atab.b , atab.c, atab.d FROM atab; If I understood your proposal that would now change to: View definition: SELECT ((float4(atab.a) * atab.b) - atab.b), atab.a, atab.b , atab.c, atab.d FROM atab; Do they really mean, that an approximate calculation with one float4 must be calculated in float4 arithmetic ? If you e.g. calculate in float8 it would still be an approximate result and thus imho conform. And still the output would be illusory: if you think you'd get 16 digits of precision that way, then you are failing to grasp the problem. I have not said 16 digits exact precision. I was saying, that an approximate result calculated in numeric makes more sense, than your float4 calculated result, and does the correct thing more often than not in the db centric cases I can think of. I do think I grasp the problem :-) (Unless of course the value in the float4 happens to be exact, eg, an integer of not very many digits. But if you are relying on that to be true, why aren't you using an exact format for storing it?) Probably because the approximate is more efficient in storage size, or the designer knew he only wants to store 6 significant digits ? Seems an exceedingly uncompelling scenario. The only values that could be expected to be stored exactly in a float4 (without very careful analysis) are integers of up to 6 digits; you might as well store the column as int4 if that's what you plan to keep in it. You can store 6 significant digits and an exponent (iirc 10E+-38) ! e.g. 1.23456E-20 an int can't do that. I give up now. I voiced my concern, and that is as far as my interest goes on this actually. I still think fielding what other db's do in this area would be a good thing before proceeding further. Andreas ---(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] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 04:57:30PM +0200, Zeugswetter Andreas SB SD wrote: Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? So? If you are storing one input as float4, then you cannot rationally say that you know the result to better than 6 digits, because you don't know the input to better than 6 digits. Claiming that 1000.001 is a more accurate answer for the product than 1000.0 is simply wishful thinking on your part: nothing to the right of the sixth digit actually means a darn thing, because you don't know whether the input was really exactly 1000, or should have been perhaps 1000.001. I still see 1E-10 as a better answer to above calculation than your 0, and my snapshot 9/11 does return that 1E-10. Well, then you'd be wrong. Numerical analysis says you _can't_ get more information out than went in to the _least_ precise part of a calculation. What your suggesting is the equivalent of wanting to put up a shelf, so you estimate the length of the wall by eyeballing it, then measure the wood for the shelf with a micrometer, to be sure it fits exactly right. We teach this in intro science classes all the time: if you calculate with 3.14 as an approximation to pi, you better not report the circumference of a circle as 2.45678932 cm, I'll take off points! I do think I grasp the problem :-) Hmm, I'm not so sure. ;-) I give up now. I voiced my concern, and that is as far as my interest goes on this actually. I still think fielding what other db's do in this area would be a good thing before proceeding further. Ah, sorry to drag this on, then. But this is one of those clear cases were we must fo the right thing, not follow the crowd. PostgreSQL gets used by a lot of scientific projects (Have you noticed all the big bioinformatics databases being mentioned on the lists?). Partly because we're always underfunded, partly because we're academics who like to have the code. If we start getting basic maths wrong, that'll be a huge balck eye for the project. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 10:30:51AM -0500, Ross J. Reedstrom wrote: Ah, sorry to drag this on, then. But this is one of those clear cases were we must fo the right thing, not follow the crowd. PostgreSQL gets do used by a lot of scientific projects (Have you noticed all the big bioinformatics databases being mentioned on the lists?). Partly because we're always underfunded, partly because we're academics who like to ^^(scientific projects) ^^ have the code. If we start getting basic maths wrong, that'll be a huge ^^(PostgreSQL) balck eye for the project. black Clearly, it's time for an early lunch for me. I need sugar for my brain. Ross ---(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] Proposal for resolving casting issues
Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? So? If you are storing one input as float4, then you cannot rationally say that you know the result to better than 6 digits, because you don't know the input to better than 6 digits. Claiming that 1000.001 is a more accurate answer for the product than 1000.0 is simply wishful thinking on your part: nothing to the right of the sixth digit actually means a darn thing, because you don't know whether the input was really exactly 1000, or should have been perhaps 1000.001. I still see 1E-10 as a better answer to above calculation than your 0, and my snapshot 9/11 does return that 1E-10. Well, then you'd be wrong. Numerical analysis says you _can't_ get more information out than went in to the _least_ precise part of a calculation. What your suggesting is the equivalent of wanting to put up a shelf, so you estimate the length of the wall by eyeballing it, then measure the wood for the shelf with a micrometer, to be sure it fits exactly right. We teach this in intro science classes all the time: if you calculate with 3.14 as an approximation to pi, you better not report the circumference of a circle as 2.45678932 cm, I'll take off points! What if he must display 9 digits and says the result is approximately 2.45678932 would that be worse than 2.4600 ? That is what I am trying to say. Probably the standard is meant as a hint for db users, that such results will be approximate, not where the first digit sits that is not exact any more. For above calculation pg will in the future return 0. as an answer to 1.01*1000.0-1000.0 when used in my example context, while it currently returns 0.0010 ... You both are saying, that 0. is a better answer. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 06:00:37PM +0200, Zeugswetter Andreas SB SD wrote: What if he must display 9 digits and says the result is approximately 2.45678932 would that be worse than 2.4600 ? Yup. Trailing zeros are not significant. That's why scientific notation is nice: you don't fill in all those insignificant placeholders. For above calculation pg will in the future return 0. as an answer to 1.01*1000.0-1000.0 when used in my example context, while it currently returns 0.0010 ... You both are saying, that 0. is a better answer. That's right. And correct, as well. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for resolving casting issues
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: For above calculation pg will in the future return 0. as an answer to 1.01*1000.0-1000.0 when used in my example context, while it currently returns 0.0010 ... You both are saying, that 0. is a better answer. Not exactly: we are saying it is not a worse answer. There's no reason to prefer one over the other, because they are both within the range of uncertainty given the inherent uncertainty in the float4 input. If you want exact results, you should be using exact datatypes. 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] [GENERAL] killing process question
On 19 Sep 2002 at 11:19, Johnson, Shaunn wrote: --howdy: --not that the process is doing a lot or taking up --a lot of resources, it's just something --that i allow the users to kill and then --it get's passed to me for correction if the --simple 'kill pid' thing doesn't work. --what i'm trying to understand is if there --is a way to do this without having to restart --the database (remember, it's still production) --everytime there is a runaway process AND not --kill -9 pid. --how can i do this? I did a quick 'grep -rin' on postgresql source code I have(CVS, a week old). Looks like postgresql backend is ignoring the SISPIPE which is delivered to backend process when other end is closed. Obviously this is going to cause hanging back-ends. I guess a backend should terminate as if connection is closed. What say? Bye Shridhar -- Guillotine, n.: A French chopping center. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: The TODO List (Was: Re: [HACKERS] Open 7.3 items)
Tom Lane wrote: AFAICS, getting SIMILAR TO to operate per spec would require adding some sort of translation function that converts the spec-style pattern into a Posix pattern that our regex match engine would handle. This would at least require adding ^ and $ around the pattern, converting the escape character if any, and translating % and _ into .* and . respectively. There are probably some differences of detail that we'd need to fix later, but that would get it to a state where we need not be ashamed to release it. We already have a similar mechanism for handling LIKE ... ESCAPE clauses, so it doesn't seem too difficult to do. But I haven't got time for it... It seems like a merge of regex and LIKE patterns. ANSI doesn't have regex so maybe SIMILAR TO is their solution to that. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] killing process question
Shridhar Daithankar [EMAIL PROTECTED] writes: I guess a backend should terminate as if connection is closed. What say? No. It will terminate when it tries to read the next query from the client. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] killing process question
On 19 Sep 2002 at 11:49, Tom Lane wrote: Shridhar Daithankar [EMAIL PROTECTED] writes: I guess a backend should terminate as if connection is closed. What say? No. It will terminate when it tries to read the next query from the client. OK. But what if it never reads anything? I mean if the client dies after a complete transaction i.e. no input pending for either back end or client, will it just sit around waiting for select to signal that fd?(AFAIU, that's how things goes in there..) Clearly we have a case where backend is hung persumably. Either it has to have an explanation(OK client did aborted abruptly) and/or a possible corrective action.. Just some thoughts.. Bye Shridhar -- QOTD: I won't say he's untruthful, but his wife has to call the dog for dinner. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] killing process question
Johnson, Shaunn [EMAIL PROTECTED] writes: --okay, but the client has since terminated --it's session (if i understand you correctly). --is this just something that will just have to --hang around until i shutdown the database / boot --the machine? I dunno. Are you sure this is a backend process? What is it doing (or not doing) ... is it chewing any CPU cycles? What status does it show in ps? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Memory Errors...
Ian Harding [EMAIL PROTECTED] writes:
It is pltcl [not plpgsql]
Ah. I don't think we've done much of any work on plugging leaks in
pltcl :-(.
It hurts when I do this:
drop function memleak();
create function memleak() returns int as '
for {set counter 1} {$counter 10} {incr counter} {
set sql select ''foo''
spi_exec $sql
}
' language 'pltcl';
select memleak();
Yeah, I see very quick memory exhaustion also :-(. Looks like the
spi_exec call is the culprit, but I'm not sure exactly why ...
anyone have time to look at this?
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])
SIMILAR TO syntax (Was: Re: The TODO List (Was: Re: [HACKERS] O...)
On Thu, 19 Sep 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Who implemented SIMILAR TO in the first place? Thomas. He put in the syntax, but as it stands it's simply syntactic sugar for ~ --- that is, our Posix-compatible regex match operator. Since the spec demands very non-Posix behavior, this is wrong. AFAICS, getting SIMILAR TO to operate per spec would require adding some sort of translation function that converts the spec-style pattern into a Posix pattern that our regex match engine would handle. This would at least require adding ^ and $ around the pattern, converting the escape character if any, and translating % and _ into .* and . respectively. There are probably some differences of detail that we'd need to fix later, but that would get it to a state where we need not be ashamed to release it. We already have a similar mechanism for handling LIKE ... ESCAPE clauses, so it doesn't seem too difficult to do. But I haven't got time for it... 'K, just curious here, but ... Thomas, do you agree with Tom's interpretation of the spec? If so, would it be possible to get the above fixed? Or is there an ambiguity there (not like *that* has never happened before) that Tom/Peter are being more strict about then the spec requires? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] PGXLOG variable worthwhile?
On Thu, 19 Sep 2002, Robert Treat wrote: I don't know if I agree with that. Most servers (apache for instance) have configuration variables on where files are going to live, not command line options. Not where it involves *critical* files: OPTIONS -R libexecdir This option is only available if Apache was built with the SHARED_CORE rule enabled which forces the Apache core code to be placed into a dynamic shared object (DSO) file. This file is searched in a hardcoded path under Server- Root per default. Use this option if you want to override it. Well, as with most (all?) GUC variables, wouldn't you have the option of doing postmaster -o pgxlog=/dev/null and have the same functionality as -X ? True, but then that negates the whole argument about not having a command line option, no? Which I believe was the whole argument on this ... no? Shouldn't this work the other way around? Use what's in the conf file unless I explicitly state otherwise? IIRC that's how it works with -i God, I wish I had thought to note it at the time ... one of the things I did when I dove into this was to check how various Unix daemons were doing it, now I can't recall which I was looking at that mentioned the config file overriding the command line options, but you are correct, the command line should override the conf file ... ---(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] [GENERAL] Memory Errors...
I said: Yeah, I see very quick memory exhaustion also :-(. Looks like the spi_exec call is the culprit, but I'm not sure exactly why ... anyone have time to look at this? On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. pltcl_SPI_execp() has a similar problem, and there may be comparable bugs in other pltcl routines (not to mention other sources of memory leaks, but I think this is the problem for your example). I have no time to work on this right now; any volunteers out there? 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] [GENERAL] Memory Errors...
Tom Lane wrote: I said: Yeah, I see very quick memory exhaustion also :-(. Looks like the spi_exec call is the culprit, but I'm not sure exactly why ... anyone have time to look at this? On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. pltcl_SPI_execp() has a similar problem, and there may be comparable bugs in other pltcl routines (not to mention other sources of memory leaks, but I think this is the problem for your example). I have no time to work on this right now; any volunteers out there? I can give it a shot, but probably not until the weekend. I haven't really followed this thread closely, and don't know tcl very well, so it would help if someone can send me a minimal tcl function which triggers the problem. Thanks, Joe ---(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] unaccent
[EMAIL PROTECTED] writes: Not to_ascii, since there are so many extended UNICODE characters that doesn't have any accent and should not be converted to an ASCII character. Really, the accent conversion should be part of the character set conversion routines. At least my local iconv does that. In general, the determination of what is an accent and how to convert it is both dependent on locale and the intended usage. It's not clear how that should be handled. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Inconsistent Conversion Names
Tatsuo Ishii writes: The conversion named SJIS is different from IANA's shift_jis. It actually matches Windows-31J in IANA, which is too ugly to being emploied as our conversion name, IMO. OK I agree with win1250 - windows_1250, win1251 - windows_1251, but do not agree with renaming win866. There's no windows_866 in IANA. Maybe that should be ibm866? Is it ibm866 or are you wondering yourself? Someone said that the conversion table is actually koi8r + koi8u, being different from IANA's koi8_r. Not sure though. I found mention in the archives by Oleg B. that it is in fact koi8_r. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Having no luck with getting pgbench to run multiple times
Hi everyone,
Am trying my hand at a bit of C code again. Specifically am trying to
get Tatsuo's pgbench code to loop around more than once, but it keeps
on hanging forever at this line:
if ((nsocks = select(maxsock + 1, input_mask, (fd_set *) NULL,
(fd_set *) NULL, (struct timeval *) NULL)) 0)
{
etc
Running this on a FreeBSD 4.6.2 system with PostgreSQL 7.2.2 and gcc
2.95.3. Looking around the Net seems to say that hangs like this are
caused by the select blocking, but that's not helping me any with
knowing what to do.
Does anyone have an idea of what I can do, or maybe have a few minutes
to look at my code and point out the problem?
:-)
Regards and best wishes,
Justin Clift
--
My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there.
- Indira Gandhi
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] PL/pgSQL question
Hi, is a pl/pgSQL function completely parsed once? Or is only the next statement parsed as with many interpreters? If it's the latter it would mean one has to run each branch just to see if the syntax is correct. Is that true? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] PL/pgSQL question
Michael Meskes wrote: Hi, is a pl/pgSQL function completely parsed once? Or is only the next statement parsed as with many interpreters? If it's the latter it would mean one has to run each branch just to see if the syntax is correct. Is that true? Michael If the docs are true, than the plain PL/pgSQL code is parsed at once, but SQL expressions and queries are not prepared until the branch is used. But read for yourself. To quote from Programmers Guide (Chapter 23, Section 1): The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first time the function is called (within any one backend process). The instruction tree fully translates the PL/pgSQL statement structure, but individual SQL expressions and SQL queries used in the function are not translated immediately. As each expression and SQL query is first used in the function, the PL/pgSQL interpreter creates a prepared execution plan (using the SPI manager's SPI_prepare and SPI_saveplan functions). Subsequent visits to that expression or query re-use the prepared plan. Thus, a function with conditional code that contains many statements for which execution plans might be required, will only prepare and save those plans that are really used during the lifetime of the database connection. This can provide a considerable savings of parsing activity. A disadvantage is that errors in a specific expression or query may not be detected until that part of the function is reached in execution. Regards, Michael ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PL/pgSQL question
Michael Paesold wrote: Michael Meskes wrote: Hi, is a pl/pgSQL function completely parsed once? Or is only the next statement parsed as with many interpreters? If it's the latter it would mean one has to run each branch just to see if the syntax is correct. Is that true? Michael If the docs are true, than the plain PL/pgSQL code is parsed at once, but SQL expressions and queries are not prepared until the branch is used. But read for yourself. That's the way I implemented it. Unless someone changed it, the documentation is correct. Someone might think now it'd be at least handy to have a mechanism to enforce parsing of all expressions and queries for debugging purposes. But that's not that easy. As soon as you use for example a record variable, each reference to one of the result row columns is of unknown datatype until that query is actually executed. You cannot parse an SQL query with unknown parameters via SPI. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Backend crash (long)
I've definitely seen errors from including vacuum and/or analyze
statements in functions, I think I've seen crashes too. If you check the
docs I'm pretty sure they mention the specifics of not being able to use
such statements.
Robert Treat
On Wed, 2002-09-18 at 04:09, Michael Paesold wrote:
Hi all,
I have written a test function, that will create a sequence and a table,
than insert one million rows into the table, analyze the table and create an
index on one of the columns.
(so this will all happen inside on transaction)
After doing that, the backend will crash.
(but the data will be inserted)
If I comment out the table analyzing and the create index (I have not tested
which on leads to the crash), everything works fine. I have sent a copy of
the error log, the psql session, the function and some parts of my
postgresql.conf file.
My system is RedHat 7.2, Kernel 2.4.9-34, glibc-2.2.4, gcc 2.96, PostgreSQL
7.2.2 built from source.
If you want, I could try other combinations of create/insert/analyze etc. to
test the exact steps needed to crash the backend.
I know what I am doing is not really standard. This was rather a stability
test of postgres :). What do you think about this all?
Best Regards,
Michael Paesold
-- logfile:
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'bench_big_pkey' for table 'bench_big'
DEBUG: recycled transaction log file 009F
[...skipping: recycled transaction log file 00A0 to
00AE]
DEBUG: recycled transaction log file 00B0
DEBUG: Analyzing bench_big
DEBUG: server process (pid 13840) was terminated by signal 11
DEBUG: terminating any other active server processes
DEBUG: all server processes terminated; reinitializing shared memory and
semaphores
DEBUG: database system was interrupted at 2002-09-17 11:45:56 CEST
DEBUG: checkpoint record is at 0/B41170A4
DEBUG: redo record is at 0/B400DF34; undo record is at 0/0; shutdown FALSE
DEBUG: next transaction id: 96959; next oid: 6282462
DEBUG: database system was not properly shut down; automatic recovery in
progress
DEBUG: redo starts at 0/B400DF34
DEBUG: ReadRecord: record with zero length at 0/B495F754
DEBUG: redo done at 0/B495F730
DEBUG: recycled transaction log file 00B2
DEBUG: recycled transaction log file 00B1
DEBUG: recycled transaction log file 00B3
DEBUG: database system is ready
The first time I tried the insert, there was an additional notice from
another backend, just after the line DEBUG: terminating any other active
server processes:
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
-- in psql:
billing=# select create_benchmark ();
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'bench_big_pkey' for table 'bench_big'
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!# \c
Password:
You are now connected to database billing as user billing.
billing=# select real_time from bench_big where int_id in (1, 100);
real_time
---
2002-09-17 11:32:22.63334+02
2002-09-17 11:46:16.601282+02
(2 rows)
-- all rows have definatly been inserted!
-- the trigger function:
CREATE OR REPLACE FUNCTION create_benchmark () RETURNS BOOLEAN AS '
DECLARE
char100 VARCHAR :=
\'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZäöüÄÖÜß1234567890!§$%
/()=?+*#|-_,;.:^°{}´`[]\';
r1 INTEGER;
r2 INTEGER;
r3 INTEGER;
BEGIN
CREATE SEQUENCE bench_seq;
CREATE TABLE bench_big (
int_id INTEGER NOT NULL default nextval(\'bench_seq\'),
bigint_id BIGINT NOT NULL,
sometext1 VARCHAR (50),
sometext2 VARCHAR (50),
sometext3 VARCHAR (50),
trx_time TIME WITHOUT TIME ZONE NOT NULL default CURRENT_TIME,
trx_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL default
CURRENT_TIMESTAMP,
trx_date DATE NOT NULL default CURRENT_DATE,
real_time TIMESTAMP NOT NULL default timeofday(),
someboolean1 BOOLEAN NOT NULL,
someboolean2 BOOLEAN NOT NULL,
PRIMARY KEY (int_id)
);
FOR i IN 1..100 LOOP
r1 = CAST( RANDOM() * 49 AS INTEGER );
r2 = CAST( RANDOM() * 49 AS INTEGER );
r3 = CAST( RANDOM() * 49 AS INTEGER );
INSERT INTO bench_big
(bigint_id, sometext1, sometext2, sometext3, someboolean1,
someboolean2)
VALUES (
CAST(RANDOM() * 100 AS BIGINT),
SUBSTR(char100, 50, 49), -- this should be r1, r1
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
[ back to thinking about this patch ] Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane dijo: One corner case is that I think we currently allow create table p (f1 int); create table c (f1 int) inherits(p); In this case, c.f1.attisinherited count is 2; thus when I drop f1 from p, it is not dropped from c. That seems right, but the problem I have with it is that the resulting state of c.f1 is attisinherited = 1. This means that you cannot drop c.f1. It seems to me that we should have this behavior: create table p (f1 int); create table c (f1 int not null) inherits(p); drop column c.f1; -- should be rejected since c.f1 is inherited drop column p.f1; -- c.f1 is still there, but no longer inherited drop column c.f1; -- should succeed; but will fail with patch as given as compared to create table p (f1 int); create table c () inherits(p); drop column c.f1; -- should be rejected since c.f1 is inherited drop column p.f1; -- c.f1 is dropped now, since there is no local definition for it And if you aren't confused yet, what about non-recursive drops of p.f1 (ie, alter table ONLY p drop column f1)? This case seems clear: create table p (f1 int); create table c () inherits(p); drop column c.f1; -- should be rejected since c.f1 is inherited drop ONLY column p.f1; -- c.f1 is NOT dropped, but must now be considered non-inherited drop column c.f1; -- should succeed And then I think we should say create table p (f1 int); create table c (f1 int not null) inherits(p); drop column c.f1; -- should be rejected since c.f1 is inherited drop ONLY column p.f1; -- c.f1 is still there, but no longer inherited drop column c.f1; -- should succeed I am not sure how to make all four of these cases work. We might need two fields :-( ... a locally defined boolean and a number of times inherited counter. This seems like overkill though. If we don't have the locally defined boolean then I think we have to make the first case work like so: create table p (f1 int); create table c (f1 int not null) inherits(p); drop column p.f1; -- c.f1 GOES AWAY, because its inherit count went to zero Is this reasonable behavior? I'm not sure. You could probably argue it either way. Another interesting case is multiple inheritance. create table p1 (f1 int); create table p2 (f1 int); create table c () inherits(p1, p2); drop ONLY column p1.f1; drop column p2.f1; After this sequence, what is the state of c.f1? Is it still there? Should it be? If it is still there, will it be possible to get rid of it with drop column c.f1? What if we did DROP ONLY on *both* ancestors? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] PGXLOG variable worthwhile?
On Thu, 19 September 2002, Marc G. Fournier wrote:
On Thu, 19 Sep 2002, Robert Treat wrote:
gt; Well, as with most (all?) GUC variables, wouldn't you have the option of
gt; doing postmaster -o quot;pgxlog=/dev/nullquot; and have the same
functionality
gt; as -X ?
True, but then that negates the whole argument about not having a command
line option, no? Which I believe was the whole argument on this ... no?
Well, I think it negates the the whole reason to have a specifc command line
option for this. Personally I'd like to see all (well, most) of the command line
options to go away. We still get people emailing us because they cant get
phpPgAdmin to work on a system because they forgot to start it with -i. I try to
explain to them to edit the tcpip setting in the postgresql.conf, but many have
never heard of that setting.
gt; Shouldn't this work the other way around? Use what's in the conf file
gt; unless I explicitly state otherwise? IIRC that's how it works with -i
God, I wish I had thought to note it at the time ... one of the things I
did when I dove into this was to check how various Unix daemons were doing
it, now I can't recall which I was looking at that mentioned the config
file overriding the command line options, but you are correct, the command
line should override the conf file ...
Robert Treat
--
LAMP :: Linux Apache {middleware} PostgreSQL
---(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] Inconsistent Conversion Names
On Thu, 19 Sep 2002, Peter Eisentraut wrote: Tatsuo Ishii writes: The conversion named SJIS is different from IANA's shift_jis. It actually matches Windows-31J in IANA, which is too ugly to being emploied as our conversion name, IMO. OK I agree with win1250 - windows_1250, win1251 - windows_1251, but do not agree with renaming win866. There's no windows_866 in IANA. Maybe that should be ibm866? Is it ibm866 or are you wondering yourself? it's a total mess. I know CP866, CP-866,IBM866,IBM_866 IANA isn't a standard but a recommendation. glibc uses name mangling, so KOI8-R - koi8r Someone said that the conversion table is actually koi8r + koi8u, being different from IANA's koi8_r. Not sure though. I found mention in the archives by Oleg B. that it is in fact koi8_r. on my system (linux) I have ru_RU.KOI8-R locale. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PGXLOG variable worthwhile?
It seems all of this discussion misses the point. Either it has a large amount of impact and the idea gets rejected because of implementation issues, or it has little impact but it's nothing the core group wants to implement. If the problem is finding someone to implement it, it sounds like Justin has found such a person, so are we going to stand in his way while we wax poetic about OS religion and corporate philosophies or can he start submitting patches? Robert Treat On Mon, 2002-09-16 at 14:11, Justin Clift wrote: Peter Eisentraut wrote: Justin Clift writes: WinNT/2k/XP costs a few hundred dollars. MS SQL Server costs a few thousand dollars. The places that run Windows can be categorized into three camps: snip How about this? The places that run Windows can be categorised a number of different ways, depending on what you're looking for. 1) Places that have in-house staff that can do or learn everything. Many of these places are really small, some are not. PostgreSQL fits well here, Windows or not, as these people are prepared to learn how to use it best. 2) Companies that hire external IT services. Often the software implemented here will be dependent on outside sources of advice such as consultants, executives who take an interest in IT mags, etc. Look at Windows NT on the server in the first place. Microsoft leveraged the marketplace through making itself available then promoting the heck out of itself into the IT press, industry mags, etc. These places will be receptive to PostgreSQL as our reputation further becomes known and they can see where PostgreSQL will be useful to them. PostgreSQL on Win NT/2K/XP will definitely be of use to a sizable number of these businesses. 3) Companies who depend on multiple external sources of IT support. i.e. One reasonable sized enterprise here in Australia has over 450 *development* companies presently working on applications for their environment. Because of the scope of standardisation needed, they standardised on WinNT many years ago. It still works for them. They don't even have SP6 installed on their desktops as it breaks too many of the desktop applications. etc. These people are not clueless. They make strategic decisions when they're necessary, and it all comes down to flexibility, reliability, and cost. For some things they run Unix, or Windows, or Novell, or OS/390, or any number of other stuff. Because of the years of experience some of their support companies have with WinNT, it works reliably enough for them. They don't have the need to reboot once per week thing with their servers. These guys will become receptive to PostgreSQL too, and it will be in our favour to be able to demonstrate very good performance across all platforms that we can, not just our own *personally preferred* platforms. By giving them options when it doesn't take a *whole bunch of effort* to do so, we open up ways for PostgreSQL to be used that we haven't even thought of before. We all know this already. It wouldn't really surprise me greatly if at some point this proved beneficial to a non-Windows platform for some reason too. :-) Regards and best wishes, Justin Clift So where is the market? -- Peter Eisentraut [EMAIL PROTECTED] -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Bruce Momjian wrote: I am working with several groups getting the Win32 port ready for 7.4 and I have a few questions: What is the standard workaround for the fact that rename() isn't atomic on Win32? Do we need to create our own locking around the reading/writing of files that are normally updated in place using rename()? Visual C++ comes with the source to Microsoft's C library: rename() calls MoveFile() which will error if: 1. The target file exists 2. The source file is in use MoveFileEx() (not available on 95/98) can overwrite the target file if it exists. The Apache APR portability library uses MoveFileEx() to rename files if under NT/XP/2K vs. a sequence of : 1. CreateFile() to test for target file existence 2. DeleteFile() to remove the target file 3. MoveFile() to rename the old file to new under Windows 95/98. Of course, some other process could create the target file between 2 and 3, so their rename() would just error out in that situation. I haven't tested it, but I recall reading somewhere that MoveFileEx() has the ability to rename an opened file. I'm 99% sure MoveFile() will fail if the source file is open. OK, I downloaded APR and see in apr_file_rename(): if (MoveFileEx(frompath, topath, MOVEFILE_REPLACE_EXISTING | MOVEFILE_COPY_ALLOWED)) Looking at the entire APR function, they have lots of tests so it works on Win9X and wide characters. I think we will just use the APR as a guide in implementing the things we need. I think MoveFileEx() is the proper way to go; any other solution requires loop tests for rename. I see the MoveFileEx manual page at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/movefile.asp Second, when you unlink() a file on Win32, do applications continue accessing the old file contents if they had the file open before the unlink? unlink() just calls DeleteFile() which will error if: 1. The target file is in use CreateFile() has the option: FILE_FLAG_DELETE_ON_CLOSE which might be able to be used to simulate traditional unlink() behavior. No, that flag isn't going to help us. I wonder what MoveFileEx does if the target file exists _and_ is open by another user? I don't see any loop in that Win32 rename() routine, and I looked at the Unix version of apr_file_rename and its just a straight rename() call. -- 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] PGXLOG variable worthwhile?
Robert Treat wrote: It seems all of this discussion misses the point. Either it has a large amount of impact and the idea gets rejected because of implementation issues, or it has little impact but it's nothing the core group wants to implement. If the problem is finding someone to implement it, it sounds like Justin has found such a person, so are we going to stand in his way while we wax poetic about OS religion and corporate philosophies or can he start submitting patches? Well, I have Win32 patches here I am reviewing. I think I can say that the changes are minimal and probably will be accepted for addition into 7.4. I am actually surprised at how little is required. Right now, 7.4 is targeted with point-in-time recovery and Win32. And, in fact, both patches are almost ready for inclusion into CVS, so we may find that 7.4 has a very short release cycle. -- 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] [GENERAL] Memory Errors...
Ian Harding [EMAIL PROTECTED] writes: It is pltcl [not plpgsql] Quick, minor point, in the manner of a question: Why is the pltcl directory called tcl where all the other pls are pllanguage? That's in src/pl of course. Also in my anoncvs fetch which is a few weeks old now being from the day before beta freeze. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Memory Errors...
Nigel J. Andrews wrote: Ian Harding [EMAIL PROTECTED] writes: It is pltcl [not plpgsql] Quick, minor point, in the manner of a question: Why is the pltcl directory called tcl where all the other pls are pllanguage? I asked the same question a while ago. I asked about changing it but others didn't want the change. It is hard to rename stuff in CVS and keep proper history. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Memory Errors...
Nigel J. Andrews [EMAIL PROTECTED] writes: Why is the pltcl directory called tcl where all the other pls are pllanguage? Consistency? We don't need no steenking consistency! Personally I'd prefer to remove the pl prefix from the other subdirectories of src/pl/ ... it seems redundantly wasted excessive typing ;-) And I'd have preferred to flatten out the src/ subdirectory of src/pl/[pl]pgsql, which is likewise redundant and inconsistent with the other PLs. However, it's fairly painful to make any such change without losing the CVS version history for the moved files, which is Not a Good Thing. Or breaking our ability to reconstitute old releases from the CVS tree, which is Much Worse. So I'm afraid we're stuck with this historical mischance. 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: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Tom Lane writes: Yeah, we should do something with that. Are people okay with the idea of CREATE LANGUAGE, etc, retroactively changing prorettype from OPAQUE to the correct thing? Seems like an appropriate time to throw a notice, though. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] PGXLOG variable worthwhile?
Marc G. Fournier writes: My point is, the functionality is there, and should be documented properly ... encourage ppl to use the GUC setting in postmaster.conf, but just because you can't grasp that some of us *like* to use command line args, don't remove such functionality ... Top secret information: If it's made a GUC variable, it's automatically a command-line option. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: AIX compilation problems (was Re: [HACKERS] Proposal ...)
Zeugswetter Andreas SB SD writes: configure somehow thinks it needs to #define _LARGE_FILES though, which then clashes with pg_config.h's _LARGE_FILES. I think the test needs to #include unistd.h . _LARGE_FILES is defined because it's necessary to make off_t 64 bits. If you disagree, please post compiler output. and mb conversions (pg_ascii2mic and pg_mic2ascii not found in the postmaster and not included from elsewhere) shared libs on AIX need to be able to resolve all symbols at linkage time. Those two symbols are in backend/utils/SUBSYS.o but not in the postgres executable. My guess is, that they are eliminated by the linker ? Do they need an extern declaration ? They are defined in backend/utils/mb/conv.c and declared in include/mb/pg_wchar.h. They're also linked into the postmaster. I don't see anything unusual. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Optimizer generates bad plans.
While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. Kris Jurka The query tries to determine what foreign keys exists between the following tables. create table people (id int4 primary key, name text); create table policy (id int4 primary key, name text); create table users (id int4 primary key, people_id int4, policy_id int4, CONSTRAINT people FOREIGN KEY (people_id) references people(id), constraint policy FOREIGN KEY (policy_id) references policy(id)); SELECT DISTINCT n.nspname as pnspname, n2.nspname as fnspname, c.relname as prelname, c2.relname as frelname, t.tgconstrname, a.attnum as keyseq, ic.relname as fkeyname, t.tgdeferrable, t.tginitdeferred, t.tgnargs,t.tgargs, p1.proname as updaterule, p2.proname as deleterule FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2, pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1, pg_catalog.pg_proc p2, pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE (t.tgrelid=c.oid AND t.tgisconstraint AND t.tgconstrrelid=c2.oid AND t.tgfoid=p1.oid and p1.proname like 'RI\_FKey\_%\_upd') AND (t1.tgrelid=c.oid and t1.tgisconstraint and t1.tgconstrrelid=c2.oid AND t1.tgfoid=p2.oid and p2.proname like 'RI\_FKey\_%\_del') AND i.indrelid=c.oid AND i.indexrelid=ic.oid AND ic.oid=a.attrelid AND i.indisprimary AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid AND c2.relname='users' ORDER BY prelname,keyseq ; Unique (cost=1.06..1.10 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) - Sort (cost=1.06..1.07 rows=1 width=594) (actual time=845786.00..845786.00 rows=2 loops=1) Sort Key: c.relname, a.attnum, n.nspname, n2.nspname, c2.relname, t.tgconstrname, ic.relname, t.tgdeferrable, t.tginitdeferred, t.tgnargs, t.tgargs, p1.proname, p2.proname - Merge Join (cost=1.03..1.05 rows=1 width=594) (actual time=844522. 00..845786.00 rows=2 loops=1) Merge Cond: (outer.tgconstrrelid = inner.tgconstrrelid) Join Filter: ((inner.tgfoid = outer.oid) AND (inner.tgrelid = outer.oid)) - Nested Loop (cost=0.00..27709.41 rows=1 width=510) (actual time=844522.00..845786.00 rows=12 loops=1) Join Filter: ((inner.indexrelid = outer.oid) AND (inner.indrelid = outer.oid)) - Nested Loop (cost=0.00..27706.67 rows=1 width=502) (actual time=843375.00..843954.00 rows=10620 loops=1) Join Filter: ((inner.tgconstrrelid = outer.oid) AND (outer.relnamespace = inner.oid)) - Index Scan using pg_class_oid_index on pg_class c2 (cost=0.00..15.67 rows=1 width=72) (actual time=1.00..1.00 rows=1 loops=1) Filter: (relname = 'users'::name) - Materialize (cost=27690.93..27690.93 rows=4 width=430) (actual time=843374.00..843781.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27690.93 rows=4 width=430) (actual time=614674.00..843125.00 rows=42480 loops=1) - Nested Loop (cost=0.00..27689.85 rows=1 width=362) (actual time=614674.00..842368.00 rows=10620 loops=1) Join Filter: ((outer.tgfoid = inner.oid) AND (outer.tgrelid = inner.oid)) - Seq Scan on pg_trigger t1 (cost=0.00..1.02 rows=1 width=12) (actual time=0.00..1.00 rows=6 loops=1) Filter: tgisconstraint - Materialize (cost=26180.37..26180.37 rows=100564 width=350) (actual time=83492.50..135359.33 rows=3637350 loops=6) - Nested Loop (cost=0.00..26180.37 rows=100564 width=350) (actual time=68978.00..481414.00 rows=3637350 loops=1) Join Filter: (inner.relnamespace = outer.oid) - Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=68) (actual time=0.00..0.00 rows=4 loops=1) - Materialize (cost=5287.78..5287.78 rows=100564 width=282) (actual time=17273.75..110243.25 rows=3637350 loops=4)
Re: [HACKERS] Optimizer generates bad plans.
Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Optimizer generates bad plans.
Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Interesting. The inconsistency you're seeing is a result of GEQO. I would have hoped that it would have produced a better quality plan more often, but apparently not. On my system, the regular query optimizer handily beats GEQO for this query: it produces more efficienty query plans 100% of the time and takes less time to do so. For *this* query at least, raising geqo_threshold would be a good idea, but that may not be true universally. I thought someone might be interested in a test case for the optimizer. Thanks, it's a useful query -- I've been meaning to take a look at GEQO for a while now... Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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] Optimizer generates bad plans.
On Thu, 19 Sep 2002, Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] writes: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. Hm, does an ANALYZE help? Yes, it does, but I don't understand why. The query is entirely against pg_catalog tables which have had all of three tables added to them. How can the new ANALYZE stats be significantly different than what came from the ANALYZED template1. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer generates bad plans.
Neil Conway [EMAIL PROTECTED] writes: Interesting. The inconsistency you're seeing is a result of GEQO. I would have hoped that it would have produced a better quality plan more often, but apparently not. On my system, the regular query optimizer handily beats GEQO for this query: it produces more efficienty query plans 100% of the time and takes less time to do so. For *this* query at least, raising geqo_threshold would be a good idea, but that may not be true universally. The current GEQO threshold was set some time ago; since then, the regular optimizer has been improved while the GEQO code hasn't been touched. It might well be time to ratchet up the threshold. Anyone care to do some additional experiments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposal for resolving casting issues
Will the new casting stuff address this kind of annoyance? usa=# select average(octet_length(val)) from users_sessions; ERROR: Function 'average(int4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres 7.2.2 Segment Error
DELETE FROM users_sessions WHERE changed ('now'::timestamp - '1440
minutes'::interval) AND name = 'fhnid';
What does EXPLAIN show as the plan for that query? I'm guessing an
indexscan, and that the error was caused by reading a broken item
pointer from the index. (1342198864 = hex 50005450, which sure looks
like the upper 5 shouldn't be there ... how big is the table, anyway?)
NOTICE: QUERY PLAN:
Index Scan using users_sessions_cha_name_idx on users_sessions
(cost=0.00..12738.07 rows=1275 width=6) (actual time=231.74..239.39 rows=2
loops=1)
Total runtime: 239.81 msec
EXPLAIN
The size of the table:
canaveral# ls -al 44632
-rw--- 1 pgsql pgsql 357130240 Sep 19 18:52 44632
The size of the index:
canaveral# ls -al 7331245
-rw--- 1 pgsql pgsql 8151040 Sep 19 18:51 7331245
Holy crap - that table is huge. It's like it's never had a vacuum full sort
of thing. Going select count(*) takes _ages_ even though there's only 1451
rows in it - and not particularly large rows. Actually, the longest text
entry is 3832 characters and the average is 677.
The sessions table holds normal site session data, like a uid, username,
some other stuff, etc. However entries older than two hours or so get
deleted. We VACUUM everynight, so why is the on-disk relation growing so
huge?
However, I cannot repeat the error now. Is this a bug in postgres
somewhere.
If the broken item pointer were indeed in the index, I'd expect it to be
100% repeatable. I'm wondering about flaky memory or some such. Have
you run any hardware diagnostics?
No - the thought occured to me that there might be something wacky going on.
We've had problems with users_sessions before. Remember when I mailed about
vacuum failing on it before? You suggested doing a select for update on the
relation and that fixed it.
Chris
---(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] DROP COLUMN misbehaviour with multiple inheritance
That seems right, but the problem I have with it is that the resulting state of c.f1 is attisinherited = 1. This means that you cannot drop c.f1. It seems to me that we should have this behavior: Has anyone given much thought as to perhaps we could just drop multiple inheritance from Postgres? There are people using single inheritance - but how many actually use multiple inheritance? If we dumped it we could use the proposed all-child-tables-in-one-relation idea, and everything would become very easy... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Christopher Kings-Lynne wrote: That seems right, but the problem I have with it is that the resulting state of c.f1 is attisinherited = 1. This means that you cannot drop c.f1. It seems to me that we should have this behavior: Has anyone given much thought as to perhaps we could just drop multiple inheritance from Postgres? There are people using single inheritance - but how many actually use multiple inheritance? If we dumped it we could use the proposed all-child-tables-in-one-relation idea, and everything would become very easy... I am for it. Multiple inheritance is more of a mess than a help. Just look at C++. Everyone is moving away from multiple inheritance for that reason. -- 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] Proposal for resolving casting issues
Doh - I'm stupid. Ignore my question :) Helps if you spell 'average' as 'avg' :) Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher Kings-Lynne Sent: Friday, 20 September 2002 10:03 AM To: Tom Lane; Zeugswetter Andreas SB SD Cc: Bruce Momjian; [EMAIL PROTECTED] Subject: Re: [HACKERS] Proposal for resolving casting issues Will the new casting stuff address this kind of annoyance? usa=# select average(octet_length(val)) from users_sessions; ERROR: Function 'average(int4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for resolving casting issues
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Will the new casting stuff address this kind of annoyance? usa=# select average(octet_length(val)) from users_sessions; ERROR: Function 'average(int4)' does not exist regression=# select * from pg_proc where proname = 'average'; proname | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proargtypes | prosrc | probin | proacl -+--+--+-+--+---+-+---+-+--++-+++ (0 rows) No, I think you'll get the same error ... 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] Win32 rename()/unlink() questions
Bruce Momjian wrote: Second, when you unlink() a file on Win32, do applications continue accessing the old file contents if they had the file open before the unlink? unlink() just calls DeleteFile() which will error if: 1. The target file is in use CreateFile() has the option: FILE_FLAG_DELETE_ON_CLOSE which might be able to be used to simulate traditional unlink() behavior. No, that flag isn't going to help us. I wonder what MoveFileEx does if the target file exists _and_ is open by another user? I don't see any loop in that Win32 rename() routine, and I looked at the Unix version of apr_file_rename and its just a straight rename() call. This says that if the target is in use, it is overwritten: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q140570; While I think that is good news, does it open the problem of other readers reading partial updates to the file and therefore seeing garbage. Not sure how to handle that, nor am I even sure how I would test it. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Bruce Momjian [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: Has anyone given much thought as to perhaps we could just drop multiple inheritance from Postgres? I am for it. Multiple inheritance is more of a mess than a help. I'm not agin it ... but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. This patch is off the must fix for 7.3 list, no? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: Has anyone given much thought as to perhaps we could just drop multiple inheritance from Postgres? I am for it. Multiple inheritance is more of a mess than a help. I'm not agin it ... but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. This patch is off the must fix for 7.3 list, no? I don't think a few days before beta2 is the time to be making such decisions. I think we have to keep the course and open the discussion in 7.4. Sorry. -- 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] DROP COLUMN misbehaviour with multiple inheritance
I am for it. Multiple inheritance is more of a mess than a help. I'm not agin it ... but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. This patch is off the must fix for 7.3 list, no? Multiple inheritance patches should go in for 7.3, since we support multiple inheritance in 7.3. However, I think thought should be put into removing multiple inheritance in 7.4 - after a user survey perhaps. If removing multiple inheritance means we can have perfece, indexable single inheritance then I think it's worth it. Unless the spec calls for multiple inheritance of course. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Can I buy an extra day or two? I'm in DC till Saturday then there's the trip home. How 'bout a wednesday beta release? On Thu, 19 Sep 2002, Marc G. Fournier wrote: On Wed, 18 Sep 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: ... I'm going to do up a beta2 on Friday due to the number changes that have been committed over the past 2 weeks ... I want to review and apply Alvaro's attisinherited fix before we go beta2. I think I can get that done tomorrow. I can't recall any other initdb-forcing fixes in the pipeline; Bruce, do you? Which is not to say we don't have a ton of known bugs to fix... I'd lean towards a Monday-ish beta2 myself. 'k, then let's go with a Sunday night packaging, Monday announce, so that we have beta2 testing starting right at the beginning of the week ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not agin it ... but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. This patch is off the must fix for 7.3 list, no? I don't think a few days before beta2 is the time to be making such decisions. The decision at hand is whether to apply a patch. You cannot say we're not deciding now, because that is a decision... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: I'm not agin it ... but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. This patch is off the must fix for 7.3 list, no? I don't think a few days before beta2 is the time to be making such decisions. The decision at hand is whether to apply a patch. You cannot say we're not deciding now, because that is a decision... Yes. I am saying we should not assume we are going to remove multiple inheritance. We should apply the patch and make things a good as they can be for 7.3. -- 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] DROP COLUMN misbehaviour with multiple inheritance
The decision at hand is whether to apply a patch. You cannot say we're not deciding now, because that is a decision... Yes. I am saying we should not assume we are going to remove multiple inheritance. We should apply the patch and make things a good as they can be for 7.3. I think the patch should be applied. That way people who are using multiple inheritance (if there are any) can know that they have a vaguely bug free implementation in 7.3 until they redo their stuff for 7.4. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DROP COLUMN misbehaviour with multiple inheritance
Tom Lane [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: Has anyone given much thought as to perhaps we could just drop multiple inheritance from Postgres? I am for it. Multiple inheritance is more of a mess than a help. I'm not agin it I'm abstaining. but if that's the lay of the land then we have no need to apply a last-minute catalog reformatting to fix a multiple-inheritance bug. The catalog format has changed since beta1 anyway due to the casting changes, right? (not to mention the split - split_part change). If that's the case, I don't see a good reason not to include the fix, provided it's reasonably low-risk. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgres 7.2.2 Segment Error
On Fri, 20 Sep 2002, Christopher Kings-Lynne wrote:
DELETE FROM users_sessions WHERE changed ('now'::timestamp - '1440
minutes'::interval) AND name = 'fhnid';
What does EXPLAIN show as the plan for that query? I'm guessing an
indexscan, and that the error was caused by reading a broken item
pointer from the index. (1342198864 = hex 50005450, which sure looks
like the upper 5 shouldn't be there ... how big is the table, anyway?)
NOTICE: QUERY PLAN:
Index Scan using users_sessions_cha_name_idx on users_sessions
(cost=0.00..12738.07 rows=1275 width=6) (actual time=231.74..239.39 rows=2
loops=1)
Total runtime: 239.81 msec
EXPLAIN
The size of the table:
canaveral# ls -al 44632
-rw--- 1 pgsql pgsql 357130240 Sep 19 18:52 44632
This seems remarkably large. Does pg_filedump reveal anything of interest?
Gavin
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] BLOB
Christopher Kings-Lynne writes: Is there some reason why we didn't call text 'clob' and bytea 'blob'? At the time our types were created there was no standard defining the other types. or at least add aliases? Mapping clob to text might be OK, but blob and bytea have totally different input formats. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GRANT EXECUTE
Christopher Kings-Lynne writes: Should someone just go though contrib/ and add GRANT EXECUTE on everything? Seems pointless doing it ad hoc by the maintainer as it is at the moment...? Please. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Memory Errors...
On Thu, 19 Sep 2002, Joe Conway wrote: Tom Lane wrote: I said: Yeah, I see very quick memory exhaustion also :-(. Looks like the spi_exec call is the culprit, but I'm not sure exactly why ... anyone have time to look at this? On looking a little more closely, it's clear that pltcl_SPI_exec() should be, and is not, calling SPI_freetuptable() once it's done with the tuple table returned by SPI_exec(). This needs to be done in all the non-elog code paths after SPI_exec has returned SPI_OK_SELECT. pltcl_SPI_execp() has a similar problem, and there may be comparable bugs in other pltcl routines (not to mention other sources of memory leaks, but I think this is the problem for your example). I have no time to work on this right now; any volunteers out there? I can give it a shot, but probably not until the weekend. I haven't really followed this thread closely, and don't know tcl very well, so it would help if someone can send me a minimal tcl function which triggers the problem. I can probably take a look at this tomorrow, already started by looking at the pltcl_SPI_exec routine. I think a quick glance at ...init_unknown() also shows a lack of tuptable freeing. -- Nigel J. Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Yeah, we should do something with that. Are people okay with the idea of CREATE LANGUAGE, etc, retroactively changing prorettype from OPAQUE to the correct thing? Seems like an appropriate time to throw a notice, though. Of course. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [GENERAL] Memory Errors...
Nigel J. Andrews wrote: On Thu, 19 Sep 2002, Joe Conway wrote: I can give it a shot, but probably not until the weekend. I haven't really followed this thread closely, and don't know tcl very well, so it would help if someone can send me a minimal tcl function which triggers the problem. I can probably take a look at this tomorrow, already started by looking at the pltcl_SPI_exec routine. I think a quick glance at ...init_unknown() also shows a lack of tuptable freeing. OK -- let me know if you can't find the time and I'll jump back in to it. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Yeah, we should do something with that. Are people okay with the idea of CREATE LANGUAGE, etc, retroactively changing prorettype from OPAQUE to the correct thing? Seems like an appropriate time to throw a notice, though. Of course. Now that we have additional elog levels, is it a NOTICE or a WARNING. I am leaning to the latter. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] GRANT EXECUTE
Christopher Kings-Lynne wrote: Hi, Should someone just go though contrib/ and add GRANT EXECUTE on everything? Seems pointless doing it ad hoc by the maintainer as it is at the moment...? Added to open item list: Add GRANT EXECUTE to all /contrib functions -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Optimizer generates bad plans.
Congratulations. That is the largest plan I have ever seen. ;-) --- Kris Jurka wrote: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Postgres 7.2.2 Segment Error
Index Scan using users_sessions_cha_name_idx on users_sessions (cost=0.00..12738.07 rows=1275 width=6) (actual time=231.74..239.39 rows=2 loops=1) Total runtime: 239.81 msec EXPLAIN The size of the table: canaveral# ls -al 44632 -rw--- 1 pgsql pgsql 357130240 Sep 19 18:52 44632 This seems remarkably large. Does pg_filedump reveal anything of interest? Where on earth do I find that? BTW - I want to vacuum full this table but I'm holding off until someone like Tom tells me there's nothing more to be gained from it... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Bruce Momjian wrote: Mike Mascari wrote: I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. I was focusing on handling of pg_pwd and other config file that are written by various backend while other backends are reading them. The actual data files should be OK because we have an exclusive lock when we are adding/removing them. OK. So you want to test: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 renames foo to bar 4. Process 2 can safely read from its open file handle Is that what you want tested? I have a small Win32 app ready to test. Just let me know the scenarios... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Bruce Momjian wrote: Mike Mascari wrote: I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. I was focusing on handling of pg_pwd and other config file that are written by various backend while other backends are reading them. The actual data files should be OK because we have an exclusive lock when we are adding/removing them. OK. So you want to test: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 renames foo to bar 4. Process 2 can safely read from its open file handle Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Is that correct? Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Win32 rename()/unlink() questions
Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 rename()/unlink() questions
On Fri, 20 Sep 2002, Mike Mascari wrote: Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Does a sequence like Process 1 opens foo Process 2 opens foo Process 1 creates bar Process 1 renames foo to something - where something is generated to not overlap an existing file Process 1 renames bar to foo Process 2 continues reading let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) ---(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] Win32 rename()/unlink() questions
Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: Bruce Momjian wrote: Mike Mascari wrote: Actually, looking at the pg_pwd code, you want to determine a way for: 1. Process 1 opens foo 2. Process 2 opens foo 3. Process 1 creates bar 4. Process 1 renames bar to foo 5. Process 2 can continue to read data from the open file handle and get the original foo data. Yep, that's it. So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Does a sequence like Process 1 opens foo Process 2 opens foo Process 1 creates bar Process 1 renames foo to something - where something is generated to not overlap an existing file Process 1 renames bar to foo Process 2 continues reading let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) Yes! Indeed that does work. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 rename()/unlink() questions
On Fri, 20 Sep 2002, Mike Mascari wrote: Stephan Szabo wrote: On Fri, 20 Sep 2002, Mike Mascari wrote: So far, MoveFileEx(foo, bar, MOVEFILE_REPLACE_EXISTING) returns Access Denied when Process 1 attempts the rename. But I'm continuing to investigate the possibilities... Does a sequence like Process 1 opens foo Process 2 opens foo Process 1 creates bar Process 1 renames foo to something - where something is generated to not overlap an existing file Process 1 renames bar to foo Process 2 continues reading let you do the replace and keep reading (at the penalty that you've now got to have a way to know when to remove the various somethings) Yes! Indeed that does work. Thinking back, I think that may still fail on Win95 (using MoveFile). Once in the past I had to work on (un)installers for Win* and I vaguely remember Win95 being more strict than Win98 but that may just have been with moving the executable you're currently running. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Beta2 on Friday Morning (Was: Re: [HACKERS] Open 7.3 items)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Yeah, we should do something with that. Are people okay with the idea of CREATE LANGUAGE, etc, retroactively changing prorettype from OPAQUE to the correct thing? Seems like an appropriate time to throw a notice, though. Of course. Now that we have additional elog levels, is it a NOTICE or a WARNING. I am leaning to the latter. NOTICE seems sufficient to me. 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] Optimizer generates bad plans.
Well I was really hoping pg_constraint would solve all my problems, but since contrib/array is not installed by default the conkeys and confkeys columns aren't terribly useful because they can't be joined to pg_attribute. Also there is not a column to tell you the unique constraint that supports a given foreign key constraint. See my post to bugs: http://fts.postgresql.org/db/mw/msg.html?mid=1074855 Kris Jurka On Thu, 19 Sep 2002, Bruce Momjian wrote: Congratulations. That is the largest plan I have ever seen. ;-) --- Kris Jurka wrote: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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] Optimizer generates bad plans.
Maybe not nice, but there's only 32 (64 now?) of them... JOIN pg_attribute WHERE attnum IN (conkeys[1], conkeys[2], conkeys[3], ..., conkeys[32]) Great fun... On Thu, 2002-09-19 at 18:31, Kris Jurka wrote: Well I was really hoping pg_constraint would solve all my problems, but since contrib/array is not installed by default the conkeys and confkeys columns aren't terribly useful because they can't be joined to pg_attribute. Also there is not a column to tell you the unique constraint that supports a given foreign key constraint. See my post to bugs: http://fts.postgresql.org/db/mw/msg.html?mid=1074855 Kris Jurka On Thu, 19 Sep 2002, Bruce Momjian wrote: Congratulations. That is the largest plan I have ever seen. ;-) --- Kris Jurka wrote: While adding schema support to the JDBC Driver, I came across a query which occasionally generates some spectacularly bad plans. I have attached the query and explain analyze outputs against today's cvs head for queries that take between 9 and 845941 msec. In the JDBC Driver I will specify a reasonable join order using explicit JOINs, but I thought someone might be interested in a test case for the optimizer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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 -- Rod Taylor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] please apply patch to contrib/ltree
Patch applied. Thanks. --- Oleg Bartunov wrote: Tiny patch fixing small documentation typo. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Win32 rename()/unlink() questions
Bruce Momjian wrote: Bruce Momjian wrote: unlink() just calls DeleteFile() which will error if: 1. The target file is in use CreateFile() has the option: FILE_FLAG_DELETE_ON_CLOSE which might be able to be used to simulate traditional unlink() behavior. No, that flag isn't going to help us. I wonder what MoveFileEx does if the target file exists _and_ is open by another user? I don't see any loop in that Win32 rename() routine, and I looked at the Unix version of apr_file_rename and its just a straight rename() call. This says that if the target is in use, it is overwritten: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q140570; I read the article and did not come away with that conclusion. The article describes using the MOVEFILE_DELAY_UNTIL_REBOOT flag, which was created for the express purpose of allowing a SETUP.EXE to remove itself, or rather tell Windows to remove it on the next reboot. Also, if you want the Win32 port to run in 95/98/ME, you can't rely on MoveFileEx(), you have to use MoveFile(). I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Win32 rename()/unlink() questions
Mike Mascari wrote: I read the article and did not come away with that conclusion. The article describes using the MOVEFILE_DELAY_UNTIL_REBOOT flag, which was created for the express purpose of allowing a SETUP.EXE to remove itself, or rather tell Windows to remove it on the next reboot. Also, if you want the Win32 port to run in 95/98/ME, you can't rely on MoveFileEx(), you have to use MoveFile(). I will do some testing with concurrency and let you know. But don't get your hopes up. This is one of the many advantages that TABLESPACEs have when more than one relation is stored in a single DATAFILE. There was Oracle for MS-DOS, after all.. I was focusing on handling of pg_pwd and other config file that are written by various backend while other backends are reading them. The actual data files should be OK because we have an exclusive lock when we are adding/removing them. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
