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

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] 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] 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

[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] 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.

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] 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 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

[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] 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

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

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:

[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] 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

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 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] 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] 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] [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

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] 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

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] 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] [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:

[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

> 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] 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] 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

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] 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] 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] 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] 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] 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] 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] 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

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] 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] 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] 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

[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

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

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] 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

[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] 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

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

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] 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

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