Skip to content

Instantly share code, notes, and snippets.

@pgnickb

pgnickb/gist:1466853c663937c110a70f2fa054829e

Last active April 11, 2018 08:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pgnickb/1466853c663937c110a70f2fa054829e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pgnickb/1466853c663937c110a70f2fa054829e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
10:40 nick@nick *=# select * from generate_series(1, 100) f(i) where pg_try_advisory_lock(i) limit 5;
i
───
1
2
3
4
5
(5 rows)
Time: 0,166 ms
10:40 nick@nick *=# select * from pg_locks where locktype = 'advisory';
locktype │ database │ relation │ page │ tuple │ virtualxid │ transactionid │ classid │ objid │ objsubid │ virtualtransaction │ pid │ mode │ granted │ fastpath
──────────┼──────────┼──────────┼──────┼───────┼────────────┼───────────────┼─────────┼───────┼──────────┼────────────────────┼──────┼───────────────┼─────────┼──────────
advisory │ 16385 │ ∅ │ ∅ │ ∅ │ ∅ │ ∅ │ 0 │ 2 │ 1 │ 4/31 │ 6676 │ ExclusiveLock │ t │ f
advisory │ 16385 │ ∅ │ ∅ │ ∅ │ ∅ │ ∅ │ 0 │ 3 │ 1 │ 4/31 │ 6676 │ ExclusiveLock │ t │ f
advisory │ 16385 │ ∅ │ ∅ │ ∅ │ ∅ │ ∅ │ 0 │ 5 │ 1 │ 4/31 │ 6676 │ ExclusiveLock │ t │ f
advisory │ 16385 │ ∅ │ ∅ │ ∅ │ ∅ │ ∅ │ 0 │ 4 │ 1 │ 4/31 │ 6676 │ ExclusiveLock │ t │ f
advisory │ 16385 │ ∅ │ ∅ │ ∅ │ ∅ │ ∅ │ 0 │ 1 │ 1 │ 4/31 │ 6676 │ ExclusiveLock │ t │ f
(5 rows)
Time: 0,258 ms
10:40 nick@nick *=#
--------------------------------------
V2:
--------------------------------------
10:40 nick@nick *=# select pg_advisory_unlock_all();
pg_advisory_unlock_all
────────────────────────
(1 row)
Time: 0,226 ms
11:08 nick@nick *=# select objid from pg_locks where pid = pg_backend_pid() and locktype='advisory' order by objid;
objid
───────
(0 rows)
Time: 49,710 ms
11:08 nick@nick *=# select i
11:08 nick@nick *-# from
11:08 nick@nick *-# (
11:08 nick@nick *(# select i
11:08 nick@nick *(# from generate_series(1, 100) f(i)
11:08 nick@nick *(# where not exists(select from pg_locks where pid=pg_backend_pid() and objid=i)
11:08 nick@nick *(# order by i
11:08 nick@nick *(# limit 5
11:08 nick@nick *(# ) k(i)
11:08 nick@nick *-# where pg_try_advisory_lock(i);
i
───
1
2
3
4
5
(5 rows)
Time: 0,805 ms
11:08 nick@nick *=# select objid from pg_locks where pid = pg_backend_pid() and locktype='advisory' order by objid;
objid
───────
1
2
3
4
5
(5 rows)
Time: 0,345 ms
11:08 nick@nick *=# select i
from
(
select i
from generate_series(1, 100) f(i)
where not exists(select from pg_locks where pid=pg_backend_pid() and objid=i)
order by i
limit 5
) k(i)
where pg_try_advisory_lock(i);
i
────
6
7
8
9
10
(5 rows)
Time: 0,558 ms
11:08 nick@nick *=# select objid from pg_locks where pid = pg_backend_pid() and locktype='advisory' order by objid;
objid
───────
1
2
3
4
5
6
7
8
9
10
(10 rows)
Time: 0,764 ms
11:08 nick@nick *=#
@samsondav
Copy link

samsondav commented Apr 11, 2018 

with locks_held_by_this_session AS (
        SELECT objid AS id
        FROM pg_locks pl
        WHERE locktype = 'advisory'
        AND classid = 1759441536
        AND pl.pid = pg_backend_pid()
      )
            
select * from rihanna_jobs as j LEFT JOIN locks_held_by_this_session lh on lh.id = j.id WHERE pg_try_advisory_lock(1759441536, j.id) and lh.id is null AND failed_at IS NULL order by enqueued_at, j.id for update of j skip locked limit 2;

@samsondav
Copy link

samsondav commented Apr 11, 2018 

select * from rihanna_jobs as j WHERE NOT EXISTS (SELECT objid AS id
        FROM pg_locks pl
        WHERE locktype = 'advisory'
        AND classid = 1759441536
        AND pl.pid = pg_backend_pid()
        AND pl.objid = j.id
        )
       AND pg_try_advisory_lock(1759441536, j.id) AND failed_at IS NULL order by enqueued_at, j.id for update of j skip locked limit 2;

This always skips the first job. I.e. if I have

1
2
3
4

It will return 2, 3 on the first run and nothing on the second.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment