Re: synchronized standby: committed local and waiting for remote ack
How to understand "because you could easily get into a situation where *none* of the nodes represent truth."? In current design, when a user commits, it will first commit on primary, and then is waiting for slave ack. if slaves and primary are splitted in the network, then the user commit command will hang forever, and usually the user side has timeout setting, when it timeouts, users will THINK the commit fails, but actually it commits on primary, so the primary doesn't reflect the truth. If user commit first waits for slave ack, and then do local commit, if network partitioning happens, it commits on neither primary or slave which is good, and if network partitioning doesn't happen, it will more likely to commit on both primary and locally and local commit is less likely to error out. On Sat, Jan 14, 2023 at 1:31 PM Tom Lane wrote: > qihua wu writes: > > We are using patroni to set up 1 primary and 5 slaves, and using ANY 2 > (*) > > to commit a transaction if any 2 standbys receive the WAL. If there is a > > network partitioning between the primary and the slave, then commit will > > hang from user perspective, but the commit is actually done locally, just > > waiting for remote ack which is not possible because of network split. > And > > if patroni promotes a slave to primary, then we will lost data. Do you > > think of a different design: first wait for remote ACK, and then commit > > locally, this will only failed if local commit failed, but local commit > > fail is much rarer than network partitioning in a cloud env: it will only > > fail when IO issue or disk is full. So I am thinking of the possibility > of > > switch the order: first wait for remote ACK, and then commit locally. > > That just gives you a different set of failure modes. It'd be > particularly bad if you have more than one standby, because you could > easily get into a situation where *none* of the nodes represent truth. > > regards, tom lane >
Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
On 1/16/23 15:46, Rob Sargent wrote:
On 1/16/23 14:18, Ron wrote:
On 1/16/23 07:11, Laurenz Albe wrote:
On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:
This is a puzzle I have not been able to crack yet.
We have a single-page table with 28 rows that is purely read-only.
There isn't a way in postgres to make a table RO, but I say this with
confidence because pg_stat_user_tables has always showed 0
updates/deletes/inserts.
Furthermore, the schema app developers know, for certain, this table
does not get changed at all.
We installed scripts that run every few minutes that do a 'select *'
and over a period of days, we have not seen a change.
We disabled autovacuum on this table '{autovacuum_enabled=false}'. But,
despite the fact that this table is read-only (by design) and autovac
id is disabled, it got autovac'd twice in less than 10
days and on both occasions, pg_stat_activity showed the worker with 'to
prevent wraparound'. This explains why autovac did not honor the
disabled status.
But why is this table autovac'd at all?
For every table PostgreSQL stores the oldest transaction ID in an
unfrozen tuple
in "pg_class.relfrozenxid". Once that is more than
"autovacuum_freeze_max_age",
the table gets autovacuumed. If the table is already all-frozen, that
is a short
operation and will just advance "pg_class.relfrozenxid".
So OP should VACUUM FREEZE the table.
Hm, did OP say there was an actual problem as is? Or just a "puzzle" -
now explained - and no action is necessary?
"Should" as in "it's a good idea", not "it's important but not vital".
--
Born in Arizona, moved to Babylonia.
Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
On 1/16/23 14:18, Ron wrote:
On 1/16/23 07:11, Laurenz Albe wrote:
On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:
This is a puzzle I have not been able to crack yet.
We have a single-page table with 28 rows that is purely read-only.
There isn't a way in postgres to make a table RO, but I say this
with confidence because pg_stat_user_tables has always showed 0
updates/deletes/inserts.
Furthermore, the schema app developers know, for certain, this table
does not get changed at all.
We installed scripts that run every few minutes that do a 'select *'
and over a period of days, we have not seen a change.
We disabled autovacuum on this table '{autovacuum_enabled=false}'.
But, despite the fact that this table is read-only (by design) and
autovac id is disabled, it got autovac'd twice in less than 10
days and on both occasions, pg_stat_activity showed the worker with
'to prevent wraparound'. This explains why autovac did not honor the
disabled status.
But why is this table autovac'd at all?
For every table PostgreSQL stores the oldest transaction ID in an
unfrozen tuple
in "pg_class.relfrozenxid". Once that is more than
"autovacuum_freeze_max_age",
the table gets autovacuumed. If the table is already all-frozen,
that is a short
operation and will just advance "pg_class.relfrozenxid".
So OP should VACUUM FREEZE the table.
Hm, did OP say there was an actual problem as is? Or just a "puzzle" -
now explained - and no action is necessary?
Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
On 1/16/23 07:11, Laurenz Albe wrote:
On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:
This is a puzzle I have not been able to crack yet.
We have a single-page table with 28 rows that is purely read-only. There isn't
a way in postgres to make a table RO, but I say this with confidence because
pg_stat_user_tables has always showed 0
updates/deletes/inserts.
Furthermore, the schema app developers know, for certain, this table does not
get changed at all.
We installed scripts that run every few minutes that do a 'select *' and over a
period of days, we have not seen a change.
We disabled autovacuum on this table '{autovacuum_enabled=false}'. But, despite
the fact that this table is read-only (by design) and autovac id is disabled,
it got autovac'd twice in less than 10
days and on both occasions, pg_stat_activity showed the worker with 'to prevent
wraparound'. This explains why autovac did not honor the disabled status.
But why is this table autovac'd at all?
For every table PostgreSQL stores the oldest transaction ID in an unfrozen tuple
in "pg_class.relfrozenxid". Once that is more than "autovacuum_freeze_max_age",
the table gets autovacuumed. If the table is already all-frozen, that is a
short
operation and will just advance "pg_class.relfrozenxid".
So OP should VACUUM FREEZE the table.
--
Born in Arizona, moved to Babylonia.
Re: Why is a hash join preferred when it does not fit in work_mem
On Sat, 14 Jan 2023, Tom Lane wrote: Dimitrios Apostolou writes: Please correct me if I'm wrong, as I'm a newcomer to PostgreSQL, but here is how I understand things according to posts I've read, and classical algorithms: + The Hash Join is fastest when one side fits in work_mem. Then on one hand you have a hash table lookup (amortized O(1)) and on the other hand, if the table has M rows that that do not fit in memory, you have sequential reads from the disk (given low fragmentation of the table or index files): For every line you read from the disk, you lookup the key in the hash table. If the hash table does not fit in RAM then the cost becomes prohibitive. Every lookup is a random access possibly hitting the disk. The total cost should be random_page_cost * M. That would be true of a simple hash join, but Postgres uses batched hash joins: we split up the hash key space into subsets, where hopefully each subset includes few enough inner-side rows to fit into work_mem. While this can go wrong given pathological distribution of the inner-side keys, it does mean that the join can perform well even when the inner side is much larger than work_mem. So it's not the case that the planner will simply disregard hash joins beyond work_mem. It will apply a cost penalty for the predicted batching overhead; Thanks for this, I found a page [1] that describes the hash join and now I understand a bit more. [1] https://www.interdb.jp/pg/pgsql03.html I'm not sure whether the key distribution is pathological in my case. The join condition is: Hash Cond: (tasks_mm_workitems.workitem_n = workitem_ids.workitem_n) and workitem_ids.workitem_n is an integer GENERATED AS IDENTITY and PUBLIC KEY. The TABLE workitem_ids har 1.7M rows, and the other table has 3.7M rows. None of them fit in workmem. In my (simplified and pathological) case of work_mem == 1MB, the hash join does 512 batches (Buckets: 4,096 Batches: 512 Memory Usage: 759kB). I'm not sure which hash-merge strategy is followed, but based on that document, it should be the "hybrid hash join with skew". I don't quite follow the I/O requirements of this algorithm, yet. :-) but that can still come out cheaper than merge join, because the sorting needed for merge is generally also far from cheap. I was under the impression that on-disk merge-sort is a relatively cheap (logN) operation, regarding random I/O. So I would expect an increased random_page_cost to benefit the Merge Join algorithm. And since my setup involves spinning disks, it does makes sense to increase it. What is probably really happening is that random_page_cost affects the estimated cost of performing the sort using an index scan instead of a bespoke sort step. AFAIR, cost_sort doesn't consider random_page_cost at all, and neither does cost_hashjoin. On the last EXPLAIN I posted for the forced merge-join, I see that it uses an index-scan on the "small" table. It makes sense since the join happens on the primary key of the table. On the large table it does not use an index scan, because an index doesn't exist for that column. It sorts the 3.7M rows of the table (and FWIW that table only has two integer columns). If I understood correctly what you meant with "performing the sort using an index scan". The problem I see is that the estimated cost of the sort operation is 609,372.91..618,630.40. It's already way above the whole hash-join cost (121,222.68..257,633.01). However the real timings are very different. Actual time for Sort is 4,602.569..5,414.072 ms while for the whole hash join it is 145,641.295..349,682.387 ms. Am I missing some configuration knobs to put some sense to the planner? Thanks, Dimitris
minor bug
Hi, not sure if this is known behavior. Server version is 14.6 (Debian 14.6-1.pgdg110+1). In a PITR setup I have these settings: recovery_target_xid = '852381' recovery_target_inclusive = 'false' In the log file I see this message: LOG: recovery stopping before commit of transaction 852381, time 2000-01-01 00:00:00+00 But: postgres=# select * from pg_last_committed_xact(); xid | timestamp | roident +---+- 852380 | 2023-01-16 18:00:35.054495+00 | 0 So, the timestamp displayed in the log message is certainly wrong. Thanks, Torsten
Re: No function matches the given name and argument types.
> On Jan 16, 2023, at 09:53, David G. Johnston > wrote: > > I don't see any good way to say: "given this function signature, and the fact > it cannot be found, what are the next closest function signatures that are > present". I can see a use-case for such functionality, though: A "did you mean?" error message.
Re: No function matches the given name and argument types.
On Mon, Jan 16, 2023 at 10:42 AM arons wrote: > Why the error happen is clear to me, in the example is also easy to see > that the 7th parameter is the problem. > But I'm searching a more general way to find easily which of the parameter > is the problem. > Suppose you have a function with 30 parameters with mixed sort of types. > They only way I know right now is to compare the position, name and type > one parameter after the other until I found the one that do not match. > A sort of brute force. > Is there any better way to do that? > >> >> To what end? In most cases you already know precisely which function you are trying to execute. Comparing that single function against your call site and figuring out what is wrong is fairly simple debugging work. I don't see any good way to say: "given this function signature, and the fact it cannot be found, what are the next closest function signatures that are present". David J.
Re: No function matches the given name and argument types.
Hi po 16. 1. 2023 v 18:42 odesílatel arons napsal: > Why the error happen is clear to me, in the example is also easy to see > that the 7th parameter is the problem. > But I'm searching a more general way to find easily which of the parameter > is the problem. > Suppose you have a function with 30 parameters with mixed sort of types. > They only way I know right now is to compare the position, name and type > one parameter after the other until I found the one that do not match. > A sort of brute force. > Is there any better way to do that? > Unfortunately, it isn't or I don't know it Regards Pavel > > Thanks > > > On Mon, Jan 16, 2023 at 5:21 PM Adrian Klaver > wrote: > >> On 1/16/23 08:17, Adrian Klaver wrote: >> > On 1/16/23 08:04, arons wrote: >> >> Dear All, >> >> I'm facing a general problem and I'm looking the best, fastest, way >> >> how to identify the problem and solve it. >> >> >> >> As example assume we have a function like that: >> >> >> >> CREATE OR REPLACE FUNCTION testBinding01 ( >> >> >> >> p_in01 bigint, >> >> >> >> p_in02 bigint, >> >> >> >> p_in03 bigint, >> >> >> >> p_in04 bigint, >> >> >> >> p_in05 bigint, >> >> >> >> p_in06 bigint, >> >> >> >> p_text7 text >> >> >> >> ) RETURNS text >> >> >> >> LANGUAGE sql >> >> >> >> AS $$ >> >> >> >> select 'ciao'; >> >> >> >> $$; >> >> >> >> >> >> >> >> I can call the function in some of the variant below: >> >> >> >> select testBinding01(1,2,3,4,5,6,7); >> >> >> >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => >> >> 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); >> >> >> >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => >> >> 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); >> >> >> >> >> >> All of the above, produce the error: >> >> >> >> *No function matches the given name and argument types.* >> > >> > In psql what does: >> > >> > \df test* >> > >> > return for the function name. >> > >> > I'm going to guess it might be testBinding01, in other words mixed case. >> > >> > Have you tried?: >> > >> > select "testBinding01"(1,2,3,4,5,6,7); >> >> Forget the above. Instead: >> >> select testBinding01(1,2,3,4,5,6,7); >> ERROR: function testbinding01(integer, integer, integer, integer, >> integer, integer, integer) does not exist >> LINE 1: select testBinding01(1,2,3,4,5,6,7); >> >> >> select testBinding01(1,2,3,4,5,6,'7'); >> >> testbinding01 >> --- >> ciao >> >> The complete error shows what the function is receiving, all integers >> when it needs a text parameter for the last value. >> >> >> * >> >> * >> >> * >> >> * >> >> * >> >> * >> >> My question is: how is the best way to identify the problem? >> >> >> >> Is a parameter name? is a parameter type? is the function name? >> >> >> >> An especially in case is a parameter type how is the easy way to >> >> identify which parameter is causing the problem? >> >> >> >> In case a function has a lot of parameters (and in even worst case has >> >> some overloading) going trough all parameters to check its type/name >> >> costs a lot of time. >> >> >> >> >> >> Thanks for any help >> >> >> >> Renzo >> >> >> >> >> >> >> > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >>
Re: No function matches the given name and argument types.
Why the error happen is clear to me, in the example is also easy to see that the 7th parameter is the problem. But I'm searching a more general way to find easily which of the parameter is the problem. Suppose you have a function with 30 parameters with mixed sort of types. They only way I know right now is to compare the position, name and type one parameter after the other until I found the one that do not match. A sort of brute force. Is there any better way to do that? Thanks On Mon, Jan 16, 2023 at 5:21 PM Adrian Klaver wrote: > On 1/16/23 08:17, Adrian Klaver wrote: > > On 1/16/23 08:04, arons wrote: > >> Dear All, > >> I'm facing a general problem and I'm looking the best, fastest, way > >> how to identify the problem and solve it. > >> > >> As example assume we have a function like that: > >> > >> CREATE OR REPLACE FUNCTION testBinding01 ( > >> > >> p_in01 bigint, > >> > >> p_in02 bigint, > >> > >> p_in03 bigint, > >> > >> p_in04 bigint, > >> > >> p_in05 bigint, > >> > >> p_in06 bigint, > >> > >> p_text7 text > >> > >> ) RETURNS text > >> > >> LANGUAGE sql > >> > >> AS $$ > >> > >> select 'ciao'; > >> > >> $$; > >> > >> > >> > >> I can call the function in some of the variant below: > >> > >> select testBinding01(1,2,3,4,5,6,7); > >> > >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => > >> 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); > >> > >> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => > >> 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); > >> > >> > >> All of the above, produce the error: > >> > >> *No function matches the given name and argument types.* > > > > In psql what does: > > > > \df test* > > > > return for the function name. > > > > I'm going to guess it might be testBinding01, in other words mixed case. > > > > Have you tried?: > > > > select "testBinding01"(1,2,3,4,5,6,7); > > Forget the above. Instead: > > select testBinding01(1,2,3,4,5,6,7); > ERROR: function testbinding01(integer, integer, integer, integer, > integer, integer, integer) does not exist > LINE 1: select testBinding01(1,2,3,4,5,6,7); > > > select testBinding01(1,2,3,4,5,6,'7'); > > testbinding01 > --- > ciao > > The complete error shows what the function is receiving, all integers > when it needs a text parameter for the last value. > > >> * > >> * > >> * > >> * > >> * > >> * > >> My question is: how is the best way to identify the problem? > >> > >> Is a parameter name? is a parameter type? is the function name? > >> > >> An especially in case is a parameter type how is the easy way to > >> identify which parameter is causing the problem? > >> > >> In case a function has a lot of parameters (and in even worst case has > >> some overloading) going trough all parameters to check its type/name > >> costs a lot of time. > >> > >> > >> Thanks for any help > >> > >> Renzo > >> > >> > >> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: No function matches the given name and argument types.
On 1/16/23 08:17, Adrian Klaver wrote: On 1/16/23 08:04, arons wrote: Dear All, I'm facing a general problem and I'm looking the best, fastest, way how to identify the problem and solve it. As example assume we have a function like that: CREATE OR REPLACE FUNCTION testBinding01 ( p_in01 bigint, p_in02 bigint, p_in03 bigint, p_in04 bigint, p_in05 bigint, p_in06 bigint, p_text7 text ) RETURNS text LANGUAGE sql AS $$ select 'ciao'; $$; I can call the function in some of the variant below: select testBinding01(1,2,3,4,5,6,7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); All of the above, produce the error: *No function matches the given name and argument types.* In psql what does: \df test* return for the function name. I'm going to guess it might be testBinding01, in other words mixed case. Have you tried?: select "testBinding01"(1,2,3,4,5,6,7); Forget the above. Instead: select testBinding01(1,2,3,4,5,6,7); ERROR: function testbinding01(integer, integer, integer, integer, integer, integer, integer) does not exist LINE 1: select testBinding01(1,2,3,4,5,6,7); select testBinding01(1,2,3,4,5,6,'7'); testbinding01 --- ciao The complete error shows what the function is receiving, all integers when it needs a text parameter for the last value. * * * * * * My question is: how is the best way to identify the problem? Is a parameter name? is a parameter type? is the function name? An especially in case is a parameter type how is the easy way to identify which parameter is causing the problem? In case a function has a lot of parameters (and in even worst case has some overloading) going trough all parameters to check its type/name costs a lot of time. Thanks for any help Renzo -- Adrian Klaver adrian.kla...@aklaver.com
Re: No function matches the given name and argument types.
On 1/16/23 08:04, arons wrote: Dear All, I'm facing a general problem and I'm looking the best, fastest, way how to identify the problem and solve it. As example assume we have a function like that: CREATE OR REPLACE FUNCTION testBinding01 ( p_in01 bigint, p_in02 bigint, p_in03 bigint, p_in04 bigint, p_in05 bigint, p_in06 bigint, p_text7 text ) RETURNS text LANGUAGE sql AS $$ select 'ciao'; $$; I can call the function in some of the variant below: select testBinding01(1,2,3,4,5,6,7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); All of the above, produce the error: *No function matches the given name and argument types.* In psql what does: \df test* return for the function name. I'm going to guess it might be testBinding01, in other words mixed case. Have you tried?: select "testBinding01"(1,2,3,4,5,6,7); * * * * * * My question is: how is the best way to identify the problem? Is a parameter name? is a parameter type? is the function name? An especially in case is a parameter type how is the easy way to identify which parameter is causing the problem? In case a function has a lot of parameters (and in even worst case has some overloading) going trough all parameters to check its type/name costs a lot of time. Thanks for any help Renzo -- Adrian Klaver adrian.kla...@aklaver.com
No function matches the given name and argument types.
Dear All, I'm facing a general problem and I'm looking the best, fastest, way how to identify the problem and solve it. As example assume we have a function like that: CREATE OR REPLACE FUNCTION testBinding01 ( p_in01 bigint, p_in02 bigint, p_in03 bigint, p_in04 bigint, p_in05 bigint, p_in06 bigint, p_text7 text ) RETURNS text LANGUAGE sql AS $$ select 'ciao'; $$; I can call the function in some of the variant below: select testBinding01(1,2,3,4,5,6,7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text7 => 7); select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 => 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt'); All of the above, produce the error: *No function matches the given name and argument types.* My question is: how is the best way to identify the problem? Is a parameter name? is a parameter type? is the function name? An especially in case is a parameter type how is the easy way to identify which parameter is causing the problem? In case a function has a lot of parameters (and in even worst case has some overloading) going trough all parameters to check its type/name costs a lot of time. Thanks for any help Renzo
Re: glibc initdb options vs icu compatibility questions (PG15)
On 1/16/23 08:26, Laurenz Albe wrote: On Mon, 2023-01-16 at 09:30 +0100, Robert Sjöblom wrote: We have a fleet of postgres 10 servers (1 primary, 2 replicas) that we're now planning to upgrade. We've historically been forced to use the same distro (centos7) and libc version, or rely on pg_dump/restore, across pg versions due to the fact that the servers/databases were initialized with the following options: --lc-collate=sv_SE.UTF-8 We're upgrading all servers to pg15 through logical replication, and with that we'd like to remove our dependency on any specific libc version (to avoid corruption issues etc). We hope to do this by initializing our pg15 clusters with swedish icu locale (--icu-locale=sv-SE-x-icu). By using icu like this, we should have the same sorting behavior as the pg10 servers have today? By our understanding, we then should be able to use physical replication across different OS versions, without worrying about index corruption etc? According to http://peter.eisentraut.org/blog/2022/09/26/icu-features-in-postgresql-15, we still need to set a regular libc locale/option; does this mean that the dependency on libc is still present as it pertains to corruption issues and being forced to use the same libc version across all replicas? I'd say no. The problem is the collation, and for that, the ICU collation will be used. check The libc locale is for other aspects of locale. Although I am not aware of any specific/known issues, keep in mind that there is still a possibility of changes across major glibc versions for things affected by LC_CTYPE, LC_TIME, LC_MONETARY, and LC_NUMERIC - lower()/upper() for example. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: row estimate for partial index
Harmen writes: > On Sat, Jan 14, 2023 at 11:23:07AM -0500, Tom Lane wrote: >> If you are running a reasonably recent PG version you should be able to >> fix that by setting up "extended statistics" on that pair of columns: > CREATE STATISTICS dist4 (ndistinct) ON deleted, org_id FROM contactsbool; > CREATE STATISTICS dist4b (ndistinct) ON org_id, deleted FROM contactsbool; 1. ndistinct is not the correct stats type for this problem. (I think dependencies is, but generally speaking, it's not worth trying to be smarter than the system about which ones you need. Just create 'em all.) 2. Per the CREATE STATISTICS man page, the order of the columns is not significant, so you're just doubling the amount of work for ANALYZE without gaining anything. I think you will find that CREATE STATISTICS stats1 ON deleted, org_id FROM contactsbool; is enough to fix this. It improved the estimate for me in v14 and HEAD, anyway. regards, tom lane
RE: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
> The database relies on the data being consistent when it performs crash > recovery. > Imagine that a checkpoint is running while you take your snapshot. The > checkpoint > syncs a data file with a new row to disk. Then it writes a WAL record and > updates > the control file. Now imagine that the table with the new row is on a > different > file system, and your snapshot captures the WAL and the control file, but not > the new row (it was still sitting in the kernel page cache when the snapshot > was taken). > You end up with a lost row. > > That is only one scenario. Many other ways of corruption can happen. Can we say then that the risk comes only from the possibility of a checkpoint running inside the time gap between the non-simultaneous snapshots?
Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
> On 16/01/2023 13:48 CET Fred Habash wrote:
>
> This is a puzzle I have not been able to crack yet.
>
> We have a single-page table with 28 rows that is purely read-only. There isn't
> a way in postgres to make a table RO, but I say this with confidence because
> pg_stat_user_tables has always showed 0 updates/deletes/inserts.
>
> Furthermore, the schema app developers know, for certain, this table does not
> get changed at all.
Only way to ensure that is to have database users other than the table owners
or superusers connect from your app. Then you can GRANT the absolute necessary
privileges like SELECT for read-only access.
> We installed scripts that run every few minutes that do a 'select *' and over
> a period of days, we have not seen a change.
>
> We disabled autovacuum on this table '{autovacuum_enabled=false}'. But,
> despite the fact that this table is read-only (by design) and autovac id is
> disabled, it got autovac'd twice in less than 10 days and on both occasions,
> pg_stat_activity showed the worker with 'to prevent wraparound'. This explains
> why autovac did not honor the disabled status.
>
> But why is this table autovac'd at all?
Wraparound protection is always performed even if autovacuum is disabled:
https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> I have a hypothesis, but I need it validated and may be indicate if it is
> scientifically plausible. It goes like this ...
>
> 1. Application initiates a T1 transaction
> 2. App. reads multiple tables to get product metadata and this small table is
>one of them.
> 3. At some point, app. locks a row on one of the tables (not the small one).
> 4. Client app. keeps session 'idle in transaction' while it refreshes a
>webpage to render the data.
> 4. Once the client app verifies the web app has rendered the data correctly,
>it comes back to the database to finish the transaction.
>
> So, even if the small table is never changed, it is part of a transaction to
> be queried. Will this use-case cause the table to qualify for an aggressive
> autovac to prevent wraparound.
>
> If not, why else is a table with zero DML changes ever gets autovac'd?
--
Erik
Re: AW: AW: [Extern] Re: postgres restore & needed history files
On Fri, 2023-01-13 at 11:23 +, Zwettler Markus (OIZ) wrote: > > -Ursprüngliche Nachricht- > > Von: Laurenz Albe > > Gesendet: Freitag, 13. Januar 2023 11:25 > > An: Zwettler Markus (OIZ) ; pgsql- > > gene...@lists.postgresql.org > > Betreff: Re: AW: [Extern] Re: postgres restore & needed history files > > > > On Thu, 2023-01-12 at 16:50 +, Zwettler Markus (OIZ) wrote: > > > What would you do in case of a disaster when all history files in > > > pg_wal are gone and also deleted in the backup due to the backup > > > retention? > > > > Easy. Take a new base backup immediately and don't try to restore a backup > > that > > was taken on an earlier timeline. > > Hope I got you right. > > You meant the latest backup within the latest timeline is always working even > if the latest history files are gone. Yes. Yours, Laurenz Albe
Re: glibc initdb options vs icu compatibility questions (PG15)
On Mon, 2023-01-16 at 09:30 +0100, Robert Sjöblom wrote: > We have a fleet of postgres 10 servers (1 primary, 2 replicas) that > we're now planning to upgrade. We've historically been forced to use the > same distro (centos7) and libc version, or rely on pg_dump/restore, > across pg versions due to the fact that the servers/databases were > initialized with the following options: > --lc-collate=sv_SE.UTF-8 > > We're upgrading all servers to pg15 through logical replication, and > with that we'd like to remove our dependency on any specific libc > version (to avoid corruption issues etc). We hope to do this by > initializing our pg15 clusters with swedish icu locale > (--icu-locale=sv-SE-x-icu). By using icu like this, we should have the > same sorting behavior as the pg10 servers have today? By our > understanding, we then should be able to use physical replication across > different OS versions, without worrying about index corruption etc? > > According to > http://peter.eisentraut.org/blog/2022/09/26/icu-features-in-postgresql-15, > we still need to set a regular libc locale/option; does this mean that > the dependency on libc is still present as it pertains to corruption > issues and being forced to use the same libc version across all replicas? I'd say no. The problem is the collation, and for that, the ICU collation will be used. The libc locale is for other aspects of locale. > Our current initdb arguments for the pg15 version looks like this: > --lc-collate=C > --lc-ctype=C > --lc_monetary=C > --lc-numeric=C > --lc-time=C > --encoding=UTF-8 > --locale-provider=icu > --icu-locale=sv-SE-x-icu > --lc-messages=en_US.UTF-8 > > Does this correspond to the same behavior as our previous (pg10) > servers, but using icu instead of glibc? Not the exact same, since the libc collation and the ICU collation will not be exactly identical. But you should not get any complaints from your users, since the collations are presumably very similar. Yours, Laurenz Albe
Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
On Mon, 2023-01-16 at 08:41 +, HECTOR INGERTO wrote: > I have understood I shall not do it, but could the technical details be > discussed about > why silent DB corruption can occur with non-atomical snapshots? The database relies on the data being consistent when it performs crash recovery. Imagine that a checkpoint is running while you take your snapshot. The checkpoint syncs a data file with a new row to disk. Then it writes a WAL record and updates the control file. Now imagine that the table with the new row is on a different file system, and your snapshot captures the WAL and the control file, but not the new row (it was still sitting in the kernel page cache when the snapshot was taken). You end up with a lost row. That is only one scenario. Many other ways of corruption can happen. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:
> This is a puzzle I have not been able to crack yet.
>
> We have a single-page table with 28 rows that is purely read-only. There
> isn't a way in postgres to make a table RO, but I say this with confidence
> because pg_stat_user_tables has always showed 0
> updates/deletes/inserts.
>
> Furthermore, the schema app developers know, for certain, this table does not
> get changed at all.
>
> We installed scripts that run every few minutes that do a 'select *' and over
> a period of days, we have not seen a change.
>
> We disabled autovacuum on this table '{autovacuum_enabled=false}'. But,
> despite the fact that this table is read-only (by design) and autovac id is
> disabled, it got autovac'd twice in less than 10
> days and on both occasions, pg_stat_activity showed the worker with 'to
> prevent wraparound'. This explains why autovac did not honor the disabled
> status.
>
> But why is this table autovac'd at all?
For every table PostgreSQL stores the oldest transaction ID in an unfrozen tuple
in "pg_class.relfrozenxid". Once that is more than "autovacuum_freeze_max_age",
the table gets autovacuumed. If the table is already all-frozen, that is a
short
operation and will just advance "pg_class.relfrozenxid".
Yours,
Laurenz Albe
Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"
This is a puzzle I have not been able to crack yet.
We have a single-page table with 28 rows that is purely read-only. There
isn't a way in postgres to make a table RO, but I say this with confidence
because pg_stat_user_tables has always showed 0 updates/deletes/inserts.
Furthermore, the schema app developers know, for certain, this table does
not get changed at all.
We installed scripts that run every few minutes that do a 'select *' and
over a period of days, we have not seen a change.
We disabled autovacuum on this table '{autovacuum_enabled=false}'. But,
despite the fact that this table is read-only (by design) and autovac id is
disabled, it got autovac'd twice in less than 10 days and on both
occasions, pg_stat_activity showed the worker with 'to prevent wraparound'.
This explains why autovac did not honor the disabled status.
But why is this table autovac'd at all?
I have a hypothesis, but I need it validated and may be indicate if it is
scientifically plausible. It goes like this ...
1. Application initiates a T1 transaction
2. App. reads multiple tables to get product metadata and this small table
is one of them.
3. At some point, app. locks a row on one of the tables (not the small
one).
4. Client app. keeps session 'idle in transaction' while it refreshes a
webpage to render the data.
4. Once the client app verifies the web app has rendered the data
correctly, it comes back to the database to finish the transaction.
So, even if the small table is never changed, it is part of a transaction
to be queried. Will this use-case cause the table to qualify for an
aggressive autovac to prevent wraparound.
If not, why else is a table with zero DML changes ever gets autovac'd?
Thank you
Re: row estimate for partial index
On Sat, Jan 14, 2023 at 11:23:07AM -0500, Tom Lane wrote: > Harmen writes: > > Works well enough. However, we now have an org_id which has > 10% of the > > rows, > > but only a handful rows where "deleted is null" matches (so the org has a > > lot > > of "deleted" contacts). The planner doesn't like this and it falls back to a > > full table scan for the above query. > > > I've added a dedicated index just for that org_id, to see if that helps: > > "org123" btree (id) WHERE deleted IS NULL AND org_id = 123 > > The planner seems to use it now, however the row estimate is way off: > > Yeah, so that indicates that it isn't producing a good selectivity > estimate for the combination of those two conditions: it will assume > the org_id and deleted columns are independent, which per your statements > they are not. > > If you are running a reasonably recent PG version you should be able to > fix that by setting up "extended statistics" on that pair of columns: > > https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED > > (I might be wrong, but I think that will help even when one of > the troublesome conditions is a null-check. If it doesn't, then > we have something to improve there ...) Thanks for your explanation, Tom. I've setup a local test scenario, where I then add a "dependencies" stat, but that doesn't give a better plan, unfortunately. This is my test table (I use a boolean field for "deleted" to keep this test case as simple as possible. In my real case this is a "timestamptz null" field): DROP table if exists contactsbool; CREATE table contactsbool (id int not null, org_id int not null, deleted boolean not null, firstname text); CREATE index contactsbool_orgs on contactsbool (org_id, id) where not deleted; Testdata has a very low number of "orgs", and one org has almost only deleted contacts: WITH ids as (select * from generate_series(0, 1000)) insert into contactsbool select ids.generate_series, mod(ids.generate_series, 7), false, 'hello world' from ids; UPDATE contactsbool set deleted = true where id > 100 and org_id = 5; ANALYZE contactsbool; Now the new stats: CREATE STATISTICS dist4 (ndistinct) ON deleted, org_id FROM contactsbool; CREATE STATISTICS dist4b (ndistinct) ON org_id, deleted FROM contactsbool; ANALYZE contactsbool; harmen=> explain (analyze) select id, firstname from contactsbool where org_id = 5 and not deleted order by id; QUERY PLAN ── Gather Merge (cost=181983.91..299104.42 rows=1003820 width=16) (actual time=448.244..454.770 rows=14 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=180983.88..182238.66 rows=501910 width=16) (actual time=413.761..413.762 rows=5 loops=3) Sort Key: id Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB -> Parallel Seq Scan on contactsbool (cost=0.00..124881.86 rows=501910 width=16) (actual time=267.318..413.673 rows=5 loops=3) Filter: ((NOT deleted) AND (org_id = 5)) Rows Removed by Filter: 329 Planning Time: 0.565 ms JIT: Functions: 12 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 2.444 ms, Inlining 0.000 ms, Optimization 1.163 ms, Emission 13.288 ms, Total 16.895 ms Execution Time: 456.498 ms (17 rows) The "rows=501910" is what I don't expect. I expect/want/hope the plan to use the contactsbool_orgs index. (If I really (hard) delete the "deleted" contacts everything works perfectly for all orgs.) Any ideas? Thanks again, Harmen
RE: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?
I have understood I shall not do it, but could the technical details be discussed about why silent DB corruption can occur with non-atomical snapshots?
