date:20170701

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread rajan

Thanks, Jeff. That helps understanding it 50%.

*Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple is
marked for deletion. It means that *(0,2) never exists* when Session 2 is
trying to perform the update. 

In that case, how *Session 3's new row (0,4)* contains the xmax as *Session
2's txid*.



-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969661.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread Jeff Janes

On Sat, Jul 1, 2017 at 6:32 PM, rajan  wrote:

> hello,
>
> thanks for replies, Adrian, Steven.
>
> >So calling it can advance the xid manually. Some testing here showed
> >that what xmin or xmax is created depends on when you call txid_current
> >in either the original session or the concurrent sessions.
>
> I understand this and I am executing my statements inside a Transaction
> block so the xid is not incremented when calling it.
>
> >Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
> >The clue is the ctid value. In  Session 2 you are looking at the
> >original row(ctid=(0, 2) which has been marked as deleted(non-zero
> >xmax). In Session 3 you are looking at the new row(ctid(0, 4)).
>
> Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115
> in which the update failed with *UPDATE 0* . This is where I can not
> understand,
> 1. Row (0,4) is updated with correct value and (0,3) is not visible in
> Session 2, which is good.
> 2. but in *Session 3* (0,4) also carries xmax which means what? Is it also
> marked for deletion? It can't be, right?
>

When session 2 encounters the locked row which meets the criterion for the
update, it has to wait for the locking transaction to finish.  At that
point it locks the row (by writing its transaction into the xmax, and
setting a flag not visible to you, unless you use pgeinspect) and then
re-evaluates if it still meets the criterion.  Since it doesn't meet the
criterion anymore, it doesn't finish updating the tuple.

Cheers,

Jeff

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread rajan

hello,

thanks for replies, Adrian, Steven.

>So calling it can advance the xid manually. Some testing here showed 
>that what xmin or xmax is created depends on when you call txid_current 
>in either the original session or the concurrent sessions. 

I understand this and I am executing my statements inside a Transaction
block so the xid is not incremented when calling it.

>Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process. 
>The clue is the ctid value. In  Session 2 you are looking at the 
>original row(ctid=(0, 2) which has been marked as deleted(non-zero 
>xmax). In Session 3 you are looking at the new row(ctid(0, 4)). 

Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115
in which the update failed with *UPDATE 0* . This is where I can not
understand,
1. Row (0,4) is updated with correct value and (0,3) is not visible in
Session 2, which is good.
2. but in *Session 3* (0,4) also carries xmax which means what? Is it also
marked for deletion? It can't be, right?




-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread stevenchang1213

this chapter introduces mvcc
http://www.interdb.jp/pg/pgsql05.html


 原始訊息 自： rajan  日期： 2017/7/1  14:32  
(GMT+08:00) 至： pgsql-general@postgresql.org 主旨： [GENERAL] have trouble 
understanding xmin and xmax with update operations from two different sessions 
have trouble understanding xmin and xmax with update operations from two
different sessions

So, as found below, Session2 is trying to update a row which is already
updated with a different value and it's update fails with *UPDATE 0*

But from Session3, I see that xmax value is visible as Session2's txid. Why
is it like that?
Can we not put Session2's txid to xmin instead(although the update failed)?
And if we try to consider that xmax is update with Session2's txid bcoz the
update failed, then why bother updating the xmax?

Please help me understand this.

*Session1*
testdb=# BEGIN;
BEGIN
testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  | xmax | number
---++--+
 (0,2) | 519107 |    0 | 14
 (0,3) | 519112 |    0 | 23
(2 rows)

testdb=# select txid_current();
 txid_current
--
   519114
(1 row)

testdb=# update numbers set number=24 where number=14;
UPDATE 1
testdb=# COMMIT;
COMMIT


*Session 2*
testdb=# BEGIN;
BEGIN
testdb=# select txid_current();
 txid_current
--
   519115
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  | xmax | number
---++--+
 (0,2) | 519107 |    0 | 14
 (0,3) | 519112 |    0 | 23
(2 rows)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  |  xmax  | number
---+++
 (0,2) | 519107 | 519114 | 14
 (0,3) | 519112 |  0 | 23
(2 rows)

testdb=# update numbers set number=25 where number=14;
UPDATE 0
testdb=# COMMIT;
COMMIT

*Session 3*
testdb=# select txid_current();
 txid_current
--
   519116
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  |  xmax  | number
---+++
 (0,3) | 519112 |  0 | 23
 (0,4) | 519114 | 519115 | 24
(2 rows)




-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread Adrian Klaver


On 06/30/2017 11:32 PM, rajan wrote:

have trouble understanding xmin and xmax with update operations from two
different sessions

So, as found below, Session2 is trying to update a row which is already
updated with a different value and it's update fails with *UPDATE 0*

But from Session3, I see that xmax value is visible as Session2's txid. Why
is it like that?
Can we not put Session2's txid to xmin instead(although the update failed)?
And if we try to consider that xmax is update with Session2's txid bcoz the
update failed, then why bother updating the xmax?

Please help me understand this.


One thing to remember is that txid_current() is more then informational:

https://www.postgresql.org/docs/9.6/static/functions-info.html

txid_current() 	bigint 	get current transaction ID, assigning a new one 
if the current transaction does not have one


So calling it can advance the xid manually. Some testing here showed 
that what xmin or xmax is created depends on when you call txid_current 
in either the original session or the concurrent sessions.


Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process. 
The clue is the ctid value. In  Session 2 you are looking at the 
original row(ctid=(0, 2) which has been marked as deleted(non-zero 
xmax). In Session 3 you are looking at the new row(ctid(0, 4)).




*Session1*
testdb=# BEGIN;
BEGIN
testdb=# select ctid, xmin, xmax, * from numbers;
  ctid  |  xmin  | xmax | number
---++--+
  (0,2) | 519107 |0 | 14
  (0,3) | 519112 |0 | 23
(2 rows)

testdb=# select txid_current();
  txid_current
--
519114
(1 row)

testdb=# update numbers set number=24 where number=14;
UPDATE 1
testdb=# COMMIT;
COMMIT


*Session 2*
testdb=# BEGIN;
BEGIN
testdb=# select txid_current();
  txid_current
--
519115
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
  ctid  |  xmin  | xmax | number
---++--+
  (0,2) | 519107 |0 | 14
  (0,3) | 519112 |0 | 23
(2 rows)

testdb=# select ctid, xmin, xmax, * from numbers;
  ctid  |  xmin  |  xmax  | number
---+++
  (0,2) | 519107 | 519114 | 14
  (0,3) | 519112 |  0 | 23
(2 rows)

testdb=# update numbers set number=25 where number=14;
UPDATE 0
testdb=# COMMIT;
COMMIT

*Session 3*
testdb=# select txid_current();
  txid_current
--
519116
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
  ctid  |  xmin  |  xmax  | number
---+++
  (0,3) | 519112 |  0 | 23
  (0,4) | 519114 | 519115 | 24
(2 rows)




-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-01 Thread Melvin Davidson

On Sat, Jul 1, 2017 at 10:05 AM, Adrian Klaver 
wrote:

> On 06/30/2017 09:42 PM, Steven Chang wrote:
>
>> Uh...we also met duplicate rows with primary key column through
>>  restoring database by pg_basebackup.
>> H.
>> I don't think its an issue with primary key index corruption.
>>
>
> That is interesting, more information would be helpful though:
>
> Postgres version?
>
> OS and version?
>
> The pg_basebackup command line invocation?
>
> Why you don't think it is index corruption?
>
>
>
>>
>>
>> 2017-07-01 7:30 GMT+08:00 Adrian Klaver > >:
>>
>> On 06/30/2017 07:33 AM, Timokhin Maxim wrote:
>>
>> Sure, here it is.
>>
>> pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql
>> -v —xlog-method=stream —checkpoint=fast
>>
>> /usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8
>> —locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8
>> —lc-messages=en_US.utf8
>>
>> Then updating:
>> /usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d
>> /data/upgrade/94 -B /usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k
>>
>> and so on to 9.6
>>
>>
>> The original 9.4 database has the same encoding setup?
>>
>> FYI, you can use pg_upgrade to go straight from 9.4 to 9.6.
>>
>> https://www.postgresql.org/docs/9.6/static/pgupgrade.html
>> 
>>
>> "pg_upgrade supports upgrades from 8.4.X and later to the current
>> major release of PostgreSQL, including snapshot and alpha releases."
>>
>>
>>
>> after that server starts normally.
>>
>>
>> -- Timokhin 'maf' Maxim
>>
>>
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


>I don't think its an issue with primary key index corruption.

Well, have you verified that? Try running the following query and make sure
the status column shows "valid" for ALL indexes.

SELECT n.nspname as schema,
   i.relname as table,
   i.indexrelname as index,
   i.idx_scan,
   i.idx_tup_read,
   i.idx_tup_fetch,
   CASE WHEN idx.indisprimary THEN 'pkey'
  WHEN idx.indisunique  THEN 'uidx'
  ELSE 'idx'
END AS type,
   idx.indisexclusion,
   pg_get_indexdef(idx.indexrelid),
   CASE WHEN idx.indisvalid THEN 'valid'
  ELSE 'INVALID'
END as statusi,
   pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
   pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.relname LIKE '%%'
   AND n.nspname NOT LIKE 'pg_%'
ORDER BY 1, 2, 3;
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

2017-07-01 Thread Adrian Klaver


On 06/30/2017 09:42 PM, Steven Chang wrote:
Uh...we also met duplicate rows with primary key column through 
  restoring database by pg_basebackup.

H.
I don't think its an issue with primary key index corruption.


That is interesting, more information would be helpful though:

Postgres version?

OS and version?

The pg_basebackup command line invocation?

Why you don't think it is index corruption?






2017-07-01 7:30 GMT+08:00 Adrian Klaver >:


On 06/30/2017 07:33 AM, Timokhin Maxim wrote:

Sure, here it is.

pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql
-v —xlog-method=stream —checkpoint=fast

/usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8
—locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8
—lc-messages=en_US.utf8

Then updating:
/usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d
/data/upgrade/94 -B /usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k

and so on to 9.6


The original 9.4 database has the same encoding setup?

FYI, you can use pg_upgrade to go straight from 9.4 to 9.6.

https://www.postgresql.org/docs/9.6/static/pgupgrade.html


"pg_upgrade supports upgrades from 8.4.X and later to the current
major release of PostgreSQL, including snapshot and alpha releases."



after that server starts normally.


-- 
Timokhin 'maf' Maxim






-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Buy real Passports(designerdesig...@yahoo.com)Visa, Driving License, ID CARDS, marriage certificates, diplomas

2017-07-01 Thread leason

Buy real Passports(designerdesig...@yahoo.com)Visa, Driving License, ID
CARDS, marriage certificates, diplomas

We are a team of professionals with many years of experience in
manufacturing forged passports and other identity documents, the best
producers of quality fake documents. With more than 10 million
documents circulating in the world. We offer only original high
qualities of true-false passport, driving license, ID cards, stamps,
birth certificates, international fake diplomas and other products for
a number of countries like: USA, Australia, Belgium, Brazil, Norway
Canada, Italy, Finland, France, Germany, Israel, Mexico, Netherlands,
South Africa, Spain, United Kingdom.
 
whatsapp>+447732429321

This list is not complete.

For more information and to order contact us by email or phone.
BUY fake British passports (UK), Americans, Canadians
False identity CARDS ONLINE IN UNITED STATES, DRIVING LICENSE
Buy fake birth certificates
BUY FAKE DRIVING LICENSE

whatsapp>> +447732429321

Technical support ::designerdesig...@yahoo.com

Feel free to contact us anytime and in any discretion.

Keywords:

buy registered and unregistered ID cards
buy registered and unregistered drivers license worldwide
buy registered and unregistered USA(United States) passports,
buy registered and unregistered Australian passports,
buy registered and unregistered Belgium passports,
buy registered and unregistered Brazilian(Brazil) passports,
buy registered and unregistered Canadian(Canada) passports,
buy registered and unregistered Finnish(Finland) passports,
buy registered and unregistered French(France) passports,
buy registered and unregistered German(Germany) passports,
buy registered and unregistered Dutch(Netherlands/Holland) passports,
buy registered and unregistered Israel passports,
buy registered and unregistered UK(United Kingdom) passports,
buy registered and unregistered Spanish(Spain) passports,
buy registered and unregistered Mexican(Mexico) passports,
buy registered and unregistered South African passports.
buy registered and unregistered Australian driver licenses,
buy registered and unregistered Canadian driver licenses,
buy registered and unregistered Dutch(Netherlands/Holland) driving licenses,
buy registered and unregistered German(Germany) driving licenses,
buy registered and unregistered UK(United Kingdom) driving licenses,
buy registered and unregistered Diplomatic passports,

 whatsapp>> +447732429321

buy registered and unregistered USA(United States) passports,
buy registered and unregistered Australian passports,
buy registered and unregistered Belgium passports,
buy registered and unregistered Brazilian(Brazil) passports,
buy registered and unregistered Canadian(Canada) passports,
buy registered and unregistered Finnish(Finland) passports,
buy registered and unregistered French(France) passports,
buy registered and unregistered German(Germany) passports,
buy registered and unregistered Dutch(Netherlands/Holland) passports,
buy registered and unregistered UK(United Kingdom) passports,
buy registered and unregistered Mexican(Mexico) passports,
buy registered and unregistered South African passports.
buy registered and unregistered Australian driver licenses,
buy registered and unregistered Canadian driver licenses,
buy registered and unregistered Dutch(Netherlands/Holland) driving licenses,
buy registered and unregistered German(Germany) driving licenses,
buy registered and unregistered UK(United Kingdom) driving licenses,
registered Diplomatic passports,and driver license ,id cards etc
buy registered and unregistered Duplicates,and driver license ,id cards etc
buy registered and unregistered USA(united States) passports for sale,
buy registered and unregistered Australian passports for sell,and
driver license ,id cards etc
buy registered and unregistered Belgium passports for sell,
buy registered and unregistered Brazilian(Brazil) passports for sell,etc.
buy Camouflage passports,
express work permits
IELTS certificate,TOIC ETC
express Canadian citizenship documents
verified id cards
passport registered
Canada Cards
United States Cards
Student Cards
International Cards
Private Cards
Adoption Certificates
Baptism Certificates
Birth Certificates
Death Certificates
Divorce Certificates
Marriage Certificates
Custom Certificates
High School Diplomas
G.E.D. Diplomas
Home School Diplomas
College Degrees
University Degrees
Trade Skill Certificates
Social Security
Validate SSN Number
Driver License
Spy Products
Voice Changers
Listening Devices
Invisible Ink
DMV Record a
Background Check
Investigate Anyone
visa issues
Most documents available passport, identification card, cards, UK, sell,
Online, Canadian, British, sale, novelty, fake, fake, American, united
states, USA,
Italian, Malaysia, Australia, documents, identaity, identification,
driver's license,
citizenship, identity, identification, documents, diplomatic, nationality
etc
buy,

[GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

2017-07-01 Thread rajan

have trouble understanding xmin and xmax with update operations from two
different sessions

So, as found below, Session2 is trying to update a row which is already
updated with a different value and it's update fails with *UPDATE 0*

But from Session3, I see that xmax value is visible as Session2's txid. Why
is it like that?
Can we not put Session2's txid to xmin instead(although the update failed)?
And if we try to consider that xmax is update with Session2's txid bcoz the
update failed, then why bother updating the xmax?

Please help me understand this.

*Session1*
testdb=# BEGIN;
BEGIN
testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  | xmax | number
---++--+
 (0,2) | 519107 |0 | 14
 (0,3) | 519112 |0 | 23
(2 rows)

testdb=# select txid_current();
 txid_current
--
   519114
(1 row)

testdb=# update numbers set number=24 where number=14;
UPDATE 1
testdb=# COMMIT;
COMMIT


*Session 2*
testdb=# BEGIN;
BEGIN
testdb=# select txid_current();
 txid_current
--
   519115
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  | xmax | number
---++--+
 (0,2) | 519107 |0 | 14
 (0,3) | 519112 |0 | 23
(2 rows)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  |  xmax  | number
---+++
 (0,2) | 519107 | 519114 | 14
 (0,3) | 519112 |  0 | 23
(2 rows)

testdb=# update numbers set number=25 where number=14;
UPDATE 0
testdb=# COMMIT;
COMMIT

*Session 3*
testdb=# select txid_current();
 txid_current
--
   519116
(1 row)

testdb=# select ctid, xmin, xmax, * from numbers;
 ctid  |  xmin  |  xmax  | number
---+++
 (0,3) | 519112 |  0 | 23
 (0,4) | 519114 | 519115 | 24
(2 rows)




-
--
Thanks,
Rajan.
--
View this message in context: 
http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Re: [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

Re: [GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

Re: [GENERAL] duplicate key value violates unique constraint and duplicated records

[GENERAL] Buy real Passports(designerdesig...@yahoo.com)Visa, Driving License, ID CARDS, marriage certificates, diplomas

[GENERAL] have trouble understanding xmin and xmax with update operations from two different sessions

9 matches

Site Navigation

Mail list logo

Footer information