Re: Automatic PK values not added to new rows

[Adrian Klaver]

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 existence of the delete command should 
make

this self-evident.


David J.,

I didn't know that.


people_person_nbr_seq would have to somehow peek into public.people and 
automatically update itself.  I'd be hopping mad if Postgresql did that 
after I explicitly set the value of people_person_nbr_seq to the value 
of my choosing, as if Postgresql knows better than I do what I want the 
next value of people_person_nbr_seq to be.



Though to be honest if you are setting up an automatic id system, be it 
serial or identity, and then overriding it with your own values you are 
looking for issues. Either let the system do it's thing and be an out of 
site out of mind number generator or take full control of the id 
generation yourself.



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

Re: Automatic PK values not added to new rows

[Ron]

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 should make
this self-evident.


David J.,

I didn't know that.


people_person_nbr_seq would have to somehow peek into public.people and 
automatically update itself.  I'd be hopping mad if Postgresql did that 
after I explicitly set the value of people_person_nbr_seq to the value of my 
choosing, as if Postgresql knows better than I do what I want the next value 
of people_person_nbr_seq to be.


--
Angular momentum makes the world go 'round.

Re: Automatic PK values not added to new rows

[Rich Shepard]

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

Rich

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

[Abhishek Bhola]

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
> 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 ""relation_name""
>
> > This was a data corruption error that I resolved by truncating the table
> > and reloading it. However, I was curious why this message was not sent to
> > my syslog.
>
> Seems to me this filter explains that well enough:
>
> > ## Added by DataConsulting Team for syslog filter
> > if $programname == 'postgres' and \
> > ($msg contains 'CODE:28000'or \
> > $msg contains 'CODE:28P01' or \
> > $msg contains 'CODE:3D000' or \
> > $msg contains 'CODE:08006' or \
> > $msg contains 'CODE:42501'\
> > ) then ///xxx/pg_log/postgres_filter.log
>
> You should realize of course that syslog is not a 100% reliable logging
> mechanism -- at least most implementations are capable of dropping
> messages under load.  But we needn't guess about reasons for missed
> messages here.
>
> regards, tom lane
>

-- 
_This correspondence (including any attachments) is for the intended 
recipient(s) only. It may contain confidential or privileged information or 
both. No confidentiality or privilege is waived or lost by any 
mis-transmission. If you receive this correspondence by mistake, please 
contact the sender immediately, delete this correspondence (and all 
attachments) and destroy any hard copies. You must not use, disclose, copy, 
distribute or rely on any part of this correspondence (including any 
attachments) if you are not the intended 
recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_

Re: Automatic PK values not added to new rows

[Thomas Kellerer]

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 +-+---
     683 |  32 | t
(1 row)

It's out of sync with
  select max(person_nbr) from people;

Is there a way for me to synchronize the two?



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.

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

[Rich Shepard]

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

[Adrian Klaver]

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', 965);



Rich





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

Re: Automatic PK values not added to new rows

[Rich Shepard]

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

[Rich Shepard]

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

I didn't know that.

Thanks,

Rich

Re: Automatic PK values not added to new rows

[Adrian Klaver]

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 +-+---
     683 |  32 | t
(1 row)

It's out of sync with
  select max(person_nbr) from people;


What is max(person_nbr)?



Is there a way for me to synchronize the two?

Thanks,

Rich





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

Re: Automatic PK values not added to new rows

[Rich Shepard]

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 sync with
 select max(person_nbr) from people;

Is there a way for me to synchronize the two?

Thanks,

Rich

Re: "Join Postgres on Slack" ?

[Bryn Llewellyn]

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 Postgres 
>> for an invitation." The word "Postgres" is bolded. But it isn't a link. What 
>> must I do to join?
> 
> There is a web form for getting invited.
> 
> https://www.postgresql.org/community/
> https://postgres-slack.herokuapp.com/ 

Thanks, David. That was easy—when you know how!

Re: Automatic PK values not added to new rows

[David G. Johnston]

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 rows would start
> with 966 but that's apparently not happening.
>

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

Re: Automatic PK values not added to new rows

[Adrian Klaver]

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?


Do:

 select * from people_person_nbr_seq;

and report back the results.



Rich





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

Automatic PK values not added to new rows

[Rich Shepard]

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

---+---+---+--+-
 person_nbr| integer   |   | not null | 
nextval('people_person_nbr_seq'::reg
class)
 lname | character varying(32) |   | not null | '??'::character 
varying
 fname | character varying(15) |   | not null | '??'::character 
varying
 job_title | character varying(48) |   |  |
 company_nbr   | integer   |   |  |
 loc_nbr   | integer   |   | not null | 1
 loc_phone_ext | character varying(32) |   |  |
 direct_phone  | character varying(15) |   |  |
 direct_fax| character varying(15) |   |  |
 cell_phone| character varying(15) |   |  |
 email | character varying(64) |   |  |
 active| boolean   |   | not null | true
 comment   | text  |   |  | 
Indexes:

"people_pkey" PRIMARY KEY, btree (person_nbr)
Foreign-key constraints:
"people_org_nbr_fkey" FOREIGN KEY (company_nbr) REFERENCES 
companies(company_nbr) ON UPDATE CASC
ADE ON DELETE RESTRICT

I'm trying to insert 15 new rows to that table by inserting all columns
except the first one.. Psql tells me:
psql:insert_into_people.sql:16: ERROR:  duplicate key value violates unique constraint 
"people_pkey"
DETAIL:  Key (person_nbr)=(683) already exists.

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?

Rich

Re: "Join Postgres on Slack" ?

[David G. Johnston]

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 administrator
> at Postgres for an invitation." The word "Postgres" is bolded. But it isn't
> a link.
>
> What must I do to join?
>

There is a web form for getting invited.

https://www.postgresql.org/community/

https://postgres-slack.herokuapp.com/

David J.

"Join Postgres on Slack" ?

[Bryn Llewellyn]

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 at Postgres for an invitation." 
The word "Postgres" is bolded. But it isn't a link.

What must I do to join?

Re: Pg14 possible index corruption after reindex concurrently

[Aleš Zelený]

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 =>
'prematch.opportunities.pk_tabodds_idodds'::regclass::oid, heapallindexed
=> true);

Failed one rebuild and check again to ensure all things are OK.

Still have a problem and haven't found any explanation in the manuals:

Run amcheck to validate a table and its indexes:

/usr/pgsql-14/bin/pg_amcheck -d prematch -r opportunities.tab_odds
--heapallindexed -j 16 -Pv ; echo $?
...
btree index "prematch.opportunities.pk_tabodds_idodds":
ERROR:  heap tuple (2199116,5) from table "tab_odds" lacks matching
index tuple within index "pk_tabodds_idodds"
HINT:  Retrying verification using the function bt_index_parent_check()
might provide a more specific error.
2


OK, rebuild the index (first give it a try to use the concurrently option):
prematch=# REINDEX INDEX CONCURRENTLY
prematch.opportunities.pk_tabodds_idodds;

REINDEX
prematch=#
prematch=# select * from bt_index_check(index =>
'prematch.opportunities.pk_tabodds_idodds'::regclass::oid, heapallindexed
=> true);


 bt_index_check


(1 row)

Looks we were lucky.

Just to be sure, one more check from the command line:

/usr/pgsql-14/bin/pg_amcheck -d prematch -r opportunities.tab_odds
--heapallindexed -j 16 -P ; echo $?
 0/15 relations (0%),0/16807950 pages (0%)
heap table "prematch.opportunities.tab_odds", block 1649057, offset 47:
xmin 4062380236 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 1649057, offset 48:
xmax 4062380236 precedes relation freeze threshold 2:3960858664
...
xmax 4062380813 precedes relation freeze threshold 2:3960858664
heap table "prematch.opportunities.tab_odds", block 2210728, offset 8:
xmax 4062380814 precedes relation freeze threshold 2:3960858664
2

So I got non-zero exit code, but no errors in the log file:

-bash-4.2$ grep -i ERROR 20220525_1710_prematch.opportunities.tab_odds.log
-bash-4.2$

We have multiple DB servers upgraded to 14, only a few amchecks:

( time /usr/pgsql-14/bin/pg_amcheck -a --heapallindexed -j 16 -P ; echo $?
) |& tee -a `date +%Y%m%d_%H%M`_`hostname`_amcheck.log

finished with zero exit code, most of them returned 2, within the xmin/xmax
messages as shown above. Is that an issue (since missing entries in the
index cause ERRRO reported in the log, but after rebuilding the indexes it
was not the case anymore)?
If it is an issue, is there a way to fix it?

Thanks Ales

út 24. 5. 2022 v 21:58 odesílatel Thomas Munro 
napsal:

> On Wed, May 25, 2022 at 6:17 AM Aleš Zelený  wrote:
> > SELECT format('REINDEX SCHEMA CONCURRENTLY %I;', n.nspname)
>
> This may be related to bug #17485, discussed at:
>
>
> https://www.postgresql.org/message-id/flat/17485-396609c6925b982d%40postgresql.org
>

Re: Connect to specific cluster on command line

[Andreas Fröde]

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 pg_dump are your friends.

Extension pg_trgm, permissions and pg_dump order

[StMUK]

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;
GRANT USAGE ON SCHEMA ext_trgm TO limitedrole;

SET ROLE limitedrole;
CREATE TABLE x(y text);
CREATE INDEX ON x USING gist(y ext_trgm.gist_trgm_ops);
```

Dump the database with `pg_dump > /tmp/x`, then do
```sql
DROP SCHEMA ext_trgm CASCADE; DROP TABLE x;
```
(or alternatively create a fresh database and do a ` CREATE ROLE limitedrole;`)

Then try to restore the dump with `cat /tmp/x | psql`.

On version 14.2, this succeeds.
On version 14.3, this fails with "ERROR:  permission denied for schema 
ext_trgm".

Please note that in the dump, "GRANT USAGE ON SCHEMA ext_trgm TO limitedrole;" 
comes after "CREATE INDEX x_y_idx ON public.x USING gist (y 
ext_trgm.gist_trgm_ops);"; but this shouldn't matter since the restoration is 
called with "postgres" superuser privileges!?


Regards,
Franz-Josef Färber

Re: Connect to specific cluster on command line

[Carsten Klein]

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 completely right. For example, /usr/bin/pg_dump is a Perl script 
which evaluates the --cluster option and then calls the real PG tool.


@all Many thanks for all helpful suggestions :)

Regards, Carsten

Re: Connect to specific cluster on command line

[Adrian Klaver]

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.


It postgresql-common provided as part of the Debian/Ubuntu packaging. If 
you want the documentation then:


man postgresql-common



Actually, I only have the 14/main instance and cannot really test 
accessing the `test` cluster. However, psql and pg_dump actually work 
with --cluster 14/main and report an error when I specify 14/test so, 
the option --cluster seems to work. Is it intentionally not documented?


So, whats the recommended way to connect to a specific cluster? Is it 
just the port?


On Debian/Ubuntu using their packaged versions then use the 
postgresql-common tool, it makes things a lot easier. Otherwise follow 
the instructions others have provided for using -h and -p. You will also 
need to do -h/-p if you are trying to reach remote instances of Postgres.




Regards, Carsten






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

Re: Connect to specific cluster on command line

[David G. Johnston]

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 documentation not the community docs
>> in order to find out more.
>>
>>
> Sorry, my fault, it's Debian/Ubuntu. Actually they must have added the
> --cluster option to the PG programs through source code patches...
>

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.

David J.

pg_create_logical_replication_slot in DB1 is blocked by a session in DB2

[Fred Habash]

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 research, it looks like locks across DB can happen in
postgres if the session queries rely on 'shared tables'. Not sure if this
applies here.

How can this be explained?


This is the session issuing the create slot command

datid|datname  |pid  |leader_pid|usesysid|usename
|application_name|client_addr |client_hostname|client_port|backend_start
  |xact_start |query_start|state_change
 |wait_event_type|wait_event|state |backend_xid|backend_xmin|query



|backend_type  |
-|-|-|--||-|||---|---|---|---|---|---|---|--|--|---||---|--|
16408|db1  |13405|  |   16394|test99   |
|xx.xxx.xxx.x|   |  53398|2022-05-25 09:12:41|2022-05-25
09:12:42|2022-05-25 09:12:42|2022-05-25 09:12:42|Lock
 |virtualxid|active|   |171577399   |BEGIN;declare
"SQL_CUR0x14680c0bace0" cursor with hold for SELECT lsn FROM
pg_create_logical_replication_slot('qitx6iolfhy5zfkl_00016408_66eb6ba3_1fe1_4ccd_95ed_fd3d2d5d4ad8',
'test_decoding');fetch 1 in "SQL_CUR0x14680c0bace0"|client backend|


Session above is blocked by pid 8602

blocked_pid|blocked_user|blocking_pid|blocking_user  |blocked_statement



|current_statement_in_blocking_process


|
---|||---|---|---|
  14305|pq_devops   |8602|service_con|BEGIN;declare
"SQL_CUR0x1464680d6a60" cursor with hold for SELECT lsn FROM
pg_create_logical_replication_slot('4iipu5a2hnuyfp3u_00016408_036cac77_3854_4320_b329_e7209b4cccf9',
'test_decoding');fetch 1 in "SQL_CUR0x1464680d6a60"|¶  SELECT **
  |


The blocked and blocking sessions are on two different DBs

datid|datname  |pid  |leader_pid|usesysid|usename|application_name
|cl
-|-|-|--||---|--|--
16408|db1  |13405|  |   16394|test99 |
|10
16407|db2  | 8602|  |29429933|service_con_9  |PostgreSQL JDBC
Driver|10

Thank you

Re: Connect to specific cluster on command line

[Tom Lane]

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 option?

Usually you'd use -p (--port).  You *could* specify the exact path
to the postmaster's socket via -h, but I think that's not idiomatic.

regards, tom lane

Re: Connect to specific cluster on command line

[Laurenz Albe]

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 domain socket directory via the --host option?

With -h you specify the directory containing the socket, and
with -p (port) the name.

So you could

  psql -h /var/run/postgresql -p 

to use the socket /var/run/postgresql/.s.PGSQL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Connect to specific cluster on command line

[Carsten Klein]

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.



Sorry, my fault, it's Debian/Ubuntu. Actually they must have added the 
--cluster option to the PG programs through source code patches...


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 option?


Regards, Carsten

Re: connect permission based on database name

[Rob Sargent]

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 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 grants/revokes will
have any functional effect.

regards, tom lane

And then the search path is "just a string"?



Search_path isn’t a security component and accepts, but ignores, 
unknown names.  So yes, it is just a string.


David J.


Roger that, thanks.

Re: connect permission based on database name

[David G. Johnston]

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 forgetting that databases have a default GRANT CONNECT
> TO PUBLIC.  You need to revoke that before other grants/revokes will
> have any functional effect.
>
>   regards, tom lane
>
> And then the search path is "just a string"?
>
>
>
Search_path isn’t a security component and accepts, but ignores, unknown
names.  So yes, it is just a string.

David J.

Re: connect permission based on database name

[Rob Sargent]

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 need to revoke that before other grants/revokes will
have any functional effect.

regards, tom lane

And then the search path is "just a string"?

   psql --user oldrole --dbname newdb --host $DBHOST
   psql (12.11, server 12.7)
   SSL connection (protocol: TLSv1.2, cipher:
   ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
   Type "help" for help.

   newdb=> show search_path;
    search_path
   
 study, base, public
   (1 row)

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

[Tom Lane]

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 want to allow
concurrent updates.  In this case, the OP seems to want to prevent rather
than tolerate the concurrent update, so I don't think he needs to revisit
the app's use of CTID.

If you do need to support concurrent updates, then yeah relying on CTID
is likely to be problematic.

regards, tom lane

Re: Connect to specific cluster on command line

[Tom Lane]

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

regards, tom lane

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

[Ravi Krishna]

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 primary key was discouraged precisely for the 
same reason as described here for CTID. Sometimes rowid can change under 
concurrent updates.

Connect to specific cluster on command line

[Carsten Klein]

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 cluster. Is it 
sufficient to specify the cluster's port only?


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.


Actually, I only have the 14/main instance and cannot really test 
accessing the `test` cluster. However, psql and pg_dump actually work 
with --cluster 14/main and report an error when I specify 14/test so, 
the option --cluster seems to work. Is it intentionally not documented?


So, whats the recommended way to connect to a specific cluster? Is it 
just the port?


Regards, Carsten

Re: connect permission based on database name

[Rob Sargent]

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 need to revoke that before other grants/revokes will
have any functional effect.

regards, tom lane

Yes, of course.  Thanks

Re: connect permission based on database name

[Tom Lane]

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 grants/revokes will
have any functional effect.

regards, tom lane

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

[Tom Lane]

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 19267 of
> relation ""relation_name""

> This was a data corruption error that I resolved by truncating the table
> and reloading it. However, I was curious why this message was not sent to
> my syslog.

Seems to me this filter explains that well enough:

> ## Added by DataConsulting Team for syslog filter
> if $programname == 'postgres' and \
> ($msg contains 'CODE:28000'or \
> $msg contains 'CODE:28P01' or \
> $msg contains 'CODE:3D000' or \
> $msg contains 'CODE:08006' or \
> $msg contains 'CODE:42501'\
> ) then ///xxx/pg_log/postgres_filter.log

You should realize of course that syslog is not a 100% reliable logging
mechanism -- at least most implementations are capable of dropping
messages under load.  But we needn't guess about reasons for missed
messages here.

regards, tom lane

connect permission based on database name

[Rob Sargent]

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 
that role access to that database.  Of course on re-running the script a 
second time (with same names for db, role) the portion of the script 
dealing with the role, in its own transaction, fails after the "create 
role" line.  All that is clearly understood. (And I realize I could 
clean up the role per drop db.)
However, I can still connect to the new database (of same name) using 
the previously connected role using psql --user role --dbname db.  For 
the original "grant connect" to still be in play, wouldn't it have to be 
store using dbname as opposed to an id?  In a different environment, I 
can see it might be surprising that an "old" role could connect to a 
"new" database.


   begin;
   create schema if not exists study\p\g
   create role role with login encrypted password 'password'\p\g
   alter role role set search_path=study,base,public\p\g
   grant connect on database dbname to role\p\g
   -- 
   -- Allow this role to diddle with base, bulk and project tables
   -- 
   grant all on schema base, bulk, study to role\p\g

   grant all on all tables in schema base, bulk, study to role\p\g
   commit;

Re: cast to domain with default collation issue.

[David G. Johnston]

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: existing row not found by SELECT ... WHERE CTID = ?

[Laurenz Albe]

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 appropriate ESQL/C call). Any comments?
> 
> Maybe the real question is whether it is wise to use an implementation
> artifact (ctid) to identify rows?
> The textbook approach could be row locks (SELECT ... FOR SHARE/UPDATE and
> variants) to prevent concurrent changes or optimistic locking  (and a
> primary key in any case) - but maybe you already investigated those options?

Right.

REPEATABLE READ won't help you there.  True, you will see a stable snapshot
of the database inside a single transaction, but if a concurrent session has
modified the row, you will get a serialization error.
So that is not a solution.

Yours,
Laurenz Albe

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

[Christoph Moench-Tegeder]

## 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 to use an implementation
artifact (ctid) to identify rows?
The textbook approach could be row locks (SELECT ... FOR SHARE/UPDATE and
variants) to prevent concurrent changes or optimistic locking  (and a
primary key in any case) - but maybe you already investigated those options?

Regards,
Christoph

-- 
Spare Space

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

[Matthias Apitz]

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 row is by definition
> no longer visible, so it wouldn't be found by a query.

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?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

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

[Laurenz Albe]

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,
Laurenz Albe

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

[jian he]

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
>
>> 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 (provider = icu, locale = 'en-u-kf-upper');
>> CREATE TABLE icu(def text, en text COLLATE "en_US", upfirst text COLLATE
>> upperfirst);
>> INSERT INTO icu VALUES ('a', 'a', 'a'), ('b','b','b'), ('A','A','A'),
>> ('B','B','B');
>> SELECT def AS def FROM icu ORDER BY def;
>> SELECT def AS en FROM icu ORDER BY en;
>> SELECT def AS upfirst FROM icu ORDER BY upfirst;
>> SELECT def AS upfirst_explicit FROM icu ORDER BY en COLLATE upperfirst;
>> SELECT def AS en_x_explicit FROM icu ORDER BY def COLLATE "en-x-icu";
>>
>
>- trying to replicate the above quoted command.
>- So I don't know * ICU_LOCALE 'en-u-kf-upper'  *the *u *and the *kf *refer
>to?
>
> Even I followed
> https://unicode-org.github.io/icu/userguide/locale/#the-locale-concept
>
>1. Language code
>2. Script code
>3. Country code
>4. Variant code
>5. Keywords
>
> So which website can I get the info about the "kf" and "u".
>
> --
>  I recommend David Deutsch's <>
>
>   Jian
>
>
>

-- 
 I recommend David Deutsch's <>

  Jian

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

[Daniel Verite]

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 = ?

[Matthias Apitz]

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 view of the data as it stood
> when the cursor was opened, just as a plain SELECT does.  There is
> *plenty* of time for another session to get in there if you've been
> groveling through 50K records one at a time.

Tom, Thanks for pointing us in the right direction where to look for a
solution. The CURSOR was opened around 23:11 pm and the CTID not found
at 23:21 pm, i.e. ten minutes later. This piece of software does every
night some housekeeping work in the circulation area of our LMS (Library
Management System) and is meant to run as a standalone job (only one
process after the other). We're trying to figure out with the customer if 
something
else was started/running at this time between 23:11 and 23:21, to shut this
off in the future. Is it possible that the PostgreSQL 13.1 server does
something by its own to invalidate the rowid?

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

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

[Abhishek Bhola]

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 ""relation_name""
```

This was a data corruption error that I resolved by truncating the table
and reloading it. However, I was curious why this message was not sent to
my syslog.

My logging configuration in the `postgresql.conf` is as follows:
```
# Logging settings
# 
log_destination = 'csvlog,syslog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql_%Y%m%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1h
log_rotation_size = 0

log_timezone = 'Japan'
log_line_prefix = '%t [%p]: [%l-1] %h:%u@%d:[PG]:CODE:%e '

log_statement = 'all'
log_min_messages = info # DEBUG5
log_min_error_statement = info  # DEBUG5
log_error_verbosity = default
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_connections = on
log_disconnections = on
log_duration = off
log_min_duration_statement = 1000
log_autovacuum_min_duration = 3000ms
```

The OS info for the server on which this DB is running is as follows:
```
LSB Version::core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description:CentOS Linux release 7.9.2009 (Core)
Release:7.9.2009
Codename:   Core
```

And there is a filter defined for the rsyslog messages as follows:
```
## Added by DataConsulting Team for syslog filter
if $programname == 'postgres' and \
($msg contains 'CODE:28000'or \
$msg contains 'CODE:28P01' or \
$msg contains 'CODE:3D000' or \
$msg contains 'CODE:08006' or \
$msg contains 'CODE:42501'\
) then ///xxx/pg_log/postgres_filter.log

if $programname == 'postgres' and \
( \
not ($msg contains '[PG]') or \
$msg contains 'CODE:0' or \
$msg contains 'CODE:28000' or \
$msg contains 'CODE:28P01' or \
$msg contains 'CODE:3D000' or \
$msg contains 'CODE:42501' or \
$msg contains 'CODE:42601' or \
$msg contains 'CODE:42P01' or \
$msg contains 'CODE:42P02' or \
$msg contains 'CODE:08006' or \
$msg contains  'CODE:42703'   \
) then stop
```

`syslog_ident` and `syslog_facility` is as follows:
```
postgres=# show syslog_ident ;
 syslog_ident
--
 postgres
(1 row)

postgres=# show syslog_facility ;
 syslog_facility
-
 local0
(1 row)
```

I was thinking that such an error message would be sent to the
`/var/log/message`, from where it would be picked, but this wasn't the
case. Could someone explain why this is so and what can I change for these
messages to be sent to syslog?

-- 
_This correspondence (including any attachments) is for the intended 
recipient(s) only. It may contain confidential or privileged information or 
both. No confidentiality or privilege is waived or lost by any 
mis-transmission. If you receive this correspondence by mistake, please 
contact the sender immediately, delete this correspondence (and all 
attachments) and destroy any hard copies. You must not use, disclose, copy, 
distribute or rely on any part of this correspondence (including any 
attachments) if you are not the intended 
recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_

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

[jian he]

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 (provider = icu, locale = 'en-u-kf-upper');
> CREATE TABLE icu(def text, en text COLLATE "en_US", upfirst text COLLATE
> upperfirst);
> INSERT INTO icu VALUES ('a', 'a', 'a'), ('b','b','b'), ('A','A','A'),
> ('B','B','B');
> SELECT def AS def FROM icu ORDER BY def;
> SELECT def AS en FROM icu ORDER BY en;
> SELECT def AS upfirst FROM icu ORDER BY upfirst;
> SELECT def AS upfirst_explicit FROM icu ORDER BY en COLLATE upperfirst;
> SELECT def AS en_x_explicit FROM icu ORDER BY def COLLATE "en-x-icu";
>
- trying to replicate the above quoted command.
- So I don't know * ICU_LOCALE 'en-u-kf-upper'  *the *u *and the *kf *refer
to?
Even I followed
https://unicode-org.github.io/icu/userguide/locale/#the-locale-concept

   1. Language code
   2. Script code
   3. Country code
   4. Variant code
   5. Keywords

So which website can I get the info about the "kf" and "u".

-- 
 I recommend David Deutsch's <>

  Jian

Re: cast to domain with default collation issue.

[jian he]

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, consider this table definition:
>
> CREATE TABLE test1 (
> a text COLLATE "de_DE",
> b text COLLATE "es_ES",
> ...
> );
>
> Then in
>
> SELECT a < 'foo' FROM test1;
>
> the < comparison is performed according to de_DE rules, because the
> expression combines an implicitly derived collation with the default
> collation.
>
 query: * SELECT a < 'foo' FROM test1;*
is an example of {{If any non-default collation is present,  that is the
result of the collation combination. }}

So it should be something like {{ if any side of expression don't have
implicit derived collation is present, that is the result of the collation
combination}

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.

see manual quote about default collation:

> The collation of an expression can be the “default” collation, which
> means the locale settings defined for the database. It is also possible for
> an expression's collation to be indeterminate. In such cases, ordering
> operations and other operations that need to know the collation will fail.
>





On Wed, May 25, 2022 at 12:08 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> 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
>>> result is the default collation.
>>>
>>
>> I think the above quote part can be used to explain the  following
>> examples.
>>
>>> CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", c
>>> text );
>>> SELECT a < 'foo' FROM test1;
>>
>> SELECT c < 'foo' FROM test1;
>>
>> But the *non-default* seems not that correct for me. Like a column if it 
>> does not mention anything, then the default value is null. So
>> * create table test111( a tex*t) The default collation for column a is the 
>> same as the output of  *show lc_collate*.
>>
>> so there is no *non-default? *
>>
>>
> I’m not following the point you are trying to make.  table111.a
> contributes the default collation for any expression needing a collation
> implicitly resolved.
>
> David J.
>
>


-- 
 I recommend David Deutsch's <>

  Jian

Re: cast to domain with default collation issue.

[David G. Johnston]

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
>> result is the default collation.
>>
>
> I think the above quote part can be used to explain the  following
> examples.
>
>> CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", c
>> text );
>> SELECT a < 'foo' FROM test1;
>
> SELECT c < 'foo' FROM test1;
>
> But the *non-default* seems not that correct for me. Like a column if it does 
> not mention anything, then the default value is null. So
> * create table test111( a tex*t) The default collation for column a is the 
> same as the output of  *show lc_collate*.
>
> so there is no *non-default? *
>
>
I’m not following the point you are trying to make.  table111.a contributes
the default collation for any expression needing a collation implicitly
resolved.

David J.