Bug #646: Problems inserting new records

Miguel Juan (mjuan(at)cibal(dot)es) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Problems inserting new records

Long Description
Hello,

I have installed Postgresql server 7.2.1 on a windows XP, and the server runs well. I have done a dump from a database that is working on a RH Linux 7.2 and with PG 7.1.3, and I can make selects inserts, and updates on tables on the new server.

But the problem comes when I try to make a insert on a view using an appropiate rule (This works with postgresql 7.1.3 and 7.1.2)

I get the next Error (The same error in the server side using pgsql or using ODBC on a client):

SearchSys Cache: Bad cache id 27

Sample Code
\connect - postgres
DROP INDEX "i13gdinformesm_vol";
DROP INDEX "i9gdinformesm_atributo";
DROP INDEX "i12gdinformesm_codserv";
DROP INDEX "i11gdinformesm_glosario";
DROP INDEX "i10gdinformesm_uf";
DROP INDEX "i4informesm";
DROP INDEX "i3informesm";
DROP INDEX "i2informesm";
DROP INDEX "gdinformesm_codiinfo_key";
DROP INDEX "i6informesm";
DROP INDEX "i8informesm";
DROP INDEX "i7informesm";
DROP INDEX "i5informesm";
DROP TABLE "gdinformesm";

CREATE TABLE "gdinformesm" (
"codiinfo" integer DEFAULT nextval('gdinformesm_codiinfo_seq'::text) NOT NULL,
"fichero" character varying(50),
"vol" character(4),
"descripcion" character varying(30),
"fecha" date,
"hora" time DEFAULT 'now',
"codserv" integer,
"codmedico" integer,
"tipo" integer,
"validado" character(1) DEFAULT 'N' NOT NULL,
"carpeta" integer,
"glosario" integer,
"uf" integer,
"paciente" integer NOT NULL,
"atributo" integer,
"grupo" integer NOT NULL,
"pgrupo" character(1) DEFAULT '-' NOT NULL,
"potros" character(1) DEFAULT '-' NOT NULL,
CONSTRAINT "gdinformesm_potros" CHECK ((((potros = 'r'::bpchar) OR (potros = 'w'::bpchar)) OR (potros = '-'::bpchar))),
CONSTRAINT "gdinformesm_validado" CHECK ((((validado = 'S'::bpchar) OR (validado = 'N'::bpchar)) OR (validado = 'B'::bpchar))),
CONSTRAINT "gdinformesm_pgrupo" CHECK ((((pgrupo = 'r'::bpchar) OR (pgrupo = 'w'::bpchar)) OR (pgrupo = '-'::bpchar)))
);

REVOKE ALL on "gdinformesm" from PUBLIC;
GRANT ALL on "gdinformesm" to "postgres";
GRANT ALL on "gdinformesm" to GROUP "administradores";

CREATE INDEX "i5informesm" on "gdinformesm" using btree ( "paciente" "int4_ops" );

CREATE INDEX "i7informesm" on "gdinformesm" using btree ( "grupo" "int4_ops", "pgrupo" "bpchar_ops" );

CREATE INDEX "i8informesm" on "gdinformesm" using btree ( "potros" "bpchar_ops" );

CREATE INDEX "i6informesm" on "gdinformesm" using btree ( "codmedico" "int4_ops" );

CREATE UNIQUE INDEX "gdinformesm_codiinfo_key" on "gdinformesm" using btree ( "codiinfo" "int4_ops" );

CREATE INDEX "i2informesm" on "gdinformesm" using btree ( "fichero" "varchar_ops" );

CREATE INDEX "i3informesm" on "gdinformesm" using btree ( "descripcion" "varchar_ops" );

CREATE INDEX "i4informesm" on "gdinformesm" using btree ( "carpeta" "int4_ops" );

CREATE INDEX "i10gdinformesm_uf" on "gdinformesm" using btree ( "uf" "int4_ops" );

CREATE INDEX "i11gdinformesm_glosario" on "gdinformesm" using btree ( "glosario" "int4_ops" );

CREATE INDEX "i12gdinformesm_codserv" on "gdinformesm" using btree ( "codserv" "int4_ops" );

CREATE INDEX "i9gdinformesm_atributo" on "gdinformesm" using btree ( "atributo" "int4_ops" );

CREATE INDEX "i13gdinformesm_vol" on "gdinformesm" using btree ( "vol" "bpchar_ops" );

\connect - mjuan
DROP VIEW "gdinformes";

REVOKE ALL on "gdinformes" from PUBLIC;
GRANT ALL on "gdinformes" to PUBLIC;
GRANT ALL on "gdinformes" to "postgres";
GRANT ALL on "gdinformes" to "mjuan";

CREATE VIEW "gdinformes" as SELECT gdinformesm.codiinfo, gdinformesm.fichero, gdinformesm.vol, gdinformesm.descripcion, gdinformesm.fecha, gdinformesm.hora, gdinformesm.codserv, gdinformesm.codmedico, gdinformesm.tipo, gdinformesm.validado, gdinformesm.carpeta, gdinformesm.glosario, gdinformesm.uf, gdinformesm.paciente, gdinformesm.atributo, gdinformesm.grupo, gdinformesm.pgrupo, gdinformesm.potros FROM gdinformesm WHERE (((gdinformesm.codmedico = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text(getpgusername())))) OR (((gdinformesm.pgrupo = 'r'::bpchar) OR (gdinformesm.pgrupo = 'w'::bpchar)) AND (gdinformesm.grupo = ANY (SELECT gdgrupmed.codgrupo FROM gdgrupmed WHERE (gdgrupmed.codmed = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text(getpgusername())))))))) OR ((gdinformesm.potros = 'r'::bpchar) OR (gdinformesm.potros = 'w'::bpchar)));

CREATE RULE gdinformes_update AS ON UPDATE TO gdinformes DO INSTEAD UPDATE gdinformesm SET fichero = new.fichero, vol = new.vol, descripcion = new.descripcion, fecha = new.fecha, hora = new.hora, codserv = new.codserv, codmedico = new.codmedico, tipo = new.tipo, validado = new.validado, carpeta = new.carpeta, glosario = new.glosario, uf = new.uf, paciente = new.paciente, atributo = new.atributo, grupo = new.grupo, pgrupo = new.pgrupo, potros = new.potros WHERE ((gdinformesm.codiinfo = new.codiinfo) AND (((old.codmedico = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text("current_user"())))) OR ((old.pgrupo = 'w'::bpchar) AND (old.grupo = ANY (SELECT gdgrupmed.codgrupo FROM gdgrupmed WHERE (gdgrupmed.codmed = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text("current_user"())))))))) OR (old.potros = 'w'::bpchar)));

CREATE RULE gdinformes_delete AS ON DELETE TO gdinformes DO INSTEAD DELETE FROM gdinformesm WHERE ((gdinformesm.codiinfo = old.codiinfo) AND (((old.codmedico = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text("current_user"())))) OR ((old.pgrupo = 'w'::bpchar) AND (old.grupo = ANY (SELECT gdgrupmed.codgrupo FROM gdgrupmed WHERE (gdgrupmed.codmed = (SELECT gdmedicos.codmedico FROM gdmedicos WHERE (gdmedicos.clave = text("current_user"())))))))) OR (old.potros = 'w'::bpchar)));

CREATE RULE gdinformes_insert AS ON INSERT TO gdinformes DO INSTEAD INSERT INTO gdinformesm (codiinfo, fichero, vol, descripcion, fecha, hora, codserv, codmedico, tipo, validado, carpeta, glosario, uf, paciente, atributo, grupo, pgrupo, potros) VALUES (nextval('gdinformesm_codiinfo_seq'::text), new.fichero, new.vol, new.descripcion, new.fecha, "time"('now'::text), new.codserv, new.codmedico, new.tipo, gdinformes_validado_check(new.validado), new.carpeta, new.glosario, new.uf, new.paciente, new.atributo, new.grupo, gdinformes_pgrupo_check(new.pgrupo), gdinformes_potros_check(new.potros));

No file was uploaded with this report