Subject: Problems with concurrency

From: [email-masked] (Tom Vijlbrief)
Date: Mon, 4 Nov 91 11:42:37 -0800

Hallo Postgres developers,

I have created a communication scheme which allows processes to
be notified on changes to relations. The current implementation
uses normal relations. In the future this will be replaced
by asynch portals.

I create a relation ap_XXXXXX which is filled by rules
with a description of the changed object.

The relation 'geo_monitors' contains the description
of the relations to monitor.

I init the database with the following script:
================makeap================
#! /bin/sh
datab=${1:-geo}

monitor $datab << \EOF

destroy geo_ap \g

create geo_ap (hostname= char16, pid= int2) \g

destroy geo_monitors \g

create geo_monitors (geo_relation= char16, change_flags= int4) \g

destroy geo_active_apr \g

create geo_active_apr (monitor_oid= oid, change_flag= int4, ap_oid= oid) \g

EOF
============================

I create a test relation with the following script:

======================buildcol1==================
#! /bin/sh
datab=${1:-geo}
awk '
BEGIN   {
  printf "destroy col1 \\g\n"
  printf "create col1 (geo_loc=point, name=char16, height=float4, geo_color=int2) \\g\n"
  for (i= 0; i < 16; i++) {
        printf "append col1 (geo_loc=\"(%f,%f)\"::point, name=\"p%d\"::char16, height=\"%f\"::float4, geo_color=%d) \\g\n", \
                5.0+(i%25)/6, 52.0+i/9, i, (23.7 * i) % 24.7, i+2
  }

  printf "delete geo_monitors where geo_monitors.geo_relation = \"col1\"::char16 \\g\n"

  printf "append geo_monitors (geo_relation= \"col1\", change_flags= 7) \\g\n"
  }' < /dev/null | monitor $datab
========================================================

Now the fun starts (and my problem becomes clear).

The following script works fine and creates all the rules and
relations. Running two of these scripts in parallel causes the
backend to crash in various ways....

If I wait with starting the second script until the first
GEO process runs then I have no problems and everything works perfectly !

There are obviously some locking conflicts....

Tom
=============apgeo====================================
#! /bin/sh

# This script is a wrapper around GEO which implements 
# dynamic maps, e.g. maps on which changing objects are
# automatically redrawn.

MONITOR=monitor

ap_oid="-1"

# define our cleanup trap:

trap '
  case $do_exit in Y) echo "Cleaning up, please be patient..." ;; esac

  case $ap_oid in
    -1) exit ;;
    0) $MONITOR -TN -c "delete geo_ap where geo_ap.pid = $$" $GEODB; exit ;;
  esac

  for apr in `$MONITOR -TN -c "retrieve (geo_active_apr.oid) where
	geo_active_apr.ap_oid = \"$ap_oid\"::oid " $GEODB | sed "s/ //g" `
  do
    $MONITOR -TN -c "remove rewrite rule apr_$apr" $GEODB
  done

  $MONITOR -TN -c "destroy ap_$ap_oid" $GEODB
  $MONITOR -TN -c "delete geo_active_apr
		where geo_active_apr.ap_oid = \"$ap_oid\"::oid" $GEODB
  $MONITOR -TN -c "delete geo_ap where geo_ap.oid = \"$ap_oid\"::oid" $GEODB

  case $do_exit in Y) exit ;; esac
' 1 2 3 15

echo "Killing old rules and portals from died GEO processes..."

cnt=1
for gp in `$MONITOR -TN -c "retrieve (geo_ap.all, geo_ap.oid)" $GEODB`
do

  case $cnt in
  1) hostname=$gp; cnt=2; continue ;;
  2) pid=$gp; cnt=3; continue ;;
  3) oid=$gp; cnt=1;;
  esac

  if rsh $hostname kill -0 $pid | grep 'No such process'
  then
    echo "Pid $pid on $hostname is no longer alive"
    do_exit=N
    ap_oid=$oid
    kill -2 $$
    ap_oid="-1"
  else
    echo "Pid $pid on $hostname is still alive"
  fi
done

echo 'Fill geo_ap...'

hostname=`hostname`
do_exit=Y

ap_oid=0
$MONITOR -TN -c "append geo_ap(hostname= \"$hostname\", pid=$$)" $GEODB
ap_oid=`$MONITOR -TN -c "retrieve (geo_ap.oid)
	  where geo_ap.hostname = \"$hostname\"
	  and geo_ap.pid = $$" $GEODB | sed 's/ //g' `

echo "Asynch portal is: ap_$ap_oid"

$MONITOR -TN -c "create ap_$ap_oid(geo_relation= char16, the_oid= oid,
		change_flag= int4) archive = none" $GEODB

echo 'Defining rules...'

cnt=1
for mon in `$MONITOR -TN -c "retrieve (geo_monitors.all, geo_monitors.oid)" $GEODB`
do

  case $cnt in
  1) relation=$mon; cnt=2; continue ;;
  2) flags=$mon; cnt=3; continue ;;
  3) mon_oid=$mon; cnt=1;;
  esac

  echo "Relation: $relation Flags: $flags..."

  for f in 1 2 4
  do
    if test `expr $flags / $f % 2` -eq 1
    then

      echo "Flag: $f"

      $MONITOR -TN -c "append geo_active_apr (monitor_oid= $mon_oid,
	change_flag= $f, ap_oid= $ap_oid)" $GEODB
      rule_oid=`$MONITOR -TN -c "retrieve (geo_active_apr.oid) where
	geo_active_apr.ap_oid = \"$ap_oid\"::oid
	and geo_active_apr.monitor_oid = \"$mon_oid\"::oid
	and geo_active_apr.change_flag = $f" $GEODB | sed 's/ //g' `

      case $f in
	1) action="delete"; cur_oid="current.oid" ;;
	2) action="replace"; cur_oid="current.oid" ;;
	4) action="append"; cur_oid="new.oid" ;;
      esac

      $MONITOR -TN -c "define rewrite rule apr_$rule_oid is
	on $action to $relation do
	append ap_$ap_oid (geo_relation= \"$relation\",
	  the_oid= $cur_oid, change_flag= $f)" $GEODB
    fi
  done
done

echo 'Starting GEO...'

GEOAP="ap_$ap_oid,10" $GEOHOME/geo -noover -nortree -merge

# Clean up

kill $$