beta2 run, but it sounds as though maybe it could be relevant to
> this kind of workload - Is that so?
You seem to be worried about keeping indexes as small as possible.
vacuum_cleanup_index_scale_factor won't help with that.
--
Peter Geoghegan
nt release of 9.4 -- not
9.4.6. You're missing years of bug fixes by sticking to such an old
point release, including some rather nasty ones -- 9.4.23 is the
current 9.4 point release. Actually, 9.4 is going to lose support this
year, as the oldest stable version that's currently supported by the
community.
--
Peter Geoghegan
On Mon, Jul 8, 2019 at 12:19 PM Peter Geoghegan wrote:
> Well, you're still running autovacuum very aggressively here. It'll
> easily keep up when run on a relatively small table such as this.
Also, an exactly equal number of insertions and deletions is rather
likely to result in
r a REINDEX, then why bother at all? There is no reason
to think that that will be more effective than simple vacuuming.
--
Peter Geoghegan
;fragmentation" isn't usually much of a problem when using Postgres.
--
Peter Geoghegan
me existing index tuple, causing many
page splits despite there being no real change in the logical contents
of the index. Even then, the table will itself lose much of its
original order, so the index will become "unclustered" as it becomes
fragmented.
--
Peter Geoghegan
ins the heap/table sort order
among duplicates by treating heap TID as a tiebreaker column, which
may make REINDEXing totally unnecessary for you. It's harder to model
this issue because the problem with heap TID order will only be seen
when there is at least a moderate amount of churn.
--
Peter Geoghegan
On Tue, Jul 9, 2019 at 9:04 PM Peter Geoghegan wrote:
> ISTM that the simplest explanation here is that index fragmentation
> (and even index size) is a red herring, and the real issue is that
> you're suffering from problems similar to those that are described in
> these old t
here,
so it seems worth comparing index size in detail.
--
Peter Geoghegan
//www.postgresql.org/docs/current/amcheck.html
--
Peter Geoghegan
a corrupt index) ultimately risks the availability of every
database in the cluster. Many installations receive little to no
supervision, so it may just be a matter of time there. That is
certainly a bad thing.
--
Peter Geoghegan
On Wed, Jul 17, 2019 at 9:21 AM Peter Geoghegan wrote:
> It's possible that amcheck would have given you an accurate diagnosis
> of the problem -- especially if you used bt_index_parent_check():
>
> https://www.postgresql.org/docs/current/amcheck.html
BTW, be sure to use th
ore aggressive than give up
when there is a "failed to re-find parent key" or similar condition.
Perhaps it would make more sense to make the index inactive (for some
value of "inactive") instead of just complaining. That might be the
least worst option, all things considered.
--
Peter Geoghegan
ng can break when somebody
creates an index on a non-immutable expression (including VACUUM),
provided that all the problems only affect the table with the broken
index. OTOH, taking down the entire Postgres cluster as an indirect
consequence of one person's ill-considered CREATE INDEX really sucks.
That distinction seems important to me.
--
Peter Geoghegan
On Wed, Jul 17, 2019 at 10:27 AM Peter Geoghegan wrote:
> > It's possible that amcheck would have given you an accurate diagnosis
> > of the problem -- especially if you used bt_index_parent_check():
> >
> > https://www.postgresql.org/docs/current/amcheck.htm
to
corrupt B-Tree indexes (not Geometry):
https://trac.osgeo.org/postgis/ticket/3841
Though I also see what could be a comparable bug in Geometry:
https://trac.osgeo.org/postgis/ticket/3777
These bugs are from about 3 years ago. If I'm right you should be able
to isolate the bug using amc
on the
> scan and join plan types and the order on disk, but it must not be
> relied on.
>
>
> I would like to know if there is any way to change that to have a "real"
> random behaviour.
It might be an interesting exercise to implement this as a post-parsing
ho
nt that?
Look for post_parse_analyze_hook. Walk the parsed query tree, look for
queries without ordering clause and manufacture one.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2019-08-13 17:16, rihad wrote:
> If I increase it in postgresql.conf and SIGHUP the master server, will
> the change be applied to all running backends, or only to the ones
> started after the change? Thanks.
It will be applied to all running backends.
--
Peter Eisentraut
thing different,
or?
Thx for taking your time!
On Fri, Aug 16, 2019 at 3:44 PM Tom Lane wrote:
> Peter Grman writes:
> > our ORM with tenant separation enabled is creating the following query:
>
> Ugh.
>
> By my count there are nine joined tables in that query, which means
&g
fully support 64-bit integers, and the LSNs and the files were
tracked internally as pairs of 32-bit integers.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
1, re-analyzed the DB and it didn't help.
I tried to to create ndistinct statistics to include the account code in
the statistics:
CREATE STATISTICS MT_ReservationDepartureUtc (ndistinct) on "AccountCode",
"DepartureUtc" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationArrivalUtc (ndistinct) on "AccountCode",
"ArrivalUtc" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationNoShowFeeId (ndistinct) on "AccountCode",
"NoShowFeeId" from "Reservation"."Reservation";
CREATE STATISTICS MT_ReservationTimeSliceDefinitionId (ndistinct) on
"AccountCode", "TimeSliceDefinitionId" from "Reservation"."Reservation";
but that didn't help either
So I know the issue - it's using a nested loop instead of a hash join, for
that one account which has a ton of data sets - all others have less items,
that's why they are fine, although even other accounts would benefit from
hash joins - one test had 0,15ms execution time instead of 95,5ms
How can I help postgres to collect the correct data?
Thank you,
Peter
n"
add constraint
"FK_Reservation_TimeSliceDefinition_TimeSliceDefinitionId_test"
foreign key ("AccountCode", "TimeSliceDefinitionId") references
"Rates"."TimeSliceDefinition" ("AccountCode", "Id")
on delet
checked, restoring this database will take about 30GB of disk
space on top of the dump file itself.
--
Peter Geoghegan
led almost
immediately. If it happened with any regularity, somebody would have
complained by now.
As Tom said, it's hard to give a useful answer without more context --
how you define "immediate"?
--
Peter Geoghegan
ces of pg_receivexlog
running and copying things to different places. This is complicated to
do correctly with archive_command. 2) pg_receivexlog will fsync the
files it writes. This is also complicated to do correctly with
archive_command.
--
Peter Eisentraut http://www.2ndQuadra
earlier than pg93. So thank you for
> your hard work and dedication to this awesome piece of software.
How long did it take on 9.3?
I am the author of the parallel CREATE INDEX feature. It's good to get
feedback like this.
--
Peter Geoghegan
mit applies to a tuple *after*
TOAST compression has been applied.
--
Peter Geoghegan
sort code received many improvements over the years, really
starting in 9.5, and continuing in 9.6, 10 and 11. FWIW, I think that
that was probably the biggest factor here. Though parallel CREATE
INDEX will have helped as well.
--
Peter Geoghegan
seem to mean that it's ICU's bug not ours.
Some build farm coverage of Windows+ICU would be nice. We have test
cases in place that might have caught this.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
simplified your reproduction steps from the previous message to a
test case, and I can confirm that your proposed fix addresses the issue.
A patch is attached. Maybe someone can look it over. I target next
week's minor releases.
--
Peter Eisentraut http://www.2ndQuadran
useful in the test case?
Turns out it's not necessary. Attached is an updated patch that
simplifies the test even further and moves it into the
008_diff_schema.pl file.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Tra
On 2019-11-05 17:18, Andres Freund wrote:
On 2019-11-05 16:02:51 +0100, Peter Eisentraut wrote:
$node_publisher->stop('fast');
+
+
+# TODO:
https://www.postgresql.org/message-id/flat/a9139c29-7ddd-973b-aa7f-71fed9c38d75%40minerva.info
+
+$node_publisher = get_new_nod
On 2019-11-07 16:18, Jehan-Guillaume de Rorthais wrote:
On Thu, 7 Nov 2019 16:02:21 +0100
Peter Eisentraut wrote:
On 2019-11-05 17:05, Jehan-Guillaume de Rorthais wrote:
I have simplified your reproduction steps from the previous message to a
test case, and I can confirm that your proposed
ry here, modified to verify all B-Tree indexes (not
just those indexes in the pg_catalog schema):
https://www.postgresql.org/docs/10/amcheck.html
--
Peter Geoghegan
e way to go, since pg_upgrade doesn't support 8.3.
Also consider Londiste.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
code then maybe. It certainly seems a bit inconsistent.
It looks like the multi-row case in transformInsertStmt() would have to
develop a bit more smarts to discover this case and then replace the RTE
reference in the target list with a single SetToDefault node?
--
Peter Eisentraut
nd the new type.
You really need to carefully plan and test each class of scenarios
separately.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
something
- The OS underlying amazon's RDS service
Postgres itself doesn't cache any host name resolution results. I don't
know about the other two pieces.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
peak).
max_locks_per_transactions only affects relation locks (also known as
heavy weight locks), but pg_locks also shows other kinds of locks.
Filter by locktype = 'relation' to get the appropriate view.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Developmen
tion.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT
for details.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
tions on a remote host.
I'll read some more about the replication slots themselves (I did read
about them a while back), but doing the above seems like a good way to
break B from A, before resubscribing C to A instead?
Yes, that's the one you want.
--
Peter Eisentraut
ck('my_index', true);
If that doesn't show any errors, then perhaps try this:
SELECT bt_index_parent_check('my_index', true);
If you're on Postgres 10, then you should leave out the second
argument, "true", since that version doesn't have the extra
heapallindexed check.
Let us know what you see.
--
Peter Geoghegan
have that for the ICU
collations, but it currently isn't possible to use ICU as the default
collation provider. You really have to go out of your way to use ICU
collations.
--
Peter Geoghegan
4:
regression=# create extension pageinspect;
CREATE EXTENSION
regression=# select version from bt_metap('pg_aggregate_fnoid_index');
version
-
4
(1 row)
--
Peter Geoghegan
ss and until
you REINDEX. This includes cases where you're running pg_upgrade
against a restored physical backup.
--
Peter Geoghegan
each other. So you can have a logical
replication stream and wal2json next to each other, and they won't
interfere with each other. So what you are considering is possible and
safe. (I don't know whether it will give you satisfactory insights.)
--
Peter Eisentraut
libpq.so.5 => /usr/pgsql-12/lib/libpq.so.5 (0x7f93fcbe2000)
# /usr/bin/psql --version
psql (PostgreSQL) 9.2.24
How do I resolve this issue, to allow both programs to run on the same
host?
--
\\// Peter - http://www.softwolves.pp.se/
when selecting data that is not representable as LATIN1
or whatever. However, if you change the setting, that doesn't mean your
terminal setup will actually display Unicode correctly. You said you're
dealing with mostly ASCII-ish data anyway, so it will probably not make
a differe
processes in a free of any other load system.
> It is postgres 11.7
Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.
--
Peter Geoghegan
practice.
Please can anyone recommend a way of approaching this? Perhaps empty strings
are pragmatic in this situation?
Kind regards
Peter
.
Kind regards
Peter
>Is is possible to have two entries which have the same
>address_identifier_general, street and postcode, but different
>descriptions?
Unfortunately, yes. The data comes from gov't systems to
regulate the development/alteration of arbitrary pieces of property and
those pieces do not always have a
of the test
failure. (Search the file for "openssl/ssl.h" to find the right place.)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
part of PostgreSQL 9.6 release. But I am not sure
if these changes made it to any of the releases.
You are right that the fail-over slot mechanism was supposed to address
this but it never made it into a release. I'm not aware of an
open-source solution for this right now.
--
Peter Eisen
Are there error messages shown
from commands or in the server logs? Is replication progressing, but
doing something your are not expecting? etc.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
ding. So when the subscription worker connects, it
initially sees a state as of the creation of the replication slot, when
the publication did not exist yet.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
best.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
ce of advice would likely be to upgrade.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
arate vacuum tuning necessary for this, but if you
are experiencing issues, first treat it like a normal vacuum
configuration problem.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jun 8, 2020 at 5:17 PM Peter wrote:
> Loosing a RedoLog is very bad, because there is no redundancy,
> loosing a single one of them makes the timeline disappear and it
> will only reappear after another Base Backup. Very very bad.
>In this case, it seems, Postgres wil
root cause for that.
Have you checked the server logs? Maybe it has trouble applying a
change, for example due to a unique constraint or something like that.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
rites didn't restored the page to the state before the
> index-item deletion happened(that is, if full_page_writes were set to
> off.). (If it found to be the cause, I'm not sure why that didn't
> happen on 9.5.)
There is also a Heap/HOT_UPDATE log line with similar errors.
--
Peter Geoghegan
is approach to dumping out a single page image
easier, since it doesn't involve relfilenodes or filesystem files:
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib.2Fpageinspect_page_dump
--
Peter Geoghegan
anted to fix this for numeric, we'd have to invent a new
numeric datatype (called numeric2, say). That probably isn't as hard
as it sounds, since it could be part of the same B-Tree operator
family as numeric. It could also be implicitly cast to numeric.
--
Peter Geoghegan
On Tue, Aug 18, 2020 at 9:44 AM Peter Geoghegan wrote:
> If we wanted to fix this for numeric, we'd have to invent a new
> numeric datatype (called numeric2, say). That probably isn't as hard
> as it sounds, since it could be part of the same B-Tree operator
> family as num
split like this then
you need three copies of the key -- the original, the new, and a
second copy of the original. That's much more complicated.
--
Peter Geoghegan
=
operator will be broken in the presence of different display scales.
It's a non-starter.
The numeric2 design that I sketched is a bit ugly, but I can see no
better way. A three-way posting list split (i.e. the other design that
you sketched) is a special case that is very hard to test, very
complicated, and of little value in the grand scheme of things.
--
Peter Geoghegan
test. And it might defeat certain future optimizations based on heap
TID being the only tiebreaker. Having two types of equality might have
to bleed into the optimizer.
It's a question of engineering trade-offs. I don't think that it's worth it.
--
Peter Geoghegan
1.13 and 1.14
It could be related to the SCRAM pass-through.
Greig, if you have a way to reproduce it, please file a complete bug
report on GitHub.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
to stand up a 2 way SSL communication channel between the primary
and secondary, or does anyone have one that they can share?
Thanks,
Susan
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2020-09-03 14:01, Susan Joseph wrote:
Unfortunately I am not allowed to use wireshark in my environment. Good
idea though
The system view pg_stat_ssl, in combination with pg_stat_activity, will
show you whether a connection is using SSL.
--
Peter Eisentraut http://www
tion about
the table from the publisher, it is looking for a real table, which it
doesn't find, because it's a partitioned table. So this combination
doesn't work.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
. That will make the table larger initially, but leaving enough
space behind on the same heap pages for successor tuples makes it
possible to use HOT updates.
--
Peter Geoghegan
eneral
discussion and coordination of translation activities.
The time is now. Even though PostgreSQL 13 was just released,
translation updates for the PostgreSQL 13 stable branch are still welcome.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support,
width=48)
Adding the tzn.utc_offset results in the fact that the execution plan no
longer considers to use the index on the measurement_value table. Is there
any way the SQL can be rewritten so that the index is used? Or any other
solution so that the query with the timezone offset returns in a comparable
time?
Many thanks!
Peter
was aware of it)
Wkr,
Peter
Hash Join (cost=683.93..7270857.46 rows=458127 width=20)
Hash Cond: (mv_inner.device_id = d.short_id)
Join Filter: ((mv_inner."timestamp" > ('2020-11-06 00:00:00'::timestamp
without time zone - pg_timezone_names.utc_offset)) AND (mv_inner.&
t; > ('2020-11-06
00:00:00'::timestamp without time zone - pg_timezone_names.utc_offset)) AND
("timestamp" < ('2020-11-07 00:00:00'::timestamp without time zone -
pg_timezone_names.utc_offset)))
Peter
> On 10 Nov 2020, at 08:25, Pavel Stehule wrote:
>
>
>
> út 1
SubPlan 2
-> Function Scan on pg_timezone_names pg_timezone_names_1
(cost=0.00..12.50 rows=5 width=16)
Filter: (name = dc.timezone)
Now returns the 320K in less than 5sec.
I was till now convinced that correlated subqueries or joins are equiva
>
> Curious, what is seq_page_cost and random_page_cost?
show seq_page_cost
->1
show random_page_cost
->4
> Any idea of your cache hits for indexes?
No, I am afraid not. It’s been a long time since I went that deep in the RDBMS
behaviour (must have been Oracle 7.something :) )
> If they a
> Curious, how accurate is that row count of 1.2 million records for 3 days?
Not to bad actually
select count(mv_inner.*)
from measurement_value AS mv_inner
where mv_inner.timestamp > '2020-11-06'::timestamp - interval '1 day' and
mv_inner.timestamp < '2020-11-07'::timestamp + interval '1 day'
>
> Ahhh. You don't have a single column index on the timestamp value or a multi
> column one with timestamp first. No wonder the subquery didn't help. My
> apologies for not realizing that before. Thanks for satisfying my curiosity
> why it didn't perform like it should. Certainly, that index
> Good luck!
Tx! And tx for your support.
On 2020-11-16 18:30, Tom Lane wrote:
In this case, I see one use of the constant TRUE in collationcmds.c,
but I wonder how come that's there given that we deprecated upper-case
TRUE some time ago.
In 2eb4a831e5fb5d8fc17e13aea56e04af3efe27b4, I intentionally left that
there because it was the d
On 2020-11-20 08:13, Dirk Mika wrote:
we come from the Oracle world and we have an application that, depending on a
setting, sends the command ALTER SESSION SET NLS_SORT=... when connecting to
the database.
Is there a similar way to set a COLLATE for a session in PostgreSQL?
I know that I can
On 2020-12-04 17:18, Tom Lane wrote:
There is a SET COLLATION command in the SQL standard that does this.
Someone just has to implement it. It wouldn't be terribly difficult, I
think.
[ squint... ] Just because it's in the standard doesn't mean it's a
good idea. It sounds like this is morall
myuser", database "postgres",
SSL off
With patched libpq:
$ psql -h myserver -U myuser -d postgres
Password for user myuser:
psql (13.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256,
compression: off)
Type "help" for help.
postgres=>
B
On Thu, Jan 21, 2021 at 12:55 PM Tommy Li wrote:
> Is there any way to configure autovacuum to log the same information as
> VACUUM VERBOSE?
No. Though there really should be.
--
Peter Geoghegan
around the problem
You could try running: rpm -Va --nofiles --nodigest
Is this an error in the pg_partman package, or are we declaring the
dependency incorrectly on our end?
--
\\// Peter - http://www.softwolves.pp.se/
> On 25 Jan 2021, at 20:17, James B. Byrne wrote:
>
> I am recreating a test database with data provided for a series of future
> exercises. Presently I am trying to understand why a simple insert statement
> is not working. The user invoking this insert is 'postgres' and has superuser
> priv
quot;
> "Minch ()"
> "Berst"
> “Besel”
but unless you select from the resulting table using again an order by, the
rows will be returned in an undetermined order. Such is the nature of the
relational model - there is no order you can rely on when selecting, unless you
specify it
Hth,
Peter
]:
[2992414-2] 2022-03-10 09:44:50: pid 8731: DETAIL: kind == 0
The query does not appear to be relevant, it's well-formed and not the
same every time.
Now, what does this mean, and how to deal with it?
Best regards,
Peter Juhasz
Comnica Kft.
On Mon, Apr 18, 2022 at 11:37 PM bhargav kamineni wrote:
> executing the vacuum on the entire cluster is also giving the same HINTS and
> WARNING's
You're using Aurora, not PostgreSQL. Perhaps this is actually a bug,
but there is no way for anybody here to know.
--
Peter Geoghegan
On 17.04.22 13:28, cecile rougnaux wrote:
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.70.dylib
Whenever libicu's major version changes, you need to rebuilt the
postgresql package.
nxid) reached vacuum_freeze_table_age).
See my recent response to a similar question here:
https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhzao-eajflv...@mail.gmail.com
--
Peter Geoghegan
vacuum, but that's not really true (apart from VACUUM FULL, which
really is quite different). The difference between aggressive and
non-aggressive can be big in practice due to an accumulation of
unfrozen pages over multiple non-aggressive vacuums.
--
Peter Geoghegan
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]$ gre
On 23.06.22 20:21, Adrian Klaver wrote:
Not sure why that is necessary? Is seems this is low hanging fruit
that could dealt with by the equivalent of lower('en_US.UTF-8') =
lower('en_US.utf-8').
Well that was clear as mud. My point was that I don't see why the end
user should have to do this
ion is in dire need of an
overhaul. :-(
--
Peter Geoghegan
On 06.07.22 10:42, Florents Tselai wrote:
I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table
holding (key text, text text,) of around 50M rows.
These are text fields extracted from 4-5 page pdfs each.
I’m adding the following generated col to keep up with tsvectors
101 - 200 of 989 matches
Mail list logo
