Delivery-Date: Thu, 14 Oct 93 08:14:32 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9310141507.AA08959@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Thu, 14 Oct 93 11:11:15 -0400
From: witr@rwwa.COM
Message-Id: <9310141511.AA02499@relay2.UU.NET>
To: uunet!postgres.Berkeley.EDU!postgres@uunet.UU.NET
Subject: Concurrency and locking
Content-Type: text
Content-Length: 1323
Resent-To: postgres-dist
Resent-Date: Thu, 14 Oct 93 08:07:48 PDT

The following short pgperl program demonstrates `shortcomings' with
perl's concurrency and locking.  When I run two or more copies of it
I get *both* stale data updates and deadlocks.

This has been discussed before, but I don't understand what can or
should be done to remedy this so that this sort of thing works as one
would expect, that is it should demonstrate *neither* deadlocks or
stale data problems.

#!/usr/local/bin/pgperl

# Test postgres concurrency.
# 1) Createdb concur
# 2) Monitor concur
#    destroy TEST \g
#    create TEST (tkey = text, tvalue = int4) \g
#    append TEST (tkey = "foo", tvalue = 1) \g
# 3) Run two or more copies of this program.

$dbname = "concur";
&PQsetdb ($dbname);

while (1) {
  &PQexec("begin");
  $query = "retrieve portal p (t.tvalue) from t in TEST where t.tkey =
\"foo\" ";
  &PQexec($query);
  &PQexec("fetch all in p");

  $p = &PQparray("p");
  $n = &PQntuples($p);

  $value = &PQgetvalue($p, $n-1, 0);
  $value += 1;

  print("Replacing tvalue with $value.\n");
  $query = "replace TEST(tvalue=$value) where TEST.tkey = \"foo\" ";
  &PQexec($query);

  &PQexec ("close p");
  &PQexec ("end");
  &PQclear("p");
}
---
 Robert Withrow, Tel: +1 617 598 4480, Fax: +1 617 598 4430, Net: witr@rwwa.COM
 R.W. Withrow Associates, 21 Railroad Ave, Swampscott MA 01907-1821 USA