Commitf232d7c

anarazel

authored and

michaelpq

committed

tests: Restrict pg_locks queries in advisory_locks.sql to current database

Otherwise testing an existing installation can fail, if there are other locks,e.g. from one of the isolation tests.This was originally applied asc3315a7 in 16~, but it is possibleto see this test fail depending on the concurrent activity for olderactive branches.Reviewed-by: Michael Paquier <michael@paquier.xyz>Discussion:https://postgr.es/m/20221003234111.4ob7yph6r4g4ywhu@awork3.anarazel.deBackpatch-through: 12

1 parent9db4598 commitf232d7cCopy full SHA for f232d7c

File tree

2 files changed

+33

-30

lines changed

src/test/regress
- expected
  - advisory_lock.out
- sql
  - advisory_lock.sql

2 files changed

+33

-30

lines changed

`‎src/test/regress/expected/advisory_lock.out`

Lines changed: 16 additions & 15 deletions

Original file line number	Diff line number	Diff line change
`@@ -1,6 +1,7 @@`
`1`	`1`	`--`
`2`	`2`	`-- ADVISORY LOCKS`
`3`	`3`	`--`
	`4`	`+SELECT oid AS datoid FROM pg_database WHERE datname = current_database() \gset`
`4`	`5`	`BEGIN;`
`5`	`6`	`SELECT`
`6`	`7`	`pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),`
`@@ -11,7 +12,7 @@ SELECT`
`11`	`12`	`(1 row)`
`12`	`13`
`13`	`14`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`14`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`15`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`15`	`16`	`ORDER BY classid, objid, objsubid;`
`16`	`17`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`17`	`18`	`----------+---------+-------+----------+---------------+---------`
`@@ -28,7 +29,7 @@ SELECT pg_advisory_unlock_all();`
`28`	`29`
`29`	`30`	`(1 row)`
`30`	`31`
`31`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`32`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`32`	`33`	`count`
`33`	`34`	`-------`
`34`	`35`	`4`
`@@ -49,7 +50,7 @@ WARNING: you don't own a lock of type ShareLock`
`49`	`50`
`50`	`51`	`-- automatically release xact locks at commit`
`51`	`52`	`COMMIT;`
`52`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`53`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`53`	`54`	`count`
`54`	`55`	`-------`
`55`	`56`	`0`
`@@ -66,7 +67,7 @@ SELECT`
`66`	`67`	`(1 row)`
`67`	`68`
`68`	`69`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`69`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`70`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`70`	`71`	`ORDER BY classid, objid, objsubid;`
`71`	`72`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`72`	`73`	`----------+---------+-------+----------+---------------+---------`
`@@ -86,7 +87,7 @@ SELECT`
`86`	`87`
`87`	`88`	`ROLLBACK;`
`88`	`89`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`89`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`90`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`90`	`91`	`ORDER BY classid, objid, objsubid;`
`91`	`92`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`92`	`93`	`----------+---------+-------+----------+---------------+---------`
`@@ -111,7 +112,7 @@ WARNING: you don't own a lock of type ShareLock`
`111`	`112`	`t \| f \| t \| f \| t \| f \| t \| f`
`112`	`113`	`(1 row)`
`113`	`114`
`114`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`115`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`115`	`116`	`count`
`116`	`117`	`-------`
`117`	`118`	`0`
`@@ -128,7 +129,7 @@ SELECT`
`128`	`129`	`(1 row)`
`129`	`130`
`130`	`131`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`131`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`132`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`132`	`133`	`ORDER BY classid, objid, objsubid;`
`133`	`134`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`134`	`135`	`----------+---------+-------+----------+---------------+---------`
`@@ -148,7 +149,7 @@ SELECT`
`148`	`149`
`149`	`150`	`ROLLBACK;`
`150`	`151`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`151`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`152`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`152`	`153`	`ORDER BY classid, objid, objsubid;`
`153`	`154`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`154`	`155`	`----------+---------+-------+----------+---------------+---------`
`@@ -165,7 +166,7 @@ SELECT pg_advisory_unlock_all();`
`165`	`166`
`166`	`167`	`(1 row)`
`167`	`168`
`168`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`169`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`169`	`170`	`count`
`170`	`171`	`-------`
`171`	`172`	`0`
`@@ -184,7 +185,7 @@ SELECT`
`184`	`185`	`(1 row)`
`185`	`186`
`186`	`187`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`187`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`188`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`188`	`189`	`ORDER BY classid, objid, objsubid;`
`189`	`190`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`190`	`191`	`----------+---------+-------+----------+---------------+---------`
`@@ -195,7 +196,7 @@ SELECT locktype, classid, objid, objsubid, mode, granted`
`195`	`196`	`(4 rows)`
`196`	`197`
`197`	`198`	`COMMIT;`
`198`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`199`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`199`	`200`	`count`
`200`	`201`	`-------`
`201`	`202`	`0`
`@@ -213,7 +214,7 @@ SELECT`
`213`	`214`	`(1 row)`
`214`	`215`
`215`	`216`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`216`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`217`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`217`	`218`	`ORDER BY classid, objid, objsubid;`
`218`	`219`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`219`	`220`	`----------+---------+-------+----------+---------------+---------`
`@@ -233,7 +234,7 @@ SELECT`
`233`	`234`	`t \| t \| t \| t \| t \| t \| t \| t`
`234`	`235`	`(1 row)`
`235`	`236`
`236`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`237`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`237`	`238`	`count`
`238`	`239`	`-------`
`239`	`240`	`0`
`@@ -251,7 +252,7 @@ SELECT`
`251`	`252`	`(1 row)`
`252`	`253`
`253`	`254`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`254`		`-FROM pg_locks WHERE locktype = 'advisory'`
	`255`	`+FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid`
`255`	`256`	`ORDER BY classid, objid, objsubid;`
`256`	`257`	`locktype \| classid \| objid \| objsubid \| mode \| granted`
`257`	`258`	`----------+---------+-------+----------+---------------+---------`
`@@ -267,7 +268,7 @@ SELECT pg_advisory_unlock_all();`
`267`	`268`
`268`	`269`	`(1 row)`
`269`	`270`
`270`		`-SELECT count(*) FROM pg_locks WHERE locktype = 'advisory';`
	`271`	`+SELECT count(*) FROM pg_locks WHERE locktype = 'advisory' AND database = :datoid;`
`271`	`272`	`count`
`272`	`273`	`-------`
`273`	`274`	`0`

`‎src/test/regress/sql/advisory_lock.sql`

Lines changed: 17 additions & 15 deletions

Original file line number	Diff line number	Diff line change
`@@ -2,21 +2,23 @@`
`2`	`2`	`-- ADVISORY LOCKS`
`3`	`3`	`--`
`4`	`4`
	`5`	`+SELECToidAS datoidFROM pg_databaseWHERE datname= current_database() \gset`
	`6`	`+`
`5`	`7`	`BEGIN;`
`6`	`8`
`7`	`9`	`SELECT`
`8`	`10`	`pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2),`
`9`	`11`	`pg_advisory_xact_lock(1,1), pg_advisory_xact_lock_shared(2,2);`
`10`	`12`
`11`	`13`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`12`		`-FROM pg_locksWHERE locktype='advisory'`
	`14`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`13`	`15`	`ORDER BY classid, objid, objsubid;`
`14`	`16`
`15`	`17`
`16`	`18`	`-- pg_advisory_unlock_all() shouldn't release xact locks`
`17`	`19`	`SELECT pg_advisory_unlock_all();`
`18`	`20`
`19`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`21`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`20`	`22`
`21`	`23`
`22`	`24`	`-- can't unlock xact locks`
`@@ -28,7 +30,7 @@ SELECT`
`28`	`30`	`-- automatically release xact locks at commit`
`29`	`31`	`COMMIT;`
`30`	`32`
`31`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`33`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`32`	`34`
`33`	`35`
`34`	`36`	`BEGIN;`
`@@ -39,7 +41,7 @@ SELECT`
`39`	`41`	`pg_advisory_xact_lock(1,1), pg_advisory_xact_lock_shared(2,2);`
`40`	`42`
`41`	`43`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`42`		`-FROM pg_locksWHERE locktype='advisory'`
	`44`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`43`	`45`	`ORDER BY classid, objid, objsubid;`
`44`	`46`
`45`	`47`	`SELECT`
`@@ -49,7 +51,7 @@ SELECT`
`49`	`51`	`ROLLBACK;`
`50`	`52`
`51`	`53`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`52`		`-FROM pg_locksWHERE locktype='advisory'`
	`54`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`53`	`55`	`ORDER BY classid, objid, objsubid;`
`54`	`56`
`55`	`57`
`@@ -60,7 +62,7 @@ SELECT`
`60`	`62`	`pg_advisory_unlock(1,1), pg_advisory_unlock(1,1),`
`61`	`63`	`pg_advisory_unlock_shared(2,2), pg_advisory_unlock_shared(2,2);`
`62`	`64`
`63`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`65`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`64`	`66`
`65`	`67`
`66`	`68`	`BEGIN;`
`@@ -71,7 +73,7 @@ SELECT`
`71`	`73`	`pg_advisory_lock(1,1), pg_advisory_lock_shared(2,2);`
`72`	`74`
`73`	`75`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`74`		`-FROM pg_locksWHERE locktype='advisory'`
	`76`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`75`	`77`	`ORDER BY classid, objid, objsubid;`
`76`	`78`
`77`	`79`	`SELECT`
`@@ -81,14 +83,14 @@ SELECT`
`81`	`83`	`ROLLBACK;`
`82`	`84`
`83`	`85`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`84`		`-FROM pg_locksWHERE locktype='advisory'`
	`86`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`85`	`87`	`ORDER BY classid, objid, objsubid;`
`86`	`88`
`87`	`89`
`88`	`90`	`-- releasing all session locks`
`89`	`91`	`SELECT pg_advisory_unlock_all();`
`90`	`92`
`91`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`93`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`92`	`94`
`93`	`95`
`94`	`96`	`BEGIN;`
`@@ -102,12 +104,12 @@ SELECT`
`102`	`104`	`pg_advisory_xact_lock_shared(2,2), pg_advisory_xact_lock_shared(2,2);`
`103`	`105`
`104`	`106`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`105`		`-FROM pg_locksWHERE locktype='advisory'`
	`107`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`106`	`108`	`ORDER BY classid, objid, objsubid;`
`107`	`109`
`108`	`110`	`COMMIT;`
`109`	`111`
`110`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`112`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`111`	`113`
`112`	`114`	`-- grabbing session locks multiple times`
`113`	`115`
`@@ -118,7 +120,7 @@ SELECT`
`118`	`120`	`pg_advisory_lock_shared(2,2), pg_advisory_lock_shared(2,2);`
`119`	`121`
`120`	`122`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`121`		`-FROM pg_locksWHERE locktype='advisory'`
	`123`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`122`	`124`	`ORDER BY classid, objid, objsubid;`
`123`	`125`
`124`	`126`	`SELECT`
`@@ -127,7 +129,7 @@ SELECT`
`127`	`129`	`pg_advisory_unlock(1,1), pg_advisory_unlock(1,1),`
`128`	`130`	`pg_advisory_unlock_shared(2,2), pg_advisory_unlock_shared(2,2);`
`129`	`131`
`130`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`132`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`
`131`	`133`
`132`	`134`	`-- .. and releasing them all at once`
`133`	`135`
`@@ -138,9 +140,9 @@ SELECT`
`138`	`140`	`pg_advisory_lock_shared(2,2), pg_advisory_lock_shared(2,2);`
`139`	`141`
`140`	`142`	`SELECT locktype, classid, objid, objsubid, mode, granted`
`141`		`-FROM pg_locksWHERE locktype='advisory'`
	`143`	`+FROM pg_locksWHERE locktype='advisory'AND database= :datoid`
`142`	`144`	`ORDER BY classid, objid, objsubid;`
`143`	`145`
`144`	`146`	`SELECT pg_advisory_unlock_all();`
`145`	`147`
`146`		`-SELECTcount(*)FROM pg_locksWHERE locktype='advisory';`
	`148`	`+SELECTcount(*)FROM pg_locksWHERE locktype='advisory'AND database= :datoid;`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commitf232d7c

File tree

2 files changed

2 files changed

`‎src/test/regress/expected/advisory_lock.out`

`‎src/test/regress/sql/advisory_lock.sql`

0 commit comments