ERROR: function lag(numeric, bigint) does not exist (SQLSTATE 42883)

Products

• VMware Tanzu Greenplum

Issue/Introduction

Customer is upgrading from 4.x to 6.x using the backup and restore method but when they try to restore a previous backup from 4.x, it fails with the following error:

ERROR: function lag(numeric, bigint) does not exist (SQLSTATE 42883)

It fails while trying to restore a view that uses the lag() function.

Test from 4.x:

gpevlu=# CREATE VIEW  vw_lag_example

gpevlu-# AS

gpevlu-# SELECT s.a, lag(s.a, 2) OVER(

gpevlu(#   ORDER BY s.a) AS lag2

gpevlu-#    FROM generate_series(1, 10) s(a);

CREATE VIEW

Time: 971.053 ms

gpevlu=# \d+ vw_lag_example

             View "public.vw_lag_example"

Column |  Type   | Modifiers | Storage | Description

--------+---------+-----------+---------+-------------

a      | integer |           | plain   |

lag2   | integer |           | plain   |

View definition:

SELECT s.a, lag(s.a, 2::bigint) OVER(

  ORDER BY s.a) AS lag2

   FROM generate_series(1, 10) s(a)

From the definition above, this casting to bigint is not supported in 6.x and hence the restore will fail.

Environment

Product Version: 6.0

Resolution

This is documented in the migration guide below. gprestore will not restore this object.

 https://docs.vmware.com/en/VMware-Greenplum/5/greenplum-database/utility_guide-admin_utilities-gprestore.html 

When preparing Greenplum 4.3 databases and  Greenplum 5 databases for backup, note that the offset argument of the lag(expr, offset[, default]) window function changed from int8 in Greenplum 4.3 to int4 in Greenplum 5 and 6.

As a workaround, drop the views, do the backup & restore, and recreate these views by dropping the ::bigint.

Using the example above, you can recreate the view using this command on 6.x:

CREATE VIEW public.vw_lag_example AS SELECT s.a, lag(s.a, 2) OVER( ORDER BY s.a) AS lag2 FROM generate_series(1, 10) s(a);