Re: Trigger/Query Warnings

On Oct 8, 2011, at 21:45, Jake Stride <jake(at)stride(dot)me(dot)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(at)stride(dot)me(dot)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(at)postgresql(dot)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 looks like 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.