PG 10.0 - PG::UndefinedFunction - pg_xlog_location_diff & pg_current_xlog_insert_location

[himdel]

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

[himdel]

@miq-bot add_label bug, postgresql10.0

(except it doesn't exist)

[miq-bot]

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

[miq-bot]

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!

[himdel]

keepalive... @miq-bot remove_label stale

[miq-bot]

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

[JPrause]

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

[himdel]

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]

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

[JPrause]

Thanks @himdel
@miq-bot remove_label stale