Thoughts on user-defined types for talk at Postgres conference?

[Guyren Howe]

I am doing a talk at Postgres Conf about Postgres’s type system. I already 
asked about this and got some great responses:

https://www.postgresql.org/message-id/flat/b82f8886db61a3395e6eab94981258274cdbacd3.camel%40cybertec.at#f13288b72bd2d564ee758c1a273652ad

Those responses discussed mostly gotchas with built-in types. Lots of good 
stuff, thanks, all!

But what *really* sets Postgres apart from comparable systems is user defined 
types. I would like to carefully lay out how to define and use a user-defined 
type (I don’t think I have time to dig into doing fancy stuff with C functions, 
so just the basic “user defined sum type”), but also any gotchas.

And I’d like to finish with some thoughts about when and how to use 
user-defined types. My feeling is that this feature is greatly under-used, 
mostly because it’s so non-standard. But AFAICT, user-defined types are fine 
and other than some ugliness due to SQL (mainly needing parentheses in some 
unexpected places), fields in a user defined type work perfectly well in 
Postgres’s SQL. I guess you’d need to pull them apart for values returned to 
clients, but that isn’t difficult.

So, any gotchas with user defined types? Any thoughts about designing with them?

Re: Guarantees/Semantics of pg_stats

[Baziotis, Stefanos]

Hi Laurenz,

Thanks for replying. I see. Can I maybe get accurate information if the column 
has an index? In other words, are there any type of indexes through which I can 
get the number of distinct values or the values themselves, without needing to 
scan the column?

Best,
Stefanos

From: Laurenz Albe 
Sent: Saturday, March 2, 2024 04:28
To: Baziotis, Stefanos ; pgsql-general@lists.postgresql.org 

Subject: Re: Guarantees/Semantics of pg_stats

On Sat, 2024-03-02 at 07:41 +, Baziotis, Stefanos wrote:
> I'm interested in learning more about the guarantees/semantics of pg_stats.
> For example, is there a guarantee that the n_distinct and most_common_vals
> fields will take into account any values appearing more than M times or
> maybe with frequence more than f? In what cases will n_distinct and
> most_common_vals will miss some values?

Table Statistics are not exact.  They are collected from a random sample of
the data, so they are never guaranteed to be exact.

Their purpose is to estimate the result row count and cost of execution plan
steps.  You can never use them as proof.

Yours,
Laurenz Albe

Re: Content for talk on Postgres Type System at PostgresConf

[grimy . outshine830]

On Fri, Mar 01, 2024 at 03:25:35PM -0500, Tom Lane wrote:

> No, what he showed was correct.  I'm talking about a different facet
> of the problem:

> ...

> Even if that took account of the exchange rate, it'd not be great.
> But it doesn't; it's just the same digits reinterpreted with a new
> currency sign and possibly a different number of fractional digits.
> This might be sort of tolerable if your database only ever deals in
> one currency, but even then you'd likely want to lock down what that
> currency is.  Making it be controlled by a user-set GUC was probably
> not a great idea.

Yes, I get it now, thanks. Not useful, I agree.

-- 
Ian

Re: Content for talk on Postgres Type System at PostgresConf

[Laurenz Albe]

On Fri, 2024-03-01 at 08:14 -0800, Adrian Klaver wrote:

> [pointers to the documentation]

I know.  I was not searching for help with PostgreSQL,
I was trying to point out the surprising behavior of
"character" as a suggestion for the talk.

Yours,
Laurenz Albe

Re: Guarantees/Semantics of pg_stats

[Laurenz Albe]

On Sat, 2024-03-02 at 07:41 +, Baziotis, Stefanos wrote:
> I'm interested in learning more about the guarantees/semantics of pg_stats.
> For example, is there a guarantee that the n_distinct and most_common_vals
> fields will take into account any values appearing more than M times or
> maybe with frequence more than f? In what cases will n_distinct and
> most_common_vals will miss some values?

Table Statistics are not exact.  They are collected from a random sample of
the data, so they are never guaranteed to be exact.

Their purpose is to estimate the result row count and cost of execution plan
steps.  You can never use them as proof.

Yours,
Laurenz Albe

Re: High Availability and Replication

[Abdul Sayeed]

Hi,

Not required.



Thanks & Regards,
Abdul Sayeed
PostgreSQL DBA
Postgres Professional Certified
Skype: abdul.sayeed24


On Sat, 2 Mar 2024 at 12:21 PM, normandavis1990 
wrote:

> > On Saturday, March 2nd, 2024 at 1:24 AM, Abdul Sayeed <
> abdulsayee...@gmail.com> wrote:
>
> Hi,
>
> When Master server goes down, either you need to promote one of slave node
> or configure HA mechanism so that in case of master server goes down it
> will automatically promote the slave server as new master.
>
> Patroni HA tool would be good option for your requirement.
>
> https://patroni.readthedocs.io/en/latest/README.html
>
> Hope this helps.
>
>
>
> Thanks & Regards,
> Abdul Sayeed
> PostgreSQL DBA
>
>
>
> On Sat, 2 Mar 2024 at 2:15 AM, Israel Brewster 
> wrote:
>
>> On Mar 1, 2024, at 11:36 AM, normandavis1990 
>> wrote:
>>
>> > On Thursday, February 29th, 2024 at 11:38 PM, Israel Brewster <
>> ijbrews...@alaska.edu> wrote:
>>
>> On Feb 29, 2024, at 10:15 AM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>
>>
>> On Thursday, February 29, 2024, normandavis1990 <
>> normandavis1...@proton.me> 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.
>>
>>
>>
>> Hi,
>> You said "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.". What does "goes
>> down" mean?
>>
>>
>> Exactly what I said - it goes down. Not functioning. Offline.
>> Inaccessible. It is not up and running, therefore, it is down.
>>
>> In Replication mode, if the primary server is shut down, then the data
>> will also be lost?
>>
>>
>> No. As I said - and you quoted - “When the master goes down…you’ll still
>> have one or more copies of it available”. So no, the data will NOT be lost.
>>
>> ---
>> 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
>>
>>
> Hi,
> Does installing Patroni cause the primary server to stop even for a short
> time?
>