date:20170627

thanks for the explanation, Gary.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Unable-to-understand-index-only-scan-as-it-is-not-happening-for-one-table-while-it-happens-for-other-tp5968835p5968976.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] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

2017-06-27 Thread Gary Evans

Hi Rajan,

I would say that the optimiser has calculated that it would be quicker to
sequentially read thought the table to get the 354 rows returned without
the limit.  By introducing the limit, it is much faster to pick out the
first 10 rows using the index.  Using an index is usually only quicker when
a small percentage of the table like 5% is going to be returned, when more
than 5-8% of the rows are to be returned the optimiser will generally go
for a sequential scan.

Cheers
Gary

On Wed, Jun 28, 2017 at 11:22 AM, rajan  wrote:

> Thanks.
>
> Now I did the same query, but it is going for *index-only scan* only after
> I
> put *limit*
>
> localdb=# explain analyse verbose select uid from mm where uid>100 order by
> uid;
>QUERY PLAN
> 
> -
>  Sort  (cost=30.99..31.87 rows=354 width=8) (actual time=0.243..0.294
> rows=354 loops=1)
>Output: uid
>Sort Key: mm.uid
>Sort Method: quicksort  Memory: 41kB
>->  Seq Scan on public.mm  (cost=0.00..16.00 rows=354 width=8) (actual
> time=0.010..0.123 rows=354 loops=1)
>  Execution time: 0.342 ms
> (10 rows)
>
> localdb=# explain analyse verbose select uid from mm where uid>100 order by
> uid *limit 10*;
>
>  QUERY
> PLAN
> 
> 
> 
>  Limit  (cost=0.27..2.13 rows=10 width=8) (actual time=0.026..0.037 rows=10
> loops=1)
>Output: uid
>->  Index Only Scan using mm_pkey on public.mm  (cost=0.27..65.91
> rows=354 width=8) (actual time=0.025..0.034 rows=10 loops=1)
>  Output: uid
>  Index Cond: (mm.uid > 100)
>  Heap Fetches: 10
>  Planning time: 0.096 ms
>  Execution time: 0.059 ms
> (8 rows)
>
>
>
> -
> --
> Thanks,
> Rajan.
> --
> View this message in context: http://www.postgresql-archive.
> org/Unable-to-understand-index-only-scan-as-it-is-not-
> happening-for-one-table-while-it-happens-for-other-tp5968835p5968971.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] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

Thanks.

Now I did the same query, but it is going for *index-only scan* only after I
put *limit*

localdb=# explain analyse verbose select uid from mm where uid>100 order by
uid;
   QUERY PLAN
-
 Sort  (cost=30.99..31.87 rows=354 width=8) (actual time=0.243..0.294
rows=354 loops=1)
   Output: uid
   Sort Key: mm.uid
   Sort Method: quicksort  Memory: 41kB
   ->  Seq Scan on public.mm  (cost=0.00..16.00 rows=354 width=8) (actual
time=0.010..0.123 rows=354 loops=1)
 Output: uid
 Filter: (mm.uid > 100)
 Rows Removed by Filter: 46
 Planning time: 0.109 ms
 Execution time: 0.342 ms
(10 rows)

localdb=# explain analyse verbose select uid from mm where uid>100 order by
uid *limit 10*;
   QUERY
PLAN

 Limit  (cost=0.27..2.13 rows=10 width=8) (actual time=0.026..0.037 rows=10
loops=1)
   Output: uid
   ->  Index Only Scan using mm_pkey on public.mm  (cost=0.27..65.91
rows=354 width=8) (actual time=0.025..0.034 rows=10 loops=1)
 Output: uid
 Index Cond: (mm.uid > 100)
 Heap Fetches: 10
 Planning time: 0.096 ms
 Execution time: 0.059 ms
(8 rows)



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Unable-to-understand-index-only-scan-as-it-is-not-happening-for-one-table-while-it-happens-for-other-tp5968835p5968971.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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread PT

On Tue, 27 Jun 2017 16:16:53 -0700 (MST)
DrakoRod  wrote:

> Yep, the real problem was all connections are used up. A ps command showed
> this:
> 
> postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
> dbsomething 8.8.8.1[34024] PARSE waiting
> postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
> dbsomething 8.8.8.2[49193] PARSE waiting
> postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
> dbsomething 8.8.8.1[34209] PARSE waiting
> postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
> dbsomething 8.8.8.1[34244] PARSE waiting
> postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
> dbsomething 8.8.8.3[38324] PARSE waiting
> postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
> dbsomething 8.8.8.3[38359] PARSE waiting
> postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
> dbsomething 8.8.8.2[49994] PARSE waiting
> postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
> dbsomething 8.8.8.4[58916] PARSE waiting
> postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
> dbsomething 8.8.8.3[38496] PARSE waiting
> postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
> dbsomething 8.8.8.2[50088] idle in transaction
> postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
> dbsomething 8.8.8.4[58999] PARSE waiting
> postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
> dbsomething 8.8.8.5[57944] PARSE waiting
> postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
> dbsomething 8.8.8.2[50263] PARSE waiting
> postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
> dbsomething 8.8.8.4[59158] PARSE waiting
> postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
> dbsomething 8.8.8.4[59161] PARSE waiting
> postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.4[59163] PARSE waiting
> postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
> dbsomething 8.8.8.1[34469] PARSE waiting
> postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.3[38729] PARSE waiting
> postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.2[50352] PARSE waiting
> postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.2[50353] PARSE waiting
> postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
> dbsomething 8.8.8.3[38750] PARSE waiting
> postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
> dbsomething 8.8.8.4[59238] PARSE waiting
> postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
> dbsomething 8.8.8.4[59242] PARSE waiting
> postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
> dbsomething 8.8.8.2[50439] PARSE waiting
> postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
> dbsomething 8.8.8.3[38833] PARSE waiting
> postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
> dbsomething 8.8.8.3[38834] PARSE waiting
> postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
> dbsomething 8.8.8.4[59318] PARSE waiting
> postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
> dbsomething 8.8.8.4[59323] PARSE waiting
> postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
> dbsomething 8.8.8.4[59328] PARSE waiting
> 
> 
> When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
> active and most were SELECTs, then the server did not open new connections.
> I canceled many queries (only SELECTs) and server back to normal.
> 
> I understand that the principal problem probably are the application, of
> that I'm sure, but in the process debug. The best way to avoid or "fix" this
> are with connections pool like pgbouncer? How is the most secure way to
> return connections without restart service?

There are various timeout settings that can be configured:
https://www.postgresql.org/docs/9.6/static/runtime-config-client.html
idle_in_transation_session_timeout is probably the one you want to
enable.

It's likely that your application developers will start to complain about
database "errors" once you enable that, as connections will get killed and
cause errors on the application. You'll need to work to educate your
developers on how to fix their application so the situation stops happening.

-- 
PT 


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On 06/27/2017 04:16 PM, DrakoRod wrote:

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres 1172 23340 1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres 1527 23340 3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting

Hmm, the above is new one to me. Some searching found this:

https://www.postgresql.org/message-id/1282602153-sup-6272%40alvh.no-ip.org

"It means the parse phase is waiting for a lock. You can see exactly
what it's waiting for by looking at pg_locks "WHERE NOT GRANTED"."

If you have not already, you might want to log
connections/disconnections for more insight:

https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?

Close the connection.

I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.

FYI there is a difference between 'idle' connections and 'idle in
transaction', not sure which one you are referring to. See below for
more info:

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

The bottom line is connections are resources that need to be managed. To
better able to do that is going to require some detective work to
determine what is generating the connections and for what purpose.

Thanks for your help!

-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions

--
Adrian Klaver
adrian.kla...@aklaver.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] Which process is actually doing the WAL writes/calls XLogFlush?

On Tue, Jun 27, 2017 at 3:52 PM, Adrian Klaver 
wrote:

> On 06/27/2017 11:19 AM, Daniel Westermann wrote:
> >
> > Thanks, Adrian
> > It is clear now for the asynchronous stuff and wal_writer.
> > But I still did not figure out (or I am just not able to understand it
> from the README linked above)
> > which process is actually doing the write to the wal when you have
> synchronous commit set to on. Can
> > someone put some light on this?
>
> AFAIK the wal writer process.
>

Um, no.  "Synchronous" means that the caller has to wait for the result to
appear before it can move on.  "Asynchronous" means that the caller can
issue the instruction and immediately move on.  I guessing here but while
usually the caller would have to provide callback hook to get the answer in
the future in this case the caller is assuming a positive result and
doesn't listen for a response.  It is for the asynchronous mode that
wal_writer exists.  In synchronous mode it would be somewhat inefficient to
hand-off/leave the work to a separate process to perform while the main
process remains idle - better to just have the main process do it.  Its not
a total win since the WAL file takes on the inherent contention.

The linked readme (and I suspect much of the docs) was written under the
assumption that the calling session performs all work not otherwise
explicitly designated as being handled by a separate process.  That is why
you cannot find an affirmative answer to the posed question - it is taken
as something having been previously learned (or deduced in my case - the
others links being illustrative too).

Now, I'm still just going off of human documentation and not the actual
code - but my confidence level is quite high.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread DrakoRod

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting
postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
dbsomething 8.8.8.1[34209] PARSE waiting
postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
dbsomething 8.8.8.1[34244] PARSE waiting
postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
dbsomething 8.8.8.3[38324] PARSE waiting
postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
dbsomething 8.8.8.3[38359] PARSE waiting
postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
dbsomething 8.8.8.2[49994] PARSE waiting
postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
dbsomething 8.8.8.4[58916] PARSE waiting
postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
dbsomething 8.8.8.3[38496] PARSE waiting
postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
dbsomething 8.8.8.2[50088] idle in transaction
postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
dbsomething 8.8.8.4[58999] PARSE waiting
postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
dbsomething 8.8.8.5[57944] PARSE waiting
postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
dbsomething 8.8.8.2[50263] PARSE waiting
postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
dbsomething 8.8.8.4[59158] PARSE waiting
postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
dbsomething 8.8.8.4[59161] PARSE waiting
postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.4[59163] PARSE waiting
postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
dbsomething 8.8.8.1[34469] PARSE waiting
postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.3[38729] PARSE waiting
postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.2[50352] PARSE waiting
postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.2[50353] PARSE waiting
postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
dbsomething 8.8.8.3[38750] PARSE waiting
postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
dbsomething 8.8.8.4[59238] PARSE waiting
postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
dbsomething 8.8.8.4[59242] PARSE waiting
postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
dbsomething 8.8.8.2[50439] PARSE waiting
postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
dbsomething 8.8.8.3[38833] PARSE waiting
postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
dbsomething 8.8.8.3[38834] PARSE waiting
postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
dbsomething 8.8.8.4[59318] PARSE waiting
postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
dbsomething 8.8.8.4[59323] PARSE waiting
postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
dbsomething 8.8.8.4[59328] PARSE waiting


When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?

I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.

Thanks for your help! 




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5968960.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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On Tue, Jun 27, 2017 at 3:41 PM, Melvin Davidson 
wrote:

> *His problem is NOT 'idle in transaction' per se. It is all connections
> are used up.*
> *Hence the need for pg_bouncer for connection pooling.*
>
>
Whether pg_bouncer provides a viable solution is just as big an unknown as
whether "idle in transaction" is the biggest contributor to the problem.
If all of them are idle in transaction then pg_bouncer is powerless to
help.  If they are generally just long-lived sessions and only a few stay
in transaction then transaction pooling mode may help.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting


On 06/27/2017 03:41 PM, Melvin Davidson wrote:



On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 


*His problem is NOT 'idle in transaction' per se. It is all connections 
are used up.*


Not following. The 'idle in transaction' queries are coming in through a 
connection so having them around is keeping a connection open. Dealing 
with them will help with the connection count. Using pg_bouncer is an 
option, but if you have a process that is not properly closing 
transactions/connections you could get into an arms race between that 
process and the size of your connection pool.



*Hence the need for pg_bouncer for connection pooling.*
--
*Melvin Davidson*




--
Adrian Klaver
adrian.kla...@aklaver.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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread PT

On Tue, 27 Jun 2017 18:41:25 -0400
Melvin Davidson  wrote:

> On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
> wrote:
> 
> > On 06/27/2017 01:10 PM, DrakoRod wrote:
> >
> >> Hi folks.
> >>
> >> Today I had a problem with production's database PostgreSQL version
> >> 9.4.4.9.
> >> The server have max_connections set to 200, but today I reviewed
> >> pg_stat_activity and saw 199 active connections, obviously the server
> >> rejected any new connection and the production stopped.
> >>
> >> I saw another posts with a similar problems, but this was because the
> >> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> >> problems.
> >>
> >> I just canceled some SELECTs querys and the server returned to normality.
> >> Now a monitoring activity of server and I can see some backends like this:
> >>
> >> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> >> 8.8.8.8[37082] idle in transaction
> >> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> >> 8.8.8.8[54286] idle in transaction
> >>
> >> Any suggestions?
> >>
> >
> > https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> > html#PG-STAT-ACTIVITY-VIEW
> >
> > SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
> >
> > To get more detail on what is holding these transactions open.
> >
> 
> *His problem is NOT 'idle in transaction' per se. It is all connections are
> used up.*
> *Hence the need for pg_bouncer for connection pooling.*

That assessment lacks enough information to be substantiated.

One of the things I've frequently seen happen is applications written with
ORMs will create a new connection because the existing connections in the
pool are already in a transaction. If his application is not properly
committing transactions, an additional pooler layer will not improve on
the problem. Hence, what he needs to do first is gather more information and
understand exactly what's going on.

Of course, if usage has just scaled up to the point where he doesn't have
any free connections, then your assessment might be correct. But he hasn't
provided enough information to be sure of that.

Regardless, lots of "idle in transaction" connections that stick around a
long time is a clear sign of application bugs. If they're not the cause
of his immediate problem, they will be the cause of problems at some point,
so he might as well track them down and fix them.

-- 
PT 

-- 
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] Which process is actually doing the WAL writes/calls XLogFlush?

On 06/27/2017 11:19 AM, Daniel Westermann wrote:
>>On 06/23/2017 05:50 AM, Daniel Westermann wrote:
>>> Hi all,

> 
> Thanks, Adrian
> It is clear now for the asynchronous stuff and wal_writer.
> But I still did not figure out (or I am just not able to understand it from 
> the README linked above)
> which process is actually doing the write to the wal when you have 
> synchronous commit set to on. Can
> someone put some light on this?

ps axjf

/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
 \_ postgres: logger process   
 \_ postgres: checkpointer process   
 \_ postgres: writer process   
 \_ postgres: wal writer process   
 \_ postgres: autovacuum launcher process   
 \_ postgres: stats collector process   
 \_ postgres: bgworker: pglogical supervisor   

AFAIK the wal writer process.

> 
> Thanks
> Daniel
> 
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread Melvin Davidson

On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
wrote:

> On 06/27/2017 01:10 PM, DrakoRod wrote:
>
>> Hi folks.
>>
>> Today I had a problem with production's database PostgreSQL version
>> 9.4.4.9.
>> The server have max_connections set to 200, but today I reviewed
>> pg_stat_activity and saw 199 active connections, obviously the server
>> rejected any new connection and the production stopped.
>>
>> I saw another posts with a similar problems, but this was because the
>> pg_xlog was full or disk does'nt write, but the directory and disk  had no
>> problems.
>>
>> I just canceled some SELECTs querys and the server returned to normality.
>> Now a monitoring activity of server and I can see some backends like this:
>>
>> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
>> 8.8.8.8[37082] idle in transaction
>> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
>> 8.8.8.8[54286] idle in transaction
>>
>> Any suggestions?
>>
>
> https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> html#PG-STAT-ACTIVITY-VIEW
>
> SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
>
> To get more detail on what is holding these transactions open.
>
>
>>
>>
>> -
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*His problem is NOT 'idle in transaction' per se. It is all connections are
used up.*
*Hence the need for pg_bouncer for connection pooling.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On 06/27/2017 01:10 PM, DrakoRod wrote:

Hi folks.

Today I had a problem with production's database PostgreSQL version 9.4.4.9.
The server have max_connections set to 200, but today I reviewed
pg_stat_activity and saw 199 active connections, obviously the server
rejected any new connection and the production stopped.

I saw another posts with a similar problems, but this was because the
pg_xlog was full or disk does'nt write, but the directory and disk had no
problems.

I just canceled some SELECTs querys and the server returned to normality.
Now a monitoring activity of server and I can see some backends like this:

postgres 9737 23340 2 14:55 ?00:00:15 postgres: dbname user
8.8.8.8[37082] idle in transaction
postgres 9741 23340 9 14:55 ?00:00:47 postgres: dbname user
8.8.8.8[54286] idle in transaction

Any suggestions?

https://www.postgresql.org/docs/9.4/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';

To get more detail on what is holding these transactions open.

-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context:
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.kla...@aklaver.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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On Tue, Jun 27, 2017 at 1:10 PM, DrakoRod  wrote:

> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>

There is a serious lack of information provided here but "idle in
transaction" sessions are generally problematic (in particular they
continue to hold locks) and can only be fixed at the source - by fixing
code or user behavior.

David J.

Re: [GENERAL] insert on conflict

2017-06-27 Thread armand pirvu





> On Jun 27, 2017, at 3:30 PM, Peter Geoghegan  wrote:
> 
> On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu  wrote:
>> so how is it working in fact ? Isn't it working like looping in the
>> IVEE.dim_company  and for each company_id if the record does have a
>> correspondent in csischema.dim_company then update csischema.dim_company set
>> company_name = EXCLUDED.company_name  where company_id=...  ? If so isn't it
>> supposed to use the PK for each company_id ? Or is it more like building a
>> whole list from IVEE.dim_company and treat like a join ? Just trying to
>> understand
> 
> The processing here has to happen a tuple at a time. That's because
> the index structure itself is the only authoritative source of truth
> about whether or not there is a possible conflict. An MVCC snapshot
> isn't good enough, because it sees a consistent view of things, not
> the true physical reality of what exists or does not exist in the
> index.
> 
> What you end up with here is a weird nested loop join, you might say.
> The implementation couldn't do it any other way (this could never
> behave more like a merge join), because we must eagerly check for
> conflicts right as we insert (our insert would be registered by
> *other* inserters/upserters as a conflict). If the implementation did
> ever do it that way, it would break the important UPSERT guarantees
> around concurrency.
> 
> MERGE does this in other systems, which is okay for those other
> systems because MERGE makes no special promises about concurrency
> (e.g., you can get a unique violation in the joined-on column with
> MERGE). But, MERGE would be faster for bulk loading, which is what
> MERGE is good for.
> 
> -- 
> Peter Geoghegan

Hi Peter


So for example if IVEE.dim_company has 10k rows and csischema.dim_company has 
40 rows, what will happen for each row in IVEE.dim_company we check 
csischema.dim_company and if the check tells row is in it switches to update 
and this would mean yes I scan IVEE.dim_company, however should an update be 
needed in csischema.dim_company it will use the csischema.dim_company PK since 
we pass one value gotten from IVEE.dim_company
The question I guess is what happens IF I IVEE.dim_company accounts for far 
more than 5% of csischema.dim_company ? Will that translate into a scan on 
csischema.dim_company ?

What I am looking at now looks like a potential racing contention which so I am 
wondering if there are better ways to do it


Thanks
Armand

Re: [GENERAL] insert on conflict

2017-06-27 Thread Peter Geoghegan

On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu  wrote:
> so how is it working in fact ? Isn't it working like looping in the
> IVEE.dim_company  and for each company_id if the record does have a
> correspondent in csischema.dim_company then update csischema.dim_company set
> company_name = EXCLUDED.company_name  where company_id=...  ? If so isn't it
> supposed to use the PK for each company_id ? Or is it more like building a
> whole list from IVEE.dim_company and treat like a join ? Just trying to
> understand

The processing here has to happen a tuple at a time. That's because
the index structure itself is the only authoritative source of truth
about whether or not there is a possible conflict. An MVCC snapshot
isn't good enough, because it sees a consistent view of things, not
the true physical reality of what exists or does not exist in the
index.

What you end up with here is a weird nested loop join, you might say.
The implementation couldn't do it any other way (this could never
behave more like a merge join), because we must eagerly check for
conflicts right as we insert (our insert would be registered by
*other* inserters/upserters as a conflict). If the implementation did
ever do it that way, it would break the important UPSERT guarantees
around concurrency.

MERGE does this in other systems, which is okay for those other
systems because MERGE makes no special promises about concurrency
(e.g., you can get a unique violation in the joined-on column with
MERGE). But, MERGE would be faster for bulk loading, which is what
MERGE is good for.

-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread Melvin Davidson

On Tue, Jun 27, 2017 at 4:10 PM, DrakoRod  wrote:

> Hi folks.
>
> Today I had a problem with production's database PostgreSQL version
> 9.4.4.9.
> The server have max_connections set to 200, but today I reviewed
> pg_stat_activity and saw 199 active connections, obviously the server
> rejected any new connection and the production stopped.
>
> I saw another posts with a similar problems, but this was because the
> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> problems.
>
> I just canceled some SELECTs querys and the server returned to normality.
> Now a monitoring activity of server and I can see some backends like this:
>
> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> 8.8.8.8[37082] idle in transaction
> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>
>
>
> -
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions
> --
> View this message in context: http://www.postgresql-archive.
> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.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
>


*Are you asking how to track down the user using up all the connection?
With the information you provided that cannot. be down.*



*If you are asking how to prevent problems in the future, then install
Pg_Bouncer and use that to pool connections.https://pgbouncer.github.io/
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

2017-06-27 Thread DrakoRod

Hi folks.

Today I had a problem with production's database PostgreSQL version 9.4.4.9.
The server have max_connections set to 200, but today I reviewed
pg_stat_activity and saw 199 active connections, obviously the server
rejected any new connection and the production stopped.

I saw another posts with a similar problems, but this was because the
pg_xlog was full or disk does'nt write, but the directory and disk  had no
problems.

I just canceled some SELECTs querys and the server returned to normality.
Now a monitoring activity of server and I can see some backends like this: 

postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
8.8.8.8[37082] idle in transaction
postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
8.8.8.8[54286] idle in transaction

Any suggestions? 



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.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] insert on conflict

2017-06-27 Thread armand pirvu

Hi 

Got question 

birstdb=# \d csischema.dim_company
   Table "csischema.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | not null
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 
Indexes:
"dim_company_pkey" PRIMARY KEY, btree (company_id)

birstdb=# \d IVEE.dim_company
 Table "ivee.dim_company"
 Column  |Type | Modifiers 
-+-+---
 company_id  | integer | 
 company_name| character varying(100)  | 
 city| character varying(100)  | 
 state   | character varying(100)  | 
 postal_code | character varying(100)  | 
 country | character varying(100)  | 
 latitude| double precision| 
 longitude   | double precision| 
 update_datetime | timestamp without time zone | 
 company_source  | character varying(1)| 




insert into csischema.dim_company select * from IVEE.dim_company on conflict 
(company_id) do update 
SET  
company_name = EXCLUDED.company_name , 
city = EXCLUDED.city ,  
state = EXCLUDED.state ,
postal_code = EXCLUDED.postal_code ,
country = EXCLUDED.country , 
latitude = EXCLUDED.latitude ,
longitude = EXCLUDED.longitude ,
update_datetime = EXCLUDED.update_datetime ,
company_source = EXCLUDED.company_source; 
  QUERY PLAN
   
---
 Insert on dim_company  (cost=0.00..188.32 rows=1232 width=1126)
   Conflict Resolution: UPDATE
   Conflict Arbiter Indexes: dim_company_pkey
   ->  Seq Scan on dim_company dim_company_1  (cost=0.00..188.32 rows=1232 
width=1126)
(4 rows)


so how is it working in fact ? Isn't it working like looping in the 
IVEE.dim_company  and for each company_id if the record does have a 
correspondent in csischema.dim_company then update csischema.dim_company set 
company_name = EXCLUDED.company_name  where company_id=...  ? If so isn't it 
supposed to use the PK for each company_id ? Or is it more like building a 
whole list from IVEE.dim_company and treat like a join ? Just trying to 
understand

Thanks
Armand

Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?

On Tuesday, June 27, 2017, Daniel Westermann <
daniel.westerm...@dbi-services.com> wrote:

> >On 06/23/2017 05:50 AM, Daniel Westermann wrote:
>
> But I still did not figure out (or I am just not able to understand it from 
> the README linked above)
> which process is actually doing the write to the wal when you have 
> synchronous commit set to on. Can
> someone put some light on this?
>
> The one in which the "commit" command was executed.  i.e., user session
processes.

David J.

Re: [GENERAL] Which process is actually doing the WAL writes/calls XLogFlush?

2017-06-27 Thread Daniel Westermann

>On 06/23/2017 05:50 AM, Daniel Westermann wrote:
>> Hi all,
>> 
>> as I did not find the answer in the documentation: Which background 
>> process is actually doing the writes/flushes to the WAL? In the docs 
>> ( https://www.postgresql.org/docs/10/static/wal-configuration.html ) it is 
>> explained which internal functions are responsible for this: 
>> XLogInsertRecord and XLogFlush but who does call them, especially the 
>> flush? Is it the process for the user connection itself then which calls 
>> this after commit?
>
>For all the details see here:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/transam/README;h=4ae4715339e707ab5ed879a628aa96b73002ef43;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7
>  >
>> 
>> Initially I thought it is the job of the wal_writer but according to 
>> this blog post 
>> ( 
>> http://www.cybertec.at/postgresql-writer-and-wal-writer-processes-explained/ 
>> ) 
>> this seems not to be true, right? Why do I need the wal_writer at all 
>> then when synchronous_commit is set to something else than off?
>
>See here:
>
> https://www.postgresql.org/docs/9.6/static/wal-async-commit.html 
Thanks, Adrian 
It is clear now for the asynchronous stuff and wal_writer. 
But I still did not figure out (or I am just not able to understand it from the 
README linked above) 
which process is actually doing the write to the wal when you have synchronous 
commit set to on. Can 
someone put some light on this? 

Thanks 
Daniel

Re: [GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

2017-06-27 Thread Albe Laurenz

rajan wrote:
> why the index-only scan *works only* with an *order by*?
> localdb=# explain analyse verbose select uid from mm where uid>100 *order
> by* uid;
> QUERY
> PLAN
> 
>  Index Only Scan using mm_pkey on public.mm  (cost=0.27..22.47 rows=354 
> width=8)
>  (actual time=0.023..0.079 rows=354 
> loops=1)
>Output: uid
>Index Cond: (mm.uid > 100)
>Heap Fetches: 0
>  Planning time: 0.096 ms
>  Execution time: 0.131 ms
> (6 rows)

I'd guess that it would work fine, but PostgreSQL chooses to use a sequential
scan instead, because too many rows meet the condition "uid > 100".

If you add the ORDER BY, the plan with the sequential scan also has to
sort the data, which makes it much more expensive, while the index only scan
returns the data in sorted order anyway and does not have to sort,
which makes it cheaper.

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] Config for fast huge cascaded updates

2017-06-27 Thread Andrew Sullivan

On Mon, Jun 26, 2017 at 07:26:08PM -0700, Joshua D. Drake wrote:

> Alternatively, and ONLY do this if you take a backup right before hand, you
> can set the table unlogged, make the changes and assuming success, make the
> table logged again. That will great increase the write speed and reduce wal
> segment churn.

Note that this is not for just that table, but for all of the
implicated ones because of the CASCADE statements.  It sounds like the
OP is basically rewriting a significant chunk of the entire database,
so nothing is going to be super fast: all those CASCADEs have to fire
and all those other tables need to be updated too.

> However, if that fails, the table is dead. You will have to reload it from
> backup.

Right, and that goes for all the affected tables.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Accessing DB2 tables from postgresql


On 06/27/2017 03:11 AM, Swapnil Vaze wrote:

Hello,

I am trying to access few table present in DB2 LUW from postgres9.5 
database.


I have installed unixODBC driver and connection to DB2 is working fine.
I have installed CartoDB/odbc_fdw foreign data wrappers.
I have user below commands to create foreign table:
$ create extension odbc_fdw;
$ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 
options(odbc_UID '', odbc_PWD '');
$ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD 
VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database 
'TESTV9', schema 'schema1', table 'table1' );


All commands work fine, however when I try to select data from table it 
throws error:

$ select * from odbc_table;
ERROR:  Executing ODBC query

Can anyone help me here?


Might want to take a look at:

https://github.com/CartoDB/odbc_fdw

From  the examples above you need to prefix some settings with odbc_. 
In your example that would be odbc_database 'TESTV9' instead of database 
'TESTV9'.



How can I access DB2 LUW or zOS database tables from postgres?

--
Thanks & Regards,
Swapnil Vaze



--
Adrian Klaver
adrian.kla...@aklaver.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] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

Ah! Got it. Thanks. One more question,

why the index-only scan *works only* with an *order by*?
localdb=# explain analyse verbose select uid from mm where uid>100 *order
by* uid;
QUERY
PLAN
---
 Index Only Scan using mm_pkey on public.mm  (cost=0.27..22.47 rows=354
width=8) (actual time=0.023..0.079 rows=354 loops=1)
   Output: uid
   Index Cond: (mm.uid > 100)
   Heap Fetches: 0
 Planning time: 0.096 ms
 Execution time: 0.131 ms
(6 rows)



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Unable-to-understand-index-only-scan-as-it-is-not-happening-for-one-table-while-it-happens-for-other-tp5968835p5968844.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] Unable to understand index only scan as it is not happening for one table while it happens for other

2017-06-27 Thread Chris Travers

On Tue, Jun 27, 2017 at 1:09 PM, rajan  wrote:

> Please help me to understand the following,
>
> *For the following query Index Only Scan to be performance as I am querying
> the indexed fields alone. There are 20 fields in this table, inclusive of
> the three I am selecting.*
> localdb=# explain analyse verbose select uid, guid from mm where uid=100
> limit 1;
>QUERY
> PLAN
> 
> 
> 
>  Limit  (cost=0.27..8.29 rows=1 width=45) (actual time=0.017..0.017 rows=1
> loops=1)
>Output: uid, guid
>->  Index Scan using mm_pkey on public.mm  (cost=0.27..8.29 rows=1
> width=45) (actual time=0.016..0.016 rows=1 loops=1)
>  Output: uid, guid
>  Index Cond: (mm.uid = 100)
>  Planning time: 0.149 ms
>  Execution time: 0.042 ms
> (7 rows)
>
> *Index only scan runs for the following*
> localdb=# explain analyze verbose select id, z from test order by id limit
> 20;
> QUERY PLAN
> 
> ---
>  Limit  (cost=0.28..10.29 rows=20 width=17) (actual time=0.017..0.027
> rows=20 loops=1)
>Output: id, z
>->  Index Only Scan using zx on public.test  (cost=0.28..1502.47
> rows=3000 width=17) (actual time=0.016..0.024 rows=20 loops=1)
>  Output: id, z
>  Heap Fetches: 20
>  Planning time: 0.080 ms
>  Execution time: 0.050 ms
> (7 rows)
>

First guess would be that all the information needed from the index alone.
In other words for an index only scan to work all fields retrieved have to
be accessible from the same index.

>
>
>
>
>
> -
> --
> Thanks,
> Rajan.
> --
> View this message in context: http://www.postgresql-archive.
> org/Unable-to-understand-index-only-scan-as-it-is-not-
> happening-for-one-table-while-it-happens-for-other-tp5968835.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
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Accessing DB2 tables from postgresql

2017-06-27 Thread Achilleas Mantzios


On 27/06/2017 13:11, Swapnil Vaze wrote:

Hello,

I am trying to access few table present in DB2 LUW from postgres9.5 database.

I have installed unixODBC driver and connection to DB2 is working fine.
I have installed CartoDB/odbc_fdw foreign data wrappers.
I have user below commands to create foreign table:
$ create extension odbc_fdw;
$ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 options(odbc_UID 
'', odbc_PWD '');
$ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD 
VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database 
'TESTV9', schema 'schema1', table 'table1' );



You have omitted the CREATE SERVER command. Can you query the mainframe using 
isql ?


All commands work fine, however when I try to select data from table it throws 
error:
$ select * from odbc_table;
ERROR:  Executing ODBC query

Can anyone help me here?
How can I access DB2 LUW or zOS database tables from postgres?


I used to work with MVS many years ago. Good luck with your project.
For better diagnosis open all logs in both machines (postgresql, odbc, MVS, 
DB2) and have a detailed view on them.



--
Thanks & Regards,
Swapnil Vaze



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

[GENERAL] Unable to understand index only scan as it is not happening for one table while it happens for other