Re: Request for information about postgres version 15.2 stability

[Stéphane Dunand]

Le 18/04/2023 à 08:56, gayathri ramesh a écrit :

Hi Team,

Our company is currently supporting around 100+ postgres databases and 
we are planning to upgrade to version 15.2. We want to ensure that 
there are no major critical bugs that could impact our production systems.


I would appreciate if you could provide us with information about the 
stability of postgres version 15.2. We are particularly interested in 
knowing if there are any known critical bugs or issues that could 
potentially impact the system.


Thanks in advance. looking forward to hearing from you.

Best Regards.

a great summary of what was done
https://why-upgrade.depesz.com/show?from=10.23&to=15.2&keywords=

Re: Guidance on INSERT RETURNING order

[Federico]

On Tue, 18 Apr 2023 at 11:53, John Howroyd  wrote:
>
> Sorry, I may have jumped to a conclusion that's not quite correct.

Not at all, thanks for moving this along

> On Mon, 17 Apr 2023 at 23:58, Federico  wrote:
>>
>> On Tue, 18 Apr 2023 at 00:21, John Howroyd  wrote:
>> > ...
>> >
>> > Personally, I didn't see Frederico's comment as anything to do with order; 
>> > just how one could output additional values in the RETURNING clause 
>> > (namely, v.num from a subexpression of the SELECT but in whatever order it 
>> > comes).  On the other hand, that seems a lot more complicated to me 
>> > because it is not an expression in the overall SELECT feeding the INSERT, 
>> > whereas the WITH ORDINALITY is a specific declaration to match input order 
>> > with output order by inserting a counter.
>>
>> I didn't mean to suggest any particular order should be kept by insert
>> or by returning. I was merely commenting on the David G. Johnston
>> reply
>>
>>  I suppose breaking the restriction that only columns present on
>> the insertion-table can be returned is a possible option that also
>> solves another infrequent request.
>>
>>
>> ...
>> Best,
>>   Federico
>
>
> This might be a possibility.  The v.num (from the original example) is 
> accessible in the outer select, so one can a envisage a system to handle 
> this, but at (presumably) much greater expense: preparation of the SELECT, 
> orchestration of the output rows (some fields for INSERT some for RETURNING) 
> and (presumably) a whole load of mem copies to RETURNING.  Is this something 
> to consider (perhaps just for feasibility while writing an initial patch 
> based on WITH ORDINALITY)?
>
> To put it another way, v.num is a valid expression in the overall SELECT, but 
> still a lot more complicated (and presumably expensive).

I think it's probably best to focus only on INSERT VALUES WITH
ORDINALITY. Further enhancements can be added later if they seem
useful.

Best,
  Federico

COPY RETURNING?

[Dominique Devienne]

Hi.

We are switching a schema type's surrogate/primary key, from `uuid` to
`int`.
That schema has parent-child relationships enforced with foreign-keys.
Our ingestion/schema-loading code uses COPY FROM STDIN BINARY.

Before, the SK/PK was generated client-side, as random uuid.
The ingestion code maintained client-side maps from NKs to uuids,
to be used for FK columns in child-tables COPY'd later.

But now that the SK/PK is an integer identity column generated server-side,
thanks to an implicitly-created sequence, we need that generated per-row
`int` PK.
With a normal prepared statement, we'd use a RETURNING clause, to avoid
a separate round-trip to the server. Can the same somehow be achieved with
COPY?

I'm afraid the answer is no, but I want to ask anyway, maybe there's a way
or work-around?

At the end of the COPY, we do get a ResultSet, so API-wise it was be
possible for it
to contain some rows, I guess, but I'm not sure the COPY protocol supports
returning
rows, nor what the syntax would be to have a COPY RETURNING form of COPY.

Thanks for any insights. --DD

psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

[gzh]

Hi, 




I upgraded the version of PostgreSQL from 12.10 to 12.13, 

when I insert data into the t_mstr table, the to_char function in the t_mstr's 
trigger caused the following error.




psql:t_mstr.sql:994: ERROR:  function to_char(numeric) does not exist




There is no problem before the upgrade and to_char(numeric) function comes from 
the Orafce extension.

The configuration of the old and new databases is as follows.




Database server (old): PostgreSQL 12.10（orafce3.15）

Database server (new): PostgreSQL 12.13（orafce3.24）




The new database has successfully installed the orafce 3.24 extension.

It does not occur in "PostgreSQL 12.10 and orafce 3.15", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.10 and 12.13 or the difference between 
orafce 3.15 and 3.24 is suspicious.




What is the reason for the problem?




Regards

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

[Erik Wienhold]

> On 19/04/2023 15:24 CEST gzh  wrote:
>
> Hi,
>
> I upgraded the version of PostgreSQL from 12.10 to 12.13,

Better upgrade to latest release 12.14.

> when I insert data into the t_mstr table, the to_char function in the t_mstr's
> trigger caused the following error.
>
> psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
>
> There is no problem before the upgrade and to_char(numeric) function comes
> from the Orafce extension.
> The configuration of the old and new databases is as follows.
>
> Database server (old): PostgreSQL 12.10（orafce3.15）
> Database server (new): PostgreSQL 12.13（orafce3.24）
>
> The new database has successfully installed the orafce 3.24 extension.
> It does not occur in "PostgreSQL 12.10 and orafce 3.15",
> but occurs in "PostgreSQL 12.13 and orafce 3.24",
> so either the difference between 12.10 and 12.13 or the difference between
> orafce 3.15 and 3.24 is suspicious.
>
> What is the reason for the problem?

orafce 3.22 moved functions to schema oracle:

https://github.com/orafce/orafce/blob/b492a0f50d5ee866c9870f886401d7c34ad8ccb3/NEWS#L4,L5
https://github.com/orafce/orafce/commit/86a1b51440ca33a04ef47fc3bb704dee26d16753

--
Erik

Joins of data-modifying CTE with the target table

[Alex Bolenok]

Hi list,

This popped up yesterday during a discussion at the Boston PostgreSQL group
meetup, and Jesper Pedersen had advised that I post it here.

Imagine this setup:

CREATE TABLE IF NOT EXISTS mytable (id BIGSERIAL PRIMARY KEY, value TEXT
NOT NULL);

WITHinsert_cte AS
(
INSERT
INTOmytable (value)
VALUES  ('test')
RETURNING
*
)
SELECT  mytable.*
FROMinsert_cte
JOINmytable
USING   (id);

This query will return nothing, even though people would expect it to
return the newly inserted record.

This is just a minimally reproducible example, in which you can easily work
around the problem just by getting rid of the join to mytable. But during
my consulting career, I've seen people try putting together more complex
queries using the same pattern, and this always comes as a surprise.

I get why it's not working (because the statement is not allowed to see the
tuples with its own cmin), but I was wondering if it was worth it at least
to spell it out explicitly in the documentation.

Right now the documentation says:

https://www.postgresql.org/docs/15/queries-with.html#QUERIES-WITH-MODIFYING

RETURNING data is the only way to communicate changes between different
> WITH sub-statements and the main query


which I don't think is covering the JOIN issue (after all, I am using the
RETURNING clause to communicate with the main query).

Can we please add this example to the documentation? I can do the wording
if that's something worth adding.

Thank you!

Re: COPY RETURNING?

[Adrian Klaver]

On 4/19/23 02:55, Dominique Devienne wrote:

Hi.

We are switching a schema type's surrogate/primary key, from `uuid` to 
`int`.

That schema has parent-child relationships enforced with foreign-keys.
Our ingestion/schema-loading code uses COPY FROM STDIN BINARY.

Before, the SK/PK was generated client-side, as random uuid.
The ingestion code maintained client-side maps from NKs to uuids,
to be used for FK columns in child-tables COPY'd later.

But now that the SK/PK is an integer identity column generated server-side,
thanks to an implicitly-created sequence, we need that generated per-row 
`int` PK.

With a normal prepared statement, we'd use a RETURNING clause, to avoid
a separate round-trip to the server. Can the same somehow be achieved 
with COPY?


I'm afraid the answer is no, but I want to ask anyway, maybe there's a 
way or work-around?


1) Correct COPY does not have RETURNING.

2) What do you need to do with that new id?

3) Off the top of my head a possible solution may be a trigger using a 
transition relation.


https://www.postgresql.org/docs/current/sql-createtrigger.html

The REFERENCING option enables collection of transition relations, which 
are row sets that include all of the rows inserted, deleted, or modified 
by the current SQL statement. This feature lets the trigger see a global 
view of what the statement did, not just one row at a time. This option 
is only allowed for an AFTER trigger that is not a constraint trigger; 
also, if the trigger is an UPDATE trigger, it must not specify a 
column_name list. OLD TABLE may only be specified once, and only for a 
trigger that can fire on UPDATE or DELETE; it creates a transition 
relation containing the before-images of all rows updated or deleted by 
the statement. Similarly, NEW TABLE may only be specified once, and only 
for a trigger that can fire on UPDATE or INSERT; it creates a transition 
relation containing the after-images of all rows updated or inserted by 
the statement.





At the end of the COPY, we do get a ResultSet, so API-wise it was be 
possible for it
to contain some rows, I guess, but I'm not sure the COPY protocol 
supports returning

rows, nor what the syntax would be to have a COPY RETURNING form of COPY.

Thanks for any insights. --DD


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

Re: Joins of data-modifying CTE with the target table

[Tom Lane]

Alex Bolenok  writes:
> I get why it's not working (because the statement is not allowed to see the
> tuples with its own cmin), but I was wondering if it was worth it at least
> to spell it out explicitly in the documentation.

What's not explicit about this?

The sub-statements in WITH are executed concurrently with each other
and with the main query. Therefore, when using data-modifying
statements in WITH, the order in which the specified updates actually
happen is unpredictable. All the statements are executed with the same
snapshot (see Chapter 13), so they cannot “see” one another's effects
on the target tables.

regards, tom lane

Re: Joins of data-modifying CTE with the target table

[Adrian Klaver]

On 4/19/23 08:37, Alex Bolenok wrote:

Hi list,

This popped up yesterday during a discussion at the Boston PostgreSQL 
group meetup, and Jesper Pedersen had advised that I post it here.


Imagine this setup:

CREATE TABLE IF NOT EXISTS mytable (id BIGSERIAL PRIMARY KEY, value TEXT 
NOT NULL);


WITH    insert_cte AS
         (
         INSERT
         INTO    mytable (value)
         VALUES  ('test')
         RETURNING
                 *
         )
SELECT  mytable.*
FROM    insert_cte
JOIN    mytable
USING   (id);

This query will return nothing, even though people would expect it to 
return the newly inserted record.


This is just a minimally reproducible example, in which you can easily 
work around the problem just by getting rid of the join to mytable. But 
during my consulting career, I've seen people try putting together more 
complex queries using the same pattern, and this always comes as a surprise.


I get why it's not working (because the statement is not allowed to see 
the tuples with its own cmin), but I was wondering if it was worth it at 
least to spell it out explicitly in the documentation.


Right now the documentation says:

https://www.postgresql.org/docs/15/queries-with.html#QUERIES-WITH-MODIFYING 


RETURNING data is the only way to communicate changes between
different WITH sub-statements and the main query


which I don't think is covering the JOIN issue (after all, I am using 
the RETURNING clause to communicate with the main query).


Can we please add this example to the documentation? I can do the 
wording if that's something worth adding.


To add to Tom's post.

"
Data-modifying statements in WITH usually have RETURNING clauses (see 
Section 6.4), as shown in the example above. It is the output of the 
RETURNING clause, not the target table of the data-modifying statement, 
that forms the temporary table that can be referred to by the rest of 
the query. If a data-modifying statement in WITH lacks a RETURNING 
clause, then it forms no temporary table and cannot be referred to in 
the rest of the query. Such a statement will be executed nonetheless. A 
not-particularly-useful example is:


...

The sub-statements in WITH are executed concurrently with each other and 
with the main query. Therefore, when using data-modifying statements in 
WITH, the order in which the specified updates actually happen is 
unpredictable. All the statements are executed with the same snapshot 
(see Chapter 13), so they cannot “see” one another's effects on the 
target tables. This alleviates the effects of the unpredictability of 
the actual order of row updates, and means that RETURNING data is the 
only way to communicate changes between different WITH sub-statements 
and the main query. An example of this is that in


WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of 
the UPDATE, while in


WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.
"

So the RETURNING temp table is the only thing you have to work on.



Thank you!


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

Re: Joins of data-modifying CTE with the target table

[Alex Bolenok]

To reiterate, I (I personally) get why it's not working, but the
documentation is something that is intended for people who don't.

Technically, anyone can deduce it from reading the documentation thoroughly
enough, but do we need the documentation to be as terse as possible?

To answer your question, by "explicitly" I mean mentioning this very
pattern, where you join the returned id with the target table. People often
try to use this pattern for queries like "add an item to the order in a
CTE, select the order total in the main query", and often don't notice that
the order total doesn't include the new item until it hits production.

ср, 19 апр. 2023 г. в 11:46, Tom Lane :

> Alex Bolenok  writes:
> > I get why it's not working (because the statement is not allowed to see
> the
> > tuples with its own cmin), but I was wondering if it was worth it at
> least
> > to spell it out explicitly in the documentation.
>
> What's not explicit about this?
>
> The sub-statements in WITH are executed concurrently with each other
> and with the main query. Therefore, when using data-modifying
> statements in WITH, the order in which the specified updates actually
> happen is unpredictable. All the statements are executed with the same
> snapshot (see Chapter 13), so they cannot “see” one another's effects
> on the target tables.
>
> regards, tom lane
>

Re: Joins of data-modifying CTE with the target table

[Adrian Klaver]

On 4/19/23 09:09, Alex Bolenok wrote:
To reiterate, I (I personally) get why it's not working, but the 
documentation is something that is intended for people who don't.


Technically, anyone can deduce it from reading the documentation 
thoroughly enough, but do we need the documentation to be as terse 
as possible?


Not so much terse as possible as not as verbose as possible. The 
difference being that documenting every possible usage would turn the 
page into an eye glazing experience. At that point folks would really 
just move on. The documentation expectations(as I see it) are that: 1) 
People can work from basic principles to specific usage. 2) They also 
actually test/verify what they run.


What you are after is more on the lines of a separate enhanced tutorial. 
That has been discussed on this list and elsewhere. So far it has not 
progressed beyond discussion.





To answer your question, by "explicitly" I mean mentioning this very 
pattern, where you join the returned id with the target table. People 
often try to use this pattern for queries like "add an item to the order 
in a CTE, select the order total in the main query", and often don't 
notice that the order total doesn't include the new item until it hits 
production.




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

Re: Joins of data-modifying CTE with the target table

[Tom Lane]

Alex Bolenok  writes:
> To answer your question, by "explicitly" I mean mentioning this very
> pattern, where you join the returned id with the target table.

I do not see why this existing example isn't sufficient:

... An example of this is that in

WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of
the UPDATE, ...

Yeah, it lacks a JOIN and it's an UPDATE not an INSERT.  But we can't
provide an example to precisely match every possible mistake.  As
Adrian said, brevity in the docs does have value.

regards, tom lane

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

[gzh]

Thank you for your prompt reply. 
Is there another solution if the database is not upgraded to 12.14?
>Better upgrade to latest release 12.14.

















At 2023-04-19 22:51:33, "Erik Wienhold"  wrote:
>> On 19/04/2023 15:24 CEST gzh  wrote:
>>
>> Hi,
>>
>> I upgraded the version of PostgreSQL from 12.10 to 12.13,
>
>Better upgrade to latest release 12.14.
>
>> when I insert data into the t_mstr table, the to_char function in the 
>> t_mstr's
>> trigger caused the following error.
>>
>> psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist
>>
>> There is no problem before the upgrade and to_char(numeric) function comes
>> from the Orafce extension.
>> The configuration of the old and new databases is as follows.
>>
>> Database server (old): PostgreSQL 12.10（orafce3.15）
>> Database server (new): PostgreSQL 12.13（orafce3.24）
>>
>> The new database has successfully installed the orafce 3.24 extension.
>> It does not occur in "PostgreSQL 12.10 and orafce 3.15",
>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>> so either the difference between 12.10 and 12.13 or the difference between
>> orafce 3.15 and 3.24 is suspicious.
>>
>> What is the reason for the problem?
>
>orafce 3.22 moved functions to schema oracle:
>
>https://github.com/orafce/orafce/blob/b492a0f50d5ee866c9870f886401d7c34ad8ccb3/NEWS#L4,L5
>https://github.com/orafce/orafce/commit/86a1b51440ca33a04ef47fc3bb704dee26d16753
>
>--
>Erik

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

[Tom Lane]

gzh   writes:
> Thank you for your prompt reply. 
> Is there another solution if the database is not upgraded to 12.14?

The solution is the same whether you upgrade or not: you need
to adjust your search_path to include the "oracle" schema,
or else explicitly qualify references to orafce functions.

regards, tom lane

Re: psql:t_mstr.sql:994: ERROR: function to_char(numeric) does not exist

[Adrian Klaver]

On 4/19/23 10:02 AM, gzh wrote:

Thank you for your prompt reply.
Is there another solution if the database is not upgraded to 12.14?


Better upgrade to latest release 12.14.




The point being made was that 12.14 is the latest minor release so you 
might as well upgrade to it. It will not change your situation,  as 
others have pointed out that is a schema/search_path issue. You will 
still need to resolve that.


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

What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Bryn Llewellyn]

This tip

«
It is good practice to create a role that has the CREATEDB and CREATEROLE 
privileges, but is not a superuser, and then use this role for all routine 
management of databases and roles. This approach avoids the dangers of 
operating as a superuser for tasks that do not really require it.
»

used to be found in all versions of the PG doc from (at least) Version 11 
through Version 15. It was in the chapter "Role Attributes" (22.2 in "current", 
21.2 in Version 11). It immediately followed this: «A role's attributes can be 
modified after creation with ALTER ROLE. See the reference pages for the CREATE 
ROLE and ALTER ROLE commands for details.»

You can still find it in "privately" staged copies of the PG doc like, for 
example, here:

postgrespro.com/docs/postgresql/10/role-attributes
access.crunchydata.com/documentation/postgresql15/15.1/role-attributes.html

But now it's simply vanished from both the Version 11 doc and the "current" 
doc. (I didn't check the versions in between.)

What was the rationale for removing it? The practice recommendation makes sense 
to me. And I've implemented a scheme for database and role provisioning that 
uses just such a non-superuser with CREATEDB and CREATEROLE. I'm pleased with 
it.

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Jeremy Smith]

On Wed, Apr 19, 2023 at 2:19 PM Bryn Llewellyn  wrote:

> This tip
>
> «
> It is good practice to create a role that has the CREATEDB and CREATEROLE
> privileges, but is not a superuser, and then use this role for all routine
> management of databases and roles. This approach avoids the dangers of
> operating as a superuser for tasks that do not really require it.
> »


>
used to be found in all versions of the PG doc
>



> What was the rationale for removing it? The practice recommendation makes
> sense to me. And I've implemented a scheme for database and role
> provisioning that uses just such a non-superuser with CREATEDB and
> CREATEROLE. I'm pleased with it.
>
>

It was removed in this commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5

According to the commit comment, there's little security advantage to using
a role with CREATEDB and CREATEROLE privileges.

Question about accessing partitions whose name includes the schema name and a period - is this correct?

[Jay Stanley]

I've come across some interesting behavior with regards to creating a 
partition of a table that includes the schema name and a period in the 
beginning, so that the resulting name is like 
"my_schema"."my_schema.my_table_should_not_work".


After created it, most SQL won't access it at all, even when 
double-quoting the table name exactly, though drop seems to work.  It's 
very repeatable at least in versions up to v14.5.


Here's a script that demonstrates the issue -- in pl/pgsql, I'm able to 
create a partition on a table that has the schema and period included by 
using the format statement, then I'm unable to access the partition at 
all:




drop schema if exists my_schema cascade;

create schema my_schema;

create table my_schema.my_table(
  i  bigint not null primary key,
  dat  text)
  partition by range(i);

create table my_table_default partition of my_schema.my_table DEFAULT;
create table my_table_1 partition of my_schema.my_table for values from 
(1) to (100);


SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';

create procedure my_schema.test()
language plpgsql
as $BODY$
begin
  execute format('create table %I partition of %I.%I for values from 
(%s) to 
(%s)','my_schema.my_table_should_not_work','my_schema','my_table','100','200');

end;
$BODY$;

call my_schema.test();

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';

alter table "my_schema"."my_schema.my_table_should_not_work" rename to 
'fixed;


alter table my_schema.my_table detach partition 
"my_schema.my_table_should_not_work";


select count(*) from "my_schema"."my_schema.my_table_should_not_work";



When run, the partition name ends up like this:

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';
 partition_name
-
 my_table_default
 my_table_default_pkey
 my_table_1
 my_table_1_pkey
 my_schema.my_table_should_not_work
 my_schema.my_table_should_not_work_pkey
(6 rows)

and, none of the 'alter table' SQL works, similar to this error: it just 
can't find it, even with correct quoting:


SQL> select count(*) from 
"my_schema"."my_schema.my_table_should_not_work";
ERROR:  relation "my_schema.my_schema.my_table_should_not_work" does not 
exist


Does anyone have any insights regarding how this partition could be 
renamed or detached?  I've reviewed the mailing list archive and other 
sources and haven't been able to find anything similar.


Thanks everyone!

-Jay Stanley, DBA
-Cycorp: The Why behind AI

Long-time lurker, first-time poster.

Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

[Tom Lane]

Jay Stanley  writes:
> I've come across some interesting behavior with regards to creating a 
> partition of a table that includes the schema name and a period in the 
> beginning, so that the resulting name is like 
> "my_schema"."my_schema.my_table_should_not_work".
> After created it, most SQL won't access it at all, even when 
> double-quoting the table name exactly, though drop seems to work.

I think this has little to do with the funny table names, and much
to do with your being careless about which schema the partitions
end up in.  We intentionally don't constrain partitions to live
in the same schema as their parent.  So when you do

> create schema my_schema;

> create table my_schema.my_table(
>i  bigint not null primary key,
>dat  text)
>partition by range(i);

> create table my_table_default partition of my_schema.my_table DEFAULT;
> create table my_table_1 partition of my_schema.my_table for values from 
> (1) to (100);

the parent "my_table" is in "my_schema", but the partitions are
(probably) in schema "public".  Your catalog-investigation query
doesn't show that, adding to your confusion.  The commands
that don't work for you are failing because you assume the
partitions are in "my_schema", except in some places where
you leave that off, and then it does work because public
is in your search_path.

regards, tom lane

Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

[Jay Stanley]

On 2023-04-19 21:42, Tom Lane wrote:


Jay Stanley  writes:


I've come across some interesting behavior with regards to creating a
partition of a table that includes the schema name and a period in the
beginning, so that the resulting name is like
"my_schema"."my_schema.my_table_should_not_work".
After created it, most SQL won't access it at all, even when
double-quoting the table name exactly, though drop seems to work.


I think this has little to do with the funny table names, and much
to do with your being careless about which schema the partitions
end up in.  We intentionally don't constrain partitions to live
in the same schema as their parent.  So when you do


create schema my_schema;



create table my_schema.my_table(
i  bigint not null primary key,
dat  text)
partition by range(i);



create table my_table_default partition of my_schema.my_table DEFAULT;
create table my_table_1 partition of my_schema.my_table for values 
from

(1) to (100);


the parent "my_table" is in "my_schema", but the partitions are
(probably) in schema "public".  Your catalog-investigation query
doesn't show that, adding to your confusion.  The commands
that don't work for you are failing because you assume the
partitions are in "my_schema", except in some places where
you leave that off, and then it does work because public
is in your search_path.

regards, tom lane


Thanks, Tom!

the parent "my_table" is in "my_schema", but the partitions are 
(probably) in schema "public".


You are correct -- that example is putting the partition in the first 
schema in the search_path (cycdba in this case) - I apologies for the 
not ideal example.  The name of the partition created in the search_path 
schema does contain the schema name:


postgres=# select 
oid,relname,relnamespace,relnamespace::regnamespace::text as 
text_schema,reltype from pg_class where relname like 
'%should_not_work%';
  oid   | relname | relnamespace | 
text_schema | reltype

+-+--+-+-
 184482 | my_schema.my_table_should_not_work_pkey |16612 | 
cycdba  |   0
 184479 | my_schema.my_table_should_not_work  |16612 | 
cycdba  |  184481

(2 rows)

Modifying my example a bit, I can make it end up in my_schema:
postgres=#create procedure my_schema.test()
language plpgsql
as $BODY$
begin
  execute format('create table %I.%I partition of %I.%I for values from 
(%s) to 
(%s)','my_schema','my_schema.my_table_should_not_work','my_schema','my_table','100','200');

end;
$BODY$;
postgres-# postgres-# postgres$# postgres$# postgres$# postgres$# CREATE 
PROCEDURE

postgres=# call my_schema.test();
postgres=# CALL
postgres=# select 
oid,relname,relnamespace,relnamespace::regnamespace::text as 
text_schema,reltype from pg_class where relname like 
'%should_not_work%';
  oid   | relname | relnamespace | 
text_schema | reltype

+-+--+-+-
 184978 | my_schema.my_table_should_not_work_pkey |   184954 | 
my_schema   |   0
 184975 | my_schema.my_table_should_not_work  |   184954 | 
my_schema   |  184977

(2 rows)

After re-testing, I found that double-quoting the table name works for 
inserts, updates, and deletes: example


postgres=# insert into "my_schema"."my_schema.my_table_should_not_work" 
(i,dat) values (101,'test');

INSERT 0 1

However, it's failing on partition-management SQL like:

postgres=# alter table my_schema.my_table drop partition 
"my_schema"."my_schema.my_table_should_not_work";

ERROR:  syntax error at or near ""my_schema""
LINE 1: alter table my_schema.my_table drop partition "my_schema"."m...
  ^
-or-

postgres=# alter table my_schema.my_table drop partition 
my_schema."my_schema.my_table_should_not_work";

ERROR:  syntax error at or near "my_schema"
LINE 1: alter table my_schema.my_table drop partition my_schema."my_...

I noticed this while maintaining an in-house partition management 
procedure which was updated from constructing the 'create table... 
partition' sql using plpgsql format(), rather than constructing it 
without using format() using more naiive string concatenations.


-jay

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Bryn Llewellyn]

> jer...@musicsmith.net wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> This tip
>> 
>> «
>> It is good practice to create a role that has the CREATEDB and CREATEROLE 
>> privileges, but is not a superuser, and then use this role for all routine 
>> management of databases and roles. This approach avoids the dangers of 
>> operating as a superuser for tasks that do not really require it.
>> » 
>>  
>> used to be found in all versions of the PG doc... What was the rationale for 
>> removing it? The practice recommendation makes sense to me. And I've 
>> implemented a scheme for database and role provisioning that uses just such 
>> a non-superuser with CREATEDB and CREATEROLE. I'm pleased with it.
> 
> It was removed in this commit:
> 
> git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5
> 
> According to the commit comment, there's little security advantage to using a 
> role with CREATEDB and CREATEROLE privileges.  

I looked at some of the discussion here:

postgr.es/m/ca+tgmozbspl8nphvyecx7igo5qpdrqa9k_acaw1sbojugay...@mail.gmail.com

It's rather dense and I'll have to defer studying it. Anyway, I noticed an 
encouragement there to stop discussing it.

I do see that a role that has "createdb" and "createrole" is pretty powerful 
because, for example, a role with these attributes can use "set role" to become 
any other non-superuser (see the example below).

However, a role with just "createdb" and "createrole" is definitely less 
powerful than one that has "superuser". For example, a role without "superuser" 
cannot drop an object that's owned by a role that has "superuser". Nor can a 
role without "superuser" execute, for example, "alter database... set 
log_error_verbosity...". And especially any cunning scheme that somebody might 
hatch to authorize as a role with "createdb" and "createrole" and without 
"superuser" to end up where the current_role has "superuser" fails—just like 
the doc says.

The principle of least privilege is generally regarded as a good thing. And I 
like to follow it. I'm able to do the database provisioning and role 
provisioning tasks that I need to with a role that has just "createdb" and 
"createrole"—like the now-removed tip recommended. And it would be 
tautologically not least privilege to use a role with "superuser" instead—and 
therefore a bad thing.

Here's the examples that I mentioned. Please confirm that the changes brought 
by the commit referred to above won't change how it behaves in Version 15.2.

\c postgres postgres
\c postgres postgres
create role supr with superuser login password 'p';

\c postgres supr
create role joe with createdb createrole login password 'p';
create role mary with createdb createrole login password 'p';

\c postgres joe
grant postgres to joe; -- error 42501

grant mary to joe; --OK
set role mary; -- OK
select session_user, current_role;

\c postgres joe
create database d0;
alter database d0 set log_error_verbosity = terse; -- error 42501

\c postgres postgres
alter database d0 set log_error_verbosity = terse; -- OK

create schema s;
create table s.t(k int primary key);

\c postgres joe
drop table s.t; -- error 42501

\c postgres supr
drop table s.t; -- OK

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Adrian Klaver]

On 4/19/23 16:53, Bryn Llewellyn wrote:

jer...@musicsmith.net  wrote:



Here's the examples that I mentioned. Please confirm that the changes 
brought by the commit referred to above won't change how it behaves in 
Version 15.2.





The commit was over only documentation files

doc/src/sgml/ref/alter_role.sgml
doc/src/sgml/ref/create_role.sgml   
doc/src/sgml/ref/createuser.sgml
doc/src/sgml/user-manag.sgml

so I don't see how it can change behavior.

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

Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

[Erik Wienhold]

> On 20/04/2023 00:50 CEST Jay Stanley  wrote:
>
> postgres=# alter table my_schema.my_table drop partition 
> "my_schema"."my_schema.my_table_should_not_work";
> ERROR: syntax error at or near ""my_schema""
> LINE 1: alter table my_schema.my_table drop partition "my_schema"."m...
>^
> -or-
> postgres=# alter table my_schema.my_table drop partition 
> my_schema."my_schema.my_table_should_not_work";
> ERROR: syntax error at or near "my_schema"
> LINE 1: alter table my_schema.my_table drop partition my_schema."my_...

The command you're looking for is ALTER TABLE DETACH PARTITION.

DROP PARTITION means dropping the column named "partition".  The syntax error
comes from the parser expecting an optional CASCADE or RESTRICT after DROP 
PARTITION.

--
Erik

How does pg index page optimize dead tuples?

[jack...@gmail.com]

As far as I know, when a index page is full, if you insert a new tuple here, 
you will split it into two pages.
But pg won't delete the half tuples in the old page in real. So if there is 
another tuple inserted into this old
page, will pg split it again? I think that's not true, so how it solve this 
one? please give me a code example,thanks.


jack...@gmail.com

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Bryn Llewellyn]

> adrian.kla...@aklaver.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> Here's the examples that I mentioned. Please confirm that the changes 
>> brought by the commit referred to above won't change how it behaves in 
>> Version 15.2.
> 
> The commit was over only documentation files
> 
> doc/src/sgml/ref/alter_role.sgml  
> doc/src/sgml/ref/create_role.sgml 
> doc/src/sgml/ref/createuser.sgml  
> doc/src/sgml/user-manag.sgml
> 
> so I don't see how it can change behavior.

The account of the commit that Jeremy Smith referred to, here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5

had a reference to an email thread on the pgsql-hackers with subject "fixing 
CREATEROLE". It was started by Robert Haas and it begins thus:

> https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com
> 
> The CREATEROLE permission is in a very bad spot right now. The biggest 
> problem that I know about is that it allows you to trivially access the OS 
> user account under which PostgreSQL is running, which is expected behavior 
> for a superuser but simply wrong behavior for any other user. This is because 
> CREATEROLE conveys powerful capabilities not only to create roles but also to 
> manipulate them in various ways, including granting any non-superuser role in 
> the system to any new or existing user, including themselves.


The thread goes on forever. And it branches too. It's talking about possibly 
patching the code—precisely to bring about a change in behavior. And I'm asking 
if the fix(es) under discussion would change the behavior of the code that I 
showed.

The upshot of it all seems to be that the putative benefit of using a role the 
has only "createrole" and not "super" is marginal because such a role can grant 
itself shipped dangerous roles like "pg_execute_server_program" and 
"pg_write_server_files"
which are trivially exploitable.

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[David G. Johnston]

On Wednesday, April 19, 2023, Bryn Llewellyn  wrote:
>
>
> had a reference to an email thread on the pgsql-hackers with subject
> "fixing CREATEROLE". It was started by Robert Haas and it begins thus:
>
> > https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_
> J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com
> >
> > The CREATEROLE permission is in a very bad spot right now. The biggest
> problem that I know about is that it allows you to trivially access the OS
> user account under which PostgreSQL is running, which is expected behavior
> for a superuser but simply wrong behavior for any other user. This is
> because CREATEROLE conveys powerful capabilities not only to create roles
> but also to manipulate them in various ways, including granting any
> non-superuser role in the system to any new or existing user, including
> themselves.
>
>
> The thread goes on forever. And it branches too. It's talking about
> possibly patching the code—precisely to bring about a change in behavior.
> And I'm asking if the fix(es) under discussion would change the behavior of
> the code that I showed.
>

Yes, the behavior change is part of the unreleased v16.  We don’t make
non-bug changes to released versions.

David J.

Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

[Tom Lane]

Jay Stanley  writes:
> However, it's failing on partition-management SQL like:

> postgres=# alter table my_schema.my_table drop partition 
> "my_schema"."my_schema.my_table_should_not_work";
> ERROR:  syntax error at or near ""my_schema""
> LINE 1: alter table my_schema.my_table drop partition "my_schema"."m...
>^

I'm not sure why the error cursor is pointing there, but
"drop partition" is not a valid subcommand.  Try "detach
partition".

regards, tom lane