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

Hi Peter, Thanks for your input, I'm feeling more comfortable that I correctly understand the problem now and it's "just" a collation related issue. > I recommend running amcheck on all indexes, or at least all > possibly-affected text indexes. > > Will the amcheck reliably identify all issues t

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

I have three databases, two of databases where I am experiencing the issue below. The first database was created from a dump in Feb 2022 (a few weeks after the time period for which I seem to have problematic indexes, maybe). The second database was then cloned from the first (ie filesystem level

Vacuuming by non owner/super users?

Is it possible for a non-owner or non super user to be given permission to vacuum tables in the DB? My initial thought is no, but the documentation says: "To vacuum a table, one must ordinarily be the table's owner or a superuser." Where the "ordinarily" seems to imply there might be some non-or

Re: Efficiently advancing a sequence without risking it going backwards.

On Fri, 10 Jul 2020 at 10:27, Jeremy Schneider wrote: > > OP asked for a way to call setval() with a guarantee the sequence will > never go backwards IIUC. His code can check that the new value he wants to > set is higher than the current value, but there’s a race condition where a > second conne

Efficiently advancing a sequence without risking it going backwards.

I have two sequences in different dbs which I want to keep roughly in sync (they don't have to be exactly in sync, I am just keeping them in the same ballpark). Currently I have a process which periodically checks the sequences and does: 1) Check values DB1sequence: 1234 DB2sequence: 1233 (1 behi

Re: How to get timezone offset in timestamp with time zone AT TIME ZONE output.

On Wed, Sep 25, 2019 at 5:44 AM Adrian Klaver wrote: > > The issue is unclear so I am not sure you can discount this as a > solution. The OP had: > > CREATE TABLE users ( > user_id biginit, > user_timezone text, -- Eg 'Australia/Sydney','Asia/Hong_Kong' > ); > CREATE TABLE data ( > id bigint, > u

How to get timezone offset in timestamp with time zone AT TIME ZONE output.

Hi there, Does anyone have a good way of doing: = select '2020-04-04 15:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney'; timezone - 2020-04-05 02:00:00 select '2020-04-04 16:00:00+00'::timestamp with time zone AT TIME ZONE 'Australia/Sydney';

Re: Why the index is not used ?

Hi Didier, Yes, credit cards are a very specific space that probably gets people who are familiar with it going a bit. By the time you factor in general security practices, specific PCI requirements, your threat model and likely business requirements (needing relatively free access to parts of

Re: Why the index is not used ?

Hi Didier, I’m sorry to tell you that you are probably doing something (ie handling/storing credit cards) which would mean you have to comply with PCI DSS requirements. As such you should probably have a QSA (auditor) who you can run any proposed solution by (so you know they will be comfortab

Re: Why the index is not used ?

I haven’t looked up what pgp_sym_encrypt() does but assuming it does encryption the way you should be for credit card data then it will be using a random salt and the same input value won’t encrypt to the same output value so WHERE cc=pgp_sym_encrypt('test value 32', 'motdepasse'); woul

pg_stats avg_width and null_frac

Can anyone confirm that the "avg_width" reported in the pg_stats is the avg_width not including any null rows? ie if a field had: avg_width: 6 null_frac: 0.5 Then - 50% of the rows would be empty for this field - The other 50% of the rows would have data with an avg_width of 6 bytes? (according