Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M318651=pgsql-hackers-arkari@postgresql.org>)
	id 1ch0vb-0001kX-3f
	for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Feb 2017 21:27:55 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M318651=pgsql-hackers-arkari@postgresql.org>)
	id 1ch0va-0003Ya-Ms
	for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Feb 2017 21:27:54 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <nico@cryptonector.com>)
	id 1ch0vZ-0003XE-2R
	for pgsql-hackers@postgresql.org; Thu, 23 Feb 2017 21:27:53 +0000
Received: from sub4.mail.dreamhost.com ([69.163.253.135]
 helo=homiemail-a55.g.dreamhost.com)
	by makus.postgresql.org with esmtps (TLS1.1:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <nico@cryptonector.com>)
	id 1ch0vW-0008NB-Gf
	for pgsql-hackers@postgresql.org; Thu, 23 Feb 2017 21:27:51 +0000
Received: from homiemail-a55.g.dreamhost.com (localhost [127.0.0.1])
	by homiemail-a55.g.dreamhost.com (Postfix) with ESMTP id 486E068003C2B;
	Thu, 23 Feb 2017 13:27:48 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=cryptonector.com; h=date
	:from:to:cc:subject:message-id:references:mime-version
	:content-type:in-reply-to; s=cryptonector.com; bh=K0H6YfXzQROGld
	JQv2UdDVCjqRM=; b=y1gV3RU+80NyIju88RW3rGFDXsa1LmfIGCSZkORjtnyImW
	pRbbw9fbqG/Hhs5UmIdYzyjf/BrzoQZVB1HL9VqUmvkQXL6qiHOioGVVD/6Y+6TK
	nuQGOzo7Gv4qKaCmuJcCD4p+ESNRSOjipJ6OmhYwVm98BcIWWK5btd5WZv+mc=
Received: from localhost (cpe-70-123-158-140.austin.res.rr.com
 [70.123.158.140])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	(Authenticated sender: nico@cryptonector.com)
	by homiemail-a55.g.dreamhost.com (Postfix) with ESMTPSA id DB97D68003C29;
	Thu, 23 Feb 2017 13:27:47 -0800 (PST)
Date: Thu, 23 Feb 2017 15:27:43 -0600
From: Nico Williams <nico@cryptonector.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joel Jacobson <joel@trustly.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Idea on how to simplify comparing two sets
Message-ID: <20170223212743.GD30233@localhost>
References: 
 <CAASwCXeXzwpLg4b1zWKdR9y4XW7Gt8Q=Di2nCRYs4ZT_gvMFeA@mail.gmail.com>
 <15376.1486483121@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <15376.1486483121@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Tue, Feb 07, 2017 at 10:58:41AM -0500, Tom Lane wrote:
> Joel Jacobson <joel@trustly.com> writes:
> > Currently there is no simple way to check if two sets are equal.
> 
> Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2
> and SELECT set2 EXCEPT SELECT set1 are both empty?

Even better, NATURAL(*) FULL OUTER JOIN the two table sources and check
that the result is empty.  If the two sources have useful indices (or if
PG constructs suitable automatic indices for them) for this then the
query should be O(N).

(*) However, if you do this then there'd better not be any NULLs in
columns, otherwise you'll get false positives for differences.  Of
course, if the two table sources have common primary key prefixes and
you only care about equality in those columns, then just FULL OUTER JOIN
USING (<primary key prefix>).

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers