Обсуждение: TCL trigger doesn't work after deleting a column
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.
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
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,
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
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
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.
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
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
>
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 #
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 #
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.