RE: Mailing list archiver

Looks nice, thanks, however, I could not find select posts to this list from 20th December and 19th Dezember respectively. Kind regards Thiemo

Re: Returning Values from INSERT ON CONFLICT DO NOTHING

On Mon, Jan 1, 2018 at 11:59 PM, Jov wrote: > From https://www.postgresql.org/docs/devel/static/sql-insert.html: > >> The optional RETURNING clause causes INSERT to compute and return >> value(s) based on each row actually inserted (or updated, if an ON >> CONFLICT DO UPDATE

Re: Query error: could not resize shared memory segment

Thank you Thomas, I make it work with extra setting --shm-size=1g in my docker run script. On Wed, Jan 3, 2018 at 12:16 PM, Thomas Munro wrote: > On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh > wrote: >> And here is the result from

Re: Query error: could not resize shared memory segment

Thomas Munro writes: > On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh > wrote: >> mount | grep /dev/shm >> => shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k) > Bingo. Somehow your container tech is limiting

Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 6:13 PM, Thuc Nguyen Canh wrote: > And here is the result from postgres container: > > mount | grep /dev/shm > => shm on /dev/shm type tmpfs (rw,nosuid,nodev,noexec,relatime,size=65536k) Bingo. Somehow your container tech is limiting shared

Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 5:39 PM, Tom Lane wrote: > Thomas Munro writes: >> So you have 16GB of RAM and here we're failing to posix_fallocate() >> 50MB (actually we can't tell if it's the ftruncate() or >> posix_fallocate() call that failed, but

Re: Query error: could not resize shared memory segment

The last query explain is with random_page_cost = 3. Here is the query explain with random_page_cost = 2.5, that causes the 'shared memory segment' issue. 'Sort (cost=9255854.81..9356754.53 rows=40359886 width=64)' ' Sort Key: (to_char(b.week, 'dd-mm-'::text))' ' CTE sumorder' '->

Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 5:22 PM, Thuc Nguyen Canh wrote: > Here is the query plan of a query that causes above issue for any > random_page_cost < 3 (I keep the work_mem by default) > > 'Sort (cost=9441498.11..9542397.83 rows=40359886 width=64) (actual >

Re: Query error: could not resize shared memory segment

Thomas Munro writes: > So you have 16GB of RAM and here we're failing to posix_fallocate() > 50MB (actually we can't tell if it's the ftruncate() or > posix_fallocate() call that failed, but the latter seems more likely > since the former just creates a big hole in

Re: Query error: could not resize shared memory segment

Hi, Here is the query plan of a query that causes above issue for any random_page_cost < 3 (I keep the work_mem by default) 'Sort (cost=9441498.11..9542397.83 rows=40359886 width=64) (actual time=33586.588..33586.590 rows=4 loops=1)' ' Sort Key: (to_char(b.week, 'dd-mm-'::text))' ' Sort

Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 5:05 PM, Thuc Nguyen Canh wrote: > The dynamic_shared_memory_type is posix, the before and after values for > work_mem are ~41MB and ~64MB. > I'm using a Digital Ocean vps of 16RAM 8 Cores. > For more information, I managed to reproduce this issue

Re: Query error: could not resize shared memory segment

Hi, The dynamic_shared_memory_type is posix, the before and after values for work_mem are ~41MB and ~64MB. I'm using a Digital Ocean vps of 16RAM 8 Cores. For more information, I managed to reproduce this issue on a fresh vps after I changed the random_page_cost from 4.0 to 1.1. So that said, I

Re: Query error: could not resize shared memory segment

On Wed, Jan 3, 2018 at 1:22 PM, Thuc Nguyen Canh wrote: > I got following error when running some heavy queries > "ERROR: could not resize shared memory segment "/PostgreSQL.388782411" to > 50438144 bytes: No space left on device SQL state: 53100" > > I'm using a postgis

BDR: remove junk replication identifier

Hi all, I had many waste replication identifier (164 of 457), the env is BDR 0.9.3 and 9.4.4. I'm going to remove those junk replication identifiers, but there is no exactly how to do in docs. # select slot_name,plugin,datoid,restart_lsn from pg_replication_slots where slot_name like '%654018%';

Mailing list archiver

This project serves to organize and archive the postgres mailing list: http://pg.rsa.pub/ Let me know what you think, or if you have any suggestions. I left email addresses off the site to protect privacy. p.s: you can click messages to collapse the tree Thanks

Fwd: Query error: could not resize shared memory segment

Hello, I got following error when running some heavy queries "ERROR: could not resize shared memory segment "/PostgreSQL.388782411" to 50438144 bytes: No space left on device SQL state: 53100" I'm using a postgis 10 docker container with mounted volume on ubuntu 16 vps. Some of failed queries

Re: Need information on the tools available to upload the shape files to the postgreSQL database

If shp2pgsql doesn't run on windows, qgis might be an option? Jim On January 2, 2018 6:24:45 PM EST, "Ramamoorthi, Meenakshi" wrote: >Hi All: > >I wanted to know if there are any tools available to upload the shape >files from windows platform and then

Need information on the tools available to upload the shape files to the postgreSQL database

Hi All: I wanted to know if there are any tools available to upload the shape files from windows platform and then deposit it into PostgreSQL database on a UNIX system. Thanks and best regards Meenakshi Ramamoorthi

Re: Error creating a table

On 01/02/2018 02:38 PM, Dale Seaburg wrote: le): NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "public_rowkey" for table "ABSTRACT-SERVER_runsheet" ERROR:  relation "public_rowkey" already exists ** Error ** ERROR: relation "public_rowkey" already exists SQL

Error creating a table

I am in pgAdmin, attempting to create a table in an existing database. This table is normally created and deleted by software I have written (C#). I get this kind of error when executing the CREATE TABLE script (which is a copy of similarly named table): NOTICE: CREATE TABLE / PRIMARY KEY will

Re: Use of Port 5433 with Postgresql 9.6

Jeff, Andrew, Andreas: Thank you for your replies. Environment is Mageia 6 x86_64. I think the packagers must have set up the 5433 port (although I notice the documentation for pg_ctl contains several examples using 5433). Anyway, I am relieved that 5432 is still regarded as the main port, and

Re: Select for update / deadlock possibility?

Jeff Janes writes: > On Tue, Jan 2, 2018 at 3:22 AM, Durumdara wrote: >> Is "select for update" atomic (as transactions) or it isn't? > It is atomic, but you do have to worry about deadlocks. I think by "atomic" the OP intends "all the row locks are

Re: 5 USD for PostgreSQL books at PacktPub

2018-01-02 16:13 GMT+01:00 Alexander Farber : > Hello fellow PostgreSQL users, > > there is currently a sale for books at > https://www.packtpub.com/tech/PostgreSQL > > I am not affiliated in any way with them, it is just a "heads up". > > For myself I have pre-ordered

5 USD for PostgreSQL books at PacktPub

Hello fellow PostgreSQL users, there is currently a sale for books at https://www.packtpub.com/tech/PostgreSQL I am not affiliated in any way with them, it is just a "heads up". For myself I have pre-ordered https://www.packtpub.com/big-data-and-business-intelligence/mastering-postgresql-10

Re: Select for update / deadlock possibility?

Dear Jeff! So. I start this question from more far. I need to protect some resources. All modifications started with StartTransaction. Then I try to lock the articles by ids (to prevents other client's modifications). After that I insert / modify needed data. Then I commit or rollback. The locks

Re: Select for update / deadlock possibility?

On Tue, Jan 2, 2018 at 3:22 AM, Durumdara wrote: > Dear Members! > > I have to ask something that not clear for me from description, and I > can't simulate it. > > Is "select for update" atomic (as transactions) or it isn't? > > I want to avoid the deadlocks. > > If it's

Re: MSSQL compatibility layer

James Harper schrieb am 02.01.2018 um 12:24: Right now the code is pretty rough. I've only implemented bits and pieces to a proof of concept stage so it's definitely way off being useful. At the moment the following pieces are in place: . BackgroundWorker listening on port 1433 (hardcoded - I

Re: Select for update / deadlock possibility?

Hello! Read Committed. I extend the example: the concurrent connections are in transactions. begin select ... for update; end; Regards dd 2018-01-02 12:31 GMT+01:00 Rakesh Kumar : > > Shouldn't isolation level also matter ? What is the isolation level you >

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

Also got this nice suggestion at https://stackoverflow.com/q/48050127/165071 - SELECT COALESCE( json_object_agg( gid, array_to_json(y) ), '{}'::json) FROM( SELECT gid, array_agg( json_build_object( 'uid', uid, 'created',

Re: Slow system due to ReorderBufferGetTupleBuf?

On 01/01/2018, 17:45, "Peter Geoghegan" wrote: 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:

Re: PGPool encrypted connections from Application

> Does PGPool allow encrypted connections from Application? i.e. ssl > encrypted? My company wants to encrypt all traffic in the environment > and so enabled https connections between applications/load balance. I > am not sure about the PGPool. If application sends encrypted > connection to PGPool

Re: Select for update / deadlock possibility?

Shouldn't isolation level also matter ?  What is the isolation level you are using ?

MSSQL compatibility layer

Hi All, A few years ago I got bored over summer and wrote some code to act as an MSSQL compatibility layer to postgres. Mostly just to see if such a thing could work. The idea is that I can point the MSSQL Management Studio at the server running postgres and it looks like I am talking to an

Re: Returning Values from INSERT ON CONFLICT DO NOTHING

>From https://www.postgresql.org/docs/devel/static/sql-insert.html: > The optional RETURNING clause causes INSERT to compute and return > value(s) based on each row actually inserted (or updated, if an ON > CONFLICT DO UPDATE clause was used). This is primarily useful for > obtaining values that