[GENERAL] Application crashing due to idle connection
Hi all, Our application is crashing due to lot of idle connection from last 1 month it is very frequent. When i checked pg_stat_activity I found below query :SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid: is on idle state number of time. After killing connection this query is also vanishing from pg_stat_activity. Can any one please give me some clue what cloud be the possible reason, and how to get rid of this problem. Thanks in advance. Regards, Itishree
[GENERAL] Multiple Schema in One DB
Hi all, I am in a requirement to have multiple schema in one Database. Schema structure will be same with respect to DB all db objetcs like tables, procedure. However each schema will be accessed by one one application. Problem is as all DB objects are same, when application is calling to a schema we have set search_path='schema name' in our procedure level. Now we want to get rid of that. I know we have do it based on ROLE Level, question is having more than one user defined schema is it achievable, i have done a small exercise, seems to be it is taking the first schema defined in the set search_path. As i am in urgent need of it, please help me. Regards, Itishree
Re: [GENERAL] Need Help to implement Proximity search feature
Thanks for your reply, i am totally new to Postgis. we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. Regards, Itishree On Wed, Jan 15, 2014 at 11:28 PM, Oleg Bartunov wrote: > check knn search, > http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf > > On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla > wrote: > > Deal all, > > > > In my project, there is a requirement to implement proximity search > feature. > > We are running a mobile app, for which proximity search is require. Can > any > > one guide me how i can achieve this using postgis, or is there any other > way > > i can achieve this. > > > > We are using postgresql 9.2. > > > > Thanks in advance . > > > > Regards, > > Itishree >
[GENERAL] Need Help to implement Proximity search feature
Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree
[GENERAL] Multi Master Replication
Hi all, I need suggestion about setting up multi master replication between two postgresql server place two different geographical area. As i know using some third party tool like Bucardo,RubyRep it can be achievable, not sue which is the good one to use. If any one can provide me some online documentation links, it will help me as well. Thanks in advance. Regards, Itishree
Re: [GENERAL] Full text search
Hi Raghavendra,
Thanks for your response, however i want same kind of result using full
text search. is it possible?
Regards,
Itishree
On Fri, Jul 12, 2013 at 12:14 PM, Raghavendra <
raghavendra@enterprisedb.com> wrote:
>
> On Fri, Jul 12, 2013 at 11:48 AM, itishree sukla > wrote:
>
>> Hello everyone,
>>
>> I am using full text search, however it is not helping me to get the
>> partial value.
>>
>> For example in my document let Pizza Hut is there, if i am searching for
>> Pizza Hut is it giving me the values for only Pizza or a spell mistake like
>> pizz is not returning any thing. any work around for this, please suggest.
>>
>> Regards,
>> Itishree
>>
>
> Hope you are looking like this.
>
> create table foo(v text);
> insert into foo values('Near to my house there\'s no any Pizza Hut
> restuarant');
> insert into foo values('I wont like pizza, but friends are crazy of it');
>
>
> postgres=# select * from foo where v ~* 'pizz';
> v
> --
> Near to my house there's no any Pizza Hut restuarant
> I wont like pizza, but friends are crazy of it
> (2 rows)
>
> postgres=# select * from foo where v ~* 'pizza hut';
> v
> --
> Near to my house there's no any Pizza Hut restuarant
> (1 row)
>
> postgres=# select * from foo where v ~* 'pizza';
> v
> --
> Near to my house there's no any Pizza Hut restuarant
> I wont like pizza, but friends are crazy of it
> (2 rows)
>
> or
>
> with ILIKE
>
> select * from foo where v ilike '%hut%';
>
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
[GENERAL] Full text search
Hello everyone, I am using full text search, however it is not helping me to get the partial value. For example in my document let Pizza Hut is there, if i am searching for Pizza Hut is it giving me the values for only Pizza or a spell mistake like pizz is not returning any thing. any work around for this, please suggest. Regards, Itishree
Re: [GENERAL] Support for Foreign keys with arrays
Thanks, not sure how to download and apply this patch, not getting any down load link. ? On Wed, Jul 10, 2013 at 9:16 AM, Michael Paquier wrote: > On Tue, Jul 9, 2013 at 6:26 PM, itishree sukla > wrote: > > Hello Every one, > > > > I have a requirement for support for foreign keys with arrays, which is > not > > there in postgresql 9.2, however it is in development for 9.3, i can see > > there is some thread saying patch is available, can any one please help > me > > to get the patch, or any other work around by which we can achieve this. > The patch is available in a previous commit fest: > https://commitfest.postgresql.org/action/patch_view?id=900 > The discussion finished about concerns with performance, and did not go > further. > -- > Michael >
[GENERAL] Support for Foreign keys with arrays
Hello Every one, I have a requirement for support for foreign keys with arrays, which is not there in postgresql 9.2, however it is in development for 9.3, i can see there is some thread saying patch is available, can any one please help me to get the patch, or any other work around by which we can achieve this. Regards, Itishree
Re: [GENERAL] Triggers
I didn't get you, you mean trigger is possible on schema level? if yes can you give example. Thank you On Fri, Jul 5, 2013 at 3:56 PM, Jov wrote: > the later ，in same db > > jov > 在 2013-7-5 下午4:32，"itishree sukla" 写道： > > Hello Every one, >> >> Is Postgresql providing triggers on DB level, schema level ( in same DB)? >> >> >> Regards, >> Itishree >> >
[GENERAL] Triggers
Hello Every one, Is Postgresql providing triggers on DB level, schema level ( in same DB)? Regards, Itishree
Re: [GENERAL] File System backup
yes, when i tried to start, postgresql service using init.d its gave me the error removed stale pid, postgresql failed to start. Regards, Itishree On Sat, Jun 22, 2013 at 8:05 PM, Kevin Grittner wrote: > Alban Hertroys wrote: > > itishree sukla wrote: > > >> Can any one give me more suggestion, about this problem. Every > >> time my os got restart, postmaster.pid is missing. > > The pid file should only be present when postgres is running. A > clean OS shutdown should stop postgres, which should result in the > pid file being deleted. > > > Did you perhaps use reboot instead of shutdown -r? The former > > doesn't do a clean shutdown. That's how it is on *BSD at least, I > > don't know about Linux but I assume it behaves the same. > > No, `reboot` actually calls `shutdown -r now` in the distros I've > used, including Ubuntu; unless you run it with the --force option. > > > Check the logs to see if there are any errors when postgres tries > > to start up. It could be something as simple as a library version > > mismatch, or it could be data corruption in the database files. > > Right, checking the log files is the thing to do. Adding or > deleting a pid file is just about never the right thing to do. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: [GENERAL] File System backup
No, as its only one postgresql is running with default installation, server got restatared with out shuting down postgresql service properly. Then i tied to start server using ./init.d command, which didn't help me either. Then i created pstmaster.pid, that also doesn't help me much. Regards, Itishree On Fri, Jun 21, 2013 at 6:10 PM, Amit Langote wrote: > On Fri, Jun 21, 2013 at 7:53 PM, itishree sukla > wrote: > > Hi all, > > > > Can any one give me more suggestion, about this problem. Every time my os > > got restart, postmaster.pid is missing. After that createing > postmaster.pid > > is also not helping to start the server. I am using postgresql 9.2 in > > ubuntu, with default installation. Is there any possibility i can take > the > > Data directory backup, and install postgresql server, then restore Data > > directory. Will it work to get back my databases. > > Can you tell what command you use to start the server and when you > issue it? Is that after OS restarts?) Did you, by any chance, use > "restart" whereas you should have used "start" assuming you are using > pg_ctl for the same? > > Issuing "restart" requires that you should already have server running > (and that means postmaster.pid should be there in data directory). > > -- > Amit Langote >
[GENERAL] File System backup
Hi all, Can any one give me more suggestion, about this problem. Every time my os got restart, postmaster.pid is missing. After that createing postmaster.pid is also not helping to start the server. I am using postgresql 9.2 in ubuntu, with default installation. Is there any possibility i can take the Data directory backup, and install postgresql server, then restore Data directory. Will it work to get back my databases. Regards, Itishree
[GENERAL] coalesce function
Hi All, I am using coalesce(firstname,lastname), to get the result if first name is 'NULL' it will give me lastname or either way. I am having data like instead of NULL, blank null ( i mean something like '' ) for which coalesce is not working, is there any workaround or other function available in postgresql, please do let me know. Regards, Itishree
[GENERAL] Creating Extension pg_trgm
Hi all, I need to create pg_trgm extension, however in my current DB, we have some function based on pg_trgm. when i am trying to create extension, it is asking to drop all dependant function, is there anyway without dropping any dependency i can create this extension. Thanks in advance... Regards, Itishree
[GENERAL] Views
Hi all, Is there any way, i can know what all views are there on a table by a sql query? Regards, Itishree
[GENERAL] SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
Dear All,
Can any one please help me to fix this issue, i am getting this error from
our application, currently Database is running on 9.2.
2013-04-17 11:37:25:151 - {ERROR} database.ConnectionManager Thread
[http-8080-1]; --- getConnection() Exception:
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool
error Timeout waiting for idle object
at
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
at
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at
com.tenkinfo.b2g.database.ConnectionManager.getConnection(ConnectionManager.java:39)
at
com.tenkinfo.b2g.usermanagement.dao.UserManagementDAOImpl.getSessionData(UserManagementDAOImpl.java:228)
at
com.tenkinfo.mapnsav.common.action.BaseAction.execute(BaseAction.java:156)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at
org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
at
org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
Do i have to set tcp _keepalive * paramter to less sec, or need to kill the
idle connection ?
Regards,
Itishree
[GENERAL] Re: [GENERAL] Re: [GENERAL] FATAL: no pg_hba.conf entry for host “::1***"
Hi John, Thanks for your reply, however the the address is starting with host "58.137.154.189". It was working fine before, however not working in new system. On Wed, Feb 23, 2011 at 12:39 PM, John R Pierce wrote: > On 02/22/11 10:38 PM, itishree sukla wrote: > >> Hi All, >> I am using System DSN, that connects to postgreSQL, to fetch data from >> the database, and put into xls sheet .Its working fine with most of the >> machines and connects fine but on 1 machine i am getting this FATAL: no >> pg_hba.conf entry for host “::1**”, user “postgres”, database >> “myDatabase", SSL off error. Any idea why it is so, please suggest. >> Thanks in advance ... >> >> > ::1 is the IPv6 localhost. sounds like this system has ipv6 configured, so > you probably want a line in pg_hba.conf like... > > > hosts all all ::1 md5 > > (replace the md5 with whatever you normally use on the localhost linet hat > looks like > > hosts all all 127.0.0.0/8 md5 > > the other common value is 'trust'. md5 requires a password, trust > doesn't. > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] FATAL: no pg_hba.conf entry for host “::1***"
Hi All, I am using System DSN, that connects to postgreSQL, to fetch data from the database, and put into xls sheet .Its working fine with most of the machines and connects fine but on 1 machine i am getting this FATAL: no pg_hba.conf entry for host “::1**”, user “postgres”, database “myDatabase", SSL off error. Any idea why it is so, please suggest. Thanks in advance ... Regards, Itishree
[GENERAL] Faicng problem while creating system DSN
Hi All, I am creating a system DSN using PostgreSQL Unicode driver, after giving all the server credentials, when i am testing for the connection it is giving, error message like "odbcad32.exe has encountered a problem and needs to close. We are sorry for the inconvenience." Can anybody please help me out. Thanks in Advance Regards, Itishree
[GENERAL] Issue with POSTGIS
Hello ,
My question is related to some functions in PostGIS.
Problem:
We are trying to find x points from a table that are within 20
nautical miles from a given point. Would like to use the ST_Dwithin
function as it would use the GIST index. I could use the
ST_Distance_Spheroid function but that would scan the entire table.
(1) So here's the query I would like to use:
select * from table_A where st_dwithin(shape, GeomFromText('POINT(-90
45)',4269), 20.0/60);
The shape field in table_A is also stored in the coordinate system for
4269. Since a nautical mile translates into 1 arcminute of latitude,
the distance is divided by 60.
(2) I am expecting to get 11 records and confirmed it by using the
st_distance_spheroid function as show below:
select * from table_A where
ST_Distance_Spheroid(shape ,ST_GeomFromText('POINT(-90 45)',4269),
'SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]]')
<=1852*20;
(3) One another test I did was to use the following and got 11 records
as expected. Here what I did was to convert to planar coordinates.
Select * from table_A where st_dwithin( transform(shape,
2163),transform(GeomFromText('POINT(-90 45)',4269),2163), 20*1852);
The query shown in (1) gives me 3 records. What am I missing? Please
help.
Thanks & Regards,
Itishree
Re: [GENERAL] Hello, i want to subscribe...
On Fri, Apr 3, 2009 at 2:35 AM, Ricardo Fuentes wrote: > Hello, i want to subscribe to this lists > Hi Ricardo, Here is the link to subscribe. http://www.postgresql.org/community/lists/ Regards, Itishree
