Re: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On Thu, Oct 27, 2022 at 4:02 PM Tom Lane wrote: > "David G. Johnston" writes: > > Yes, the description for --username probably should be modified to read: > > > "Selects the user name of the cluster's bootstrap superuser." > > Yeah, perhaps. The term "bootstrap superuser" is reasonably well > e

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On 10/27/22 17:20, Bryn Llewellyn wrote: david.g.johns...@gmail.com wrote: b...@yugabyte.com  wrote: The fact that the "bootstrap superuser" term of art denotes a matching pair of two principals (an O/S user and a within-cluster ro

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> t...@sss.pgh.pa.us> wrote: > >> david.g.johns...@gmail.com wrote: >> >> Yes, the description for --username probably should be modified to read: >> >> "Selects the user name of the cluster's bootstrap superuser." > > Yeah, perhaps. The term "bootstrap superuser" is reasonably well established

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com> wrote: > >> b...@yugabyte.com wrote: >> >> This invariant must hold if an "ordinary" within-cluster superuser is to >> qualify as the cluster's "bootstrap superuser": >> >> the name of the bootstrap superuser's within-cluster role >> >> AND >> >> the name of the

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> You can start a session without specifying the name of the cluster role as >> which to authorize, its password, and the name of the database to which to >> connect, ONLY when these things are true: >> >> 1. The within

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> The fact that the "bootstrap superuser" term of art denotes a matching pair >> of two principals (an O/S user and a within-cluster role) > > No, it does not. It denotes only the PostgreSQL role. "service user" is > proba

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 2022-10-27 15:07:06 +0300, Kristjan Mustkivi wrote: > On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > > We use dockerized postgres. > > > > So that means you aren't just replacing PostgreSQL, but your complete OS > > (except

Re: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

"David G. Johnston" writes: > Yes, the description for --username probably should be modified to read: > "Selects the user name of the cluster's bootstrap superuser." Yeah, perhaps. The term "bootstrap superuser" is reasonably well established by now --- I count half a dozen uses in our SGML do

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On Thu, Oct 27, 2022 at 12:09 PM Bryn Llewellyn wrote: > > This invariant must hold if an "ordinary" within-cluster superuser is to > qualify as the cluster's "bootstrap superuser": > > the name of the bootstrap superuser's within-cluster role > > > AND > > the name of the O/S user that owns lot

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On Thu, Oct 27, 2022 at 3:24 PM Bryn Llewellyn wrote: > *«* > *You can start a session without specifying the name of the cluster role > as which to authorize, its password, and the name of the database to which > to connect, ONLY when these things are true:* > > > > *1. The within-cluster catalo

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On Thu, Oct 27, 2022 at 12:09 PM Bryn Llewellyn wrote: > [*] I see that, in my Ubuntu installation, critical programs like > "postgres" itself, "initdb", "pg_ctl", "pg_dump" and so on are owned by > "root". > And they exist in a "bin" directory so that any user on the system can actually execute

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> jer...@musicsmith.net wrote: > >> b...@yugabyte.com wrote: >> >> I can now characterize what I'd observed more clearly, thus: only a >> bootstrap super user (as defined above) can start a session without >> mentioning the name of the database to which to connect and

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> > I can now characterize what I'd observed more clearly, thus: only a > bootstrap super user (as defined above) can start a session without > mentioning the name of the database to which to connect and the name of the > within-cluster role to connect as—and without supplying a password. And it >

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> adrian.klaver@aklaver.comwrote: > >> b...@yugabyte.com wrote >> >> The descriptive designation "the role that owns the SQL part of the >> implementation of PostgreSQL" is too much of a mouthful for daily use. And >> anyway, this notion captures only part of the story that makes "postgres" >>

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

(David and Ian, I'm resending this because, I see that I managed to omit "pgsql-general@lists.postgresql.org " from the addressee list. So, of course, it didn't show up in the "pgsql-general" archive.) > barw...@gmail.com wro

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 10/27/22 07:07, Kristjan Mustkivi wrote: On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: We use dockerized postgres. So that means you aren't just replacing PostgreSQL, but your complete OS (except the kernel). What is the sour

Lock: Speculative token

Aurora PG based on PG 13.4 Our create concurrent index on a very large partitioned table (5 billion rows) waits in Lock: Speculative token.Never seen this error in PG. Google search also shows nothing. Is this Aurora thingy ? If this is PG related I can provide full details.

Re: EXPLAIN ANALYZE does not return accurate execution times

Mark Mizzi writes: > So to confirm, EXPLAIN ANALYZE does not detoast rows? Not values that would have been transmitted to the client, no. regards, tom lane

Re: EXPLAIN ANALYZE does not return accurate execution times

Hi, thanks for your reply. So to confirm, EXPLAIN ANALYZE does not detoast rows? The original goal of these queries was to see the effect of fetching from toast tables on query performance. On Thu, 27 Oct 2022 at 15:43, Tom Lane wrote: > Mark Mizzi writes: > > When I run > > > EXPLAIN ANALYZE S

Re: EXPLAIN ANALYZE does not return accurate execution times

Mark Mizzi writes: > When I run > EXPLAIN ANALYZE SELECT * FROM unary; > I get the following result: > Seq Scan on unary (cost=0.00..1637.01 rows=11 width=18) (actual > time=0.009..6.667 rows=11 loops=1) > Planning Time: 0.105 ms > Execution Time: 8.565 ms > On the other hand, the

Re: EXPLAIN ANALYZE does not return accurate execution times

Hi, On Thu, Oct 27, 2022 at 03:28:14PM +0200, Mark Mizzi wrote: > > EXPLAIN ANALYZE SELECT * FROM unary; > > I get the following result: > > Seq Scan on unary (cost=0.00..1637.01 rows=11 width=18) (actual > time=0.009..6.667 rows=11 loops=1) > Planning Time: 0.105 ms > Execution Time:

EXPLAIN ANALYZE does not return accurate execution times

As an example, let's take the following simple table: CREATE TABLE unary(a VARCHAR); -- simple way to make table large ALTER TABLE unary ALTER COLUMN a SET STORAGE EXTERNAL; -- insert one million large rows INSERT INTO unary SELECT repeat('a', 8000) FROM generate_series(0, 10); -- update

Re: Value Too long varchar(100)

You should be able to do something with this if you require it to be done with COPY: https://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-copy-tofrom-program/ But, as David suggested, I'd also recommend to do an intermediate step, and load the data into a table, or pre-process the file

Re: Value Too long varchar(100)

On Thu, Oct 27, 2022 at 5:02 AM Rama Krishnan wrote: > Hi team, > > We are getting csv file from client to upload data in my db table , one > particular column I. E clinet description column contains more than 100 > character hence I am getting value too long varchar (100) so we decided to > upl

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > We use dockerized postgres. > > So that means you aren't just replacing PostgreSQL, but your complete OS > (except the kernel). What is the source of your docker images? Do they >

Re: PostgreSql Service different path

I fixed it by editing the postgresql-14.service PGDATA path to the new directory Thanks, butching On Mon, Oct 24, 2022 at 7:25 PM chris navarroza wrote: > But I'm using a different path when I initdb /usr/pgsql-14/bin/initdb -D > */home/dmartuser/pgsql/14/data > *so "/var/lib/pgsql/14/data/"

GIN Index Partial Match?

I create a gin index for a custom type. I want to use partial match, but I find extract query method called twice in a query, when in the first called, I return the minimal value of the type, and I want to set the maximal value of the type in extra data will be used in compare partial function ,But

Value Too long varchar(100)

Hi team, We are getting csv file from client to upload data in my db table , one particular column I. E clinet description column contains more than 100 character hence I am getting value too long varchar (100) so we decided to upload db only first 100 characters. How to use this thing in copy

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On Thu, Oct 27, 2022 at 12:18 PM Peter J. Holzer wrote: > On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > > We use dockerized postgres. > > So that means you aren't just replacing PostgreSQL, but your complete OS > (except the kernel). What is the source of your docker images? Do they >

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On 2022-10-27 10:55:31 +0300, Kristjan Mustkivi wrote: > We use dockerized postgres. So that means you aren't just replacing PostgreSQL, but your complete OS (except the kernel). What is the source of your docker images? Do they all use the same base OS distribution? Are the locale definitions the

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

Hi Allan, We use dockerized postgres. So the upgrade is just replacing the old minor version pg docker image with new minor version docker image and that's it. Of course, I am checking the release notes to see if there is anything to pay attention to particularly. I do apologize, but I do not und

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On Thu, Oct 27, 2022 at 10:26 AM Allan Kamau wrote: > > > On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi > wrote: > >> Dear community, >> >> Right after upgrading our postgres servers from 11.15 to 11.17 we >> started to encounter problems with data. Namely, when the query hit >> the index,

Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

On Thu, Oct 27, 2022 at 10:20 AM Kristjan Mustkivi wrote: > Dear community, > > Right after upgrading our postgres servers from 11.15 to 11.17 we > started to encounter problems with data. Namely, when the query hit > the index, it returned a single row; when the query hit a relation > directly,

Index corruption revealed after upgrade to 11.17, could date back to at least 11.12

Dear community, Right after upgrading our postgres servers from 11.15 to 11.17 we started to encounter problems with data. Namely, when the query hit the index, it returned a single row; when the query hit a relation directly, it returned more than one row. Attempt to REINDEX revealed the underlyi