postresql update

Hans Scheffers

28 Jan 2004 28 Jan '04

12:16 p.m.

Hi all, I think in the update scripts for postgres there is an error. Twice there is a column added with attribute 'NOT NULL'. This isn't allowed in postgres, first you have to add the column and after that you should alter it with ' alter table alter column set not null' Grtz -- Hans Scheffers

Show replies by date

Robert Kehl

28 Jan 28 Jan

7:42 p.m.

On Wednesday, January 28, 2004 1:16 PM Hans Scheffers wrote:

...

I think in the update scripts for postgres there is an error. Twice there is a column added with attribute 'NOT NULL'. This isn't allowed in postgres, first you have to add the column and after that you should alter it with ' alter table alter column set not null'

Could you quote a URL as source of this info? Thank you, Robert Kehl -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Tel. +49 (0)6172 4832388

Hans Scheffers

29 Jan 29 Jan

1:29 p.m.

Not an URL, i will have to look that up; but from postgresql self: Welcome to psql 7.3.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit =# create table tmp ( (# col1 varchar(10) (# ) -# ; CREATE TABLE =# alter table tmp add column col2 varchar(10) not null; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ... SET NOT NULL. =# On Wed, 2004-01-28 at 20:42, Robert Kehl wrote:

...

On Wednesday, January 28, 2004 1:16 PM Hans Scheffers wrote:

...
I think in the update scripts for postgres there is an error. Twice there is a column added with attribute 'NOT NULL'. This isn't allowed in postgres, first you have to add the column and after that you should alter it with ' alter table alter column set not null'

Could you quote a URL as source of this info?

Thank you,

Robert Kehl -- Hans Scheffers JifLin Automatisering email:hans@jiflin.nl web: http://www.jiflin.nl

Robert Kehl

31 Jan 31 Jan

7:28 a.m.

On Thursday, January 29, 2004 2:29 PM Hans Scheffers wrote:

...

CREATE TABLE =# alter table tmp add column col2 varchar(10) not null; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ... SET NOT NULL. =#

Am I getting you right, the PostGreSQL-Update doesn't run through unless you manually change the source code to no longer include any 'add column xyz type(n) NOT NULL'? This were a nasty bug indeed... Could I get you to open a bug report on http://bugs.otrs.org/ ? Thank you very much, Robert Kehl -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Tel. +49 (0)6172 4832388

Hans Scheffers

9:03 a.m.

It does run, only there are errors generated, which I luckily saw and edited manually... But anyway, I filed the bug on mozilla :-) grtz On Sat, 2004-01-31 at 08:28, Robert Kehl wrote:

...

On Thursday, January 29, 2004 2:29 PM Hans Scheffers wrote:

...
CREATE TABLE =# alter table tmp add column col2 varchar(10) not null; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ... SET NOT NULL. =#

Am I getting you right, the PostGreSQL-Update doesn't run through unless you manually change the source code to no longer include any 'add column xyz type(n) NOT NULL'? This were a nasty bug indeed...

Could I get you to open a bug report on http://bugs.otrs.org/ ?

Thank you very much,

Robert Kehl

-- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Tel. +49 (0)6172 4832388

_______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting für Ihr OTRS System? => http://www.otrs.de/ -- Hans Scheffers JifLin Automatisering email:hans@jiflin.nl web: http://www.jiflin.nl

Arne Georg Gleditsch

30 Jan 30 Jan

9:04 p.m.

* Hans Scheffers

...

I think in the update scripts for postgres there is an error. Twice there is a column added with attribute 'NOT NULL'. This isn't allowed in postgres, first you have to add the column and after that you should alter it with ' alter table alter column set not null'

(I've been bitten by this as well.) On the topic of Postgres, might I also suggest ditching the bigint and serial8-types in favour of more conventional int and serial-types? As it is, OTRS is producing queries that don't take advantage of indexes. I've seen this noticeably affect performace with no more than 5-10k tickets and 20-30k articles. Literal integers default to int4 in Postgres, and because of this conditions like "... where foo = 42" will result in a sequential scan instead of an index lookup if foo is bigint. Even if foo is indexed. Alternately, this could be fixed by explicitly casting all literal integers used in comparisons with bigint-attributes to bigint. This would probably be less than compatible, and seriously, who needs 64 bits of article space anyway? 32 bits, even signed, will let you enter one article every second 24/7 for 34 years. (And by the time you need more than this, the lack of indexes is going to _really_ bite. :) Arne.

7839

Age (days ago)

7842

Last active (days ago)

List overview

Download

5 comments

3 participants

participants (3)

Arne Georg Gleditsch
Hans Scheffers
Robert Kehl