PostgreSQL support

[rubdos]

Hi!

I was just testing out whether I could have ngshare run on the same PostgreSQL server as I have JupyterHub on. I'll document my issues here.

• The Docker image needs psycopg2. I used a custom image:

  FROM libretexts/ngshare:v0.5.1
  USER 0:0
  RUN pip install psycopg2-binary
  USER 65535:65535

• The database seems to be designed with Sqlite's dynamic-ish typing. Best case, ngshare should be tested against both sqlite and PostgreSQL. Example error, although I would expect many more due to Sqlite's lax behaviour wrt types:

  sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) foreign key constraint "assignment_files_assoc_table_left_id_fkey" cannot be implemented
  DETAIL:  Key columns "left_id" and "_id" are of incompatible types: text and integer.
  
  [SQL:
  CREATE TABLE assignment_files_assoc_table (
  	left_id TEXT NOT NULL,
  	right_id INTEGER NOT NULL,
  	PRIMARY KEY (left_id, right_id),
  	FOREIGN KEY(left_id) REFERENCES assignments (_id),
  	FOREIGN KEY(right_id) REFERENCES files (_id)
  )
  
  ]
  

• Sqlite creates the database automatically when opened, but psycopg2 as driver does not. I used a Kubernetes Job to create the database instead. This might need a simple code change somewhere.
• Unless I am mistaken, the PVC that gets allocated is only used for the Sqlite support, so that should be shut of when the installations opts for PostgreSQL.

Is PostgreSQL support something you folks would be interested in? I'd provide some patches if so.

[rkevin-arch]

It would probably be a good idea, since SQLite seems to be the only bottleneck preventing multiple ngshare instances to be run at once (TODO: will the other files in the PVC cause race conditions?). I'm not super familiar with how our alembic setup (iirc it's jank) deals with postgres, but if you want to add postgres support in as a feature feel free to do so! Alternatively, let me know if you need me to look into it.

[rubdos]

Good, I will patch this then. I'm currently pressured into rolling this out asap (which means SQLite on a PVC), so it won't be for today. If I get this all rolled out in time, I might patch in PostgreSQL before September, otherwise I'm afraid I'll have to wait a year (or implement SQLite to PostgreSQL migration, which will be quite painful).

I did a quick search in the source code to find out about other files in the PVC, but I could not immediately find them.

FWIW, if I patched the Docker image with psycopg2, Alembic already started to create the necessary tables through migrations, so it should deal pretty well (except for the few type errors in the table structure).

[jtande]

Hi!

I was just testing out whether I could have ngshare run on the same PostgreSQL server as I have JupyterHub on. I'll document my issues here.

* The Docker image needs `psycopg2`. I used a custom image:
  ```dockerfile
  FROM libretexts/ngshare:v0.5.1
  USER 0:0
  RUN pip install psycopg2-binary
  USER 65535:65535
  ```

* The database seems to be designed with Sqlite's dynamic-ish typing. Best case, ngshare should be tested against both sqlite and PostgreSQL. Example error, although I would expect many more due to Sqlite's lax behaviour wrt types:
  ```
  sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) foreign key constraint "assignment_files_assoc_table_left_id_fkey" cannot be implemented
  DETAIL:  Key columns "left_id" and "_id" are of incompatible types: text and integer.
  
  [SQL:
  CREATE TABLE assignment_files_assoc_table (
  	left_id TEXT NOT NULL,
  	right_id INTEGER NOT NULL,
  	PRIMARY KEY (left_id, right_id),
  	FOREIGN KEY(left_id) REFERENCES assignments (_id),
  	FOREIGN KEY(right_id) REFERENCES files (_id)
  )
  
  ]
  ```

* Sqlite creates the database automatically when opened, but `psycopg2` as driver does not. I used a Kubernetes Job to create the database instead. This might need a simple code change somewhere.

* Unless I am mistaken, the PVC that gets allocated is only used for the Sqlite support, so that should be shut of when the installations opts for PostgreSQL.

Is PostgreSQL support something you folks would be interested in? I'd provide some patches if so.

I was wondering if @rubdos could share the temporary fix solution to this issue. I believe I am having a similar issue. I am trying to build Z2JH with nbgrader. My Z2JH works well but ngshare is failing and the same error is showing up for both kubernetes v1.17.9 and v1.20.5. I have used the following images:

 repository: libretexts/ngshare
  pullPolicy: Always
  #tag: v0.5.3-n008.h44632c7
  #tag: v0.5.3-n004.h8cb7b4f
  tag: v0.5.3

yet the same error as below shows up all the time

 kubectl -n uncg-jhub logs ngshare-bfdb546cd-299cr
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: database is locked

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/lib/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/usr/local/lib/python3.6/dist-packages/ngshare/__main__.py", line 4, in <module>
    main(sys.argv[1:])
  File "/usr/local/lib/python3.6/dist-packages/ngshare/ngshare.py", line 1027, in main
    dbutil.upgrade(args.database, cmd_opts=cmd_opts)
  File "/usr/local/lib/python3.6/dist-packages/ngshare/dbutil.py", line 42, in upgrade
    alembic.command.upgrade(get_alembic_config(db_url, cmd_opts), revision)
  File "/usr/local/lib/python3.6/dist-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.6/dist-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.6/dist-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.6/dist-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/usr/local/lib/python3.6/dist-packages/ngshare/alembic/env.py", line 81, in <module>
    run_migrations_online()
  File "/usr/local/lib/python3.6/dist-packages/ngshare/alembic/env.py", line 75, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.6/dist-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.6/dist-packages/alembic/runtime/migration.py", line 501, in run_migrations
    self._ensure_version_table()
  File "/usr/local/lib/python3.6/dist-packages/alembic/runtime/migration.py", line 442, in _ensure_version_table
    self._version.create(self.connection, checkfirst=True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 927, in create
    bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1073, in _execute_ddl
    compiled,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
[SQL: 
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)

]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

[rubdos]

I was wondering if @rubdos could share the temporary fix solution to this issue. I believe I am having a similar issue. I am trying to build Z2JH with nbgrader. My Z2JH works well but ngshare is failing and the same error is showing up for both kubernetes v1.17.9 and v1.20.5. I have used the following images:

My semi-permanent temporary fix is deploying atomically and having sqlite on a read-write storage:

deployment:
  # Only works in ngshare 0.5.2 and higher
  strategy:
    type: Recreate
pvc:
  storageClassName: "rbd-csi"
  accessModes:
    - ReadWriteOnce

[jtande]

Thank you Ruben, Sorry to bother you, I am new to the kubernetes NFS. 1. Could you be a little more specific about this statement "*deploying atomically and having sqlite on a read-write storage*". 2. I see your storageClassName as "rdb-csi", may I know what cloud provider you used, I am using Azure and had used the azurefile plugin. 3. Also, your accessmodes are ReadWriteOnce, this is similar to the user's pods modes. Could you explain the reason behind this choice? Again thank you for your help

…

On Mon, May 10, 2021 at 7:33 AM Ruben De Smet ***@***.***> wrote: I was wondering if @rubdos <https://github.com/rubdos> could share the temporary fix solution to this issue. I believe I am having a similar issue. I am trying to build Z2JH with nbgrader. My Z2JH works well but ngshare is failing and the same error is showing up for both kubernetes v1.17.9 and v1.20.5. I have used the following images: My semi-permanent temporary fix is deploying atomically and having sqlite on a read-write storage: deployment: # Only works in ngshare 0.5.2 and higher strategy: type: Recreate pvc: storageClassName: "rbd-csi" accessModes: - ReadWriteOnce — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#125 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABWKILGCPMMKMUSIHFDORSDTM7ABBANCNFSM4PZ2LM7Q> .

-- Jacob Fosso Tande, Ph.D *Research Computing Scientist* *Github: https://jtande.github.io/JFossoTande.github.io/ <https://jtande.github.io/JFossoTande.github.io/>* ResearchGate: https://www.researchgate.net/profile/Jacob_Fosso-Tande

[rubdos]

1. Could you be a little more specific about this statement "deploying atomically and having sqlite on a read-write storage".

You can only ever run a single instance of ngshare at the same time (the paradigm in Kubernetes where you scale all the things that are under load doesn't apply here, nor to jupyterhub).
You need to tear down the old instance before starting the new one (therefore strategy.type: Recreate)

2. I see your storageClassName as "rdb-csi", may I know what cloud provider you used, I am using Azure and had used the azurefile plugin.

Our own K8s cluster uses Ceph with rdb. I don't know what the Azure equivalent would be, but you can certainly find something that provides ReadWriteOnce 👇 .

3. Also, your accessmodes are ReadWriteOnce, this is similar to the user's pods modes. Could you explain the reason behind this choice?

Because you don't want two instances of ngshare accessing the Sqlite at the same time, see (1).