django.db.utils.ProgrammingError: column "proisagg" does not exist

[akorn]

Hi,

# sudo -H -u www-data python manage.py syncdb
WARNING!!! You are using a 'locmem' (local memory) caching backend,
which is OK for a low volume site running on a single-process server.
For a multi-process configuration it is neccessary to have a production
cache system, such as redis or memcached.

With local memory caching and multi-process setup you might intermittently
see outdated content on your site.

System check identified some issues:

WARNINGS:
django_authopenid.UserPasswordQueue.user: (fields.W342) Setting unique=True on a ForeignKey has the same effect as using a OneToOneField.
        HINT: ForeignKey(unique=True) is usually better served by a OneToOneField.
group_messaging.SenderList.recipient: (fields.W342) Setting unique=True on a ForeignKey has the same effect as using a OneToOneField.
        HINT: ForeignKey(unique=True) is usually better served by a OneToOneField.
Operations to perform:
  Synchronize unmigrated apps: captcha, djkombu, staticfiles, tinymce, messages, keyedcache, sitemaps, compressor, django_countries, humanize
  Apply all migrations: followit, livesettings, group_messaging, django_authopenid, sessions, admin, djcelery, robots, auth, sites, askbot, contenttypes, avatar
Synchronizing apps without migrations:
  Creating tables...
    Creating table djkombu_queue
    Creating table djkombu_message
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying askbot.0001_initial... OK
  Applying askbot.0002_auto_20151026_0715... OK
  Applying askbot.0002_auto_20151218_0908... OK
  Applying askbot.0003_auto_20151218_0909... OK
  Applying askbot.0004_auto_20151219_0751...Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 354, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 346, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 394, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 445, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/syncdb.py", line 25, in handle
    call_command("migrate", **options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 120, in call_command
    return command.execute(*args, **defaults)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 445, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 222, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 110, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 148, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/migration.py", line 115, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/operations/special.py", line 183, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/usr/local/lib/python2.7/dist-packages/askbot/migrations/0004_auto_20151219_0751.py", line 18, in init_postgresql_fts
    setup_full_text_search(script_path)
  File "/usr/local/lib/python2.7/dist-packages/askbot/search/postgresql/__init__.py", line 42, in setup_full_text_search
    cursor.execute(fts_init_query)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: column "proisagg" does not exist
LINE 1: ...FROM pg_proc WHERE proname = 'concat_tsvectors' AND proisagg
                                                               ^
HINT:  Perhaps you meant to reference the column "pg_proc.prolang".
QUERY:  SELECT * FROM pg_proc WHERE proname = 'concat_tsvectors' AND proisagg
CONTEXT:  PL/pgSQL function setup_aggregates() line 5 at FOR over SELECT rows

This is with:

Package                   Version    
------------------------- -----------
akismet                   0.2.0      
amqp                      1.4.9      
anyjson                   0.3.3      
askbot                    0.10.2     
beautifulsoup4            4.4.1      
billiard                  3.3.0.23   
celery                    3.1.18     
Coffin                    0.3.8      
Django                    1.8.19     
django-appconf            1.0.3      
django-avatar             2.2.1      
django-celery             3.1.17     
django-compressor         1.5        
django-countries          3.3        
django-followit           0.2.1      
django-keyedcache         1.5.1      
django-kombu              0.9.4      
django-picklefield        0.3.0      
django-recaptcha          1.0.5      
django-robots             1.1        
django-threaded-multihost 1.4.post1  
django-tinymce            1.5.3      
html5lib                  0.9999999  
httplib2                  0.13.1     
Jinja2                    2.8        
jsonfield                 1.0.3      
kombu                     3.0.37     
markdown2                 2.3.1      
MarkupSafe                1.1.1      
mock                      1.0.1      
oauth2                    1.9.0.post1
Pillow                    6.1.0      
pip                       18.1       
psycopg2                  2.8.3      
PyJWT                     1.4.0      
pystache                  0.3.1      
python-cas                1.1.0      
python-openid             2.2.5      
pytz                      2016.4     
regex                     2019.8.19  
requirements-parser       0.1.0      
sanction                  0.3.1      
setuptools                41.2.0     
simplejson                3.8.2      
six                       1.10.0     
Unidecode                 0.4.19     
wheel                     0.32.3     

My first attempted askbot installation. I pulled the old 1.10.0 version of six from the bowels of the Internet to workaround #735. The 0.11.x branch doesn't work for me either; I setup.py develop hits a similar error to #735 (I can file a separate issue about it if it makes sense).

The pg_proc table it created looks like this:

# \d+ pg_proc
                                       Table "pg_catalog.pg_proc"
     Column      |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
-----------------+--------------+-----------+----------+---------+----------+--------------+-------------
 proname         | name         |           | not null |         | plain    |              | 
 pronamespace    | oid          |           | not null |         | plain    |              | 
 proowner        | oid          |           | not null |         | plain    |              | 
 prolang         | oid          |           | not null |         | plain    |              | 
 procost         | real         |           | not null |         | plain    |              | 
 prorows         | real         |           | not null |         | plain    |              | 
 provariadic     | oid          |           | not null |         | plain    |              | 
 protransform    | regproc      |           | not null |         | plain    |              | 
 prokind         | "char"       |           | not null |         | plain    |              | 
 prosecdef       | boolean      |           | not null |         | plain    |              | 
 proleakproof    | boolean      |           | not null |         | plain    |              | 
 proisstrict     | boolean      |           | not null |         | plain    |              | 
 proretset       | boolean      |           | not null |         | plain    |              | 
 provolatile     | "char"       |           | not null |         | plain    |              | 
 proparallel     | "char"       |           | not null |         | plain    |              | 
 pronargs        | smallint     |           | not null |         | plain    |              | 
 pronargdefaults | smallint     |           | not null |         | plain    |              | 
 prorettype      | oid          |           | not null |         | plain    |              | 
 proargtypes     | oidvector    |           | not null |         | plain    |              | 
 proallargtypes  | oid[]        |           |          |         | extended |              | 
 proargmodes     | "char"[]     |           |          |         | extended |              | 
 proargnames     | text[]       |           |          |         | extended |              | 
 proargdefaults  | pg_node_tree |           |          |         | extended |              | 
 protrftypes     | oid[]        |           |          |         | extended |              | 
 prosrc          | text         |           | not null |         | extended |              | 
 probin          | text         |           |          |         | extended |              | 
 proconfig       | text[]       |           |          |         | extended |              | 
 proacl          | aclitem[]    |           |          |         | extended |              | 
Indexes:
    "pg_proc_oid_index" UNIQUE, btree (oid)
    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes, pronamespace)
Has OIDs: yes

[akorn]

Ah, that's a built-in table of PostgreSQL, and apparently the proisagg column got removed in PostgreSQL-11: https://www.postgresql.org/docs/11/release-11.html

[akorn]

Changing the code to use prokind = 'a' whereever it used proisagg allows it to continue, but I don't know if this is the correct fix (and obviously it's not universal because this doesn't work with earlier PostgreSQL versions).

[martin-bts]

This is a real issue which makes Askbot incompatible with PostgreSQL 11.

From the PostgreSQL 11 release notes:

Replace system catalog pg_proc's proisagg and proiswindow columns with prokind (Peter Eisentraut)
This new column more clearly distinguishes functions, procedures, aggregates, and window functions.

There is no proisagg column anymore, but there are some plsql definitions in Askbot that explicitly use it. Consequently each migration which loads such a definition must fail. We need to look at askbot/search/postgresql/*.plsql.

Besides, the 0.11.x branch is a (i.e. the only) Python 3 branch of Askbot. If you experience the same issue as described in #735, then you are most likely trying to run the Python 3 branch of Askbot using Python 2.