FirebirdSQL pseudo columns.

[Eagna]

Hi all,


What are the FirebirdSQL pseudo-columns? I did a search for "FirebirdSQL pseudocolumn list" and all I got was this:

https://www.google.com/search?client=firefox-b-d&q=firebirdsql+pseudocolumn+list

and it mentions RDB$RECORD_VERSION but nothing else.

I thought that Firebird was similar to PostgreSQL's MVCC implementation which requires a few pseudo-columns to be associated with each record?

Any input appreciated.

Pól...

[Mark Rotteveel]

For Firebird 2.5 and earlier, the only pseudo-column is RDB$DB_KEY.
Firebird 3.0 added RDB$RECORD_VERSION.

Why do you think a MVCC implementation "requires a few pseudo-columns",
and which columns would that be? (Also consider that things that are
available internally don't necessarily need to be exposed to the world
through SQL).

Mark
--
Mark Rotteveel

[Ann Harrison]

> On Jun 23, 2022, at 4:47 AM, Mark Rotteveel <ma...@lawinegevaar.nl> wrote:

Egna is probably thinking of the information in the Firebird record header - transaction id and back pointer.

Ann
>
> Mark
> --
> Mark Rotteveel
>
> --
> You received this message because you are subscribed to the Google Groups "firebird-support" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
> To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/2e46711c-3601-7348-538c-0fed7116ff9b%40lawinegevaar.nl.

[Mark Rotteveel]

On 23-06-2022 12:17, Ann Harrison wrote:
>> On Jun 23, 2022, at 4:47 AM, Mark Rotteveel <ma...@lawinegevaar.nl> wrote:
>>
>> On 23-06-2022 10:36, 'Eagna' via firebird-support wrote:
>>> What are the FirebirdSQL pseudo-columns? I did a search for "FirebirdSQL pseudocolumn list" and all I got was this:
>>> https://www.google.com/search?client=firefox-b-d&q=firebirdsql+pseudocolumn+list
>>> and it mentions RDB$RECORD_VERSION but nothing else.
>>> I thought that Firebird was similar to PostgreSQL's MVCC implementation which requires a few pseudo-columns to be associated with each record?
>>> Any input appreciated.
>>
>>
>> For Firebird 2.5 and earlier, the only pseudo-column is RDB$DB_KEY. Firebird 3.0 added RDB$RECORD_VERSION.
>>
>> Why do you think a MVCC implementation "requires a few pseudo-columns", and which columns would that be? (Also consider that things that are available internally don't necessarily need to be exposed to the world through SQL).
>
> Egna is probably thinking of the information in the Firebird record header - transaction id and back pointer.

The transaction id is represented by RDB$RECORD_VERSION.

To be clear, my reply was meant to elicit more information, but I guess
it may read a bit grumpy ;)

Mark
--
Mark Rotteveel

[Eagna]

Hi, and thanks for having replied to me.

Mark Rottveel wrote:
> The transaction id is represented by RDB$RECORD_VERSION.

> To be clear, my reply was meant to elicit more information,
> but I guess it may read a bit grumpy ;)

More information you requested and more information you shall have! :-)

PostgreSQL has 23 bytes of overhead per record - here is a list of its pseudo-columns (it calls them system columns):

https://www.postgresql.org/docs/14/ddl-system-columns.html

These give rise to an overhead of ~ 24 bytes per record, as per here:

https://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes

PostgreSQL has vacuum, Firebird has sweep.

The architectures are very similar - so I'm assuming that Firebird must have (a) similar mechanism(s) of keeping track of which records are visible to which transaction?

Or, maybe as you pointed out, these mechanisms aren't visible via SQL, unlike PostgreSQL where they are visible?

I notice that Ann Harrison (thanks to her as well - glad that she's still keeping her finger in the Firebird pie), mentions that:

> Egna is probably thinking of the information in the Firebird record header - transaction id and back pointer.

which is probably more like what I'm after. I searched for "Firebird record header" and that led me here:

https://firebirdsql.org/manual/fbint-page-5.html#fbint-p5-record-header


So, there appears to be at least ~ 14 bytes of overhead per record! But, as you say, maybe not visible through SQL?

I'm interested in why there's approx. a 10 byte greater overhead for PostgreSQL compared with FirebirdSQL?

Is there anything in the MON$ tables?


TIA and rgs,


Pól...


> Mark

[Dimitry Sibiryakov]

'Eagna' via firebird-support wrote 23.06.2022 22:17:
> PostgreSQL has vacuum, Firebird has sweep.
>
> The architectures are very similar - so I'm assuming that Firebird must have (a) similar mechanism(s) of keeping track of which records are visible to which transaction?

Actually they have nothing common. Sweep and vacuum are completely different
processes aimed to different targets.
Also "pseudo-columns" don't need to have any relation to record storage,
header and overhead. Though RDB$RECORD_VERSION indeed uses information kept in
record version header, RDB$DB_KEY isn't - it is completely calculated from
record's physical location.

--
WBR, SD.

[Ann Harrison]

On Thu, Jun 23, 2022 at 4:17 PM 'Eagna' via firebird-support <firebird...@googlegroups.com> wrote:

...

"Firebird record header" and that led me here:

https://firebirdsql.org/manual/fbint-page-5.html#fbint-p5-record-header


So, there appears to be at least ~ 14 bytes of overhead per record! But, as you say, maybe not visible through SQL?

I'm interested in why there's approx. a 10 byte greater overhead for PostgreSQL compared with FirebirdSQL?

My information is old, but maybe mostly correct.  Firebird had multi-generational concurrency control while Postgres

was still recovering from its Ingres roots.  That means the implementation is really old and reflects the bit-bumming

that was common in the mid-eighties when memory and disk space would embarrass a modern parking meter.

Part of the reason why the Firebird record header is smaller is that it doesn't contain the transaction id of the 

transaction that deleted a record.  When a record is deleted, Firebird creates a "deleted stub", an empty record.  

When the transaction that deleted the record is committed and every translation contemporary with that transaction 

has ended, the next pass of garbage collection - think of it as continuous vacuuming - will remove the stub and 

all the back versions.  If the transaction that deleted the stub fails, the stub is removed and the previous version 

becomes the primary version again.

In Firebird, record versions are linked from newest to oldest.  When creating a new version, Firebird checks

to see if there is enough space on the page with the older version - that page I'll call the primary page for the

record.  .  If so, fine and Firebird just twiddles the page index* on the primary page so all references in user 

defined indexes point to the new version.  If not, Firebird moves the old record to a different page.  Under some 

circumstances, the new record may still not fit on the primary page.  In that case, Firebird fragments the new 

record and marks its header to show that it's fragmented and where to find the rest of it.   Fragmented record 

headers are bigger than normal record headers.

Another piece of information in the record header is whether the next older version is stored completely or 

as a delta from the next newer version.  Storing deltas saves a lot of space when most changes to records are

small and it makes finding space on the primary page much easier.  If there's a one byte change in a 500 byte

record, it's stored in a few bytes.  If we had had one more bit, we would have marked the delta record version

as a delta, which would have made debugging that code much easier.  But we didn't.

There's another interesting piece of information in the record header - the format version number. When a table 

is defined, Firebird creates a record in the system table RDB$Formats which describes the format of the record 

(dunh).  When the definition changes, Firebird stores a new record in RDB$Formats describing the new format.  

When Firebird reads a record, it knows what format version it wants and converts old formats to new.  Almost 

all changes to a table can be performed without rewriting the data.  And of course, that's why for many decades, 

Firebird only allowed 255 changes to the format of a table before it's time to backup and restore the database. 

Is there anything in the MON$ tables?

Beats me. None of that stuff struck us at the time as useful to a database user.  Yes, it's probably interesting

to know how much space is used by the system vs. the stuff you actually want.

Cheers,

Ann

* OK, a bit more nitty-gritty.  The RDB$DB_KEY of a record has little to do with the record's physical location, even

though it's described as the page and line number.  The page number is actually the number of the page among the

pages in the table, itself a two level hierarchy.  A data page has two parts** - a storage part and an array of indexes

that contain the length of the compressed record and the offset of the record  in the storage part.  Here, I get 

confused because I worked on several databases that use essentially the same mechanism.  Some had the indes

growing down from the top while records were stored from the bottom up, and some the other way around.  The 

essence is if the records are big after compression, more space is used for storage.  If the records are small,

more space is used for the index. 

** actually there are two other parts, a page header that describes the page type and other stuff depending on

the page type, and space reserved for a checksum.