Subject: Re: Problem with schemas, possibly oids?
To: pgsql-admin@lists.postgresql.org
References: <3cabefc3-0713-7f5d-27c5-c0dab55cd8c6@Bios-ICT.hr>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <edfcd4d6-2db5-1ee2-f995-f6ac927b3100@gmail.com>
Date: Thu, 8 Aug 2019 07:14:06 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <3cabefc3-0713-7f5d-27c5-c0dab55cd8c6@Bios-ICT.hr>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 8/8/19 4:04 AM, Arni Kromić wrote:
> Hello.
> We have some weird errors preventing us from using a postgres database. 
> Software version is PostgreSQL 11.4. on 64-bit Debian Stretch (9.9), 
> installed from the upstream apt repo.
>
> We're using the clone_schema function from a postgres mailing list 
> (converted to a procedure now) to create new schemas from a template, and 
> so far we have created about 170 schemas that way. Now we have encountered 
> a problem which prevents us from creating new schemas:
>
> CALL clone_schema('klijent_tpl', 'test-2019')
> Although this has worked perfectly until now, the call results in this error:
>> ERROR: relation "test-2019.bc_mob_pr_pnd" does not exist CONTEXT: SQL 
>> statement "COMMENT ON TRIGGER brisi_stavku ON "test-2019".bc_mob_pr_pnd 
>> IS 'Briši stavke ponude prije brisanja zaglavlja ponude'" PL/pgSQL 
>> function clone_schema(text,text,boolean) line 444 at EXECUTE SQL state: 42P01
> Just to note that "test-2019" schema doesn't exist, ant the template 
> schema "klijent_tpl" contains NO relation named bc_mob_pr_pnd nor the 
> trigger mentioned in the error message. I see that as a clue it references 
> the wrong  schema, obviously one of the existing schemas which DO contain 
> a table named bc_mob_pr_pnd and a trigger.
>
> Moreover, trying to clone some of the existing schemas also fails:
> call clone_schema ('HR16101766338-2018', 'test')

Maybe there's a bug in clone_schema or it's wants some assumptions that you 
aren't fulfilling.

What if you manually execute the contents ofclone_schema to generate DDL and 
then run that DDL?


-- 
Angular momentum makes the world go 'round.