Trigger/Query Warnings

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 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.

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 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.

--
Jake Stride

Find out more http://about.me/jakestride or follow me on twitter @jake.