Re: unable to understand query result

On 06.07.22 11:31, Stefan Froehlich wrote: I have a database returing these result sets for two almost identical queries: #v+ $ select id, pid, length(pid), md5(pid) from product where pid like '800'; id | pid | length | md5 --+-++--

Re: Would it be possible to add functions to tab-completion in psql?

On 16.08.22 16:14, hubert depesz lubaczewski wrote: Specifically, I'd ask what is the harm of increasing what tab completion can do by a lot - for example, make it tab-complete fields from all tables. And make it possible to tab-complete column name anywhere in where clause. But function name in

Re: Monitoring multixact members growth

more sense for *your* workload, where huge differences in the rate of MultiXact consumption among tables is likely the norm. This still isn't perfect (far from it), but it has the potential to make things far better here. -- Peter Geoghegan

Re: lippq client library and openssl initialization: PQinitOpenSSL()

On 11.09.22 23:43, Daniel Gustafsson wrote: On 11 Sep 2022, at 23:35, Tom Lane wrote: Daniel Gustafsson writes: On 11 Sep 2022, at 17:08, Tom Lane wrote: Don't believe so. The HAVE_CRYPTO_LOCK stuff is all obsolete and not compiled if you built against 1.1.0. The only thing left that will

Re: Postgres calendar?

On 04.10.22 23:02, Bruce Momjian wrote: Would people be interesting in subscribing to a Postgres calendar that includes dates for minor releases, final minor release dates for major versions, commit fests, and even Postgres events? Events already exists: https://calendar.google.com/calendar/ic

Re: Weird planner issue on a standby

ll be set to false on the standby. -- Peter Geoghegan

Re: Weird planner issue on a standby

On Tue, Oct 11, 2022 at 10:04 PM Tom Lane wrote: > Do we propagate visibility-map bits to standbys? Yes. -- Peter Geoghegan

Re: Weird planner issue on a standby

interlock at all in the case of nbtree index scans with MVCC snapshots -- but *not* with index-only scans. See "Making concurrent TID recycling safe" in the nbtree README. I only got around to documenting all of the details here quite recently. The index-only scan thing dates back to 9.5. -- Peter Geoghegan

Re: ON CONFLICT and WHERE

mud, too. What does "inferred" mean here? I think it means "chosen as > arbiter index", but maybe I misunderstand.) Unique index/constraint inference is the process by which we choose an arbiter index. See the second paragraph of the "ON CONFLICT Clause" section of the INSERT docs. -- Peter Geoghegan

Re: Lots of read activity on index only scan

On Fri, Nov 18, 2022 at 12:46 PM Peter J. Holzer wrote: > Both do a parallel index only scan. Both perform 0 heap fetches. > But one reads 27336 buffers (or about 22 bytes per index entry, which > sounds reasonable) while the other reads 9995216 buffers (or almost one > full buffer p

Re: Lots of read activity on index only scan

On Fri, Nov 18, 2022 at 1:50 PM Peter J. Holzer wrote: > There should be about 27000 of them, same as for the othe index, right? There aren't that many. The point I'm making is that you can access each VM page approximately once (and check relatively many index tuple's TIDs

Re: Fwd: Postgre und GIS Umstellung von MD5 auf SCUM SHA 256

You can send questions in German to . On 23.11.22 11:22, Nikolas Hanry wrote: Liebe Community, Wir haben versucht, die Authentizierung  in unserer postgresql-Umgebung von MD5 zu SCRUM SHA 256 zu ändern (PostgreSQL V.11) - Stoppen Sie die Dienste und beenden alle aktiven Verbindungen - mit a

Re: Question regarding UTF-8 data and "C" collation on definition of field of table

relative ordering of each script can be changed this way. There is also something called merged tailorings. The OP should see the Postgres ICU docs for hints on how to use these facilities to make a custom collation that matches whatever their requirements are: https://www.postgresql.org/docs/

Re: Switching identity column to serial

On 04.02.23 21:55, Erik Wienhold wrote: Why doesn't this work? BEGIN; DROP SEQUENCE t_id; [This won't work, you need to use ALTER TABLE / DROP IDENTITY.] CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id; ALTER SEQUENCE new_t_id_seq OWNER TO new_owner; SELECT setval('new_t_id'

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

On 07.02.23 11:43, Sebastien Flaesch wrote: select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname Just a side note: You might find the quote_ident() function useful.

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

look into, in particular anything I > should check before upgrading the remaining 13.8 DB to 13.9? I recommend running amcheck on all indexes, or at least all possibly-affected text indexes. -- Peter Geoghegan

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

interface for running amcheck routine, so maybe look into that once you upgrade. -- Peter Geoghegan

Re: Automatic aggressive vacuum on almost frozen table takes too long

ust about possible with an access method like GIN. Do you have any non-btree indexes on the table? Can you show us the details of the table, including all of its indexes? In other words, can you show "\d applications" output from psql? -- Peter Geoghegan

Re: Automatic aggressive vacuum on almost frozen table takes too long

Can you run amcheck's bt_index_check() routine against some of the indexes you've shown? There is perhaps some chance that index corruption exists and causes VACUUM to take a very long time to delete index pages. This is pretty much a wild guess, though. -- Peter Geoghegan

Re: Automatic aggressive vacuum on almost frozen table takes too long

at version). And if you were on 14+, things in this area would be much better still. -- Peter Geoghegan

Re: PostgreSQL optimizations for CoW FS

On 22.02.23 10:41, HECTOR INGERTO wrote: Let’s say we have to run a PostgreSQL instance on top of a copy on write filesystem like ZFS or BTRFS. In adittion to set full_page_writes = off, what other optimizations can be done on the PostgreSQL side? Look at the settings wal_init_zero and wal_rec

Re: PG16devel - vacuum_freeze_table_age seems not being taken into account

x27;d have frozen that one tuple and then set the page all-visible. The page would likely be frozen again by the next aggressive VACUUM, which is usually much more expensive. -- Peter Geoghegan

Re: UPSERT in Postgres

PG15 in theory can be used to do UPSERT > properly that is void of the aforementioned limitation. > The downside is it is rather verbose. The MERGE command has various race conditions that are particularly relevant to UPSERT type use cases. See the wiki page you referenced for a huge amoun

Re: Problem with stored procedure and nested transactions

actually trying to do, but you might need to reorganize it a bit. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: getting pg_basebackup to use remote destination

uld run pg_basebackup on the host where you want to set up your standby. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: CREATE COLLATION to match pg_collation data

been built with ICU support for many years -- they modify the source code minimally to make this work. It may well still be impossible to use "az-x-icu" on a FreeBSD installation of 9.6, though. -- Peter Geoghegan

Re: oracle_fwd - is it safe or not?

fwd uses a kernel module? Are you sure? That is surprising. Perhaps he meant that the extension runs in the PostgreSQL "kernel" and could crash PostgreSQL, which is true of any extension. But oracle_fdw seems reasonably popular, so I would expect a base level of quality. -- Peter Eisen

Re: Channel binding not supported using scram-sha-256 passwords

VE_PGTLS_GET_PEER_CERTIFICATE_HASH into account before selecting SCRAM-SHA-256-PLUS. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Plpythonu extension

s removed here: https://github.com/Homebrew/homebrew-core/pull/36074 If you don't care about understanding that, maybe try this alternative tap: https://github.com/petere/homebrew-postgresql -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Suppo

Re: procedures and transactions

except maybe needing to retry after a failure) > and simply send `call foo(?, ?)` to the DB. You can run SET TRANSACTION ISOLATION LEVEL in a procedure. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: procedures and transactions

On 2019-02-20 17:45, Rob Nikander wrote: >> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut >> wrote: >> >> You can run SET TRANSACTION ISOLATION LEVEL in a procedure. > > I tried that before but I get this error: > > create or replace procedure t_tes

Re: Channel binding not supported using scram-sha-256 passwords

ndif > + } Is that right? Won't we then just select nothing if the macro is not defined? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Channel binding not supported using scram-sha-256 passwords

upports channel binding > (HAVE_BE_TLS_GET_CERTIFICATE_HASH). If the server does not support > channel binding, then only SCRAM is sent. After reading it again a few more times, I think your patch is correct. I tried reproducing the issue locally, but the required OpenSSL version is too old to be easily ava

Re: Channel binding not supported using scram-sha-256 passwords

custom-built OpenSSL, and I can confirm it works. > Another trick would be to comment out the sections in libpq where > HAVE_PGTLS_GET_PEER_CERTIFICATE_HASH is used to emulate a compilation > with OpenSSL 1.0.1 features and older, while still linking with > 1.0.2. Yeah, that might have bee

Re: python install location

uilding that we can tell postgres > which install of python to use when invoking plpython? No, that it determined at build time. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Property Graph Query Language proposed for SQL:2020

> * Property Graph Query Language (PGQL), an SQL-like query language for > graphs, including an Open-sourced parser and static query validator on > GitHub by Oracle. > http://pgql-lang.org/ Yes, I was just at this meeting this week: https://www.w3.org/Data/events/data-ws-2019/ -- Pet

Re: Case Insensitive

On 2019-03-28 09:20, Sridhar N Bamandlapally wrote: > Can we achieve CASE INSENSITIVE in PostgreSQL? Use the citext extension. In PostgreSQL 12, there will be support for case-insensitive collations. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 2

Re: logical replication - negative bitmapset member not allowed

an't quite reproduce this. There are various other checks that prevent this scenario, but it's plausible that with a bit of whacking around you could hit this error message. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seeded Replication

; post  > https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ >  . I doubt you will get a huge speedup, since the dump and the initial data copy use the same COPY command internally. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: Pg analog to DBCC CCHECKDB?

ilable. See: https://github.com/petergeoghegan/amcheck -- Peter Geoghegan

Re: Primary key data type: integer vs identity

Hi Rich I think you may have serial already there (indicated by sequence in the the default value). If you wish to change to identity columns this should be useful: https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/ Regards Peter

Re: Upgrading locale issues

manually, but what would it look like for en-x-icu? It is safe to call pg_import_system_collations() directly, which is all that initdb does. This is documented, so you wouldn't be relying on a hack. -- Peter Geoghegan

Re: Upgrading locale issues

ning that you clear manually with a command, not a mechanism that > really tracks which database objects were last rebuilt/validated with > a given version. Yes, that does seem like a big remaining weakness. -- Peter Geoghegan

Re: Upgrading locale issues

On 2019-05-03 15:35, Daniel Verite wrote: > 'b' < 'a' is never true for any locale AFAIK, But there is 'bb' < 'aa' in Danish. :-) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Refresh Publication takes hours and doesn´t finish

On 2019-05-20 23:30, Tom Lane wrote: > Hmm ... given that pg_get_publication_tables() shouldn't return any > duplicate OIDs, it does seem unnecessarily inefficient to put it in > an IN-subselect condition. Peter, is there a reason why this isn't > a straight lateral joi

Re: Questions about btree_gin vs btree_gist for low cardinality columns

hin GIN, where it's impossible for the main entry tree to have duplicates without at least storing them in a posting list. -- Peter Geoghegan

Re: pg_repack issue

_repack] Error 1 > make[1]: Leaving directory `/home/postgres/software/pg_repack-1.4.4/bin' > make: *** [all] Error 2* It's a bit bogus that pg_repack would require this, but perhaps installing the readline-devel (or similar) package would get you past this. -- Peter Eisentraut

Re: Table partition with primary key in 11.3

lations for global indexes, though maybe global indexes could focus our attention on that problem. -- Peter Geoghegan

Re: Table partition with primary key in 11.3

I'd be particularly concerned about the complexity of VACUUM there, whereas that doesn't seem all that bad in the case of global indexes. -- Peter Geoghegan

Re: Table partition with primary key in 11.3

make a good case for asynchronously cleaning up the dead entries that point to a dropped partition (probably within VACUUM). Perhaps we should offer *both* as options. -- Peter Geoghegan

Re: Table partition with primary key in 11.3

bare minimum amount of space is used for partition numbers. Maybe it won't matter that much if partition numbers cannot be recycled due to this asynchronous processing. -- Peter Geoghegan

Re: Table partition with primary key in 11.3

could make that happen. Maybe this same representation could be used for all nbtree indexes, not just global nbtree indexes. -- Peter Geoghegan

Re: Table partition with primary key in 11.3

as hard as it sounds. You don't have to be Claude Shannon to realize that it's kind of silly to reserve 16 bits for the offset number component of a TID/ItemPointer. We need to continue to support offset numbers that go that high, but the implementation would optimize for the common case where offset numbers are less than 512 (or maybe less than 1024). -- Peter Geoghegan

Re: Table partition with primary key in 11.3

anything now, but I think that it's possible that the page split thing will very effective. Perhaps even so effective that it won't make much sense to vacuum global indexes just because there is a pending dropped partition. -- Peter Geoghegan

Re: checkpoints taking much longer than expected

an "immediate" mode shutdown is appropriate, then. That will mean that the primary will need to go through crash recovery if and when you bring it back up, though. -- Peter Geoghegan

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

which reports a avg_leaf_density for the index. Though I agree that that's not likely to help matters. Apart from anything else, the steady state of an index is embodied by more than just its avg_leaf_density. Especially following the v12 enhancements to B-Tree indexes. -- Peter Geoghegan

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

ey're both causes of index bloat that VACUUM cannot usually prevent. -- Peter Geoghegan

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

nce. It's easy to show problems with very low cardinality indexes in the old code. You'll definitely notice a difference there. > Is there a pdf or text version? Just the talk slides: https://www.pgcon.org/2019/schedule/attachments/518_nbtree-arch-pgcon.pdf -- Peter Geoghegan

Re: Postgresql 12 Beta2 Crashes for any Insert/Update

e? https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend Thanks -- Peter Geoghegan

Re: VACUUM (INDEX_CLEANUP OFF) and GIN indexes

7;t necessarily going to stop autovacuum/autoanalyze from doing pending list cleanup. The ANALYZE pending list cleanup path has some problems: https://postgr.es/m/cah2-wzkjrk556envtflmyxedw91xguwiyzvep2kp5yqt_-3...@mail.gmail.com -- Peter Geoghegan

Re: ICU, locale and collation question

On 09.05.23 08:54, Oscar Carlberg wrote: Our initdb setup would then look like this for compatibility; -E 'UTF-8' --locale-provider=icu --icu-locale=sv-SE-x-icu --lc_monetary=sv_SE.UTF-8 --lc-numeric=sv_SE.UTF-8 --lc-time=sv_SE.UTF-8 --lc-messages=en_US.UTF-8 Should we still provide createdb wit

Re: ICU, locale and collation question

On 10.05.23 07:02, Kirk Wolak wrote: On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut <mailto:peter.eisentr...@enterprisedb.com>> wrote: On 09.05.23 08:54, Oscar Carlberg wrote: > Our initdb setup would then look like this for compatibility; > -E 'UTF-8'

Re: function signature allow "default" keyword

On 15.05.23 10:33, jian he wrote: function idea. allow function calling using the default keyword for any of the input arguments. example: https://dbfiddle.uk/FQwnfdmm So something like this "SELECT * FROM customer_orders(2579927, 'order_placed_on DESC', default

Re: Large pkey index on insert-only table

ing is something that theoretically can be avoided by the implementation (if there were deletes involved then that wouldn't apply, but there aren't). -- Peter Geoghegan

Re: Query performance going from Oracle to Postgres

1) Index Cond: (a = 1) Filter: (b <> 42) Rows Removed by Filter: 1 Heap Fetches: 0 Buffers: shared hit=11 Planning Time: 0.076 ms Execution Time: 3.204 ms (8 rows) (There are lots of index tuples matching (a, b) = "(1, NULL)" here, you see.) -- Peter Geoghegan

Re: Query performance going from Oracle to Postgres

On Thu, Sep 7, 2023 at 3:48 AM David Rowley wrote: > On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan wrote: > > It seems likely that the problem here is that some of the predicates > > appear as so-called "Filter:" conditions, as opposed to true index > > quals. >

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

On 10.10.23 08:22, Laurenz Albe wrote: Apart from that, it is a good idea to use table names that are standard SQL identifiers, so that you don't have to double quote them all the time. FWIW, the Chinese character sequences posted here would be valid unquoted identifiers if PostgreSQL implemen

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On 19.02.24 04:32, Darryl Green wrote: I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense. The change that I think yo

Re: Non-Stored Generated Columns

On 28.02.24 16:09, Dominique Devienne wrote: We use generated columns extensively. And we have foreign-keys attached to those generated columns. The fact they are always Stored thus wastes space in our case. Any chance PostgreSQL might gain actual virtual / non-stored generated columns soon? Eve

Re: (When) can a single SQL statement return multiple result sets?

On 11.04.24 01:02, Tom Lane wrote: And if not, why can't I write a stored procedure or function that returns multiple result sets? [ shrug... ] Lack of round tuits, perhaps. We don't have any mechanism today whereby a stored procedure could say "please ship this resultset off to the client, bu

Re: Identity column data type difference in PostgreSQL and Oracle

On 03.05.24 12:57, Muhammad Ikram wrote: Tables which have an identity column in Oracle when migrated to PostgreSQL, the data type of Identity column is changed to bigint from number by the tools. This causes the size of column to be reduced to max value supported by bigint which is way lower t

Re: PG17 optimizations to vacuum

s > and the total size of the WAL. Instead, WAL numbers have significantly > degraded. > > What am I doing wrong? That does seem weird. CC'ing the authors of the relevant VACUUM enhancements. -- Peter Geoghegan

Re: PG17 optimizations to vacuum

On Mon, Sep 2, 2024 at 4:58 PM Peter Geoghegan wrote: > On Mon, Sep 2, 2024 at 4:35 PM Pavel Luzanov wrote: > > If it helps, without creating index on id column, the numbers will be > > much closer: > > Yes, avoiding all index vacuuming seems useful. It makes the test cas

Re: PG17 optimizations to vacuum

ed to WAL, buffers, and CPU time that changed. Perhaps I'm not thinking of something obvious. Maybe it's extra VISIBILITY records? But I'd expect the number of VISIBILITY records to match the number of pages frozen, given these particulars. VACUUM VERBOSE at least shows that that hasn't changed. -- Peter Geoghegan

Re: PG17 optimizations to vacuum

inated in pages that only contained existing LP_UNUSED items when scanned by VACUUM? -- Peter Geoghegan

Re: PG17 optimizations to vacuum

ally cause an increase in the number of WAL records written? I'd have thought that that was simply impossible. -- Peter Geoghegan

Re: PG17 optimizations to vacuum

're writing extra FPIs to set hint bits. But that explanation only works if you assume that page-level checksums are in use (or that wal_log_hints is turned on). -- Peter Geoghegan

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

ave lots of duplicates, which did perform rather badly prior to Postgres 12. I bet that you'd benefit from upgrading to Postgres 12, or especially to Postgres 13. The B-Tree space management is a lot better now. (Actually, it'll be better again in Postgres 14.) -- Peter Geoghegan

Re: hot_standby_feedback implementation

ly rare in practice. -- Peter Geoghegan

Re: When to REINDEX a serial key?

On 06.07.21 14:19, Ron wrote: On 7/6/21 4:52 AM, David Rowley wrote: On Tue, 6 Jul 2021 at 21:35, Ron wrote: The legacy RDBMS which I used to manage has a tool for analyzing (not in the Postgresql meaning of the word) an index, and displaying a histogram of how many layers deep various part

Re: Question about how to handle numeric (decimal) data types while using libpq

On 06.07.21 13:04, Sudheer H R wrote: I am trying to use libpq for interfacing with PostgreSQL from a C/C++ based application. I have tried to use binary format of data for both sending and receiving data to and from server (resultFormat = 1). As I understand most binary types, int, float etc

Re: pg_upgrade as a way of cloning an instance?

On 07.07.21 08:53, Luca Ferrari wrote: Hi all, someone pointed me out that pg_upgrade can be used to do a clone of the database, specifying the same binaries such as pg_upgrade -B /usr/pgsql-13/bin -b /usr/pgsql-13/bin -D /data/clone -d /data/src I tested it and it seems to work, even if I don'

Re: libicu global support

On 11.07.21 23:52, Jakub Jedelsky wrote: during the adoption of Centos 8 on our servers we ran into problems with Postgresql (13.3), glibc (delivered by the Centos) and performance of sorting. Because of that we're planning to use the ICU collations (en-x-icu), but the current implementation is

Re: pg_wal lifecycle

On 13.07.21 09:07, Luca Ferrari wrote: I'd like to see if I get it right about pg_wal: it grows up to pretty much max_wal_size, at which point a checkpoint is triggered. If the server triggers a timed checkpoint before the pg_wal is at max_wal_size, the system recycles the wals thus keeping the p

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

dexes won't grow at all due to garbage index tuples, especially on Postgres 14. -- Peter Geoghegan

Re: sort order

On 27.07.21 19:07, Marc Millas wrote: so, obviously, both lc_collate knows about the é but obviously, too, they do behave differently on the impact of the beginning white space. I didn't see anything about this behaviour on the doc, unless the reference at the libc should be understood as ple

Re: lower() and unaccent() not leakproof

On 26.08.21 06:52, David G. Johnston wrote: On Wednesday, August 25, 2021, Christophe Pettus > wrote: lower() and unaccent() (and most string functions) are not marked as leakproof.  Is this due to possible locale / character encoding errors they might encou

Re: lower() and unaccent() not leakproof

On 26.08.21 10:40, Daniel Gustafsson wrote: On 26 Aug 2021, at 09:58, Peter Eisentraut wrote: On 26.08.21 06:52, David G. Johnston wrote: On Wednesday, August 25, 2021, Christophe Pettus mailto:x...@thebuild.com>> wrote: lower() and unaccent() (and most string functions) are not

Re: lower() and unaccent() not leakproof

On 26.08.21 16:00, Tom Lane wrote: Generally speaking, we've been resistant to marking anything leakproof unless it has a very small code footprint that can be easily audited. In particular, anything that shares a lot of infrastructure with not-leakproof functions seems quite hazardous. Even if

Re: gen_random_uuid key collision

ll block write DML. You might prefer to just use the first query if this is running in a production environment. -- Peter Geoghegan

Re: Behavior change in PostgreSQL 14Beta3 or bug?

It's a non-hot update, and so there is a single dead index tuple. You're seeing the new optimization that makes vacuum skip indexes in marginal cases. Try running vacuum with index cleanup = on. Peter Geoghegan (Sent from my phone)

Re: Behavior change in PostgreSQL 14Beta3 or bug?

ing alone, the number of vacuum operations that do unnecessary index vacuuming doesn't increase at all (except perhaps due to rounding effects). -- Peter Geoghegan

Re: Behavior change in PostgreSQL 14Beta3 or bug?

eds to do work that is proportional to the number of unset-in-vm heap pages. I believe that that trade-off makes a lot of sense. Autovacuum has little chance of keeping anything like 100% of all pages set in the VM anyway. But it can get a lot closer to it in some cases now. -- Peter Geoghegan

Re: Behavior change in PostgreSQL 14Beta3 or bug?

On Mon, Sep 6, 2021 at 7:52 AM Daniel Westermann (DWE) wrote: > >Try running vacuum with index cleanup = on. > > Thank you, Peter Thanks for testing! -- Peter Geoghegan

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

e: https://www.postgresql.org/message-id/flat/CAL9smLC%3DSxYiN7yZ4HDyk0RnZyXoP2vaHD-Vg1JskOEHyhMXug%40mail.gmail.com#e79eca5922789de828314e296fdcb82d -- Peter Geoghegan

Re: Segmentation fault in volatile c function

do just pass a string some other way), but it's definitely considered the way to go. Note that you can do stuff like this from SQL: pg@regression:5432 [2999218]=# select 'pg_class'::regclass::oid; oid ─── 1,259 (1 row) -- Peter Geoghegan

Re: Error with Insert from View with ON Conflict

CONFLICT. And because the only alternative interpretation is that Alex intends to update those columns using their current values (not new values), which won't really change anything -- that seems unlikely to have been the intent. -- Peter Geoghegan

Re: Understanding the differences between Temporal tables, CDC and Time Series.

On 10.11.21 07:43, SQL Padawan wrote: I'm looking into Temporal Tables (TT - i.e. System/Application time versioning) but I would like to understand the differences between TTs and two other features (which appear similar in many ways) - Change Data Capture and Time Series. Are Temporal Table

Re: Issues cross-compiling libpq 14.x to MacOS armv8

On 30.11.21 22:04, Tom Lane wrote: Daniel Gustafsson writes: On 30 Nov 2021, at 20:59, Tom Lane wrote: AFAICS this is the only test in our configure script that is a hard fail when cross-compiling, and I don't see a reason for it to be that. We could just assume that /dev/urandom will be avail

Re: case insensitive collation of Greek's sigma

On 26.11.21 08:37, Jakub Jedelsky wrote: postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US", postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US" postgres-# ;  ?column? | ?column? --+--  t        | f (1 row) postgres=# SELECT postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu", po

<    1   2   3   4   5   6   7   8   9   10   >