Encryption Options

[sud]

Hello Friends,

We are newly moving to postgres database (yet to decide if it would be an
on premise one or AWS aurora postgres). However ,  we want to understand
what  encryption / decryption techniques are available in the postgres
database.

We may have some sensitive/"personal information" (like customer name,
account number etc )stored in the database and thus we may need "data at
rest encryption", what are the options available here?

 Along with that, we want to understand, any other option to store the
specific "data attribute" itself in the database by encrypting, so it won't
be visible in clear text to anybody and decrypting the same while needed
and what would be the performance overhead of those options?

Regards
Sud

Re: How to do faster DML

[veem v]

On Fri, 16 Feb 2024 at 06:04, Peter J. Holzer  wrote:

> On 2024-02-15 16:51:56 -0700, David G. Johnston wrote:
> > On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer 
> wrote:
> > On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> > > On Tuesday, February 13, 2024, veem v  wrote:
> > >
> > > float data types rather than numeric. This will give better
> > > performance.
> > >
> > >
> > > Only use an inexact floating-point data type if you truly
> > > understand what you are getting yourself into.  Quickly getting
> > > the wrong answer isn’t tolerable solution.
> >
> > Do NOT assume that a decimal type (even if it can grow to ridiculuous
> > lengths like PostgreSQL's numeric) is exact in the mathematical
> sense.
> > It isn't. It cannot represent almost all real numbers
> >
> >
>
> Thank You.

So it looks like the use cases where we don't need precision or decimal
point values to be stored in postgres , integer data type is the way to go
without a doubt.

However in cases of precision is required, as you all mentioned there are
certain issues(rounding error etc) with "Float" data type and considering a
normal developers usage point of view, it should be the Numeric type which
we should use. I think the consistent working or functionality of an
application takes precedence over performance. And I believe , in most real
life scenarios, when we need precisions we expect them to behave
consistently across all the application and database platforms(mainly
banking industries), and thus it seems Numeric data type is the safest one
to use as a multi database platform type. Please correct me if I'm wrong.

Regards
Veem

Re: How to do faster DML

[Peter J. Holzer]

On 2024-02-15 16:51:56 -0700, David G. Johnston wrote:
> On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer  wrote:
> On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> > On Tuesday, February 13, 2024, veem v  wrote:
> >
> >     float data types rather than numeric. This will give better
> > performance.
> >
> >
> > Only use an inexact floating-point data type if you truly
> > understand what you are getting yourself into.  Quickly getting
> > the wrong answer isn’t tolerable solution.
> 
> Do NOT assume that a decimal type (even if it can grow to ridiculuous
> lengths like PostgreSQL's numeric) is exact in the mathematical sense.
> It isn't. It cannot represent almost all real numbers
> 
> 
> That is an unusual definition for exact, I wouldn't have considered the
> requirement to represent all real numbers to be included in it.
> 
> What you see with an exact type is what you get, which allows for implementing
> equality, unlike inexact which requires epsilon checking.

You can check binary fp values for equality. If they are equal, they
will compare equal. If they aren't, they won't.

What you can't expect is that the laws of commutativity, associativity,
etc. hold. If you compute a value in two different ways which should be
equivalent mathematically (e.g. a*b/c vs. a/c*b), the result may be
different and an equality test may fail. But that is also the case for
numeric (and of course integer). You might get around that by epsilon
checking, but whether that's the right thing to do depends on your
application.

And most importantly, and gets most people on the "oh noes, binary fp is
inexact" bandwagon is that decimal fractions (1/10, 1/100, ...) are not
exactly representable in binary, just like 1/3, 1/7, 1/11, ... aren't
exactly represntable in decimal. People are used the latter, but not
the former. But mathematically, that doesn't really make a difference.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: PostgreSQL DB in prod, test, debug

[Jay Stanley]

On 2024-02-15 23:51, Peter J. Holzer wrote:

On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote: On 14 Feb 2024, 
at 10:59, Simon Connah  wrote:

This is probably a stupid question so I apologies in advance.
There is no such thing.

What I think is the best way to do this is to do a pg_dump of the
database (using the --schema-only flag) and then load it into a test
only database that gets created at the start of the unit tests and
destroyed at the end. The automated tests will insert, update,
delete and select data to test if it all still works.
If the source of truth for your schema is the database, then sure.  If 
the
source of truth is a .sql file in your source code repository then you 
should

use that.


I sort of do both for one of my projects:

I originally created the SQL script by running pg_dump on a manually
constructed test database (I do have code to create the empty schema,
but I had to insert the test data manually). That script went into the
git repo.

The test files all contain a fixture which drops and recreates the test
database using that sql file.

When I add a new test case I try to make do with the existing test data.

When I need additional data for a new test case, I create a new pristine
test database using the sql file, add the new data, and then create a
new sql file using sql_dump which is then committed with the test cases.

Same for migrations: If I need to migrate the schema, I run the
migration on the test database, then dump and commit it.

This project is small enough (86 tests in 10 files) that all test cases
can use the same test data. However, I could easily use different test
data for different tests.

hp

I had a very similar issue a few years ago; our large-ish codebase needs 
a lot of suites tests (about 100) to run daily against specific database 
conditions -- each test involved specific data in around 50 tables.  At 
first we had 'source of truth' sql scripts checked into git, and with 
jenkins would run each to create a database for the test, load it with 
the exact data needed for that suite of tests, run the tests and record 
them, then drop the database.


This worked fine for a few tests but became unmanageable as more tests 
were added and the data volume increased. Instead, I created a 'master' 
script that creates one _database template_ for each suite of tests by 
creating a blank database and running those same build scripts in git, 
then disconnecting from that target database and switching it to become 
a template.  This was re-done very infrequently -- only when we wanted 
to use a different testing database, or needed to refresh test content, 
drop tests or add more tests.  That's right - we have about 100 
templates; I've found copying a template is FAR faster than reloading it 
from SQL.


When each test is run, it creates a new database from the template 
appropriate for that suite of tests.  When the test suite completes, the 
new database is dropped.  This sped up our tests by at least 2 orders of 
magnitude, and it was in a way more reliable because each test gets a 
binary copy of the test database including exactly how vacuumed each 
table is, exact state of the indexes, every block in the same place, 
etc.   Results were definitely more consistent in how long they ran.  
Note that while a database is in template mode, it cannot be changed 
(unless a DBA switches it back to non-template-mode).


This has been in production for a few years, in multiple projects, and 
hasn't really hit any issues; the one cavaet is that you can't switch a 
database to 'template mode' if anything's logged into the target.  
According to the doc, I don't see an upper limit for the number of 
template databases but there must be one - I haven't tested it with 
1000's of templates.


See doc for 'create database DBNAME template TEMPLATENAME' as well as 
'alter database DBNAME set datistemplate = true|false'.


- jay stanley
- https://cyc.com; The Next Generation of Enterprise AI

Re: Using a Conversion Table

[Peter J. Holzer]

On 2024-02-14 10:02:37 -0500, Greg Sabino Mullane wrote:
>     "Fiscal year" double precision,
> 
> This column is an INTEGER in your other table, so your schema is not even
> internally consistent! Try to use TEXT, INT, DATE and TIMESTAMPTZ whenever
> possible,

While using double precision for fiscal year is rather grotesque
overkill (smallint would be sufficient) it isn't wrong: Any value you
could conceivably want to store for a fiscal year fits nicely (with lots
of room to spare) into a double precision.

I agree that consistency would be nice, though.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: How to do faster DML

[David G. Johnston]

On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer  wrote:

> On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> > On Tuesday, February 13, 2024, veem v  wrote:
> >
> > float data types rather than numeric. This will give better
> performance.
> >
> >
> >  Only use an inexact floating-point data type if you truly understand
> what you
> > are getting yourself into.  Quickly getting the wrong answer isn’t
> tolerable
> > solution.
>
> Do NOT assume that a decimal type (even if it can grow to ridiculuous
> lengths like PostgreSQL's numeric) is exact in the mathematical sense.
> It isn't. It cannot represent almost all real numbers
>

That is an unusual definition for exact, I wouldn't have considered the
requirement to represent all real numbers to be included in it.

What you see with an exact type is what you get, which allows for
implementing equality, unlike inexact which requires epsilon checking.
That you need to round some values to the nearest exact value is true but
doesn't make represented values less exact.  But yes, numbers in computers
are complicated and require attention to use.  But not having to worry
about epsilon is still a win.

David J.

Re: PostgreSQL DB in prod, test, debug

[Peter J. Holzer]

On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote:
> > On 14 Feb 2024, at 10:59, Simon Connah  
> > wrote:
> 
> > This is probably a stupid question so I apologies in advance.
> 
> There is no such thing.
> 
> > What I think is the best way to do this is to do a pg_dump of the
> > database (using the --schema-only flag) and then load it into a test
> > only database that gets created at the start of the unit tests and
> > destroyed at the end. The automated tests will insert, update,
> > delete and select data to test if it all still works.
> 
> If the source of truth for your schema is the database, then sure.  If the
> source of truth is a .sql file in your source code repository then you should
> use that.

I sort of do both for one of my projects:

I originally created the SQL script by running pg_dump on a manually
constructed test database (I do have code to create the empty schema,
but I had to insert the test data manually). That script went into the
git repo.

The test files all contain a fixture which drops and recreates the test
database using that sql file.

When I add a new test case I try to make do with the existing test data.

When I need additional data for a new test case, I create a new pristine
test database using the sql file, add the new data, and then create a
new sql file using sql_dump which is then committed with the test cases.

Same for migrations: If I need to migrate the schema, I run the
migration on the test database, then dump and commit it.

This project is small enough (86 tests in 10 files) that all test cases
can use the same test data. However, I could easily use different test
data for different tests.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: How to do faster DML

[Peter J. Holzer]

On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> On Tuesday, February 13, 2024, veem v  wrote:
> 
> float data types rather than numeric. This will give better performance.
> 
> 
>  Only use an inexact floating-point data type if you truly understand what you
> are getting yourself into.  Quickly getting the wrong answer isn’t tolerable
> solution.

Do NOT assume that a decimal type (even if it can grow to ridiculuous
lengths like PostgreSQL's numeric) is exact in the mathematical sense.
It isn't. It cannot represent almost all real numbers. No pi or e of
course, but also no seemingly simple fractions like 1/3 or 1/7.

Unless you never divide anything, you will need to be aware of the
rounding behaviour, just as you have to with binary floating point
types. And if you use a finite precision you will also have to deal with
rounding on multiplication (and possibly even addition and subtraction,
if you use different precisions).

Almost 40 years ago, our numerical methods professor started his first
lecture with the sentence "You can use a computer for anything - except
computing". He spent the rest of the semester proving himself wrong,
of course, but computing correctly is hard - and choosing a data type
which more closely mimics the way we learn to compute in primary school
doesn't necessarily make it easier. Mostly it just makes it harder to
spot the errors ;-).

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: How to do faster DML

[Greg Sabino Mullane]

I really worry you are overthinking this. The only real concern is going
from INT to BIGINT, jumping from 4 to 8 bytes or storage. That really
covers 99% of real world cases, and the canonical advice is to start with
BIGINT if you ever think your rows are going to be numbered in the
billions. Also, a NUMERIC can cause a table rewrite - try changing the
scale, not just the precision. And if your scale is 0, why are you using
numeric? :)

Cheers,
Greg

Re: How to do faster DML

[David G. Johnston]

On Thu, Feb 15, 2024 at 12:18 PM veem v  wrote:

>
> So one learning for me, i.e. one of the downside of fixed length data type
> is, with fixed length data types any future changes to it , will be a full
> table rewrite. And thus this sort of change for big tables will be a
> nightmare.
>
>
Yes, using the wording in the documentation, there is no such thing as a
"binary coercible" change for a fixed-width data type.  Or for most types
really.  Text is one of the few for which the typmod has no meaning and
there are variant spellings like varchar that allow for the underlying
storage representation to be the same.

David J.

Re: Trouble with v16 new CREATEROLE semantic

[Bruce Momjian]

On Thu, Feb 15, 2024 at 06:07:51PM +0100, Dominique Devienne wrote:
> When I read about v16, I thought great, this fits our intent, a single "owner"
> ROLE with CREATEROLE which is limited to administering only the ROLEs it
> created itself. I've always been bothered by the mega-power of CREATEROLE. But
> now that we're actually trying to use it, I'm a bit worried. Can someone
> explain (or guess) what I'm missing here?

Well, have you read the PG 16 release notes?

https://www.postgresql.org/docs/current/release-16.html

You might also want to review my unreleased slide deck about users:

https://momjian.us/main/writings/pgsql/user.pdf

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Only you can decide what is important to you.

Re: How to do faster DML

[veem v]

On Thu, 15 Feb 2024 at 22:40, Adrian Klaver 
wrote:

> On 2/15/24 09:00, Greg Sabino Mullane wrote:
> > On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > That is a mixed bag:
> >
> >
> > Ha! Good point. Our contrived example table does suffer from that, so
> > perhaps the test should be:
> >
> > create table int_test(c1 int, c2 int);
>
> Alright now I see:
>
> test=# create table int_test(c1 int, c2 int);
> CREATE TABLE
>
> test=# select pg_relation_filenode('int_test');
>   pg_relation_filenode
> --
>  70021
> (1 row)
>
>
> test=# insert into int_test select a, a+1  from generate_series(1,
> 1, 1) as t(a);
> INSERT 0 1
>
> test=# select pg_relation_size('int_test');
>   pg_relation_size
> --
> 368640
> (1 row)
>
> test=# alter table int_test alter column c2 set data type bigint;
> ALTER TABLE
>
> test=# select pg_relation_filenode('int_test');
>   pg_relation_filenode
> --
>  70024
> (1 row)
>
> test=# select pg_relation_size('int_test');
>   pg_relation_size
> --
> 450560
> (1 row)
>
>
Thank you.

Did a similar test as below using DB fiddle. Same results for fixed length
data type i.e the size is getting increased. However for variable
length types (like numeric) , it remains the same, so it must be just
metadata change and thus should be quick enough even for a big table.
So one learning for me, i.e. one of the downside of fixed length data type
is, with fixed length data types any future changes to it , will be a full
table rewrite. And thus this sort of change for big tables will be a
nightmare.

https://dbfiddle.uk/_gNknf0D

Regards
Veem

Re: Trouble with v16 new CREATEROLE semantic

[Pavel Luzanov]

Hi,

On 15.02.2024 20:07, Dominique Devienne wrote:

And now with V16.1 ===


ddevienne=> create role zowner nologin createrole; -- owner of app's 
schemas and manager of related roles

CREATE ROLE



ddevienne=> set role zowner;
ERROR:  permission denied to set role "zowner"
ddevienne=> select roleid::regrole::text, member::regrole::text, 
grantor::regrole::text, admin_option, set_option, inherit_option from 
pg_auth_members where roleid::regrole::text like 'z%' or 
member::regrole::text like 'z%';
 roleid |  member   | grantor  | admin_option | set_option | 
inherit_option

+---+--+--++
 zowner | ddevienne | postgres | t            | f          | f
(1 row)


You can use new psql command \drg for this query.


(2 rows)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- 
means to become zowner explicitly

ERROR:  permission denied to grant role "zowner"
DETAIL:  Only roles with the ADMIN option on role "zowner" may grant 
this role.

ddevienne=>

So first surprise in V16. Despite having admin_option, from being the 
creator of the zowner role, I can't SET ROLE to it. I have to 
explicitly add the SET privilege.


Yes, but you can automate it with setting new parameter createrole_self_grant.

postgres@demo=# create role ddevienne login createrole;
CREATE ROLE
postgres@demo=# alter role ddevienne set createrole_self_grant = 'INHERIT, SET';
ALTER ROLE
postgres@demo=# \c - ddevienne
You are now connected to database "demo" as user "ddevienne".
ddevienne@demo=> create role zowner nologin createrole;
CREATE ROLE
ddevienne@demo=> \drg ddevienne
   List of role grants
 Role name | Member of |   Options|  Grantor
---+---+--+---
 ddevienne | zowner| INHERIT, SET | ddevienne
 ddevienne | zowner| ADMIN| postgres
(2 rows)

ddevienne@demo=> set role zowner;
SET

And then, when ddevienne SET ROLE's to zowner, and tries to create 
zadmin *and* add it at the same time as a member of zowner (the 
current_role), it fails.


So it looks like, despite ddevienne having admin_option on zowner, 
because it is on a "different line" than the set_option, it still 
cannot add members in zowner???


Behaviorchanged    for 
security reasons in v15. From Release Notes:
    > Remove the default ADMIN OPTION privilege a login role has on its own 
role membership (Robert Haas)
    > Previously, a login role could add/remove members of its own role, even 
without ADMIN OPTION privilege.

Zowner can create zadmin, but no way to grant membership in itself.

What you can do is create a role zadmin by ddevienne:

ddevienne@demo=> reset role;
RESET
ddevienne@demo=> create role zadmin nologin noinherit;
CREATE ROLE
ddevienne@demo=> grant zowner to zadmin with inherit true, set true;
GRANT ROLE
ddevienne@demo=> \drg zadmin
   List of role grants
 Role name | Member of |   Options|  Grantor
---+---+--+---
 zadmin| zowner| INHERIT, SET | ddevienne
(1 row)

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: How to do faster DML

[Adrian Klaver]

On 2/15/24 09:00, Greg Sabino Mullane wrote:
On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


That is a mixed bag:


Ha! Good point. Our contrived example table does suffer from that, so 
perhaps the test should be:


create table int_test(c1 int, c2 int);


Alright now I see:

test=# create table int_test(c1 int, c2 int);
CREATE TABLE

test=# select pg_relation_filenode('int_test');
 pg_relation_filenode
--
70021
(1 row)


test=# insert into int_test select a, a+1  from generate_series(1, 
1, 1) as t(a);

INSERT 0 1

test=# select pg_relation_size('int_test');
 pg_relation_size
--
   368640
(1 row)

test=# alter table int_test alter column c2 set data type bigint;
ALTER TABLE

test=# select pg_relation_filenode('int_test');
 pg_relation_filenode
--
70024
(1 row)

test=# select pg_relation_size('int_test');
 pg_relation_size
--
   450560
(1 row)



Cheers,
Greg



--
Adrian Klaver
adrian.kla...@aklaver.com

Trouble with v16 new CREATEROLE semantic

[Dominique Devienne]

Hi. Our "app" depends on many ROLEs and SCHEMAs, and manages GRANTs between
those.
Typically, each "instance" of our app lives in its own DB, and uses a
naming convention for its ROLEs, to make those role names unique
per-app-instance. All the app roles are created by a single master role
(the "owner" role), with CREATEROLE, and that master role also owns all the
schemas (of that app's instance, also using a (schema) naming convention
similar to the role's one, despite schemas not being cluster-wide like
roles).

We started this on v12. No problem with v14. But with v16, we're running
into trouble...
So I've tried to replicate our setup in a single demo, on both v14 and v16.

with V14.8 ===

ddevienne=> select roleid::regrole::text, member::regrole::text,
admin_option from pg_auth_members where roleid::regrole::text like 'z%' or
member::regrole::text like 'z%';
 roleid | member | admin_option
++--
(0 rows)
ddevienne=> create role zowner nologin createrole; -- owner of app's
schemas and manager of related roles
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid | member | grantor | admin_option
++-+--
(0 rows)
ddevienne=> grant zowner to ddevienne;
GRANT ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid |  member   |  grantor  | admin_option
+---+---+--
 zowner | ddevienne | ddevienne | f
(1 row)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- means
to become zowner explicitly
CREATE ROLE
ddevienne=> create role zuser nologin; -- has grants on zowner's schemas
CREATE ROLE
ddevienne=> create role zuser_a in role zuser;
CREATE ROLE
ddevienne=> create role zuser_b in role zuser, zadmin;
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid |  member   |  grantor  | admin_option
+---+---+--
 zowner | ddevienne | ddevienne | f
 zowner | zadmin| zowner| f
 zuser  | zuser_a   | zowner| f
 zuser  | zuser_b   | zowner| f
 zadmin | zuser_b   | zowner| f
(5 rows)


And now with V16.1 ===

ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid | member | grantor | admin_option
++-+--
(0 rows)
ddevienne=> create role zowner nologin createrole; -- owner of app's
schemas and manager of related roles
CREATE ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option from pg_auth_members where
roleid::regrole::text like 'z%' or member::regrole::text like 'z%';
 roleid |  member   | grantor  | admin_option
+---+--+--
 zowner | ddevienne | postgres | t
(1 row)
ddevienne=> set role zowner;
ERROR:  permission denied to set role "zowner"
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option, set_option, inherit_option from
pg_auth_members where roleid::regrole::text like 'z%' or
member::regrole::text like 'z%';
 roleid |  member   | grantor  | admin_option | set_option | inherit_option
+---+--+--++
 zowner | ddevienne | postgres | t| f  | f
(1 row)
ddevienne=> grant zowner to ddevienne;
GRANT ROLE
ddevienne=> select roleid::regrole::text, member::regrole::text,
grantor::regrole::text, admin_option, set_option, inherit_option from
pg_auth_members where roleid::regrole::text like 'z%' or
member::regrole::text like 'z%';
 roleid |  member   |  grantor  | admin_option | set_option | inherit_option
+---+---+--++
 zowner | ddevienne | postgres  | t| f  | f
 zowner | ddevienne | ddevienne | f| t  | t
(2 rows)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- means
to become zowner explicitly
ERROR:  permission denied to grant role "zowner"
DETAIL:  Only roles with the ADMIN option on role "zowner" may grant this
role.
ddevienne=>

So first surprise in V16. Despite having admin_option, from being the
creator of the zowner role, I can't SET ROLE to it. I have to explicitly
add the SET privilege.

And then, when ddevienne SET ROLE's to zowner, and tries to 

Re: How to do faster DML

[Greg Sabino Mullane]

On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver 
wrote:

> That is a mixed bag:
>

Ha! Good point. Our contrived example table does suffer from that, so
perhaps the test should be:

create table int_test(c1 int, c2 int);

Cheers,
Greg

Re: How to do faster DML

[Adrian Klaver]

On 2/15/24 08:16, Greg Sabino Mullane wrote:

So as I also tested the same as you posted, there has been no change
in "ctid" , when I altered the column data type from 'int' to
'bigint' in the table, so that means full table rewriting
won't happen in such a scenario.


No it was definitely rewritten - do not depend on the ctid to verify 
that. Take our word for it, or 
use*pg_relation_filenode('int_test');* before and after, as well as 
*pg_relation_size('int_test')*;


That is a mixed bag:

test=# select pg_relation_filenode('int_test');
 pg_relation_filenode
--
6
(1 row)

test=# select pg_relation_size('int_test');
 pg_relation_size
--
   368640
(1 row)

test=# alter table int_test alter column int_fld set data type bigint;
ALTER TABLE
test=# select pg_relation_filenode('int_test');
 pg_relation_filenode
--
70002
(1 row)

test=# select pg_relation_size('int_test');
 pg_relation_size
--
   368640



Cheers,
Greg



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to do faster DML

[Greg Sabino Mullane]

>
> So as I also tested the same as you posted, there has been no change in
> "ctid" , when I altered the column data type from 'int' to 'bigint' in the
> table, so that means full table rewriting won't happen in such a scenario.


No it was definitely rewritten - do not depend on the ctid to verify that.
Take our word for it, or use* pg_relation_filenode('int_test');* before and
after, as well as *pg_relation_size('int_test')*;

Cheers,
Greg

Re: pg_stat_activity.query_id <-> pg_stat_statements.queryid

[Julien Rouhaud]

Hi,

On Thu, Feb 15, 2024 at 10:52:42AM +, Daniel Westermann (DWE) wrote:
>
> quick question: What would be the cases for a query_id in pg_stat_activity
> not showing up in pg_stat_statements.queryid assuming pg_stat_statements.max
> is not yet reached?

Well, first the query_id in pg_stat_activity is only the query_id of top-level
statements, that may still be running while pg_stat_statements only show
statistics of already executed statements (top level only or not depends on
config).

You may also be running some utility statements, which will display a query_id
in pg_stat_activity while pg_stat_statements will ignore them by default IIRC.

No other idea apart from that.

pg_stat_activity.query_id <-> pg_stat_statements.queryid

[Daniel Westermann (DWE)]

Hi,

quick question: What would be the cases for a query_id in pg_stat_activity not 
showing up in pg_stat_statements.queryid assuming pg_stat_statements.max is not 
yet reached?

Regards
Daniel

Re: Query regarding function cleanup in extension upgrade path

[Daniel Gustafsson]

> On 14 Feb 2024, at 21:56, Tom Lane  wrote:
> Ayush Vatsa  writes:

>> 2. While going through the contrib folder I find that in the regress test
>> there are two .out files with respect to a single .sql file, example
>> citext.out and citext_1.out wrt citext.sql. Why is it so? Even in git blame
>> , I couldn't find much!
> 
> We use that when the expected test output is environment-dependent.
> If the diff between the .out files isn't pretty self-explanatory,
> you can try checking the git log for the "_1.out" file to see why it
> was created.

Some further details on this may be gleaned from the pg_regress source code
where it checks for an alternate file (_1 through _9) in case the main expected
output file created a diff:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/pg_regress.c;h=c894005dac06bc233896520c83c18e51af8ace9c;hb=HEAD#l1450

--
Daniel Gustafsson