Re: connect permission based on database name

Rob Sargent writes: > Just wondering if I've bumped into some security issue. > I'm somewhat surprised that "grant connect to database   to > " appears to be stored "by name"? I think you are forgetting that databases have a default GRANT CONNECT TO PUBLIC. You need to revoke that before other

Connect to specific cluster on command line

Hi there, how can I connect to a specific cluster on the command line, e. g. with psql, pg_dump or pg_dumpall? pg_lsclusters returns a list of all clusters available: Ver Cluster Port [...] 14 main5432 ... 14 test5433 ... I want to connect to or dump database xyz in the test

Re: existing row not found by SELECT ... WHERE CTID = ?

No. PostgreSQL may remove a dead row, but a dead row is by definition no longer visible, so it wouldn't be found by a query. I am wondering whether it is a good practice to use CTID in a where clause. years ago when I use to code in Informix, using ROWID as a generic substitute for

Re: connect permission based on database name

On 5/25/22 08:20, Tom Lane wrote: Rob Sargent writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I think you are forgetting that databases have a default GRANT CONNECT TO PUBLIC. You

Re: existing row not found by SELECT ... WHERE CTID = ?

Ravi Krishna writes: >> No. PostgreSQL may remove a dead row, but a dead row is by definition >> no longer visible, so it wouldn't be found by a query. > I am wondering whether it is a good practice to use CTID in a where > clause. It's fine if part of your business logic is that you don't

Re: connect permission based on database name

On Wednesday, May 25, 2022, Rob Sargent wrote: > On 5/25/22 08:20, Tom Lane wrote: > > Rob Sargent writes: > > Just wondering if I've bumped into some security issue. > I'm somewhat surprised that "grant connect to database to > " appears to be stored "by name"? > > I think you are

Re: Connect to specific cluster on command line

On Wed, 2022-05-25 at 16:48 +0200, Carsten Klein wrote: > So, forget about the packager. With core PostgreSQL tools it is possible > to have more than one cluster. How do you specify what cluster to > connect to with psql or pg_dump? Do I have to specify the cluster's > corresponding Unix

Re: Connect to specific cluster on command line

Carsten Klein writes: > So, forget about the packager. With core PostgreSQL tools it is possible > to have more than one cluster. How do you specify what cluster to > connect to with psql or pg_dump? Do I have to specify the cluster's > corresponding Unix domain socket directory via the --host

pg_create_logical_replication_slot in DB1 is blocked by a session in DB2

I'm running this command while connected to pg cluster DB1: SELECT * FROM pg_create_logical_replication_slot('test_slot_99', 'test_decoding'); When I examine pg_locks, I see the session is waiting on virtualxid and blocked and blocking sessions are on two different DBs. After doing some

Re: connect permission based on database name

On 5/25/22 08:20, Tom Lane wrote: Rob Sargent writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I think you are forgetting that databases have a default GRANT CONNECT TO PUBLIC. You

Re: Connect to specific cluster on command line

Carsten Klein writes: > how can I connect to a specific cluster on the command line, e. g. with > psql, pg_dump or pg_dumpall? > pg_lsclusters returns a list of all clusters available: pg_lsclusters is not part of core Postgres, and neither is this --cluster option you mention. I'm vaguely

Re: connect permission based on database name

On 5/25/22 08:44, David G. Johnston wrote: On Wednesday, May 25, 2022, Rob Sargent wrote: On 5/25/22 08:20, Tom Lane wrote: Rob Sargent writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect

Re: Connect to specific cluster on command line

pg_lsclusters is not part of core Postgres, and neither is this --cluster option you mention. I'm vaguely aware that some packager (Debian I think) has added an overlay of that sort; but you'd need to consult the package-level documentation not the community docs in order to find out more.

Re: Connect to specific cluster on command line

On Wednesday, May 25, 2022, Carsten Klein wrote: > > >> pg_lsclusters is not part of core Postgres, and neither is this >> --cluster option you mention. I'm vaguely aware that some packager >> (Debian I think) has added an overlay of that sort; but you'd need >> to consult the package-level

Re: Connect to specific cluster on command line

On 5/25/22 07:30, Carsten Klein wrote: Hi there, Perl script pg_backupcluster calls psql and pg_dump with a --cluster option: pg_dump --cluster 14/test ... However, this option is not documented (at least I didn't find anything) and also I didn't find anything in the sources on GitHub.

Re: Connect to specific cluster on command line

On Wed 2022-05-25 at 17:05 David G. Johnston wrote: IIRC they write wrapper scripts they put into the version-agnostic bin directory that deal with the version/cluster-name scheme they’ve setup before calling the core commands located in the version-specific install directory. You are

Re: Automatic PK values not added to new rows

On Wed, 25 May 2022, David G. Johnston wrote: The value the sequence provides next is wholly independent of everything except the state of the sequence. It doesn’t care how many rows any table, even its owner, has. The very existence of the delete command should make this self-evident. David

Re: Automatic PK values not added to new rows

On 5/25/22 11:15 AM, Rich Shepard wrote: On Wed, 25 May 2022, Adrian Klaver wrote: Do: select * from people_person_nbr_seq; and report back the results. Adrian, Huh! bustrac=# select * from people_person_nbr_seq;  last_value | log_cnt | is_called +-+---    

Re: Automatic PK values not added to new rows

On Wed, 25 May 2022, Adrian Klaver wrote: What is max(person_nbr)? bustrac=# select max(person_nbr) from people; max - 965 (1 row) Rich

Re: Automatic PK values not added to new rows [RESOLVED]

On Wed, 25 May 2022, Adrian Klaver wrote: From: https://www.postgresql.org/docs/current/functions-sequence.html SELECT setval('people_person_nbr_seq', 965); Adrian, Thanks. I didn't know where to look in the docs. Regards, Rich

Re: Automatic PK values not added to new rows

On Wed, 25 May 2022, Thomas Kellerer wrote: If you want to prevent such a situation in the future, you might want to consider defining those columns as "generated always as identity" instead of "serial". Then you'd get an error if you try to bypass the database generated values. Thanks,

Re: Pg14 possible index corruption after reindex concurrently

Hello, you are right it looks to be related, on our running system with connected users such an issue happens not only on primary keys but also on other (non-unique) indexes. I've checked all indexes using amcheck: select * from bt_index_check(index =>

Re: "Join Postgres on Slack" ?

On Wednesday, May 25, 2022, Bryn Llewellyn wrote: > Forgive me if this is the wrong address. > > I went to https://postgresteam.slack.com/join/signup but found that my > email address has an unknown domain. It says "Don’t have an email address > from one of those domains? Contact the workspace

Re: Automatic PK values not added to new rows

On 5/25/22 11:18 AM, Rich Shepard wrote: On Wed, 25 May 2022, Adrian Klaver wrote: What is max(person_nbr)? bustrac=# select max(person_nbr) from people;  max -  965 (1 row) From: https://www.postgresql.org/docs/current/functions-sequence.html SELECT setval('people_person_nbr_seq',

Re: Automatic PK values not added to new rows

On 5/25/22 10:43 AM, Rich Shepard wrote: The People table has 965 rows; the table structure is: person_nbr 683 is not in any row to be inserted. I was under the impression that the person_nbr for the new rows would start with 966 but that's apparently not happening. What have I missed?

Re: Automatic PK values not added to new rows

On Wednesday, May 25, 2022, Rich Shepard wrote: > > I'm trying to insert 15 new rows to that table by inserting all columns > except the first one.. That… > > person_nbr 683 is not in any row to be inserted. Implies that. > > I was under the impression that the person_nbr for the new

"Join Postgres on Slack" ?

Forgive me if this is the wrong address. I went to https://postgresteam.slack.com/join/signup but found that my email address has an unknown domain. It says "Don’t have an email address from one of those domains? Contact the workspace administrator

Re: Automatic PK values not added to new rows

On Wed, 25 May 2022, Adrian Klaver wrote: Do: select * from people_person_nbr_seq; and report back the results. Adrian, Huh! bustrac=# select * from people_person_nbr_seq; last_value | log_cnt | is_called +-+--- 683 | 32 | t (1 row) It's out of

Re: PG 13.6 : Data corruption error message not being sent to syslog

But the error doesn't consist of any of the above codes, so why is it being skipped? On Wed, 25 May 2022, 23:11 Tom Lane, wrote: > Abhishek Bhola writes: > > This morning I noticed this error in my PG CSV log file. > > 2022-05-25 23:59:17.776 JST,,,117110,,628cf2c5.1c976,1,,2022-05-25 >

Re: "Join Postgres on Slack" ?

david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> …I went to https://postgresteam.slack.com/join/signup but found that my >> email address has an unknown domain. It says "Don’t have an email address >> from one of those domains? Contact the workspace administrator at

Re: Automatic PK values not added to new rows

Rich Shepard schrieb am 25.05.2022 um 20:15: On Wed, 25 May 2022, Adrian Klaver wrote: Do: select * from people_person_nbr_seq; and report back the results. Adrian, Huh! bustrac=# select * from people_person_nbr_seq;  last_value | log_cnt | is_called +-+---

Automatic PK values not added to new rows

The People table has 965 rows; the table structure is: Table "public.people" Column | Type | Collation | Nullable | Default

Re: Automatic PK values not added to new rows

On 5/25/22 20:10, Ron wrote: On 5/25/22 13:17, Rich Shepard wrote: On Wed, 25 May 2022, David G. Johnston wrote: The value the sequence provides next is wholly independent of everything except the state of the sequence. It doesn’t care how many rows any table, even its owner, has. The very

Re: Automatic PK values not added to new rows

On 5/25/22 13:17, Rich Shepard wrote: On Wed, 25 May 2022, David G. Johnston wrote: The value the sequence provides next is wholly independent of everything except the state of the sequence. It doesn’t care how many rows any table, even its owner, has. The very existence of the delete command

Re: cast to domain with default collation issue.

postgresql 15 manual parts: Otherwise, all input expressions must have the same implicit collation > derivation or the default collation. If any non-default collation is > present, that is the result of the collation combination. Otherwise, the > result is the default collation. > For example,

Re: cast to domain with default collation issue.

Please don’t top-post. On Tuesday, May 24, 2022, jian he wrote: > > Otherwise, all input expressions must have the same implicit collation >> derivation or the default collation. If any non-default collation is >> present, that is the result of the collation combination. Otherwise, the >>

link that explain ICU_LOCALE locale concept about local variant code and keywords.

code from https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndquadrant.com > DROP DATABASE IF EXISTS dbicu; > CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'en_US' ICU_LOCALE > 'en-u-kf-upper' template 'template0'; > \c dbicu > CREATE COLLATION upperfirst

PG 13.6 : Data corruption error message not being sent to syslog

This morning I noticed this error in my PG CSV log file. ``` 2022-05-25 23:59:17.776 JST,,,117110,,628cf2c5.1c976,1,,2022-05-25 23:59:17 JST,9/611296,0,ERROR,XX001,"uncommitted xmin 16395356 from before xid cutoff 144683296 needs to be frozen","while scanning block 19267 of relation

Re: existing row not found by SELECT ... WHERE CTID = ?

El día martes, mayo 24, 2022 a las 12:11:49 -0400, Tom Lane escribió: > Laurenz Albe writes: > > It may well be that somebody deleted or updated a few rows between the time > > the cursor was materialized and the time the 5th row was fetched. > > Even without HOLD, a cursor will return a

Re: link that explain ICU_LOCALE locale concept about local variant code and keywords.

I found answer on https://www.unicode.org/reports/tr35/tr35-collation.html and https://cldr.unicode.org/index/bcp47-extension On Wed, May 25, 2022 at 1:52 PM jian he wrote: > > code from > https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndquadrant.com > >>

Re: link that explain ICU_LOCALE locale concept about local variant code and keywords.

jian he wrote: > So which website can I get the info about the "kf" and "u". https://unicode.org/reports/tr35/#u_Extension https://www.unicode.org/reports/tr35/tr35-collation.html#Collation_Settings Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite

Re: existing row not found by SELECT ... WHERE CTID = ?

On Wed, 2022-05-25 at 11:21 +0200, Matthias Apitz wrote: > Is it possible that the PostgreSQL 13.1 server does something by its own to > invalidate the rowid? No. PostgreSQL may remove a dead row, but a dead row is by definition no longer visible, so it wouldn't be found by a query. Yours,

Re: existing row not found by SELECT ... WHERE CTID = ?

## Matthias Apitz (g...@unixarea.de): > We will solve the problem now with setting the session after connect to > >SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; > > (with an appropriate ESQL/C call). Any comments? Maybe the real question is whether it is wise

Re: cast to domain with default collation issue.

On Wednesday, May 25, 2022, jian he wrote: > > I personally feel wording *non-default* may not be that correct. Because > if the column is text then it automatically at least has default collation. > Non-default means “a value that is not the default value”. David J.

Re: PG 13.6 : Data corruption error message not being sent to syslog

Abhishek Bhola writes: > This morning I noticed this error in my PG CSV log file. > 2022-05-25 23:59:17.776 JST,,,117110,,628cf2c5.1c976,1,,2022-05-25 23:59:17 > JST,9/611296,0,ERROR,XX001,"uncommitted xmin 16395356 from before xid > cutoff 144683296 needs to be frozen","while scanning block

Re: existing row not found by SELECT ... WHERE CTID = ?

El día Mittwoch, Mai 25, 2022 a las 12:51:02 +0200, Laurenz Albe escribió: > On Wed, 2022-05-25 at 11:21 +0200, Matthias Apitz wrote: > > Is it possible that the PostgreSQL 13.1 server does something by its own to > > invalidate the rowid? > > No. PostgreSQL may remove a dead row, but a dead

Re: existing row not found by SELECT ... WHERE CTID = ?

On Wed, 2022-05-25 at 14:27 +0200, Christoph Moench-Tegeder wrote: > ## Matthias Apitz (g...@unixarea.de): > > > We will solve the problem now with setting the session after connect to > > > >    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE > > READ; > > > > (with an

connect permission based on database name

Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I have the luxury of dropping/recreate databases at will (within limits).  My script for creating a new db also creates a role and grants

Re: Connect to specific cluster on command line

On 5/25/22 16:48, Carsten Klein wrote: So, forget about the packager. With core PostgreSQL tools it is possible to have more than one cluster. How do you specify what cluster to connect to with psql or pg_dump? psql, pg_dump and friends know the -p (or --port) option. Maybe man psql and man

Extension pg_trgm, permissions and pg_dump order

Hello, using the postgres:14.3 docker container, the following fails - whereas using the 14.2 container, it succeeds. A bug? My minimal example goes like this: On the fresh container, execute ```sql CREATE ROLE limitedrole; CREATE SCHEMA ext_trgm; CREATE EXTENSION pg_trgm SCHEMA ext_trgm;