public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql 4+ messages / 2 participants [nested] [flat]
* [PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql @ 2021-10-18 16:19 Célestin Matte <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Célestin Matte @ 2021-10-18 16:19 UTC (permalink / raw) To: PostgreSQL WWW <[email protected]> Hello, While installing pgarchives, I've encountered issues that are addressed in this patch. Django's model and schema.sql both contain tables that are necessary for the execution of the application, but are mutually exclusive. This is due to some fields in the "messages" table that cannot be defined in django, and definition of tables in schema.sql that are already created by django. Additionally, I have two questions: - Where is the "tsparser" parser defined? (See commit e05f813b of pgarchives). It is used in schema.sql, but I haven't found its definition in the pgarchives, pglister or pgweb repositories. Is it an alias to pg_catalog.pg_ts_parser? (I have been able to complete the install procedure by reverting e05f813b, but I have no idea what the consequences are for the application) - Same question for /usr/share/postgresql/12/tsearch_data/pg_dict.stop I've found pg_dict.syn in pgweb, but not this file. Cheers, -- Célestin Matte Attachments: [text/x-patch] 0001-Fix-database-install-procedure-remove-redundant-tabl.patch (3.0K, 2-0001-Fix-database-install-procedure-remove-redundant-tabl.patch) download | inline diff: From 58af95a89f08e7bd94b738013fecbe6c8030cff1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]> Date: Mon, 18 Oct 2021 18:04:37 +0200 Subject: [PATCH] Fix database install procedure: remove redundant tables in schema.sql Django's model and schema.sql both contain tables that are necessary for the execution of the application, but are mutually exclusive. This is due to some fields in the "messages" table that cannot be defined in django, and definition of tables in schema.sql that are already created by django. --- loader/sql/schema.sql | 49 ++++--------------------------------------- 1 file changed, 4 insertions(+), 45 deletions(-) diff --git a/loader/sql/schema.sql b/loader/sql/schema.sql index d9b5d5f..2834e12 100644 --- a/loader/sql/schema.sql +++ b/loader/sql/schema.sql @@ -2,23 +2,10 @@ BEGIN; -CREATE TABLE messages ( - id SERIAL NOT NULL PRIMARY KEY, - parentid int REFERENCES messages, - threadid int NOT NULL, - _from text NOT NULL, - _to text NOT NULL, - cc text NOT NULL, - subject text NOT NULL, - date timestamptz NOT NULL, - loaddate timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, - has_attachment boolean NOT NULL, - hiddenstatus int NULL, - messageid text NOT NULL, - bodytxt text NOT NULL, - rawtxt bytea NOT NULL, - fti tsvector NOT NULL -); +ALTER TABLE messages + ADD COLUMN rawtxt bytea NOT NULL, + ADD COLUMN fti tsvector NOT NULL; + CREATE INDEX idx_messages_threadid ON messages(threadid); CREATE UNIQUE INDEX idx_messages_msgid ON messages(messageid); CREATE INDEX idx_messages_date ON messages(date); @@ -42,21 +29,6 @@ CREATE TABLE unresolved_messages( CREATE UNIQUE INDEX idx_unresolved_msgid_message ON unresolved_messages(msgid, message); -CREATE TABLE listgroups( - groupid int NOT NULL PRIMARY KEY, - groupname text NOT NULL UNIQUE, - sortkey int NOT NULL -); - -CREATE TABLE lists( - listid int NOT NULL PRIMARY KEY, - listname text NOT NULL UNIQUE, - shortdesc text NOT NULL, - description text NOT NULL, - active boolean NOT NULL, - groupid int NOT NULL REFERENCES listgroups(groupid) -); - CREATE TABLE list_months( listid int NOT NULL REFERENCES lists(listid), year int NOT NULL, @@ -71,13 +43,6 @@ CREATE TABLE list_threads( ); CREATE INDEX list_threads_listid_idx ON list_threads(listid); -CREATE TABLE attachments( - id serial not null primary key, - message int not null references messages(id), - filename text not null, - contenttype text not null, - attachment bytea not null -); CREATE INDEX idx_attachments_msg ON attachments(message); CREATE TABLE apiclients( @@ -86,12 +51,6 @@ CREATE TABLE apiclients( postback varchar(500) NOT NULL ); -CREATE TABLE threadsubscriptions( - id SERIAL NOT NULL PRIMARY KEY, - apiclient_id integer NOT NULL REFERENCES apiclients(id), - threadid integer NOT NULL -); - CREATE TABLE threadnotifications( apiclient_id integer NOT NULL REFERENCES apiclients(id), threadid integer NOT NULL, -- 2.33.1 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql @ 2021-10-22 15:19 Célestin Matte <[email protected]> parent: Célestin Matte <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Célestin Matte @ 2021-10-22 15:19 UTC (permalink / raw) To: [email protected] Hello, Apologies, patch sent in previous email of this thread is incorrect. Please find correct patch attached. In fact, the pgarchives repository is in a weird state, in which the migration adding two tables is missing (0005_apiclient_threadsubscription.py when running makemigrations). My guess is that this is because these tables use SERIALs, which django does not handle properly, and must by created using raw SQL. Am I right? Is it something that should be fixed? I would still need answers for the two questions asked in previous email: > Additionally, I have two questions: > - Where is the "tsparser" parser defined? (See commit e05f813b of pgarchives). > It is used in schema.sql, but I haven't found its definition in the > pgarchives, pglister or pgweb repositories. Is it an alias to > pg_catalog.pg_ts_parser? > (I have been able to complete the install procedure by reverting e05f813b, > but I have no idea what the consequences are for the application) > - Same question for /usr/share/postgresql/12/tsearch_data/pg_dict.stop > I've found pg_dict.syn in pgweb, but not this file. > > Cheers, > Cheers, -- Célestin Matte Attachments: [text/x-patch] 0001-Fix-database-install-procedure-remove-redundant-tabl.patch (2.6K, 2-0001-Fix-database-install-procedure-remove-redundant-tabl.patch) download | inline diff: From 8b76960c1b265b26801b5c7791db6c3e4f40bfc3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]> Date: Mon, 18 Oct 2021 18:04:37 +0200 Subject: [PATCH] Fix database install procedure: remove redundant tables in schema.sql Django's model and schema.sql both contain tables that are necessary for the execution of the application, but are mutually exclusive. This is due to some fields in the "messages" table that cannot be defined in django, and definition of tables in schema.sql that are already created by django. --- loader/sql/schema.sql | 43 ++++--------------------------------------- 1 file changed, 4 insertions(+), 39 deletions(-) diff --git a/loader/sql/schema.sql b/loader/sql/schema.sql index d9b5d5f..a86c6a9 100644 --- a/loader/sql/schema.sql +++ b/loader/sql/schema.sql @@ -2,23 +2,10 @@ BEGIN; -CREATE TABLE messages ( - id SERIAL NOT NULL PRIMARY KEY, - parentid int REFERENCES messages, - threadid int NOT NULL, - _from text NOT NULL, - _to text NOT NULL, - cc text NOT NULL, - subject text NOT NULL, - date timestamptz NOT NULL, - loaddate timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, - has_attachment boolean NOT NULL, - hiddenstatus int NULL, - messageid text NOT NULL, - bodytxt text NOT NULL, - rawtxt bytea NOT NULL, - fti tsvector NOT NULL -); +ALTER TABLE messages + ADD COLUMN rawtxt bytea NOT NULL, + ADD COLUMN fti tsvector NOT NULL; + CREATE INDEX idx_messages_threadid ON messages(threadid); CREATE UNIQUE INDEX idx_messages_msgid ON messages(messageid); CREATE INDEX idx_messages_date ON messages(date); @@ -42,21 +29,6 @@ CREATE TABLE unresolved_messages( CREATE UNIQUE INDEX idx_unresolved_msgid_message ON unresolved_messages(msgid, message); -CREATE TABLE listgroups( - groupid int NOT NULL PRIMARY KEY, - groupname text NOT NULL UNIQUE, - sortkey int NOT NULL -); - -CREATE TABLE lists( - listid int NOT NULL PRIMARY KEY, - listname text NOT NULL UNIQUE, - shortdesc text NOT NULL, - description text NOT NULL, - active boolean NOT NULL, - groupid int NOT NULL REFERENCES listgroups(groupid) -); - CREATE TABLE list_months( listid int NOT NULL REFERENCES lists(listid), year int NOT NULL, @@ -71,13 +43,6 @@ CREATE TABLE list_threads( ); CREATE INDEX list_threads_listid_idx ON list_threads(listid); -CREATE TABLE attachments( - id serial not null primary key, - message int not null references messages(id), - filename text not null, - contenttype text not null, - attachment bytea not null -); CREATE INDEX idx_attachments_msg ON attachments(message); CREATE TABLE apiclients( -- 2.33.1 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql @ 2021-10-23 14:16 Magnus Hagander <[email protected]> parent: Célestin Matte <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Magnus Hagander @ 2021-10-23 14:16 UTC (permalink / raw) To: Célestin Matte <[email protected]>; +Cc: PostgreSQL WWW <[email protected]> On Mon, Oct 18, 2021 at 6:19 PM Célestin Matte <[email protected]> wrote: > Hello, > > While installing pgarchives, I've encountered issues that are addressed in > this patch. > Django's model and schema.sql both contain tables that are necessary for > the execution of the application, but are mutually exclusive. > This is due to some fields in the "messages" table that cannot be defined > in > django, and definition of tables in schema.sql that are already created > by django. > Ugh, yeah that is clearly a mess. I thought we had cleaned that up at some point, but clearly we haven't, and tracking changse has been inconsistent. I think the correct solution to the problem is to actually get rid of loader/sql/schema.sql and move it all into the django migrations. Trying to keep track of half in one place and half in another is just going to lead to more problems down the road. So your removal of messages there is a good start, but adding some columns back after the fact in a different file is a big ugh. We should just make all that happen in 0001_initial.py. Additionally, I have two questions: > - Where is the "tsparser" parser defined? (See commit e05f813b of > pgarchives). > It is used in schema.sql, but I haven't found its definition in the > pgarchives, pglister or pgweb repositories. Is it an alias to > pg_catalog.pg_ts_parser? > It's this one over here: https://github.com/postgrespro/pg_tsparser > (I have been able to complete the install procedure by reverting e05f813b, > but I have no idea what the consequences are for the application) > - Same question for /usr/share/postgresql/12/tsearch_data/pg_dict.stop > I've found pg_dict.syn in pgweb, but not this file. > This is just a textfile with stop words. interestingly enough the prod version for the archives just has the word "sql" in it -- I think there was a great big plan to do somethign good with that, that turned out to never happen. -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/; Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql @ 2021-10-23 14:30 Magnus Hagander <[email protected]> parent: Célestin Matte <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Magnus Hagander @ 2021-10-23 14:30 UTC (permalink / raw) To: Célestin Matte <[email protected]>; Jonathan S. Katz <[email protected]>; +Cc: PostgreSQL WWW <[email protected]> On Fri, Oct 22, 2021 at 5:20 PM Célestin Matte <[email protected]> wrote: > Hello, > > Apologies, patch sent in previous email of this thread is incorrect. > Please find correct patch attached. > > In fact, the pgarchives repository is in a weird state, in which the > migration adding two tables is missing > (0005_apiclient_threadsubscription.py when running makemigrations). My > guess is that this is because these tables use SERIALs, which django does > not handle properly, and must by created using raw SQL. Am I right? > Is it something that should be fixed? > Ugfh, yeah that one is really embarrassing on my part, and I thought I'd cleaned that one up but clearly not. The whole threadsubscription part wasn't finished, and was not supposed to be included in 4d159ca8b4efb0b1cf0893bca63d6e9489a9095f. The correct solution there is to revert most of 4d159ca8b4efb0b1cf0893bca63d6e9489a9095f, to have it include only the parts that are supposed to be there. And then reapply them at some time in the future when the code is finished. I'll go clean that up -- thanks for bringing my attention back to that! -- Magnus Hagander Me: https://www.hagander.net/ <http://www.hagander.net/; Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/; ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-10-23 14:30 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-10-18 16:19 [PATCH] pgarchives: Fix database install procedure: remove redundant tables in schema.sql Célestin Matte <[email protected]> 2021-10-22 15:19 ` Célestin Matte <[email protected]> 2021-10-23 14:30 ` Magnus Hagander <[email protected]> 2021-10-23 14:16 ` Magnus Hagander <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox