Re: Functions and Indexes

[Moreno Andreo]

On 18/11/24 15:43, Gianni Ceccarelli wrote:

On Mon, 18 Nov 2024 15:37:57 +0100
Moreno Andreo  wrote:


If a column appears in the WHERE clause (and so it should be placed
in index), in case it is "processed" in a function (see below), is it
possible to insert this function to further narrow down things?

You probably want to look at
https://www.postgresql.org/docs/current/indexes-expressional.html


Hi Gianni,
    correct. That's a good starting point (now I have to add complexity 
since the index should have 4 columns, one of them is referenced with LIKE)

Thanks!
Moreno

Re: Functions and Indexes

[Moreno Andreo]

On 18/11/24 20:05, Laurenz Albe wrote:

On Mon, 2024-11-18 at 15:37 +0100, Moreno Andreo wrote:

I'm creating indexes for some tables, and I came across a doubt.

If a column appears in the WHERE clause (and so it should be placed in
index), in case it is "processed" in a function (see below), is it
possible to insert this function to further narrow down things?

Common index:
SELECT foo1, foo2 FROM bar WHERE foo1 = 2
CREATE index bar1_idx ON bar USING btree(foo1);

What about if query becomes
SELECT foo1, foo2 FROM bar WHERE (POSITION(foo1 IN 'blah blah') >0)

You could create an index like

   CREATE INDEX ON bar (position(foo1 IN 'blah blah'));

Alternatively, you could have a partial index:

   CREATE INDEX ON bar (foo1) INCLUDE (foo2)
   WHERE position(foo1 IN 'blah blah') > 0;

Interesting. Never seen this form, I'll look further on it.

I stumbled into
https://www.cybertec-postgresql.com/en/indexing-like-postgresql-oracle/
and discovered text_pattern_ops.
I'm wondering if it can be of any use in my index, that should hold a 
WHERE condition with a combination of LIKE and the POSITION expression 
above.

More docs to read ... :-)



Second question: I 've seen contrasting opinions about putting JOIN
parameters (ON a.field1 = b.field2) in an index and I'd like to know
your thoughts.

That depends on the join strategy PostgreSQL chooses.
You can use EXPLAIN to figure out the join strategy.
This article should explain details:
https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/

Very nice article, clear and easy to understand!


Yours,
Laurenz Albe



Thanks,
Moreno.

Re: Help with restoring database from old version of PostgreSQL

[Catherine Frock]

Do you know what versions of PG and pgAdmin4 I need? Also, I don't know
where to get a different version of PG.

--
Arden


On Tue, Nov 19, 2024 at 5:01 PM Ron Johnson  wrote:

> Ah, so it's a plain old SQL file.  That makes things much easier!!
>
> Here's the thing: PG (whether 9.6 or 16 or 17) is the database engine; no
> GUI, and just a basic command line interface.
> PgAdmin4 is a GUI interface to PG.  Newer versions of PgAdmin4 are not
> compatible with EOL PG versions like 9.6.
>
> What you need is *both* a new PG version *and* a new PgAdmin4 version.
>
> *Then* you need to:
> CREATE USER basic_user WITH PASSWORD 'random_horse';
>
> After that, you can run the "backup sql script".
>
> On Tue, Nov 19, 2024 at 4:47 PM Catherine Frock 
> wrote:
>
>> I am trying to restore a previously backed up database .sql file that was
>> created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I was
>> using).
>>
>> In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if so,
>> can you please provide a website where I can download PG16?
>>
>> In response to Adrian: How do I determine what version of PostGIS I need
>> to be using? When I installed it, I was only given one option of a version
>> to install.
>>
>> Since my previous installation of pgAdmin4 was not working, I have
>> started over today, uninstalling all versions of PostgreSQL that I had and
>> reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/ and
>> pgAdmin4 version 8.12 from https://www.pgadmin.org. I tried installing
>> PostgreSQL 9.6.24 also, but I received an error message when I run the
>> installation file: "An error occurred executing the Microsoft VC++ runtime
>> installer." I installed the latest versions of the PostGIS (3.5.0) and
>> psqlODBC drivers (I'm forgot to write that down and am not sure how to
>> check the version). My installation of PostgreSQL/PostGIS was verified
>> as successful. I am able to connect to the PostgreSQL 9.6 and 17 servers. I
>> set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin. I
>> have tried to restore my database using this in the command prompt: "psql.exe
>> db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty database
>> in both the PostgreSQL 9.6 and 17 servers, and this is the result:
>>
>> Password for user postgres:
>> SET
>> SET
>> SET
>> SET
>> SET
>> SET
>> SET
>> SET
>> WARNING:  database "db_2017" does not exist
>> COMMENT
>> CREATE SCHEMA
>>
>> [snip]
>
>> CREATE TRIGGER
>> ALTER TABLE
>> ALTER TABLE
>> ALTER TABLE
>> ERROR:  role "basic_user" does not exist
>> ERROR:  role "basic_user" does not exist
>> ERROR:  role "basic_user" does not exist
>> ERROR:  role "basic_user" does not exist
>> SET
>>
> [snip]
>
>> SET
>> ALTER DEFAULT PRIVILEGES
>> ERROR:  role "basic_user" does not exist
>> SET
>> ALTER DEFAULT PRIVILEGES
>> ERROR:  role "basic_user" does not exist
>> SET
>> ALTER DEFAULT PRIVILEGES
>> ERROR:  role "basic_user" does not exist
>> SET
>> ALTER DEFAULT PRIVILEGES
>> ERROR:  role "basic_user" does not exist
>>
>>
>
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!
>

Re: Help with restoring database from old version of PostgreSQL

[Adrian Klaver]

On 11/19/24 14:13, Catherine Frock wrote:
Do you know what versions of PG and pgAdmin4 I need? Also, I don't know 
where to get a different version of PG.




You need to take a step back and take stock of where you started and 
where you want to be.


To that end answer the following:

1) What are the Postgres and PostGIS versions you started with?

2) What database or databases do you want to move forward?

3) Is there a hard requirement for what currently supported version of 
Postgres you want to use?


4) What do you want to do with the new install?

5) What versions of Postgres, pgAdmin4 and PostGIS do you currently have 
installed?


6) You are looking at jumping a minimum of 4 major Postgres versions up 
to a maximum of 8 versions. You are also jumping some number of PostGIS 
versions as well. In either case you should at some point go over the 
release notes for each major version jump to see if anything is going to 
cause issues.



--
Arden




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

Re: Functions and Indexes

[Laurenz Albe]

On Tue, 2024-11-19 at 11:53 +0100, Moreno Andreo wrote:
> > > What about if query becomes
> > > SELECT foo1, foo2 FROM bar WHERE (POSITION(foo1 IN 'blah blah') >0)
> >
> > You could create an index like
> > 
> >     CREATE INDEX ON bar (position(foo1 IN 'blah blah'));
> > 
> > Alternatively, you could have a partial index:
> > 
> >     CREATE INDEX ON bar (foo1) INCLUDE (foo2)
> >     WHERE position(foo1 IN 'blah blah') > 0;
>
> Interesting. Never seen this form, I'll look further on it.
> 
> I stumbled into
> https://www.cybertec-postgresql.com/en/indexing-like-postgresql-oracle/
> and discovered text_pattern_ops.
> I'm wondering if it can be of any use in my index, that should hold a 
> WHERE condition with a combination of LIKE and the POSITION expression 
> above.
> More docs to read ... :-)

I don't think "text_pattern_ops" will help here - queries that use LIKE
to search for a substring (LIKE '%string%') cannot make use of a b-tree
index.

Yours,
Laurenz Albe

Specifying columns returned by a function, when the function is in a SELECT column list?

[Ron Johnson]

It's trivial to specify columns when a table-returning function is the FROM
clause, but how does one specify columns when the table-returning function
is a column in a SELECT clause?

I don't have the proper vocabulary to know what to Google for.

Examples below:

Easy:
cdsbmop=# select downstream_table, downstream_column, downstream_index
cdsbmop-# from dba.get_fk_referenced_by('cdsschema.retention_policy_code');
   downstream_table|downstream_column |
 downstream_index
---+--+-
 cdsschema.bank_item_type_ret  | retention_policy_code_id |
ALERT!!! MISSING INDEX
 cdsschema.bank_item_type_ret_hist | retention_policy_code_id |
ALERT!!! MISSING INDEX
 cdsschema.customer_item_type_ret  | retention_policy_code_id |
idx_customer_item_type_ret_retention_policy_code_id
 cdsschema.customer_item_type_ret_hist | retention_policy_code_id |
ALERT!!! MISSING INDEX
 cdsschema.ex_data_partition_policy| retention_policy_code_id |
ALERT!!! MISSING INDEX
 cdsschema.lockbox_item_type_ret   | retention_policy_code_id |
idx_lockbox_item_type_ret_retention_policy_code_id
 cdsschema.lockbox_item_type_ret_hist  | retention_policy_code_id |
ALERT!!! MISSING INDEX
(7 rows)

cdsbmop=# select table_name
from dba.latest_vacuum_and_analyze
where table_name = 'retention_policy_code';
  table_name
---
 retention_policy_code
(1 row)

Here, I want to only specify some of the dba.get_fk_referenced_by() columns:

cdsbmop=# select dba.get_fk_referenced_by('cdsschema.'||table_name)
from dba.latest_vacuum_and_analyze where table_name =
'retention_policy_code';

 get_fk_referenced_by

--
 
(retention_policy_code_id,cdsschema.bank_item_type_ret,retention_policy_code_id,"ALERT!!!
MISSING INDEX")
 
(retention_policy_code_id,cdsschema.bank_item_type_ret_hist,retention_policy_code_id,"ALERT!!!
MISSING INDEX")
 
(retention_policy_code_id,cdsschema.customer_item_type_ret,retention_policy_code_id,idx_customer_item_type_ret_retention_policy_code_id)
 
(retention_policy_code_id,cdsschema.customer_item_type_ret_hist,retention_policy_code_id,"ALERT!!!
MISSING INDEX")
 
(retention_policy_code_id,cdsschema.ex_data_partition_policy,retention_policy_code_id,"ALERT!!!
MISSING INDEX")
 
(retention_policy_code_id,cdsschema.lockbox_item_type_ret,retention_policy_code_id,idx_lockbox_item_type_ret_retention_policy_code_id)
 
(retention_policy_code_id,cdsschema.lockbox_item_type_ret_hist,retention_policy_code_id,"ALERT!!!
MISSING INDEX")
(7 rows)


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

Re: Functions and Indexes

[Laurenz Albe]

On Tue, 2024-11-19 at 14:30 +0100, Moreno Andreo wrote:
> Inhttps://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/
>  you say 
>  "Note that for inner joins there is no distinction between the join 
> condition and the WHERE condition, but that doesn't hold for outer joins."
>  What do you mean?

CREATE TABLE a (id integer);

INSERT INTO a VALUES (1), (2), (3);

CREATE TABLE b (id integer);

INSERT INTO b VALUES (1), (2), (4);

SELECT * FROM a JOIN b ON a.id = b.id AND b.id < 2;
 id │ id 
╪
  1 │  1
(1 row)

SELECT * FROM a JOIN b ON a.id = b.id WHERE b.id < 2;
 id │ id 
╪
  1 │  1
(1 row)

SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.id < 2;
 id │ id 
╪
  1 │  1
  2 │  ∅
  3 │  ∅
(3 rows)

SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.id < 2;
 id │ id 
╪
  1 │  1
(1 row)

Yours,
Laurenz Albe

Re: Help with restoring database from old version of PostgreSQL

[Adrian Klaver]

On 11/19/24 1:47 PM, Catherine Frock wrote:
I am trying to restore a previously backed up database .sql file that 
was created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version 
I was using).


In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if 
so, can you please provide a website where I can download PG16?


In response to Adrian: How do I determine what version of PostGIS I need 
to be using? When I installed it, I was only given one option of a 
version to install.


Go here:

https://postgis.net/development/source_code/

and look in the release notes. They will tell you what range of Postgres 
versions are supported. For instance the latest 3.5.0:


https://git.osgeo.org/gitea/postgis/postgis/raw/tag/3.5.0/NEWS

"To take advantage of all postgis_sfcgal extension features SFCGAL 1.5+ 
is needed. PostgreSQL 12-17 required. GEOS 3.8+ required. Proj 6.1+ 
required. "




Since my previous installation of pgAdmin4 was not working, I have 
started over today, uninstalling all versions of PostgreSQL that I had 
and reinstalling PostgreSQL Version 17.0 from 
https://www.enterprisedb.com/  and 
pgAdmin4 version 8.12 from https://www.pgadmin.org 
. I tried installing PostgreSQL 9.6.24 also, 
but I received an error message when I run the installation file: "An 
error occurred executing the Microsoft VC++ runtime installer." I 
installed the latest versions of the PostGIS (3.5.0) and psqlODBC 
drivers (I'm forgot to write that down and am not sure how to check the 
version). My installation of PostgreSQL/PostGIS was verified as 
successful. I am able to connect to the PostgreSQL 9.6 and 17 servers. I 
set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin. I 
have tried to restore my database using this in the command prompt: 
"psql.exe db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an 
empty database in both the PostgreSQL 9.6 and 17 servers, and this is 
the result:


1) Is db_2017 a database you had on old server?

2) What backup did you do in pgAdmin4 for the 9.6 instance?

Did you do the entire server:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_server_dialog.html

or backup just one database?:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_dialog.html

If the latter then you did not get the global objects for the Postgres 
9.6 instance. This includes roles(users) and would account for the user 
errors.


To get the global objects use:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_globals_dialog.html


Password for user postgres:
SET
SET
SET
SET
SET
SET
SET
SET
WARNING:  database "db_2017" does not exist
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE VIEW
ALTER TABLE
COMMENT
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
COPY 0
  setval

       1
(1 row)

COPY 0
SET
COPY 3
COPY 7
COPY 6
COPY 3
SET
COPY 67
COPY 37649
  setval

   38344
(1 row)

COPY 38783
COPY 38344
  setval

   41058
(1 row)

COPY 81
COPY 81
  setval

     654
(1 row)

SET
COPY 35
COPY 40
COPY 39
COPY 35
COPY 35
COPY 0
COPY 58
COPY 39
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
COMMENT
CREATE TRIGGER
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
ERRO

Re: Help with restoring database from old version of PostgreSQL

[Ron Johnson]

You say that you just installed PG17.  That's perfectly fine.   If pgAdmin4
version 8.12 is recent, then it's fine to use that, too.

The thing is that sql files are just... a bunch of SQL statements.
Whatever SQL is generated by v9.6 will be easily processed by a modern
version.

On Tue, Nov 19, 2024 at 5:13 PM Catherine Frock  wrote:

> Do you know what versions of PG and pgAdmin4 I need? Also, I don't know
> where to get a different version of PG.
>
> --
> Arden
>
>
> On Tue, Nov 19, 2024 at 5:01 PM Ron Johnson 
> wrote:
>
>> Ah, so it's a plain old SQL file.  That makes things much easier!!
>>
>> Here's the thing: PG (whether 9.6 or 16 or 17) is the database engine; no
>> GUI, and just a basic command line interface.
>> PgAdmin4 is a GUI interface to PG.  Newer versions of PgAdmin4 are not
>> compatible with EOL PG versions like 9.6.
>>
>> What you need is *both* a new PG version *and* a new PgAdmin4 version.
>>
>> *Then* you need to:
>> CREATE USER basic_user WITH PASSWORD 'random_horse';
>>
>> After that, you can run the "backup sql script".
>>
>> On Tue, Nov 19, 2024 at 4:47 PM Catherine Frock 
>> wrote:
>>
>>> I am trying to restore a previously backed up database .sql file that
>>> was created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I
>>> was using).
>>>
>>> In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if
>>> so, can you please provide a website where I can download PG16?
>>>
>>> In response to Adrian: How do I determine what version of PostGIS I need
>>> to be using? When I installed it, I was only given one option of a version
>>> to install.
>>>
>>> Since my previous installation of pgAdmin4 was not working, I have
>>> started over today, uninstalling all versions of PostgreSQL that I had and
>>> reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/ and
>>> pgAdmin4 version 8.12 from https://www.pgadmin.org. I tried installing
>>> PostgreSQL 9.6.24 also, but I received an error message when I run the
>>> installation file: "An error occurred executing the Microsoft VC++ runtime
>>> installer." I installed the latest versions of the PostGIS (3.5.0) and
>>> psqlODBC drivers (I'm forgot to write that down and am not sure how to
>>> check the version). My installation of PostgreSQL/PostGIS was verified
>>> as successful. I am able to connect to the PostgreSQL 9.6 and 17 servers. I
>>> set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin.
>>> I have tried to restore my database using this in the command prompt: 
>>> "psql.exe
>>> db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty database
>>> in both the PostgreSQL 9.6 and 17 servers, and this is the result:
>>>
>>> Password for user postgres:
>>> SET
>>> SET
>>> SET
>>> SET
>>> SET
>>> SET
>>> SET
>>> SET
>>> WARNING:  database "db_2017" does not exist
>>> COMMENT
>>> CREATE SCHEMA
>>>
>>> [snip]
>>
>>> CREATE TRIGGER
>>> ALTER TABLE
>>> ALTER TABLE
>>> ALTER TABLE
>>> ERROR:  role "basic_user" does not exist
>>> ERROR:  role "basic_user" does not exist
>>> ERROR:  role "basic_user" does not exist
>>> ERROR:  role "basic_user" does not exist
>>> SET
>>>
>> [snip]
>>
>>> SET
>>> ALTER DEFAULT PRIVILEGES
>>> ERROR:  role "basic_user" does not exist
>>> SET
>>> ALTER DEFAULT PRIVILEGES
>>> ERROR:  role "basic_user" does not exist
>>> SET
>>> ALTER DEFAULT PRIVILEGES
>>> ERROR:  role "basic_user" does not exist
>>> SET
>>> ALTER DEFAULT PRIVILEGES
>>> ERROR:  role "basic_user" does not exist
>>>
>>>
>>
>> Death to , and butter sauce.
>> Don't boil me, I'm still alive.
>>  lobster!
>>
>

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

Re: Help with restoring database from old version of PostgreSQL

[Catherine Frock]

I am trying to restore a previously backed up database .sql file that was
created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I was
using).

In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if so,
can you please provide a website where I can download PG16?

In response to Adrian: How do I determine what version of PostGIS I need to
be using? When I installed it, I was only given one option of a version to
install.

Since my previous installation of pgAdmin4 was not working, I have started
over today, uninstalling all versions of PostgreSQL that I had and
reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/ and
pgAdmin4 version 8.12 from https://www.pgadmin.org. I tried installing
PostgreSQL 9.6.24 also, but I received an error message when I run the
installation file: "An error occurred executing the Microsoft VC++ runtime
installer." I installed the latest versions of the PostGIS (3.5.0) and
psqlODBC drivers (I'm forgot to write that down and am not sure how to
check the version). My installation of PostgreSQL/PostGIS was verified as
successful. I am able to connect to the PostgreSQL 9.6 and 17 servers. I
set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin. I
have tried to restore my database using this in the command prompt: "psql.exe
db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty database
in both the PostgreSQL 9.6 and 17 servers, and this is the result:

Password for user postgres:
SET
SET
SET
SET
SET
SET
SET
SET
WARNING:  database "db_2017" does not exist
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE VIEW
ALTER TABLE
COMMENT
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
COPY 0
 setval

  1
(1 row)

COPY 0
SET
COPY 3
COPY 7
COPY 6
COPY 3
SET
COPY 67
COPY 37649
 setval

  38344
(1 row)

COPY 38783
COPY 38344
 setval

  41058
(1 row)

COPY 81
COPY 81
 setval

654
(1 row)

SET
COPY 35
COPY 40
COPY 39
COPY 35
COPY 35
COPY 0
COPY 58
COPY 39
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
COMMENT
CREATE TRIGGER
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
SET
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
ERROR:  role "basic_user" does not exist
SET
ALTER DEFAULT PRIVILEGES
ERROR:  role "basic_user" does not exist
SET
ALTER DEFAULT PRIVILEGES
ERROR:  role "basic_user" does not exist
SET
ALTER DEFAULT PRIVILEGES
ERROR:  role "basic_user" does not exist
SET
ALTER DEFAULT PRIVILEGES
ERROR:  role "basic_user" does not exist

--
Arden


On Thu, Nov 14, 2024 at 12:02 PM Adrian Klaver 
wrote:

> On 11/14/24 07:00, Catherine Frock wrote:
> > Yes, I still have the backup file. I tried to open pgAdmin4 today to see
> > what version I'm running, and now this happened (see attachments). My
> > operating system is Windows 10 Home, version 10.0.19045 Build 19045. I
> > installed Postgres using these instructions:
> >
> https://basille.org/postgis2017/installation_instructions/install_postgresql.html
> <
> https://basille.org/postgis2017/installation_instructions/install_postgresql.html>
>  I'm
> guessing 

Re: Running docker in postgres, SHM size of the docker container in postgres 16

[Thomas Munro]

On Wed, Nov 20, 2024 at 11:22 AM Koen De Groote  wrote:
> Why would that be? It's the exact same data. The install is about 50GB in 
> size. Is there something wrong with postgres 16, or did some settings 
> significantly change, that I need to know about? I went over all the 
> changelogs, nothing stood out as relevant to this, but that might be a lack 
> of experience on my end.

Parallel vacuum arrived in PostgreSQL 13, and that uses "dynamic
shared memory" to share state between workers, and assuming
dynamic_shared_memory_type=posix, that means shm_open(), which opens
files under /dev/shm on Linux.

Re: Re : Credcheck extension

[張宸瑋]

Thank you for your help!After applying the patch, the above issue has been
resolved.

 I have another question: After identifying who is in the banned_role, the
GitHub example uses the command SELECT pg_banned_role_reset(); to unlock
everyone. I would like to know if there is a way to unlock a specific
individual rather than unlocking everyone.

Adrian Klaver 於 2024年11月20日 週三，上午12:25寫道：

> On 11/19/24 00:40, 張宸瑋 wrote:
> > Sorry for the inconvenience, but I used make and make install to build
> > the credcheck--2.8.0.sql sources zip file. I would like to ask how I can
> > update and apply the changes to the system, as I modified the files in
> > credcheck/test/expected/06_reuse_interval.out and
> > credcheck/test/sql/06_reuse_interval.sql. However, after running make
> > and make install again, I don’t see any changes.
>
> Pretty sure you need to do:
>
> make clean
>
> first, then the rest of the install process.
>
> That process is shown here:
>
> https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation
>
>
> >
> >
> > Adrian Klaver  > >於 2024年11月18日 週一，下午11:15寫道：
> >
> > On 11/18/24 01:03, 張宸瑋 wrote:
> >  > Hello!
> >  >I would like to inquire about the installation of the credcheck
> >  > third-party package to support password complexity and expiration
> > date,
> >  > etc., when setting up open-source PostgreSQL. I am using the
> >  > credcheck--2.8.0.sql version from GitHub. After completing the
> > setup, I
> >  > encountered the following issue: when an account exceeds the
> > configured
> >  > number of incorrect login attempts, it gets locked. The command
> > SELECT *
> >  > FROM pg_banned_role; should display the columns roleid,
> > failure_count,
> >  > and banned_date, and the view is working properly and shows the
> >  > information. However, according to the example, the roleid does
> not
> >  > correctly display the corresponding oid for the account with
> failed
> >  > login attempts. I would like to ask if there is a solution for
> this
> >  > issue. Thank you!
> >
> > Have you looked a?:
> >
> > https://github.com/HexaCluster/credcheck/issues/39
> > 
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Running docker in postgres, SHM size of the docker container in postgres 16

[Koen De Groote]

Hello all,

Assume a machine with 16 CPU cores and 64GB of RAM. 300 max connections.

Postgres is running here, inside a docker container. Docker containers
receive a small amount of shared memory from /dev/shm

Experience teaches that the default 64MB of /dev/shm for the postgres
container, is not enough for databases of even 50GB in size. Previously
I've increased the /dev/shm setting to 256MB for the postgres container.
Issues that made this necessary all came in the form of too many queries
running at once, asking too much memory space, I assume.

I recently upgraded a setup from postgres 11 to postgres 16 via logical
replication, and found that running "vacuum(analyze, verbose)" fails unless
I give the container 1GB of /dev/shm. Where previously 256MB sufficed on
the old postgres 11 version.

Why would that be? It's the exact same data. The install is about 50GB in
size. Is there something wrong with postgres 16, or did some settings
significantly change, that I need to know about? I went over all the
changelogs, nothing stood out as relevant to this, but that might be a lack
of experience on my end.

Regards,
Koen De Groote

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Paul Foerster]

Hi Adrian,

> On 19 Nov 2024, at 17:17, Adrian Klaver  wrote:
> 
> Read this:
> 
> https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/
> 
> and hold off awhile.

Thanks very much. I will.

Cheers,
Paul

Re: Re : Credcheck extension

[張宸瑋]

Thank you for your help.Problem solved!

Adrian Klaver 於 2024年11月20日 週三，下午12:04寫道：

> On 11/19/24 19:01, 張宸瑋 wrote:
> > Thank you for your help!After applying the patch, the above issue has
> > been resolved.
> >
> >   I have another question: After identifying who is in the banned_role,
> > the GitHub example uses the command SELECT pg_banned_role_reset(); to
> > unlock everyone. I would like to know if there is a way to unlock a
> > specific individual rather than unlocking everyone.
>
> It's in the docs:
>
> https://github.com/hexacluster/credcheck?tab=readme-ov-file#examples
>
> Authentication failure ban
>
> "A superuser can also reset the content of the banned user cache by
> calling a function named public.pg_banned_role_reset(). If it is called
> without an argument, all the banned cache will be cleared. To only
> remove the record registered for a single user, just pass his name as
> parameter. This function returns the number of records removed from the
> cache. A restart of PostgreSQL also clear the cache."
>
> I would suggest reading the entire documentation.
>
> >
> > Adrian Klaver  > >於 2024年11月20日 週三，上午12:25寫道：
> >
> > On 11/19/24 00:40, 張宸瑋 wrote:
> >  > Sorry for the inconvenience, but I used make and make install to
> > build
> >  > the credcheck--2.8.0.sql sources zip file. I would like to ask
> > how I can
> >  > update and apply the changes to the system, as I modified the
> > files in
> >  > credcheck/test/expected/06_reuse_interval.out and
> >  > credcheck/test/sql/06_reuse_interval.sql. However, after running
> > make
> >  > and make install again, I don’t see any changes.
> >
> > Pretty sure you need to do:
> >
> > make clean
> >
> > first, then the rest of the install process.
> >
> > That process is shown here:
> >
> >
> https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation <
> https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation>
> >
> >
> >  >
> >  >
> >  > Adrian Klaver  > 
> >  >  > >>於 2024年11月18日 週一，下午
> > 11:15寫道：
> >  >
> >  > On 11/18/24 01:03, 張宸瑋 wrote:
> >  >  > Hello!
> >  >  >I would like to inquire about the installation of the
> > credcheck
> >  >  > third-party package to support password complexity and
> > expiration
> >  > date,
> >  >  > etc., when setting up open-source PostgreSQL. I am using
> the
> >  >  > credcheck--2.8.0.sql version from GitHub. After completing
> the
> >  > setup, I
> >  >  > encountered the following issue: when an account exceeds
> the
> >  > configured
> >  >  > number of incorrect login attempts, it gets locked. The
> > command
> >  > SELECT *
> >  >  > FROM pg_banned_role; should display the columns roleid,
> >  > failure_count,
> >  >  > and banned_date, and the view is working properly and
> > shows the
> >  >  > information. However, according to the example, the roleid
> > does not
> >  >  > correctly display the corresponding oid for the account
> > with failed
> >  >  > login attempts. I would like to ask if there is a solution
> > for this
> >  >  > issue. Thank you!
> >  >
> >  > Have you looked a?:
> >  >
> >  > https://github.com/HexaCluster/credcheck/issues/39
> > 
> >  >  > >
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> >  >>
> >  >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Help with restoring database from old version of PostgreSQL

[Ron Johnson]

Ah, so it's a plain old SQL file.  That makes things much easier!!

Here's the thing: PG (whether 9.6 or 16 or 17) is the database engine; no
GUI, and just a basic command line interface.
PgAdmin4 is a GUI interface to PG.  Newer versions of PgAdmin4 are not
compatible with EOL PG versions like 9.6.

What you need is *both* a new PG version *and* a new PgAdmin4 version.

*Then* you need to:
CREATE USER basic_user WITH PASSWORD 'random_horse';

After that, you can run the "backup sql script".

On Tue, Nov 19, 2024 at 4:47 PM Catherine Frock  wrote:

> I am trying to restore a previously backed up database .sql file that was
> created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I was
> using).
>
> In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if so,
> can you please provide a website where I can download PG16?
>
> In response to Adrian: How do I determine what version of PostGIS I need
> to be using? When I installed it, I was only given one option of a version
> to install.
>
> Since my previous installation of pgAdmin4 was not working, I have started
> over today, uninstalling all versions of PostgreSQL that I had and
> reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/ and
> pgAdmin4 version 8.12 from https://www.pgadmin.org. I tried installing
> PostgreSQL 9.6.24 also, but I received an error message when I run the
> installation file: "An error occurred executing the Microsoft VC++ runtime
> installer." I installed the latest versions of the PostGIS (3.5.0) and
> psqlODBC drivers (I'm forgot to write that down and am not sure how to
> check the version). My installation of PostgreSQL/PostGIS was verified as
> successful. I am able to connect to the PostgreSQL 9.6 and 17 servers. I
> set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin. I
> have tried to restore my database using this in the command prompt: "psql.exe
> db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty database
> in both the PostgreSQL 9.6 and 17 servers, and this is the result:
>
> Password for user postgres:
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> WARNING:  database "db_2017" does not exist
> COMMENT
> CREATE SCHEMA
>
> [snip]

> CREATE TRIGGER
> ALTER TABLE
> ALTER TABLE
> ALTER TABLE
> ERROR:  role "basic_user" does not exist
> ERROR:  role "basic_user" does not exist
> ERROR:  role "basic_user" does not exist
> ERROR:  role "basic_user" does not exist
> SET
>
[snip]

> SET
> ALTER DEFAULT PRIVILEGES
> ERROR:  role "basic_user" does not exist
> SET
> ALTER DEFAULT PRIVILEGES
> ERROR:  role "basic_user" does not exist
> SET
> ALTER DEFAULT PRIVILEGES
> ERROR:  role "basic_user" does not exist
> SET
> ALTER DEFAULT PRIVILEGES
> ERROR:  role "basic_user" does not exist
>
>

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

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Paul Foerster]

Hi Alvaro,

> On 19 Nov 2024, at 17:34, Alvaro Herrera  wrote:
> 
> It doesn't really matter when you do it, because the constraint only
> gets broken by running DETACH with the old server code.  You have
> already run the DETACH sometime in the past (that's how the constraint
> got broken), which means you would not run it again now to the same
> table.  The old server code will behave correctly when doing ADD / DROP
> constraint, as will the new server code.  Feel free to run it when it's
> more convenient to you.

Thanks.

> I'd advise against running ALTER TABLE DETACH until you have upgraded,
> however -- at least, for partitioned tables that have foreign keys
> pointing to other partitioned tables.

I have no influence on that. It's a third party application. In fact, I can't 
even do much about the applications developed inhouse because they're too big. 
That means, if there is anything built into an application, then it takes many 
moons to get it out again.

Cheers,
Paul

PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Paul Foerster]

Hi,

the PostgreSQL 15.9 release notes instruct to look out for especially detached 
partitions with foreign key constraints. I'm in the process of updating our 
databases from 15.8 to 15.9 now and found a case where the select statement 
returns a constraint.

The release notes say nothing about when to fix that using the generated add or 
drop statements.

Do I want/need to do that before or after I exchange the software? And if it is 
to be done on a particular of the two releases, why is that?

https://www.postgresql.org/docs/15/release-15-9.html
Section E.1.2, changelog entry 5.

Any insight would be highly appreciated. Thanks in advance.

Cheers
Paul

Re: Re : Credcheck extension

[Adrian Klaver]

On 11/19/24 19:01, 張宸瑋 wrote:
Thank you for your help!After applying the patch, the above issue has 
been resolved.


  I have another question: After identifying who is in the banned_role, 
the GitHub example uses the command SELECT pg_banned_role_reset(); to 
unlock everyone. I would like to know if there is a way to unlock a 
specific individual rather than unlocking everyone.


It's in the docs:

https://github.com/hexacluster/credcheck?tab=readme-ov-file#examples

Authentication failure ban

"A superuser can also reset the content of the banned user cache by 
calling a function named public.pg_banned_role_reset(). If it is called 
without an argument, all the banned cache will be cleared. To only 
remove the record registered for a single user, just pass his name as 
parameter. This function returns the number of records removed from the 
cache. A restart of PostgreSQL also clear the cache."


I would suggest reading the entire documentation.



Adrian Klaver >於 2024年11月20日 週三，上午12:25寫道：


On 11/19/24 00:40, 張宸瑋 wrote:
 > Sorry for the inconvenience, but I used make and make install to
build
 > the credcheck--2.8.0.sql sources zip file. I would like to ask
how I can
 > update and apply the changes to the system, as I modified the
files in
 > credcheck/test/expected/06_reuse_interval.out and
 > credcheck/test/sql/06_reuse_interval.sql. However, after running
make
 > and make install again, I don’t see any changes.

Pretty sure you need to do:

make clean

first, then the rest of the install process.

That process is shown here:

https://github.com/hexacluster/credcheck?tab=readme-ov-file#installation 



 >
 >
 > Adrian Klaver mailto:adrian.kla...@aklaver.com>
 > >>於 2024年11月18日 週一，下午
11:15寫道：
 >
 >     On 11/18/24 01:03, 張宸瑋 wrote:
 >      > Hello!
 >      >    I would like to inquire about the installation of the
credcheck
 >      > third-party package to support password complexity and
expiration
 >     date,
 >      > etc., when setting up open-source PostgreSQL. I am using the
 >      > credcheck--2.8.0.sql version from GitHub. After completing the
 >     setup, I
 >      > encountered the following issue: when an account exceeds the
 >     configured
 >      > number of incorrect login attempts, it gets locked. The
command
 >     SELECT *
 >      > FROM pg_banned_role; should display the columns roleid,
 >     failure_count,
 >      > and banned_date, and the view is working properly and
shows the
 >      > information. However, according to the example, the roleid
does not
 >      > correctly display the corresponding oid for the account
with failed
 >      > login attempts. I would like to ask if there is a solution
for this
 >      > issue. Thank you!
 >
 >     Have you looked a?:
 >
 > https://github.com/HexaCluster/credcheck/issues/39

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

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Alvaro Herrera]

Hi Paul,

On 2024-Nov-19, Paul Foerster wrote:

> the PostgreSQL 15.9 release notes instruct to look out for especially
> detached partitions with foreign key constraints. I'm in the process
> of updating our databases from 15.8 to 15.9 now and found a case where
> the select statement returns a constraint.
> 
> The release notes say nothing about when to fix that using the
> generated add or drop statements.
> 
> Do I want/need to do that before or after I exchange the software? And
> if it is to be done on a particular of the two releases, why is that?

It doesn't really matter when you do it, because the constraint only
gets broken by running DETACH with the old server code.  You have
already run the DETACH sometime in the past (that's how the constraint
got broken), which means you would not run it again now to the same
table.  The old server code will behave correctly when doing ADD / DROP
constraint, as will the new server code.  Feel free to run it when it's
more convenient to you.

I'd advise against running ALTER TABLE DETACH until you have upgraded,
however -- at least, for partitioned tables that have foreign keys
pointing to other partitioned tables.

Thanks,

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Postgres is bloatware by design: it was built to house
 PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Tom Lane]

Paul Foerster  writes:
> the PostgreSQL 15.9 release notes instruct to look out for especially 
> detached partitions with foreign key constraints. I'm in the process of 
> updating our databases from 15.8 to 15.9 now and found a case where the 
> select statement returns a constraint.

> The release notes say nothing about when to fix that using the generated add 
> or drop statements.

Generally speaking, our release notes are addressed to someone who's
already installed the update (or a later one).

regards, tom lane

Re: Re : Credcheck extension

[張宸瑋]

Sorry for the inconvenience, but I used make and make install to build the
credcheck--2.8.0.sql sources zip file. I would like to ask how I can update
and apply the changes to the system, as I modified the files in
credcheck/test/expected/06_reuse_interval.out and
credcheck/test/sql/06_reuse_interval.sql. However, after running make and
make install again, I don’t see any changes.


Adrian Klaver 於 2024年11月18日 週一，下午11:15寫道：

> On 11/18/24 01:03, 張宸瑋 wrote:
> > Hello!
> >I would like to inquire about the installation of the credcheck
> > third-party package to support password complexity and expiration date,
> > etc., when setting up open-source PostgreSQL. I am using the
> > credcheck--2.8.0.sql version from GitHub. After completing the setup, I
> > encountered the following issue: when an account exceeds the configured
> > number of incorrect login attempts, it gets locked. The command SELECT *
> > FROM pg_banned_role; should display the columns roleid, failure_count,
> > and banned_date, and the view is working properly and shows the
> > information. However, according to the example, the roleid does not
> > correctly display the corresponding oid for the account with failed
> > login attempts. I would like to ask if there is a solution for this
> > issue. Thank you!
>
> Have you looked a?:
>
> https://github.com/HexaCluster/credcheck/issues/39
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Functions and Indexes

[Moreno Andreo]

On 19/11/24 12:34, Laurenz Albe wrote:

On Tue, 2024-11-19 at 11:53 +0100, Moreno Andreo wrote:

What about if query becomes
SELECT foo1, foo2 FROM bar WHERE (POSITION(foo1 IN 'blah blah') >0)

You could create an index like

     CREATE INDEX ON bar (position(foo1 IN 'blah blah'));

Alternatively, you could have a partial index:

     CREATE INDEX ON bar (foo1) INCLUDE (foo2)
     WHERE position(foo1 IN 'blah blah') > 0;

Interesting. Never seen this form, I'll look further on it.

I stumbled into
https://www.cybertec-postgresql.com/en/indexing-like-postgresql-oracle/
and discovered text_pattern_ops.
I'm wondering if it can be of any use in my index, that should hold a
WHERE condition with a combination of LIKE and the POSITION expression
above.
More docs to read ... :-)

I don't think "text_pattern_ops" will help here - queries that use LIKE
to search for a substring (LIKE '%string%') cannot make use of a b-tree
index.
Oh, OK, i was happy to use BTREEs 'cause I had some issues with GIN/GIST 
(like indexes way bigger than table and so inefficient). OK, I'll stick 
with these and try harder to obtain better results.


One thing I can't understand well.
In 
https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/

you say
"Note that for inner joins there is no distinction between the join 
condition and the|WHERE|condition, but that doesn't hold for outer joins."

What do you mean?

Thanks
Moreno

Re: PostgreSQL 15.9 Update: Partitioned tables with foreign key constraints

[Paul Foerster]

Hi Tom,

> On 19 Nov 2024, at 17:25, Tom Lane  wrote:
> 
> Generally speaking, our release notes are addressed to someone who's
> already installed the update (or a later one).

Thank you for the advice.

Cheers,
Paul