Re: Interesting case of IMMUTABLE significantly hurting performance

[Dominique Devienne]

On Fri, Apr 11, 2025 at 5:52 AM Tom Lane  wrote:
> Merlin Moncure  writes:
> > I guess the real problems here are lack of feedback on a number of fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream consequences
>
> That's debatable I think.  If you know what you're doing, you're going
> to be annoyed by warnings telling you that you don't.

True, but that's typically what pragmas in the code are for then,
to explicitly suppress warnings.

Or a new option in the CREATE FUNCTION command. Better safe than sorry,
and I prefer useful "on-by-default" warnings for the non-experts,
which can be disabled, than silence.

PS: And FWIW, I removed all attributes from the functions I'm
writting, based on your aerlier advice Tom.

Re: pg_restore causing ENOSPACE on the WAL partition

[Dimitrios Apostolou]

Happened again even with max_wal_size=32GB on the 128GB WAL partition. At
this point I'm quite sure what happens:

+ a checkpoint is going on for several minutes (because of I/O contention
  on the archival drive)

+ meanwhile data keeps coming in through several processes of pg_restore

+ data is coming in much faster because the WAL partition is high perf

+ The checkpoint makes it before full and finishes and frees up 32GB
  (max_wal_size) from the WAL drive. Lets say the WAL partition is now at
  80/128GB full.

+ Immediately another checkpoint starts. This one has to checkpoint 80GB
  of data.

+ This doesn't make it in time and the WAL partition fills up.

+ Painful recovery follows, the database server won't even succeed in
  doing recovery when starting up...


I'm surprised that there is no mechanism for the backends to block while
the WAL is overflowing.

Dimitris



On Thu, 10 Apr 2025, Dimitrios Apostolou wrote:


Hello list,

last night I got ENOSPACE on the WAL partition while running a huge
pg_restore on an empty and idle database.
The checkpoint that started 7 minutes earlier never finished:

  04:31:09 LOG:  checkpoint starting: wal
  ...
  04:38:04 PANIC:  could not write to file "pg_wal/xlogtemp.546204": No
  space left on device
  : CONTEXT:  COPY table_partition_123, line 120872534

This is most likely because most of the data is written to a very slow
"archival" tablespace. No I/O errors on the system logs, I know things go
slow on that device (iSCSI drive over a shared spinning-disks pool and busy
network).

This happened even though I'm keeping the WAL in its own partition (128GB
partition size), with a lot of free space above max_wal_size (64GB). Somehow
it managed to grow above max_wal_size and fill 100% of the partition.

I'm running latest PostgreSQL 17 and the settings have been temporarily
tweaked for fastest pg_restore:

max_wal_size=64GB
max_replication_slots = 0
max_logical_replication_workers = 0
max_wal_senders = 0
wal_level = minimal
autovacuum = off

Several things seem to have gone wrong here. Questions:

+ The WAL partition is much faster than the archival tablespace. Am I in
  constant danger of overruning max_wal_size? How to make 100% sure this
  never happens again?

+ After recovery, with the database idling, I notice that WAL space usage
  is constant at 64GB. Why doesn't it free up space down to min_wal_size
  (1GB)?

+ I just created a 13GB zstd-compressed tarball of those 64GB WAL
  files. This indicates that the files are compressible despite using
  wal_compression=zstd setting. Could it be that postgres ignores the flag
  and does not compress the WAL? How to check?

+ I'm using parallel pg_restore --data-only, can't avoid that for now.
  Even though all the tables are empty (I truncated everything before
  starting pg_restore), I can't find a way to avoid going through the WAL.
  Ideas?


Thanks in advance,
Dimitris

Re: To take backup of Postgresql Database without large objects

[sivapostg...@yahoo.com]

Hello,
Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in Windows 10.
Trying to take backup of a database, using pg_dump, where one table contains 
bytea datatype, which I don't want to include in the backup.
My command was:"E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U 
 --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 

the backup includes the bytea field also.  
I tried with "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U 
 -B -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 

also, which also included the bytea field.
What wrong I'm doing?  Couldn't figure it out.   Any help is appreciated.
Happiness Always
BKR Sivaprakash

Re: To take backup of Postgresql Database without large objects

[Adrian Klaver]

On 4/11/25 05:55, sivapostg...@yahoo.com wrote:

Hello,

Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in 
Windows 10.


Trying to take backup of a database, using pg_dump, where one table 
contains bytea datatype, which I don't want to include in the backup.


My command was:
"E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  
--no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6


the backup includes the bytea field also.

I tried with
"E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  -B 
-F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6


also, which also included the bytea field.


1) Short version

Short version bytea fields != large objects.

2) Long version

From here:

https://www.postgresql.org/docs/current/app-pgdump.html

-B
--no-large-objects
--no-blobs (deprecated)

Exclude large objects in the dump.

When both -b and -B are given, the behavior is to output large 
objects, when data is being dumped, see the -b documentation.


Where large objects are defined here:

https://www.postgresql.org/docs/current/largeobjects.html






What wrong I'm doing?  Couldn't figure it out.   Any help is appreciated.

Happiness Always
BKR Sivaprakash



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: To take backup of Postgresql Database without large objects

[Ron Johnson]

On Fri, Apr 11, 2025 at 8:56 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:

> Hello,
>
> Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in
> Windows 10.
>

That's 11 patch releases behind current.


> Trying to take backup of a database, using pg_dump, where one table
> contains bytea datatype, which I don't want to include in the backup.
>
> My command was:
> "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U 
> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> the backup includes the bytea field also.
>
> I tried with
> "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  -B -F
> c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> also, which also included the bytea field.
>
> What wrong I'm doing?  Couldn't figure it out.   Any help is appreciated.
>

The manual explicitly states what you're doing wrong.
https://www.postgresql.org/docs/15/app-pgdump.html

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Interesting case of IMMUTABLE significantly hurting performance

[Merlin Moncure]

On Thu, Apr 10, 2025 at 10:51 PM Tom Lane  wrote:

> Merlin Moncure  writes:
> > I guess the real problems here are lack of feedback on a number of
> fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream consequences
>
> That's debatable I think.  If you know what you're doing, you're going
> to be annoyed by warnings telling you that you don't.
>
> > *) there is no way to discern inline vs non-inlined execution in explain
>
> That's simply false.  Using the examples in this thread:
>

ah -- gotcha.  misread the original email --  should have known better :)
thanks

merlin

Clarification on the docs

[Igor Korot]

Hi, ALL,
On the 
https://www.postgresql.org/docs/17/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
its said:

[quote]
The optional WITH clause specifies storage parameters for the index.
Each index method has its own set of allowed storage parameters. The
B-tree, hash, GiST and SP-GiST index methods all accept this
parameter:
[/quote]

It states that each index method has its own set.

However only 3 out of predefined are given.

Moore over after that quote there is a list of parameters. But the
last phrase indicates a single parameter.

So how should I read that?

Is it a typo and it has to be plural, oor the list needs to be
ybindented and only one parameter should be there?

Thank you.

Re: Clarification on the docs

[David G. Johnston]

On Fri, Apr 11, 2025 at 6:49 PM Igor Korot  wrote:

> Hi, ALL,
> On the
> https://www.postgresql.org/docs/17/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
> its said:
>
> [quote]
> The optional WITH clause specifies storage parameters for the index.
> Each index method has its own set of allowed storage parameters. The
> B-tree, hash, GiST and SP-GiST index methods all accept this
> parameter:
> [/quote]
>
>
These are the index methods and the valid lists for each.  The docs are
correct in how they remove duplication.  I'm undecided on whether that is
the best presentation choice.  I would at minimum place a new paragraph
after "own set of allowed storage parameters." so that "The B-tree,
hash..." begins its own line.

B-tree:
fillfactor
deduplicate_items

Hash:
fillfactor

Gist:
fillfactor
buffering

SP-Gist:
fillfactor

GIN:
fastupdate
gin_pending_list_limit

BRIN:
pages_per_range
autosummarize

David J.

Re: Clarification on the docs

[Igor Korot]

Hi, David,

On Fri, Apr 11, 2025 at 9:04 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Apr 11, 2025 at 6:49 PM Igor Korot  wrote:
>
>> Hi, ALL,
>> On the
>> https://www.postgresql.org/docs/17/sql-createindex.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
>> its said:
>>
>> [quote]
>> The optional WITH clause specifies storage parameters for the index.
>> Each index method has its own set of allowed storage parameters. The
>> B-tree, hash, GiST and SP-GiST index methods all accept this
>> parameter:
>> [/quote]
>>
>>
> These are the index methods and the valid lists for each.  The docs are
> correct in how they remove duplication.  I'm undecided on whether that is
> the best presentation choice.  I would at minimum place a new paragraph
> after "own set of allowed storage parameters." so that "The B-tree,
> hash..." begins its own line.
>
> B-tree:
> fillfactor
> deduplicate_items
>
> Hash:
> fillfactor
>
> Gist:
> fillfactor
> buffering
>
> SP-Gist:
> fillfactor
>
> GIN:
> fastupdate
> gin_pending_list_limit
>
> BRIN:
> pages_per_range
> autosummarize
>

This above looks much better. What stops you from pushing it?

There is no double meaning and everything is split nicely.

Thank you.


David J.
>
>

Re: To take backup of Postgresql Database without large objects

[David G. Johnston]

On Friday, April 11, 2025, sivapostg...@yahoo.com 
wrote:
>
>
> bytea field also included in the backup.
>

Bytea typed columns are completely separate things than large objects.  You
cannot exclude individual columns using pg_dump.

David J.

Re: To take backup of Postgresql Database without large objects

[sivapostg...@yahoo.com]

 I tried all the combinations, as mentioned in the 
documenthttps://www.postgresql.org/docs/current/app-pgdump.html

"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U  --no-blobs 
-F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 

"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U  -B -F c -v 
-f "E:\DBBackup\demo6_110420251637.bak" demo6 

"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U  -B 
--no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 

With version 15--no-large-objects  is not working.I tried with all the other 
combinations.  NO LUCK.
bytea field also included in the backup.   
I tried taking backup using pg_dump of version 17 also, with no luck.   [ 
Database version is 15 and the pg_dump version is 17 ]. 
Confirming this after restoring the backup file in a new database.   The new 
database contains contents from bytea field also.  
Either my command should be wrong or I'm missing something.  
Happiness Always
BKR Sivaprakash
On Friday 11 April, 2025 at 08:31:31 pm IST, Adrian Klaver 
 wrote:  
 
 On 4/11/25 05:55, sivapostg...@yahoo.com wrote:
> Hello,
> 
> Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in 
> Windows 10.
> 
> Trying to take backup of a database, using pg_dump, where one table 
> contains bytea datatype, which I don't want to include in the backup.
> 
> My command was:
> "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  
> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
> 
> the backup includes the bytea field also.
> 
> I tried with
> "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  -B 
> -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
> 
> also, which also included the bytea field.

1) Short version

Short version bytea fields != large objects.

2) Long version

 From here:

https://www.postgresql.org/docs/current/app-pgdump.html

-B
--no-large-objects
--no-blobs (deprecated)

    Exclude large objects in the dump.

    When both -b and -B are given, the behavior is to output large 
objects, when data is being dumped, see the -b documentation.

Where large objects are defined here:

https://www.postgresql.org/docs/current/largeobjects.html




> 
> What wrong I'm doing?  Couldn't figure it out.   Any help is appreciated.
> 
> Happiness Always
> BKR Sivaprakash
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Re: Clarification on the docs

[David G. Johnston]

On Friday, April 11, 2025, Igor Korot  wrote:

> Hi, David,
>
> On Fri, Apr 11, 2025 at 9:04 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Fri, Apr 11, 2025 at 6:49 PM Igor Korot  wrote:
>>
>>> Hi, ALL,
>>> On the https://www.postgresql.org/docs/17/sql-createindex.html#
>>> SQL-CREATEINDEX-STORAGE-PARAMETERS
>>> its said:
>>>
>>> [quote]
>>> The optional WITH clause specifies storage parameters for the index.
>>> Each index method has its own set of allowed storage parameters. The
>>> B-tree, hash, GiST and SP-GiST index methods all accept this
>>> parameter:
>>> [/quote]
>>>
>>>
>> These are the index methods and the valid lists for each.  The docs are
>> correct in how they remove duplication.  I'm undecided on whether that is
>> the best presentation choice.  I would at minimum place a new paragraph
>> after "own set of allowed storage parameters." so that "The B-tree,
>> hash..." begins its own line.
>>
>

> This above looks much better. What stops you from pushing it?
>
> There is no double meaning and everything is split nicely.
>

Because I’m undecided on what exactly would be an improvement and don’t
care enough on the basis of this single question to put effort into
figuring that out.  Committers read these and if one of them wants to act
on my new paragraph suggestion great.  If not, it isn’t that big a deal.
For me, this doesn’t warrant a CF entry.

David J.

Re: To take backup of Postgresql Database without large objects

[Adrian Klaver]

On 4/11/25 22:06, sivapostg...@yahoo.com wrote:


Either my command should be wrong or I'm missing something.


This was explained in my post as  quoted below.



Happiness Always
BKR Sivaprakash

On Friday 11 April, 2025 at 08:31:31 pm IST, Adrian Klaver 
 wrote:



On 4/11/25 05:55, sivapostg...@yahoo.com  
wrote:

 > Hello,
 >
 > Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in
 > Windows 10.
 >
 > Trying to take backup of a database, using pg_dump, where one table
 > contains bytea datatype, which I don't want to include in the backup.
 >
 > My command was:
 > "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U 
 > --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
 >
 > the backup includes the bytea field also.
 >
 > I tried with
 > "E:\DBBackup\bin\pg_dump.exe"  -h 192.168.1.1 -p 5432 -U  -B
 > -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
 >
 > also, which also included the bytea field.

1) Short version

Short version bytea fields != large objects.

2) Long version

 From here:

https://www.postgresql.org/docs/current/app-pgdump.html 



-B
--no-large-objects
--no-blobs (deprecated)

     Exclude large objects in the dump.

     When both -b and -B are given, the behavior is to output large
objects, when data is being dumped, see the -b documentation.

Where large objects are defined here:

https://www.postgresql.org/docs/current/largeobjects.html 







 >
 > What wrong I'm doing?  Couldn't figure it out.   Any help is appreciated.
 >
 > Happiness Always
 > BKR Sivaprakash

 >

--
Adrian Klaver
adrian.kla...@aklaver.com 






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: To take backup of Postgresql Database without large objects

[David G. Johnston]

On Friday, April 11, 2025, Adrian Klaver  wrote:

> On 4/11/25 22:06, sivapostg...@yahoo.com wrote:
>
> Either my command should be wrong or I'm missing something.
>>
>
> This was explained in my post as  quoted below.


Yeah, the short version.  Then you added a long version that just confused
the issue.  Why point out exclude blobs if you know they are using bytea?

David J.

Re: To take backup of Postgresql Database without large objects

[sivapostg...@yahoo.com]

 Thanks.Then I've misunderstood large objects.  Is there document to explain 
large objects?  We store images in bytea column.
Happiness Always
BKR Sivaprakash
On Saturday 12 April, 2025 at 10:44:21 am IST, David G. Johnston 
 wrote:  
 
 On Friday, April 11, 2025, sivapostg...@yahoo.com  
wrote:

bytea field also included in the backup.   

Bytea typed columns are completely separate things than large objects.  You 
cannot exclude individual columns using pg_dump.
David J.
  

Re: To take backup of Postgresql Database without large objects

[Christophe Pettus]

> On Apr 11, 2025, at 22:34, sivapostg...@yahoo.com wrote:
> Then I've misunderstood large objects.  Is there document to explain large 
> objects?  

Large objects are a relatively old and now little-used feature of PostgreSQL 
that predates the bytea type:

https://www.postgresql.org/docs/current/largeobjects.html

As was mentioned, you cannot exclude individual columns with pg_dump; you have 
to exclude the entire table.