WEBVTT

00:00.000 --> 00:20.240
So, that's 40. So, I'm an active member of the French community and I'm also

00:20.240 --> 00:27.000
the main developer on the next extension called Prosecutor and Animizer. So, my

00:27.000 --> 00:33.560
journey, basically, I've been working with Postgres for the last 25 years and I just discovered

00:33.560 --> 00:40.840
the rest last year. So, probably most of you know much more about 12 than me. So, anyway,

00:40.840 --> 00:48.920
I've gone try to keep up. So, my story is like a few years ago, I started this extension

00:48.920 --> 00:56.440
and I had to write a lot of secret and I really, really don't like to write secret. And,

00:57.560 --> 01:03.240
and so, last year I decided to rewrite everything in West and so, that's my story.

01:04.200 --> 01:10.360
So, I'm going to talk to you first of all with what is the Postgres extension. So, you understand

01:10.360 --> 01:20.360
what is I take here and then I will present a rest framework called PGRX and then we'll give you

01:20.440 --> 01:27.480
just practical examples. So, you see really quickly how to get involved. And, Sharma,

01:27.480 --> 01:35.640
the lesson I learned when I did the migration. Okay. So, let's start with one of the

01:35.640 --> 01:44.280
worst advice in computing is choose the right tool for the job. So, this is really, really dumb.

01:45.160 --> 01:51.720
And, with Postgres especially, because you will always find people saying, okay, you need a specific

01:51.720 --> 01:59.160
database for this kind of data. Maybe MongoDB for no SQL data or whatever. This is utterly

01:59.160 --> 02:07.560
false. PostgresQL is good enough for almost all kinds of usage. And, why is that? It's because

02:07.640 --> 02:14.200
PostgresQL is not a database. It's a platform. You have hundreds and hundreds of extensions

02:14.200 --> 02:21.160
that will end on the almost any kind of use case you have. So, it may not be the best

02:22.440 --> 02:30.680
in all of these areas, but it's good enough. And, most of the time, good enough is better than choosing

02:30.760 --> 02:39.160
the right tool for the job. So, this is a bit of an exploration. But, so why is Postgres good enough

02:39.160 --> 02:46.680
in all these areas? Well, it's because of its unique ecosystem of extension. So, we do now

02:46.680 --> 02:56.520
have around 1,000 known extensions published on GitHub. Of course, they are not all ready for prediction.

02:57.000 --> 03:06.920
I would say you have one quarter of them that are okay to run in prediction, but that's already

03:08.600 --> 03:15.080
very impressive. You know other database system as this kind of ecosystem around it.

03:17.720 --> 03:23.800
So, what's the Postgres extension? Well, it's quite simple. It's basically SQL objects.

03:24.280 --> 03:39.960
And, some procedural code or a compile library, or both. So, the easiest way to write an extension is

03:39.960 --> 03:47.720
to write it in SQL or in PL code. It's easy. It's a great way to move code from to share

03:48.040 --> 03:55.640
functions between your databases. It's very stable between major version. It's kind of so,

03:56.280 --> 04:05.880
but you can write it in any type of language you want. So, the most common language is PLQGSQL,

04:05.880 --> 04:16.200
which is inspired by the Oracle PLSQL language. But, you also have all kinds of flavors of

04:16.200 --> 04:23.480
language are possible. So, basically, there's an example of a function you can write. And,

04:24.680 --> 04:29.960
yeah, this one is written in SQL, but you could write again you can write in any flavor you want.

04:30.840 --> 04:40.040
And, then you can call it within your select queries. Yeah, you might have seen that there's a

04:40.120 --> 04:47.160
PL rest. It's great. I'm not going to talk about it today, because it's a wall of the story,

04:47.160 --> 04:56.600
but yes, you can run a rest at the procedural level. But, I'm going to go deeper,

04:56.600 --> 05:05.240
because what I want with my extension is go directly into the core of Postgres. So, yeah,

05:05.480 --> 05:13.880
general is the way to do this is to write the extension in C. So, this extension will be

05:13.880 --> 05:19.240
it's a libraries that will be loaded when the in-sum stops. It's really fast, it's direct,

05:19.240 --> 05:25.240
and you have that direct access to the internals. So, you really, you are inside the

05:26.440 --> 05:34.120
database engine, and you can call any kind of PostgresQL function inside this. So, it's very

05:34.200 --> 05:41.000
low level code, you have no abstraction. Probably every year, there's a new version of Postgres,

05:41.000 --> 05:47.400
which is released, you'll code Willbreak, because they will add new signatures to function,

05:47.400 --> 05:55.560
or new features. And, so you'll need every year to maintain your extensions to keep up.

05:57.240 --> 06:03.960
And, the desk, also tooling around it, which is called PG Access, is very, very limited actually.

06:05.080 --> 06:12.680
And, also, you have no security barriers, so you get a lot, a lot, a lot, a lot of

06:12.680 --> 06:21.560
set faults. And, if you set fault, an extension, the entire instance will crash. So, let me repeat that,

06:22.280 --> 06:28.440
if you extension crashes, the world server will crash too. So, it's kind of bad.

06:28.760 --> 06:34.200
Yeah, people really don't want that, actually, if they are installing your extension,

06:34.200 --> 06:39.400
they don't expect that you extension with six of the world ship, if it has a problem.

06:41.160 --> 06:47.320
So, can you have the best of before, actually, can we have the safety of the PL function,

06:47.320 --> 06:53.720
but also the preferences, we want a level extraction, but we also want to have access to

06:53.720 --> 06:59.320
it to the processing channels. And, we want a modern language, but we also want stability.

07:00.440 --> 07:07.320
So, this is what PGRX is for. It's a brush of fresh air, actually, if you want,

07:07.320 --> 07:17.160
if you have an extension developer, it's a really, really nice solution. So, the idea is to

07:17.160 --> 07:23.400
framework that bridge the gap between your rest code and the post-resculine channels. And,

07:24.440 --> 07:31.480
and you can write rest extension without it, it's possible. It won't need it, but actually,

07:31.480 --> 07:37.640
it really makes your life very easier. So, the principle is just you're going to expose

07:37.640 --> 07:45.480
rest functions inside the post-res. And, you will have direct mapping between the

07:45.480 --> 07:53.800
post-resculine data types and the rest types. And, the other way around too. So, basically,

07:53.800 --> 07:59.800
if you have a text, it's going to be interpreted as string or STR in your code.

08:00.840 --> 08:08.680
If you have an integer, it's going to be a nice 32, etc, etc. And, for the types that don't exist in

08:08.760 --> 08:19.400
the rest directly, like a date, the PIGG RX from Uncle Poor I do, those types. And, of course,

08:19.400 --> 08:25.240
new does not really exist in restory. You will have an option 9 for that. 11 month feedback,

08:25.240 --> 08:29.240
too. So, it's very easy to try something, get feedback, and move on.

08:29.960 --> 08:38.840
All right. So, but what is PIGG RX? Because, if you're going to work with this to build your

08:38.840 --> 08:45.640
extension, you need, like the previous talk, was saying, if you had this kind of dependency to your

08:45.640 --> 08:51.080
project, you better have trust in this project. So, this was a project that was launched by a

08:51.080 --> 08:56.920
single company, but it's now transferred to a foundation. So, there's a role, all collective around it,

08:57.000 --> 09:06.280
so it's trustable. And, it's also, there's a very, very friendly discord community around it,

09:06.280 --> 09:17.960
and which is very helpful when you want to start. Okay, that's was for the big picture.

09:17.960 --> 09:25.640
Let's go in practical details now. Okay, let's go. So, first of all, we need to install that PIGG RX,

09:26.600 --> 09:33.400
with a classic cargo tuning, and we're going to initialize the development of our amount

09:33.400 --> 09:39.880
with cargo PIGG RX in it. So, what this is going to do, it's going to create for you five

09:39.880 --> 09:46.920
post-rescurial instances in your own directory. Okay, so you're going to have five different

09:47.640 --> 09:56.040
post-rescurial instances in your own directory for testing. So, it's going to hit a lot of space

09:56.040 --> 10:04.760
on your own folder, but yeah, you're going to be able to launch any at any time

10:05.720 --> 10:12.600
that was basically an instance. So, it's now created a project called World, and we've

10:13.400 --> 10:21.560
cargo PIGG RX new world, and let's look inside what this new project. In this new project,

10:21.560 --> 10:29.320
you have a lib RC file with this function, that you may recognize as vras code.

10:31.000 --> 10:35.960
I guess you understand what this does, but the new thing here is that you have this decores,

10:35.960 --> 10:45.480
it's attribute at the first time, which says PIGG XR, all right? So, this macro attribute,

10:46.360 --> 10:54.040
we only will just say that this function will be co-label, you can call this inside post-res.

10:54.040 --> 11:03.160
That's all. That's all we need to do to expose you with, just drop the previous version of the

11:03.240 --> 11:13.000
extension and create the new one. And here we go, I can select this function with my parameter.

11:14.280 --> 11:20.840
All right, let's go ahead with the lib owner. Oh, yeah, sorry, if you want it to do this in C,

11:22.280 --> 11:31.240
this is, I'm hoping maybe you can do it in a few less lines, but basically that's this is how you

11:31.320 --> 11:44.760
would do it in C. So, yeah, no comment. Any kind of gents in the room? Good, good. So, let's have a

11:44.760 --> 11:51.960
better example. So, the kind of gents social insurance number, which I will call sin,

11:52.520 --> 11:59.400
is composed of nine numbers, nine digits, sorry. So, the eight first are the real digit, and you have

12:00.280 --> 12:06.920
the last one is just a control bit, a checksum. And this control bit is computed with the

12:06.920 --> 12:14.680
length formula, which says that if you have zero for six, etc, the control digit is six.

12:16.200 --> 12:25.960
How does that cost? I don't know, I don't care. But unfortunately, there is a crate that has implemented

12:26.040 --> 12:35.080
this, this algorithm. So, I'm going to use it. And I'm going to expose a function that just

12:37.240 --> 12:44.760
actually compute this checksum. So, I'm just going to import the checksum function because I don't

12:44.760 --> 12:54.120
need anything else. And again, I'm going to take an input text and I'll put just the character

12:54.200 --> 13:03.400
of the checksum. Let's go back. We rebuild the checksum, let's go again. And here we go. We have

13:03.400 --> 13:11.320
the checksum implementation right away. And of course, if I provide something that is a text,

13:11.320 --> 13:18.920
but she's not a number, it will fail. And again, it will fail without crashing the world several

13:18.920 --> 13:27.480
or so, it's, yeah. But this is manual testing. But now I would like to test this. I would like

13:27.480 --> 13:35.640
to add a unique test around this. So, again, I'm going to be used very classic tooling from us

13:36.520 --> 13:46.200
with this test function. And again, you have the PG test attributes for this function,

13:46.760 --> 13:53.560
which is just going to say, okay, this functions, you need a possible instance to test it.

13:54.520 --> 14:00.600
So, you see, again, I'm going to just going to check that the checksum of one is eight.

14:03.800 --> 14:13.880
Let's go. I'm simply going to log cargo PG racks test. All right. And so, but cargo PG racks

14:13.960 --> 14:19.320
will launch up a special instance and call all my tests against this instance.

14:22.200 --> 14:28.280
And if I want to check on another versions, I can just say, look, put functions. So, this

14:28.280 --> 14:36.200
has the two first lines here are crucial. And the input function are just traits, all right.

14:36.840 --> 14:41.880
So, I'm going to declare a trait, an input trait that will receive a C string.

14:42.840 --> 14:55.480
And I will have now put traits that will display, again, the text representation of my type.

14:56.040 --> 15:04.840
So, the input is quite simple. I'm just going to receive the text. I'm going to remove all those spaces

15:05.560 --> 15:16.120
and I'm going to validate that this is a correct LUN number, all right.

15:17.960 --> 15:23.480
And there was a wire round. I'm going to take a valid number and I'm going to output it.

15:24.840 --> 15:32.040
Yeah, I'm just going to split the number into three parts. So, it's easier to to learn to watch.

15:33.000 --> 15:40.280
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

15:40.280 --> 15:48.280
and transform it into my new C-type. So, the first one will work. As you see, I'm going to push

15:48.280 --> 15:56.200
some text and I'll put his waveform. And there's a round. If I'm pushing an invalid number,

15:56.200 --> 16:06.120
I'm going to get an error. Okay, so let's now use this as an inside the table. So, I'm going to

16:06.120 --> 16:15.400
table for all the televisions and it won't work. It won't work because I didn't specify my

16:15.400 --> 16:22.360
operator classes for this type, you know, because as it's this is a primary key, I need to have

16:22.360 --> 16:30.520
some operators to sort this data. So, let's go back to the rest code. And I'm going to add more

16:30.520 --> 16:39.480
and more operators. So, more attributes to derive all these ordering operators, equality operators,

16:39.480 --> 16:48.200
etc. etc. Logical replication system. You can basically build your index method. You can also

16:48.280 --> 16:59.720
build your table access method. This is, you can almost do anything with this. So, it's my feedback

17:00.520 --> 17:16.360
now from rewriting the extension in C2 rest. So, the first question is, it's just a data

17:16.360 --> 17:23.080
masking extension. So, you can put rules upon some columns and say, okay, this column should be

17:23.080 --> 17:32.920
masked, that is, or this column should be blurred or anything. I'm not going to talk about it right

17:32.920 --> 17:37.960
now because I'm going to talk about it tomorrow in the possible development if you want to learn more

17:38.040 --> 17:47.960
about data privacy, see you tomorrow. But, yeah, on the technical side, it was about one

17:47.960 --> 17:59.320
thousand lines of code of C code. So, not that big of a project, but still, again, some, yeah,

17:59.320 --> 18:11.000
some fairly used code base. And I took, took for me, like, a few weeks to rewrite everything

18:11.000 --> 18:18.840
without pre-unnerability. So, it was kind of difficult at the beginning. But, you get a sense of

18:18.920 --> 18:29.560
deja vu, which means, yeah, as a post-rescule DBA, you already kind of a custom to the fact that,

18:31.800 --> 18:38.200
it's sad at the beginning to make things, when you start to make things properly. First compare,

18:38.200 --> 18:47.000
it's kind of, yeah, dull and rough for beginners, I think. Most people know that. But, once you climb

18:47.080 --> 18:56.280
the ladder, you reach the certain level, you get rewarded because you got good habits about it.

18:56.280 --> 19:03.560
And it's pretty much like the post-rescule. So, yeah. And so, you may get gains by switching to

19:03.560 --> 19:12.440
rest was the comfort of the development, because I don't spend a lot of time of this project,

19:12.440 --> 19:20.200
it's kind of a side project. So, when I go back to the code, I need to, to both in a few lines,

19:20.200 --> 19:27.000
to blur the imaging inside the database. This is something that would have been very,

19:27.000 --> 19:38.280
very difficult to do in C, of course, and even more difficult in a peer code. And so, I get

19:38.360 --> 19:43.960
stability. So, no more stakeholders, and basically, I'm really, really, I'm sleeping better now

19:43.960 --> 19:51.640
that I know that this extension doesn't want a crash in the front of the end users.

19:54.600 --> 20:05.000
So, there's gains, but also some culture differences. So, the first one is that, in rest, nothing

20:05.000 --> 20:11.320
is new. Everything is defined and more defined. Whereas, in post-rescule, by default, if you don't

20:13.160 --> 20:18.760
set a value into the current, it's going to be the current, it's going to be new. So, you need to

20:20.040 --> 20:27.960
switch a little bit, your mindset about that. So, everything is not about the post-rescule

20:27.960 --> 20:34.680
internal functions. Everything is not radial, available right now. So, you still need to be

20:35.080 --> 20:45.560
to do some digging and some work around to get what you want. And also, it's kind of

20:46.920 --> 20:52.760
complicated at first, because you have two basically, you have two memory contexts. You have

20:52.760 --> 20:57.720
post-rescule memory context and the extension memory context. And if you want to pass a

20:57.720 --> 21:04.360
object from one to another, it's not that simple. And it's not magic. If you want to work,

21:06.600 --> 21:11.720
it's not entirely slept. So, you still need to do some unsafe section in your code.

21:13.160 --> 21:18.440
The building process is really, really slow. So, that's a major problem for us,

21:19.320 --> 21:26.840
because it took basically, you can see the extension took like one minute to compile in CI.

21:28.920 --> 21:37.320
And currently in CI, it will take like 20 minutes to compile. So, yes, and you need to compile

21:37.320 --> 21:44.200
it upon the five current puzzle versions. So, that's five multiplied by 20.

21:45.160 --> 21:54.040
You also have no support of Windows at the moment. So, I don't know, it's a problem, but maybe

21:54.040 --> 22:03.000
for some problem, for some people. And for advanced visual, you still need to read and understand

22:03.000 --> 22:12.840
the puzzle sheet. And so, yeah, just come on and try by yourself. Actually, it's really,

22:13.000 --> 22:20.120
is it to bring back your code close to the data, always a good idea, if you ask me.

22:23.160 --> 22:33.400
As we said, one of the easiest way to start is to create data types that are relevant for your

22:33.480 --> 22:44.200
use case, and use them to, for indexation, for control, for data quality. And just think of

22:44.200 --> 22:52.840
both ways as a platform and not just the database. And yes, overall, if you want to have a first step

22:52.840 --> 22:59.320
in the process of community, actually, rest extension, I will give it a great entry point to start.

23:03.480 --> 23:11.400
And that's it for me. So, if you want to learn more about the PGRX framework,

23:11.400 --> 23:16.200
the website is there. I also did a four hour tutorial with many, many, many, many, many more

23:18.440 --> 23:22.760
examples, which is available. And if you want to try the post-rescue and the

23:22.760 --> 23:36.840
laser extension, it's also on GitHub. Thanks a lot.

23:36.840 --> 23:46.680
Any questions?

23:47.560 --> 23:53.160
Thank you. How do you distribute the extensions?

23:53.160 --> 23:55.160
I did not understand.

23:55.160 --> 23:57.160
How do you distribute the extension?

23:57.160 --> 24:06.280
Ah, it's a really, really good question. So, you have in charge of cargo PGRX, you have

24:06.360 --> 24:23.000
a release option that will build up all the folder for building packages. So, again, I would

24:23.000 --> 24:28.440
don't advise to people who want to use cargo to install this in production. Of course,

24:28.920 --> 24:38.200
so you need to build RPM and DBM packages. The problem now is that the official

24:39.320 --> 24:48.600
post-rescue repository will not build this RPM for you. So, I won't build

24:48.600 --> 24:55.480
rest project and go project, actually, or two. So, right now, the extension is not

24:56.040 --> 25:02.200
available on the PGRG, on the official post-rescue delivery repositories. This is the

25:03.320 --> 25:08.280
situation right now. It's made change. But, like I said, with a building time,

25:13.640 --> 25:19.640
the CPU time it takes to build all these versions. It's not a simple issue for them.

25:25.960 --> 25:33.640
Currently, Postgres has role-level security and I was wondering, will this extension mechanism

25:33.640 --> 25:38.600
can you make that even finer and build field-level security by implementing your rest

25:38.600 --> 25:42.040
extension? Sorry, I didn't know that, then again.

25:42.280 --> 25:55.960
Currently, Postgres has role-level security implemented in it. Not perfect, but it's there.

25:55.960 --> 26:03.080
I was wondering, if you can write the rest extension to make that even finer and do it at

26:03.160 --> 26:14.680
field-level, would that be fixed by my extension against the data, like having fixture

26:14.680 --> 26:23.800
already loaded in the database, how can I tell it? For example, if I want to test my extension

26:23.800 --> 26:31.480
against the full table like with thousands of lines, yes, I want to write, you just create this