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 writes: > > the document

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 (positive-east-of-Greenwich) everywhere ex

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 13:00:0

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 > finished the full output

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 writes: > The out-of

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

Yuri Budilov writes: > The out-of-memory error happens if I also retrieve another JSON Column like > so: > CREATE TABLE csnbi_stg.junk4 > AS > SELECT >json_rmq->>'totalSize' as totalSize, -- this plus array below causes > out of memory error >json_array_elements(json_rmq -> 'ord

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

the plot thickens! I have more information. The out-of-memory error happens if I also retrieve another JSON Column like so: -- fails CREATE TABLE csnbi_stg.junk4 AS SELECT json_rmq->>'totalSize' as totalSize, -- this plus array below causes out of memory error json_array_elemen

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

On 12/3/2017 3:18 PM, Yuri Budilov wrote: Posted on Stack Overflow, sadly no replies, so trying here ... ,,, why did you email me personally ? -- john r pierce, recycling bits in santa cruz

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 t

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. https://www.postgresq

Re: large numbers of inserts out of memory strategy

On 2017-11-30 14:27:58 -0600, Ted Toth wrote: > On Thu, Nov 30, 2017 at 11:40 AM, Peter J. Holzer wrote: > > On 2017-11-30 08:43:32 -0600, Ted Toth wrote: > >> One thing that is unclear to me is when commits occur while using psql > >> would you know where in the docs I can find information on thi

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

On Sun, 2017-12-03 at 23:18 +, Yuri Budilov wrote: > 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 c

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 it has about ~150,000 array

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 abou

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 >>> >>> Near the end (page 2

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 refer

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 mor

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