Обсуждение: Bug #646: Problems inserting new records

Miguel Juan (mjuan@cibal.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
thatis 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
and7.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.codmedicoFROM gdmedicos WHERE (gdmedicos.clave = text(getpgusername())))) OR (((gdinformesm.pgrupo =
'r'::bpchar)OR (gdinformesm.pgrupo = 'w'::bpchar)) AND (gdinformesm.grupo = ANY (SELECT gdgrupmed.codgrupo FROM
gdgrupmedWHERE (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.codgrupoFROM 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.codgrupoFROM 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

pgsql-bugs@postgresql.org writes:
> SearchSys Cache: Bad cache id 27

IIRC, this is a common symptom of a plpgsql.so library that's out of
sync with your backend (ie, 7.2 backend, 7.1 plpgsql.so).  I suspect
the failure is coming from a plpgsql function or trigger and has
nothing to do with the rules you showed us.

Check whether the declaration of plpgsql_call_handler points at the
right .so file.

            regards, tom lane