Error updating to v5 build 2960

Post Reply
gcurrie
Posts: 25
Joined: 02 May 10 2:55

Error updating to v5 build 2960

Post by gcurrie » 07 Jun 20 19:09

Running v5 Server Edition PostgreSQL (9.6).

After updating the client and initially connecting to database, I am prompted to update the database.

I then get this error:

Unable to connect to the database.

The database reports:
Something went wrong while upgrading your PostgreSQL Catalog towards version 3023 (last successful step is 3021)

TPostgreSQLConnection: Execution of query failed (PostgreSQL: Error: trigger "idsearchdataupdatefts" for table "idsearchdata" does not exist

Severity: ERROR
SQL State: 42704
Primary Error: trigger "idsearchdataupdatefts" for table "idsearchdata" does not exist)


I took a look at the db and tables in pgAdmin 4:
The table idsearchdata exists.
There are no triggers on any tables that I can find and idsearchdataupdatefts definitely does not exist.
There IS a trigger function process_idsearchdataupdatefts() that exists, but it needs a trigger.

I seem to be stuck here. Please advise how I can continue and complete the database update.

Are there any SQL snippets I can run to create the missing idsearchdataupdatefts trigger?
Gordon Currie
Photo Supreme 5.4 PostgreSQL on Windows Server Essentials 2016

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: Error updating to v5 build 2960

Post by Hert » 07 Jun 20 19:38

Here are statements that allow you to create the trigger. The database upgrade will drop them again;

Code: Select all

create or replace function process_idSearchDataUpdateFTS() RETURNS trigger AS $$ 
begin 
  new.ContentTypeIdx  := to_tsvector('english', new.ContentType);
  new.ContentGroupIdx := to_tsvector('english', new.ContentGroup); 
  new.ContentValueIdx := to_tsvector('english', new.ContentValue); 
  return new; 
end; 
$$ LANGUAGE plpgsql;

create trigger idSearchDataUpdateFTS before insert or update on idSearchData for each row 
  execute procedure process_idSearchDataUpdateFTS();
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

gcurrie
Posts: 25
Joined: 02 May 10 2:55

Re: Error updating to v5 build 2960

Post by gcurrie » 07 Jun 20 20:34

Hert:

I ran the supplied code to create the trigger function and trigger.

Upon trying to update the database via client connection I now get a new error:

Unable to connect to the database.

The database reports:
Something went wrong while upgrading your PostgreSQL Catalog towards version 3023 (last successful step is 3021)

TPostgreSQLConnection: Execution of query failed (PostgreSQL: Error: syntax error at end of input
LINE 1: drop function process_idSearchDataUpdateFTS

Severity: ERROR
SQL State: 42601
Primary Error: syntax error at end of input
Character 44).


I notice all table names, triggers and trigger functions are camel cased in your example. From within PG Admin 4 on Windows Server 2016 I see table names are ALL lower case. Could there be an issue with casing?
Gordon Currie
Photo Supreme 5.4 PostgreSQL on Windows Server Essentials 2016

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: Error updating to v5 build 2960

Post by Hert » 07 Jun 20 21:18

There’s nothing wrong with that statement. Did you install psu with the correct PostgreSQL version selected?

Names are not case sensitive. I don’t understand why you didn’t have the trigger and function in the first place. Before this alter, psu couldn’t function properly without that trigger. So it must have existed.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

gcurrie
Posts: 25
Joined: 02 May 10 2:55

Re: Error updating to v5 build 2960

Post by gcurrie » 07 Jun 20 23:22

Hert:

Yes, correct PostgreSQL version always.

Obviously casing is a non-issue.

The script you supplied me enables me to re-create trigger and trigger function, but then the upgrade code successfully DROPS the trigger and then fails on DROPPING the trigger function. If I run the update again, I'm right back to my original error.

I've been upgrading PSU versions all spring, but this is the first one this year that required a database change. Usually they go quickly and easily.
I'm not sure of the version prior; I didn't think to check as I've never had a problem before (upgrading since PSU first shipped). It has been at least a month since my last upgrade.
Gordon Currie
Photo Supreme 5.4 PostgreSQL on Windows Server Essentials 2016

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: Error updating to v5 build 2960

Post by Hert » 08 Jun 20 8:06

This is the database upgrade the database from version 3021 to version 3022;

Code: Select all

[edit Hert; removed the lines]
Again, it sounds like something is not right in your database.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

gcurrie
Posts: 25
Joined: 02 May 10 2:55

Re: Error updating to v5 build 2960

Post by gcurrie » 08 Jun 20 20:58

Hert:

Thanks for this script. I was able to update the database.

I had to modify the script. When I executed "drop function process_idSearchDataUpdateFTS;" I got a syntax error. I had to DROP that function manually.
Then I was able to run the next two lines without errors.

Connecting an updated client updated the version to 3023.

I compact the database weekly. If there is anything I can do to ensure a healthy database, I'm open to suggestions.
Gordon Currie
Photo Supreme 5.4 PostgreSQL on Windows Server Essentials 2016

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: Error updating to v5 build 2960

Post by Hert » 08 Jun 20 21:37

That’s great. Apart from compacting, I have no suggestions.

Hope you’ll notice the new and improved search speed in the search box
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

tonydeancoscia
Posts: 1
Joined: 14 Jun 20 17:54

Re: Error updating to v5 build 2960

Post by tonydeancoscia » 14 Jun 20 17:58

Gordon,

I'm having the same problem. Can you walk me through the steps? I don't understand the entry by Hert which says [edit Hert; removed the lines]

Thanks,

Dean

Post Reply