[GENERAL] database corruption?
Hi, all. I'm relatively new to PostgreSQL, but I've been quite impressed with it so far. This may be due to too much experience with MySQL. :) I'm currently getting this error on my nightly vacuum. These two indices (as you may have guessed already) are on columns named interface and ewhen, on a table named error. The error table is constantly being updated. (No comments about the implications of that, please.) NOTICE: Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) NOTICE: Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) NOTICE: Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) NOTICE: Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) Is this indicative of a serious problem? If this were MySQL, I'd assume I had a corrupt table, and I'd run isamchk on it. But I don't see a similar utility for PgSQL. AFAIK, nothing really bad has happened to the host; it hasn't crashed or anything, though it was rebooted ungracefully a few weeks ago. This is on an i386 running NetBSD 1.4.1, if that makes a difference. TIA for any help. Chris -- [EMAIL PROTECTED] Chris Jones System Administrator, Right Now Technologies, Inc. "Is this going to be a stand-up programming session, sir, or another bug hunt?"
Re: [GENERAL] database corruption?
Chris Jones wrote: NOTICE: Index error_interface_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) NOTICE: Index error_ewhen_idx: NUMBER OF INDEX' TUPLES (226766) IS NOT THE SAME AS HEAP' (226765) Hope this was not already answered... I believe it means that the indices "error_interface_idx" and error_ewhen_idx" have become corrupted. The fix is to drop and rebuild them. Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Jason Vasquez wrote: I've looked through the documentation, but I've not found anything that addresses this situation. Is this possible with PostgreSQL? Or maybe there is a better way to accomplish what I want to do? Basically, I'd like to set up an ACL-like property for each record. A table could be strucured like this: Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... Hmmm. Why would you want that? Do you really mean to insert multiple records, each with an individual access_control value? At the risk of completely oversimplifying your problem, my guess is that you're looking for the notion of a foreign key in myItem, i.e., Table ACL: uniqid integer not null unique, -- or use pgsql 'serial' type... ... Table myItem: uniqid integer not null unique, ... ACL_uniqid integer not null -- a foreign key into ACL Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Ed Loehr wrote: Jason Vasquez wrote: Basically, I'd like to set up an ACL-like property for each record. A table could be strucured like this: Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... Hmmm. Why would you want that? Do you really mean to insert multiple records, each with an individual access_control value? At the risk of completely oversimplifying your problem, my guess is that you're looking for the notion of a foreign key in myItem, i.e., Table ACL: uniqid integer not null unique, -- or use pgsql 'serial' type... ... Table myItem: uniqid integer not null unique, ... ACL_uniqid integer not null -- a foreign key into ACL Well, I snooped around a bit and apparently there is a built-in type called 'acl'. So either I spoke up when I had no idea what I was talking about (and no idea that I had no idea) but you did, or you got unlucky and named a table by the same name as a pgsql built-in type. Chances are it's the former. Cheers, Ed Loehr
Re: [GENERAL] Nested tables
Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... So you will have your tables with data redundancies and not normalized. __ Get Your Private, Free Email at http://www.hotmail.com