Re: [GENERAL] "Reverse" inheritance?
I agree with the barking up the wrong tree, building a physical tree in tables doesn't sound right given that you will have to create a new branch in the tree when a new version/variation of ubuntu comes out. Also think about how you are going to do basic queries like listing all known unix variants; if that is hidden in the table names then you'll have to issue DDL queries to do the work of SELECT queries, which just sounds wrong to me. I'd go for a tree, possibly using recursive CTE's to dig it. On 2017-04-04 05:19, Tim Uckun wrote: 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. -- 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] "Reverse" inheritance?
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?
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?
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
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
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
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
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
Re: [GENERAL] My humble tribute to psql -- usql v0.5.0
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
> 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
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
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 >> mailto:moreno.and...@evolu-s.it>> 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
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
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.
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
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); + LINEARINTERVAL_ADD_INT64(span, INT64_AL32(tmp) << 32); + + /* the third term */ + LINEARINTERVAL_ADD_INT64
[GENERAL] Oddity with time zones.
# 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
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()
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()
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()
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