Postgresql upgrade not working from 0.6pl3 to current

[daFool]

Sep 5 12:38:24 error postgres[17298]: [9-1] ERROR: column "authorid" does not exist
Sep 5 12:38:24 error postgres[17298]: [10-1] NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "serendipity_config_pkey" for table "serendipity_config"
Sep 5 12:38:24 error postgres[17298]: [11-1] ERROR: syntax error at or near "after" at character 85
Sep 5 12:38:24 error postgres[17298]: [12-1] ERROR: adding columns with defaults is not implemented
Sep 5 12:38:24 error postgres[17298]: [12-2] HINT: Add the column, then use ALTER TABLE SET DEFAULT.
Sep 5 12:38:24 error postgres[17298]: [13-1] ERROR: column "path" does not exist
Sep 5 12:38:24 error postgres[17298]: [14-1] ERROR: syntax error at or near "(" at character 52
Sep 5 12:38:24 error postgres[17298]: [15-1] ERROR: adding columns with defaults is not implemented
Sep 5 12:38:24 error postgres[17298]: [15-2] HINT: Add the column, then use ALTER TABLE SET DEFAULT.

Another thing that does not work is updating .htaccess, upgrader recognizes that it should be updated but does nothing to it.

I can and probably will fix all postgresql errors with a little work but should I do so or wait for official fixes? Shall I post diffs/patches to somewhere if I do?

I don't have energy to find out why .htaccess will not be updated but that I can fix manually by copying changes from scracth installation.

We are also in process of translating Serendipity to Finnish, has someone else done it or in progress doing it?

[daFool]

I can and probably will fix all postgresql errors with a little work but should I do so or wait for official fixes? Shall I post diffs/patches to somewhere if I do?

I don't have energy to find out why .htaccess will not be updated but that I can fix manually by copying changes from scracth installation.

We are also in process of translating Serendipity to Finnish, has someone else done it or in progress doing it?

I guess no-one had tested those update-scripts before with postgresql? At least 7.4.2 needed a lot of mods to work. If some one is interested for the mods I did, I am happy to provide. Unfortunately they are not compatible with automatic upgrading and they have to be run by hand and either require sed* or editing by hand. There were several mistakes in following form

Code: Select all

ALTER TABLE foobar ADD goo int(11) NOT NULL default '1';

Postgresql does not support field widths with integer. Postgresql does not support setting defaults when adding columns to an existing table. And the "not null"-constraint, afaik, should have it's own clause. At least the following syntax is legal:

Code: Select all

ALTER TABLE foobar ADD COLUMN goo INT;
ALTER TABLE foobar ALTER COLUMN goo SET default 1;
UPDATE TABLE foobar SET goo=1;
ALTER TABLE foobar ALTER COLUMN goo SET NOT NULL;

*(sed -i /serendipity_/your table-prefix/g up*_postgresql.sql)

[garvinhicking]

Hi!

Thanks a lot for your tests and your report. With your help I could correct many of those mistakes. Could you please have a look at those files again and see if they're also correct for you?

Regards,
Garvin.

[daFool]

Hi!

Thanks a lot for your tests and your report. With your help I could correct many of those mistakes. Could you please have a look at those files again and see if they're also correct for you?

Regards,
Garvin.

Took todays cvs-snapshot. You managed to fix some of them but not all.

Code: Select all

Sep  6 18:12:38 fault postgres[5539]: [9-1] ERROR:  column "authorid" does not exist
Sep  6 18:12:38 fault postgres[5539]: [10-1] NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "serendipity_config_pkey" for table "serendipity_config"
Sep  6 18:12:39 fault postgres[5539]: [11-1] ERROR:  syntax error at or near "UPDATE" at character 63
Sep  6 18:12:39 fault postgres[5539]: [12-1] ERROR:  column "oldmime" does not exist
Sep  6 18:12:39 fault postgres[5539]: [13-1] ERROR:  column "oldmime" of relation "serendipity_images" does not exist
Sep  6 18:12:39 fault postgres[5539]: [14-1] ERROR:  column "mime" contains null values

And again the automatic update resulted a .htaccess file that does not work. All "clicks" result 404.

I could go through your changes, fix what is broken and post diff -c's here?

[garvinhicking]

Hi daFool!

Code: Select all

Took todays cvs-snapshot. You managed to fix some of them but not all. 
[code]

Ah, darn. I did some testing and it worked, but maybe I wasn't using 0.6-pl3 but 0.6.2 instead.

Could you maybe look which files from db/* still need tweaking exactly?

About your .htaccess problem - can you post what your file looks like?

Do you have MSN/IRC/ICQ available so that we could work it out together?

[quote]
I could go through your changes, fix what is broken and post diff -c's here?
[/quote]

That would be lovely! :-)

Thanks a lot,
Garvin

[daFool]

Hi daFool!

Could you maybe look which files from db/* still need tweaking exactly?

About your .htaccess problem - can you post what your file looks like?

Do you have MSN/IRC/ICQ available so that we could work it out together?

I could go through your changes, fix what is broken and post diff -c's here?

That would be lovely!

Thanks a lot,
Garvin

Actually it would be an overkill to do diff -c. You just missed one missing semicolon in the very first line of db_update_0.6.8_0.6.9_postgres.sql

(I can usually be reached from irc with nick daFool/IRCNet?. Sometimes I do yahoo with mosfi.)

I have not tried if this makes the automatic update to work, as I inserted every file manually after seding them for prefix. I will try it tomorrow, I will post the .htaccess file differences at the same time.

[garvinhicking]

Hi dafool!

Thanks, I merged your patch - I didn't spot that one, must have happend during copy&paste.

Say - how about, if you enjoy Serendipity, to join our Mailinglist and become one of our postgreSQL-support guy? Joseph Tate is the only other one currently looking into it, and he's sometimes out of time to check our upgrade SQL bits+pieces. I would love to be able to use your experience

About the .htacces file, I'm waiting for tomorrow when you can post it. Thanks!

Regards,
Garvin

[daFool]

Say - how about, if you enjoy Serendipity, to join our Mailinglist and become one of our postgreSQL-support guy? Joseph Tate is the only other one currently looking into it, and he's sometimes out of time to check our upgrade SQL bits+pieces. I would love to be able to use your experience

About the .htacces file, I'm waiting for tomorrow when you can post it. Thanks!

I do enjoy serendipity, our community has three installations running. And I have two test installations to play with before upgrading to next release. I joined the list and will contribute if there is anything I can do and have time to do.

The difference between a scratch installation of .htaccess and upgrade installation .htaccess is just two lines. Scratch-installation has

Code: Select all

ErrorDocument 404 /~mos/serendipity/index.php
DirectoryIndex /~mos/serendipity/index.php

and upgraded installation has just:

Code: Select all

DirectoryIndex index.php