Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzC8f-002QJC-AW for pgsql-general@arkaria.postgresql.org; Tue, 23 Apr 2024 09:08:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rzC8d-007Oa1-R9 for pgsql-general@arkaria.postgresql.org; Tue, 23 Apr 2024 09:08:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzC8d-007OZt-7J for pgsql-general@lists.postgresql.org; Tue, 23 Apr 2024 09:08:31 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzC8Y-004Dz2-Cj for pgsql-general@lists.postgresql.org; Tue, 23 Apr 2024 09:08:29 +0000 Received: by mail-ed1-x52a.google.com with SMTP id 4fb4d7f45d1cf-56e37503115so4405880a12.1 for ; Tue, 23 Apr 2024 02:08:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=erinors-com.20230601.gappssmtp.com; s=20230601; t=1713863305; x=1714468105; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=IKj8e3rNOY6ZGrEEVFI66omEVHeKaDD/94Iq+uKze+8=; b=gh30w70OvSurehZMgz3hfMIDoMeSdmw+GgGUJ2yH4vZLoX7nwQXPFObct5cL/gaWDZ XMesw8mdzHRbG0S6SMfJ9Y8q08KoqCpBY5xWc6HQ2SFnTqxOyQCMmGdIj2Wor4SkJ7cw AQx1AXYj7BdcZPlr4a6yU5N655L/9Uovz0ZD+dz4kYsaGl9A2R2mbrJqMb40TWNwqezX 5QBTPD6666v3QSAZHjpUYWyDVG58qpknmEC87wyWSo2iyl9H9Ldn8hNGo4NdlKlnvKbd YAvXZMfdJjoM8Uzp2Pm68Sw9JO+HANWQPROEAOkxggILm3UmQwrcICAXi73z8b0FpJEk wN4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713863305; x=1714468105; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=IKj8e3rNOY6ZGrEEVFI66omEVHeKaDD/94Iq+uKze+8=; b=sJ33KeDHQ8kmqlRofdw7+sLtcbSzzSnP3b7VnXwuL0lXhcR9ih3OcQT7w+OwsNYT82 CSYSSyC0OCuml6P4FKMN3BeEnGxjqgA/qco0SSJKOWlk+wO2ezc6qVcDTYuJkvADe6gM ZGsn7jSNcGmS9Zu1UfqxEsz3dncvhuUN5t4RLoRHLEU4LALo2yjLA0mwA54eYRSLLJZ8 Vl9t85KSGFdKvwPUGBSnyTuR3Uu80AJ5E+0r4lNqpzYm4HjnpaO6CY9BSh/zrSwu0pKh 1O/zGuWl2aMBDIQCZ2eIrRXapEg/loEaH7D20+89SS6hsGyuh2cR8ComYDz3KF5aOL1e PTMQ== X-Forwarded-Encrypted: i=1; AJvYcCVneMB5xqAbR0rtYoL4GqieiyIc09QnggHDqvX6JZx5tmYVDnzzAv2M6qFY4gqmRrfGIKeC3enw9VFe67yImt4i7XzXjyUQ7RTmw4+rkoAgM+GY X-Gm-Message-State: AOJu0YyCzY+lX4Og2/cOHg49hhJ36Mcbpe1hse6/53HRsWo+lkDe6L0G Zh6cS3TEVq+HBO9Ot1NCQBKgYM0rFzOe2MeFwnbRc2NFbJu2NoHFnafEgJymBJ4= X-Google-Smtp-Source: AGHT+IE3mmKOzPYBnXBCP+j9RGNxyZgO5ZKiFh1Uy/BU7T/ABN1dalKvmiswoMvd9ga9cKIS03IJPw== X-Received: by 2002:a50:9b50:0:b0:571:bd88:e832 with SMTP id a16-20020a509b50000000b00571bd88e832mr6983903edj.41.1713863304490; Tue, 23 Apr 2024 02:08:24 -0700 (PDT) Received: from ?IPV6:2a02:ab88:850a:4c00:85c4:79c3:3df5:72a0? ([2a02:ab88:850a:4c00:85c4:79c3:3df5:72a0]) by smtp.gmail.com with ESMTPSA id ij6-20020a056402158600b005705bfeeb27sm6381946edb.66.2024.04.23.02.08.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 23 Apr 2024 02:08:23 -0700 (PDT) Message-ID: <0d83d817-0ae5-4b1b-af18-63ec89701301@erinors.com> Date: Tue, 23 Apr 2024 11:08:26 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: query multiple schemas To: Dominique Devienne , Tom Lane Cc: Steve Baldwin , pgsql-general@lists.postgresql.org References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> <2857958.1713733916@sss.pgh.pa.us> Content-Language: en-GB, hu From: =?UTF-8?Q?Norbert_S=C3=A1ndor?= In-Reply-To: Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > using a dynamic number of schemas

Although there are historical reasons behind our "schema per tenant" architecture, it provides very good logical separation of data, and is very convenient that we don't need to include the tenant ID in each query (I'm sure that it would cause lots of bugs and trouble).
Besides, we use Hibernate and it has great support for this architecture.

> In any case, I'm interested in what works well for you.

I went with the dynamic solution I proposed in my original email (which performs a UNION of the queried table from all tenants).
Performance is currently not a priority in our use-cases, otherwise I would have probably chosen a more static solution (which wouldn't be easy to maintain continuously, I'm sure of).

I further simplified its usage, so finally it is fairly comfortable to use either directly:
select tenantId, (record).*
from tenant_query(null::mytable)
where (record).type=2
order by tenantId, (record).name;
or by using a temporary view:
create temp view all_tenant_mytable as
select tenantId, (record).* from tenant_query(null::mytable);

select *
from all_tenant_mytable
where type=2
order by tenantId, name;
> In my case, the revision/version of the schema could be different as well

This complicates things very much, it is probably not possible to implement a solution as comfortable as in my case.

Although I think the JSON-based solutions proposed in this thread would work in your case as well.
In my current solution I also use JSON as intermediate representation - although I feel it is because of my lack of deeper knowledge of Postgresql's type system.
The difference is that you would need to use JSON as the final representation, and reference the JSON fields using Postgres's JSON operators.

> And if/when I get back to this issue myself, I'll do the same.

My current solution is not much different than the one I posted in my original question.
My main difficulty was the relatively static nature of Postgresql's type system, so this solution is a result of lots of trial-and-error rounds :)
Take a look at it, and you (and maybe others) may have recommendations, e.g. how I could get rid of the usage of the intermediate JSON "layer".
(Although I have to admit: it is amazing that it is possible to implement this at all ;) )

CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement)

RETURNS setof json AS $func$

declare

_select text;

begin

_select := (select

string_agg(

format('select t.*, %L tenantId from %I.%I t', schema_name, schema_name, pg_typeof(tbl)),

E'\n' || ' union all ' || E'\n')

from (

SELECT schema_name

FROM information_schema.schemata

where schema_name not in ('information_schema') and schema_name not like 'pg_%'

) tenants

);

return query execute 'select row_to_json(r) from (' || _select || ') as r';

END;

$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tenant_query(tbl anyelement)

RETURNS table(tenantId text, record anyelement) AS $func$

begin

return query

select t.tenantId, t.rec

from (

select

jr->>'tenantid' tenantId,

json_populate_record(tbl, jr) rec

from tenant_query_json(tbl) jr

) t;

END;

$func$ LANGUAGE plpgsql;

As you can see in my examples above, I use the tenant_query() function but in your case (if your schemas are different) something similar to tenant_query_json() may work better.

--
Norbi

On 2024. 04. 23. 9:33, Dominique Devienne wrote:
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Baldwin <steve.baldwin@gmail.com> writes:
> If the number of tenant schemas is reasonably static, you could write a
> plpgsql function to create a set of UNION ALL views

Another idea is to build a partitioned table

Hi Norbert. I asked a [similar question][1] a while back,
and unfortunately didn't get any actionable input, perhaps
because I already mentioned in my message the options
proposed here so far. Seems like people like us, using a
dynamic number of schemas, are outliers in database-land.

In my case, the revision/version of the schema could be
different as well, which would complicate the partitioning idea.

In any case, I'm interested in what works well for you.
And if/when I get back to this issue myself, I'll do the same.

Thanks, --DD