Thread: BUG #8056: postgres forgets hstore over time
The following bug has been logged on the website: Bug reference: 8056 Logged by: Eugene Email address: knowzerox@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.
2013/4/9 <knowzerox@yahoo.com>: > The following bug has been logged on the website: > > Bug reference: 8056 > Logged by: Eugene > Email address: knowzerox@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: guedes@guedesoft.net - skype: guediz http://github.com/guedes - http://guedesoft.net http://www.postgresql.org.br
You mean by running current_schemas(true) on that session?=0A=0A=0ASo 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 b= e sure this addresses the issue)=0A=0ACREATE OR REPLACE FUNCTION accounts.m= yhstore_merge(myh hstore, mytext text[])=0A=A0 RETURNS hstore AS=0A$BODY$BE= GIN=0ASET search_path TO public;=0ARETURN myh || hstore(mytext);=0A=0AEND;$= BODY$=0A=A0 LANGUAGE plpgsql VOLATILE=0A=A0 COST 100;=0A=0A=0A_____________= ___________________=0A From: Dickson S. Guedes <listas@guedesoft.net>=0ATo:= knowzerox@yahoo.com =0ACc: pgsql-bugs@postgresql.org =0ASent: Wednesday, A= pril 10, 2013 7:19 AM=0ASubject: Re: [BUGS] BUG #8056: postgres forgets hst= ore over time=0A =0A2013/4/9=A0 <knowzerox@yahoo.com>:=0A> The following bu= g has been logged on the website:=0A>=0A> Bug reference:=A0 =A0 =A0 8056=0A= > Logged by:=A0 =A0 =A0 =A0 =A0 Eugene=0A> Email address:=A0 =A0 =A0 knowze= rox@yahoo.com=0A> PostgreSQL version: 9.2.1=0A=0APlease update your Postgre= s to 9.2.4.=0A=0A> I have hstore installed on my server and it works fine. = But sometimes=0A> postgres would give me errors such as:=0A>=0A> type "hsto= re" does not exist=0A=0ADid you check search_path? Compare search_path betw= een sessions that=0Aare getting error and see if hstore type/functions are = reached through=0Ait.=0A=0A[]s=0A-- =0ADickson S. Guedes=0Amail/xmpp: guede= s@guedesoft.net - skype: guediz=0Ahttp://github.com/guedes - http://guedeso= ft.net=0Ahttp://www.postgresql.org.br
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
Ok, after making my own functions and putting:=0A=0ASET search_path TO publ= ic;=0A=0A=0AOn top, the errors seemed to have stopped. So it seems like thi= s is an issue with the search_path being forgotten.=0A=0A=0A_______________= _________________=0A From: Dickson S. Guedes <listas@guedesoft.net>=0ATo: k= nowzerox@yahoo.com =0ACc: pgsql-bugs@postgresql.org =0ASent: Wednesday, Apr= il 10, 2013 7:19 AM=0ASubject: Re: [BUGS] BUG #8056: postgres forgets hstor= e over time=0A =0A=0A2013/4/9=A0 <knowzerox@yahoo.com>:=0A> The following b= ug has been logged on the website:=0A>=0A> Bug reference:=A0 =A0 =A0 8056= =0A> Logged by:=A0 =A0 =A0 =A0 =A0 Eugene=0A> Email address:=A0 =A0 =A0 kno= wzerox@yahoo.com=0A> PostgreSQL version: 9.2.1=0A=0APlease update your Post= gres to 9.2.4.=0A=0A> I have hstore installed on my server and it works fin= e. But sometimes=0A> postgres would give me errors such as:=0A>=0A> type "h= store" does not exist=0A=0ADid you check search_path? Compare search_path b= etween sessions that=0Aare getting error and see if hstore type/functions a= re reached through=0Ait.=0A=0A[]s=0A-- =0ADickson S. Guedes=0Amail/xmpp: gu= edes@guedesoft.net - skype: guediz=0Ahttp://github.com/guedes - http://gued= esoft.net=0Ahttp://www.postgresql.org.br