[SQL] HELP pg_proc is corrupted!
OK, I before any starts on my about backups...I have one a week old, but I want to fix this the right way! (and I want this past week's worth of data. What happened: o downloaded ip and macaddr type programs written by Paul Vixie et al., scrapped the ip stuff, and compiled the macaddr stuff. Ran the included sql script to load the "mac.so" file, create the custom macaddr type in and out functions, etc. At this point everything is good. o downloaded latest copy of IEEE MAC Address to Vendor list. Updated the mac.h file and recompiled the module. o Dropped all the functions and the types. o Re-ran the SQL script to create the handler, type, functions, etc after putting the new mac.so module in the proper path. o All hell breaks loose: o use functions are missing from pgaccess o pg_dump dies with invalid OID number(s) o multiple instances of macaddr definitions in pg_proc... o Tried to fix this with a reindex, but I was told that I had to drop the DB into single user mode, I tried this but the directions don't jive with pg_ctl nor with postmaster. I can't figure how to get to single mode Questions: 1. How do I to get the DB into single user mode? 2. How can I fix this problem? TIA. tim [EMAIL PROTECTED]
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
>Timothy, Tom: > >> >1. a. Create new record with new key value in hosts table with the >> >desired value >> > b. Update the routes record to reference the new value >> > c. Delete the old record in the hosts table >> > >> >> Yes, that's what I tried. >> >> 1. foo.old.com exists in "hosts" table and "routes" table >> 2. create foo.new.com in "hosts" table >> 3. delete foo.old.com in "routes" table >> 4. add foo.new.com into "routes" table >> 5. try to delete foo.old.com and it complains! > >Tom - not to interrupt your coding :-) this sounds like a bug. Any >thoughts? No, Tom's not needed. I double checked things again today, and was able to delete the problem records today I'll blame it on gremlins. ;-) I suppose that I got lost in the data and the gremlins must have cleaned it up while I slept last night;-) Sorry to get any feathers ruffled Still, I would like an easy way to drop and recreate foreign keys and from what I see, it will appear that there is not a "safe" way to do this yet. > >> >2. a. Drop the Foriegn Key constraint >> > b. Update both the routes and hosts tables >> > c. Re-establish the foriegn key constraint >> >> This is the part that I'm fuzzy on. I've tried this before >> with complete DB corruption resulting. I had to dump each table >> one by one, edit my schema with vi, create new DB, import tables >> one by onevery painful! > >This also sounds like a problem. One should be able to drop a >constraint, the re-create the restraint and check existing records >against it. You can do this in MSSQL and Oracle. > >> PPS. As I replied to Stephan, I'm contracting at a company and I >> don't have access to e-mail. Taking a schema home is NOT OK. >> I already asked the manager if I could GPL my DNS-DB implementation. >> As you might expect, the non-technical manager, didn't know what >> GPL was, and he was NOT going to allow my work to be released to >> publicAnd of course, higher ups in company may decide that >> my solution breaks the "don't build if you can buy" policy, in which >> case all of my work is for naught! ARGH!! > >Well, if they don't use it, you can easily re-create your work at home >and GPL it. It also depends on the contract you signed ... Yes, well, gremlin infested work is not good to distribute onto the net...;-) Time will tell > > -Josh >-- >__AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 436-9166 > for law firms, small businesses fax 436-0137 >and non-profit organizations. pager 338-4078 > San Francisco
[SQL] [GENERAL] Foreign Keys Help Delete!
DB: Postgresql 7.0.2 OS: Solaris 2.6 Schema: hosts table with fqhn column routes table with fqhn foreign key hosts(fqhn) Problem: 1. When I try to change fqhn in hosts, it complains that I have now violated entry in "routes" table. 2. When I try to update "routes" table, it updates. 3. Go back to "hosts" table and now try to rename/delete old fqhn and it complains about object missing with OID=x. Questions: 1. What's the proper way to delete foreign keys? (I can dump the DB, edit it, and restore it, but that is not effecient!!) 2. How can I avoid problems such as above, besides not using foreign keys? 3. Are foreign keys broken in pg7.0.2??? TIA tim [EMAIL PROTECTED]
[SQL] What's ETA for read/write Views?
I'm working on a project and using postgres 7.0.2 with phpPgAdmin and php3.0.16. Anyhow, the more I learn about relations database systems, the more orthogonal my data becomes. However, I then need to have read/write views so that "ordinary" humans can make sense of the data While, I'm at it. I've always disliked MySQL because it wasn't GPL'd nor was it a real RDBMS. Anyhow, from what I can tell, they went GPL because that was the only way to move forward due to their BDB kludge.Just my 2 cents TIA. tim [EMAIL PROTECTED]
