Re: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL]

[Kyotaro Horiguchi]

Hi, Mark.

At Thu, 29 Feb 2024 04:39:49 +, Mark Schloss  
wrote in 
> Thanks for looking at this. I think I complicated things by
> including barman. I was just wanting to point out each primary
> streams to two locations - the walreceiver on the replica and the
> walreciver used by barman. We think the reason the barman
> WAL-receiver didn't fail is because there is no apply of the WAL in
> Barman but the Standby is applying and it's WAL-receiver got
> terminated, so the barman server can be taken out of this picture
> completely, they were just there as a by-product in trying to
> determine the effect.  We are only interested in the killing of the
> standby wal-receiver and that the pg_waldump showed the failing lsn
> was a commit.

It seems that an issue raised in the -hackers thread [1] might be the
same issue as yours. The discussion might be a help for you, although
it's not clear what is happening yet.

[1] 
https://www.postgresql.org/message-id/CAFh8B%3DmozC%2Be1wGJq0H%3D0O65goZju%2B6ab5AU7DEWCSUA2OtwDg%40mail.gmail.com

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Content for talk on Postgres Type System at PostgresConf

[Paul Jungwirth]

On 2/29/24 14:47, Guyren Howe wrote:
What is worth saying about custom range types? What even would be a good use case? A range of 
strings or ip address ranges, something like that, I suppose?


I wrote a blog post and an extension for an inetrange type. It's from 2016 so it might need some 
updates, but you are welcome to use anything you like for your talk:


https://illuminatedcomputing.com/posts/2016/06/inet-range/

Yours,

--
Paul  ~{:-)
p...@illuminatedcomputing.com

Re: Content for talk on Postgres Type System at PostgresConf

[Guyren Howe]

On 29 Feb 2024, at 14:51, Tom Lane  wrote:
> 
>>> - time with time zone *does* store the time zone, but this isn’t
>>> actually useful and should be avoided (I’m not entirely sure why and the
>>> docs only gesture at the problems without stating them, IIRC)
> 
>> No it doesn't store the time zone.  Nor do the docs say they do.  And
>> clearly point out the issue that evaluating a time zone without both date
>> and time inputs is basically pointless.
> 
> timetz *does* store a time zone, in the sense of storing a numeric
> offset from UTC (i.e., "so many minutes east or west of Greenwich").
> The problem is that in most real-world applications your notion of
> "time zone" probably includes annual DST changes, which timetz can't
> represent.  I don't say the type is completely useless, but its
> usefulness is a lot less than you might guess.

The closest I can come to this in the docs is:

"The appropriate time zone offset is recorded in the time with time zone value 
and is output as stored; it is not adjusted to the active time zone.”

I expect to be submitting some documentation updates as part of this project, 
fwiw.

Re: Content for talk on Postgres Type System at PostgresConf

[Tom Lane]

"David G. Johnston"  writes:
> On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe  wrote:
>> My current understanding:
>> 
>> - character is fixed-length, blank-padded. Not sure when you’d want
>> that, but it seems clear. Is the name just confusing?

> Character is not stored fixed length, it is stored variable width but it
> just so happens that the variable length is the same for all rows.

It's nastier than that: the restriction is that the length in
*characters* be the same in all rows.  In a variable-width encoding
like UTF8 the length in bytes can vary.  This means that the
optimization that CHARACTER was meant to enable (ie "the physical
width of this column is fixed, so you don't have to store a length
indication") doesn't work for us, making it just about totally
useless.

BTW, the SQL spec words the constraint the same way, making me wonder
if any modern RDBMS can use this type in the way it was designed.

>> - time with time zone *does* store the time zone, but this isn’t
>> actually useful and should be avoided (I’m not entirely sure why and the
>> docs only gesture at the problems without stating them, IIRC)

> No it doesn't store the time zone.  Nor do the docs say they do.  And
> clearly point out the issue that evaluating a time zone without both date
> and time inputs is basically pointless.

timetz *does* store a time zone, in the sense of storing a numeric
offset from UTC (i.e., "so many minutes east or west of Greenwich").
The problem is that in most real-world applications your notion of
"time zone" probably includes annual DST changes, which timetz can't
represent.  I don't say the type is completely useless, but its
usefulness is a lot less than you might guess.

>> money is a fixed-point decimal value, the number of decimal places is
>> locale determined. I’m not aware of any particular problems with that

> You forget about the currency symbol dynamic. Like with time zones the
> local session provides the context, not the stored data.

Yeah.  The fact that the same stored value might look like 10.00 euros
to one session and 1000 yen to another one is pretty catastrophic.
The other nasty thing about money is that it's an int64 so it can't
represent more than 2^63 pennies (for whatever a "penny" is).  Now,
that's still a Frickin Lot Of Money in any non-hyperinflated currency,
but it's the sort of restriction that banks don't like to hear of.

regards, tom lane

Re: Content for talk on Postgres Type System at PostgresConf

[Guyren Howe]

On 28 Feb 2024, at 17:08, Guyren Howe  wrote:
> 
> I am to talk about Postgres’s type system at PGConf:
> 
> https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system
> 
> I picked the issue because I think it’s poorly understood, greatly 
> under-discussed, and an excellent way to empower postgres users.
> 
> I am reasonably conversant with the issue. I’m not looking for others to 
> write the talk for me, but in order to make the best talk I can, I’m asking:
> 
> What would you want to see in such a talk?
> 
> I’m planning on covering:
> 
> - The built-in types that are underused and their advantages (eg inet)
> - domains
> - such things as details of arrays including multidimensional arrays
> - user-defined types, their relationship to tables, and generally how to use 
> them
> 
> I would spend most of the time discussing ways to make effective use of 
> types. Some examples:
> - defining functions of rows so the table can be used kind of like a set of 
> objects (including the dot notation for invoking functions)
> - using UDFs to make code clearer eg if you have an idiosyncratic functional 
> index, define it using a function of the row, so it’s easy to get right when 
> querying
> - using UDFs as a kind of better domain. eg differentiating imperial from 
> metric units by requiring an explicit constructor, not just accepting any old 
> number
> 
> I would mention enumerated types, although I’m inclined to advise that their 
> inflexibility (eg can’t delete or rearrange them) means that a related table 
> is probably better (I’d be delighted to be proved wrong).
> 
> Custom Range Types are an interesting feature, but I can’t think of a good 
> use case. Any good examples?
> 
> Automatic casting is a feature I’m aware of, but I’d be interested in any 
> cool use cases.
> 
> 
> Anything I’m missing? Any existing good discussions of the subject?

Any gotchas about user defined types or arrays? I was only planning on 
discussing simple user defined types, without going into writing the optional 
features in C.

What is worth saying about custom range types? What even would be a good use 
case? A range of strings or ip address ranges, something like that, I suppose?

Is there a good case for enumerations versus a fk to a table? I guess if you’re 
sure you won’t need to change the values in the enumeration? Days of the week, 
that sort of thing.

Re: Content for talk on Postgres Type System at PostgresConf

[David G. Johnston]

On Thu, Feb 29, 2024 at 2:38 PM Guyren Howe  wrote:

> In case I’m not correct on the issues with these types, myself, what are
> the misconceptions, or where might I find them for  myself?
>
> My current understanding:
>
>- character is fixed-length, blank-padded. Not sure when you’d want
>that, but it seems clear. Is the name just confusing?
>
> Character is not stored fixed length, it is stored variable width but it
just so happens that the variable length is the same for all rows.



>
>- timestamptz is just converted to a timestamp in UTC. Folks might
>imagine that it stores the time zone but it doesn’t.
>
> You are missing the point then whenever converting a stored timestamptz to
textual representation the action is stable because it involves looking up
the session time zone setting.  The same goes in reverse unless the textual
representation includes the time zone specification to use.

And yes, people do believe that whatever timezone was in effect when the
data was recorded is saved when indeed it is not; such information would
need to be captured separately.

>
>- time with time zone *does* store the time zone, but this isn’t
>actually useful and should be avoided (I’m not entirely sure why and the
>docs only gesture at the problems without stating them, IIRC)
>
> No it doesn't store the time zone.  Nor do the docs say they do.  And
clearly point out the issue that evaluating a time zone without both date
and time inputs is basically pointless.

>  money is a fixed-point decimal value, the number of decimal places is
locale determined. I’m not aware of any particular problems with that

You forget about the currency symbol dynamic. Like with time zones the
local session provides the context, not the stored data.

David J.

Re: Content for talk on Postgres Type System at PostgresConf

[Adrian Klaver]

On 2/29/24 1:38 PM, Guyren Howe wrote:
In case I’m not correct on the issues with these types, myself, what 
are the misconceptions, or where might I find them for  myself?


My current understanding:

  * character is fixed-length, blank-padded. Not sure when you’d want
that, but it seems clear. Is the name just confusing?
  * timestamptz is just converted to a timestamp in UTC. Folks might
imagine that it stores the time zone but it doesn’t.

The issue I see is people getting confused when it come back rotated to 
whatever TimeZone is set to, which can change from client to client.


  * time with time zone *does* store the time zone, but this isn’t
actually useful and should be avoided (I’m not entirely sure why
and the docs only gesture at the problems without stating them, IIRC)
  * money is a fixed-point decimal value, the number of decimal places
is locale determined. I’m not aware of any particular problems
with that



test=# show lc_monetary ;
 lc_monetary
-
 en_US.UTF8
(1 row)

test=# select 1000::money;
   money
---
 $1,000.00
(1 row)

test=# set lc_monetary = 'en_GB.utf8';
SET
test=# select 1000::money;
   money
---
 £1,000.00


On 29 Feb 2024 at 01:11 -0800, Laurenz Albe 
, wrote:

On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote:

I am to talk about Postgres’s type system at PGConf:

https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system

I picked the issue because I think it’s poorly understood, greatly
under-discussed, and an excellent way to empower postgres users.

What would you want to see in such a talk?


It might be good to explain how "timestamp with time zone" works.
That's often confusing for beginners, because it is different from
other databases and arguably deviates from the SQL standard.

Perhaps it would be good to warn people about using data types like
"character", "time with time zone" and "money".

Yours,
Laurenz Albe


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

Re: Content for talk on Postgres Type System at PostgresConf

[Guyren Howe]

In case I’m not correct on the issues with these types, myself, what are the 
misconceptions, or where might I find them for  myself?

My current understanding:

• character is fixed-length, blank-padded. Not sure when you’d want that, but 
it seems clear. Is the name just confusing?
• timestamptz is just converted to a timestamp in UTC. Folks might imagine that 
it stores the time zone but it doesn’t.
• time with time zone *does* store the time zone, but this isn’t actually 
useful and should be avoided (I’m not entirely sure why and the docs only 
gesture at the problems without stating them, IIRC)
• money is a fixed-point decimal value, the number of decimal places is locale 
determined. I’m not aware of any particular problems with that

On 29 Feb 2024 at 01:11 -0800, Laurenz Albe , wrote:
> On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote:
> > I am to talk about Postgres’s type system at PGConf:
> >
> > https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system
> >
> > I picked the issue because I think it’s poorly understood, greatly
> > under-discussed, and an excellent way to empower postgres users.
> >
> > What would you want to see in such a talk?
>
> It might be good to explain how "timestamp with time zone" works.
> That's often confusing for beginners, because it is different from
> other databases and arguably deviates from the SQL standard.
>
> Perhaps it would be good to warn people about using data types like
> "character", "time with time zone" and "money".
>
> Yours,
> Laurenz Albe

Re: Question on Table creation

[sud]

Thank you so much.

I tested and it worked as you mentioned i.e the template table is helping
to add the extra indexes or constraints to the child table/partitions (by
inheriting those from the template table if defined), those are not defined
there in the main table. Also dropping the default partition does no harm
to the new partition creation through the automatic maintenance job.

Though I am unable to visualize the situation in which we want the child
table/partitions to be having additional indexes or constraints as compared
to the main table. But I also see that the template table will be phased
out quickly. So I believe it's better to just go with the default template
table with current Pg version 16.

https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md

IMPORTANT NOTES:

   -

   The template table feature is only a temporary solution to help speed up
   declarative partitioning adoption. As things are handled better in core,
   the use of the *template table will be phased out quickly *from
   pg_partman. If a feature that was managed by the template is supported in
   core in the future, it will eventually be removed from template management
   in pg_partman, so please plan ahead for that during major version upgrading
   if it applies to you.




On Thu, Feb 29, 2024 at 1:58 AM veem v  wrote:

>
> On Wed, 28 Feb 2024 at 01:24, sud  wrote:
>
>> While testing the pg_partman extension I see it by default creates a
>> "default partition" even if we provide the parameter p_start_partition as
>> "current date". But if someone purposely doesn't want to have the default
>> partitions , so as to avoid any unwanted data entering the table , which
>> may be required from a data quality perspective. Is there any option in the
>> create_parent function to achieve that? Or do we have to manually drop the
>> default partition after getting this created through create_parent function?
>>
>> I am not able to understand the significance of the "p_template_table"
>> parameter. When we create the partitions without passing this parameter the
>> template table gets created automatically in the partman schema. Checking
>> the details of the template table, it doesn't show any indexes or
>> constraints present in it, but still the child partitions get created with
>> all the necessary indexes and constraints as those are there defined for
>> the main table. So wondering , in what real life scenario do we really need
>> the template table to be defined and how will it help if the partitions are
>> different then from the main table structure?
>>
>> Lastly , the template table is by default created in the partman schema
>> but it also works without error,  if we pass the template table to be
>> created in the application schema. So is there any downside of having the
>> template table reside in the application schema?
>>
>>
>
> As per my understanding , you can't control the creation of the default
> partition. If you somehow don't want to keep it then, you can drop it post
> creation of the partition for the first time i.e after the create_parent
> function call.
>
> Template_table is necessary when someone needs the child partitions to
> have a difference in structure as compared to main table like difference in
> indexes or constraints. But I think the number and data types of columns
> should be the same in main table and template tables. I have not tried
> though.
>
> I think keeping a template table in an application schema is not a good
> idea considering the fact that it's not a business table but a technical
> one, and nobody should accidentally alter this.
>
>>
>>
>

Re: High Availability and Replication

[Israel Brewster]

> On Feb 29, 2024, at 10:15 AM, David G. Johnston  
> wrote:
> 
> 
> 
> On Thursday, February 29, 2024, normandavis1990  > wrote:
>> 
>> What is the difference between High Availability and Replication?
> 
> The former is a goal, the later is a technique.

Perhaps more specifically: Replication is simply Replicating - or copying - the 
“master” database to one or more “slave” databases, generally in real-time such 
that the slave database clusters are replicas of the master. This is good when 
the master goes down, because you’ll still have one or more copies of it 
available, but by itself it doesn’t keep there from being an outage if/when the 
master goes down.

High Availability layers on top of replication to provide some means of 
ensuring that the database is HIGHLY available, such as an automatic failover 
system or load balancer. Many different options that work in many different 
ways are available to help meet this goal.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> 
> David J.
>  

Re: High Availability and Replication

[Adrian Klaver]

On 2/29/24 12:02, normandavis1990 wrote:

What does this technique do?


https://www.postgresql.org/docs/current/high-availability.html



On Thursday, February 29th, 2024 at 10:45 PM, David G. Johnston 
 wrote:



On Thursday, February 29, 2024, normandavis1990 
mailto:normandavis1...@proton.me>> wrote:



What is the difference between High Availability and Replication?


The former is a goal, the later is a technique.

David J.




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

Re: High Availability and Replication

[normandavis1990]

What does this technique do?

On Thursday, February 29th, 2024 at 10:45 PM, David G. Johnston 
 wrote:

> On Thursday, February 29, 2024, normandavis1990  
> wrote:
>
>> What is the difference between High Availability and Replication?
>
> The former is a goal, the later is a technique.
>
> David J.

RE: High Availability and Replication

[Clay Jackson (cjackson)]

What David said 

Disclaimer – I work for Quest Software – we produce database tools, including 
SharePlex, which can be used to help create High Availably and Disaster 
Recovery solutions.

Here’s a blog I did several years ago that might be helpful
https://www.quest.com/community/blogs/b/database-management/posts/high-availability-vs-disaster-recovery---what-s-the-difference


Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg@01DA6B01.DC2DA850]
clay.jack...@quest.com
office  949-754-1203  mobile 425-802-9603

From: David G. Johnston 
Sent: Thursday, February 29, 2024 11:15 AM
To: normandavis1990 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: High Availability and Replication

CAUTION: This email originated from outside of the organization. Do not follow 
guidance, click links, or open attachments unless you recognize the sender and 
know the content is safe.



On Thursday, February 29, 2024, normandavis1990 
mailto:normandavis1...@proton.me>> wrote:

What is the difference between High Availability and Replication?

The former is a goal, the later is a technique.

David J.

Re: High Availability and Replication

[David G. Johnston]

On Thursday, February 29, 2024, normandavis1990 
wrote:

>
> What is the difference between High Availability and Replication?
>

The former is a goal, the later is a technique.

David J.

High Availability and Replication

[normandavis1990]

Hello,
What is the difference between High Availability and Replication?

Cheers.

Re: Content for talk on Postgres Type System at PostgresConf

[grimy . outshine830]

On Thu, Feb 29, 2024 at 10:11:03AM +0100, Laurenz Albe wrote:

> It might be good to explain how "timestamp with time zone" works.
> That's often confusing for beginners, because it is different from
> other databases and arguably deviates from the SQL standard.

The most confusing part is the name :-P

It is natural for a beginner -- and I am not that far from being a
beginner -- to assume a data type with this name must store a time
zone somehow.

-- 
Ian

Re: SUSE repositories not longer available

[Adrian Klaver]

On 2/29/24 08:07, Or Cohen wrote:

I am aware of it, that's not the problem. The problem I have is repositories 
are non-existent, returning 404 -
https://download.postgresql.org/pub/repos/zypp/15/suse/sles-15.4-x86_64/
That's one for instance, it was the same for me for 12-16, and I can't even 
find a reference of PostgreSQL repo for SUSE 12.5.
I would like to know if it was intentionally removed and no longer supported, I 
could not find an announcement about dropping support.


I see this:

https://download.postgresql.org/pub/repos/zypp/15/suse/

where SLES 15.5 and SLES 12.5 are present.

I would suggest asking over on:

https://www.postgresql.org/list/pgsql-pkg-yum/

they maybe able to sort it out better for you.

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

Re: Voluntary Product Assessment For pgAdmin 8.3

[Adrian Klaver]

On 2/29/24 04:31, Anthony Codjoe - IQ-C wrote:

To whom it may concern,

Your product, pgAdmin 8.3, is a candidate technology for use within the 
U.S. General Services Administration (GSA) enterprise environment.  
Technologies under review by GSA’s Office of the Chief Technology 
Officer (OCTO) must be accompanied by a completed Voluntary Product 
Assessment Template (VPAT).   The template and additional Section 508 
program information may be found at: 
https://www.section508.gov/sell/how-to-create-acr-with-vpat/ 
.  Once 
the VPAT is completed, please return it in PDF or .doc format to the 
contact information provided below.


If this artifact is available via the web, please provide a download 
link.  In the event that your organization declines to complete the 
VPAT/ACR, please notify me at the contact information provided below.


You would have a better luck here:

https://www.postgresql.org/list/pgadmin-support/

or at the company that supports it's development:

https://www.enterprisedb.com/




Very Respectfully,

Anthony Codjoe
anthony.cod...@gsa.gov 


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

RE: SUSE repositories not longer available

[Or Cohen]

I am aware of it, that's not the problem. The problem I have is repositories 
are non-existent, returning 404 -
https://download.postgresql.org/pub/repos/zypp/15/suse/sles-15.4-x86_64/
That's one for instance, it was the same for me for 12-16, and I can't even 
find a reference of PostgreSQL repo for SUSE 12.5.
I would like to know if it was intentionally removed and no longer supported, I 
could not find an announcement about dropping support.

-Original Message-
From: Adrian Klaver 
Sent: Thursday, February 29, 2024 5:57 PM
To: Or Cohen ; pgsql-general@lists.postgresql.org
Subject: Re: SUSE repositories not longer available

On 2/29/24 02:16, Or Cohen wrote:
> I have noticed since the changes on the 27^th this month
> (https://people.planetpostgresql.org/devrim/index.php?/archives/122-Installing-PostgreSQL-on-SLES-15-just-got-easier-and-better!.html
>  
> )
>  that some repositories are no longer available.
>
> I specifically noticed it with all SuSE 15.4 repositories, and
> PostgreSQL 16 for SuSE 12.5 repository (also had new dependency
> problem on PostgreSQL 14+15 for SuSE 12.5, might be unrelated).
>
> To be clear, SuSE 12.5 and 15.4 are currently at LTSS phase.
>
> I haven’t found any message regarding PostgreSQL dropping support for
> SuSE 12.5 and 15.4, so I wanted to make sure whether it was dropped
> intentionally or not.
>
> If it’s not planned to add these repositories back, I will appreciate
> if there is an archive with these packages available somewhere.

Have you read?:

https://zypp.postgresql.org/news/pgdg-zypp-repo-gpg-key-update/

>
> Thanks,
>
> Or
>
> ---
> This message is confidential. If you believe you received this message
> in error, please inform the sender and delete this message and all
> attachments.

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

---
This message is confidential. If you believe you received this message in 
error, please inform the sender and delete this message and all attachments.

Re: SUSE repositories not longer available

[Adrian Klaver]

On 2/29/24 02:16, Or Cohen wrote:
I have noticed since the changes on the 27^th this month 
(https://people.planetpostgresql.org/devrim/index.php?/archives/122-Installing-PostgreSQL-on-SLES-15-just-got-easier-and-better!.html ) that some repositories are no longer available.


I specifically noticed it with all SuSE 15.4 repositories, and 
PostgreSQL 16 for SuSE 12.5 repository (also had new dependency problem 
on PostgreSQL 14+15 for SuSE 12.5, might be unrelated).


To be clear, SuSE 12.5 and 15.4 are currently at LTSS phase.

I haven’t found any message regarding PostgreSQL dropping support for 
SuSE 12.5 and 15.4, so I wanted to make sure whether it was dropped 
intentionally or not.


If it’s not planned to add these repositories back, I will appreciate if 
there is an archive with these packages available somewhere.


Have you read?:

https://zypp.postgresql.org/news/pgdg-zypp-repo-gpg-key-update/



Thanks,

Or

---
This message is confidential. If you believe you received this message 
in error, please inform the sender and delete this message and all 
attachments.


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

Re: Voluntary Product Assessment For pgAdmin 8.3

[Laurenz Albe]

On Thu, 2024-02-29 at 07:31 -0500, Anthony Codjoe - IQ-C wrote:
> Your product, pgAdmin 8.3, is a candidate technology for use within the
> U.S. General Services Administration (GSA) enterprise environment.
> Technologies under review by GSA’s Office of the Chief Technology Officer 
> (OCTO)
> must be accompanied by a completed Voluntary Product Assessment Template 
> (VPAT).

This is a mailing list for PostgreSQL, not for pgAdmin.

However, both are open source software.

Please refer to your organization's rules for the correct procedure concerning
open source software.  There is no entity that would fill in your forms.

Yours,
Laurenz Albe

Voluntary Product Assessment For pgAdmin 8.3

[Anthony Codjoe - IQ-C]

To whom it may concern,

Your product, pgAdmin 8.3, is a candidate technology for use within the
U.S. General Services Administration (GSA) enterprise environment.
Technologies under review by GSA’s Office of the Chief Technology Officer
(OCTO) must be accompanied by a completed Voluntary Product Assessment
Template (VPAT).   The template and additional Section 508 program
information may be found at:
https://www.section508.gov/sell/how-to-create-acr-with-vpat/.  Once the
VPAT is completed, please return it in PDF or .doc format to the contact
information provided below.

If this artifact is available via the web, please provide a download link.
In the event that your organization declines to complete the VPAT/ACR,
please notify me at the contact information provided below.


Very Respectfully,

Anthony Codjoe
anthony.cod...@gsa.gov

Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes

[Erik Wienhold]

cc'ing list.

On 2024-02-29 07:16 +0100, Anthony Apollis wrote:
> I cant change data type using the advance editor in destination:
> [image: image.png]
> [image: image.png]

The column type must be changed in the database itself.

On second thought, are you sure "Metric Value" is the right destination
column for your import?  Maybe type numeric(19,18) is intentional.

> On Thu, 29 Feb 2024 at 08:11, Anthony Apollis 
> wrote:
> 
> > I tried multiple data types, including: "Metric Value" text COLLATE
> > pg_catalog."default", "Metric Value" character varying(510) COLLATE
> > pg_catalog."default", and Decimal/Numeric.

Did you get the same error with the text columns?  In plain SQL you can
certainly insert numeric into text columns.  Postgres does an implicit
cast in that case.  Not sure what your middleware does in that case
though.

> > On Thu, 29 Feb 2024 at 02:12, Erik Wienhold  wrote:
> >
> >> On 2024-02-28 21:22 +0100, Anthony Apollis wrote:
> >> > Please assist. I am using SSIS to read data from an Excel sheet into
> >> > Postgres. I have increased the column size a few times, just cant seem
> >> to
> >> > get the data in. Getting errors relating to destination column size.
> >>
> >> What is the data type of that column?
> >>
> >> > [Excel Source [24]] Error: System.Exception: SqlTruncateException:
> >> > Numeric arithmetic causes truncation.. RowNumber=1, ColumnName=Metric
> >> > Value [DataType=DT_NUMERIC,Length=0], Data=[Length=12,
> >> > Value=36655.63]
> >> >at
> >> ZappySys.PowerPack.Adapter.SqlDataComponentBase.HandleException(Exception
> >> > ex)
> >> >at ZappySys.PowerPack.Adapter.SqlDataComponentBase.PrimeOutput(Int32
> >> > outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
> >> >at
> >> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100
> >> > wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers,
> >> > IntPtr ppBufferWirePacket)
> >>
> >> Not sure what I'm looking at, but screenshots 1 and 2 say precision=19
> >> and scale=18 which would be numeric(19,18).  But value 36655.63
> >> shown in the error message requires a precision that is at least 9
> >> digits larger than the scale:
> >>
> >> regress=# select '36655.63'::numeric(19,18);
> >> ERROR:  numeric field overflow
> >> DETAIL:  A field with precision 19, scale 18 must round to an
> >> absolute value less than 10^1.
> >> regress=# select '36655.63'::numeric(27,18);
> >>numeric
> >> --
> >>  36655.63
> >> (1 row)

-- 
Erik

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

[Rob Sargent]

On 2/29/24 01:18, Matthew Dennison wrote:

Here's the results:

psql: error: connection to server at "hostname.mydomain.net" (::1), port 5432 
failed: GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more 
information: No Kerberos credentials available (default cache: KCM:)

If I run kinit and get Kerberos ticket ahead of running the command I then 
receive:

psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 
failed: could not initiate GSSAPI security context: Unspecified GSS failure.  Minor code 
may provide more information: Server not found in Kerberos database
connection to server at " hostname.mydomain.net " (::1), port 5432 failed: 
GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more 
information: Server not found in Kerberos database



Should a FQDN resolve to ::1?

Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

[Stephen Frost]

Greetings,

* Matthew Dennison (m...@matty-uk.co.uk) wrote:
> If I run kinit and get Kerberos ticket ahead of running the command I then 
> receive:

Right, have to kinit first.

> psql: error: connection to server at " hostname.mydomain.net " (::1), port 
> 5432 failed: could not initiate GSSAPI security context: Unspecified GSS 
> failure.  Minor code may provide more information: Server not found in 
> Kerberos database

Note the minor code info- Server not found in Kerberos database.  Also
note that it apparently connected to '::1' which would be localhost- by
default, the Kerberos library will do a reverse DNS lookup on the IP
that it connected to, to get the canonical name of the host.  That can
be disabled if necessary but really, when you use
'hostname.mydomain.net' that should be returning the host's real IP
address and not ::1.  I'd suggest looking into fixing that (maybe it's
in /etc/hosts that way?) and then this should work.

There are options to disable reverse DNS for Kerberos too and then it'll
use whatever you pass to '-h' to look the host up in the Kerberos
database, but that's really not ideal.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Non-Stored Generated Columns

[Laurenz Albe]

On Thu, 2024-02-29 at 12:07 +0100, Dominique Devienne wrote:
> But I'm sure indexes on columns "not used at all in a statement" are 
> eliminated early and easily/cheaply,
> w/o even getting into more complex consideration like statistics and co. 
> Aren't they?

You may want a "SELECT count(*) FROM tab" to consider an index-only
scan on a small index, right?  I'm not saying that it is a large
overhead, but if you actually have dozens of indexes, it can
make processing slower.

Yours,
Laurenz Albe

RE: SUSE repositories not longer available

[Or Cohen]

Woops.
Don't worry about it, just used wrong label.
The rest of the world is free to view the message while it can.

-Original Message-
From: Matthias Apitz  
Sent: Thursday, February 29, 2024 12:46 PM
To: Or Cohen 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: SUSE repositories not longer available

El día jueves, febrero 29, 2024 a las 10:16:59 +, Or Cohen escribió:

> ...
> Thanks,
> Or
> ---
> This message is confidential. If you believe you received this message in 
> error, please inform the sender and delete this message and all attachments.

Interesting signature :-)
You have sent a confidential message to a mailing list with many
subscribers and which will be visible in the archive for the world until
the end of it (which could be soon).

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, BLOCKEDunixarea[.]de/BLOCKED 
+49-176-38902045
Public GnuPG key: BLOCKEDunixarea[.]de/key[.]pubBLOCKED

I am not at war with Russia.  Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.

Re: Non-Stored Generated Columns

[Dominique Devienne]

On Thu, Feb 29, 2024 at 11:58 AM Laurenz Albe 
wrote:

> On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> > On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe 
> wrote:
> > > You could use conditional indexes, but then you have to make sure that
> > > the optimizer knows it can use these indexes.
> >
> > I'm not following. Are you saying the planner is not good at that on its
> own?
> > I need to do something from my end???
>
> I wasn't sure, but now I tested: a conditional index can also be used
> by a cascading delete or update.  So that's not a worry.
>

Great. Thanks a bunch for confirming for me!


> > Something I maybe didn't make clear. The XArc virtual columns are never
> accessed.
>
> Yes, they are.  The query planner considers all indexes.
>

Not sure if I'm missing some terminology or something, to understand your
point.
The XArc columns are never explicitly SELECT'd by our code.
Nor are they used in any of our WHERE clauses.

So yes, the additional indexes our PFK pattern introduces make the pool of
indexes considered larger.
But I'm sure indexes on columns "not used at all in a statement" are
eliminated early and easily/cheaply,
w/o even getting into more complex consideration like statistics and co.
Aren't they? Thanks, --DD

Re: Moving PostgreSQL servers to a new server

[Jason Long]

Hello,I have the same question. I hope someone can answer this question 
clearly. 
 
  On Wed, Feb 28, 2024 at 10:04 PM, normandavis1990 
wrote:   Hi,I am new to PostgreSQL. My scenario is as follows:
https://cdn.bloghunch.com/uploads/uNxNoi5uVKeDibd5.webp

I have a Master server and two Replica (Replica-1 and Replica-2) servers. One 
of these Replica servers is in read-only mode and a report is prepared from it 
using Microsoft PowerBI. Due to a series of issues, I want to transfer the 
Master server and one of the Replica servers to other servers in two steps, but 
I don't want to have any interruptions.
A) Assuming PostgreSQL is installed on the new server, what should I do first 
to migrate the Master? Please show me a tutorial and share your experiences.
B) After transferring the Master, I want to transfer one of the Replica 
servers.  What should I do to transfer this server?

Cheers.  

Re: Non-Stored Generated Columns

[Laurenz Albe]

On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe  
> wrote:
> 
> Honestly, I'm not sure why supporting the non-stored variant of generated
> columns is so controversial...
>  
> > I am sure there are some use cases for "virtual" generated columns, and
> > I don't think that a patch that tries to implement them will be rejected
> > out of hand.  It's just that I don't think it is a very important feature.
> 
> Fair enough. And a reaction I expected when I first posted.
> The outright rejection of it ever being useful, that's what surprised me.


I don't think anybody rejected the idea unconditionally.

But there is always a trade-off between the maintenance burden and the
usefulness of a feature.  All that was said is that the usefulness is low.

> > You could use conditional indexes, but then you have to make sure that
> > the optimizer knows it can use these indexes.
> 
> I'm not following. Are you saying the planner is not good at that on its own?
> I need to do something from my end???

I wasn't sure, but now I tested: a conditional index can also be used
by a cascading delete or update.  So that's not a worry.

> > The sum of the sizes of these indexes shouldn't exceed the size of an
> > unconditional index by much, but they would still be more expensive:
> > each statement would have to look through all the indexes to decide
> > which ones it can use and which ones not.
> 
> Something I maybe didn't make clear. The XArc virtual columns are never 
> accessed.

Yes, they are.  The query planner considers all indexes.

Yours,
Laurenz Albe

Re: SUSE repositories not longer available

[Matthias Apitz]

El día jueves, febrero 29, 2024 a las 10:16:59 +, Or Cohen escribió:

> ...
> Thanks,
> Or
> ---
> This message is confidential. If you believe you received this message in 
> error, please inform the sender and delete this message and all attachments.

Interesting signature :-)
You have sent a confidential message to a mailing list with many
subscribers and which will be visible in the archive for the world until
the end of it (which could be soon).

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

I am not at war with Russia.  Я не воюю с Россией.
Ich bin nicht im Krieg mit Russland.

Re: Non-Stored Generated Columns

[Dominique Devienne]

On Thu, Feb 29, 2024 at 11:26 AM Peter Eisentraut 
wrote:

> On 28.02.24 16:09, Dominique Devienne wrote:
> > Any chance PostgreSQL might gain actual virtual / non-stored generated
> > columns soon? Ever?
>
> I plan to work on this for PG18.
>

Thanks for the update, Peter and Alvaro. --DD

Re: Non-Stored Generated Columns

[Peter Eisentraut]

On 28.02.24 16:09, Dominique Devienne wrote:

We use generated columns extensively.
And we have foreign-keys attached to those generated columns.
The fact they are always Stored thus wastes space in our case.
Any chance PostgreSQL might gain actual virtual / non-stored generated 
columns soon? Ever?


I plan to work on this for PG18.

Re: Non-Stored Generated Columns

[Alvaro Herrera]

On 2024-Feb-29, Dominique Devienne wrote:

> Honestly, I'm not sure why supporting the non-stored variant of generated
> columns is so controversial...

I don't think there's anything controversial about virtual generated
columns, really ... it's just that it's tricky to implement and we don't
have it yet.  I have interest in working on something related that might
allow us to implement virtual generated columns later, but it's not for
the near future.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Most hackers will be perfectly comfortable conceptualizing users as entropy
 sources, so let's move on."   (Nathaniel Smith)
  https://mail.gnu.org/archive/html/monotone-devel/2007-01/msg00080.html

SUSE repositories not longer available

[Or Cohen]

I have noticed since the changes on the 27th this month 
(https://people.planetpostgresql.org/devrim/index.php?/archives/122-Installing-PostgreSQL-on-SLES-15-just-got-easier-and-better!.html)
 that some repositories are no longer available.
I specifically noticed it with all SuSE 15.4 repositories, and PostgreSQL 16 
for SuSE 12.5 repository (also had new dependency problem on PostgreSQL 14+15 
for SuSE 12.5, might be unrelated).
To be clear, SuSE 12.5 and 15.4 are currently at LTSS phase.
I haven't found any message regarding PostgreSQL dropping support for SuSE 12.5 
and 15.4, so I wanted to make sure whether it was dropped intentionally or not.
If it's not planned to add these repositories back, I will appreciate if there 
is an archive with these packages available somewhere.

Thanks,
Or
---
This message is confidential. If you believe you received this message in 
error, please inform the sender and delete this message and all attachments.

Re: Non-Stored Generated Columns

[Dominique Devienne]

On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe 
wrote:

> On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote:
> Polymorphic Foreign Keys are nigh impossible to model well in SQL,
> and I doubt that non-stored generated columns will solve that.
>

It is modelled. It works. As I already wrote above.
It's not for everyone, no doubt about it. it has its costs. But we
definitely must use it.

Non-stored generated columns simply makes that implementation more costly
in space that it could/should be.
Honestly, I'm not sure why supporting the non-stored variant of generated
columns is so controversial...


> > For NOT NULL PFKs, that more of a PITA [...]
>
> See?
>

And?  I'm not sure I see you point Laurenz.
The alternative to NOT implementing PFKs is NOT having referential
integrity.
Which is a big no-no for us. The "costs" of PFKs are outweighed by lack of
RI.


> > So has Ron says. If SQLite and Oracle has them, that's not an accident.
>
> Oracle has a lot of things that are not enviable...
>

The same can't be said for SQLite :)
And yet it has them too!


> I am sure there are some use cases for "virtual" generated columns, and
> I don't think that a patch that tries to implement them will be rejected
> out of hand.  It's just that I don't think it is a very important feature.
>

Fair enough. And a reaction I expected when I first posted.
The outright rejection of it ever being useful, that's what surprised me.
I'm not a PostgreSQL server developer. So a patch won't be coming from me
though...
I'm "only" a PostgreSQL / LIBPQ user, albeit maybe an "above average" one I
dare say.


> > Are the NULLs stored in these indexes, consuming extra space?
>
> Yes, NULLs are stored in indexes, just like everything else.
>

OK, that confirms my worry. Thanks.


> You could use conditional indexes, but then you have to make sure that
> the optimizer knows it can use these indexes.
>

I'm not following. Are you saying the planner is not good at that on its
own?
I need to do something from my end???


> The sum of the sizes of these indexes shouldn't exceed the size of an
> unconditional index by much, but they would still be more expensive:
> each statement would have to look through all the indexes to decide
> which ones it can use and which ones not.
>

Something I maybe didn't make clear. The XArc virtual columns are never
accessed.
Only the ID and CODE concrete columns are read and written.
The XArcs are an implementation detail solely to ensure RI of PFKs.
The only reason we index them is for efficient CASCADE'ing of the FKs (on
XArcs).

Surely the fact XArc "FK" indexes are partial/expression-based,
shouldn't prevent the PostgreSQL-internal CASCADE from using them, no?

Otherwise that would defeat having indexed FK columns, no?
Or did I misunderstand you last point?

Thanks again for taking time on this particular thread. Appreciated. --DD

Re: Content for talk on Postgres Type System at PostgresConf

[Laurenz Albe]

On Wed, 2024-02-28 at 17:08 -0800, Guyren Howe wrote:
> I am to talk about Postgres’s type system at PGConf:
> 
> https://postgresconf.org/conferences/2024/program/proposals/postgres-s-type-system
> 
> I picked the issue because I think it’s poorly understood, greatly
> under-discussed, and an excellent way to empower postgres users.
> 
> What would you want to see in such a talk?

It might be good to explain how "timestamp with time zone" works.
That's often confusing for beginners, because it is different from
other databases and arguably deviates from the SQL standard.

Perhaps it would be good to warn people about using data types like
"character", "time with time zone" and "money".

Yours,
Laurenz Albe

Re: Non-Stored Generated Columns

[Laurenz Albe]

On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote:
> On Wed, Feb 28, 2024 at 8:11 PM Tom Lane  wrote:
> > Dominique Devienne  writes:
> > > Views can have foreign-keys?
> > 
> > Surely you'd put the FK on the underlying table.
> 
> Again, the FKs are on the *generated* columns. So 
>  
> > > Generated view columns be indexed?
> > 
> > [...[ it's hard to see much use-case here
> 
> The use-case is Polymorphic Foreign Key (aka PFKs).
> 
> For NULL'able PFKs, you have two read-write concrete columns, the ID
> and the CODE (aka TYPE) of the relation.
> Then you have 1 generated column (that I call XArc) per possible CODE/TYPE
> value (for that specific relation), which is either ID or NULL, depending
> on the CODE.
> And the "regular" FK is on that generated (and ideally Non-Stored, the
> whole point of this thread), since it points to a single table now.
> And since these FKs are CASCADE'ing, you want them INDEX'ed of course.

I concede that that is a borderline use case.

But you may as well have the foreign key columns as actual columns
(or as stored generated columns).
If that's a handful or so, it shouldn't be a big problem.
If you have hundred types (hundred referenced tables), you'd end up
with hundreds of indexes, and that already looks like a very bad idea
(both DML and query planning time will be affected).

Polymorphic Foreign Keys are nigh impossible to model well in SQL,
and I doubt that non-stored generated columns will solve that.

> For NOT NULL PFKs, that more of a PITA [...]

See?

> So has Ron says. If SQLite and Oracle has them, that's not an accident.

Oracle has a lot of things that are not enviable...

I am sure there are some use cases for "virtual" generated columns, and
I don't think that a patch that tries to implement them will be rejected
out of hand.  It's just that I don't think it is a very important feature.

> PS: Since we are on this subject, I'd like to take an opportunity to
> ask a question I've had for a long time, but didn't have time to research.
> For a given PFK, all its XArc columns are mutually-exclusive (by design,
> see above). So their indexes are full of NULLs.
> Are the NULLs stored in these indexes, consuming extra space? In our use
> case, we don't care about NULLs.
> So should be use an expression index to explicitly not index the NULLs?
> The sum of indexes for a given PFK should in theory be the same as a
> single index, whatever the cardinality of the PFK, "logically".

Yes, NULLs are stored in indexes, just like everything else.

You could use conditional indexes, but then you have to make sure that
the optimizer knows it can use these indexes.

The sum of the sizes of these indexes shouldn't exceed the size of an
unconditional index by much, but they would still be more expensive:
each statement would have to look through all the indexes to decide
which ones it can use and which ones not.

Yours,
Laurenz Albe

Re: Fastest way to clone schema ~1000x

[Emiel Mols]

An update on this for anyone else who has similar issues/considerations:
- As suggested, we are sticking to 1 database-per-test, and not a
schema-per-test.
- We moved our testing setup to maintain at most 1 connection per backend
worker. If a request for a different test comes in, we close and reconnect
to the different DB (in the future, we might test some affinity based
routing here). This does empirically seem to improve performance around
~5-10% over maintaining 1000+ connections. It probably helps that the
connection is local over a unix domain socket. We now set max_connections
in testing to 512, where we usually max out at ~200.
- We tried PgBouncer, which seems to work as well, but doesn't improve
performance much compared to the previous point. (Also it adds quite some
complexity, we still need to maintain the 1000+ connections to the pooler
as it doesn't support changing databases and the user names need to be
known/configured ahead of time which is annoying for us)
- We looked more into the `native_queued_spin_lock_slowpath` issue,
distilling a flamegraph to identify the actual caller(s): our latest
thinking now is that this is an artifact of measurement with perf/bpf (!!),
so the concerns with this can be ignored otherwise. Flame graph:
https://ibb.co/sW34mgq. Reason to think so is that
`bpf_trampoline_6442485415_0` is in its call path. (Please do reach out if
you think this is wrong)

Thanks again for the quick help.

- Emiel

On Mon, Feb 26, 2024 at 5:10 PM Adrian Klaver 
wrote:

> On 2/26/24 01:06, Emiel Mols wrote:
> > On Mon, Feb 26, 2024 at 3:50 PM Daniel Gustafsson  > > wrote:
> >
> > There is a measurable overhead in connections, regardless of if they
> > are used
> > or not.  If you are looking to squeeze out performance then doing
> > more over
> > already established connections, and reducing max_connections, is a
> > good place
> > to start.
> >
> >
> > Clear, but with database-per-test (and our backend setup), it would have
> > been *great* if we could have switched database on the same connection
> > (similar to "USE xxx" in mysql). That would limit the connections to the
> > amount of workers, not multiplied by tests.
>
> That is because:
>
> https://dev.mysql.com/doc/refman/8.3/en/glossary.html#glos_schema
>
> "In MySQL, physically, a schema is synonymous with a database. You can
> substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax,
> for example using CREATE SCHEMA instead of CREATE DATABASE. "
>
>
> >
> > Even with a pooler, we're still going to be maintaining 1000s of
> > connections from the backend workers to the pooler. I would expect this
> > to be rather efficient, but still unnecessary. Also, both
> > pgbouncer/pgpool don't seem to support switching database in-connection
> > (they could have implemented the aforementioned "USE" statement I
> > think!). [Additionally we're using PHP that doesn't seem to have a good
> > shared memory pool implementation -- pg_pconnect is pretty buggy].
> >
> > I'll continue with some more testing. Thanks for now!
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

RE: Unable to get PostgreSQL 15 with Kerberos (GSS) working

[Matthew Dennison]

Here's the results:

psql: error: connection to server at "hostname.mydomain.net" (::1), port 5432 
failed: GSSAPI continuation error: Unspecified GSS failure.  Minor code may 
provide more information: No Kerberos credentials available (default cache: 
KCM:)

If I run kinit and get Kerberos ticket ahead of running the command I then 
receive:

psql: error: connection to server at " hostname.mydomain.net " (::1), port 5432 
failed: could not initiate GSSAPI security context: Unspecified GSS failure.  
Minor code may provide more information: Server not found in Kerberos database
connection to server at " hostname.mydomain.net " (::1), port 5432 failed: 
GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide 
more information: Server not found in Kerberos database


R
-Original Message-
From: Stephen Frost  
Sent: Wednesday, February 28, 2024 6:35 PM
To: Matthew Dennison 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Unable to get PostgreSQL 15 with Kerberos (GSS) working

Greetings,

* Matthew Dennison (m...@matty-uk.co.uk) wrote:
> Sorry, yes I tried that and received the same results.

Did you try doing a 'klist' after?  Would be interesting to know if you got a 
Kerberos ticket or not.

Thanks,

Stephen