PR#3615: fix different PG capabilities in schema - koji

koji

#3615 fix different PG capabilities in schema

Merged 2 years ago by tkopecek. Opened 2 years ago by tkopecek.

tkopecek/koji issue3594 into master

update supported platforms

Tomas Kopecek • 2 years ago

b18d998

fix different PG capabilities in schema

Tomas Kopecek • 2 years ago

c110569

docs/schema-upgrade-1.30-1.31.sql

file modified

+12 -2

		`@@ -2,6 +2,16 @@`
		`-- from version 1.30 to 1.31`

		`BEGIN;`
		`- -- index for default search method for rpms`
		`- CREATE INDEX rpminfo_filename ON rpminfo((name \|\| '-' \|\| version \|\| '-' \|\| release \|\| '.' \|\| arch \|\| '.rpm')) INCLUDE (id);`
		`+ -- index for default search method for rpms, PG11+ can benefit from new include method`
		`+ DO $$`
		`+ DECLARE version integer;`
		`+ BEGIN`
		`+ SELECT current_setting('server_version_num')::integer INTO version;`
		`+ IF version >= 110000 THEN`
		`+ EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name \|\| ''-'' \|\| version \|\| ''-'' \|\| release \|\| ''.'' \|\| arch \|\| ''.rpm'')) INCLUDE (id);';`
		`+ ELSE`
		`+ EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name \|\| ''-'' \|\| version \|\| ''-'' \|\| release \|\| ''.'' \|\| arch \|\| ''.rpm''));';`
		`+ END IF;`
		`+ END`
		`+ $$;`
		`COMMIT;`

docs/schema.sql

file modified

+12 -1

		`@@ -734,7 +734,18 @@`
		`CONSTRAINT rpminfo_unique_nvra UNIQUE (name,version,release,arch,external_repo_id)`
		`) WITHOUT OIDS;`
		`CREATE INDEX rpminfo_build ON rpminfo(build_id);`
		`- CREATE INDEX rpminfo_filename ON rpminfo((name \|\| '-' \|\| version \|\| '-' \|\| release \|\| '.' \|\| arch \|\| '.rpm')) INCLUDE (id);`
		`+ -- index for default search method for rpms, PG11+ can benefit from new include method`
		`+ DO $$`
		`+ DECLARE version integer;`
		`+ BEGIN`
		`+ SELECT current_setting('server_version_num')::integer INTO version;`
		`+ IF version >= 110000 THEN`
		`+ EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name \|\| ''-'' \|\| version \|\| ''-'' \|\| release \|\| ''.'' \|\| arch \|\| ''.rpm'')) INCLUDE (id);';`
		`+ ELSE`
		`+ EXECUTE 'CREATE INDEX rpminfo_filename ON rpminfo((name \|\| ''-'' \|\| version \|\| ''-'' \|\| release \|\| ''.'' \|\| arch \|\| ''.rpm''));';`
		`+ END IF;`
		`+ END`
		`+ $$;`

		`-- sighash is the checksum of the signature header`
		`CREATE TABLE rpmsigs (`

docs/source/supported_platforms.rst

file modified

		`@@ -11,3 +11,9 @@`
		`+===========+=====+=====+=========+=======+=====+=====+`
		`\| Python \| 3.6 \| 3.6 \| 2.7 \| 3.6 \| 2.7 \| 2.7 \|`
		`+-----------+-----+-----+---------+-------+-----+-----+`
		`+`
		`+ For database we're supporting RHEL/CentOS 8+. So, it means that`
		`+ postgresl 10 is still supported, anyway we encourage using at`
		+ least PG 12 (``dnf module enable postgresql:12``). At least some
		`+ indices are set up in more efficient way with newer PG`
		`+ capabilities.`

tkopecek commented 2 years ago

mikem commented 2 years ago

I don't love having the schema vary like this, but I guess we have little choice, short of dropping the include altogether (introduced in #3448).

mikem commented 2 years ago

At what point do we think we can require PG>=11?

mikem commented 2 years ago

The change itself looks fine

mikem commented 2 years ago

Are you planning to add a separate PR to address the docs part? I'm a little worried about that getting lost in the shuffle since #3595 is marked as a dup

tkopecek commented 2 years ago

It is tricky to set up PG11+ requirement here. We can technically live with the older one. RHEL/CentOS 8 has PG10 as default and modules for PG 9.6/10/12/13m while CentOS9 has base 13 and module for 15 now. I would go ahead to PG12 which we're using now.

I would add a docs update here. What do you think? (I'm inclined to say there that we're ok with PG10, but encourages PG12 as better tested).

Metadata Update from @tkopecek:
- Pull-request tagged with: testing-ready

2 years ago

mikem commented 2 years ago