Re: [GENERAL] MinGW compiled client library

2013-08-16 Thread Albe Laurenz 
Michael Cronenworth wrote:
> On 08/15/2013 10:59 AM, Michael Cronenworth wrote:
> > The attached patches resolve the issue.
> 
> Should I forward the patches on to the pgsql-hackers list for review or is 
> this
> list sufficient? (First time PostgreSQL hacker.)

Yes, any patches should be posted to -hackers, in this case
with a archive reference to the discussion on -general.

Please read http://wiki.postgresql.org/wiki/Submitting_a_Patch

To make sure that the patch does not get lost, add it to
the next commitfest on https://commitfest.postgresql.org/
(this is not required for bugfixes, but helps).

Yours,
Laurenz Albe

-- 
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] SSL connection has been closed unexpectedly

2013-08-16 Thread Albe Laurenz 
Stuart Ford wrote:
> We have a problem on our development database server, which supports a PHP
> application, which connects to it from a different server. Sometimes,
> around 1 in 4 page loads, it fails and reports the following error message:
> 
> FATAL: terminating connection due to administrator command SSL connection
> has been closed unexpectedly

Funny - that error message (with the SSL part included) does not
appear in PostgreSQL source.

Could you dig the exact error messages out of the database log?

Yours,
Laurenz Albe

-- 
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] devide and summarize sql result (all)

2013-08-16 Thread Janek Sendrowski 

Thanks for your Answers,

 

my problem is, that there is no column with the name 'percentage'.

It's just a result of my query. So how can I use it anyway?

Should I just store the result in a record variable and do another query?

 

Janek

 

Gesendet: Freitag, 16. August 2013 um 00:24 Uhr
Von: bricklen 
An: "Janek Sendrowski" 
Cc: "pgsql-general@postgresql.org" 
Betreff: Re: [GENERAL] devide and summarize sql result (all)




On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski  wrote:




Hi,
 
My sql query results sth. like this:
 
user percentage
franz 78%
smith 98%
franz 81%
jason 79%

smith 89%

smith 85%

smith 99%

 

Now I'd like to summarize the percentages oder every user like this.

smith

2 matches 95-100%

2 matches 85-95%

0 mathes 75-85%

 

franz

0 mathes 95-100%

...






A CASE statement should work, if you are willing to hard-code the list of expressions.

SELECT  username,
    sum(case when avg between 76 and 85 then 1 else 0 end) as "76 to 85",
    sum(case when avg between 86 and 95 then 1 else 0 end) as "86 to 95",
    sum(case when avg > 95 then 1 else 0 end) as ">95"
FROM yourtable
GROUP BY username

 

Re: [GENERAL] devide and summarize sql result (all)

2013-08-16 Thread salah jubeh 
Hello, 

Use a view 

Regards





 From: Janek Sendrowski 
To: pgsql-general@postgresql.org 
Sent: Friday, August 16, 2013 11:55 AM
Subject: Re: [GENERAL] devide and summarize sql result (all)
 


Thanks for your Answers,
 
my problem is, that there is no column with the name 'percentage'.
It's just a result of my query. So how can I use it anyway?
Should I just store the result in a record variable and do another query?
 
Janek
  
Gesendet: Freitag, 16. August 2013 um 00:24 Uhr
Von: bricklen 
An: "Janek Sendrowski" 
Cc: "pgsql-general@postgresql.org" 
Betreff: Re: [GENERAL] devide and summarize sql result (all)
On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski  wrote: 
Hi,
> 
>My sql query results sth. like this:
> 
>user percentage
>franz 78%
>smith 98%
>franz 81%
>jason 79%
>smith 89%
>smith 85%
>smith 99%
> 
>Now I'd like to summarize the percentages oder every user like this.
>smith
>2 matches 95-100%
>2 matches 85-95%
>0 mathes 75-85%
> 
>franz
>0 mathes 95-100%
>...
A CASE statement should work, if you are willing to hard-code the list of 
expressions.

SELECT  username,
    sum(case when avg between 76 and 85 then 1 else 0 end) as "76 to 85",
    sum(case when avg between 86 and 95 then 1 else 0 end) as "86 to 95",
    sum(case when avg > 95 then 1 else 0 end) as ">95"
FROM yourtable
GROUP BY username

Re: [GENERAL] Forcing materialize in the planner

2013-08-16 Thread Jack Christensen 
Have you tried putting those components in a common table expression? 
I'm not sure if it absolutely forces the materialization or not, but in 
practice that has been my experience.


Robert James wrote:

I have a query which, when I materialize by hand some of its
components, runs 10x faster (including the time needed to
materialize).  Is there any way to force Postgres to do that? Or do I
need to do this by hand using temp tables?





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

[GENERAL] what \ command actually run

2013-08-16 Thread Steve Clark 

Hello,

I seem to recall seeing somewhere that you can turn on an option that
will let you see what the \ command actually run, but googling and doing
a quick scan of the docs didn't turn it up. Could someone assist me on
this?

Thanks,
--
Stephen Clark
*NetWolves*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Re: [GENERAL] what \ command actually run

2013-08-16 Thread Adrian Klaver 

On 08/16/2013 07:07 AM, Steve Clark wrote:

Hello,

I seem to recall seeing somewhere that you can turn on an option that
will let you see what the \ command actually run, but googling and doing
a quick scan of the docs didn't turn it up. Could someone assist me on
this?


psql -E

http://www.postgresql.org/docs/9.2/interactive/app-psql.html

-E
--echo-hidden
Echo the actual queries generated by \d and other backslash commands. 
You can use this to study psql's internal operations. This is equivalent 
to setting the variable ECHO_HIDDEN from within psql.




Thanks,
--
Stephen Clark
*NetWolves*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com



--
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] Commit problem in read-commited isolation level

2013-08-16 Thread Adrian Klaver 

On 08/15/2013 10:45 PM, S H wrote:


 > Any triggers on the table?
There are no trigger associated with this table.

 > FYI 8.1 is no longer supported.
I understand that. If there are some known related issues, it will be
easy to convince, Product mgmt team to upgrade the version of postgresql.

Are there known issues related to commit problem in 8.1 version.


Could be, there where 24 releases over five years. Just not sure at this 
point you actually are facing a commit problem and more information is 
needed in any case.


1) What is your exact Postgres version i.e 8.1.5.?


2) In your original post you had this sequence:

Current value in colname = 5;
Update tablename set colname = 0 where key = 18;
commit , in parallel to above queries ( either vacuum or reindex of 
table was running)


After 10 sec following query is executed.
select colname from tablename where key = 18 ;it is returning old value 
i.e colname = 5.
After another few seconds select colname from tablename where key = 18 
;it is returning new value i.e colname = 5.


You say at the top the new value is 0, but show 5 as the new value at 
the bottom.


Which is correct?

3) You also say it works fine in two environments, but not one.

What are the environments?
 OS and version, memory, Postgres versions, etc.





 > Date: Wed, 7 Aug 2013 17:05:59 -0700
 > From: adrian.kla...@gmail.com
 > To: msq...@live.com
 > CC: pgsql-general@postgresql.org
 > Subject: Re: [GENERAL] Commit problem in read-commited isolation level
 >
 > On 08/07/2013 04:54 PM, S H wrote:
 > > Hi,
 > >
 > > I have faced very strange problem in one of psotgresql query in one of
 > > the production environment. It is working fine in development and other
 > > environment.
 > >
 > > Current value in colname = 5;
 > > Update tablename set colname = 0 where key = 18;
 > > commit , in parallel to above queries ( either vacuum or reindex of
 > > table was running)
 > >
 > > After 10 sec following query is executed.
 > >
 > > select colname from tablename where key = 18 ;
 > > it is returning old value i.e colname = 5.
 > >
 > > After another few seconds
 > > select colname from tablename where key = 18 ;
 > > it is returning new value i.e colname = 5.
 >
 > I thought the new value is 0?
 > >
 > >
 > > Isolevel level is readcommited.
 > > Is there any possibility of bug in commit in V8.1 leading to delay of
 > > commit ?
 >
 > Any triggers on the table?
 >
 > FYI 8.1 is no longer supported.
 >
 > >
 > > I need to provide explanation of above behavior to my customer.
 > >
 > > Regards,
 >
 >
 > --
 > 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



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

2013-08-16 Thread David Johnston 
Basavaraj wrote
> i have two unrelated tables as their is no common column to apply joins or
> anything, so i want to join those tables...

I can understand being required to join them (for some unstated reason) but
a simple want does not seem a strong enough reason to go through the pain...


> ...using simple query...

If you have to join these tables together the complexity of the resultant
query should be irrelevant.  Furthermore how one defines "simple" is quite
subjective.


> finally the result shoule be
> 
> name| address|email|mobileNo|firstname|lastName|
> --
> abc   some1mail1   1234564 def  xyz
> 
> 5 records   |   10 records
> 
>|
> 
> Very thankful for solution

I have no clue what you mean when you indicate "5 records | 10 records" in
the final result.

I'm tempted to ask you leading questions but instead am going to ask that
you consider your goal more closely and be more explicit in your
description/request.

If you just want to go and play with it you can consider two possible
options:

SELECT * FROM tbl1 CROSS JOIN tbl2 --(this will return X times Y rows - or
10 times 5 = 50)

or 

SELECT * 
FROM (SELECT row_number, * FROM tbl1) t1 
FULL OUTER JOIN (SELECT row_number, * FROM tbl2) t2 USING (row_number)
-- this will return 10 rows with 5 of them containing NULL values for t1
columns where t2 has row numbers not existing in t1.

What you are doing, by the example given, is wrong.  Proposing an
alternative is impossible since you have not explained WHY you feel you need
to do this or WHAT you are actually trying to accomplish.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Here-is-my-problem-tp5766954p5767617.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] pg_get_triggerdef can't find the trigger using OID.

2013-08-16 Thread AI Rumman 
Why can't pg_get_triggerdef find the trigger using OID.

testdb=# SELECT
testdb-#   p.oid,
testdb-#   n.nspname as "Schema",
testdb-#   p.proname as "Name",
testdb-#   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
testdb-#   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data
types",
testdb-#  CASE
testdb-#   WHEN p.proisagg THEN 'agg'
testdb-#   WHEN p.proiswindow THEN 'window'
testdb-#   WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
THEN 'trigger'
testdb-#   ELSE 'normal'
testdb-# END as "Type"
testdb-# FROM pg_catalog.pg_proc p
testdb-#  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
testdb-# WHERE pg_catalog.pg_function_is_visible(p.oid)
testdb-#   AND n.nspname <> 'pg_catalog'
testdb-#   AND n.nspname <> 'information_schema'
testdb-# ORDER BY 1, 2, 4;
  oid  | Schema |  Name   | Result data type | Argument
data types  |  Type
---++-+--+--+-
 18249 | public | test_f  | trigger  |
 | trigger


testdb=# select pg_get_triggerdef(18249);
ERROR:  could not find tuple for trigger 18249

Thanks.

Re: [GENERAL] Debugging Postgres?

2013-08-16 Thread Dennis 
tcpdump and/or wireshark will help with detecting network issues.
Wireshark is a little easier to grok if you are not used to doing packet
dumps.  http://www.wireshark.org/

 

Dennis

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jayadevan M
Sent: Wednesday, August 14, 2013 9:14 PM
To: Barth Weishoff
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Debugging Postgres?

 

Hi,

Having a look at the PostgreSQL log may help.
http://stackoverflow.com/questions/71/how-to-log-postgres-sql-queries

You could also try logging in via psql on the database host itself, to
ensure it is not a network issue.

Regards,

Jayadevan

 

On Wed, Aug 14, 2013 at 2:21 AM, Barth Weishoff  wrote:

Hello

   I'm having an interesting issue with PGSQL.   It seems that I'm
experiencing timeouts at various times.   The servers are not busy and have
plenty of resources.  The databases are ~50GB in size, the systems typically
have 8-12GB physical RAM, and the connections are low (less than 15 at any
given time).  

The issue I'm seeing is that randomly I'm getting these pauses, or stalls,
while trying to simply connect to the database server(s) from connected
clients using the psql command line client.  I cannot tell if the server is
even getting the request for service as they don't seem to show up in the
logs at the time the event is occurring, so I'm thinking it's maybe a
client-side issue.   

Is there a good general starting place for debugging these types of issues ?





-B.

Re: [GENERAL] SSL connection has been closed unexpectedly

2013-08-16 Thread Guy Helmer 
On Aug 15, 2013, at 5:41 AM, Stuart Ford  wrote:

> Dear community
> 
> We have a problem on our development database server, which supports a PHP
> application, which connects to it from a different server. Sometimes,
> around 1 in 4 page loads, it fails and reports the following error message:
> 
> FATAL: terminating connection due to administrator command SSL connection
> has been closed unexpectedly
> 
> Reloading the page usually works, sometimes doesn't, sometimes it requires
> several more refresh attempts before it magically works again. The odd
> thing is that we also have a live platform that is set up in the same way,
> and this does not occur, thankfully, but I expect it could.
> 
> I've tried turning off all SSL features on the development platform, but
> oddly, the same problem persists. I've also tried whacking the logging
> level up to debug5, but still nothing appears in the PG logs when the
> problem occurs.
> 
> Does anybody have any idea what could be happening here?
> 
> Many thanks in advance
> 
> Stuart Ford

Any chance you are using HP ProCurve switches? I believe we have seen these 
switches corrupt SSL connections when systems use flow control signaling. 
Utterly bizarre behavior, but we've seen it at multiple customer sites.

Guy



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

[GENERAL] need help

2013-08-16 Thread Basavaraj 
i have two unrelated tables as their is no common column to apply joins or
anything, so i want to join those tables using simple query(only two tables
should be used and no common table) can you pls help me out how to do that.
I am new user to postgres

this is table format

table1 table2

name| address|email| mobileNo|firstname|lastName|
--  --
abcsome1   mail1   1234564|def | 
: :
: :
  :
:
:5 records  10 records


finally the result shoule be

name| address|email|mobileNo|firstname|lastName|
--
abc   some1mail1   1234564 def  xyz

5 records   |   10 records

   |

Very thankful for solution





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Here-is-my-problem-tp5766954p5767611.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] pg_get_triggerdef can't find the trigger using OID.

2013-08-16 Thread bricklen 
On Fri, Aug 16, 2013 at 8:01 AM, AI Rumman  wrote:

> Why can't pg_get_triggerdef find the trigger using OID.
>
> testdb=# SELECT
> testdb-#   p.oid,
> testdb-#   n.nspname as "Schema",
> testdb-#   p.proname as "Name",
> testdb-#   pg_catalog.pg_get_function_result(p.oid) as "Result data type",
> testdb-#   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data
> types",
> testdb-#  CASE
> testdb-#   WHEN p.proisagg THEN 'agg'
> testdb-#   WHEN p.proiswindow THEN 'window'
> testdb-#   WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
> THEN 'trigger'
> testdb-#   ELSE 'normal'
> testdb-# END as "Type"
> testdb-# FROM pg_catalog.pg_proc p
> testdb-#  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
> testdb-# WHERE pg_catalog.pg_function_is_visible(p.oid)
> testdb-#   AND n.nspname <> 'pg_catalog'
> testdb-#   AND n.nspname <> 'information_schema'
> testdb-# ORDER BY 1, 2, 4;
>   oid  | Schema |  Name   | Result data type |
> Argument data types  |  Type
>
> ---++-+--+--+-
>  18249 | public | test_f  | trigger  |
>  | trigger
>
>
> testdb=# select pg_get_triggerdef(18249);
> ERROR:  could not find tuple for trigger 18249
>
> Thanks.
>

Is it because you need the oid from pg_trigger, rather than pg_proc?
The following query is a fragment of one I needed to put together the other
day and it might be useful to you (the last few SELECT columns are taken
from your query)

SELECT  DISTINCT
tr.oid,
n.nspname as schemaname,
c.relname as tablename,
tr.tgname as triggername,
pr.proname as function_name,
pg_catalog.pg_get_function_result(pr.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(pr.oid) as "Argument data
types",
CASE WHEN pr.proisagg THEN 'agg' WHEN pr.proiswindow THEN 'window'
WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
'trigger'   ELSE 'normal' END as "Type",
CASE WHEN pr.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
THEN pg_get_triggerdef(tr.oid) ELSE NULL END as trigger_def
FROM pg_catalog.pg_class as c
INNER JOIN pg_catalog.pg_attribute as a ON (a.attrelid = c.oid)
INNER JOIN pg_catalog.pg_type as t ON (t.oid = a.atttypid)
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = c.reltablespace
LEFT JOIN pg_trigger tr ON tr.tgrelid::regclass::text = c.relname
LEFT JOIN pg_proc pr ON pr.oid = tr.tgfoid
WHERE a.attnum > 0  -- no system cols
AND NOT attisdropped-- no dropped cols
AND c.relkind = 'r'
AND tr.tgisinternal is not true
AND tr.tgname IS NOT NULL
ORDER BY n.nspname, c.relname

Re: [GENERAL] devide and summarize sql result

2013-08-16 Thread Rob Sargent 

Yeah, I have written that sort of query too, but with more info on tables and 
the SQL you are unlikely to get much help

Sent from my iPad

On Aug 15, 2013, at 2:46 PM, "Janek Sendrowski"  wrote:

> Hi,
>  
> My sql query results sth. like this:
>  
> user  percentage
> franz 78%
> smith 98%
> franz 81%
> jason
> 
> 
> -- 
> 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] Streaming Replication Randomly Locking Up

2013-08-16 Thread John DeSoi 

On Aug 15, 2013, at 1:07 PM, Andrew Berman  wrote:

> I'm having an issue where streaming replication just randomly stops working.  
> I haven't been able to find anything in the logs which point to an issue, but 
> the Postgres process shows a "waiting" status on the slave:
> 
> postgres  5639  0.1 24.3 3428264 2970236 ? Ss   Aug14   1:54 postgres: 
> startup process   recovering 0001053D003F waiting
> postgres  5642  0.0 21.4 3428356 2613252 ? Ss   Aug14   0:30 postgres: 
> writer process
> postgres  5659  0.0  0.0 177524   788 ?Ss   Aug14   0:03 postgres: 
> stats collector process
> postgres  7159  1.2  0.1 3451360 18352 ?   Ss   Aug14  17:31 postgres: 
> wal receiver process   streaming 549/216B3730
> 
> The replication works great for days, but randomly seems to lock up and 
> replication halts.  I verified that the two databases were out of sync with a 
> query on both of them.  Has anyone experienced this issue before? 
> 
> Here are some relevant config settings:
> 
> Master:
> 
> wal_level = hot_standby
> checkpoint_segments = 32
> checkpoint_completion_target = 0.9
> archive_mode = on
> archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f  max_wal_senders = 2   
> wal_keep_segments = 32

I recently posted about the same thing -- replication just stops after working 
OK for days or weeks, no errors in the logs on master or slave.

It appears I solved it by adding --timeout=30 to my rsync command. My guess was 
some kind of network hang and then rsync would just wait forever and never 
return.

John DeSoi, Ph.D.



-- 
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] Streaming Replication Randomly Locking Up

2013-08-16 Thread Andrew Berman 
Awesome, I'll give that a shot John.


On Fri, Aug 16, 2013 at 8:39 AM, John DeSoi  wrote:

>
> On Aug 15, 2013, at 1:07 PM, Andrew Berman  wrote:
>
> > I'm having an issue where streaming replication just randomly stops
> working.  I haven't been able to find anything in the logs which point to
> an issue, but the Postgres process shows a "waiting" status on the slave:
> >
> > postgres  5639  0.1 24.3 3428264 2970236 ? Ss   Aug14   1:54
> postgres: startup process   recovering 0001053D003F waiting
> > postgres  5642  0.0 21.4 3428356 2613252 ? Ss   Aug14   0:30
> postgres: writer process
> > postgres  5659  0.0  0.0 177524   788 ?Ss   Aug14   0:03
> postgres: stats collector process
> > postgres  7159  1.2  0.1 3451360 18352 ?   Ss   Aug14  17:31
> postgres: wal receiver process   streaming 549/216B3730
> >
> > The replication works great for days, but randomly seems to lock up and
> replication halts.  I verified that the two databases were out of sync with
> a query on both of them.  Has anyone experienced this issue before?
> >
> > Here are some relevant config settings:
> >
> > Master:
> >
> > wal_level = hot_standby
> > checkpoint_segments = 32
> > checkpoint_completion_target = 0.9
> > archive_mode = on
> > archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f
>  > max_wal_senders = 2
> > wal_keep_segments = 32
>
> I recently posted about the same thing -- replication just stops after
> working OK for days or weeks, no errors in the logs on master or slave.
>
> It appears I solved it by adding --timeout=30 to my rsync command. My
> guess was some kind of network hang and then rsync would just wait forever
> and never return.
>
> John DeSoi, Ph.D.
>
>

Re: [GENERAL] Streaming Replication Randomly Locking Up

2013-08-16 Thread Jeff Janes 
On Thu, Aug 15, 2013 at 1:28 PM, Andrew Berman  wrote:
> Hi Jeff,
>
> Here is the full process list at the time it stopped working (I have changed
> the actual username, db and IP for security).  Would the idle in transaction
> process be the culprit?

Most likely, yes.  You should be able to dig into pg_locks to verify.


Cheers,

Jeff


-- 
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] Streaming Replication Randomly Locking Up

2013-08-16 Thread Jeff Janes 
On Fri, Aug 16, 2013 at 9:45 AM, Jeff Janes  wrote:
> On Thu, Aug 15, 2013 at 1:28 PM, Andrew Berman  wrote:
>> Hi Jeff,
>>
>> Here is the full process list at the time it stopped working (I have changed
>> the actual username, db and IP for security).  Would the idle in transaction
>> process be the culprit?
>
> Most likely, yes.  You should be able to dig into pg_locks to verify.

Actually, you can't.  The waiting doesn't show up in pg_locks, because
it polls in a sleep-loop, rather than doing a normal wait on the lock.

Still, that idle in transaction process is almost surely the culprit.

Cheers,

Jeff


-- 
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] Streaming Replication Randomly Locking Up

2013-08-16 Thread Andrew Berman 
Ok, next time it happens I'll try to do more sleuthing to figure out if
that's the issue.  For now, I'm going to try adding --timeout=30 to the
rsync command and see if that fixes things.

Thanks again for your help!

Andrew


On Fri, Aug 16, 2013 at 10:12 AM, Jeff Janes  wrote:

> On Fri, Aug 16, 2013 at 9:45 AM, Jeff Janes  wrote:
> > On Thu, Aug 15, 2013 at 1:28 PM, Andrew Berman 
> wrote:
> >> Hi Jeff,
> >>
> >> Here is the full process list at the time it stopped working (I have
> changed
> >> the actual username, db and IP for security).  Would the idle in
> transaction
> >> process be the culprit?
> >
> > Most likely, yes.  You should be able to dig into pg_locks to verify.
>
> Actually, you can't.  The waiting doesn't show up in pg_locks, because
> it polls in a sleep-loop, rather than doing a normal wait on the lock.
>
> Still, that idle in transaction process is almost surely the culprit.
>
> Cheers,
>
> Jeff
>

[GENERAL] Listing privileges on a schema

2013-08-16 Thread David Salisbury 



Hello,


Is there a query out there where I can get a list of permissions associated to 
a schema?
Something like the below query that I can do for a table, but for a schema 
instead?

SELECT grantee, privilege_type 
 FROM information_schema.role_table_grants 


  WHERE table_name='sites' order by grantee, privilege_type;

I'm not seeing anything on the net or anything useful in information_schema 
like a
'role_schema_grants' view, and it doesn't appear I can do a \dp on a schema.
Nor the query psql uses for \dp on a table doesn't seem to have a nice way to
convert it to a schema permissions list.  The secret is escaping me. :(

-ds


Background: -

In pg_log, I'm seeing an error I'd like to clean up.

2013-08-15 13:00:32 GMT ERROR:  permission denied for schema public at 
character 98
2013-08-15 13:00:32 GMT STATEMENT:select s.id, s.name, s.activate_at, s.old_sitecode, s.latitude, s.longitude, s.elevation from 
public.site s where new_schoolid = $1


I get this error on a development system that is a spin off of a production 
system.  The production system doesn't ever produce this
error.  Doing a \dp between production and development the sites table shows no difference.  I'd like to do the same for the public 
schema.  The public schema by default is open to all imho, and by explicitly opening it up ( to who I don't know) I may be just 
removing a symptom.. but not the real problem.




--
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] Listing privileges on a schema

2013-08-16 Thread Adrian Klaver 

On 08/16/2013 10:27 AM, David Salisbury wrote:



Hello,


Is there a query out there where I can get a list of permissions
associated to a schema?
Something like the below query that I can do for a table, but for a
schema instead?

SELECT grantee, privilege_type

FROM information_schema.role_table_grants
   WHERE table_name='sites' order by grantee,
privilege_type;

I'm not seeing anything on the net or anything useful in
information_schema like a
'role_schema_grants' view, and it doesn't appear I can do a \dp on a
schema.
Nor the query psql uses for \dp on a table doesn't seem to have a nice
way to
convert it to a schema permissions list.  The secret is escaping me. :(


aklaver@killi:~> psql -d test -U postgres  -E
psql (9.0.13)
Type "help" for help.

test=# \dn+
* QUERY **
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
  pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE   (n.nspname !~ '^pg_temp_' OR
 n.nspname = (pg_catalog.current_schemas(true))[1])
ORDER BY 1;
**

 List of schemas
Name|  Owner   |  Access privileges   | 
Description

+--+--+--
 information_schema | postgres | postgres=UC/postgres+|
|  | =U/postgres  |
 pg_catalog | postgres | postgres=UC/postgres+| system catalog 
schema

|  | =U/postgres  |
 pg_toast   | postgres |  | reserved schema 
for TOAST tables

 pg_toast_temp_1| postgres |  |
 public | postgres | postgres=UC/postgres+| standard public 
schema

|  | =UC/postgres |
(5 rows)




-ds






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

[GENERAL] Using an index to materialize a function

2013-08-16 Thread Robert James 
If I have a slow_function(), and I create an index of
slow_function(field), will Postgres use that index to avoid having to
recompute the function?

Example:

SELECT slow_function(field1) FROM table1 WHERE id = 5

It won't use the index on field1 to _find_ the record.  Can it use it
to compute the field?


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

[GENERAL] Does string a begin with string b?

2013-08-16 Thread Robert James 
What's the best way to check if string a begins with string b?

Both a and b are coming from fields in a table.

Requirements:

* Either a or b might have special chars (such as '%') in them which
should NOT do anything special - they're just plain strings, not
regular expressions.
* a and b can be of arbitrary length (no fixed limits)
* Ideally, I'd like it to be transparent to the query engine, so that
it can use indexes.  If both a and b are indexed, it's possible in
theory to use the index to do most of the work - but I can't come up
with a query that makes use of it.


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

2013-08-16 Thread John R Pierce 

On 8/16/2013 6:35 AM, Basavaraj wrote:

finally the result shoule be

name| address|email|mobileNo|firstname|lastName|
--
abc   some1mail1   1234564 def  xyz

5 records   |   10 records

|



First, a table is an unordered SET of tuples(rows).  it only has an 
order when one is applied to it.   so which rows of table 1 would go 
with what rows of table 2?


Second, if that 2nd table has 10 records and the first table only has 5, 
what would those other 5 look like in your example?   no answer I can 
think of makes sense.   all rows of a recordset have to have the same 
fields.


If this data is unrelated, then it does not belong together in a relation.


--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
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] Does string a begin with string b?

2013-08-16 Thread Jeff Janes 
On Fri, Aug 16, 2013 at 11:04 AM, Robert James  wrote:
> What's the best way to check if string a begins with string b?
>
> Both a and b are coming from fields in a table.
>
> Requirements:
>
> * Either a or b might have special chars (such as '%') in them which
> should NOT do anything special - they're just plain strings, not
> regular expressions.
> * a and b can be of arbitrary length (no fixed limits)

where substring(a,1,length(b)) = b

> * Ideally, I'd like it to be transparent to the query engine, so that
> it can use indexes.  If both a and b are indexed, it's possible in
> theory to use the index to do most of the work - but I can't come up
> with a query that makes use of it.

I wouldn't count on this being much help.  It might be able to use an
index-only-scan if both a and b are in the index, but then you would
just be treating the index as a skinny table, not as an index.

Cheers,

Jeff


-- 
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] Using an index to materialize a function

2013-08-16 Thread Kevin Grittner 
Robert James  wrote:

> If I have a slow_function(), and I create an index of
> slow_function(field), will Postgres use that index to avoid having to
> recompute the function?
>
> Example:
>
> SELECT slow_function(field1) FROM table1 WHERE id = 5
>
> It won't use the index on field1 to _find_ the record.  Can it use it
> to compute the field?

If you're not going to search on the function results you are
probably better off adding it to the table itself and maintaining
it on BEFORE INSERT and BEFORE UPDATE triggers.  You could play
around with trying to put it just in an index with other columns
and hoping for an index-only scan, but that is probably not a great
way to go.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] Select performance variation based on the different combinations of using where lower(), order by, and limit

2013-08-16 Thread Tyler Reese 
I have a table that has around 8 million rows.  The table has 71 columns
and 33 indexes.

The relevant indexes are:
"callingpartynumber" btree ("CallingPartyNumber")
"callingpartynumber_lower" btree (lower("CallingPartyNumber")
text_pattern_ops)

My question is about the explain analyze output, below.  In my desired
query, I want to use (1) a where clause (with a call to lower() on the
column name), (2) order by, and (3) limit.  When I use all three, my query
is slow, but if i use 2 of the 3 clauses, the query is fast.

case 1: where clause with lower(), and order by
case 2: where clause without lower(), order by, and limit
case 3: where clause with lower(), and limit
case 4: where clause with lower(), order by, and limit

I don't understand why the performance of case 4 is so much slower than the
other three cases.  It isn't using the callingpartynumber_lower index, when
the only difference between case 4 and case 1 is the limit 100 clause.  If
I were to use limit 1, there is no difference.


case 1:
mydb=> explain analyze SELECT * FROM "cdr" WHERE
lower("CallingPartyNumber") = '9725551212' order by "key";
  QUERY
PLAN
--
 Sort  (cost=128701.67..128804.70 rows=41212 width=757) (actual
time=0.425..0.495 rows=11 loops=1)
   Sort Key: key
   Sort Method:  quicksort  Memory: 30kB
   ->  Bitmap Heap Scan on cdr  (cost=916.83..111735.11 rows=41212
width=757) (actual time=0.118..0.246 rows=11 loops=1)
 Recheck Cond: (lower("CallingPartyNumber") = '9725551212'::text)
 ->  Bitmap Index Scan on callingpartynumber_lower
 (cost=0.00..906.53 rows=41212 width=0) (actual time=0.083..0.083 rows=11
loops=1)
   Index Cond: (lower("CallingPartyNumber") =
'9725551212'::text)
 Total runtime: 0.830 ms
(8 rows)


case 2:
mydb=> explain analyze SELECT * FROM "cdr" WHERE "CallingPartyNumber" =
'9725551212' order by "key" limit 100;
QUERY PLAN

--
 Limit  (cost=958.12..958.37 rows=100 width=757) (actual time=3.741..4.035
rows=11 loops=1)
   ->  Sort  (cost=958.12..958.72 rows=240 width=757) (actual
time=3.723..3.834 rows=11 loops=1)
 Sort Key: key
 Sort Method:  quicksort  Memory: 30kB
 ->  Bitmap Heap Scan on cdr  (cost=7.30..948.94 rows=240
width=757) (actual time=3.425..3.553 rows=11 loops=1)
   Recheck Cond: ("CallingPartyNumber" = '9725551212'::text)
   ->  Bitmap Index Scan on callingpartynumber
 (cost=0.00..7.24 rows=240 width=0) (actual time=3.385..3.385 rows=11
loops=1)
 Index Cond: ("CallingPartyNumber" = '9725551212'::text)
 Total runtime: 4.550 ms
(9 rows)


case 3:
mydb=> explain analyze SELECT * FROM "cdr" WHERE
lower("CallingPartyNumber") = '9725551212' limit 100;
   QUERY
PLAN


 Limit  (cost=0.00..385.54 rows=100 width=757) (actual time=0.079..0.343
rows=11 loops=1)
   ->  Index Scan using callingpartynumber_lower on cdr
 (cost=0.00..158886.65 rows=41212 width=757) (actual time=0.059..0.177
rows=11 loops=1)
 Index Cond: (lower("CallingPartyNumber") = '9725551212'::text)
 Total runtime: 0.687 ms
(4 rows)


case 4:
mydb=> explain analyze SELECT * FROM "cdr" WHERE
lower("CallingPartyNumber") = '9725551212' order by "key" limit 100;
QUERY PLAN

--
 Limit  (cost=0.00..72882.05 rows=100 width=757) (actual
time=20481.083..30464.960 rows=11 loops=1)
   ->  Index Scan using cdr_pkey on cdr  (cost=0.00..30036152.32 rows=41212
width=757) (actual time=20481.049..30464.686 rows=11 loops=1)
 Filter: (lower("CallingPartyNumber") = '9725551212'::text)
 Total runtime: 30465.246 ms
(4 rows)

Re: [GENERAL] Using an index to materialize a function

2013-08-16 Thread Tom Lane 
Kevin Grittner  writes:
> Robert James  wrote:
>> If I have a slow_function(), and I create an index of
>> slow_function(field), will Postgres use that index to avoid having to
>> recompute the function?
>> 
>> Example:
>> SELECT slow_function(field1) FROM table1 WHERE id = 5
>> 
>> It won't use the index on field1 to _find_ the record.  Can it use it
>> to compute the field?

> If you're not going to search on the function results you are
> probably better off adding it to the table itself and maintaining
> it on BEFORE INSERT and BEFORE UPDATE triggers.  You could play
> around with trying to put it just in an index with other columns
> and hoping for an index-only scan, but that is probably not a great
> way to go.

Yeah, the functionality for that is pretty primitive right now.  It
will happen if the conditions are right, but the planner doesn't credit
a plan of that form with saving the function computation, so the index
would have to be one it would use anyway.  Moreover the index has to cover
the base column(s) of the function call or a index-only scan will be ruled
out.  Putting those things together, what you'd need for the above example
is an index on (id, slow_function(field1), field1).  Or, if you sometimes
have queries that constrain id and field1, you could build the index on
(id, field1, slow_function(field1)).  Either way, this is going to be
a bulky and rather special-purpose index, so the usefulness of doing this
is debatable.  Kevin's idea of an auto-maintained column in the base
table is probably better.

Even with the planner deficiencies rectified, an index on
slow_function(field1) alone would be totally useless for this query.
There's no way to magically find the index entry for a given row.
An index on (id, slow_function(field1)) could be useful, given a better
planner.

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] MinGW compiled client library

2013-08-16 Thread Michael Cronenworth 

On 08/16/2013 02:12 AM, Albe Laurenz wrote:

Yes, any patches should be posted to -hackers, in this case
with a archive reference to the discussion on -general.

Please readhttp://wiki.postgresql.org/wiki/Submitting_a_Patch

To make sure that the patch does not get lost, add it to
the next commitfest onhttps://commitfest.postgresql.org/
(this is not required for bugfixes, but helps).


Thanks for the help. Just submitted the patch.



--
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] Seemingly inconsistent ORDER BY behavior

2013-08-16 Thread BladeOfLight16 
On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane  wrote:

> Our interpretation is that a bare column name ("ORDER BY foo") is resolved
> first as an output-column label, or failing that as an input-column name.
> However, as soon as you embed a name in an expression, it will be treated
> *only* as an input column name.
>
> The SQL standard is not a lot of help here.  In SQL92, the only allowed
> forms of ORDER BY arguments were an output column name or an output column
> number.  SQL99 and later dropped that definition (acknowledging that they
> were being incompatible) and substituted some fairly impenetrable verbiage
> that seems to boil down to allowing input column names that can be within
> expressions.  At least that's how we've chosen to read it.  Our current
> behavior is a compromise that tries to support both editions of the spec.
>

Asking as a comparative know-nothing who would like to be more informed, is
there something wrong with the notion of throwing an error that m in the
ORDER BY clause is ambiguous here? As near as I can tell, it really is
ambiguous as long as both input or output columns are accepted, so either
way is essentially a total guess about what the user wants. It seems to me
that throwing an error would be the most intuitive and clearly defined way
of handling this case.

Re: [GENERAL] Seemingly inconsistent ORDER BY behavior

2013-08-16 Thread Scott Marlowe 
On Fri, Aug 16, 2013 at 8:37 PM, BladeOfLight16
 wrote:
> On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane  wrote:
>>
>> Our interpretation is that a bare column name ("ORDER BY foo") is resolved
>> first as an output-column label, or failing that as an input-column name.
>> However, as soon as you embed a name in an expression, it will be treated
>> *only* as an input column name.
>>
>> The SQL standard is not a lot of help here.  In SQL92, the only allowed
>> forms of ORDER BY arguments were an output column name or an output column
>> number.  SQL99 and later dropped that definition (acknowledging that they
>> were being incompatible) and substituted some fairly impenetrable verbiage
>> that seems to boil down to allowing input column names that can be within
>> expressions.  At least that's how we've chosen to read it.  Our current
>> behavior is a compromise that tries to support both editions of the spec.
>
>
> Asking as a comparative know-nothing who would like to be more informed, is
> there something wrong with the notion of throwing an error that m in the
> ORDER BY clause is ambiguous here? As near as I can tell, it really is
> ambiguous as long as both input or output columns are accepted, so either
> way is essentially a total guess about what the user wants. It seems to me
> that throwing an error would be the most intuitive and clearly defined way
> of handling this case.

Well it's not likely that the current behaviour will be changed since
there are likely apps that rely on it working (sort of) the way it is.

A warning or notice might make sense then.

-- 
To understand recursion, one must first understand recursion.


-- 
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] Commit problem in read-commited isolation level

2013-08-16 Thread S H 
> 1) What is your exact Postgres version i.e 8.1.5.?
V - 8.1.18
> 3) You also say it works fine in two environments, but not one.
> 
> What are the environments?
> OS and version, memory, Postgres versions, etc.

It is working on most of the production system ( more than 100) but failed once 
in one of the system ( not always).
> OS and version, memory, Postgres versions, etc.RHEL 5.2 , 32 GB , 8.1.18 
 >Current value in colname = 5;
> Update tablename set colname = 0 where key = 18;
> commit , in parallel to above queries ( either vacuum or reindex of 
> table was running)
> 
> After 10 sec following query is executed.
> select colname from tablename where key = 18 ;it is returning old value 
> i.e colname = 5.
> After another few seconds select colname from tablename where key = 18 
> ;it is returning new value i.e colname = 0.
> 
> You say at the top the new value is 0, but show 5 as the new value at 
> the bottom.
> 
Sorry it is returning new value ie 0 after some time. Corrected the above 
description.