Re: replication primary writting infinite number of WAL files

Peter J. Holzer (2023. nov. 26., V, 12:30): > > > nov 24 10:20:19 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:19.691 CET > [35] > LOG: checkpoints are occurring too frequently (23 seconds apart) > > nov 24 10:20:42 docker02 33d8b96b9062[1200]: 2023-11-24 10:20:42.938 CET > [35] > LOG: checkpo

Re: replication primary writting infinite number of WAL files

> > > A single sequence for all id columns across all tables? > > How is the sequence value landing in the id column? > In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts are sent with fixed increasing va

Re: replication primary writting infinite number of WAL files

> > > >> Sequence is incremented by 100, so for example, between 2023-11-24 > >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it > >> is not possible to insert 62188671 rows into a table. A psql function > >> might be able to increment a sequence 62M times / minute, I'm not

Re: replication primary writting infinite number of WAL files

> > > > I have no explanation for that, except a coincidence. > Replication slots don't generate WAL. > The problem stopped exactly when I dropped the slot. It happened three times in a row. :-(

Re: replication primary writting infinite number of WAL files

Laurenz Albe (2023. nov. 24., P, 16:00): > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > Under normal circumstances, the number of write operations is relatively > low, with an > > average of 4-5 MB/sec total write speed on the disk associated with the > data director

Re: replication primary writting infinite number of WAL files

> > > First I was also thinking about vacuum. But removing a replication > > slot should have no effect on vacuum on the primary (AFAIK). Please > > correct me if I'm wrong. > > > > yeah, depends. there are 2 processes: > > * 1 process generating the wal's, maybe a VACUUM > * an inactive slot hold

Re: replication primary writting infinite number of WAL files

Andreas Kretschmer wrote (2023. nov. 24., P, 13:22): > > > Am 24.11.23 um 12:39 schrieb Les: > > > > Hello, > > > > please check the database log, a VACUUM can also lead to massive wal > generation. Can you find other related messages? by the way, the picture

pgsql --echo-errors --quiet and setval

Dear fellow PostgreSQL users, Today I caught this with postgresql v15, while restoring a database with psql --echo-errors --quiet. (The dump was made using pg_dump -Fp). It logged lots of these messages: setval 1001 (1 row) In other words, it logs **some** things that are not er

pg_restore unexpected end of file

I'm trying to migrate a database from version 11 to version 15. I have created a dump file on v11: pg_dump --dbname=not_telling -Fc --no-owner > dump Then I was trying to import it on v15: pg_restore -v --dbname=not_telling --no-user /restore/dump pg_restore: error: could not read from input fi

Re: invalid value for parameter "default_text_search_config": "public.pg"

> > > I'm not sure what applied="public.pg", error="setting could not be > applied" > > means. > > No, "applied" is "f" (false), meaning that the setting is not actually > usable. > I'm a little surprised that it seems to have gotten into your live session > anyway, although perhaps that's because

Re: invalid value for parameter "default_text_search_config": "public.pg"

> > However, the parallel worker should just be absorbing the same > configuration settings your main session is using. So what remains > to be explained is why you aren't seeing the same complaint when > starting a fresh session. It might be useful to look at the > output of > > show default_tex

Re: invalid value for parameter "default_text_search_config": "public.pg"

>> > SQL Error [22023]: ERROR: invalid value for parameter >> > "default_text_search_config": "public.pg " >> >Where: while setting parameter "default_text_search_config" to >> > "public.pg " parallel worker >> >> What client are you using to run the commands

Re: invalid value for parameter "default_text_search_config": "public.pg"

> > > > SQL Error [22023]: ERROR: invalid value for parameter > > "default_text_search_config": "public.pg " > >Where: while setting parameter "default_text_search_config" to > > "public.pg " parallel worker > > What client are you using to run the commands?

Re: invalid value for parameter "default_text_search_config": "public.pg"

. Johnston ezt írta (időpont: 2023. júl. 21., P, 7:41): > On Thursday, July 20, 2023, Les wrote: > >> I try to execute this on a very simple table, in a production database: >> > > Please provide version information and any extensions you may have > installed. > > David J. > >

invalid value for parameter "default_text_search_config": "public.pg"

I try to execute this on a very simple table, in a production database: alter table product.product add proc_order int8; create index idx_product_proc_order_status on product.product(proc_order nulls last, product_status_id /* this has type uuid */ ); and I get this error: SQL Error [22023]: ERR

Re: foreign keys on multiple parent table

> > . > > From programming point of view and also to reduce the number of objects in > DB could be convinient create just an audit table with a structure like: > >- auditi id >- reference_uuid (the key of the main table) >- table_name >- list of audit data > > Could work, but is t

how to return data from insert into ... on conflict ... returning ... into

Consider this example drop table if exists tbl; drop sequence if exists seq; create sequence seq; create table tbl( id int8 not null primary key, d bytea not null, h bytea not null, -- hash of the data, calculated automatically dummy byte default 0 -- dummy value, see below... ); alter t

Re: possible bug

> Which PG version is this exactly? Given the Memoize node shown > in your plan, I suppose 14.something, but is it up to date? > There were Memoize-related bug fixes in 14.2 and 14.4, and the > one in 14.2 looks particularly likely to be relevant. > > If you are on the current minor release, does

Re: possible bug

> > > > So what happens if query the table directly?: > > select * from wf.workflow where head_table_id::float8::int8 = 25408438504; > > vs > > select * from wf.workflow where head_table_id = 25408438504; > > Both return lots of rows. The same number of rows. select count(*) from wf.workflow wh

Re: possible bug

Not that I know of. I just tried this: reindex table kap.course; reindex table kap.course_user; reindex table wf.workflow; reindex table kap.training_type; But it is still wrong. Adrian Klaver ezt írta (időpont: 2022. okt. 21., P, 19:57): > On 10/21/22 10:50 AM, Les wrote: > &

Re: possible bug

|code|regno|col_3| ---++-+-+ 26437091668|TA-T| 2632|1| 26643094740|PEG | 2905|0| Although all identifier columns are defined as int8. Les ezt írta (időpont: 2022. okt. 21., P, 19:50): > Hello, > > We had a support request today, and we have narrowed

possible bug

Hello, We had a support request today, and we have narrowed down the problem to a query that behaves very strangely. The actual query was much more complicated, but I came up with this minimal example. This is what we have seen inside our application: select * from test where id in (26643094740,

RE: WAL File Recovery on Standby Server Stops Before End of WAL Files

-10-28 06:25:41.264 MDT [5512] LOG: database system is ready to accept read only connections Does the "switched WAL source from archive to stream after failure" indicate a problem with the WAL file? Anyway, it looks like I need to restore the standby server from a new backup. Thank

WAL File Recovery on Standby Server Stops Before End of WAL Files

starting with 00010419005B but the restore process always stops at 00010419005A. I have two questions: * Why does the WAL file recovery process now stop after it reads 00010419005A? * What do I need to do to get PostgreSQL to recover the rest of