Обсуждение: Trigger/Query Warnings
Hi,
I've been staring at this for hours and was hoping somebody could
point me in the right direction.
I have a trigger setup on a table to update some values based on the
values being inserted/updated and keep getting warning messages in the
logs, even tho this query has the desired effect and the values are
updated in the database:
2011-09-20 15:20:50 BST WARNING: here, 'email':3B
'jake@stride.me.uk':2B 'test':1A
2011-09-20 15:20:50 BST CONTEXT: SQL statement "UPDATE resource_field_values
SET
boolean_value=false,
updated=now(),
updated_by='221ee00f-df61-4095-a380-896b9947f551'
WHERE
boolean_value=true AND
resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
key!=NEW.key AND
resource_key IN
(
SELECT DISTINCT r.key
FROM
resource_field_values e,
resource_field_values t,
resources r
WHERE
r.key=e.resource_key AND
r.key=t.resource_key AND
r.subsequent_version_key IS NULL AND
r.deleted=false AND
e.resource_key=t.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f'
--AND
--lower(trim(e.varchar_value)) = lower(trim(NEW.varchar_value))
)"
PL/pgSQL function "process_newsletter_email_address" line 5 at SQL statement
I'm confused as line 5 is surely updating the uuid value for
updated_by. Any help/pointers would be much appreciated and I've
included the trigger that calls this is:
CREATE TRIGGER process_newsletter_email_uniqueness
BEFORE INSERT OR UPDATE ON
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".resource_field_values
FOR EACH ROW
WHEN (
NEW.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
NEW.boolean_value = true
)
EXECUTE PROCEDURE
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address();
And the function looks like:
CREATE OR REPLACE FUNCTION
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (TG_OP = 'UPDATE' OR 'TG_OP' = 'INSERT')
THEN
UPDATE resource_field_values
SET
boolean_value=false,
updated=now(),
updated_by='221ee00f-df61-4095-a380-896b9947f551'
WHERE
boolean_value=true AND
resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
--resource_key!=NEW.resource_key AND
resource_key IN
(
SELECT r.key
FROM
resource_field_values e,
resource_field_values t,
resources r,
(
-- This gets the email of the value we are updating
SELECT e.varchar_value
FROM
resource_field_values e,
resource_field_values t,
resource_field_values n
WHERE
e.subsequent_version_key IS NULL AND
t.subsequent_version_key IS NULL AND
n.subsequent_version_key IS NULL AND
e.resource_key=t.resource_key AND
e.resource_key=n.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd'
AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91'
AND
n.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2'
AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND
n.key=NEW.key
) n
WHERE
r.key=e.resource_key AND
r.key=t.resource_key AND
e.subsequent_version_key IS NULL AND
t.subsequent_version_key IS NULL AND
r.subsequent_version_key IS NULL AND
r.deleted=false AND
e.resource_key=t.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND
lower(trim(e.varchar_value)) = lower(trim(n.varchar_value))
) ,;
END IF;
RETURN NEW;
END;
$$;
--
Jake Stride
Find out more http://about.me/jakestride or follow me on twitter @jake.
On Oct 8, 2011, at 21:45, Jake Stride <jake@stride.me.uk> wrote: > Hi, > > I've been staring at this for hours and was hoping somebody could > point me in the right direction. > > I have a trigger setup on a table to update some values based on the > values being inserted/updated and keep getting warning messages in the > logs, even tho this query has the desired effect and the values are > updated in the database: > > 2011-09-20 15:20:50 BST WARNING: here, 'email':3B > 'jake@stride.me.uk':2B 'test':1A > 2011-09-20 15:20:50 BST CONTEXT: SQL statement "UPDATE resource_field_values > SET > boolean_value=false, > updated=now(), > updated_by='221ee00f-df61-4095-a380-896b9947f551' > WHERE > boolean_value=true AND > resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND > key!=NEW.key AND > resource_key IN > ( > SELECT DISTINCT r.key > FROM > resource_field_values e, > resource_field_values t, > resources r > WHERE > r.key=e.resource_key AND > r.key=t.resource_key AND > r.subsequent_version_key IS NULL AND > r.deleted=false AND > e.resource_key=t.resource_key AND > e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND > t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND > t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' > --AND > --lower(trim(e.varchar_value)) = lower(trim(NEW.varchar_value)) > )" > PL/pgSQL function "process_newsletter_email_address" line 5 at SQL statement > > I'm confused as line 5 is surely updating the uuid value for > updated_by. Any help/pointers would be much appreciated and I've > included the trigger that calls this is: > > CREATE TRIGGER process_newsletter_email_uniqueness > BEFORE INSERT OR UPDATE ON > "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".resource_field_values > FOR EACH ROW > WHEN ( > NEW.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND > NEW.boolean_value = true > ) > EXECUTE PROCEDURE > "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address(); > > And the function looks like: > > CREATE OR REPLACE FUNCTION > "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address() > RETURNS trigger > LANGUAGE plpgsql > AS $$ > BEGIN > IF (TG_OP = 'UPDATE' OR 'TG_OP' = 'INSERT') > THEN > UPDATE resource_field_values > SET > boolean_value=false, > updated=now(), > updated_by='221ee00f-df61-4095-a380-896b9947f551' > WHERE > boolean_value=true AND > resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND > --resource_key!=NEW.resource_key AND > resource_key IN > ( > SELECT r.key > FROM > resource_field_values e, > resource_field_values t, > resources r, > ( > -- This gets the email of the value we are updating > SELECT e.varchar_value > FROM > resource_field_values e, > resource_field_values t, > resource_field_values n > WHERE > e.subsequent_version_key IS NULL AND > t.subsequent_version_key IS NULL AND > n.subsequent_version_key IS NULL AND > e.resource_key=t.resource_key AND > e.resource_key=n.resource_key AND > e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' > AND > t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' > AND > n.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' > AND > t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND > n.key=NEW.key > ) n > WHERE > r.key=e.resource_key AND > r.key=t.resource_key AND > e.subsequent_version_key IS NULL AND > t.subsequent_version_key IS NULL AND > r.subsequent_version_key IS NULL AND > r.deleted=false AND > e.resource_key=t.resource_key AND > e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND > t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND > t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND > lower(trim(e.varchar_value)) = lower(trim(n.varchar_value)) > ) ,; > END IF; > RETURN NEW; > END; > $$; > > -- > Jake Stride > > Find out more http://about.me/jakestride or follow me on twitter @jake. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general The warning looks as if it is coming from a "RAISE WARNING" statement...which the listed function does not have. It lookslike debugging code from the "test" value. You might want to look for mis-schemaed/duplicate functions that might be called instead of the one you listed here.
Hi David, Thanks, that's exactly what it was. Jake On 9 October 2011 10:05, David Johnston <polobo@yahoo.com> wrote: > On Oct 8, 2011, at 21:45, Jake Stride <jake@stride.me.uk> wrote: > >> Hi, >> >> I've been staring at this for hours and was hoping somebody could >> point me in the right direction. >> >> I have a trigger setup on a table to update some values based on the >> values being inserted/updated and keep getting warning messages in the >> logs, even tho this query has the desired effect and the values are >> updated in the database: >> >> 2011-09-20 15:20:50 BST WARNING: here, 'email':3B >> 'jake@stride.me.uk':2B 'test':1A >> 2011-09-20 15:20:50 BST CONTEXT: SQL statement "UPDATE resource_field_values >> SET >> boolean_value=false, >> updated=now(), >> updated_by='221ee00f-df61-4095-a380-896b9947f551' >> WHERE >> boolean_value=true AND >> resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND >> key!=NEW.key AND >> resource_key IN >> ( >> SELECT DISTINCT r.key >> FROM >> resource_field_values e, >> resource_field_values t, >> resources r >> WHERE >> r.key=e.resource_key AND >> r.key=t.resource_key AND >> r.subsequent_version_key IS NULL AND >> r.deleted=false AND >> e.resource_key=t.resource_key AND >> e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND >> t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND >> t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' >> --AND >> --lower(trim(e.varchar_value)) = lower(trim(NEW.varchar_value)) >> )" >> PL/pgSQL function "process_newsletter_email_address" line 5 at SQL statement >> >> I'm confused as line 5 is surely updating the uuid value for >> updated_by. Any help/pointers would be much appreciated and I've >> included the trigger that calls this is: >> >> CREATE TRIGGER process_newsletter_email_uniqueness >> BEFORE INSERT OR UPDATE ON >> "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".resource_field_values >> FOR EACH ROW >> WHEN ( >> NEW.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND >> NEW.boolean_value = true >> ) >> EXECUTE PROCEDURE >> "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address(); >> >> And the function looks like: >> >> CREATE OR REPLACE FUNCTION >> "e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address() >> RETURNS trigger >> LANGUAGE plpgsql >> AS $$ >> BEGIN >> IF (TG_OP = 'UPDATE' OR 'TG_OP' = 'INSERT') >> THEN >> UPDATE resource_field_values >> SET >> boolean_value=false, >> updated=now(), >> updated_by='221ee00f-df61-4095-a380-896b9947f551' >> WHERE >> boolean_value=true AND >> resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND >> --resource_key!=NEW.resource_key AND >> resource_key IN >> ( >> SELECT r.key >> FROM >> resource_field_values e, >> resource_field_values t, >> resources r, >> ( >> -- This gets the email of the value we are updating >> SELECT e.varchar_value >> FROM >> resource_field_values e, >> resource_field_values t, >> resource_field_values n >> WHERE >> e.subsequent_version_key IS NULL AND >> t.subsequent_version_key IS NULL AND >> n.subsequent_version_key IS NULL AND >> e.resource_key=t.resource_key AND >> e.resource_key=n.resource_key AND >> e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' >> AND >> t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' >> AND >> n.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' >> AND >> t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND >> n.key=NEW.key >> ) n >> WHERE >> r.key=e.resource_key AND >> r.key=t.resource_key AND >> e.subsequent_version_key IS NULL AND >> t.subsequent_version_key IS NULL AND >> r.subsequent_version_key IS NULL AND >> r.deleted=false AND >> e.resource_key=t.resource_key AND >> e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND >> t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND >> t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND >> lower(trim(e.varchar_value)) = lower(trim(n.varchar_value)) >> ) ,; >> END IF; >> RETURN NEW; >> END; >> $$; >> >> -- >> Jake Stride >> >> Find out more http://about.me/jakestride or follow me on twitter @jake. >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > The warning looks as if it is coming from a "RAISE WARNING" statement...which the listed function does not have. It lookslike debugging code from the "test" value. > > You might want to look for mis-schemaed/duplicate functions that might be called instead of the one you listed here. -- Jake Stride Find out more http://about.me/jakestride or follow me on twitter @jake.