[BUGS] unsubscribe
---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Corrpupt Data File
Hi, I have a postgres data base that is probably about 2gigs in size spread across a couple of tables. The orders table is about 500megs in size and is mostly writes. When running "select count(1) from orders" on the database this evening, the DB responded with "ERROR: Invalid page header in block 57879 of orders". After doing a bunch of reading online, I downloaded pg_filedump and ran it against the offending block with these results: * * PostgreSQL File/Block Formatted Dump Utility - Version 1.1 * * File: 309928249 * Options used: -R 57879 * * Dump created on: Thu Feb 26 01:19:09 2004 * Block 57879 ** - Block Offset: 0x1c42e000 Offsets: Lower 3 (0x0003) Block: Size 16384 Version 24Upper10679 (0x29b7) LSN: logid 808988729 recoff 0x0037 Special79 (0x004f) Items: -4 Free Space: 10676 Length (including item array): 24 Error: Invalid header information. -- Error: Item index corrupt on block. Offset: <-4>. - Error: Invalid special section encountered. *** End of Requested Range Encountered. Last Block Read: 57879 *** The blocks both before and after this block are fine. (I'll include the header from one at the end of this message). I should mention that I am running postgres 7.3 on AMD-64 hardware with kernel 2.4.25. deleting the indexes from the table did not help. Is there a tool or method that that I could use to simply skip the bad data and at least recover the good blocks? Thanks -Alex * * PostgreSQL File/Block Formatted Dump Utility - Version 1.1 * * File: 309928249 * Options used: -R 57880 * * Dump created on: Thu Feb 26 01:24:00 2004 * Block 57880 ** - Block Offset: 0x1c43 Offsets: Lower 336 (0x0150) Block: Size 8192 Version1Upper 336 (0x0150) LSN: logid 17 recoff 0x5e6c8704 Special 8192 (0x2000) Items: 79 Free Space:0 Length (including item array): 340 -- Item 1 -- Length: 102 Offset: 8088 (0x1f98) Flags: USED Item 2 -- Length: 94 Offset: 7992 (0x1f38) Flags: USED Item 3 -- Length: 120 Offset: 7872 (0x1ec0) Flags: USED Item 4 -- Length: 120 Offset: 7752 (0x1e48) Flags: USED Item 5 -- Length: 120 Offset: 7632 (0x1dd0) Flags: USED Item 6 -- Length: 102 Offset: 7528 (0x1d68) Flags: USED Item 7 -- Length: 95 Offset: 7432 (0x1d08) Flags: USED Item 8 -- Length: 120 Offset: 7312 (0x1c90) Flags: USED Item 9 -- Length: 102 Offset: 7208 (0x1c28) Flags: USED Item 10 -- Length: 102 Offset: 7104 (0x1bc0) Flags: USED Item 11 -- Length: 104 Offset: 7000 (0x1b58) Flags: USED Item 12 -- Length: 104 Offset: 6896 (0x1af0) Flags: USED Item 13 -- Length: 120 Offset: 6776 (0x1a78) Flags: USED Item 14 -- Length: 72 Offset: 6704 (0x1a30) Flags: USED Item 15 -- Length: 72 Offset: 6632 (0x19e8) Flags: USED Item 16 -- Length: 120 Offset: 6512 (0x1970) Flags: USED Item 17 -- Length: 96 Offset: 6416 (0x1910) Flags: USED Item 18 -- Length: 104 Offset: 6312 (0x18a8) Flags: USED Item 19 -- Length: 104 Offset: 6208 (0x1840) Flags: USED Item 20 -- Length: 72 Offset: 6136 (0x17f8) Flags: USED Item 21 -- Length: 120 Offset: 6016 (0x1780) Flags: USED Item 22 -- Length: 96 Offset: 5920 (0x1720) Flags: USED Item 23 -- Length: 72 Offset: 5848 (0x16d8) Flags: USED Item 24 -- Length: 72 Offset: 5776 (0x1690) Flags: USED Item 25 -- Length: 72 Offset: 5704 (0x1648) Flags: USED Item 26 -- Length: 120 Offset: 5584 (0x15d0) Flags: USED Item 27 -- Length: 120 Offset: 5464 (0x1558) Flags: USED Item 28 -- Length: 101 Offset: 5360 (0x14f0) Flags: USED Item 29 -- Length: 72 Offset: 5288 (0x14a8) Flags: USED Item 30 -- Length: 94 Offset: 5192 (0x1448) Flags: USED Item 31 -- Length: 102 Offset: 5088 (0x13e0) Flags: USED Item 32 -- Length: 72 Offset: 5016 (0x1398) Flags: USED Item 33 -- Length: 120 Offset: 4896 (0x1320) Flags: USED Item 34 -- Length: 93 Offset: 4800 (0x12c0) Flags: USED Item 35 -- Length: 72 Offset: 4728 (0x1278) Flags: USED Item 36 -- Length: 120 Offset: 4608 (0x1200) Flags: USED Item 37 -- Length: 102 Offset: 4504 (0x1198) Flags: USED Item 38 -- Length: 94 Offset: 4408 (0x1138) Flags: USED Item 39 -- Length: 101 Offset: 4304 (0x10d0) Flags: USED Item 40 -- Length: 120 Offset: 4184 (0x1058) Flags: USED Item 41 -- Length: 120 Offset: 4064 (0x0fe0) Flags: USED Item 42 -- Length: 103 Offset: 3960 (0x0f78) Flags: USED Item 43 -- Len
Re: [BUGS] BUG #1082: Order by doesn't sort correctly.
Dear Tom, Thank you for your explanation. It's very helpful, although I was extremely surprised! I agree, it's not a postgresql bug. Can I suggest it might be worth a mention on the "Order By" part of the documentation. i.e. this page: http://www.postgresql.org/docs/7.3/static/sql-select.html#SQL-ORDERBY could possibly use a little more emphasis of this last paragraph: Data of character types is sorted according to the locale-specific collation order that was established when the database cluster was initialized. or perhaps a link to here: http://www.postgresql.org/docs/7.3/static/charset.html#AEN21582 I did realise that the sort would be locale dependent, but failed to realise it wasn't byte-at-a-time. Best wishes Richard Tom Lane wrote: Richard Neill <[EMAIL PROTECTED]> writes: This ordering is perverse! No kidding. No matter what the priority is of the different characters, I cannot understand how the above can arise. You are assuming that it's a byte-at-a-time process. It's not. I believe the first pass considers only letters and digits. You can easily prove to yourself that it's not just Postgres. Here's an example on my Linux laptop: [EMAIL PROTECTED] tgl]$ cat zzz Cymbal #1 Cymbal - 18 inch Cymbal #2 [EMAIL PROTECTED] tgl]$ LC_ALL=C sort zzz Cymbal #1 Cymbal #2 Cymbal - 18 inch [EMAIL PROTECTED] tgl]$ LC_ALL=en_GB sort zzz Cymbal #1 Cymbal - 18 inch Cymbal #2 [EMAIL PROTECTED] tgl]$ regards, tom lane -- [EMAIL PROTECTED] ** http://www.richardneill.org Richard Neill, Trinity College, Cambridge, CB21TQ, U.K. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] The syntax about CREATE DOMAIN
Dear Sir,
I would like to tell you that the SQL about "Create Domain" is still
cannot involve constraints other than NULL | NOT NULL in Postgresql 7.4.1.
For instances, when I type the following sentences, it shows error
message:
Create Domain GenderDomain CHAR(1) Constraint g Check Value in ('M','F');
Error: syntax error at or new "VALUE" at character ...
How can I obtain the correct syntax about domain in postgresql?
Regards,
Gary
_
Get 10Mb extra storage for MSN Hotmail. Subscribe Now!
http://join.msn.com/?pgmarket=en-hk
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1082: Order by doesn't sort correctly.
Tom Lane wrote: "PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes: Description:Order by doesn't sort correctly. It almost certainly is the correct sort order according to the locale you're using. Use pg_controldata to check the database locale. You'll probably want to re-initdb in C locale. Most non-C locales have weird rules that try to approximate dictionary sort order. regards, tom lane Dear Tom, Thanks for your email. I did check pg_controldata and found: LC_COLLATE: en_GB LC_CTYPE:en_GB The bug isn't the particular ascii-betical (or other) order. But what I am getting as a supposedly ordered list includes: Cymbal #1 Cymbal - 18 inch Cymbal #2 This ordering is perverse! No matter what the priority is of the different characters, I cannot understand how the above can arise. Whether '#' comes before or after '-', '#1' and '#2' should be adjacent. Richard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Corrpupt Data File
Alex Derbes <[EMAIL PROTECTED]> writes: > deleting the indexes from the table did not help. Is there a tool or > method that that I could use to simply skip the bad data and at least > recover the good blocks? Just zero out the broken page, eg with dd from /dev/zero. Note it might be a good idea to shut down the postmaster while you do so, else there could be a copy of that page in Postgres shared buffers, which'd mask your attempted fix. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Corrpupt Data File
for the annals of postgres maintenance I got my DB running by using DD to copy the blocks before the corrupt block, then add block from /dev/zero, then add the remaining blocks from the file. I discovered the block size by looking at he output of pg_filedump. To find the offending file select rel_filename from pg_class where relname = TABLE_NAME. My original error was : "ERROR: Invalid page header in block 57879 of orders" -- thus the bad block is #57879 cp 309928249 orders.bak dd bs=8192 count=57879 if=309928249 of=out dd bs=8192 count=1 seek=57879 if=/dev/zero of=out dd bs=8192 skip=57880 seek=57880 if=309928249 of=out mv out 309928249 Many thanks to Tom for his quick reply & Good luck to all others. Thanks, -Alex On Thu, 26 Feb 2004, Tom Lane wrote: > Alex Derbes <[EMAIL PROTECTED]> writes: > > deleting the indexes from the table did not help. Is there a tool or > > method that that I could use to simply skip the bad data and at least > > recover the good blocks? > > Just zero out the broken page, eg with dd from /dev/zero. Note it > might be a good idea to shut down the postmaster while you do so, > else there could be a copy of that page in Postgres shared buffers, > which'd mask your attempted fix. > > regards, tom lane > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-= [EMAIL PROTECTED] - [EMAIL PROTECTED] f720.920.1148 - c415.572.1669 http://www.brillig.org/~acd 01 1010 100 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Known issue with Reindex-based corruption?
Tom, > One question that comes to mind is were you reindexing a system or user > table? User. > Another is whether you were using disks that lie about write > completion (SCSI vs IDE)? First thing I thought of. Haven't been able to verify, yet. The basic symptoms are: 1) Machine stated scheduled REINDEX. 2) Unexpected power-out 3) On reboot, we have 2 different versions of the index file on disk, one with 0 bytes. Attempts to use the index (via SELECT) result in statement-fatal errors. I'm waiting on more data. For now, I was wondering whether there was a known issue with WAL recovery on indexes in 7.2.4. Neil thought there was. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 651-9224 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1083: Insert query reordering interacts badly with
Tom Lane wrote:
"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes:
/* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
column order of the table */
INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));
This is not a bug. The order of evaluation of select-lists and
values-lists is not defined anywhere in the SQL standard, nor promised
anywhere in the Postgres documentation.
Thanks for the clarification. I am curious, however: I can't find a
means to achieve the same effect in a deterministic manner. Any pointers?
regards,
martin
--
---
Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224 MOB: +64(21)364-017
Make things as simple as possible, but no simpler - Einstein
---
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] The syntax about CREATE DOMAIN
On Tue, 24 Feb 2004, Gary WAN wrote:
> Dear Sir,
> I would like to tell you that the SQL about "Create Domain" is still
> cannot involve constraints other than NULL | NOT NULL in Postgresql 7.4.1.
>
> For instances, when I type the following sentences, it shows error
> message:
>
> Create Domain GenderDomain CHAR(1) Constraint g Check Value in ('M','F');
>
> Error: syntax error at or new "VALUE" at character ...
>
> How can I obtain the correct syntax about domain in postgresql?
sszabo=# \h create domain
Command: CREATE DOMAIN
Description: define a new domain
Syntax:
CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }
Note the parentheses around the check expression.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] The syntax about CREATE DOMAIN
"Gary WAN" <[EMAIL PROTECTED]> writes:
> Create Domain GenderDomain CHAR(1) Constraint g Check Value in ('M','F');
You need parentheses around the check expression. AFAICS this is per
spec.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] intermittent error: 'variable not found in subplan target list' - INCOMPLETE SUBMISSION
Damon Hart <[EMAIL PROTECTED]> writes: > I had to remove the attached schema.sql file as the list manager refused > the complete submission for overall message size (112KB). I tried to > reduce the size of the data, but ran into trouble reproducing the error. You can send me the test case off-list if you like. First please confirm that you are on 7.4.1 not 7.4? This looks a bit like a bug that I thought was fixed in 7.4.1. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] intermittent error: 'variable not found in subplan target list' - INCOMPLETE SUBMISSION
name: Damon Hart email: [EMAIL PROTECTED] System Configuration - Architecture : Intel PIII dual 800MHz, 1.5 GB RAM Operating System : Linux xs1.xd 2.4.22-1.2115.nptlsmp #1 SMP Wed Oct 29 15:30:09 EST 2003 i686 i686 i386 GNU/Linux (Fedora Core 1) PostgreSQL version : PostgreSQL-7.4.1 Compiler used : PDG RPMs Description of problem: --- My query (sample attached) fails intermittently with the backend error 'variable not found in subplan target list.' The error is reliably reproducible on specific datasets and intermittent on others. An earlier post to the list, [EMAIL PROTECTED], (2003-12-02) accessible at http://www.spinics.net/lists/pgsql/msg03081.html suggests issues with the planner. That list thread does not indicate a solution was found, but that Tom Lane could not reproduce the error with an unpopulated schema. His post suggests toggling enable_nestloop, enable_mergejoin, and enable_hashjoin and in fact my error occurs only with enable_nestloop ON. I have isolated a reproducible example. As mentioned, it is data dependent and the script queries do not result in the error if run against empty tables. Sorry about the resulting length of this post. Steps to reproduce: --- Attached are pg_dump output of a minimal database schema and data excerpt as well as a short script which triggers the error. You may need to run the excepts as 'postgres' or explicitly edit schema.sql or otherwise give yourself required permissions to the resultant database. $ createdb pgsql_bug $ psql pgsql_bug -f schema.sql $ psql pgsql_bug -f script.sql The final statement in script.sql triggers the error in question: psql:script.sql:63: ERROR: XX000: variable not found in subplan target list LOCATION: replace_vars_with_subplan_refs_mutator, setrefs.c:685 ADDENDUM: - I had to remove the attached schema.sql file as the list manager refused the complete submission for overall message size (112KB). I tried to reduce the size of the data, but ran into trouble reproducing the error. With the original data or a smaller extract without running 'vacuum analyze' (or a smaller extract and running 'vacuum analyze') the planner takes a different path and the error is not triggered. I cannot divine enough information to force the planner to take the error path, as I can't use 'EXPLAIN' on this query (it fails in the same place.) Please provide feedback with an off-list email address for the data sample for reproduction. thanks, Damon Hart -- only needed if script rerun without psql restart drop table fill_dates; drop table tmp_contract; set ENABLE_NESTLOOP to on; set ENABLE_MERGEJOIN to on; set ENABLE_HASHJOIN to on; VACUUM ANALYZE; SELECT DISTINCT d.date INTO TEMP fill_dates FROM daily d, futures f WHERE d.inst_id = f.inst_id AND f.fut_base_id = 3209 AND f.inst_id <= 3517 AND d.date >= '2002-01-08' AND d.data_source_id = 36869 AND d.trade_venue_id = 3347 ORDER BY d.date DESC LIMIT 300; SELECT date, open, high, low, close, volume, open_interest, update_time, user_id INTO TEMP tmp_contract FROM daily WHERE inst_id = 3517::bigint AND data_source_id = 36869::bigint AND trade_venue_id = 3347::bigint AND date >= (SELECT MIN(date) FROM fill_dates); create unique index tmp_contract_date on tmp_contract (date); vacuum analyze tmp_contract; DELETE FROM fill_dates WHERE EXISTS (SELECT tc.date FROM tmp_contract tc WHERE tc.date = fill_dates.date); set ENABLE_NESTLOOP to on; set ENABLE_MERGEJOIN to off; set ENABLE_HASHJOIN to off; -- query triggers error INSERT INTO tmp_contract SELECT d.date, d.open + tc.close - d2.close AS open, d.high + tc.close - d2.close AS high, d.low + tc.close - d2.close AS low, d.close + tc.close - d2.close AS close, d.volume, d.open_interest, d.update_time, d.user_id FROM daily d, daily d2, tmp_contract tc, fill_dates fd WHERE d.date = fd.date AND d.inst_id = 3516::bigint AND d.data_source_id = 36869::bigint AND d.trade_venue_id = 3347::bigint AND d2.inst_id = 3516::bigint AND d2.data_source_id = 36869::bigint AND d2.trade_venue_id = 3347::bigint AND d2.date = (SELECT MIN(d3.date) FROM daily d3, tmp_contract tc2 WHERE d3.date = tc2.date AND d3.data_source_id = 36869::bigint AND d3.trade_venue_id = 3347::bigint AND d3.inst_id = 3516::bigint AND d3.date > d.date) AND tc.date = d2.date; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1083: Insert query reordering interacts badly with
Tom Lane wrote:
How about
SELECT nextval('seq'); -- ignore result
INSERT INTO ... VALUES (currval('seq'), currval('seq'));
Well, it works for my sample case, I have to agree. Maybe I should
mention that I tried to boil down the bugreport to the simplest repro
case I could.
My actual SQL looks roughly like
INSERT INTO destination (record_id, page, row)
SELECT
(SELECT record_id FROM record ORDERED BY name),
(NEXTVAL('seq') / 200),
(CURRVAL('seq') % 200)
While I have a workaround, I am definitely curious as to whether there
is actually a way to do it. Thanks for your patience.
regards,
martin
--
---
Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224 MOB: +64(21)364-017
Make things as simple as possible, but no simpler - Einstein
---
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] Can't do any operation with the Server.
Hello, I downloaded an install the 'PeerDirect PostgreSQL beta for Windows'. I followed all instructions ' started the server and I verified that port 5432 is listening. Also : 'pg_ctl ststus' = running. However when I try to createdb , I get stucked and do nothing. What is the problem? Please assist. Ronen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Known issue with Reindex-based corruption?
Tom, > It'd be more productive for them to update to 7.4 ... It's a distributed app, meaning that they have boxes in the field which can not be practically updated by remote. They'll be using 7.4 for *new* boxes, sometime around November. Their requirements include 6 months of testing before release. -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 651-9224 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Known issue with Reindex-based corruption?
Josh Berkus <[EMAIL PROTECTED]> writes: > Would this be back-patchable by a good PG hacker? The client has $$$. It'd be more productive for them to update to 7.4 ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Known issue with Reindex-based corruption?
Tom, > That's a definite possibility. Before 7.4 we did not emit WAL records > for data written during index build. What we could have here is that > the transaction completed and synced to WAL, but none of the data-file > writes were sent to disk before power-out. On restart, WAL replay would > faithfully update the pg_class row, but the index file would still be > empty :-( Would this be back-patchable by a good PG hacker? The client has $$$. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Known issue with Reindex-based corruption?
Josh Berkus <[EMAIL PROTECTED]> writes: > The basic symptoms are: > 1) Machine stated scheduled REINDEX. > 2) Unexpected power-out > 3) On reboot, we have 2 different versions of the index file on disk, > one with 0 bytes. Attempts to use the index (via SELECT) result in > statement-fatal errors. Hm. Unless the REINDEX actually *completed* before the power-out, it should not have had any effect other than creation of an unreferenced file. My guess is that the reindex did complete, and updated the index's pg_class row to point at the new file, but for some reason only the pg_class update got down to disk. > I'm waiting on more data. For now, I was wondering whether there was > a known issue with WAL recovery on indexes in 7.2.4. Neil thought > there was. That's a definite possibility. Before 7.4 we did not emit WAL records for data written during index build. What we could have here is that the transaction completed and synced to WAL, but none of the data-file writes were sent to disk before power-out. On restart, WAL replay would faithfully update the pg_class row, but the index file would still be empty :-( regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
