RE: Compatibility Issues with Npgsql and .NET Framework 3.5 after Upgrading to PostgreSQL 16.3

[馬 騰飛]

Thank you very much for your response.
After changing the "password_encryption" option to "md5" in the postgresql.conf 
file, the server can now successfully connect to the database.
Thanks again for your help.

-Original Message-
From: Adrian Klaver  
Sent: Wednesday, October 30, 2024 12:06 AM
To: Daniel Gustafsson ; 馬　騰飛 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Compatibility Issues with Npgsql and .NET Framework 3.5 after 
Upgrading to PostgreSQL 16.3

CAUTION：This is an external email. Please be cautious when opening links or 
attachments in email.



On 10/29/24 04:01, Daniel Gustafsson wrote:
>> On 29 Oct 2024, at 04:25, 馬　騰飛  wrote:
>>
>> Thank you very much for your reply.
>> However, after installing PostgreSQL 16.4, our server still encounters the 
>> following error when connecting to the database, just like with version 16.3:
>> “Only AuthenticationClearTextPassword and AuthenticationMD5Password 
>> supported for now. Received: 10”.
>> Could you please advise how to resolve this error? Should we also update 
>> Npgsql? If Npgsql needs to be updated, to which version should we update it?
>
> I don't know the first thing about Npgsql so I can't offer any advice, 
> but it sounds like the problems is that the server wants to use SCRAM 
> passwords and your version of npgsql only support cleartext and md5.  
> Changing the METHOD in pg_hba.conf to md5 could fix the immediate 
> problem perhaps but upgrading to a version that support SCRAM seems 
> the most appropriate (especially since md5 passwords are likely going away 
> soon).

Potential issue with above per:

https://www.postgresql.org/docs/current/auth-password.html

"To ease transition from the md5 method to the newer SCRAM method, if
md5 is specified as a method in pg_hba.conf but the user's password on the 
server is encrypted for SCRAM (see below), then SCRAM-based authentication will 
automatically be chosen instead."

Therefore a roles password could still be in SCRAM. You would need to change 
the actual passwords also.


According to the issue below Npgsql supports SCRAM:

https://github.com/npgsql/npgsql/issues/5593

Which translates into Npgsql 3.2.7:

https://github.com/npgsql/npgsql/releases/tag/v3.2.7

>
> --
> Daniel Gustafsson
>
>
>

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

Re: COPY documentation with regard to references constraints

[Dominique Devienne]

On Thu, Oct 31, 2024 at 1:04 PM Bruno Wolff III  wrote:
> I was unable to find any documentation clarifying if using COPY to load a
> table with rows referencing others rows in the same table, whether I
> had to worry about ordering of the input.
> What I found (in 17) is that even if the references constraint wasn't
> set as deferrable, I could have rows for children before their parents
> in the COPY data and things still worked.

AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
to postpone the enforcement of FKs at COMMIT time of the transaction.

While a single COPY is just one statement, so whether a temporary violation
for self-referential FKs would occur would be implementation-dependent,
and thus "neither logical" nor predictable.

Applies to other statement types too.

But of course, more authoritative answers will come I'm sure. --DD

Re: Index Partition Size Double of its Table Partition?

[Don Seiler]

On Wed, Oct 30, 2024 at 4:59 PM David Mullineux  wrote:

> Are you able to cluster the table ? The idea is that rows ordered in the
> same way  as the index might reduce it's size ?
>
I'm not sure on this. There are other indexes on these table partitions as
well.

Another bit of useful info that I should have shared immediately is that
this is a monthly partitioned table, going back years. We don't drop old
partitions (yet) on this one. For now we've only added this index to a few
individual partitions. The hope was to add it to all of them and then
eventually the template (using an older version of pg_partman).

Don.
-- 
Don Seiler
www.seiler.us

Re: Index Partition Size Double of its Table Partition?

[Rob Sargent]

On Oct 31, 2024, at 10:02 AM, Don Seiler  wrote:On Wed, Oct 30, 2024 at 4:59 PM David Mullineux  wrote:Are you able to cluster the table ? The idea is that rows ordered in the same way  as the index might reduce it's size ? I'm not sure on this. There are other indexes on these table partitions as well.Another bit of useful info that I should have shared immediately is that this is a monthly partitioned table, going back years. We don't drop old partitions (yet) on this one. For now we've only added this index to a few individual partitions. The hope was to add it to all of them and then eventually the template (using an older version of pg_partman).Don. -- Whats the fill factor?

COPY documentation with regard to references constraints

[Bruno Wolff III]

I was unable to find any documentation clarifying if using COPY to load a 
table with rows referencing others rows in the same table, whether I 
had to worry about ordering of the input.
What I found (in 17) is that even if the references constraint wasn't 
set as deferrable, I could have rows for children before their parents 
in the COPY data and things still worked.

Re: COPY documentation with regard to references constraints

[Laurenz Albe]

On Thu, 2024-10-31 at 06:38 -0500, Bruno Wolff III wrote:
> I was unable to find any documentation clarifying if using COPY to load a 
> table with rows referencing others rows in the same table, whether I 
> had to worry about ordering of the input.

I cannot find it in the documentation either, but since COPY is a single
statement and foreign key constraints are checked at the end of the
statement, it should work fine.

Yours,
Laurenz Albe

Re: Plans for partitioning of inheriting tables

[Adrian Klaver]

On 10/25/24 11:47, Thiemo Kellner wrote:


Am 25.10.2024 um 17:57 schrieb Adrian Klaver:


I do not feel it applies to my case. I tried to create a partitioned 
table that inherits columns from a base table. The documentation you 
provided the URL seems to speak of realising partitioning by using 
inheritance.


This needs a code example to go any further.


Sorry, my bad. I posted the URL of the table that is inherited from. The 
recepient is 
https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sqlcreate table if not exists TOPO_FILES (


  SOURCE_ID  uuid
 constraint TOPO_FILES␟FK_01
 references SOURCES (ID)
 match full
 not null
 ,FILE_NAME  text
 not null
 ,TILE   raster
 not null
 ,FILE_CREATION_PIT  timestamp(6) with time zone
 not null
 ,FILE_HASH  text
 not null
 ,constraint TOPO_FILES␟PK primary key (ID)
 ,constraint TOPO_FILES␟UQ unique (SOURCE_ID
  ,FILE_NAME)
)
inherits(TEMPLATE_TECH);
-- partition by hash (source_id); --NOTE Up to PG 17, partitioning with 
inheriting table is not possible.


It is just not the way you want to do it, see:

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE





The spender table 
ishttps://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/PostgreSQL/tables/TEMPLATE_TECH.pg_sql

create table if not exists TEMPLATE_TECH (
  ID uuid
 constraint TEMPLATE_TECH␟PK primary key
 not null
 default gen_random_uuid()
 ,ENTRY_PIT  timestamp(6) with time zone
 not null
 default clock_timestamp()
);




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

Re: What to do with a PAAS-pg server

[Bruce Momjian]

On Wed, Oct 30, 2024 at 11:49:29AM -0400, Ron Johnson wrote:
> Settings, like "SELECT * FROM pg_settings;"

This query will show you the non-default settings:

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

---

> On Wed, Oct 30, 2024 at 11:32 AM alexander al (leiden) 
> wrote:
> 
> Hi,
> 
> we have a supplier (via our client) who has an self build PAAS-version
> of postgresql. Ok, you would say, that's fine. But, there is always an
> but, we think the settings are not quite ok. We really want to know how
> much memory etc there is on that server. So we can recommend the
> recommended settings. Is there a way to get those information on the
> server itself from psql?
> 
> regards,
> 
> Alexander Al
> 
> 
> 
> 
> 
> 
> 
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  crustacean!

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

Re: COPY documentation with regard to references constraints

[Bruno Wolff III]

On Thu, Oct 31, 2024 at 13:15:34 +0100,
 Dominique Devienne  wrote:


AFAIK, DEFERRABLE is for temporary violations crossing statement boundaries,
to postpone the enforcement of FKs at COMMIT time of the transaction.

While a single COPY is just one statement, so whether a temporary violation
for self-referential FKs would occur would be implementation-dependent,
and thus "neither logical" nor predictable.

Applies to other statement types too.


Thanks. I needed to look more carefully under deferrable where it says 
the following:
This controls whether the constraint can be deferred. A constraint that is 
not deferrable will be checked immediately after every command.


That is pretty clear.

Re: What to do with a PAAS-pg server

[Ian Lawrence Barwick]

2024年10月31日(木) 0:32 alexander al (leiden) :
>
> Hi,
>
> we have a supplier (via our client) who has an self build PAAS-version
> of postgresql. Ok, you would say, that's fine. But, there is always an
> but, we think the settings are not quite ok. We really want to know how
> much memory etc there is on that server. So we can recommend the
> recommended settings. Is there a way to get those information on the
> server itself from psql?

If you mean you e.g. only have access to psql on the server in question,
but want to know more about the server itself (i.e. not the PostgreSQL
settings), then you might have luck poking around with the pg_read_file()
function, e.g.:

SELECT pg_read_file('/proc/meminfo');

Note you will need database superuser permission to execute pg_read_file(),
or for non-superusers be granted EXECUTE permission on it.

Regards

Ian Barwick