search

sjacorg / bayanat

Open source data management solution for human rights documentation.
https://bayanat.org/
GNU Affero General Public License v3.0
27 stars 16 forks source link

flask create-db fails to CREATE TABLE bulletin #13

Closed saadcaffeine closed 3 years ago

saadcaffeine commented 3 years ago 
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "("
LINE 28:  search TEXT GENERATED ALWAYS AS (
                                          ^

[SQL: 
CREATE TABLE bulletin (
    created_at TIMESTAMP WITHOUT TIME ZONE, 
    updated_at TIMESTAMP WITHOUT TIME ZONE, 
    deleted BOOLEAN, 
    id SERIAL NOT NULL, 
    title VARCHAR(255) NOT NULL, 
    title_ar VARCHAR(255), 
    sjac_title VARCHAR(255), 
    sjac_title_ar VARCHAR(255), 
    user_id INTEGER, 
    assigned_to_id INTEGER, 
    description TEXT, 
    reliability_score INTEGER, 
    first_peer_reviewer_id INTEGER, 
    second_peer_reviewer_id INTEGER, 
    publish_date TIMESTAMP WITHOUT TIME ZONE, 
    documentation_date TIMESTAMP WITHOUT TIME ZONE, 
    status VARCHAR(255), 
    source_link VARCHAR(255), 
    source_link_type BOOLEAN, 
    ref VARCHAR[], 
    originid VARCHAR, 
    comments TEXT, 
    review TEXT, 
    review_action VARCHAR, 
    tsv TSVECTOR, 
    search TEXT GENERATED ALWAYS AS (
        (((((((((((((((((id)::text || ' '::text) || (COALESCE(title, ''::character varying))::text) || ' '::text) ||
                        (COALESCE(title_ar, ''::character varying))::text) || ' '::text) ||
                      COALESCE(description, ''::text)) || ' '::text) ||
                    (COALESCE(originid, ''::character varying))::text) || ' '::text) ||
                  (COALESCE(sjac_title, ''::character varying))::text) || ' '::text) ||
                (COALESCE(sjac_title_ar, ''::character varying))::text) || ' '::text) ||
                (COALESCE(source_link, ''::character varying))::text) || ' '::text) 
                ||  ' '::text) || COALESCE(comments, ''::text)
        ) STORED, 
    PRIMARY KEY (id), 
    FOREIGN KEY(user_id) REFERENCES "user" (id), 
    FOREIGN KEY(assigned_to_id) REFERENCES "user" (id), 
    FOREIGN KEY(first_peer_reviewer_id) REFERENCES "user" (id), 
    FOREIGN KEY(second_peer_reviewer_id) REFERENCES "user" (id)
)

To Reproduce Steps to reproduce the behavior:

Follow Manual installation steps as at https://docs.bayanat.org/en/install Configure following configuration steps as at https://docs.bayanat.org/en/config


(env) pi@raspberry:~/bayanat$ export FLASK_APP=run.py
(env) pi@raspberry:~/bayanat$ flask create-db
Trigram extension installed successfully
Postgis extension installed successfully
Traceback (most recent call last):
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: syntax error at or near "("
LINE 28:  search TEXT GENERATED ALWAYS AS (
                                          ^

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

Traceback (most recent call last):
  File "/home/pi/bayanat/env/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 990, in main
    cli.main(args=sys.argv[1:])
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 596, in main
    return super().main(*args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 440, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pi/bayanat/enferno/commands.py", line 23, in create_db
    db.create_all()
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 1094, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 1086, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4742, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2980, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2016, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
    return meth(obj, **kw)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 850, in visit_metadata
    _is_metadata_operation=True,
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
    return meth(obj, **kw)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 895, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 78, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1296, in _execute_ddl
    compiled,
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1749, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1930, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 198, in raise_
    raise exception
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "("
LINE 28:  search TEXT GENERATED ALWAYS AS (
                                          ^

[SQL: 
CREATE TABLE bulletin (
    created_at TIMESTAMP WITHOUT TIME ZONE, 
    updated_at TIMESTAMP WITHOUT TIME ZONE, 
    deleted BOOLEAN, 
    id SERIAL NOT NULL, 
    title VARCHAR(255) NOT NULL, 
    title_ar VARCHAR(255), 
    sjac_title VARCHAR(255), 
    sjac_title_ar VARCHAR(255), 
    user_id INTEGER, 
    assigned_to_id INTEGER, 
    description TEXT, 
    reliability_score INTEGER, 
    first_peer_reviewer_id INTEGER, 
    second_peer_reviewer_id INTEGER, 
    publish_date TIMESTAMP WITHOUT TIME ZONE, 
    documentation_date TIMESTAMP WITHOUT TIME ZONE, 
    status VARCHAR(255), 
    source_link VARCHAR(255), 
    source_link_type BOOLEAN, 
    ref VARCHAR[], 
    originid VARCHAR, 
    comments TEXT, 
    review TEXT, 
    review_action VARCHAR, 
    tsv TSVECTOR, 
    search TEXT GENERATED ALWAYS AS (
        (((((((((((((((((id)::text || ' '::text) || (COALESCE(title, ''::character varying))::text) || ' '::text) ||
                        (COALESCE(title_ar, ''::character varying))::text) || ' '::text) ||
                      COALESCE(description, ''::text)) || ' '::text) ||
                    (COALESCE(originid, ''::character varying))::text) || ' '::text) ||
                  (COALESCE(sjac_title, ''::character varying))::text) || ' '::text) ||
                (COALESCE(sjac_title_ar, ''::character varying))::text) || ' '::text) ||
                (COALESCE(source_link, ''::character varying))::text) || ' '::text) 
                ||  ' '::text) || COALESCE(comments, ''::text)
        ) STORED, 
    PRIMARY KEY (id), 
    FOREIGN KEY(user_id) REFERENCES "user" (id), 
    FOREIGN KEY(assigned_to_id) REFERENCES "user" (id), 
    FOREIGN KEY(first_peer_reviewer_id) REFERENCES "user" (id), 
    FOREIGN KEY(second_peer_reviewer_id) REFERENCES "user" (id)
)

]
(Background on this error at: http://sqlalche.me/e/14/f405)
(env) pi@raspberry:~/bayanat$ locate settings.py
/usr/lib/python3/dist-packages/isort/settings.py
/usr/lib/python3/dist-packages/jedi/settings.py
/usr/lib/python3/dist-packages/mypy/typeshed/third_party/2and3/pynamodb/settings.pyi
/usr/lib/python3/dist-packages/reportlab/rl_settings.py
/usr/share/doc/python3-guizero/examples/layout_settings.py
/usr/share/hplip/devicesettings.py
/usr/share/hplip/printsettings.py
/usr/share/system-config-printer/firewallsettings.py
/usr/share/system-config-printer/serversettings.py
(env) pi@raspberry:~/bayanat$ export FLASK_APP=run.py
(env) pi@raspberry:~/bayanat$ flask create-db
Trigram extension installed successfully
Postgis extension installed successfully
Traceback (most recent call last):
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: syntax error at or near "("
LINE 28:  search TEXT GENERATED ALWAYS AS (
                                          ^

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

Traceback (most recent call last):
  File "/home/pi/bayanat/env/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 990, in main
    cli.main(args=sys.argv[1:])
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 596, in main
    return super().main(*args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/decorators.py", line 21, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask/cli.py", line 440, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pi/bayanat/enferno/commands.py", line 23, in create_db
    db.create_all()
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 1094, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py", line 1086, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 4742, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2980, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2016, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
    return meth(obj, **kw)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 850, in visit_metadata
    _is_metadata_operation=True,
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 483, in traverse_single
    return meth(obj, **kw)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 895, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 78, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1296, in _execute_ddl
    compiled,
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1749, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1930, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 198, in raise_
    raise exception
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/pi/bayanat/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 681, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "("
LINE 28:  search TEXT GENERATED ALWAYS AS (
                                          ^

[SQL: 
CREATE TABLE bulletin (
    created_at TIMESTAMP WITHOUT TIME ZONE, 
    updated_at TIMESTAMP WITHOUT TIME ZONE, 
    deleted BOOLEAN, 
    id SERIAL NOT NULL, 
    title VARCHAR(255) NOT NULL, 
    title_ar VARCHAR(255), 
    sjac_title VARCHAR(255), 
    sjac_title_ar VARCHAR(255), 
    user_id INTEGER, 
    assigned_to_id INTEGER, 
    description TEXT, 
    reliability_score INTEGER, 
    first_peer_reviewer_id INTEGER, 
    second_peer_reviewer_id INTEGER, 
    publish_date TIMESTAMP WITHOUT TIME ZONE, 
    documentation_date TIMESTAMP WITHOUT TIME ZONE, 
    status VARCHAR(255), 
    source_link VARCHAR(255), 
    source_link_type BOOLEAN, 
    ref VARCHAR[], 
    originid VARCHAR, 
    comments TEXT, 
    review TEXT, 
    review_action VARCHAR, 
    tsv TSVECTOR, 
    search TEXT GENERATED ALWAYS AS (
        (((((((((((((((((id)::text || ' '::text) || (COALESCE(title, ''::character varying))::text) || ' '::text) ||
                        (COALESCE(title_ar, ''::character varying))::text) || ' '::text) ||
                      COALESCE(description, ''::text)) || ' '::text) ||
                    (COALESCE(originid, ''::character varying))::text) || ' '::text) ||
                  (COALESCE(sjac_title, ''::character varying))::text) || ' '::text) ||
                (COALESCE(sjac_title_ar, ''::character varying))::text) || ' '::text) ||
                (COALESCE(source_link, ''::character varying))::text) || ' '::text) 
                ||  ' '::text) || COALESCE(comments, ''::text)
        ) STORED, 
    PRIMARY KEY (id), 
    FOREIGN KEY(user_id) REFERENCES "user" (id), 
    FOREIGN KEY(assigned_to_id) REFERENCES "user" (id), 
    FOREIGN KEY(first_peer_reviewer_id) REFERENCES "user" (id), 
    FOREIGN KEY(second_peer_reviewer_id) REFERENCES "user" (id)
)

]
(Background on this error at: http://sqlalche.me/e/14/f405)```
sjacgit commented 3 years ago

Many thanks for reporting this issue. Unfortunately we can't reproduce this issue on our end. Can you please provide more information about your system? (OS, python and PostreSQL versions, etc) Also, can you confirm that the config SQLALCHEMY_DATABASE_URI is set correctly to the database you created?

saadcaffeine commented 3 years ago 
Linux raspberry 4.19.0-13-amd64 #1 SMP Debian 4.19.160-2 (2020-11-28) x86_64 GNU/Linux
(env) pi@raspberry:~/bayanat$ pip --version
pip 21.2.4 from /home/pi/bayanat/env/lib/python3.7/site-packages/pip (python 3.7)
(env) pi@raspberry:~/bayanat$ flask --version
Python 3.7.3
Flask 2.0.1
Werkzeug 2.0.1
(env) pi@raspberry:~/bayanat$ psql --version
psql (PostgreSQL) 11.13 (Debian 11.13-0+deb10u1)

repeated reported steps to re-create the issue resulting in the same SQL error. unable to CREATE TABLE bulletin

(env) pi@raspberry:~/bayanat $ pip3 list 
Package             Version
------------------- ---------
alembic             1.7.4
amqp                2.6.1
anyjson             0.3.3
appnope             0.1.2
arrow               0.17.0
Babel               2.9.0
backcall            0.2.0
bcrypt              3.1.7
billiard            3.6.3.0
bleach              3.3.1
blinker             1.4
boto3               1.17.34
botocore            1.20.34
bouncer             0.1.12
cachelib            0.2.0
celery              4.4.7
certifi             2020.12.5
cffi                1.14.5
chardet             4.0.0
click               7.1.2
cryptography        3.3.2
decorator           4.4.2
dnspython           1.16.0
docutils            0.16
email-validator     1.1.2
Flask               2.0.1
Flask-Babel         2.0.0
Flask-BabelEx       0.9.4
flask-bouncer       0.3.0
Flask-Caching       1.9.0
Flask-DebugToolbar  0.11.0
Flask-Login         0.5.0
Flask-Mail          0.9.1
Flask-Migrate       2.7.0
Flask-Principal     0.4.0
Flask-Script        2.0.6
Flask-Security-Too  4.0.1
Flask-Session       0.3.2
flask-shell-ipython 0.4.1
Flask-SQLAlchemy    2.5.1
Flask-WTF           0.14.3
GeoAlchemy2         0.8.5
greenlet            1.1.0
idna                2.10
importlib-metadata  2.1.1
importlib-resources 5.3.0
ipython             7.9.0
ipython-genutils    0.2.0
itsdangerous        2.0.1
jedi                0.17.2
Jinja2              3.0.1
jmespath            0.10.0
kombu               4.6.11
Mako                1.1.4
MarkupSafe          2.0.1
more-itertools      7.2.0
numpy               1.21.2
oauthlib            3.1.0
packaging           21.0
pandas              1.3.2
parso               0.7.1
passlib             1.7.4
pexpect             4.8.0
pickleshare         0.7.5
Pillow              8.3.2
pip                 21.3.1
prompt-toolkit      2.0.10
psycopg2-binary     2.8.6
ptyprocess          0.7.0
pycparser           2.20
pyexifinfo          0.4.0
Pygments            2.7.4
pyparsing           2.4.7
PyQRCode            1.2.1
python-dateutil     2.8.1
python-dotenv       0.15.0
python-editor       1.0.4
pytz                2021.1
redis               3.5.3
requests            2.25.1
s3transfer          0.3.6
setuptools          58.3.0
Shapely             1.7.1
shortuuid           1.0.1
six                 1.15.0
speaklater          1.3
SQLAlchemy          1.4.26
text-unidecode      1.3
traitlets           4.3.3
urllib3             1.26.5
uWSGI               2.0.19.1
vine                1.3.0
wcwidth             0.2.5
webencodings        0.5.1
Werkzeug            2.0.1
wheel               0.37.0
WTForms             2.3.3
zipp                3.6.0
sjacgit commented 3 years ago

Many thanks for providing this information. We believe the issue to be caused by the PostgreSQL version you're using as it doesn't support generated columns: https://www.postgresql.org/docs/11/unsupported-features-sql-standard.html

If you can try to upgrade to a more recent version that would likely fix the issue you're having. Please let us know if the issue persist with newer versions.

saadcaffeine commented 3 years ago

Thank you for checking that, upgrading to PostgresSQL 14 resolves the issue.

Debian Buster main apt sources.list provides Postgresql 11 as the most recent version. APT-Sources: http://cdn-aws.deb.debian.org/debian buster/main amd64 Packages

The PostgreSQL Global Development Group (PGDG) maintains an APT repository of PostgreSQL packages for Debian and Ubuntu located at http://apt.postgresql.org/pub/repos/apt/

to add apt.postgresql.org as a source for apt packages:

sudo apt install postgresql-common gnupg

sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

uninstall version 11 of PostgreSQL sudo apt remove postgresql-11

and then install the latest version sudo apt-get install postgresql