TCL trigger doesn't work after deleting a column

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

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

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.

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

=?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

Nulls. That's the only thing I can think of.

Josu� Maldonado wrote:

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,

=?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

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.

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"

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
************************************************************/

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.

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 #

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 #

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.