Re: [BUGS] BUG #4186: set lc_messages does not work
From: Dave Page [EMAIL PROTECTED] On Thu, May 29, 2008 at 2:05 AM, Thomas H. [EMAIL PROTECTED] wrote: From: Thomas H. [EMAIL PROTECTED] i've just verified that the 8.3.1 msi version provided on postgres.org also does NOT contain the locale folder files. should i report this as a separate bug/problem? How exactly did you do that? My installation certainly has it. If memory serves, it's not installed by default (you have to select it in the feature list), but it's there alright. hmm by just clicking through the standard settings. i've seen now that the national language support is set to do not install by default. so it is a feature, not a bug, sorry. i was under the obviously wrong impression a zip-file upgrade would be the same as an msi-upgrade. - thomas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4186: set lc_messages does not work
From: Tom Lane [EMAIL PROTECTED] Thomas H. [EMAIL PROTECTED] writes: nevertheless the problem/bug exists: changing LC_MESSAGES has no effect on the windows boxes, while it works on the non-win32 systems. all i really would like is to get english system messages back on our non-english win32 servers - like they were pre 8.3.1. So far as I can tell, the backend's handling of LC_MESSAGES hasn't changed at all between 8.1.2 and 8.3.1, so if it used to work for you then there's been some other relevant change. Any idea what? well... i'm not saying it worked in earlier versions. but prior to 8.3.1 (i've tested 8.3.0 and 8.2.3), the error messages were in english no matter what the config file or the os locale says. now, with 8.3.1, error system messages are always in the os' locale, and thus the bugreport. there is currently no way to set the pg system messages' language, as the LC_MESSAGES setting seems to be defunct on win32 systems. as a small proof, i installed the win32 8.3.0 from postgresql.org as a fresh install and changed the postgresl.conf' lc_messages value to 'English_UnitedStates'. here's the psql output: 8.3.0: --- postgres=# show lc_messages; lc_messages English_UnitedStates (1 row) postgres=# select x; ERROR: column x does not exist LINE 1: select x; ^ postgres=# set lc_messages='French'; SET postgres=# select x; ERROR: column x does not exist LINE 1: select x; ^ postgres=# --- -- all system messages are in english. after *upgrading* to 8.3.1 (again, using the official binaries), the output looks like this: 8.3.1: --- postgres=# show lc_messages; lc_messages -- English_UnitedStates (1 Zeile) postgres=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ postgres=# set lc_messages='French'; SET postgres=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ postgres=# show lc_messages; lc_messages - French (1 Zeile) postgres=# --- -- all system messages are in german (the os' locale) so clearly between 8.3.0 and 8.3.1, something must have changed. but the only patch that concerned win32 msvc/locale is the one you said wasn't even included... what i noticed: if i delete the folder share/locale/de/ the system messages are back to english - but that can't be THE solution, can it? :) regards, thomas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4186: set lc_messages does not work
From: Thomas H. [EMAIL PROTECTED] what i noticed: if i delete the folder share/locale/de/ the system messages are back to english - but that can't be THE solution, can it? :) well, it actually was the solution, at least to the weird part of the problem: there are two versions of win32 binaries available on postgres.org. there's the installer msi version, and there is the installerless zip version. the installer version does NOT install the folder share/locale/! so when using the msi installer, postgres has no translations at all - thus the fallback to english. the zipped version contains the share/locale/ folder. for installing 8.3.0, i've been using the msi installer version. for upgrading to 8.3.1, i've been unpacking the files from zip version into the postgresql directory... i've just verified that the 8.3.1 msi version provided on postgres.org also does NOT contain the locale folder files. should i report this as a separate bug/problem? so at least that explains the changed behaviour. nevertheless, LC_MESSAGES seems to be defunct - with the locale folder present, pg always picks the os' language and ignores the lc_message value. - thomas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4186: set lc_messages does not work
From: Tom Lane [EMAIL PROTECTED]
the patch discussed here [1] that supposedly made the win32 msvc builds use
lc_locale properly has flaws.
I think a large part of the confusion that's been evidenced in this
thread is because you are submitting a bug report about a patch that is
not in fact in 8.3.1 (and is unlikely ever to appear in 8.3.x at all).
It's unclear what you are really testing: stock 8.3.1? 8.3.1 plus that
patch? CVS HEAD plus the patch?
it's the 8.3.1 win32 binary coming from postgresql.org i'm using.
i was digging in the mailing list archives for some answers and found
the conversation about the patch which i believed to be included in
8.3.1. sorry for the mess :|
nevertheless the problem/bug exists: changing LC_MESSAGES has no effect
on the windows boxes, while it works on the non-win32 systems. all i
really would like is to get english system messages back on our
non-english win32 servers - like they were pre 8.3.1.
the main reason for this being: the german error messages have non-ascii
special characters in the error text ('»' and '«') which mess with some
of our applications (whereas english messages use '').
regards,
thomas
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4186: set lc_messages does not work
Euler Taveira de Oliveira wrote: please observe the (previously already submitted) test queries. i've removed the date/time testqueries to no further distract from the problem. the bogus query select x; always results in a german error messages no matter what LC_MESSAGES is set: OK, that's another problem. AFAIK, that's a known problem because Windows doesn't have LC_MESSAGES. The above comment (pg_locale.c) suggests that there is no verification for the locale that is been set. A possible solution is to use IsValidLocaleName() [1] or LocaleNameToLCID() [2] but it seems that they're only available for Vista. :( Maybe we could emulate one of these functions with a mapping table [3]. [searching ...] It seems there are problems with LCIDs; they don't describe the locales acurately. pgwin hackers? how does LC_MESSAGES differ from for example LC_TIME? in LC_TIME, the checking of the specified locale seems to work: endor=# set LC_MESSAGES = 'en-US'; SET endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# set LC_TIME = 'en-US'; FEHLER: ungültiger Wert für Parameter »lc_time«: »en-US« endor=# set LC_TIME = 'en'; FEHLER: ungültiger Wert für Parameter »lc_time«: »en« endor=# set LC_TIME = 'English'; SET maybe one could as a workaround just use the lc_time locale checks for lc_messages on windows systems? or at least match against the internal pgsql supported translations. i don't mind having to specify en instead of English if that gets me english error messages ;) Could you try to use one of the locale names described in [4]? i take it you meant link [3]. i've tried 'en-US' and others, same problem, errors in german (excerpt above). [1] http://msdn.microsoft.com/en-us/library/ms776379(VS.85).aspx [2] http://msdn.microsoft.com/en-us/library/ms776388(VS.85).aspx [3] http://msdn.microsoft.com/en-us/library/ms776260.aspx regards, thomas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4186: set lc_messages does not work
euler taveira de olivieira wrote: the patch discussed here [1] that supposedly made the win32 msvc builds use lc_locale properly has flaws. I think you misunderstood the feature [1] added recently. This new actually no. the problem is as i intended to point out with the system generated error messages. their language is supposedly controlled by LC_MESSAGES (as was the time/date output, according to the first february patch submission). please observe the (previously already submitted) test queries. i've removed the date/time testqueries to no further distract from the problem. the bogus query select x; always results in a german error messages no matter what LC_MESSAGES is set: endor=# set lc_messages to 'sv_SE'; SET endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'de_DE'; SET endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'English_United_States'; SET endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'fr'; SET endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; setting LC_MESSAGES in postgres.conf doesn't change anything either. and the comment there says explicitly that LC_MESSAGES is used for system errors: lc_messages = 'English_United_States' # locale for system error message # strings nevertheless, everything is outputted in german now, errors as well as logs. which seems to hint at 8.3.1 ignoring the LC_MESSAGES and always using the os' locale. regards, thomas -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4186: set lc_messages does not work
The following bug has been logged online: Bug reference: 4186 Logged by: Thomas H Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.1 Operating system: Windows 2003 Description:set lc_messages does not work Details: the patch discussed here [1] that supposedly made the win32 msvc builds use lc_locale properly has flaws. while indeed it does force pgsql to use the native win32 locale for error messages (before 8.3.1, system messages were always english), it broke the functionality to actually have the lc_messages locale set manually through conf or in a user session. the following output is from a win2003 system with German_Switzerland.1252 locale. the queries are identically to the one used as examples by the patch author. i have added a select x; to trigger a system error message to show that its actually misbehaving - all output is always in (swiss) german despite the set locale. - thomas [1] http://archives.postgresql.org/pgsql-patches/2008-02/msg00038.php Dies ist psql 8.3.1, das interaktive PostgreSQL-Terminal. Geben Sie ein: \copyright für Urheberrechtsinformationen \h für Hilfe über SQL-Anweisungen \? für Hilfe über interne Anweisungen \g oder Semikolon, um eine Anfrage auszuführen \q um zu beenden Warnung: Konsolencodeseite (850) unterscheidet sich von der Windows- Codeseite (1252). 8-Bit-Zeichen funktionieren möglicherweise nicht richtig. Einzelheiten finden Sie auf der psql-Handbuchseite unter »Notes for Windows users«. endor=# set lc_messages to 'sv_SE'; SET endor=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Mai 2008 Freitag Mai 2008 Samstag Mai 2008 Sonntag Mai 2008 Montag Mai 2008 Dienstag Mai 2008 Mittwoch Mai 2008 (7 Zeilen) endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'de_DE'; SET endor=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Mai 2008 Freitag Mai 2008 Samstag Mai 2008 Sonntag Mai 2008 Montag Mai 2008 Dienstag Mai 2008 Mittwoch Mai 2008 (7 Zeilen) endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'English_United_States'; SET endor=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Mai 2008 Freitag Mai 2008 Samstag Mai 2008 Sonntag Mai 2008 Montag Mai 2008 Dienstag Mai 2008 Mittwoch Mai 2008 (7 Zeilen) endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; ^ endor=# endor=# set lc_messages to 'fr'; SET endor=# select to_char((current_date + s.a),'TMDay TMMonth ') as dates from generate_series(0,6) as s(a); dates - Donnerstag Mai 2008 Freitag Mai 2008 Samstag Mai 2008 Sonntag Mai 2008 Montag Mai 2008 Dienstag Mai 2008 Mittwoch Mai 2008 (7 Zeilen) endor=# select x; FEHLER: Spalte »x« existiert nicht ZEILE 1: select x; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #3766: tsearch2 index creation error
Tom Lane wrote: Thomas H. [EMAIL PROTECTED] writes: Tom Lane wrote: Can any Windows hackers check into whether the WIN32 coding in wchar2char() and char2wchar() in ts_locale.c is sane? has anyone had the chance to look into that problem? i'd be more than willing to help testing an updated build if needed. After re-reading Microsoft's man pages I think I see the problem --- attached patch is applied. regards, tom lane tsearch2 works fine now in the official win32 b4 build thanks, thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3766: tsearch2 index creation error
tom lane wrote: Can any Windows hackers check into whether the WIN32 coding in wchar2char() and char2wchar() in ts_locale.c is sane? has anyone had the chance to look into that problem? i'd be more than willing to help testing an updated build if needed. After re-reading Microsoft's man pages I think I see the problem --- attached patch is applied. thank you for taking a shot at the problem. unfortunately, i still couldn't get around to get a mvc build environement up running so i can not compile the patch myself. if any of the win32-hackers (magnus?) can provide me with a binary, i can test it. else i'll wait for the next official build. thanks, thomas ---(end of broadcast)--- TIP 1: 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: [BUGS] BUG #3766: tsearch2 index creation error
Tom Lane wrote:
Operating system: Windows 2003
CREATE INDEX posts_fts_idx ON forum.posts USING gin(to_tsvector('english',
p_msg_clean));
ERROR: translation from wchar_t to server encoding failed: No error
Hmm. That error message is close to some code that is specific to the
Windows-and-UTF8 case, which might explain why I don't see it.
Can any Windows hackers check into whether the WIN32 coding in
wchar2char() and char2wchar() in ts_locale.c is sane?
has anyone had the chance to look into that problem? i'd be more than
willing to help testing an updated build if needed.
thanks,
thomas
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
the reported problem below can be reproduced by using this simple query
straight from the documentation:
SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat');
-- postgres.exe dies instantly, with the logs being the same as in the
bugreport.
interestingly using ::tsvector (which according to the documentation is
equivalent) works just fine:
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
gives the correct result.
default configuration for text search is set to:
default_text_search_config = 'pg_catalog.german'
- thomas
Original Message
Subject: [BUGS] BUG #3767: tsearch2 index creation fatal crash
From: Thomas Haegi [EMAIL PROTECTED]
To: pgsql-bugs@postgresql.org
Date: 21.11.2007 03:25
The following bug has been logged online:
Bug reference: 3767
Logged by: Thomas Haegi
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3b3
Operating system: Windows 2003
Description:tsearch2 index creation fatal crash
Details:
the previously reported problem gets worse if you execute the query 2-3
times... postgres.exe terminates:
Faulting application postgres.exe, version 8.3.0.7319, faulting module
postgres.exe, version 8.3.0.7319, fault address 0x001ced2f.
from the pgsql logs:
2007-11-21 03:24:40 CET LOG: server process (PID 2376) exited with exit
code 128
2007-11-21 03:24:40 CET LOG: terminating any other active server processes
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET WARNING: terminating connection because of crash of
another server process
2007-11-21 03:24:40 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2007-11-21 03:24:40 CET HINT: In a moment you should be able to reconnect
to the database and repeat your command.
2007-11-21 03:24:40 CET LOG: all server processes terminated;
reinitializing
2007-11-21 03:24:41 CET FATAL: pre-existing shared memory block is still in
use
2007-11-21 03:24:41 CET HINT: Check if there are any old server processes
still running, and terminate them.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 1: 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: [BUGS] BUG #3767: tsearch2 index creation fatal crash
the reported problem below can be reproduced by using this simple query
straight from the documentation:
SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat');
Works for me:
u=# set default_text_search_config = 'pg_catalog.german';
SET
u=# SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat');
to_tsvector
---
'a':1,6,10 'on':5 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
(1 row)
even when implicitly setting default_text_search_config before the query
as you did, it fails (but gives a slightly different error message):
ERROR: translation from wchar_t to server encoding failed: No such file
or directory
maybe win32 / file paths related?
there are more problems with tsvectors. this also fails:
SELECT ' just a test: 123 '::tsvector;
ERROR: syntax error in tsvector: just a test: 123
** Error **
ERROR: syntax error in tsvector: just a test: 123
SQL state: 42601
without : it works:
SELECT ' just a test 123 '::tsvector;
regards, thomas
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3767: tsearch2 index creation fatal crash
there are more problems with tsvectors. this also fails: SELECT ' just a test: 123 '::tsvector; ERROR: syntax error in tsvector: just a test: 123 That's not a bug; your input isn't valid tsvector syntax. ok. after re-reading page http://www.postgresql.org/docs/8.3/static/textsearch-intro.html#TEXTSEARCH-SEARCHES i saw my mistake. i misinterpreted the examples to show the possibility to convert *any* text by using casting to tsvector as an alternative to using to_tsvector :) to new tsearch-users, it might not be obvious clear that you can't just cast any text but should use to_tsvector. the example string 'a fat cat sat on a mat and ate a fat rat' looks like an normal random text string, especially when a tsvector in psql looks like 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 and doesn't contain stopwords like a and and, which are included in the casted string... maybe an additional example that shows the usage of to_tsvector for any input string would help... thanks, thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] 8.3b2: problem using COPY ... TO/FROM .... BINARY
hi there i'm not sure it its really a bug - the manual specifies that COPY ... BINARY between different PGSQL versions might be problematic. nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without any problems, until one table produced an error on a timestamp field: from pgsql 8.2.5: COPY users.ratings TO '/temp/ratings' BINARY; pgsql 8.3b2: COPY users.ratings FROM '/temp/ratings' BINARY; 2007-11-17 20:17:55 CET ERROR: timestamp out of range 2007-11-17 20:17:55 CET CONTEXT: COPY ratings, line 15081, column r_date when using COPY ... CVS/TEXT; everything works. by using CVS output, i was able do determine that the failing record is: 447,883,0.0,1999-01-01 00:00:00,f it seems to be the only timestamp that is failing, i've removed it from the source table and was able to binary copy the remaining data without problems. is this a bug or a feature? :) - thomas ps: is there a way to convert the binary file to plain sql to directly check which record fails? i've tried to use pg_restore to read the file, but it said does not appear to be a valid archive. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] 8.3b2: problem using COPY ... TO/FROM .... BINARY
tom lane wrote: i'm not sure it its really a bug - the manual specifies that COPY ... BINARY between different PGSQL versions might be problematic. nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without any problems, until one table produced an error on a timestamp field: I'll bet a nickel that you built one version with float timestamps and the other with integer ... both versions are the official win32 builds from postgresl.org... - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3715: StackBuilder failing
Bug reference: 3715 PostgreSQL version: 8.3b2 Operating system: Windows 2003 Description:StackBuilder failing some additional info to the just submitted bugreport: - pgAdminIII fails as well - postgres service starts fine - eventlog shows missing dependencies: Source: SideBySide: Dependent Assembly Microsoft.VC80.CRT could not be found and Last Error was The referenced assembly is not installed on your system. Source: SideBySide: Resolve Partial Assembly failed for Microsoft.VC80.CRT. Reference error message: The referenced assembly is not installed on your system. Source: SideBySide: Generate Activation Context failed for C:\Program Files\PostgreSQL\8.3-beta2\bin\StackBuilder.exe. Reference error message: The referenced assembly is not installed on your system. - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #3715: StackBuilder failing
Already fixed - theres an updated build at http://developer.pgadmin.org/~dpage/postgresql-8.3-beta2-2.zip Thanks for the report though. thanks, works fine now. maybe worth a short note in the download directory, so that others won't report the same thing? - thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #3378: UTF-8 upper() and lower() don't work
hi kenneth
these special characters work fine here:
select lower('ÆØÅ'), upper('æøå'), lower('Æble, tørret'), upper('Æble,
tørret');
result: æøå ÆØÅ æble, tørretÆBLE, TØRRET
as pavel hinted, you probably aren't using the proper locale settings
cheers,
thomas
Original Message
Subject: Re:[BUGS] BUG #3378: UTF-8 upper() and lower() don't work
From: Pavel Stehule [EMAIL PROTECTED]
To: Kenneth Christensen [EMAIL PROTECTED]
Date: 10.06.2007 15:36
Hello,
You have to well initialized database cluster with correct locales.
I don't know good danish locales, but I expect so it will be similar
with czech.
my database cluster was initialised with cs_CZ.UTF-8 and default
encoding is UTF8.
postgres=# select lower('ŽLUTÝ KŮŇ'), upper('žlutý kůň');
lower | upper
---+---
žlutý kůň | ŽLUTÝ KŮŇ
Check your locales, if is UTF8.
postgres=# SHOW lc_collate ;
lc_collate
-
cs_CZ.UTF-8
(1 row)
Regars
Pavel Stehule
2007/6/10, Kenneth Christensen [EMAIL PROTECTED]:
The following bug has been logged online:
Bug reference: 3378
Logged by: Kenneth Christensen
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2
Operating system: Mac OS 10.4.9
Description:UTF-8 upper() and lower() don't work
Details:
I have a DB where encoding is set to UTF-8.
The DB have some tables where some of the columns (varchar) contains
danish
chars.
It looks like lower() and upper() ignores the danish chars when I try to
convert to lowercase or uppercase.
E.g.
Case 1:
---
Column 'name' contains 'Æble, tørret':
select upper(food_name.name) from food_name
Result: ÆBLE, TøRRET
Expected result: ÆBLE, TØRRET
or
select lower(food_name.name) from food_name
Result: Æble, tørret
Expected result: æble, tørret
Case 2:
---
Column 'name' contains 'æøå':
select upper(food_name.name) from food_name
Result: æøå
Expected result: ÆØÅ
Case 3:
---
Column 'name' contains 'ÆØÅ':
select lower(food_name.name) from food_name
Result: ÆØÅ
Expected result: æøå
---
I can see I'm not alone with this kind of bug. This bug is really a big
problem for me.
I really don't want to replace PostgreSQL with MySQL !
Will this bug be fixed very soon?
Best regards
Kenneth Christensen
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #2853: Internal error XXOO Hangs while attempting to clear table after several successful iterations
upgrade to 8.2.0 that problem was fixed there (had it myself as well) - thomas - Original Message - From: Terry Askew [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Sent: Thursday, December 21, 2006 6:13 PM Subject: [BUGS] BUG #2853: Internal error XXOO Hangs while attempting to clear table after several successful iterations The following bug has been logged online: Bug reference: 2853 Logged by: Terry Askew Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Windows Server 2000 Description:Internal error XXOO Hangs while attempting to clear table after several successful iterations Details: Batch Process completes, then Data Profiles creation is attempted, but stalls after several Profiles create. To create the next profile,we require a data dump of the current table in memory just created, so that as the profiles are being created on the fly, the table acts as a tempTable. This is the only technical issue left to resolve so that the LIVE system can be used and accepted by our users. Pls Help. Sincerely, Terry Askew Executive VP, Software Development Foresight Technologies, LLC. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] No error when FROM is missing in subquery
Is it a bug? If no, maybe to produce warning in such cases? oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... no clue which ones :-/ - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? that was done by the application with the returned recordset. also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... LIMIT is often meaningfull only in conjuction with ORDER BY yep but not here. all i wanted to do is to get names from 2 movies and run an *observed* edit on them. what did pgsql actually do with that subquery? did it return all records for which mov_name match '%, %'? - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] No error when FROM is missing in subquery
SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? the data is damaged so the result isn't the same... regenearting it now from a backup. from first tests i would say it returned records with names that match the WHERE in the subselect. i guess what happened is: it took each record in movies.names, then run the subquery for that record which resulted in WHERE mov_id IN (mov_id) = true for records with a ', ' in the name and WHERE mov_id IN () = false for all others. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Here's a few seconds of the log output (this has been going on for 10 mins as of this e-mail being sent): 2006-11-28 16:16:10 LOG: could not fsync segment 0 of relation 1663/16404/30267: Permission denied 2006-11-28 16:16:10 ERROR: storage sync failed on magnetic disk: Permission denied Here's the FileMon output from the same seconds: 4:16:10 PM postgres.exe:3168 OPENC:\Program Files\PostgreSQL\8.1\data\base\16404\30267 DELETE PEND Options: Open Access: 0012019F I still don't want to make mdsync() treat EACCES as an ignorable error. However, in this situation we've got an infinite loop because the checkpoint will never succeed and thus the bgwriter will never reach smgrcloseall(), which seems to be what's needed to allow the deleted file to die the real death. Perhaps a suitable workaround would be to make the bgwriter do smgrcloseall in its error recovery path? That is /* * Sleep at least 1 second after any error. A write error is likely * to be repeated, and we don't want to be filling the error logs as * fast as we can. */ pg_usleep(100L); + + /* Drop open files to allow deleted files to really go away */ + smgrcloseall(); } /* We can now handle ereport(ERROR) */ PG_exception_stack = local_sigjmp_buf; Perhaps this should be #ifdef WIN32, although there's probably no harm in doing it on Unixen too. Can someone test this idea? in 8.2.0 the error messages changed a bit: 2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:15 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:16 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:17 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:18 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:19 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:20 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:21 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:22 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:23 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:24 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:25 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:26 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:27 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:28 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:29 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:30 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:31 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:32 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:33 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:52:34 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:34 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:52:35 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:36 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:37 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:38 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:39 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:40 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied 2006-12-05 03:52:41 [736] ERROR: could not open relation 1663/16692/2361668: Permission denied ... and so on. - thomas ---(end of broadcast)--- TIP 1: 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: [BUGS] fsync and semctl errors with 8.1.5/win32
in 8.2.0 the error messages changed a bit: 2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied So what's holding the file open now? It's evidently not the bgwriter. one of the unnamed postgresql.exe processes from the connection pool: postgres: db_outnow outnow 127.0.0.1(3384) idle might be related: in addition to the above messages, the log is now also flooded by: 2006-12-05 04:16:29 [5196] LOG: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. there is no pgstat.tmp file in global... - thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
2006-12-05 03:47:12 [736] LOG: could not fsync segment 0 of relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:12 [736] ERROR: storage sync failed on magnetic disk: Permission denied 2006-12-05 03:47:13 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied 2006-12-05 03:47:14 [736] ERROR: could not open relation 1663/16692/2361629: Permission denied So what's holding the file open now? It's evidently not the bgwriter. btw: FileMon reports every few seconds: 04:24:28 postgres.exe:736 OPEN D:\DB\postgreSQL.82\data\base\16692\2361629 DELETE PEND Options: Open Access: 0012019F the time corresponds to the could not open relation logentries i would interpret this as: postgresql pid 736 (bgwriter) is trying to open the file 2361629 which fails because it is marked as to be deleted. the file system operation is pending because another process (from the pgsql connection pool) is still keeping a handle open. as it is a connection pool process, it will be recycled after a while and release open handles: everytime the error messages disappear after some minutes... - thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
... in addition to the above messages, the log is now also flooded by: 2006-12-05 04:16:29 [5196] LOG: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. Hm ... there simply isn't anything that holds pgstat.stat open for long, so this behavior seems independent of any other issues we might have. Can you find any evidence about what's wrong here? hope this helps: 05:33:14 postgres.exe:5196 CREATE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: OverwriteIf Access: 00120196 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 0 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 4096 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 8192 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 12288 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 16384 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 20480 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 24576 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 28672 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 32768 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 36864 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 40960 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 45056 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 49152 Length: 1650 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: Open Access: 00110080 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS FileAttributeTagInformation 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Attributes: A 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.stat SUCCESS Options: Open Access: 0012 05:33:14 postgres.exe:5196 SET INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp * 0xC123 FileRenameInformation 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.stat SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp NOT FOUND Options: Open Access: 00110080 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp NOT FOUND Options: Open Access: 00010080 05:33:14 postgres.exe:5196 CREATE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: OverwriteIf Access: 00120196 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 0 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 4096 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 8192 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 12288 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 16384 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 20480 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 24576 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 28672 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 32768 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 36864 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 40960 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 45056 Length: 4096 05:33:14 postgres.exe:5196 WRITE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Offset: 49152 Length: 1650 05:33:14 postgres.exe:5196 CLOSE D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS 05:33:14 postgres.exe:5196 OPEN D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS Options: Open Access: 00110080 05:33:14 postgres.exe:5196 QUERY INFORMATION D:\DB\postgreSQL.82\data\global\pgstat.tmp SUCCESS FileAttributeTagInformation 05:33:14
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
So what's holding the file open now? It's evidently not the bgwriter. one of the unnamed postgresql.exe processes from the connection pool: postgres: db_outnow outnow 127.0.0.1(3384) idle Hm. I would imagine that as soon as this process does something, the messages stop? (It should close its file handle in response to a relcache flush that it will read as soon as it becomes active.) from what i observe i would say the process dies (timeouts?) and then bgwriter is happy again: here's *all* more information i got from filemon when filtering for one of the relation that produced the error: http://rafb.net/paste/results/3uozHD77.html its pid 3772 that still has a handle open, while all the others have closed it properly after pid 2780 issued a DELETE. the process itself has 3 threads that are in: - postgres.exe+0x1220 - postgres.exe!pg_queue_signal+0x120 - postgres.exe!shmctl+0x80 (i can get stacktraces for all of them if usefull) pid 3772 died at 05:55:22 (~20min after its last access to the file), and bgwriter could finally write, and the error messages are gone. - thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
2006-11-29 23:57:52 LOG: could not rename file pg_xlog/00010019005E to pg_xlog/00010019007F, continuing to try i had this one as well. good news is: this bug is fixed in 8.2 - thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Did you run into problems where transactions would hang? If so, did those disappear in 8.2? well, i wasn't really able to exactly determine under what conditions that xlog bug appeared in our case. tho it always was when lots of data is imported at once within one transaction. under normal load i've never seen the xlog bug. as far as i know it was some sort of lifelock: as with the other error messages, another postgres.exe kept a lock of the xlog file, which the bgwriter-process wanted to rename which lead to the complete halt of the db system, due to the importance of xlog/bgwriter. you can force an unload of the locked xlog file handle in processmon, and postgresql will resume normally. i had a transaction lately that created 7gb of xlog-files (vacuum full of a mid-sized table) without any xlog-lockup, so i guess this problem is really fixed in the latest 8.2 build :-) if you have hanging transactions but other db activity works well, i would rather guess its a side effect of the other file problems with the relation-files that can't be renamed. i've never been able to see any impact of that error message. even when it appears 10 times a second everything seems ok. but on the other side, in our case, we use the database as a web backend and have always around 20-30 concurrent connections, so its hard to debug. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
We were also running it on Windows Server 2003. We ended up rolling back service pack 1 and it seems to have taken care of the hanging transactions and we haven't seen a semctl error in awhile. interesting. we're using sp1 pgsql since day 1 and the problem only started when testing 8.2b1. but on the other hand, it might be that a hotfix is the cause for this error, as i haven't seen it before aug/sept 06. i sure would have noticed... - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2781: database dump/restore problems
regarding pg_dump: where there some changes from b3 to rc1 that would explain the resulting rc1 pg_dump output (-c) being half as big as with b3? No... regards, tom lane well, it was 300mb before rc1, and now its only 188mb. inbetween i did a vacuum full on one table. that shoulnd't affect backup size, should it? i'll restore one of the new backups later on to be sure all data is still there. - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
I forgot to mention - this problem is occurring on multiple Windows machines. One of them is running Windows XP Professional. The other is running Windows Server 2003. I have disabled indexing, virus scanning, and all non-essential services on both of them. The problem continues to show up even when no queries are being run (although it might always start while queries are running) seems exactly what i'm noticing since 8.2x on windows 2003 as well - no disk services (backup, virus, ...) are running that would block files, and processmon/filemon always show that the files in question are locked by pgsql processes... under higher insert/update load, the errors appear more often here, do you experience the same finding when loading bulk data? - thomas ---(end of broadcast)--- TIP 1: 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: [BUGS] fsync and semctl errors with 8.1.5/win32
Perhaps this should be #ifdef WIN32, although there's probably no harm in doing it on Unixen too. Can someone test this idea? if magnus/dave could provide me a patched rc1 exe, i could run it in our semi-productive environment for some tests. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
well yes, as the system is live, users are browsing the website. but all queries that try to access the table in question are stalled at the moment. when querying server status i'm seeing lots of queries that are waiting for access to the table. would vacuum freeze be faster? Vacuum freeze won't move tuples so it won't reclaim any more space than a normal vacuum. Cluster, however, rewrites the whole table and compacts the space, and runs faster than vacuum full on a badly bloated table. It will also recreate all indexes. will give it a try later on, thanks! In the future, instead of updating a whole table with UPDATE, you should consider doing a SELECT INTO to create a new table, dropping the old table and renaming the new one in place of the old one. the problem is: the table was far from being bloated, IMO. it was 2 days old, every record at most 2-3 times updated. the space needed for the table dropped from 400mb to roughly 200mb after the 1.5hr vacuum full... i've never had such a long vacuuming time before, even on tables that are much larger and contains more dead rows. the table uses tsearch2 and a gin-index, could that be the problem? the gin faq says a drop/create index would be much faster than a reindex. maybe this is also true when vacuuming a table with a gin-index? - thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #2781: database dump/restore problems
We have never promised backward compatibility of pg_dump output to older server versions. regarding pg_dump: where there some changes from b3 to rc1 that would explain the resulting rc1 pg_dump output (-c) being half as big as with b3? i've rerun pg_dump several times with the same result, and no error messages. - thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] 8.2rc1: vacuum full fills up disk space
this somehow sounds buggy: there's this table forum.posts which had 224mb table size, 145mb toast table size and 176mb indexes size (aproximately 60'000 rows). as i was doing some updates of all the records, i've issued a VACUUM FULL tablename... this was merely 60min ago, and it hasn't yet finished... instead in pg_xlog there are now 380+ files, totalling in over 6gb. available disk space is almost used up... the table was freshly loaded yestertday when reloading for rc1. as the process of vacuuming is still ongoing, is there something i can do to... a) .. prevent total diskspace fillup. what happens if i send a cancel signal to the process? b) .. gather some evidence if needed? c) .. check if the vacuum process is still alive? d) .. give the process in question a higher priority so it would finish sooner? thanks, thomas
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
this somehow sounds buggy: vacuum full absolutely *will* bloat your index, if run on a heavily-modified table. I do not think it will bloat pg_xlog by itself however; are you sure you don't have some other open transactions? well yes, as the system is live, users are browsing the website. but all queries that try to access the table in question are stalled at the moment. when querying server status i'm seeing lots of queries that are waiting for access to the table. would vacuum freeze be faster? a) .. prevent total diskspace fillup. what happens if i send a cancel = signal to the process? Killing it should not corrupt anything; if it does that IS a serious bug and will be met with a full alarm response... after a while it eventually finished just in time before the hd was filled up :-) 2006-11-27 07:18:04 [2096] LOG: duration: 6093332.000 ms statement: VACUUM FULL VERBOSE ANALYZE forum.posts thanks, - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2780: could not fsync segment 0
The following bug has been logged online: Bug reference: 2780 Logged by: Thomas H. Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2rc1 Operating system: windows 2003 standard Description:could not fsync segment 0 Details: still seeing lots of these errors on rc1 when doing bulk data load: 2006-11-26 04:34:30 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176087: Permission denied 2006-11-26 04:34:30 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:36:41 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176107: Permission denied 2006-11-26 04:36:41 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:38:47 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176119: Permission denied 2006-11-26 04:38:47 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:43:43 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176158: Permission denied 2006-11-26 04:43:43 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:39 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:39 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:40 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:40 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:41 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:41 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:42 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:42 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:43 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:43 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:44 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:44 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:45 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:45 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:46 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:46 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:47 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied 2006-11-26 04:59:47 [2124] ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-26 04:59:48 [2124] LOG: could not fsync segment 0 of relation 1663/16692/2176296: Permission denied all other problems i've been seeing in the earlier builds seem to be fixed. thanks for the great work! - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] xlog lockup patch (was: BUG #2712: could not fsync segment: Permission)
me wrote: i've loaded 1gb of data without any xlog-problems, whereas with the 8.2b2 executable it locked up after ~100mb. the xlog-files are cycling... if i need to test for some specific behaviour let me know. what's the status on this patch for inclusion in future 8.2 builds? would be nice to see it in b4 or rc1... thanks, thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
this bug (please see below) is unfortunately still present in beta3 (win32
build). test case still crashes the child process and lets postmaster kill
reload everything.
it is not GiST-related, i've just validated the same problem using GIN.
this breaks tsearch2 functionality on our win32 system as no
tsvector-indexing of new/existing rows is possible (crash after ~10
processed rows). searching already indexed rows works fine.
best regards,
thomas
- Original Message -
From: [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]
Cc: pgsql-bugs@postgresql.org
Sent: Tuesday, October 17, 2006 9:19 PM
Subject: Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
the following query will crash the server process:
INSERT INTO news.news
SELECT * FROM news.news2;
This is undoubtedly data-dependent. Can you supply some sample data
that makes it happen?
it's not only happening with INSERTS, but also updates. as thats easier to
test, here's how i can reproduce the error:
1. create new database (encoding: UTF8) with tsearch2 on 8.2b1 win32
(system
locale: de_CH.1252)
2. insert the data from the zip file
[http://alternize.com/pgsql/tsearch2test.zip] (be sure to also update
pg_ts_cf /
pg_ts_cfgmap as we have WIN1252 locale)
3. execute UPDATE test SET idxFTI = to_tsvector('default', sometext); or
similar queries
4. hopefully see the process crashing as i do ;-)
2006-10-17 17:23:44 LOG: server process (PID 4584) exited with exit
code -1073741819
2006-10-17 17:23:44 LOG: terminating any other active server processes
2006-10-17 17:23:44 WARNING: terminating connection because of crash of
another server process
2006-10-17 17:23:44 DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
{snipp}
2006-10-17 17:23:44 LOG: all server processes terminated; reinitializing
2006-10-17 17:23:44 LOG: database system was interrupted at 2006-10-17
17:23:41 W. Europe Daylight Time
2006-10-17 17:23:44 LOG: Windows fopen(recovery.conf,r) failed: code
2,
errno 2
2006-10-17 17:23:44 LOG: Windows fopen(pg_xlog/0001.history,r)
failed: code 2, errno 2
2006-10-17 17:23:44 LOG: Windows fopen(backup_label,r) failed: code
2,
errno 2
2006-10-17 17:23:44 LOG: checkpoint record is at 0/E2ECA728
2006-10-17 17:23:44 LOG: redo record is at 0/E2ECA728; undo record is at
0/0; shutdown FALSE
2006-10-17 17:23:44 LOG: next transaction ID: 0/514299; next OID: 6276957
2006-10-17 17:23:44 LOG: next MultiXactId: 1; next MultiXactOffset: 0
2006-10-17 17:23:44 LOG: database system was not properly shut down;
automatic recovery in progress
2006-10-17 17:23:44 LOG: redo starts at 0/E2ECA778
2006-10-17 17:23:44 LOG: unexpected pageaddr 0/DB0CC000 in log file 0,
segment 227, offset 835584
2006-10-17 17:23:44 LOG: redo done at 0/E30CBE78
2006-10-17 17:23:45 LOG: database system is ready
2006-10-17 17:23:45 LOG: Windows fopen(global/pg_fsm.cache,rb)
failed:
code 2, errno 2
2006-10-17 17:23:45 LOG: transaction ID wrap limit is 2147484172, limited
by database postgres
2006-10-17 17:23:45 LOG: Windows fopen(global/pgstat.stat,rb) failed:
code 2, errno 2
i've also tried to update each record on its own in a for-loop. here the
crash happens as well, sometimes after 10 updates, sometimes after 100
updates, sometimes even after 1 update. but eventually every record can be
updated. so i do not think its entierly content-related...
for what its worth, here's the output of pg_controldata:
pg_control version number:822
Catalog version number: 200609181
Database system identifier: 4986650172201464825
Database cluster state: in production
pg_control last modified: 17.10.2006 17:44:29
Current log file ID: 0
Next log file segment:230
Latest checkpoint location: 0/E4E0F978
Prior checkpoint location:0/E46BF420
Latest checkpoint's REDO location:0/E4E03098
Latest checkpoint's UNDO location:0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/531333
Latest checkpoint's NextOID: 6285149
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint:17.10.2006 17:43:45
Minimum recovery ending location: 0/0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index: 32
Date/time type storage: floating-point numbers
Maximum length of locale name:128
LC_COLLATE: German_Switzerland.1252
LC_CTYPE: German_Switzerland.1252
let me know if more information / data is
Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
here are the steps to reproduce:
1. intall win32 beta3 as new instance using UTF8 / en_US and tsearch2
module, everything else default
2. create new db with encoding UTF8, standard template
3. load data from http://alternize.com/pgsql/tsearch2test.zip (updated dump
so it only includes the test table/data)
4. issue query: UPDATE test SET idxFTI = to_tsvector('default', sometext);
5. watch the process die... *sniff*
---
(steps 1 2 can probably be skipped, but i wanted to have a clean test env)
best regards,
thomas
- Original Message -
From: imad [EMAIL PROTECTED]
To: Magnus Hagander [EMAIL PROTECTED]
Cc: Thomas H. [EMAIL PROTECTED]; pgsql-bugs@postgresql.org; Tom Lane
[EMAIL PROTECTED]
Sent: Sunday, November 12, 2006 5:20 PM
Subject: Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
yeah... i dont think it will be related to index anyway.
it looks to me that some extra bytes are written to the allocated
memory when locale is set to en-US etc. The warning
WARNING: detected write past chunk end in ExprContext 02C6D0F8
is generated when you write bytes on a location more than its allocated
size,
and this thing will eventualy lead to a server crash.
Now, I would suggest try to minimize the script and look for the action
with leads to this warning. Then a debug would be easy on the execution
path and I guess, there won't be any problem in parsing and planning.
Can you send me the knotty script BTW?
--Imad
www.EnterpriseDB.com
On 11/12/06, Magnus Hagander [EMAIL PROTECTED] wrote:
Ok, I've run this test on an assert enabled build (my msvc build,
actually, so I could get a debugger on it if needed). It then outputs:
WARNING: detected write past chunk end in ExprContext 02C6D0F8
WARNING: detected write past chunk end in ExprContext 02C6AEA0
WARNING: detected write past chunk end in ExprContext 02C6B200
WARNING: detected write past chunk end in ExprContext 02C44630
WARNING: detected write past chunk end in ExprContext 02C4C118
WARNING: problem in alloc set ExprContext: bogus aset link in block
02C435A8, c
hunk 02C44520
WARNING: detected write past chunk end in ExprContext 02C66440
WARNING: detected write past chunk end in ExprContext 02C3B9D0
WARNING: detected write past chunk end in ExprContext 02C3BDE8
WARNING: detected write past chunk end in ExprContext 02C4E7E0
WARNING: detected write past chunk end in ExprContext 02C47508
WARNING: problem in alloc set ExprContext: bogus aset link in block
02C435A8, c
hunk 02C47528
WARNING: detected write past chunk end in ExprContext 02C43800
WARNING: detected write past chunk end in ExprContext 02C66C90
WARNING: detected write past chunk end in ExprContext 02C68270
WARNING: detected write past chunk end in ExprContext 02C4F5D8
WARNING: problem in alloc set ExprContext: bogus aset link in block
02C4E6F8, c
hunk 02C4F5F8
WARNING: detected write past chunk end in ExprContext 02C7B680
WARNING: detected write past chunk end in ExprContext 02C45190
WARNING: detected write past chunk end in ExprContext 02C46AC8
WARNING: detected write past chunk end in ExprContext 02C3C538
WARNING: detected write past chunk end in ExprContext 02C67B90
WARNING: detected write past chunk end in ExprContext 02C438F0
WARNING: problem in alloc set ExprContext: bad single-chunk 02C43FB8 in
block 0
2C435A8
WARNING: problem in alloc set ExprContext: bogus aset link in block
02C435A8, c
hunk 02C43FB8
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The actual crash happens on line 1142 of AllocSetCheck. Full callstack
is:
postgres.exe!AllocSetCheck(MemoryContextData *
context=0x02c455b8) Line 1142 + 0x11 bytes C
postgres.exe!AllocSetReset(MemoryContextData *
context=0x02c455b8) Line 409 + 0x9 bytes C
postgres.exe!MemoryContextReset(MemoryContextData *
context=0x02c455b8) Line 129 + 0xf bytes C
postgres.exe!ExecScan(ScanState * node=0x02c227d0,
TupleTableSlot * (ScanState *)* accessMtd=0x00530b70) Line 91 + 0xc
bytes C
postgres.exe!ExecSeqScan(ScanState * node=0x02c227d0) Line 130
+ 0xe bytes C
postgres.exe!ExecProcNode(PlanState * node=0x02c227d0) Line 349
+ 0x9 bytes C
postgres.exe!ExecutePlan(EState * estate=0x02c223e8, PlanState *
planstate=0x02c227d0, CmdType operation=CMD_UPDATE, long numberTuples=0,
ScanDirection direction=ForwardScanDirection, _DestReceiver *
dest=0x02bf8d30) Line 1081 + 0x9 bytes C
postgres.exe!ExecutorRun(QueryDesc * queryDesc=0x02c44078,
ScanDirection direction=ForwardScanDirection, long count=0) Line 246 +
0x20 bytes C
postgres.exe!ProcessQuery(Query * parsetree=0x02bddab0, Plan *
plan=0x02bf7e28, ParamListInfoData * params=0x, _DestReceiver *
dest=0x02bf8d30, char * completionTag=0x00d4fb24) Line 157 + 0xd bytes
C
postgres.exe!PortalRunMulti(PortalData * portal
Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
Did you do anything to install tsearch2 into this fresh database beyond \i tsearch2.sql? no, i used the win32 setup and selected to install tsearch2 contrib module... so i didn't even had to run \i tsearch2.sql. installation logs are available if helpful. should i try a manual install of tsearch2.sql to see if that changes anything? - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
it should... at least for 8.2 this worked fine without manually installing tsearch2. the tsearch2.sql is run by the installer, the tsearch2 objects are present in template1 and thus in the freshly created db. also the function and types are there or else the table creation would fail in first place (as one of its column is tsvector). of course you have to tweak the config afterwards if you want it to run smooth on non-default locales. just for fun i set up an instance without selecting the tsearch2 option and run tsearch2.sql manually. there was no error with this script. the result in my test case is unfortunately the same - crashing. - thomas - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Thomas H. [EMAIL PROTECTED] Cc: imad [EMAIL PROTECTED]; Magnus Hagander [EMAIL PROTECTED]; pgsql-bugs@postgresql.org Sent: Sunday, November 12, 2006 6:56 PM Subject: Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector) Thomas H. [EMAIL PROTECTED] writes: no, i used the win32 setup and selected to install tsearch2 contrib module... so i didn't even had to run \i tsearch2.sql. installation logs are available if helpful. Hm, I wonder whether the windows installer changes tsearch2's configuration at all. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2712: could not fsync segment: Permission
Magnus Hagander [EMAIL PROTECTED] writes: It seems to me that it's not been included in b3. Tom? I'm waiting for some report of whether it fixes the problems? voilà: Sent: Tuesday, October 31, 2006 7:23 AM Subject: Re: [BUGS] xlog lockup patch (was: BUG #2712: could not fsync segment: Permission) i've loaded 1gb of data without any xlog-problems, whereas with the 8.2b2 executable it locked up after ~100mb. the xlog-files are cycling... if i need to test for some specific behaviour let me know. after using the patched b2 executable, the db system never locked up again, and no xlog error messages in the logs either. the other problem with locked files within the data directories is unaffected by the patch, obviously - but as that does not lock up the system, its not that severe (but still scary ;-)) regards, thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #2731: Cannot install PostgreSQL server on WinXP
Windows has another bug; they don't include a proper loopback function with the standard distribution _and_ they have some asenine view that if there's no physical network connection available, they tear down the network stack! This means that anything that connects with TCP/IP can't work, even if it's to the local machine. I consider this idiocy, but, there's windows for you in a nutshell... However, you _can_ find a proper loopback driver, I think they call it - software, of course. Also, I carry in my laptop case a special HARDWARE loopback connector! It's an RJ45 that has the wires crossed over. I use it when I'm at a customer site or otherwise away from home and my customer doesn't have the software loopback - plug my loopback connector into any twisted pair ethernet socket and suddenly the TCP/IP software works fine! there is a loopback driver shipped with windows xp / windows 2003. but you have to add it manually (add hardware -- add a new hardware device -- select from a list -- show all devices -- microsoft -- Microsoft Loopback Adapter) cheers, thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] beta2: process crash: server process (PID 4872) exited with exit code -1073741819
unfortunately, my problems with 8.2 on win32 become
more and more severe. when i wanted to test magnus compiled patch for the xlog
transaction rename lockup, i run into more problems (with the original beta2
files):
it seems that processes regurarly crash after a
number of transactions, where that number is strongly related to the amount of
data updated. for example i have one table that consists of 8 integers, 3 bools,
2 timestamps, 1 varchar(150)for columns, has 1 foreign keyand
contains roughly 6600 rows. the table is vacuumed, analyzed, reindexed. when
updating any of the integer fields, the backend process crashes after 320 to 450
updates, no matter it the updates are in 1 transaction for all or 1 update per
transaction).
there are no log entries (beside the ones from the
startup sequence) prior to the process error...
i first logged the problem as tsearch2 problem (http://archives.postgresql.org/pgsql-bugs/2006-10/msg00123.php).
but it seems it is a more general problem.
if anyone wants (db-)access for debuging or
checking stuff, please let me know.
best regards,
thomas
2006-10-31 01:04:29 [3304] LOG: 0:
database system was interrupted at 2006-10-31 01:04:27 W. Europe Standard
Time2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:46602006-10-31 01:04:29 [3304] LOG: 0: Windows
fopen("recovery.conf","r") failed: code 2, errno 22006-10-31 01:04:29 [3304]
LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:29 [3304] LOG:
0: Windows fopen("pg_xlog/0001.history","r") failed: code 2, errno
22006-10-31 01:04:29 [3304] LOCATION: AllocateFile,
fd.c:12892006-10-31 01:04:29 [3304] LOG: 0: Windows
fopen("backup_label","r") failed: code 2, errno 22006-10-31 01:04:29 [3304]
LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:29 [3304] LOG:
0: checkpoint record is at C/84D208102006-10-31 01:04:29 [3304]
LOCATION: StartupXLOG, xlog.c:47302006-10-31 01:04:29 [3304]
LOG: 0: redo record is at C/84D20810; undo record is at 0/0; shutdown
TRUE2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:47572006-10-31 01:04:29 [3304] LOG: 0: next transaction ID:
0/16121161; next OID: 66609082006-10-31 01:04:29 [3304] LOCATION:
StartupXLOG, xlog.c:47612006-10-31 01:04:29 [3304] LOG: 0: next
MultiXactId: 1; next MultiXactOffset: 02006-10-31 01:04:29 [3304]
LOCATION: StartupXLOG, xlog.c:47642006-10-31 01:04:29 [3304]
LOG: 0: database system was not properly shut down; automatic recovery
in progress2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:48312006-10-31 01:04:29 [3304] LOG: 0: redo starts at
C/84D208602006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:48932006-10-31 01:04:29 [3304] LOG: 0: record with zero
length at C/85164D682006-10-31 01:04:29 [3304] LOCATION: ReadRecord,
xlog.c:29972006-10-31 01:04:29 [3304] LOG: 0: redo done at
C/85164D382006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:49632006-10-31 01:04:29 [3304] LOG: 0: database system is
ready2006-10-31 01:04:29 [3304] LOCATION: StartupXLOG,
xlog.c:51562006-10-31 01:04:29 [3304] LOG: 0: Windows
fopen("global/pg_fsm.cache","rb") failed: code 2, errno 22006-10-31 01:04:29
[3304] LOCATION: AllocateFile, fd.c:1289
2006-10-31 01:04:54 [3272] LOG: 0: server
process (PID 4872) exited with exit code -10737418192006-10-31 01:04:54
[3272] LOCATION: LogChildExit, postmaster.c:2385
2006-10-31 01:04:54 [3272] LOG: 0:
terminating any other active server processes2006-10-31 01:04:54 [3272]
LOCATION: HandleChildCrash, postmaster.c:22772006-10-31 01:04:54
[3272] LOG: 0: all server processes terminated;
reinitializing2006-10-31 01:04:54 [3272] LOCATION: reaper,
postmaster.c:21792006-10-31 01:04:54 [580] LOG: 0: database system
was interrupted at 2006-10-31 01:04:29 W. Europe Standard Time2006-10-31
01:04:54 [580] LOCATION: StartupXLOG, xlog.c:46602006-10-31 01:04:54
[580] LOG: 0: Windows fopen("recovery.conf","r") failed: code 2, errno
22006-10-31 01:04:54 [580] LOCATION: AllocateFile,
fd.c:12892006-10-31 01:04:54 [580] LOG: 0: Windows
fopen("pg_xlog/0001.history","r") failed: code 2, errno 22006-10-31
01:04:54 [580] LOCATION: AllocateFile, fd.c:12892006-10-31 01:04:54
[580] LOG: 0: Windows fopen("backup_label","r") failed: code 2, errno
22006-10-31 01:04:54 [580] LOCATION: AllocateFile,
fd.c:12892006-10-31 01:04:54 [580] LOG: 0: checkpoint record is at
C/85164D682006-10-31 01:04:54 [580] LOCATION: StartupXLOG,
xlog.c:47302006-10-31 01:04:54 [580] LOG: 0: redo record is at
C/85164D68; undo record is at 0/0; shutdown TRUE2006-10-31 01:04:54 [580]
LOCATION: StartupXLOG, xlog.c:47572006-10-31 01:04:54 [580] LOG:
0: next transaction ID: 0/16122460; next OID: 66691002006-10-31 01:04:54
[580] LOCATION: StartupXLOG, xlog.c:47612006-10-31 01:04:54 [580]
LOG: 0: next MultiXactId: 1; next MultiXactOffset: 02006-10-31
01:04:54 [580] LOCATION: StartupXLOG, xlog.c:47642006-10-31
Re: [BUGS] xlog lockup patch (was: BUG #2712: could not fsync segment: Permission)
i've loaded 1gb of data without any xlog-problems, whereas with the 8.2b2 executable it locked up after ~100mb. the xlog-files are cycling... if i need to test for some specific behaviour let me know. maybe a similar patch could be found for the 2nd permission problem, where the writer process tries to use a previously deleted file whose filehandle is still in use by another postgresql process: 2006-10-31 07:12:37 [5392] ERROR: 42501: could not open relation 1663/3964774/6696548: Permission denied 2006-10-31 07:12:37 [5392] LOCATION: mdopen, md.c:366 2006-10-31 07:12:38 [5392] ERROR: 42501: could not open relation 1663/3964774/6696548: Permission denied 2006-10-31 07:12:38 [5392] LOCATION: mdopen, md.c:366 thanks for your efforts, very much appreciated! - thomas - Original Message - Sent: Sunday, October 29, 2006 6:10 PM Subject: Re: [BUGS] BUG #2712: could not fsync segment: Permission I haven't reproduced this on my box. But if you can give me a patch to try I can build binaries for Thomas to test, if he can do testing but not building. Utterly untested ... BTW, why does pgrename have an #if to check either GetLastError() or errno, but pgunlink doesn't? Ok, I've built a .EXE with this patch. It's compiled without pretty much all other options, hope that still works :-) (Meaning no NLS, no kerberos, no SSL etc) Grab the exe from http://www.hagander.net/download/postgres_renamepatch.zip. Sorry about the delay. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2712: could not fsync segment: Permission
thanks alot. using only standard features, so the missing bits shouldn't be a problem. will try to test later tonight or tomorrow. Grab the exe from http://www.hagander.net/download/postgres_renamepatch.zip. report will follow. regards, thomas - Original Message - From: Magnus Hagander [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Peter Brant [EMAIL PROTECTED]; Thomas H. [EMAIL PROTECTED]; pgsql-bugs@postgresql.org; Bruce Momjian [EMAIL PROTECTED] Sent: Sunday, October 29, 2006 6:10 PM Subject: RE: [BUGS] BUG #2712: could not fsync segment: Permission I haven't reproduced this on my box. But if you can give me a patch to try I can build binaries for Thomas to test, if he can do testing but not building. Utterly untested ... BTW, why does pgrename have an #if to check either GetLastError() or errno, but pgunlink doesn't? Ok, I've built a .EXE with this patch. It's compiled without pretty much all other options, hope that still works :-) (Meaning no NLS, no kerberos, no SSL etc) Grab the exe from http://www.hagander.net/download/postgres_renamepatch.zip. Sorry about the delay. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2712: could not fsync segment: Permission
It might be interesting to think about not requiring the ControlFileLock to be held while making new WAL segments. I think the only reason it does that is to ensure that link/rename failure can be treated as a hard error (because no one could have beat us to the filename), but we're already having to back off that stance for Windows ... on a sidenote, i was able to work around the total xlog-lock by ingreasing checkpoint_segments from 3 (default) to 12. that seems enough to have the processes release (timeout?) the filehandles before writer-process wants to rename the xlog file, at least under normal workload. if there is a data load, the lockup still happens, but i can live with that for now. the logs are still being swamped with the other delete error messages, tho: 2006-10-27 16:16:58 [5828] ERROR: XX000: storage sync failed on magnetic disk: Permission denied 2006-10-27 16:16:58 [5828] LOCATION: smgrsync, smgr.c:888 2006-10-27 16:16:59 [5828] LOG: 42501: could not fsync segment 0 of relation 1663/3964774/6495380: Permission denied 2006-10-27 16:16:59 [5828] LOCATION: mdsync, md.c:785 2006-10-27 16:16:59 [5828] ERROR: XX000: storage sync failed on magnetic disk: Permission denied 2006-10-27 16:16:59 [5828] LOCATION: smgrsync, smgr.c:888 magnus, where you able to do a debug build for me to test the patch? would be nice if a solution could be found for the final 8.2 release. cheers, thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] COPY fails on 8.1 with invalid byte sequences in text
FYI, prior to 8.2, there is another source of bad UTF8 byte sequences: when using tsearch2 on utf8 content in 8.2, tsearch2 was generating bad utf8 sequences. as tsearch2 does lowercase each char in the text its indexing, it did also do so with multibyte-characters... unfortunately taking each byte separately, so it seems. the unicode-representation of german umlauts (äöü) are some examples of charcodes, that where turned into invalid sequences. this data could be successfully pg_dump'ed, but not pg_restore'd. in 8.2, this looks fixed. to upgrade from 8.1.5 to 8.2b1 we had to remove all tsearch2 index data, dump the db, restore the db in 8.2 and recreate the indices. - thomas - Original Message - From: Jeff Davis [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Sent: Saturday, October 28, 2006 12:38 AM Subject: Re: [BUGS] COPY fails on 8.1 with invalid byte sequences in text On Fri, 2006-10-27 at 14:42 -0700, Jeff Davis wrote: It seems to be essentially a data corruption issue if applications insert binary data in text fields using escape sequences. Shouldn't PostgreSQL reject an invalid UTF8 sequence in any text type? Another note: PostgreSQL rejects invalid UTF8 sequences in other contexts. For instance, if you use PQexecParams() and insert using type text and any format (text or binary), it will reject invalid sequences. It will of course allow anything to be sent when the type is bytea. Also, I thought I'd publish the workaround that I'm using. I created a function that seems to work for validating text data as being valid UTF8. CREATE OR REPLACE FUNCTION valid_utf8(TEXT) returns BOOLEAN LANGUAGE plperlu AS $valid_utf8$ use utf8; return utf8::decode($_[0]) ? 1 : 0; $valid_utf8$; I just add a check constraint on all of my text attributes in all of my tables. Not fun, but it works. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] 8.2b2: update.bad in windows release points to wrong .msi
as the subject says - the upgrade.bat in the b2 release thats currently being mirrored points to the installation files of8.1 instead of the 8.2 ones. best regards, thomas
Re: [BUGS] BUG #2712: could not fsync segment: Permission
I'm not in a position to test this though. Magnus or Bruce? I haven't reproduced this on my box. But if you can give me a patch to try I can build binaries for Thomas to test, if he can do testing but not building. a binary would be marvelous. if too much hasle, i can setup a msvc++ 2005 here and try to build it on my own, but would obviously prefere if i won't have to... b2 is installed here, but i'm seeing the same problems, so yes, i'm ready for testing ;-) thanks, thomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
just a small update: this problem is also present in beta 2.
not a big problem for the moment, as we currently have disabled fulltext
search capabilities on the website.
regards,
thomas
- Original Message -
From: [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]
Cc: pgsql-bugs@postgresql.org
Sent: Tuesday, October 17, 2006 10:19 PM
Subject: Re: [BUGS] 8.2beta1 (w32): server process crash (tsvector)
the following query will crash the server process:
INSERT INTO news.news
SELECT * FROM news.news2;
This is undoubtedly data-dependent. Can you supply some sample data
that makes it happen?
it's not only happening with INSERTS, but also updates. as thats easier to
test, here's how i can reproduce the error:
1. create new database (encoding: UTF8) with tsearch2 on 8.2b1 win32
(system
locale: de_CH.1252)
2. insert the data from the zip file
[http://alternize.com/pgsql/tsearch2test.zip] (be sure to also update
pg_ts_cf /
pg_ts_cfgmap as we have WIN1252 locale)
3. execute UPDATE test SET idxFTI = to_tsvector('default', sometext); or
similar queries
4. hopefully see the process crashing as i do ;-)
2006-10-17 17:23:44 LOG: server process (PID 4584) exited with exit
code -1073741819
2006-10-17 17:23:44 LOG: terminating any other active server processes
2006-10-17 17:23:44 WARNING: terminating connection because of crash of
another server process
2006-10-17 17:23:44 DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
{snipp}
2006-10-17 17:23:44 LOG: all server processes terminated; reinitializing
2006-10-17 17:23:44 LOG: database system was interrupted at 2006-10-17
17:23:41 W. Europe Daylight Time
2006-10-17 17:23:44 LOG: Windows fopen(recovery.conf,r) failed: code
2,
errno 2
2006-10-17 17:23:44 LOG: Windows fopen(pg_xlog/0001.history,r)
failed: code 2, errno 2
2006-10-17 17:23:44 LOG: Windows fopen(backup_label,r) failed: code
2,
errno 2
2006-10-17 17:23:44 LOG: checkpoint record is at 0/E2ECA728
2006-10-17 17:23:44 LOG: redo record is at 0/E2ECA728; undo record is at
0/0; shutdown FALSE
2006-10-17 17:23:44 LOG: next transaction ID: 0/514299; next OID: 6276957
2006-10-17 17:23:44 LOG: next MultiXactId: 1; next MultiXactOffset: 0
2006-10-17 17:23:44 LOG: database system was not properly shut down;
automatic recovery in progress
2006-10-17 17:23:44 LOG: redo starts at 0/E2ECA778
2006-10-17 17:23:44 LOG: unexpected pageaddr 0/DB0CC000 in log file 0,
segment 227, offset 835584
2006-10-17 17:23:44 LOG: redo done at 0/E30CBE78
2006-10-17 17:23:45 LOG: database system is ready
2006-10-17 17:23:45 LOG: Windows fopen(global/pg_fsm.cache,rb)
failed:
code 2, errno 2
2006-10-17 17:23:45 LOG: transaction ID wrap limit is 2147484172, limited
by database postgres
2006-10-17 17:23:45 LOG: Windows fopen(global/pgstat.stat,rb) failed:
code 2, errno 2
i've also tried to update each record on its own in a for-loop. here the
crash happens as well, sometimes after 10 updates, sometimes after 100
updates, sometimes even after 1 update. but eventually every record can be
updated. so i do not think its entierly content-related...
for what its worth, here's the output of pg_controldata:
pg_control version number:822
Catalog version number: 200609181
Database system identifier: 4986650172201464825
Database cluster state: in production
pg_control last modified: 17.10.2006 17:44:29
Current log file ID: 0
Next log file segment:230
Latest checkpoint location: 0/E4E0F978
Prior checkpoint location:0/E46BF420
Latest checkpoint's REDO location:0/E4E03098
Latest checkpoint's UNDO location:0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/531333
Latest checkpoint's NextOID: 6285149
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint:17.10.2006 17:43:45
Minimum recovery ending location: 0/0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index: 32
Date/time type storage: floating-point numbers
Maximum length of locale name:128
LC_COLLATE: German_Switzerland.1252
LC_CTYPE: German_Switzerland.1252
let me know if more information / data is needed.
on a sidenote: are those fopen() errors debug-code-leftovers or something
one should worry about? i can't find those files on the file system.
- thomas
---(end of broadcast)---
TIP 4: Have you searched our list
Re: [BUGS] BUG #2712: could not fsync segment: Permission
As for fixing the problem we do understand: ISTM it's just an awful idea for pgrename and pgunlink to be willing to loop forever. I think they should time out and report the failure after some reasonable period (say between 10 sec and a minute). is the main problem realy in the rename/delete function? while i'm in no position of actually knowing whats going on under the hood, my observations in +10 cases during this afternoon/evening revealed some patterns: it is defenitely the writer process that blocks the db. but in every case the writer process seems to fail to rename the file due to another postgresql still holding a filehandle to the very xlog file that should be renamed. ProcessExplorer lets you force a close of the file handle - as soon as you do this [which is a bad thing to do, i assume], the rename succeeds and processing continues normally. i actually can reproduce the error at will now - i just need do pump enough data into the db (~200mb data seems sufficient) to have it lock up. - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] could not rename xlog (was: BUG #2712)
Peter Brant [EMAIL PROTECTED] writes: The same problem exists in 8.1 too. See this thread http://archives.postgresql.org/pgsql-bugs/2006-04/msg00177.php Tom and Magnus tracked down a cause, but I don't think a fix was ever implemented. Thomas seems to have two different issues there: the could not rename file problem on the pg_xlog file is probably explained by the mechanism we identified back then (and I'm not sure why no fix has been installed) just had another total lockdown. the writer-process was trying to rename the C1 to CA which failed: 2006-10-24 14:27:58 [5196] LOG: 0: could not rename file pg_xlog/0001000400C1 to pg_xlog/0001000400CA, continuing to try 2006-10-24 14:27:58 [5196] LOCATION: pgrename, dirmod.c:142 when checking the process with process explorer, it reveals that it has this file handles to pg_xlog open: D:\DB\PostgreSQL-8.2\data\pg_xlog D:\DB\PostgreSQL-8.2\data\pg_xlog\0001000400C3 under normal operation, writer process does not seem to have a file handle to the xlog directory (D:\DB\PostgreSQL-8.2\data\pg_xlog) the last error log entry prior to the lockdown was about 15min, so probably the two problems are unrelated. unfortunately, before i could try to get more informations, i had to restart the pg_ctl due to the system being in half-productive mode. that leads me to the question: what are the chances of me being helpful with getting more informations? i can have the db run like that for some more days, but for long term i can't babysit our application and might have to go back to 8.1. thanks, thomas ---(end of broadcast)--- TIP 1: 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: [BUGS] could not rename xlog (was: BUG #2712)
another lockup, this time due to pgstat_write_statsfile :-( 2006-10-24 17:01:17 [5412] LOG: XX000: could not rename temporary statistics file global/pgstat.tmp to global/pgstat.stat: A blocking operation was interrupted by a call to WSACancelBlockingCall. 2006-10-24 17:01:17 [5412] LOCATION: pgstat_write_statsfile, pgstat.c:2008 2006-10-24 17:23:23 [3280] LOG: 0: received fast shutdown request 2006-10-24 17:23:23 [3280] LOCATION: pmdie, postmaster.c:1903 2006-10-24 17:23:23 [3280] LOG: 0: aborting any active transactions 2006-10-24 17:23:23 [3280] LOCATION: pmdie, postmaster.c:1910 2006-10-24 17:23:23 [3468] FATAL: 57P01: terminating connection due to administrator command 2006-10-24 17:23:23 [3468] LOCATION: ProcessInterrupts, postgres.c:2465 this is what filemon reports (~10 times a second): 17:23:18 postgres.exe:1432 OPEN D:\DB\PostgreSQL-8.2\data\pg_xlog\0001000400DB DELETE PEND Options: Open Access: 00110080 17:23:18 postgres.exe:1432 OPEN D:\DB\PostgreSQL-8.2\data\pg_xlog\0001000400DB DELETE PEND Options: Open Access: 00110080 17:23:18 postgres.exe:1432 OPEN D:\DB\PostgreSQL-8.2\data\pg_xlog\0001000400DB DELETE PEND Options: Open Access: 00110080 sorry for flooding. just tell me if i shall rather stop. - thomas - Original Message - From: Thomas H. [EMAIL PROTECTED] To: pgsql-bugs@postgresql.org Sent: Tuesday, October 24, 2006 3:15 PM Subject: Re: [BUGS] could not rename xlog (was: BUG #2712) Peter Brant [EMAIL PROTECTED] writes: The same problem exists in 8.1 too. See this thread http://archives.postgresql.org/pgsql-bugs/2006-04/msg00177.php Tom and Magnus tracked down a cause, but I don't think a fix was ever implemented. Thomas seems to have two different issues there: the could not rename file problem on the pg_xlog file is probably explained by the mechanism we identified back then (and I'm not sure why no fix has been installed) just had another total lockdown. the writer-process was trying to rename the C1 to CA which failed: 2006-10-24 14:27:58 [5196] LOG: 0: could not rename file pg_xlog/0001000400C1 to pg_xlog/0001000400CA, continuing to try 2006-10-24 14:27:58 [5196] LOCATION: pgrename, dirmod.c:142 when checking the process with process explorer, it reveals that it has this file handles to pg_xlog open: D:\DB\PostgreSQL-8.2\data\pg_xlog D:\DB\PostgreSQL-8.2\data\pg_xlog\0001000400C3 under normal operation, writer process does not seem to have a file handle to the xlog directory (D:\DB\PostgreSQL-8.2\data\pg_xlog) the last error log entry prior to the lockdown was about 15min, so probably the two problems are unrelated. unfortunately, before i could try to get more informations, i had to restart the pg_ctl due to the system being in half-productive mode. that leads me to the question: what are the chances of me being helpful with getting more informations? i can have the db run like that for some more days, but for long term i can't babysit our application and might have to go back to 8.1. thanks, thomas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2712: could not fsync segment: Permission denied
unfortunately not. and this is not happening with 8.1 regards, thomas - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Thomas H [EMAIL PROTECTED] Cc: pgsql-bugs@postgresql.org Sent: Monday, October 23, 2006 4:07 AM Subject: Re: [BUGS] BUG #2712: could not fsync segment: Permission denied Thomas H [EMAIL PROTECTED] writes: Operating system: windows 2003 standard Description:could not fsync segment: Permission denied The usual answer to this has been that you're running some overenthusiastic antivirus software. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2712: could not fsync segment: Permission denied
there is defenitely something terribly wrong in the windows 8.2b1 regarding
file access/locking. 2nd total db lockup today due to file access locks (all
hold by postmaster):
{...}
2006-10-23 17:48:10 LOG: 42501: could not fsync segment 0 of relation
1663/3964774/6419608: Permission denied
2006-10-23 17:48:10 LOCATION: mdsync, md.c:785
2006-10-23 17:48:10 ERROR: XX000: storage sync failed on magnetic disk:
Permission denied
2006-10-23 17:48:10 LOCATION: smgrsync, smgr.c:888
2006-10-23 17:48:10 LOG: 0: duration: 327.999 ms statement: SELECT
threads.*, first.login AS first_user, last.login AS last_user FROM
forum.threads JOIN users.users AS first ON first.id = threads.t_first_user
LEFT JOIN users.users AS last ON last.id = threads.t_last_user WHERE t_b_id
= 4 AND t_status_deleted = false ORDER BY t_status_sticky DESC, t_last_post
DESC
2006-10-23 17:48:10 LOCATION: exec_simple_query, postgres.c:1007
2006-10-23 17:48:14 LOG: 0: could not rename file
pg_xlog/00010004002E to pg_xlog/000100040037,
continuing to try
2006-10-23 17:48:14 LOCATION: pgrename, dirmod.c:142
2006-10-23 18:12:05 LOG: 0: received fast shutdown request
2006-10-23 18:12:05 LOCATION: pmdie, postmaster.c:1903
2006-10-23 18:12:05 LOG: 0: aborting any active transactions
2006-10-23 18:12:05 LOCATION: pmdie, postmaster.c:1910
2006-10-23 18:12:05 FATAL: 57P01: terminating connection due to
administrator command
2006-10-23 18:12:05 LOCATION: ProcessInterrupts, postgres.c:2465
2006-10-23 18:12:06 ERROR: XX000: could not rename file
pg_xlog/00010004002E to pg_xlog/000100040037
(initialization of log file 4, segment 55): A blocking operation was
interrupted by a call to WSACancelBlockingCall.
2006-10-23 18:12:06 LOCATION: InstallXLogFileSegment, xlog.c:2201
{...}
from 17:48:14 on pgsql didn't handle anymore queries until shutdown. as soon
as one restarts postmaster, the file locks are cleared up.
and no, there are no other file locking tools (av scanners and the such)
running - 8.1 on the same box (even on same partition) run fine.
regarnds,
- thomas
- Original Message -
From: Thomas H. [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]
Cc: pgsql-bugs@postgresql.org
Sent: Monday, October 23, 2006 11:52 AM
Subject: Re: [BUGS] BUG #2712: could not fsync segment: Permission denied
unfortunately not. and this is not happening with 8.1
regards,
thomas
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
To: Thomas H [EMAIL PROTECTED]
Cc: pgsql-bugs@postgresql.org
Sent: Monday, October 23, 2006 4:07 AM
Subject: Re: [BUGS] BUG #2712: could not fsync segment: Permission denied
Thomas H [EMAIL PROTECTED] writes:
Operating system: windows 2003 standard
Description:could not fsync segment: Permission denied
The usual answer to this has been that you're running some
overenthusiastic antivirus software.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2712: could not fsync segment: Permission
The same problem exists in 8.1 too. See this thread its only appearing in 8.2 here, i've just rechecked our logs... is there any workaround? how did you get around that problem of having a total lockdown? thanks, thomas Thomas H. [EMAIL PROTECTED] 23.10.2006 18:21 there is defenitely something terribly wrong in the windows 8.2b1 regarding file access/locking. 2nd total db lockup today due to file access locks (all hold by postmaster): 2006-10-23 17:48:10 LOCATION: exec_simple_query, postgres.c:1007 2006-10-23 17:48:14 LOG: 0: could not rename file pg_xlog/00010004002E to pg_xlog/000100040037, continuing to try ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2712: could not fsync segment: Permission
Actually, now that I look back in the archives, I think we had theorized that the fsync errors come from attempting to fsync a file that's already been deleted but some backend still has a reference to. Apparently that leads to EACCES instead of ENOENT (which the code is already prepared to expect). with process explorer i can actually check which postgres.exe instance (in all cases i've checked its just 1 instance, and always just 1 file) holds the lock for the file in question. but will that help in determining why it is still holding a reference? the postgres instance that holds the lock eventually closes the filehandle after some minutes. the process itself is not killed but continues thereafter. let me know if i can be of any assistance. since we do regurarly reindex one table whose index size keeps growing despite of often vacuuming, the fsync-problem happens almost 4-5 times per hour. regards, thomas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2712: could not fsync segment: Permission
with process explorer i can actually check which postgres.exe instance (in all cases i've checked its just 1 instance, and always just 1 file) holds the lock for the file in question. So which one is it? it's always one of the db-slaves and not logger process, stats collector process or writer process: right now its PID 4844 (\BaseNamedObjects\pgident: postgres: db_outnow outnow1 127.0.0.1(2122) idle) that tries to write D:\DB\PostgreSQL-8.2\data\base\3964774\6422331 can i somehow check what object that file-OID belong(ed/s) to? - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2712: could not fsync segment: Permission
Thomas H. [EMAIL PROTECTED] writes:
right now its PID 4844 (\BaseNamedObjects\pgident: postgres: db_outnow
outnow1 127.0.0.1(2122) idle) that tries to write
D:\DB\PostgreSQL-8.2\data\base\3964774\6422331
Do you actually mean it's trying to write that file? Or is it just
sitting there holding the open filehandle?
well, hard to tell :-)
according to the log-messages i would assume it is *trying* to write. but
the file in question isn't physically there anymore, it's just the open file
handle that keeps it from vanish totally - you do not have access to the
file (permission denied / access denied) if you for example try to read it
or its attributes in file explorer.
i've installed Filemon (http://www.sysinternals.com/Utilities/Filemon.html)
now. this gives more insight what happens to the file. in this case its file
6422806, the first error message appeared at 23:45:21, the last one at
23:45:26 (only short duration this time).
{}
23:44:57 postgres.exe:1944 WRITE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS Offset: 16384 Length:
8192
23:44:57 postgres.exe:1944 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:44:57 postgres.exe:1944 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS Options: Open
Access: 00010080
23:44:57 postgres.exe:1944 QUERY INFORMATION
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
FileAttributeTagInformation
23:44:57 postgres.exe:1944 DELETE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:44:57 postgres.exe:1944 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:44:57 postgres.exe:1944 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806.1 NOT FOUND Options: Open
Access: 00010080
23:44:57 postgres.exe:5364 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:44:57 postgres.exe:2780 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:44:59 postgres.exe:6036 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:45:11 postgres.exe:5196 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:45:20 postgres.exe:1268 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:45:21 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:22 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:23 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:24 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:25 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:26 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open
Access: 0012019F
23:45:26 postgres.exe:5428 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:45:26 postgres.exe:2200 CLOSE
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 SUCCESS
23:45:27 postgres.exe:5196 OPEN
D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 NOT FOUND Options: Open
Access: 0012019F
i have earlier log data for this file if needed, but at :45:27 was the last
entry. unfortunately i wasn't quick enough to find the blocking process in
processviewer, but i guess its pid 5196
can i somehow check what object that file-OID belong(ed/s) to?
You can check in pg_class.relfilenode and pg_class.oid of that database
to see if you get a match. But our theory is that this table has been
deleted ...
nothing there as assumed.
- thomas
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2712: could not fsync segment: Permission
The log messages you have don't make it clear which process is trying to do the fsync, but I would expect it to be the bgwriter. (Possibly you should modify log_line_prefix to include PID so we can tell a bit better.) you're right (as always :-)). its the writer process (pid 5196) that outputs the error messages: 2006-10-24 00:09:09 [5196] ERROR: XX000: storage sync failed on magnetic disk: Permission denied 2006-10-24 00:09:09 [5196] LOCATION: smgrsync, smgr.c:888 2006-10-24 00:09:10 [5196] LOG: 42501: could not fsync segment 0 of relation 1663/3964774/6422947: Permission denied 2006-10-24 00:09:10 [5196] LOCATION: mdsync, md.c:785 and in this case, its process 5988 that keeps the file handle open (its entry in pg_class is already deleted): \BaseNamedObjects\pgident: postgres: db_outnow outnow1 127.0.0.1(2362) idle D:\DB\PostgreSQL-8.2\data\base\3964774\6422947 (1 references, 1 handle) ... while pid 5196 constantly tries to open the file (for over 15min in this case), until... 00:22:18 postgres.exe:5196 OPEN D:\DB\PostgreSQL-8.2\data\base\3964774\6422947 DELETE PEND Options: Open Access: 0012019F 00:22:19 postgres.exe:5196 OPEN D:\DB\PostgreSQL-8.2\data\base\3964774\6422947 DELETE PEND Options: Open Access: 0012019F 00:22:20 postgres.exe:5988 CLOSE D:\DB\PostgreSQL-8.2\data\base\3964774\6422947 SUCCESS 00:22:20 postgres.exe:5196 OPEN D:\DB\PostgreSQL-8.2\data\base\3964774\6422947 NOT FOUND Options: Open Access: 0012019F is that of any use? what more logging options would be interesting? - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2712: could not fsync segment: Permission denied
The following bug has been logged online:
Bug reference: 2712
Logged by: Thomas H
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2b1
Operating system: windows 2003 standard
Description:could not fsync segment: Permission denied
Details:
sometimes we're seeing loads of errors in the log:
2006-10-22 23:48:50 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:50 ERROR: storage sync failed on magnetic disk: Permission
denied
2006-10-22 23:48:51 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:51 ERROR: storage sync failed on magnetic disk: Permission
denied
2006-10-22 23:48:52 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:52 ERROR: storage sync failed on magnetic disk: Permission
denied
2006-10-22 23:48:53 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:53 ERROR: storage sync failed on magnetic disk: Permission
denied
{...}
when this happens, there are also files locked within the data\base\{dbid}\.
access to those files are denied by the os - the files vanish as soon as
postmaster ist stopped restarted.
i haven't yet found a possible reason - i suspect the error to appear
*sometimes* after issuing a VACUUM FULL ANALYZE {tablename} / REINDEX
TABLE {tablename}.
the hardware is checked and ok.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2712: could not fsync segment: Permission denied
in verbose mode, the log shows a little bit more:
2006-10-23 03:23:14 LOG: 42501: could not fsync segment 0 of relation
1663/3964774/6411190: Permission denied
2006-10-23 03:23:14 LOCATION: mdsync, md.c:785
2006-10-23 03:23:14 ERROR: XX000: storage sync failed on magnetic disk:
Permission denied
2006-10-23 03:23:14 LOCATION: smgrsync, smgr.c:888
- thomas
- Original Message -
From: Thomas H [EMAIL PROTECTED]
To: pgsql-bugs@postgresql.org
Sent: Monday, October 23, 2006 1:28 AM
Subject: [BUGS] BUG #2712: could not fsync segment: Permission denied
The following bug has been logged online:
Bug reference: 2712
Logged by: Thomas H
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2b1
Operating system: windows 2003 standard
Description:could not fsync segment: Permission denied
Details:
sometimes we're seeing loads of errors in the log:
2006-10-22 23:48:50 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:50 ERROR: storage sync failed on magnetic disk:
Permission
denied
2006-10-22 23:48:51 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:51 ERROR: storage sync failed on magnetic disk:
Permission
denied
2006-10-22 23:48:52 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:52 ERROR: storage sync failed on magnetic disk:
Permission
denied
2006-10-22 23:48:53 LOG: could not fsync segment 0 of relation
1663/3964774/6409340: Permission denied
2006-10-22 23:48:53 ERROR: storage sync failed on magnetic disk:
Permission
denied
{...}
when this happens, there are also files locked within the
data\base\{dbid}\.
access to those files are denied by the os - the files vanish as soon as
postmaster ist stopped restarted.
i haven't yet found a possible reason - i suspect the error to appear
*sometimes* after issuing a VACUUM FULL ANALYZE {tablename} / REINDEX
TABLE {tablename}.
the hardware is checked and ok.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
