Re: pg_stat_statements
Hi, On Tue, Jan 11, 2022 at 03:04:14PM +, Dirschel, Steve wrote: > > I'm not sure if this is the correct distribution list for this type of > question but I'll try anyways. We have an app that uses multiple schemas. > It will do a set schema 'schema_name' and execute queries. The queries > executed are the same regardless of the schema the connection set. > > In pg_stat_statements the exact same query will get a different queryid for > each schema that executes the query. > > I'm unable to determine which queryid comes from which schema the query was > executed under. Is anyone aware of a way to determine this? > Unfortunately this is a known limitation. There were some previous discussions (e.g. [1] and [2] more recently), but I don't think there was a real consensus on how to solve that problem. Storing a query text with fully qualified names (either the current query or a new field) is not practical for performance purpose, but there were no objections to storing additional information, like the active search_path when the entry was created. But as noted, while it technically gives the information you need it's far from being convenient to use, which is probably why it was never implemented. [1] https://www.postgresql.org/message-id/flat/8f54c609-17c6-945b-fe13-8b07c0866420%40dalibo.com [2] https://www.postgresql.org/message-id/flat/9baf5c06-d6ab-c688-010c-843348e3d98c%40gmail.com
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
On 1/11/22 01:34, Dominique Devienne wrote: On Mon, Jan 10, 2022 at 11:13 PM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: I go into use cases in my previous message, send a minute ago. In your OP the error occurred here: DROP OWNED BY "Epos-PNS (a73e1fbdcc5b4460ba55703a0d6b6ad7)" CASCADE The CASCADE allows to command to recurse to objects that may not be owned by the specified role. Is that what you want? Sure. Although that's irrelevant. There are no such other objects. Except we did not know that until now. Each 2+N schemas "instance" is self-contained, and all ROLEs associated to those SCHEMAs only have privileges associated to those schemas. Actual LOGIN USERs are granted access to a subset of ROLEs in "instances" based on individual permissions. So when I nuke an "instance", I DROP all its SCHEMAs, and all their associated ROLEs. Actually you do DROP OWNED BY ... CASCADE. And DROP'ing those "instance"-specific ROLEs implicitly severs the GRANTs made on LOGIN USERs, who remain. What is the purpose of the process, clearing out given schema, dropping objects only owned by a given role, or something else? "dropping objects only owned by a given role" indeed. I.e. exactly what DROP OWNED BY is designed to do, no? As pointed out it can end up dropping objects owned by other roles due to dependencies. This means DROP OWNED BY ... CASCADE can extend past 'dropping objects only owned by a given role'. It just so happens that it seems impossible to do that, because that involves too many locks :(. It is not impossible, just difficult to predict what to set max_locks_per_transaction to? With 1400 relations for each of the N schemas, and actual in-prod instances at client sites that require hundreds such schemas (on the current system not based on PostgreSQL), I'm guessing So now we get to the crux of the issue, this is a migration from another system. It would be useful to know what that system is and how it is handled there. There may be people on this list that have similar experience's. I can't assign 1400 * 1000 = over a million locks on the cluster, can I? So now the question I asked in that other message, is whether I should use a dedicated DB per "instance" instead? --DD -- Adrian Klaver adrian.kla...@aklaver.com
Re: Template for schema? (as opposed to databases)
On Tue, Jan 11, 2022 at 9:42 AM Dominique Devienne wrote: > On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Tuesday, January 11, 2022, Dominique Devienne >> wrote: >>> >>> This means the template-schema name is part of the DDL for the schema, >>> and a clone would need to use its own search-path, not the original. >>> >> >> This is your major limitation. You are required to create new objects >> from code and cannot leverage any kind of copy of existing objects. >> > > But how to avoid that limitation? > Sometimes limitations cannot be avoided...in this case however: Get rid of the libpq requirement and you have options...namely I would create a template database (with 4 schemas) during a "build" stage and backup the containing cluster using pg_basebackup; and then during the "testing" I would restore that cluster in the testing environment as part of its setup and then copy the template database as needed to support the testing. That avoids all of the parse/execute of SQL that happens in the server when using libpq. Note: pg_dump/pg_restore is a libpq implemented procedure. > >> Beyond that there is too little detail to provide suggestions. >> > > What kind of further details would you need? > The precise nature of these tests. How many there are of what general types/purpose. How long they take to run. What kind of changes to the build/test system are reasonable. How inflexible are the constraints that have been provided (or, at minimum, why are they there at all). > > >> I would observe that for testing the meaningful values of N are 1 and 2. >> Having a setup where N = 5 is not materially different than the N = 2 case >> (usually). > > > W/o more context, sure, that seems logical. But in this case, that's > unlikely, to simulate the "real world". > > It doesn't really need context - it's a base premise. There are only three kinds of meaningful cardinality. Zero, One, More than One (which is sufficiently handled by using a quantity of 2). Abide by that unless you can argue why breaking down "more than one" is important. Unless you are doing a performance test, simulating the "real world" to the fullest is not something that your testing environment needs to do. David J.
Re: Template for schema? (as opposed to databases)
On Tue, Jan 11, 2022 at 5:38 PM Francisco Olarte wrote: > On Tue, 11 Jan 2022 at 17:10, Dominique Devienne > wrote: > ... > > Creating the first two "fixed" schemas is fast enough. 300ms, including > populating the 2nd with a bunch of rows. > > But creating the 3rd kind, which will be done many times, is not so > fast, at 2.5 - 3s, which may seem fast, > > but when you do it dozens of time in unit-tests, adds up quickly to slow > running tests... > > First question would be, are you running a test server? No. This is a "site" server, used by everyone. Devs and testers / users. > Are you testing crashes of just application logic? > Well, I try to write code that does not crash :) But yes, it's testing results of running the code are as expected. > For these I normally use wal_level=minimal, fsync=off, same as for > full cluster restores ( it is faster to redo it if restore fails, > reinitdb on test cases ), it normally gives quite a nice performance boost. > I'm well versed in libpq, not so much in server admin. The server is "remote" and "just runs", and I connect to it... --DD
Re: Template for schema? (as opposed to databases)
On Tue, Jan 11, 2022 at 5:38 PM Francisco Olarte wrote: > > Right now, that implies quite a few round-trips to the server, about > 1'100, but on the LAN with sub-ms latency; > > How much sub-ms? I mean, I have 0.74 pings to my router, but this > would be .82s, a good chunk of your quoted 2-3s, (and I'm not sure if > libpq can do all queries in one RTT ). > Windows ping only says: bytes=32 time<1ms TTL=64 But Linux ping does say: icmp_seq=8 ttl=64 time=0.236 ms (I picked the worse one). So even with 1K round-trips, that should account only for +/- 250ms (1/10th the total). --DD
Re: Template for schema? (as opposed to databases)
On Tue, Jan 11, 2022 at 5:30 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, January 11, 2022, Dominique Devienne > wrote: >> >> This means the template-schema name is part of the DDL for the schema, >> and a clone would need to use its own search-path, not the original. >> > > This is your major limitation. You are required to create new objects > from code and cannot leverage any kind of copy of existing objects. > But how to avoid that limitation? Triggers in a schema should functions correctly, whether or not client sessions set the search_path, or use fully qualified object names. I was actually surprised that functions from the schema itself (where the trigger is defined), do "not bind more tightly" to the dot (.) schema, the "owner" schema of the trigger, compared to functions elsewhere. Perhaps there's something I'm missing around trigger and name resolution? > Unless the test database need only be three schemas always - then the > schema can be identical because you can place them into different databases. > No, as I wrote, it's 2+N, > > >> Please do note this needs to be done from a libpq client, which has no >> direct access to the server (i.e. cannot use dump/restore solutions). >> > > PQexec and put the entire script into a single string. That seems like > the fastest possible way to create new objects (see the first point). > That didn't occur to me indeed. As it's not server-side. I might try that. Although I'd prefer a server-side solution. > Beyond that there is too little detail to provide suggestions. > What kind of further details would you need? > I would observe that for testing the meaningful values of N are 1 and 2. > Having a setup where N = 5 is not materially different than the N = 2 case > (usually). W/o more context, sure, that seems logical. But in this case, that's unlikely, to simulate the "real world". Thanks, --DD
Re: Template for schema? (as opposed to databases)
Hi Dominique: On Tue, 11 Jan 2022 at 17:10, Dominique Devienne wrote: ... > Creating the first two "fixed" schemas is fast enough. 300ms, including > populating the 2nd with a bunch of rows. > But creating the 3rd kind, which will be done many times, is not so fast, at > 2.5 - 3s, which may seem fast, > but when you do it dozens of time in unit-tests, adds up quickly to slow > running tests... First question would be, are you running a test server? Are you testing crashes of just application logic? For these I normally use wal_level=minimal, fsync=off, same as for full cluster restores ( it is faster to redo it if restore fails, reinitdb on test cases ), it normally gives quite a nice performance boost. ... > Right now, that implies quite a few round-trips to the server, about 1'100, > but on the LAN with sub-ms latency; How much sub-ms? I mean, I have 0.74 pings to my router, but this would be .82s, a good chunk of your quoted 2-3s, (and I'm not sure if libpq can do all queries in one RTT ). ... > One idea I had, similar to the template-DB feature, would be to create 1 > largish read-only schema at installation time > (i.e. not create only 2 but the 3 kinds of schemas), and have the ability to > clone this 3rd (largish) kind entirely server-side. You could try to use do-blocks, or even create procedures, even parametrized. If all schemas are similar you could theoretically send a parametrized procedure to create one, send another to execute it N times, execute it, drop both. Basically, send some of your logic to the database. > So if there a way to close a schema easily in PostgreSQL? s/close/clone/, I assume. ... > Please do note this needs to be done from a libpq client, which has no direct > access to the server (i.e. cannot use dump/restore solutions). pg_dump/pg_restore work with the same connection as libpq, I even suspect they are written using libpq ( but the need to use your own client may preclude using dump/restore ). Of course these are generalities for speeding up tsts. FOS.
Re: Template for schema? (as opposed to databases)
On Tuesday, January 11, 2022, Dominique Devienne wrote: > > This means the template-schema name is part of the DDL for the schema, and > a clone would need to use its own search-path, not the original. > This is your major limitation. You are required to create new objects from code and cannot leverage any kind of copy of existing objects. Unless the test database need only be three schemas always - then the schema can be identical because you can place them into different databases. > Please do note this needs to be done from a libpq client, which has no > direct access to the server (i.e. cannot use dump/restore solutions). > PQexec and put the entire script into a single string. That seems like the fastest possible way to create new objects (see the first point). Beyond that there is too little detail to provide suggestions. I would observe that for testing the meaningful values of N are 1 and 2. Having a setup where N = 5 is not materially different than the N = 2 case (usually). David J.
Template for schema? (as opposed to databases)
Hi, As many have surmised from my previous messages, I need to create a set of schemas, where two schemas as smallish and distincts, with an arbitrary number of additional schemas (of a 3rd kind), which is "largish" (244 tables, 4'000+ columns, 1'300+ relations). That's why I say / write that I have a system of 2+N schemas. All 3 schemas types are created from code, which issues a bunch of DDL statements via libpq. Creating the first two "fixed" schemas is fast enough. 300ms, including populating the 2nd with a bunch of rows. But creating the 3rd kind, which will be done many times, is not so fast, at 2.5 - 3s, which may seem fast, but when you do it dozens of time in unit-tests, adds up quickly to slow running tests... My question is what could be done to speed up the creation of that 3rd type of schema? Right now, that implies quite a few round-trips to the server, about 1'100, but on the LAN with sub-ms latency; And IO wise, totals less than 1MB (since mostly DDLs): EXEC: 1,125x (0 rows,445,373 bytes) in 1.810s ( 0.2 MB/s) RSET: 1,187x ( 14 rows,263,480 bytes) in 0.000s ( 0.0 MB/s) (1.8s is spent in libpq itself, the rest to reach to 2.5s - 3s are overhead in our own code, to generate the schema). One idea I had, similar to the template-DB feature, would be to create 1 largish read-only schema at installation time (i.e. not create only 2 but the 3 kinds of schemas), and have the ability to clone this 3rd (largish) kind entirely server-side. So if there a way to close a schema easily in PostgreSQL? Do note our triggers use the set search_path to avoid relying on the session's search_path, to make sure we call "our" functions (in schema). (we have different client code-bases, some set the search-path, others don't, and that's the only way we found to have the trigger function correctly). This means the template-schema name is part of the DDL for the schema, and a clone would need to use its own search-path, not the original. At this point, the schema is "standalone", it does not have foreign-keys outside itself, nor does it use functions/procedures from the other 2 schemas. But in the future it could. And right now, the schema does use functions from extensions (e.g. uuid-ossp, lo, ...). In all cases, the clone should continue referencing external objects as-is. If the "SCHEMA template" idea is difficult to pull of, has anyone experience on the gains from issuing the CREATE TABLE DDLs as part of the CREATE SCHEMA one? That would result in fewer round-trips, but I somehow doubt it would gain much (again, because on the LAN with sub-ms latencies). Any other tips on how to clone a schema? Or speed up creation of a schema in general? Please do note this needs to be done from a libpq client, which has no direct access to the server (i.e. cannot use dump/restore solutions). Thanks for any insights on the above. --DD
Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
út 11. 1. 2022 v 16:51 odesílatel napsal:
> Hi, Thank you for pointing this part of the documentation.
> It's actually works with EXECUTE 'INSERT INTO my_table VALUES((random() *
> 100)::INT);'; INSTEAD OF INSERT INTO my_table VALUES((random() * 100)::INT);
> And it's possible to suppose that the test_search_path_v2 worked before
> because of the
> PERFORM set_config('search_path', '"$user", public', true);
> I imagine that the changement of search_path have on side effect to
> invalidate the cached plans here and force to recompute the query plan.
> I imagine that the probleme appears in test_search_path_v1 after second
> call maybe because the cache is kept by following rules such as a certain
> number of executions
>
> In this example, use EXECUTE only in INSERT INTO my_table is sufficient to
> remove the problem.
> subsequent SELECT works (without the EXECUTE).
> Does doing an EXECUTE on a table have the side effect of invalidating
> caches using that table name or is it just a "chance" here and for added
> security I should use EXECUTE everywhere?
>
EXECUTE uses one shot plan - this plan is not cached. It has not any impact
on others' plans.
Regards
Pavel
> Thanks
>
> De : "David G. Johnston"
> A : "benj@laposte.net" ,"pgsql-gene...@postgresql.org"
> Envoyé: mardi 11 Janvier 2022 15:18
> Objet : Re: plpgsql function problem whith creating temp table - not
> correctly using search_path ?
>
> On Tuesday, January 11, 2022, wrote:
>>
>>
>> SHOW search_path -- => "$user", public;
>> DROP TABLE IF EXISTS my_table;
>> -- Executing each row on differents transactions but in the same session
>> /*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true);
>> -- => OK takes table from pg_temp (no existing table in public)
>> /*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false);
>> -- => OK takes table from public
>> /*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true);
>> -- => OK takes table from pg_temp (and the existing from public)
>> /*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false);
>> -- => OK takes table from public
>> /*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true);
>> -- => NOK => it takes public and not pg_temp
>
>
> Per [1], you are violating:
>
> “Because PL/pgSQL saves prepared statements and sometimes execution plans
> in this way, SQL commands that appear directly in a PL/pgSQL function
> must refer to the same tables and columns on every execution; that is, you
> cannot use a parameter as the name of a table or column in an SQL command.”
>
> While that wording is odd, it is not even possible to use variables in
> place of table and column names, what you are doing here is effectively the
> same. I cannot explain the precise observed behavior, and maybe there is a
> bug here, but on its face what you are doing is simply invalid in face of
> how the session relation cache works.
>
> [1]
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>
>
> David J.
>
>
Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
Hi, Thank you for pointing this part of the documentation.
It's actually works with EXECUTE 'INSERT INTO my_table VALUES((random() *
100)::INT);'; INSTEAD OF INSERT INTO my_table VALUES((random() * 100)::INT);
And it's possible to suppose that the test_search_path_v2 worked before because
of the
PERFORM set_config('search_path', '"$user", public', true);
I imagine that the changement of search_path have on side effect to invalidate
the cached plans here and force to recompute the query plan.
I imagine that the probleme appears in test_search_path_v1 after second call
maybe because the cache is kept by following rules such as a certain number of
executions
In this example, use EXECUTE only in INSERT INTO my_table is sufficient to
remove the problem.
subsequent SELECT works (without the EXECUTE).
Does doing an EXECUTE on a table have the side effect of invalidating caches
using that table name or is it just a "chance" here and for added security I
should use EXECUTE everywhere?
Thanks
De : "David G. Johnston"
A : "benj@laposte.net" ,"pgsql-gene...@postgresql.org"
Envoyé: mardi 11 Janvier 2022 15:18
Objet : Re: plpgsql function problem whith creating temp table - not correctly
using search_path ?
On Tuesday, January 11, 2022, wrote:
SHOW search_path -- => "$user", public;
DROP TABLE IF EXISTS my_table;
-- Executing each row on differents transactions but in the same session
/*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true); -- =>
OK takes table from pg_temp (no existing table in public)
/*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); -- =>
OK takes table from public
/*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true); -- =>
OK takes table from pg_temp (and the existing from public)
/*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); -- =>
OK takes table from public
/*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true); -- =>
NOK => it takes public and not pg_temp
Per [1], you are violating:
“Because PL/pgSQL saves prepared statements and sometimes execution plans in
this way, SQL commands that appear directly in a PL/pgSQL function must refer
to the same tables and columns on every execution; that is, you cannot use a
parameter as the name of a table or column in an SQL command.”
While that wording is odd, it is not even possible to use variables in place of
table and column names, what you are doing here is effectively the same. I
cannot explain the precise observed behavior, and maybe there is a bug here,
but on its face what you are doing is simply invalid in face of how the session
relation cache works.
[1]
https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
David J.
Re: Folding of case of identifiers
Niels Jespersen writes: > According to https://www.postgresql.org/docs/current/sql-syntax-lexical.html, > "Key words and unquoted identifiers are case insensitive." And "SQL > identifiers and key words must begin with a letter (a-z, but also letters > with diacritical marks and non-Latin letters) or an underscore (_). > Subsequent characters in an identifier or key word can be letters, > underscores, digits (0-9), or dollar signs ($)." > So far so good. Non-latin letters are included, which I take to also include > the danish letters æøå/ÆØÅ. > However, name-folding is odd for these letters. Of these three create tables, > the two first succeed, the last one does not (G and g is equivalent, Æ and æ > is not). Whether non-ASCII characters get downcased is very context dependent. You've not mentioned the database encoding or the locale (LC_CTYPE) setting, but both of those are relevant. Basically, in a single-byte encoding we'll apply tolower() to identifier characters; but we don't attempt to case-fold multi-byte characters at all. This logic is pretty hoary, dating from before Unicode became widespread, but I'd be hesitant to change it now. regards, tom lane
Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
benj@laposte.net writes: > -- Executing each row on differents transactions but in the same session > /*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true); -- => > OK takes table from pg_temp (no existing table in public) > /*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); -- => > OK takes table from public > /*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true); -- => > OK takes table from pg_temp (and the existing from public) > /*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); -- => > OK takes table from public > /*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true); -- => > NOK => it takes public and not pg_temp I think what you've done here is maneuver the plpgsql function into a state where it has a cached query plan [1] using the public table. Dropping the public table, or changing your search_path, would invalidate that cached plan ... but creating a new temp table does not. (This isn't specific to temp tables --- in any situation where you have more than one schema in the search path, creation of a new object could mask objects later in the path, and we won't invalidate plans just because that possibly happened.) My advice is "don't do that", ie, avoid masking permanent objects with temporary ones. It's extremely confusing to humans as well as machines. If you must do it, DISCARD PLANS might help you with keeping plpgsql functions in line. regards, tom lane [1] https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Dominique Devienne writes: > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you > suggest) requires just the same. But it doesn't. regards, tom lane
pg_stat_statements
Hello, I'm not sure if this is the correct distribution list for this type of question but I'll try anyways. We have an app that uses multiple schemas. It will do a set schema 'schema_name' and execute queries. The queries executed are the same regardless of the schema the connection set. In pg_stat_statements the exact same query will get a different queryid for each schema that executes the query. I'm unable to determine which queryid comes from which schema the query was executed under. Is anyone aware of a way to determine this? Thanks in advance. Steve This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
On Tuesday, January 11, 2022, wrote: > > SHOW search_path -- => "$user", public; > DROP TABLE IF EXISTS my_table; > -- Executing each row on differents transactions but in the same session > /*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true); -- > => OK takes table from pg_temp (no existing table in public) > /*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); -- > => OK takes table from public > /*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true); -- > => OK takes table from pg_temp (and the existing from public) > /*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); -- > => OK takes table from public > /*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true); -- > => NOK => it takes public and not pg_temp > Per [1], you are violating: “Because PL/pgSQL saves prepared statements and sometimes execution plans in this way, SQL commands that appear directly in a PL/pgSQL function must refer to the same tables and columns on every execution; that is, you cannot use a parameter as the name of a table or column in an SQL command.” While that wording is odd, it is not even possible to use variables in place of table and column names, what you are doing here is effectively the same. I cannot explain the precise observed behavior, and maybe there is a bug here, but on its face what you are doing is simply invalid in face of how the session relation cache works. [1] https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING David J.
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
On Tue, Jan 11, 2022 at 12:47 PM Wolfgang Walther wrote: > Dominique Devienne: > > I wish for DB-specific ROLEs BTW... > > Same here. That would be so useful. > In fact, in my case, I also want something even narrower than that, which are SCHEMA specific ROLEs. ROLEs tied to a given schema, implicitly DROP'ed when their "owner" SCHEMA is DROP'ed , and which can only take GRANTs/privileges on objects from it owner schema. I'm not saying CLUSTER-wide ROLEs are not useful. They are, mostly for LOGIN USERs IMHO. But for NOLOGIN ROLEs used to group permissions, often in a single DB, or even a single SCHEMA like in my case, the fact ROLEs are CLUSTER-wide is problematic for the naming. FWIW. --DD PS: I've read the note that DB-specific ROLEs kinda exists, but since the doc explicitly mentions to avoid them, I don't use them. And in case, as I wrote above, SCHEMA-correlated ROLEs is what I really would like to use.
Re: plpgsql function problem whith creating temp table - not correctly using search_path ?
út 11. 1. 2022 v 10:54 odesílatel napsal:
> Hi,
>
> I would like to submit a problem (bug ?) that I encountered while handling
> temporary tables in plpgsql functions.
>
> First, if I create a TABLE and a TEMP TABLE with the same name, and I
> request without specified the schema, the temporary table is used.
>
> -- SHOW search_path; -- => "$user", public
> -- Exectute next commands in the same transaction
> /* Début Transaction 1 */
> DROP TABLE IF EXISTS public.myexemple;
> DROP TABLE IF EXISTS pg_temp.myexemple;
> CREATE TABLE IF NOT EXISTS myexemple(i INT);
> CREATE TEMP TABLE IF NOT EXISTS myexemple(i INT) ON COMMIT DROP;
> INSERT INTO public.myexemple VALUES(1);
> INSERT INTO public.myexemple VALUES(2);
> INSERT INTO public.myexemple VALUES(3);
> INSERT INTO myexemple VALUES(7);
> SELECT 'public', * FROM public.myexemple
> UNION ALL SELECT 'noschema', * FROM myexemple
> UNION ALL SELECT 'pg_temp', * FROM pg_temp.myexemple;
> /* Fin Transaction 1 */
> =>
> public;1
> public;2
> public;3
> noschema;7
> pg_temp;7
>
> Here, all is fine It's the expected behavior.
>
> But If I create the the TEMP TABLE in a function, it's (sometimes) the
> public table which is used and not the temporary table.
>
> CREATE OR REPLACE FUNCTION test_search_path_v1(tmp boolean)
> RETURNS TABLE(ori text, i int)
> LANGUAGE plpgsql
> AS $_$
> DECLARE
> txt text;
> BEGIN
> CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT
> DROP;
>
> IF $1 THEN
> CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
> ELSE
> CREATE TABLE IF NOT EXISTS my_table(i INT);
> END IF;
>
> SELECT setting FROM pg_settings WHERE name = 'search_path' INTO txt;
> RAISE INFO 'search_path = %', txt;
>
> INSERT INTO my_table VALUES((random() * 100)::INT);
>
> FOR txt IN SELECT schemaname FROM pg_tables WHERE tablename = 'my_table'
> LOOP
> RAISE INFO '==> %', txt;
> END LOOP;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname ='public' AND
> tablename = 'my_table') THEN
> RAISE INFO 'public.my_table exists';
> INSERT INTO return_table SELECT 'public', t.i FROM public.my_table t;
> END IF;
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname LIKE 'pg_temp%' AND
> tablename = 'my_table') THEN
> RAISE INFO 'pg_temp.my_table exists';
> INSERT INTO return_table SELECT 'pg_temp', t.i FROM pg_temp.my_table
> t;
> END IF;
>
> INSERT INTO return_table SELECT '', t.i FROM my_table t;
> RETURN QUERY SELECT t.ori, t.i FROM return_table t;
> END;
> $_$;
>
> SHOW search_path -- => "$user", public;
> DROP TABLE IF EXISTS my_table;
> -- Executing each row on differents transactions but in the same session
> /*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true); --
> => OK takes table from pg_temp (no existing table in public)
> /*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); --
> => OK takes table from public
> /*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true); --
> => OK takes table from pg_temp (and the existing from public)
> /*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); --
> => OK takes table from public
> /*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true); --
> => NOK => it takes public and not pg_temp
>
>
I cannot reproduce any fails in this test.
> In fact, if I call 0 or 1 time "test_search_path_v1(false)" all the call
> with "true" will be ok.
> But AFTER the second call with false, all subsequent call with true will
> failed
>
What is error message?
Regards
Pavel
> => using public instead of pg_temp for the INSERT INTO my_table
> VALUES((random() * 100)::INT)
>
> If I do the test with changing session before exh call, the problem
> doesn't appear
> -- Executing each row on differents session
> /*Session A */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (no existing table in public)
> /*Session B */ SELECT * FROM test_search_path_v1(false); -- => OK takes
> table from public
> /*Session C */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (and the existing from public)
> /*Session D */ SELECT * FROM test_search_path_v1(false); -- => OK takes
> table from public
> /*Session E */ SELECT * FROM test_search_path_v1(true); -- => OK takes
> table from pg_temp (and the existing from public)
>
>
> It's possible to bypass te problem with enforce the use of pg_temp like in
> this second version.
>
> CREATE OR REPLACE FUNCTION test_search_path_v2(tmp boolean) RETURNS
> table(ori text, i int)
> LANGUAGE plpgsql
> AS $_$
> DECLARE
> txt text;
> BEGIN
> CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT
> DROP;
>
> IF $1 THEN
> PERFORM set_config('search_path', 'pg_temp, "$user", public', true);
> -- is_local = true
> CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
> ELSE
> PERFORM
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Dominique: On Tue, 11 Jan 2022 at 11:57, Dominique Devienne wrote: > On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte > wrote: >> Not going to enter into the lock situation but... > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you > suggest) requires just the same. It certainly does, yours is a extreme use case but should have a way to make it work. From what I've understood it is just a problem of hitting a limit, but it should work even if the solution is just "put some more memory, increase that parameter". Re: dropping a DB, I do not think you are going to hit this limit there, as iy is a fundamentally different operation, someone more experienced may page in, but I think it is a matter of checking nobody uses it, updating a few rows in global catalogs and deleting all the files. I've been out of DB programming for some years, but I remember it was very fast. But know you say it, it may need tracking, but I think depending object is only needed when dropping the role. >> On Tue, 11 Jan 2022 at 10:24, Dominique Devienne wrote: >> > I need for unit testing purposes to be able to support multiple (2+N >> > schemas) "instances". ... >> I'm not sure if you are going to hit other limitations, but I've >> normally done tests with the "template database" approach ( using >> create database template=, dropping the DB at the end ). It is fast, >> it is simple, it is easy. Have you tried that? > No, I haven't. I did see that feature, in the doc, and wondered about it for > Production, but not for testing. I've never used it for production ( I can see its usefulness for the hosting people which have a lot of customers or sites which the same database, but has never been my case ). For testing, at least for me, create with template was just like a very fast sql script for creation, or a very fast restore. They were normally databases with little data ( typically only the control tables populated 9. >> seems much easier/faster than building and dropping all this >> schemas/roles,specially for testing. > Good to here. But when you write "I've done tests", do you mean manual tests? > Or automated unit-tests that create DBs (from a template) on-the-fly and DROP > them? > Concurrently from different CI agents? Well, they were not too "unity", creating the DB for a real unit ( of behaviour ) test was too expensive, but I think you have that solved. They were a batch of tests run either manually via single script ( pick a name, copy the database, run the tests in it, report, drop the database ( unless a flag was given, for foresincs ) ) or the same script from the CI server. > The reason I didn't consider DB templates for unit-testing, is that the > schemas are changing often. > And creating the schemas is all automated in code already. And you use this to create the template, via CI if you want. > Plus ROLEs are CLUSTER-wide, so the DB template does nothing to help with > SCHEMA-associated roles. --DD You may avoid the need to create and drop roles. If the test dbs are isolated, you should not need to drop the roles when they are "live" in the testing setup. You just need to script role creation ( globally ), plus template creation after it, then create with template and drop for tests, and when template needs changing you either script the update directly ( no need to drop roles ) or drop it, drop the roles ( which now have nothing depending on them ) and recreate from start. More knowledge of exact procedures is needed, but as it seems you have everything scripted you may be able to test that, and I remember copying databases by create template was very fast. Of course we did not have much roles or schemas, but it seemed a natural way to go for testing in what you described, and also having different databases for each test runs can give you better test isolation, specially in your case of concurrent testing. Just seemed the proper tool for the job, and I thought you may not be familiar with it. Francisco Olarte.
Folding of case of identifiers
Hello all According to https://www.postgresql.org/docs/current/sql-syntax-lexical.html, "Key words and unquoted identifiers are case insensitive." And "SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($)." So far so good. Non-latin letters are included, which I take to also include the danish letters æøå/ÆØÅ. However, name-folding is odd for these letters. Of these three create tables, the two first succeed, the last one does not (G and g is equivalent, Æ and æ is not). create table æblegrød (a int, køn text); create table ÆblegrØd (a int, køn text); create table ÆbleGrØd (a int, køn text); Can anyone explain the logic that rules this. Regards Niels Jespersen
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Dominique Devienne: I wish for DB-specific ROLEs BTW... Same here. That would be so useful.
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte wrote: > Dominique: > Not going to enter into the lock situation but... > OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you suggest) requires just the same. > On Tue, 11 Jan 2022 at 10:24, Dominique Devienne > wrote: > ... > > I need for unit testing purposes to be able to support multiple (2+N > schemas) "instances". > > Each instance (of 2+N schemas) is owned by a separate ROLE, created for > that express purpose. > > I designed / coded it to be able to have several "instances" per DB, > that come and go for unit testing purpose, > > and they will come and go concurrently (when CI kicks in, on several > platforms/configurations in parallel). > > And I thought DROP OWNED BY was going to be convenient (fewer > client-server round-trips, perfectly models the *intent*). > > But obviously given the limitations I'm discovering, that's not the case. > > > > In production, there will typically be a single "instance" per DB. > > > > So, should I redesign for each instance to be in its own DB? And instead > of just creating schemas on the fly when running tests, creating DBs on the > fly? > > That means I'd could then DROP the whole DB (I wish for DB-specific > ROLEs BTW...). Does that buy me anything? Does that help with locks-per-tx > at all? > > I'm happy to do that, if necessary. But is using a dedicated DB per 2+N > schemas "instance" the right approach? > > I'm not sure if you are going to hit other limitations, but I've > normally done tests with the "template database" approach ( using > create database template=, dropping the DB at the end ). It is fast, > it is simple, it is easy. Have you tried that? No, I haven't. I did see that feature, in the doc, and wondered about it for Production, but not for testing. > seems much easier/faster than building and dropping all this > schemas/roles,specially for testing. > Good to here. But when you write "I've done tests", do you mean manual tests? Or automated unit-tests that create DBs (from a template) on-the-fly and DROP them? Concurrently from different CI agents? The reason I didn't consider DB templates for unit-testing, is that the schemas are changing often. And creating the schemas is all automated in code already. Plus ROLEs are CLUSTER-wide, so the DB template does nothing to help with SCHEMA-associated roles. --DD
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
Dominique: Not going to enter into the lock situation but... On Tue, 11 Jan 2022 at 10:24, Dominique Devienne wrote: ... > I need for unit testing purposes to be able to support multiple (2+N schemas) > "instances". > Each instance (of 2+N schemas) is owned by a separate ROLE, created for that > express purpose. > I designed / coded it to be able to have several "instances" per DB, that > come and go for unit testing purpose, > and they will come and go concurrently (when CI kicks in, on several > platforms/configurations in parallel). > And I thought DROP OWNED BY was going to be convenient (fewer client-server > round-trips, perfectly models the *intent*). > But obviously given the limitations I'm discovering, that's not the case. > > In production, there will typically be a single "instance" per DB. > > So, should I redesign for each instance to be in its own DB? And instead of > just creating schemas on the fly when running tests, creating DBs on the fly? > That means I'd could then DROP the whole DB (I wish for DB-specific ROLEs > BTW...). Does that buy me anything? Does that help with locks-per-tx at all? > I'm happy to do that, if necessary. But is using a dedicated DB per 2+N > schemas "instance" the right approach? I'm not sure if you are going to hit other limitations, but I've normally done tests with the "template database" approach ( using create database template=, dropping the DB at the end ). It is fast, it is simple, it is easy. Have you tried that? seems much easier/faster than building and dropping all this schemas/roles,specially for testing. Francisco Olarte.
plpgsql function problem whith creating temp table - not correctly using search_path ?
Hi,
I would like to submit a problem (bug ?) that I encountered while handling
temporary tables in plpgsql functions.
First, if I create a TABLE and a TEMP TABLE with the same name, and I request
without specified the schema, the temporary table is used.
-- SHOW search_path; -- => "$user", public
-- Exectute next commands in the same transaction
/* Début Transaction 1 */
DROP TABLE IF EXISTS public.myexemple;
DROP TABLE IF EXISTS pg_temp.myexemple;
CREATE TABLE IF NOT EXISTS myexemple(i INT);
CREATE TEMP TABLE IF NOT EXISTS myexemple(i INT) ON COMMIT DROP;
INSERT INTO public.myexemple VALUES(1);
INSERT INTO public.myexemple VALUES(2);
INSERT INTO public.myexemple VALUES(3);
INSERT INTO myexemple VALUES(7);
SELECT 'public', * FROM public.myexemple
UNION ALL SELECT 'noschema', * FROM myexemple
UNION ALL SELECT 'pg_temp', * FROM pg_temp.myexemple;
/* Fin Transaction 1 */
=>
public;1
public;2
public;3
noschema;7
pg_temp;7
Here, all is fine It's the expected behavior.
But If I create the the TEMP TABLE in a function, it's (sometimes) the public
table which is used and not the temporary table.
CREATE OR REPLACE FUNCTION test_search_path_v1(tmp boolean)
RETURNS TABLE(ori text, i int)
LANGUAGE plpgsql
AS $_$
DECLARE
txt text;
BEGIN
CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT DROP;
IF $1 THEN
CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
ELSE
CREATE TABLE IF NOT EXISTS my_table(i INT);
END IF;
SELECT setting FROM pg_settings WHERE name = 'search_path' INTO txt;
RAISE INFO 'search_path = %', txt;
INSERT INTO my_table VALUES((random() * 100)::INT);
FOR txt IN SELECT schemaname FROM pg_tables WHERE tablename = 'my_table' LOOP
RAISE INFO '==> %', txt;
END LOOP;
IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname ='public' AND tablename =
'my_table') THEN
RAISE INFO 'public.my_table exists';
INSERT INTO return_table SELECT 'public', t.i FROM public.my_table t;
END IF;
IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname LIKE 'pg_temp%' AND
tablename = 'my_table') THEN
RAISE INFO 'pg_temp.my_table exists';
INSERT INTO return_table SELECT 'pg_temp', t.i FROM pg_temp.my_table t;
END IF;
INSERT INTO return_table SELECT '', t.i FROM my_table t;
RETURN QUERY SELECT t.ori, t.i FROM return_table t;
END;
$_$;
SHOW search_path -- => "$user", public;
DROP TABLE IF EXISTS my_table;
-- Executing each row on differents transactions but in the same session
/*Session A - Transaction 1*/ SELECT * FROM test_search_path_v1(true); -- =>
OK takes table from pg_temp (no existing table in public)
/*Session A - Transaction 2*/ SELECT * FROM test_search_path_v1(false); -- =>
OK takes table from public
/*Session A - Transaction 3*/ SELECT * FROM test_search_path_v1(true); -- =>
OK takes table from pg_temp (and the existing from public)
/*Session A - Transaction 4*/ SELECT * FROM test_search_path_v1(false); -- =>
OK takes table from public
/*Session A - Transaction 5*/ SELECT * FROM test_search_path_v1(true); -- =>
NOK => it takes public and not pg_temp
In fact, if I call 0 or 1 time "test_search_path_v1(false)" all the call with
"true" will be ok.
But AFTER the second call with false, all subsequent call with true will failed
=> using public instead of pg_temp for the INSERT INTO my_table
VALUES((random() * 100)::INT)
If I do the test with changing session before exh call, the problem doesn't
appear
-- Executing each row on differents session
/*Session A */ SELECT * FROM test_search_path_v1(true); -- => OK takes table
from pg_temp (no existing table in public)
/*Session B */ SELECT * FROM test_search_path_v1(false); -- => OK takes table
from public
/*Session C */ SELECT * FROM test_search_path_v1(true); -- => OK takes table
from pg_temp (and the existing from public)
/*Session D */ SELECT * FROM test_search_path_v1(false); -- => OK takes table
from public
/*Session E */ SELECT * FROM test_search_path_v1(true); -- => OK takes table
from pg_temp (and the existing from public)
It's possible to bypass te problem with enforce the use of pg_temp like in this
second version.
CREATE OR REPLACE FUNCTION test_search_path_v2(tmp boolean) RETURNS table(ori
text, i int)
LANGUAGE plpgsql
AS $_$
DECLARE
txt text;
BEGIN
CREATE TEMP TABLE IF NOT EXISTS return_table(ori TEXT, i INT) ON COMMIT DROP;
IF $1 THEN
PERFORM set_config('search_path', 'pg_temp, "$user", public', true); --
is_local = true
CREATE TEMP TABLE IF NOT EXISTS my_table(i INT) ON COMMIT DROP;
ELSE
PERFORM set_config('search_path', '"$user", public', true); -- is_local =
true
CREATE TABLE IF NOT EXISTS my_table(i INT);
END IF;
SELECT setting FROM pg_settings WHERE name = 'search_path' INTO txt;
RAISE INFO 'search_path = %', txt;
INSERT INTO my_table VALUES((random() * 100)::INT);
FOR txt IN SELECT schemaname FROM pg_tables WHERE tablename =
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
On Mon, Jan 10, 2022 at 11:13 PM Adrian Klaver wrote: > On 1/10/22 13:58, Dominique Devienne wrote: > > > Given that Tom mentions max_locks_per_transaction can be safely > increased, > > and given the stats I mentioned in this thread, what would a > > "reasonable" max_locks_per_transaction > > be in my case? By reasonable, I mean "as large as possible w/o being > too large"... > > > > Obviously 64*100 is not quite large enough to be safe in this case. > I'd appreciate some advise. TIA, --DD > > I think at this point it might be a good idea to explore what the case is? > I go into use cases in my previous message, send a minute ago. In your OP the error occurred here: > DROP OWNED BY "Epos-PNS (a73e1fbdcc5b4460ba55703a0d6b6ad7)" CASCADE > The CASCADE allows to command to recurse to objects that may not be > owned by the specified role. Is that what you want? > Sure. Although that's irrelevant. There are no such other objects. Each 2+N schemas "instance" is self-contained, and all ROLEs associated to those SCHEMAs only have privileges associated to those schemas. Actual LOGIN USERs are granted access to a subset of ROLEs in "instances" based on individual permissions. So when I nuke an "instance", I DROP all its SCHEMAs, and all their associated ROLEs. And DROP'ing those "instance"-specific ROLEs implicitly severs the GRANTs made on LOGIN USERs, who remain. > What is the purpose of the process, clearing out given schema, dropping > objects only owned by a given role, or something else? > "dropping objects only owned by a given role" indeed. I.e. exactly what DROP OWNED BY is designed to do, no? It just so happens that it seems impossible to do that, because that involves too many locks :(. With 1400 relations for each of the N schemas, and actual in-prod instances at client sites that require hundreds such schemas (on the current system not based on PostgreSQL), I'm guessing I can't assign 1400 * 1000 = over a million locks on the cluster, can I? So now the question I asked in that other message, is whether I should use a dedicated DB per "instance" instead? --DD
Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory
On Mon, Jan 10, 2022 at 10:40 PM Alvaro Herrera wrote: > On 2022-Jan-10, Dominique Devienne wrote: > > > Btw, is there a catalog accurately count a schémas relations from > the catalog? > > pg_class > ddevienne=> select relnamespace::regnamespace::text, count(*) from pg_class where relnamespace::regnamespace::text like '"PNS:%"' group by relnamespace; relnamespace | count +--- "PNS:ec44cdde757b572a8f367277e67f1e3f" | 1308 ... ddevienne=> select relnamespace::regnamespace::text, relkind, count(*) from pg_class where relnamespace::regnamespace::text like '"PNS:%"' group by relnamespace, relkind; relnamespace | relkind | count +-+--- "PNS:ec44cdde757b572a8f367277e67f1e3f" | S | 229 "PNS:ec44cdde757b572a8f367277e67f1e3f" | i | 828 "PNS:ec44cdde757b572a8f367277e67f1e3f" | r | 244 "PNS:ec44cdde757b572a8f367277e67f1e3f" | v | 7 ... So from what I was told in this thread, 1308 locks need to be taken, just for that one schema. And my "system" can have N of those (and there can be N systems in a DB). (the other two "fixed" schemas have way fewer relations, 32 for one). > > Of course I can do that. But it doesn’t feel right. > > Then you should increase max_locks_per_transaction to a value that better > suits you. > But the point is that there's no limit on how many schema a given ROLE can own. So you can't pick a value that will always work.So that makes DROP OWNED BY pretty much useless as too unreliable in my case. Unless I'm missing something? That's definitely something that's not mentioned in the doc of DROP OWNED BY. Deserve a mention IMHO. I need for unit testing purposes to be able to support multiple (2+N schemas) "instances". Each instance (of 2+N schemas) is owned by a separate ROLE, created for that express purpose. I designed / coded it to be able to have several "instances" per DB, that come and go for unit testing purpose, and they will come and go concurrently (when CI kicks in, on several platforms/configurations in parallel). And I thought DROP OWNED BY was going to be convenient (fewer client-server round-trips, perfectly models the *intent*). But obviously given the limitations I'm discovering, that's not the case. In production, there will typically be a single "instance" per DB. So, should I redesign for each instance to be in its own DB? And instead of just creating schemas on the fly when running tests, creating DBs on the fly? That means I'd could then DROP the whole DB (I wish for DB-specific ROLEs BTW...). Does that buy me anything? Does that help with locks-per-tx at all? I'm happy to do that, if necessary. But is using a dedicated DB per 2+N schemas "instance" the right approach? What kind of other limitations I'm not aware of, and the doc glosses over (or that I missed/haven't read yet), that I'd discover then? This is for this kind of insights that I turn to experts on MLs. Thanks, --DD
