[pgpool-general: 2760] Re: Error message on pgpool log Unable to parse the query
Yugo Nagata
nagata at sraoss.co.jp
Mon Apr 14 15:33:01 JST 2014
Hi,
Thanks for your sending information.
I found that the problem is the table name alias "off". The following query
causes the same log message:
SELECT * FROM tbl AS off;
The alias "off" is accepted by PostgreSQL 9.1 but not 9.0 or older. pgpool 3.2
can't parse this since this uses older parser. However the query is sent to all
backends and executed, that is, load-balance doesn't work.
To avoid the message and to use load-balance, you can use pgpool 3.3 that uses
newer parser and can accept the query successfully. Or rewrite your application
not to use alias 'off' or quote the aliase by double-quotation like:
SELECT "off".* FROM tbl AS "off";
On Fri, 11 Apr 2014 09:46:02 +0000
NATAF Jeremie <Jeremie.NATAF at universcience.fr> wrote:
> Hi,
> Thank for your prompt response, i join log in debug mode and the structure of the table
>
> Tks
> Jérémie
>
> CREATE TABLE plagedates
> (
> pla_kid bigint NOT NULL, -- PLA_KID
> per_kid bigint, -- PER_KID
> pla_ddeb timestamp without time zone, -- Date début
> pla_dfin timestamp without time zone, -- Date fin
> CONSTRAINT plagedates_pkey PRIMARY KEY (pla_kid),
> CONSTRAINT fk_plagedat_contient1_periode FOREIGN KEY (per_kid)
> REFERENCES periode (per_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT sys_c00323976 CHECK (per_kid IS NOT NULL)
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE plagedates
> OWNER TO agenda;
> COMMENT ON TABLE plagedates
> IS 'Plage de dates';
> COMMENT ON COLUMN plagedates.pla_kid IS 'PLA_KID';
> COMMENT ON COLUMN plagedates.per_kid IS 'PER_KID';
> COMMENT ON COLUMN plagedates.pla_ddeb IS 'Date début';
> COMMENT ON COLUMN plagedates.pla_dfin IS 'Date fin';
>
>
> ----------
>
> CREATE TABLE periode
> (
> per_kid bigint NOT NULL, -- PER_KID
> com_kid bigint, -- COM_KID
> per_llib character varying(255), -- Libellé
> per_lcouleur character varying(7),
> CONSTRAINT periode_pkey PRIMARY KEY (per_kid),
> CONSTRAINT fk_periode_est_ratta_comporte FOREIGN KEY (com_kid)
> REFERENCES comportement (com_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT sys_c00323970 CHECK (com_kid IS NOT NULL)
> )
> ----------------
> CREATE TABLE offre
> (
> off_kid bigint NOT NULL, -- OFF_KID
> tof_kid bigint, -- TOF_KID
> com_kid bigint, -- COM_KID
> top_kid bigint, -- TOP_KID
> off_ctype character(3), -- Offre = OFF et sous-offre = SOF
> off_cetat character(3) DEFAULT 'MOD'::bpchar, -- Etat, MOD:Modifié, AVA: A valider, VAL:Validé, ASU: A supprimer, SUP: Supprimé, PUB:Publié
> off_ddemarrage timestamp without time zone, -- Date de démarrage réel de l'offre
> off_dperemption timestamp without time zone, -- Date péremption
> off_npriorite smallint DEFAULT 0, -- Notion de priorité 0 (faible priorité) ou 1 (forte priorité) ou -1 (masquée)
> off_bcritere character(1) DEFAULT 'O'::bpchar, -- Indique si l'offre fait partie de la liste des critéres offre/sous-offre
> off_bpermanent character(1) DEFAULT 'N'::bpchar, -- Permet d'indiquer si une offre est déclarée comme permanente (O ou N)
> off_bseance character(1) DEFAULT 'N'::bpchar, -- Permet de spécifier si l'offre fonctionne comme la Cité des Enfants
> off_mtarifeur numeric(8,2), -- Tarif Euros
> off_nduree smallint, -- Durée de la séance convertie en mn
> off_dheureouv timestamp without time zone, -- Heure ouverture
> off_dheureferm timestamp without time zone, -- Heure fermeture
> off_limage character varying(255), -- adresse url de l image
> off_lresa character varying(255), -- Permet de saisir un né de tél
> off_bhmoteur character(1) DEFAULT 'N'::bpchar, -- Picto handicapé moteur
> off_bsourd character(1) DEFAULT 'N'::bpchar, -- Picto personne sourde
> off_bnonvoyant character(1) DEFAULT 'N'::bpchar, -- Picto personne non voyante
> off_bslangsignes character(1) DEFAULT 'N'::bpchar, -- Picto langage des signes
> off_bhmental character(1) DEFAULT 'N'::bpchar, -- Picto handicapé mental
> off_cetatimage character(3) DEFAULT 'MOD'::bpchar, -- Etat de l'image de présentation : MOD (modifié), PUB (publié)
> off_lcss character varying(255), -- Feuille de style de l'appli Front-Office é utiliser pour un agenda thématique basé cette offre
> off_lbandeau character varying(255), -- Bandeau de l'appli Front-Office é utiliser pour un agenda thématique basé cette offre
> off_contact character varying(300),
> off_comp_kid bigint,
> off_logo character varying(255),
> groupe_kid bigint,
> off_ddatepub timestamp without time zone,
> off_dcreation timestamp without time zone,
> off_ddernmod timestamp without time zone,
> off_kulogin character varying(32),
> off_nchgmentale bigint,
> off_ndelaireco bigint,
> off_ncaract bigint,
> CONSTRAINT offre_pkey PRIMARY KEY (off_kid),
> CONSTRAINT fk_off_groupe FOREIGN KEY (groupe_kid)
> REFERENCES groupe (groupe_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_offre_off_act_typeoffr FOREIGN KEY (tof_kid)
> REFERENCES typeoffre (tof_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_offre_off_com_comporte FOREIGN KEY (com_kid)
> REFERENCES comportement (com_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT fk_offre_off_top_toponyme FOREIGN KEY (top_kid)
> REFERENCES toponyme (top_kid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT ckc_off_bcritere_offre CHECK (off_bcritere = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bhmental_offre CHECK (off_bhmental = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bhmoteur_offre CHECK (off_bhmoteur = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bnonvoyant_offre CHECK (off_bnonvoyant = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bpermanent_offre CHECK (off_bpermanent = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bseance_offre CHECK (off_bseance = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bslangsignes_offre CHECK (off_bslangsignes = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_bsourd_offre CHECK (off_bsourd = ANY (ARRAY['O'::bpchar, 'N'::bpchar])),
> CONSTRAINT ckc_off_ctype_offre CHECK (off_ctype = ANY (ARRAY['OFF'::bpchar, 'SOF'::bpchar, 'AGE'::bpchar])),
> CONSTRAINT ckc_off_npriorite_offre CHECK (off_npriorite = ANY (ARRAY[0, 1, (-1)])),
> CONSTRAINT sys_c00323937 CHECK (off_ctype IS NOT NULL),
> CONSTRAINT sys_c00323938 CHECK (off_cetat IS NOT NULL),
> CONSTRAINT sys_c00323939 CHECK (off_npriorite IS NOT NULL),
> CONSTRAINT sys_c00323940 CHECK (off_bcritere IS NOT NULL),
> CONSTRAINT sys_c00323941 CHECK (off_bpermanent IS NOT NULL),
> CONSTRAINT sys_c00323942 CHECK (off_bseance IS NOT NULL),
> CONSTRAINT sys_c00323943 CHECK (off_bhmoteur IS NOT NULL),
> CONSTRAINT sys_c00323944 CHECK (off_bsourd IS NOT NULL),
> CONSTRAINT sys_c00323945 CHECK (off_bnonvoyant IS NOT NULL),
> CONSTRAINT sys_c00323946 CHECK (off_bslangsignes IS NOT NULL),
> CONSTRAINT sys_c00323947 CHECK (off_bhmental IS NOT NULL)
> )
> -----Message d'origine-----
> De : Yugo Nagata [mailto:nagata at sraoss.co.jp]
> Envoyé : jeudi 10 avril 2014 05:12
> À : NATAF Jeremie
> Cc : pgpool-general at pgpool.net
> Objet : Re: [pgpool-general: 2732] Error message on pgpool log Unable to parse the query
>
> Hi,
>
> I can't reprodce this. Could you please send me debug log and the table schemas of plagedates, periode, offre?
>
> On Tue, 8 Apr 2014 10:30:34 +0000
> NATAF Jeremie <Jeremie.NATAF at universcience.fr> wrote:
>
> > Hi everybody,
> > we used pgpool 3.2.6 with postgresql 9.2.4 on centos 6.3
> >
> > A sql query print a error log with pgpool, when we executed this
> > request directly on database server (without pgpool) , we didn't have
> > this messages
> >
> > the error on log :
> >
> > Unable to parse the query: "select pla.PLA_KID as PLA1_38_0_, pla.PLA_DFIN as PLA2_38_0_, pla.PLA_DDEB as PLA3_38_0_, pla.PER_KID as PER4_38_0_ from plagedates pla, periode per, offre off
> > where off.off_kid = 1
> > and per.com_kid = off.com_kid
> > and per.per_kid = pla.per_kid
> > and pla.pla_ddeb <= '08/04/2013'
> > and pla.pla_dfin >= '08/04/2015'
> >
> >
> > Is there an issue for error « unable to parse query »?
> >
> > Thks
> > Jérémie
> > [Universcience : Etablissement public du Palais de la découverte et de
> > la Cité des sciences et de l'industrie]
>
>
> --
> Yugo Nagata <nagata at sraoss.co.jp>
> [Universcience : Etablissement public du Palais de la découverte et de la Cité des sciences et de l'industrie]
--
Yugo Nagata <nagata at sraoss.co.jp>
More information about the pgpool-general
mailing list