Re: Repeatable Read Isolation Level "transaction start time"

[Adrian Klaver]

On 9/24/24 05:59, Ron Johnson wrote:
On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:


On Monday, September 23, 2024, Wizard Brony mailto:wizardbr...@gmail.com>> wrote:

https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ 


The PostgreSQL documentation for the Repeatable Read Isolation
Level states the following:

“UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE
commands behave the same as SELECT in terms of searching for
target rows: they will only find target rows that were committed
as of the transaction start time.”

What is defined as the "transaction start time?" When I first
read the statement, I interpreted it as the start of the
transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

But in my testing, I find that according to that statement, the
transaction start time is actually "the start of the first
non-transaction-control statement in the transaction" (as
mentioned earlier in the section). Is my conclusion correct, or
am I misunderstanding the documentation?


Probably, since indeed the transaction cannot start at begin because
once it does start it cannot be modified.

Huh?


BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

I read it as the transaction does not start at BEGIN because if it did 
you could not SET TRANSACTION to change it's characteristics.


The docs go into more detail:

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

The transaction isolation level cannot be changed after the first query 
or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, 
FETCH, or COPY) of a transaction has been executed.



So:

begin ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET
select * from csv_test ;
[...]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query




--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 crustacean!


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

Re: Issues with PostgreSQL Source Code Installation

[Tom Lane]

Ayush Vatsa  writes:
> So actually I wanted to modify and playaround with the code,
> hence I am using the github repo and building postgres from it.

Seems reasonable.  The tip of master branch is occasionally broken,
but seldom for very long, and none of our buildfarm animals are
reporting failures like this.  It looks to me like there is something
wrong with your libicu installation --- perhaps headers out of sync
with shared library?

regards, tom lane

Re: Repeatable Read Isolation Level "transaction start time"

[Ron Johnson]

On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, September 23, 2024, Wizard Brony  wrote:
>
>>
>> https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ
>>
>> The PostgreSQL documentation for the Repeatable Read Isolation Level
>> states the following:
>>
>> “UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands
>> behave the same as SELECT in terms of searching for target rows: they will
>> only find target rows that were committed as of the transaction start time.”
>>
>> What is defined as the "transaction start time?" When I first read the
>> statement, I interpreted it as the start of the transaction:
>>
>> BEGIN;
>> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>>
>> But in my testing, I find that according to that statement, the
>> transaction start time is actually "the start of the first
>> non-transaction-control statement in the transaction" (as mentioned earlier
>> in the section). Is my conclusion correct, or am I misunderstanding the
>> documentation?
>>
>>
> Probably, since indeed the transaction cannot start at begin because once
> it does start it cannot be modified.
>

Huh?

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 crustacean!

Re: Repeatable Read Isolation Level "transaction start time"

[David G. Johnston]

On Monday, September 23, 2024, Wizard Brony  wrote:

> https://www.postgresql.org/docs/16/transaction-iso.html#
> XACT-REPEATABLE-READ
>
> The PostgreSQL documentation for the Repeatable Read Isolation Level
> states the following:
>
> “UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands
> behave the same as SELECT in terms of searching for target rows: they will
> only find target rows that were committed as of the transaction start time.”
>
> What is defined as the "transaction start time?" When I first read the
> statement, I interpreted it as the start of the transaction:
>
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>
> But in my testing, I find that according to that statement, the
> transaction start time is actually "the start of the first
> non-transaction-control statement in the transaction" (as mentioned earlier
> in the section). Is my conclusion correct, or am I misunderstanding the
> documentation?
>
>
Probably, since indeed the transaction cannot start at begin because once
it does start it cannot be modified.

David J.

Issues with PostgreSQL Source Code Installation

[Ayush Vatsa]

Hi PostgreSQL Community,

I am encountering an issue while trying to install PostgreSQL from its
source code and would appreciate any guidance you can provide.
Here are the steps I've taken so far:
sudo yum install libicu-devel
git clone git://git.postgresql.org/git/postgresql.git
./configure --enable-tap-tests --prefix=`pwd`/build
make world-bin

However, during the make world-bin process, I am encountering the following
errors:

/usr/bin/ld: commands/collationcmds.o: in function
`pg_import_system_collations':
collationcmds.c:(.text+0xeed): undefined reference to
`uloc_countAvailable_73'
/usr/bin/ld: collationcmds.c:(.text+0xf0a): undefined reference to
`uloc_getAvailable_73'
/usr/bin/ld: collationcmds.c:(.text+0xfc6): undefined reference to
`uloc_getDisplayName_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isalpha':
regcomp.c:(.text+0xe41): undefined reference to `u_isalpha_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isspace':
regcomp.c:(.text+0xef1): undefined reference to `u_isspace_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isdigit':
regcomp.c:(.text+0x1031): undefined reference to `u_isdigit_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isalnum':
regcomp.c:(.text+0x10e1): undefined reference to `u_isalnum_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isgraph':
regcomp.c:(.text+0x1c81): undefined reference to `u_isgraph_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_isupper':
regcomp.c:(.text+0x1d31): undefined reference to `u_isupper_73'
/usr/bin/ld: regex/regcomp.o: in function `pg_wc_islower':

I have ICU version 73.1 installed, which I confirmed with: icu-config
--version
My ~/.zshrc file looks like:

export LDFLAGS="-L/usr/lib64 -L/usr/local/lib"
export CPPFLAGS="-I/usr/include"
export PATH="/usr/lib64/icu:$PATH"
export PKG_CONFIG_PATH="/usr/lib64/pkgconfig/:/usr/local/lib/pkgconfig"
export LD_LIBRARY_PATH="/usr/lib64:/usr/local/lib:$LD_LIBRARY_PATH"

Output for: sudo find / -name libicui18n.so
/usr/lib64/libicui18n.so

I am using:
Linux kernel version: 6.1.106-116.188.amzn2023.x86_64
Architecture: x86_64

Regards
Ayush

Re: Issues with PostgreSQL Source Code Installation

[Adrian Klaver]

On 9/24/24 04:14, Ayush Vatsa wrote:

Hi PostgreSQL Community,

I am encountering an issue while trying to install PostgreSQL from its 
source code and would appreciate any guidance you can provide.

Here are the steps I've taken so far:
sudo yum install libicu-devel
git clone git://git.postgresql.org/git/postgresql.git 


Why are you building against the repo instead  of a fixed version from 
here?:


https://www.postgresql.org/ftp/source/



./configure --enable-tap-tests --prefix=`pwd`/build


Did you checkout a given tag?


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

Re: Repeatable Read Isolation Level "transaction start time"

[Ron Johnson]

On Tue, Sep 24, 2024 at 12:06 PM Adrian Klaver 
wrote:

> On 9/24/24 05:59, Ron Johnson wrote:
> > On Tue, Sep 24, 2024 at 8:29 AM David G. Johnston
> > mailto:david.g.johns...@gmail.com>> wrote:
> >
> > On Monday, September 23, 2024, Wizard Brony  > > wrote:
> >
> >
> https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ
> <
> https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ
> >
> >
> > The PostgreSQL documentation for the Repeatable Read Isolation
> > Level states the following:
> >
> > “UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE
> > commands behave the same as SELECT in terms of searching for
> > target rows: they will only find target rows that were committed
> > as of the transaction start time.”
> >
> > What is defined as the "transaction start time?" When I first
> > read the statement, I interpreted it as the start of the
> > transaction:
> >
> > BEGIN;
> > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> >
> > But in my testing, I find that according to that statement, the
> > transaction start time is actually "the start of the first
> > non-transaction-control statement in the transaction" (as
> > mentioned earlier in the section). Is my conclusion correct, or
> > am I misunderstanding the documentation?
> >
> >
> > Probably, since indeed the transaction cannot start at begin because
> > once it does start it cannot be modified.
> >
> > Huh?
>
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>
> I read it as the transaction does not start at BEGIN because if it did
> you could not SET TRANSACTION to change it's characteristics.
>
> The docs go into more detail:
>
> https://www.postgresql.org/docs/current/sql-set-transaction.html
>
> The transaction isolation level cannot be changed after the first query
> or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE,
> FETCH, or COPY) of a transaction has been executed.
>
>
> So:
>
> begin ;
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> SET
> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
> SET
> select * from csv_test ;
> [...]
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
>

Makes sense.  Never would have occurred to me to try and change the
isolation level using a second SET TRANSACTION statement, though.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 crustacean!

Re: Repeatable Read Isolation Level "transaction start time"

[Adrian Klaver]

On 9/24/24 09:12, Ron Johnson wrote:

Makes sense.  Never would have occurred to me to try and change the 
isolation level using a second SET TRANSACTION statement, though.


From the docs:

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

The SET TRANSACTION command sets the characteristics of the current 
transaction. It has no effect on any subsequent transactions. SET 
SESSION CHARACTERISTICS sets the default transaction characteristics for 
subsequent transactions of a session. These defaults can be overridden 
by SET TRANSACTION for an individual transaction.





--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 crustacean!


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

Re: Repeatable Read Isolation Level "transaction start time"

[Tom Lane]

Wizard Brony  writes:
> But in my testing, I find that according to that statement, the transaction 
> start time is actually "the start of the first non-transaction-control 
> statement in the transaction" (as mentioned earlier in the section). Is my 
> conclusion correct, or am I misunderstanding the documentation?

It's even looser than that, really: it's the first statement that
requires an MVCC snapshot.  From memory, LOCK TABLE is an important
exception --- you can acquire table locks before pinning down
a snapshot, and this is important in some scenarios.

regards, tom lane

Re: Issues with PostgreSQL Source Code Installation

[Ayush Vatsa]

> Why are you building against the repo instead  of a fixed version from
> here?:
So actually I wanted to modify and playaround with the code,
hence I am using the github repo and building postgres from it.

> Did you checkout a given tag?
I haven't checkout on any branch and currently
on branch master where I am trying to build postgres

Regards
Ayush

Re: Issues with PostgreSQL Source Code Installation

[Adrian Klaver]

On 9/24/24 09:32, Ayush Vatsa wrote:

 > Why are you building against the repo instead  of a fixed version from
 > here?:
So actually I wanted to modify and playaround with the code,
hence I am using the github repo and building postgres from it.

 > Did you checkout a given tag?
I haven't checkout on any branch and currently
on branch master where I am trying to build postgres


Well I would imagine that could be at any state and not necessarily one 
that could be built.


I would go to the source link I posted download a specific version and 
verify it builds. Then you know whether your setup is correct. Then you 
could go back to exploring the Git code.




Regards
Ayush


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

Re: Issues with PostgreSQL Source Code Installation

[Ayush Vatsa]

> I would go to the source link I posted download a specific version and
> verify it builds. Then you know whether your setup is correct. Then you
> could go back to exploring the Git code.
Thanks will check that out

MError after replication in postgresql

[Danish Hajwane]

Hi Team,

We are getting error after doing replication on posrgresql 16.3 (OS is HP
UX)
error is regarding Libpq library, we are unable to start sync getting error

FATAL: could not connect to the primary server : libpq is incorrectly
linked to backend functions.
LOG: waiting for WAL to become available at 0/3618.

Regards
Danish

Repeatable Read Isolation Level "transaction start time"

[Wizard Brony]

https://www.postgresql.org/docs/16/transaction-iso.html#XACT-REPEATABLE-READ

The PostgreSQL documentation for the Repeatable Read Isolation Level states the 
following:

“UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave 
the same as SELECT in terms of searching for target rows: they will only find 
target rows that were committed as of the transaction start time.”

What is defined as the "transaction start time?" When I first read the 
statement, I interpreted it as the start of the transaction:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

But in my testing, I find that according to that statement, the transaction 
start time is actually "the start of the first non-transaction-control 
statement in the transaction" (as mentioned earlier in the section). Is my 
conclusion correct, or am I misunderstanding the documentation?

PgBackRest : Restore to a checkpoint shows further transactions

[KK CHN]

List,


PgBackRest :  I tried to restore the latest backup taken at my RepoServer
to a  testing EPAS server freshly deployed .

I have a full backup, two diff  and one INCR  as on today morning.   The
latest one is INCR


full backup: 20240922-232733F
timestamp start/stop: 2024-09-22 23:27:33+05:30 / 2024-09-23
09:17:00+05:30

  diff backup: 20240922-232733F_20240924-222336D
timestamp start/stop: 2024-09-24 22:23:36+05:30 / 2024-09-24
22:55:41+05:30


 incr backup: 20240922-232733F_20240925-082637I
timestamp start/stop: 2024-09-25 08:26:37+05:30 / 2024-09-25
08:36:00+05:30



On my Test  EPAS Server :

[root@dbch ~]# *sudo -u enterprisedb pgbackrest --stanza=Repo1  --delta
--set=20240922-232733F_20240925-082637*I  --target-timeline=current
*restore*


2024-09-25 10:28:42.493 P00   INFO: restore command end: completed
successfully (2657236ms)


Now I  comment out the archive command in the test EPAS server
postgresql.conf  and started the EPAS server.


WHen I issue a query  to select few rows  To my surprise  I am seeing the
records with columns with time stamp up to a time 10.36:11:968  and  1
0:36:13.363 :


How did this happen ?   I specified the restore point file (incr) taken at
2024-09-25 08:26:37,   naturally  I expected restore may show  records up
to this time stamp or   up to 2024-09-25 08:36:00+05:30   but it shows
further to  10.36:11:968  and  0:36:13.363 but not beyond this !!!


But my restore  ends successfully   at  2024-09-25 10:28:42.493 P00   INFO:
restore command end: completed successfully (2657236ms)

 Could someone explain how this comes about ?

But no other records  latest  than  10.36:11:968  and  0:36:13.363
 showing  .. How is it delimited here at this time stamp ?


SO  I guess this is due to specifying  --target-timeline=current?  But
restore finished at 10.28:42.493

OR

 Does this take all wal and replay up to  the  EPAS  service starting  time
of the testing EPAS server ?


Thank you,

Krishane.


For more inputs :  I have queried like this below..


t_db=# select * from c.cti_all_info  ORDER BY received_time DESC LIMIT 1;
   id| caller_number |  call_identifier   | ivr_start_time  |
ivr_connect_time | ivr_drop_time | ivr_drop_reason | call_landing_time |
call_start_time |  call_end_time  | call_drop_reason
| sip_extension | call_direction |


 message_list



 | voice_path | partition_key |  received_time
   | remarks | source_ip_address | pilot_number
-+---++-+--+---+-+---+-+-+---
+---+++---+--
---+-+---+--
 66769044 |555657643942 | 140771.5140 | 2024-09-25 10:36:11.968 |
   |   | |   |
| 2024-09-25 10:36:13.363 | User Disconnected
|   | IN | ["{\"srcType\":\"ACS\",\"srId\":\"



I have PgBack successfully running on a Production Server and a Repo
Server   RHEL9.4, PgBackRest 2.52.1 and EPAS 16.1 .  Restore performing for
the first time.