launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #21207
[Merge] lp:~wgrant/launchpad/postgresql-9.5-9.6 into lp:launchpad
William Grant has proposed merging lp:~wgrant/launchpad/postgresql-9.5-9.6 into lp:launchpad.
Commit message:
Update database schema for PostgreSQL 9.5 and 9.6.
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~wgrant/launchpad/postgresql-9.5-9.6/+merge/310767
Update database schema for PostgreSQL 9.5 and 9.6. 9.3 still works, and 9.4 probably does too but it's not in any supported Ubuntu release so I don't much care.
There are three classes of PostgreSQL change that required us to adapt:
- IS vs == operator precedence changed in 9.5.
- tsquery parsing now treats '<' as the start of a phrase search operator as of 9.6.
- A couple of system tables' columns changed in 9.4 and 9.6.
Check constraints are stored parsed, so changing history without a fresh patch is fine. But for functions we need to replace them entirely: http://paste.ubuntu.com/23475270/ is the effective diff to the original patches.
Patch 2209-21-4's creation of update_database_disk_utilization() failed
at application time, and wasn't depended on by any later patches, so
I've deleted that bit of history. For new databases the function won't
exist until 2209-81-0.
Text search query parsing has changed subtly: any '<' characters are
stripped ('<' begins 9.6's new phrase operator), meaning that terms such
as XML tags are no longer ignored. But such searches were already pretty
broken, and this arguably makes them slightly better.
--
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/postgresql-9.5-9.6 into lp:launchpad.
=== modified file 'database/schema/patch-2209-11-1.sql'
--- database/schema/patch-2209-11-1.sql 2012-02-16 09:45:47 +0000
+++ database/schema/patch-2209-11-1.sql 2016-11-14 12:11:11 +0000
@@ -17,7 +17,7 @@
product integer REFERENCES product,
distribution integer REFERENCES distribution,
type integer NOT NULL,
- CONSTRAINT has_target CHECK (product IS NULL != distribution IS NULL)
+ CONSTRAINT has_target CHECK ((product IS NULL) != (distribution IS NULL))
);
CREATE UNIQUE INDEX accesspolicy__product__type__key
@@ -29,7 +29,7 @@
id serial PRIMARY KEY,
bug integer REFERENCES bug,
branch integer, -- FK to be added later.
- CONSTRAINT has_artifact CHECK (bug IS NULL != branch IS NULL)
+ CONSTRAINT has_artifact CHECK ((bug IS NULL) != (branch IS NULL))
);
CREATE UNIQUE INDEX accessartifact__bug__key
=== modified file 'database/schema/patch-2209-21-4.sql'
--- database/schema/patch-2209-21-4.sql 2012-06-01 12:02:00 +0000
+++ database/schema/patch-2209-21-4.sql 2016-11-14 12:11:11 +0000
@@ -1,110 +1,8 @@
-CREATE OR REPLACE FUNCTION update_database_disk_utilization() RETURNS void
- LANGUAGE sql SECURITY DEFINER
- SET search_path TO public
- AS $$
- INSERT INTO DatabaseDiskUtilization
- SELECT
- CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
- namespace, name,
- sub_namespace, sub_name,
- kind,
- (namespace || '.' || name || COALESCE(
- '/' || sub_namespace || '.' || sub_name, '')) AS sort,
- (stat).table_len,
- (stat).tuple_count,
- (stat).tuple_len,
- (stat).tuple_percent,
- (stat).dead_tuple_count,
- (stat).dead_tuple_len,
- (stat).dead_tuple_percent,
- (stat).free_space,
- (stat).free_percent
- FROM (
- -- Tables
- SELECT
- pg_namespace.nspname AS namespace,
- pg_class.relname AS name,
- NULL AS sub_namespace,
- NULL AS sub_name,
- pg_class.relkind AS kind,
- pgstattuple(pg_class.oid) AS stat
- FROM pg_class, pg_namespace
- WHERE
- pg_class.relnamespace = pg_namespace.oid
- AND pg_class.relkind = 'r'
- AND pg_table_is_visible(pg_class.oid)
-
- UNION ALL
-
- -- Indexes
- SELECT
- pg_namespace_table.nspname AS namespace,
- pg_class_table.relname AS name,
- pg_namespace_index.nspname AS sub_namespace,
- pg_class_index.relname AS sub_name,
- pg_class_index.relkind AS kind,
- pgstattuple(pg_class_index.oid) AS stat
- FROM
- pg_namespace AS pg_namespace_table,
- pg_namespace AS pg_namespace_index,
- pg_class AS pg_class_table,
- pg_class AS pg_class_index,
- pg_index,
- pg_am
- WHERE
- pg_class_index.relkind = 'i'
- AND pg_am.amname <> 'gin' -- pgstattuple doesn't support GIN
- AND pg_table_is_visible(pg_class_table.oid)
- AND pg_class_index.relnamespace = pg_namespace_index.oid
- AND pg_class_table.relnamespace = pg_namespace_table.oid
- AND pg_class_index.relam = pg_am.oid
- AND pg_index.indexrelid = pg_class_index.oid
- AND pg_index.indrelid = pg_class_table.oid
-
- UNION ALL
-
- -- TOAST tables
- SELECT
- pg_namespace_table.nspname AS namespace,
- pg_class_table.relname AS name,
- pg_namespace_toast.nspname AS sub_namespace,
- pg_class_toast.relname AS sub_name,
- pg_class_toast.relkind AS kind,
- pgstattuple(pg_class_toast.oid) AS stat
- FROM
- pg_namespace AS pg_namespace_table,
- pg_namespace AS pg_namespace_toast,
- pg_class AS pg_class_table,
- pg_class AS pg_class_toast
- WHERE
- pg_class_toast.relnamespace = pg_namespace_toast.oid
- AND pg_table_is_visible(pg_class_table.oid)
- AND pg_class_table.relnamespace = pg_namespace_table.oid
- AND pg_class_toast.oid = pg_class_table.reltoastrelid
-
- UNION ALL
-
- -- TOAST indexes
- SELECT
- pg_namespace_table.nspname AS namespace,
- pg_class_table.relname AS name,
- pg_namespace_index.nspname AS sub_namespace,
- pg_class_index.relname AS sub_name,
- pg_class_index.relkind AS kind,
- pgstattuple(pg_class_index.oid) AS stat
- FROM
- pg_namespace AS pg_namespace_table,
- pg_namespace AS pg_namespace_index,
- pg_class AS pg_class_table,
- pg_class AS pg_class_index,
- pg_class AS pg_class_toast
- WHERE
- pg_class_table.relnamespace = pg_namespace_table.oid
- AND pg_table_is_visible(pg_class_table.oid)
- AND pg_class_index.relnamespace = pg_namespace_index.oid
- AND pg_class_table.reltoastrelid = pg_class_toast.oid
- AND pg_class_index.oid = pg_class_toast.reltoastidxid
- ) AS whatever;
-$$;
+SET client_min_messages = ERROR;
+
+-- This patch originally added update_database_disk_utilization(), but
+-- it failed to apply on PostgreSQL >=9.5. It has been replaced with a
+-- more compatible version in 2209-81-0. Deleting history is gross, but
+-- we can get away without properly altering it.
INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 21, 4);
=== modified file 'database/schema/patch-2209-30-1.sql'
--- database/schema/patch-2209-30-1.sql 2012-08-23 23:51:58 +0000
+++ database/schema/patch-2209-30-1.sql 2016-11-14 12:11:11 +0000
@@ -12,7 +12,7 @@
-- If type is set, then either product or distribution must be set and person must be null.
ALTER TABLE accesspolicy ADD CONSTRAINT has_target
CHECK (
- (type IS NOT NULL AND (product IS NULL <> distribution IS NULL) AND person IS NULL)
+ (type IS NOT NULL AND ((product IS NULL) <> (distribution IS NULL)) AND person IS NULL)
OR
(type IS NULL AND person IS NOT NULL and product IS NULL AND distribution IS NULL) );
=== added file 'database/schema/patch-2209-81-0.sql'
--- database/schema/patch-2209-81-0.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-81-0.sql 2016-11-14 12:11:11 +0000
@@ -0,0 +1,346 @@
+SET client_min_messages = ERROR;
+
+-- Update functions for PostgreSQL 9.5 and 9.6 support, in addition to 9.3.
+
+
+-- From 2209-00-0
+CREATE OR REPLACE FUNCTION update_branch_name_cache() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ needs_update boolean := FALSE;
+BEGIN
+ IF TG_OP = 'INSERT' THEN
+ needs_update := TRUE;
+ ELSIF (NEW.owner_name IS NULL
+ OR NEW.unique_name IS NULL
+ OR OLD.owner_name <> NEW.owner_name
+ OR OLD.unique_name <> NEW.unique_name
+ OR ((NEW.target_suffix IS NULL) <> (OLD.target_suffix IS NULL))
+ OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
+ OR OLD.name <> NEW.name
+ OR OLD.owner <> NEW.owner
+ OR COALESCE(OLD.product, -1) <> COALESCE(NEW.product, -1)
+ OR COALESCE(OLD.distroseries, -1) <> COALESCE(NEW.distroseries, -1)
+ OR COALESCE(OLD.sourcepackagename, -1)
+ <> COALESCE(NEW.sourcepackagename, -1)) THEN
+ needs_update := TRUE;
+ END IF;
+
+ IF needs_update THEN
+ SELECT
+ Person.name AS owner_name,
+ COALESCE(Product.name, SPN.name) AS target_suffix,
+ '~' || Person.name || '/' || COALESCE(
+ Product.name,
+ Distribution.name || '/' || Distroseries.name
+ || '/' || SPN.name,
+ '+junk') || '/' || NEW.name AS unique_name
+ INTO NEW.owner_name, NEW.target_suffix, NEW.unique_name
+ FROM Person
+ LEFT OUTER JOIN DistroSeries ON NEW.distroseries = DistroSeries.id
+ LEFT OUTER JOIN Product ON NEW.product = Product.id
+ LEFT OUTER JOIN Distribution
+ ON Distroseries.distribution = Distribution.id
+ LEFT OUTER JOIN SourcepackageName AS SPN
+ ON SPN.id = NEW.sourcepackagename
+ WHERE Person.id = NEW.owner;
+ END IF;
+
+ RETURN NEW;
+END;
+$$;
+
+
+-- From 2209-21-4
+CREATE OR REPLACE FUNCTION update_database_disk_utilization() RETURNS void
+ LANGUAGE sql SECURITY DEFINER
+ SET search_path TO public
+ AS $$
+ INSERT INTO DatabaseDiskUtilization
+ SELECT
+ CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
+ namespace, name,
+ sub_namespace, sub_name,
+ kind,
+ (namespace || '.' || name || COALESCE(
+ '/' || sub_namespace || '.' || sub_name, '')) AS sort,
+ (stat).table_len,
+ (stat).tuple_count,
+ (stat).tuple_len,
+ (stat).tuple_percent,
+ (stat).dead_tuple_count,
+ (stat).dead_tuple_len,
+ (stat).dead_tuple_percent,
+ (stat).free_space,
+ (stat).free_percent
+ FROM (
+ -- Tables
+ SELECT
+ pg_namespace.nspname AS namespace,
+ pg_class.relname AS name,
+ NULL AS sub_namespace,
+ NULL AS sub_name,
+ pg_class.relkind AS kind,
+ pgstattuple(pg_class.oid) AS stat
+ FROM pg_class, pg_namespace
+ WHERE
+ pg_class.relnamespace = pg_namespace.oid
+ AND pg_class.relkind = 'r'
+ AND pg_table_is_visible(pg_class.oid)
+
+ UNION ALL
+
+ -- Indexes
+ SELECT
+ pg_namespace_table.nspname AS namespace,
+ pg_class_table.relname AS name,
+ pg_namespace_index.nspname AS sub_namespace,
+ pg_class_index.relname AS sub_name,
+ pg_class_index.relkind AS kind,
+ pgstattuple(pg_class_index.oid) AS stat
+ FROM
+ pg_namespace AS pg_namespace_table,
+ pg_namespace AS pg_namespace_index,
+ pg_class AS pg_class_table,
+ pg_class AS pg_class_index,
+ pg_index,
+ pg_am
+ WHERE
+ pg_class_index.relkind = 'i'
+ AND pg_am.amname <> 'gin' -- pgstattuple doesn't support GIN
+ AND pg_table_is_visible(pg_class_table.oid)
+ AND pg_class_index.relnamespace = pg_namespace_index.oid
+ AND pg_class_table.relnamespace = pg_namespace_table.oid
+ AND pg_class_index.relam = pg_am.oid
+ AND pg_index.indexrelid = pg_class_index.oid
+ AND pg_index.indrelid = pg_class_table.oid
+
+ UNION ALL
+
+ -- TOAST tables
+ SELECT
+ pg_namespace_table.nspname AS namespace,
+ pg_class_table.relname AS name,
+ pg_namespace_toast.nspname AS sub_namespace,
+ pg_class_toast.relname AS sub_name,
+ pg_class_toast.relkind AS kind,
+ pgstattuple(pg_class_toast.oid) AS stat
+ FROM
+ pg_namespace AS pg_namespace_table,
+ pg_namespace AS pg_namespace_toast,
+ pg_class AS pg_class_table,
+ pg_class AS pg_class_toast
+ WHERE
+ pg_class_toast.relnamespace = pg_namespace_toast.oid
+ AND pg_table_is_visible(pg_class_table.oid)
+ AND pg_class_table.relnamespace = pg_namespace_table.oid
+ AND pg_class_toast.oid = pg_class_table.reltoastrelid
+
+ UNION ALL
+
+ -- TOAST indexes
+ SELECT
+ pg_namespace_table.nspname AS namespace,
+ pg_class_table.relname AS name,
+ pg_namespace_index.nspname AS sub_namespace,
+ pg_class_index.relname AS sub_name,
+ pg_class_index.relkind AS kind,
+ pgstattuple(pg_class_index.oid) AS stat
+ FROM
+ pg_namespace AS pg_namespace_table,
+ pg_namespace AS pg_namespace_index,
+ pg_class AS pg_class_table,
+ pg_class AS pg_class_index,
+ pg_class AS pg_class_toast,
+ pg_index
+ WHERE
+ pg_class_table.relnamespace = pg_namespace_table.oid
+ AND pg_table_is_visible(pg_class_table.oid)
+ AND pg_class_index.relnamespace = pg_namespace_index.oid
+ AND pg_class_table.reltoastrelid = pg_class_toast.oid
+ AND pg_class_index.oid = pg_index.indexrelid
+ AND pg_index.indrelid = pg_class_toast.oid
+ ) AS whatever;
+$$;
+
+
+-- From 2209-24-3
+CREATE OR REPLACE FUNCTION _ftq(text) RETURNS text
+ LANGUAGE plpythonu IMMUTABLE STRICT
+ AS $_$
+ import re
+
+ # I think this method would be more robust if we used a real
+ # tokenizer and parser to generate the query string, but we need
+ # something suitable for use as a stored procedure which currently
+ # means no external dependancies.
+
+ # Convert to Unicode
+ query = args[0].decode('utf8')
+ ## plpy.debug('1 query is %s' % repr(query))
+
+ # Replace tsquery operators with ' '. '<' begins all the phrase
+ # search operators, and a standalone '>' is fine.
+ query = re.sub('[|&!<]', ' ', query)
+
+ # Normalize whitespace
+ query = re.sub("(?u)\s+"," ", query)
+
+ # Convert AND, OR, NOT to tsearch2 punctuation
+ query = re.sub(r"(?u)\bAND\b", "&", query)
+ query = re.sub(r"(?u)\bOR\b", "|", query)
+ query = re.sub(r"(?u)\bNOT\b", " !", query)
+ ## plpy.debug('2 query is %s' % repr(query))
+
+ # Deal with unwanted punctuation.
+ # ':' is used in queries to specify a weight of a word.
+ # '\' is treated differently in to_tsvector() and to_tsquery().
+ punctuation = r'[:\\]'
+ query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
+ ## plpy.debug('3 query is %s' % repr(query))
+
+ # Now that we have handle case sensitive booleans, convert to lowercase
+ query = query.lower()
+
+ # Remove unpartnered bracket on the left and right
+ query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
+ query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
+
+ # Remove spurious brackets
+ query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
+ ## plpy.debug('5 query is %s' % repr(query))
+
+ # Insert & between tokens without an existing boolean operator
+ # ( not proceeded by (|&!
+ query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
+ ## plpy.debug('6 query is %s' % repr(query))
+ # ) not followed by )|&
+ query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
+ ## plpy.debug('6.1 query is %s' % repr(query))
+ # Whitespace not proceded by (|&! not followed by &|
+ query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
+ ## plpy.debug('7 query is %s' % repr(query))
+
+ # Detect and repair syntax errors - we are lenient because
+ # this input is generally from users.
+
+ # Fix unbalanced brackets
+ openings = query.count("(")
+ closings = query.count(")")
+ if openings > closings:
+ query = query + " ) "*(openings-closings)
+ elif closings > openings:
+ query = " ( "*(closings-openings) + query
+ ## plpy.debug('8 query is %s' % repr(query))
+
+ # Strip ' character that do not have letters on both sides
+ query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
+
+ # Brackets containing nothing but whitespace and booleans, recursive
+ last = ""
+ while last != query:
+ last = query
+ query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
+ ## plpy.debug('9 query is %s' % repr(query))
+
+ # An & or | following a (
+ query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
+ ## plpy.debug('10 query is %s' % repr(query))
+
+ # An &, | or ! immediatly before a )
+ query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
+ ## plpy.debug('11 query is %s' % repr(query))
+
+ # An &,| or ! followed by another boolean.
+ query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
+ ## plpy.debug('12 query is %s' % repr(query))
+
+ # Leading & or |
+ query = re.sub(r"(?u)^[\s\&\|]+", "", query)
+ ## plpy.debug('13 query is %s' % repr(query))
+
+ # Trailing &, | or !
+ query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
+ ## plpy.debug('14 query is %s' % repr(query))
+
+ # If we have nothing but whitespace and tsearch2 operators,
+ # return NULL.
+ if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
+ return None
+
+ # Convert back to UTF-8
+ query = query.encode('utf8')
+ ## plpy.debug('15 query is %s' % repr(query))
+
+ return query or None
+ $_$;
+
+
+-- From 2209-53-1
+CREATE OR REPLACE FUNCTION activity()
+RETURNS SETOF pg_stat_activity
+VOLATILE SECURITY DEFINER SET search_path = public
+LANGUAGE plpgsql AS $$
+DECLARE
+ a pg_stat_activity%ROWTYPE;
+BEGIN
+ IF EXISTS (
+ SELECT 1 FROM pg_attribute WHERE
+ attrelid =
+ (SELECT oid FROM pg_class
+ WHERE relname = 'pg_stat_activity')
+ AND attname = 'wait_event_type') THEN
+ -- >= 9.6
+ RETURN QUERY SELECT
+ datid, datname, pid, usesysid, usename, application_name,
+ client_addr, client_hostname, client_port, backend_start,
+ xact_start, query_start, state_change, wait_event_type,
+ wait_event, state, backend_xid, backend_xmin,
+ CASE
+ WHEN query LIKE '<IDLE>%'
+ OR query LIKE 'autovacuum:%'
+ THEN query
+ ELSE
+ '<HIDDEN>'
+ END AS query
+ FROM pg_catalog.pg_stat_activity;
+ ELSIF EXISTS (
+ SELECT 1 FROM pg_attribute WHERE
+ attrelid =
+ (SELECT oid FROM pg_class
+ WHERE relname = 'pg_stat_activity')
+ AND attname = 'backend_xid') THEN
+ -- >= 9.4
+ RETURN QUERY SELECT
+ datid, datname, pid, usesysid, usename, application_name,
+ client_addr, client_hostname, client_port, backend_start,
+ xact_start, query_start, state_change, waiting, state,
+ backend_xid, backend_xmin,
+ CASE
+ WHEN query LIKE '<IDLE>%'
+ OR query LIKE 'autovacuum:%'
+ THEN query
+ ELSE
+ '<HIDDEN>'
+ END AS query
+ FROM pg_catalog.pg_stat_activity;
+ ELSE
+ -- >= 9.2; anything older is unsupported
+ RETURN QUERY SELECT
+ datid, datname, pid, usesysid, usename, application_name,
+ client_addr, client_hostname, client_port, backend_start,
+ xact_start, query_start, state_change, waiting, state,
+ CASE
+ WHEN query LIKE '<IDLE>%'
+ OR query LIKE 'autovacuum:%'
+ THEN query
+ ELSE
+ '<HIDDEN>'
+ END AS query
+ FROM pg_catalog.pg_stat_activity;
+ END IF;
+END;
+$$;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 81, 0);
=== modified file 'lib/lp/services/database/doc/textsearching.txt'
--- lib/lp/services/database/doc/textsearching.txt 2016-01-26 15:47:37 +0000
+++ lib/lp/services/database/doc/textsearching.txt 2016-11-14 12:11:11 +0000
@@ -340,14 +340,12 @@
XXX Abel Deuring 2012-06-20 bug=1015519: XML tags cannot be searched.
- >>> print search_same('foo <bar> baz')
- FTI data: 'baz':2 'foo':1 query: 'foo' & 'baz' match: True
-
-More specifically, tags are simply dropped from the FTI data and from
-search queries.
+Tags are simply dropped from the FTI data. The terms show up without
+brackets in parsed queries as a consequence of phrase operator stripping
+added for PostgreSQL 9.6.
>>> print search('some text <div>whatever</div>', '<div>')
- FTI data: 'text':2 'whatev':3 query: None match: None
+ FTI data: 'text':2 'whatev':3 query: 'div' match: False
Of course, omitting '<' and '>'from the query does not help.
=== modified file 'utilities/launchpad-database-setup'
--- utilities/launchpad-database-setup 2014-12-20 16:38:50 +0000
+++ utilities/launchpad-database-setup 2016-11-14 12:11:11 +0000
@@ -18,7 +18,7 @@
# https://dev.launchpad.net/DatabaseSetup which are intended for
# initial Launchpad setup on an otherwise unconfigured postgresql instance
-for pgversion in 9.1 9.3
+for pgversion in 9.3 9.5 9.6
do
sudo grep -qs "^auto" /etc/postgresql/$pgversion/main/start.conf
if [ $? -eq 0 ]; then
Follow ups
