pg_locks.relation question

Hi, session one: locks=# begin; BEGIN locks=# alter table test add column b text; ALTER TABLE locks=# Session 2 querying pg_locks for the PID from above: locks=# select locktype,relation::regclass,mode from pg_locks where pid = 2026 and locktype = 'relation'; locktype | relation | mod

Re: pg_locks.relation question

On Wed, Apr 18, 2018 at 5:35 PM, Daniel Westermann < [ mailto:daniel.westerm...@dbi-services.com | daniel.westerm...@dbi-services.com ] > wrote: Hi, session one: locks=# begin; BEGIN locks=# alter table test add column b text; ALTER TABLE locks=# Session 2 querying pg_locks f

Re: Strange error in Windows 10 Pro

Il 21/04/2018 22:35, Adrian Klaver ha scritto: > On 04/21/2018 01:08 PM, Dale Seaburg wrote: >> Thanks Adrian for the suggestion of running the installer with Admin >> rights. Unfortunately, I get the same results. It appears that all >> of the folders within C:\Program Files\PostgreSQL\9.6 pat

PG11 Hash partitioning and null values in the partition key

Hi, given this setup: create table part2 ( a int, list varchar(10) ) partition by hash (a); create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0); create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1); create table part2_3 partition of

Re: PG11 Hash partitioning and null values in the partition key

Hi, given this setup: create table part2 ( a int, list varchar(10) ) partition by hash (a); create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0); create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1); create table part2_3 partition

index only scan question

Hi quick question: Given these steps: postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;     QUERY PLAN     --  Index Only Scan using i2 on t1

Re: index only scan question

>Am 09.11.2018 um 13:58 schrieb Daniel Westermann: >> Is that because of some sort of caching? >no, but vacuum updated the visibility map in the meantime. No, it do not, double checked that with: select pg_visibility_map('t1'::regclass, 0);

Re: index only scan question

>I'd say the old index tuple was killed during the first scan: >https://www.cybertec-postgresql.com/en/killed-index-tuples/ Thanks Laurenz, I will check that

Re: index only scan question

>I'd say the old index tuple was killed during the first scan: >https://www.cybertec-postgresql.com/en/killed-index-tuples/ ... from your blog: "Whenever an index scan fetches a heap tuple only to find that it is dead (that the entire “

Re: index only scan question

sorry, hit the wrong key >I'd say the old index tuple was killed during the first scan: >https://www.cybertec-postgresql.com/en/killed-index-tuples/ ... from your blog: "Whenever an index scan fetches a heap tuple only to find that it

Is the ring buffer not used for index blocks

Hi, we just wondered: When a huge table is loaded into buffer cache it goes into the ring buffer to not pollute the cache. The same is apparently not true for indexes as much more blocks are cached. -- Restarted the instance pgbench=# explain (analyze,buffers) select count(*) from pgbench_acco

What does tcop stand for?

Hi, currently browsing the source code in src/include/tcop. What does tcop stand for? Can not find any hints in the files. Thanks Daniel

Re: What does tcop stand for?

>> Hi, >> >> currently browsing the source code in src/include/tcop. What does tcop stand >> for? Can not find any hints in the files. >Traffic cop. Thanks cop mean?

Re: What does tcop stand for?

currently browsing the source code in src/include/tcop. What does tcop stand for? Can not find any hints in the files. >> >>>Traffic cop. >> >> Thanks >> cop mean? >A cop means a policeman. Probably the name came from that it is >responsible for the traffic control between fronte

Re: Master-Slave error: the database system is starting up

> http://www.postgresql-archive.org/Master-Slave-error-the-database-system-is-starting-up-td6004044.html > > >please execute "select * from pg_stat_replication;" and "select * from >pg_replication_slots ;" on the master and show us the output. >Please check also the logs on the standby. Did

Re: POLL: Adding transaction status to default psql prompt

>On Thu, Feb 06, 2020 at 03:54:48AM +0100, Vik Fearing wrote: >> Please answer +1 if you want or don't mind seeing transaction status by >> default in psql or -1 if you would prefer to keep the current default. > >+1 +1 Daniel

Temporary tablespaces on a RAM disk

Hi, is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archives but could not find any information that either confirmed it is a bad idea nor the opposite. Thanks in advance Daniel

Re: Temporary tablespaces on a RAM disk

On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) mailto:daniel.westerm...@dbi-services.com>> wrote: is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archives but could not find any information that either con

Re: Temporary tablespaces on a RAM disk

Daniel Westermann wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences with > that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it is a bad idea nor the opposite. >Wel

Re: Temporary tablespaces on a RAM disk

Ron writes: > On 3/17/20 12:19 PM, David G. Johnston wrote: >> There is a nice big bold warning callout in the documentation that covers >> this explicitly. >> https://www.postgresql.org/docs/12/manage-ag-tablespaces.html >>> Warning >>> Placing a tablespace on a temporary file system like a RAM

Re: Temporary tablespaces on a RAM disk

>"Daniel Westermann (DWE)" writes: >>> People have asked about this before, so maybe it'd be an idea to make >>> an explicit concept of a temp tablespace that only accepts temp tables, >>> and do whatever is needful to make that robust. Bu

Re: Temporary tablespaces on a RAM disk

Hi Laurenz, >From: Laurenz Albe >Sent: Tuesday, March 17, 2020 20:50 >To: Daniel Westermann (DWE) ; >pgsql-general@lists.postgresql.org >Subject: Re: Temporary tablespaces on a RAM disk >On Tue, 2020-03-17 at 09:49 +, Daniel Westermann (DWE) wrote: >> is

Re: Temporary tablespaces on a RAM disk

>Laurenz Albe writes: >> On Tue, 2020-03-17 at 19:57 +0000, Daniel Westermann (DWE) wrote: >> >>> >> is someone using temporary tablespaces on a RAM disk ? Any experiences >>> >> with that? >>> >> I did some quick tests and checked

Out of memory with "create extension postgis"

Hi, we have a very strange behavior on PostgreSQL 12.3 when we try to create the extension postgis. Postgres and postgis have both been installed from packages: postgresql12-server-12.3-5PGDG postgis30_12-3.0.1-5.rhel7 The OS is RedHat 7.7. Creating other extensions works fine, we only see thi

Re: Out of memory with "create extension postgis"

>"Daniel Westermann (DWE)" writes: >> we have a very strange behavior on PostgreSQL 12.3 when we try to create the >> extension postgis. Postgres and postgis have both been installed from >> packages: >> ... >> The process eats all the available mem

Re: Out of memory with "create extension postgis"

>>> "Daniel Westermann (DWE)" writes: >>>> The process eats all the available memory and finally dies: >>>> # create extension postgis; >>>> ERROR: out of memory >>>> DETAIL: Failed on request of size 8265691 in memory

Re: Out of memory with "create extension postgis"

>>>> "Daniel Westermann (DWE)" writes: >>>>> The process eats all the available memory and finally dies: >>>>> # create extension postgis; >>>>> ERROR:  out of memory >>>>> DETAIL:  Failed on request of size 8265

Re: Out of memory with "create extension postgis"

From: Tom Lane Sent: Wednesday, July 29, 2020 17:05 To: Daniel Westermann (DWE) Cc: pgsql-general@lists.postgresql.org Subject: Re: Out of memory with "create extension postgis"   "Daniel Westermann (DWE)" writes: >> So this is what we got today. In the log file the

Re: Out of memory with "create extension postgis"

>> Here is a new one with bt at the end: >That's just showing the stack when the backend is idle waiting for input. >We need to capture the stack at the moment when the "out of memory" error >is reported (errfinish() should be the top of stack). Then I don't know what/how to do it. Here is a comp

Re: Out of memory with "create extension postgis"

>I am aware that the behavior is different from what we've seen last week but >this is how it looks today. >Anything we missed or did not do correct? Finally this can be re-produced quite easily by installing this extension: https://de.osdn.net/projects/pgstoreplans/downloads/72297/pg_store_plan

PG 13 trusted extensions and pg_available_extensions

Hi, I was playing a bit with trusted extensions and wondered if there is a reason that the "trusted" flag is not exposed in pg_available_extensions. I believe that information would be quite useful so one can easily identify extensions that can be installed as "normal" user. Regards Daniel

Re: PG 13 trusted extensions and pg_available_extensions

On Thu, Sep 24, 2020 at 10:58 AM Michael Paquier wrote: >> >> On Wed, Sep 23, 2020 at 03:28:45PM +0000, Daniel Westermann (DWE) wrote: >> > I was playing a bit with trusted extensions and wondered if there is >> > a reason that the "trusted" flag is not ex

Compiler warnings on Debian 10

HI, I was wondering if someone already has seen these warnings on Debian 10 (PostgreSQL 13.1): ../../../../src/include/port.h:176:70: warning: 'format' attribute argument not supported: gnu_printf [-Wignored-attributes] extern int pg_snprintf(char *str, size_t count, const char *fmt,

Re: Compiler warnings on Debian 10

>> Nothing special. Configure and make log attached. The same procedure works >> fine on my local Debian 10.7. >Quite odd.  The configure output clearly shows that it thought gnu_printf >is fine: >checking for printf format archetype... gnu_printf >Possibly the corresponding section of config.l

oid2name: Why table and not relation?

Hi, given this simple example: postgres=# create table t ( a int ); CREATE TABLE postgres=# create index i on t(a); CREATE INDEX postgres=# select pg_relation_filepath('i'); pg_relation_filepath -- base/5/16388 (1 row) postgres=# \! oid2name --oid 16388 >From database "post

Re: How to execute .sql file inside a postgres schema

>>I have a database name “kbdb” that is having a schema “kb” and I want to >>execute the test.sql file inside >>this schema, set schema 'your_schema'; Cheers, Daniel

Understanding pg_stat_io.evictions

Hi, I am trying to understand the evictions statistic in pg_stat_io. I know what evictions are, so this is not the question. Given this: postgres=# select version(); version --

Re: Understanding pg_stat_io.evictions

>> postgres=# insert into t select i, md5(i::text), md5(i::text), md5(i::text) >> from generate_series(1,100) i; >.. >> client backend | 207 | normal >> client backend | 0 | vacuum >> >> Shouldn't these evictions show up under context blkwrite? The description in >> docs is:

Corruption or wrong results with 14.10?

Hi, I have a very strange behavior on 14.10. smrdbprod=# select version(); version -- PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc

Re: Corruption or wrong results with 14.10?

>smrdbprod=# select count(*) from smrr_mgr.formula where crart_id = 2150718 >and chemin = '@'; > count >--- > 1 >(1 row) > >smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group by >crart_id, chemin having count(*) > 1; > crart_id | chemin | count >--+

pg_stat_activity.query_id <-> pg_stat_statements.queryid

Hi, quick question: What would be the cases for a query_id in pg_stat_activity not showing up in pg_stat_statements.queryid assuming pg_stat_statements.max is not yet reached? Regards Daniel

Re: Upgrading from 11 to 13

>On Tue, Mar 30, 2021 at 04:34:34PM +0200, Guillaume Lelarge wrote: >> Hi, >> >> Le mar. 30 mars 2021 à 16:10, Susan Joseph a écrit  >> : >> >> I am currently using PostgreSQL 11.2 and would like to try and upgrade it >> to the latest version 13.  Can I go straight from 11 to 13 or do I

Re: Upgrading from 11 to 13

>On Tue, Mar 30, 2021 at 08:10:08AM -0700, Adrian Klaver wrote: >> On 3/30/21 8:06 AM, Bruce Momjian wrote: >> > What other software needs to upgrade through all intermediate versions? >> >> Many variations of OS'es, though not all. One of the benefits of Ubuntu is >> that you can jump from x.LTS

Behavior change in PostgreSQL 14Beta3 or bug?

Hi, while playing with PostgreSQL 14 Beta 3 I noticed a change when it comes to the visibility map and vacuum. Test case: gbench=# create table t1 ( a int, b text ) with ( fillfactor = 100 ); CREATE TABLE pgbench=# insert into t1 select a, a::text from generate_series(1,100) a; INSERT 0 100

Re: Behavior change in PostgreSQL 14Beta3 or bug?

>It's a non-hot update, and so there is a single dead index tuple. You're >seeing the new optimization that makes vacuum skip indexes in >marginal cases. >Try running vacuum with index cleanup = on. Thank you, Peter

Why does the OID jump by 3 when creating tables?

Hi all, as I could not find the reason in the source code, can someone tell me why the OID counter jumps by 3 between two create table statements? postgres=# create table t1 ( a int ); CREATE TABLE postgres=# create table t2 ( a int ); CREATE TABLE postgres=# select oid,relname from pg_class whe

Re: Why does the OID jump by 3 when creating tables?

>>Le sam. 30 oct. 2021 à 10:55, Daniel Westermann (DWE) >> a écrit : >>Hi all, >>as I could not find the reason in the source code, can someone tell me why >>the OID counter jumps by 3 between two create table >>statements? >>postgres=# create tab

Re: WAL Archiving and base backup

>On 1/14/22 1:40 PM, Stephen Frost wrote: >snip] >> We shouldn't be trying to provide >> documentation around how to write a tool like pgbackrest, we should, >> instead, have a tool like pgbackrest in core with its own documentation, >> as most other RDBMS's do. >That's an excellent solution to th