Re: [HACKERS] mingw configure failure workaround
Andrew Dunstan wrote: Even if we don't do that can we *please* put in something that detects the error, and tells the user what they will have to do to fix it? Failing in a situation which we know we can detect and not telling the user is intolerable, IMNSHO. Can you try a more recent version of autoconf and see if that behaves more tolerably? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Timezone database questions
I have added a timezone database to CVS, and enabled it for Win32. This allows Win32 to pass our pre-1970 regression tests. There are also plans to enable this code under Unix so we have a standard database for all installs and so we can query for valid timezone names. However, this brings up some questions: 1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. 2) Does ecpg need to use our database or can it use the local one? If it uses ours, it adds a requirement that all ecpg executables need access to our database in a compile-time-defined fixed directory. (Yuck.) If it does not, is it OK that there is a mismatch? I am sure we had this issue before because you could run clients and servers on different OS's, but when everything was on the same OS, there was no mismatch, while if ecpg uses the local OS, there will be a mismatch where there wasn't one before. 3) Should we move the timezone source code from src/timezone to src/backend/timezone if only the backend is using the timezone database. 4) The timezone data files are installed in /pgsql/share/timezone. Is that OK? 5) We only had a compiled-in location for /lib in the past for dynamic loading, and had a GUC variable to override it. initdb always used /share in a fixed location, but it has a flag to override it. With /share/timezone, the server now requires the timezone database to be in a fixed location too. Do we now need a sharedir GUC variable? 6) Can someone get this code working on Unix. I get the right value for timeofday(), but now() and CURRENT_TIMESTAMP return wrong and different values. 7) Why can't we just test for valid timezones by setting the timezone string and checking that the timezone returned isn't GMT: $ TZ=abc date Sun May 2 11:02:26 GMT 2004 As you can see, our own timezone database has brought perhaps more problems than it will solve. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [pgsql-hackers-win32] Timezone database questions
I have added a timezone database to CVS, and enabled it for Win32. This allows Win32 to pass our pre-1970 regression tests. There are also plans to enable this code under Unix so we have a standard database for all installs and so we can query for valid timezone names. However, this brings up some questions: 1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. Could we just require that it is configured through postgresql.conf? IIRC there is a parameter to set it there. IMO, that would be enough. If not, we can use GetTimeZoneInformation(). It returns the standard name (amongst other things) of the tz configured in the system. Or do nothing. On unix it defaults to the value of the TZ environment variables. If you set that one before you start it, you get the same behaviour. In that case, I'd still say we *recommend* setting it in postgresql.conf, but it will still work. 2) Does ecpg need to use our database or can it use the local one? If it uses ours, it adds a requirement that all ecpg executables need access to our database in a compile-time-defined fixed directory. (Yuck.) If it does not, is it OK that there is a mismatch? I am sure we had this issue before because you could run clients and servers on different OS's, but when everything was on the same OS, there was no mismatch, while if ecpg uses the local OS, there will be a mismatch where there wasn't one before. As I've said before, I don't hink ecpg should use the same database. And AFAIK the TZ information is only used for input and output of date/time, not for storing it. So I don't see where a mismatch would cause problems of that kind. 3) Should we move the timezone source code from src/timezone to src/backend/timezone if only the backend is using the timezone database. I'll defer that to you core people. 4) The timezone data files are installed in /pgsql/share/timezone. Is that OK? I think so. But then I was the one to suggest it, so I'll defer to others to comment on this one too. 5) We only had a compiled-in location for /lib in the past for dynamic loading, and had a GUC variable to override it. initdb always used /share in a fixed location, but it has a flag to override it. With /share/timezone, the server now requires the timezone database to be in a fixed location too. Do we now need a sharedir GUC variable? I think Claudio is working on this. IIRC with the help of Tom and Peter. I'm not sure if share was specifically included in the list of directories he's working on, though, but several others. Claudio - correct me if I'm off target here. 6) Can someone get this code working on Unix. I get the right value for timeofday(), but now() and CURRENT_TIMESTAMP return wrong and different values. I can perhaps take a look at this a bit later, but if someone else can step up and do it sooner, I'd be happy to see that :-) 7) Why can't we just test for valid timezones by setting the timezone string and checking that the timezone returned isn't GMT: $ TZ=abc date Sun May 2 11:02:26 GMT 2004 Good question. I *think* that should be safe. At least with the new TZ library. Not sure about others. As you can see, our own timezone database has brought perhaps more problems than it will solve. Not sure if I agree here. At least not in the win32 scope. //Magnus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Fixed directory locations in installs
I have been looking at our use of fixed directory specifications in binaries. Right now we have libdir (dynamic_library_path) predefined in the compile, with a GUC to override it. initdb also needs to be able to find its input files, and that can be overridden by an initdb flag. Adding the timezone database, backends now need to know where /share is, not just initdb. Seems we need a new GUC variable for that too. Also, Win32 installs are going to want to be more directory independent than Unix. Right now if we move /lib or (or in the future /share) we need to edit postgresql.conf. Is there an easier way to do this? Should initdb be setting libdir and sharedir in GUC, rather than leave these as compiled-in defaults? Because Win32 can probe for the location of the binary, it seems it should check to see if it can find libdir and sharedir own its own and set those GUC values accordingly as part of initdb. Basically I am sugesting that initdb set these defaults as GUC variables, rather than having the backend use compiled-in defaults, and Win32 can also use the binary location to find them and set the GUC values. In other words: #dynamic_library_path = '$libdir' could maybe become: #dynamic_library_path = '/usr/local/pgsql/lib' and if it doesn't match the default, the comment is removed: dynamic_library_path = '/usr/local/pgsql/lib' initdb would make these adjustments. Same for sharedir. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgsql-hackers-win32] Timezone database questions
Magnus Hagander wrote: I have added a timezone database to CVS, and enabled it for Win32. This allows Win32 to pass our pre-1970 regression tests. There are also plans to enable this code under Unix so we have a standard database for all installs and so we can query for valid timezone names. However, this brings up some questions: 1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. Could we just require that it is configured through postgresql.conf? IIRC there is a parameter to set it there. IMO, that would be enough. We would get too many problem reports we would get if we always defaulted the database timezone to GMT. We have to set the default, perhaps during initdb. (I just posted something else explaining why we should have initdb set sharedir and libdir too.) If not, we can use GetTimeZoneInformation(). It returns the standard name (amongst other things) of the tz configured in the system. Yep, we will need that, and for Unix too. My guess is that we will need something in the postmaster startup that will set that default based on the OS timezone. Or do nothing. On unix it defaults to the value of the TZ environment variables. If you set that one before you start it, you get the same behaviour. In that case, I'd still say we *recommend* setting it in postgresql.conf, but it will still work. Right now I think it defaults to the OS timezone if you don't set PGTZ. We can't require everyone to set PGTZ to get a reasonable default timezone. 2) Does ecpg need to use our database or can it use the local one? If it uses ours, it adds a requirement that all ecpg executables need access to our database in a compile-time-defined fixed directory. (Yuck.) If it does not, is it OK that there is a mismatch? I am sure we had this issue before because you could run clients and servers on different OS's, but when everything was on the same OS, there was no mismatch, while if ecpg uses the local OS, there will be a mismatch where there wasn't one before. As I've said before, I don't hink ecpg should use the same database. And AFAIK the TZ information is only used for input and output of date/time, not for storing it. So I don't see where a mismatch would cause problems of that kind. OK, but Tom seemed to think it should be used for ecpg, but I am just asking. 7) Why can't we just test for valid timezones by setting the timezone string and checking that the timezone returned isn't GMT: $ TZ=abc date Sun May 2 11:02:26 GMT 2004 Good question. I *think* that should be safe. At least with the new TZ library. Not sure about others. As you can see, our own timezone database has brought perhaps more problems than it will solve. Not sure if I agree here. At least not in the win32 scope. Yea, new code is a clearly good on Win32. -- 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] SET WITHOUT CLUSTER patch
Christopher Kings-Lynne wrote: Now, I have to change that relation_expr to qualified_name. However, this causes shift/reduce errors. (Due to ALTER TABLE relation_expr SET WITHOUT OIDS.) Well, seems like what you have to do is leave it as relation_expr as far as bison is concerned, but test in the C-code action and error out if * was specified. (Accepting ONLY seems alright to me.) Actually, it occurs to me that the SET WITHOUT CLUSTER form CAN recurse. Should I make it do that, even though the CLUSTER ON form cannot? I just thought about this. CLUSTER is more of a storage-level specification, rather than a logical one. Seems it is OK that WITOUTH CLUSTER not recurse into inherited tables, especially since the CLUSTER command does not. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Fixed directory locations in installs
Bruce Momjian wrote: Also, Win32 installs are going to want to be more directory independent than Unix. Why? Because Win32 can probe for the location of the binary, it seems it should check to see if it can find libdir and sharedir own its own and set those GUC values accordingly as part of initdb. This is just going to open up the possibility of silently finding the wrong files. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Fixed directory locations in installs
Peter Eisentraut wrote: Bruce Momjian wrote: Also, Win32 installs are going to want to be more directory independent than Unix. Why? Because when I install Win32 stuff on my machine via an installer, it says Where do you want the files and puts it in C:\ or C:\Program Files or whatever I supply. We need to have that functionality. Because Win32 can probe for the location of the binary, it seems it should check to see if it can find libdir and sharedir own its own and set those GUC values accordingly as part of initdb. This is just going to open up the possibility of silently finding the wrong files. Yes, it does. We need to check if the directory actually exists. If the files we need don't exist in there, we will throw an error anyway, and they will have to use a flag to specify the location. Win32 is going to install everything under one directory anyway, so it should work fine in most cases. The only problem case would be where you have an initdb binary that sits in a directory tree where the ../share and ../lib have files that initdb needs, but they don't match the initdb version or something, but that seems like a wacked-out configuration by any measure. -- 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: [HACKERS] Fixed directory locations in installs
Peter Eisentraut wrote: Bruce Momjian wrote: Also, Win32 installs are going to want to be more directory independent than Unix. Why? Common practice, for one thing. Windows programs are typically relocatable, and Windows admins regard programs that rely on hardcoded paths very poorly indeed. The usual location used by an installer is something like %ProgramFiles%/progname or %ProgramFiles%/progname/version - the definition of %ProgramFiles% is determined by the machine it is being installed on, not by the installer, and certainly can't be set at compile time. The machine might not even have a C: drive, for instance. But this is not only a Windows issue, as Tom reminded us recently. If I understood him correctly, there have been calls for relocatable installations from other binary packagers. Because Win32 can probe for the location of the binary, it seems it should check to see if it can find libdir and sharedir own its own and set those GUC values accordingly as part of initdb. This is just going to open up the possibility of silently finding the wrong files. Maybe it could be improved by using more version markers? cheers andrew ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SET WITHOUT CLUSTER patch
Actually, it occurs to me that the SET WITHOUT CLUSTER form CAN recurse. Should I make it do that, even though the CLUSTER ON form cannot? I just thought about this. CLUSTER is more of a storage-level specification, rather than a logical one. Seems it is OK that WITOUTH CLUSTER not recurse into inherited tables, especially since the CLUSTER command does not. The patch I submitted earlier already does do recursion - I don't see why it shouldn't really. It's better than failing saying that legal grammar is in fact illegal :) Chris ---(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] SET WITHOUT CLUSTER patch
Christopher Kings-Lynne wrote: Actually, it occurs to me that the SET WITHOUT CLUSTER form CAN recurse. Should I make it do that, even though the CLUSTER ON form cannot? I just thought about this. CLUSTER is more of a storage-level specification, rather than a logical one. Seems it is OK that WITOUTH CLUSTER not recurse into inherited tables, especially since the CLUSTER command does not. The patch I submitted earlier already does do recursion - I don't see why it shouldn't really. It's better than failing saying that legal grammar is in fact illegal :) Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SET WITHOUT CLUSTER patch
Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. Well, maybe - up to you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Fixed directory locations in installs
Bruce Momjian wrote: In other words: #dynamic_library_path = '$libdir' could maybe become: #dynamic_library_path = '/usr/local/pgsql/lib' Not sure I follow - dynamic_library_path is a colon-separated set of paths. How will somone using $libdir have that resolved? ISTM we need to have another GUC var which sets it, rather than have it compiled in and not able to be overridden. It's not really an initdb-time thing either - it's an install-time thing - so the installer could add the right path to postgresql.conf.sample. Of course, if people move directories around after installation they are asking to shoot themselves in the foot, but that's true now anyway. 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] Fixed directory locations in installs
Andrew Dunstan [EMAIL PROTECTED] writes: Peter Eisentraut wrote: This is just going to open up the possibility of silently finding the wrong files. Maybe it could be improved by using more version markers? AFAICS the sharedir will already be sufficiently checked by means of initdb's check on the postgres.bki version marker. In some sense, the sharedir used by initdb is the *right* one for an installation by definition --- I'm not even convinced that we should allow people to fool with this after the fact. (However, it's probably not worth the trouble to develop a non-GUC mechanism to transmit the setting from initdb to backend.) We could add a version-marker file to libdir, but it'd not really be a sufficient check since people might copy their own shlibs in there from a prior installation without recompiling; and as soon as someone adds more directories to dynamic_library_path, all bets are off anyway. We've seen the wrong version of plpgsql.so symptom often enough that I've thought seriously of insisting on a backend-version marker embedded right into shlibs loaded by the backend. This'd be easy enough if we were willing to demand a source code addition in loadable modules, say a macro BACKEND_VERSION_MARKER which'd compile to some sort of preinitialized global variable or constant function returning a version string. I haven't been able to think of a way to insert such a marker without source-level cooperation though. 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] Fixed directory locations in installs
I wrote: AFAICS the sharedir will already be sufficiently checked by means of initdb's check on the postgres.bki version marker. In some sense, the sharedir used by initdb is the *right* one for an installation by definition --- I'm not even convinced that we should allow people to fool with this after the fact. Actually, looking at the present contents of the sharedir, I'm not even sure that the backend needs to access it at all. Most of the files in there are used only by initdb. The only thing that seems needed after initdb is unknown.pltcl, and I'd not have a problem with moving that to libdir. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Fixed directory locations in installs
Tom Lane wrote: I wrote: AFAICS the sharedir will already be sufficiently checked by means of initdb's check on the postgres.bki version marker. In some sense, the sharedir used by initdb is the *right* one for an installation by definition --- I'm not even convinced that we should allow people to fool with this after the fact. Actually, looking at the present contents of the sharedir, I'm not even sure that the backend needs to access it at all. Most of the files in there are used only by initdb. The only thing that seems needed after initdb is unknown.pltcl, and I'd not have a problem with moving that to libdir. Are you aware timezone is now in /share, at least in Win32, and maybe on Unix some day? -- 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] ecpg and the timezone database
On Fri, Apr 30, 2004 at 04:48:33PM -0400, Bruce Momjian wrote: Does ecpg need to use the same timezone database as the backend? I have to check what you changed. ecpg itself does not use the timezone database, but some of that code is used in pgtypeslib. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers-win32] Timezone database questions
1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. Could we just require that it is configured through postgresql.conf? IIRC there is a parameter to set it there. IMO, that would be enough. We would get too many problem reports we would get if we always defaulted the database timezone to GMT. We have to set the default, perhaps during initdb. (I just posted something else explaining why we should have initdb set sharedir and libdir too.) Set the defualt timezone at initdb sounds a bit weird. Again, timezone stuff is only used to present data, not to change it. You can even change it during a transaction. Setting the default at inidb seems weird. If you want to set the default, use postgresql.conf. Otherwise, we need to pick it up somewhere else. If not, we can use GetTimeZoneInformation(). It returns the standard name (amongst other things) of the tz configured in the system. Yep, we will need that, and for Unix too. Can't tell you how to do it on Unix :-( Or do nothing. On unix it defaults to the value of the TZ environment variables. If you set that one before you start it, you get the same behaviour. In that case, I'd still say we *recommend* setting it in postgresql.conf, but it will still work. Right now I think it defaults to the OS timezone if you don't set PGTZ. We can't require everyone to set PGTZ to get a reasonable default timezone. Note - TZ, not PGTZ. At least it says so in the postgresql.conf that is installed. So it's at least a standard env variable, and not a PG only one. I don't know how often this one is actually set, though. //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ecpg and the timezone database
Michael Meskes wrote: On Fri, Apr 30, 2004 at 04:48:33PM -0400, Bruce Momjian wrote: Does ecpg need to use the same timezone database as the backend? I have to check what you changed. ecpg itself does not use the timezone database, but some of that code is used in pgtypeslib. For changes see include/port.h: #define localtime(timep) pg_localtime(timep) #define gmtime(timep) pg_gmtime(timep) #define asctime(timep) pg_asctime(timep) #define ctime(timep) pg_ctime(timep) #define difftime(t1,t2) pg_difftime(t1,t2) #define mktime(tm) pg_mktime(tm) #define tzset pg_tzset Right now it is only Win32, but there are plans to use this for all ports. -- 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] Fixed directory locations in installs
Bruce Momjian wrote: Tom Lane wrote: I wrote: AFAICS the sharedir will already be sufficiently checked by means of initdb's check on the postgres.bki version marker. In some sense, the sharedir used by initdb is the *right* one for an installation by definition --- I'm not even convinced that we should allow people to fool with this after the fact. Actually, looking at the present contents of the sharedir, I'm not even sure that the backend needs to access it at all. Most of the files in there are used only by initdb. The only thing that seems needed after initdb is unknown.pltcl, and I'd not have a problem with moving that to libdir. Are you aware timezone is now in /share, at least in Win32, and maybe on Unix some day? We could move the timezone under /lib if that would make things easier. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [INTERFACES] [HACKERS] ecpg and the timezone database
Michael Meskes wrote: On Fri, Apr 30, 2004 at 04:48:33PM -0400, Bruce Momjian wrote: Does ecpg need to use the same timezone database as the backend? I have to check what you changed. ecpg itself does not use the timezone database, but some of that code is used in pgtypeslib. Yea, that's where I saw it used. -- 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] [pgsql-hackers-win32] Timezone database questions
Magnus Hagander wrote: 1) How do we set the default local timezone for our database? The OS knows the local timezone. How do we set our local timezone on Win32? On Unix? (On Unix, there is usually an /etc/localtime file that is created during install.) Perhaps we can query the current timezone specification (e.g. EDT), and do some kind of lookup. I know of no way to get the full specification, e.g. EST5EDT or America/New_York. Could we just require that it is configured through postgresql.conf? IIRC there is a parameter to set it there. IMO, that would be enough. We would get too many problem reports we would get if we always defaulted the database timezone to GMT. We have to set the default, perhaps during initdb. (I just posted something else explaining why we should have initdb set sharedir and libdir too.) Set the defualt timezone at initdb sounds a bit weird. Again, timezone stuff is only used to present data, not to change it. You can even change it during a transaction. Setting the default at inidb seems weird. If you want to set the default, use postgresql.conf. Otherwise, we need to pick it up somewhere else. Yea, sorry, I meant to say we need to pick up the local timezone on postmaster start. If not, we can use GetTimeZoneInformation(). It returns the standard name (amongst other things) of the tz configured in the system. Yep, we will need that, and for Unix too. Can't tell you how to do it on Unix :-( Or do nothing. On unix it defaults to the value of the TZ environment variables. If you set that one before you start it, you get the same behaviour. In that case, I'd still say we *recommend* setting it in postgresql.conf, but it will still work. Right now I think it defaults to the OS timezone if you don't set PGTZ. We can't require everyone to set PGTZ to get a reasonable default timezone. Note - TZ, not PGTZ. At least it says so in the postgresql.conf that is installed. So it's at least a standard env variable, and not a PG only one. I don't know how often this one is actually set, though. Yes, sorry, TZ. It is not set on my box. -- 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] Fixed directory locations in installs
Andrew Dunstan wrote: Common practice, for one thing. Windows programs are typically relocatable, and Windows admins regard programs that rely on hardcoded paths very poorly indeed. OK, but how can that work in general? How do other programs handle this? I don't think we should design a solution that goes like ok, if we move that file from share to lib then we could get away with it for now. That will only postpone the potential problems. There needs to be a definite and fixed place where programs can go looking to find the files they need. Maybe it should be stored in that registry thing? ---(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] Fixed directory locations in installs
Common practice, for one thing. Windows programs are typically relocatable, and Windows admins regard programs that rely on hardcoded paths very poorly indeed. OK, but how can that work in general? How do other programs handle this? I don't think we should design a solution that goes like ok, if we move that file from share to lib then we could get away with it for now. That will only postpone the potential problems. There needs to be a definite and fixed place where programs can go looking to find the files they need. Maybe it should be stored in that registry thing? To make it work more cross-platform, replace that registry thing with postgresql.conf. It's basically the same thing, except the registry has a hierarchy model. And yes, that's how most other programs handle it. //Magnus ---(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] Fixed directory locations in installs
Magnus Hagander wrote: To make it work more cross-platform, replace that registry thing with postgresql.conf. It's basically the same thing, except the registry has a hierarchy model. That only works as long as all the files we want to refer to are used by the server. But how will psql find /etc/psqlrc, how will libpq find pg_service.conf, how will pg_dumpall find pg_dump, etc.? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixed directory locations in installs
Peter Eisentraut wrote: Andrew Dunstan wrote: Common practice, for one thing. Windows programs are typically relocatable, and Windows admins regard programs that rely on hardcoded paths very poorly indeed. OK, but how can that work in general? How do other programs handle this? I don't think we should design a solution that goes like ok, if we move that file from share to lib then we could get away with it for now. That will only postpone the potential problems. There needs to be a definite and fixed place where programs can go looking to find the files they need. Maybe it should be stored in that registry thing? Not portable. Also, storing things in the registry is pretty much the Windows equivalent of storing things in /etc, an idea which has lately been treated with less than universal approval, and suffers from almost exactly the same problems. In particular, we need to ensure that a) you don't need to be root/Administrator to install, and b) you can reliably have multiple installations coexisting. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixed directory locations in installs
Peter Eisentraut wrote: Magnus Hagander wrote: To make it work more cross-platform, replace that registry thing with postgresql.conf. It's basically the same thing, except the registry has a hierarchy model. That only works as long as all the files we want to refer to are used by the server. But how will psql find /etc/psqlrc, how will libpq find pg_service.conf, how will pg_dumpall find pg_dump, etc.? Actually, postgresql.conf only works once they run initdb (and we have PGDATA defined). We still have the open issue of how initdb finds postgresql.conf.sample unless we embed the file in the initdb binary (double-yuck). For pg_dump on Unix, we hopefully have it in our path, but on Win32, we will not. For Win32, we could use the registry. For Unix, we can't use /etc because we can't be sure we are root. Can we create a dot-file in the user's home directory during install? -- 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] SET WITHOUT CLUSTER patch
Christopher Kings-Lynne wrote: Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. Well, maybe - up to you. Well, if we don't recurse on creation, does it make sense to recurse on destruction? Seems it might surpise people. Do we have that asymetry in any other area? -- 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] Fixed directory locations in installs
Bruce Momjian wrote: Peter Eisentraut wrote: Magnus Hagander wrote: To make it work more cross-platform, replace that registry thing with postgresql.conf. It's basically the same thing, except the registry has a hierarchy model. That only works as long as all the files we want to refer to are used by the server. But how will psql find /etc/psqlrc, how will libpq find pg_service.conf, how will pg_dumpall find pg_dump, etc.? Actually, postgresql.conf only works once they run initdb (and we have PGDATA defined). We still have the open issue of how initdb finds postgresql.conf.sample unless we embed the file in the initdb binary (double-yuck). For pg_dump on Unix, we hopefully have it in our path, but on Win32, we will not. For Win32, we could use the registry. For Unix, we can't use /etc because we can't be sure we are root. Can we create a dot-file in the user's home directory during install? We can't be sure we are Administrator either. Binaries can find other binaries the way they do now: look in our own location, then in the path. Other files could be found by looking in 1) as per commandline (e.g. -L in initdb) 2) hardcoded location, 3) our-location/../share No config files / registry entries should be necessary, AFAICS. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET WITHOUT CLUSTER patch
On Sun, May 02, 2004 at 06:23:30PM -0400, Bruce Momjian wrote: Christopher Kings-Lynne wrote: Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. Well, maybe - up to you. Well, if we don't recurse on creation, does it make sense to recurse on destruction? Seems it might surpise people. Do we have that asymetry in any other area? I'm not sure if it's assymetric. You can't recursively set the cluster bit, because child tables may not have an equally named index. However when you are unsetting the bit it doesn't matter how is the index named. I'm not sure what side does this argument favor. I'd say ALTER TABLE/WITHOUT CLUSTER shouldn't recurse but I don't feel strongly about it. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Everybody understands Mickey Mouse. Few understand Hermann Hesse. Hardly anybody understands Einstein. And nobody understands Emperor Norton. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fixed directory locations in installs
Andrew Dunstan wrote: Binaries can find other binaries the way they do now: look in our own location, then in the path. No, we can't look into the path. We have no information that says that anything useful pertaining to our installation is in the path. Other files could be found by looking in 1) as per commandline (e.g. -L in initdb) 2) hardcoded location, 3) our-location/../share Nothing says that ../share contains anything useful. Maybe it's ../share/pgsql, or maybe ../share/postgresql, or maybe ../share/postgresql-7.4.2 or maybe it's elsewhere altogether because you have just moved your installation tree to make room for a new one. We can't take guesses like this based on usual installations. The only hard facts that we can use are hardcoded/compiled-in locations and explicit information passed via command-line arguments or environment variables. None of this seems to be useful for Windows installations. As far as I recall, the Windows installation routines only ask you for one installation directory but not all the individual ones. If this is true, then we could hardcode relative paths, but maybe I'm mistaken. Can someone give a couple of full examples of typical Windows installation layouts? ---(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] Fixed directory locations in installs
Peter Eisentraut wrote: Andrew Dunstan wrote: Binaries can find other binaries the way they do now: look in our own location, then in the path. No, we can't look into the path. We have no information that says that anything useful pertaining to our installation is in the path. Well, assuming all the binaries are installed in one location our own location should do the trick. Other files could be found by looking in 1) as per commandline (e.g. -L in initdb) 2) hardcoded location, 3) our-location/../share Nothing says that ../share contains anything useful. Maybe it's ../share/pgsql, or maybe ../share/postgresql, or maybe ../share/postgresql-7.4.2 or maybe it's elsewhere altogether because you have just moved your installation tree to make room for a new one. We can't take guesses like this based on usual installations. The only hard facts that we can use are hardcoded/compiled-in locations and explicit information passed via command-line arguments or environment variables. None of this seems to be useful for Windows installations. As far as I recall, the Windows installation routines only ask you for one installation directory but not all the individual ones. If this is true, then we could hardcode relative paths, but maybe I'm mistaken. Can someone give a couple of full examples of typical Windows installation layouts? The only one I can think is typical is all in one location, e.g. as if you had specified --prefix=c:/foo/postgresql But there might be more exotic animals out there. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET WITHOUT CLUSTER patch
Alvaro Herrera wrote: On Sun, May 02, 2004 at 06:23:30PM -0400, Bruce Momjian wrote: Christopher Kings-Lynne wrote: Uh, if the CLUSTER doesn't recurse, the WITHOUT shouldn't either, I think, and throwing an error seems fine to me, even if it isn't the same wording as a syntax error. Well, maybe - up to you. Well, if we don't recurse on creation, does it make sense to recurse on destruction? Seems it might surpise people. Do we have that asymetry in any other area? I'm not sure if it's assymetric. You can't recursively set the cluster bit, because child tables may not have an equally named index. However when you are unsetting the bit it doesn't matter how is the index named. Right, we can recurse on WITHOUT and not using WITH, but would people expect WITHOUT to recurse? If we allowed indexes to span tables, it would be nice for both to recurse, but because we don't, I think neither should. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
We are seeing occasional long lockouts from out DB. When I do a 'select * from pg_locks', I find that everybody is waiting for pg_listener, and that the lock on pg_listener is currently held by a long-running ANALYZE VERBOSE. I saw the following in the change logs (not sure if it's relevant): should have gotten this notify. But to do that, we'd have to wait to see if he commits or not, or make UNLISTEN hold exclusive lock on pg_listener until commit. Either of these answers is deadlock-prone, not to mention horrible for interactive performance. Do it this way for now. (What happened to that project to do LISTEN/NOTIFY in memory with no table, anyway?) Does this mean that ANALYZE will take an exclusive lock on pg_listener until the ANALYZE finishes? Or is there some other cause? Any help or suggestions would be appreciated... 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 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] [pgsql-hackers-win32] Timezone database questions
5) We only had a compiled-in location for /lib in the past for dynamic loading, and had a GUC variable to override it. initdb always used /share in a fixed location, but it has a flag to override it. With /share/timezone, the server now requires the timezone database to be in a fixed location too. Do we now need a sharedir GUC variable? I think Claudio is working on this. IIRC with the help of Tom and Peter. I'm not sure if share was specifically included in the list of directories he's working on, though, but several others. Claudio - correct me if I'm off target here. /share was in the list. I've received no reply from Tom or Peter. Only solution so far is the one rejected a little over a month ago. Unless someone pipes up soon, I'm just going to re-ask that it be accepted (iirc, Bruce had an off-list idea he was going to post too). Cheers, Claudio --- Certain disclaimers and policies apply to all email sent from Memetrics. For the full text of these disclaimers and policies see a href=http://www.memetrics.com/emailpolicy.html;http://www.memetrics.com/em ailpolicy.html/a ---(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] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner [EMAIL PROTECTED] writes: Does this mean that ANALYZE will take an exclusive lock on pg_listener until the ANALYZE finishes? Or is there some other cause? ANALYZE does not take an exclusive lock on anything. However, the async.c functions want AccessExclusiveLock on pg_listener, so they quite possibly would get blocked by ANALYZE's not-so-exclusive lock. Possibly we could reduce the strength of the lock taken by the async.c functions ... I haven't thought hard about it. The long-term answer is certainly a wholesale rewrite of the listen/notify mechanism. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Does this mean that ANALYZE will take an exclusive lock on pg_listener until the ANALYZE finishes? Or is there some other cause? ANALYZE does not take an exclusive lock on anything. However, the async.c functions want AccessExclusiveLock on pg_listener, so they quite possibly would get blocked by ANALYZE's not-so-exclusive lock. Possibly we could reduce the strength of the lock taken by the async.c functions ... I haven't thought hard about it. The long-term answer is certainly a wholesale rewrite of the listen/notify mechanism. Gavin was working on it a while ago but I am not sure how far he got. -- 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] Fixed directory locations in installs
Andrew Dunstan wrote: For Win32, we could use the registry. For Unix, we can't use /etc because we can't be sure we are root. Can we create a dot-file in the user's home directory during install? We can't be sure we are Administrator either. Exactly. IMHO, using the registry is the worst possible solution, for the reasons Andrew has already pointed out (ie. multiple installs, user privileges, ...). Binaries can find other binaries the way they do now: look in our own location, then in the path. Other files could be found by looking in 1) as per commandline (e.g. -L in initdb) 2) hardcoded location, 3) our-location/../share I concur (ie. on 2, the hard-coded configure location, which is c:/msys/1.0/local/pgsql/etc on my box, will rarely point to anything useful on a virgin user machine, and could point to the wrong thing on a developers machine with multiple installs... hmmm). I'm yet to see a convincing argument for why we can't adopt the binary-location/../share approach as submitted late March. AFAICS, it was rejected on the basis that it was not platform independent (no arguments there) and that we could not rely on the .. approach. Well, why not? It would greatly simplify the Win32 installer as users need only nominate where they want PostgreSQL to live (ie. all it has to do in this regard is dump the entire pgsql directory structure under a single root, without any config), and windows users who go and muck with the internal directory structure of their installed apps don't generally expect their app to continue working... :-) The other thing to point out is that, given that it is reasonable to expect that the vast majority of Windows users won't be rolling their own install, a solution for this is *needed* for Win32, but is merely a desirable for the other platforms. If this idea is to be rejected on the grounds that we'd like a platform independent solution, then I'd like to see discussion focused in this regard. Cheers, Claudio --- Certain disclaimers and policies apply to all email sent from Memetrics. For the full text of these disclaimers and policies see a href=http://www.memetrics.com/emailpolicy.html;http://www.memetrics.com/em ailpolicy.html/a ---(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] ANALYZE locks pg_listener in EXCLUSIVE for long
At 12:45 PM 3/05/2004, Tom Lane wrote: Possibly we could reduce the strength of the lock taken by the async.c functions If possible, this seems like a great option. We currently have a large database with several hundred users who get locked out for as much as half an hour while ANALYZE runs. The data in the database is extremely dynamic, so the analyze needs to be run regularly; we could run less often but this will just mean the problem happens once per week instead of once per day. Would ACCESS SHARE be OK? 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner [EMAIL PROTECTED] writes: Would ACCESS SHARE be OK? Certainly not, since the point of the locks in async.c is that only one backend should execute those routines at a time. ExclusiveLock might work okay ... but I still haven't thought hard about it ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner [EMAIL PROTECTED] writes: If possible, this seems like a great option. We currently have a large database with several hundred users who get locked out for as much as half an hour while ANALYZE runs. If it takes half an hour to ANALYZE pg_listener, I think that ANALYZE is not your real problem :-(. You need a much more aggressive vacuuming policy on that table. Maybe a cron job issuing vacuum pg_listener once a minute would do? And get the size of the table knocked down to something less stratospheric to begin with --- perhaps stop all the listeners while you TRUNCATE the table. The existing listen/notify infrastructure isn't really designed for notification rates exceeding a few events per minute ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
On Sun, 2 May 2004, Bruce Momjian wrote: Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: Does this mean that ANALYZE will take an exclusive lock on pg_listener until the ANALYZE finishes? Or is there some other cause? ANALYZE does not take an exclusive lock on anything. However, the async.c functions want AccessExclusiveLock on pg_listener, so they quite possibly would get blocked by ANALYZE's not-so-exclusive lock. Possibly we could reduce the strength of the lock taken by the async.c functions ... I haven't thought hard about it. The long-term answer is certainly a wholesale rewrite of the listen/notify mechanism. Gavin was working on it a while ago but I am not sure how far he got. Its basically written. It is implemented using shared memory. I got stuck when I considered the situation where we rung out of shared memory. Some emails in the archive suggested we just fire all listeners but I didn't like that. What I was considering was that when someone issues a NOTIFY, we reserve a slot for the NOTIFY (plus a message, which is why I originally looked at the problem) in shared memory. At the end of the transaction, we update a flag to say that the transaction successed or we remove it if we've aborted. Does anyone else have any thoughts about it? Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 01:46 PM 3/05/2004, Tom Lane wrote: If it takes half an hour to ANALYZE pg_listener, I think that ANALYZE is not your real problem :-(. You need a much more aggressive vacuuming policy on that table. Maybe a cron job issuing vacuum pg_listener once a minute would do? And get the size of the table knocked down to something less stratospheric to begin with --- perhaps stop all the listeners while you TRUNCATE the table. It's a general ANALYZE command for the entire DB. It's about 6GB in size, and is vacuumed as frequently as possible; there is certainly unreclaimed space, but it does not substantially outweigh used space. My *guess* is that the largest table is being ANALYZEd at the time (it uses most of the 6GB), and for some reason pg_listeners is being locked in ACCESS SHARE the entire time. Just vacuuming pg_listener produces: vacuum verbose pg_listener; INFO: vacuuming pg_catalog.pg_listener INFO: pg_listener: found 0 removable, 0 nonremovable row versions in 0 pages VACUUM 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL pre-fork speedup
I had lots of trouble posting so you may receive this more than once. My apologies.. -- Hi, I know the issue of pre-fork PostgreSQL has been discussed previously. Someone mentionned pre-fork can be implemented when schemas become available in PostgreSQL because there will be less of the need to run multiple databases. I think Oracle 7 uses pre-forking and it helps speed up the startup time considerably. Often, there are cases where connection pooling or persistent connection cannot be used efficiently (e.g. replicated or splitted databases over hundreds of machines or where persistent connection opens up too many idle connections). Instead, there's a big need to create a new connection on every query and with PostgreSQL needing to fork on every incoming connection can be quite slow. This could be a big win since even a moderate improvement at the connection level will affect almost every user. Any chance of that happening for 7.5? Thanks. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(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] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner [EMAIL PROTECTED] writes: ... for some reason pg_listeners is being locked in ACCESS SHARE the entire time. Just vacuuming pg_listener produces: vacuum verbose pg_listener; INFO: vacuuming pg_catalog.pg_listener INFO: pg_listener: found 0 removable, 0 nonremovable row versions in 0 pages VACUUM [blinks...] There's something pretty strange about that. Are you using LISTEN/NOTIFY at all? 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] ANALYZE locks pg_listener in EXCLUSIVE for long
At 02:21 PM 3/05/2004, Tom Lane wrote: [blinks...] There's something pretty strange about that. Are you using LISTEN/NOTIFY at all? Nope. 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 8: explain analyze is your friend
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long time?
Philip Warner [EMAIL PROTECTED] writes: At 02:21 PM 3/05/2004, Tom Lane wrote: [blinks...] There's something pretty strange about that. Are you using LISTEN/NOTIFY at all? Nope. In that case there's no reason for anything to be taking any particular locks on pg_listener; and it's simply not possible for ANALYZE to spend half an hour on a zero-page table if it's not blocked by a lock. Could you dig a little deeper and see where the problem really is? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I will log everything I check next time; unfortunately, when it happens, the priority is on unlocking everything so I have a limited time to play. So far, killing the ANALYZE has fixed the problem each time. 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