Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jul 9, 2019 at 9:04 PM Peter Geoghegan wrote: > ISTM that the simplest explanation here is that index fragmentation > (and even index size) is a red herring, and the real issue is that > you're suffering from problems similar to those that are described in > these old threads: > >

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jul 9, 2019 at 3:18 PM John Lumby wrote: > > Or, it could be that range scan performance benefitted from reduced > > fragmentation, > > > > Yes, I think so. ISTM that the simplest explanation here is that index fragmentation (and even index size) is a red herring, and the real issue is

Re: Restoring a database restores to unexpected tablespace

On 7/10/19 2:56 AM, Alex Williams wrote: Hi, Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other ways

Re: number of concurrent writes that are allowed for database

Any advice is appreciated, thank you, guys!!! Sent from my iPhone On Jul 9, 2019, at 3:56 PM, Julie Nishimura mailto:juliez...@hotmail.com>> wrote: Hello guys, How do I determine the number of concurrent writes that are allowed for some databases? Is it max of connections parameter? Is there

Re: how to return rows of data via function written by language C strict

On Tue, Jul 9, 2019 at 1:00 PM Christoph Bilz wrote: > Hello, > > i want write functions like this: > > CREATE FUNCTION foo(text) returns real as '/foo.dll', 'foo' LANGUAGE C > STRICT;" > CREATE FUNCTION foo2(text) returns table(c1 text, c2 int) as > '/foo2.dll', 'foo' LANGUAGE C STRICT; > >

Re: how to return rows of data via function written by language C strict

On 7/10/19 5:00 AM, Christoph Bilz wrote: Hello, i want write functions like this: |CREATEFUNCTIONfoo(text)returns real as'/foo.dll','foo'LANGUAGE C STRICT;" CREATE FUNCTION foo2(text) returns table(c1 text, c2 int) as '/foo2.dll', 'foo' LANGUAGE C STRICT;| So far, so clear. I don't want to

Re: pg_dump and search_path

On 7/9/19 4:24 PM, Igal @ Lucee.org wrote: On 7/9/2019 10:45 AM, Adrian Klaver wrote: On 7/9/19 7:41 AM, Igal @ Lucee.org wrote: On 7/9/2019 7:02 AM, Adrian Klaver wrote: On 7/8/19 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;     search_path

Re: pg_dump and search_path

On 7/9/2019 10:45 AM, Adrian Klaver wrote: On 7/9/19 7:41 AM, Igal @ Lucee.org wrote: On 7/9/2019 7:02 AM, Adrian Klaver wrote: On 7/8/19 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;     search_path --   "staging,

number of concurrent writes that are allowed for database

Hello guys, How do I determine the number of concurrent writes that are allowed for some databases? Is it max of connections parameter? Is there a way to determine how many we are currently using on average? Thank you, julie

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

> From: Peter Geoghegan > Sent: July 9, 2019 5:15 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Tue, Jul 9, 2019 at 12:29 PM John Lumby wrote: > > I was not thinking of a new command, just an extension of the existing

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jul 9, 2019 at 12:29 PM John Lumby wrote: > I was not thinking of a new command, just an extension of the existing > REINDEX > which would apply a fillfactor equal to current average page density, > by adding a preliminary step to sample that first. That would be a very different thing

Re: execute_values

On 7/9/19 11:04 AM, Weatherby,Gerard wrote: ​Can I use pyscopy2.extras.execute_values with a prepared statement? I don't think so as you cannot pass in VALUES(): cur.execute('prepare pt(integer, varchar, boolean) AS INSERT INTO prepare_test values ($1, $2, $3)') execute_values(cur,

how to return rows of data via function written by language C strict

Hello, i want write functions like this: CREATE FUNCTION foo(text) returns real as '/foo.dll', 'foo' LANGUAGE C STRICT;" CREATE FUNCTION foo2(text) returns table(c1 text, c2 int) as '/foo2.dll', 'foo' LANGUAGE C STRICT; So far, so clear. I don't want to return one scalar value or SETOF smth,

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

> From: Peter Geoghegan > Sent: July 9, 2019 3:01 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Tue, Jul 9, 2019 at 11:27 AM John Lumby wrote: > > And the point of the REINDEX at that point (below) is to remove dead tuple

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jul 9, 2019 at 11:27 AM John Lumby wrote: > And the point of the REINDEX at that point (below) is to remove dead tuple > keys-tids > and reorganize those split pages back into physical order without losing the > freespace. VACUUM already removes the tuples, accounting for all

Advice on Best BCP Plan

Dear Team, We have a production database with two hot standby's at two different locations. Let's say A (production) has two standby's (B). Now we are planning to do a BCP activity. What would be the best plan to do this activity? As I have two standby's configured, is shutting down one

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

And the point of the REINDEX at that point (below) is to remove dead tuple keys-tids and reorganize those split pages back into physical order without losing the freespace. > From: Peter Geoghegan > Sent: July 9, 2019 1:47 PM > Subject: Re: REINDEX : new parameter to preserve current average

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

> From: Peter Geoghegan > Sent: July 9, 2019 1:47 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Tue, Jul 9, 2019 at 10:31 AM John Lumby wrote: > > Yes, I see that. But surely "making splits occur less often" is a

execute_values

?Can I use pyscopy2.extras.execute_values with a prepared statement? -- Gerard Weatherby| Application Architect NMRbox | Department of Molecular Biology and Biophysics | UConn Health 263 Farmington Avenue, Farmington, CT 06030-6406 Phone: 860 679 8484 uchc.edu

Restoring a database restores to unexpected tablespace

Hi, Can someone point me in the right direction for this issue we are having -- our goal is to dump a database that is currently on a tablespace named data2 that we want to restore on the same server but on tablespace pg_default -- we tried other ways like: ALTER DATABASE "[database_name]" SET

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jul 9, 2019 at 10:31 AM John Lumby wrote: > Yes, I see that. But surely "making splits occur less often" is a > desirable > objective in itself, is it not? And I believe that a parameter to > preserve the "steady-state" > density in high-traffic indexes would help achieve that

Re: pg_dump and search_path

On 7/9/19 7:41 AM, Igal @ Lucee.org wrote: On 7/9/2019 7:02 AM, Adrian Klaver wrote: On 7/8/19 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;     search_path --   "staging, transient, pg_catalog" (1 row) I ran

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

> From: Peter Geoghegan > Sent: July 8, 2019 1:39 PM > Subject: Re: REINDEX : new parameter to preserve current average leaf density > as new implicit FILLFACTOR > > On Mon, Jul 8, 2019 at 9:23 AM John Lumby wrote: > > > Although a welcome improvement, I think it is not enough to justify >

Re: pg_dump and search_path

On Tue, Jul 9, 2019 at 8:48 AM Igal @ Lucee.org wrote: > David, > > On 7/9/2019 7:49 AM, David G. Johnston wrote: > > On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org wrote: > >> search_path is not set int he config, but rather with ALTER DATABASE SET >> search_path TO ... but I have executed

Re: pg_dump and search_path

David, On 7/9/2019 7:49 AM, David G. Johnston wrote: On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org > wrote: search_path is not set int he config, but rather with ALTER DATABASE SET search_path TO ... but I have executed that prior to the RESTORE on the

Re: pg_dump and search_path

On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org wrote: > search_path is not set int he config, but rather with ALTER DATABASE SET > search_path TO ... but I have executed that prior to the RESTORE on the > target database. Would it make a difference if I set it in the config? > What is your

Re: pg_dump and search_path

On 7/9/2019 7:02 AM, Adrian Klaver wrote: On 7/8/19 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;     search_path --   "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only reference in

Re: SuperUser permission denied when creating table.

Dave Hughes writes: > I recently noticed that my default superuser "postgres" cannot create > tables in new schemas I create, but can in some existing ones. > So as the postgres user I ran: > afleads=# create schema mytest; > CREATE SCHEMA > afleads=# create table mytest.test (col1 int); >

Re: pg_dump and search_path

On 7/9/19 6:28 AM, Ron wrote: Then should search_path be set at the end of pg_restore? 1) That would be to late for anything happening in the restore. 2) The: SELECT pg_catalog.set_config('search_path', '', false); only applies to the restore session. After the restore the search_path

Re: SuperUser permission denied when creating table.

On 9/7/19 4:50 μ.μ., Dave Hughes wrote: Sure thing.  I attached it as a txt file.  Thanks for the help! PostgreSQL version? On Tue, Jul 9, 2019 at 9:29 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote: On 9/7/19 3:31 μ.μ., Dave Hughes wrote: > > Has anyone ran

Re: pg_dump and search_path

On 7/8/19 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;    search_path --  "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only reference in the output to search_path is:   SELECT

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

> I think it could fail that way for two reasons: /dev/shm size limit > (mount option, which I think you are saying you have set to 4GB?), or > your system ran out of RAM +swap. df /dev/shm Filesystem 1K-blocks Used Available Use% Mounted on shm 4194304 351176 3843128 9%

Re: pg_dump and search_path

On 7/9/19 2:22 AM, Laurenz Albe wrote: On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path; search_path -- "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only

Re: SuperUser permission denied when creating table.

On 9/7/19 3:31 μ.μ., Dave Hughes wrote: Has anyone ran across something like this before? can you show your pg_dumpall -x --schema-only ? -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt

Logical Replication for Very Large Databases

Hi friends, We have a relatively large database (~5TB) receiving very frequent INSERTs/UPDATEs, and we’d like to create a logical replica of it. To put things in perspective, we often generate up to 10 WAL-segments (16MB each) per second (wal_level=replica). We successfully set up logical

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

On Wed, Jul 10, 2019 at 12:27 AM Konstantin Malanchev wrote: > I'm executing the query with smaller work_mem, it will take some time. But I > still confused why it used all /dev/shm (4GB) and fails with "no space left" > error while work_mem = 512MB. I think it could fail that way for two

SuperUser permission denied when creating table.

I recently noticed that my default superuser "postgres" cannot create tables in new schemas I create, but can in some existing ones. So as the postgres user I ran: afleads=# create schema mytest; CREATE SCHEMA afleads=# create table mytest.test (col1 int); ERROR: permission denied for schema

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

Thank you! > For example, if you have one Parallel Hash Join in your plan, it could > allocate up to 512MB * 3 of shared memory (3 = leader process + 2 > workers). I'm executing the query with smaller work_mem, it will take some time. But I still confused why it used all /dev/shm (4GB) and

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

On Tue, Jul 9, 2019 at 11:11 PM Konstantin Malanchev wrote: > Thank you for explanation. work_mem = 512MB and > max_parallel_workers_per_gather = 2 and I run only one Postgres instance and > only one query. EXPLAIN shows "Workers Planned: 2" for this query. Why it can > use more than 1GB of

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

Hello Thomas, Thank you for explanation. work_mem = 512MB and max_parallel_workers_per_gather = 2 and I run only one Postgres instance and only one query. EXPLAIN shows "Workers Planned: 2" for this query. Why it can use more than 1GB of /dev/shm? Konstantin > On 9 Jul 2019, at 13:51, Thomas

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

On Tue, Jul 9, 2019 at 10:15 PM Jean Louis wrote: > * Konstantin Malanchev [2019-07-09 12:10]: > > 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

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

* 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

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

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

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

* 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 > >

PGSQL 11.4: shared_buffers and /dev/shm size

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

Re: Active connections are terminated because of small wal_sender_timeout

Hello Everyone! > I do not think anybody thinks this is a bug. Setting wal_sender_timeout > too small is a configuration mistake. I don't understand why it is a mistake. 1second is acceptable value for wal_sender_timeout. Moreover the behaviour contradicts with the official description for

Re: Error: rows returned by function are not all of the same row type

> Please do not top post on the Postgres lists. Thank you for the advice. I will take into account this rule in the future. > See contrib/tablefunc/tablefunc.c crosstab_hash() as an example. Thank you. I hope this will be good starting point for me. -- Best regards, Andrey Sychev

Re: pg_dump and search_path

On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote: > > I have a custom search_path: > > > > # show search_path; > >search_path > > -- > > "staging, transient, pg_catalog" > > (1 row) > > > > I ran `pg_dump --schema-only` and the only reference

Re: pg_dump and search_path

On 7/8/2019 11:48 PM, Igal @ Lucee.org wrote: I have a custom search_path: # show search_path;    search_path --  "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only reference in the output to search_path is:   SELECT

pg_dump and search_path

I have a custom search_path: # show search_path;    search_path --  "staging, transient, pg_catalog" (1 row) I ran `pg_dump --schema-only` and the only reference in the output to search_path is:   SELECT pg_catalog.set_config('search_path', '', false);