Postgresql 8.4 update query syntax error in plpgsql function

Question

I am using PostgreSQL 8.4 and creating a plpgsql function. In the body of this function I have a query to update records.

...
  UPDATE device_syncfiles SET 
  state_code = 1, updated_at = NOW() at time zone 'UTC'
  WHERE 
  ((state_code = 2 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > 3600) OR 
  (state_code = 3 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > 3600));
...

When I load this function into database, a syntax error turns out

ERROR:  syntax error at or near "$1"
LINE 1: UPDATE device_syncfiles SET  $1  = 1,  $2  = NOW() at time z...
                                     ^
QUERY:  UPDATE device_syncfiles SET  $1  = 1,  $2  = NOW() at time zone 'UTC' WHERE (( $1  = 2 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' -  $2 ::timestamp without time zone)) >  $3 ) OR ( $1  = 3 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' -  $2 ::timestamp without time zone)) >  $4 ))
CONTEXT:  SQL statement in PL/PgSQL function "syncfile_get" near line 19

I cannot find any problem with this query. What's wrong here?

UPDATE: (missing information)

Table: device_syncfiles

  id PK integer auto inc
  user_id integer FK
  file_name character varying(255) NOT NULL,
  state_code integer NOT NULL FK,
  md5 character varying(255) NOT NULL,
  msg character varying(255),
  created_at timestamp without time zone,
  updated_at timestamp without time zone

Function: syncfile_get()

CREATE OR REPLACE FUNCTION syncfile_get()
  RETURNS TABLE(id integer, user_id integer, file_name character varying, state_code integer, md5 character varying, created_at timestamp without time zone, updated_at timestamp without time zone) AS
$BODY$
      DECLARE
        _device_syncfile_id integer;
        _download_timeout integer;
        _processing_timeout integer;
      BEGIN
        -- GET all timeout info
        SELECT state_timeout INTO _download_timeout FROM device_syncfile_states 
        WHERE state_name = 'downloading';
        SELECT state_timeout INTO _processing_timeout FROM device_syncfile_states 
        WHERE state_name = 'processing';
        -- GET syncfile id
        _device_syncfile_id = NULL;

        -- Reset timed out file to idel state
        UPDATE device_syncfiles SET 
        state_code = 1, updated_at = NOW() at time zone 'UTC'
        WHERE 
        ((state_code = 2 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > _download_timeout) OR 
        (state_code = 3 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > _processing_timeout));

        -- GET the id of one idel/timed out file => result could be a integer or NULL
        SELECT device_syncfiles.id INTO _device_syncfile_id FROM device_syncfiles 
        WHERE 
        device_syncfiles.state_code = 1 OR 
        (device_syncfiles.state_code = 2 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - device_syncfiles.updated_at::timestamp without time zone)) > _download_timeout) OR
        (device_syncfiles.state_code = 3 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - device_syncfiles.updated_at::timestamp without time zone)) > _processing_timeout) 
        LIMIT 1;

        -- WHEN NULL skip state update and return empty set of record
        -- Otherwise return the set of record with the id found in last step
        IF _device_syncfile_id IS NOT NULL THEN
          PERFORM syncfile_update(_device_syncfile_id, 2, NULL);
        END IF;

        RETURN QUERY SELECT 
        device_syncfiles.id,
        device_syncfiles.user_id ,
        device_syncfiles.file_name ,
        device_syncfiles.state_code ,
        device_syncfiles.md5 ,
        device_syncfiles.created_at ,
        device_syncfiles.updated_at 
        FROM device_syncfiles WHERE device_syncfiles.id = _device_syncfile_id;
      END;
      $BODY$
  LANGUAGE plpgsql VOLATILE

Answer 1

Many problems.

0.

I am using Postgresql 8.4

Postgres 8.4 reached EOL in July 2014. Consider upgrading to a current version. Urgently.

1.

Your question does not disclose the complete function (at least header and footer) nor any table definition and some sample data to help us help you.

I have to make assumptions, and my educated guess is that you have a function parameter named state_code, which conflicts with the identical column name. In three places. Basics:

• Postgresql - INSERT RETURNING INTO ambiguous column reference
• How to return result of a SELECT inside a function in PostgreSQL?

You must be aware the all fields declared in a RETURNS TABLE clause are effectively OUT parameters as well. (As stated in the first sentence of the first link.) So your Q update confirmed my assumptions.

2.

Your error message reports the first of those instances here:

UPDATE device_syncfiles SET 
  state_code = 1 ...

That's a consequence of 0.. You are tripping over your long dead and forgotten version of Postgres, where the superficial syntax check at function creation time used to detect a naming conflict between target columns of UPDATE statements and function parameters. Which is silly and was later removed: those target columns cannot conflict with function parameters on principal.

Your error reproduced in Postgres 8.4: dbfiddle here

The same does not happen in Postgres 9.4: dbfiddle here

To fix, best rename the function parameter to avoid conflicts. Related:

• Postgres function returning a row as JSON value

3.

There are two more instances:

WHERE 
  ((state_code = 2 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > 3600) OR 
   (state_code = 3 AND EXTRACT(EPOCH FROM (NOW() at time zone 'UTC' - updated_at::timestamp without time zone)) > 3600));

Would need a fix in any version. Postgres cannot tell whether to resolve to the function parameter or the table column. Best table-qualify all columns to avoid any possible conflicts with parameter names a priori. (Except for UPDATE target columns, which do not need nor allow table qualification.)

4.

That's still lipstick on a pig. Improve the query like this:

UPDATE device_syncfiles d
SET    state_code = 1
     , updated_at = NOW() AT TIME ZONE 'UTC'
WHERE  d.state_code IN (2, 3)
AND    d.updated_at < (now() - interval '1 hour') AT TIME ZONE 'UTC';

Shorter, faster, can use an index on updated_at.

5.

Finally consider using timestamp with time zone instead of timestamp without time zone to begin with:

• Ignoring timezones altogether in Rails and PostgreSQL