Re: [BUGS] BUG #2712: could not fsync segment: Permission
i've installed Filemon (http://www.sysinternals.com/Utilities/Filemon.html) now. this gives more insight what happens to the file. ... D:\DB\PostgreSQL-8.2\data\base\3964774\6422806 DELETE PEND Options: Open Access: 0012019F This is quite interesting, because it says that Filemon knows how to distinguish a delete pending error from other errors. If we could do that, then my prior worries about ignoring all EACCES errors would go away. What's it looking at exactly? filemon is a kernel mode filter driver. So it's looking at kernel-only datastructures. AFAIK, there is no way to see that from userspace. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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
[BUGS] ERROR: failed to build any 4-way joins SQL state: XX000, PostgreSQL 8.2 beta1
Hi, I have a query that throws ERROR: failed to build any 4-way joins SQL state: XX000. Here's an (arguably) simplified version of it that doesn't require any of my table: select 1 from (select 1 as col) t1 cross join (select 1 as col) t2 left outer join (select 1 as col) t3 on t1.col = t3.col and t2.col = t3.col and 1 = t3.col left outer join (select 1 as col) t4 on t3.col = t4.col I am on PostgreSQL 8.2 beta1 under Windows XP Service Pack 2. Thanks, Jean-Pierre Pelletier e-djuster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
[BUGS] BUG #2717: performance problem with enable_bitmapscan
The following bug has been logged online: Bug reference: 2717 Logged by: Pavel Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Linux Redhat Description:performance problem with enable_bitmapscan Details: Hi, i have a following performance problem by Postgresql 8.1.4. I think that the Optimizer joins the tables wrong. My settings: enable_bitmapscan=on If I set enable_bitmapscan=off; the optimizer will be join a right column. any ideas? is this a bug? -- Query SELECT ft.val_10 FROM dbflat AS ft , bx , en , dbflat AS ft0, (SELECT fts.val_1, max(fts.val_6) AS val_6 FROM dbflat AS fts, bx, en WHERE (bx.mem=144134500 AND bx.com=222492995 AND bx.hide=FALSE AND bx.en=fts.en AND en.preview=FALSE AND fts.en=en.id AND fts.docstart=1) GROUP BY fts.val_1) AS sub , dbflat AS ft1 , dbflat AS ft2 WHERE bx.mem=144134500 AND bx.com=222492995 AND bx.hide=FALSE AND bx.en=ft.en AND en.preview=FALSE AND ft.en=en.id AND ft0.flatid=ft.flatid AND (ft0.val_9='1' OR ft0.val_9='2') AND ft1.val_1=sub.val_1 AND ft1.flatid=ft.flatid AND ft2.val_6=sub.val_6 AND ft2.flatid=ft.flatid AND (((ft.docstart=1 OR ft.docstart=0) AND NOT ft.val_10 IS NULL) OR (ft.docstart=1 AND ft.val_10 IS NULL)) GROUP BY ft.val_10 ORDER BY ft.val_10 ASC LIMIT 200 ; --- EXPLAIN PLAN Limit (cost=88.30..88.31 rows=1 width=8) - Group (cost=88.30..88.31 rows=1 width=8) - Sort (cost=88.30..88.31 rows=1 width=8) Sort Key: ft.val_10 - Nested Loop (cost=36.95..88.29 rows=1 width=8) - Nested Loop (cost=24.52..72.84 rows=1 width=53) Join Filter: (outer.en = inner.en) - Nested Loop (cost=0.00..9.85 rows=1 width=8) - Index Scan using bx_j_index on bx (cost=0.00..4.95 rows=1 width=4) Index Cond: ((com = 222492995) AND (mem = 144134500)) Filter: (NOT hide) - Index Scan using en_pk on en (cost=0.00..4.88 rows=1 width=4) Index Cond: (en.id = outer.en) Filter: (NOT preview) - Nested Loop (cost=24.52..62.94 rows=4 width=57) - Nested Loop (cost=22.49..41.62 rows=1 width=30) - Nested Loop (cost=17.44..25.47 rows=2 width=23) - HashAggregate (cost=17.44..17.45 rows=1 width=16) - Nested Loop (cost=0.00..17.43 rows=1 width=16) - Nested Loop (cost=0.00..9.85 rows=1 width=8) - Index Scan using bx_j_index on bx (cost=0.00..4.95 rows=1 width=4) Index Cond: ((com = 222492995) AND (mem = 144134500)) Filter: (NOT hide) - Index Scan using en_pk on en (cost=0.00..4.88 rows=1 width=4) Index Cond: (en.id = outer.en) Filter: (NOT preview) - Index Scan using dbflat_en on dbflat fts (cost=0.00..7.56 rows=2 width=20) Index Cond: ((outer.en = fts.en) AND (fts.docstart = 1)) - Index Scan using dbflat_val_1 on dbflat ft1 (cost=0.00..7.98 rows=2 width=23) Index Cond: (ft1.val_1 = outer.val_1) - Bitmap Heap Scan on dbflat ft2 (cost=5.06..8.06 rows=1 width=23) Recheck Cond: ((ft2.flatid = outer.flatid) AND (ft2.val_6 = outer.val_6)) - BitmapAnd (cost=5.06..5.06 rows=1 width=0) - Bitmap Index Scan on dbflat_flatid (cost=0.00..2.03 rows=7 width=0) Index Cond: (ft2.flatid = outer.flatid) - Bitmap Index Scan on dbflat_val_6 (cost=0.00..2.78 rows=223 width=0)
Re: [BUGS] ERROR: failed to build any 4-way joins SQL state: XX000, PostgreSQL 8.2 beta1
JEAN-PIERRE PELLETIER [EMAIL PROTECTED] writes: I have a query that throws ERROR: failed to build any 4-way joins Fixed --- thanks for the report! (This didn't make beta2, but will be in the next one.) regards, tom lane ---(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 #2701: PQserverVersion function missing
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Actually, the 8.0.X libpq function would still report 70401 if connected to a 7.4.1 database, That was exactly the reason why the docs were written like that. Using two examples that are both from the same major release doesn't seem to me to be an improvement. Actually, the problem with 8.0.X is that the major version number had a zero in it. 8.1 does not, so I think it is actually better to use the same major version number in both examples. You're ignoring the point at hand, which is exactly that the libpq function will work when connected to servers older (in fact much older) than it is. Fine, but the examples are not trying to show that. The example is to show the trailing zero added for 8.1. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Bug related to out of memory condition (more information)
I have made a clearer example of the bug I reported to -hackers yesterday: http://archives.postgresql.org/pgsql-hackers/2006-10/msg01252.php The following example shows a simple case that fails on 8.0+ (including CVS HEAD), but works fine on 7.4. There are two almost identical situations, and one causes an ERROR and the other a PANIC. The only difference is the column type: INT versus TEXT, respectively. I am on FreeBSD. An OOM condition must be caused to see this bug. In 7.4, an OOM condition is not even caused for the query, so perhaps it has a the same bug, but handles foreign keys differently. Incidently, foreign keys are all AFTER triggers, even in 7.4, but I don't understand why 7.4 doesn't exhaust itself of memory collecting the trigger events, as is described in the following mailing list post: http://archives.postgresql.org/pgsql-bugs/2006-05/msg00036.php Also, and this is pure conjecture, this bug may be related to the following change in the 8.0 release notes: Nondeferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation. For example, if a function inserts a new row into a table, any nondeferred foreign key checks occur before proceeding with the function. Regards, Jeff Davis Step 1: Create 4 tables - CREATE TABLE r1( i INT PRIMARY KEY ); INSERT INTO r1 VALUES(1); CREATE TABLE r2( i INT PRIMARY KEY ); INSERT INTO r2 VALUES(1); CREATE TABLE r3( i INT PRIMARY KEY ); INSERT INTO r3 VALUES(1); CREATE TABLE r4( i INT PRIMARY KEY ); INSERT INTO r4 VALUES(1); Step 2: Cause an out of memory condition. The result is an ERROR, as expected. - BEGIN; CREATE TABLE crashme ( attr1INT REFERENCES r1(i), attr2INT REFERENCES r2(i), attr3INT REFERENCES r3(i), attr4INT REFERENCES r4(i), attr5TEXT ); INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,'t' FROM generate_series(1,500); Step 3: Do almost exacly the same thing, except attr5 is INT and not TEXT type. This causes a PANIC instead of an ERROR. The bug is that this should be only an ERROR, since everything is the same except the column type for attr5. --- BEGIN; CREATE TABLE crashme ( attr1INT REFERENCES r1(i), attr2INT REFERENCES r2(i), attr3INT REFERENCES r3(i), attr4INT REFERENCES r4(i), attr5INT ); INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,1 FROM generate_series(1,500); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2704: pg_class.relchecks overflow problem
How about this patch? Of course, it might be a rare case that such check is necessary... Toru SHIMOGAKI wrote: The following bug has been logged online: Bug reference: 2704 Logged by: Toru SHIMOGAKI Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Red Hat Enterprise Linux AS 4 Description:pg_class.relchecks overflow problem Details: Hi, pg_class.relchecks is defined as int2. But the upper bound of this value is not checked and it overflows. I found it at the following case: 1. I tried to add check constraints: alter table test_a add check (aaa i); (0 = i = 32767) 2. When I added the 32768th check constraint, the value of pg_class.relchecs became -32768. postgres=# alter table test_a add check ( aaa 32768 ); ALTER TABLE postgres=# select relname, relchecks from pg_class where relname = 'test_a'; relname | relchecks -+--- test_a | -32768 (1 row) 3. The following error message was found when I added the next one: postgres=# alter table test_a add check ( aaa 32769 ); ERROR: unexpected constraint record found for rel test_a postgres=# select relname, relchecks from pg_class where relname = 'test_a'; relname | relchecks -+--- test_a | -32768 (1 row) Best regards, ---(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 -- Toru SHIMOGAKI[EMAIL PROTECTED] diff -cpr postgresql-8.1.5-orig/src/backend/catalog/heap.c postgresql-8.1.5/src/backend/catalog/heap.c *** postgresql-8.1.5-orig/src/backend/catalog/heap.c2006-04-24 10:40:39.0 +0900 --- postgresql-8.1.5/src/backend/catalog/heap.c 2006-10-23 16:50:22.0 +0900 *** AddRelationRawConstraints(Relation rel, *** 1525,1530 --- 1525,1535 continue; Assert(cdef-cooked_expr == NULL); + if (numchecks == 0x7FFF) + ereport(ERROR, + (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), + errmsg(cannot have more than 2^15-1 checks in a table))); + /* * Transform raw parsetree to executable expression. */ ---(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
