Schemas in Firebird - SYSTEM schema, migration from old versions and plugin objects

108 views
Skip to first unread message

Adriano dos Santos Fernandes

unread,
May 31, 2024, 10:02:16 PM5/31/24
to firebir...@googlegroups.com
Hi!

There will be SYSTEM schema where system objects will live.

My idea is to not block this schema for user and treat system objects
like now, i.e., they cannot be changed but new objects will be allowed
in system schema.

Migration from old versions to separate schemas (including system schema
plus single user schema) could be complicated, so I'm inclined to make
gbak restore any objects from non-schema backup into SYSTEM schema.

There is also PLG$ objects, like security and profiler. Same thing here,
they will be created in SYSTEM schema.

Please note that if there were no backward compatibility involved, my
opinions on this probably would be different. But I think this is enough
compromise for an initial version, and tools to "move" from SYSTEM
schema could be introduced later.


Adriano

Mark Rotteveel

unread,
Jun 1, 2024, 2:47:56 AM6/1/24
to firebir...@googlegroups.com
On 01/06/2024 04:02, Adriano dos Santos Fernandes wrote:
> My idea is to not block this schema for user and treat system objects
> like now, i.e., they cannot be changed but new objects will be allowed
> in system schema.

I'm not sure I like that. Currently we have no schemas, so when moving
to schemas, we should close off unnecessary stuff I think. Allowing
creation of indexes and triggers on system tables (and as a result,
placing them in the SYSTEM schema), sounds OKish to me.

However, I don't think that should mean users should have free reign to
just create objects in SYSTEM. That sounds like a recipe for disaster,
and/or SYSTEM becoming the de-facto default schema.

> Migration from old versions to separate schemas (including system schema
> plus single user schema) could be complicated, so I'm inclined to make
> gbak restore any objects from non-schema backup into SYSTEM schema.

What would be the problem with migrating things into a default user
schema unless they are a system table, or a user-created object on a
system table (e.g. a trigger or index)?

> There is also PLG$ objects, like security and profiler. Same thing here,
> they will be created in SYSTEM schema.

Have you considered a separate schema for plugins?

> Please note that if there were no backward compatibility involved, my
> opinions on this probably would be different. But I think this is enough
> compromise for an initial version, and tools to "move" from SYSTEM
> schema could be introduced later.

Specifically what backwards compatibility issues would necessitate
having gbak create objects in SYSTEM?

Mark
--
Mark Rotteveel

Adriano dos Santos Fernandes

unread,
Jun 1, 2024, 5:41:12 PM6/1/24
to firebir...@googlegroups.com
On 01/06/2024 03:47, 'Mark Rotteveel' via firebird-devel wrote:
> That sounds like a recipe for disaster,

Only if you consider current Firebird as a disaster.


> and/or SYSTEM becoming the de-facto default schema.
>

For existing databases, I'm afraid yes, but not for new ones.


>> Migration from old versions to separate schemas (including system schema
>> plus single user schema) could be complicated, so I'm inclined to make
>> gbak restore any objects from non-schema backup into SYSTEM schema.
>
> What would be the problem with migrating things into a default user
> schema unless they are a system table, or a user-created object on a
> system table (e.g. a trigger or index)?
>

It would need to re-encode BLR when an user object references a system one.

That is complicated alone, but it will make downgrades impossible.


>> There is also PLG$ objects, like security and profiler. Same thing here,
>> they will be created in SYSTEM schema.
>
> Have you considered a separate schema for plugins?
>

It's the same and even worse. You know that team decided my intentions
to improve system GDML queries where not necessary and that GPRE is good
enough.

Team also decided that GPRE does not need to be improved for schemas.

So let's live with the decisions consequences.

So inside Firebird code, GPRE-generated modules should be bound to the
SYSTEM schema.


Adriano

Dimitry Sibiryakov

unread,
Jun 1, 2024, 5:44:31 PM6/1/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 01.06.2024 23:41:
> It's the same and even worse. You know that team decided my intentions
> to improve system GDML queries where not necessary and that GPRE is good
> enough.
>
> Team also decided that GPRE does not need to be improved for schemas.
>
> So let's live with the decisions consequences.
>
> So inside Firebird code, GPRE-generated modules should be bound to the
> SYSTEM schema.

Not SYSTEM, but DEFAULT or PUBLIC, please.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Jun 1, 2024, 5:49:25 PM6/1/24
to firebir...@googlegroups.com
SYSTEM.


Adriano

Dimitry Sibiryakov

unread,
Jun 1, 2024, 5:50:59 PM6/1/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 01.06.2024 23:49:
>>> So inside Firebird code, GPRE-generated modules should be bound to the
>>> SYSTEM schema.
>>   Not SYSTEM, but DEFAULT or PUBLIC, please.
>>
> SYSTEM.

Is there a technical reason for that?

--
WBR, SD.

Mark Rotteveel

unread,
Jun 2, 2024, 5:13:04 AM6/2/24
to firebir...@googlegroups.com
On 01/06/2024 23:41, Adriano dos Santos Fernandes wrote:
> On 01/06/2024 03:47, 'Mark Rotteveel' via firebird-devel wrote:
>> That sounds like a recipe for disaster,
>
> Only if you consider current Firebird as a disaster.

I mean that this behaviour will result in upgraded databases using
SYSTEM as a normal schema, instead of reserving it for system objects.
And because normal objects will be in there, people will continue to add
new objects in there, and likely will do the same with _new_ database,
because their other databases do the same.

That is a situation I consider a disaster. Because then you might just
as well not have a SYSTEM schema, or schemas at all.

>> and/or SYSTEM becoming the de-facto default schema.
>
> For existing databases, I'm afraid yes, but not for new ones.
>
>>> Migration from old versions to separate schemas (including system schema
>>> plus single user schema) could be complicated, so I'm inclined to make
>>> gbak restore any objects from non-schema backup into SYSTEM schema.
>>
>> What would be the problem with migrating things into a default user
>> schema unless they are a system table, or a user-created object on a
>> system table (e.g. a trigger or index)?
>>
>
> It would need to re-encode BLR when an user object references a system one.
>
> That is complicated alone, but it will make downgrades impossible.

To be honest, I think that is a necessary pain to avoid creating a
confusing mess of the SYSTEM schema.

On the other hand, I recognize that is easy for me to say from the
sidelines.

Mark
--
Mark Rotteveel

Денис Симонов

unread,
Jun 3, 2024, 10:11:12 AM6/3/24
to firebird-devel
Why not make the default schema PUBLIC, like Postgresql does?

Let all system tables be located in the SYSTEM schema. There are many ways to solve backward compatibility problems, for example making all current RDB$ tables views on the SYSTEM table, or aliases of objects from the SYSTEM schema.

воскресенье, 2 июня 2024 г. в 12:13:04 UTC+3, Mark Rotteveel:

Денис Симонов

unread,
Jun 3, 2024, 10:32:17 AM6/3/24
to firebird-devel
Technically, the USE SCHEMA <some> statement will automatically disable the ability to read metadata in any legacy application, so to solve backward compatibility problems we need to come up with something better than just placing SYSTEM objects in the default schema. 
For example, you can make sure that system objects are visible in any of the newly created schemas. What other opinions are there on this matter?

понедельник, 3 июня 2024 г. в 17:11:12 UTC+3, Денис Симонов:

Dimitry Sibiryakov

unread,
Jun 3, 2024, 10:46:35 AM6/3/24
to firebir...@googlegroups.com
Денис Симонов wrote 03.06.2024 16:32:
> Technically, the USE SCHEMA <some> statement will automatically disable the
> ability to read metadata in any legacy application, so to solve backward
> compatibility problems we need to come up with something better than just
> placing SYSTEM objects in the default schema.
> For example, you can make sure that system objects are visible in any of the
> newly created schemas. What other opinions are there on this matter?

In previous discussion we agreed that there must be support for PATH for
unqualified identifier.
Search for existing objects can follow this rule:

1) SYSTEM schema
2) Defined schema PATH (or USE SCHEMA as you suggest)
3) PUBLIC schema

Creation of new objects with unqualified identifier can use different rules
that may be one of these:

1) Current schema
2) Default user schema
3) DEFAULT schema

Thus no creation of user objects in SYSTEM schema allowed while system
objects are freely available to any old application (including GDML ones).

New plugins are strongly recommended to create their objects in own schema.
Old plugins may have their objects created in user schema and adapting to the
new rules is their problem, not ours.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Jun 3, 2024, 4:52:03 PM6/3/24
to firebir...@googlegroups.com
On 03/06/2024 11:11, Денис Симонов wrote:
> Why not make the default schema PUBLIC, like Postgresql does?
>

Different default schema for new objects can be done, specially in new
databases.

But this is not topic of this thread. This is about migration of backups
done in previous versions.


> Let all system tables be located in the SYSTEM schema. There are many
> ways to solve backward compatibility problems, for example making all
> current RDB$ tables views on the SYSTEM table, or aliases of objects
> from the SYSTEM schema.
>

First, we're not going to create aliases (synonyms).

Second, half made solutions will not make good things.

It's not about system tables that can be transformed to views only.

There is domains, system domains that can be used when user's views
references system views, system packages. etc.


Adriano

Adriano dos Santos Fernandes

unread,
Jun 3, 2024, 7:33:15 PM6/3/24
to firebir...@googlegroups.com
On 03/06/2024 11:46, 'Dimitry Sibiryakov' via firebird-devel wrote:
> Денис Симонов wrote 03.06.2024 16:32:
>> Technically, the USE SCHEMA <some> statement will automatically
>> disable the ability to read metadata in any legacy application, so to
>> solve backward compatibility problems we need to come up with
>> something better than just placing SYSTEM objects in the default schema.
>> For example, you can make sure that system objects are visible in any
>> of the newly created schemas. What other opinions are there on this
>> matter?
>
>   In previous discussion we agreed that there must be support for PATH
> for unqualified identifier.

Paths should be used in prepare (and hence BLR generation) time.

Paths does not solve problem of BLR not referencing schemas.

Current BLR verbs that uses object names should be used only in the case
of an object referencing another object in the same schema. References
to others schemas should use new verbs with explicit schema name.

That's why separation of system/user schema requires BLR re-encoding.

Again, I'm not against it. But initial, very incomplete, full of bugs
patch of schemas is already 27K lines, touching many files. Add to that
that we have concurrent branch (shared metadata) also touching many and
same files, we have merge disaster for integration of this.

So any non-fundamental thing should IMO be deferred to after initial
merge. That could mean even before the release, but maybe not.


Adriano

Mark Rotteveel

unread,
Jun 4, 2024, 3:11:24 AM6/4/24
to firebir...@googlegroups.com
On 03/06/2024 16:11, Денис Симонов wrote:
> Why not make the default schema PUBLIC, like Postgresql does?
>
> Let all system tables be located in the SYSTEM schema. There are many
> ways to solve backward compatibility problems, for example making all
> current RDB$ tables views on the SYSTEM table, or aliases of objects
> from the SYSTEM schema.

In PostgreSQL, the system tables do not reside in public, but in pg_catalog.

Mark
--
Mark Rotteveel

Денис Симонов

unread,
Jun 4, 2024, 5:12:44 AM6/4/24
to firebird-devel
I'm not saying that system objects should be located in PUBLIC. On the contrary, they should be in SYSTEM. But it is better to make the default scheme PUBLIC. And yes, system objects must be visible in any schema as PATH/SYSNONIM, otherwise, when switching the schema via the USE SCHEMA statement, any Legacy application will immediately stop working, since the drivers will not be able to read the metadata.

But if the current implementation is only preliminary in order to generally debug the database schemas, then I see nothing wrong with what Adriano proposed.

вторник, 4 июня 2024 г. в 10:11:24 UTC+3, Mark Rotteveel:

Dimitry Sibiryakov

unread,
Jun 4, 2024, 5:24:21 AM6/4/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 03.06.2024 23:10:
> Again, I'm not against it. But initial, very incomplete, full of bugs
> patch of schemas is already 27K lines, touching many files. Add to that
> that we have concurrent branch (shared metadata) also touching many and
> same files, we have merge disaster for integration of this.

That's why big things must be done by little logically complete steps that
can be implemented, reviewed and tested independently.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Sep 19, 2024, 7:53:11 AM9/19/24
to firebir...@googlegroups.com
On 31/05/2024 23:02, Adriano dos Santos Fernandes wrote:
> There will be SYSTEM schema where system objects will live.
>
> My idea is to not block this schema for user and treat system objects
> like now, i.e., they cannot be changed but new objects will be allowed
> in system schema.
>
> Migration from old versions to separate schemas (including system schema
> plus single user schema) could be complicated, so I'm inclined to make
> gbak restore any objects from non-schema backup into SYSTEM schema.

Going back to this topic, it's not about only restore user's objects
from old ODS into new non-SYSTEM schema.

It's about new databases and restore to old ODS too.

Any BLR-based code present in non-SYSTEM schema referencing SYSTEM
objects will have new BLR incompatible with older Firebird versions.

So, are we prepared to invalidate restore to older ODS (at least in this
case of user code referencing SYSTEM objects)?


Adriano

Dimitry Sibiryakov

unread,
Sep 19, 2024, 8:00:06 AM9/19/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 19.09.2024 13:53:
> Any BLR-based code present in non-SYSTEM schema referencing SYSTEM
> objects will have new BLR incompatible with older Firebird versions.

Just don't do it.

Why "referencing SYSTEM objects will have new BLR"? Existing BLR is enough
for that: tables are referenced either by id (good for system tables) or by name
(good for user tables). In the latter case name is resolved in runtime by
ordinary search path.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Sep 19, 2024, 8:04:41 AM9/19/24
to firebir...@googlegroups.com
Referenced objects should be unambiguous.

Cross schema references should use new BLR. Stored objects do not use
IDs as they are not stable.

We may think if system objects can be considered to have stable IDs.


Adriano

Dimitry Sibiryakov

unread,
Sep 19, 2024, 8:10:02 AM9/19/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 19.09.2024 14:04:
> Referenced objects should be unambiguous.

No, they don't. It is actually the purpose of schema and object name
resolution path to provide ability for the same PSQL code work differently for
different users.
If you have "select f from t" inside of SP, this SP is supposed to refer
<current schema>.T where current schema is variable, no?..

> We may think if system objects can be considered to have stable IDs

Currently they are considered to be stable and are referred by ID in
generated BLR.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Sep 19, 2024, 9:13:04 PM9/19/24
to firebir...@googlegroups.com
On 19/09/2024 09:09, 'Dimitry Sibiryakov' via firebird-devel wrote:
> Adriano dos Santos Fernandes wrote 19.09.2024 14:04:
>> Referenced objects should be unambiguous.
>
>   No, they don't. It is actually the purpose of schema and object name
> resolution path to provide ability for the same PSQL code work
> differently for different users.

For the last time I will explain again:

Unqualified objects should be qualified at compile time and
unambiguously be referenced at BLR. New objects appearing in a schema
should not change execution of another already created object.

>> We may think if system objects can be considered to have stable IDs
>
>   Currently they are considered to be stable and are referred by ID in
> generated BLR.
>

Not fully true. The only time they are referenced by ids are in DSQL
(non-stored) statements and GPRE-compiled ones for server only.

Even GPRE-compiled for client (gbak, isql, etc) references objects by names.


Adriano

Adriano dos Santos Fernandes

unread,
Sep 19, 2024, 9:27:16 PM9/19/24
to firebir...@googlegroups.com
On 19/09/2024 22:12, Adriano dos Santos Fernandes wrote:
>
>>> We may think if system objects can be considered to have stable IDs
>>
>>   Currently they are considered to be stable and are referred by ID in
>> generated BLR.
>>
>
> Not fully true. The only time they are referenced by ids are in DSQL
> (non-stored) statements and GPRE-compiled ones for server only.
>
> Even GPRE-compiled for client (gbak, isql, etc) references objects by names.
>

There is not even id-based BLR verb for all objects. Generators can only
be referenced by name, which invalidates plan to reference system
objects by id and be able to downgrade databases with gbak when user
schema references system objects, unless new BLR verbs are created and
backported.


Adriano

Dimitry Sibiryakov

unread,
Sep 20, 2024, 4:58:32 AM9/20/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 20.09.2024 3:12:
> For the last time I will explain again:
>
> Unqualified objects should be qualified at compile time and
> unambiguously be referenced at BLR. New objects appearing in a schema
> should not change execution of another already created object.

Is it ANSI standard requirement?

--
WBR, SD.

Mark Rotteveel

unread,
Sep 20, 2024, 5:01:40 AM9/20/24
to firebir...@googlegroups.com
I pretty much expect so, but I'm not in the mood to dig. You don't want
a runtime schema path to influence the behaviour of compiled objects:
those references should be resolved at compile time, not runtime.

Mark
--
Mark Rotteveel

Denis Simonov

unread,
Sep 20, 2024, 5:11:31 AM9/20/24
to firebird-devel
> No, they don't. It is actually the purpose of schema and object name
> resolution path to provide ability for the same PSQL code work differently for
> different users.
> If you have "select f from t" inside of SP, this SP is supposed to refer
> <current schema>.T where current schema is variable, no?..

This is counterintuitive. Another schema may not have a table with the same name at all, or it may have a different set of fields. 
I would really not want my stored objects to change their behavior depending on the phase of the moon, etc.

пятница, 20 сентября 2024 г. в 12:01:40 UTC+3, Mark Rotteveel:

Dimitry Sibiryakov

unread,
Sep 20, 2024, 5:18:38 AM9/20/24
to firebir...@googlegroups.com
Denis Simonov wrote 20.09.2024 11:11:
> This is counterintuitive. Another schema may not have a table with the same name
> at all, or it may have a different set of fields.
> I would really not want my stored objects to change their behavior depending on
> the phase of the moon, etc

If you keep in database bookkeeping of several users in different schemas and
has a procedure to calculate balance or other report, how would you use it to
calculate balance of current user? By cloning of the procedure to scheme of
every user? Modifications of such cloned procedure would be a nightmare.

--
WBR, SD.

Vlad Khorsun

unread,
Sep 20, 2024, 6:09:21 AM9/20/24
to firebir...@googlegroups.com
20.09.2024 4:12, Adriano dos Santos Fernandes:

> Unqualified objects should be qualified at compile time and
> unambiguously be referenced at BLR. New objects appearing in a schema
> should not change execution of another already created object.

Compile or parse (prepare) ? I ask because engine always "compiles" BLR
of stored object when loading it from disk. Thus, I suppose, you meant prepare
time, correct ? If yes, why we need new BLR verb for object referenced by name
(fully qualified or not) ?

Regards,
Vlad

Dimitry Sibiryakov

unread,
Sep 20, 2024, 6:42:58 AM9/20/24
to firebir...@googlegroups.com
Vlad Khorsun wrote 20.09.2024 12:09:
> Thus, I suppose, you meant prepare
> time, correct ? If yes, why we need new BLR verb for object referenced by name
> (fully qualified or not) ?

I guess the new version of blr_relation must have an additional parameter for
schema name. I.e. something like this: `blr_relation3 schema_name, name,
alias_name, context-variable`.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Sep 20, 2024, 6:43:10 AM9/20/24
to firebir...@googlegroups.com
On 20/09/2024 07:09, Vlad Khorsun wrote:
> 20.09.2024 4:12, Adriano dos Santos Fernandes:
>
>> Unqualified objects should be qualified at compile time and
>> unambiguously be referenced at BLR. New objects appearing in a schema
>> should not change execution of another already created object.
>
>   Compile or parse (prepare) ?

Compile of text to BLR here.

BLR should be stored in a way when it's loaded references does not
switch from one object to another.

The current BLR verbs are now used to reference objects from the same
schema where the stored object is, thus the problem of user schema ->
SYSTEM schema -> old ODS.


Adriano

Mark Rotteveel

unread,
Sep 20, 2024, 7:00:28 AM9/20/24
to firebir...@googlegroups.com
I consider compilation the transformation from PSQL to BLR.

Mark
--
Mark Rotteveel

Vlad Khorsun

unread,
Sep 20, 2024, 7:48:03 AM9/20/24
to firebir...@googlegroups.com
20.09.2024 13:43, Adriano dos Santos Fernandes:
> On 20/09/2024 07:09, Vlad Khorsun wrote:
>> 20.09.2024 4:12, Adriano dos Santos Fernandes:
>>
>>> Unqualified objects should be qualified at compile time and
>>> unambiguously be referenced at BLR. New objects appearing in a schema
>>> should not change execution of another already created object.
>>
>>   Compile or parse (prepare) ?
>
> Compile of text to BLR here.

Ok

> BLR should be stored in a way when it's loaded references does not
> switch from one object to another.

Ok

> The current BLR verbs are now used to reference objects from the same
> schema where the stored object is, thus the problem of user schema ->
> SYSTEM schema -> old ODS.

Why this requirement ? Why it can't reference qualified name in single string ?

Regards,
Vlad

Adriano dos Santos Fernandes

unread,
Sep 20, 2024, 8:03:06 AM9/20/24
to firebir...@googlegroups.com
On 20/09/2024 08:47, Vlad Khorsun wrote:
>
>   Why this requirement ? Why it can't reference qualified name in single
> string ?
>

Because it's already valid object name without considering schema.

It also does not make anything better in relation to the subj.


Adriano

Vlad Khorsun

unread,
Sep 20, 2024, 8:33:33 AM9/20/24
to firebir...@googlegroups.com
20.09.2024 15:02, Adriano dos Santos Fernandes:
> On 20/09/2024 08:47, Vlad Khorsun wrote:
>>
>>   Why this requirement ? Why it can't reference qualified name in single
>> string ?
>>
>
> Because it's already valid object name without considering schema.

Sorry, I don't get you here. BLR itself perfectly can reference both
'table' and 'schema.table'. All else is work for BLR parser and compiler.

> It also does not make anything better in relation to the subj.

It allows to not introduce new BLR verbs, so - it does.

Regards,
Vlad

Dimitry Sibiryakov

unread,
Sep 20, 2024, 8:35:31 AM9/20/24
to firebir...@googlegroups.com
Vlad Khorsun wrote 20.09.2024 14:33:
> BLR itself perfectly can reference both
> 'table' and 'schema.table'. All else is work for BLR parser and compiler.

Only if you use zero as a separator. Otherwise "schema"."table" is not
distinguishable from "schema.table".

--
WBR, SD.

Vlad Khorsun

unread,
Sep 20, 2024, 8:44:10 AM9/20/24
to firebir...@googlegroups.com
20.09.2024 15:35, 'Dimitry Sibiryakov' via firebird-devel:
Hmm... How SQL parser deals with it ?

Regards,
Vlad

Mark Rotteveel

unread,
Sep 20, 2024, 8:56:58 AM9/20/24
to firebir...@googlegroups.com
In SQL, schema.table is equivalent to "SCHEMA"."TABLE"

Mark
--
Mark Rotteveel

Dimitry Sibiryakov

unread,
Sep 20, 2024, 9:02:52 AM9/20/24
to firebir...@googlegroups.com
'Mark Rotteveel' via firebird-devel wrote 20.09.2024 14:56:
>>>    Only if you use zero as a separator. Otherwise "schema"."table" is not
>>> distinguishable from "schema.table".
>>
>>    Hmm... How SQL parser deals with it ?
>
>
> In SQL, schema.table is equivalent to "SCHEMA"."TABLE"

Besides, name length in BLR is one byte so 2*63 characters won't fit.

--
WBR, SD.

Alex Peshkoff

unread,
Sep 20, 2024, 9:59:31 AM9/20/24
to firebir...@googlegroups.com
On 9/20/24 13:43, Adriano dos Santos Fernandes wrote:
> BLR should be stored in a way when it's loaded references does not
> switch from one object to another.

Why this requirement? It solves next to nothing. When object with same
name arrives in schema wih higher priority it's enough to recompile SQL
source for some minor reason (like changed comment in SQL text), and
hops - it's using that object instead initial one. I would prefer to
always use it when loading BLR - that's at least more consistent.


Adriano dos Santos Fernandes

unread,
Sep 20, 2024, 8:46:44 PM9/20/24
to firebir...@googlegroups.com
First of all, stored PSQL text is just informative and removable for
Firebird, so if you want engine to recompile PSQL by his own, you should
first convince people that its storage is required. If people agreed
with this, workarounds were not created when system tables became read only.


Adriano

Adriano dos Santos Fernandes

unread,
Sep 20, 2024, 9:26:20 PM9/20/24
to firebir...@googlegroups.com
On 20/09/2024 10:59, Alex Peshkoff wrote:
It's also very important to note that this above idea is incorrect per
the SQL standard, and IMO the standard is good in this behavior.

Stored objects do not use search paths in its parse/compilation (PSQL to
BLR). Any reference to object in another schema must be qualified.

That's how Firebird should do IMO. Search paths are used in DSQL and
EXECUTE STATEMENT only.

So, if we again think on the problem of stored user's objects
referencing a Firebird system object and the fact that user's object
will be migrated to another schema on restore (1), the original DDL
commands will not work without edit and qualify system objects. And
that's reasonable IMO.

But here we are talking about the actual downgrade restore (2) of cases
like that, what we can do or if that databases will be left for
recreation with non-standard Firebird tools.

(1) The idea here was to not re-encode BLR on restore from old to new
ODS, but to have some metadata flag active only when the object was
created from old backup, actually making the SYSTEM schema in the search
path. That flag would vanish when user manipulates the object again.

(2) The above idea not work here and one workable way would be if gbak
can re-encode BLR for older (and newer too) ODS. This is not going to be
easy.


Adriano

Dimitry Sibiryakov

unread,
Sep 21, 2024, 9:08:18 AM9/21/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 21.09.2024 2:46:
> First of all, stored PSQL text is just informative and removable for
> Firebird, so if you want engine to recompile PSQL by his own, you should
> first convince people that its storage is required. If people agreed
> with this, workarounds were not created when system tables became read only.

IIRC this workaround was created only because it was simpler than proper
solution implemented by RD which hid some fields in system tables from users
that were not allowed to see them.

> (1) The idea here was to not re-encode BLR on restore from old to new
> ODS, but to have some metadata flag active only when the object was
> created from old backup, actually making the SYSTEM schema in the search
> path. That flag would vanish when user manipulates the object again.

This "flag" is BLR version. Raising of BLR version would allow to use the
same BLR verbs with different parameters.

> (2) The above idea not work here and one workable way would be if gbak
> can re-encode BLR for older (and newer too) ODS. This is not going to be
> easy.

Or we can return to RD solution and this time hide SP sources really well so
only internal queries can see them and use them for recompilation on load.

--
WBR, SD.

Adriano dos Santos Fernandes

unread,
Sep 21, 2024, 8:06:07 PM9/21/24
to firebir...@googlegroups.com
There was the case of blr_marks created in v4 and making almost any new
PSQL code to use a new BLR.

It was backported to be parsed and ignored in v3.

We should do the same for schemas, backport new BLR verbs and make them
ignore the schema name. It will make possible to downgrade databases
without repeated names in different schemas, and that is a requirement
for such downgrades.


Adriano

Denis Simonov

unread,
Sep 23, 2024, 2:24:45 AM9/23/24
to firebird-devel
Why not adopt the rule that unqualified table names belong to the same schema as the stored procedure/function?

воскресенье, 22 сентября 2024 г. в 03:06:07 UTC+3, Adriano dos Santos Fernandes:

Adriano dos Santos Fernandes

unread,
Sep 23, 2024, 7:41:50 AM9/23/24
to firebir...@googlegroups.com
On 23/09/2024 03:24, Denis Simonov wrote:
> Why not adopt the rule that unqualified table names belong to the same
> schema as the stored procedure/function?

This rule is already adopted.

The problem is that we want to separate user's objects in a different
schema than Firebird system objects, and user's object can reference the
system objects.


Adriano

Dimitry Sibiryakov

unread,
Sep 23, 2024, 7:45:07 AM9/23/24
to firebir...@googlegroups.com
Adriano dos Santos Fernandes wrote 23.09.2024 13:41:
> The problem is that we want to separate user's objects in a different
> schema than Firebird system objects, and user's object can reference the
> system objects.

The simplest solution would be to deny upgrade and downgrade by gbak completely.

--
WBR, SD.

Vlad Khorsun

unread,
Sep 23, 2024, 10:27:59 AM9/23/24
to firebir...@googlegroups.com
23.09.2024 14:45, 'Dimitry Sibiryakov' via firebird-devel:

>   The simplest solution would be to deny upgrade and downgrade by gbak completely.

The best solution would be if you don't do such "useful" comments at all

Vlad

Dimitry Sibiryakov

unread,
Sep 23, 2024, 10:29:20 AM9/23/24
to firebir...@googlegroups.com
Vlad Khorsun wrote 23.09.2024 16:27:
>
>   The best solution would be if you don't do such "useful" comments at all

Nothing prevent you from ignoring them.

--
WBR, SD.

Dmitry Yemanov

unread,
Sep 23, 2024, 11:43:14 AM9/23/24
to firebir...@googlegroups.com
Sounds like a good plan.


Dmitry

Vlad Khorsun

unread,
Sep 23, 2024, 11:44:27 AM9/23/24
to firebir...@googlegroups.com
22.09.2024 3:06, Adriano dos Santos Fernandes:
If new BLR verbs unavoidable - this looks as a good solution to me.

Regards,
Vlad

Reply all
Reply to author
Forward
0 new messages