Re: message log merge (streaming replication)

[Ian Lawrence Barwick]

2022年6月9日(木) 14:32 Peter Adlersburg :
>
> Dear fellow DBA's,
>
>
> While troubleshooting one of our production replication clusters (phys. 
> streaming replication using the patroni framework)
> I stumbled over a - at least for me - strange phenomenon in the postgres logs 
> of the two cluster members:
>
> *** node-01 ***
>
> [postgres@db-node-01 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> down at 2022-06-05 18:25:23 CEST
> time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready to 
> accept connections
> time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was interrupted 
> while in recovery at log time 2022-06-05 18:29:11 CEST
> time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> accept connections
>
> *** node-02 ***
>
> [postgres@db-node-02 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> down at 2022-06-05 18:25:23 CEST
> time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready to 
> accept connections
> time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was interrupted 
> while in recovery at log time 2022-06-05 18:29:11 CEST
> time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> accept read only connections
> time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> accept connections
>
> The output is by no means complete - I only kept the duplicate entries.
>
> My question:
>
> How is it possible that the error logs are 'merged' across the two database 
> nodes?

That's impossible to determine on the basis of the available information.

How is logging set up? What kind of environments are the nodes running in?
Is it possible they are able to write to a shared disk of some sort?

> Are the message/error-logs also replicated?

No.

> Is this the intended behaviour?

It's not typical behaviour, but I suppose it's conceivable someone designed
such a setup for some reason.

Regards

Ian Barwick

Re: message log merge (streaming replication)

[Kyotaro Horiguchi]

At Thu, 9 Jun 2022 16:26:24 +0900, Ian Lawrence Barwick  
wrote in 
> 2022年6月9日(木) 14:32 Peter Adlersburg :
> >
> > Dear fellow DBA's,
> >
> >
> > While troubleshooting one of our production replication clusters (phys. 
> > streaming replication using the patroni framework)
> > I stumbled over a - at least for me - strange phenomenon in the postgres 
> > logs of the two cluster members:
> >
> > *** node-01 ***
> >
> > [postgres@db-node-01 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> > time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> > time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> > down at 2022-06-05 18:25:23 CEST
> > time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready 
> > to accept read only connections
> > time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready 
> > to accept connections
> > time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was 
> > interrupted while in recovery at log time 2022-06-05 18:29:11 CEST
> > time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> > accept read only connections
> > time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> > accept connections
> >
> > *** node-02 ***
> >
> > [postgres@db-node-02 main]$ grep 'LOG:  database' postgresql-2022-06-05.log
> > time=2022-06-05 18:25:26 CEST, pid=1720 LOG:  database system is shut down
> > time=2022-06-05 18:25:29 CEST, pid=3252374 LOG:  database system was shut 
> > down at 2022-06-05 18:25:23 CEST
> > time=2022-06-05 18:25:31 CEST, pid=3252371 LOG:  database system is ready 
> > to accept read only connections
> > time=2022-06-05 18:29:11 CEST, pid=3252371 LOG:  database system is ready 
> > to accept connections
> > time=2022-06-05 18:32:01 CEST, pid=1816 LOG:  database system was 
> > interrupted while in recovery at log time 2022-06-05 18:29:11 CEST
> > time=2022-06-05 18:32:03 CEST, pid=1813 LOG:  database system is ready to 
> > accept read only connections
> > time=2022-06-05 19:00:26 CEST, pid=1813 LOG:  database system is ready to 
> > accept connections
> >
> > The output is by no means complete - I only kept the duplicate entries.
> >
> > My question:
> >
> > How is it possible that the error logs are 'merged' across the two database 
> > nodes?
> 
> That's impossible to determine on the basis of the available information.
> 
> How is logging set up? What kind of environments are the nodes running in?
> Is it possible they are able to write to a shared disk of some sort?
> 
> > Are the message/error-logs also replicated?
> 
> No.
> 
> > Is this the intended behaviour?
> 
> It's not typical behaviour, but I suppose it's conceivable someone designed
> such a setup for some reason.

If the node-02 is a replica of the node-01 or vise-versa, and the log
file is in $PGDATA, it's possible that pg_basebackup (or just cp)
copies in the server log file to the replica.  In that case, the first
half of the log file of the day of taking the backup is taken over
from the primary and the last half differs.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Cluster OID Limit

[Lucas]

Hello,

In the company I work for, some clusters reached the OID limit (2^32) and
we had to reinstall the cluster.

I was wondering if there is any discussion on:
* "compress" the OID space
* "warp around" the OID space
* segment a OID range for temporary tables with "wrap around"

--
Lucas

A function to find errors in groups in a table

[Shaozhong SHI]

There is a table full of grouped values like the following

nodeid link_type  primary
11   outflowlink   1
11  inflowlink  1
11  outflowlink 2

Primary of 1 indicates a primary water course.  Primary of 2 indicates a
secondary water course.

Obviously, one of the out flow links is an error, as its primacy value is
2.  It is wrong that water flows from a primary water course into a
secondary water course.

How can a function can be designed to find and report such errors?

Regards,

David

Re: Cluster OID Limit

[Tom Lane]

Lucas  writes:
> In the company I work for, some clusters reached the OID limit (2^32) and
> we had to reinstall the cluster.

Uh ... why did you think you needed to do that?  The OID counter
will wrap around and things should carry on fine.  There are defenses
to prevent creation of duplicate OID values within any one catalog
or TOAST table, and it doesn't particularly matter if there are
duplicates across tables.

regards, tom lane

Re: Cluster OID Limit

[Adrian Klaver]

On 6/9/22 02:10, Lucas wrote:

Hello,

In the company I work for, some clusters reached the OID limit (2^32) 
and we had to reinstall the cluster.


Was this really about OIDs or XID wraparound?:

https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND



I was wondering if there is any discussion on:
* "compress" the OID space
* "warp around" the OID space
* segment a OID range for temporary tables with "wrap around"

--
Lucas



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

Re: Cluster OID Limit

[SERHAD ERDEM]

Hi ,
its  about  xid.
u may use the following sqls  for check.


---Transaction ID Exhaustion Analysis  --

SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database where datallowconn = true
ORDER BY 2 DESC;



WITH max_age AS (
SELECT 20 as max_old_xid
, setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
SELECT datname
, m.max_old_xid::int
, m.autovacuum_freeze_max_age::int
, age(d.datfrozenxid) AS oldest_current_xid
FROM pg_catalog.pg_database d
JOIN max_age m ON (true)
WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
, max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS 
percent_towards_wraparound
, max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS 
percent_towards_emergency_autovac
FROM per_database_stats;





SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 100;

From: Adrian Klaver 
Sent: Thursday, June 9, 2022 3:02 PM
To: Lucas ; pgsql-general@lists.postgresql.org 

Subject: Re: Cluster OID Limit

On 6/9/22 02:10, Lucas wrote:
> Hello,
>
> In the company I work for, some clusters reached the OID limit (2^32)
> and we had to reinstall the cluster.

Was this really about OIDs or XID wraparound?:

https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

>
> I was wondering if there is any discussion on:
> * "compress" the OID space
> * "warp around" the OID space
> * segment a OID range for temporary tables with "wrap around"
>
> --
> Lucas


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

Re: Cluster OID Limit

[Joshua Drake]

Lucas,

If you run out of OIDs you are doing something wrong. We haven't supported
user space OIDs in a lot of releases. Which release are you using?

JD

On Thu, Jun 9, 2022 at 2:11 AM Lucas  wrote:

> Hello,
>
> In the company I work for, some clusters reached the OID limit (2^32) and
> we had to reinstall the cluster.
>
> I was wondering if there is any discussion on:
> * "compress" the OID space
> * "warp around" the OID space
> * segment a OID range for temporary tables with "wrap around"
>
> --
> Lucas
>

Re: Sharing DSA pointer between parallel workers after they've been created

[Joshua Drake]

Marcus,

This is probably better suited for -hackers.

JD

On Wed, Jun 8, 2022 at 8:00 PM Ma, Marcus  wrote:

> Hey,
>
>
>
> I’m currently working on a parallelization optimization of the Sequential
> Scan in the codebase, and I need to share information between the workers
> as they scan a relation. I’ve done a decent amount of testing, and I know
> that the parallel workers all share the same dsa_area in the plan state.
> However, by the time I’m actually able to allocate a dsa_pointer via
> dsa_allocate0(), the separate parallel workers have already been created so
> I can’t actually share the pointer with them. Since the workers all share
> the same dsa_area, all I need to do is be able to share the single
> dsa_pointer with them but so far I’ve been out of luck. Any advice?
>
>
>
> Marcus
>

Re: Logical replication of large objects

[Joshua Drake]

Large objects are largely considered a deprecated feature.

Though I like the idea, was there any consensus on -hackers?

JD

On Sun, Jun 5, 2022 at 2:23 AM Andreas Joseph Krogh 
wrote:

> I started this thread 5 years ago:
> https://www.postgresql.org/message-id/flat/7c70d9bd-76fc-70fa-cfec-14f00a4a49c3%40matrix.gatewaynet.com#15cbf1c82be9341e551e60e287264380
>
>
>
> We'd be willing to help funding development needed to support Large Object
> logical replication.
>
> Anyone interested?
>
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andr...@visena.com
> www.visena.com
> 
>

Re: Logical replication of large objects

[Andreas Joseph Krogh]

På torsdag 09. juni 2022 kl. 20:24:56, skrev Joshua Drake 
mailto:j...@commandprompt.com>>:

Large objects are largely considered a deprecated feature.


Though I like the idea, was there any consensus on -hackers?
Nobody seems interested in it…






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: cast to domain with default collation issue.

[David G. Johnston]

On Tue, May 24, 2022 at 7:42 AM Tom Lane  wrote:

> I wrote:
> > Perhaps this should be documented more clearly, but it's not obviously
> > wrong.  If the domain declaration doesn't include an explicit COLLATE
> > then casting to the domain doesn't create an explicit collation
> > requirement.  (That is, the domain *doesn't* have a specific
> > collation attached to it, any more than type text does.)
>
> Perhaps we could improve matters like this?
>
> diff --git a/doc/src/sgml/ref/create_domain.sgml
> b/doc/src/sgml/ref/create_domain.sgml
> index 81a8924926..e4b856d630 100644
> --- a/doc/src/sgml/ref/create_domain.sgml
> +++ b/doc/src/sgml/ref/create_domain.sgml
> @@ -94,7 +94,8 @@ CREATE DOMAIN  class="parameter">name [ AS ] 
> 
>  An optional collation for the domain.  If no collation is
> -specified, the underlying data type's default collation is used.
> +specified, the domain has the same collation behavior as its
> +underlying data type.
>  The underlying type must be collatable if
> COLLATE
>  is specified.
> 
>
>
+1

The lack of any explicitness pushes evaluation down to the base type -
which is a behavioral thing as opposed to some kind of attribute it
possesses.

David J.

Re: Row level security insert policy does not validate update new values/content?

[David G. Johnston]

On Tue, May 17, 2022 at 4:57 AM alias  wrote:

>
> My thought process:
>
>> update = delete  + insert.
>> so * create policy emp_upd on emp for update to public using (true); *should
>> be ok for updating every row, let insert policy handle new row
>> *. *
>> since there is only one check_expression, also no need to worry about
>> permissive/restrictive.
>>
>
> but it seems, I need to create the same policy as insert operation to
> update operation to validate the new content/row of update operation.
>

update = delete + insert is not universally true.  Its main point is that
due to MVCC when you update something the old tuple is deleted (but remains
around waiting to be vacuumed) and a new tuple is created.  It is also an
implementation detail - while the usage of INSERT/UPDATE/DELETE in CREATE
POLICY are semantically significant and mean to convey the user writing out
those specific commands (or subcommand in the case of the ON CONFLICT
UPDATE subclause of INSERT).

In this case it gives the policy writer flexibility, at the cost of some
duplication.  One useful thing to do is write a function that accepts
either columns, or the table's data type, as an input argument and put the
logic in there.  Then just call the function in the policy with check
and/or using clauses.

David J.