Re: [GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Monday, March 21, 2016, Tom Lane  wrote:

> "David G. Johnston" > writes:
> > On Monday, March 21, 2016, Tom Lane >
> wrote:
> >> What about just discarding the old format entirely, and printing one of
> >> these two things:
> >>
> >> Timestamp (every Ns)
> >>
> >> User Given Title  Timestamp (every Ns)
>
> > This works for me.
>
> If I don't hear objections PDQ, I'm going to update the docs and commit
> it like that.
>
>
Saw it go in.  Thank You.

David J.

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

[Michael Paquier]

On Tue, Mar 22, 2016 at 6:25 AM, Tom Lane  wrote:
> If I don't hear objections PDQ, I'm going to update the docs and commit
> it like that.

Thanks!
-- 
Michael


-- 
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] Slow GIN indexes after bulk insert

[Jeff Janes]

On Mon, Mar 21, 2016 at 10:55 AM, Chris Spencer  wrote:
> I have 5 GIN indexes on a PG 9.3 table containing about 50 million records.
> Each index covers a "group" of common records like billing address, shipping
> address, contact names, etc.
>
> When first created, the indexes works miracles in speeding up the full text
> search of these fields. However, I'm running into some serious maintenance
> headaches.
>
> After I insert a few thousand new records, the indexes seem to have no
> effect. A query that might normally take 1 second now takes 5 minutes.

I'd try turning fastupdate off on the indexes.  It will probably slow
down your bulk inserts, (perhaps by a lot, but you won't know until
you try).  But if you can handle that, it will make your slow select
problems go away like magic.

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: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Alvaro Herrera]

Tom Lane wrote:
> "David G. Johnston"  writes:
> > On Monday, March 21, 2016, Tom Lane  wrote:
> >> What about just discarding the old format entirely, and printing one of
> >> these two things:
> >> 
> >> Timestamp (every Ns)
> >> 
> >> User Given Title  Timestamp (every Ns)
> 
> > This works for me.
> 
> If I don't hear objections PDQ, I'm going to update the docs and commit
> it like that.

It works for me too.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Request - repeat value of \pset title during \watch interations

[Tom Lane]

"David G. Johnston"  writes:
> On Monday, March 21, 2016, Tom Lane  wrote:
>> What about just discarding the old format entirely, and printing one of
>> these two things:
>> 
>> Timestamp (every Ns)
>> 
>> User Given Title  Timestamp (every Ns)

> This works for me.

If I don't hear objections PDQ, I'm going to update the docs and commit
it like that.

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] Slow GIN indexes after bulk insert

[Tom Lane]

Chris Spencer  writes:
> What constitutes a "large" work_mem? My server has 61GB of memory and my
> work_mem is currently set to include all of that.

Ouch.  That's a mistake independently of GIN.  The primary usage of
work_mem is to define how much memory an individual sorting or hashing
query step is allowed to use.  A complex query might have several sort or
hash steps, and then you need to worry about concurrent queries in
different sessions; not to mention that this is not the only demand on
your server's RAM.  I'd be hesitant to set work_mem much above 1GB, maybe
even quite a bit less than that depending on what your workload is like.

Cutting work_mem to ~100MB might alone be enough to fix your GIN issue;
if not you could experiment with forced flushes of the GIN pending lists
via VACUUM (or ANALYZE might do it too, and be more directly useful).

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] Slow GIN indexes after bulk insert

[Chris Spencer]

Thanks for the tips.

What constitutes a "large" work_mem? My server has 61GB of memory and my
work_mem is currently set to include all of that. What percent of my total
memory should I lower that to so it won't impact GIN updates?

On Mon, Mar 21, 2016 at 2:27 PM, Tom Lane  wrote:
>
> Chris Spencer  writes:
> > I have 5 GIN indexes on a PG 9.3 table containing about 50 million
records.
> > Each index covers a "group" of common records like billing address,
> > shipping address, contact names, etc.
>
> > When first created, the indexes works miracles in speeding up the full
text
> > search of these fields. However, I'm running into some serious
maintenance
> > headaches.
>
> > After I insert a few thousand new records, the indexes seem to have no
> > effect. A query that might normally take 1 second now takes 5 minutes.
>
> Probably the problem is that most of the new index entries are still
> sitting in the index's pending list and haven't been merged to the main
> search tree.  Try VACUUM'ing the table after a bulk insert.  (I think
> there are other ways of triggering a GIN pending-list cleanup, but
> I don't recall which ones were available in 9.3.)
>
> Worthy of note here is that the max pending list size is governed by
> work_mem, so a large work_mem can make this effect worse.  (We got
> smart and made it an independent setting, but not till 9.5.)
>
> regards, tom lane

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Monday, March 21, 2016, Tom Lane  wrote:

> "David G. Johnston" > writes:
> > I'll admit it's awkward because it's abbreviated but if someone enters
> > \watch 5 and then sees (5s) in the title I think they can put two and two
> > together.
>
> Where I find this to be awkward is that the format is randomly different
> between the user-title and no-user-title cases.
>
> What about just discarding the old format entirely, and printing one of
> these two things:
>
> Timestamp (every Ns)
>
> User Given Title  Timestamp (every Ns)
>
>
This works for me.

David J.

Re: [GENERAL] [HACKERS] Request - repeat value of \pset title during \watch interations

[Tom Lane]

Alvaro Herrera  writes:
> (I'll also use this opportunity to complain again about not being able
> to use floating point sleep time.)

That's not unreasonable either, though it seems like material for a
separate patch.

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] [HACKERS] Request - repeat value of \pset title during \watch interations

[Tom Lane]

"David G. Johnston"  writes:
> I'll admit it's awkward because it's abbreviated but if someone enters
> \watch 5 and then sees (5s) in the title I think they can put two and two
> together.

Where I find this to be awkward is that the format is randomly different
between the user-title and no-user-title cases.

What about just discarding the old format entirely, and printing one of
these two things:

Timestamp (every Ns)

User Given Title  Timestamp (every Ns)

> If the watched query takes a long to run, or there is a disruption, knowing
> when the last one ran and how often it is supposed to run is useful info to
> have at ones fingertips.

That's not unreasonable.  I just want it to look less weirdly different
between the two cases.

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

[GENERAL] Re: [HACKERS] Request - repeat value of \pset title during \watch interations

[Alvaro Herrera]

David G. Johnston wrote:

> Tom doesn't care enough to veto and you don't really care...
> 
> I'll admit it's awkward because it's abbreviated but if someone enters
> \watch 5 and then sees (5s) in the title I think they can put two and two
> together.
> 
> If the watched query takes a long to run, or there is a disruption, knowing
> when the last one ran and how often it is supposed to run is useful info to
> have at ones fingertips.  I have done this myself occasionally so I'm not
> speaking from theory.  But I won't complain if its removed.

I like David's UI better FWIW.

(I'll also use this opportunity to complain again about not being able
to use floating point sleep time.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Monday, March 21, 2016, Robert Haas  wrote:

> On Mon, Mar 21, 2016 at 2:09 PM, David G. Johnston
> > wrote:
> > On Monday, March 21, 2016, Tom Lane >
> wrote:
> >> "David G. Johnston" > writes:
> >> > I'd rather not omit sleep but removing "Watch every" is fine
> (preferred
> >> > actually), so:
> >> > Title Is Here Mon Mar 21 15:05:06 2016 (5s)
> >>
> >> Meh ... seems a bit awkward to me.  Couldn't you include " (5s)" in the
> >> title, if you want that info?  If it's variable, you could still
> >> accommodate that:
> >
> > Actually, only if it's a variable that you setup and repeat and you
> show.  A
> > bit cumbersome and mixes the parts that are title and those that are
> present
> > only because you are watching.
>
> Ah, come on.  This doesn't really seem like an issue we should spend
> more time quibbling about.  I think Tom's version is fine.
>
>
Tom doesn't care enough to veto and you don't really care...

I'll admit it's awkward because it's abbreviated but if someone enters
\watch 5 and then sees (5s) in the title I think they can put two and two
together.

If the watched query takes a long to run, or there is a disruption, knowing
when the last one ran and how often it is supposed to run is useful info to
have at ones fingertips.  I have done this myself occasionally so I'm not
speaking from theory.  But I won't complain if its removed.

David J.

Re: [GENERAL] Slow GIN indexes after bulk insert

[Tom Lane]

Chris Spencer  writes:
> I have 5 GIN indexes on a PG 9.3 table containing about 50 million records.
> Each index covers a "group" of common records like billing address,
> shipping address, contact names, etc.

> When first created, the indexes works miracles in speeding up the full text
> search of these fields. However, I'm running into some serious maintenance
> headaches.

> After I insert a few thousand new records, the indexes seem to have no
> effect. A query that might normally take 1 second now takes 5 minutes.

Probably the problem is that most of the new index entries are still
sitting in the index's pending list and haven't been merged to the main
search tree.  Try VACUUM'ing the table after a bulk insert.  (I think
there are other ways of triggering a GIN pending-list cleanup, but
I don't recall which ones were available in 9.3.)

Worthy of note here is that the max pending list size is governed by
work_mem, so a large work_mem can make this effect worse.  (We got
smart and made it an independent setting, but not till 9.5.)

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] Request - repeat value of \pset title during \watch interations

[Robert Haas]

On Mon, Mar 21, 2016 at 2:09 PM, David G. Johnston
 wrote:
> On Monday, March 21, 2016, Tom Lane  wrote:
>> "David G. Johnston"  writes:
>> > I'd rather not omit sleep but removing "Watch every" is fine (preferred
>> > actually), so:
>> > Title Is Here Mon Mar 21 15:05:06 2016 (5s)
>>
>> Meh ... seems a bit awkward to me.  Couldn't you include " (5s)" in the
>> title, if you want that info?  If it's variable, you could still
>> accommodate that:
>
> Actually, only if it's a variable that you setup and repeat and you show.  A
> bit cumbersome and mixes the parts that are title and those that are present
> only because you are watching.

Ah, come on.  This doesn't really seem like an issue we should spend
more time quibbling about.  I think Tom's version is fine.

-- 
Robert Haas
EnterpriseDB: 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

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Monday, March 21, 2016, Tom Lane  wrote:

> "David G. Johnston" > writes:
> > I'd rather not omit sleep but removing "Watch every" is fine (preferred
> > actually), so:
> > Title Is Here Mon Mar 21 15:05:06 2016 (5s)
>
> Meh ... seems a bit awkward to me.  Couldn't you include " (5s)" in the
> title, if you want that info?  If it's variable, you could still
> accommodate that:


Actually, only if it's a variable that you setup and repeat and you show.
A bit cumbersome and mixes the parts that are title and those that are
present only because you are watching.


> regression=# \set delay 5
> regression=# \pset title 'My Title (':delay' s)'
> Title is "My Title (5 s)".
> regression=# select repeat('xyzzy',12) \watch :delay
>

David J.

Re: [GENERAL] postgresql timezone and OS localtime correspondence

[Tom Lane]

Pavel Suderevsky  writes:
> My question is related to correspondence of postgresql "timezone" parameter
> with OS timezone settings in debian and red hat family systems.

> In debian by default postgresql "timezone" parameter value is "localtime"
> and it succesfully gets current OS timezone. (most probably it is not
> dynamically updated and with changing OS timezone postgresql to be
> restarted likewise, but still)

> In centos 7.2 postgresql doesn't accept "localtime" value and timezone must
> be specified directly.

The reason that happens is that Debian creates a symlink named "localtime"
within the timezone data file tree (probably via zic's -l option, though
maybe they do it by hand).  Red Hat doesn't do that; they follow a
different historical convention in which /etc/localtime defines the system
default zone.  Arguably, "zic -l" is a violation of filesystem layout
conventions, since it puts what ought to be system-specific configuration
data into /usr/share.

> So the common question is: is there a way to configure postgresql server to
> get OS localtime value rather than setting it manually in red hat family
> systems?

You could make your own symlink, though I'm unsure whether it'd survive
tzdata package updates.

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

[GENERAL] Slow GIN indexes after bulk insert

[Chris Spencer]

I have 5 GIN indexes on a PG 9.3 table containing about 50 million records.
Each index covers a "group" of common records like billing address,
shipping address, contact names, etc.

When first created, the indexes works miracles in speeding up the full text
search of these fields. However, I'm running into some serious maintenance
headaches.

After I insert a few thousand new records, the indexes seem to have no
effect. A query that might normally take 1 second now takes 5 minutes.

If I drop and recreate the indexes, then performance returns to normal, but
this causes a couple hours of downtime, even if I recreate indexes
concurrently.

Why are these GIN indexes becoming unusable after a large number of
inserts? Is this a known limitation or is there anything I can do to fix
this?

Chris

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Tom Lane]

"David G. Johnston"  writes:
> I'd rather not omit sleep but removing "Watch every" is fine (preferred
> actually), so:
> Title Is Here Mon Mar 21 15:05:06 2016 (5s)

Meh ... seems a bit awkward to me.  Couldn't you include " (5s)" in the
title, if you want that info?  If it's variable, you could still
accommodate that:

regression=# \set delay 5
regression=# \pset title 'My Title (':delay' s)'
Title is "My Title (5 s)".
regression=# select repeat('xyzzy',12) \watch :delay
   My Title (5 s)   Mon Mar 21 13:39:25 2016

repeat
--
 xyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzyxyzzy
(1 row)

But I don't care enough to veto it.
Anyone else have an opinion?

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: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Mon, Mar 21, 2016 at 10:14 AM, Tom Lane  wrote:

> Robert Haas  writes:
> > Well, the title isn't normally centered, but yeah, that is odd.  Yeah,
> > that is odd.  Come to think of it, I think I might have expected the
> > title to appear *above* "Watch every %s", not below it.  That might
> > decrease the oddness.
>
> AFAICS, it appears *beside* it with this patch.  It's only below if the
> terminal is narrow enough that it wraps to there.
>
> > As for letting the committer decide, I don't care about this
> > personally at all, so I'm only looking at it to be nice to the people
> > who do.  Whatever is the consensus is OK with me.  I just don't want
> > to get yelled at later for committing something here, so it would be
> > nice to see a few votes for whatever we're gonna do here.
>
> I'm still of the opinion that what would make the most sense is to replace
> the "Watch every Ns" text with the user-given title, if there is one.
> I ran that up the flagpole already and didn't get a lot of salutes, but
> it seems to respond to your concern that the user title ought to be first.
>
> Regardless of that, I concur with your complaints about coding style, in
> particular with the need to repeat the magic constant 50 in several
> places.  Also, I think the patch makes do_watch return the wrong result
> code for the (typical) case where we exit because of query cancel not
> PSQLexecWatch failure.
>
> So on the whole, I'd do it as attached.
>
​
I'd rather not omit sleep but removing "Watch every" is fine (preferred
actually), so:

if (user_title)​
​snprintf(title, title_len, "%s\t%s (%ld​s)", user_title,
asctime(localtime()), sleep)

"""
Title Is Here Mon Mar 21 15:05:06 2016 (5s)

col1
-
1
"""

David J.

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Tom Lane]

Robert Haas  writes:
> Well, the title isn't normally centered, but yeah, that is odd.  Yeah,
> that is odd.  Come to think of it, I think I might have expected the
> title to appear *above* "Watch every %s", not below it.  That might
> decrease the oddness.

AFAICS, it appears *beside* it with this patch.  It's only below if the
terminal is narrow enough that it wraps to there.

> As for letting the committer decide, I don't care about this
> personally at all, so I'm only looking at it to be nice to the people
> who do.  Whatever is the consensus is OK with me.  I just don't want
> to get yelled at later for committing something here, so it would be
> nice to see a few votes for whatever we're gonna do here.

I'm still of the opinion that what would make the most sense is to replace
the "Watch every Ns" text with the user-given title, if there is one.
I ran that up the flagpole already and didn't get a lot of salutes, but
it seems to respond to your concern that the user title ought to be first.

Regardless of that, I concur with your complaints about coding style, in
particular with the need to repeat the magic constant 50 in several
places.  Also, I think the patch makes do_watch return the wrong result
code for the (typical) case where we exit because of query cancel not
PSQLexecWatch failure.

So on the whole, I'd do it as attached.

regards, tom lane

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index eef6e4b..a309109 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** static bool
*** 3020,3026 
  do_watch(PQExpBuffer query_buf, long sleep)
  {
  	printQueryOpt myopt = pset.popt;
! 	char		title[50];
  
  	if (!query_buf || query_buf->len <= 0)
  	{
--- 3020,3029 
  do_watch(PQExpBuffer query_buf, long sleep)
  {
  	printQueryOpt myopt = pset.popt;
! 	const char	 *user_title;
! 	char		 *title;
! 	int			  title_len;
! 	int			  res = 0;
  
  	if (!query_buf || query_buf->len <= 0)
  	{
*** do_watch(PQExpBuffer query_buf, long sle
*** 3034,3042 
  	 */
  	myopt.topt.pager = 0;
  
  	for (;;)
  	{
- 		int			res;
  		time_t		timer;
  		long		i;
  
--- 3037,3055 
  	 */
  	myopt.topt.pager = 0;
  
+ 	/*
+ 	 * If there's a title in the user configuration, make sure we have room
+ 	 * for it in the title buffer.
+ 	 */
+ 	user_title = myopt.title;
+ 	if (user_title)
+ 		title_len = strlen(user_title) + 50;
+ 	else
+ 		title_len = 50;
+ 	title = pg_malloc(title_len);
+ 
  	for (;;)
  	{
  		time_t		timer;
  		long		i;
  
*** do_watch(PQExpBuffer query_buf, long sle
*** 3045,3052 
  		 * of completion of the command?
  		 */
  		timer = time(NULL);
! 		snprintf(title, sizeof(title), _("Watch every %lds\t%s"),
!  sleep, asctime(localtime()));
  		myopt.title = title;
  
  		/* Run the query and print out the results */
--- 3058,3071 
  		 * of completion of the command?
  		 */
  		timer = time(NULL);
! 		if (user_title)
! 			snprintf(title, title_len,
! 	 "%s\t%s",
! 	 user_title, asctime(localtime()));
! 		else
! 			snprintf(title, title_len,
! 	 _("Watch every %lds\t%s"),
! 	 sleep, asctime(localtime()));
  		myopt.title = title;
  
  		/* Run the query and print out the results */
*** do_watch(PQExpBuffer query_buf, long sle
*** 3056,3065 
  		 * PSQLexecWatch handles the case where we can no longer repeat the
  		 * query, and returns 0 or -1.
  		 */
! 		if (res == 0)
  			break;
- 		if (res == -1)
- 			return false;
  
  		/*
  		 * Set up cancellation of 'watch' via SIGINT.  We redo this each time
--- 3075,3082 
  		 * PSQLexecWatch handles the case where we can no longer repeat the
  		 * query, and returns 0 or -1.
  		 */
! 		if (res == 0 || res == -1)
  			break;
  
  		/*
  		 * Set up cancellation of 'watch' via SIGINT.  We redo this each time
*** do_watch(PQExpBuffer query_buf, long sle
*** 3084,3090 
  		sigint_interrupt_enabled = false;
  	}
  
! 	return true;
  }
  
  /*
--- 3101,3108 
  		sigint_interrupt_enabled = false;
  	}
  
! 	pg_free(title);
! 	return (res >= 0);
  }
  
  /*

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

[Jernigan, Kevin]

On 3/21/16, 9:10 AM, "pgsql-general-ow...@postgresql.org on behalf of Rakesh 
Kumar"  wrote:


>On 03/21/2016 10:57 AM, Thomas Kellerer wrote:
>
>> So - at least as far as I can tell - it's usually only used where 
>> high-availability is really important, e.g. where zero-downtime is required.
>> If you can live with a short downtime, a hot standby is much cheaper and 
>> probably not that much slower.
>
>Even the above statement can be challenged , given the rising popularity 
>of nosql databases which are all based on
>eventual consistency (aka async replication).
>
>A PG with BDR and an application designed to read/write only
>one node via connection mapping can match the high availability
>requirement of RAC.
>
>BTW disk is a single point of failure in RAC.
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Disk is only a single point of failure in RAC if you configure non-redundant 
storage. In general, Oracle recommends triple mirroring to protect against disk 
failures, as they have had many experiences over the years where customers with 
mirrored disks would see consecutive disk failures within short periods of time.

And RAC is widely used by Oracle’s larger customers, not only for HA, but also 
in some cases for scale-out. Having said that, it’s very true that any 
application running on Oracle RAC must be configured to avoid hot block 
contention across RAC nodes, so it’s not a completely transparent solution for 
scale out.

-KJ (original product manager for Oracle Parallel Server, the distant ancestor 
of RAC)

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

[Rakesh Kumar]

On 03/21/2016 10:57 AM, Thomas Kellerer wrote:


So - at least as far as I can tell - it's usually only used where 
high-availability is really important, e.g. where zero-downtime is required.
If you can live with a short downtime, a hot standby is much cheaper and 
probably not that much slower.


Even the above statement can be challenged , given the rising popularity 
of nosql databases which are all based on

eventual consistency (aka async replication).

A PG with BDR and an application designed to read/write only
one node via connection mapping can match the high availability
requirement of RAC.

BTW disk is a single point of failure in RAC.


--
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] [SQL] plan not correct?

[Adrian Klaver]

On 03/21/2016 08:29 AM, Bert wrote:

That is easy to check.

Let's do the same test again:
# select count(1) from dlp.st_itemseat;
  count
---
 12
(1 row)

# select count(1) from loaddlp.st_itemseat_insert;
  count
---
 87  --> of which 12 are already in the dlp.st_itemseat table
(1 row)

# explain analyze *

QUERY PLAN
-
  Insert on st_itemseat  (cost=55.47..69.97 rows=150 width=228) (actual
time=2.345..2.345 rows=0 loops=1)
CTE upsert
  ->  Update on st_itemseat et  (cost=17.50..55.42 rows=2 width=240)
(actual time=0.493..0.545 rows=12 loops=1)
->  Hash Join  (cost=17.50..55.42 rows=2 width=240) (actual
time=0.303..0.318 rows=12 loops=1)
  Hash Cond: ((et.tick_server_id =
st_itemseat_insert_1.tick_server_id) AND (et.itemseat_id =
st_itemseat_insert_1.itemseat_id))
  ->  Seq Scan on st_itemseat et  (cost=0.00..13.10
rows=310 width=14) (actual time=0.025..0.028 rows=12 loops=1)
  ->  Hash  (cost=13.00..13.00 rows=300 width=234)
(actual time=0.244..0.244 rows=87 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 13kB
->  Seq Scan on st_itemseat_insert
st_itemseat_insert_1  (cost=0.00..13.00 rows=300 width=234) (actual
time=0.005..0.120 rows=87 loops=1)
->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150
width=228) (actual time=0.637..0.726 rows=75 loops=1)
  Filter: (NOT (hashed SubPlan 2))
  Rows Removed by Filter: 12
  SubPlan 2
->  CTE Scan on upsert  (cost=0.00..0.04 rows=2 width=8)
(actual time=0.498..0.561 rows=12 loops=1)
  Planning time: 1.122 ms
  Execution time: 2.682 ms

# *
INSERT 0 0

# select count(1) from dlp.st_itemseat;
  count
---
 87
(1 row)


* the upsert query can be found attached to the first mail, but the
difference is that the 'where loadtabletime' is removed

As you can see the in the update part of the explain the 'rows' nr is
12. Which is what is expected.
But the rows on the insert are again 0, while it should be 75.


They are seen, including the 12 rows that are filtered out for updating:

" ->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150 
width=228) (actual time=0.637..0.726 rows=75 loops=1)

 Filter: (NOT (hashed SubPlan 2))
 Rows Removed by Filter: 12
 SubPlan 2
"

I do not know why that value is not propagated up to 'Insert on 
st_itemseat ...'.





wkr,
Bert




--
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] [SQL] plan not correct?

[Adrian Klaver]

On 03/21/2016 08:29 AM, Bert wrote:

My mistake, Cced wrong list.


That is easy to check.

Let's do the same test again:
# select count(1) from dlp.st_itemseat;
  count
---
 12
(1 row)

# select count(1) from loaddlp.st_itemseat_insert;
  count
---
 87  --> of which 12 are already in the dlp.st_itemseat table
(1 row)

# explain analyze *

QUERY PLAN
-
  Insert on st_itemseat  (cost=55.47..69.97 rows=150 width=228) (actual
time=2.345..2.345 rows=0 loops=1)
CTE upsert
  ->  Update on st_itemseat et  (cost=17.50..55.42 rows=2 width=240)
(actual time=0.493..0.545 rows=12 loops=1)
->  Hash Join  (cost=17.50..55.42 rows=2 width=240) (actual
time=0.303..0.318 rows=12 loops=1)
  Hash Cond: ((et.tick_server_id =
st_itemseat_insert_1.tick_server_id) AND (et.itemseat_id =
st_itemseat_insert_1.itemseat_id))
  ->  Seq Scan on st_itemseat et  (cost=0.00..13.10
rows=310 width=14) (actual time=0.025..0.028 rows=12 loops=1)
  ->  Hash  (cost=13.00..13.00 rows=300 width=234)
(actual time=0.244..0.244 rows=87 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 13kB
->  Seq Scan on st_itemseat_insert
st_itemseat_insert_1  (cost=0.00..13.00 rows=300 width=234) (actual
time=0.005..0.120 rows=87 loops=1)
->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150
width=228) (actual time=0.637..0.726 rows=75 loops=1)
  Filter: (NOT (hashed SubPlan 2))
  Rows Removed by Filter: 12
  SubPlan 2
->  CTE Scan on upsert  (cost=0.00..0.04 rows=2 width=8)
(actual time=0.498..0.561 rows=12 loops=1)
  Planning time: 1.122 ms
  Execution time: 2.682 ms

# *
INSERT 0 0

# select count(1) from dlp.st_itemseat;
  count
---
 87
(1 row)


* the upsert query can be found attached to the first mail, but the
difference is that the 'where loadtabletime' is removed

As you can see the in the update part of the explain the 'rows' nr is
12. Which is what is expected.
But the rows on the insert are again 0, while it should be 75.

wkr,
Bert

On Mon, Mar 21, 2016 at 4:01 PM, Adrian Klaver
> wrote:

On 03/21/2016 07:54 AM, Bert wrote:

Ccing list

Hello Ardian,

The PostgreSQL version is 9.4.5

The reason I have the 'returning' statement in the update section is
because I only insert the data that has not been updated. I
don't see
why I would need to return anything in the insert section?


Well it was more about what you saw as the result of the UPDATE. It
is not clear to me whether that is 'UPDATE count' or the rows from
RETURNING?


On Mon, Mar 21, 2016 at 3:39 PM, Adrian Klaver

>> wrote:

 On 03/21/2016 07:03 AM, Bert wrote:

 Dear all,

 I am not sure if I am looking at a bug, or I am just doing
 something wrong.
 Anyhow, to me it seems that the plan for an upsert is
wrong. (I
 can not
 find how many rows are inserted in the table)

 Regard the following setup:
 # select count(1) from dlp.st_itemseat;
count
 ---
0
 (1 row)

 # select count(1) from loaddlp.st_itemseat_insert where
 loadtabletime =
 '2016-03-21 14:53:28.771467';
count
 ---
   12
 (1 row)

 # explain analyze *

 QUERY PLAN


-
Insert on st_itemseat  (cost=26.14..41.39 rows=1
width=228)
 (actual
 time=1.282..1.282 rows=0 loops=1)
  CTE upsert
->  Update on st_itemseat et  (cost=0.15..26.11
rows=1
 width=240)
 (actual time=0.066..0.066 rows=0 loops=1)
  ->  Nested Loop  (cost=0.15..26.11 rows=1
 width=240) (actual
 time=0.061..0.061 rows=0 loops=1)
->  Seq Scan on st_itemseat_insert
 st_itemseat_insert_1  (cost=0.00..13.75 rows=2
width=234) (actual
 time=0.031..0.040 rows=12 

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Robert Haas]

On Mon, Mar 21, 2016 at 11:17 AM, David G. Johnston
 wrote:
>> And does everybody agree that this is a desirable change?
>
> Adding the title is desirable.  While I'm inclined to bike-shed this
> anything that gets it in I can live with and so I'm content letting the
> author/committer decide where exactly things (including whitespace) appear.
>
> It is a bit odd that the "Watch every %s" gets centered if the result is
> wide but that the title remains left-aligned.

Well, the title isn't normally centered, but yeah, that is odd.  Yeah,
that is odd.  Come to think of it, I think I might have expected the
title to appear *above* "Watch every %s", not below it.  That might
decrease the oddness.

As for letting the committer decide, I don't care about this
personally at all, so I'm only looking at it to be nice to the people
who do.  Whatever is the consensus is OK with me.  I just don't want
to get yelled at later for committing something here, so it would be
nice to see a few votes for whatever we're gonna do here.

-- 
Robert Haas
EnterpriseDB: 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

Re: [GENERAL] Including SQL files

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Monday, March 21, 2016 10:43 AM
To: pgsql-general 
Subject: [GENERAL] Including SQL files

Hello fellow pgsql users,
I am programming a word game backend in PL/pgSQL and have already reached a 
point, where (too) many stored functions are declared in a single file 
words.sql:

# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = 
ns.oid)
WHERE ns.nspname = 'public' ORDER BY proname;
?column?
-
 test1(integer)
 words_hash(character varying)
 words_join_new_game(integer, integer)
 words_merge_users(jsonb, inet)
 words_paying_user(integer)
 words_play(integer, integer, jsonb)
 words_shuffle(character varying[])
 words_skip_game(integer, integer)
 words_surrender_game(integer, integer)
 words_swap_game(integer, integer, character varying)
 words_trigger()
 words_valid_user(integer, character varying, character varying)
(12 rows)
I would prefer to have every stored function in a separate file (which would 
make reading git history easier too) and include them from words.sql.
Is there such a thing for PostgreSQL 9.5.1 available please and if not - could 
you share your approaches here?
The "EXEC SQL INCLUDE" described at 
http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be 
something different?
Regards
Alex


Psql directive \i – is your friend.
In your words.sql:
\i words_hash
\i words_join_new_game
\i …

Regards,
Igor Neyman

[GENERAL] postgresql timezone and OS localtime correspondence

[Pavel Suderevsky]

Hi,

My question is related to correspondence of postgresql "timezone" parameter
with OS timezone settings in debian and red hat family systems.

In debian by default postgresql "timezone" parameter value is "localtime"
and it succesfully gets current OS timezone. (most probably it is not
dynamically updated and with changing OS timezone postgresql to be
restarted likewise, but still)

In centos 7.2 postgresql doesn't accept "localtime" value and timezone must
be specified directly.

Mar 21 10:55:50 dbtest3.local pg_ctl[11242]: 2016-03-21 05:55:50 EST  LOG:
>  invalid value for parameter "TimeZone": "localtime"
> [root@dbtest3 ~]# ls -la /etc/localtime
>
> lrwxrwxrwx. 1 root root 23 Mar 21 12:10 /etc/localtime ->
> /usr/share/zoneinfo/GMT


I couldn't find information related to difference between debian and red
hat family systems for timezone parameter in docs.

So the common question is: is there a way to configure postgresql server to
get OS localtime value rather than setting it manually in red hat family
systems?

Re: [GENERAL] [SQL] plan not correct?

[Bert]

That is easy to check.

Let's do the same test again:
# select count(1) from dlp.st_itemseat;
 count
---
12
(1 row)

# select count(1) from loaddlp.st_itemseat_insert;
 count
---
87  --> of which 12 are already in the dlp.st_itemseat table
(1 row)

# explain analyze *

QUERY
PLAN

-
 Insert on st_itemseat  (cost=55.47..69.97 rows=150 width=228) (actual
time=2.345..2.345 rows=0 loops=1)
   CTE upsert
 ->  Update on st_itemseat et  (cost=17.50..55.42 rows=2 width=240)
(actual time=0.493..0.545 rows=12 loops=1)
   ->  Hash Join  (cost=17.50..55.42 rows=2 width=240) (actual
time=0.303..0.318 rows=12 loops=1)
 Hash Cond: ((et.tick_server_id =
st_itemseat_insert_1.tick_server_id) AND (et.itemseat_id =
st_itemseat_insert_1.itemseat_id))
 ->  Seq Scan on st_itemseat et  (cost=0.00..13.10 rows=310
width=14) (actual time=0.025..0.028 rows=12 loops=1)
 ->  Hash  (cost=13.00..13.00 rows=300 width=234) (actual
time=0.244..0.244 rows=87 loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 13kB
   ->  Seq Scan on st_itemseat_insert
st_itemseat_insert_1  (cost=0.00..13.00 rows=300 width=234) (actual
time=0.005..0.120 rows=87 loops=1)
   ->  Seq Scan on st_itemseat_insert  (cost=0.04..14.54 rows=150
width=228) (actual time=0.637..0.726 rows=75 loops=1)
 Filter: (NOT (hashed SubPlan 2))
 Rows Removed by Filter: 12
 SubPlan 2
   ->  CTE Scan on upsert  (cost=0.00..0.04 rows=2 width=8) (actual
time=0.498..0.561 rows=12 loops=1)
 Planning time: 1.122 ms
 Execution time: 2.682 ms

# *
INSERT 0 0

# select count(1) from dlp.st_itemseat;
 count
---
87
(1 row)


* the upsert query can be found attached to the first mail, but the
difference is that the 'where loadtabletime' is removed

As you can see the in the update part of the explain the 'rows' nr is 12.
Which is what is expected.
But the rows on the insert are again 0, while it should be 75.

wkr,
Bert

On Mon, Mar 21, 2016 at 4:01 PM, Adrian Klaver 
wrote:

> On 03/21/2016 07:54 AM, Bert wrote:
>
> Ccing list
>
>> Hello Ardian,
>>
>> The PostgreSQL version is 9.4.5
>>
>> The reason I have the 'returning' statement in the update section is
>> because I only insert the data that has not been updated. I don't see
>> why I would need to return anything in the insert section?
>>
>
> Well it was more about what you saw as the result of the UPDATE. It is not
> clear to me whether that is 'UPDATE count' or the rows from RETURNING?
>
>
>> On Mon, Mar 21, 2016 at 3:39 PM, Adrian Klaver
>> > wrote:
>>
>> On 03/21/2016 07:03 AM, Bert wrote:
>>
>> Dear all,
>>
>> I am not sure if I am looking at a bug, or I am just doing
>> something wrong.
>> Anyhow, to me it seems that the plan for an upsert is wrong. (I
>> can not
>> find how many rows are inserted in the table)
>>
>> Regard the following setup:
>> # select count(1) from dlp.st_itemseat;
>>count
>> ---
>>0
>> (1 row)
>>
>> # select count(1) from loaddlp.st_itemseat_insert where
>> loadtabletime =
>> '2016-03-21 14:53:28.771467';
>>count
>> ---
>>   12
>> (1 row)
>>
>> # explain analyze *
>>
>> QUERY PLAN
>>
>> -
>>Insert on st_itemseat  (cost=26.14..41.39 rows=1 width=228)
>> (actual
>> time=1.282..1.282 rows=0 loops=1)
>>  CTE upsert
>>->  Update on st_itemseat et  (cost=0.15..26.11 rows=1
>> width=240)
>> (actual time=0.066..0.066 rows=0 loops=1)
>>  ->  Nested Loop  (cost=0.15..26.11 rows=1
>> width=240) (actual
>> time=0.061..0.061 rows=0 loops=1)
>>->  Seq Scan on st_itemseat_insert
>> st_itemseat_insert_1  (cost=0.00..13.75 rows=2 width=234) (actual
>> time=0.031..0.040 rows=12 loops=1)
>>  Filter: (loadtabletime = '2016-03-21
>> 14:53:28.771467'::timestamp without time zone)
>>  Rows Removed by Filter: 75
>>->  Index Scan using pk_st_itemseat on
>> st_itemseat et
>> (cost=0.15..6.17 rows=1 width=14) (actual time=0.001..0.001
>> rows=0 loops=12)
>>  Index Cond: ((tick_server_id =
>> st_itemseat_insert_1.tick_server_id) AND (itemseat_id =
>> 

Re: [GENERAL] grant select on pg_stat_activity

[Adrian Klaver]

On 03/21/2016 07:15 AM, Vick Khera wrote:


On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver
> wrote:

They should be able to, see below. If that is not your case, then
more information is needed.


You can see your own queries, however non-superuser will not see the
query for other users. You will be able to see the other info, though.


Did not think of that.



I do not know what permission is necessary to make that visible. My
hunch is it will require superuser privileges


Hmm, I would hesitate to mess with permissions on a system view.

A quick and dirty fix as a superuser:

CREATE FUNCTION pg_stat_allusers( )
 RETURNS setof pg_stat_activity
 LANGUAGE sql SECURITY DEFINER
AS $function$
SELECT * FROM pg_stat_activity;
$function$


test=> select current_user;
-[ RECORD 1 ]+--
current_user | guest

test=> select * from pg_stat_allusers();
-[ RECORD 1 ]+--
datid| 983301
datname  | test
pid  | 5886
usesysid | 10
usename  | postgres
application_name | psql
client_addr  |
client_hostname  |
client_port  | -1
backend_start| 2016-03-21 08:03:43.60797-07
xact_start   |
query_start  | 2016-03-21 08:14:47.166341-07
state_change | 2016-03-21 08:14:47.166953-07
waiting  | f
state| idle
backend_xid  |
backend_xmin |
query| SELECT pg_catalog.pg_get_functiondef(1730587)
-[ RECORD 2 ]+--
datid| 983301
datname  | test 



pid  | 5889 



usesysid | 432800 



usename  | guest 



application_name | psql 



client_addr  | 



client_hostname  | 



client_port  | -1 



backend_start| 2016-03-21 08:03:48.559611-07 



xact_start   | 2016-03-21 08:18:40.245858-07 



query_start  | 2016-03-21 08:18:40.245858-07 



state_change | 2016-03-21 08:18:40.245862-07 



waiting  | f 



state| active 



backend_xid  | 



backend_xmin | 119564 



query| select * from pg_stat_allusers();
--
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: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[David G. Johnston]

On Mon, Mar 21, 2016 at 8:03 AM, Robert Haas  wrote:

> On Sun, Mar 20, 2016 at 9:31 AM, Michael Paquier
>  wrote:
> > And the patch attached gives the following output:
> > With title:
> > =# \watch 1
> > Watch every 1sSun Mar 20 22:28:38 2016
> > popo
> >  a
> > ---
> >  1
> > (1 row)
>

​This doesn't show the blank line above "popo" that the prior example
had...​

>
> > And without title:
> > Watch every 1sSun Mar 20 22:29:31 2016
> >
> >  a
> > ---
> >  1
> > (1 row)
>
>
​Unchanged from present behavior - but its not obvious that the watch line
is center-aligned​

And does everybody agree that this is a desirable change?
>

​Adding the title is desirable.  While I'm inclined to bike-shed this
anything that gets it in I can live with and so I'm content letting the
author/committer decide where exactly things (including whitespace) appear.

​It is a bit odd that the "Watch every %s" gets centered if the result
is wide but that the title remains left-aligned.

The minimally invasive change would be the following:

>optional title<
>watch<
>(blank line)
>headers
>head-body divider
>body
>optional footer

Though I like the idea of:

>optional title
>(blank line - if Title present)
>headers
>head-body divider
>body
>optional footer
>watch

​David J.​

Re: [GENERAL] PostgreSQL advocacy

[Scott Marlowe]

On Mon, Mar 21, 2016 at 7:44 AM, Mark Morgan Lloyd
 wrote:
> If anybody puts together a "just the facts" document after Oracle's attack
> on PostgreSQL in Russia, please make sure it's drawn to the attention of
> this mailing list for the benefit of those who aren't in -advocacy.
>
> I was discussing this sort of thing elsewhere in the context of MS's
> apparent challenge to Oracle and IBM, and the dominant feeling appeared to
> be that actual use of things like Oracle RAC was vanishingly uncommon. Which
> surprised me, and which I'm treating with caution since the fact that
> facilities aren't used (in a certain population of developers etc.) can in
> no way be interpreted as meaning that the technology is not unavailable or
> unreliable.


I've submitted three different bug reports and had a patch within 48
hours each time. the responsiveness of this list, and the folks who
code PostgreSQL is far above any level of support I've ever gotten
from Oracle.

I once asked Oracle to please package the newest connection libs into
an RPM for RHEL5 and their response was "do it yourself."

Yeah, I know which database has REAL, USEFUL support for a DBA and it
isn't Oracle.


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

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

[Robert Haas]

On Sun, Mar 20, 2016 at 9:31 AM, Michael Paquier
 wrote:
> And the patch attached gives the following output:
> With title:
> =# \watch 1
> Watch every 1sSun Mar 20 22:28:38 2016
> popo
>  a
> ---
>  1
> (1 row)
>
> And without title:
> Watch every 1sSun Mar 20 22:29:31 2016
>
>  a
> ---
>  1
> (1 row)

And does everybody agree that this is a desirable change?

As for the patch itself, you could replace all this:

+   /*
+* Take into account any title present in the user setup as a part of
+* what is printed for each iteration by using it as a header.
+*/
+   if (myopt.title)
+   {
+   title_len = strlen(myopt.title);
+   title = pg_malloc(title_len + 50);
+   head_title = pg_strdup(myopt.title);
+   }
+   else
+   {
+   title_len = 0;
+   title = pg_malloc(50);
+   head_title = pg_strdup("");
+   }

...with:

head_title = pg_strdup(myopt.title != NULL ? myopt.title : "");
title_len = strlen(head_title);
title = pg_malloc(title_len + 50);

Better yet, include the + 50 in title_len, and then you don't need to
reference the number 50 again further down.

-- 
Robert Haas
EnterpriseDB: 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

Re: [GENERAL] [SQL] plan not correct?

[Adrian Klaver]

On 03/21/2016 07:54 AM, Bert wrote:

Ccing list

Hello Ardian,

The PostgreSQL version is 9.4.5

The reason I have the 'returning' statement in the update section is
because I only insert the data that has not been updated. I don't see
why I would need to return anything in the insert section?


Well it was more about what you saw as the result of the UPDATE. It is 
not clear to me whether that is 'UPDATE count' or the rows from RETURNING?




On Mon, Mar 21, 2016 at 3:39 PM, Adrian Klaver
> wrote:

On 03/21/2016 07:03 AM, Bert wrote:

Dear all,

I am not sure if I am looking at a bug, or I am just doing
something wrong.
Anyhow, to me it seems that the plan for an upsert is wrong. (I
can not
find how many rows are inserted in the table)

Regard the following setup:
# select count(1) from dlp.st_itemseat;
   count
---
   0
(1 row)

# select count(1) from loaddlp.st_itemseat_insert where
loadtabletime =
'2016-03-21 14:53:28.771467';
   count
---
  12
(1 row)

# explain analyze *

QUERY PLAN

-
   Insert on st_itemseat  (cost=26.14..41.39 rows=1 width=228)
(actual
time=1.282..1.282 rows=0 loops=1)
 CTE upsert
   ->  Update on st_itemseat et  (cost=0.15..26.11 rows=1
width=240)
(actual time=0.066..0.066 rows=0 loops=1)
 ->  Nested Loop  (cost=0.15..26.11 rows=1
width=240) (actual
time=0.061..0.061 rows=0 loops=1)
   ->  Seq Scan on st_itemseat_insert
st_itemseat_insert_1  (cost=0.00..13.75 rows=2 width=234) (actual
time=0.031..0.040 rows=12 loops=1)
 Filter: (loadtabletime = '2016-03-21
14:53:28.771467'::timestamp without time zone)
 Rows Removed by Filter: 75
   ->  Index Scan using pk_st_itemseat on
st_itemseat et
(cost=0.15..6.17 rows=1 width=14) (actual time=0.001..0.001
rows=0 loops=12)
 Index Cond: ((tick_server_id =
st_itemseat_insert_1.tick_server_id) AND (itemseat_id =
st_itemseat_insert_1.itemseat_id))
 ->  Seq Scan on st_itemseat_insert  (cost=0.02..15.27 rows=1
width=228) (actual time=0.175..0.201 rows=12 loops=1)
   Filter: ((loadtabletime = '2016-03-21
14:53:28.771467'::timestamp without time zone) AND (NOT (hashed
SubPlan 2)))
   Rows Removed by Filter: 75
   SubPlan 2
 ->  CTE Scan on upsert  (cost=0.00..0.02 rows=1
width=8)
(actual time=0.068..0.068 rows=0 loops=1)
   Planning time: 1.022 ms
   Execution time: 1.596 ms
(16 rows)


# *
INSERT 0 0

# select count(1) from dlp.st_itemseat;
   count
---
  12
(1 row)

* the upsert query is added as an attachment to this mail.


In the query plan it seems that 0 rows are inserted; although 12
rows
are inserted when we compare the 2 counts.
When an update happens, the rows reported in the 'update'
statement are
correct.


Do you get a row count or the rows?

The reason I ask is that in the UPDATE section you have
'...returning ET.*', but not in the INSERT section.

Not sure if it matters in this case, but the Postgres version might
provide context.



Is this a bug? Or am I looking at the wrong part of the plan? I
would
like to check how many rows are actually inserted from the plan.

wkr,
Bert

--
Bert Desmet
0477/305361 





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




--
Bert Desmet
0477/305361



--
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] Including SQL files

[Alexander Farber]

Thanks for your replies.

While I use "\i" regularly I just didn't realize it would be suitable here
as well :-)

Regards
Alex

Re: [GENERAL] PostgreSQL advocacy

[Thomas Kellerer]

Mark Morgan Lloyd schrieb am 21.03.2016 um 14:44:
> I was discussing this sort of thing elsewhere in the context of MS's
> apparent challenge to Oracle and IBM, and the dominant feeling
> appeared to be that actual use of things like Oracle RAC was
> vanishingly uncommon. Which surprised me, and which I'm treating with
> caution since the fact that facilities aren't used (in a certain
> population of developers etc.) can in no way be interpreted as
> meaning that the technology is not unavailable or unreliable.

RAC is usually used for high-availability not for (horizontal) scaling. 

All nodes in a RAC cluster share the same I/O system. So I/O is still the 
bottleneck and you can't use a RAC to scale a system that is I/O bound. 

Back in the days when RAC was introduced multi-core, multi-CPU servers weren't 
that common (and and way fewer CPUs as high-servers today) and for systems like 
that, RAC _can_ indeed be used to scale the system. 

And the cache synchronization across the nodes can quickly become a *serious* 
bottleneck if the application isn't really designed for it. 
I have seen misbehaving applications that would cause Oracle to spent over 30% 
of its processing time only with sending blocks back and forth between the 
nodes.

So - at least as far as I can tell - it's usually only used where 
high-availability is really important, e.g. where zero-downtime is required. 
If you can live with a short downtime, a hot standby is much cheaper and 
probably not that much slower. 

See e.g. here: http://www.sdmc.nl/YouProbablyDontNeedRACUSVersion.pdf
and here: 
http://nyoug.org/Presentations/2006/September_NYC_Metro_Meeting/200609Zito_You%20Probably%20DO%20Need%20RAC.pdf

Thomas




-- 
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] Including SQL files

[Adrian Klaver]

On 03/21/2016 07:42 AM, Alexander Farber wrote:

Hello fellow pgsql users,

I am programming a word game backend in PL/pgSQL and have already
reached a point, where (too) many stored functions are declared in a
single file words.sql:

# SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
 FROM pg_proc INNER JOIN pg_namespace ns ON
(pg_proc.pronamespace = ns.oid)
 WHERE ns.nspname = 'public' ORDER BY proname;
 ?column?
-
  test1(integer)
  words_hash(character varying)
  words_join_new_game(integer, integer)
  words_merge_users(jsonb, inet)
  words_paying_user(integer)
  words_play(integer, integer, jsonb)
  words_shuffle(character varying[])
  words_skip_game(integer, integer)
  words_surrender_game(integer, integer)
  words_swap_game(integer, integer, character varying)
  words_trigger()
  words_valid_user(integer, character varying, character varying)
(12 rows)

I would prefer to have every stored function in a separate file (which
would make reading git history easier too) and include them from words.sql.


So I am clear, what you are saying is that the definitions for the 12 
functions you list above are all contained(external to the database) in 
a text file words.sql. You would prefer that they be in separate files 
externally with the ability to aggregate them in the words.sql by just 
using an include statement instead of the complete text of each 
function. Is this correct?




Is there such a thing for PostgreSQL 9.5.1 available please and if not -
could you share your approaches here?

The "EXEC SQL INCLUDE" described at
http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be
something different?

Regards
Alex




--
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] Including SQL files

[Victor Yegorov]

2016-03-21 16:42 GMT+02:00 Alexander Farber :

> I would prefer to have every stored function in a separate file (which
> would make reading git history easier too) and include them from words.sql.
>
> Is there such a thing for PostgreSQL 9.5.1 available please and if not -
> could you share your approaches here?
>

If you're processing your files with Postgres' `psql` tool, you can use
`\i` directive to include other files.


-- 
Victor Y. Yegorov

Re: [GENERAL] Including SQL files

[Pavel Stehule]

Hi

2016-03-21 15:42 GMT+01:00 Alexander Farber :

> Hello fellow pgsql users,
>
> I am programming a word game backend in PL/pgSQL and have already reached
> a point, where (too) many stored functions are declared in a single file
> words.sql:
>
> # SELECT proname || '(' || oidvectortypes(proargtypes) || ')'
> FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace =
> ns.oid)
> WHERE ns.nspname = 'public' ORDER BY proname;
> ?column?
> -
>  test1(integer)
>  words_hash(character varying)
>  words_join_new_game(integer, integer)
>  words_merge_users(jsonb, inet)
>  words_paying_user(integer)
>  words_play(integer, integer, jsonb)
>  words_shuffle(character varying[])
>  words_skip_game(integer, integer)
>  words_surrender_game(integer, integer)
>  words_swap_game(integer, integer, character varying)
>  words_trigger()
>  words_valid_user(integer, character varying, character varying)
> (12 rows)
>
> I would prefer to have every stored function in a separate file (which
> would make reading git history easier too) and include them from words.sql.
>
> Is there such a thing for PostgreSQL 9.5.1 available please and if not -
> could you share your approaches here?
>

why you need it?

I developed large plpgsql application, and I used more than thirty files
fit plpgsql functions. For deployment  I had prepared Makefile. One file
holds one schema usually.

Regards

Pavel


>
> The "EXEC SQL INCLUDE" described at
> http://www.postgresql.org/docs/9.5/static/ecpg-preproc.html seems to be
> something different?
>
> Regards
> Alex
>
>

Re: [GENERAL] grant select on pg_stat_activity

[Vick Khera]

On Fri, Mar 18, 2016 at 5:46 PM, Adrian Klaver 
wrote:

> They should be able to, see below. If that is not your case, then more
> information is needed.
>

You can see your own queries, however non-superuser will not see the query
for other users. You will be able to see the other info, though.

I do not know what permission is necessary to make that visible. My hunch
is it will require superuser privileges.

[GENERAL] PostgreSQL advocacy

[Mark Morgan Lloyd]

If anybody puts together a "just the facts" document after Oracle's 
attack on PostgreSQL in Russia, please make sure it's drawn to the 
attention of this mailing list for the benefit of those who aren't in 
-advocacy.


I was discussing this sort of thing elsewhere in the context of MS's 
apparent challenge to Oracle and IBM, and the dominant feeling appeared 
to be that actual use of things like Oracle RAC was vanishingly 
uncommon. Which surprised me, and which I'm treating with caution since 
the fact that facilities aren't used (in a certain population of 
developers etc.) can in no way be interpreted as meaning that the 
technology is not unavailable or unreliable.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]


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

[GENERAL] pglogical two way replication problem

[alexey.i.la...@gmail.com]

Hi All,

Try to configure pglogical in such way:
srv1, srv2: CentOS 6.7, PostgreSQL 9.5.1, pglogical 1.0.1

On both servers created database "xdray", schema is common and consists
tables t1 and t2. 
The srv1 writes to the t1. The t1 added to replication set repset_t1 on
the srv1. 
The srv2 writes to the t2. The t2 added to replication set repset_t2 on
the srv2.
The srv1 subscribes on repset_t2.
The srv2 subscribes on repset_t1.



  SRV1  SRV2
+--+   +--+
|  repset_t1   |   |  |
| +-+  |   |  |
| |t1   +->t1 |
| +-+  |   |  |
|  |   |   repset_t2  |
|  |   |  --+ |
| t2<-+t2   | |
|  |   |  +-+ |
+--+   +--+


Commands for reproduce.


On Srv1

$ psql xdray
xdray=# create table t1 (id serial constraint pk_t1 primary key);
xdray=# create table t2 (id serial constraint pk_t2 primary key);
xdray=# select pglogical.create_node('srv1','host=srv1 user=xdray 
dbname=xdray');
xdray=# select pglogical.create_replication_set('repset_t1');
xdray=# select pglogical.replication_set_add_table('repset_t1', 'public.t1');

On Srv2

$ psql xdray
xdray=# create table t1 (id serial constraint pk_t1 primary key);
xdray=# create table t2 (id serial constraint pk_t2 primary key);
xdray=# select pglogical.create_node('srv2','host=srv2 user=xdray 
dbname=xdray');
xdray=# select pglogical.create_replication_set('repset_t2');
xdray=# select pglogical.replication_set_add_table('repset_t2', 'public.t2');



On Srv1

xdray=# select pglogical.create_subscription(
  subscription_name := 'sub_t2',
  provider_dsn := 'host=srv2 user=xdray dbname=xdray',
  replication_sets := ARRAY['repset_t2'],
  synchronize_structure := false,
  synchronize_data := true);

On Svr2

xdray=# select pglogical.create_subscription(
  subscription_name := 'sub_t1',
  provider_dsn := 'host=srv1  user=xdray dbname=xdray',
  replication_sets := ARRAY['repset_t1'],
  synchronize_structure := false,
  synchronize_data := true);


So far, so good.
But as soon as we commit first transactions on any "producer" tables we
have:


On Srv1
xdray=# insert into t1 default values;

On log file of Srv1 there are end less errors like:
< 2016-03-21 11:04:26.836 MSK >LOG:  worker process: pglogical apply 
16385:1317353735 (PID 31952) exited with exit code 1
< 2016-03-21 11:04:26.838 MSK >LOG:  starting apply for subscription sub_t2
< 2016-03-21 11:04:26.844 MSK >ERROR:  cache lookup failed for replication 
origin 'pgl_xdray_srv1_sub_t1'
< 2016-03-21 11:04:26.845 MSK >LOG:  worker process: pglogical apply 
16385:1317353735 (PID 31953) exited with exit code 1
< 2016-03-21 11:04:26.847 MSK >LOG:  starting apply for subscription sub_t2
< 2016-03-21 11:04:26.853 MSK >ERROR:  cache lookup failed for replication 
origin 'pgl_xdray_srv1_sub_t1'
< 2016-03-21 11:04:26.854 MSK >LOG:  worker process: pglogical apply 
16385:1317353735 (PID 31954) exited with exit code 1
< 2016-03-21 11:04:26.856 MSK >LOG:  starting apply for subscription sub_t2
< 2016-03-21 11:04:26.862 MSK >ERROR:  cache lookup failed for replication 
origin 'pgl_xdray_srv1_sub_t1'

On Srv2 log:
< 2016-03-21 11:04:26.837 MSK >LOG:  could not receive data from client: 
Connection reset by peer
< 2016-03-21 11:04:26.837 MSK >LOG:  unexpected EOF on standby connection
< 2016-03-21 11:04:26.840 MSK >LOG:  connection received: host=192.168.0.104 
port=42966
< 2016-03-21 11:04:26.842 MSK >LOG:  replication connection authorized: 
user=xdray
< 2016-03-21 11:04:26.844 MSK >LOG:  starting logical decoding for slot 
"pgl_xdray_srv2_sub_t2"
< 2016-03-21 11:04:26.844 MSK >DETAIL:  streaming transactions committing after 
0/39E1060, reading WAL from 0/39E1028
< 2016-03-21 11:04:26.844 MSK >LOG:  logical decoding found consistent point at 
0/39E1028
< 2016-03-21 11:04:26.844 MSK >DETAIL:  There are no running transactions.
< 2016-03-21 11:04:26.847 MSK >LOG:  could not receive data from client: 
Connection reset by peer
< 2016-03-21 11:04:26.847 MSK >LOG:  unexpected EOF on standby connection
< 2016-03-21 11:04:26.849 MSK >LOG:  connection received: host=192.168.0.104 
port=42967
< 2016-03-21 11:04:26.851 MSK >LOG:  replication connection authorized: 
user=xdray
< 2016-03-21 11:04:26.853 MSK >LOG:  starting logical decoding for slot 
"pgl_xdray_srv2_sub_t2"
< 2016-03-21 11:04:26.853 MSK >DETAIL:  streaming transactions committing after 
0/39E1060, reading WAL from 0/39E1028
< 2016-03-21 11:04:26.853 MSK >LOG:  logical decoding found consistent point at 
0/39E1028
< 2016-03-21 11:04:26.853 MSK >DETAIL:  There are no running transactions.
< 2016-03-21 11:04:26.855 MSK >LOG:  could not receive data from client: 
Connection reset by peer
< 2016-03-21 11:04:26.855 MSK >LOG:  unexpected EOF on standby 

Re: [GENERAL] pg_dump crashing ... solved

[Matthias Schmitt]

Hello,

thank you for your answers.

> On 20 Mar 2016, at 16:56, Adrian Klaver  wrote:
> 
> So what happens if you either?:
> 
> 1) In the root crontab, change the command to:
> 
> /Users/…/bin/pg_dump -p 5433 mydatabase_1_0_0 -U mmpostgres > 
> /my_backup_path/mydatabase_1_0_0.dump

This will make no difference.

> On 21 Mar 2016, at 03:42, Thomas Munro  wrote:
> 
> Is this related?
> 
> http://www.postgresql.org/message-id/cak7teys9-o4bterbs3xuk2bffnnd55u2sm9j5r2fi7v6bhj...@mail.gmail.com

Yes, this is related. I tried to set RemoveIPC=no, but it made no difference.

Finally I found a solution with the hint given here:
http://www.postgresql.org/message-id/56a52018.1030...@gmx.net

Systemd defaults to remove all IPC (including SYSV memory) when a user "fully" 
logs out. This seems to be happen when running the cron job. It seems to be a 
difference if the job is running as a “normal” user or a “system” user.

So I modified my existing postgres user to be a system user

usermod -u  mmppostgres

an now my backup is running. I cannot remember finding such a hint in the 
PostgreSQL documentation. It would be worth mentioning it.

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu






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