Re: date_trunc invalid units with infinite value

On Thu, Dec 05, 2024 at 02:34:41PM +0900, Michael Paquier wrote: > I agree that it is inconsistent that we allow infinite values to go > through this function call even for fields that are not listed as > supported by the documentation. So, yes, I think that what you are > doing the right thing by

Re: Recovering from detoast-related catcache invalidations

On Tue, Dec 24, 2024 at 12:18:09AM +0200, Heikki Linnakangas wrote: > My first attempt was to insert the CatCTup or CatCList entry to the catcache > before starting to build it, marked with a flag to indicate that the entry > isn't fully built yet. But when I started to write that it got pretty > i

Re: Allow ILIKE forward matching to use btree index

On Fri, 20 Dec 2024 03:22:26 +0900 Yugo Nagata wrote: > Hi, > > Currently, btree indexes cannot used for ILIKE (~~*) operator if the pattern > has case-varying characters although LIKE (~~) expression can be converted > to indexable clauses by the planner support function (if the collation > is

RE: Parallel heap vacuum

Dear Sawada-san, Thanks for updating the patch. ISTM that 0001 and 0002 can be applied independently. Therefore I can firstly post some comments only for them. Comments for 0001: ``` +/* New estimated total # of tuples and total # of live tuples */ ``` There is a unnecessary blank. ``` +

adjust_limit_rows_costs algorithm

Hi Hackers There should be many people who have encountered the problem of order by col limit 1 without index filtering,Here is an example of my test create extension pg_trgm ; CREATE TABLE public.dba_users ( userid integer primary key, username character varying(64), password character varyin

Re: Make pg_stat_io view count IOs as bytes instead of blocks

On Fri, Dec 06, 2024 at 12:41:55PM +0300, Nazir Bilal Yavuz wrote: > Thanks! I think 'track' is a better word in this context. I used > 'tracked in ...', as it sounded more correct to me (I hope it is). Splitting op_bytes into three fields sounds like a good idea. Count me in regarding the concep

Exists pull-up application with JoinExpr

Hi, hackers! I found one pull-up that works if the inner join condition is written through the where condition, |create temp table ta (id int primary key, val int); insert into ta values(1,1); insert into ta values(2,2); ||insert into ta values(3,3);| |create temp table tb (id int primary k

Re: per backend I/O statistics

On Fri, Dec 20, 2024 at 09:57:19AM +, Bertrand Drouvot wrote: > BTW, now that the per backend I/O statistics is done, I'll start working on > per > backend wal statistics. I think that this is a good idea. It does not actually overlap the proposal in [1] as the stats persistency is not the s

Re: transaction lost when delete clog file after normal shutdown

On Tue, Dec 24, 2024 at 09:55:09AM +0800, wenhui qiu wrote: > However, on the other hand, oracle has many solutions to open the database > after the data files are damaged, and his intention should be to start the > database even if some critical files are damaged to salvage the data > inside.Becau

Re: stored procedures vs pg_stat_statements

On Mon, Dec 23, 2024 at 10:06:58PM -0600, Merlin Moncure wrote: > I'm aware of that and will set it -- it's the only option if I'm following > you. The way I've been doing things lately for bulk processing is a lot > of orchestrated procedures that are organized for purposes of monitoring > and e

Re: stored procedures vs pg_stat_statements

On Mon, Dec 23, 2024 at 3:58 PM Tom Lane wrote: > Merlin Moncure writes: > > Can this simply be disabled for stored procedures as a special case? I'm > > hoping this might do something useful that is also safe. Curious if > anyone > > has any thoughts on this. > > No, I don't think that would

Re: Add Postgres module info

On 12/23/24 17:26, David E. Wheeler wrote: > On Dec 23, 2024, at 15:17, Tom Lane wrote: >> How would that work for extensions where the C code is intentionally >> supporting multiple versions of the SQL objects? > > I guess some people do that, eh? In that case it wouldn’t. A function pointer ra

Re: Add Postgres module info

On Mon, Dec 16, 2024 at 12:02 PM Andrei Lepikhov wrote: > On 12/13/24 10:17, Tom Lane wrote: > > Andrei Lepikhov writes: > >> On 12/12/24 21:02, Yurii Rashkovskii wrote: > >>> 2. Any reasons to dictate MAJ.MIN format? With semantic versioning > >>> abound, it's rather common to use MAJ.MIN.PATCH

Re: Make tuple deformation faster

On Tue, 24 Dec 2024 at 11:19, David Rowley wrote: > The attached adjusts that Assert code so that a fresh CompactAttribute > is populated instead of modifying the TupleDesc's one. I'm not sure > if populate_compact_attribute_internal() is exactly the nicest way to > do this. I'll think a bit hard

Re: transaction lost when delete clog file after normal shutdown

HI Jan Wieck > Why would you trust the other 99.999 TB if something corrupted the clog > file? However, on the other hand, oracle has many solutions to open the database after the data files are damaged, and his intention should be to start the database even if some critical files are damaged to s

Re: Add Postgres module info

On 12/24/24 02:23, David E. Wheeler wrote: On Dec 11, 2024, at 19:49, Euler Taveira wrote: FWIW, Id like to have some more information in there, without commenting on the specifics. +1 for the general idea. Same. I received some reports like [1] related to wal2json that people wants to o

Re: transaction lost when delete clog file after normal shutdown

>Why would you trust the other 99.999 TB if >something corrupted the clog >file? +1 On Tue, 24 Dec 2024 at 02:06, Jan Wieck wrote: > On 12/23/24 06:01, 章晨曦@易景科技 wrote: > > Yes, of course we can solve this by restoring from backup. > > But if the database volumn is large, say, 100TB or more, the

Re: Add Postgres module info

On Dec 23, 2024, at 15:17, Tom Lane wrote: > How would that work for extensions where the C code is intentionally > supporting multiple versions of the SQL objects? I guess some people do that, eh? In that case it wouldn’t. D

Re: Make tuple deformation faster

On Tue, 24 Dec 2024 at 02:00, Alexander Lakhin wrote: > Please look at the following query, which triggers (sometimes not on a > first run) an assert added with 5983a4cff: > regression=# SELECT COUNT(*) FROM > (SELECT (aclexplode(proacl)).* FROM pg_proc) a, > (SELECT oid FROM pg_proc UNION ALL SE

Re: Recovering from detoast-related catcache invalidations

On 14/12/2024 02:06, Heikki Linnakangas wrote: Ok, I missed that. It does not handle the 2nd scenario though: If a new catalog tuple is concurrently inserted that should be part of the list, it is missed. I was able to reproduce that, by pausing a process with gdb while it's building the list

Re: stored procedures vs pg_stat_statements

Merlin Moncure writes: > Can this simply be disabled for stored procedures as a special case? I'm > hoping this might do something useful that is also safe. Curious if anyone > has any thoughts on this. No, I don't think that would help. The restriction on utility statements would cover CREATE

stored procedures vs pg_stat_statements

Hello, I've noticed that in pg_stat_statements, stored procedures are almost always given a unique query id unless the query is textually identical. pg_stat_statements.c makes this pretty clear as procedures are considered utility statements and no normalization analysis is done against them. Cli

Re: Document NULL

On Mon, Dec 23, 2024 at 11:39 AM Marcos Pegoraro wrote: > Em dom., 22 de dez. de 2024 às 15:07, David G. Johnston < > david.g.johns...@gmail.com> escreveu: > > On section Null-Valued Settings is not easy to understand that "show > example.string" will result in an "ERROR: unrecognized...", that B

Re: nbtree VACUUM's REDO routine doesn't clear page's VACUUM cycle ID

On Wed, Nov 20, 2024 at 4:41 AM Andrey M. Borodin wrote: > > On 15 Nov 2024, at 21:33, Peter Geoghegan wrote: > > I propose this for the master branch only. > > The change seems correct to me: anyway cycle must be less than cycle of any > future vacuum after promotion. The cycles set in the pag

Re: nbtree VACUUM's REDO routine doesn't clear page's VACUUM cycle ID

On Wed, Nov 20, 2024 at 1:32 AM Michael Paquier wrote: > Perhaps this patch should also explain why this is better this way? See the commit message, and my remarks to Andrey just now. > This path does not manipulate btpo_level, for one. Why would it do so? Changing the btree level would corrupt

Re: Make tuple deformation faster

On Tue, 24 Dec 2024 at 02:00, Alexander Lakhin wrote: > regression=# SELECT COUNT(*) FROM > (SELECT (aclexplode(proacl)).* FROM pg_proc) a, > (SELECT oid FROM pg_proc UNION ALL SELECT oid FROM pg_proc) b; > WARNING: terminating connection because of crash of another server process > ... > TRAP: f

Re: [EXTERNAL] Re: Add non-blocking version of PQcancel

Jelte Fennema-Nio writes: > On Fri, 22 Nov 2024 at 01:37, Tom Lane wrote: >> How would we do that? libpqsrv_cancel is not chartered to wait around >> for the results of the cancel, and I'm not even sure that it could >> know what to check for. > Ah yeah, you're right. I got confused by the two

Re: Add Postgres module info

"David E. Wheeler" writes: > Not a mutually-exclusive proposal, of course; I think it makes sense to have > metadata included in the binary itself. Would be useful to compare against > what CREATE EXTENSION thinks is the version and raising an error or warning > when they diverge. How would th

Re: Add Postgres module info

On Dec 11, 2024, at 19:49, Euler Taveira wrote: >> FWIW, Id like to have some more information in there, without commenting on >> the specifics. > > +1 for the general idea. Same. > I received some reports like [1] related to wal2json > that people wants to obtain the output plugin version. Si

Re: Document NULL

Em dom., 22 de dez. de 2024 às 15:07, David G. Johnston < david.g.johns...@gmail.com> escreveu: On section Null-Valued Settings is not easy to understand that "show example.string" will result in an "ERROR: unrecognized...", that BEGIN or ROLLBACK does not return anything. So, what do you think ab

Re: help in allocating shared module within a module

On Mon, 23 Dec 2024, 18:32 Luca Ferrari, wrote: > > Hi all, > hope this is the right place to ask for, otherwise please point me in > the right resource. > I'm trying to develop a module that needs shared memory between > background workers. > > The _PG_init calls a function to reserve the shared

Re: transaction lost when delete clog file after normal shutdown

On 12/23/24 06:01, 章晨曦@易景科技 wrote: Yes, of course we can solve this by restoring from backup. But if the database volumn is large, say, 100TB or more, the cost is really too expensive just because the tiny clog file corrupt. Why would you trust the other 99.999 TB if something corrupted the clo

More reliable nbtree detection of unsatisfiable RowCompare quals involving a leading NULL key/element

Currently, nbtree has inconsistent detection of unsatisfiable RowCompare quals that involve a leading NULL key. I'll show a test case that illustrates where nbtree doesn't behave as expected right now, resulting in an unnecessary full index scan. Test case setup === pg@regression:5432

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

On Sat, Dec 21, 2024 at 10:28 AM Robert Treat wrote: > > On Tue, Dec 17, 2024 at 5:51 PM Melanie Plageman > wrote: > > > It feels to me like eager vacuums are not so much a distinct thing, > but that, like how all vacuum do index cleanup unless told not to, all > vacuums are optimistic that they

Re: Eagerly scan all-visible pages to amortize aggressive vacuum

Updated v4 attached. On Wed, Dec 18, 2024 at 4:30 PM Robert Haas wrote: > > On Tue, Dec 17, 2024 at 5:54 PM Melanie Plageman > wrote: > > Makes sense. I've attempted to clarify as you suggest in v3. > > I would just commit 0001. There's nothing to be gained by waiting around. Done. > I don't c

help in allocating shared module within a module

Hi all, hope this is the right place to ask for, otherwise please point me in the right resource. I'm trying to develop a module that needs shared memory between background workers. The _PG_init calls a function to reserve the shared memory, which in turn calls RequestAddinShmemSpace, which seems

Re: Using Expanded Objects other than Arrays from plpgsql

Michel Pelletier writes: > On Wed, Dec 18, 2024 at 12:22 PM Tom Lane wrote: >> So, just to clarify where we're at: you are satisfied that the current >> patch-set does what you need? > There are a few cases where I have to force an expansion, I work around > this by calling a `wait()` function,

Re: Use "protocol options" name instead of "protocol extensions" everywhere

On Mon, Dec 23, 2024 at 8:39 AM Jelte Fennema-Nio wrote: > On Thu, 31 Oct 2024 at 18:15, Jelte Fennema-Nio > wrote: > > > > On Thu, 31 Oct 2024 at 15:50, Heikki Linnakangas > wrote: > > > Bikeshedding time: > > > > Another few options: > > Okay let's just pick one of the available options. The

RE: AIX support

Hello Team, In response to our previous discussions, we have refined the code to address only the necessary adjustments focusing solely on the essential modifications for AIX compatibility. The codebase was pulled from the GitHub repository (https://github.com/postgres/postgres.git) and we resolved

Re: Use "protocol options" name instead of "protocol extensions" everywhere

On Thu, 31 Oct 2024 at 18:15, Jelte Fennema-Nio wrote: > > On Thu, 31 Oct 2024 at 15:50, Heikki Linnakangas wrote: > > Bikeshedding time: > > Another few options: Okay let's just pick one of the available options. The current situation where we use different terminology for the same thing across

Re: Logical replication timeout

> I came up with an alternate approach. In this approach we keep track > of wal segment the transaction is part of. This helps to iterate > through only required files during clean up. > > On my machine, I am running the testcase provided by you in [1]. It is > generating ~1.9 million spill files.

Re: Make tuple deformation faster

Hello David, 20.12.2024 12:31, David Rowley wrote: The attcacheoff removal is now pushed. I've attached the two remaining patches. Please look at the following query, which triggers (sometimes not on a first run) an assert added with 5983a4cff: regression=# SELECT COUNT(*) FROM (SELECT (aclexp

RE: Logical replication timeout

Dear Shlok, > > Thanks for sharing the analysis. > > I tested the patch on my machine as well and it has worse performance > for me as well. > I came up with an alternate approach. In this approach we keep track > of wal segment the transaction is part of. This helps to iterate > through only re

Re: Exporting float_to_shortest_decimal_buf(n) with Postgres 17 on Windows

Vladlen Popolitov писал(а) 2024-12-23 15:14: Yes, it is also working option. I applied it and tested in the current master under Windows, it works. Attached patch changes this line in meson.build link_with: cflag_libs, + link_whole: cflag_libs, c_pch: pch_c_h, I also cre

Re: Exporting float_to_shortest_decimal_buf(n) with Postgres 17 on Windows

Heikki Linnakangas писал(а) 2024-12-23 14:05: On 23/12/2024 12:32, Vladlen Popolitov wrote: I found the reason of this bug and the fix for it. Cool! Fortunatelly meson has option to force put all object files to library - add dependency with the flag link_whole . I made the one-line patch a

Re: transaction lost when delete clog file after normal shutdown

> On 23 Dec 2024, at 16:01, 章晨曦@易景科技 wrote: > > Yes, of course we can solve this by restoring from backup. > But if the database volumn is large, say, 100TB or more, the cost > is really too expensive just because the tiny clog file corrupt. There are corresponding commitfest entries [0,1],

RE: Logical replication timeout

Dear Marc, Thanks for the reply! > Thanks for your suggestions that were both already tested. In our (real) case > (a > single transaction with 12 millions sub-transactions): > > 1) setting the subscription as streaming, just delay a bit the spill file > surge. It does > not prevent the creati

Re: Exporting float_to_shortest_decimal_buf(n) with Postgres 17 on Windows

On 23/12/2024 12:32, Vladlen Popolitov wrote: I found the reason of this bug and the fix for it. Cool! Fortunatelly meson has option to force put all object files to library - add dependency with the flag link_whole . I made the one-line patch and it fixes this issue. -    'dependencies

Re: Re: transaction lost when delete clog file after normal shutdown

Yes, of course we can solve this by restoring from backup.  But if the database volumn is large, say, 100TB or more, the cost is really too expensive just because the tiny clog file corrupt. Regards, Jet Daniel Gustafsson

Re: transaction lost when delete clog file after normal shutdown

> On 23 Dec 2024, at 15:36, 章晨曦@易景科技 wrote: > > disk errors, or even cosmic rays ... It is file system responsibility to protect directory structure. > Human errors, We cannot protect database from this with technical measures. I concur with Tom about firing DBA who removed xact log file.

Re: transaction lost when delete clog file after normal shutdown

> On 23 Dec 2024, at 11:36, 章晨曦@易景科技 wrote: > Human errors, disk errors, or even cosmic rays ... That sounds exactly like the scenario which backups are made for. In all these error cases there is no way of being sure that no other part of the system has been compromised, so you restore from ba

Re: Re: transaction lost when delete clog file after normal shutdown

Human errors, disk errors, or even cosmic rays ... Regards, Jet Andrey Borodin

Re: Exporting float_to_shortest_decimal_buf(n) with Postgres 17 on Windows

Michael Paquier писал(а) 2024-09-14 03:15: On Fri, Sep 13, 2024 at 03:26:42PM -0700, Andrew Kane wrote: This was my hunch as well since none of the source files changed. Also, neither function is present with `dumpbin /EXPORTS /SYMBOLS lib\postgres.lib`, which led me to believe it may need to b

Re: transaction lost when delete clog file after normal shutdown

> On 23 Dec 2024, at 15:03, 章晨曦@易景科技 wrote: > > S1: > When database normal shutdown, and clog file missing, the database cannot > restart. And if make a zero clog file, database started but may cause > transactions lost. > > S2: > When database crashed, and clog file missing, when database

Re: Repeatable read transaction doesn't see dropped table

On Mon, Dec 23, 2024 at 3:17 PM David G. Johnston wrote: > The quoted section describes how two consecutive select queries will see the > same data. Your example shows how a single query behaves in isolation. The > “as the first query saw it” is fundamentally important since until it > succe

Re: Re: transaction lost when delete clog file after normal shutdown

S1: When database normal shutdown, and clog file missing, the database cannot restart. And if make a zero clog file, database started but may cause transactions lost. S2: When database crashed, and  clog file missing, when database restart, it will try to recover. And everything is ok So I t

Re: Repeatable read transaction doesn't see dropped table

On 23/12/2024 10:17, David G. Johnston wrote: On Monday, December 23, 2024, Daniil Davydov <3daniss...@gmail.com > wrote: Hi, The documentation for PostgreSQL 17 says the following : "query in a repeatable read transaction sees a snapshot as of the s

Re: transaction lost when delete clog file after normal shutdown

> On 23 Dec 2024, at 14:12, 章晨曦@易景科技 wrote: > > we simulate crash and clog file corrupt (delete the clog file) Clog file cannot disappear as a result of a crash. What makes you think otherwise? Best regards, Andrey Borodin.

Re: Logical replication timeout

> Can you enable the parameter "streaming" to on on your system [1]? It allows > to > stream the in-progress transactions to the subscriber side. I feel this can > avoid > the case that there are many .spill files on the publisher side. > Another approach is to tune the logical_decoding_work_mem

RE: Logical replication timeout

Dear Marc, > For some unknown reason (probably a very big transaction at the source), we > experienced a logical decoding breakdown, ... > When those timeout occurred, the sender was still busy deleting files from > data/pg_replslot/bdcpb21_sene, accumulating more than 6 millions small > ".spill"

Re: Re: transaction lost when delete clog file after normal shutdown

Thanks tom. But what I think is we may provide a better experience.  Consider the below example: [jet@halodev-jet-01 data]$ psqlpsql (16.6) Type "help" for help. postgres=# CREATE TABLE a_test (n INT); CREATE TABLE postgres=# INSERT INTO a_test VALUES (1); INSERT 0 1 postgres=# 2024-12-23 16

Re: Repeatable read transaction doesn't see dropped table

On Monday, December 23, 2024, Daniil Davydov <3daniss...@gmail.com> wrote: > Hi, > The documentation for PostgreSQL 17 says the following : > "query in a repeatable read transaction sees a snapshot as of the > start of the first non-transaction-control statement in the > transaction, not as of the