Hello
I'm using pgpool in replication mode and i have a big problem with
tables with same name. It happens with postgres 8.4.4 and pgpool-II
2.3.3. I'm attaching a simplified dump of a database. It has 2 tables:
mytable in schema public and in schema buffor. They both have very
different columns. And when i try to make an insert:
"INSERT INTO mytable (p5, p9, p8, p6, p7, p2, p3, p4, p10, p11, p12,
p13, p14, p15, p17, p18, p1) values ('2010-08-04 15:38:43.808000
+02:00:00', '2010-08-04 15:38:43.791000 +02:00:00', NULL, NULL, true,
599112, 599113, 1, NULL, NULL, NULL, NULL, NULL, null, NULL, NULL,
299299);"
which should go into public.mytable i get an error:
"ERROR: column "p59" of relation "mytable" does not exist
LINE 1: ... p17, p18, p1) values ('2010-08-04 15:38:43.808000 +02:00:00..."
The problem that there's no column p59 in public.mytable - it exists
only in buffor.mytable.
Pgpool log says:
"SimpleQuery: Error or notice message from backend: : DB node id: 0
backend pid: 18429 statement: INSERT INTO "mytable"("p5", "p9", "p8",
"p6", "p7", "p2", "p3", "p4", "p10", "p11", "p12", "p13", "p14",
"p15", "p17", "p18", "p1", "p59") VALUES ('2010-08-04 15:38:43.808000
+02:00:00','2010-08-04 15:38:43.791000
+02:00:00',NULL,NULL,'t'::bool,599112,599113,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,299299,'2010-08-06
15:14:37.669948+02') message: column "p59" of relation "mytable" does
not exist"
It seems then that it's pgpool which added that column to my insert.
It works ok when i explicit write public.mytable in insert but that
insert is autogenerated by an hibernate so i can't workaround it that
way.That insert also works ok if i run it on same postgres but without
pgpool.
Any idea how to fix it?
--
Pozdrawiam
Marcin Kwiatkowski
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- Name: buffor; Type: SCHEMA; Schema: -; Owner: pgpool-test
--
CREATE SCHEMA buffor;
ALTER SCHEMA buffor OWNER TO "pgpool-test";
SET search_path = public, pg_catalog;
--
-- Name: pgpool_recovery(text, text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pgpool_recovery(text, text, text) RETURNS boolean
LANGUAGE c STRICT
AS '$libdir/pgpool-recovery', 'pgpool_recovery';
ALTER FUNCTION public.pgpool_recovery(text, text, text) OWNER TO postgres;
--
-- Name: pgpool_remote_start(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pgpool_remote_start(text, text) RETURNS boolean
LANGUAGE c STRICT
AS '$libdir/pgpool-recovery', 'pgpool_remote_start';
ALTER FUNCTION public.pgpool_remote_start(text, text) OWNER TO postgres;
SET search_path = buffor, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: mytable; Type: TABLE; Schema: buffor; Owner: pgpool-test; Tablespace:
--
CREATE TABLE mytable (
p1 integer NOT NULL,
p2 character varying(30),
p3 character varying(30),
p4 character varying(15),
p5 timestamp without time zone,
p6 character varying(15),
p7 character varying(15),
p8 character varying(1),
p9 character varying(50),
p10 character varying(1),
p11 character varying(1),
p12 character varying(11),
p13 character varying(13),
p14 character varying(50),
p15 integer,
p16 character varying(30),
p17 character varying(8),
p18 character varying(4),
p19 character varying(6),
p20 character varying(20),
p21 character varying(15),
p22 character varying(30),
p23 integer,
p24 character varying(30),
p25 character varying(8),
p26 character varying(4),
p27 character varying(20),
p28 character varying(6),
p29 character varying(11),
p30 timestamp without time zone,
p31 character varying(200),
p32 timestamp without time zone,
p33 character varying(15),
p34 smallint,
p35 timestamp without time zone,
p36 integer DEFAULT 0,
p37 integer DEFAULT 0,
p38 integer DEFAULT 0,
p39 integer DEFAULT 0,
p40 integer DEFAULT 0,
p41 bigint,
p42 bigint,
p43 bigint,
p44 integer,
p45 bigint,
p46 bigint,
p47 bigint,
p48 bigint,
p49 bigint,
p50 bigint,
p51 bigint,
p52 bigint,
p53 bigint,
p54 bigint,
p55 smallint,
p56 character varying,
p57 character varying,
p58 bigint,
p59 timestamp without time zone DEFAULT now(),
p60 bigint
);
ALTER TABLE buffor.mytable OWNER TO "pgpool-test";
SET search_path = public, pg_catalog;
--
-- Name: mytable; Type: TABLE; Schema: public; Owner: pgpool-test; Tablespace:
--
CREATE TABLE mytable (
p1 bigint NOT NULL,
p2 bigint,
p3 bigint,
p4 bigint,
p5 timestamp without time zone,
p6 integer,
p7 boolean DEFAULT true,
p8 bigint,
p9 timestamp without time zone,
p10 character(1),
p11 bigint,
p12 bigint,
p13 bigint,
p14 timestamp without time zone,
p15 character varying,
p16 bigint,
p17 integer,
p18 character varying
);
ALTER TABLE public.mytable OWNER TO "pgpool-test";
SET search_path = buffor, pg_catalog;
--
-- Data for Name: mytable; Type: TABLE DATA; Schema: buffor; Owner: pgpool-test
--
COPY mytable (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34, p35, p36, p37, p38, p39, p40, p41, p42, p43, p44, p45, p46, p47, p48, p49, p50, p51, p52, p53, p54, p55, p56, p57, p58, p59, p60) FROM stdin;
\.
SET search_path = public, pg_catalog;
--
-- Data for Name: mytable; Type: TABLE DATA; Schema: public; Owner: pgpool-test
--
COPY mytable (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18) FROM stdin;
\.
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
_______________________________________________
Pgpool-general mailing list
Pgpool-general@pgfoundry.org
http://pgfoundry.org/mailman/listinfo/pgpool-general
