Merge ~cjwatson/launchpad:db-plpython3 into launchpad:db-devel

Proposed by Colin Watson
Status: Merged
Approved by: Colin Watson
Approved revision: 519d66f3aae79cc1d7dc66ef27a8d72b79872308
Merge reported by: Otto Co-Pilot
Merged at revision: not available
Proposed branch: ~cjwatson/launchpad:db-plpython3
Merge into: launchpad:db-devel
Diff against target: 771 lines (+765/-0)
1 file modified
database/schema/patch-2210-46-0.sql (+765/-0)
Reviewer Review Type Date Requested Status
William Grant db Approve
Review via email: mp+418572@code.launchpad.net

Commit message

Replace PL/Python 2 functions using PL/Python 3

Description of the change

This is a prerequisite for bootstrapping Launchpad on Ubuntu 20.04, where PL/Python 2 is unavailable.

The effective diff introduced by this commit is as follows: https://paste.ubuntu.com/p/Q8wWqxG5dn/

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) :
review: Approve (db)
Revision history for this message
Colin Watson (cjwatson) :

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2210-46-0.sql b/database/schema/patch-2210-46-0.sql
2new file mode 100644
3index 0000000..00039ea
4--- /dev/null
5+++ b/database/schema/patch-2210-46-0.sql
6@@ -0,0 +1,765 @@
7+-- Copyright 2021 Canonical Ltd. This software is licensed under the
8+-- GNU Affero General Public License version 3 (see the file LICENSE).
9+
10+SET client_min_messages=ERROR;
11+
12+-- Replace all PL/Python2 functions with PL/Python3 equivalents.
13+
14+CREATE EXTENSION IF NOT EXISTS plpython3u WITH SCHEMA pg_catalog;
15+
16+COMMENT ON EXTENSION plpython3u IS 'PL/Python3U untrusted procedural language';
17+
18+CREATE OR REPLACE FUNCTION _ftq(text) RETURNS text
19+ LANGUAGE plpython3u IMMUTABLE STRICT
20+ AS $_$
21+ import re
22+
23+ # I think this method would be more robust if we used a real
24+ # tokenizer and parser to generate the query string, but we need
25+ # something suitable for use as a stored procedure which currently
26+ # means no external dependencies.
27+
28+ query = args[0]
29+ ## plpy.debug('1 query is %s' % repr(query))
30+
31+ # Replace tsquery operators with ' '. '<' begins all the phrase
32+ # search operators, and a standalone '>' is fine.
33+ query = re.sub('[|&!<]', ' ', query)
34+
35+ # Normalize whitespace
36+ query = re.sub("\s+"," ", query)
37+
38+ # Convert AND, OR, NOT to tsearch2 punctuation
39+ query = re.sub(r"\bAND\b", "&", query)
40+ query = re.sub(r"\bOR\b", "|", query)
41+ query = re.sub(r"\bNOT\b", " !", query)
42+ ## plpy.debug('2 query is %s' % repr(query))
43+
44+ # Deal with unwanted punctuation.
45+ # ':' is used in queries to specify a weight of a word.
46+ # '\' is treated differently in to_tsvector() and to_tsquery().
47+ punctuation = r'[:\\]'
48+ query = re.sub(r"%s+" % (punctuation,), " ", query)
49+ ## plpy.debug('3 query is %s' % repr(query))
50+
51+ # Now that we have handle case sensitive booleans, convert to lowercase
52+ query = query.lower()
53+
54+ # Remove unpartnered bracket on the left and right
55+ query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
56+ query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
57+
58+ # Remove spurious brackets
59+ query = re.sub(r"\(([^\&\|]*?)\)", r" \1 ", query)
60+ ## plpy.debug('5 query is %s' % repr(query))
61+
62+ # Insert & between tokens without an existing boolean operator
63+ # ( not proceeded by (|&!
64+ query = re.sub(r"(?<![\(\|\&\!])\s*\(", "&(", query)
65+ ## plpy.debug('6 query is %s' % repr(query))
66+ # ) not followed by )|&
67+ query = re.sub(r"\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
68+ ## plpy.debug('6.1 query is %s' % repr(query))
69+ # Whitespace not proceded by (|&! not followed by &|
70+ query = re.sub(r"(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
71+ ## plpy.debug('7 query is %s' % repr(query))
72+
73+ # Detect and repair syntax errors - we are lenient because
74+ # this input is generally from users.
75+
76+ # Fix unbalanced brackets
77+ openings = query.count("(")
78+ closings = query.count(")")
79+ if openings > closings:
80+ query = query + " ) "*(openings-closings)
81+ elif closings > openings:
82+ query = " ( "*(closings-openings) + query
83+ ## plpy.debug('8 query is %s' % repr(query))
84+
85+ # Strip ' character that do not have letters on both sides
86+ query = re.sub(r"((?<!\w)'|'(?!\w))", "", query)
87+
88+ # Brackets containing nothing but whitespace and booleans, recursive
89+ last = ""
90+ while last != query:
91+ last = query
92+ query = re.sub(r"\([\s\&\|\!]*\)", "", query)
93+ ## plpy.debug('9 query is %s' % repr(query))
94+
95+ # An & or | following a (
96+ query = re.sub(r"(?<=\()[\&\|\s]+", "", query)
97+ ## plpy.debug('10 query is %s' % repr(query))
98+
99+ # An &, | or ! immediatly before a )
100+ query = re.sub(r"[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
101+ ## plpy.debug('11 query is %s' % repr(query))
102+
103+ # An &,| or ! followed by another boolean.
104+ query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
105+ ## plpy.debug('12 query is %s' % repr(query))
106+
107+ # Leading & or |
108+ query = re.sub(r"^[\s\&\|]+", "", query)
109+ ## plpy.debug('13 query is %s' % repr(query))
110+
111+ # Trailing &, | or !
112+ query = re.sub(r"[\&\|\!\s]+$", "", query)
113+ ## plpy.debug('14 query is %s' % repr(query))
114+
115+ # If we have nothing but whitespace and tsearch2 operators,
116+ # return NULL.
117+ if re.search(r"^[\&\|\!\s\(\)]*$", query) is not None:
118+ return None
119+
120+ ## plpy.debug('15 query is %s' % repr(query))
121+
122+ return query or None
123+ $_$;
124+
125+CREATE OR REPLACE FUNCTION assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean
126+ LANGUAGE plpython3u STABLE
127+ AS $$
128+ rv = plpy.execute("""
129+ SELECT * FROM LaunchpadDatabaseRevision
130+ WHERE major=%d AND minor=%d AND patch=%d
131+ """ % (major, minor, patch))
132+ if len(rv) == 0:
133+ raise Exception(
134+ 'patch-%d-%02d-%d not applied.' % (major, minor, patch))
135+ else:
136+ return True
137+$$;
138+
139+CREATE OR REPLACE FUNCTION valid_bug_name(text) RETURNS boolean
140+ LANGUAGE plpython3u IMMUTABLE STRICT
141+ AS $_$
142+ import re
143+ name = args[0]
144+ pat = r"^[a-z][a-z0-9+\.\-]+$"
145+ if re.match(pat, name):
146+ return 1
147+ return 0
148+$_$;
149+
150+CREATE OR REPLACE FUNCTION valid_name(text) RETURNS boolean
151+ LANGUAGE plpython3u IMMUTABLE STRICT
152+ AS $$
153+ import re
154+ name = args[0]
155+ pat = r"^[a-z0-9][a-z0-9\+\.\-]*\Z"
156+ if re.match(pat, name):
157+ return 1
158+ return 0
159+$$;
160+
161+CREATE OR REPLACE FUNCTION debversion_sort_key(version text) RETURNS text
162+ LANGUAGE plpython3u IMMUTABLE STRICT
163+ AS $_$
164+ # If this method is altered, then any functional indexes using it
165+ # need to be rebuilt.
166+ import re
167+
168+ VERRE = re.compile("(?:([0-9]+):)?(.+?)(?:-([^-]+))?$")
169+
170+ MAP = "0123456789ABCDEFGHIJKLMNOPQRSTUV"
171+
172+ epoch, version, release = VERRE.match(args[0]).groups()
173+ key = []
174+ for part, part_weight in ((epoch, 3000), (version, 2000), (release, 1000)):
175+ if not part:
176+ continue
177+ i = 0
178+ l = len(part)
179+ while i != l:
180+ c = part[i]
181+ if c.isdigit():
182+ key.append(part_weight)
183+ j = i
184+ while i != l and part[i].isdigit(): i += 1
185+ key.append(part_weight+int(part[j:i] or "0"))
186+ elif c == "~":
187+ key.append(0)
188+ i += 1
189+ elif c.isalpha():
190+ key.append(part_weight+ord(c))
191+ i += 1
192+ else:
193+ key.append(part_weight+256+ord(c))
194+ i += 1
195+ if not key or key[-1] != part_weight:
196+ key.append(part_weight)
197+ key.append(part_weight)
198+ key.append(1)
199+
200+ # Encode our key and return it
201+ #
202+ result = []
203+ for value in key:
204+ if not value:
205+ result.append("000")
206+ else:
207+ element = []
208+ while value:
209+ element.insert(0, MAP[value & 0x1F])
210+ value >>= 5
211+ element_len = len(element)
212+ if element_len < 3:
213+ element.insert(0, "0"*(3-element_len))
214+ elif element_len == 3:
215+ pass
216+ elif element_len < 35:
217+ element.insert(0, MAP[element_len-4])
218+ element.insert(0, "X")
219+ elif element_len < 1027:
220+ element.insert(0, MAP[(element_len-4) & 0x1F])
221+ element.insert(0, MAP[(element_len-4) & 0x3E0])
222+ element.insert(0, "Y")
223+ else:
224+ raise ValueError("Number too large")
225+ result.extend(element)
226+ return "".join(result)
227+$_$;
228+
229+CREATE OR REPLACE FUNCTION ftiupdate() RETURNS trigger
230+ LANGUAGE plpython3u
231+ AS $_$
232+ new = TD["new"]
233+ args = TD["args"][:]
234+
235+ # Short circuit if none of the relevant columns have been
236+ # modified and fti is not being set to NULL (setting the fti
237+ # column to NULL is thus how we can force a rebuild of the fti
238+ # column).
239+ if TD["event"] == "UPDATE" and new["fti"] != None:
240+ old = TD["old"]
241+ relevant_modification = False
242+ for column_name in args[::2]:
243+ if new[column_name] != old[column_name]:
244+ relevant_modification = True
245+ break
246+ if not relevant_modification:
247+ return "OK"
248+
249+ # Generate an SQL statement that turns the requested
250+ # column values into a weighted tsvector
251+ sql = []
252+ for i in range(0, len(args), 2):
253+ sql.append(
254+ "setweight(to_tsvector('default', coalesce("
255+ "substring(ltrim($%d) from 1 for 2500),'')),"
256+ "CAST($%d AS \"char\"))" % (i + 1, i + 2))
257+ args[i] = new[args[i]]
258+
259+ sql = "SELECT %s AS fti" % "||".join(sql)
260+
261+ # Execute and store in the fti column
262+ plan = plpy.prepare(sql, ["text", "char"] * (len(args) // 2))
263+ new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
264+
265+ # Tell PostgreSQL we have modified the data
266+ return "MODIFY"
267+$_$;
268+
269+CREATE OR REPLACE FUNCTION ftq(text) RETURNS tsquery
270+ LANGUAGE plpython3u IMMUTABLE STRICT
271+ AS $_$
272+ p = plpy.prepare(
273+ "SELECT to_tsquery('default', _ftq($1)) AS x", ["text"])
274+ query = plpy.execute(p, args, 1)[0]["x"]
275+ return query or None
276+ $_$;
277+
278+CREATE OR REPLACE FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text
279+ LANGUAGE plpython3u IMMUTABLE
280+ AS $$
281+ # If this method is altered, then any functional indexes using it
282+ # need to be rebuilt.
283+ import re
284+ import datetime
285+
286+ date_expected, name = args
287+
288+ def substitute_filled_numbers(match):
289+ return match.group(0).zfill(5)
290+
291+ name = re.sub('\d+', substitute_filled_numbers, name)
292+ if date_expected is None:
293+ # NULL dates are considered to be in the future.
294+ date_expected = datetime.datetime(datetime.MAXYEAR, 1, 1)
295+ return '%s %s' % (date_expected, name)
296+$$;
297+
298+CREATE OR REPLACE FUNCTION mv_validpersonorteamcache_emailaddress() RETURNS trigger
299+ LANGUAGE plpython3u SECURITY DEFINER
300+ AS $_$
301+ # This trigger function keeps the ValidPersonOrTeamCache materialized
302+ # view in sync when updates are made to the EmailAddress table.
303+ # Note that if the corresponding person is a team, changes to this table
304+ # have no effect.
305+ PREF = 4 # Constant indicating preferred email address
306+
307+ if "delete_plan" not in SD:
308+ param_types = ["int4"]
309+
310+ SD["is_team"] = plpy.prepare("""
311+ SELECT teamowner IS NOT NULL AS is_team FROM Person WHERE id = $1
312+ """, param_types)
313+
314+ SD["delete_plan"] = plpy.prepare("""
315+ DELETE FROM ValidPersonOrTeamCache WHERE id = $1
316+ """, param_types)
317+
318+ SD["insert_plan"] = plpy.prepare("""
319+ INSERT INTO ValidPersonOrTeamCache (id) VALUES ($1)
320+ """, param_types)
321+
322+ SD["maybe_insert_plan"] = plpy.prepare("""
323+ INSERT INTO ValidPersonOrTeamCache (id)
324+ SELECT Person.id
325+ FROM Person
326+ JOIN EmailAddress ON Person.id = EmailAddress.person
327+ LEFT OUTER JOIN ValidPersonOrTeamCache
328+ ON Person.id = ValidPersonOrTeamCache.id
329+ WHERE Person.id = $1
330+ AND ValidPersonOrTeamCache.id IS NULL
331+ AND status = %(PREF)d
332+ AND merged IS NULL
333+ -- AND password IS NOT NULL
334+ """ % vars(), param_types)
335+
336+ def is_team(person_id):
337+ """Return true if person_id corresponds to a team"""
338+ if person_id is None:
339+ return False
340+ return plpy.execute(SD["is_team"], [person_id], 1)[0]["is_team"]
341+
342+ class NoneDict:
343+ def __getitem__(self, key):
344+ return None
345+
346+ old = TD["old"] or NoneDict()
347+ new = TD["new"] or NoneDict()
348+
349+ #plpy.info("old.id == %s" % old["id"])
350+ #plpy.info("old.person == %s" % old["person"])
351+ #plpy.info("old.status == %s" % old["status"])
352+ #plpy.info("new.id == %s" % new["id"])
353+ #plpy.info("new.person == %s" % new["person"])
354+ #plpy.info("new.status == %s" % new["status"])
355+
356+ # Short circuit if neither person nor status has changed
357+ if old["person"] == new["person"] and old["status"] == new["status"]:
358+ return
359+
360+ # Short circuit if we are not mucking around with preferred email
361+ # addresses
362+ if old["status"] != PREF and new["status"] != PREF:
363+ return
364+
365+ # Note that we have a constraint ensuring that there is only one
366+ # status == PREF email address per person at any point in time.
367+ # This simplifies our logic, as we know that if old.status == PREF,
368+ # old.person does not have any other preferred email addresses.
369+ # Also if new.status == PREF, we know new.person previously did not
370+ # have a preferred email address.
371+
372+ if old["person"] != new["person"]:
373+ if old["status"] == PREF and not is_team(old["person"]):
374+ # old.person is no longer valid, unless they are a team
375+ plpy.execute(SD["delete_plan"], [old["person"]])
376+ if new["status"] == PREF and not is_team(new["person"]):
377+ # new["person"] is now valid, or unchanged if they are a team
378+ plpy.execute(SD["insert_plan"], [new["person"]])
379+
380+ elif old["status"] == PREF and not is_team(old["person"]):
381+ # No longer valid, or unchanged if they are a team
382+ plpy.execute(SD["delete_plan"], [old["person"]])
383+
384+ elif new["status"] == PREF and not is_team(new["person"]):
385+ # May now be valid, or unchanged if they are a team.
386+ plpy.execute(SD["maybe_insert_plan"], [new["person"]])
387+$_$;
388+
389+CREATE OR REPLACE FUNCTION mv_validpersonorteamcache_person() RETURNS trigger
390+ LANGUAGE plpython3u SECURITY DEFINER
391+ AS $_$
392+ # This trigger function could be simplified by simply issuing
393+ # one DELETE followed by one INSERT statement. However, we want to minimize
394+ # expensive writes so we use this more complex logic.
395+ PREF = 4 # Constant indicating preferred email address
396+
397+ if "delete_plan" not in SD:
398+ param_types = ["int4"]
399+
400+ SD["delete_plan"] = plpy.prepare("""
401+ DELETE FROM ValidPersonOrTeamCache WHERE id = $1
402+ """, param_types)
403+
404+ SD["maybe_insert_plan"] = plpy.prepare("""
405+ INSERT INTO ValidPersonOrTeamCache (id)
406+ SELECT Person.id
407+ FROM Person
408+ LEFT OUTER JOIN EmailAddress
409+ ON Person.id = EmailAddress.person AND status = %(PREF)d
410+ LEFT OUTER JOIN ValidPersonOrTeamCache
411+ ON Person.id = ValidPersonOrTeamCache.id
412+ WHERE Person.id = $1
413+ AND ValidPersonOrTeamCache.id IS NULL
414+ AND merged IS NULL
415+ AND (teamowner IS NOT NULL OR EmailAddress.id IS NOT NULL)
416+ """ % vars(), param_types)
417+
418+ new = TD["new"]
419+ old = TD["old"]
420+
421+ # We should always have new, as this is not a DELETE trigger
422+ assert new is not None, 'New is None'
423+
424+ person_id = new["id"]
425+ query_params = [person_id] # All the same
426+
427+ # Short circuit if this is a new person (not team), as it cannot
428+ # be valid until a status == 4 EmailAddress entry has been created
429+ # (unless it is a team, in which case it is valid on creation)
430+ if old is None:
431+ if new["teamowner"] is not None:
432+ plpy.execute(SD["maybe_insert_plan"], query_params)
433+ return
434+
435+ # Short circuit if there are no relevant changes
436+ if (new["teamowner"] == old["teamowner"]
437+ and new["merged"] == old["merged"]):
438+ return
439+
440+ # This function is only dealing with updates to the Person table.
441+ # This means we do not have to worry about EmailAddress changes here
442+
443+ if (new["merged"] is not None or new["teamowner"] is None):
444+ plpy.execute(SD["delete_plan"], query_params)
445+ else:
446+ plpy.execute(SD["maybe_insert_plan"], query_params)
447+$_$;
448+
449+CREATE OR REPLACE FUNCTION name_blacklist_match(text, integer) RETURNS integer
450+ LANGUAGE plpython3u STABLE STRICT SECURITY DEFINER
451+ SET search_path TO 'public'
452+ AS $_$
453+ import re
454+ name = args[0]
455+ user_id = args[1]
456+
457+ # Initialize shared storage, shared between invocations.
458+ if "regexp_select_plan" not in SD:
459+
460+ # All the blacklist regexps except the ones we are an admin
461+ # for. These we do not check since they are not blacklisted to us.
462+ SD["regexp_select_plan"] = plpy.prepare("""
463+ SELECT id, regexp FROM NameBlacklist
464+ WHERE admin IS NULL OR admin NOT IN (
465+ SELECT team FROM TeamParticipation
466+ WHERE person = $1)
467+ ORDER BY id
468+ """, ["integer"])
469+
470+ # Storage for compiled regexps
471+ SD["compiled"] = {}
472+
473+ # admins is a celebrity and its id is immutable.
474+ admins_id = plpy.execute(
475+ "SELECT id FROM Person WHERE name='admins'")[0]["id"]
476+
477+ SD["admin_select_plan"] = plpy.prepare("""
478+ SELECT TRUE FROM TeamParticipation
479+ WHERE
480+ TeamParticipation.team = %d
481+ AND TeamParticipation.person = $1
482+ LIMIT 1
483+ """ % admins_id, ["integer"])
484+
485+ # All the blacklist regexps except those that have an admin because
486+ # members of ~admin can use any name that any other admin can use.
487+ SD["admin_regexp_select_plan"] = plpy.prepare("""
488+ SELECT id, regexp FROM NameBlacklist
489+ WHERE admin IS NULL
490+ ORDER BY id
491+ """, ["integer"])
492+
493+
494+ compiled = SD["compiled"]
495+
496+ # Names are never blacklisted for Lauchpad admins.
497+ if user_id is not None and plpy.execute(
498+ SD["admin_select_plan"], [user_id]).nrows() > 0:
499+ blacklist_plan = "admin_regexp_select_plan"
500+ else:
501+ blacklist_plan = "regexp_select_plan"
502+
503+ for row in plpy.execute(SD[blacklist_plan], [user_id]):
504+ regexp_id = row["id"]
505+ regexp_txt = row["regexp"]
506+ if (compiled.get(regexp_id) is None
507+ or compiled[regexp_id][0] != regexp_txt):
508+ regexp = re.compile(regexp_txt, re.IGNORECASE | re.VERBOSE)
509+ compiled[regexp_id] = (regexp_txt, regexp)
510+ else:
511+ regexp = compiled[regexp_id][1]
512+ if regexp.search(name) is not None:
513+ return regexp_id
514+ return None
515+$_$;
516+
517+CREATE OR REPLACE FUNCTION person_sort_key(displayname text, name text) RETURNS text
518+ LANGUAGE plpython3u IMMUTABLE STRICT
519+ AS $$
520+ # NB: If this implementation is changed, the person_sort_idx needs to be
521+ # rebuilt along with any other indexes using it.
522+ import re
523+
524+ try:
525+ strip_re = SD["strip_re"]
526+ except KeyError:
527+ strip_re = re.compile("(?:[^\w\s]|[\d_])")
528+ SD["strip_re"] = strip_re
529+
530+ displayname, name = args
531+
532+ # Strip noise out of displayname. We do not have to bother with
533+ # name, as we know it is just plain ascii.
534+ displayname = strip_re.sub('', displayname.lower())
535+ return "%s, %s" % (displayname.strip(), name)
536+$$;
537+
538+CREATE OR REPLACE FUNCTION sane_version(text) RETURNS boolean
539+ LANGUAGE plpython3u IMMUTABLE STRICT
540+ AS $_$
541+ import re
542+ if re.search("""^(?ix)
543+ [0-9a-z]
544+ ( [0-9a-z] | [0-9a-z.-]*[0-9a-z] )*
545+ $""", args[0]):
546+ return 1
547+ return 0
548+$_$;
549+
550+CREATE OR REPLACE FUNCTION sha1(text) RETURNS character
551+ LANGUAGE plpython3u IMMUTABLE STRICT
552+ AS $$
553+ import hashlib
554+ return hashlib.sha1(args[0].encode()).hexdigest()
555+$$;
556+
557+CREATE OR REPLACE FUNCTION ulower(text) RETURNS text
558+ LANGUAGE plpython3u IMMUTABLE STRICT
559+ AS $$
560+ return args[0].lower()
561+$$;
562+
563+CREATE OR REPLACE FUNCTION update_database_stats() RETURNS void
564+ LANGUAGE plpython3u SECURITY DEFINER
565+ SET search_path TO 'public'
566+ AS $_$
567+ import re
568+ import subprocess
569+
570+ # Prune DatabaseTableStats and insert current data.
571+ # First, detect if the statistics have been reset.
572+ stats_reset = plpy.execute("""
573+ SELECT *
574+ FROM
575+ pg_catalog.pg_stat_user_tables AS NowStat,
576+ DatabaseTableStats AS LastStat
577+ WHERE
578+ LastStat.date_created = (
579+ SELECT max(date_created) FROM DatabaseTableStats)
580+ AND NowStat.schemaname = LastStat.schemaname
581+ AND NowStat.relname = LastStat.relname
582+ AND (
583+ NowStat.seq_scan < LastStat.seq_scan
584+ OR NowStat.idx_scan < LastStat.idx_scan
585+ OR NowStat.n_tup_ins < LastStat.n_tup_ins
586+ OR NowStat.n_tup_upd < LastStat.n_tup_upd
587+ OR NowStat.n_tup_del < LastStat.n_tup_del
588+ OR NowStat.n_tup_hot_upd < LastStat.n_tup_hot_upd)
589+ LIMIT 1
590+ """, 1).nrows() > 0
591+ if stats_reset:
592+ # The database stats have been reset. We cannot calculate
593+ # deltas because we do not know when this happened. So we trash
594+ # our records as they are now useless to us. We could be more
595+ # sophisticated about this, but this should only happen
596+ # when an admin explicitly resets the statistics or if the
597+ # database is rebuilt.
598+ plpy.notice("Stats wraparound. Purging DatabaseTableStats")
599+ plpy.execute("DELETE FROM DatabaseTableStats")
600+ else:
601+ plpy.execute("""
602+ DELETE FROM DatabaseTableStats
603+ WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
604+ - CAST('21 days' AS interval));
605+ """)
606+ # Insert current data.
607+ plpy.execute("""
608+ INSERT INTO DatabaseTableStats
609+ SELECT
610+ CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
611+ schemaname, relname, seq_scan, seq_tup_read,
612+ coalesce(idx_scan, 0), coalesce(idx_tup_fetch, 0),
613+ n_tup_ins, n_tup_upd, n_tup_del,
614+ n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum,
615+ last_autovacuum, last_analyze, last_autoanalyze
616+ FROM pg_catalog.pg_stat_user_tables;
617+ """)
618+
619+ # Prune DatabaseCpuStats. Calculate CPU utilization information
620+ # and insert current data.
621+ plpy.execute("""
622+ DELETE FROM DatabaseCpuStats
623+ WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
624+ - CAST('21 days' AS interval));
625+ """)
626+ dbname = plpy.execute(
627+ "SELECT current_database() AS dbname", 1)[0]['dbname']
628+ ps = subprocess.Popen(
629+ ["ps", "-C", "postgres", "--no-headers", "-o", "cp,args"],
630+ stdin=subprocess.PIPE, stdout=subprocess.PIPE,
631+ stderr=subprocess.STDOUT)
632+ stdout, stderr = ps.communicate()
633+ cpus = {}
634+ # We make the username match non-greedy so the trailing \d eats
635+ # trailing digits from the database username. This collapses
636+ # lpnet1, lpnet2 etc. into just lpnet.
637+ ps_re = re.compile(
638+ r"(?m)^\s*(\d+)\spostgres:\s(\w+?)\d*\s%s\s" % dbname)
639+ for ps_match in ps_re.finditer(stdout):
640+ cpu, username = ps_match.groups()
641+ cpus[username] = int(cpu) + cpus.setdefault(username, 0)
642+ cpu_ins = plpy.prepare(
643+ "INSERT INTO DatabaseCpuStats (username, cpu) VALUES ($1, $2)",
644+ ["text", "integer"])
645+ for cpu_tuple in cpus.items():
646+ plpy.execute(cpu_ins, cpu_tuple)
647+$_$;
648+
649+CREATE OR REPLACE FUNCTION valid_absolute_url(text) RETURNS boolean
650+ LANGUAGE plpython3u IMMUTABLE STRICT
651+ AS $$
652+ from urllib.parse import urlparse, uses_netloc
653+ # Extend list of schemes that specify netloc.
654+ if 'bzr' not in uses_netloc:
655+ uses_netloc.insert(0, 'bzr')
656+ uses_netloc.insert(0, 'bzr+ssh')
657+ uses_netloc.insert(0, 'ssh') # Mercurial
658+ (scheme, netloc, path, params, query, fragment) = urlparse(args[0])
659+ return bool(scheme and netloc)
660+$$;
661+
662+CREATE OR REPLACE FUNCTION valid_branch_name(text) RETURNS boolean
663+ LANGUAGE plpython3u IMMUTABLE STRICT
664+ AS $$
665+ import re
666+ name = args[0]
667+ pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
668+ if re.match(pat, name):
669+ return 1
670+ return 0
671+$$;
672+
673+CREATE OR REPLACE FUNCTION valid_cve(text) RETURNS boolean
674+ LANGUAGE plpython3u IMMUTABLE STRICT
675+ AS $_$
676+ import re
677+ name = args[0]
678+ pat = r"^(19|20)\d{2}-\d{4,}$"
679+ if re.match(pat, name):
680+ return 1
681+ return 0
682+$_$;
683+
684+CREATE OR REPLACE FUNCTION valid_debian_version(text) RETURNS boolean
685+ LANGUAGE plpython3u IMMUTABLE STRICT
686+ AS $_$
687+ import re
688+ m = re.search("""^(?ix)
689+ ([0-9]+:)?
690+ ([0-9a-z][a-z0-9+:.~-]*?)
691+ (-[a-z0-9+.~]+)?
692+ $""", args[0])
693+ if m is None:
694+ return 0
695+ epoch, version, revision = m.groups()
696+ if not epoch:
697+ # Can''t contain : if no epoch
698+ if ":" in version:
699+ return 0
700+ if not revision:
701+ # Can''t contain - if no revision
702+ if "-" in version:
703+ return 0
704+ return 1
705+$_$;
706+
707+CREATE OR REPLACE FUNCTION valid_fingerprint(text) RETURNS boolean
708+ LANGUAGE plpython3u IMMUTABLE STRICT
709+ AS $$
710+ import re
711+ if re.match(r"[\dA-F]{40}", args[0]) is not None:
712+ return 1
713+ else:
714+ return 0
715+$$;
716+
717+CREATE OR REPLACE FUNCTION valid_git_repository_name(text) RETURNS boolean
718+ LANGUAGE plpython3u IMMUTABLE STRICT
719+ AS $$
720+ import re
721+ name = args[0]
722+ pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
723+ if not name.endswith(".git") and re.match(pat, name):
724+ return 1
725+ return 0
726+$$;
727+
728+CREATE OR REPLACE FUNCTION valid_keyid(text) RETURNS boolean
729+ LANGUAGE plpython3u IMMUTABLE STRICT
730+ AS $$
731+ import re
732+ if re.match(r"[\dA-F]{8}", args[0]) is not None:
733+ return 1
734+ else:
735+ return 0
736+$$;
737+
738+CREATE OR REPLACE FUNCTION valid_regexp(text) RETURNS boolean
739+ LANGUAGE plpython3u IMMUTABLE STRICT
740+ AS $$
741+ import re
742+ try:
743+ re.compile(args[0])
744+ except Exception:
745+ return False
746+ else:
747+ return True
748+$$;
749+
750+CREATE OR REPLACE FUNCTION version_sort_key(version text) RETURNS text
751+ LANGUAGE plpython3u IMMUTABLE STRICT
752+ AS $$
753+ # If this method is altered, then any functional indexes using it
754+ # need to be rebuilt.
755+ import re
756+
757+ [version] = args
758+
759+ def substitute_filled_numbers(match):
760+ # Prepend "~" so that version numbers will show up first
761+ # when sorted descending, i.e. [3, 2c, 2b, 1, c, b, a] instead
762+ # of [c, b, a, 3, 2c, 2b, 1]. "~" has the highest ASCII value
763+ # of visible ASCII characters.
764+ return '~' + match.group(0).zfill(5)
765+
766+ return re.sub('\d+', substitute_filled_numbers, version)
767+$$;
768+
769+DROP EXTENSION plpythonu;
770+
771+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 46, 0);

Subscribers

People subscribed via source and target branches

to status/vote changes: