TCL trigger doesn't work after deleting a column

[Josué Maldonado]

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.

[Josué Maldonado]

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,
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[darren]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Josué Maldonado]

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]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

=?ISO-8859-1?Q?Josu=E9_Maldonado?= <jo***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Josué Maldonado]

Hi Tom,

Tom Lane wrote:

=?ISO-8859-1?Q?Josu=E9_Maldonado?= <jo***@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,

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

=?ISO-8859-1?Q?Josu=E9_Maldonado?= <jo***@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Josué Maldonado]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera Munoz]

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"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Alvaro Herrera Munoz <al******@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
************************************************** **********/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Josué Maldonado]

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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ian Harding]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ian Harding]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jan Wieck]

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

da****@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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jan Wieck]

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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Josué Maldonado]

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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org