BUG #8056: postgres forgets hstore over time

[know...@yahoo.com]

The following bug has been logged on the website:

Bug reference: 8056
Logged by: Eugene
Email address: know...@yahoo.com
PostgreSQL version: 9.2.1
Operating system: centos 6 64bit
Description:

I have hstore installed on my server and it works fine. But sometimes
postgres would give me errors such as:

type "hstore" does not exist

or telling me || operator is invalid or not recognizing the hstore
functions.

I am using DBD-Pg in Perl when I get these errors in my error log when
running my website. I am unable to replicate these issues when running a
perl script or anything.


I think this issue is cache related or something because if I have:

hstore(?) giving me an error and replace it with public.hstore(?) it starts
working for a while then stops working and forgets hstore.

To note, this is limited to that single instance. If I am getting the hstore
errors and I test manually at the same time via both Perl or pgadmin, I
don't get any errors. This makes me think that this is limited to that
instance since the website is dealing with persistent connections due to
FCGI.

So I am guessing that if the connection runs for too long, it forgets hstore
datatype, hstore operators and hstore functions over time.



--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Dickson S. Guedes]

2013/4/9 <know...@yahoo.com>:

> The following bug has been logged on the website:
>
> Bug reference: 8056
> Logged by: Eugene
> Email address: know...@yahoo.com
> PostgreSQL version: 9.2.1

Please update your Postgres to 9.2.4.

> I have hstore installed on my server and it works fine. But sometimes
> postgres would give me errors such as:
>
> type "hstore" does not exist

Did you check search_path? Compare search_path between sessions that
are getting error and see if hstore type/functions are reached through
it.

[]s
--
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

[E E]

You mean by running current_schemas(true) on that session?

So far I have remade the hstore functions into this and it seems to be working for now: (But this happens randomly after time so it might take a day or 2 to be sure this addresses the issue)

CREATE OR REPLACE FUNCTION accounts.myhstore_merge(myh hstore, mytext text[])

  RETURNS hstore AS

$BODY$BEGIN

SET search_path TO public;

RETURN myh || hstore(mytext);

END;$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

---

From: Dickson S. Guedes <lis...@guedesoft.net>
To: know...@yahoo.com
Cc: pgsql...@postgresql.org
Sent: Wednesday, April 10, 2013 7:19 AM
Subject: Re: [BUGS] BUG #8056: postgres forgets hstore over time

[John R Pierce]

On 4/10/2013 11:01 AM, E E wrote:

CREATE OR REPLACE FUNCTION accounts.myhstore_merge(myh hstore, mytext text[])

  RETURNS hstore AS

$BODY$BEGIN

SET search_path TO public;

RETURN myh || hstore(mytext);

END;$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

public.hstore(...)    would have been much simpler than changing the search_path, which is persistent to the connection and likely will backfire if your apps are changing it to something else.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast