PostgreSQL Apache AGE Rails (GraphDB driver questions / problems) about rails HOT 6 CLOSED

I have found a workaround - if I rewrite the schema.rb to:

ActiveRecord::Schema[7.1].define(version: 2024_05_05_183043) do
  execute('CREATE EXTENSION IF NOT EXISTS age;')
  execute <<-SQL
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1
        FROM pg_namespace
        WHERE nspname = 'ag_catalog'
      ) THEN
        CREATE SCHEMA ag_catalog;
      END IF;
    END $$;
  SQL

  # These are extensions that must be enabled in order to support this database
  enable_extension 'age'
  enable_extension 'plpgsql'

  # Load the age code
  execute("LOAD 'age';")

  # Load the ag_catalog into the search path
  execute('SET search_path = ag_catalog, "$user", public;')

  execute <<-SQL
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'fk_graph_oid'
      ) THEN
        ALTER TABLE ag_label ADD CONSTRAINT fk_graph_oid FOREIGN KEY (graph) REFERENCES ag_graph (graphid);
      END IF;
    END $$;
  SQL

  # create_schema 'age_schema'
  execute <<-SQL
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1
        FROM ag_catalog.ag_graph
        WHERE name = 'age_schema'
      ) THEN
        PERFORM create_graph('age_schema');
      END IF;
    END $$;
  SQL
end

instead of - which indeed different than the migration (& incorrect):

ActiveRecord::Schema[7.1].define(version: 2024_05_05_183043) do
  create_schema "ag_catalog"
  create_schema "age_schema"

  # These are extensions that must be enabled in order to support this database
  enable_extension "age"
  enable_extension "plpgsql"

  add_foreign_key "ag_label", "ag_graph", column: "graph", primary_key: "graphid", name: "fk_graph_oid"
end

unfortunately, I need to reset the schema.rb after each new migration. Is there a solution to control the schema.rb generation and make it compatible with ApaageAGE?

from rails.

Can you try with structure.sql instead of schema.rb? https://edgeguides.rubyonrails.org/active_record_migrations.html#schema-dumping-and-you

from rails.

@btihen thanks for working on this, I'd also be interested in exploring AGE for graph support.

You probably already saw this, but some ideas if name collision hasn't already been ruled out:
PG::DuplicateObject: ERROR: constraint "fk_graph_oid" for relation "ag_label" already exists (PG::DuplicateObject).

DBMS-level name collision?

You may have ruled this out already, but I wonder if create_graph('age_schema') is running twice, once successfully in dev migrations, then again in test, but failing since it has the same age_schema namespace, and that namespace is being shared across the dev and test databases.

Potential fixes

Separate graph namespaces for dev and test

You might be able to find examples among gems that have implemented support for PG extensions like activerecord-postgis-adapter and see how the situations where a common extension catalog and schema can handle this.

Option 1 - Isolate by DB, nothing shared at DBMS (preferred)

• If AGE allows complete isolation where the graph and extension are unique to the dev or test DB, rather than sharing some elements like the graph name at the DBMS level, this might be easiest. In this approach, after migration there's an age_schema graph in your dev db and completely independent age_schema graph in your dev db. If AGE supports DB-level isolation of graphs like this instead of DBMS-level, this might be your best option.

Option 2 - Isolate by graph name

• Modify execute("SELECT create_graph('age_schema');") to avoid dev/test namespace collision for the name age_schema, the end goal would be that when run in test mode the name of age_schema would be changing the age_schema_test. So you'd have age_schema and age_schema_test after running migrations.

Note this should be avoided if possible, I'd only consider it if AGE does not support DB-level isolation

Single graph namespace

• Goal: modify execute("SELECT create_graph('age_schema');") by adding the equivalent of an IF NOT EXISTS statement.

Option 1 - Add check for existing AGE graph using AGE functions

• Since create_graph is a function, you may want to make use of the AGE graph_exists() or get_graphnames() functions, integrating them into the equivalent of an IF NOT EXISTS check in the graph creation query above.

Option 2 - Add check for existing AGE graph using Postgres DDL queries

• This would require running the create_graph function, determining the underlying structures you want to look for that are often created by create_graph then query to determine if they are present.

from rails.

@simi

Can you try with structure.sql instead of schema.rb?

I will need to experiment a bit more. I am using a dockerized version of the DB with AGE (Postgres v16) but my psql version is postgresql v14). At the moment i can't change this setup n this computer - i'll see if i can get another setup going.

ActiveRecord::Schema[7.1].define(version: 20_240_505_183_043) do
  # Allow age extension
  execute('CREATE EXTENSION IF NOT EXISTS age;')

  # Load the age code
  execute("LOAD 'age';")

  # Load the ag_catalog into the search path
  execute('SET search_path = ag_catalog, "$user", public;')

  # Create age_schema graph if it doesn't exist
  execute("SELECT create_graph('age_schema');")
end

in the meantime I found I can just copy the migration into the schema.rb and just reset it with careful commits.

from rails.

thanks for working on this, I'd also be interested in exploring AGE for graph support.

You probably already saw this, but some ideas if name collision hasn't already been ruled out:
PG::DuplicateObject: ERROR: constraint "fk_graph_oid" for relation "ag_label" already exists (PG::DuplicateObject).

@mashallah - Hi I'd be glad to collaborate.

I did see the name collision, but I've been ignoring them since I discoved the problem was that the schema is incorrectly generated - when i copy the migration into the schema - \i can run tests and there is no name collision - the schema just needs to be correct.

I have some basic working rails code at: https://github.com/btihen-dev/rails_graphdb_age_app (with tests running)

PS - code is still evolving, only handles ons schema, and doesn't work with all data types supported by AGE & I havn;t yet tested unique restrictions.

from rails.

Please use https://discuss.rubyonrails.org/ for questions/help, where a wider community will be able to help you. We reserve the issues tracker for issues only.

from rails.