sql - PostgreSQL concurrent transaction issues -

-

i'm building crawler. multiple crawling workers access same postgresql database. sadly i'm encountering issues main transaction presented here:

begin isolation level serializable;     update webpages     set locked = true     url in          (             select distinct on (source) url             webpages                             (                     last null                     or                     last < refreshfrequency                 )                 ,                 locked = false             limit limit         )     returning *; commit;
  • url url (string)
  • source domain name (string)
  • last last time page crawled (date)
  • locked boolean set indicate webpage being crawled (boolean)

i tried 2 different transaction isolation levels:

  • isolation level serializable, errors could not serialize access due concurrent update
  • isolation level read committed, duplicate urls concurrent transactions due data being "frozen" time transaction first committed (i think)

i'm new postgresql , sql in general i'm not sure fix issue.

update:
postgresql version 9.2.x.
webpage table definition:

create table webpages (   last timestamp time zone,   locked boolean default false,   url text not null,   source character varying(255) primary key );

clarification

the question leaves room interpretation. how understand task:

lock maximum of limit urls fulfill criteria , not locked, yet. spread out load on sources, every url should come different source.

db design

assuming separate table source: makes job faster , easier. if don't have such table, create it, it's proper design anyway:

create table source (   source_id serial not null primary key , source    text not null );  create table webpage (   source_id int not null references source   url       text not null primary key   locked    boolean not null default false,        -- may not needed   last      timestamp not null default '-infinity' -- makes query simpler );

alternatively can use recursive cte efficiently:

basic solution advisory locks

i using advisory locks make safe , cheap in default read committed isolation level:

update webpage w set    locked = true  (    select (select url              webpage             source_id = s.source_id            ,   (last >= refreshfrequency) not true            ,    locked = false            ,    pg_try_advisory_xact_lock(url)  -- true free            limit  1     -- 1 url per source           ) url     (       select source_id  -- fk column in webpage         source       order  random()       limit  limit      --  random selection of "limit" sources       ) s       update    ) l  w.url = l.url returning *;

alternatively, work only advisory locks , not use table column locked @ all. run the select statement. locks kept until end of transaction. can use pg_try_advisory_lock() instead keep locks till end of session. update once set last when done (and possible release advisory lock).

other major points

  • in postgres 9.3 or later use lateral join instead of correlated subquery.

  • i chose pg_try_advisory_xact_lock() because lock can (and should) released @ end of transaction. detailed explanation advisory locks:

  • you less limit rows if sources have no more url crawl.

  • the random selection of sources wild educated guess, since information not available. if source table big, there faster ways:

  • refreshfrequency should called lastest_last, since it's not "frequency", timestamp or date.

recursive alternatve

to full limit number of rows if available, use recursive cte , iterate sources until found enough or no more can found.

as mentioned above, may not need column locked @ , operate advisory locks (cheaper). set last @ end of transaction, before start next round.

with recursive s (    select source_id, row_number() on (order random()) rn    source  -- might exclude "empty" sources ...    ) , page(source_id, rn, ct, url) (    select 0, 0, 0, ''::text   -- dummy init row    union    select s.source_id, s.rn         , case when t.url <> ''                p.ct + 1                else p.ct end  -- inc. if url found last round         , (select url              webpage             source_id = t.source_id            ,   (last >= refreshfrequency) not true            ,    locked = false  -- may not needed            ,    pg_try_advisory_xact_lock(url)  -- true free            limit  1           -- 1 url per source           ) url            -- try, may come empty      page p    join   s on s.rn = p.rn + 1     case when p.url <> ''                p.ct + 1                else p.ct end < limit  -- limit here    ) select url   page  url <> '';             -- exclude '' , null

alternatively, if need manage locked, too, use query above update.

further reading

you love skip locked in the upcoming postgres 9.5:

related:


Comments

Post a Comment

Popular posts from this blog

SQL php on different pages to Insert (mysqli) -

-
i have piece of php code, works when placed on index page ( index.php ), nothing if put in page, i.e. add.php for example: having code add.php page, tried go to: www.myhost.com/add.php , nothing happen. if copy code index.php , go page www.myhost.com/index.php add row in sql table. why that? <?php $server="mysql.server.com"; $user="randomusr"; $pass="randompwd"; $db="randomdb"; $link=mysqli_connect($server, $user, $pass, $db); $result=mysqli_query($link,"select * `battery` `id`"); $query = "insert battery (id,type,owner,charge,time) values ('1','lily002','maisnow','15','2015-04-22 17:20:28')"; mysqli_query($link, $query); header("location: index.php"); //this 1 comment out when copied index.php ?> l # name type collation attributes null 1 id int(16) no ...
Read more

How to combine associative arrays in bash? -

-
does know of elegant way combine 2 associative arrays in bash normal array? here's i'm talking about: in bash can combine 2 normal arrays follows: declare -ar array1=( 5 10 15 ) declare -ar array2=( 20 25 30 ) declare -ar array_both=( ${array1[@]} ${array2[@]} ) item in ${array_both[@]}; echo "item: ${item}" done i want same thing 2 associative arrays, following code not work: declare -ar array1=( [5]=true [10]=true [15]=true ) declare -ar array2=( [20]=true [25]=true [30]=true ) declare -ar array_both=( ${array1[@]} ${array2[@]} ) key in ${!array_both[@]}; echo "array_both[${key}]=${array_both[${key}]}" done it gives following error: ./associative_arrays.sh: line 3: array_both: true: must use subscript when assigning associative array the following work-around came with: declare -ar array1=( [5]=true [10]=true [15]=true ) declare -ar array2=( [20]=true [25]=true [30]=true ) declare -a array_both=() key in ${!array1[@]}; ...
Read more

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co...
Read more