Re: [GENERAL] prevent connection using pgpass.conf

[Alban Hertroys]

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

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bc4402710411493216889!



-- 
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] prevent connection using pgpass.conf

[John R Pierce]

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 think. 


--
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] Dynamic Catalog Views

[tv]

> 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, data about whats in the cache and buffers is not accessible via
> any pg_catalog schema tables.

It's not accessible by default, but there is contrib package called
'pg_buffercache':

http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html

I guess that might provide the information about buffers.

Tomas




-- 
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] Need some help with a query (uniq -c)

[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 time values are contiguous, but there's probably a
#similar trick for non-contiguous ,too.

--create data
drop table foo;
create table foo( time int,message text);
insert into foo values(1,'a');
insert into foo values(2,'b');
insert into foo values(3,'b');
insert into foo values(4,'c');
insert into foo values(5,'a');
insert into foo values(6,'c');
insert into foo values(7,'c');
insert into foo values(8,'a');
insert into foo values(9,'a');
insert into foo values(10,'a');

--begin Answer
with recursive r as (
select foo.time,foo.message,1  as dummy from foo
union all
select foo.time,foo.message,r.dummy+1 from foo , r
where foo.time=r.time-1 and foo.message=r.message
)
,rr as (
select foo.time,foo.message,'OLID' as flag  from foo
union all
select foo.time,foo.message,'DUP' as flag from foo , rr
where foo.time-1=rr.time-2 and foo.message=rr.message
)
select time min,time+max(dummy)-1 max,message,max(dummy) counts
from r where time not in (select distinct (time+1) times from rr
where flag='DUP') group by time,message order by time;

--result
postgres(# where flag='DUP') group by time,message order by time;
 min | max | message | counts
-+-+-+
   1 |   1 | a   |  1
   2 |   3 | b   |  2
   4 |   4 | c   |  1
   5 |   5 | a   |  1
   6 |   7 | c   |  2
   8 |  10 | a   |  3
(6 rows)

--end

But I think some one can provide more simple SQL.

Thank you.

On Apr 12, 2010, at 11:31 AM, Scott Marlowe wrote:

   

On Mon, Apr 12, 2010 at 12:22 PM, A B  wrote:
 

Hello!

I have a table (think of it as a table of log messages)

time |  message
---
1  |   a
2  |   b
3  |   b
4  |  b
5  |  a

the three 'b' are the same message, so I would like to write a query
that would give me a result that is similar to what the unix command
"uniq -c" would give:

first |  message | last | count
--
1 | a  |   1   | 1
2 | b  |   4   | 3<--- here it squeezes
similar consecutive messages into a single row
5 | a  |   5   | 1

How do I write such a command?
   

Pretty straight ahead:

select min(t), message, max(t), count(*) from table group by message.
 

That was my first though too, but it combines everything not just adjacent 
messages.

Something like this, maybe

select t1.message, t1.time as first, t2.time as last, t2.time-t1.time+1 as count
 from foo as t1, foo as t2
 where t1.time<= t2.time and t1.message = t2.message
 and not exists
 (select * from foo as t3
  where (t3.time between t1.time and t2.time and t3.message<>  
t1.message)
  or (t3.time = t2.time + 1 and t3.message = t1.message)
  or (t3.time = t1.time - 1 and t3.message = t1.message));

  message | first | last | count
-+---+--+---
  a   | 1 |1 | 1
  b   | 2 |4 | 3
  a   | 5 |5 | 1

That'll only work if the time values are contiguous, but there's probably a
similar trick for non-contiguous.

Cheers,
   Steve


   



--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



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

[GENERAL] Email address column verification for address list

[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 ?
How to simplify/improve  this validation, probably some newer root domain
names are not allowed by this validation  ?

Andrus.

update klient set email=null where email='' OR
(trim(trailing from email) !~*
'^...@]*@(?:[...@]*\\.)?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa|eu)$');alter
 table klient add check (trim(trailing from email) 
~*'^...@]*@(?:[...@]*\\.)?[a-z0-9_-]+\\.(?:a[defgilmnoqrstuwz]|b[abdefghijmnorstvwyz]|c[acdfghiklmnoruvxyz]|d[ejkmoz]|e[ceghrst]|f[ijkmorx]|g[abdefhilmnpqrstuwy]|h[kmnrtu]|i[delnoqrst]|j[mop]|k[eghimnprwyz]|l[abcikrstuvy]|m[acdghklmnopqrstuvwxyz]|n[acefgilopruz]|om|p[aefghklmnrtwy]|qa|r[eouw]|s[abcdeghijklmnortvyz]|t[cdfghjkmnoprtvwz]|u[agkmsyz]|v[aceginu]|w[fs]|y[etu]|z[amw]|edu|com|net|org|gov|mil|info|biz|coop|museum|aero|name|pro|mobi|arpa|eu)$');

--
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] Where to configure pg_xlog file-size?

[Clemens Eisserer]

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.
I am currently using pre-built binaries but don't want to touch the OS,
so no way arround compiling PG in the long term ;)

> I don't believe any serious effort has been made to quantify that;
> the knob is there but you're on your own to figure out how hard to
> twist it.
Ok, understood :)

Thanks, Clemens

-- 
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 for address list

[Grzegorz Jaśkiewicz]

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

[Peter Geoghegan]

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 ?
> How to simplify/improve  this validation, probably some newer root domain
> names are not allowed by this validation  ?
>
> Andrus.

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).

Here's a reasonable email address domain. Note that it is AS text, not
AS character(200). It does not attempt to match the TLD to a list of
known TLDs, nor should it (that's very probably impractical, at least
with regex):

CREATE DOMAIN email_address
  AS text
   CONSTRAINT email_address_check CHECK ((VALUE ~
'^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\\-+)|([A-Za-z0-9]+\\.+)|([A-Za-z0-9]+\\++))*[A-Za-z0-9]+@((\\w+\\-+)|(\\w+\\.))*\\w{1,63}\\.[a-zA-Z]{2,6}$'::text));

If you absolutely must put an arbitrary number of e-mail addresses in
one field, I suggest you use the domain in an array. Unfortunately,
arrays of domains are not directly supported, last I checked.

Could someone weigh in on how to roll this domain into a custom
email_address_array domain?

Regards,
Peter Geoghegan

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

[Andrus]

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 database deployed to many sites and used by many programs.
Re-factoring db and software to add this minor feature seems to be not 
reasonable. For 99% of cases field contains only single address.



Here's a reasonable email address domain. Note that it is AS text, not
AS character(200). It does not attempt to match the TLD to a list of
known TLDs, nor should it (that's very probably impractical, at least
with regex):



CREATE DOMAIN email_address
 AS text
  CONSTRAINT email_address_check CHECK ((VALUE ~
'^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\\-+)|([A-Za-z0-9]+\\.+)|([A-Za-z0-9]+\\++))*[A-Za-z0-9]+@((\\w+\\-+)|(\\w+\\.))*\\w{1,63}\\.[a-zA-Z]{2,6}$'::text));
If you absolutely must put an arbitrary number of e-mail addresses in

one field, I suggest you use the domain in an array. Unfortunately,
arrays of domains are not directly supported, last I checked.

I don't know how to use arrrays to solve this.

How to create constraint for char(200) column for comma separated email list 
?


Andrus.


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

[GENERAL] Query is stuck

[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_os, report_time, sys_name,  sys_user, sys_user_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =
(select max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1';

 QUERY PLAN




 Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)

   Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
(report_time = (subplan)))

   SubPlan

 ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)

   ->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1
width=8)

 Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
(dm_user)::text) AND ((ss_key)::text <> ''::text))

(6 rows)

 

But EXPLAIN ANALYSE query hangs (is not giving me any output even after
30 minutes).

 

Pg_stat_activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time = (select
max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30

 

 

In such a case what can I do ? 

 

Re: [GENERAL] Query is stuck

[Plugge, Joe R.]

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...@postgresql.org
Subject: [ADMIN] 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_domain, ss_key, login_time, role_id, 
new_vlan_id from repcopy as a where report_time = (select max(report_time) from 
repcopy as b where a.dm_ip = b.dm_ip and a.dm_user=b.dm_user and b.ss_key != 
'') and report_status = 0 and dm_user = 'u1';
 QUERY PLAN

 Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)
   Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND 
(report_time = (subplan)))
   SubPlan
 ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)
   ->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1 width=8)
 Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = 
(dm_user)::text) AND ((ss_key)::text <> ''::text))
(6 rows)

But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 
minutes).

Pg_stat_activity shows this - SELECT procpid, usename, current_query, 
query_start from pg_stat_activity:
2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, 
sys_name,  sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id 
from repcopy as a where report_time = (select max(report_time) from repcopy as 
b where a.dm_ip = b.dm_ip and a.dm_user=b.dm_user and b.ss_key != '') and 
report_status = 0 and dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30


In such a case what can I do ?

Re: [GENERAL] [ADMIN] Query is stuck

[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=# explain select report_id, dm_ip, dm_mac, dm_user, dm_os,
> report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
> role_id, new_vlan_id from repcopy as a where report_time = (select
> max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
> a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user =
> 'u1';
>
>  QUERY PLAN
>
>
> 
>
>  Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)
>
>Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
> (report_time = (subplan)))
>
>SubPlan
>
>  ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)
>
>->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1 width=8)
>
>  Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
> (dm_user)::text) AND ((ss_key)::text <> ''::text))
>
> (6 rows)
>
>
>
> But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30
> minutes).
>
>
>
> Pg_stat_activity shows this - SELECT procpid, usename, current_query,
> query_start from pg_stat_activity:
>
> 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
> report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
> role_id, new_vlan_id from repcopy as a where report_time = (select
> max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
> a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user =
> 'u1'; | 2010-04-13 18:20:02.828623+05:30
>
>
>
>
>
> In such a case what can I do ?
>
>
> First things that came to my mind:

1. Check if the query waits on some lock: add the column `waiting` to the
above query from pg_stat_activity.
2. Run vacuum analyze on the table repcopy


regards
Szymon Guz

Re: [GENERAL] Query is stuck

[Satish Burnwal (sburnwal)]

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: 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...@postgresql.org
Subject: [ADMIN] 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_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =
(select max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1';

 QUERY PLAN




 Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)

   Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
(report_time = (subplan)))

   SubPlan

 ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)

   ->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1
width=8)

 Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
(dm_user)::text) AND ((ss_key)::text <> ''::text))

(6 rows)

 

But EXPLAIN ANALYSE query hangs (is not giving me any output even after
30 minutes).

 

Pg_stat_activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time = (select
max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30

 

 

In such a case what can I do ? 

 

[GENERAL] Unknown winsock error 10061

[Sofer, Yuval]

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 on client connection
2010-04-07 07:00:35.694 LOG:  unexpected EOF on client connection
2010-04-07 08:27:53.934 LOG:  received fast shutdown request
2010-04-07 08:27:53.934 LOG:  aborting any active transactions

Why did the postgres server crashed?
Is it operating system problem or postgres?
Is there any work around ? 

Thanks, 

Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_so...@bmc.com


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

[Randal L. Schwartz]

> "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 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.

I sense this code will end up on thedailywtf.com[1] when you leave
and your successor discovers what you insanely tried to do.

[1] which should be mandatory reading for *all* devs, with the goal
of "never let my code end up here"

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
 http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion

-- 
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] Query is stuck

[Plugge, Joe R.]

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 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: 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...@postgresql.org
Subject: [ADMIN] 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_domain, ss_key, login_time, role_id, 
new_vlan_id from repcopy as a where report_time = (select max(report_time) from 
repcopy as b where a.dm_ip = b.dm_ip and a.dm_user=b.dm_user and b.ss_key != 
'') and report_status = 0 and dm_user = 'u1';
 QUERY PLAN

 Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)
   Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND 
(report_time = (subplan)))
   SubPlan
 ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)
   ->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1 width=8)
 Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = 
(dm_user)::text) AND ((ss_key)::text <> ''::text))
(6 rows)

But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 
minutes).

Pg_stat_activity shows this - SELECT procpid, usename, current_query, 
query_start from pg_stat_activity:
2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, 
sys_name,  sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id 
from repcopy as a where report_time = (select max(report_time) from repcopy as 
b where a.dm_ip = b.dm_ip and a.dm_user=b.dm_user and b.ss_key != '') and 
report_status = 0 and dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30


In such a case what can I do ?

Re: [GENERAL] Email address column verification foraddresslist

[Andrus]

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 comma-separated
addresses, which are not universally usable when a single address
is provided.


Application code does not process this column content. It passes it to 
blat.dll which

can process list.

Andrus. 



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

[Peter Geoghegan]

> 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 Randal. Client application developers can simply read the
regex in the check constraint to see its behaviour :-)

Regards,
Peter Geoghegan

-- 
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] Query is stuck

[Ray Stell]

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

[Bill Moran]

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=# explain select report_id, dm_ip, dm_mac, dm_user, dm_os,
> > report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
> > role_id, new_vlan_id from repcopy as a where report_time = (select
> > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
> > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user =
> > 'u1';
> >
> >  QUERY PLAN
> >
> >
> > 
> >
> >  Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)
> >
> >Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
> > (report_time = (subplan)))
> >
> >SubPlan
> >
> >  ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)
> >
> >->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1 width=8)
> >
> >  Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
> > (dm_user)::text) AND ((ss_key)::text <> ''::text))
> >
> > (6 rows)
> >
> >
> >
> > But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30
> > minutes).
> >
> >
> >
> > Pg_stat_activity shows this - SELECT procpid, usename, current_query,
> > query_start from pg_stat_activity:
> >
> > 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
> > report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
> > role_id, new_vlan_id from repcopy as a where report_time = (select
> > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
> > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user =
> > 'u1'; | 2010-04-13 18:20:02.828623+05:30
> >
> >
> >
> >
> >
> > In such a case what can I do ?
> >
> >
> > First things that came to my mind:
> 
> 1. Check if the query waits on some lock: add the column `waiting` to the
> above query from pg_stat_activity.
> 2. Run vacuum analyze on the table repcopy

In addition to that, indexes on report_time, report_status, and dm_user
might help.

And your query is not "hung", it's just taking a LONG time.  Based
on the explain, it could take several hours to complete.  How many
rows are in repcopy?  What is your vacuum schedule?  Do a vacuum verbose,
if the number of dead rows is very high on that table, you may benefit
from doing a VACUUM FULL + REINDEX or CLUSTER on the table.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
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] Query is stuck

[Satish Burnwal (sburnwal)]

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

 

What version of postgres are you on?

 

Re: [GENERAL] Unknown winsock error 10061

[Jaiswal Dhaval Sudhirkumar]

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...@postgresql.org] On Behalf Of Sofer, Yuval
Sent: Tuesday, April 13, 2010 1:48 PM
To: pgsql-general@postgresql.org
Cc: Primor, Shay
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 on client connection
2010-04-07 07:00:35.694 LOG:  unexpected EOF on client connection
2010-04-07 08:27:53.934 LOG:  received fast shutdown request
2010-04-07 08:27:53.934 LOG:  aborting any active transactions

Why did the postgres server crashed?
Is it operating system problem or postgres?
Is there any work around ? 

Thanks, 

Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_so...@bmc.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any 
action in reliance upon,this information by persons or entities other than the 
intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the 
material from your computer. 
Microland takes all reasonable steps to ensure that its electronic 
communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for 
any virus introduced by this e-mail or any attachment and you are advised to 
use up-to-date virus checking software. 


-- 
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] Query is stuck

[Adrian Klaver]

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 is you are being caught by this;

pg_stat_activity

"Furthermore, these columns are only visible if the user examining the view is 
a 
superuser or the same as the user owning the process being reported on. '

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] Query is stuck

[Jaiswal Dhaval Sudhirkumar]

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)
Sent: Tuesday, April 13, 2010 2:04 PM
To: Plugge, Joe R.; pgsql-general@postgresql.org
Cc: pgsql-ad...@postgresql.org
Subject: 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: 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...@postgresql.org
Subject: [ADMIN] 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_domain, ss_key,
login_time, role_id, new_vlan_id from repcopy as a where report_time =
(select max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1';

 QUERY PLAN




 Seq Scan on repcopy a  (cost=0.00..1630178118.35 rows=35 width=133)

   Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
(report_time = (subplan)))

   SubPlan

 ->  Aggregate  (cost=8151.65..8151.66 rows=1 width=8)

   ->  Seq Scan on repcopy b  (cost=0.00..8151.65 rows=1
width=8)

 Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
(dm_user)::text) AND ((ss_key)::text <> ''::text))

(6 rows)

 

But EXPLAIN ANALYSE query hangs (is not giving me any output even after
30 minutes).

 

Pg_stat_activity shows this - SELECT procpid, usename, current_query,
query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os,
report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time,
role_id, new_vlan_id from repcopy as a where report_time = (select
max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1'; | 2010-04-13 18:20:02.828623+05:30

 

 

In such a case what can I do ? 

 

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any 
action in reliance upon,this information by persons or entities other than the 
intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the 
material from your computer. 
Microland takes all reasonable steps to ensure that its electronic 
communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for 
any virus introduced by this e-mail or any attachment and you are advised to 
use up-to-date virus checking software. 

Re: [GENERAL] [ADMIN] Query is stuck

[Satish Burnwal (sburnwal)]

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:  vacuuming "public.repcopy"
INFO:  "repcopy": found 0 removable, 20 nonremovable row versions in 4652 
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 182 to 182 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 416144 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 6856 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  index "repcopy_pk" now contains 20 row versions in 441 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.00u sec elapsed 0.06 sec.
INFO:  "repcopy": moved 0 row versions, truncated 4652 to 4652 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_18398"
INFO:  "pg_toast_18398": found 0 removable, 0 nonremovable row versions in 0 
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_18398_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.repcopy"
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 ;

There is no update happening to the table.

-

> 1. Check if the query waits on some lock: add the column `waiting` to the
> above query from pg_stat_activity.
> 2. Run vacuum analyze on the table repcopy

In addition to that, indexes on report_time, report_status, and dm_user
might help.

And your query is not "hung", it's just taking a LONG time.  Based
on the explain, it could take several hours to complete.  How many
rows are in repcopy?  What is your vacuum schedule?  Do a vacuum verbose,
if the number of dead rows is very high on that table, you may benefit
from doing a VACUUM FULL + REINDEX or CLUSTER on the table.


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

[GENERAL] General question about speed of functions

[Brent Friedman]

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 
performance gains available, so even a five percent difference between a 
function written in c vs pgsql would be helpful.


The apparent choices available for the existing application are to use 
functions in pgsql, php or c.


Any comments about relative performance of using any of those languages 
would be greatly appreciated.


Thanks,

Brent Friedman

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

[tv]

> 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" table, occupying roughly 40MB. And according to the explain plan
you've posted earlier, there's a seq scan for each row - that gives 20
sequential scans on the table ... which is about 8TB of data. Sure, most of
the data will be read from disk cache / shared buffers etc. but still it's
a lot of data to process - that's why it takes so long.

I'd recommend creating a index on (dm_user, dm_ip) columns, but it depends
on how many different values are in these columns (the more the better).

What information do we need to give better recommendations:

1) info about structure of the "repcopy" table (column data types, indexes)
2) info about data (how many different values are there)
3) what does the system do when running the query (use 'top' or 'dstat' to
get iowait / CPU / disk / memory etc.)

regards
Tomas

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

[GENERAL] optimalisation with EXCEPT clause

[Kincel, Martin]

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 exactly as I need, but there is a small issue I am thinking
about how to improve. Yes it's performance, what else? :)

Since I am INSERTing new_collected_data() in 1-rows chunks into a
table already containing millions of rows, it takes a few minutes
(literally), which is something I understand and accept. 
However, I am wondering whether there is any way how to improve the
performance, either via indices, or ALTERing TABLE with UNIQUE
constraint or something else I might have completely forgot about.

Does anyone have any recommended approach how to speed up queries
containing EXCEPT clause? 


Thanks a lot,
Winco

-- 
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] optimalisation with EXCEPT clause

[Grzegorz Jaśkiewicz]

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).


-- 
GJ

Re: [GENERAL] General question about speed of functions

[tv]

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 implementations in the past, but this project can use any 
> performance gains available, so even a five percent difference between a 
> function written in c vs pgsql would be helpful.
> 
> The apparent choices available for the existing application are to use 
> functions in pgsql, php or c.

This greatly depends on the type of processing you're doing. If you perform
a CPU intensive data processing (a lot of math applied to the data), the C
functions may give you tremendous performance gain.

But in most cases the processing consists of calls to the SQL engine and
simple processing of the results, so it will be about as fast as C (and
much easier to develop and maintain).

I have no experience with PL/PHP, but I'd say it will be about as fast as
PL/pgSQL ...

> Any comments about relative performance of using any of those languages 
> would be greatly appreciated.

My recommendation is "Pay attention to properly design the schema, and
don't
hesitate to change it during development. Choose one of the languages and
stick with. And the money you save (due to development in a single
language)
invest into better hardware.

Tomas

-- 
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] Dynamic Catalog Views

[raghavendra t]

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 & Regards
Raghavendra

2010/4/13 

>  > 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, data about whats in the cache and buffers is not accessible via
> > any pg_catalog schema tables.
>
> It's not accessible by default, but there is contrib package called
> 'pg_buffercache':
>
> http://www.postgresql.org/docs/8.4/interactive/pgbuffercache.html
>
> I guess that might provide the information about buffers.
>
> Tomas
>
>
>
>
> --
> 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] Dynamic Catalog Views

[Greg Smith]

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_statio_user_tables, pg_stat_user_indexes, and pg_statio_user_indexes
2) Dig into pg_buffercache to find out what's sitting in RAM and how 
well the usage count implementation is working for you

3) Monitor pg_stat_bgwriter to find out what checkpoints are doing
4) Turn on log_temp_files (8.3 or later) to log when work_mem is being 
exceeded and sorts are going to disk.


That's really it; the rest of the sort of data that's available in the 
v$ views isn't exposed in nearly as much detail in PostgreSQL yet.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] C-language functions: SRF question

[Tom Lane]

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 we should
> use for a whole-plugin-lifetime persistence.

fn_mcxt will typically point at a query-lifespan context.  If you need
to allocate storage that will live as long as the session, there's not
that much reason not to use malloc, except perhaps that you have to
remember to do your own out-of-memory-failure checks.  The typical
coding pattern in the backend for session-lifespan storage is to use
TopMemoryContext, or possibly make your own context (as a child of
TopMemoryContext) so that you can easily identify how much storage
you're using for this purpose.

regards, tom lane

-- 
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] Need some help with a query (uniq -c)

[A B]

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 time values are contiguous, but there's probably a
> #similar trick for non-contiguous ,too.
>
> --create data
> drop table foo;
> create table foo( time int,message text);
> insert into foo values(1,'a');
> insert into foo values(2,'b');
> insert into foo values(3,'b');
> insert into foo values(4,'c');
> insert into foo values(5,'a');
> insert into foo values(6,'c');
> insert into foo values(7,'c');
> insert into foo values(8,'a');
> insert into foo values(9,'a');
> insert into foo values(10,'a');
>
> --begin Answer
> with recursive r as (
> select foo.time,foo.message,1  as dummy from foo
> union all
> select foo.time,foo.message,r.dummy+1 from foo , r
> where foo.time=r.time-1 and foo.message=r.message
> )
> ,rr as (
> select foo.time,foo.message,'OLID' as flag  from foo
> union all
> select foo.time,foo.message,'DUP' as flag from foo , rr
> where foo.time-1=rr.time-2 and foo.message=rr.message
> )
> select time min,time+max(dummy)-1 max,message,max(dummy) counts
> from r where time not in (select distinct (time+1) times from rr
> where flag='DUP') group by time,message order by time;
>
> --result
> postgres(# where flag='DUP') group by time,message order by time;
>  min | max | message | counts
> -+-+-+
>   1 |   1 | a       |      1
>   2 |   3 | b       |      2
>   4 |   4 | c       |      1
>   5 |   5 | a       |      1
>   6 |   7 | c       |      2
>   8 |  10 | a       |      3
> (6 rows)
>
> --end
>
> But I think some one can provide more simple SQL.
>
> Thank you.
>>
>> On Apr 12, 2010, at 11:31 AM, Scott Marlowe wrote:
>>
>>
>>>
>>> On Mon, Apr 12, 2010 at 12:22 PM, A B  wrote:
>>>

 Hello!

 I have a table (think of it as a table of log messages)

 time |  message
 ---
 1      |   a
 2      |   b
 3      |   b
 4      |  b
 5      |  a

 the three 'b' are the same message, so I would like to write a query
 that would give me a result that is similar to what the unix command
 "uniq -c" would give:

 first |  message | last | count
 --
 1     |     a              |   1   |     1
 2     |     b              |   4   |     3<--- here it squeezes
 similar consecutive messages into a single row
 5     |     a              |   5   |     1

 How do I write such a command?

>>>
>>> Pretty straight ahead:
>>>
>>> select min(t), message, max(t), count(*) from table group by message.
>>>
>>
>> That was my first though too, but it combines everything not just adjacent
>> messages.
>>
>> Something like this, maybe
>>
>> select t1.message, t1.time as first, t2.time as last, t2.time-t1.time+1 as
>> count
>>         from foo as t1, foo as t2
>>     where t1.time<= t2.time and t1.message = t2.message
>>         and not exists
>>             (select * from foo as t3
>>              where (t3.time between t1.time and t2.time and t3.message<>
>>  t1.message)
>>              or (t3.time = t2.time + 1 and t3.message = t1.message)
>>              or (t3.time = t1.time - 1 and t3.message = t1.message));
>>
>>  message | first | last | count
>> -+---+--+---
>>  a       |     1 |    1 |     1
>>  b       |     2 |    4 |     3
>>  a       |     5 |    5 |     1
>>
>> That'll only work if the time values are contiguous, but there's probably
>> a
>> similar trick for non-contiguous.
>>
>> Cheers,
>>   Steve
>>
>>
>>
>
>
> --
> 
> Kenichiro Tanaka
> K.K.Ashisuto
> http://www.ashisuto.co.jp/english/index.html
> 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] When is an explicit cast necessary?

[Tom Lane]

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 constants
to implicitly cast to smallint parameters would outweigh the
positives.  As an example, such simple expressions as "2 + 2" would
start to fail because it'd be unclear whether int or smallint addition
is meant.  (And the point isn't academic, since for example it would
affect the overflow threshold.)

regards, tom lane

-- 
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] psql's \d display of unique index vs. constraint

[Tom Lane]

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
>> when I have to manually drop and recreate the index or constraint to
>> speed up a bulk load.

> Yes, I have also been annoyed by that.  Perhaps you could work out a
> proposed change and send it to the hackers list.  You don't necessarily
> need to code it up, but make some mock-ups about how things would look
> in different situations.

Please note that we already rejected the use of a separate constraints
subheading in connection with EXCLUDE constraints; a patch to introduce
one in order to distinguish unique constraints from manually-created
unique indexes isn't likely to fare much better.  My recollection is
that it's intentional that psql obscures the difference, because for
most querying purposes there isn't any difference.  I agree that
sometimes you'd like to know the difference, so I could see making some
small change that would make it possible to tell the difference when
needed.  But I think it shouldn't make the two cases look completely
unrelated.  Maybe something like saying "unique constraint" vs just
"unique" would fly.

regards, tom lane

-- 
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] Showing debug messages in my C function

[Tom Lane]

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/docs/8.4/interactive/error-message-reporting.html
> http://www.postgresql.org/docs/current/static/runtime-config-logging.html

> but I can't see any of my log messages.

Seems likely to me that the function is crashing before it gets to any
of the elog calls; which would most likely be a problem in linking or in
declaring the function.  Did you remember to use the
PG_FUNCTION_INFO_V1() macro?  Try writing a trivial function that only
does an elog and see what it takes to get that to work.

regards, tom lane

-- 
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] psql's \d display of unique index vs. constraint

[Scott Marlowe]

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 two. Normally, I don't care which is which, except for
>>> when I have to manually drop and recreate the index or constraint to
>>> speed up a bulk load.
>
>> Yes, I have also been annoyed by that.  Perhaps you could work out a
>> proposed change and send it to the hackers list.  You don't necessarily
>> need to code it up, but make some mock-ups about how things would look
>> in different situations.
>
> Please note that we already rejected the use of a separate constraints
> subheading in connection with EXCLUDE constraints; a patch to introduce
> one in order to distinguish unique constraints from manually-created
> unique indexes isn't likely to fare much better.  My recollection is
> that it's intentional that psql obscures the difference, because for
> most querying purposes there isn't any difference.  I agree that
> sometimes you'd like to know the difference, so I could see making some
> small change that would make it possible to tell the difference when
> needed.  But I think it shouldn't make the two cases look completely
> unrelated.  Maybe something like saying "unique constraint" vs just
> "unique" would fly.

Yeah, probably make it show up for \d+ or something.

-- 
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] Unknown winsock error 10061

[Craig Ringer]

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:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fuzzy string matching of product names

[Bruce Momjian]

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 separate
column.

---


> 
> George
> 
> On Mon, Apr 5, 2010 at 4:23 PM, Brian Modra  wrote:
> 
> > On 05/04/2010, Peter Geoghegan  wrote:
> > > Hello,
> > >
> > > At the moment, users of my application, which runs on 8.4.3, may
> > > search for products in a way that is implemented roughly like this:
> > >
> > > SELECT * FROM products WHERE description ILIKE '%%usr_string%%';
> > >
> > > This works reasonably well. However, I thought it would be a nice
> > > touch to give my users leeway to spell product names incorrectly when
> > > searching, or to not have to remember if a product is entered as "coca
> > > cola", "CocaCola" or "Coca-cola". At the moment, they don't have to
> > > worry about case sensitivity because I use ILIKE - I'd like to
> > > preserve that. I'd also like to not have it weigh against them heavily
> > > when they don't search for a specific product, but just a common
> > > substring. For example, if they search for "coca-cola", there may be a
> > > number of different coca-cola products: "CocaCola 330ml can",
> > > "Coca-Cola 2 litre bottle", but no actual plain "cocacola". That ought
> > > to not matter too much - all cocacola products should be returned.
> > >
> > > This isn't important enough for me to be willing to add a big
> > > dependency to my application. I'd really prefer to limit myself to the
> > > contrib modules. pg_trgm and fuzzystrmatch look very promising, but
> > > it's not obvious how I can use either to achieve what I want.
> > > Postgres's built-in regex support may have a role to play too.
> > >
> > > I can live with it not being indexable, because typically there are
> > > only tens of thousands of products in a production system.
> > >
> > > Could someone suggest an approach that is reasonably simple and
> > > reasonably generic ?
> >
> > What I do is to create another column that has a simplified version of
> > the string in it.
> > (I created a function to simplify strings, and when the source column
> > is changed or inserted, I also update the "simplified" column.
> > Then when searching, I use the same function to "simplify" the search
> > string and use "=" to test against the "simplified" column.
> >
> > E.g.
> > if the table has a column called "name" that you want to search, you
> > create a name_simplified column, and fill it as so:
> > update your_table set name_simplified=yourSimplifyFunction(name);
> >
> > Then to search:
> > select * from your_table where simplified_name =
> > yourSimplifyFunction('Coca-Cola');
> >
> > This is really fast, because the match is using the index rather than
> > a sequential scan.
> >
> > >
> > > Thanks,
> > > Peter Geoghegan
> > >
> > > --
> > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-general
> > >
> >
> >
> > --
> > Brian Modra   Land line: +27 23 5411 462
> > Mobile: +27 79 69 77 082
> > 5 Jan Louw Str, Prince Albert, 6930
> > Postal: P.O. Box 2, Prince Albert 6930
> > South Africa
> > http://www.zwartberg.com/
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
> 
> 
> 
> -- 
> George R. C. Silva
> 
> Desenvolvimento em GIS
> http://blog.geoprocessamento.net

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
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] psql's \d display of unique index vs. constraint

[Josh Kupershmidt]

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 to try a patch myself at the moment :(

Josh

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

[GENERAL] pl/java status

[John R Pierce]

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, but 
the foundry has 1.4.0 from 2008 that supports 8.3. only


--
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] psql's \d display of unique index vs. constraint

[Greg Smith]

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 with clear value, which we can 
always use more of.  I added it to 
http://wiki.postgresql.org/wiki/Prioritised_Todo#psql  so people looking 
for an easy patch to chew on one day can find it.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] pl/java status

[Greg Smith]

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 dead as it appears.  The code in CVS supports 8.4, there just hasn't 
been an official release in a while:  
http://pgfoundry.org/tracker/?func=detail&atid=335&aid=1010695&group_id=138


There are a couple of bugs to note at 
http://pgfoundry.org/tracker/?atid=334&group_id=138&func=browse like 
lack of support for JDK 1.6 and some known complier issues. 


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
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] pl/java status

[John R Pierce]

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 it's not 
quite as dead as it appears.  The code in CVS supports 8.4, there just 
hasn't been an official release in a while:  
http://pgfoundry.org/tracker/?func=detail&atid=335&aid=1010695&group_id=138 



There are a couple of bugs to note at 
http://pgfoundry.org/tracker/?atid=334&group_id=138&func=browse 
like lack of support for JDK 1.6 and some known complier issues.


k.  the guys who asked me are probably fine with java 1.5, but they 
definately want to use 8.4.  on solaris 10 sparc  :)



does the package contain a test suite so I'm sure its functional if I 
build it?




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

[GENERAL] Server not Starting

[raghavendra t]

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 postgres
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[postg...@dbarhel564 mypg]$

Checked in the Data Directory
---
[postg...@dbarhel564 mypg]$ ls
arch  db.dmp  mypg.dmp mytablespace  pg_hba.confpg_log
pg_stat_tmp  pg_tblspcPG_VERSION  postgresql.conf
base  global  mypgtar.tar  pg_clog   pg_ident.conf  pg_multixact
pg_subtrans  pg_twophase  pg_xlog postmaster.opts
[postg...@dbarhel564 mypg]$
I havnt find the postmaster.pid here.

Could you please guide me.

Regards
Raghavendra

Re: [GENERAL] Server not Starting

[Ashesh Vashi]

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...
>
> 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 postgres
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
> [postg...@dbarhel564 mypg]$
>
> Checked in the Data Directory
> ---
> [postg...@dbarhel564 mypg]$ ls
> arch  db.dmp  mypg.dmp mytablespace  pg_hba.confpg_log
> pg_stat_tmp  pg_tblspcPG_VERSION  postgresql.conf
> base  global  mypgtar.tar  pg_clog   pg_ident.conf  pg_multixact
> pg_subtrans  pg_twophase  pg_xlog postmaster.opts
> [postg...@dbarhel564 mypg]$
> I havnt find the postmaster.pid here.
>
> Could you please guide me.
>
> Regards
> Raghavendra
>
>

Re: [GENERAL] Server not Starting

[raghavendra t]

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;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100   # (change requires restart)

regards
raghavendra


On Wed, Apr 14, 2010 at 11:00 AM, Ashesh Vashi <
ashesh.va...@enterprisedb.com> wrote:

> 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...
>>
>> 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 postgres
>> psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>> [postg...@dbarhel564 mypg]$
>>
>> Checked in the Data Directory
>> ---
>> [postg...@dbarhel564 mypg]$ ls
>> arch  db.dmp  mypg.dmp mytablespace  pg_hba.confpg_log
>> pg_stat_tmp  pg_tblspcPG_VERSION  postgresql.conf
>> base  global  mypgtar.tar  pg_clog   pg_ident.conf  pg_multixact
>> pg_subtrans  pg_twophase  pg_xlog postmaster.opts
>> [postg...@dbarhel564 mypg]$
>> I havnt find the postmaster.pid here.
>>
>> Could you please guide me.
>>
>> Regards
>> Raghavendra
>>
>>
>
>

Re: [GENERAL] Server not Starting

[raghavendra t]

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 889
DETAIL:  Could not read from file "pg_clog/" at offset 0: Success.
LOG:  startup process (PID 303) exited with exit code 1
LOG:  aborting startup due to startup process failure
~
Regards
Raghavendra
On Wed, Apr 14, 2010 at 11:02 AM, raghavendra t
wrote:

> 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;
> # defaults to 'localhost', '*' =
> all
> # (change requires restart)
> port = 5432 # (change requires restart)
> max_connections = 100   # (change requires restart)
>
> regards
> raghavendra
>
>
> On Wed, Apr 14, 2010 at 11:00 AM, Ashesh Vashi <
> ashesh.va...@enterprisedb.com> wrote:
>
>> 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 <
>> raagavendra@gmail.com> wrote:
>>
>>> 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 postgres
>>> psql: could not connect to server: No such file or directory
>>> Is the server running locally and accepting
>>> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>>> [postg...@dbarhel564 mypg]$
>>>
>>> Checked in the Data Directory
>>> ---
>>> [postg...@dbarhel564 mypg]$ ls
>>> arch  db.dmp  mypg.dmp mytablespace  pg_hba.confpg_log
>>> pg_stat_tmp  pg_tblspcPG_VERSION  postgresql.conf
>>> base  global  mypgtar.tar  pg_clog   pg_ident.conf  pg_multixact
>>> pg_subtrans  pg_twophase  pg_xlog postmaster.opts
>>> [postg...@dbarhel564 mypg]$
>>> I havnt find the postmaster.pid here.
>>>
>>> Could you please guide me.
>>>
>>> Regards
>>> Raghavendra
>>>
>>>
>>
>>
>

Re: [GENERAL] pl/java status

[Craig Ringer]

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's not
really much reason Java should be restricted to the client side when the
same language can work well server-side too.

Not to mention all those Oracle users out there who're hooked on the stuff.

Of course, Java is extremely thread-happy and PostgreSQL is extremely
*not*, so perhaps there are reasons it doesn't seem to see much work. If
it's anything like working on an embedded Python interpreter...

--
Craig Ringer

-- 
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] Server not Starting

[Shoaib Mir]

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
> LOG:  redo is not required
> FATAL:  could not access status of transaction 889
> DETAIL:  Could not read from file "pg_clog/" at offset 0: Success.
> LOG:  startup process (PID 303) exited with exit code 1
> LOG:  aborting startup due to startup process failure
>

Try using pg_resetxlog to reset the control info on the DB cluster. Once you
have done that try re-starting and hopefully that should work then, but you
will I think lose everything from the last checkpoint by using pg_resetxlog.

-- 
Shoaib Mir
http://shoaibmir.wordpress.com/