Trying to get Logidze working

[SimonDKnight]

Hi, I am having some issues getting Logidze working. I have followed your install instructions and whenever I try to create I get the following error

PG::UndefinedFunction - ERROR:  function to_jsonb(positions) does not exist
LINE 1: SELECT logidze_snapshot(to_jsonb(NEW.*), ts_column, columns_...
                                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT logidze_snapshot(to_jsonb(NEW.*), ts_column, columns_blacklist)
CONTEXT:  PL/pgSQL function logidze_logger() line 20 at assignment

Any ideas?

I am using the Apartment gem which is tenanting my customers using postgres schemas, perhaps this is the problem?

[palkan]

Hi!

Which PostgreSQL version do you use?

[palkan]

@SimonDKnight ping)

[palkan]

Closed as stale

[ydakuka]

I got the same error.

ydakuka@Yauheni-Work:~/virtual_box/project$ ruby -v
ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-linux]
ydakuka@Yauheni-Work:~/virtual_box/project$ rails -v
Rails 5.1.4
ydakuka@Yauheni-Work:~/virtual_box/project$ psql --version
psql (PostgreSQL) 9.5.10

ydakuka@Yauheni-Work:~/virtual_box/project$ rails db:migrate
== 20171230234326 AddLogidzeToUsers: migrating ================================
-- add_column(:users, :log_data, :jsonb)
   -> 0.0008s
-- execute("      CREATE TRIGGER logidze_on_users\n      BEFORE UPDATE OR INSERT ON users FOR EACH ROW\n      WHEN (coalesce(current_setting('logidze.disabled'), '') <> 'on')\n      EXECUTE PROCEDURE logidze_logger(null, 'updated_at');\n")
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UndefinedFunction: ERROR:  function logidze_logger() does not exist
:       CREATE TRIGGER logidze_on_users
      BEFORE UPDATE OR INSERT ON users FOR EACH ROW
      WHEN (coalesce(current_setting('logidze.disabled'), '') <> 'on')
      EXECUTE PROCEDURE logidze_logger(null, 'updated_at');

[palkan]

@ydakuka this is not the same; your error says that there is no logidze_logger function. Looks like you haven't initialized Logidze; you have to run rails generate logidze:install, which creates a migration with all necessary functions. Note: that migration should run before any other Logidze-related migration.

[derekgstevens]

Ignore the below - I discovered the issue was with the apartment gem (here). For anyone running into this issue in the future, apartment does not run sql execute statements in pg schemas.

I hope you don't mind me commenting on a closed issue, but I'm getting the same issue as ydakuka above, but while using the apartment gem like SimonGKnight. It appears that my initial logidze migration isn't being run across the pg schemas I have, which is causing it to throw the 'function not found' error.

Any ideas?

[palkan]

Hi @derekgstevens!

Check this section from the apartment Readme.

It explains how to enhance schemas with raw SQL (that's exactly what we need to install Logidze).

[archonic]

@palkan Is raw SQL required when using Apartment + Logidze or do the previous instructions (steps 1-3) work as well?

Update: I had Apartment and Logidze working well together before I needed to reset my DB but I can't get back to a working state. I've tried every combination of steps on that link. In my seeds.rb, I create a tenant. rails db:reset works, and running run migrations for xxx_logidze_install and xxx_add_logidze_to_documents works, but on saving the second version of a document I get PG::UndefinedFunction: ERROR: function hstore(documents) does not exist LINE 2: hstore(NEW.*) - hstore(OLD.*).

If I use config.use_sql = true in the apartment config then reset, I get a duplicate table warning after it successfully completes the PG dump. Evidently we could use some guidance on how to mix apartment with logidze and some tips on how to maintain an app that can safely run rails db:reset.

[palkan]

@archonic Looks like your schema doesn't have hstore extension installed. Do you have a xxx_enable_hstore.rb migration? And CREATE EXTENSION IF NOT EXISTS HSTORE in your structure.sql (if any)?

[archonic]

I've had the error above when I had the xxx_enable_hstore.rb migration. I removed it since I figured it wasn't required when this is in my schema.rb.

enable_extension "hstore"
enable_extension "plpgsql"
enable_extension "uuid-ossp"

I'm not using a structure.sql file.

Interestingly it seems to have not installed uuid-ossp.

\dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

More info:
I'm unable to create tenants while I have config.use_sql = true for Apartment. It complains about the first table already existing. If I disable it, I can create tenants and work with documents without getting errors, but I can't call Logidze methods because log_data is empty. I get Document#log_size delegated to log_data.size, but log_data is nil. The trigger does exist however. I can query with SELECT * FROM pg_trigger; and I can see logidze_on_documents.

[archonic]

I have a minimal logidze + apartment application here which has the same issue:
https://github.com/archonic/logidze-apartment

If you create then update a document in the public schema, logidze will populate log_data and everything is fine. If you create then update a document within a tenant, log_data is not populated. I believe that's because the trigger isn't available within that schema, which is why apartment mentions the schema_search_path and the shared_extensions schema which holds extensions and triggers. Am I understanding that correctly?

[palkan]

@archonic Thanks for sharing an examples app! I made it work, here is a PR with a description: archonic/logidze-apartment#1

[archonic]

Beauty! 🙌 Thanks very much. Let me know if I can buy you a beer 😄

[palkan]

Let me know if I can buy you a beer

😺 Do you deliver to Moscow?))

[archonic]

https://twitter.com/palkan_tula/status/996002315701284865

Totally agree. Sounds like an idea for an app. How about an email money transfer to the email on your profile?

[palkan]

@archonic You can send me a postcard. That would be fair enough)

[palkan]

Hey @archonic! Hope you're doing well.

It looks like logidze-apartment repo is no longer available (and my fork as well). And I can't remember what was the fix 🙂

Could you please remind me?

[archonic]

Hey! Now I wish I hadn't deleted it. I don't have it locally either, but the fix is in another repo. Let me see if I can find it.

[archonic]

https://github.com/archonic/logidze-apartment

I'm not sure where the fix was but that's an example that can safely run rails db:reset.

Update: I just remembered a minor important detail about apartment. It will run migrations in tenants with config.db_migrate_tenants = true, but it only plugs into rails db:migrate. It won't run tenant migrations if your migrate instruction is rails db:prepare.

[palkan]

@archonic Thanks!