[pgpool-general: 496] Re: Error on insert using pgpool and 2 postgresql servers

[Carman, Darren]

Yes, there is an error:

2012-05-23 17:00:07 BST 192.168.84.60 ERROR:  function
pgpool_regclass(unknown) does not exist at character 551
2012-05-23 17:00:07 BST 192.168.84.60 HINT:  No function matches the
given name and argument types. You might need to add explicit type
casts.
2012-05-23 17:00:07 BST 192.168.84.60 STATEMENT:  SELECT attname,
d.adsrc, coalesce((d.adsrc LIKE '%now()%' OR d.adsrc LIKE
'%''now''::text%') AND (a.atttypid = 'timestamp'::regtype::oid OR
a.atttypid = 'timestamp with time zone'::regtype::oid OR a.atttypid =
'date'::regtype::oid OR a.atttypid = 'time'::regtype::oid OR a.atttypid
= 'time with time zone'::regtype::oid) , false) FROM pg_catalog.pg_class
c, pg_catalog.pg_attribute a  LEFT JOIN pg_catalog.pg_attrdef d ON
(a.attrelid = d.adrelid AND a.attnum = d.adnum) WHERE c.oid = a.attrelid
AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid =
pgpool_regclass('geometry_columns') ORDER BY a.attnum
2012-05-23 17:00:07 BST 192.168.84.60 ERROR:  current transaction is
aborted, commands ignored until end of transaction block

I tried running this as postgres and as the other user. It worked for
postgres and not for the user.

I added public to the search path of the user and it now appears to be
working.

I'll give it some more testing today but thank you for pointing me in
the right direction and getting it working, very much appreciated. Can't
believe I missed this yesterday.

Regards
Darren


-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at postgresql.org] 
Sent: 24 May 2012 10:52
To: Carman, Darren
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 490] Error on insert using pgpool and 2
postgresql servers

Can you please look into PostgreSQL log to find an error message
"pgpool_regclass(unknown) does not exist" or some such? That might
elaborate the problem.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Yes, I installed it to my database and to template1 on both servers.
> 
> Is this function only used for inserts?
> 
> Running \df on both servers as postgres user shows:
>  public | pgpool_recovery | boolean | text, text, text | normal  
> public | pgpool_regclass | oid | cstring | normal  public | 
> pgpool_remote_start | boolean | text, text | normal  public | 
> pgpool_switch_xlog | text | text | normal
>  public | pgpool_walrecrunning | boolean |       | normal
> 
> The connections used for inserts were as another user, but as the 
> above are public I assume this isn't a problem (dblink functions are 
> public and useable).
> 
> 
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at postgresql.org]
> Sent: 24 May 2012 09:54
> To: Carman, Darren
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 490] Error on insert using pgpool and 2 
> postgresql servers
> 
> Are you sure that you installed pgpool_regclass in *all* the
databases?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> It is installed. Initially that was the only sql I installed but now 
>> I
> 
>> have built/installed all the packages/sql files from the install sql 
>> directory to see if I was missing anything.
>> 
>> I have pgpool-recovery.so, pgpool-regclass.so and 
>> pgpool-walrecrunning.so in my postgres lib directory and c language 
>> support is built into the databases.
>> 
>> 
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at postgresql.org]
>> Sent: 23 May 2012 23:29
>> To: Carman, Darren
>> Cc: pgpool-general at pgpool.net
>> Subject: Re: [pgpool-general: 490] Error on insert using pgpool and 2

>> postgresql servers
>> 
>>> I am getting an error when performing an insert using pgpool. I have
>>> 2
>> 
>>> servers with latest postgresql 9.1/postgis 1.5, 2nd server uses a 
>>> direct copy of the data directory from the first server to ensure 
>>> they
>> 
>>> are the same (i have tried dump/restore as well).
>>> 
>>>  
>>> 
>>> Version of pgpool is 3.1.3 and was installed using default configure

>>> (so no arguments). And is set in replication mode with load 
>>> balancing
> 
>>> on (config at the end of email).
>>> 
>>>  
>>> 
>>> All servers are running RedHat AS 6.1.
>>> 
>>>  
>>> 
>>> Selects work fine and when only using 1 server node in the config so

>>> do inserts. When using 2 server nodes I get an error inserting.
>> 
>> You seem to forgot to install pgpool_regclass().
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
> 

Infoterra Ltd. Is part of the Astrium GEO-Information Services Division and a wholly owned subsidiary of Astrium, Europe's leading space systems and services specialist.

Disclaimer. The information contained in this e-mail and its attachments are confidential and intended only for the use of the named addressee(s). If you are not the intended addressee, please do not read, copy, use or disclose this message or its attachments. If you have received this message in error, please notify the sender immediately and delete or destroy all copies of this message and attachments in all media. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Infoterra Ltd and shall not form part of any binding agreement.

Infoterra Limited a company registered in England under number 2359955 and having its registered office at Atlas House, 41 Wembley Road, Leicester, LE3 1UT. VAT number GB 476 0468 27. 

P Before printing, think about the environment