Re: [SQL] Another postgres 'file not found' error
> Not if you don't know how to run PostgreSQL or your machines, no. > Migrating to a new platform without sufficient testing or expertise > is always unwise (sorry, but you deserved that for the dig at > Postgres). Heh. Well it's most certainly the case I'm still learning about Postgres (aren't we all?), but I did run it for a few months on unimportant stuff and to be honest, this application I'm having trouble with at the moment is not real important even though it's production. The database is being continuously populated and drained by different applications (its a spool area mainly), so activity is high even though volume is not. > Either: > 1. You have a bad PostgreSQL install. What OS are you using? Debian Woody with 2.4.18 Linux kernel. Postgres install from apt-get (7.2.1). > 2. You are out of disk space on the PostgreSQL partition. Approx 15gig free. > 3. You are having hardware errors, such as a corrupt HDD. No other untoward signs. > 4. Your OS is having disk or file management errors. No other untoward signs. > Regardless, you'd better shutdown Postgres and defer all work on the > database until you've diagnosed your hardware/configuration problem. I > hope you have a previous backup, as you may find that you need to > revert to an earlier backup to restore your data. In this instance, simply dropping the index and recreating it solved the problem. I'll continue using this configuration whilst trying to find the problem, however I'll defer any future migrations though until I've got to the root of it. -- Ian Cass ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Quickest way to insert unique records?
Hi, I've got a number of files containing generic log data & some of the lines may or may not be duplicated across files that I'm feeding into a database using Perl DBI. I'm just ignoring any duplicate record errors. This is fine for day to day running when the data feeds in at a sensible rate, however, if I wanted to feed in a load of old data in a short space of time, this solution simply is not quick enough. I can modify the feeder script to generate formated CSV files that I can then COPY into the database into a temporary table. However, I'll then need to select each record from the temporary table and insert into the main table, omitting duplicates. I guess I'd need something like this INSERT INTO messages (host, messageid, body, and, loads, more) SELECT host, messageid, body, and, loads, more FROM messages_tmp ; However, when that hit a duplicate, it would fail wouldn't it? Also, would this actually be any quicker than direct insertion from Perl DBI? -- Ian Cass ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Help with function optimisation
Hi,
I've got a function that I run as a trigger to update a summary table on
insert to the main table. As you can see below, it does a select & an INSERT
if not found, or an UPDATE if found. This currently works OK, but I'd like
to improve performance by removing the SELECT & attempting an UPDATE. If it
fails, I'd like to INSERT instead. Can this be done? If so, what's the
syntax?
CREATE FUNCTION update_summary ()
RETURNS opaque AS '
DECLARE
result TEXT;
BEGIN
IF NEW.user_name NOT LIKE ''U%'' THEN
SELECT into result originator FROM summary
WHERE date = date_trunc(''hour'', NEW.logtime)
AND client_id = NEW.client_id AND originator =
NEW.originator;
IF NOT FOUND THEN
INSERT INTO summary (date, client_id, originator,
status, total)
values (date_trunc(''hour'', NEW.logtime),
NEW.client_id, NEW.originator, NEW.status, ''1'');
ELSE
UPDATE summary SET total = total + 1
WHERE date = date_trunc(''hour'', NEW.logtime)
AND client_id = NEW.client_id AND originator =
NEW.originator;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
--
Ian Cass
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] FATAL 1
Couldn't find any mention of this anywhere. Anyone any idea what it means? A db stop/start seems to have cured it. postgres@judas:~$ psql master psql: FATAL 1: fixrdesc: no pg_class entry for pg_class postgres@judas:~$ psql -V psql (PostgreSQL) 7.2.1 contains support for: readline, history, multibyte Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. postgres@judas:~$ uname -a Linux judas 2.4.18 #1 Fri May 3 11:45:59 UTC 2002 i686 unknown -- Ian Cass ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] FATAL 1
>From /var/log/syslog Jul 22 13:27:28 judas postgres[31058]: [10] DEBUG: connection: host=192.168.6.4 user=postgres database=master Jul 22 14:08:30 judas postgres[31183]: [10] DEBUG: connection: host=192.168.6.4 user=postgres database=master Jul 22 14:08:30 judas postgres[31183]: [11] FATAL 1: fixrdesc: no pg_class entry for pg_class (repeat until I restart pg) nothing in /var/log/postgres.log -- Ian Cass - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Ian Cass" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, July 24, 2002 12:44 AM Subject: Re: [SQL] FATAL 1 > > It means something is really wacked out about your installation. What > does the server log show? > > ------ - > > Ian Cass wrote: > > Couldn't find any mention of this anywhere. Anyone any idea what it means? A > > db stop/start seems to have cured it. > > > > postgres@judas:~$ psql master > > psql: FATAL 1: fixrdesc: no pg_class entry for pg_class > > > > postgres@judas:~$ psql -V > > psql (PostgreSQL) 7.2.1 > > contains support for: readline, history, multibyte > > Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group > > Portions Copyright (c) 1996, Regents of the University of California > > Read the file COPYRIGHT or use the command \copyright to see the > > usage and distribution terms. > > > > postgres@judas:~$ uname -a > > Linux judas 2.4.18 #1 Fri May 3 11:45:59 UTC 2002 i686 unknown > > > > -- > > Ian Cass > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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
