Re: RowLock and multiple transactions

[Laurenz Albe]

On Wed, 2024-02-14 at 23:52 +0100, Hannes Erven wrote:
> when "SELECT .. WHERE .. FOR NO KEY UPDATE" is used synchronize access, 
> and the transaction holding the lock completes, how does PostgreSQL 
> decide /which one/ of multiple waiting transactions will the lock be 
> granted to next?
> 
> In my testing (on Ubuntu 16.1-1.pgdg20.04+1, 64bit) with a real-world 
> application (that acquires multiple locks on a number of relations) it 
> seems that it is always the transaction that attempted to lock _last_ ... ?
> I thought that would most probably be random, or if it was not, the 
> order would have been explained in the docs?

Transactions queue behind a lock, and they get the lock in a "first come,
first served" order.

Yours,
Laurenz Albe

Re: How to do faster DML

[David G. Johnston]

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.

David J.

Re: How to do faster DML

[Ron Johnson]

On Wed, Feb 14, 2024 at 11:58 PM veem v  wrote:

>
>
> On Thu, 15 Feb 2024 at 00:43, Adrian Klaver 
> wrote:
>
>> It depends:
>>
>> https://www.postgresql.org/docs/current/sql-altertable.html
>>
>> "Adding a column with a volatile DEFAULT or changing the type of an
>> existing column will require the entire table and its indexes to be
>> rewritten. As an exception, when changing the type of an existing column,
>> if the USING clause does not change the column contents and the old type
>> is either binary coercible to the new type or an unconstrained domain over
>> the new type, a table rewrite is not needed. However, indexes must always
>> be rebuilt unless the system can verify that the new index would be
>> logically equivalent to the existing one. For example, if the collation for
>> a column has been changed, an index rebuild is always required because the
>> new sort order might be different. However, in the absence of a collation
>> change, a column can be changed from text to varchar (or vice versa)
>> without rebuilding the indexes because these data types sort identically.
>> Table and/or index rebuilds may take a significant amount of time for a
>> large table; and will temporarily require as much as double the disk space.
>>
>> "
>>
>>
>> create table int_test(int_fld integer);
>>
>> insert into int_test select * from generate_series(1, 1, 1);
>>
>>
>> select ctid, int_fld from int_test ;
>>
>> ctid   | int_fld
>> --+-
>>  (0,1)|   1
>>  (0,2)|   2
>>  (0,3)|   3
>>  (0,4)|   4
>>  (0,5)|   5
>>  (0,6)|   6
>>  (0,7)|   7
>>  (0,8)|   8
>>  (0,9)|   9
>>  (0,10)   |  10
>>
>>
>> alter table int_test alter column int_fld set data type bigint;
>>
>> select ctid, int_fld from int_test ;
>>
>>   ctid   | int_fld
>> --+-
>>  (0,1)|   1
>>  (0,2)|   2
>>  (0,3)|   3
>>  (0,4)|   4
>>  (0,5)|   5
>>  (0,6)|   6
>>  (0,7)|   7
>>  (0,8)|   8
>>  (0,9)|   9
>>  (0,10)   |  10
>>
>> update int_test set  int_fld = int_fld;
>>
>> select ctid, int_fld from int_test  order by int_fld;
>>
>> (63,1)|   1
>>  (63,2)|   2
>>  (63,3)|   3
>>  (63,4)|   4
>>  (63,5)|   5
>>  (63,6)|   6
>>  (63,7)|   7
>>  (63,8)|   8
>>  (63,9)|   9
>>  (63,10)   |  10
>>
>>
>> Where ctid is:
>>
>> https://www.postgresql.org/docs/current/ddl-system-columns.html
>>
>>
>>  Thank you so much.
> 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.
>

It happened when I altered columns from INTEGER to BIGINT.  How do I know?

The disk filled up.

>

Re: How to do faster DML

[veem v]

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

> It depends:
>
> https://www.postgresql.org/docs/current/sql-altertable.html
>
> "Adding a column with a volatile DEFAULT or changing the type of an
> existing column will require the entire table and its indexes to be
> rewritten. As an exception, when changing the type of an existing column,
> if the USING clause does not change the column contents and the old type
> is either binary coercible to the new type or an unconstrained domain over
> the new type, a table rewrite is not needed. However, indexes must always
> be rebuilt unless the system can verify that the new index would be
> logically equivalent to the existing one. For example, if the collation for
> a column has been changed, an index rebuild is always required because the
> new sort order might be different. However, in the absence of a collation
> change, a column can be changed from text to varchar (or vice versa)
> without rebuilding the indexes because these data types sort identically.
> Table and/or index rebuilds may take a significant amount of time for a
> large table; and will temporarily require as much as double the disk space.
>
> "
>
>
> create table int_test(int_fld integer);
>
> insert into int_test select * from generate_series(1, 1, 1);
>
>
> select ctid, int_fld from int_test ;
>
> ctid   | int_fld
> --+-
>  (0,1)|   1
>  (0,2)|   2
>  (0,3)|   3
>  (0,4)|   4
>  (0,5)|   5
>  (0,6)|   6
>  (0,7)|   7
>  (0,8)|   8
>  (0,9)|   9
>  (0,10)   |  10
>
>
> alter table int_test alter column int_fld set data type bigint;
>
> select ctid, int_fld from int_test ;
>
>   ctid   | int_fld
> --+-
>  (0,1)|   1
>  (0,2)|   2
>  (0,3)|   3
>  (0,4)|   4
>  (0,5)|   5
>  (0,6)|   6
>  (0,7)|   7
>  (0,8)|   8
>  (0,9)|   9
>  (0,10)   |  10
>
> update int_test set  int_fld = int_fld;
>
> select ctid, int_fld from int_test  order by int_fld;
>
> (63,1)|   1
>  (63,2)|   2
>  (63,3)|   3
>  (63,4)|   4
>  (63,5)|   5
>  (63,6)|   6
>  (63,7)|   7
>  (63,8)|   8
>  (63,9)|   9
>  (63,10)   |  10
>
>
> Where ctid is:
>
> https://www.postgresql.org/docs/current/ddl-system-columns.html
>
>
>  Thank you so much.
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.

Regards
Veem

Re: Accessing parameters of a prepared query inside an FDW

[David G. Johnston]

On Wednesday, February 14, 2024, Adam Fletcher 
wrote:
>
>
> Is it possible to get the parameterized prepared query inside an FDW such
> that it can be prepared/bind'd/execute'd on the receiving end of the FDW?
>
> For example, if I `PREPARE stmt(int) AS SELECT * from fdwrapped_tbl where
> pk = $1;` then `execute stmt(1);` I want my FDW be aware that the query was
> prepared.
>

That isn’t how the separation of responsibilities works in PostgreSQL.
Execute is capable of producing a custom plan where instead of adding in
parameters and then planning around those unknowns the newly created plan
uses the supplied constants while planning.

I do suspect that if a generic plan is chosen you will see the expected
parse nodes and can thus build a generic access plan to your foreign server
accordingly.

You can control this for ease of testing via plan_cache_mode

https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE

David J.

Accessing parameters of a prepared query inside an FDW

[Adam Fletcher]

Hi Folks,

Is it possible to get the parameterized prepared query inside an FDW such
that it can be prepared/bind'd/execute'd on the receiving end of the FDW?

For example, if I `PREPARE stmt(int) AS SELECT * from fdwrapped_tbl where
pk = $1;` then `execute stmt(1);` I want my FDW be aware that the query was
prepared.

Right now, if given the above, and I walk through the postgres_fdw code and
output the parse tree from root->parse->query (the PlannerInfo node), I see
no PARAM nodes - inside the OPEXPR is just the VAR & CONST (in this case,
the CONST is 1). Note that if I call ereport() I do see the correct
prepared statement (eg, with the $1) in the output (as debug_query_string
has that statement in it).

I cannot find an example of an FDW that supports passing on prepared
statements.  Any help appreciated!

-Adam

RowLock and multiple transactions

[Hannes Erven]

Hi,


when "SELECT .. WHERE .. FOR NO KEY UPDATE" is used synchronize access, 
and the transaction holding the lock completes, how does PostgreSQL 
decide /which one/ of multiple waiting transactions will the lock be 
granted to next?


In my testing (on Ubuntu 16.1-1.pgdg20.04+1, 64bit) with a real-world 
application (that acquires multiple locks on a number of relations) it 
seems that it is always the transaction that attempted to lock _last_ ... ?
I thought that would most probably be random, or if it was not, the 
order would have been explained in the docs?



Thank you for any insights...
Best regards

-hannes

Re: Query regarding function cleanup in extension upgrade path

[Tom Lane]

Ayush Vatsa  writes:
> Just two follow up queries regarding this -
> 1. Suppose I created a new version 1.1 in which I reduce the C function to
> throw an error then ship it, will users get the .c latest file immediately
> and their old function will throw error but they have to use ALTER
> EXTENSION xyz UPGRADE TO 1.1 to use the latest objects defined in 1.1.sql.
> Is this the correct understanding?

Yes, if you do it like that then once they install the new shlib their
function will be broken until they do ALTER EXTENSION UPGRADE.

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

regards, tom lane

Re: PITR

[Greg Sabino Mullane]

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem  wrote:

>  I checked the Postgresql.conf file and can't find the
> appropriate parameter to set the target time.
>

It is set inside the postgresql.conf file. Unless you have modified it,
there is a section in there called "Recovery Target" which has a handful of
commented-out variables starting with "recovery_"

Cheers,
Greg

Re: PITR

[Ron Johnson]

On Wed, Feb 14, 2024 at 1:39 PM Yongye Serkfem  wrote:

> Hi,
> I hope you are all doing well. I am trying to do PITR on Postgresql v12.
> Now that the recovery.conf file is not available in this version, where
> should I set the recovery_target_time? I checked the Postgresql.conf file
> and can't find the appropriate parameter to set the target time.
>

Consider using PgBackRest.  It's multithreaded, and you specify the
"restore-from start point" and "recover until" time on the command line.
It then handles everything for you, besides the final "pg_ctl start -D ...".

Re: How to do faster DML

[Adrian Klaver]

On 2/14/24 10:11 AM, veem v wrote:


On Wed, 14 Feb, 2024, 10:30 am Ron Johnson,  
wrote:


On Tue, Feb 13, 2024 at 4:17 PM veem v  wrote:
[sni[]

One question here, if we have defined one column as a
fixed length data type "integer" and slowly we noticed the
length of data keeps increasing (in case of a sequence
generated PK column which will keep increasing), and we want
to alter the column to "bigint" now. In such scenario, will it
append/pad the additional spaces to all the existing values
which were already stored  with integer type initially in the
table. And that would be then an increase to the existing
table storage. Please correct me if I'm wrong.


ALTER TABLE foo ALTER COLUMN bar BIGINT; rewrites the whole
table.  Do yourself a favor, and start with BIGINT.



Not yet tried, but that's interesting. I was initially thinking as it 
will be increasing the length, so it would be just a metadata change 
and finish within seconds.



It depends:

https://www.postgresql.org/docs/current/sql-altertable.html

"Adding a column with a volatile |DEFAULT| or changing the type of an 
existing column will require the entire table and its indexes to be 
rewritten. As an exception, when changing the type of an existing 
column, if the |USING| clause does not change the column contents and 
the old type is either binary coercible to the new type or an 
unconstrained domain over the new type, a table rewrite is not needed. 
However, indexes must always be rebuilt unless the system can verify 
that the new index would be logically equivalent to the existing one. 
For example, if the collation for a column has been changed, an index 
rebuild is always required because the new sort order might be 
different. However, in the absence of a collation change, a column can 
be changed from |text| to |varchar| (or vice versa) without rebuilding 
the indexes because these data types sort identically. Table and/or 
index rebuilds may take a significant amount of time for a large table; 
and will temporarily require as much as double the disk space.


"


create table int_test(int_fld integer);

insert into int_test select * from generate_series(1, 1, 1);


select ctid, int_fld from int_test ;

ctid   | int_fld
--+-
 (0,1)    |   1
 (0,2)    |   2
 (0,3)    |   3
 (0,4)    |   4
 (0,5)    |   5
 (0,6)    |   6
 (0,7)    |   7
 (0,8)    |   8
 (0,9)    |   9
 (0,10)   |  10


alter table int_test alter column int_fld set data type bigint;

select ctid, int_fld from int_test ;

  ctid   | int_fld
--+-
 (0,1)    |   1
 (0,2)    |   2
 (0,3)    |   3
 (0,4)    |   4
 (0,5)    |   5
 (0,6)    |   6
 (0,7)    |   7
 (0,8)    |   8
 (0,9)    |   9
 (0,10)   |  10

update int_test set  int_fld = int_fld;

select ctid, int_fld from int_test  order by int_fld;

(63,1)    |   1
 (63,2)    |   2
 (63,3)    |   3
 (63,4)    |   4
 (63,5)    |   5
 (63,6)    |   6
 (63,7)    |   7
 (63,8)    |   8
 (63,9)    |   9
 (63,10)   |  10


Where ctid is:

https://www.postgresql.org/docs/current/ddl-system-columns.html


"

|ctid|

   The physical location of the row version within its table. Note that
   although the |ctid| can be used to locate the row version very
   quickly, a row's |ctid| will change if it is updated or moved by
   |VACUUM FULL|. Therefore |ctid| is useless as a long-term row
   identifier. A primary key should be used to identify logical rows.

"



 But as you mentioned, it seems to be the effect of "fixed length data 
type" which is why it's going to rewrite whole table even we just 
increases the column length. Hope it won't be the case in variable 
length data type.



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

PITR

[Yongye Serkfem]

Hi,
I hope you are all doing well. I am trying to do PITR on Postgresql v12.
Now that the recovery.conf file is not available in this version, where
should I set the recovery_target_time? I checked the Postgresql.conf file
and can't find the appropriate parameter to set the target time.
I'm looking forward to reading your thoughts.

Best regards
Yong Serkfem

Re: How to do faster DML

[veem v]

On Wed, 14 Feb, 2024, 10:30 am Ron Johnson,  wrote:

> On Tue, Feb 13, 2024 at 4:17 PM veem v  wrote:
> [sni[]
>
>> One question here, if we have defined one column as a fixed length data
>> type "integer" and slowly we noticed the length of data keeps increasing
>> (in case of a sequence generated PK column which will keep increasing), and
>> we want to alter the column to "bigint" now. In such scenario, will it
>> append/pad the additional spaces to all the existing values which were
>> already stored  with integer type initially in the table. And that would be
>> then an increase to the existing table storage. Please correct me if I'm
>> wrong.
>>
>
> ALTER TABLE foo ALTER COLUMN bar BIGINT; rewrites the whole table.  Do
> yourself a favor, and start with BIGINT.
>


Not yet tried, but that's interesting. I was initially thinking as it will
be increasing the length, so it would be just a metadata change and finish
within seconds.

 But as you mentioned, it seems to be the effect of "fixed length data
type" which is why it's going to rewrite whole table even we just increases
the column length. Hope it won't be the case in variable length data type.

>

Re: Query regarding function cleanup in extension upgrade path

[Ayush Vatsa]

Hi Tom thanks for the answer,
Just two follow up queries regarding this -
1. Suppose I created a new version 1.1 in which I reduce the C function to
throw an error then ship it, will users get the .c latest file immediately
and their old function will throw error but they have to use ALTER
EXTENSION xyz UPGRADE TO 1.1 to use the latest objects defined in 1.1.sql.
Is this the correct understanding?
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!

Regards
Ayush Vatsa
Amazon Web Services (AWS)

On Wed, 14 Feb 2024 at 22:07, Tom Lane  wrote:

> Ayush Vatsa  writes:
> > To ask the question let me give a hypothetical example:-
> > Suppose we have an extension named xyz with version 1.0. It has
> > xyz--1.0.sql and xyz.c file. I have declared a function named fun() in
> the
> > xyz--1.0.sql file and its definition in the xyz.c file.
> > Now I want to drop this function in the next upgrade i.e. xyz--1.0--1.1
> so
> > I will use DROP FUNCTION fun(); in it and remove the definition from the
> > xyz.c file.
> > Here my doubt is wouldn't xyz--1.0 complain about the missing definition
> of
> > fun() and if yes how can I clean up my function definition in the xyz.c
> > file?
>
> Yeah, you can't really remove the C extern symbol ever.  You can
> reduce the C function to a stub that just throws a not-supported
> error, perhaps, but your users won't necessarily appreciate that.
> It's usually better to make the shlib capable of supporting both
> the 1.0 and 1.1 APIs, so that users aren't forced into updating
> the extension's SQL declarations immediately.
>
> If you look at the standard contrib modules, you'll find a number
> of cases where there are backwards-compatibility functions that
> just exist to support people who're still using an older version
> of the extension's SQL declarations.  Those are likely to remain
> there indefinitely.
>
> regards, tom lane
>

Re: PostgreSQL DB in prod, test, debug

[Adrian Klaver]

On 2/14/24 02:38, Erik Wienhold wrote:

On 2024-02-14 10:59 +0100, Simon Connah wrote:




The database schema should be defined by migration scripts that you also
check into version control with the rest of your application sources.
Some people also prefer a separate repository just for the database
schema, depending on how tightly coupled database and application are,
or if there are other applications relying on the database schema.

I use Sqitch[1] which works well if you want to track an existing
database schema.  Alembic is popular in Python but I don't know how it
works with an existing schema because Alembic migrations are usually not
written in plain SQL.


Just to be clear you can start from scratch with Sqitch. The only thing 
that needs to be done outside of it is the original CREATE DATABASE 
statement. After that you can build the contents of the database from 
the beginning. At any point you can move forward(deploy) or 
backward(revert). For your use case the important feature is target, 
where each target is a different instance of the database. In your case 
prod, test and debug. This allow you to make changes in test/debug and 
when they are verified good deploy them to prod.




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

Re: Query regarding function cleanup in extension upgrade path

[Tom Lane]

Ayush Vatsa  writes:
> To ask the question let me give a hypothetical example:-
> Suppose we have an extension named xyz with version 1.0. It has
> xyz--1.0.sql and xyz.c file. I have declared a function named fun() in the
> xyz--1.0.sql file and its definition in the xyz.c file.
> Now I want to drop this function in the next upgrade i.e. xyz--1.0--1.1 so
> I will use DROP FUNCTION fun(); in it and remove the definition from the
> xyz.c file.
> Here my doubt is wouldn't xyz--1.0 complain about the missing definition of
> fun() and if yes how can I clean up my function definition in the xyz.c
> file?

Yeah, you can't really remove the C extern symbol ever.  You can
reduce the C function to a stub that just throws a not-supported
error, perhaps, but your users won't necessarily appreciate that.
It's usually better to make the shlib capable of supporting both
the 1.0 and 1.1 APIs, so that users aren't forced into updating
the extension's SQL declarations immediately.

If you look at the standard contrib modules, you'll find a number
of cases where there are backwards-compatibility functions that
just exist to support people who're still using an older version
of the extension's SQL declarations.  Those are likely to remain
there indefinitely.

regards, tom lane

Query regarding function cleanup in extension upgrade path

[Ayush Vatsa]

Hi PostgreSQL Community,
I have been working on a few extensions but got confused in the upgrade
scenario.
To ask the question let me give a hypothetical example:-
Suppose we have an extension named xyz with version 1.0. It has
xyz--1.0.sql and xyz.c file. I have declared a function named fun() in the
xyz--1.0.sql file and its definition in the xyz.c file.
Now I want to drop this function in the next upgrade i.e. xyz--1.0--1.1 so
I will use DROP FUNCTION fun(); in it and remove the definition from the
xyz.c file.
Here my doubt is wouldn't xyz--1.0 complain about the missing definition of
fun() and if yes how can I clean up my function definition in the xyz.c
file?
I had earlier asked the same question in DBS StackExchange

but didn't get any reply hence trying my luck here.

Regards
Ayush Vatsa
Amazon Web Services (AWS)

Re: Postgres pg_cron extension

[Ron Johnson]

On Wed, Feb 14, 2024 at 10:23 AM Greg Sabino Mullane 
wrote:

> Yes, definitely the wrong forum. RDS is not Postgres, and "parameter
> groups" is an Amazon thing. A quick web search would seem to indicate that
> the default group is replaced by the custom one, rather than enhancing it.
> But really, this is a simple "try it and see" sort of task.
>
>
> https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/parameter-groups-overview.html
>

Parameter Groups are AWS' name for, and clunky web implementation of,
postgresql.conf.  You can use the same Parameter Group for multiple
instances, or you can create a different PaGr for each instance.

(Modifying a PaGr via the aws cli made it tolerably useful.)

Re: How should we design our tables and indexes

[Greg Sabino Mullane]

On Tue, Feb 13, 2024 at 2:26 PM veem v  wrote:

> Can the optimizer, only scan the TABLE1  using ACCESS criteria "
> TABLE1.MID in ()" or "TABLE1.CID in ()" which will be catered by
> two different index i.e one index on column "MID" and other on column "CID"?
>

Yes:

greg=# create table t1(pr_id int generated always as identity primary key,
mid int, cid int);
CREATE TABLE
greg=# insert into t1(mid,cid) select random()*12345, random()*12345 from
generate_series(1,123456);
INSERT 0 123456
greg=# create index t1_mid on t1(mid);
CREATE INDEX
greg=# create index t1_cid on t1(cid);
CREATE INDEX
greg=# analyze t1;
ANALYZE
greg=#  explain select * from t1 where mid in (1,2,3,4) and cid IN
(5,6,7,8);
   QUERY PLAN
-
 Bitmap Heap Scan on t1  (cost=50.03..109.55 rows=49 width=12)
   Recheck Cond: ((cid = ANY ('{5,6,7,8}'::integer[])) AND (mid = ANY
('{1,2,3,4}'::integer[])))
   ->  BitmapAnd  (cost=50.03..50.03 rows=49 width=0)
 ->  Bitmap Index Scan on t1_cid  (cost=0.00..24.88 rows=2469
width=0)
   Index Cond: (cid = ANY ('{5,6,7,8}'::integer[]))
 ->  Bitmap Index Scan on t1_mid  (cost=0.00..24.88 rows=2469
width=0)
   Index Cond: (mid = ANY ('{1,2,3,4}'::integer[]))

It can utilize other columns as access criteria those used in join
> conditions like MID, PR_ID, in which case a composite index on  the
> columns(CID,PR_ID) (MID, PR_ID) will provide better selectivity and faster
> access?
>

If you query on the primary key, it's going to use the associated PK index,
not a composite one in which the PK is buried. But try creating the sample
table t1 above yourself and play around with the various indexes and query
combinations.

Cheers,
Greg

Re: Postgres pg_cron extension

[Greg Sabino Mullane]

Yes, definitely the wrong forum. RDS is not Postgres, and "parameter
groups" is an Amazon thing. A quick web search would seem to indicate that
the default group is replaced by the custom one, rather than enhancing it.
But really, this is a simple "try it and see" sort of task.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/parameter-groups-overview.html

Other than that, reach out to Amazon support.

Cheers,
Greg

Re: Two test failures on v16 (compared to v14)

[Tom Lane]

Dominique Devienne  writes:
> Just an FYI. Running the same test suite against V16 triggered two failures.
> ...
> Second, a LO test failed to open a given OID.
> The test starts a read-only transaction, then tries a loOpen(oid,
> INV_WRITE),
> but does only a loRead(). Now in v16, the loOpen fails, returning -1, while
> it used to be OK.
> So this must be a bug fix of some kind I guess. Using INV_READ fixed the
> test.

Yup, see commit 55f480278

regards, tom lane

Re: Using a Conversion Table

[Greg Sabino Mullane]

>
> "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, with NUMERIC as needed for things with a precision. See:

https://www.postgresql.org/docs/current/datatype-numeric.html

Cheers,
Greg

P.S. +1 for use of an identity column, though.

Re: Using a Conversion Table

[David G. Johnston]

On Wednesday, February 14, 2024, Anthony Apollis 
wrote:

>
> *Conversion Table :*
>
>
> CREATE TABLE IF NOT EXISTS dim."IMETA_Master_Currency_Data_TA_BR"
> (
> "Currency" character varying(255) COLLATE pg_catalog."default",
> "Currency name" character varying(255) COLLATE pg_catalog."default",
> "Currency from" character varying(255) COLLATE pg_catalog."default",
> "Scenario" character varying(255) COLLATE pg_catalog."default",
> "Fiscal year" double precision,
> "Fiscal period" character varying(255) COLLATE pg_catalog."default",
> "Currency from value" double precision,
> "Currency to value" double precision,
> "Loaddate" date
> )
>

Your data type choices are unconventional and even arguably wrong for using
double for currency.



>
> *I am getting no output for using:*
>
> FROM
> system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
> WHERE
> z."Fiscal_Year" = 2024
>

Your table contains no matching rows.  The use of left join basically
precludes any other explanation.

David J.

Using a Conversion Table

[Anthony Apollis]

*I am trying to convert a column from ZAR Column "
Amount_in_Company_Code_Currency"  " to USD.*
 Table:
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2"
(
"Company_Code" character varying(255) COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" character varying(255) COLLATE
pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date DEFAULT CURRENT_DATE,
"Row_Hash" text COLLATE pg_catalog."default",
"LoadTime" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"ZTBR_TransactionCode" integer NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey2" PRIMARY KEY
("ZTBR_TransactionCode")
)

*Conversion Table :*


CREATE TABLE IF NOT EXISTS dim."IMETA_Master_Currency_Data_TA_BR"
(
"Currency" character varying(255) COLLATE pg_catalog."default",
"Currency name" character varying(255) COLLATE pg_catalog."default",
"Currency from" character varying(255) COLLATE pg_catalog."default",
"Scenario" character varying(255) COLLATE pg_catalog."default",
"Fiscal year" double precision,
"Fiscal period" character varying(255) COLLATE pg_catalog."default",
"Currency from value" double precision,
"Currency to value" double precision,
"Loaddate" date
)

TABLESPACE pg_default;

*I am getting no output for using:*

SELECT
z."Fiscal_Year",
SUM(z."Amount_in_Company_Code_Currency") AS Total_Amount,
ROUND(SUM(z."Amount_in_Company_Code_Currency" / CASE
WHEN c."Currency" = 'USD' THEN 1
ELSE c."Currency to value" END)::numeric, 2) AS Total_Amount_USD
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
LEFT JOIN
(SELECT
"Currency",
"Currency to value"
 FROM
dim."IMETA_Master_Currency_Data_TA_BR"
 WHERE
"Scenario" = 'Actual'  -- Adjust the scenario as needed
AND "Fiscal year" = 2024) c  -- Adjust the fiscal year as needed
ON
z."Company_Code_Currency" = c."Currency"
WHERE
z."Fiscal_Year" = 2024
GROUP BY
z."Fiscal_Year";

*In a previous calculation/join i used the code below and it worked, what
am i doing wrong?*

-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View

-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"
 AS
 SELECT t."ZTBR_TransactionCode",
t."Company_Code",
t."Posting_Period",
t."Fiscal_Year",
t."Profit_Center",
t."Account_Number",
t."Business_Process",
t."Internal_Order",
t."Amount_in_Company_Code_Currency",
t."Company_Code_Currency",
t."BRACS_FA",
t."Expense_Type",
t."Primary_ZTBR_TransactionCode",
t."DIM_BRACS_Account_Description" AS "Acct_Type",
t."DIM_Classification",
t."DIM_Direct_Primary_Key",
t."DIM_Order",
t."SDM_BRACSFA",
t."SDM_Function",
t."BRACS_Level_1",
t."BRACS_Level_2",
t."BRACS_Level_3",
t."Roll_Up_Currency",
t."Roll_Up_Account_Description",
t."BRACS_Account",
t."BRACS_Account_Description",
t."IS_BS",
t."Classification",
t."Roll_Up_Function",
t."Region",
t."Roll_Up",
t."Entity",
t."Entity_Name",
t."Entity_Level",
t."Entity_Level_1",
t."Entity_Level_2",
t."Entity_Level_3",
t."Entity_Level_4",
t."Entity_Level_5",
t."Entity_Level_6",
t."Region_Mapping_CoCd",
t."Region_Mapping_Sub_Region",
t."Region_Mapping_Region",
t."Region_Mapping_BRACS_Entity",
t."Region_Mapping_Consul",
t."Region_Mapping_Report",
t."Region_Mapping_Region_BRACS",
t."Region_Mapping_Group",
t."Region_Mapping_Group_BRACS",
round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",
CASE
WHEN t."Fiscal_Year"::double precision =
date_part('year'::text, CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Current Period",
CASE
WHEN t."Fiscal_Year"::double precision =
(date_part('year'::text, CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Prior Period",
CASE
WHEN t."Fiscal_Year"::double precision =
date_part('yea

Re: PostgreSQL DB in prod, test, debug

[Simon Connah]

On Wednesday, 14 February 2024 at 10:38, Erik Wienhold  wrote:

> 

> 

> On 2024-02-14 10:59 +0100, Simon Connah wrote:
> 

> > This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15.
> > 

> > This is probably a stupid question so I apologies in advance.
> > 

> > I'm building a website using PostgreSQL and since I've just been doing
> > some dev work on it I've just manually played around with the database
> > if I needed new tables or functions for example but I want to start
> > doing automated testing and need to import a clean snapshot of the
> > database with no data and then use the automated tests to test if
> > things work with the tests.
> > 

> > What I think is the best way to do this is to do a pg_dump of the
> > database (using the --schema-only flag)
> 

> 

> You create a dump from the prod database each time? Yikes.

Sorry. That came out wrong. I don't take a dump each time I run tests but I 
will generally take a full dump when working on something specific to the 
database.

> 

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

> > My main question is does this sound OK? And if so is there a nice way
> > to automate the dump / restore in Python?
> 

> 

> The database schema should be defined by migration scripts that you also
> check into version control with the rest of your application sources.
> Some people also prefer a separate repository just for the database
> schema, depending on how tightly coupled database and application are,
> or if there are other applications relying on the database schema.
> 

> I use Sqitch[1] which works well if you want to track an existing
> database schema. Alembic is popular in Python but I don't know how it
> works with an existing schema because Alembic migrations are usually not
> written in plain SQL.
> 

> This is also useful for automated deployment because it allows you to
> migrate the database to a specific schema version that is necessary for
> your application.
> 

> For testing, I have a Bash script that starts a Docker container with
> Postgres and then runs sqitch-deploy before running pytest. That can of
> course be adapted to spin up a local Postgres instance instead. I also
> use pgTAP[2] with that to just test the database schema.
> 

> You can also use testcontainers[3] to start/stop a Docker container in
> pytest and run sqitch with subprocess before running all tests.
> 

> [1] https://sqitch.org/
> [2] https://pgtap.org/
> [3] 
> https://testcontainers-python.readthedocs.io/en/latest/postgres/README.html
> 

> --
> Erik

Thank you. Squitch looks like a useful tool. I'll certainly look into all of 
that.

I've never really used Docker before. Instead I just spin up Vultr instances 
but I can read about Docker as well.

Simon.

signature.asc
Description: OpenPGP digital signature

Re: PostgreSQL DB in prod, test, debug

[Simon Connah]

On Wednesday, 14 February 2024 at 10:25, Daniel Gustafsson  
wrote:

> 

> 

> > On 14 Feb 2024, at 10:59, Simon Connah simon.n.con...@protonmail.com 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. In essence, create the test database identically to how you create
> the production database to ensure that you are testing what you will run in
> production.
> 

> --
> Daniel Gustafsson

Thank you. I'm a bit disorganised with things at the moment. I'm kinda 
developing this site in an ad hoc manner which is probably a bad idea so I'm 
trying to fix up a few mistakes I made early on.

In terms of the database I've just added new functions as needed which I use 
instead of direct SQL in my Python code. Maybe I should sit down with Visio and 
try and do a diagram before I go any further?

I'll just write a simple script for backup and restore and call it before each 
test run but have the schema only dump come from production. At this point it 
doesn't really matter as the website is so small.

Simon.

signature.asc
Description: OpenPGP digital signature

Two test failures on v16 (compared to v14)

[Dominique Devienne]

Just an FYI. Running the same test suite against V16 triggered two failures.

First, a test of getting the direct members of a role failed.
Returned one more role. The role that created the introspected role.
That's the new CREATEROLE semantic, which adds the role creator
as a direct member of the created role, with admin option.

Second, a LO test failed to open a given OID.
The test starts a read-only transaction, then tries a loOpen(oid,
INV_WRITE),
but does only a loRead(). Now in v16, the loOpen fails, returning -1, while
it used to be OK.
So this must be a bug fix of some kind I guess. Using INV_READ fixed the
test.

I was not expecting failures, but both have good reasons and are easily
fixed. FWIW. --DD

Re: PostgreSQL DB in prod, test, debug

[Erik Wienhold]

On 2024-02-14 10:59 +0100, Simon Connah wrote:
> This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15.
> 
> This is probably a stupid question so I apologies in advance.
> 
> I'm building a website using PostgreSQL and since I've just been doing
> some dev work on it I've just manually played around with the database
> if I needed new tables or functions for example but I want to start
> doing automated testing and need to import a clean snapshot of the
> database with no data and then use the automated tests to test if
> things work with the tests.
> 
> What I think is the best way to do this is to do a pg_dump of the
> database (using the --schema-only flag)

You create a dump from the prod database each time?  Yikes.

> 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.
> 
> My main question is does this sound OK? And if so is there a nice way
> to automate the dump / restore in Python?

The database schema should be defined by migration scripts that you also
check into version control with the rest of your application sources.
Some people also prefer a separate repository just for the database
schema, depending on how tightly coupled database and application are,
or if there are other applications relying on the database schema.

I use Sqitch[1] which works well if you want to track an existing
database schema.  Alembic is popular in Python but I don't know how it
works with an existing schema because Alembic migrations are usually not
written in plain SQL.

This is also useful for automated deployment because it allows you to
migrate the database to a specific schema version that is necessary for
your application.

For testing, I have a Bash script that starts a Docker container with
Postgres and then runs sqitch-deploy before running pytest.  That can of
course be adapted to spin up a local Postgres instance instead.  I also
use pgTAP[2] with that to just test the database schema.

You can also use testcontainers[3] to start/stop a Docker container in
pytest and run sqitch with subprocess before running all tests.

[1] https://sqitch.org/
[2] https://pgtap.org/
[3] https://testcontainers-python.readthedocs.io/en/latest/postgres/README.html

-- 
Erik

Postgres pg_cron extension

[arun chirappurath]

Dear all,

I am trying to enable pg_cron extension in RDS postgres and I got to know
it will be enabled only in custom parameter group..it can't be enabled in
default one.
1. Suppose if we create a custom group for existing postgres 14
databases,will all the existing parameters in default group gets copied
over to custom group?

2.Also will there be any impact if we change this parameter group?

3.Also if we upgrade 14 to 15 in future,Do we need to change the parameter
to 15 compatible?

Apologies if its in wrong forum,

Thanks,
Arun

Re: PostgreSQL DB in prod, test, debug

[Daniel Gustafsson]

> 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.  In essence, create the test database identically to how you create
the production database to ensure that you are testing what you will run in
production.

--
Daniel Gustafsson

PostgreSQL DB in prod, test, debug

[Simon Connah]

Hi,

This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15.

This is probably a stupid question so I apologies in advance.

I'm building a website using PostgreSQL and since I've just been doing some dev 
work on it I've just manually played around with the database if I needed new 
tables or functions for example but I want to start doing automated testing and 
need to import a clean snapshot of the database with no data and then use the 
automated tests to test if things work with the tests.

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.

My main question is does this sound OK? And if so is there a nice way to 
automate the dump / restore in Python?

Simon.

signature.asc
Description: OpenPGP digital signature