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

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

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

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%3D

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

> 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/

How does pg index page optimize dead tuples?

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 th

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

> 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... >

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

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 documenta

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

> 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 avo

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

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

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

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 w

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

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, eve

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

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

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

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

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

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 chang

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

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

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

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 PostgreSQ

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

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

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

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

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

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 questi

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

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); W

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

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 ex

Re: COPY RETURNING?

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 c

Joins of data-modifying CTE with the target table

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 ( INSER

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

> 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: ERRO

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

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 upgra

COPY RETURNING?

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 m

Re: Guidance on INSERT RETURNING order

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: >> > ... >> > >> > Personall

Re: Request for information about postgres version 15.2 stability

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