Thread: TCL trigger doesn't work after deleting a column

TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Hello list,

The TCL trigger that uses NEW and OLD arrays failed after after I
removed a unused column, now I got this error:

pltcl: Cache lookup for attribute '........pg.dropped.24........' type 0
failed

I already did a vacuum, but the error remain. Any idea how to fix/avoid
that?

Thanks in advance

--
Josué Maldonado.


Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Hi Darren,

darren@crystalballinc.com wrote:

>From what i am seeing / thinking did you drop it from the table ???
>
> I am assuming yes and therefore you should recreate the function and the
> trigger and you should be fine.
>
> Using 7.3.x use the CREATE OR REPLACE FUNCTION to replace the function so
> it will still have the same OID.
>
> Next drop the trigger and then recreate the trigger and you should be fine

Did that, and still got the same error. I'm currently preparing the
server to update to 7.3.4 I hope this could help.

Thanks



Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Hi Ian,

Ian Harding wrote:

>
> Is the column you deleted one that you referred explicitly by name in
> your function?

No, the column I deleted does not get refered explicitly in the function.

>
> What version are you using?
>

7.3.3, and I'm planning to upgrade to 7.3.4 later today maybe that can help.

Thanks,


Re: TCL trigger doesn't work after deleting a column

From
darren@crystalballinc.com
Date:
From what i am seeing / thinking did you drop it from the table ???

I am assuming yes and therefore you should recreate the function and the
trigger and you should be fine.

Using 7.3.x use the CREATE OR REPLACE FUNCTION to replace the function so
it will still have the same OID.

Next drop the trigger and then recreate the trigger and you should be fine

HTH
Darren


On Wed, 3 Sep 2003, Josué Maldonado wrote:

> Hello list,
>
> The TCL trigger that uses NEW and OLD arrays failed after after I
> removed a unused column, now I got this error:
>
> pltcl: Cache lookup for attribute '........pg.dropped.24........' type 0
> failed
>
> I already did a vacuum, but the error remain. Any idea how to fix/avoid
> that?
>
> Thanks in advance
>
>

--
Darren Ferguson


Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Noticed that the loop does not go through all fields:

foreach id [array names NEW] {
   elog NOTICE "ID tiene $id)"
   elog NOTICE "OLD tiene $OLD($id)"
   elog NOTICE "NEW tiene $NEW($id)"
}

foreach id [array names OLD] {
   elog NOTICE "ID tiene $id)"
   elog NOTICE "OLD tiene $OLD($id)"
   elog NOTICE "NEW tiene $NEW($id)"
}

Both cases the loop skips some fields, any idea?

Thanks.



Re: TCL trigger doesn't work after deleting a column

From
darren@crystalballinc.com
Date:
Not sure exactly why but put [array names NEW] in an elog and put [array
names OLD] in an elog then make sure they are all there.

If they are all there then the foreach will definately work

Also the ID tiene $id) <--- if not in original code good otherwise should
not be there

Darren

On Wed, 3 Sep 2003, Josué Maldonado wrote:

> Noticed that the loop does not go through all fields:
>
> foreach id [array names NEW] {
>    elog NOTICE "ID tiene $id)"
>    elog NOTICE "OLD tiene $OLD($id)"
>    elog NOTICE "NEW tiene $NEW($id)"
> }
>
> foreach id [array names OLD] {
>    elog NOTICE "ID tiene $id)"
>    elog NOTICE "OLD tiene $OLD($id)"
>    elog NOTICE "NEW tiene $NEW($id)"
> }
>
> Both cases the loop skips some fields, any idea?
>
> Thanks.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Darren Ferguson


Re: TCL trigger doesn't work after deleting a column

From
Tom Lane
Date:
=?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:
> Noticed that the loop does not go through all fields:

Hard to believe.  Could you give us a complete example, not a partial
one?

            regards, tom lane

Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Hi Tom,

Tom Lane wrote:
> =?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:
>
>>Noticed that the loop does not go through all fields:
>
>
> Hard to believe.  Could you give us a complete example, not a partial
> one?

This is the code in the trigger function:

-- Function: public.audit_log()

-- DROP FUNCTION public.audit_log();

CREATE OR REPLACE FUNCTION public.audit_log()
   RETURNS trigger AS
'
  elog NOTICE "Inicio: "
if {[string match $TG_op UPDATE]} {
   foreach id [array names OLD] {
    #if { $OLD($id) != $NEW($id) } {
       elog NOTICE "ID tiene $id)"
       elog NOTICE "OLD tiene $OLD($id)"
       elog NOTICE "NEW tiene $NEW($id)"
       # tcl says $NEW(duser) does not exist
       # elog NOTICE "USER tiene $NEW(duser)"
       set lcsql "insert into audit (accion, campo, oldval, newval,
tabla, usuario ) "
       #append lcsql "values
(\'UPD\',\'$id\',\'$OLD($id)\'::text,\'$NEW($id)\'::text,\'$1\',\'$NEW(duser)\')"

    #spi_exec "$lcsql"
    #}
    }
}

if {[string match $TG_op INSERT]} {
   foreach id [array names NEW] {
     if { [info exists NEW($id)] } {
            set lcsql "insert into audit (accion, campo, newval, tabla,
usuario ) "
    append lcsql "values
(\'INS\',\'$id\',\'$NEW($id)\',\'$1\',\'$NEW(duser)\')"
    spi_exec "$lcsql"
     }
   }
}

if {[string match $TG_op DELETE]} {

   foreach id [array names OLD] {
     if { [info exists OLD($id)] } {
    set lcsql "insert into audit (accion, campo, oldval, tabla, usuario ) "
    append lcsql "values
(\'DEL\',\'$id\',\'$OLD($id)\',\'$1\',\'$OLD(duser)\')"
    spi_exec "$lcsql"
    return [array get OLD]
     }
   }
}
return [array get NEW]
' LANGUAGE 'pltcl' VOLATILE;


And this is the way a defined the trigger in my table

-- Trigger: tinv_auditor on public.tinv

-- DROP TRIGGER tinv_auditor ON public.tinv;

CREATE TRIGGER tinv_auditor
   AFTER INSERT OR UPDATE OR DELETE
   ON public.tinv
   FOR EACH ROW
   EXECUTE PROCEDURE public.audit_log('tinv');

Thanks,



Re: TCL trigger doesn't work after deleting a column

From
Tom Lane
Date:
=?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:
> Tom Lane wrote:
>> Hard to believe.  Could you give us a complete example, not a partial
>> one?

> This is the code in the trigger function:

I tried this with a table created like so:

regression=# create table tinv (
regression(# f1 int,
regression(# f2 text,
regression(# f3 float8,
regression(# duser name);
CREATE TABLE

and I get output like

regression=# update tinv set f1=55;
NOTICE:  Inicio:
NOTICE:  ID tiene f2)
NOTICE:  OLD tiene z
NOTICE:  NEW tiene z
NOTICE:  ID tiene f3)
NOTICE:  OLD tiene 3.4
NOTICE:  NEW tiene 3.4
NOTICE:  ID tiene duser)
NOTICE:  OLD tiene d
NOTICE:  NEW tiene d
NOTICE:  ID tiene f1)
NOTICE:  OLD tiene 1
NOTICE:  NEW tiene 55
UPDATE 1
regression=#

which is okay as far as I can see...

            regards, tom lane

Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Tom,

This is my table definition:

-- Table: public.tinv

-- DROP TABLE public.tinv;

CREATE TABLE public.tinv
(
   dep_codigo varchar(6) NOT NULL,
   dep_nombre varchar(35),
   dep_cantid int4,
   dep_fecadq date,
   dep_vidaut int4,
   dep_pordep int4,
   dep_valdep float4,
   dep_acumul float4,
   dep_tipo int4,
   duser char(10),
   costod numeric(12,4),
   modelo varchar(30),
   serie varchar(30),
   marca varchar(30),
   descrialt varchar(255),
   notes varchar(255),
   deprecia numeric(1),
   valoraseg numeric(12,4),
   downer char(40),
   dfoto varchar(40),
   tienda char(1) DEFAULT '1',
   store numeric(1) DEFAULT 1,
   jobdep_fk char(2),
   taction text,
   dep_valadq numeric(12,4)
) WITH OIDS;


Could you please try it?

Thanks.


Re: TCL trigger doesn't work after deleting a column

From
Alvaro Herrera Munoz
Date:
On Thu, Sep 04, 2003 at 09:01:08AM -0600, Josu? Maldonado wrote:
> Tom,
>
> This is my table definition:
[etc]

It would help if you send the complete example, including the part
where you actually drop a column from the table.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La victoria es para quien se atreve a estar solo"

Re: TCL trigger doesn't work after deleting a column

From
Tom Lane
Date:
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> It would help if you send the complete example, including the part
> where you actually drop a column from the table.

Oh, I forgot about that part (should read the Subject: line again ;-)).

I do see a failure after dropping a column.  I've applied this patch.

            regards, tom lane

*** src/pl/tcl/pltcl.c.orig    Fri Aug  8 17:47:53 2003
--- src/pl/tcl/pltcl.c    Thu Sep  4 11:06:53 2003
***************
*** 2312,2317 ****
--- 2312,2321 ----

      for (i = 0; i < tupdesc->natts; i++)
      {
+         /* ignore dropped attributes */
+         if (tupdesc->attrs[i]->attisdropped)
+             continue;
+
          /************************************************************
           * Get the attribute name
           ************************************************************/
***************
*** 2382,2387 ****
--- 2386,2395 ----

      for (i = 0; i < tupdesc->natts; i++)
      {
+         /* ignore dropped attributes */
+         if (tupdesc->attrs[i]->attisdropped)
+             continue;
+
          /************************************************************
           * Get the attribute name
           ************************************************************/

Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Alvaro,

Actually I build a new table from the old one, so the table in metion is
a new table with the same structure as before,  the way I droped the
column was right click on it from pgadmin III then delete, why should
that matter?

Thanks

Alvaro Herrera Munoz wrote:

> On Thu, Sep 04, 2003 at 09:01:08AM -0600, Josu? Maldonado wrote:
>
>>Tom,
>>
>>This is my table definition:
>
> [etc]
>
> It would help if you send the complete example, including the part
> where you actually drop a column from the table.
>


--
Josué Maldonado.


Re: TCL trigger doesn't work after deleting a column

From
Ian Harding
Date:
Nulls.  That's the only thing I can think of.

Josué Maldonado wrote:

> Noticed that the loop does not go through all fields:
>
> foreach id [array names NEW] {
>   elog NOTICE "ID tiene $id)"
>   elog NOTICE "OLD tiene $OLD($id)"
>   elog NOTICE "NEW tiene $NEW($id)"
> }
>
> foreach id [array names OLD] {
>   elog NOTICE "ID tiene $id)"
>   elog NOTICE "OLD tiene $OLD($id)"
>   elog NOTICE "NEW tiene $NEW($id)"
> }
>
> Both cases the loop skips some fields, any idea?
>
> Thanks.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings




Re: TCL trigger doesn't work after deleting a column

From
Ian Harding
Date:
It seems to work for me...
create table foobar (
        col1    varchar,
        col2    varchar)
;

create function testfunc() returns trigger language pltcl as '

foreach id [array names NEW] {
    elog NOTICE $NEW($id)
}
';

create trigger testtrig after insert or update on foobar for each row
execute procedure testfunc();

crap=# insert into foobar values ('asdf', 'asdf');
NOTICE:  asdf
NOTICE:  asdf
INSERT 191088282 1
crap=# alter table foobar drop column col1;
ALTER TABLE
crap=# insert into foobar values ('asdf');
NOTICE:  asdf
INSERT 191088394 1

Is the column you deleted one that you referred explicitly by name in
your function?

What version are you using?


Josué Maldonado wrote:

> Hello list,
>
> The TCL trigger that uses NEW and OLD arrays failed after after I
> removed a unused column, now I got this error:
>
> pltcl: Cache lookup for attribute '........pg.dropped.24........' type
> 0 failed
>
> I already did a vacuum, but the error remain. Any idea how to
> fix/avoid that?
>
> Thanks in advance
>



Re: TCL trigger doesn't work after deleting a column

From
Jan Wieck
Date:
The arrays NEW and OLD only contain entries for attributes in the
respective rows that are not NULL. The list $TG_relatts contains a list
of all column names of the triggering relation with a leading empty
element (to make the index in that list reflect pg_attribute.attnum).


Jan

darren@crystalballinc.com wrote:

> Not sure exactly why but put [array names NEW] in an elog and put [array
> names OLD] in an elog then make sure they are all there.
>
> If they are all there then the foreach will definately work
>
> Also the ID tiene $id) <--- if not in original code good otherwise should
> not be there
>
> Darren
>
> On Wed, 3 Sep 2003, Josué Maldonado wrote:
>
>> Noticed that the loop does not go through all fields:
>>
>> foreach id [array names NEW] {
>>    elog NOTICE "ID tiene $id)"
>>    elog NOTICE "OLD tiene $OLD($id)"
>>    elog NOTICE "NEW tiene $NEW($id)"
>> }
>>
>> foreach id [array names OLD] {
>>    elog NOTICE "ID tiene $id)"
>>    elog NOTICE "OLD tiene $OLD($id)"
>>    elog NOTICE "NEW tiene $NEW($id)"
>> }
>>
>> Both cases the loop skips some fields, any idea?
>>
>> Thanks.
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: TCL trigger doesn't work after deleting a column

From
Jan Wieck
Date:
close the connection and reconnect to get a fresh backend that doesn't
cache anything.


Jan

Josué Maldonado wrote:

> Hello list,
>
> The TCL trigger that uses NEW and OLD arrays failed after after I
> removed a unused column, now I got this error:
>
> pltcl: Cache lookup for attribute '........pg.dropped.24........' type 0
> failed
>
> I already did a vacuum, but the error remain. Any idea how to fix/avoid
> that?
>
> Thanks in advance
>

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: TCL trigger doesn't work after deleting a column

From
Josué Maldonado
Date:
Hi Jan,

You are right, that's why the loop doesn't goes through all the columns.

Thanks.

Jan Wieck wrote:

> The arrays NEW and OLD only contain entries for attributes in the
> respective rows that are not NULL. The list $TG_relatts contains a list
> of all column names of the triggering relation with a leading empty
> element (to make the index in that list reflect pg_attribute.attnum).


--
Josué Maldonado.