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

Erwin Brandstetter · Accepted Answer · 2024-05-26 02:33:27Z

3

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:

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 time zones for Postgres timestamps

edited May 26, 2024 at 2:33

answered Dec 12, 2017 at 2:14

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bf39L Over a year ago

Thanks for your answer. I cannot upgrade db because infrastructure change will have too many side effects. And I am pretty sure the variable name does not conflict with table column and state_code is one of the column in device_syncfiles table. I tried to isolated run this update query and its works so just not working when this query is in a function.

bf39L Over a year ago

An update: I create a void function just contains this update query says A() then I call: perform A() in the original function. Then all functions works normally. So think this is a postgresql bug in 8.4? Thanks

Erwin Brandstetter Over a year ago

@Lester: If you take this seriously, edit your question to provide the missing information.

Erwin Brandstetter Over a year ago

@Lester: All fields declared in RETURNS TABLE are OUT parameters visible in all SQL queries in the function body. It's exactly like I guessed. I clarified some more above.

bf39L Over a year ago

awsome! Problem solved! Thank you so much! You are a legend!

Collectives™ on Stack Overflow

Postgresql 8.4 update query syntax error in plpgsql function

1 Answer 1

0.

1.

2.

3.

4.

5.

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

0.

1.

2.

3.

4.

5.

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related