From: | Robert Lakes <robertl(at)propaas(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Harry Ambrose <harry(dot)ambrose(at)gmail(dot)com>, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100 |
Date: | 2017-06-09 16:34:29 |
Message-ID: | CAHnqRj3Eo_M2cKnc-7TorV4ASgSN0GvKCt6gOJ3W=e-oxxE=uA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ha guys,
I am new to postgress and I am trying to write my first function to insert,
update or delete and trap errors as a result of the table not existing ,
the columns not exist or if any other error simply pass back the sqlstate
here's my code can you help
CREATE OR REPLACE FUNCTION listings_audit() RETURNS TRIGGER AS
$listings_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
IF (EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
)) THEN
INSERT INTO listings_changes
SELECT now(), 'DELETE', OLD.*;
RETURN OLD;
ELSE RAISE EXCEPTION 'Table does not exists';
END IF;
ELSIF (TG_OP = 'UPDATE') THEN
IF (EXISTS (
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
)) THEN
INSERT INTO listings_changes
SELECT now(), 'UPDATE', NEW.*;
RETURN NEW;
ELSE RAISE EXCEPTION 'Table does not exists';
END IF;
ELSEIF (TG_OP = 'INSERT') THEN
INSERT INTO listings_changes
SELECT now(), 'INSERT', NEW.*;
RETURN NEW;
END IF;
EXCEPTION
WHEN SQLSTATE '42611' THEN
RAISE EXCEPTION 'Columns do not match audit file does not match user
file';
WHEN SQLSTATE '42P16' THEN
RAISE EXCEPTION 'Table does not exists';
WHEN OTHERS THEN
RAISE EXCEPTION 'PostgresSQL error code that has occurred';
RETURN SQLSTATE;
END;
$listings_audit$ LANGUAGE plpgsql;
On Thu, Jun 8, 2017 at 12:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Harry Ambrose <harry(dot)ambrose(at)gmail(dot)com> writes:
> > Please find the jar attached (renamed with a .txt extension as I know
> some
> > email services deem jars a security issue).
>
> Hmm, the output from this script reminds me quite a lot of one I was
> sent in connection with bug #14444 awhile back:
> https://www.postgresql.org/message-id/20161201165505.
> 4360.28203%40wrigleys.postgresql.org
> Was that a colleague of yours?
>
> Anyway, the bad news is I couldn't reproduce the problem then and I can't
> now. I don't know if it's a timing issue or if there's something critical
> about configuration that I'm not duplicating. Can you explain what sort
> of platform you're testing on, and what nondefault configuration settings
> you're using?
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2017-06-09 16:45:04 | Re: pg_upgrade --link on Windows |
Previous Message | Adrian Klaver | 2017-06-09 16:23:50 | Re: Vacuum and state_change |