PostgreSQL: Improve performance and squish bug.

Improve performance of the unread messages backlog fetcher by using a normal JOIN instead of a LEFT JOIN.

Correct for bug in which buffer.lastseenmsgid was set to a value higher than buffer.lastmsgid. This is a client bug, but this prevents it from screwing up the database.

diff --git a/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql b/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql
index d5c01dc..a40bd5a 100644 (file)
--- a/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql
+++ b/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql
@@ -1,8 +1,9 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
-LEFT JOIN sender ON backlog.senderid = sender.senderid
+JOIN sender ON backlog.senderid = sender.senderid
 WHERE backlog.messageid >= $1
 AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = $1)
+AND backlog.messageid <= buffer.lastmsgid
 AND bufferid = $2
 ORDER BY messageid DESC
-LIMIT $3
\ No newline at end of file
+LIMIT $3

diff --git a/src/core/SQL/PostgreSQL/20/select_messagesRange.sql b/src/core/SQL/PostgreSQL/20/select_messagesRange.sql
index 9bd8613..b7af909 100644 (file)
--- a/src/core/SQL/PostgreSQL/20/select_messagesRange.sql
+++ b/src/core/SQL/PostgreSQL/20/select_messagesRange.sql
@@ -1,8 +1,8 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
-LEFT JOIN sender ON backlog.senderid = sender.senderid
+JOIN sender ON backlog.senderid = sender.senderid
 WHERE backlog.messageid >= $1
     AND backlog.messageid < $2
     AND bufferid = $3
 ORDER BY messageid DESC
-LIMIT $4
\ No newline at end of file
+LIMIT $4

diff --git a/src/core/SQL/PostgreSQL/20/setup_050_buffer.sql b/src/core/SQL/PostgreSQL/20/setup_050_buffer.sql
index bbf8ab6..a6665b5 100644 (file)
--- a/src/core/SQL/PostgreSQL/20/setup_050_buffer.sql
+++ b/src/core/SQL/PostgreSQL/20/setup_050_buffer.sql
@@ -11,5 +11,6 @@ create TABLE buffer (
        markerlinemsgid integer NOT NULL DEFAULT 0,
        key varchar(128),
        joined boolean NOT NULL DEFAULT FALSE, -- BOOL
-       UNIQUE(userid, networkid, buffercname)
+       UNIQUE(userid, networkid, buffercname),
+       CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)
 )

diff --git a/src/core/SQL/PostgreSQL/20/upgrade_003_correct_bad_lastseenmsgid.sql b/src/core/SQL/PostgreSQL/20/upgrade_003_correct_bad_lastseenmsgid.sql
new file mode 100644 (file)
index 0000000..a09dea1
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/20/upgrade_003_correct_bad_lastseenmsgid.sql
@@ -0,0 +1,3 @@
+UPDATE buffer
+SET lastseenmsgid = buffer.lastmsgid
+WHERE buffer.lastseenmsgid > buffer.lastmsgid

diff --git a/src/core/SQL/PostgreSQL/20/upgrade_004_add_lastseenmsgid_constraint.sql b/src/core/SQL/PostgreSQL/20/upgrade_004_add_lastseenmsgid_constraint.sql
new file mode 100644 (file)
index 0000000..da37927
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/20/upgrade_004_add_lastseenmsgid_constraint.sql
@@ -0,0 +1 @@
+ALTER TABLE buffer ADD CONSTRAINT badLastSeenMsgId CHECK (buffer.lastseenmsgid <= buffer.lastmsgid)

diff --git a/src/core/sql.qrc b/src/core/sql.qrc
index 22de48e..52e2f4a 100644 (file)
--- a/src/core/sql.qrc
+++ b/src/core/sql.qrc
@@ -78,7 +78,9 @@
     <file>./SQL/PostgreSQL/20/setup_100_user_setting.sql</file>
     <file>./SQL/PostgreSQL/20/setup_110_alter_sender_seq.sql</file>
     <file>./SQL/PostgreSQL/20/setup_120_alter_messageid_seq.sql</file>
+    <file>./SQL/PostgreSQL/20/setup_130_function_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/20/update_backlog_bufferid.sql</file>
+    <file>./SQL/PostgreSQL/20/update_buffer_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/20/update_buffer_lastseen.sql</file>
     <file>./SQL/PostgreSQL/20/update_buffer_markerlinemsgid.sql</file>
     <file>./SQL/PostgreSQL/20/update_buffer_name.sql</file>
@@ -93,10 +95,10 @@
     <file>./SQL/PostgreSQL/20/update_username.sql</file>
     <file>./SQL/PostgreSQL/20/update_userpassword.sql</file>
     <file>./SQL/PostgreSQL/20/upgrade_000_alter_buffer_add_lastmsgid.sql</file>
-    <file>./SQL/PostgreSQL/20/update_buffer_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/20/upgrade_001_add_function_populate_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/20/upgrade_002_run_function_populate_lastmsgid.sql</file>
-    <file>./SQL/PostgreSQL/20/setup_130_function_lastmsgid.sql</file>
+    <file>./SQL/PostgreSQL/20/upgrade_003_correct_bad_lastseenmsgid.sql</file>
+    <file>./SQL/PostgreSQL/20/upgrade_004_add_lastseenmsgid_constraint.sql</file>
     <file>./SQL/SQLite/1/upgrade_000_drop_coreinfo.sql</file>
     <file>./SQL/SQLite/1/upgrade_010_create_coreinfo.sql</file>
     <file>./SQL/SQLite/1/upgrade_020_update_schemaversion.sql</file>