Ticket #94 (closed Bug report: fixed)

Opened 13 years ago

Last modified 12 years ago

schema update to version 35 is not compatible with postgresql 7.4

Reported by: kaouete@… Owned by: max-horvath
Priority: high Milestone: WifiDog Auth Server 1.0
Component: Auth server, Other Version: Auth Server SVN
Keywords: Cc:

Description

Hi,

when updating my schema to version 35 with current svn (r985) i get this error :

execSqlUpdate(): An error occured while executing the following SQL query :

BEGIN;


UPDATE schema_info SET value='35' WHERE tag='schema_version';
CREATE TABLE servers ( 
  server_id text NOT NULL PRIMARY KEY,
  is_default_server boolean NOT NULL DEFAULT FALSE,
  name text NOT NULL DEFAULT 'Unnamed server' CHECK (name<>''),
  creation_date date NOT NULL DEFAULT now(),
  hostname text NOT NULL DEFAULT 'localhost' CHECK (name<>''),
  ssl_available BOOLEAN NOT NULL DEFAULT FALSE,
  gmaps_api_key text
);
INSERT INTO servers (server_id, is_default_server, name, creation_date, hostname, ssl_available, gmaps_api_key) VALUES ('auth-wifi-crazydwarves-org', TRUE, 'Unnamed server', (SELECT creation_date FROM networks GROUP BY (creation_date) ORDER BY min(creation_date) LIMIT 1), 'auth.wifi.crazydwarves.org', TRUE, 'ABQIAAAA5q67PKeUpLHnR1x60n17GxTPNWs86dub6uFngnGM2hkyRN6FHxRAFHmlyBKr05GOJ8l2pFSMZVcIEw');
ALTER TABLE networks ADD COLUMN gmaps_initial_latitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_longitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_zoom_level integer;
ALTER TABLE networks ADD COLUMN gmaps_map_type text NOT NULL DEFAULT 'G_MAP_TYPE' CHECK (gmaps_map_type<>'');
UPDATE networks SET gmaps_initial_latitude = '48.856', gmaps_initial_longitude = '2.348', gmaps_initial_zoom_level = '5';

COMMIT;
VACUUM ANALYZE;

Error message :
ERROR: adding columns with defaults is not implemented HINT: Add the column, then use ALTER TABLE SET DEFAULT.

you can see on the Notes section of  http://www.postgresql.org/docs/7.4/interactive/sql-altertable.html that :

In the current implementation of ADD COLUMN, default and NOT NULL clauses for the new column are not supported. The new column always comes into being with all values null. You can use the SET DEFAULT form of ALTER TABLE to set the default afterward. (You may also want to update the already existing rows to the new default value, using UPDATE.) If you want to mark the column non-null, use the SET NOT NULL form after you've entered non-null values for the column in all rows.

Thanks you :)

Change History

Changed 13 years ago by anonymous

here is my updated version :

BEGIN;


UPDATE schema_info SET value='35' WHERE tag='schema_version';
CREATE TABLE servers ( 
  server_id text NOT NULL PRIMARY KEY,
  is_default_server boolean NOT NULL DEFAULT FALSE,
  name text NOT NULL DEFAULT 'Unnamed server' CHECK (name<>''),
  creation_date date NOT NULL DEFAULT now(),
  hostname text NOT NULL DEFAULT 'localhost' CHECK (name<>''),
  ssl_available BOOLEAN NOT NULL DEFAULT FALSE,
  gmaps_api_key text
);
INSERT INTO servers (server_id, is_default_server, name, creation_date, hostname, ssl_available, gmaps_api_key) VALUES ('auth-wifi-crazydwarves-org', TRUE, 'Unnamed server', (SELECT creation_date FROM networks GROUP BY (creation_date) ORDER BY min(creation_date) LIMIT 1), 'auth.wifi.crazydwarves.org', TRUE, 'ABQIAAAA5q67PKeUpLHnR1x60n17GxTPNWs86dub6uFngnGM2hkyRN6FHxRAFHmlyBKr05GOJ8l2pFSMZVcIEw');
ALTER TABLE networks ADD COLUMN gmaps_initial_latitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_longitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_zoom_level integer;
ALTER TABLE networks ADD COLUMN gmaps_map_type text CHECK (gmaps_map_type<>'');
ALTER TABLE networks ALTER COLUMN gmaps_map_type SET DEFAULT 'G_MAP_TYPE';
UPDATE networks SET gmaps_map_type = 'G_MAP_TYPE';
ALTER TABLE networks ALTER COLUMN gmaps_map_type SET NOT NULL;
UPDATE networks SET gmaps_initial_latitude = '48.856', gmaps_initial_longitude = '2.348', gmaps_initial_zoom_level = '5';

COMMIT;
VACUUM ANALYZE;

Changed 13 years ago by benoitg

  • owner set to max-horvath

Changed 13 years ago by max-horvath

  • status changed from new to assigned
  • milestone set to WifiDog Auth Server 1.0

I'm sorry for that ... seems it's an error my developer machine runs Postgres 8.1 ...

I'll be fixing it in my next commit ...

Changed 13 years ago by max-horvath

  • status changed from assigned to closed
  • resolution set to fixed

Fixed in revision [986].

Note: See TracTickets for help on using tickets.