Re: PGSQL 11.4: shared_buffers and /dev/shm size

[Jean Louis]

* Konstantin Malanchev  [2019-07-09 11:51]:
> Hello,
> 
> I'm running PostgreSQL 11.4 on Linux 4.12.14 and I see the following issue 
> while executing single one query:
> ERROR:  could not resize shared
> memory segment "/PostgreSQL.1596105766" to 536870912 bytes: No space left on 
> device
> 
> In my postgresql.conf I set sharred_buffers=256MB, I see that it is applied:
> SHOW shared_buffers;
>  shared_buffers
> 
>  256MB
> 
> At the same time during the query execution, I see a lot of files in /dev/shm 
> with the total size more than 256MB
> 
> ls -lh /dev/shm
> 
> How can I configure limit for total shared memory size?

The limit is mostly set by the memory, as /dev/shm
is like virtual memory or RAM disk.

Increase the RAM.

Jean

Re: PGSQL 11.4: shared_buffers and /dev/shm size

[Jean Louis]

* Konstantin Malanchev  [2019-07-09 12:10]:
> Hello Jean,
> 
> I have 8 GB RAM and /dev/shm size is 4GB, and there is no significant memory 
> usage by other system processes. I surprised that Postgres uses more space in 
> /dev/shm than sharred_buffers parameter allows, probably I don't understand 
> what this parameter means.
> 
> I have no opportunity to enlarge total RAM and probably this query requires 
> too much RAM to execute. Should Postgres just use HDD as temporary storage in 
> this case?

That I cannot know. I know that /dev/shm could
grow as much as available free RAM.

Jean

how to concat/concat_ws all fields without braces

[Jean Louis]

Hello,

I have tried doing something like:

SELECT concat_ws(' ', table.*) FROM table;

and if I do that way, it is essentially same as 

SELECT concat(table.*) FROM table;

and I get the items in braces like (1,something).

Why do I get it in braces?

Is there a way without specifying specific fields
to get all items concatenated without braces?

I would prefer conat_ws option.

Jean

Re: how to concat/concat_ws all fields without braces

[Jean Louis]

Dear Pavel,

Ahoj.

* Pavel Stehule  [2019-06-15 11:37]:
> you can write own function that will do what you want
> 
> create or replace function rec_concat_fields(record, text)
> returns text as $$
>   begin
> return string_agg(value, '|') from json_each_text(row_to_json($1));
>   end
> $$ language plpgsql;
> 
> postgres=# select rec_concat_fields(foo.*, '*') from foo;
> ┌───┐
> │ rec_concat_fields │
> ╞═══╡
> │ ahoj|svete│
> └───┘
> (1 row)
> 

I have tried that one, not working quite;

create or replace function concat_fields(record, text)
returns text as $$
  begin
return string_agg(value, ' ') from json_each_text(row_to_json($1));
  end
$$ language plpgsql;
rcdbusiness$# ERROR:  PL/pgSQL functions cannot accept type record

Jean

Re: how to concat/concat_ws all fields without braces

[Jean Louis]

Dear Thomas,

* Thomas Kellerer  [2019-06-15 11:37]:
> Jean Louis schrieb am 15.06.2019 um 13:19:
> > I have tried doing something like:
> > 
> > SELECT concat_ws(' ', table.*) FROM table;
> > 
> > and if I do that way, it is essentially same as 
> > 
> > SELECT concat(table.*) FROM table;
> > 
> > and I get the items in braces like (1,something).
> > 
> > Why do I get it in braces?
> > 
> > Is there a way without specifying specific fields
> > to get all items concatenated without braces?
> > 
> > I would prefer conat_ws option.
> 
> you can use the json functions for that:
> 
>   select (select string_agg(x.v, ',') from jsonb_each_text(to_jsonb(t)) as 
> x(k,v)) as all_columns
>   from the_table t;

Danke, that works well. I did not know it works
through Jansson exports. I hope it is fast enough.

Jean