Re: [GENERAL] Re: Backups WAS: 2 gig file size limit
> I mentioned this on general a while ago. I'm not usually there/here, but subscribed recently to avoid annoying bounce messages from replies to messages cross posted to -hackers. I may not stay long, since the volume is hard to keep up with. > I had the problem when I dumped my 7.0.3 db to upgrade to 7.1. I had to > modify the dump because there were some 60 seconds in there. It was > obvious in the code in backend/utils/adt/datetime that it was using > sprintf to do the formatting, and sprintf was taking the the float the > represented the seconds and rounding it. > > select '2001-07-10 15:39:59.999'::timestamp; > ?column? > --- > 2001-07-10 15:39:60.00-04 > (1 row) Ah, right. I remember that now. Will continue to look at it... - Thomas ---(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] Re: Backups WAS: 2 gig file size limit
> > Have you observed otherwise? > Yes. Specifically timestamps are dumped in a way that (1) they lose > percision (2) sometimes have 60 in the seconds field which prevents the > dump from being restored. The loss of precision for timestamp data stems from conservative attempts to get consistant behavior from the data type. It is certainly not entirely successful, but changes would have to solve some of these problems without introducing more. I've only seen the "60 seconds problem" with earlier Mandrake distros which combined normal compiler optimizations with a "fast math" optimization, against the apparent advice of the gcc developers. What kind of system are you on, and how did you build PostgreSQL? Regards. - Thomas ---(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] Re: [DOCS] QUERY - Including the new LDP PostgreSQL HOWTO in the main PostgreSQL distribution
> > For a long time, I thougt HOWTO is docuement for "quick start". Isn't it? > I'm not sure there is an understood meaning of what a HOWTO actually it. > It's an explanation of how to do something, I guess... Generally I've found > (as in your example) that it's putting software together and making it work, > like Apache/SSL/Frontpage/something -- for that there is no single document > that describes how to set it up. I really don't think the PostgreSQL > installation guide could get much more simple -- why re-invent the wheel > here? To have adequate representation of PostgreSQL in the LDP docs set. It may be sufficient to package the installation guide with some Linux-specific FAQ information (which afaik is now obsolete and could be freshened and moved to the SGML-based docs). - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] unix timestamp
> how can i use unix timestamp as a data type?
You don't want to. Really.
> i thought that the timestamp data type use the unix timestamp but
> found out that it didnt.. :(
Right. One second resolution and limited range was considered a problem.
> do i need to use an int data type for holding unix timestamp or there
> is a data type which was build specialy for that?
I would suggest using the native timestamp type. You can convert back
and forth using various techniques, but you should find that the
examples below give you a good start:
lockhart=# select date_part('epoch',timestamp 'now');
date_part
---
997993780
lockhart=# select timestamp(integer '997993780');
timestamp
2001-08-16 20:29:40+00
hth
- Thomas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Max number of tables in a db?
> The web site lists a lot of the limitations for postgresql, but not the > max number of tables that are allowed in a given db. Anyone care to come > up with a number? It is limited by your filesystem's ability to handle large directories. I have noticed in the past (not with PostgreSQL per se) that ~3000 files in a directory is noticably slower that a typically small directory. afaik that is the only real limitation, so you can try some timing and performance tests using, for example, a perl script to create and access a large number of files. - Thomas ---(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: [GENERAL] PL/java?
... > Java stored procedures are the #1 most-desired-by-me feature for PostgreSQL. > Oracle and Sybase are examples of databases that have this feature already. ... How does java manage its resources when it is an embedded component? When it is standalone, the Sun JDK seems to want to have a fixed maximum amount of memory, as an example. I'd hate to have a nice 10MB PostgreSQL backend calling a 256MB jvm to execute a simple small program. The other embedded languages we have start small and can grow dynamically. But I don't know what is possible in this regard for Java... - Thomas ---(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: [GENERAL] Error: Bad Timestamp Format
> BTW, i don't know who thought of it but -dd-mm is a terrible format and > should be shot. Where does -dd-mm appear in PostgreSQL? What is the issue here? - Thomas ---(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] Performance problems with arithmetic operators
> Has anyone else experienced serious performance problems when using arithmetic > expressions in queries? ... > a and b are numeric(10, 3) numeric() is implemented with binary coded decimal representation, which is substantially slower than direct processor supported math involving just ints and floats. Try testing this with different types to see what happens. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] virtual filesystem atop a PostgreSQL database
> I am wondering whether anyone has already tried it, or if not, looking > for starting points as to how to go about doing it: I recall seeing an article in a magazine a couple of years ago by someone using PostgreSQL to implement a versioning file system on Linux. I *think* it was in Linux Journal. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] function compiled with g++
> Why the same function writen in C, compiled with gcc works OK, but compiled
> with g++, doesn't even load? Not mentioning those written in C++.
> i.e. loader reports undefined symbol: pg_detoast_datum__FP7varlena or some
> other undefined symbol...
C++ compilers do "name mangling" to allow function overloading. If you
are going to call a C++ routine from C or most other languages, you need
to wrap the declaration for the called function in the following:
extern "C" {
retval your_function_here(arg1,arg2);
}
hth
- Thomas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] CREATE USER
> I have to create users via an ODBC connection, but I get this error: > CREATE USER: may not be called in a transaction block > With psql I have no problems. Any body knows what is happening? Yeah. There are too many transaction block wrappers (BEGIN/COMMIT statements) in the ODBC driver. I'm not sure if we have patches for it yet... - Thomas
Re: [GENERAL] NOTICE: DateStyle is Postgres with Eropean convention
> and every time the servlets (via web) are accesing to posgres > database it shows, in the shell, this: > NOTICE: DateStyle is Postgres with European conventions Servlets? So are you using JDBC? Some of the interfaces (JDBC is one of them, afaik) explicitly set the date/time style because they are responsible for manipulating date/time strings coming back from the server, and need to ensure that these strings are in a known format. The notice comes from an explicit "SET DATESTYLE", and should be considered normal behavior when using some interfaces. Not all interfaces need to do this automatically, so you will have to be more specific on your exact scenerio to tell for sure whether you can safely supress this. - Thomas
Re: [GENERAL] Access Permission Denied
> Warning: PostgresSQL query failed: ERROR: userinfo: Permission denied.
> when I try to connect to a database over the web.
Your web interface is running as a specific user ("nobody"? Something
else??) and this user must be known to Postgres via the createuser
command.
> p.s. Is there any new-user friendly documentation available?
We'd like to think that all of the documentation is "new user friendly",
but of course it is probably not, at least in some areas :/
We'd be happy to address any specific questions, comments, and
suggestions, and getting feedback from new users is the way to do it.
Once you aren't a "new user", then you will want something different
from the docs, and those of us who have been around Postgres for a while
probably do not understand very well what exactly is missing.
So, please send along specific suggestions, or general comments on what
you are having trouble finding or understanding, and we'll try to evolve
the docs to do better.
Regards.
- Thomas
Re: [GENERAL] ODBC drivers for Macintosh?
> Do the ODBC drivers compile for Macintosh? Actually, I've looked > at the source and it doesn't look like Mac was taken into > account, but, before my Macintosh CodeWarrior C compiler arrives > and I start hacking, has anyone attempted this? I think not. But we would welcome compatible patches to support this. > Oh, and can I directly post to pgsql-patches or is that a closed > list? "Open" to subscribers only. There is also a pseudo-list you can subscribe to which will allow you to post to other lists, including patches. - Thomas
Re: [GENERAL] How to dump from Postgre
> Yeah, it just vanished. Not my fault. The bitbucket ate it. Ah, so sorry. Use pg_dump. Hope you find your docs soon ;) - Thomas
Re: [GENERAL] Importing data w/ Unix timestamp
> insert into RealTable select uname, timestamp(abstime(timeinAsInt4)), > duration, etc from TempTable; Another side comment: afaik an explicit conversion to abstime is not required to go from Unix time to timestamp. So insert into RealTable select uname, timestamp(timeinAsInt4), duration, etc from TempTable; should be equivalent. - Thomas
Re: [GENERAL] Performance of Postgres via network connections
> After this is all set up, if anyone would like, I may type up an > explanation of how things were done as well as costs, for those going > through the same sort of growing pains. It's certainly been a lot of > work for us to hammer out all of the details, hopefully that would > help someone else avoid the work. That would be great. It would be fun to see some performance numbers too. And it would be a great contribution to the "user cases" that help people see the range of applications and runtime environments which have been successful. - Thomas
Re: [GENERAL] Re: [HACKERS] proposed improvements to PostgreSQL license
> That depends on what your market is - for businesses who wants to be > able to hide source, yes. For businesses who use it, being sure the > source is available is the best - which the GPL guarantees. BSD gives > the middle man more freedom to screw the end user ;) Well, we all want more freedom, right? (please note sarcastic tone ;) > > What we'd like to propose is a general tightening up of what the > > existing license is *supposed* to be doing in the first place - > > protecting the developers who worked on the code, and ensuring that > > the code stays open source in perpetuity. > GPL would solve this - the main advantage of BSDish licenses is you > can go closed source if you want to. I imagine that RH has extensive ongoing internal discussions of licenses. Is there a "company opinion" that the main advantage of BSD is that you can go closed source? imho an advantage of BSD is that there is no question that you can use the open source anywhere you want, at any time, mixed with any other code you want. For some, that might be a "main advantage"; for others, a "don't care". Can't really see it as a negative from my PoV. > Now, I don't advocate a change in license - my main consern is "there > are enough licenses in the world". I think the "each package one > license" is a bad trend. Me too. PostgreSQL has been distributed with a plain-vanilla BSD license forever. We would like to keep it that way. But BSD doesn't say anything about developers outside of the UC system, so in the long run we probably need to do something to address that. And I don't know about any BSD licenses or existing offshoots which do that (though I haven't looked much beyond the packages I already know). istm that in most cases "companies with lawyers" go for something much tighter and more restrictive than BSD or the recently suggested modification. Regards. - Thomas
Re: [GENERAL] change in now() and ago with 7.x
> Pre-7.x, we used to use a line like: " request.date_submitted > (now() > + '24 hours ago')" > to pull data created in the past day (used against a timestamp). > Something changed with 7.x that blows that line out. You will have to be more specific on what is not working for you. afaict that line should still work: lockhart=# select * from t1 where d < now() + '24 hours ago'; i |d ---+- 1 | Mon Jul 03 00:00:60.00 2000 PDT ... (though my personal preference is for something like (timestamp 'now' - interval '24 hours') ). One thing that changed for 7.0 is that "timestamp" is the primary date/time type. Make sure that you do everything using that type, rather than using "datetime"; the latter is sort-of supported by the parser to help with upgrades, but that support is not complete.
[GENERAL] Re: [HACKERS] pl/pgsql function out parameters
> Does anyone know if out parameters are supported in pl/pgsql functions? Yes. They are not supported. I've got patches ready to submit which recognize the IN, OUT and INOUT keywords defined in SQL99, but the patches will just throw an explicit error if you specify an OUT/INOUT parameter. btw, everyone: any objections to or comments on the above? - Thomas
Re: [GENERAL] Find all the dates in the calendar week?
> ISO-week: week that has more than 4 day and start in Sunday.
> In future I try implement 'IW' that is ISO-week, but now I work on different
> things.
> In current 7.0 is probably better for week operations use
> date_part('week', TIMESTAMP) than to_char().
Note that the above returns ISO-week, not some Sun-Sat thing ('dow' ==
"day of week" does that). I haven't yet implemented ISO-year (Karel?)
which would seem to be an essential piece to use ISO-week effectively.
- Thomas
Re: [GENERAL] Find all the dates in the calendar week?
> yes, my current to_char() is almost compatible with oracle, but I need > last two features --- IYYY (ISO-year) and IW (ISO-week). But I not explore > it exactly yet. I mean that current (PG's) week-of-year is like ISO or not? Yes. 'week' is "ISO week" (new for 7.0 afaicr). And it wasn't *entirely* trivial to implement, so you may want to steal code for to_char() ;) I just didn't implement the corresponding "year" code at least partly because I wasn't sure what to call it. 'iyear' seems like a pretty good choice, or should it be 'isoyear'? 'year' is already used, obviously, and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek' for consistancy. Comments? - Thomas
Re: [GENERAL] PostgreSQL & the BSD License
> Now, a) is easily fixable by just extending the date to 2000, but that > still only covers "UNIVERSITY OF CALIFORNIA", and none of the actual > developers ... afaik we can't unilaterally alter the original license, either for dates or for participants. However, we can send along a second license (or first, primary, license) in the same file. - Thomas
Re: [GENERAL] Find all the dates in the calendar week?
> > I just didn't implement the corresponding "year" code at least partly > > because I wasn't sure what to call it. 'iyear' seems like a pretty good > > choice, or should it be 'isoyear'? 'year' is already used, obviously, > > and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek' > > for consistancy. Comments? > Then we should probably rather change 'year' to something else. Standards > should be preferred. "Standards" in this case include common usage and the ISO-defined business usage of business-year/week-of-year. If we had to choose, clearly common usage wins. > Out of curiosity, what's the difference between > ISO-year and proprietary-year? I can see the week-of-year thing, but the > year of a year is always constant, no? ISO-year/week-of-year is a business-only construct, perhaps helping with payment intervals. There is some slop in the beginning and end of each calendar year, which can result in a particular day in a calendar year fitting into a different ISO-year (not the right term btw). > Btw., isn't there an SQL EXTRACT function for all of this? Shouldn't we be > thinking in terms of that? EXTRACT() is implemented with date_part(). - Thomas
Re: [GENERAL] Postgres DateStyle
(My mailer was fouled up; I had sent this a few days ago...) > > The documentation of Postgresql 7.0.2 says that the statement: > > SET DATESTYLE TO 'Postgres'; > > would led to an output of the form: > > Wed Dec 17 07:37:16 1997 PST > > but I can't reproduce this behavior!!! > > lockhart=# select timestamp 'now'; > ?column? > --- > 2000-07-11 15:48:24.00+00 > (1 row) > > lockhart=# set datestyle to 'Postgres'; > SET VARIABLE > lockhart=# select timestamp 'now'; > ?column? > - > Tue Jul 11 15:48:35.00 2000 UTC > (1 row) > > Please show a transcript of your session if you need some more > suggestions. > > - Thomas
Re: [GENERAL] performance question
> ... I know all the stats show the MySQL is faster... A story which, if and when true, stays true only for cases with one or a very few users. We used to just accept MySQL's claims in this regard without question, but it is finally dawning on us that they are not doing representative tests for a multi-client environment :) - Thomas
Re: [GENERAL] Logging Help Needed
> > Yup ;-). It's nice having the devel docs up there, but seems like they > > ought to be labeled or segregated or something. Vince, can we re-target the most-visible docs references to point at the 7.0 release docs? I would assume it is just a matter of making or changing a soft link or two... - Thomas
Re: [GENERAL] Logging Help Needed
> Today I find that they're now v6.5 and v7.0. I know I was working in there a bit to post the 6.5 and 7.0 docs, and to get the auto-updating development docs to work. But I can't recall changing directory structure (though I *may* have done so). Should I feel guilty, or have you identified another culprit? Can anyone speak up who had an opinion (or who has dirty hands ;) on this so Vince can get back to being right-side-up and oriented? - Thomas
Re: [GENERAL] From timestamp to seconds since epoch?
> I've read through all the documentation, and I can't find a function
> that takes a timestamp and converts iit to seconds since epoch.
That would be date_part('epoch', ts) ...
> Here's what I need to do: I have pairs of timestamps ('2000-07-12
> 03:33:53+00'), and I want to find out how many seconds are in the
> interval between them. I know I can do
> select timestamp1 - timestamp2
> and I get a result in days, hours, mins, and seconds, but is there a
> way to get that whole thing in just seconds? This has to be pretty
> efficient, because it's going to get called a lot.
... and that would be date_part('epoch', ts1 - ts2)
- Thomas
Re: [GENERAL] Aggregate time data on half hour interval
> In the long run it seems like date_trunc ought to be more flexible
> than it is...
Now that we can easily overload built-in functions, we should be able to
have an alternate form for date_trunc() which can do this. Say, by
accepting a double value as the first argument, which would be the
amount to round, in seconds, like this:
date_trunc(20.0, tsfield)
or by adding one more argument which would be the units, like this:
date_trunc('minutes', 20.0, tsfield)
I think I like this second one better.
Or are you proposing something specific for the current date_trunc()?
The current form is modeled on the Ingres function of the same name.
- Thomas
Re: [GENERAL] Some questions on user defined types and functions.
> Well, that's a good question. Does anyone else have an opinion on > whether this would be a good/bad/indifferent feature? We've seen > problems in the past caused by depending on postmaster environment > variables (restart the postmaster with different environment than > usual, things mysteriously break). So I'm inclined to feel that adding > more dependence on them isn't such a hot idea. But I'm not going to > veto it if there's interest in the feature from other people. As usual, I would like to see *more* support for environment variables etc. This would fall into that category. You can choose to use it, or choose to not, but the system has *more* flexibility when all is said and done. There is code in the postmaster which does the same thing, nearly. You might want to check out the implementation there... - Thomas
Re: [GENERAL] Connection problem under extreme load.
> We have been doing some load testing with postgresql, and we have been > getting the following error when libpq attempts to connect to the > backend. This only happens occasionally and, as I said under extreme > load (e.g. load average 30+ on a single processor Sun). > connectDBStart() -- connect() failed: Connection refused > Is the postmaster running at 'localhost' and accepting connections on > Unix socket '6700'? > Has anyone seen this before or know what could be happening? One theory > that we have is that the connection request timed out because the server > was under such heavy load that it couldn't respond to the request. Is > this possible? You are increasing the number of allowed connections to above 32, right? The runtime default is 32, but it can be increased with a commandline switch. - Thomas
Re: [HACKERS] Re: [GENERAL] Trouble with float4 after upgrading from 6.5.3 to 7.0.2
> Perhaps the old way of considering equality only to float accuracy > is more useful, even though it opens us up to problems like overflow > errors in "float4var = 1e100". Comments anyone? I would not have anticipated this either. I agree that downconverting to float4 is the right solution. Possible overflow errors can be checked in advance using the macros or routines already there. This may be an example of why those could be A Good Thing in some instances. - Thomas
Re: [HACKERS] Re: [GENERAL] Trouble with float4 afterupgrading from 6.5.3 to 7.0.2
> I would have expected the latter to be at worst 10.10 +/- > .01. > Am I missing something? Well, yes :) 10.1 can't be represented exactly, so the float8 representation has bits set way down at the low end of the mantissa. When converting to float4 those low bits get rounded up or down into the lowest bit of the float4 representation. At that point, you have lost knowledge that this ever was supposed to be *exactly* 10.1. And when converting back to float8, that float4 low bit becomes a middle-range bit in the float8 representation, with all the bits underneath that zeroed. Back in the old days, before printf() implementations settled down, you would be reminded of this any time you did anything, since just assigning 10.1 and then printing it out would give you some goofy 10.0998 or 10.101 (don't count the number of digits here too closely, they are only qualitatively correct). - Thomas
Re: [GENERAL] nasty problem with redhat 6.2 + pg 7.02
> > The first result (30 sept 23:00:00) is obviously due to > > a timezone-daylight saving issue. Fixed in current sources by using mktime() rather than by rotating the date to 12 noon to try to get the correct time zone (didn't work around daylight savings time). > Thomas Lockhart is our lead guy on date/time operations, and it's > clearly time to get him involved. Thomas, have you noticed this > thread? Any luck reproducing the problem? Hmm, didn't see the thread (I unsubscribed from -general due to mailing list overload). And I don't yet see the problem on my machine: setenv PGTZ America/Buenos_Aires lockhart=# set datestyle='postgres,european'; lockhart=# select '01-10-2000'::date::timestamp; Sun 01 Oct 00:00:00 2000 ART lockhart=# select '13-10-2000'::date::timestamp; Fri 13 Oct 00:00:00 2000 ART lockhart=# select '01-10-2000'::date::timestamp; Sun 01 Oct 00:00:00 2000 ART lockhart=# select version(); PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 This is on a Mandrake 7.1 box with RPMs built from Lamar's source RPMs. Can someone else reproduce the problem on a RedHat 6.2 box? - Thomas
[GENERAL] Re: [PORTS] Updated RPMset available (7.0.3-2)
> Available at: > ftp://ftp.postgresql.org/pub/binary/v7.0.3/RPMS > or on your favorite PostgreSQL mirror. > PPC RPM's should be available soon, as should Mandrake 7.1 RPM's. I've just posted the RPMs for Mandrake. They built without any changes to the spec file (thanks Lamar!) except of course for putting the "2mdk" as the release. - Thomas > Caldera eServer 2.3, RedHat 6.x and 7.0, TurboLinux 6.0.4, and SuSE 6.4 > and 7.0 i386 binary RPM's are available, and a cross-distribution > capable source RPM is also available. Source RPM's for eServer 2.3 and > SuSE are also available, and are packaged in the respective directory. > The SuSE RPM's, due to their different source, are not updated to > 7.0.3-2, as the dependency problem doesn't exist on them.
[GENERAL] Re: [HACKERS] is it a bug?
> ... it seems the "lpad", "rpad" don't work,
> when I type:
> select lpad('laser', 4, 'a');
> in psql, the result is still
> 'laser', the same with 'rpad',
> Is it a bug or I'm mis-understaning the lpad and/or rpad functions?
A simple misunderstanding. The length argument is for the *total*
length. So padding a 5 character string to a length of 4 will do
nothing. But padding to a length of 6 will add a single "a" to the
string.
- Thomas
[GENERAL] Re: [DOCS] organization file
> hi, what kind of organization file does postgresql use? Hi. I'm not sure what you mean by "file organization". Are you asking about the file format of tables, or about the directory layout? Both are discussed in the documentation afaik. - Thomas
Re: [GENERAL] Problem migrating dump to latest CVS snapshot.
> Seems Mandrake Linux is not the only platform where roundoff behavior is > less IEEE-perfect than Thomas would like it to be. Perhaps we need a > slightly more robust approach to controlling roundoff error. Go ahead. istm that asking modulo, trunc, etc to Do The Right Thing is not a big deal, and it would be better to understand how to build executables that can do math. Certainly better than writing a bunch of extra checking code to work around the inability of a compiler (or compiler options) to do IEEE math. It *is* a standard, ya know ;) - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: JDBC and Perl compiling problems w/ postgresql-7.1rc4
> The reason I need to compile w/ Perl > support turned on is what I am reading > in the README.rserv of the ERServer > available in contrib directory. > It says that the requirements are: > - PostgreSQL >= 7.0.X >A separate Makefile is required for PostgreSQL 7.0.x and earlier > - Perl5 and the PostgreSQL perl interface > I am thinking that it only requires client lib as > the module compiles just fine. Can you confirm this please? Yes. It is only the external (client-side) perl interface which is required, to support the rserv scripts. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Indexes not used in 7.1RC4: Bug?
> I have the following table, containing about 57 Rows, but some > indexes are not used, on 7.1RC4, freshly vacuumed (analyse). It was the > same at least in 7.1RC1 > CREATE TABLE access_log( > access_time timestamp NOT NULL DEFAULT NOW(), > method_num int2NOT NULL, > url_id int4NOT NULL REFERENCES urls(id), > ); > CREATE INDEX method_idx ON access_log(method_num); > CREATE INDEX url_idx ON access_log(url_id); > url_idx seems OK: > But the others not: > logger=# EXPLAIN SELECT * FROM access_log WHERE method_num = 0; > Seq Scan on access_log (cost=0.00..16443.71 rows=559371 width=89) The parser does not know that your int4 constant "0" can be represented as an int2. Try SELECT * FROM access_log WHERE method_num = int2 '0'; (note the type coersion on the constant; there are other ways of specifying the same thing). For the other cases, PostgreSQL is estimating the query cost to be lower with a sequential scan. For the "SELECT 1" subselect case, it may be that the optimizer does not cheat and determine that there will be only one row returned, or that the query can be reformulated to use a simple constant. HTH - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Speaking of Indexing... (Text indexing)
> Furthermore, after trying to just index on a 8191-character long substring > of the resume, I run into the following: > ERROR: btree: index item size 3948 exceeds maximum 2713 > The only way I could actually get the index created was to substring the > body of the resumes down to 2k. I also later tried using HASH rather than > BTREE, which worked, but none of these solutions really appreciably > increased performance in the way we were hoping. > > Are these known and accepted limitations of the current 7.1 > implementation, or am I doing something terribly wrong? ;) Hmm. I'm pretty sure that a single index on the entire contents of a resume *as a single field* is close to useless. And an index on an 8k piece is also useless. Presumably you really want an index covering each significant word of each resume, in which case you would not run into the 4k limit (or 2k limit? it is documented somewhere) on the size of an *index* field (which is still a limitation on PostgreSQL built with the standard 8k block size. Of course, you can build with a larger block size). hth - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Hardcopy docs available
> ... if there is interest in an A4 layout of the docs, let me know... I've gotten several requests for the A4 format, and have completed four of the six docs in that format. Thanks for the feedback. They should be available in the next couple of days... - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Hardcopy docs available
> > ... if there is interest in an A4 layout of the docs, let me know... > I've gotten several requests for the A4 format, and have completed four > of the six docs in that format. Thanks for the feedback. They should be > available in the next couple of days... OK, A4 docs are now posted on the web site and the ftp site. Also, I've put copies of the html tarballs on the ftp site, so there should now be tarballs, two kinds of postscript, and PDFs available there. If someone wants to run the A4 docs through a PDF converter, send 'em to me and I'll post them too. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: [HACKERS] Struggles with RC1 and RPMS
> > No manual entry for pg_ctl > Waiting on that man page The man pages are done and available at a secret, hidden location ;) Try something like http://www.postgresql.org/user-lounge/7.0/docs/man.tar.gz - Thomas -- Thomas Lockhart [EMAIL PROTECTED] South Pasadena, California
Re: [GENERAL] 7.0 RPM?
> My point is that my RPMS use the final release sources Doh! Sorry, I was focused on the RC1 thing... -- Thomas Lockhart [EMAIL PROTECTED] South Pasadena, California
Re: [GENERAL] Question about databases in alternate locations...
> Having the
> ability to organize tables, indices, etc into tablespaces, and then
> distributing the datafiles in some quasi intelligent fashion is truly pretty
> powerful.
Great feedback! Everyone will agree that there is no problem with the
overall goal. We're just working out the details, and your use-case
with Oracle should and will be one of the use-cases that any
improvements should actually improve :)
- Thomas
--
Thomas Lockhart [EMAIL PROTECTED]
South Pasadena, California
