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

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

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 ?

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

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: 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

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: 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

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

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

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