Re: [GENERAL] "Reverse" inheritance?

[Tim Uckun]

I have thought of doing something like a single table inheritance and it
could be done but I thought this might be a little more elegant.

On Tue, Apr 4, 2017 at 2:15 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun  wrote:
>
>> I am trying to make postgres tables work like an object hierarchy. As an
>> example I have done this.
>>
>
> ​I suspect you are barking up the wrong tree ;)
>
> You are probably better off incorporating something like the "ltree" type
> to encode the taxonomy.
>
> https://www.postgresql.org/docs/current/static/ltree.html
>
> I haven't had a chance to leverage it myself but the concept it embodies
> is solid.
>
> David J.
> ​
>

Re: [GENERAL] "Reverse" inheritance?

[David G. Johnston]

On Mon, Apr 3, 2017 at 7:07 PM, Tim Uckun  wrote:

> I am trying to make postgres tables work like an object hierarchy. As an
> example I have done this.
>

​I suspect you are barking up the wrong tree ;)

You are probably better off incorporating something like the "ltree" type
to encode the taxonomy.

https://www.postgresql.org/docs/current/static/ltree.html

I haven't had a chance to leverage it myself but the concept it embodies is
solid.

David J.
​

[GENERAL] "Reverse" inheritance?

[Tim Uckun]

I am trying to make postgres tables work like an object hierarchy. As an
example I have done this.

drop table if exists os.linux cascade;
create table os.linux
(
script_name character varying(255) NOT NULL,
script_body text,
CONSTRAINT os_linux_pkey PRIMARY KEY (script_name)
);

drop table if exists os.red_hat;

CREATE TABLE os.red_hat
(
CONSTRAINT os_red_hat_pkey PRIMARY KEY (script_name)
)INHERITS (os.linux);

drop table if exists os.debian;
CREATE TABLE os.debian
(
CONSTRAINT os_debian_pkey PRIMARY KEY (script_name)
)INHERITS (os.linux);


insert into os.linux(script_name, script_body) VALUES ('package', 'tgz' );
insert into os.linux(script_name, script_body) VALUES ('awk', 'awk' );
insert into os.debian(script_name, script_body) values( 'package', 'apt');
insert into os.red_hat(script_name, script_body) values( 'package', 'yum');

When I do  SELECT * from os.debian I would like to get two records one
where the package is 'apt' and one where the awk is 'awk'.

So the package row is overridden in the child but it inherits the parent
row.

Is there a way to do this?

Ideally I would like to have a deeper hierarchy like nix -> linux -> debian
-> ubuntu -> ubuntu_16_04

so that when I select from ubuntu_16_04 I get all the rows from all the
parent tables but properly overridden so they don't union.

Thanks.

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

[Merlin Moncure]

On Mon, Apr 3, 2017 at 4:42 PM, Kenneth Shaw  wrote:
> UTF-8 works just fine with MS SQL:
>
> ms:booktest@192.168.1.5=> select N'这是一个';
> col0
> +--+
>   这是一个
> (1 rows)

confirmed!

merlin


-- 
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] My humble tribute to psql -- usql v0.5.0

[Kenneth Shaw]

UTF-8 works just fine with MS SQL:

ms:booktest@192.168.1.5=> select N'这是一个';
col0
+--+
  这是一个
(1 rows)

You need to use a "unicode" string, as indicated here using N''. You
might be able to change the default behavior in SQL Server to treat
strings as unicode strings, by changing the default collation settings
(which is latin1 or windows' 1252 encoding or whatever it is). If
you're working with a table, when columns are N* column types, it will
work fine.


-Ken


On Tue, Apr 4, 2017 at 4:20 AM, Kenneth Shaw  wrote:
> This doesn't seem to be a problem with PostgreSQL, MySQL, Oracle, or
> SQLite3 databases. It was a problem with MSSQL in the limited check
> that I just did. I don't see any driver option for the MSSQL Go
> driver. I'll poke around and see if there's a "client encoding"
> option.
>
> -Ken
>
>
> On Tue, Apr 4, 2017 at 4:08 AM, Karsten Hilbert  
> wrote:
>> On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote:
>>
>>> It should work. What database did you try that with? I haven't tried
>>> to do heavy / extensive utf8 tests, but all of Go (including the
>>> "readline" package that is used for capturing input are native utf8.
>>> The problem is potentially a connect option in the DSN for the
>>> database needs to be manually added
>>
>> Do you "SET client_encoding" ?
>>
>> Karsten
>> --
>> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general


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

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

[Kenneth Shaw]

This doesn't seem to be a problem with PostgreSQL, MySQL, Oracle, or
SQLite3 databases. It was a problem with MSSQL in the limited check
that I just did. I don't see any driver option for the MSSQL Go
driver. I'll poke around and see if there's a "client encoding"
option.

-Ken


On Tue, Apr 4, 2017 at 4:08 AM, Karsten Hilbert  wrote:
> On Tue, Apr 04, 2017 at 03:48:16AM +0700, Kenneth Shaw wrote:
>
>> It should work. What database did you try that with? I haven't tried
>> to do heavy / extensive utf8 tests, but all of Go (including the
>> "readline" package that is used for capturing input are native utf8.
>> The problem is potentially a connect option in the DSN for the
>> database needs to be manually added
>
> Do you "SET client_encoding" ?
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

[Kenneth Shaw]

On Mon, Apr 3, 2017 at 8:51 PM, Merlin Moncure  wrote:
> Wow! this is _fantastic_.  I use "sqsh" for connecting to sql server
> -- this is already a significant improvement in many ways (based on
> playing around for around 10 minutes).  This app would fill a real
> need for me.  Here is some feedback:

Cool, I'm glad it will be useful to more than just myself.

>
> *) Does UTF8 support work?  It appears not to for me:
> => select '这是一个';
>   col0
> +--+
>   
> (1 rows)

It should work. What database did you try that with? I haven't tried
to do heavy / extensive utf8 tests, but all of Go (including the
"readline" package that is used for capturing input are native utf8.
The problem is potentially a connect option in the DSN for the
database needs to be manually added, or some other reason I'm not
aware of. However, this is 100% part/parcel to usql, so I'd like to
figure it out ASAP. I also plan to provide the \encoding equivalents
for the various databases, but just haven't gotten to that yet.

>
> *) Is there any way to disable SSL to postgres in the DSN?  I get,
> error: pq: SSL is not enabled on the server

usql pg://user:pass@host/?sslmode=disable

>
> *) Interacting with PAGER is a really essential feature

Yes, for me too! I just haven't had a chance to get to that. This is
actually a bit more complicated in general.

>
> *) Ditto \copy (to my limited understanding this would be a fairly big
> development challenge)

So, it's not "too difficult" as things go -- for pg at least, this is
built into the protocol, for the other databases, however, the issue
is that they don't necessarily have the equivalent in their protocol
or possible in the underlying Go driver. I'm likely going to do this
slightly differently than relying on the protocol version, and the
plan is to bring in support when I add in the ability to
simultaneously work with multiple connections. The point would be to
essentially write a "migration" script that would be something like:

\copy -N connection1 (SELECT * FROM mytable) to -N connection2 mytable

I'm still in the process of figuring out what this is

>
> *) Advise reserving -h command line switch for 'host', not --help

Not all the various command line options are there yet. It's better to
just use the URL style for connections.

>
> *) \g switch is not working FWICT (it outputs to stdout) -- this makes
> it hard to benchmark performance since output to display is the main
> bottlenect.  First impressions though is that the tool is pretty fast.

Yeah -- output redirection (\o) is also not available yet. This is
would have been part of v0.5.0, but I just didn't have enough time,
and wanted to get the variable stuff out. Look for this in the next
couple days.

Anyways, I really appreciate the feedback! I'm hoping this will grow
into its own project, but personally, I'm just glad I took the time
(why did I not do this 10 years ago??) to juts build out something
that works the same with every database.


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

[Alex Kliukin]

> On 30. Mar 2017, at 12:10, Moreno Andreo  wrote:

> as databases aren't built for type of dynamic scheduling that something like 
> kubernetes (or any other container management solution), due to how they 
> interact with the filesystem, network stack, and more.
> Further more, he suggests that because of this lack of integration, that if 
> you do run your database in kubernetes/docker, you are very likely to 
> encounter data corruption and data loss"
> 
> What's your advice?


I think dynamic scheduling just increases the chances of your database instance 
being killed. Your K8S cluster, if not configured properly, may decide to kill 
your master database pod when scaling down nodes in order to utilise resources 
efficiently.

If you run your PostgreSQL on K8S make sure you have a good and battle-tested 
HA setup, know your way around configuring both PostgreSQL streaming 
replication and Kubernetes cluster, and have a number of databases to manage, 
as it makes little sense to build an aircraft carrier to carry a single Cessna. 

 
Kind regards,
--
Oleksii “Alex" Kliukin

Re: [GENERAL] spin locks and starvation

[George Neuner]

On Mon, 3 Apr 2017 11:40:29 +0200, Tom DalPozzo 
wrote:

>I saw that postgresql implementation makes big use of spin locks.
>I was wondering if I should be concerned about possible starvation problem
>because I looked around and couldn't find an absolute answer about if linux
>spinlocks guarantee protection about starvation or not. I'm using ubuntu
>16.04.
>
>I've noticed no problem so far, I'm just wondering.

No form of locking can guarantee progress - starvation avoidance
requires use of a wait-free arbitration method.

Note that "wait-free" is not the same as "lock-less".  Lock-less
methods guarantee only that *some* thread can make progress, not that
all threads will make progress.  Any particular thread may starve
under lock-less arbitration.

There are a number of lock-less algorithms to choose from, but truely
wait-free algorithms are complex and difficult to implement correctly.
Outside of hard real-time systems they are quite rare.
http://www.cs.technion.ac.il/~erez/Papers/wfquque-ppopp.pdf


Spin locking is the optimal *locking* technique to use when conflicts
are expected to occur relatively often, but the period of locking is
relatively short [wrt to the unlocked period and for some respective
definitions of "relatively"].

YMMV,
George



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

[Joe Conway]

On 03/31/2017 01:58 AM, Moreno Andreo wrote:
> Il 30/03/2017 14:38, Vick Khera ha scritto:
>>
>> On Thu, Mar 30, 2017 at 6:10 AM, Moreno Andreo
>> > wrote:
>>
>> Since I'm on Google Cloud Platform, I thought it would be a good
>> idea to see what it offers. 
>>
>>
>> They currently have in beta a Postgres flavor of their cloudsql. I
>> haven't used it yet, but I plan to test it sometime in the next couple
>> of months.
> 
> I'm aware of this (and I'm about to test it too), but while this is in
> beta I need a solution to keep up with my architecture... I can't stay
> with a single server and no backup (except a snapshot of the disk made
> on a daily basis... but that's no real backup) waiting for Cloud SQL to
> get out of beta...

Take a look at:
https://github.com/CrunchyData/crunchy-containers

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Unexpected interval comparison

[Tom Lane]

Kyotaro HORIGUCHI  writes:
> Ok, the attached patch changes the result type of
> interval_cmp_value from TimeOffset(=int64) to new 128 bit
> LinearInterval. The value is hidden under the functions
> interval_eq/ge.../cmp and all other stuff seems to use the
> functions.

Looking at this now ... why isn't the INT64_AU32 macro just

#define INT64_AU32(i64) ((i64) >> 32)

?  The business with subtracting and re-adding 1 seems unnecessary, and it
also creates a risk of overflow with the minimum possible int64 value.

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] My humble tribute to psql -- usql v0.5.0

[Merlin Moncure]

On Sun, Apr 2, 2017 at 7:55 AM, Kenneth Shaw  wrote:
> Hi All,
>
> I apologize in advance if this is somewhat off-topic, but I thought I
> would inform the people (ie, psql users) about usql, a
> universal-command line tool that aims to work the same way psql does,
> but with every database (not just PostgreSQL).
>
> usql is built in Go, and as of today supports all the major databases
> (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database)
> and more! Additionally, with v0.5.0, released today, usql now has
> implemented most of the basic, and much of the advanced features of
> psql:
>
> * Interpolated variables (ie, via \set, \unset, etc.)
> * Backslash commands, and similar shell escapes/evaluation (ie, \echo
> `date` style)
> * Working with a query buffer (ie, \e, \p, \r, etc.)
> * Password (.usqlpass) / RC (.usqlrc) files
>
> If you happen to have a Go tool chain installed, you can simply install with:
>
> go get -u github.com/knq/usql
>
> Alternatively, you may download a binary release for Windows/Linux/OSX here:
>
> https://github.com/knq/usql/releases
>
> I just released usql v0.5.0 and feel that it is now ready for a wider
> audience, and I thought what better audience than those already
> familiar with the type/style of cli interface psql offers. I'm hoping
> that there are those of you on this list that might like to have a
> tool in their toolbox that works with every other database in a
> similar way to how psql does.
>
> Over the last 15 years that I've been a heavy PostgreSQL user, and I
> have likely used the psql command line for -- literally -- thousands
> of hours! Over that same period of time, I have continually found
> myself needing to reread documentation for all the various cli clients
> out there. usql is the tool I wish I had 10+ years ago.
>
> I built usql primarily due to my frustration with having to work with
> 5 different, broken CLI interfaces for all the different/major
> databases on a relatively routine basis. I am posting this here,
> because I imagine many of the other users on this list on occasion
> have had, on occasion, needed to work with other databases, but are
> stymied/annoyed/stopped/frustrated-to-no-ends with having to use
> multiple, incompatible clients.
>
> At least now, while you might be forced to use some other, inferior
> database, at least you can still hit the ground running. I appreciate
> any feedback/questions/etc you might have!
>
> And thank you all to the PostgreSQL + psql developers out there! Don't
> forget that imitation is the most sincerest form of flattery ...
> cheers!
>
> -Ken
>
> (ps: usql is brand new; it's great for doing many common tasks, minor
> to medium sized database administration tasks and development at the
> moment; in time, it'll be ready for anything, but CAVEAT USER until
> then ...)

Wow! this is _fantastic_.  I use "sqsh" for connecting to sql server
-- this is already a significant improvement in many ways (based on
playing around for around 10 minutes).  This app would fill a real
need for me.  Here is some feedback:

*) Does UTF8 support work?  It appears not to for me:
=> select '这是一个';
  col0
+--+
  
(1 rows)

*) Is there any way to disable SSL to postgres in the DSN?  I get,
error: pq: SSL is not enabled on the server

*) Interacting with PAGER is a really essential feature

*) Ditto \copy (to my limited understanding this would be a fairly big
development challenge)

*) Advise reserving -h command line switch for 'host', not --help

*) \g switch is not working FWICT (it outputs to stdout) -- this makes
it hard to benchmark performance since output to display is the main
bottlenect.  First impressions though is that the tool is pretty fast.

merlin


-- 
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] Oddity with time zones.

[Tom Lane]

Steve Rogerson  writes:
> # select (now());
>   now
> ---
>  2017-04-03 11:57:09.891043+01
> (1 row)

> sjr_local1db=# select (now() AT TIME ZONE  'UTC');
>   timezone
> 
>  2017-04-03 10:57:11.714571
> (1 row)

> sjr_local1db=# select (now() AT TIME ZONE  'UTC') AT TIME ZONE 'UTC';
>timezone
> ---
>  2017-04-03 11:57:14.088515+01
> (1 row)

> This makes no sense to me.

Looks perfectly fine from here.  You're rotating a timestamp with time
zone (displayed in your local zone, evidently GMT+1) to a timestamp
without time zone expressed in UTC, and then back to a timestamp with time
zone.  That round trip should be a no-op, barring weird corner cases.

I'd be the first to agree that the notation is pretty opaque --- why
use the same "operator" for both transformation directions? --- but
don't blame us, blame the SQL spec.

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] Unexpected interval comparison

[Kyotaro HORIGUCHI]

Hmm. It took a bit longer time than expected.

At Fri, 31 Mar 2017 13:29:24 -0400, Tom Lane  wrote in 
<10353.1490981...@sss.pgh.pa.us>
> Kyotaro HORIGUCHI  writes:
> > int128 is seen in numeric.c. It is doable in the same manner. In
> > that case it will be a bit slower on the platforms without
> > int128.
> 
> > By the way is it right that we don't assume this as a bug-fix
> > which should be done in the Pg10 dev cycle, but an improvement
> > for 11?
> 
> Well, it seems like a bug to me.  We might conclude that the fix
> is too risky to back-patch, but it's hard to make that decision
> before having a patch in hand to evaluate.

Ok, the attached patch changes the result type of
interval_cmp_value from TimeOffset(=int64) to new 128 bit
LinearInterval. The value is hidden under the functions
interval_eq/ge.../cmp and all other stuff seems to use the
functions.

For platforms without 128 bit support, int64 * 2 version of
interval_cmp_value is used.

I added separate test for the near-overflow values since just
adding such values into INTERVAL_TABLE resuted in a mess. (I ran
64-bit version by commenting-out the definition of PG_INT128_TYPE
in pg_config.h).

The attached patch is that.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
>From e8866f35b95dc09cfc570923ee9be3e3a8e93f8a Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi 
Date: Mon, 3 Apr 2017 19:41:09 +0900
Subject: [PATCH] Fix overflow during interval comparison.

The values in interval are compared by TimeOffset results of
interval_cmp_value but it is so narrow that overflows quite easily.
This patch widen the output of the function to 128 bit. For platforms
without 128 bit arithmetic, a pair of 64 bit intergers is used
instead.
---
 src/backend/utils/adt/timestamp.c  | 121 ++---
 src/include/datatype/timestamp.h   |  13 
 src/test/regress/expected/interval.out |  36 ++
 src/test/regress/sql/interval.sql  |  17 +
 4 files changed, 178 insertions(+), 9 deletions(-)

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 4be1999..c9e811d 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2289,25 +2289,116 @@ timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
 /*
  *		interval_relop	- is interval1 relop interval2
  */
-static inline TimeOffset
+#ifdef HAVE_INT128
+static inline LinearInterval
 interval_cmp_value(const Interval *interval)
 {
-	TimeOffset	span;
+	LinearInterval	span;
 
-	span = interval->time;
-	span += interval->month * INT64CONST(30) * USECS_PER_DAY;
-	span += interval->day * INT64CONST(24) * USECS_PER_HOUR;
+	span = (int128)interval->time;
+	span += (int128)interval->month * INT64CONST(30) * USECS_PER_DAY;
+	span += (int128)interval->day * INT64CONST(24) * USECS_PER_HOUR;
 
 	return span;
 }
+#else
+/*
+ * arithmetic$ 32 bit extraction from int64
+ *
+ * INT64_AU32 extracts significant 32 bit of int64 as a int64, and INT64_AL32
+ * extracts non-siginificant 32 bit as a int64. Both macros extends sign bits
+ * according to the given value.
+ */
+
+#define INT64_AU32(i64) (((i64) < 0 ? (((i64) - 1) >> 32) + 1: ((i64) >> 32)))
+#define INT64_AL32(i64) (((i64) & 0x) | ((i64) < 0 ? 0x : 0))
+
+/* Adds signed int64 value into LinearInterval variable  */
+#define LINEARINTERVAL_ADD_INT64(li, v) \
+{ \
+	int64 t = (int64)(v); \
+	int64 p = (int64)(li).lo; \
+	(li).lo += t; \
+	if (t > 0 && p > 0 && (int64)(li).lo < 0) \
+		(li).hi += 1; \
+	else if ((t < 0 && p == 0) || \
+			 (t < 0 && p < 0 && (int64)(li).lo > 0))	\
+		(li).hi -= 1; \
+}
+
+static inline LinearInterval
+interval_cmp_value(const Interval *interval)
+{
+	LinearInterval	span = {0, 0};
+	int64	dayfraction;
+	int64	days, tmp;
+
+	/* days cannot overflow here */
+	dayfraction = interval->time % USECS_PER_DAY;
+	days = interval->time / USECS_PER_DAY;
+	days += interval->month * INT64CONST(30);
+	days += interval->day;
+
+	/* we assume arithmetic shift here */
+	Assert(-1 >> 1 == -1);
+
+	/*
+	 * perform 128 bit multiplication using 64 bit variables.
+	 *
+	 *   x * y = ((x.hi << 32) + x.lo) * (((y.hi << 32) + y.lo)
+	 * = (x.hi * y.hi) << 64 +
+	 *   ((x.hi * y.lo) + (x.lo * y.hi)) << 32 +
+	 *   x.lo * y.lo
+	 */
+
+	/* We don't bother calculation results in zero */
+	if (days != 0)
+	{
+		/*
+		 * Here, the possible maximum number of days is 0xf_865ce7d8 and the
+		 * constant USECS_PER_DAY is 0x14_1dd76000. So the first and second
+		 * term can be calculated safely in 64 bit arithmetic.
+		 */
+		int64 days_u32 = INT64_AU32(days);
+		int64 days_l32 = INT64_AL32(days);
+
+		/* the first term */
+		span.hi = days_u32 * (USECS_PER_DAY >> 32);
+
+		/* the second term */
+		tmp = days_l32 * (USECS_PER_DAY >> 32)
+			+ days_u32 * (USECS_PER_DAY & 0x);
+		span.hi += INT64_AU32(tmp);
+		

[GENERAL] Oddity with time zones.

[Steve Rogerson]

# select (now());
  now
---
 2017-04-03 11:57:09.891043+01
(1 row)

sjr_local1db=# select (now() AT TIME ZONE  'UTC');
  timezone

 2017-04-03 10:57:11.714571
(1 row)

sjr_local1db=# select (now() AT TIME ZONE  'UTC') AT TIME ZONE 'UTC';
   timezone
---
 2017-04-03 11:57:14.088515+01
(1 row)



This makes no sense to me.

Steve


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

[GENERAL] spin locks and starvation

[Tom DalPozzo]

Hi,
I saw that postgresql implementation makes big use of spin locks.
I was wondering if I should be concerned about possible starvation problem
because I looked around and couldn't find an absolute answer about if linux
spinlocks guarantee protection about starvation or not. I'm using ubuntu
16.04.

I've noticed no problem so far, I'm just wondering.

Regards
Pupillo

Re: [GENERAL] Suggestion to improve select pg_reload_conf()

[Michael Paquier]

On Mon, Apr 3, 2017 at 4:39 PM, Achilleas Mantzios
 wrote:
> On 03/04/2017 10:31, Thomas Kellerer wrote:
>>
>> I would like to suggest an improvement to the select pg_reload_conf()
>> function.
>>
>> Currently this will only return true or false indicating if reloading was
>> successful.
>>
>> I think it would be a "nice-to-have" if the function would also return the
>> GUCs that have been changed, similar to what is being written to the
>> logfile.
>>
>> To not break existing code (e.g. scripts that only expect true/false),
>> this could be done through an optional boolean parameter (e.g. named
>> "verbose").
>
> To my understanding pg_reload_conf just SIGHUP's the postmaster. So it might
> not be so trivial.

Yup. But there is a workaround possible at SQL-level to know what are
the parameters that would be updated on SIGHUP by comparing the values
in pg_file_settings that are the values found in the configuration
files with the current settings applied in pg_settings.
-- 
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] Suggestion to improve select pg_reload_conf()

[Achilleas Mantzios]

On 03/04/2017 10:31, Thomas Kellerer wrote:

I would like to suggest an improvement to the select pg_reload_conf() function.

Currently this will only return true or false indicating if reloading was 
successful.

I think it would be a "nice-to-have" if the function would also return the GUCs 
that have been changed, similar to what is being written to the logfile.

To not break existing code (e.g. scripts that only expect true/false), this could be done 
through an optional boolean parameter (e.g. named "verbose").

To my understanding pg_reload_conf just SIGHUP's the postmaster. So it might 
not be so trivial.


Any thoughts?

Thomas






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



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

[GENERAL] Suggestion to improve select pg_reload_conf()

[Thomas Kellerer]

I would like to suggest an improvement to the select pg_reload_conf() function.

Currently this will only return true or false indicating if reloading was 
successful.

I think it would be a "nice-to-have" if the function would also return the GUCs 
that have been changed, similar to what is being written to the logfile. 

To not break existing code (e.g. scripts that only expect true/false), this 
could be done through an optional boolean parameter (e.g. named "verbose").

Any thoughts?

Thomas



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