Manageiq: PG 10.0 - PG::UndefinedFunction - pg_xlog_location_diff & pg_current_xlog_insert_location

Created on 27 Sep 2017 · 9Comments · Source: ManageIQ/manageiq

Starting with postgres 10.0, xlog was renamed to wal and all related functions got renamed as well. Also, looks like location got changed to lsn (ref).

This causes 3 more (in addition to https://github.com/ManageIQ/manageiq/issues/13200) failures...

  1) PglogicalSubscription#backlog returns the correct value
     Failure/Error: select_value("SELECT pg_xlog_location_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
       LINE 1: SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
     # ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'
     # ./app/models/pglogical_subscription.rb:105:in `backlog'
     # ./spec/models/pglogical_subscription_spec.rb:422:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
     #   LINE 1: SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'

  2) ar_dba extension #xlog_location returns a valid lsn
     Failure/Error: select_value("SELECT pg_current_xlog_insert_location()")

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_current_xlog_insert_location() does not exist
       LINE 1: SELECT pg_current_xlog_insert_location()
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_current_xlog_insert_location()
     # ./lib/extensions/ar_adapter/ar_dba.rb:15:in `xlog_location'
     # ./spec/lib/extensions/ar_dba_spec.rb:6:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_current_xlog_insert_location() does not exist
     #   LINE 1: SELECT pg_current_xlog_insert_location()
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:15:in `xlog_location'

  3) ar_dba extension #xlog_location_diff returns the correct xlog difference
     Failure/Error: select_value("SELECT pg_xlog_location_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
       LINE 1: SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
     # ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'
     # ./spec/lib/extensions/ar_dba_spec.rb:12:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
     #   LINE 1: SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'

Finished in 12 minutes 19 seconds (files took 34.74 seconds to load)
1922 examples, 3 failures, 1 pending

Failed examples:

rspec ./spec/models/pglogical_subscription_spec.rb:418 # PglogicalSubscription#backlog returns the correct value
rspec ./spec/lib/extensions/ar_dba_spec.rb:5 # ar_dba extension #xlog_location returns a valid lsn
rspec ./spec/lib/extensions/ar_dba_spec.rb:11 # ar_dba extension #xlog_location_diff returns the correct xlog difference

Looks like just a rename..

pg_xlog_location_diff -> pg_wal_lsn_diff
pg_current_xlog_insert_location -> pg_current_wal_insert_lsn

diff --git a/lib/extensions/ar_adapter/ar_dba.rb b/lib/extensions/ar_adapter/ar_dba.rb
index c1db88cd77..42729b4d9d 100644
--- a/lib/extensions/ar_adapter/ar_dba.rb
+++ b/lib/extensions/ar_adapter/ar_dba.rb
@@ -12,11 +12,11 @@ ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
   end

   def xlog_location
-    select_value("SELECT pg_current_xlog_insert_location()")
+    select_value("SELECT pg_current_wal_insert_lsn()")
   end

   def xlog_location_diff(lsn1, lsn2)
-    select_value("SELECT pg_xlog_location_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i
+    select_value("SELECT pg_wal_lsn_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i
   end

   def client_connections

seems to fix it, but there's also pglogical/client.rb, and I guess compatibility concerns..

But as with 9.6, most of the UI is unaffected.

bug

Source

himdel

Most helpful comment

@himdel Cannot apply the following label because they are not recognized: postgresql10.0

miq-bot on 3 Apr 2018

😄2

All 9 comments

@miq-bot add_label bug, postgresql10.0

(except it doesn't exist)

himdel on 27 Sep 2017

@himdel Cannot apply the following label because they are not recognized: postgres10.0

miq-bot on 27 Sep 2017

This issue has been automatically marked as stale because it has not been updated for at least 6 months.

If you can still reproduce this issue on the current release or on master, please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions!

miq-bot on 2 Apr 2018

keepalive... @miq-bot remove_label stale

himdel on 3 Apr 2018

@himdel Cannot apply the following label because they are not recognized: postgresql10.0

miq-bot on 3 Apr 2018

😄2

@himdel is this still a valid issue. If not can you close.
If there's no update by next week, I'll be closing this issue.

JPrause on 23 Jan 2019

Confirmed, this is still true (for postgresql 10+):

$ apt policy postgresql
postgresql:
  Installed: 11+198
...

$ bundle exec rspec spec/models/pglogical_subscription_spec.rb spec/lib/extensions/ar_dba_spec.rb
...
Failures:

  1) ar_dba extension #xlog_location_diff returns the correct xlog difference
     Failure/Error: select_value("SELECT pg_xlog_location_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
       LINE 1: SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
     # ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'
     # ./spec/lib/extensions/ar_dba_spec.rb:12:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
     #   LINE 1: SELECT pg_xlog_location_diff('18/72F84A48', '18/72F615B8')
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'

  2) ar_dba extension #xlog_location returns a valid lsn
     Failure/Error: select_value("SELECT pg_current_xlog_insert_location()")

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_current_xlog_insert_location() does not exist
       LINE 1: SELECT pg_current_xlog_insert_location()
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_current_xlog_insert_location()
     # ./lib/extensions/ar_adapter/ar_dba.rb:15:in `xlog_location'
     # ./spec/lib/extensions/ar_dba_spec.rb:6:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_current_xlog_insert_location() does not exist
     #   LINE 1: SELECT pg_current_xlog_insert_location()
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:15:in `xlog_location'

  3) PglogicalSubscription#backlog returns the correct value
     Failure/Error: select_value("SELECT pg_xlog_location_diff(#{quote(lsn1)}, #{quote(lsn2)})").to_i

     ActiveRecord::StatementInvalid:
       PG::UndefinedFunction: ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
       LINE 1: SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
                      ^
       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
       : SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
     # ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'
     # ./app/models/pglogical_subscription.rb:111:in `backlog'
     # ./spec/models/pglogical_subscription_spec.rb:479:in `block (3 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # PG::UndefinedFunction:
     #   ERROR:  function pg_xlog_location_diff(unknown, unknown) does not exist
     #   LINE 1: SELECT pg_xlog_location_diff('0/42108F8', '0/420D9A0')
     #                  ^
     #   HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
     #   ./lib/extensions/ar_adapter/ar_dba.rb:19:in `xlog_location_diff'

Finished in 3.86 seconds (files took 6.46 seconds to load)
40 examples, 3 failures

Failed examples:

rspec ./spec/lib/extensions/ar_dba_spec.rb:11 # ar_dba extension #xlog_location_diff returns the correct xlog difference
rspec ./spec/lib/extensions/ar_dba_spec.rb:5 # ar_dba extension #xlog_location returns a valid lsn
rspec ./spec/models/pglogical_subscription_spec.rb:475 # PglogicalSubscription#backlog returns the correct value

Randomized with seed 48251

himdel on 23 Jan 2019

(Not sure what the plan is with 9.6+ support, but we're currently 3 versions behind, will be 4 in q4 2019.)

himdel on 23 Jan 2019

Thanks @himdel
@miq-bot remove_label stale

JPrause on 24 Jan 2019

Was this page helpful?

0 / 5 - 0 ratings