Re: ISO8601 vs POSIX offset clarification

To be more specific, I expected the output of both these queries to be the same. # select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone '+11:00'; timezone - 2017-11-30 13:00:00 # select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at

Re: ISO8601 vs POSIX offset clarification

Sorry I didn't mean for it to come out as a complaint, just that I am confused since the result of the SQL query was not what I expected. I expected +11:00 to be 11 hours east of UTC which wasn't the case. On 4 December 2017 at 13:55, Tom Lane wrote: > Bharanee Rathna

Re: ISO8601 vs POSIX offset clarification

Bharanee Rathna writes: > the documentation around how numeric offsets are parsed from strings is a > bit confusing, are they supposed to be treated as ISO8601 or POSIX ? Our documentation about this says clearly that Postgres considers offsets to be ISO

ISO8601 vs POSIX offset clarification

Hi, the documentation around how numeric offsets are parsed from strings is a bit confusing, are they supposed to be treated as ISO8601 or POSIX ? e.g. select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone '+11:00'; timezone - 2017-11-30

Re: JSON out of memory error on PostgreSQL 9.6.x

On Mon, Dec 4, 2017 at 9:39 AM, Tom Lane wrote: > Ah. The problem here is that "json_rmq->>'totalSize'" leaks some memory > on each execution, and it's executed again for each row produced by the > json_array_elements() SRF, and the memory can't be reclaimed until we've >

Re: JSON out of memory error on PostgreSQL 9.6.x

thank you, I will look into the work-around ! From: Tom Lane Sent: Monday, 4 December 2017 11:39 AM To: Yuri Budilov Cc: rob stone; pgsql-general@lists.postgresql.org Subject: Re: JSON out of memory error on PostgreSQL 9.6.x Yuri Budilov

Re: JSON out of memory error on PostgreSQL 9.6.x

On 12/3/2017 3:18 PM, Yuri Budilov wrote: |CREATETABLEX ASSELECTjson_array_elements(json_rmq ->'orders'::text)ASorderFROMtable_name WHEREblah;| I get out of memory error. are you sure thats a postgres error ?  are you doing this in psql, or what sort of application environment ? how many

Re: JSON out of memory error on PostgreSQL 9.6.x

hello good people it is *not* JSONB, just plain JSON the version 9.6.3 and running inside AWS RDS PostgreSQL (DBaaS) the machine size is just 1 GB RAM and 1 CPU, is it a called "micro" size AWS RDS instance, we use it for DEV we have also reproduced it on 2 CPU 8 GB RAM instance, FWIW. It

Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

On 2017-11-30 15:49:28 -0800, Alan Hodgson wrote: > On Thu, 2017-11-30 at 22:59 +, Ben Nachtrieb wrote: > Thank you! Solution: build them from source on the server? > > Well, it would be more maintainable to find a source for packages built for > your particular OS.

Re: JSON out of memory error on PostgreSQL 9.6.x

Yuri Budilov writes: > Posted on Stack Overflow, sadly no replies, so trying here > CREATE TABLE X AS > SELECT json_array_elements(json_rmq -> 'orders'::text) AS order > FROM table_name > WHERE blah; > I get out of memory error. > The JSON column is about ~5 MB and

JSON out of memory error on PostgreSQL 9.6.x

Posted on Stack Overflow, sadly no replies, so trying here CREATE TABLE X AS SELECT json_array_elements(json_rmq -> 'orders'::text) AS order FROM table_name WHERE blah; I get out of memory error. Is there anything I can do to unpack the above? The JSON column is about ~5 MB and it has

Re: building a server

> On 03 Des. 2017, at 23:39 , Rob Sargent wrote: > On 12/03/2017 02:15 PM, John R Pierce wrote: >> On 12/3/2017 12:47 PM, Gmail wrote: >>> So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf >>>

Re: building a server

On 12/3/2017 1:39 PM, Rob Sargent wrote: Granted! I suppose I'm looking for confirmation/correction on believing that for the server to make use of multiple cores is more dependent on the nature of the queries handled.  Concurrency should not be a huge problem for this project, though I've put

Re: building a server

On 12/03/2017 02:15 PM, John R Pierce wrote: On 12/3/2017 12:47 PM, Gmail wrote: So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf Near the end (page 24) I spotted: Heavy use of server-side functions might generate significant CPU load Just want to confirm that this

Re: building a server

On 12/3/2017 12:47 PM, Gmail wrote: So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf Near the end (page 24) I spotted: Heavy use of server-side functions might generate significant CPU load Just want to confirm that this referring to workload on the mainly-single-threaded

building a server

So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf Near the end (page 24) I spotted: Heavy use of server-side functions might generate significant CPU load Just want to confirm that this referring to workload on the mainly-single-threaded server not an advocacy for

Re: Partition pruning / agg push down for star schema in pg v11

Adding partitioning on the dim tables, with the same keys as those used in the fact table, gives any star schema a good chance to use Partition Wise Join / Aggregate plans. Will test it soon Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html