First time here? Check out the FAQ!
1

Migration problem with Postgresql 9

In the installation process, when i run

python manage.py migrate askbot

i get this error

- Migrating forwards to 0111_populate__thread__added_at.
> askbot:0106_update_postgres_full_text_setup
Traceback (most recent call last):
  File "manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/management/commands/migrate.py", line 105, in handle
    ignore_ghosts = ignore_ghosts,
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/__init__.py", line 191, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 221, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 292, in migrate_many
    result = self.migrate(migration, database)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 125, in migrate
    result = self.run(migration)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 99, in run
    return self.run_migration(migration)
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/usr/lib/python2.7/site-packages/South-0.7.3-py2.7.egg/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/home/relax/Temp/askmath/askbot/migrations/0106_update_postgres_full_text_setup.py", line 26, in forwards
    setup_full_text_search(script_path)
  File "/home/relax/Temp/askmath/askbot/search/postgresql/__init__.py", line 19, in setup_full_text_search
    cursor.execute(fts_init_query)
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/db/backends/util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.7/site-packages/Django-1.3.1-py2.7.egg/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
    return self.cursor.execute(query, args)
django.db.utils.DatabaseError: cannot change name of input parameter "question_id"
HINT:  Use DROP FUNCTION first.

My postgresql version is 9.1.3 and my django version is 1.3.1. Thanks in advance.

Near the soul's avatar
25
Near the soul
asked 2012-03-11 12:38:39 -0500
Evgeny's avatar
13.2k
Evgeny
updated 2012-03-11 12:51:22 -0500
edit flag offensive 0 remove flag close merge delete

Comments

add a comment see more comments

2 Answers

1

This is the code of file

askbot/search/postgresql/thread_and_post_models_01162012.plsql

that works for me on Postgresql 9.1.3 to succesfull run migration:

/* function testing for existence of a column in a table
  if table does not exists, function will return "false" */
DROP FUNCTION IF EXISTS column_exists(colname text, tablename text);
CREATE OR REPLACE FUNCTION column_exists(colname text, tablename text)
RETURNS boolean AS 
$$
DECLARE
    q text;
    onerow record;
BEGIN

    q = 'SELECT attname FROM pg_attribute WHERE attrelid = ( SELECT oid FROM pg_class WHERE relname = '''||tablename||''') AND attname = '''||colname||''''; 

    FOR onerow IN EXECUTE q LOOP
    RETURN true;
    END LOOP;

    RETURN false;
END;
$$ LANGUAGE plpgsql;

/* function adding tsvector column to table if it does not exists */
DROP FUNCTION IF EXISTS add_tsvector_column(colname text, tablename text);
CREATE OR REPLACE FUNCTION add_tsvector_column(colname text, tablename text)
RETURNS boolean AS
$$
DECLARE
    q text;
BEGIN
    IF NOT column_exists(colname, tablename) THEN
    q = 'ALTER TABLE ' || tablename || ' ADD COLUMN ' || colname || ' tsvector';
    EXECUTE q;
    RETURN true;
    ELSE
    q = 'UPDATE ' || tablename || ' SET ' || colname || '=NULL';
    EXECUTE q;
    RETURN false;
    END IF;
END;
$$ LANGUAGE plpgsql;

/* aggregate function that concatenates tsvectors */
CREATE OR REPLACE FUNCTION tsv_add(tsv1 tsvector, tsv2 tsvector)
RETURNS tsvector AS
$$
BEGIN
    RETURN tsv1 || tsv2;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS setup_aggregates();
CREATE OR REPLACE FUNCTION setup_aggregates() RETURNS boolean AS
$$
DECLARE
    onerow record;
BEGIN
    FOR onerow IN SELECT * FROM pg_proc WHERE proname = 'concat_tsvectors' AND proisagg LOOP
    DROP AGGREGATE concat_tsvectors(tsvector);
    END LOOP;
    CREATE AGGREGATE concat_tsvectors (
    BASETYPE = tsvector,
    SFUNC = tsv_add,
    STYPE = tsvector,
    INITCOND = ''
    );
    RETURN true;
END;
$$ LANGUAGE plpgsql;

SELECT setup_aggregates();

/* calculates text search vector for the individual thread row
DOES not include question body post, answers or comments */
DROP FUNCTION IF EXISTS get_thread_tsv(title text, tagnames text);
CREATE OR REPLACE FUNCTION get_thread_tsv(title text, tagnames text)
RETURNS tsvector AS
$$
BEGIN
    /* todo add weight depending on votes */
    RETURN  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(tagnames, '')), 'A');
END;
$$ LANGUAGE plpgsql;

/* calculates text seanch vector for the individual question row */
DROP FUNCTION IF EXISTS get_post_tsv(text text, post_type text);
CREATE FUNCTION get_post_tsv(text text, post_type text)
RETURNS tsvector AS
$$
BEGIN
    /* todo adjust weights to reflect votes */
    IF post_type='question' THEN
    RETURN setweight(to_tsvector('english', coalesce(text, '')), 'B');
    ELSIF post_type='answer' THEN
    /* todo reflect whether the answer acepted or not */
    RETURN setweight(to_tsvector('english', coalesce(text, '')), 'B');
    ELSIF post_type='comment' THEN
    RETURN setweight(to_tsvector('english', coalesce(text, '')), 'C');
    ELSE
    RETURN to_tsvector('');
    END IF;
END;
$$ LANGUAGE plpgsql;

/* calculates text search vector for the question body part by thread id
here we extract question title and the text by thread_id and then
calculate the text search vector. In the future question
title will be moved to the askbot_thread table and this function
will be simpler.
*/
DROP FUNCTION IF EXISTS get_thread_question_tsv(thread_id integer);
CREATE OR REPLACE FUNCTION get_thread_question_tsv(thread_id integer)
RETURNS tsvector AS
$$
DECLARE
    query text;
    onerow record;
BEGIN
    query = 'SELECT text FROM askbot_post WHERE thread_id=' || thread_id ||
        ' AND post_type=''question'' AND deleted=false';
    FOR onerow in EXECUTE query LOOP
    RETURN get_post_tsv(onerow.text, 'question');
    END LOOP;
    RETURN to_tsvector('');
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS ...
(more)
Near the soul's avatar
25
Near the soul
answered 2012-03-11 14:53:52 -0500, updated 2012-03-11 14:55:18 -0500
edit flag offensive 0 remove flag delete link

Comments

Hey, thanks, could you try please the version that I've pushed to the repository? I've left only two drop function statements and changed the arguments. My version works for psql 8.4.11.

Evgeny's avatar Evgeny (2012-03-11 23:15:35 -0500) edit

I try the new version from repository and works for me too (with Postgresql 9.1.3) ;)!

Near the soul's avatar Near the soul (2012-03-12 10:04:41 -0500) edit
add a comment see more comments
0

This is because of the newer version of postgres, I will try to find a fix for this today.

Right now you can do the following: take a look into file askbot/search/postgresql/thread_and_post_models_01162012.plsql, Find all instances of CREATE OR REPLACE FUNCTION and right before that line add: DROP FUNCTION IF EXISTS <funcname>; (where <funcname> is name of the function being created) then rerun the migrations.

With postgresql migrations run inside transactions, and if one fails - nothing bad happens, you are just stuck at a previous migration. In some other databases, where transactions are not supported, a broken migration can leave the database in an inconsistent state, so postgres is a good choice.

Please let me know if it helps. I will today test the same on postgres 8.3.

What OS distribution are you using?

Evgeny's avatar
13.2k
Evgeny
answered 2012-03-11 12:47:56 -0500, updated 2012-03-11 14:03:48 -0500
edit flag offensive 0 remove flag delete link

Comments

I'm using archlinux. I try to modify the file askbot/search/postgresql/thread_and_post_models_01162012.plsql but i get this error "django.db.utils.DatabaseError: syntax error at or near "IF" LINE 107: DROP FUNCTION get_thread_question_tsv IF EXISTS; CREATE OR R..."

Near the soul's avatar Near the soul (2012-03-11 13:47:24 -0500) edit

Sorry, it's DROP FUNCTION IF EXISTS funcname.

Evgeny's avatar Evgeny (2012-03-11 14:03:08 -0500) edit

Ok ;)! Whit the new change i get this error "django.db.utils.DatabaseError: cannot drop function tsv_add(tsvector,tsvector) because other objects depend on it DETAIL: function concat_tsvectors(tsvector) depends on function tsv_add(tsvector,tsvector) HINT: Use DROP ... CASCADE to drop the dependent objects too. "

Near the soul's avatar Near the soul (2012-03-11 14:24:04 -0500) edit

Don't drop this one then. Look for the ones that really conflict - apparently there is an issue with psql 9 with the replacement of functions where variable names change. There are just a few of them like that.

Evgeny's avatar Evgeny (2012-03-11 14:28:11 -0500) edit

When you find the solution - would you tell me which functions you needed to drop and I'll test that in the older version of postgres?

Evgeny's avatar Evgeny (2012-03-11 14:38:32 -0500) edit
add a comment see more comments