Обсуждение: Tables and functions and triggers oh my...

I'm going nuts, when I have this table/function/trigger combo my table
never updates - works great as soon as I drop the trigger. Can someone
not so green in PostgreSQL take a gander and tell me what I've done
wrong?

CREATE TABLE syslogTB (
    facility char(10),
      priority char(10),
      date date,
      time time,
      host varchar(128),
      message  text
);

create function PIXMail() returns opaque as '
DECLARE
        logRec RECORD;
        textMessage text;
BEGIN
        select into logRec date,time,message from syslogtb where message
= NEW.message;
        if logRec.message = NEW.message then
                textMessage := ''The PIX has generated the following: ''
logRec.message '''';
                perform pgmail(''PIX <some_email_addy>'',''Paul Fontenot
<some_email_addy>'',''PIX Message'', t
extMessage);
        end if;
        return NEW;
END;' language 'plpgsql';

CREATE TRIGGER trgPIXMail
        BEFORE INSERT OR UPDATE ON syslogtb FOR EACH ROW
        EXECUTE PROCEDURE pixmail();

 ***PRIVILEGED & CONFIDENTIAL***
Unless expressly stated otherwise, this message (and any attachment(s)
thereto) is confidential and may be privileged. It is intended for the
addressee(s) only. If you are not an addressee, any disclosure or
copying of the contents of this e-mail or any action taken (or not
taken) in reliance on it is strictly prohibited. If you are not an
addressee, please inform sender immediately and delete this message from
your system.

Fontenot, Paul wrote:
> I'm going nuts, when I have this table/function/trigger combo my table
> never updates - works great as soon as I drop the trigger. Can someone
> not so green in PostgreSQL take a gander and tell me what I've done
> wrong?

[...snip...]

> textMessage := ''The PIX has generated the following: ''
> logRec.message '''';

The only thing I can see wrong is the above line. You probably want
something like:

textMessage := ''The PIX has generated the following: '' || logRec.message;

or maybe:

textMessage := ''The PIX has generated the following: '''''' ||
logRec.message || '''''''';

HTH,

Joe

Hi,
i am using postgresql-7.2.3. One of the tables in my database seems to be
corrupted.
It has about 250000 records, but when dumping the table postmaster crashes
when querys exceeding row 152238.

"select * from prvlog limit 1,152238;" works fine

"select * from prvlog limit 1,152239;" results in:
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

The same happens to anything above 152239. (well, i didnt tried all...)
The first (regarding to the tabledefinition) 2 fields of row 152239 can be
viewed. Querying other rows show the above error.
There is no index on the table.  Is there something i can do to rescue the
data?

Regards
Thilo

--- Thilo Hille <thilo@resourcery.de> wrote:
> Hi,
> i am using postgresql-7.2.3. One of the tables in my
> database seems to be
> corrupted.
> It has about 250000 records, but when dumping the
> table postmaster crashes
> when querys exceeding row 152238.
>
> "select * from prvlog limit 1,152238;" works fine
>
> "select * from prvlog limit 1,152239;" results in:
> server closed the connection unexpectedly
>         This probably means the server terminated
> abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting
> reset: Succeeded.
>
> The same happens to anything above 152239. (well, i
> didnt tried all...)
> The first (regarding to the tabledefinition) 2
> fields of row 152239 can be
> viewed. Querying other rows show the above error.
> There is no index on the table.  Is there something
> i can do to rescue the
> data?

If you search the archives (or via Google) you will
find advice on recovering.  I believe there is really
no way to "recover" the corrupted data, the best you
will be able to do is delete the corrupted record(s)
(You may be able to get away with setting the affected
fields to null).

The best way to unequivocally identify the corrupt
records is by ctid.  Do "select ctid, * ..." until you
are sure that you have identified the corrupt
record(s), then delete it/them.  Then run "vacuum
full".  If that goes ok, you should be able to dump
without trouble.

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

Thilo,

> The same happens to anything above 152239. (well, i didnt tried all...)
> The first (regarding to the tabledefinition) 2 fields of row 152239 can be
> viewed. Querying other rows show the above error.
> There is no index on the table.  Is there something i can do to rescue the
> data?

First, make a copy of your files for this table ASAP, preferably to another
HDD or another machine.   If you can't figure out which files they are,
simple shut down PostgreSQL and back up the whole $PGDATA/base directory,
which might be a good idea in any case.

I've seen the symptoms you describe in 2 cases:
1) A bad sector on your hard drive;
2) systems where unexpected power-outs occur frequently.

So the next thing to do is to run some kind of exhaustive HDD scan for bad
sectors, and possibly replace the HDD.

Finally, I take it from your e-mail that you don't have a backup of the
database.   While there are several tools to help you scan & read and find
corruption in postgresql files in your /contrib source, I'm not sure that any
of them will allow you to recover your data for the affected records.

--
Josh Berkus
Aglio Database Solutions
San Francisco