From: hernani Date: Mon, 19 Apr 2010 14:53:36 +0000 (+0000) Subject: improved FTS. make fts indexes update faster and take the load out of the node table. X-Git-Tag: live~1018 X-Git-Url: https://git.openstreetmap.org./osqa.git/commitdiff_plain/b484fb008ae574dd4ef10a12d4e530ab96743acc improved FTS. make fts indexes update faster and take the load out of the node table. git-svn-id: http://svn.osqa.net/svnroot/osqa/trunk@56 0cfe37f9-358a-4d5e-be75-b63607b5c754 --- diff --git a/forum/migrations/0021_create_new_pg_fts_triggers.py b/forum/migrations/0021_create_new_pg_fts_triggers.py index bb5e04f..16ef25b 100644 --- a/forum/migrations/0021_create_new_pg_fts_triggers.py +++ b/forum/migrations/0021_create_new_pg_fts_triggers.py @@ -12,7 +12,6 @@ class Migration(SchemaMigration): try: import forum_modules.pgfulltext.management except Exception, e: - print e pass diff --git a/forum/settings/__init__.py b/forum/settings/__init__.py index e7f3e39..2446496 100644 --- a/forum/settings/__init__.py +++ b/forum/settings/__init__.py @@ -5,7 +5,7 @@ from forms import ImageFormWidget from django.forms.widgets import Textarea from django.utils.translation import ugettext_lazy as _ -INTERNAL_VERSION = Setting('INTERNAL_VERSION', "201003270") +INTERNAL_VERSION = Setting('INTERNAL_VERSION', "56") SETTINGS_PACK = Setting('SETTINGS_PACK', "default") from basic import * diff --git a/forum/startup.py b/forum/startup.py index d8d0cd5..71e3347 100644 --- a/forum/startup.py +++ b/forum/startup.py @@ -5,4 +5,5 @@ import forum.subscriptions from forum.modules import get_modules_script -get_modules_script('settings') \ No newline at end of file +get_modules_script('settings') +get_modules_script('startup') \ No newline at end of file diff --git a/forum_modules/pgfulltext/management.py b/forum_modules/pgfulltext/management.py deleted file mode 100644 index 60baecd..0000000 --- a/forum_modules/pgfulltext/management.py +++ /dev/null @@ -1,31 +0,0 @@ -import os - -from django.db import connection, transaction -from django.conf import settings - -import forum.models - -def install_pg_fts(**kwargs): - f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r') - - try: - cursor = connection.cursor() - cursor.execute(f.read()) - transaction.commit_unless_managed() - except: - pass - finally: - cursor.close() - - f.close() - -if settings.DATABASE_ENGINE in ('postgresql_psycopg2', 'postgresql', ): - from django.db.models.signals import post_syncdb - post_syncdb.connect(install_pg_fts, sender=forum.models, weak=False) - - -try: - from south.signals import post_migrate - post_migrate.connect(install_pg_fts, weak=False) -except: - pass \ No newline at end of file diff --git a/forum_modules/pgfulltext/pg_fts_install.sql b/forum_modules/pgfulltext/pg_fts_install.sql index 21cbf95..9ac5db9 100644 --- a/forum_modules/pgfulltext/pg_fts_install.sql +++ b/forum_modules/pgfulltext/pg_fts_install.sql @@ -21,64 +21,66 @@ CREATE OR REPLACE FUNCTION node_ranking(node_id int, srch text) RETURNS float AS $$ declare v tsvector; + cv tsvector; + rev_id int; + child_count int; + r record; begin - SELECT tsv INTO v FROM forum_node WHERE id = node_id; - RETURN ts_rank_cd(v || children_tsv(node_id), plainto_tsquery(srch), 32); + SELECT active_revision_id INTO rev_id FROM forum_node WHERE id = node_id; + SELECT tsv INTO v FROM forum_noderevision WHERE id = rev_id; + + SELECT count(*) INTO child_count FROM forum_node WHERE abs_parent_id = node_id AND NOT deleted; + + IF child_count > 0 THEN + FOR r in SELECT * FROM forum_node WHERE abs_parent_id = node_id AND NOT deleted LOOP + SELECT tsv INTO cv FROM forum_noderevision WHERE id = r.active_revision_id; + v :=(v || cv); + END LOOP; + END IF; + RAISE NOTICE '%', v; + + RETURN ts_rank_cd(v, plainto_tsquery('english', srch), 32); end $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION children_tsv(id int) RETURNS tsvector AS $$ - declare - v tsvector := ''::tsvector; - r record; - begin - FOR r IN SELECT * FROM forum_node WHERE parent_id = id LOOP - v := v || r.tsv || children_tsv(r.id); - END LOOP; - RETURN v; - end - $$ LANGUAGE plpgsql; +select node_ranking(50, 'free'); + + CREATE OR REPLACE FUNCTION set_node_tsv() RETURNS TRIGGER AS $$ begin - IF (tg_op = 'INSERT') THEN new.tsv := setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || setweight(to_tsvector('english', coalesce(new.body,'')), 'C'); - ELSIF (new.active_revision_id <> old.active_revision_id) OR (new.tsv IS NULL) THEN - new.tsv := - setweight(to_tsvector('english', coalesce(new.tagnames,'')), 'A') || - setweight(to_tsvector('english', coalesce(new.title,'')), 'B') || - setweight(to_tsvector('english', coalesce(new.body,'')), 'C'); - END IF; + RETURN new; end $$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION public.create_tsv_node_column () + CREATE OR REPLACE FUNCTION public.create_tsv_noderevision_column () RETURNS TEXT AS $$ - ALTER TABLE forum_node ADD COLUMN tsv tsvector; + ALTER TABLE forum_noderevision ADD COLUMN tsv tsvector; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE - ON forum_node FOR EACH ROW EXECUTE PROCEDURE set_node_tsv(); + ON forum_noderevision FOR EACH ROW EXECUTE PROCEDURE set_node_tsv(); - CREATE INDEX node_tsv ON forum_node USING gin(tsv); + CREATE INDEX noderevision_tsv ON forum_noderevision USING gin(tsv); SELECT 'tsv column created'::TEXT; $$ LANGUAGE 'sql'; SELECT CASE WHEN - (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_node') AND attname = 'tsv') + (SELECT true::BOOLEAN FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'forum_noderevision') AND attname = 'tsv') THEN (SELECT 'Tsv column already exists'::TEXT) ELSE - (SELECT public.create_tsv_node_column()) + (SELECT public.create_tsv_noderevision_column()) END; - DROP FUNCTION public.create_tsv_node_column (); + DROP FUNCTION public.create_tsv_noderevision_column(); - UPDATE forum_node SET id=id WHERE TRUE; + UPDATE forum_noderevision SET id=id WHERE TRUE; \ No newline at end of file diff --git a/forum_modules/pgfulltext/startup.py b/forum_modules/pgfulltext/startup.py new file mode 100644 index 0000000..14eea8e --- /dev/null +++ b/forum_modules/pgfulltext/startup.py @@ -0,0 +1,38 @@ +import os +from forum.models import KeyValue +from django.db import connection, transaction + +KEY = 'PG_FTSTRIGGERS_VERSION' +VERSION = 2 +install = False + +try: + version = KeyValue.objects.get(key=KEY).value + if version < VERSION: + install = True +except: + install = True + + +if install: + f = open(os.path.join(os.path.dirname(__file__), 'pg_fts_install.sql'), 'r') + + try: + cursor = connection.cursor() + cursor.execute(f.read()) + transaction.commit_unless_managed() + except: + pass + finally: + cursor.close() + + f.close() + + try: + kv = KeyValue.objects.get(key=KEY) + except: + kv = KeyValue(key=KEY) + + kv.value = VERSION + kv.save() + \ No newline at end of file