Mailing List Archive

This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "rt".

The branch, 5.0/pg-attachments-index-primary-key has been created
at ebbbf7ecea263face8ee28b7f29a2ee7e9c54e64 (commit)

- Log -----------------------------------------------------------------
commit ebbbf7ecea263face8ee28b7f29a2ee7e9c54e64
Author: sunnavy <sunnavy@bestpractical.com>
Date: Fri Dec 15 06:51:21 2023 -0500

Set id as the PRIMARY KEY of AttachmentsIndex for Pg

This is consistent with mysql's fulltext table. As id is the primary
key, DB automatically indexes the column which helps performance
especially for the following SQL that is called quite frequently when
indexing data:

SELECT MAX(id) FROM AttachmentsIndex

diff --git a/etc/upgrade/5.0.6/schema.Pg b/etc/upgrade/5.0.6/schema.Pg
new file mode 100644
index 0000000000..49f28588c0
--- /dev/null
+++ b/etc/upgrade/5.0.6/schema.Pg
@@ -0,0 +1,2 @@
+ALTER TABLE IF EXISTS AttachmentsIndex DROP CONSTRAINT IF EXISTS attachmentsindex_pkey;
+ALTER TABLE IF EXISTS AttachmentsIndex ADD PRIMARY KEY(id);
diff --git a/sbin/rt-setup-fulltext-index.in b/sbin/rt-setup-fulltext-index.in
index 4e75e7eda7..d079ab4092 100644
--- a/sbin/rt-setup-fulltext-index.in
+++ b/sbin/rt-setup-fulltext-index.in
@@ -327,7 +327,8 @@ elsif ( $DB{'type'} eq 'Pg' ) {
push @schema, split /;\n+/, <<SCHEMA;
CREATE TABLE $table (
id BIGSERIAL,
- $column tsvector
+ $column tsvector,
+ PRIMARY KEY (id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON $table TO "$DB{user}"
SCHEMA

-----------------------------------------------------------------------


hooks/post-receive
--
rt
_______________________________________________
rt-commit mailing list
rt-commit@lists.bestpractical.com
https://lists.bestpractical.com/mailman/listinfo/rt-commit