Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgxSo-0006L1-Gw for pgsql-bugs@arkaria.postgresql.org; Thu, 04 Jun 2020 21:31:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jgxSn-0005Tl-Du for pgsql-bugs@arkaria.postgresql.org; Thu, 04 Jun 2020 21:31:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgxR9-00020B-1U for pgsql-bugs@lists.postgresql.org; Thu, 04 Jun 2020 21:30:07 +0000 Received: from mail-ed1-x544.google.com ([2a00:1450:4864:20::544]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jgxR6-0004NO-CX for pgsql-bugs@lists.postgresql.org; Thu, 04 Jun 2020 21:30:05 +0000 Received: by mail-ed1-x544.google.com with SMTP id t21so3234132edr.12 for ; Thu, 04 Jun 2020 14:30:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bowt-ie.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=iwwhM41q4TgpaIiLR56UXLcRamI9z/aJrcV+Am3nwDY=; b=Y2P3/3hmpl2W2WSkemILFQw1Ph0gvaWtA8l4XCEUZu//Vg2X04q4280GyI0nyX3y5V jN1/NKj0tbCJkMuIwjfoLC+XI4O+z9QAhuXcau6HC11QTee+9yqyte+uw608ppgQ86i4 vjNG4ngtQDLqDhNNqxT91Q/BvwAFJlFS721s9puBUZgNN21aUXwSMaE1CE0Y4ThqKcYG LjnRbipyoNE6RqGBTjtHdtjAfSzh9cBU6iPhmMwAfwPy0u+PFaXbReXasPUUX1iVrZHs rYGPfSO1fZReP6v2mgq1IQMw2uD2vN5VnT/336VsJs41/b2Vqn31RA55LuufxWYsEGHp Jplw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=iwwhM41q4TgpaIiLR56UXLcRamI9z/aJrcV+Am3nwDY=; b=rRXn5WEZ6MWH/s1xi+7gJPZLaG62sAv98BtSB/ZMC3g23yRL1Hh127WAthgeTK14X1 njijZx0iNYgbHIxQaHPkyhTxgpII1qrK03DX7t85RRypl0jchjOJCxcDpAweV0imiEXl sI4Sxt4G+Kw14tJFFMMIQXAnsW1V7wl+RnUAECn49zyw0YlpHwwDCaqdNdumha10jlzQ 0uakK8ClbqwT5eJ1kKFxE8LCBIcEEWxhjCYYuD28aT327YOD7N2tqKnyAX3mHJguHi0Q HaxjVGLFnw/OIljrQLZA/FJQ2B8z2UaPiU6pueCewwtn5dzouY2+LDM1kpYVXspvkAy+ p8bA== X-Gm-Message-State: AOAM531VdiZFAP8YgZAVvia7PM69bqKYHwC9DPgoyHcO++d7235lFPib AyYvzZKX1YHnOdhUWW55QH7aFgOIANc4iY+k8ACg+w4ZVOY= X-Google-Smtp-Source: ABdhPJyJhzu+6JX/iELoKn1VyuLpyGVz+Wna5iVPiA8WTZ8vk+sQNfWYT8D005Mgykt9RGoqD7B9v1rim1R8Eq5Jm/0= X-Received: by 2002:a50:8dc2:: with SMTP id s2mr6473638edh.318.1591306202010; Thu, 04 Jun 2020 14:30:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Peter Geoghegan Date: Thu, 4 Jun 2020 14:29:44 -0700 Message-ID: Subject: Re: Potential G2-item cycles under serializable isolation To: Kyle Kingsbury Cc: PostgreSQL mailing lists Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, Jun 4, 2020 at 1:35 PM Kyle Kingsbury wrote: > >> How can I determine what SQL each transaction generates from these values? It's > >> not obvious to me which of the three tables (which of txn0, txn1, and txn2) are affected in each case. > > This is a good and obvious question which I don't yet have a good answer for. > Reading the source gives you *some* idea of what SQL's being generated, but > there's some stuff being done by next.jdbc and JDBC itself, so I don't know how > to show you *exactly* what goes over the wire. A terrible way to do this is to > look at the pcap traces in wireshark--you can correlate from the timestamps in > jepsen.log, or search for the transactions which interacted with specific keys. I'd appreciate it if you could provide this information, so I can be confident I didn't get something wrong. I don't really understand how Elle detects this G2-Item anomaly, nor how it works in general. PostgreSQL doesn't really use 2PL, even to a limited degree (unlike Oracle), so a lot of the definitions from the "Generalized Isolation Level Definitions"/Adya paper are not particularly intuitive to me. That said, I find it easy to understand why the "G2-item: Item Anti-dependency Cycles" example from the paper exhibits behavior that would be wrong for Postgres -- even in repeatable read mode. If Postgres exhibits this anomaly (in repeatable read more or serializable mode), that would be a case of a transaction reading data that isn't visible to its original transaction snapshot. The paper supposes that this could happen when another transaction (the one that updated the sum-of-salaries from the example) committed. Just being able to see the SQL executed by each of the two transactions would be compelling evidence of a serious problem, provided the details are equivalent to the sum-of-salaries example from the paper. > One option would be to add some sort of tracing thing to the test so that it > records the SQL statements it generates as extra metadata on operations. I can > look into doing that for you later on. :) If each Jepsen worker has its own connection for the duration of the test (which I guess must happen already), and each connection specified an informative and unique "application_name", it would be possible to see Jepsen's string from the Postgres logs, next to the SQL text. With prepared statements, you'd see the constants used, though not in all log messages (iirc they don't appear in error messages, but do appear in regular log messages). See: https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX If you can't figure out how to get JDBC to accept the application_name you want to provide, then you execute a "set application_name = 'my application name';" SQL statement within each Jepsen worker instead. Do this at the start, I suppose. Whichever approach is easiest and makes sense. You might have something like this in postgresql.conf to see the "application_name" string next to each statement from the log (This will increase the log volume considerably, which should still be manageable): log_line_prefix='%p %a %l' log_statement=all (I also suggest further customizing log_line_prefix in whatever way seems most useful to you.) -- Peter Geoghegan