write an analyze_function for own type

I now have some custom data like: [0x1 0x22 0x365] It has a level attribute, that is, the level of 0x1 is 1, and the level of 0x22 is 2. How should I count the minimum level in a table? I want to use this statistic in GIN index.

Unnecessary locks for partitioned tables

Hello! Recently I`ve been pushing into life a new project and immediately experienced an Out of shared memory error while querying partitioned tables. Imagine a huge busy table that you want to split into hundreds of partitions by list. Partition key is a kind of surrogate key that can be c

Q: documentation improvement re collation version mismatch

Dear all, regarding changed collation versions this https://www.postgresql.org/docs/devel/sql-altercollation.html says: The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on the

Re: Unnecessary locks for partitioned tables

On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote: > Recently I`ve been pushing into life a new project and immediately > experienced an > Out of shared memory error while querying partitioned tables. >   > ERROR: out of shared memory >   Hint: You might need to increase max_loc

RE: Unnecessary locks for partitioned tables

> > On Wed, 2022-11-09 at 14:11 +0300, n.kobza...@aeronavigator.ru wrote: > > Recently I`ve been pushing into life a new project and immediately > > experienced an Out of shared memory error while querying partitioned tables. > > > > ERROR: out of shared memory > > Hint: You might need to incr

Re: Unnecessary locks for partitioned tables

writes: > Oh, I did not explicitly write that, in case of custom plan (first attempts > or with force_custom_plan) database holds only a couple of locks! Why in this > case it is sufficient to lock only one partition and parent table ? Because partition routing is done at planning time in that

RE: Unnecessary locks for partitioned tables

> writes: > > Oh, I did not explicitly write that, in case of custom plan (first attempts or with force_custom_plan) database > > holds only a couple of locks! > > Why in this > case it is sufficient to lock only one partition and parent table ? > Because partition routing is done at planning

Re: copy file from a client app to remote postgres isntance

On 2022-11-07 19:57:04 +0300, Вадим Самохин wrote: > I have an application that must copy a local file in csv format to a postgres > table on a remote host. The closest solution is this one (https:// > stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy > meta-command in a psql

Re: copy file from a client app to remote postgres isntance

On 2022-11-07 14:40:40 -0600, Ron wrote: > On 11/7/22 10:57, Вадим Самохин wrote: > I have an application that must copy a local file in csv format to a > postgres table on a remote host. The closest solution is this one > (https:// > stackoverflow.com/a/9327519/618020). It boils down

Re: Unnecessary locks for partitioned tables

On Thu, 10 Nov 2022 at 04:11, wrote: > If someone would create delayed locking for generic plans, after parameters > are known and partition pruning occurs, I believe generic plan will be on > pars with custom. > So, I`m sticking with plan cache parameter for feature development, that was > clear.

Re: "set role" semantics

> adrian.kla...@aklaver.com wrote: > >> david.g.johns...@gmail.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> Notice that I didn't grant "connect" on either of the databases, "d1" or >>> "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr". >> >> You didn't have to since PUBLIC get

Re: copy file from a client app to remote postgres isntance

On 11/9/22 10:17, Peter J. Holzer wrote: On 2022-11-07 14:40:40 -0600, Ron wrote: On 11/7/22 10:57, Вадим Самохин wrote: I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https:// stackoverflow.c

Re: "set role" semantics

Hi, Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn a écrit : > adrian.kla...@aklaver.com wrote: > > david.g.johns...@gmail.com wrote: > > b...@yugabyte.com wrote: > > Notice that I didn't grant "connect" on either of the databases, "d1" or > "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mg

Re: "set role" semantics

On 11/9/22 10:55 AM, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: Revoking PUBLIC has been explained before to you (Bryn Llewellyn). A quick search: https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us

Re: "set role" semantics

On Wed, Nov 9, 2022 at 11:55 AM Bryn Llewellyn wrote: > > Here's an extract from the script that I copied in my first email: > > > > > > > *create database d1;revoke all on database d1 from public;create database > d2;revoke all on database d2 from public;* > > Didn't I do exactly what you both s

Re: "set role" semantics

On 11/9/22 10:55 AM, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: Revoking PUBLIC has been explained before to you (Bryn Llewellyn). A quick search: https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us

Re: "set role" semantics

On Tue, Nov 8, 2022 at 5:16 PM Bryn Llewellyn wrote: > > Is there anything that can be done to limit the scope of the ability to > end up in a database like I'd thought would be possible? (A little test > showed me that "set role" doesn't fire an event trigger.) > > I do see that, as far as I've

Re: "set role" semantics

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Is there anything that can be done to limit the scope of the ability to end >> up in a database like I'd thought would be possible? (A little test showed >> me that "set role" doesn't fire an event trigger.) >> >> I do see

Re: "set role" semantics

On 11/9/22 12:31, Bryn Llewellyn wrote: Thanks. If nobody thinks that ending up as I showed is possible brings any kind of risk, then I’m happy to accept that. More generally, I’m a huge fan of the principle of least privilege, and (as far as it concerns what I asked about in this thread), it

Re: copy file from a client app to remote postgres isntance

On 2022-11-09 12:57:23 -0600, Ron wrote: > On 11/9/22 10:17, Peter J. Holzer wrote: > > On 2022-11-07 14:40:40 -0600, Ron wrote: > > > On 11/7/22 10:57, Вадим Самохин wrote: > > > I have an application that must copy a local file in csv format to a > > > postgres table on a remote host. T

Re: "set role" semantics

adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: > > Connecting to database and the role that is in play inside a session are two > different things. Making them the same would make things [security define vs > "security invoker"] go sideways. I said nothing to suggest that the r

Re: Q: pg_hba.conf separate database names file format

On Wed, Nov 09, 2022 at 04:02:43AM -0600, Ron wrote: > Are these "include" files supposed to solve the problem of having a *lot* of > databases (or users) that you want to allow access to? Yes, splitting the list of users and database eases the maintenance and readability of pg_hba.conf as each HB

List user databases

Hi, ALL, According to https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, there are generally 3 system DBs. However I'm confused with the word general. How many system databases can be made on a PG server? Thank you.

Re: List user databases

2022年11月10日(木) 13:41 Igor Korot : > > Hi, ALL, > According to > https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, > there are generally 3 system DBs. > > However I'm confused with the word general. > How many system databases can be made on a PG se

Re: List user databases

On 11/9/22 20:57, Ian Lawrence Barwick wrote: 2022年11月10日(木) 13:41 Igor Korot : Hi, ALL, According to https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, there are generally 3 system DBs. However I'm confused with the word general. How many syst

Re: "set role" semantics

On 11/9/22 15:23, Bryn Llewellyn wrote: adrian.kla...@aklaver.com wrote: b...@yugabyte.com wrote: Connecting to database and the role that is in play inside a session are two different things. Making them the same would make things [security define vs "security invoker"] go sideways. I s

Re: List user databases

Hi, On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote: > On 11/9/22 20:57, Ian Lawrence Barwick wrote: > > > > template0 and template1 are the mandatory system databases which > > cannot be dropped. > > Actually that is not strictly true: > > https://www.postgresql.org/docs/current/man

Re: "set role" semantics

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> Anyway, all this is moot (except in that thinking about it helps me to >> enrich my mental model) because the privilege notions here will never change. > > So, I want it but not really. I’d rather say “I’d very much prefer

Re: List user databases

2022年11月10日(木) 14:16 Adrian Klaver : > > On 11/9/22 20:57, Ian Lawrence Barwick wrote: > > 2022年11月10日(木) 13:41 Igor Korot : > >> > >> Hi, ALL, > >> According to > >> https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/, > >> there are generally 3 syst