On Tuesday, January 11, 2022, Dominique Devienne <ddevienne@gmail.com> 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
