Re: [GENERAL] Server not Starting

On Wed, Apr 14, 2010 at 3:35 PM, raghavendra t wrote: > Hi, > > Log file > = > > LOG: database system was interrupted; last known up at 2010-04-12 10:53:12 > IST > LOG: database system was not properly shut down; automatic recovery in > progress > LOG: record with zero length at 0/1A0003C8

Re: [GENERAL] pl/java status

John R Pierce wrote: > is pl/java kind of dead? I don't see much activity since years ago. I've been a bit worried about that myself. With OpenJDK and a GPL java, it makes a lot of sense to make Java a first-class PL in PostgreSQL. There's a fair bit of activity from Java-using users, and there'

Re: [GENERAL] Server not Starting

Hi, Log file = LOG: database system was interrupted; last known up at 2010-04-12 10:53:12 IST LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/1A0003C8 LOG: redo is not required FATAL: could not access status of transaction

Re: [GENERAL] Server not Starting

Hi Ashesh Postgresql.conf file (port part of conf file) # - Connection Settings - listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defa

Re: [GENERAL] Server not Starting

What is the value set for the port in postgresql.conf? -- Thanks & Regards, Ashesh Vashi EnterpriseDB INDIA: Enterprise Postgres Company On Wed, Apr 14, 2010 at 10:57 AM, raghavendra t wrote: > Hi All, > > Please find the below scenario, and suggest me... > > Tryin

[GENERAL] Server not Starting

Hi All, Please find the below scenario, and suggest me... Trying to start the server, but not starting. -- [postg...@dbarhel564 mypg]$ pg_ctl -D /usr/local/pgsql/mypg/ start server starting [postg...@dbarhel564 mypg]$ psql -p 5432 -d postgre

Re: [GENERAL] pl/java status

Greg Smith wrote: John R Pierce wrote: is pl/java kind of dead? I don't see much activity since years ago. this page seems quite out of date, talking about 1.1.0b1 released, but the foundry has 1.4.0 from 2008 that supports 8.3. only The last set of commits there was about 9 months ago, so

Re: [GENERAL] pl/java status

John R Pierce wrote: is pl/java kind of dead? I don't see much activity since years ago. this page seems quite out of date, talking about 1.1.0b1 released, but the foundry has 1.4.0 from 2008 that supports 8.3. only The last set of commits there was about 9 months ago, so it's not quite as

Re: [GENERAL] psql's \d display of unique index vs. constraint

Josh Kupershmidt wrote: FWIW, I'm not religious about psql's formatting; I'd be happy with this information being displayed only for \d+, in whatever form makes folks happy. I unfortunately don't have much time to try a patch myself at the moment :( It's a straightforward patch to write wit

[GENERAL] pl/java status

is pl/java kind of dead? I don't see much activity since years ago. I note the pgfoundry page, http://pgfoundry.org/projects/pljava/?readme the link to the project home page http://wiki.tada.se/display/pljava/Home is broken this page seems quite out of date, talking about 1.1.0b1 released

Re: [GENERAL] psql's \d display of unique index vs. constraint

On Tue, Apr 13, 2010 at 4:53 PM, Scott Marlowe wrote: > Yeah, probably make it show up for \d+ or something. FWIW, I'm not religious about psql's formatting; I'd be happy with this information being displayed only for \d+, in whatever form makes folks happy. I unfortunately don't have much time

Re: [GENERAL] Fuzzy string matching of product names

George Silva wrote: > The above is true. For geocoding the same idea is used: the metaphone > function is used against street names, and searched to a simples column, > filled with the results of the metaphone function. It works quite well. I would think an expression index would be better than a

Re: [GENERAL] Unknown winsock error 10061

On 13/04/10 21:15, Jaiswal Dhaval Sudhirkumar wrote: Is there any other server running over the same system ? Is there any firewall or anti-virus software installed? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

Re: [GENERAL] psql's \d display of unique index vs. constraint

On Tue, Apr 13, 2010 at 1:36 PM, Tom Lane wrote: > Peter Eisentraut writes: >> On fre, 2010-04-09 at 18:01 -0400, Josh Kupershmidt wrote: >>> I often come across tables with either a unique index or a unique >>> constraint on them, and psql isn't helpful at showing the difference >>> between the

Re: [GENERAL] Showing debug messages in my C function

Jorge Arevalo writes: > I'm sorry, because this may be a simple question: I'm programming a C > function that returns a set (PostgreSQL 8.4). The function crash, and > I'm trying to > debug it. I've read: > http://www.postgresql.org/docs/8.4/interactive/xfunc-c.html > http://www.postgresql.org/do

Re: [GENERAL] psql's \d display of unique index vs. constraint

Peter Eisentraut writes: > On fre, 2010-04-09 at 18:01 -0400, Josh Kupershmidt wrote: >> I often come across tables with either a unique index or a unique >> constraint on them, and psql isn't helpful at showing the difference >> between the two. Normally, I don't care which is which, except for >

Re: [GENERAL] When is an explicit cast necessary?

Martijn van Oosterhout writes: > The question is: does the column really need to be smallint. Yeah. Usually, declaring a function's argument as int rather than smallint is the easiest fix. We have looked into this in the past, and concluded that the negative aspects of allowing integer constant

Re: [GENERAL] Need some help with a query (uniq -c)

Thank you all who has replied. I will study your suggestions and see what will work best in my case. 2010/4/13 Kenichiro Tanaka : > Hello. > > I try with "With Query". > http://www.postgresql.org/docs/8.4/static/queries-with.html > > #We can use "With Queries" >  v8.4 > #That'll only work if the t

Re: [GENERAL] C-language functions: SRF question

Jorge Arevalo writes: > BTW, this code is for WKT Raster. A PostGIS extension. We can use the > memory context I said (fcinfo->flinfo->fn_mcxt) to allocate memory > when we need to call one of our functions from a standard "version 1" > function, but is this the right context? I mean, the context

Re: [GENERAL] Dynamic Catalog Views

raghavendra t wrote: Can we get the dynamic information like in oracle(v$) views. Same as in postgresql buffers(shared,temp,wal) & cache(maintainence_work_me, effective_mem). The information available in this area includes: 1) Look at buffer cache hit rates using pg_stat_user_tables, pg_st

Re: [GENERAL] Dynamic Catalog Views

Hi All, Thank you for your support. Yes, i tried with pg_buffercache, it is giving the information on the hit ratio of shared_buffers. Can we get the dynamic information like in oracle(v$) views. Same as in postgresql buffers(shared,temp,wal) & cache(maintainence_work_me, effective_mem). Thanks

Re: [GENERAL] General question about speed of functions

On Tue, 13 Apr 2010 10:26:04 -0400, Brent Friedman wrote: > I am starting a project next week that looks like it will involve some > massive sql rewrites to gain needed performance, and I am looking at > implementing as many functions as possible. I haven't worried that much > about specific i

Re: [GENERAL] optimalisation with EXCEPT clause

if you have a primary key on the table, and you should, you might get better performance using LEFT JOIN. EXCEPT will compare all columns, which might not be that fast, especially if those are text. (hence why I always tell others to use int as key in a table, but that's a different story). -- G

[GENERAL] optimalisation with EXCEPT clause

Hello, everyday I collect a couple of thousands rows of unique data from our systems and I INSERT them into the table. Since I need no duplicate data, I use EXCEPT clause when INSERTing, like this: === INSERT INTO data SELECT * FROM new_collected_data() EXCEPT SELECT * FROM data; === It works e

Re: [GENERAL] [ADMIN] Query is stuck

> INFO: "repcopy": scanned 3000 of 4652 pages, containing 128964 live rows > and 0 dead rows; 3000 rows in sample, 199980 estimated total rows > VACUUM > controlsmartdb=# select distinct report_status from repcopy ; According to the vacuum output, there are about 20 rows in the "repcopy" tab

[GENERAL] General question about speed of functions

I am starting a project next week that looks like it will involve some massive sql rewrites to gain needed performance, and I am looking at implementing as many functions as possible. I haven't worried that much about specific implementations in the past, but this project can use any performan

Re: [GENERAL] [ADMIN] Query is stuck

I am using 8.1, so waiting coln is not there in pg_stat_activity. I frequently see these in the server logs: LOG: autovacuum: processing database "controlsmartdb" Though I can give you the result of vacuum run (but it is not helping): controlsmartdb=# vacuum full verbose analyze repcopy; INFO:

Re: [GENERAL] Query is stuck

select procpid, current_query,query_start - now(), backend_start from pg_stat_activity where current_query not like '%IDLE%' and waiting = 't'; -- Thanks Dhaval From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Satish Burnwal (sburnwal) S

Re: [GENERAL] Query is stuck

On Tuesday 13 April 2010 6:03:43 am Satish Burnwal (sburnwal) wrote: > controlsmartdb=# select * from pg_stat_activity where waiting='t'; > > ERROR: column "waiting" does not exist > > From here: http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS My guess

Re: [GENERAL] Unknown winsock error 10061

Is there any other server running over the same system ? Have you killed any processes from OS level ? What is the message showing when you are trying to start the pg server ? -- Thanks Dhaval -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@pos

Re: [GENERAL] Query is stuck

I am on postgres 8.1. bash-3.2$ postgres --version postgres (PostgreSQL) 8.1.11 From: Plugge, Joe R. [mailto:jrplu...@west.com] Sent: Tuesday, April 13, 2010 6:37 PM To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject: RE: Query is stuck

Re: [GENERAL] [ADMIN] Query is stuck

In response to Szymon Guz : > 2010/4/13 Satish Burnwal (sburnwal) > > > I have a query which is not giving me the result even after 30 minutes. I > > want to know how to detect what is going and what’s wrong ? > > > > > > > > EXPLAIN query - gives me the following: > > > > controlsmartdb=# expl

Re: [GENERAL] Query is stuck

On Tue, Apr 13, 2010 at 06:28:18PM +0530, Satish Burnwal (sburnwal) wrote: > > In such a case what can I do ? don't crosspost? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Email address column verification foraddress list

> So for most applications written against this database, they're probably > assuming only one email address in this column. > > And then you confuse the issue by putting two or more comma-separated > addresses, which are not universally usable when a single address > is provided. Surely not Randa

Re: [GENERAL] Email address column verification foraddresslist

So for most applications written against this database, they're probably assuming only one email address in this column. This column content is transparent to application. It is passed to e-mail sender (blat.dll) without any processing. And then you confuse the issue by putting two or more com

Re: [GENERAL] Query is stuck

What version of postgres are you on? From: Satish Burnwal (sburnwal) [mailto:sburn...@cisco.com] Sent: Tuesday, April 13, 2010 8:04 AM To: Plugge, Joe R.; pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject: RE: Query is stuck controlsmartdb=# select * from pg_stat_activity where

Re: [GENERAL] Email address column verification foraddress list

> "Andrus" == Andrus writes: Andrus> This is existing database deployed to many sites and used by many programs. Andrus> Re-factoring db and software to add this minor feature seems to be not Andrus> reasonable. For 99% of cases field contains only single address. So for most applications

[GENERAL] Unknown winsock error 10061

Hi, Postgres server crashed on windows vista. From the log: 2010-04-07 07:00:35.694 LOG: could not receive data from client: Unknown winsock error 10061 2010-04-07 07:00:35.694 LOG: could not receive data from client: Unknown winsock error 10061 2010-04-07 07:00:35.694 LOG: unexpected EOF o

Re: [GENERAL] Query is stuck

controlsmartdb=# select * from pg_stat_activity where waiting='t'; ERROR: column "waiting" does not exist From: Plugge, Joe R. [mailto:jrplu...@west.com] Sent: Tuesday, April 13, 2010 6:32 PM To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Cc: pgsql-ad...@postgresql.org Subject:

Re: [GENERAL] [ADMIN] Query is stuck

2010/4/13 Satish Burnwal (sburnwal) > I have a query which is not giving me the result even after 30 minutes. I > want to know how to detect what is going and what’s wrong ? > > > > EXPLAIN query - gives me the following: > > controlsmartdb=# explain select report_id, dm_ip, dm_mac, dm_user, dm_

Re: [GENERAL] Query is stuck

What do you get when you run this? select * from pg_stat_activity where waiting='t'; From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Satish Burnwal (sburnwal) Sent: Tuesday, April 13, 2010 7:58 AM To: pgsql-general@postgresql.org Cc: pgsql-ad...@po

[GENERAL] Query is stuck

I have a query which is not giving me the result even after 30 minutes. I want to know how to detect what is going and what's wrong ? EXPLAIN query - gives me the following: controlsmartdb=# explain select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, sys_name, sys_user, sys_user_do

Re: [GENERAL] Email address column verification foraddress list

Peter, thank you. Why don't you just separate them into individual fields (perhaps in a separate table to have arbitrary many addresses)? What you're doing violates 1NF - fields should be atomic (i.e. in their simplest form, so you never have to parse values from them). This is existing datab

Re: [GENERAL] Email address column verification for address list

2010/4/13 Andrus : > Email address field email has type character(200) > It can contain multiple e-mail addresses separated by comma. > Applying validation code below removes and does not allow comma separated > address list. > > How to modify this so that comma separated address list is allowed ?

Re: [GENERAL] Email address column verification for address list

however you are going to validate it, create yourself a domain for it (custom type). That way, if it changes, you have to only update it in one place, instead of doing it on column by column basis.

Re: [GENERAL] Where to configure pg_xlog file-size?

Hello again, > Well, if you were using 8.4 you could fool with configure's > --with-wal-segsize option.  Since you're not, look into > src/include/pg_config_manual.h.  In either case, expect to do a > full recompile and initdb after changing it. Good to know - I plan to upgrade to 8.4/9.0 anyway.

[GENERAL] Email address column verification for address list

Email address field email has type character(200) It can contain multiple e-mail addresses separated by comma. Applying validation code below removes and does not allow comma separated address list. How to modify this so that comma separated address list is allowed ? How to simplify/improve this

Re: [GENERAL] Need some help with a query (uniq -c)

Hello. I try with "With Query". http://www.postgresql.org/docs/8.4/static/queries-with.html #We can use "With Queries" > v8.4 #That'll only work if the time values are contiguous, but there's probably a #similar trick for non-contiguous ,too. --create data drop table foo; create table foo( tim

Re: [GENERAL] Dynamic Catalog Views

> raghavendra t wrote: >> Hi All, >> >> Could please guide me in knowing the Dynamic Catalog views which will >> tell about the Buffers and Cache information using System Catalogs. >> > > you mean, stuff like > http://www.postgresql.org/docs/current/static/catalog-pg-statistic.html ? > > afaik, dat

Re: [GENERAL] prevent connection using pgpass.conf

Alban Hertroys wrote: afaik, the .pgpass file is something the user creates with his text editor. if it was encrypted or hashed, there would need to be a client side utility to create it. Yes of course, something like ssh-keygen(1) for example. well, more like smbpasswd, I'd thi

Re: [GENERAL] prevent connection using pgpass.conf

On 13 Apr 2010, at 2:36, John R Pierce wrote: > Alban Hertroys wrote: >> Storing those passwords encrypted on the client side seems the proper way to >> deal with this issue. IMHO, time working on that is better spent than time >> trying to prevent .pgpass files from working. > > afaik, the .pg