Re: invalid memory alloc request size 576460752438159360

2018-01-01 Thread Ibrahim Edib Kokdemir 
hi Peter,
today, we took the dump of database and restored to another empty cluster
and run the queries on it for test purposes, no problem at all. All errors
are gone.

Selecting a JSON object of arrays from a PostgreSQL table

2018-01-01 Thread Alexander Farber 
Hello and happy new year!

I have prepared a simple SQL Fiddle demonstrating my problem:
http://sqlfiddle.com/#!17/2c9c5/1

In a two-player game I store user chats in a table:

CREATE TABLE chat(
gid integer,/* game id */
uid integer,/* user id */
created timestamptz,
msg text
);

Here I fill the table with a simple test data:

INSERT INTO chat(gid, uid, created, msg) VALUES
(10, 1, NOW() + interval '1 min', 'msg 1'),
(10, 2, NOW() + interval '2 min', 'msg 2'),
(10, 1, NOW() + interval '3 min', 'msg 3'),
(10, 2, NOW() + interval '4 min', 'msg 4'),
(10, 1, NOW() + interval '5 min', 'msg 5'),
(10, 2, NOW() + interval '6 min', 'msg 6'),
(20, 3, NOW() + interval '7 min', 'msg 7'),
(20, 4, NOW() + interval '8 min', 'msg 8'),
(20, 4, NOW() + interval '9 min', 'msg 9');

And can fetch the data by running the SELECT query:

SELECT ARRAY_TO_JSON(
  COALESCE(ARRAY_AGG(ROW_TO_JSON(x)),
   array[]::json[])) FROM (
SELECT
gid,
uid,
EXTRACT(EPOCH FROM created)::int AS created,
msg
FROM chat) x;

which returns me a JSON-array:

[{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
 {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
 {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
 {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
 {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
 {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
 {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
 {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
 {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]

However I would like to use "gid" as JSON object properties and the rest
data as values in that object:

{"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
{"uid":2,"created":1514813103,"msg":"msg 2"},
{"uid":1,"created":1514813163,"msg":"msg 3"},
{"uid":2,"created":1514813223,"msg":"msg 4"},
{"uid":1,"created":1514813283,"msg":"msg 5"},
{"uid":2,"created":1514813343,"msg":"msg 6"}],

 "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
{"uid":4,"created":1514813463,"msg":"msg 8"},
{"uid":4,"created":1514813523,"msg":"msg 9"}]}

Is that please doable by using the PostgreSQL JSON functions?

Thank you
Alex

Re: Selecting a JSON object of arrays from a PostgreSQL table

2018-01-01 Thread Ivan E. Panchenko 

Hi Alex!

Why not:

select json_object_agg(gid, y) from
    (
 select gid, jsonb_agg(row_to_json(chat)) y
 from chat
 group by gid
    ) x;


Regards,

Ivan

01.01.2018 16:41, Alexander Farber пишет:

Hello and happy new year!

I have prepared a simple SQL Fiddle demonstrating my problem: 
http://sqlfiddle.com/#!17/2c9c5/1 


In a two-player game I store user chats in a table:

CREATE TABLE chat(
    gid integer,            /* game id */
    uid integer,            /* user id */
    created timestamptz,
    msg text
);

Here I fill the table with a simple test data:

INSERT INTO chat(gid, uid, created, msg) VALUES
    (10, 1, NOW() + interval '1 min', 'msg 1'),
    (10, 2, NOW() + interval '2 min', 'msg 2'),
    (10, 1, NOW() + interval '3 min', 'msg 3'),
    (10, 2, NOW() + interval '4 min', 'msg 4'),
    (10, 1, NOW() + interval '5 min', 'msg 5'),
    (10, 2, NOW() + interval '6 min', 'msg 6'),
    (20, 3, NOW() + interval '7 min', 'msg 7'),
    (20, 4, NOW() + interval '8 min', 'msg 8'),
    (20, 4, NOW() + interval '9 min', 'msg 9');

And can fetch the data by running the SELECT query:

SELECT ARRAY_TO_JSON(
  COALESCE(ARRAY_AGG(ROW_TO_JSON(x)),
           array[]::json[])) FROM (
SELECT
    gid,
    uid,
    EXTRACT(EPOCH FROM created)::int AS created,
    msg
FROM chat) x;

which returns me a JSON-array:

[{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
 {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
 {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
 {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
 {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
 {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
 {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
 {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
 {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]

However I would like to use "gid" as JSON object properties and the 
rest data as values in that object:


{"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
        {"uid":2,"created":1514813103,"msg":"msg 2"},
        {"uid":1,"created":1514813163,"msg":"msg 3"},
        {"uid":2,"created":1514813223,"msg":"msg 4"},
        {"uid":1,"created":1514813283,"msg":"msg 5"},
        {"uid":2,"created":1514813343,"msg":"msg 6"}],

 "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
        {"uid":4,"created":1514813463,"msg":"msg 8"},
        {"uid":4,"created":1514813523,"msg":"msg 9"}]}

Is that please doable by using the PostgreSQL JSON functions?

Thank you
Alex

Re: Selecting a JSON object of arrays from a PostgreSQL table

2018-01-01 Thread Alexander Farber 
Hi Ivan,

On Mon, Jan 1, 2018 at 3:34 PM, Ivan E. Panchenko <
i.panche...@postgrespro.ru> wrote:

>
> select json_object_agg(gid, y) from
> (
>  select gid, jsonb_agg(row_to_json(chat)) y
>  from chat
>  group by gid
> ) x;
>
>
> 01.01.2018 16:41, Alexander Farber пишет:
>
>>
>> I have prepared a simple SQL Fiddle demonstrating my problem:
>> http://sqlfiddle.com/#!17/2c9c5/1
>>
>>
that is great, thank you!

Slow system due to ReorderBufferGetTupleBuf?

2018-01-01 Thread Martin Moore 
Postgres v10 on Debian stretch

I’m suffering from an occasionally very slow system. A few weeks ago someone 
mentioned using perf. I’ve installed this and caught the system during a slow 
period. It shows the following as the top cpu users:

9.09%  postgres  [.] ReorderBufferGetTupleBuf
6.14%  postgres  [.] ReorderBufferReturnChange



When ReorderBufferReturnChange is no longer running:

14.35%  postgres  [.] ReorderBufferGetTupleBuf


Can someone shed some light on this and advise how to prevent it reoccurring?


Cheers,

Martin.

Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Graeme 
If the default port for v9.6 is 5433, why does the utility pg_isready 
still default to searching for 5432?


Graeme

Re: Slow system due to ReorderBufferGetTupleBuf?

2018-01-01 Thread Peter Geoghegan 
On Mon, Jan 1, 2018 at 8:56 AM, Martin Moore  wrote:
> Can someone shed some light on this and advise how to prevent it reoccurring?

You're using v10, which has these two commits:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=58b25e98106dbe062cec0f3d31d64977bffaa4af

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9fab40ad32efa4038d19eaed975bb4c1713ccbc0

Unfortunately, per the commit message of the first commit, it doesn't
look like the tuple allocator uses any new strategy, at least until
this v11 commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4ccc1cef5a04cc054af83bc4582a045d5232cb3

My guess is that that would make a noticeable difference, once v11
becomes available. Could you test this yourself by building from the
master branch?

-- 
Peter Geoghegan

Re: Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Karsten Hilbert 
On Mon, Jan 01, 2018 at 05:42:28PM +, Graeme wrote:

> If the default port for v9.6 is 5433,

It is not.

> why does the utility pg_isready still
> default to searching for 5432?

Which is why.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Andreas Kretschmer 
On 1 January 2018 18:42:28 CET, Graeme  wrote:
>If the default port for v9.6 is 5433, why does the utility pg_isready 
>still default to searching for 5432?

The default port for PostgreSQL is 5432. You can use option -p or --port to 
specify an other Port. You can also set Environment-varibles, please see our 
fine documention (libpq).

Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Jeff Janes 
On Mon, Jan 1, 2018 at 12:42 PM, Graeme  wrote:

> If the default port for v9.6 is 5433, why does the utility pg_isready
> still default to searching for 5432?


The Ubuntu packages use 5433 if you already have something (either a
different packaged version, or an unpackaged system) running on 5432.  That
does not change the compiled-in default, but it will make a shell wrapper
around pg_isready which causes it to look at the right port, as long as you
invoked the correct pg_isready (the one that came with the package)

Cheers,

Jeff

Re: Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Paul Jungwirth 

On 01/01/2018 10:40 AM, Jeff Janes wrote:

The Ubuntu packages use 5433 if you already have something (either a
different packaged version, or an unpackaged system) running on 5432.


Also on Ubuntu lots of the Postgres utilities will obey an envvar like 
`PGCLUSTER=9.5/main` to let you choose which version of 
psql/pg_dump/etc. I don't know about pg_isready but it's worth a try. 
Even if pg_isready doesn't follow that pattern, it is probably easy to 
"teach" it. Here is how I did a similar thing for pg_config:


https://stackoverflow.com/questions/30143046/pg-config-shows-9-4-instead-of-9-3/43403193#43403193

Yours,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Returning Values from INSERT ON CONFLICT DO NOTHING

2018-01-01 Thread Igal Sapir 
Hello,

I want to use the "UPSERT" syntax for returning an ID if it exists, or
inserting a record and returning the new ID if it does not exist.

INSERT INTO users(email, name)
  VALUES('u...@domain.tld', 'User')
  ON CONFLICT (email) DO NOTHING
  RETURNING user_id, (xmax::text::int > 0) as existed;

When an email address does not exist then it works fine and I get the new
user_id, but when it does exist, I get no results at all.

How can I get the results in case of a CONFLICT?

Thanks,


Igal

Re: Returning Values from INSERT ON CONFLICT DO NOTHING

2018-01-01 Thread Igal Sapir 
It seems that if I do a simple update it resolves my issue:

INSERT INTO users(email, name)
  VALUES('u...@domain.tld', 'User')
  ON CONFLICT (email) DO UPDATE
SET email = excluded.email  -- users.email works too, not sure if makes
a difference
  RETURNING user_id, (xmax::text::int > 0) as existed;

But if anyone has a better solution then I'd love to hear it.

Thanks,


Igal

On Mon, Jan 1, 2018 at 11:07 PM, Igal Sapir  wrote:

> Hello,
>
> I want to use the "UPSERT" syntax for returning an ID if it exists, or
> inserting a record and returning the new ID if it does not exist.
>
> INSERT INTO users(email, name)
>   VALUES('u...@domain.tld', 'User')
>   ON CONFLICT (email) DO NOTHING
>   RETURNING user_id, (xmax::text::int > 0) as existed;
>
> When an email address does not exist then it works fine and I get the new
> user_id, but when it does exist, I get no results at all.
>
> How can I get the results in case of a CONFLICT?
>
> Thanks,
>
>
> Igal
>