Re: [SQL] Relation 0 does not exist
Alvaro Thak you for your quick reply. > Hmm. Who knows which one of the data-corrupting unfixed > known bugs in this ancient version caused this particular Is this the 'unfixed know bugs' of 7.2.X ? It means the solution is only upgrading PotgreSQL to 7.3.X ot 7.4.X ? One more information. I have rebooted the system after this trouble happens, I was able to access database normally and error log messsage never generated. But I have not tried to run SQL command 'INSERT INTO SDBA2 SELECT * FROM VHIST2'; since this is the production environment our clients are using now... > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Alvaro Herrera > Sent: Sunday, February 26, 2006 12:24 AM > To: Yasuhiro Furuse > Cc: [email protected] > Subject: Re: [SQL] Relation 0 does not exist > > Yasuhiro Furuse wrote: > > > When I run SQL command, I've got the error messe 'Relation > 0 doesn't exist' > > and following log messages were generated. > > After this error happens, I can not access database at all. > > > > Could you kindly advise why this kind of error happens? > > > > Regards, > > y.furuse > > > > > -- > > -- > > --- > > [Version] > > PosrgreSQL7.2.4 > > Hmm. Who knows which one of the data-corrupting unfixed > known bugs in this ancient version caused this particular > problem. I doubt you will find much people trying to figure > it out; instead, you should be looking into doing an upgrade > rather soonish. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---(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 6: explain analyze is your friend
[SQL] Disabling triggers/constraints pg<8.1
Folks, Questions about disabling/enabling triggers/constraints "through the back door" in pg versions that do not support DISABLE|ENABLE of such things? Background: pg 8.1 has the ability to DISABLE/ENABLE a particular trigger, and it looks like we'll be getting DISABLE/ENABLE CONSTRAINT statements at some stage. Great!! Toggling things on/off is MUCH better than dropping/recreating them. However, I note that pgdump from earlier pg releases includes code that does coarse-grained disabling of triggers (all on a table, not just a nominated trigger): UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = '"myschema"."mytable"'::pg_catalog.regclass; UPDATE pg_catalog.pg_class SET reltriggers = ( SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid ) WHERE oid = '"myschema"."mytable"'::pg_catalog.regclass; Note: These are from dumps created using pgAdmin3 Questions: 1. Is it possible to disable only a single trigger using similar SQL, without confusing postgres during inserts/updates/deletes if there are other triggers active against that table? (I've had problems trying to drop tables when pg_class.reltrigger doesn't match the count in pg_trigger - such as when a restore from ASCII dumps are interrupted halfway through a COPY). 2. Is there a similar SQL query that effectively disables constraints (even if this does not include those involving indices such as primary or unique constraints)? 3. How "evil" are such queries? Should they be avoided wherever possible? What other "gotchas" should I watch out for? Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Dump/restore comments only?
Folks, There have been a number of times when I've wanted to ignore everything in a dump file apart from comments. I am interested not just in comments for tables/views/columns but constraints, indices and functions as well. Many of my comments are multi-line and often resemble manpages, so a simple grep for '^COMMENT ON' will not work for me. Has anyone got a script/view in SQL that will generate fully-qualified COMMENT ON statements from the pg_catalog/information_schema, a sed|awk|perl script to munge the ascii file produced by pg_dump, or even pg_dump undocumented options that might help me? (If there are no such options for dump/restore, might these be useful inclusions in the future?) Thanks in advance. -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem with query on history table
Hi all! I don't know if there's a standard solution to the kind of problem I'm trying to solve, but I will appreciate your thougts(and maybe solution:) on this problem of mine: I have 2 tables: hist and curr which hold numbers for "history-data" and "current-data" respectivly. Here is a simplified version of the schema: CREATE TABLE curr ( id integer NOT NULL, etc integer NOT NULL, created timestamp without time zone NOT NULL, modified timestamp without time zone ); CREATE TABLE hist ( id serial NOT NULL, curr_id integer NOT NULL REFERENCES curr(id), etc integer NOT NULL, modified timestamp without time zone NOT NULL ); andreak=# SELECT * from curr; id | etc | created | modified +-+-+- 1 | 5 | 2006-02-01 00:00:00 | 2 | 10 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 3 | 10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 (3 rows) andreak=# SELECT * from hist; id | curr_id | etc | modified ++-+- 1 | 3 | 30 | 2006-01-16 00:00:00 2 | 3 | 20 | 2006-01-25 00:00:00 3 | 2 | 20 | 2006-01-26 00:00:00 (3 rows) Now - I would like to get a report on what the "ETC" is on a given entry in "curr" in a given "point in time". Let me explain. If I want status for 17. jan.(17.01.2006) I would like to get these numbers out from the query: id | created |curr_modified|hist_modified| etc +-+-+-+- 3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-16 00:00:00 | 30 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 | 20 1 | 2006-02-01 00:00:00 | | | 5 That is; If the entry is modified after it's created, a snapshot of the "old version" is copied to table "hist" with the hist.modified field set to the "modified-timestamp". So there will exist several entries in "hist" for each time an entry in "curr" is modified. If I want status for the 27. jan. I would like the query to return the following rows: id | created |curr_modified|hist_modified| etc +-+-+-+- 3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-25 00:00:00 | 10 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 | 10 1 | 2006-02-01 00:00:00 | | | 5 select curr.id, curr.created, curr.modified as curr_modified, hist.modified as hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN hist ON(curr.id = hist.curr_id) WHERE ... I'm really stuck here. It seems to me that I need a lot of CASE...WHEN...ELSE.. statements in the query, but is there an easier way? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
