Re: [HACKERS] "UNICODE" encoding
Agent M wrote: > Would the version bump be a good time to fix the "UNICODE" encoding > misnomer in database creation and in the backend param status? I > assume it should be "UTFx". We could make UTF8 the canonical form in the aliasing mechanism, but beta 4 is a bit late to come up with this kind of idea. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] windows milestone
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Andrew Dunstan > Sent: 25 October 2004 00:57 > To: PostgreSQL-development > Subject: [HACKERS] windows milestone > > > With the patches Tom applied today, regarding the seg and > cube modules and get_progname(), cvs tip now fully (and for > the first time) passes all the buildfarm tests, including > contrib installcheck. That's good news :-) Thanks Andrew. Regards, Dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Compile error on 7.2.6/contrib/seg
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sun, 24 Oct 2004, Tom Lane wrote: I get an error while building 7.2.6 RPMS on Fedora Core 1. bison -y -d -p seg_yy segparse.y segparse.y:101.7: syntax error, unexpected "|" Not sure anyone still cares, but I've backpatched the 7.3 fix for this, just in case we do make any further 7.2.* releases. Thanks. I'll rebuild 7.2.6 RPMS with that fix. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBfLOgtl86P3SPfQ4RAq0tAJ0VkXZYtPdSFJBTmroujRGyTAhciQCgrED+ m1ukj9fzbz0Z7xBi+WkX/8k= =x0V+ -END PGP SIGNATURE- ---(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] Proposed Query Planner TODO items
Tatsuo Ishii schrieb: I see nice graphs for each DBT3 query(for example, http://developer.osdl.org/markw/dbt3-pgsql/42/q_time.png). It seems they do not come with normal dbt3-1.4 kit. How did you get them? Maybe you have slightly modified dbt3 kit? This looks like a simple ploticus one-liner. like: pl -png -o vbars.png -prefab vbars data=dbt3.data x=1 y=2 barwidth=line see for example: http://ploticus.sourceforge.net/doc/prefab_vbars.html or http://phpwiki.sourceforge.net/phpwiki/PhpMemoryExhausted/Testresults -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] to_char/to_number loses sign
On Sat, 2004-10-23 at 17:25 -0400, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > SELECT to_number('485-', '999S');
> > to_number
> > ---
> >485
>
> > Is this a bug or intentional?
>
> Tracing through this, it looks like the problem is that NUM_processor()
> has no switch case for NUM_S (nor does the default case raise an error,
> which seems a risky practice to me).
>
> Karel, can you verify this and submit a fix?
Yes, you're right. It strange, but NUM_S missing there. The conversion
from string to number is less stable part of formatting.c...
I have already 2000 lines of code of new generation of to_..()
functions. But all will available in 8.1.
The patch is in the attachment.
Karel
--
Karel Zak
http://home.zf.jcu.cz/~zakkr
--- pgsql/src/backend/utils/adt/formatting.c.num_s 2004-10-25 13:51:58.009789928 +0200
+++ pgsql/src/backend/utils/adt/formatting.c 2004-10-25 15:23:09.315025104 +0200
@@ -3625,7 +3625,7 @@
{
#ifdef DEBUG_TO_FROM_CHAR
- elog(DEBUG_elog_output, " --- scan start --- ");
+ elog(DEBUG_elog_output, " --- scan start --- >>%s<<", Np->number);
#endif
if (*Np->inout_p == ' ')
@@ -3642,7 +3642,7 @@
/*
* read sign
*/
- if (*Np->number == ' ' && (id == NUM_0 || id == NUM_9 || NUM_S))
+ if (*Np->number == ' ' && (id == NUM_0 || id == NUM_9 || id == NUM_S))
{
#ifdef DEBUG_TO_FROM_CHAR
@@ -4138,6 +4138,7 @@
case NUM_0:
case NUM_DEC:
case NUM_D:
+case NUM_S:
if (Np->type == TO_CHAR)
{
NUM_numpart_to_char(Np, n->key->id);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner wrote: > If we can adopt the move-after-create solution, then we really only have > two options: > > - virtual tablespaces (which do seem kind of useful, especially for > development vs. production config where the local/personal dev version > can use the same script as a production DB but not need half a dozen TSs) > > - magic-tablespace-var that behaves like the schema search path I was thinking we could have a var like schema search path that specifies where we try to create the object: SET tablespace_path = 'tblspc1, pg_default'; CREATE TABLE test(x int); This combines the idea of pulling the TABLESPACE specification out of the CREATE, and allows a fallback if the primary tablespace doesn't exist. -- 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] Using ALTER TABLESPACE in pg_dump
Bruce Momjian <[EMAIL PROTECTED]> writes: > I was thinking we could have a var like schema search path that > specifies where we try to create the object: > SET tablespace_path = 'tblspc1, pg_default'; > CREATE TABLE test(x int); > This combines the idea of pulling the TABLESPACE specification out of > the CREATE, and allows a fallback if the primary tablespace doesn't > exist. ... and takes us even further away from the notion that the default tablespace is determined by the parent object (database or schema). I think that we have a clean, understandable, easy-to-use tablespace behavior now, and we should not muck it up for abstract second-order goals like having portable dumps for databases that were created unportably in the first place. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Proposed Query Planner TODO items
Hi Tatsuo, Yes, I've been updating the dbt3 kit over the past several months. The query time graph is a new feature. It's available via BitKeeper at bk://developer.osdl.org:/var/bk/dbt3 but I haven't tested the kit well enough to make a v1.5 release yet. If BitKeeper isn't something you can use, I can make a preliminary tarball for you. Mark On Mon, Oct 25, 2004 at 01:59:46PM +0900, Tatsuo Ishii wrote: > Mark, > > I see nice graphs for each DBT3 query(for example, > http://developer.osdl.org/markw/dbt3-pgsql/42/q_time.png). It seems > they do not come with normal dbt3-1.4 kit. How did you get them? > Maybe you have slightly modified dbt3 kit? > -- > Tatsuo Ishii > > > On 6 Feb, To: [EMAIL PROTECTED] wrote: > > > On 5 Jan, Tom Lane wrote: > > >> Josh Berkus <[EMAIL PROTECTED]> writes: > > >>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES > > >> > > >>> Summary: Currently, queries with complex "or group" criteria get devolved by > > >>> the planner into canonical and-or filters resulting in very poor execution on > > >>> large data sets. We should find better ways of dealing with these queries, > > >>> for example UNIONing. > > >> > > >>> Description: While helping OSDL with their derivative TPC-R benchmark, we ran > > >>> into a query (#19) which took several hours to complete on PostgreSQL. > > > > http://developer.osdl.org/markw/dbt3-pgsql/ > > > > There's a short summary of the tests I ran over the weekend, with links > > to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it > > looks like query #7 had the only significant improvement. Oprofile data > > should be there too, if that'll help. Let us know if there's anything > > else we can try for you. > > > > Mark > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] rmtree() failure on Windows
Houston, we have a problem. Shown below is an extract from the traces of make installcheck in contrib. It is decorated with some extra traces I built into src/port/dirmod.c::rmtree(). It shows quite reproducible failure of rmtree(), mostly at the rmdir calls, but even more worryingly there are consistent unlink failures also. At this stage I have no idea how to go about fixing it. cheers andrew ../../src/test/regress/pg_regress init int2 int4 int8 float4 float8 cash oid timestamp timestamptz time timetz date interval macaddr inet cidr text varchar char bytea bit varbit numeric == dropping database "regression" == DROP DATABASE ../../src/test/regress/pg_regress cube == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/55646": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/55646" ../../src/test/regress/pg_regress dblink == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/69688": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/69688" ../../src/test/regress/pg_regress earthdistance == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72850": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72850" ../../src/test/regress/pg_regress _int == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72914": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72914" ../../src/test/regress/pg_regress ltree == dropping database "regression" == DROP DATABASE WARNING: rmtree could not unlink "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72987/80059": No such file or directory WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/72987" ../../src/test/regress/pg_regress pg_trgm == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/80061": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/80061" ../../src/test/regress/pg_regress init md5 sha1 hmac-md5 hmac-sha1 blowfish rijndael crypt-des crypt-md5 crypt-blowfish crypt-xdes == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/82213": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/82213" ../../src/test/regress/pg_regress rtree_gist == dropping database "regression" == DROP DATABASE WARNING: rmtree could not unlink "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/83241/83277": No such file or directory WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/83241" ../../src/test/regress/pg_regress seg == dropping database "regression" == DROP DATABASE WARNING: rmtree could not unlink "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/83283/86678": No such file or directory WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/83283" ../../src/test/regress/pg_regress tablefunc == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/90065": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/90065" ../../src/test/regress/pg_regress tsearch == dropping database "regression" == DROP DATABASE WARNING: rmtree could not rmdir "C:/msys/1.0/home/pgrunner/pgbuildfarm/root/HEAD/inst.blurfl/data/base/92694": Directory not empty WARNING: could not remove database directory "C:/msys/1.0/home/pgrunner/pgbuildfarm/r
Re: [HACKERS] ARC Memory Usage analysis
On 10/22/2004 4:09 PM, Kenneth Marshall wrote: On Fri, Oct 22, 2004 at 03:35:49PM -0400, Jan Wieck wrote: On 10/22/2004 2:50 PM, Simon Riggs wrote: >I've been using the ARC debug options to analyse memory usage on the >PostgreSQL 8.0 server. This is a precursor to more complex performance >analysis work on the OSDL test suite. > >I've simplified some of the ARC reporting into a single log line, which >is enclosed here as a patch on freelist.c. This includes reporting of: >- the total memory in use, which wasn't previously reported >- the cache hit ratio, which was slightly incorrectly calculated >- a useful-ish value for looking at the "B" lists in ARC >(This is a patch against cvstip, but I'm not sure whether this has >potential for inclusion in 8.0...) > >The total memory in use is useful because it allows you to tell whether >shared_buffers is set too high. If it is set too high, then memory usage >will continue to grow slowly up to the max, without any corresponding >increase in cache hit ratio. If shared_buffers is too small, then memory >usage will climb quickly and linearly to its maximum. > >The last one I've called "turbulence" in an attempt to ascribe some >useful meaning to B1/B2 hits - I've tried a few other measures though >without much success. Turbulence is the hit ratio of B1+B2 lists added >together. By observation, this is zero when ARC gives smooth operation, >and goes above zero otherwise. Typically, turbulence occurs when >shared_buffers is too small for the working set of the database/workload >combination and ARC repeatedly re-balances the lengths of T1/T2 as a >result of "near-misses" on the B1/B2 lists. Turbulence doesn't usually >cut in until the cache is fully utilized, so there is usually some delay >after startup. > >We also recently discussed that I would add some further memory analysis >features for 8.1, so I've been trying to figure out how. > >The idea that B1, B2 represent something really useful doesn't seem to >have been borne out - though I'm open to persuasion there. > >I originally envisaged a "shadow list" operating in extension of the >main ARC list. This will require some re-coding, since the variables and >macros are all hard-coded to a single set of lists. No complaints, just >it will take a little longer than we all thought (for me, that is...) > >My proposal is to alter the code to allow an array of memory linked >lists. The actual list would be [0] - other additional lists would be >created dynamically as required i.e. not using IFDEFs, since I want this >to be controlled by a SIGHUP GUC to allow on-site tuning, not just lab >work. This will then allow reporting against the additional lists, so >that cache hit ratios can be seen with various other "prototype" >shared_buffer settings. All the existing lists live in shared memory, so that dynamic approach suffers from the fact that the memory has to be allocated during ipc_init. What do you think about my other theory to make C actually 2x effective cache size and NOT to keep T1 in shared buffers but to assume T1 lives in the OS buffer cache? Jan Jan, From the articles that I have seen on the ARC algorithm, I do not think that using the effective cache size to set C would be a win. The design of the ARC process is to allow the cache to optimize its use in response to the actual workload. It may be the best use of the cache in some cases to have the entire cache allocated to T1 and similarly for T2. If fact, the ability to alter the behavior as needed is one of the key advantages. Only the "working set" of the database, that is the pages that are very frequently used, are worth holding in shared memory at all. The rest should be copied in and out of the OS disc buffers. The problem is, with a too small directory ARC cannot guesstimate what might be in the kernel buffers. Nor can it guesstimate what recently was in the kernel buffers and got pushed out from there. That results in a way too small B1 list, and therefore we don't get B1 hits when in fact the data was found in memory. B1 hits is what increases the T1target, and since we are missing them with a too small directory size, our implementation of ARC is propably using a T2 size larger than the working set. That is not optimal. If we would replace the dynamic T1 buffers with a max_backends*2 area of shared buffers, use a C value representing the effective cache size and limit the T1target on the lower bound to effective cache size - shared buffers, then we basically moved the T1 cache into the OS buffers. This all only holds water, if the OS is allowed to swap out shared memory. And that was my initial question, how likely is it to find this to be true these days? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #==
Re: [HACKERS] Daylight saving time
Dear Dennis and my Fellowmen, How would you like to evaluate a new simplified time zone system for the world and in decimal? This is found in http://www.geocities.com/peacecrusader888/timezone.htm. Best regards, Aristeo Canlas Fernando, Peace Crusader, ICD Motto: pro aris et focis http://www,geocities.com/peacecrusader888/ [EMAIL PROTECTED] (Dennis Bjorklund) wrote in message news:<[EMAIL PROTECTED]>... > I found a nice page about daylight saving time that I want to share: > > http://timeanddate.com/time/aboutdst.html > > Here are some fun quotes from the page: > > "Sometimes DST is used for longer periods than just one summer, as in the > United States during World War II. From 3 Feb 1942 to 30 Sep 1945 most of > United States had DST all year, it was called "War Time"." > > "many countries change the transition days/principles every year because > of special happenings or conditions that has happened or will happen." > > Also notice the current list of DST changes for this fall 2004: > > http://timeanddate.com/time/dst2004b.html > > I can understand why they did not try to formalize that in the sql spec. > > ps. This letter does not mean that I think it's bad to handle time zone > names, just that it's even more difficult then I first thought. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Shown below is an extract from the traces of make installcheck in > contrib. It is decorated with some extra traces I built into > src/port/dirmod.c::rmtree(). It shows quite reproducible failure of > rmtree(), mostly at the rmdir calls, but even more worryingly there are > consistent unlink failures also. I kinda suspect that what you are looking at is a problem with the delayed-unlinking feature that we built to cope with Windows' inability to unlink open files, ie, it's being a little too slow to do the unlinks. Would you refresh my memory about exactly where and when the unlink happens if the initial try fails? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Jan Wieck <[EMAIL PROTECTED]> writes: > This all only holds water, if the OS is allowed to swap out shared > memory. And that was my initial question, how likely is it to find this > to be true these days? I think it's more likely that not that the OS will consider shared memory to be potentially swappable. On some platforms there is a shmctl call you can make to lock your shmem in memory, but (a) we don't use it and (b) it may well require privileges we haven't got anyway. This has always been one of the arguments against making shared_buffers really large, of course --- if the buffers aren't all heavily used, and the OS decides to swap them to disk, you are worse off than you would have been with a smaller shared_buffers setting. However, I'm still really nervous about the idea of using effective_cache_size to control the ARC algorithm. That number is usually entirely bogus. Right now it is only a second-order influence on certain planner estimates, and I am afraid to rely on it any more heavily than that. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] rmtree() failure on Windows
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Shown below is an extract from the traces of make installcheck in contrib. It is decorated with some extra traces I built into src/port/dirmod.c::rmtree(). It shows quite reproducible failure of rmtree(), mostly at the rmdir calls, but even more worryingly there are consistent unlink failures also. I kinda suspect that what you are looking at is a problem with the delayed-unlinking feature that we built to cope with Windows' inability to unlink open files, ie, it's being a little too slow to do the unlinks. Would you refresh my memory about exactly where and when the unlink happens if the initial try fails? Same file, although Bruce says that looping code is now redundant, as we open files in a way that allows unlinking even if they are open. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I kinda suspect that what you are looking at is a problem with the >> delayed-unlinking feature that we built to cope with Windows' inability >> to unlink open files, ie, it's being a little too slow to do the >> unlinks. Would you refresh my memory about exactly where and when the >> unlink happens if the initial try fails? >> > Same file, although Bruce says that looping code is now redundant, as we > open files in a way that allows unlinking even if they are open. Does the problem go away if you insert a "sleep 1" at the bottom of the pg_regress script? I have been thinking of proposing that anyway, because on some platforms I consistently get "database removal failed: database "regression" is being accessed by other users" failures from the contrib regression tests because the previous backend doesn't get enough time to quit before the next test tries to drop and recreate the regression database. I think what you are looking at may be some variant of the same issue, ie, old backend still running. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Dear Tom, [...] This combines the idea of pulling the TABLESPACE specification out of the CREATE, and allows a fallback if the primary tablespace doesn't exist. ... and takes us even further away from the notion that the default tablespace is determined by the parent object (database or schema). I think that we have a clean, understandable, easy-to-use tablespace behavior now, and we should not muck it up for abstract second-order goals like having portable dumps for databases that were created unportably in the first place. I disagree on the view that being able to restore a database on another machine after a crash is an "abstract second-order goal";-) ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow "abstract", but when and if you need it, it is not "second-order" at all;-) and it is much too late to redo the dump. When a machine crashes, usually I did not foresee how it will crash, and whether I will or will not be able to restore on the same machine, with or without the same tablespaces... It depends on what went wrong. Thus ISTM that having the ability to fix that at restore time is simply what is needed, when it is needed. Now I do agree that having a straight behavior is a much better thing. The "ALTER ... TABLESPACE ..." generated by restore from some headers seems the right simple solution to me, but the alter syntax is not fully implemented AFAICR:-( Completing the implementation for the missing parts (ALTER DATABASE... and ALTER SCHEMA... ?), feature/beta freeze or not, would seem the reasonnable path to me. I'm sorry I don't have time to develop and submit a patch... Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] rmtree() failure on Windows
Tom Lane schrieb: Andrew Dunstan <[EMAIL PROTECTED]> writes: Shown below is an extract from the traces of make installcheck in contrib. It is decorated with some extra traces I built into src/port/dirmod.c::rmtree(). It shows quite reproducible failure of rmtree(), mostly at the rmdir calls, but even more worryingly there are consistent unlink failures also. I kinda suspect that what you are looking at is a problem with the delayed-unlinking feature that we built to cope with Windows' inability to unlink open files, ie, it's being a little too slow to do the unlinks. Would you refresh my memory about exactly where and when the unlink happens if the initial try fails? You can have a look into the cygwin sources how we do that :) kinda problematic. http://sources.redhat.com/cgi-bin/cvsweb.cgi/winsup/cygwin/delqueue.cc?cvsroot=uberbaum http://sources.redhat.com/cgi-bin/cvsweb.cgi/winsup/cygwin/syscalls.cc?cvsroot=uberbaum in short: if the return status of DeleteFileA() is ERROR_SHARING_VIOLATION, defer deletion until the end of the process. but win95 reports ERROR_ACCESS_DENIED and not ERROR_SHARING_VIOLATION as NT does. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] timestamp with time zone a la sql99
Tom, > As far as I can tell, Dennis is planning slavish adherence to the spec, > which will mean that the datatype is unable to cope effectively with > daylight-savings issues. ÂSo I'm unconvinced that it will be very > helpful to you for remembering local time in addition to true > (universal) time. As somebody who codes calendar apps, I have to say that I have yet to see an implementation of time zones which is at all useful for this purpose, including the current implementation. My calendar apps on PostgreSQL 7.4 use "timestamp without time zone" and keep the time zone in a seperate field. The reason is simple: our current implementation, which does include DST, does not include any provision for the exceptions to DST -- such as Arizona -- or for the difference between "1 day" and "24 hours". (Try adding "30 days" to "2004-10-05 10:00 PDT", you'll see what I mean). Nor do I see a way out of this without raising the complexity, and configurability, level of timezones significantly. So if we're going to be broken (at least from the perspective of calendar applications) we might as well be broken in a spec-compliant way. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Fabien COELHO <[EMAIL PROTECTED]> writes: > I disagree on the view that being able to restore a database on another > machine after a crash is an "abstract second-order goal";-) > ISTM that the core business of a database is to help organize and protect > data, and it is plainly that. You just wish you won't need it, so it is > somehow "abstract", but when and if you need it, it is not "second-order" > at all;-) and it is much too late to redo the dump. So you create some tablespaces by hand. Big deal. This objection is not strong enough to justify an ugly, klugy definition for where tables get created. If tablespaces had to be associated with physically distinct devices then there would be merit in your concerns, but they are only directories and so there is no reason that you cannot create the same set of tablespace names on your new machine that you had on your old. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Another data point - when rmdir() fails it fails quickly, but when > unlink (i.e. our pg_unlink()) fails it takes a very long time (minutes) > to fail. And the file is actually not there. So it looks like we loop > over and over and keep getting EACCESS, and then get ENOENT, but the > last one that failed with EACCESS actually succeeded. *sigh* ... or someone else deleted it in between our last EACCESS failure and the ENOENT try. What someone else would that be? More than likely, the same guy who was holding it open to cause the EACCESS failures. Perhaps there are paths in the code that don't go through win32_open? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Tom Lane <[EMAIL PROTECTED]> writes: > However, I'm still really nervous about the idea of using > effective_cache_size to control the ARC algorithm. That number is > usually entirely bogus. It wouldn't be too hard to have a port-specific function that tries to guess the total amount of memory. That isn't always right but it's at least a better ballpark default than a fixed arbitrary value. However I wonder about another approach entirely. If postgres timed how long reads took it shouldn't find it very hard to distinguish between a cached buffer being copied and an actual i/o operation. It should be able to track the percentage of time that buffers requested are in the kernel's cache and use that directly instead of the estimated cache size. Adding two gettimeofdays to every read call would be annoyingly expensive. But a port-specific function to check the cpu instruction counter could be useful. It doesn't have to be an accurate measurement of time (such as on some multi-processor machines) as long as it's possible to distinguish when a slow disk operation has occurred from when no disk operation has occurred. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] RPMS, time, and other things.
It is with some sadness I write this message to this group of talented people that I have come to think of as almost an extended family. As release time for PostgreSQL 8.0 comes near, I find myself unable to spend as much time on RPMs as I once did; frankly, I find myself not quite as interested in doing so as I once did. The situation in which I jumped feet first back in July of 1999 was one where RPMs for Red Hat releases came out infrequently, and where upgrading RPMs was fraught with peril. Times have changed: Red Hat now employs Tom Lane full-time to work on PostgreSQL; Red Hat RPM releases, handled by Tom, are both frequent and well-managed; another community maintainer, Devrim Gunduz, has stepped up to the plate and has managed the release of several RPMs at this point. These are good changes. Upgrading RPMs of PostgreSQL is still fraught with peril, however. I had attempted to make this easier at one point, but it turned into a maintenance nightmare, causing me to remove the half-attempt. I had a plan to help in this by allowing multiple versions of PostgreSQL to coexist (which I would still like to see happen, although I cannot at this time take the time to do it myself), but found myself unable to complete the probably small amount of work necessary to make it happen. This project deserves some fresh interest in this area; thus, I am stepping down as active RPM maintainer. I do not intend to unsubscribe from this list, however, and I intend to make myself available to answer Devrim and Tom (or anyone else!) on any questions they may have regarding the RPM packaging. I may critique from time-to-time the packaging, if Devrim doesn't mind, but I just simply cannot invest as much of my dwindling free time any more. My apologies for not making this decision earlier in the 8.0 beta cycle. This is a great group; in my opinion it is THE best open source development communities in existence anywhere. I have been honored to have served in this capacity, and look forward to PostgreSQL's continuing improvement due to the marvelous efforts of this active, vibrant, community. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timestamp with time zone a la sql99
Josh Berkus <[EMAIL PROTECTED]> writes: > The reason is simple: our current implementation, which does include DST, > does not include any provision for the exceptions to DST -- such as Arizona Say what? regression=# set timezone to 'MST7MDT'; SET regression=# select now(); now --- 2004-10-25 11:52:47.093538-06 (1 row) regression=# set timezone to 'US/Arizona'; SET regression=# select now(); now --- 2004-10-25 10:52:49.441559-07 (1 row) > -- or for the difference between "1 day" and "24 hours". (Try adding "30 > days" to "2004-10-05 10:00 PDT", you'll see what I mean). This is the point about how interval needs to treat "day" as different from "24 hours". I agree with that; the fact that it's not done already is just a reflection of limited supply of round tuits. I think it's orthogonal to the question of how flexible timestamp with time zone needs to be, though. > Nor do I see a way out of this without raising the complexity, and > configurability, level of timezones significantly. This does not seem to me to be an argument why timestamp with time zone ought to be incapable of dealing with DST-aware time zones. That simply guarantees that calendar apps won't be able to use the datatype. If they still can't use it when it can do that, then we can look at the next blocking factor. > So if we're going to be broken (at least from the perspective of calendar > applications) we might as well be broken in a spec-compliant way. I have not said that we can't comply with the spec. I have said that our ambitions need to be higher than merely complying with the spec. 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] timestamp with time zone a la sql99
Tom, > regression=# set timezone to 'US/Arizona'; > SET > regression=# select now(); > now > --- > 2004-10-25 10:52:49.441559-07 Wow! When did that get fixed? How do I keep track of this stuff if you guys keep fixing it? ;-) Of course, it would be very helpful if the result above could display "Arizona" instead of the non-specific "-07", but I'm pretty sure that's already a TODO. > This is the point about how interval needs to treat "day" as different > from "24 hours". I agree with that; the fact that it's not done already > is just a reflection of limited supply of round tuits. Well, when I first brought up the issue (2001) I was shot down on the basis of spec-compliance, since SQL92 recognizes only Year/Month and Day/Hour/Minute/etc. partitions. Glad it's up for consideration again. Come to think of it, it was Thomas Lockhart who shot down the idea of fixing Interval, and he's retired now ... > This does not seem to me to be an argument why timestamp with time zone > ought to be incapable of dealing with DST-aware time zones. That simply > guarantees that calendar apps won't be able to use the datatype. If > they still can't use it when it can do that, then we can look at the > next blocking factor. That's definitely a progressive attitude pardon me for being pessimistic. > I have not said that we can't comply with the spec. I have said that > our ambitions need to be higher than merely complying with the spec. Hmmm ... well, does the spec specifically prohibit DST, or just leave it out? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timestamp with time zone a la sql99
Josh Berkus <[EMAIL PROTECTED]> writes: >> regression=# set timezone to 'US/Arizona'; >> SET >> regression=# select now(); >> now >> --- >> 2004-10-25 10:52:49.441559-07 > Wow! When did that get fixed? How do I keep track of this stuff if you > guys keep fixing it? ;-) > Of course, it would be very helpful if the result above could display > "Arizona" instead of the non-specific "-07", but I'm pretty sure that's > already a TODO. Well, that is *exactly what I'm talking about*. I want timestamp with time zone to carry "US/Arizona" not just "-07". Obviously there needs to be some option to get the latter displayed when that's all you want, but internally a value of the datatype needs to be able to carry full knowledge of which timezone it's supposed to be in. Dumbing that down to a simple numeric GMT offset isn't good enough. >> I have not said that we can't comply with the spec. I have said that >> our ambitions need to be higher than merely complying with the spec. > Hmmm ... well, does the spec specifically prohibit DST, or just leave it out? It just doesn't talk about it AFAICS. To comply with the spec we definitely need to be *able* to support timezone values that are simple numeric GMT offsets. But I think we ought also to be able to store values that are references to any of the zic database entries. This looks to me like a straightforward extension of the spec. We went to all the trouble of importing src/timezone in order that we could make a significant upgrade in our timezone capability, and now it's time to take the steps that that enables. Before we were limited to the lowest-common-denominator of the libc timezone routines on all our different platforms, but now we are not... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: > Hmmm ... well, does the spec specifically prohibit DST, or just leave it > out? It doesn't discuss it. According to the spec a timestamp with time zone is a UTC value + a HH:MM offset from GMT. And intervals in the spec is either a year-month value or a day-time value. One can only compare year-month values with each other and day-time values with each other. So they avoid the problem of the how many days is a month by not allowing it. The spec is not a full solution, it's also not a useless solution. I'm happy as long as the spec is a subset of what pg implements. If not then I would like to be able to have both but with different names or something similar (but I think that should not be needed). -- /Dennis Björklund ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp with time zone a la sql99
Dennis, > It doesn't discuss it. According to the spec a timestamp with time zone is > a UTC value + a HH:MM offset from GMT. And intervals in the spec is either > a year-month value or a day-time value. One can only compare year-month > values with each other and day-time values with each other. So they avoid > the problem of the how many days is a month by not allowing it. That's not what Tom and I were talking about. The issue is that the spec defines Days/Weeks as being an agglomeration of hours and not an atomic entity like Months/Years are. This leads to some wierd and calendar-breaking behavior when combined with DST, for example: template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL template1-> ; ?column? 2004-11-23 09:00:00-08 (1 row) Because of the DST shift, you get an hour shift which is most decidely not anything real human beings would expect from a calendar. The answer is to try-partition INTERVAL values, as: Hour/Minute/Second/ms Day/Week Month/Year However, this could be considered to break the spec; certainly Thomas thought it did. My defense is that the SQL committee made some mistakes, and interval is a big one. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timestamp with time zone a la sql99
Josh Berkus <[EMAIL PROTECTED]> writes: >> It doesn't discuss it. According to the spec a timestamp with time zone is >> a UTC value + a HH:MM offset from GMT. And intervals in the spec is either >> a year-month value or a day-time value. One can only compare year-month >> values with each other and day-time values with each other. So they avoid >> the problem of the how many days is a month by not allowing it. > That's not what Tom and I were talking about. The issue is that the spec > defines Days/Weeks as being an agglomeration of hours and not an atomic > entity like Months/Years are. I think though that these points are closely related. The reason the spec does that is exactly that they are ignoring DST and so they can assume that 1 day == 24 hours == 86400 seconds. In a DST-aware world you have to make a separation between days and the smaller units, just as months are separated from smaller units because there's not a fixed conversion factor. To some extent the interval and timestamptz issues are orthogonal, but I think it would be good to fix them in the same release if possible. There will undoubtedly be some backwards-compatibility problems, and I suppose that users would prefer to take them all at once than via the chinese water torture method ... > However, this could be considered to break the spec; certainly Thomas > thought it did. My defense is that the SQL committee made some > mistakes, and interval is a big one. I'm not clear to what extent we have to actually break the spec, as opposed to extend it, in order to do this to the "interval" type. To do everything the spec says we need to do, we'll have to be able to make some comparisons that aren't strictly valid (which amounts to assuming that 1 day == 24 hours for some limited purposes) but we already do much the same things with respect to months. (See other thread about whether 1 year == 360 days...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: > Dennis, > > > It doesn't discuss it. According to the spec a timestamp with time zone is > > a UTC value + a HH:MM offset from GMT. And intervals in the spec is either > > a year-month value or a day-time value. One can only compare year-month > > values with each other and day-time values with each other. So they avoid > > the problem of the how many days is a month by not allowing it. > > That's not what Tom and I were talking about. You wanted to know what the standard said, and I told what I knew. > The issue is that the spec defines Days/Weeks as being an agglomeration > of hours and not an atomic entity like Months/Years are. I don't know what you mean with this. The standard does treat them as year month day hour minute second (with fractions) There is no weeks there, if that is what you mean. > This leads to some wierd and calendar-breaking behavior when combined > with DST, for example: > > template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL > template1-> ; > ?column? > > 2004-11-23 09:00:00-08 > (1 row) > > Because of the DST shift, you get an hour shift which is most decidely not > anything real human beings would expect from a calendar. I don't see how the above can be caused by the representation of an interval. The above timestamp is 2004-10-09 10:00 PDT which in the standard would be 2004-10-09 10:00 -07 and after the additon would be 2004-11-23 10:00:00-07 Here the time zone is wrong since the standard does not know about named zones and dst. An implementation like the one Tom (and I) want would start with 2004-10-09 10:00 PDT and then after the addition one would get 2004-11-23 10:00:00 PST At least that's my understanding of what we want and what we can get (plus that we also need to support HH:MM tz values since those also exist in the world, check this emails header for example). It's possible that you discuss something else, but that has been lost on me so far. -- /Dennis Björklund ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: > Hour/Minute/Second/ms > Day/Week > Month/Year And just when I pressed "send" on the previous mail I got the problem :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: > Hour/Minute/Second/ms > Day/Week > Month/Year This is embarrasing. I'm still a bit confused :-) The standard treat days as a separate entry, it does not assume that a day is 24 hours. It restricts the hour field to the interval 0-23 so one can never have something like 25 hours. So it does not need to worry about how many days that translate to. And why do we need weeks also? Well, this is the last mail I send before I've been thinking about this for a while more :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, Oct 25, 2004 at 21:18:52 +0200, Dennis Bjorklund <[EMAIL PROTECTED]> wrote: > On Mon, 25 Oct 2004, Josh Berkus wrote: > > > Hour/Minute/Second/ms > > Day/Week > > Month/Year > > This is embarrasing. I'm still a bit confused :-) > > The standard treat days as a separate entry, it does not assume that a day > is 24 hours. It restricts the hour field to the interval 0-23 so one can > never have something like 25 hours. So it does not need to worry about how > many days that translate to. > > And why do we need weeks also? For convenience. Just like years are a group of months, weeks are a group of days. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp with time zone a la sql99
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > The standard treat days as a separate entry, it does not assume that a day > is 24 hours. SQL92 says 4.5.2 Intervals There are two classes of intervals. One class, called year-month intervals, has an express or implied datetime precision that in- cludes no fields other than YEAR and MONTH, though not both are required. The other class, called day-time intervals, has an ex- press or implied interval precision that can include any fields other than YEAR or MONTH. AFAICS the reason for this rule is that they expect all Y/M intervals to be comparable (which they are) and they also expect all D/H/M/S intervals to be comparable, which you can only do by assuming that 1 D == 24 H. It seems to me though that we can store days separately and do interval comparisons with the assumption 1 D == 24 H, and be perfectly SQL-compatible as far as that goes, and still make good use of the separate day info when adding to a timestamptz that has a DST-aware timezone. In a non-DST-aware timezone the addition will act the same as if we weren't distinguishing days from h/m/s. Therefore, an application using only the spec-defined features (ie, only fixed-numeric-offset timezones) will see no deviation from the spec behavior. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] code question: storing INTO relation
On Sat, 2004-10-23 at 00:29, Greg Stark wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > > I agree, hence why this should be a user option. The usage of this is > > restricted to particular classes of database usage: data warehousing or > > very large database applications. This isn't intended for use in OLTP or > > web-site databases. > > Well a lot of users also just don't use online backups. For these users > there's no downside to CREATE INDEX/REINDEX/CREATE TABLE AS not logging. > Yes, you're right. I'm just aiming higher, that's all... A DW with large fact tables will benefit from the optimisation, since the data loading can often be used to recover the database if required. Reference data tables don't benefit from the optimization since they are smaller and much easier to backup/recover. We want to join the fact tables to the reference data tables, so would like both to exist in a database that has BOTH PITR and non-logged bulk operations. The alternative is to have an ODS that uses PITR, alongside a DW that doesn't, though with data copying from the ODS to the DW. The latter step is a time-waster I see no reason to encourage. Anyway... I see no huge agreement with my viewpoint, so I'll just add it to my own list... -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Tom Lane wrote: > There are two classes of intervals. One class, called year-month > intervals, has an express or implied datetime precision that in- > cludes no fields other than YEAR and MONTH, though not both are > required. The other class, called day-time intervals, has an ex- > press or implied interval precision that can include any fields > other than YEAR or MONTH. > > AFAICS the reason for this rule is that they expect all Y/M intervals to > be comparable (which they are) and they also expect all D/H/M/S intervals > to be comparable, which you can only do by assuming that 1 D == 24 H. I said I was not going to send any more mails, but here we go again :-) The standard restrict the hour field to the interval 0-23, so there can never be any compare between for example '1 day 1 hour' and '25 hours'. This means that one can not add two intervals together to get a bigger one but that it would still work to do timestamp+interval+interval. > It seems to me though that we can store days separately and do interval > comparisons with the assumption 1 D == 24 H, and be perfectly > SQL-compatible as far as that goes, and still make good use of the > separate day info when adding to a timestamptz that has a DST-aware > timezone. In a non-DST-aware timezone the addition will act the same as > if we weren't distinguishing days from h/m/s. Therefore, an application > using only the spec-defined features (ie, only fixed-numeric-offset > timezones) will see no deviation from the spec behavior. I agree with this. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timestamp with time zone a la sql99
Dennis, > An implementation like the one Tom (and I) want would start with > > 2004-10-09 10:00 PDT > > and then after the addition one would get > > 2004-11-23 10:00:00 PST Sounds like we're on the same page then. > The standard restrict the hour field to the interval 0-23, so there can > never be any compare between for example '1 day 1 hour' and '25 hours'. > This means that one can not add two intervals together to get a bigger > one but that it would still work to do timestamp+interval+interval. Hour field of the timestamp, or hour field of interval? There a world of difference. As long as we're willing to live with the understanding that +1day 1 hour may produce a slightly different result than + 25 hours, I don't see the problem. Currently I can add +900 hours if I like, postgreSQL will support it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp with time zone a la sql99
On Mon, 25 Oct 2004, Josh Berkus wrote: > > The standard restrict the hour field to the interval 0-23, so there can > > never be any compare between for example '1 day 1 hour' and '25 hours'. > > This means that one can not add two intervals together to get a bigger > > one but that it would still work to do timestamp+interval+interval. > > Hour field of the timestamp, or hour field of interval? There a world of > difference. Hour field of an interval can be 0-23 according to the spec (doesn't say that we need that restriction, but we do need to understand what the spec say). -- /Dennis Björklund ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] timestamp with time zone a la sql99
Josh Berkus <[EMAIL PROTECTED]> writes: > As long as we're willing to live with the understanding that +1day 1 hour may > produce a slightly different result than + 25 hours, I don't see the problem. Right, which is exactly why we can't accept the spec's restriction that the hour field be limited to 0-23. People may legitimately want to add 48 hours to a timestamp, and *not* have that mean the same as adding "2 days". Besides, we would have a backwards-compatibility problem if we tried to forbid it, since as you note we've always accepted such input. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Lamar stepping down
Lamar, On behalf of the Core Team, we thank Lamar for five years of making packages available for PostgreSQL. His efforts have made a substantial difference in many PostgreSQL users' jobs by making downloads available for those who are uncomfortable with source installs or use RPMs by policy. We're particularly grateful because "RPM Maintainer" is not a fun or prestigious job, and yet Lamar did such a professional job that several people have been surprised to learn he is/was a volunteer. We are glad that Lamar will still be around the project to offer advice -- as well as being around to chat once in a while. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] rmtree() failure on Windows
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Another data point - when rmdir() fails it fails quickly, but when unlink (i.e. our pg_unlink()) fails it takes a very long time (minutes) to fail. And the file is actually not there. So it looks like we loop over and over and keep getting EACCESS, and then get ENOENT, but the last one that failed with EACCESS actually succeeded. *sigh* ... or someone else deleted it in between our last EACCESS failure and the ENOENT try. What someone else would that be? More than likely, the same guy who was holding it open to cause the EACCESS failures. Perhaps there are paths in the code that don't go through win32_open? Well, on looking at the MS API at http://msdn.microsoft.com/library/en-us/vclib/html/vcrefRunTimeLibraryReference.asp I see that opendir() and friends aren't there, which means we might be at the mercy of what mingw does for us. If I increase the sleep time before dropdb enormously (35 secs) the unlink errors seem to go away, and instead they become rmdir errors like the rest. I am wondering if we need to look at using direct calls to the Windows API (findfirst() and friends). cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] rmtree() failure on Windows
Andrew Dunstan <[EMAIL PROTECTED]> writes: > If I increase the sleep time before dropdb enormously (35 secs) the > unlink errors seem to go away, and instead they become rmdir errors like > the rest. Do you have anything equivalent to ps that you could use to check whether there is still an old backend alive during this interval? Does Windows forbid deleting a directory that is the current working directory of some process (assuming they even have the same concept of cwd as Unixen)? If so, is this the same error symptom you would get? I am wondering if the rmdir failure occurs because an old backend is still chdir'd into the database directory. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Greg Stark <[EMAIL PROTECTED]> writes:
> However I wonder about another approach entirely. If postgres timed how long
> reads took it shouldn't find it very hard to distinguish between a cached
> buffer being copied and an actual i/o operation. It should be able to track
> the percentage of time that buffers requested are in the kernel's cache and
> use that directly instead of the estimated cache size.
I tested this with a program that times seeking to random locations in a file.
It's pretty easy to spot the break point. There are very few fetches that take
between 50us and 1700us, probably they come from the drive's onboard cache.
The 1700us bound probably would be lower for high end server equipment with
10k RPM drives and RAID arrays. But I doubt it will ever come close to the
100us edge, not without features like cache ram that Postgres would be better
off considering to be part of "effective_cache" anyways.
So I would suggest using something like 100us as the threshold for determining
whether a buffer fetch came from cache.
Here are two graphs, one showing a nice curve showing how disk seek times are
distributed. It's neat to look at for that alone:
<>
This is the 1000 fastest data points zoomed to the range under 1800us:
<>
This is the program I wrote to test this:
#include
#include
#include
#include
#include
#include
#include
#include
int main(int argc, char *argv[])
{
int rep = atoi(argv[1]);
int i;
char *filename;
int fd;
struct stat statbuf;
off_t filesize;
unsigned blocksize;
void *blockbuf;
filename = argv[2];
fd = open(filename, O_RDONLY);
fstat(fd, &statbuf);
filesize = statbuf.st_size;
blocksize = statbuf.st_blksize;
blockbuf = malloc(blocksize);
srandom(getpid()^clock());
for (i=0;i
Here are the commands I used to generate the graphs:
$ dd bs=1M count=1024 if=/dev/urandom of=/tmp/big
$ ./a.out 1 /tmp/big > /tmp/l
$ gnuplot
gnuplot> set terminal png
gnuplot> set output "/tmp/plot1.png"
gnuplot> plot '/tmp/l2' with points pointtype 1 pointsize 1
gnuplot> set output "/tmp/plot2.png"
gnuplot> plot [0:2000] [0:1000] '/tmp/l2' with points pointtype 1 pointsize 1
--
greg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: > Fabien COELHO <[EMAIL PROTECTED]> writes: > > I disagree on the view that being able to restore a database on another > > machine after a crash is an "abstract second-order goal";-) > > > ISTM that the core business of a database is to help organize and protect > > data, and it is plainly that. You just wish you won't need it, so it is > > somehow "abstract", but when and if you need it, it is not "second-order" > > at all;-) and it is much too late to redo the dump. > > So you create some tablespaces by hand. Big deal. This objection is > not strong enough to justify an ugly, klugy definition for where tables > get created. > > If tablespaces had to be associated with physically distinct devices > then there would be merit in your concerns, but they are only > directories and so there is no reason that you cannot create the same > set of tablespace names on your new machine that you had on your old. I am confused. I thought Tom's argument was that we shouldn't add an overly complex tablespace SET variable just to prevent the non-standard TABLESPACE in CREATE, which I can understand. However, the text above seems to indicate we don't need an 'ignore tablespace specification if it does not exist' which I think we do need for cases where we want to restore on to a system that doesn't use tablespaces or for non-super-user restores. -- 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 7: don't forget to increase your free space map settings
[HACKERS] copy - fields enclosed by, ignore x lines
Any chance of changing \copy and COPY to allow specifying what the fields are enclosed by (such as quotes) and to ignore the first x number of lines? I have data like below and don't know of an easy way to finesse it for importing (a simple regexp would remove quotes, but I just got tripped up on commas *within* values). "field1","field2","field3" "val1","val2","val3" __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Greg Stark <[EMAIL PROTECTED]> writes: > So I would suggest using something like 100us as the threshold for > determining whether a buffer fetch came from cache. I see no reason to hardwire such a number. On any hardware, the distribution is going to be double-humped, and it will be pretty easy to determine a cutoff after minimal accumulation of data. The real question is whether we can afford a pair of gettimeofday() calls per read(). This isn't a big issue if the read actually results in I/O, but if it doesn't, the percentage overhead could be significant. If we assume that the effective_cache_size value isn't changing very fast, maybe it would be good enough to instrument only every N'th read (I'm imagining N on the order of 100) for this purpose. Or maybe we need only instrument reads that are of blocks that are close to where the ARC algorithm thinks the cache edge is. One small problem is that the time measurement gives you only a lower bound on the time the read() actually took. In a heavily loaded system you might not get the CPU back for long enough to fool you about whether the block came from cache or not. Another issue is what we do with the effective_cache_size value once we have a number we trust. We can't readily change the size of the ARC lists on the fly. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] copy - fields enclosed by, ignore x lines
Ah, looks like "enclosed by" will be in PG 8 :). Is "QUOTE [ AS ] 'quote'" for the "enclosed by" character? Ignore x lines would be nice, but not as big of a deal. http://developer.postgresql.org/docs/postgres/sql-copy.html --- CSN <[EMAIL PROTECTED]> wrote: > Any chance of changing \copy and COPY to allow > specifying what the fields are enclosed by (such as > quotes) and to ignore the first x number of lines? I > have data like below and don't know of an easy way > to > finesse it for importing (a simple regexp would > remove > quotes, but I just got tripped up on commas *within* > values). > > "field1","field2","field3" > "val1","val2","val3" > > > > __ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We > finish. > http://promotions.yahoo.com/new_mail > ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Bruce Momjian <[EMAIL PROTECTED]> writes: > I am confused. I thought Tom's argument was that we shouldn't add an > overly complex tablespace SET variable just to prevent the non-standard > TABLESPACE in CREATE, which I can understand. However, the text above > seems to indicate we don't need an 'ignore tablespace specification if > it does not exist' which I think we do need for cases where we want to > restore on to a system that doesn't use tablespaces or for > non-super-user restores. I'm willing to live with a "soft error" type of GUC variable for those cases. I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. (Which would be consistent with the way we handle selection of which schema to create in, so I'm not necessarily against it.) I guess what I'm trying to say is I don't want a hodgepodge design, because I think it'll be confusing and unusable. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I am confused. I thought Tom's argument was that we shouldn't add an > > overly complex tablespace SET variable just to prevent the non-standard > > TABLESPACE in CREATE, which I can understand. However, the text above > > seems to indicate we don't need an 'ignore tablespace specification if > > it does not exist' which I think we do need for cases where we want to > > restore on to a system that doesn't use tablespaces or for > > non-super-user restores. > > I'm willing to live with a "soft error" type of GUC variable for those > cases. I don't want a GUC variable that actively changes the default > tablespace; at least not unless you want to abandon the current > mechanisms for default tablespace choices entirely, and go over to > making the GUC variable be the sole arbiter. (Which would be consistent > with the way we handle selection of which schema to create in, so I'm > not necessarily against it.) I guess what I'm trying to say is I don't > want a hodgepodge design, because I think it'll be confusing and > unusable. Agreed. My tablespace path idea would be very hard to understand if combined with the existing db/schema/table default rules. I can't decide which is the best approach. Don't indexes default to the schema of the table rather than the schema path, so they aren't 100% controlled by the search path? -- 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] RPMS, time, and other things.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 25 Oct 2004, Lamar Owen wrote: I may critique from time-to-time the packaging, if Devrim doesn't mind, It would not, and won't be without you. It's good to know that you are around, good to know that you'll keep your eye on the packages. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBfXu9tl86P3SPfQ4RAmyKAJ0eMLyNDk5o3vvSnxLGFxDCPmpaJACfaXI5 rUyETygu8GyIZjeCn/46N/o= =pDMj -END PGP SIGNATURE- ---(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] rmtree() failure on Windows
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: If I increase the sleep time before dropdb enormously (35 secs) the unlink errors seem to go away, and instead they become rmdir errors like the rest. Do you have anything equivalent to ps that you could use to check whether there is still an old backend alive during this interval? Does Windows forbid deleting a directory that is the current working directory of some process (assuming they even have the same concept of cwd as Unixen)? If so, is this the same error symptom you would get? I am wondering if the rmdir failure occurs because an old backend is still chdir'd into the database directory. No, testing shows you get "permission denied" rather than "directory not empty" in this case. cheers andrew ---(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] Lamar stepping down
Hello, Thanks for all your hard work Lamar. Sincerely, Joshua D. Drake Josh Berkus wrote: Lamar, On behalf of the Core Team, we thank Lamar for five years of making packages available for PostgreSQL. His efforts have made a substantial difference in many PostgreSQL users' jobs by making downloads available for those who are uncomfortable with source installs or use RPMs by policy. We're particularly grateful because "RPM Maintainer" is not a fun or prestigious job, and yet Lamar did such a professional job that several people have been surprised to learn he is/was a volunteer. We are glad that Lamar will still be around the project to offer advice -- as well as being around to chat once in a while. -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Kenneth Marshall <[EMAIL PROTECTED]> writes: > How invasive would reading the "CPU counter" be, if it is available? Invasive or not, this is out of the question; too unportable. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner <[EMAIL PROTECTED]> writes: > At 08:00 AM 26/10/2004, Tom Lane wrote: >> I don't want a GUC variable that actively changes the default >> tablespace; at least not unless you want to abandon the current >> mechanisms for default tablespace choices entirely, and go over to >> making the GUC variable be the sole arbiter. > Something consistent with Schemas does sound good to me; a tablespace > search path (or just single default), and support for a TABLESPACE clause > on table and INDEX definitions would be good. I can't see what a search path would be good for. > For the three largest databases I work on, the namespace/schema that a > table resides in is irrelevant to the tablespace that it should be stored > in. So default tablespaces on the schema are a bit of a pointless feature. > The ability to have the features of schemas: default tablespace for given > users, a GUC variable, and ACLs on tablespaces would be far more valuable. Another nice thing is that not having default tablespaces associated with schemas eliminates that nasty issue about being able to drop such a tablespace while the schema is still there. It seems like we still need some notion of a database's schema, to put the system catalogs in, but perhaps that need not be the same as the default schema for user tables created in the database? I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. Does anyone want to draft a concrete proposal? It seems like the basic elements are: * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; I'm not sure if this is a good idea, or if the indexes should go into default_tablespace absent a TABLESPACE clause attached directly to their defining constraints. We certainly want default_tablespace to control indexes created by separate commands, so there'd be some inconsistency if we do the former. 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] Using ALTER TABLESPACE in pg_dump
At 09:28 AM 26/10/2004, Tom Lane wrote: I can't see what a search path would be good for. Nothing at this stage. It seems like we still need some notion of a database's schema, Yes. I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. I'll have a look in the next 18 hours... * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. Agree. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). Agree. * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? Nothing else would be my call; make it like the tablespace on tables. * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the same command; This is a hard one. We need ALTER INDEX or STORE INDEX or whatever if we can't nicely put each index in it's own tablespace. We're only talking PKs aren't we? I'll have to think about this. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(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] Using ALTER TABLESPACE in pg_dump
On Mon, 25 Oct 2004, Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > > At 08:00 AM 26/10/2004, Tom Lane wrote: > >> I don't want a GUC variable that actively changes the default > >> tablespace; at least not unless you want to abandon the current > >> mechanisms for default tablespace choices entirely, and go over to > >> making the GUC variable be the sole arbiter. > > > Something consistent with Schemas does sound good to me; a tablespace > > search path (or just single default), and support for a TABLESPACE clause > > on table and INDEX definitions would be good. > > I can't see what a search path would be good for. I agree. > > > For the three largest databases I work on, the namespace/schema that a > > table resides in is irrelevant to the tablespace that it should be stored > > in. So default tablespaces on the schema are a bit of a pointless feature. > > The ability to have the features of schemas: default tablespace for given > > users, a GUC variable, and ACLs on tablespaces would be far more valuable. > > Another nice thing is that not having default tablespaces associated > with schemas eliminates that nasty issue about being able to drop such a > tablespace while the schema is still there. Hmmm.. despite that problem, I was rather fond of schema default tablespaces because they allow DBAs to set a policy for a particular schema. The cases I've discussed with people so far are things like creating a schema for a (closed source) application and associating that with a tablespace. There by, all new objects created will be in that tablespace without the need for DBA intervention. Its not necessary, but its nice I think. > It seems like we still need some notion of a database's schema, to put > the system catalogs in, but perhaps that need not be the same as the > default schema for user tables created in the database? By schema here, do you mean tablespace? > > I'd be willing to jump this way if we can work out the > default-tablespace inconsistencies that Bruce has on the open items > list. Does anyone want to draft a concrete proposal? It seems like the > basic elements are: > > * A GUC variable named something like default_tablespace that > controls which TS objects are created in when there's > no explicit TABLESPACE clause. The factory default for this > would of course be pg_default. Otherwise it's settable just > like any other GUC var. > > * Get rid of TABLESPACE clause for CREATE SCHEMA, and > pg_namespace.nsptablespace (ooops, another initdb). > > * Need to define exactly what TABLESPACE clause for a database > controls; location of its catalogs of course, but anything else? This could be a bit messy (from a user's point of view). There are two meanings (according to your plan): 1) the tablespace clause is the default for the catalogs AND for newly created objects (we set default_tablespace in datconfig); OR, 2) it only sets the tablespace for the catalogs. (You could say that it just sets the default tablespace for new objects, but then how do you set the catalog tablespace). I guess (1) makes sense but it limits people. If we do (2), we have two options: a) User needs to ALTER DATABASE SET default_table.. b) we add a new key work. I think (b) is ugly. > > * We could possibly say that a TABLESPACE clause attached to > CREATE TABLE determines the default tablespace for indexes > created by the same command; I'm not sure if this is a good > idea, or if the indexes should go into default_tablespace > absent a TABLESPACE clause attached directly to their defining > constraints. We certainly want default_tablespace to control > indexes created by separate commands, so there'd be some > inconsistency if we do the former. I think a viable solution is to go with the latter (ie, for CREATE TABLE foo(i int primary key) TABLESPACE ts; the index on i is created in default_tablespace). However, I might be nice to be able to specify the tablespace as part of the primary key clause. I say nice, but not necessary. > > regards, tom lane Thanks, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Gavin Sherry <[EMAIL PROTECTED]> writes: > Hmmm.. despite that problem, I was rather fond of schema default > tablespaces because they allow DBAs to set a policy for a particular > schema. The cases I've discussed with people so far are things > like creating a schema for a (closed source) application and associating > that with a tablespace. There by, all new objects created will be in that > tablespace without the need for DBA intervention. Its not necessary, but > its nice I think. On the other hand, driving it from a GUC variable would allow you to easily set a per-user default, which might be at least as useful. >> It seems like we still need some notion of a database's schema, to put >> the system catalogs in, but perhaps that need not be the same as the >> default schema for user tables created in the database? > By schema here, do you mean tablespace? Sorry, fingers faster than brain obviously. Time to take a break... > I think a viable solution is to go with the latter (ie, for CREATE TABLE > foo(i int primary key) TABLESPACE ts; the index on i is created in > default_tablespace). However, I might be nice to be able to specify the > tablespace as part of the primary key clause. I say nice, but not > necessary. We already have that don't we? create table foo (f1 int, primary key (f1) using index tablespace its) tablespace tts; The question is where to put foo_pkey when "using index tablespace" isn't there but "tablespace" is. (BTW, since we stole that syntax from Oracle, maybe we should check what they do...) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
On Mon, 25 Oct 2004, Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > Hmmm.. despite that problem, I was rather fond of schema default > > tablespaces because they allow DBAs to set a policy for a particular > > schema. The cases I've discussed with people so far are things > > like creating a schema for a (closed source) application and associating > > that with a tablespace. There by, all new objects created will be in that > > tablespace without the need for DBA intervention. Its not necessary, but > > its nice I think. > > On the other hand, driving it from a GUC variable would allow you to > easily set a per-user default, which might be at least as useful. > > >> It seems like we still need some notion of a database's schema, to put > >> the system catalogs in, but perhaps that need not be the same as the > >> default schema for user tables created in the database? > > > By schema here, do you mean tablespace? > > Sorry, fingers faster than brain obviously. Time to take a break... > > > I think a viable solution is to go with the latter (ie, for CREATE TABLE > > foo(i int primary key) TABLESPACE ts; the index on i is created in > > default_tablespace). However, I might be nice to be able to specify the > > tablespace as part of the primary key clause. I say nice, but not > > necessary. > > We already have that don't we? > > create table foo (f1 int, > primary key (f1) using index tablespace its) > tablespace tts; > > The question is where to put foo_pkey when "using index tablespace" > isn't there but "tablespace" is. Hah. I wasn't sure if that ever got in -- guess I should have checked. > > (BTW, since we stole that syntax from Oracle, maybe we should check what > they do...) As an aside -- I'm not quite sure we stole the syntax from Oracle. Oracle has *a lot* more functionality and nothing like the parent's tablespace system. Basically, more than one database object can be stored in a single data file in oracle. A tablespace is a group of such files. You can have two files in a tablespace in diferent locations. That is, tablespace foo might consist of /data1/a.dat and /data2/b.dat. So, when you create a new database, you can determine where the 'system catalogs' are by setting the datafile location for the system catalog tablespaces. You can *also* set a default tablespace for the database -- default in the sense that all newly created objects with no explicit tablespace clause are created in the tablespace. With an exception as follows: Oracle relies heavily on the concept of a user's default tablespace. Interestingly, this is what you just mentioned above :-). Gavin ---(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] Using ALTER TABLESPACE in pg_dump
Gavin Sherry <[EMAIL PROTECTED]> writes: > [ ... in Oracle: ] > So, when you create a new database, you can determine where the 'system > catalogs' are by setting the datafile location for the system catalog > tablespaces. You can *also* set a default tablespace for the database -- > default in the sense that all newly created objects with no explicit > tablespace clause are created in the tablespace. With an exception as > follows: Oracle relies heavily on the concept of a user's default > tablespace. Interestingly, this is what you just mentioned above :-). So if we went with a GUC-driven approach, we could emulate both of those things easily, because people could set the default_tablespace GUC variable in either ALTER DATABASE or ALTER USER. This is starting to sound like a win. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] timestamp with time zone a la sql99
regression=# set timezone to 'US/Arizona'; SET regression=# select now(); now --- 2004-10-25 10:52:49.441559-07 Wow! When did that get fixed? How do I keep track of this stuff if you guys keep fixing it? ;-) That's worked for ages. What doesn't work is this: usatest=# select current_timestamp at time zone 'US/Arizona'; ERROR: time zone "us/arizona" not recognized Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp with time zone a la sql99
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > So if I understand you correctly you are planning to extend the current > timestamp type to work with both named time zones and HH:MM ones? I didn't > think you wanted the last one since your plan was to store a UTC+OID where > the OID pointed to a named time zone. And I guess that you don't plan to > add 00:00, 00:01, 00:02, ... as named zones with an OID. I missed getting back to you on this, but I think we can do both. Some random points: * Once we expand timestamptz to bigger than 8 bytes, there's essentially zero cost to making it 12 bytes, and for that matter we could go to 16 without much penalty, because of alignment considerations. So there's plenty of space. * What we need is to be able to represent either a fixed offset from UTC or a reference of some kind to a zic database entry. The most bit-splurging way of doing the former is a signed offset in seconds from Greenwich, which would take 17 bits. It'd be good enough to represent the offset in minutes, which needs only 11 bits. * I suggested OIDs for referencing zic entries, but we don't have to do that; any old mapping table will do. 16 bits would surely be plenty to assign a unique label to every present and future zic entry. * My inclination therefore is to extend timestamptz with two 16-bit fields, one being the offset from UTC (in minutes) and one being the zic identifier. If the identifier is zero then it's a straight numeric offset from UTC and the offset field is all you need (this is the SQL spec compatible case). If the identifier is not zero then it gives you an index to look up the timezone rules. However, there is no need for the offset field to go to waste; we should store the offset anyway, since that might save a trip to the zic database in some cases. * It's not clear to me yet whether the stored offset in the second case should be the zone's standard UTC offset (thus always the same for a given zone ID) or the current-time offset for the timestamp (thus different if the timestamp is in daylight-savings or standard time). * If we store the current-time offset then it almost doesn't matter whether the timestamp itself is stored as a UTC or local time value; you can trivially translate either to the other by adding or subtracting the offset (*60). But I'm inclined to store UTC for consistency with past practice, and because it will make comparisons a bit faster: you can compare the timestamps without adjusting first. Generally I think comparisons ought to be the best-optimized operations in a Postgres datatype, because index operations will do a ton of 'em. (We definitely do NOT want to have to visit the zic database in order to compare two timestamptz values.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp with time zone a la sql99
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > That's worked for ages. What doesn't work is this: > usatest=# select current_timestamp at time zone 'US/Arizona'; > ERROR: time zone "us/arizona" not recognized Right, and similarly you can do regression=# select '2004-10-25 21:32:33.430222 MST'::timestamptz; timestamptz --- 2004-10-26 00:32:33.430222-04 (1 row) but not regression=# select '2004-10-25 21:32:33.430222 US/Arizona'::timestamptz; ERROR: invalid input syntax for type timestamp with time zone: "2004-10-25 21:32:33.430222 US/Arizona" I would like to see both of these cases working in 8.1; and furthermore I'd like to see the timezone specs coming back as entered, not as bare numeric offsets. (This will need to be adjustable via a DateStyle option, of course, but I want the information to be in there whether it is displayed or not.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Proposed Query Planner TODO items
Hi, Thanks for the info. Would you give me the tarball? -- Tatsuo Ishii > Hi Tatsuo, > > Yes, I've been updating the dbt3 kit over the past several months. > The query time graph is a new feature. It's available via BitKeeper > at bk://developer.osdl.org:/var/bk/dbt3 but I haven't tested the kit > well enough to make a v1.5 release yet. If BitKeeper isn't something > you can use, I can make a preliminary tarball for you. > > Mark > > On Mon, Oct 25, 2004 at 01:59:46PM +0900, Tatsuo Ishii wrote: > > Mark, > > > > I see nice graphs for each DBT3 query(for example, > > http://developer.osdl.org/markw/dbt3-pgsql/42/q_time.png). It seems > > they do not come with normal dbt3-1.4 kit. How did you get them? > > Maybe you have slightly modified dbt3 kit? > > -- > > Tatsuo Ishii > > > > > On 6 Feb, To: [EMAIL PROTECTED] wrote: > > > > On 5 Jan, Tom Lane wrote: > > > >> Josh Berkus <[EMAIL PROTECTED]> writes: > > > >>> 2) DEVELOP BETTER PLANS FOR "OR GROUP" QUERIES > > > >> > > > >>> Summary: Currently, queries with complex "or group" criteria get devolved by > > > >>> the planner into canonical and-or filters resulting in very poor execution on > > > >>> large data sets. We should find better ways of dealing with these queries, > > > >>> for example UNIONing. > > > >> > > > >>> Description: While helping OSDL with their derivative TPC-R benchmark, we ran > > > >>> into a query (#19) which took several hours to complete on PostgreSQL. > > > > > > http://developer.osdl.org/markw/dbt3-pgsql/ > > > > > > There's a short summary of the tests I ran over the weekend, with links > > > to detailed retults. Comparing runs 43 (7.4) and 52 (7.5devel), it > > > looks like query #7 had the only significant improvement. Oprofile data > > > should be there too, if that'll help. Let us know if there's anything > > > else we can try for you. > > > > > > Mark > > > > > > ---(end of broadcast)--- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > > -- > Mark Wong - - [EMAIL PROTECTED] > Open Source Development Lab Inc - A non-profit corporation > 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 > (503) 626-2455 x 32 (office) > (503) 626-2436 (fax) > http://developer.osdl.org/markw/ > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Philip Warner wrote: > At 09:28 AM 26/10/2004, Tom Lane wrote: > > >I can't see what a search path would be good for. > > Nothing at this stage. The idea of a tablespace search path was that restores could specify a fallback if the tablespace doesn't exist, but it seems easier for the SET to just fail because the tablespace doesn't exist and the object goes into the default location. -- 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 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
Tom Lane wrote: > > I think a viable solution is to go with the latter (ie, for CREATE TABLE > > foo(i int primary key) TABLESPACE ts; the index on i is created in > > default_tablespace). However, I might be nice to be able to specify the > > tablespace as part of the primary key clause. I say nice, but not > > necessary. > > We already have that don't we? > > create table foo (f1 int, > primary key (f1) using index tablespace its) > tablespace tts; > > The question is where to put foo_pkey when "using index tablespace" > isn't there but "tablespace" is. I think that lacking a tablespace clause in the index section the behavior of least surprise would be to use the outer tablespace specification if it exists, and if not use the GUC variable for the tablespace (basically the tablespace of the table for the index). We already name the tablespace using our own name if we create it as part of CREATE TABLE so it seems natural to also use the tablespace of the table. The idea that a non-specified value defaults to the outer level's default (tablespace) is a natural behavior people expect. -- 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] Command-line parsing in pg_ctl is not portable
Peter Eisentraut wrote: > The command-line argument parsing in pg_ctl is not portable. This is the > output on a glibc system: > > $ pg_ctl start stop > pg_ctl: too many command-line arguments (first is "start") > > But: > > $ POSIXLY_CORRECT=1 pg_ctl start stop > pg_ctl: too many command-line arguments (first is "stop") > > This is probably because GNU getopt rearranges the arguments, and since pg_ctl > uses two while loops to try to allow non-option arguments before options, > things may get reordered multiple times. > > Now this particular case is minor trouble, but I wonder in what other > situations arguments will get reordered where the order does make a > difference. Yea, I found that GNU getopt reordering thing to be very strange. I can imagine some risks to such reordering. Fortunately we don't have any other commands where we have to do this weird double-calls. -- 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 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Tom Lane <[EMAIL PROTECTED]> writes: > I see no reason to hardwire such a number. On any hardware, the > distribution is going to be double-humped, and it will be pretty easy to > determine a cutoff after minimal accumulation of data. Well my stats-fu isn't up to the task. My hunch is that the wide range that the disk reads are spread out over will throw off more sophisticated algorithms. Eliminating hardwired numbers is great, but practically speaking it's not like any hardware is ever going to be able to fetch the data within 100us. If it does it's because it's really a solid state drive or pulling the data from disk cache and therefore really ought to be considered part of effective_cache_size anyways. > The real question is whether we can afford a pair of gettimeofday() calls > per read(). This isn't a big issue if the read actually results in I/O, but > if it doesn't, the percentage overhead could be significant. My thinking was to use gettimeofday by default but allow individual ports to provide a replacement function that uses the cpu TSC counter (via rdtsc) or equivalent. Most processors provide such a feature. If it's not there then we just fall back to gettimeofday. Your idea to sample only 1% of the reads is a fine idea too. My real question is different. Is it worth heading down this alley at all? Or will postgres eventually opt to use O_DIRECT and boost the size of its buffer cache? If it goes the latter route, and I suspect it will one day, then all of this is a waste of effort. I see mmap or O_DIRECT being the only viable long-term stable states. My natural inclination was the former but after the latest thread on the subject I suspect it'll be forever out of reach. That makes O_DIRECT And a Postgres managed cache the only real choice. Having both caches is just a waste of memory and a waste of cpu cycles. > Another issue is what we do with the effective_cache_size value once we > have a number we trust. We can't readily change the size of the ARC > lists on the fly. Huh? I thought effective_cache_size was just used as an factor the cost estimation equation. My general impression was that a higher effective_cache_size effectively lowered your random page cost by making the system think that fewer nonsequential block reads would really incur the cost. Is that wrong? Is it used for anything else? -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Is something broken with the list software? I'm receiving other emails from the list but I haven't received any of the mails in this thread. I'm only able to follow the thread based on the emails people are cc'ing to me directly. I think I've caught this behaviour in the past as well. Is it a misguided list software feature trying to avoid duplicates or something like that? It makes it really hard to follow threads in MUAs with good filtering since they're fragmented between two mailboxes. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] ARC Memory Usage analysis
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> Another issue is what we do with the effective_cache_size value once we >> have a number we trust. We can't readily change the size of the ARC >> lists on the fly. > Huh? I thought effective_cache_size was just used as an factor the cost > estimation equation. Today, that is true. Jan is speculating about using it as a parameter of the ARC cache management algorithm ... and that worries 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: [PATCHES] [HACKERS] ARC Memory Usage analysis
On Tue, 26 Oct 2004, Greg Stark wrote: > I see mmap or O_DIRECT being the only viable long-term stable states. My > natural inclination was the former but after the latest thread on the subject > I suspect it'll be forever out of reach. That makes O_DIRECT And a Postgres > managed cache the only real choice. Having both caches is just a waste of > memory and a waste of cpu cycles. I don't see why mmap is any more out of reach than O_DIRECT; it's not all that much harder to implement, and mmap (and madvise!) is more widely available. But if using two caches is only costing us 1% in performance, there's not really much point cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA ---(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] Using ALTER TABLESPACE in pg_dump
Dear Tom, ISTM that the core business of a database is to help organize and protect data, and it is plainly that. You just wish you won't need it, so it is somehow "abstract", but when and if you need it, it is not "second-order" at all;-) and it is much too late to redo the dump. So you create some tablespaces by hand. Big deal. I agree that is is doable this way, although not really nice. This objection is not strong enough to justify an ugly, klugy definition for where tables get created. I do also agree about this. My real point is that while reading the thread quickly, I was afraid the problem would not be better addressed at all in the coming release. It seems that I was wrong as it does not look to be the case. Any fix instead of nothing is fair enough for me. Thanks for your answer, have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
