So, that's 40. So, I'm an active member of the French community and I'm also
the main developer on the next extension called Prosecutor and Animizer. So, my
journey, basically, I've been working with Postgres for the last 25 years and I just discovered
the rest last year. So, probably most of you know much more about 12 than me. So, anyway,
I've gone try to keep up. So, my story is like a few years ago, I started this extension
and I had to write a lot of secret and I really, really don't like to write secret. And,
and so, last year I decided to rewrite everything in West and so, that's my story.
So, I'm going to talk to you first of all with what is the Postgres extension. So, you understand
what is I take here and then I will present a rest framework called PGRX and then we'll give you
just practical examples. So, you see really quickly how to get involved. And, Sharma,
the lesson I learned when I did the migration. Okay. So, let's start with one of the
worst advice in computing is choose the right tool for the job. So, this is really, really dumb.
And, with Postgres especially, because you will always find people saying, okay, you need a specific
database for this kind of data. Maybe MongoDB for no SQL data or whatever. This is utterly
false. PostgresQL is good enough for almost all kinds of usage. And, why is that? It's because
PostgresQL is not a database. It's a platform. You have hundreds and hundreds of extensions
that will end on the almost any kind of use case you have. So, it may not be the best
in all of these areas, but it's good enough. And, most of the time, good enough is better than choosing
the right tool for the job. So, this is a bit of an exploration. But, so why is Postgres good enough
in all these areas? Well, it's because of its unique ecosystem of extension. So, we do now
have around 1,000 known extensions published on GitHub. Of course, they are not all ready for prediction.
I would say you have one quarter of them that are okay to run in prediction, but that's already
very impressive. You know other database system as this kind of ecosystem around it.
So, what's the Postgres extension? Well, it's quite simple. It's basically SQL objects.
And, some procedural code or a compile library, or both. So, the easiest way to write an extension is
to write it in SQL or in PL code. It's easy. It's a great way to move code from to share
functions between your databases. It's very stable between major version. It's kind of so,
but you can write it in any type of language you want. So, the most common language is PLQGSQL,
which is inspired by the Oracle PLSQL language. But, you also have all kinds of flavors of
language are possible. So, basically, there's an example of a function you can write. And,
yeah, this one is written in SQL, but you could write again you can write in any flavor you want.
And, then you can call it within your select queries. Yeah, you might have seen that there's a
PL rest. It's great. I'm not going to talk about it today, because it's a wall of the story,
but yes, you can run a rest at the procedural level. But, I'm going to go deeper,
because what I want with my extension is go directly into the core of Postgres. So, yeah,
general is the way to do this is to write the extension in C. So, this extension will be
it's a libraries that will be loaded when the in-sum stops. It's really fast, it's direct,
and you have that direct access to the internals. So, you really, you are inside the
database engine, and you can call any kind of PostgresQL function inside this. So, it's very
low level code, you have no abstraction. Probably every year, there's a new version of Postgres,
which is released, you'll code Willbreak, because they will add new signatures to function,
or new features. And, so you'll need every year to maintain your extensions to keep up.
And, the desk, also tooling around it, which is called PG Access, is very, very limited actually.
And, also, you have no security barriers, so you get a lot, a lot, a lot, a lot of
set faults. And, if you set fault, an extension, the entire instance will crash. So, let me repeat that,
if you extension crashes, the world server will crash too. So, it's kind of bad.
Yeah, people really don't want that, actually, if they are installing your extension,
they don't expect that you extension with six of the world ship, if it has a problem.
So, can you have the best of before, actually, can we have the safety of the PL function,
but also the preferences, we want a level extraction, but we also want to have access to
it to the processing channels. And, we want a modern language, but we also want stability.
So, this is what PGRX is for. It's a brush of fresh air, actually, if you want,
if you have an extension developer, it's a really, really nice solution. So, the idea is to
framework that bridge the gap between your rest code and the post-resculine channels. And,
and you can write rest extension without it, it's possible. It won't need it, but actually,
it really makes your life very easier. So, the principle is just you're going to expose
rest functions inside the post-res. And, you will have direct mapping between the
post-resculine data types and the rest types. And, the other way around too. So, basically,
if you have a text, it's going to be interpreted as string or STR in your code.
If you have an integer, it's going to be a nice 32, etc, etc. And, for the types that don't exist in
the rest directly, like a date, the PIGG RX from Uncle Poor I do, those types. And, of course,
new does not really exist in restory. You will have an option 9 for that. 11 month feedback,
too. So, it's very easy to try something, get feedback, and move on.
All right. So, but what is PIGG RX? Because, if you're going to work with this to build your
extension, you need, like the previous talk, was saying, if you had this kind of dependency to your
project, you better have trust in this project. So, this was a project that was launched by a
single company, but it's now transferred to a foundation. So, there's a role, all collective around it,
so it's trustable. And, it's also, there's a very, very friendly discord community around it,
and which is very helpful when you want to start. Okay, that's was for the big picture.
Let's go in practical details now. Okay, let's go. So, first of all, we need to install that PIGG RX,
with a classic cargo tuning, and we're going to initialize the development of our amount
with cargo PIGG RX in it. So, what this is going to do, it's going to create for you five
post-rescurial instances in your own directory. Okay, so you're going to have five different
post-rescurial instances in your own directory for testing. So, it's going to hit a lot of space
on your own folder, but yeah, you're going to be able to launch any at any time
that was basically an instance. So, it's now created a project called World, and we've
cargo PIGG RX new world, and let's look inside what this new project. In this new project,
you have a lib RC file with this function, that you may recognize as vras code.
I guess you understand what this does, but the new thing here is that you have this decores,
it's attribute at the first time, which says PIGG XR, all right? So, this macro attribute,
we only will just say that this function will be co-label, you can call this inside post-res.
That's all. That's all we need to do to expose you with, just drop the previous version of the
extension and create the new one. And here we go, I can select this function with my parameter.
All right, let's go ahead with the lib owner. Oh, yeah, sorry, if you want it to do this in C,
this is, I'm hoping maybe you can do it in a few less lines, but basically that's this is how you
would do it in C. So, yeah, no comment. Any kind of gents in the room? Good, good. So, let's have a
better example. So, the kind of gents social insurance number, which I will call sin,
is composed of nine numbers, nine digits, sorry. So, the eight first are the real digit, and you have
the last one is just a control bit, a checksum. And this control bit is computed with the
length formula, which says that if you have zero for six, etc, the control digit is six.
How does that cost? I don't know, I don't care. But unfortunately, there is a crate that has implemented
this, this algorithm. So, I'm going to use it. And I'm going to expose a function that just
actually compute this checksum. So, I'm just going to import the checksum function because I don't
need anything else. And again, I'm going to take an input text and I'll put just the character
of the checksum. Let's go back. We rebuild the checksum, let's go again. And here we go. We have
the checksum implementation right away. And of course, if I provide something that is a text,
but she's not a number, it will fail. And again, it will fail without crashing the world several
or so, it's, yeah. But this is manual testing. But now I would like to test this. I would like
to add a unique test around this. So, again, I'm going to be used very classic tooling from us
with this test function. And again, you have the PG test attributes for this function,
which is just going to say, okay, this functions, you need a possible instance to test it.
So, you see, again, I'm going to just going to check that the checksum of one is eight.
Let's go. I'm simply going to log cargo PG racks test. All right. And so, but cargo PG racks
will launch up a special instance and call all my tests against this instance.
And if I want to check on another versions, I can just say, look, put functions. So, this
has the two first lines here are crucial. And the input function are just traits, all right.
So, I'm going to declare a trait, an input trait that will receive a C string.
And I will have now put traits that will display, again, the text representation of my type.
So, the input is quite simple. I'm just going to receive the text. I'm going to remove all those spaces
and I'm going to validate that this is a correct LUN number, all right.
And there was a wire round. I'm going to take a valid number and I'm going to output it.
Yeah, I'm just going to split the number into three parts. So, it's easier to to learn to watch.
Again, let's go let's try this. So, as you see there, I'm going to cast. So, I'm going to take a text
and transform it into my new C-type. So, the first one will work. As you see, I'm going to push
some text and I'll put his waveform. And there's a round. If I'm pushing an invalid number,
I'm going to get an error. Okay, so let's now use this as an inside the table. So, I'm going to
table for all the televisions and it won't work. It won't work because I didn't specify my
operator classes for this type, you know, because as it's this is a primary key, I need to have
some operators to sort this data. So, let's go back to the rest code. And I'm going to add more
and more operators. So, more attributes to derive all these ordering operators, equality operators,
etc. etc. Logical replication system. You can basically build your index method. You can also
build your table access method. This is, you can almost do anything with this. So, it's my feedback
now from rewriting the extension in C2 rest. So, the first question is, it's just a data
masking extension. So, you can put rules upon some columns and say, okay, this column should be
masked, that is, or this column should be blurred or anything. I'm not going to talk about it right
now because I'm going to talk about it tomorrow in the possible development if you want to learn more
about data privacy, see you tomorrow. But, yeah, on the technical side, it was about one
thousand lines of code of C code. So, not that big of a project, but still, again, some, yeah,
some fairly used code base. And I took, took for me, like, a few weeks to rewrite everything
without pre-unnerability. So, it was kind of difficult at the beginning. But, you get a sense of
deja vu, which means, yeah, as a post-rescule DBA, you already kind of a custom to the fact that,
it's sad at the beginning to make things, when you start to make things properly. First compare,
it's kind of, yeah, dull and rough for beginners, I think. Most people know that. But, once you climb
the ladder, you reach the certain level, you get rewarded because you got good habits about it.
And it's pretty much like the post-rescule. So, yeah. And so, you may get gains by switching to
rest was the comfort of the development, because I don't spend a lot of time of this project,
it's kind of a side project. So, when I go back to the code, I need to, to both in a few lines,
to blur the imaging inside the database. This is something that would have been very,
very difficult to do in C, of course, and even more difficult in a peer code. And so, I get
stability. So, no more stakeholders, and basically, I'm really, really, I'm sleeping better now
that I know that this extension doesn't want a crash in the front of the end users.
So, there's gains, but also some culture differences. So, the first one is that, in rest, nothing
is new. Everything is defined and more defined. Whereas, in post-rescule, by default, if you don't
set a value into the current, it's going to be the current, it's going to be new. So, you need to
switch a little bit, your mindset about that. So, everything is not about the post-rescule
internal functions. Everything is not radial, available right now. So, you still need to be
to do some digging and some work around to get what you want. And also, it's kind of
complicated at first, because you have two basically, you have two memory contexts. You have
post-rescule memory context and the extension memory context. And if you want to pass a
object from one to another, it's not that simple. And it's not magic. If you want to work,
it's not entirely slept. So, you still need to do some unsafe section in your code.
The building process is really, really slow. So, that's a major problem for us,
because it took basically, you can see the extension took like one minute to compile in CI.
And currently in CI, it will take like 20 minutes to compile. So, yes, and you need to compile
it upon the five current puzzle versions. So, that's five multiplied by 20.
You also have no support of Windows at the moment. So, I don't know, it's a problem, but maybe
for some problem, for some people. And for advanced visual, you still need to read and understand
the puzzle sheet. And so, yeah, just come on and try by yourself. Actually, it's really,
is it to bring back your code close to the data, always a good idea, if you ask me.
As we said, one of the easiest way to start is to create data types that are relevant for your
use case, and use them to, for indexation, for control, for data quality. And just think of
both ways as a platform and not just the database. And yes, overall, if you want to have a first step
in the process of community, actually, rest extension, I will give it a great entry point to start.
And that's it for me. So, if you want to learn more about the PGRX framework,
the website is there. I also did a four hour tutorial with many, many, many, many, many more
examples, which is available. And if you want to try the post-rescue and the
laser extension, it's also on GitHub. Thanks a lot.
Any questions?
Thank you. How do you distribute the extensions?
I did not understand.
How do you distribute the extension?
Ah, it's a really, really good question. So, you have in charge of cargo PGRX, you have
a release option that will build up all the folder for building packages. So, again, I would
don't advise to people who want to use cargo to install this in production. Of course,
so you need to build RPM and DBM packages. The problem now is that the official
post-rescue repository will not build this RPM for you. So, I won't build
rest project and go project, actually, or two. So, right now, the extension is not
available on the PGRG, on the official post-rescue delivery repositories. This is the
situation right now. It's made change. But, like I said, with a building time,
the CPU time it takes to build all these versions. It's not a simple issue for them.
Currently, Postgres has role-level security and I was wondering, will this extension mechanism
can you make that even finer and build field-level security by implementing your rest
extension? Sorry, I didn't know that, then again.
Currently, Postgres has role-level security implemented in it. Not perfect, but it's there.
I was wondering, if you can write the rest extension to make that even finer and do it at
field-level, would that be fixed by my extension against the data, like having fixture
already loaded in the database, how can I tell it? For example, if I want to test my extension
against the full table like with thousands of lines, yes, I want to write, you just create this