Search Postgresql Archives

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Harry Ambrose <harry.ambrose@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux