Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:09 PM hubert depesz lubaczewski wrote: > You could add event triggers to achieve similar functionality. > https://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/ > and > https://www.postgresql.org/docs/current/sql-createeventtrigger.html Thanks, I was ware of

Re: get last timestamp of table ddl

On Wed, Nov 24, 2021 at 3:30 PM Achilleas Mantzios wrote: > You mean like trying to correlate pg_class.xmin with some timestamp via > track_commit_timestamp or other means? Pretty much yes: since pg_class and pg_attribute comes to my mind. Luca

ANALYZE, pg_class.xmin && pg_class.reltuples

Hi all, I've a doubt about running ANALYZE and inspecting pg_class.xmin and pg_class.reltuples. Let's create a simple table (note, I'm the only user on this instance): testdb=> CREATE TABLE fizz AS SELECT v FROM generate_series( 1, 100 ) v; SELECT 100 testdb=> SELECT relpages, reltuples, xmin, age

max_connections different between primary and standby: is it possible?

Hi all, running PostgreSQL 14, physical replication with slot, after changing (increasing) the max_connections on the primary, I had this message at a restart from the standby: DETAIL: max_connections = 100 is a lower setting than on the primary server, where its value was 300. and the standby d

Re: [GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

On Mon, Nov 20, 2017 at 4:38 PM, Tom Lane wrote: > Those two aren't correlated ... but I think what you want is > > select ...,v,f > from > pg_stats, > rows from (unnest(most_common_vals::text::text[]), > unnest(most_common_freqs)) r(v,f) > where ... Of course I was meaning MCVs

Re: Buffers option

On Fri, Nov 24, 2017 at 12:30 PM, hmidi slim wrote: > I didn't get the number of read and hit buffers when I rexecute the query > many times.How can I Fix that? and I tried the options costs, timing and I > got the same plan query however when trying the option format json it works. Not sure but

session_replication_role meaning?

Hi all, now this should be trivial, but I cannot udnerstand what is the purpose of session_replication_role or better, when I should use it in a way different from 'origin'. I've a logical replicatio

Re: session_replication_role meaning?

On Wed, Jan 31, 2018 at 5:19 AM, Peter Eisentraut wrote: > The documentation was recently updated in the master branch, so maybe > you will find this explanation a bit more detailed: > https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE > Looks a l

passing a pointer from a BackgroundWorker to a DynamicBackgroundWorker

Hi all, I've a doubt about background workers. I've a worker (loaded by a module via shared_preloaded_libraries) should register a dyanmic background worker per database, in a manner similar to what autovacuum does. I would like to pass to these dynamic background workers a pointer to a custom stru

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

On Thu, Mar 20, 2025 at 8:18 AM Sebastien Flaesch wrote: > > Let's not deviate from my request: > > I have implemented various DB client modules using the C client APIs, for > Oracle DB, SQL Server, IBM DB2, MySQL/MariaDB, SQLite and PostgreSQL. > Good for you! I don't think anybody is trying to

Re: Creating a new database on a different file system

On Mon, Mar 17, 2025 at 3:49 PM Ian Dauncey wrote: > > We have created a few databases on the file system defined in the > postgresql.conf, but now I would like to create another database within the > same cluster but on a different file system. > > Is this possible and if so, how do we go about

alter system appending to a value

Hi all, as trivial as it sounds, is there a smart way to use ALTER SYSTEM to append to a value? Something like: ALTER SYSTEM shared_preloaded_libraries = current_setting( 'shared_preloaded_libraries' ) || ',foo'; Rationale: I'm using ansible to configure, thru different steps, PostgreSQL instances

Re: pg_rewind problem: cannot find WAL

On Thu, May 8, 2025 at 8:54 AM Luca Ferrari wrote: > > I've pgbackrest making backups, so I have an archive_command. I'm > going to see if putting a restore_command can fix the problem. > But I'm facing a quite trivial problem: in ubuntu installation the configurati

ERROR: could not read block 0 in file when creating an index out of a function

Hi all, this is a little controversial, but hagin a function defined as immutable that selects a record out of a table, it is impossible to create an index over such function if the column has a constraint like a primary key. Here it is the use case: DROP TABLE if exists t; drop table if exist

Re: ERROR: could not read block 0 in file when creating an index out of a function

On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov wrote: > > I can reproduce this with the table `t` on PG 15.10. I didn't mention I'm running 16.6, but I'm pretty sure it is reproducible on other versions too. > > In your case `base/357283/365810` file is a new index file. For some > reason Postgr

pg_rewind problem: cannot find WAL

Hi all, running 17.4 on ubuntu 24.04 machines. I've three hosts, pg-1 (primary) and two physical replicas. I then promote host pg-3 as a master (pg_promote()) and want to rewind the pg-1 to follow the new master, so: ssh pg-3 'sudo -u postgres /usr/lib/postgresql/17/bin/pg_rewind -D /var/lib/postg

Re: pg_rewind problem: cannot find WAL

On Wed, May 7, 2025 at 3:55 PM Laurenz Albe wrote: > > I don't think it is connected to a WAL switch. > Thanks. > I'd say that you should set "wal_keep_size" high enough that all the WAL > needed for pg_rewind is still present. > > If you have a WAL archive, you could define a restore_command on

Re: pg_rewind problem: cannot find WAL

On Thu, May 8, 2025 at 4:04 PM Rob Sargent wrote: > > > A symlink from $PGDATA to where actual file? > Could be, I need to experiment with pg_basebackup to ensure it is not conflicting with the /etc/ configuration file when creating a clone. Luca

Re: pg_rewind problem: cannot find WAL

On Thu, May 8, 2025 at 5:11 PM Adrian Klaver wrote: > /usr/lib/postgresql/17/bin/pg_rewind --help > pg_rewind resynchronizes a PostgreSQL cluster with another copy of the > cluster. >--config-file=FILENAME use specified main server configuration shame on me! I was grepping config_fil

unexpected pageaddr in WAL segment

In one physical replica running version 17.4 I'm seeing this entries repeated in the logs: unexpected pageaddr 0/400 in WAL segment 00010009, LSN 0/900, offset 0 the system is working correctly, what does the above mean? Thanks, Luca

<    1   2   3