Re: [BUGS] BUG #2379: Duplicate pkeys in table
Philip Warner wrote: > We have an intermittent bug that occurs on a table which is updated several > times per second. The bug occurs every few days/weeks. It is usually > preceeded by a "tuple concurrently updated" messages, but I could not swear > it is always preceeded by it. > > The result of the bug is demonstrated by: > > select id,count(*) from xxx group by id having count(*)>1; > id | count > ---+--- > 24613 | 6 > (1 row) Please do a SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; if you still have that particular manifestation. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2379: Duplicate pkeys in table
On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote: > Please do a > > SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; > > if you still have that particular manifestation. Also, you'll probably need to set enable_indexscan to off prior to running the above query. -- Michael Fuhr ---(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 #2379: Duplicate pkeys in table
Michael Fuhr wrote: > On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote: > >> Please do a >> >> SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; >> # set enable_indexscan=off; # SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; xmin | xmax | cmin | cmax --+--+--+-- 32902771 |0 | 20 | 32902872 32902771 |0 | 20 | 32902872 32902771 |0 | 20 | 32902872 32902771 |0 | 20 | 32902872 32902771 |0 | 20 | 32902872 32902771 | 33048159 | 20 | 20 (6 rows) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] PostgreSQL 8.1.3.6044 crashes randomly.
On Apr 2, 2006, at 8:36 PM, Anthony Ransley wrote: The Windows version of PostgreSQL 8.1.3.6044 has randomly crashed a few times now. Can anyone supply me with the symbol set for the 8.1.3.6044 Windows release, so I can provide more information and maybe even debug it. What's the data contained in the error report (the "click here" link)? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2372: dblink_exec doesn't return. NEVER!
On Apr 5, 2006, at 7:28 AM, William Leite Araújo wrote: On 4/3/06, Tom Lane <[EMAIL PROTECTED]> wrote: (...) You need to read up on SECURITY DEFINER functions. regards, tom lane Ok, I'll do this way, but still don't understand why it doesn't returns. I'm doing things simillar to this to create users automatically, and works fine... As Tom mentioned, it's probably an issue of waiting on a lock that it can't acquire. Just because it worked for creating users doesn't mean it's a good idea. It's certainly less efficient than a SECURITY DEFINER function, for starters... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Philip Warner <[EMAIL PROTECTED]> writes: > # set enable_indexscan=off; > # SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; >xmin | xmax | cmin | cmax > --+--+--+-- > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 | 33048159 | 20 | 20 > (6 rows) For completeness, could we also see ctid in that query? 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 #2379: Duplicate pkeys in table
Philip Warner wrote: > # set enable_indexscan=off; > # SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; >xmin | xmax | cmin | cmax > --+--+--+-- > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 |0 | 20 | 32902872 > 32902771 | 33048159 | 20 | 20 > (6 rows) Ugh. Do the triggers involved have EXCEPTION clauses? (I assume they are written in PL/pgSQL -- are there any in other languages?) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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 #2379: Duplicate pkeys in table
Tom Lane wrote: > For completeness, could we also see ctid in that query? mail=# set enable_indexscan=off; mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613; xmin | xmax | cmin | cmax | ctid --+--+--+--+- 32902771 |0 | 20 | 32902872 | (0,7) 32902771 |0 | 20 | 32902872 | (2,27) 32902771 |0 | 20 | 32902872 | (58,27) 32902771 |0 | 20 | 32902872 | (60,28) 32902771 |0 | 20 | 32902872 | (69,3) 32902771 | 33048159 | 20 | 20 | (72,27) (6 rows) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Alvaro Herrera wrote: > Do the triggers involved have EXCEPTION clauses? (I assume they are > written in PL/pgSQL -- are there any in other languages?) Triggers that update this table are in pl/pgsql, and can *raise* exceptions (using RAISE) if that is what you mean. They do not handle them -- is that even possible on pl/pgsql? Other triggers (for slony) are written in plpgsql and C, and I know some of the pl/pgsql triggers raise exceptions. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Philip Warner <[EMAIL PROTECTED]> writes: > mail=# set enable_indexscan=off; > mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613; >xmin | xmax | cmin | cmax | ctid > --+--+--+--+- > 32902771 |0 | 20 | 32902872 | (0,7) > 32902771 |0 | 20 | 32902872 | (2,27) > 32902771 |0 | 20 | 32902872 | (58,27) > 32902771 |0 | 20 | 32902872 | (60,28) > 32902771 |0 | 20 | 32902872 | (69,3) > 32902771 | 33048159 | 20 | 20 | (72,27) > (6 rows) The "cmax" values in the first 5 rows are evidently really xvac values, ie, these have all been moved by VACUUM FULL. (I assume you run VACUUM FULL regularly on this table?) The thing that is striking though is that the xmin/cmin values are all the same, indicating that all six tuples were inserted by the same command. That seems pretty odd. Can you show us the procedure by which rows are inserted in this table? Also, the last tuple has either been deleted or locked-for-update by transaction 33048159; if it were an attempted deletion we'd have to conclude that 33048159 failed to commit. Do you use SELECT FOR UPDATE on this table? BTW, which of these rows is selected by an indexscan-enabled query, ie, set enable_indexscan=on then repeat same query? regards, tom lane ---(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 #2379: Duplicate pkeys in table
Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > >> mail=# set enable_indexscan=off; >> mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613; >>xmin | xmax | cmin | cmax | ctid >> --+--+--+--+- >> 32902771 |0 | 20 | 32902872 | (0,7) >> 32902771 |0 | 20 | 32902872 | (2,27) >> 32902771 |0 | 20 | 32902872 | (58,27) >> 32902771 |0 | 20 | 32902872 | (60,28) >> 32902771 |0 | 20 | 32902872 | (69,3) >> 32902771 | 33048159 | 20 | 20 | (72,27) >> (6 rows) >> > > The "cmax" values in the first 5 rows are evidently really xvac values, > ie, these have all been moved by VACUUM FULL. (I assume you run VACUUM > FULL regularly on this table?) Yes, every minute. Table has about 1500 rows and grows *very* fast due to updates. > The thing that is striking though is > that the xmin/cmin values are all the same, indicating that all six > tuples were inserted by the same command. That seems pretty odd. Can > you show us the procedure by which rows are inserted in this table? > The original insertion is probably not relevant (it happened months ago); there are many places that update the table. And for the specific row in question, it was probably inserted directly by psql. Other rows exhibit this problem (less often), were usually inserted by a long pgsql procedure. Updates happen regularly from many sources, but the procedure that does the most updates is a trigger. Do you want to see that? > Also, the last tuple has either been deleted or locked-for-update by > transaction 33048159; if it were an attempted deletion we'd have to > conclude that 33048159 failed to commit. Do you use SELECT FOR UPDATE > on this table? > No. But when a new row is added, I do lock the table in exclusive mode: Lock Table xxx In Exclusive Mode; The specific row in these examples will never be deleted. > BTW, which of these rows is selected by an indexscan-enabled query, > ie, set enable_indexscan=on then repeat same query? > xmin | xmax | cmin | cmax | ctid --+--+--+--+- 32902771 | 33048159 | 20 | 20 | (72,27) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Philip Warner <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The thing that is striking though is >> that the xmin/cmin values are all the same, indicating that all six >> tuples were inserted by the same command. That seems pretty odd. Can >> you show us the procedure by which rows are inserted in this table? >> > The original insertion is probably not relevant (it happened months > ago); there are many places that update the table. OK, what I should say is that all these tuples were updated by the same command. > Updates happen regularly from many sources, but the procedure that does > the most updates is a trigger. Do you want to see that? Please. Also, if you care to run pg_filedump -i -F over the table, it'd be interesting to see the complete header info for each of these tuples. regards, tom lane ---(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 #2379: Duplicate pkeys in table
Tom Lane wrote: >> Updates happen regularly from many sources, but the procedure that does >> the most updates is a trigger. Do you want to see that? >> > > Please. > public | tg_update_qqq_date | "trigger" | | mail | plpgsql | Declare uid bigint; Begin uid = (select owner_id from yyy m where m.f1 = NEW.f1); if (uid <> 0 and not uid is null) then update xxx set qqq_date = 'now' where id=uid; end if; Return NEW; End; | and there's also a rewrite rule: zzz_update_r1 AS ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f1 = new.f1 WHERE xxx.id = new.id zzz_update_r2 AS ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f2 = new.f2 WHERE xxx.id = new.id > Also, if you care to run pg_filedump -i -F over the table, it'd be > interesting to see the complete header info for each of these tuples. > obviously from different blocks (do you need more details?): Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 Block Id: 0 linp Index: 7 Attributes: 34 Size: 36 infomask: 0x2913 (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 [4]: 0x00 0f50: 730ef601 1400 d80ef601 s... 0f60: 07002200 1329249f 807e8400 .."..)$..~.. 0f70: d37e 2560 0900 .~..%`.. 0f80: 0f90: 0400 12bcf968 ...h 0fa0: d28fa741 2200 5f5f4021 696e7465 ...A"[EMAIL PROTECTED] 0fb0: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us 0fc0: 65722140 5f5f [EMAIL PROTECTED] 0fd0: 0100 3c307819 0e1fa441 <0xA 0fe0: 3c307819 0e1fa441 3c307819 0e1fa441 <0xA<0xA 0ff0: Item 27 -- Length: 168 Offset: 2700 (0x0a8c) Flags: USED XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 Block Id: 2 linp Index: 27 Attributes: 34 Size: 36 infomask: 0x2913 (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 [4]: 0x00 0a8c: 730ef601 1400 d80ef601 s... 0a9c: 0200 1b002200 1329249f 807e8400 .."..)$..~.. 0aac: d37e 2560 0900 .~..%`.. 0abc: 0acc: 0400 12bcf968 ...h 0adc: d28fa741 2200 5f5f4021 696e7465 ...A"[EMAIL PROTECTED] 0aec: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us 0afc: 65722140 5f5f [EMAIL PROTECTED] 0b0c: 0100 3c307819 0e1fa441 <0xA 0b1c: 3c307819 0e1fa441 3c307819 0e1fa441 <0xA<0xA 0b2c: Item 27 -- Length: 168 Offset: 7724 (0x1e2c) Flags: USED XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 Block Id: 58 linp Index: 27 Attributes: 34 Size: 36 infomask: 0x2913 (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 [4]: 0x00 1e2c: 730ef601 1400 d80ef601 s... 1e3c: 3a00 1b002200 1329249f 807e8400 ..:..."..)$..~.. 1e4c: d37e 2560 0900 .~..%`.. 1e5c: 1e6c: 0400 12bcf968 ...h 1e7c: d28fa741 2200 5f5f4021 696e7465 ...A"[EMAIL PROTECTED] 1e8c: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us 1e9c: 65722140 5f5f [EMAIL PROTECTED] 1eac: 0100 3c307819 0e1fa441 <0xA 1ebc: 3c307819 0e1fa441 3c307819 0e1fa441 <0xA<0xA 1ecc: Item 28 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 Block Id: 60 linp Index: 28 Attributes: 34 Size: 36 infomask: 0x2913 (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 [4]: 0x00 1f58: 730ef601 1400 d80ef601 s... 1f68: 3c00 1c002200 1329249f 807e8400 ..<..."..)$..~.. 1f78: d37e 2560 0900 .~..%`.. 1f88: 1f98: 0400 12bcf968 ...h 1fa8: d28fa741 2200 5f5f4021 696e7465 ...A"[EMAIL PROTECTED] 1fb8: 726e616c 5f64656c 69766572 795f7573 rnal_delivery_us 1fc8: 65722140 5f5f [EMAIL PROTECTED] 1fd8: 0100 3c307819 0e1fa441 <0xA 1fe8: 3c307819 0e1fa441 3c307819 0e1fa441 <0xA<0xA 1ff8:
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Philip Warner <[EMAIL PROTECTED]> writes: > public | tg_update_qqq_date | "trigger" > | | mail | plpgsql | > Declare > uid bigint; > Begin > uid = (select owner_id from yyy m where m.f1 = NEW.f1); > if (uid <> 0 and not uid is null) then > update xxx set qqq_date = 'now' where id=uid; > end if; > Return NEW; > End; | > and there's also a rewrite rule: > zzz_update_r1 AS > ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f1 = new.f1 > WHERE xxx.id = new.id > zzz_update_r2 AS > ON UPDATE TO zzz DO INSTEAD UPDATE xxx SET f2 = new.f2 > WHERE xxx.id = new.id OK, I'm a bit confused by the obfuscation here. The table with the duplicates is xxx, or qqq? Which one is the trigger attached to? zzz is a view I suppose? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Tom Lane wrote: > OK, I'm a bit confused by the obfuscation here. The table with the > duplicates is xxx, or qqq? Yes, xxx is the broken table. The two rewrite rules map updates on a view to an underlying table (the broken one). Updates on the view occur very frequently. Perhaps 400,000 per day? And, sadly, 200,000 of those on this one row. > Which one is the trigger attached to? > zzz is a view I suppose? > The trigger is attached to yet another table; when an insert or update occurs in this third table, a date field on xxx is updated. Updates/inserts on this happen less frequently -- perhaps low 10's of thousands per day. I had forgotten about the view/rewrite rules until I looked again at the db source (I thought they were triggers). These are the source of 90% of the updates. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Tom Lane wrote: > OK, I'm a bit confused by the obfuscation here. The table with the > duplicates is xxx, or qqq? Possibly less obscure version: public | tg_update_anotherTable_date | "trigger" | | mail | plpgsql | Declare uid bigint; Begin uid = (select owner_id from anotherNotherTable m where m.keyField = NEW.fkField); if (uid <> 0 and not uid is null) then update brokenTable set some_date = 'now' where id=uid; end if; Return NEW; End; | and there's also a rewrite rule: aView_update_r1 AS ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1 WHERE brokenTable.id = new.id aView_update_r2 AS ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2 WHERE brokenTable.id = new.id ---(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 #2379: Duplicate pkeys in table
Philip Warner <[EMAIL PROTECTED]> writes: > aView_update_r1 AS > ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1 > WHERE brokenTable.id = new.id > aView_update_r2 AS > ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2 > WHERE brokenTable.id = new.id OK, that's a bit clearer. It'd help to see the view definition too. regards, tom lane ---(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 #2379: Duplicate pkeys in table
Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > >> aView_update_r1 AS >> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1 >> WHERE brokenTable.id = new.id >> aView_update_r2 AS >> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2 >> WHERE brokenTable.id = new.id >> > > OK, that's a bit clearer. It'd help to see the view definition too. > Nothing special, just some 'fake' fields and no join (mainly there for access control and compatibility across several applications): SELECT brokenTable.id, brokenTable.someField1 AS someAlias1, brokenTable."someField2"::character varying(64) AS someAlias2, 0::bigint AS someAlias3, brokenTable.someField4, 'crypt'::character varying AS someAlias5, brokenTable.fieldUpdatedByTrigger, brokenTable.fieldUpdatedByRule1 FROM brokenTable; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Oops. Minor change. Last two fields are updated by rules. Tom Lane wrote: > Philip Warner <[EMAIL PROTECTED]> writes: > >> aView_update_r1 AS >> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f1 = new.f1 >> WHERE brokenTable.id = new.id >> aView_update_r2 AS >> ON UPDATE TO aView DO INSTEAD UPDATE brokenTable SET f2 = new.f2 >> WHERE brokenTable.id = new.id >> > > OK, that's a bit clearer. It'd help to see the view definition too. > Nothing special, just some 'fake' fields and no join (mainly there for access control and compatibility across several applications): SELECT brokenTable.id, brokenTable.someField1 AS someAlias1, brokenTable."someField2"::character varying(64) AS someAlias2, 0::bigint AS someAlias3, brokenTable.someField4, 'crypt'::character varying AS someAlias5, brokenTable.fieldUpdatedByRule1, brokenTable.fieldUpdatedByRule2 FROM brokenTable; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] right sibling is not next child
Apologies if this is a duplicate, but my original post stalled and I noticed I had omitted the postgres version, which you will want. I'm reporting this as a PostgreSQL bug because it involves an index corruption. I can't see any other way our application should be able to corrupt an index. I will attach the tail of the log when the corruption was detected (and the postmaster shut itself down), as well as the subsequent attempt to start. Fortunately we run our web site off of a farm of four database servers, so we are taking one of the others out of the mix, stopping postmaster, and copying its data directory over to this machine for recovery, so we don't need advice on that aspect of things; but, we'd like to do what we can to help track down the cause to prevent a recurrence. We have renamed the data directory to make room for recovery at the normal location, but otherwise the failing data directory structure is unmodified. For context, this is running on Windows 2003 Server. Eight Xeon box, no HT, 6 GB RAM, 13 drive 15,000 RPM RAID5 array through battery backed controller for everything. This database is about 180 GB with about 300 tables. We are running 8.1.3 modified with a patch we have submitted (pending review last I saw) to implement the standard_conforming_strings TODO. We have autovacuum running every ten seconds because of a few very small tables with very high update rates, and we have a scheduled VACUUM ANALYZE VERBOSE every night. It appears that last night's vacuum found the problem, which the previous night's vacuum didn't. We had some event which started at 14:25 yesterday which persisted until we restarted the middle tier at 15:04. The symptom was that a fraction of the queries which normally run in a few ms were timing out on a 20 second limit. pg_locks showed no blocking. We've been getting episodes with these symptoms occassionally, but they have only lasted a minute or two; this duration was unusual. We haven't identified a cause. One odd thing is that with the number of queries per second that we run, the number of timeouts during an episode is too small to support the notion that _all_ similar queries are failing. How best to proceed? -Kevin postgresql-2006-04-06_00-tail.log Description: Binary data postgresql-2006-04-06_072250.log Description: Binary data ---(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 #2379: Duplicate pkeys in table
Philip Warner wrote: > Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED > XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 > Block Id: 0 linp Index: 7 Attributes: 34 Size: 36 > infomask: 0x2913 > (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) > t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 > [4]: 0x00 Hmm, shouldn't we see the MOVED_OFF bit set also if the cmax/xvac field is actually xvac? > Item 27 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED > XMIN: 32902771 CMIN: 20 XMAX: 33048159 CMAX|XVAC: 20 > Block Id: 318 linp Index: 6 Attributes: 34 Size: 36 > infomask: 0x2913 > (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) > t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 > [4]: 0x00 I'm confused -- the original report showed this tuple with ctid (72,27), but this seems to be in a different block? What's the explanation for this tuple to have cmin=cmax? Is this normal? Sorry I have only questions :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Philip Warner wrote: >> Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED >> XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872 >> Block Id: 0 linp Index: 7 Attributes: 34 Size: 36 >> infomask: 0x2913 >> (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) >> t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 >> [4]: 0x00 > Hmm, shouldn't we see the MOVED_OFF bit set also if the cmax/xvac field > is actually xvac? These are probably MOVED_IN not MOVED_OFF tuples; VACUUM FULL clears their MOVED_IN flags at the end (see update_hint_bits()), which explains the lack of any flag being set. >> Item 27 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED >> XMIN: 32902771 CMIN: 20 XMAX: 33048159 CMAX|XVAC: 20 >> Block Id: 318 linp Index: 6 Attributes: 34 Size: 36 >> infomask: 0x2913 >> (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) >> t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84 >> [4]: 0x00 > I'm confused -- the original report showed this tuple with ctid (72,27), > but this seems to be in a different block? The blockid/linp fields are pointing to the updated version of the row that xid 33048159 tried to create. It's a plausible theory that the reason 33048159 failed (as it evidently did, XMAX_INVALID) is that it failed on insertion of a duplicate key into the pkey index. It is interesting that the CMIN and CMAX are the same, but that probably just says that 32902771 and 33048159 were instances of the exact same SQL command pattern, and so their attempted updates both came 20 commands into the transaction. Philip suggested to me off-list that the initial error may have been the VACUUM FULL (xid 32902872) creating duplicate moved copies of a single valid row. That seems plausible because VACUUM FULL suppresses duplicate-index checks, and it's real hard to see any other way that a single transaction could have inserted all of these tuples without triggering the btree duplicate-key check (barring a completely corrupt index anyway). I wouldn't be surprised in the least bit to find another corner-case bug in the VACUUM FULL tuple chain moving code ... that bit of spaghetti has been too complex since day one ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] right sibling is not next child
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > [2006-04-06 02:19:57.460 ] 3848 PANIC: > right sibling is not next child in "Panel_pkey" This should be repeatable by re-attempting a VACUUM, right? Please find out which page exactly it's unhappy about (either gdb the crash or add a printout of the "parent" variable to the elog call in nbtpage.c), then pg_filedump the index and look to see what the index contains. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] right sibling is not next child
Right now the postmaster refuses to start. What is the best way to get past that to try what you suggest? [2006-04-06 07:22:50.347 ] 3984 LOG: database system was interrupted while in recovery at 2006-04-06 02:19:59 Central Daylight Time [2006-04-06 07:22:50.347 ] 3984 HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. [2006-04-06 07:22:50.347 ] 3984 LOG: checkpoint record is at F6/50052F10 [2006-04-06 07:22:50.347 ] 3984 LOG: redo record is at F6/50052F10; undo record is at 0/0; shutdown FALSE [2006-04-06 07:22:50.347 ] 3984 LOG: next transaction ID: 313381941; next OID: 2321597 [2006-04-06 07:22:50.347 ] 3984 LOG: next MultiXactId: 1; next MultiXactOffset: 0 [2006-04-06 07:22:50.347 ] 3984 LOG: database system was not properly shut down; automatic recovery in progress [2006-04-06 07:22:50.347 ] 3984 LOG: redo starts at F6/50052F58 [2006-04-06 07:22:50.347 ] 4076 FATAL: the database system is starting up [2006-04-06 07:22:50.347 ] 4064 FATAL: the database system is starting up [2006-04-06 07:22:50.378 ] 3984 PANIC: heap_clean_redo: no block -Kevin >>> On Thu, Apr 6, 2006 at 12:40 pm, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: >> [2006- 04- 06 02:19:57.460 ] 3848 PANIC: right > sibling is not next child in "Panel_pkey" > > This should be repeatable by re- attempting a VACUUM, right? Please find > out which page exactly it's unhappy about (either gdb the crash or add a > printout of the "parent" variable to the elog call in nbtpage.c), then > pg_filedump the index and look to see what the index contains. > > regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] right sibling is not next child
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Right now the postmaster refuses to start. What is the best way to get > past that to try what you suggest? > [2006-04-06 07:22:50.378 ] 3984 PANIC: heap_clean_redo: no block Hm, did this start happening immediately after the other problem? That would suggest that you've got worse problems than just a corrupt index. You weren't by any chance running with full_page_writes = off were you? You could get past the startup failure with pg_resetxlog, but it's not clear whether you'd have a consistent database afterward. What I'd suggest first is saving a copy of the entire $PGDATA tree for forensic purposes (not to mention being able to go back to that state if you need to). Is there any chance of letting someone else have a look at the database contents? Otherwise you're going to have to do your own sleuthing to figure out what went wrong ... 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] right sibling is not next child
>>> On Thu, Apr 6, 2006 at 12:57 pm, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: >> Right now the postmaster refuses to start. What is the best way to get >> past that to try what you suggest? > >> [2006- 04- 06 07:22:50.378 ] 3984 PANIC: heap_clean_redo: no block > > Hm, did this start happening immediately after the other problem? This started happening on the first attempt to start the postmaster after the other error, which left the postmaster down, apparently after a failed restart attempt. > That would suggest that you've got worse problems than just a corrupt > index. You weren't by any chance running with full_page_writes = off > were you? Yes we were. Apparently I have misunderstood the implications of this. Somehow I had convinced myself that this setting was relatively safe in our environment, due to our battery-backed controllers. I'd convinced myself, after reading carefully through the documentation of this setting, that I would be OK as long as that functioned correctly, and have problems regardless of this setting if it didn't. If you show me where I went wrong, maybe I can suggest a patch to the docs to prevent others from going down the wrong path in this regard. (Of course, maybe it's all there and I just had a bad day when I thought this through.) > You could get past the startup failure with pg_resetxlog, but it's not > clear whether you'd have a consistent database afterward. What I'd > suggest first is saving a copy of the entire $PGDATA tree for forensic > purposes We already have this forensic copy and a replacement production copy on this box. I think we'll need to copy to another box to get a second forensic copy, to avoid risking an out-of-space condition. That can be done, but it'll take a few hours. > (not to mention being able to go back to that state if you need > to). That's not an issue for production purposes. > Is there any chance of letting someone else have a look at the database > contents? There is a lot of data in the database which is confidential by law. I'd have to jump through a lot of hoops to get anyone to even consider letting me ship it off site. If you're asking whether you could access in to our site, that might be arranged. -Kevin ---(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] right sibling is not next child
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> You weren't by any chance running with full_page_writes = off >> were you? > Yes we were. Apparently I have misunderstood the implications of this. So had we all :-(. It just plain doesn't work in 8.1.*, and will be disabled in 8.1.4 --- see discussion last week. It might or might not get resurrected in 8.2, depending on how messy it seems to be to fix. Anyway, that explains your "heap_clean_redo: no block" failure. I think you're stuck risking a pg_resetxlog to try to get back into the database. If that results in a hopelessly corrupt database, we can try modifying the WAL replay code to not consider this a fatal error, and see if that produces anything we can use for debugging. I'm glad this isn't your only copy of the database ... > There is a lot of data in the database which is confidential by law. > I'd have to jump through a lot of hoops to get anyone to even consider > letting me ship it off site. If you're asking whether you could access > in to our site, that might be arranged. It sounds like the DB is too big to consider shipping anywhere anyway. As long as you're comfortable doing stuff like pg_filedump and modifying the code to get more debug info, we can proceed without getting into the question of remote access. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] right sibling is not next child
>>> On Thu, Apr 6, 2006 at 1:26 pm, in message <[EMAIL PROTECTED]>, > "Kevin Grittner" <[EMAIL PROTECTED]> writes: >> Tom Lane <[EMAIL PROTECTED]> wrote: >>> You weren't by any chance running with full_page_writes = off >>> were you? > >> Yes we were. Apparently I have misunderstood the implications of this. > > So had we all :- (. It just plain doesn't work in 8.1.*, and will be > disabled in 8.1.4 --- see discussion last week. Dang! I've not been able to keep up with the lists, and I missed that thread. I'll change that setting as soon as possible. > Anyway, that explains your "heap_clean_redo: no block" failure. I think > you're stuck risking a pg_resetxlog to try to get back into the > database. If that results in a hopelessly corrupt database, we can try > modifying the WAL replay code to not consider this a fatal error, and > see if that produces anything we can use for debugging. Will do. Before I do that, though, is it worth making a copy? (The down side is primarily the delay of copying it.) > I'm glad this isn't your only copy of the database ... We go beyond being a belt-and-suspenders shop. Think staples and glue-gun, too. ;-) This is one of four copies of what is redundant data to start with, and we have backups. So recovery is no sweat, but we got emails about timeouts from the public during this incident, so the underlying bug is important to us. > As long as you're comfortable doing stuff like pg_filedump and modifying > the code to get more debug info, we can proceed without getting into the > question of remote access. OK. If you later think we do need to go that direction, send me an email off-list. -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] right sibling is not next child
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Anyway, that explains your "heap_clean_redo: no block" failure. I think >> you're stuck risking a pg_resetxlog to try to get back into the >> database. > Will do. Before I do that, though, is it worth making a copy? (The > down side is primarily the delay of copying it.) Yeah, I think you'd better --- pg_resetxlog is destructive, and if we find the database unusable for debugging afterward, we'll need that copy. 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
[BUGS] Bug in window xp
Version(8.1.3) Bug in window xp: C:\Documents and Settings\openbase>pg_ctl start LOG: database system was shut down at 2006-4-04 15:54:43 中国标准时间LOG: checkpoint record is at 0/38C2E0LOG: redo record is at 0/38C2E0; undo record is at 0/0; shutdown TRUELOG: next transaction ID: 569; next OID: 24576LOG: next MultiXactId: 1; next MultiXactOffset: 0LOG: database system is readyLOG: transaction ID wrap limit is 2147484146, limited by database "postgres" C:\Documents and Settings\openbase>C:\Documents and Settings\openbase>C:\Documents and Settings\openbase>C:\Documents and Settings\openbase>psqlWelcome to psql 8.1.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit openbase=# SELECT (-2147483648) / (-1);LOG: server process (PID 3760) was terminated by signal 21LOG: terminating any other active server processesLOG: all server processes terminated; reinitializing服务器意外地关闭了联接 这种现象通常意味着服务器在处理请求之前或者正在处理请求的时候意外中止与服务器的联接已丢失. 尝试重置: LOG: database system was interrupted at 2006-0-05 08:39:56 中国标准时间LOG: checkpoint record is at 0/38C2E0LOG: redo record is at 0/38C2E0; undo record is at 0/0; shutdown TRUELOG: next transaction ID: 569; next OID: 24576LOG: next MultiXactId: 1; next MultiXactOffset: 0LOG: database system was not properly shut down; automatic recovery in progres FATAL: the database system is starting up失败.!> LOG: record with zero length at 0/38C328LOG: redo is not requiredLOG: database system is readyLOG: transaction ID wrap limit is 2147484146, limited by database "postgres" 王海永东软集团软件产品事业部 地址：沈阳市浑南高新技术产业开发区东软软件园 A1座邮编：110179电话：024－83661905公司网址：www.neusoft.com Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) is intended only for the use of the intended recipient and may be confidential and/or privileged of Neusoft Group Ltd., its subsidiaries and/or its affiliates. If any reader of this communication is not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying is strictly prohibited, and may be unlawful. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete the original message and all copies from your system. Thank you.
Re: [BUGS] NLS vs error processing, again
> JiangWei <[EMAIL PROTECTED]> writes: > > LANG=zh_CN.UTF-8 > > [ set client_encoding to LATIN1 and provoke an error ] > > OK, I can reproduce the crash after initdb'ing with that LANG setting > (in an nls-enabled build). The postmaster log fills with a whole lot > of occurrences of > > ��: � UTF-8 �� 0x00e9 > ��: � UTF-8 �� 0x00e8 > ��: � UTF-8 �� 0x00e8 > ��: � UTF-8 �� 0x00e8 > �: ERRORDATA_STACK_SIZE exceeded > > Tracing through the dump shows that the error-handling code is > recursively producing this warning while trying to translate the word > WARNING to LATIN1. The zh_CN.po file shows the translation as > > #: utils/error/elog.c:1909 > msgid "WARNING" > msgstr "" > > (which apparently is GB2312?) It seems. zh_CN.po has the line: "Content-Type: text/plain; charset=GB2312\n" Which means at least someone who wrote the file intended to be it as GB2312. However, please note that GB2312 is a character set, not an encoding. The reality is that the file seems encoded in EUC-CN. Note that I have confirmed this by just examining the bytes above () are correct EUC-CN byte sequences. It is posibble that the file is not written in EUC-CN, but I guess it's hardly possible. > and what's actually getting passed to > utf8_to_iso8859_1() is > > (gdb) x/6o str > 0x8b89d8: 035002550246034502210212 > > I have no idea if this is a correct UTF8 transliteration of the GB2312 > phrase --- can anyone confirm? As fas as looking into utils/mb/Unicode/euc_cn_to_utf8.map, the translation above seems to be correct. BTW, who does the translation from EUC-CN to UTF-8? Maybe gettext()? -- Tatsuo Ishii SRA OSS, Inc. Japan > But anyway, if this is Chinese then it's > hardly surprising that there would be no LATIN1 equivalent. And then > trying to report the problem gets us into a new instance of the same > problem. Even the code that's supposed to stop error recursion doesn't > get us out of it. > > It seems to me that there basically is no graceful solution to this sort > of mismatch. It might be possible to kluge things so that we disable > NLS once we've recursed too many times in error processing, but that's > surely pretty ugly. What would be a lot more user-friendly would be to > refuse the attempt to set client_encoding to something that can't handle > our error message encoding, but I don't know what a reasonable set of > restrictions would be. > > Comments? > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2380: Sequence problem
The following bug has been logged online: Bug reference: 2380 Logged by: Alex Fomin Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: Ubuntu Linux Description:Sequence problem Details: While using the following function: --- nextval(sequence_name) returns currval(sequence_name) -1 --- while +1 is expected. It happens only sometimes, no dependency can be found. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2378: installtation fail with error in Runinitdb
The following bug has been logged online: Bug reference: 2378 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.1 Operating system: windown XP, sp2 Description:installtation fail with error in Runinitdb Details: I follow the document of installation to install postgre database. The error message in log file is listed below. This problem is repeatable. Any advice to solve it? Thanks for any help! Wei 1. messages in install log file: MSI (s) (4C:74): Executing op: CustomActionSchedule(Action=SetPermissions,ActionType=1025,Source=BinaryData ,[EMAIL PROTECTED],CustomActionData=1033;D:\PostgreSQL\8.1\;D:\Postgre SQL\8.1\data\;MECHINE1;postgres;passwprd) MSI (s) (4C:74): Creating MSIHANDLE (2) of type 790536 for thread 3188 MSI (s) (4C:74): Executing op: ActionStart(Name=RunInitdb,Description=Initializing database cluster (this may take a minute or two)...,) Action 15:19:44: RunInitdb. Initializing database cluster (this may take a minute or two)... MSI (s) (4C:74): Executing op: CustomActionSchedule(Action=RunInitdb,ActionType=1025,Source=BinaryData,Targ [EMAIL PROTECTED],CustomActionData=1033;D:\PostgreSQL\8.1\;D:\PostgreSQL\8.1\da ta\;D:\PostgreSQL\8.1\share\;5432;;C;SQL_ASCII;postgres;password;MECHINE1 ;postgres;password;) MSI (s) (4C:74): Creating MSIHANDLE (3) of type 790536 for thread 3188 DEBUG: Error 2888: Executing the TextStyle view failed The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2888. The arguments are: TextStyle, , DEBUG: Error 2867: The error dialog property is not set The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2867. The arguments are: , , 2. error message in runinitdb.log The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale English_United States. fixing permissions on existing directory C:/Program Files/PostgreSQL/8.1/data ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/global ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_xlog ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_xlog/archive_status ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_clog ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_subtrans ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_twophase ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_multixact/members ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_multixact/offsets ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/base ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/base/1 ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in C:/Program Files/PostgreSQL/8.1/data/base/1 ... initdb: could not execute command ""D:/PostgreSQL/8.1/bin/postgres.exe" -boot -x1 -F template1": Invalid argument initdb: removing contents of data directory "D:/PostgreSQL/8.1/data" ---(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] right sibling is not next child
I'm reporting this as a PostgreSQL bug because it involves an index corruption. I can't see any other way our application should be able to corrupt an index. I will attach the tail of the log when the corruption was detected (and the postmaster shut itself down), as well as the subsequent attempt to start. Fortunately we run our web site off of a farm of four database servers, so we are taking one of the others out of the mix, stopping postmaster, and copying its data directory over to this machine for recovery, so we don't need advice on that aspect of things; but, we'd like to do what we can to help track down the cause to prevent a recurrence. We have renamed the data directory to make room for recovery at the normal location, but otherwise the failing data directory structure is unmodified. For context, this is running on Windows 2003 Server. Eight Xeon box, no HT, 6 GB RAM, 13 drive 15,000 RPM RAID5 array through battery backed controller for everything. This database is about 180 GB with about 300 tables. We have autovacuum running every ten seconds because of a few very small tables with very high update rates, and we have a scheduled VACUUM ANALYZE VERBOSE every night. It appears that last night's vacuum found the problem, which the previous night's vacuum didn't. We had some event which started at 14:25 yesterday which persisted until we restarted the middle tier at 15:04. The symptom was that a fraction of the queries which normally run in a few ms were timing out on a 20 second limit. pg_locks showed no blocking. We've been getting episodes with these symptoms occassionally, but they have only lasted a minute or two; this duration was unusual. We haven't identified a cause. One odd thing is that with the number of queries per second that we run, the number of timeouts during an episode is too small to support the notion that _all_ similar queries are failing. How best to proceed? -Kevin postgresql-2006-04-06_00-tail.log Description: Binary data postgresql-2006-04-06_072250.log Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2376: permission roles not respected
The following bug has been logged online: Bug reference: 2376 Logged by: John Sweeney Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: fedora 3 Description:permission roles not respected Details: If a user logs on using md5 authentication, being the member of a role does not give the user permissions to see a table. This is VERY VERY VERY frustrating! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2377: pg_constraint didnt't updated when table columns deleted
The following bug has been logged online:
Bug reference: 2377
Logged by: Pavel Golub
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system: Windows XP
Description:pg_constraint didnt't updated when table columns deleted
Details:
To illustrate the bug I'll use such schema:
CREATE TABLE test."Cars"
(
"CarID" SERIAL,
"Model" varchar,
"OrderID" int4 NOT NULL,
PRIMARY KEY ("CarID")
)
WITHOUT OIDS;
CREATE TABLE test."Orders"
(
"OrderID" SERIAL,
"OrderTime" timestamp,
"CarID" int4 DEFAULT 0,
FOREIGN KEY ("CarID")
REFERENCES test."Cars" ("CarID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
Then to fetch information about foreign keys of table test."Orders" I'll use
such query:
SELECT ncon.nspname AS constraint_schema,
c.oid as constraint_table_oid,
c.relname AS constraint_table,
con.conname AS constraint_name,
con.conkey, --this is the column we're watching for
refn.nspname as ref_schema,
refc.oid as ref_table_oid,
refc.relname as ref_table,
con.confkey,
con.confmatchtype AS match_option,
con.confupdtype AS update_rule,
con.confdeltype AS delete_rule,
con.condeferrable,
con.condeferred
FROM pg_namespace ncon
JOIN pg_constraint con ON ncon.oid = con.connamespace
JOIN pg_class c ON con.conrelid = c.oid
JOIN pg_class refc ON con.confrelid = refc.oid
JOIN pg_namespace refn ON refn.oid = refc.relnamespace
WHERE c.relkind = 'r'::"char"
AND con.contype = 'f'::"char"
AND c.oid = 60464 ; --this is test."Orders" OID
This is the returned data:
"test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
";"a";"a";f;f
For now all correct. conkey equal 3.
Then we drop "OrderTime" column:
ALTER TABLE test."Orders" DROP COLUMN "OrderTime";
Then execute the same query and get the result:
"test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
";"a";"a";f;f
As you can see pg_constraint.conkey column didn't updated.
I didn't check this behavior with other kind of constraints, only on FOREIGN
KEYs.
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
Re: [BUGS] BUG #2377: pg_constraint didnt't updated when table
On Wed, 5 Apr 2006, Pavel Golub wrote:
> The following bug has been logged online:
>
> Bug reference: 2377
> Logged by: Pavel Golub
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.1.0
> Operating system: Windows XP
> Description:pg_constraint didnt't updated when table columns deleted
> Details:
>
> To illustrate the bug I'll use such schema:
>
> CREATE TABLE test."Cars"
> (
> "CarID" SERIAL,
> "Model" varchar,
> "OrderID" int4 NOT NULL,
> PRIMARY KEY ("CarID")
> )
> WITHOUT OIDS;
>
> CREATE TABLE test."Orders"
> (
> "OrderID" SERIAL,
> "OrderTime" timestamp,
> "CarID" int4 DEFAULT 0,
> FOREIGN KEY ("CarID")
> REFERENCES test."Cars" ("CarID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> );
>
> Then to fetch information about foreign keys of table test."Orders" I'll use
> such query:
>
> SELECT ncon.nspname AS constraint_schema,
> c.oid as constraint_table_oid,
> c.relname AS constraint_table,
> con.conname AS constraint_name,
> con.conkey, --this is the column we're watching for
> refn.nspname as ref_schema,
> refc.oid as ref_table_oid,
> refc.relname as ref_table,
> con.confkey,
> con.confmatchtype AS match_option,
> con.confupdtype AS update_rule,
> con.confdeltype AS delete_rule,
> con.condeferrable,
> con.condeferred
> FROM pg_namespace ncon
> JOIN pg_constraint con ON ncon.oid = con.connamespace
> JOIN pg_class c ON con.conrelid = c.oid
> JOIN pg_class refc ON con.confrelid = refc.oid
> JOIN pg_namespace refn ON refn.oid = refc.relnamespace
> WHERE c.relkind = 'r'::"char"
> AND con.contype = 'f'::"char"
> AND c.oid = 60464 ; --this is test."Orders" OID
>
> This is the returned data:
> "test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
> ";"a";"a";f;f
>
> For now all correct. conkey equal 3.
>
> Then we drop "OrderTime" column:
>
> ALTER TABLE test."Orders" DROP COLUMN "OrderTime";
>
> Then execute the same query and get the result:
>
> "test";60464;"Orders";"Orders_CarID_fkey";"{3}";"test";60454;"Cars";"{1}";"u
> ";"a";"a";f;f
>
> As you can see pg_constraint.conkey column didn't updated.
Don't those values reference the attnum(s) of the column(s) which don't
change after a drop column I believe.
sszabo=# select * from pg_attribute where attrelid = (select oid from
pg_class where relname='Orders');
attrelid | attname | atttypid | attstattarget | attlen | attnum |
attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign |
attnotnull | atthasdef | attisdropped | attislocal | attinhcount
--+---+--+---+++--+-+---+--++--++---+--++-
160255 | tableoid | 26 | 0 | 4 | -7 |
0 | -1 |-1 | t| p | i| t
| f | f| t | 0
160255 | cmax | 29 | 0 | 4 | -6 |
0 | -1 |-1 | t| p | i| t
| f | f| t | 0
160255 | xmax | 28 | 0 | 4 | -5 |
0 | -1 |-1 | t| p | i| t
| f | f| t | 0
160255 | cmin | 29 | 0 | 4 | -4 |
0 | -1 |-1 | t| p | i| t
| f | f| t | 0
160255 | xmin | 28 | 0 | 4 | -3 |
0 | -1 |-1 | t| p | i| t
| f | f| t | 0
160255 | ctid | 27 | 0 | 6 | -1 |
0 | -1 |-1 | f| p | s| t
| f | f| t | 0
160255 | OrderID | 23 |-1 | 4 | 1 |
0 | -1 |-1 | t| p | i| t
| t | f| t | 0
160255 | OrderTime | 1114 |-1 | 8 | 2 |
0 | -1 |-1 | f| p | d| f
| f | f| t | 0
160255 | CarID | 23 |-1 | 4 | 3 |
0 | -1 |-1 | t| p | i| f
| t | f| t | 0
(9 rows)
sszabo=#
sszabo=#
sszabo=# ALTER TABLE test."Orders" DROP COLUMN "OrderTime";
ALTER TABLE
sszabo=# select * from pg_attribute where attrelid = (select oid from
pg_class where relname='Orders');
attrelid | attname| atttypid | attstattarget |
attlen | attnum | attndims | attcacheoff | atttypmod | attbyval |
attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal
| attinhcount
--+--+-
Re: [BUGS] BUG #2379: Duplicate pkeys in table
Tom Lane wrote: > Philip suggested to me off-list that the initial error may have been the > VACUUM FULL (xid 32902872) creating duplicate moved copies of a single > valid row. That seems plausible because VACUUM FULL suppresses > duplicate-index checks, and it's real hard to see any other way that a > single transaction could have inserted all of these tuples without > triggering the btree duplicate-key check (barring a completely corrupt > index anyway). Another interesting factor -- these problems have not yet happened on any replicated DB. Slony replication works by using a trigger to store data changes in a log table; these changes are then applied on the destination DB. Slony also disables triggers on the destination. Because of the update load we also run the same vacuum regime. This seems to make it unlikely that vacuum is the sole culprit. Another difference is that the replicated DB is only ever updated. Virtually no load from applications reading data. I wonder if shared buffers in some way also interact here. ---(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 #2380: Sequence problem
On Thu, Apr 06, 2006 at 10:04:03AM +, Alex Fomin wrote: > While using the following function: > --- > nextval(sequence_name) returns currval(sequence_name) -1 > --- > while +1 is expected. It happens only sometimes, no dependency can be found. Could you provide a complete test case? That is, all SQL statements that somebody could execute in an empty database to reproduce the problem. It doesn't have to be 100% reproducible as long as it does exhibit the behavior every once in a while (an indication of how often or under what circumstances, if known, would be helpful). Is it possible that another session is altering the sequence to start with a lower value? Have you perchance set the sequence's CACHE setting to a value other than 1 (one)? What's the output of "SELECT * FROM sequence_name"? Are you making queries from more than one session? Are you using connection pooling? -- Michael Fuhr ---(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 #2376: permission roles not respected
"John Sweeney" <[EMAIL PROTECTED]> writes: > If a user logs on using md5 authentication, being the member of a role does > not give the user permissions to see a table. This is VERY VERY VERY > frustrating! You really ought to provide some details ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
