Postgres on the wire

Video in TIB AV-Portal: Postgres on the wire

Formal Metadata

Postgres on the wire
A look at the PostgreSQL wire protocol
Title of Series
Number of Parts
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
While it usually Just Works, sometimes it's useful to know exactly what happens between hitting return in psql and seeing results appear on the screen. This talk will explain how the PostgreSQL frontend/backend protocol works. We will look at the low-level blocks from which the protocol is built and try to give the audience a solid understanding of how bytes are transferred back and forth between their applications and the Postgres server. Even if you never directly deal with the PostgreSQL protocol, every application you're writing or maintaining uses it and it's still useful to know the basics of how it works. Sometimes, knowing the fundamentals can also help you understand some behaviour that otherwise would seems mysterious or quirky. The talk is aimed at users, DBAs and system administrators interested in learning a bit about how the sausage gets sent. We'll go through: protocol versions and how they differ differences between simple and extended protocol text and binary query formats authentication and encryption asynchronous features: async queries and NOTICE the COPY protocol query cancellation and how does it work future development of the protocol
Source code Slide rule Slide rule Whiteboard Source code Coma Berenices Online help Grand Unified Theory Communications protocol Logic gate Local ring
Parsing Structural load Length View (database) Multiplication sign WebDAV Execution unit File format Database Client (computing) Mereology Computer font Computer programming Variable (mathematics) Coefficient of determination Computer configuration Different (Kate Ryan album) Query language Process (computing) Series (mathematics) Exception handling Physical system Computer font Email Closed set Fitness function Electronic mailing list Sound effect Mass Bit Instance (computer science) Price index Sequence Connected space Type theory Message passing Process (computing) Computer configuration Cycle (graph theory) Data structure Resultant Frame problem Slide rule Server (computing) Socket-Schnittstelle Regulärer Ausdruck <Textverarbeitung> Identifiability Authentication Electronic mailing list Bit Field (computer science) Connectionism Number Revision control Regular graph Internetworking Operator (mathematics) Energy level Computer worm Integer Communications protocol Message passing Summierbarkeit Traffic reporting ASCII Authentication Default (computer science) Dependent and independent variables Dataflow Key (cryptography) Server (computing) Interface (computing) Length Client (computing) Field (computer science) Database Limit (category theory) Frame problem Software Basis <Mathematik> Query language Revision control Statement (computer science) Key (cryptography) Exception handling Communications protocol Computer worm Extension (kinesiology)
Server (computing) INTEGRAL Length Authentication 1 (number) Password Client (computing) Number String (computer science) Hash function Message passing Data type Authentication Server (computing) Interface (computing) Cellular automaton Mass Database Price index Instance (computer science) Library catalog Flow separation Symbol table Frame problem Connected space Type theory Arithmetic mean Process (computing) Hash function Basis <Mathematik> Multi-agent system Password Configuration space Right angle Encryption Quicksort Window Local ring
Building State of matter Multiplication sign Decision theory Parameter (computer programming) Client (computing) Special unitary group Storage area network MKS system of units Computer configuration Single-precision floating-point format Error message Physical system Moment (mathematics) Parameter (computer programming) Mass Instance (computer science) Connected space Category of being Message passing Telecommunication Right angle Encryption Escape character Information security Asynchronous Transfer Mode Ocean current Stapeldatei Server (computing) Heat transfer Code Field (computer science) Revision control String (computer science) Infinite impulse response Codierung <Programmierung> Communications protocol Message passing Form (programming) Addition Standard deviation Metre Dataflow Server (computing) Cellular automaton Planning Client (computing) Error message Basis <Mathematik> Query language Revision control Speech synthesis Negative number Communications protocol
Standard deviation State of matter Code Source code Client (computing) Mereology Special unitary group Mathematics Computer configuration Hash function Core dump Query language Process (computing) Information Error message Library (computing) Source code Computer file Binary code Interior (topology) Electronic mailing list Mass Median Principal ideal domain Price index Instance (computer science) Flow separation Open set Connected space Position operator Message passing Process (computing) Order (biology) Normal (geometry) Right angle Video game console Quicksort Data structure Server (computing) Socket-Schnittstelle Dependent and independent variables Line (geometry) Authentication Motion capture Letterpress printing Electronic mailing list Streaming media Bit Theory Field (computer science) Revision control String (computer science) Data structure Communications protocol Message passing Fingerprint Module (mathematics) Authentication Execution unit Dependent and independent variables Dataflow Key (cryptography) Coalition Server (computing) Cellular automaton Code Planning Client (computing) Field (computer science) Core dump Line (geometry) Binary file Cryptography Cartesian coordinate system Frame problem Explosion Error message Query language String (computer science) Revision control Video game Key (cryptography) Electronic visual display Euler method Communications protocol Local ring Library (computing)
Presentation of a group Mountain pass Source code Time zone File format Cloud computing Client (computing) Open set Computer configuration Set (mathematics) Row (database) Office suite Error message Exception handling Web page Binary code Interior (topology) Parameter (computer programming) Database transaction Bit Instance (computer science) Message passing Process (computing) Oval Order (biology) Personal area network Representation (politics) Simulation Point (geometry) Device driver Average Number Product (business) Representation (politics) Data structure Communications protocol Data type Pairwise comparison Key (cryptography) Information Server (computing) Length Code Core dump Client (computing) Binary file Cursor (computers) System call Explosion Software Query language String (computer science) Function (mathematics) Cube Spherical cap Revision control Interpreter (computing) Table (information) Integer Length Multiplication sign Mathematical singularity Decimal Set (mathematics) Binary code Parameter (computer programming) Database transaction Timestamp Hash function Query language Information File format Computer file Point (geometry) Measurement Connected space Type theory Computer configuration Interface (computing) Right angle Authorization Video game console Data structure Resultant Frame problem Server (computing) Codierung <Programmierung> Real number Protein Field (computer science) String (computer science) Integer Message passing Proxy server Database Basis <Mathematik> Key (cryptography) Electronic visual display Communications protocol Computer worm
Server (computing) Design by contract Cursor (computers) Database transaction Twitter Personal digital assistant Query language Process (computing) Communications protocol Extension (kinesiology) Message passing Error message Descriptive statistics Metropolitan area network Dependent and independent variables Server (computing) Client (computing) Binary file CAN bus Message passing Process (computing) Error message Query language String (computer science) Pattern language Simulation Communications protocol
Discrete group Parsing Copula (linguistics) Source code File format Client (computing) Special unitary group Atomic number Forest Single-precision floating-point format Personal digital assistant Row (database) Local ring Error message Descriptive statistics Physical system Computer font Arm Keyboard shortcut Binary code Sampling (statistics) Parameter (computer programming) Maxima and minima Bit Instance (computer science) Complete metric space Message passing Process (computing) Series (mathematics) Order (biology) Personal area network Escape character Directed graph Slide rule Dependent and independent variables Device driver Online help Rule of inference Number Product (business) Goodness of fit Energy level Communications protocol Form (programming) Data type Default (computer science) Beer stein Standard deviation Metre Dataflow Server (computing) Weight Heat transfer Login Client (computing) Line (geometry) Binary file Limit (category theory) Error message Software Query language Personal digital assistant String (computer science) Function (mathematics) Network topology Statement (computer science) Free variables and bound variables Abfrageverarbeitung Table (information) Family Library (computing) Digital electronics State of matter Multiplication sign Strut 1 (number) Set (mathematics) Complete metric space Parameter (computer programming) Function (mathematics) Mereology Database transaction Variance Heegaard splitting Mathematics Synchronization Military operation Query language Series (mathematics) Data conversion Injektivität Parsing File format Ext functor Flow separation Measurement Parsing Connected space Type theory output Conformal map Resultant Asynchronous Transfer Mode Row (database) Reading (process) Asynchronous Transfer Mode Server (computing) Interpolation Functional (mathematics) Game controller Identifiability Limit (category theory) Heat transfer Mortality rate Field (computer science) Emulation Power (physics) Revision control Causality String (computer science) Statement (computer science) Gamma function Message passing Summierbarkeit Multiplication Dependent and independent variables Forcing (mathematics) Planning CAN bus Number Synchronization Object (grammar) Communications protocol Extension (kinesiology)
Server (computing) State of matter Line (geometry) Direction (geometry) Multiplication sign Authentication Electronic mailing list Heat transfer Streaming media Variance Number Revision control Mechanism design Different (Kate Ryan album) Pressure volume diagram Query language Row (database) Communications protocol Extension (kinesiology) Data compression Authentication Email Dependent and independent variables Cellular automaton Projective plane Counting Coma Berenices Database transaction Price index Instance (computer science) Connected space Message passing Process (computing) Query language Auditory masking Buffer solution Revision control Different (Kate Ryan album) Summierbarkeit Video game console Communications protocol Sinc function
so I'm going to hit the lights that and so on soul height minds young I work at the small company called that sport and I'll be talking locals just on the wire but for those of you rewatching that there is just 1 of all the slides the slides very than anything at the source the slides from the if you wanna help fix them up and customers around so posed rests on the wire
you just this 1 yet you have
what is the of these is all of the things that you know so poses the
wire and I actually not talk about the wire so this is the
outline of the talk as it is so 1st and talk about a bit about how the poses protocol is fractured and what that the basic concepts and then I'm gonna go into how the messages are sent back and forth the types of messages with the framing of the effect of sending queries because 1 of the more useful things you can do with the poses probable is sent queries to the database and get results and then I'm going to try to go over some lesser-known features or or some things are outside of the typical query response cycle so free-form out 1st of all it's not the program versions there are several versions of the full just political and you might see on some slides i'm gonna call it the being which this for short for front and back and so sometimes people say the view and they mean the poses protocol it's it's it's at the end you might and to so the version 2 0 good introduced in 1999 if I'm having mistaken might get longer queries and actually the before that only added probable versioning and version 3 0 which is the current version got added in 2003 so I'm going to focus on 3 0 because it's been around a while but in some places and on trying to find the differences between 2 0 3 0 goes to 0 still used from time to time and I'm sure some of you who levels of an interface systems that still have to seek to 0 and the fun fact is that the server still speaks 1 0 so if you factor clients to to negotiate use probable version 1 0 it's the little work so talk about backwards complex and 3 0 0 was added some new features which makes it worth while on the exam could a probable which of them go into detail in the query part of the talk and call better and in general it just feels more much more structured organized it's much more can confused like most undirected someone thought everything through and just make everything work Morrison are so 1st of all the probable starts with a client connecting to postmaster with the main political process but are as soon as the postmaster receives a connectionist forks off a process to handle the probable so the actual probable fasting and everything that has to do with the protocol happens in a separate but this means that if anything goes already with the protocol you want must you across the market which is about this so it's very interesting because of the postmaster does not touch whites that come from that are the other the other thing that's that this means is that indication is that after the processes for so this means you without any authentication just look report you can force the postmaster to for which is well it's it's kind of an expensive operation but then again you're not supposed to have your post must support open to the wider Internet and that's probably find and this also means that the that the protocol and it goes over 1 to collection and it's intimately connected with the bike and that's that you're talking to so connection and back in the kind of the same thing in what if 1 goes away government was way to close the connection the process that's handling it will die but it might 1st have to notice that the connection between close which means that just closing the connection will not necessarily immediately terminate the query as being run this means that for instance if you run something and that kind of locked up everything in this using all the resources just killing the client will not necessarily stop the query from being executed so well not the frame from this is that the very basic things almost all frames are look like that so they have 1 character ASCII identifier which identifies which kind of frame is that then they have a 32 bit length and then the paper and the payload is different depending on what what frame we talking about sometimes there's no plain old and but but basically it is was vessel parsing is relatively simple you look at the 1st bite you say 0 this is a message for then you get the length and then you know how much how many bytes you need to read and then you know Hollinger but then because you know the frame but not the exception to this and that the so called start up but it starts with the length followed by the probable versions of 32 bit integer and then the baby so the start of pockets of kind of those doesn't have a tight because the very 1st piece of data that the postmaster receives is treated to be is interpreted to be the start of it so you know it's the start of what this is the 1st thing you got a connection so let's let's see how the start of but it looks like you have your mind you have your portal version and then in version 3 . 0 you have a series of name-value parts of the so you have a name the name of the value of the light than the value and the like underlying the name little value milk and it goes on that until it said it reaches the final note and of course the length needs to add up to 2 of the length header set on their improbable to don't tell it to look different but start a participatory at all is fixed size there's that there's a fixed number of bytes so you can't really fits well you can fit only a certain number of options in the uh in 3 . 0 there's no real limit the scope of what can be as big as as you want and then I will the key values like value some of those our are special importance like user or database like 1 of those fields is you learn that the back and we know that the process handling the collection will know which database you're trying to connect you need to be connected as they use it you can have in and in this connection the start of packet needs to specify a user if not the connection the close of the database is not really what you have to have a database but since it defaults to the user you just have to send the user name you don't really have to use you really have to send the database name and there's a number of special keys that are interpreted especially like options which are actually in the options field you can send the command line switches for low back and process about that so I'm pretty gonna go into the edges that some keys are a bit special and the rest are just gets so any got that you can set you can send it in the stock market and then the collection use those dogs when when you actually go get high and network that work or so so a starter pocket and then this is a regular that data but I guess that's totally identified just this 1 this always 7 that green it between this is asking to fire and then there's a pair what follows is lists for instance the accounts the query thus larger than 1 gigabyte in 1 go so if you have an instance statement of the Brazilian values you countries and more than 1 gigabyte if not for other reasons like society then because it just won't fit in a query but it and then the payload how you interpret it depends on the type so how does the start-up sequence work like from the initial TCP connections or of the initial units going so that connection because as you know the probable works over TCP IP or over units going sockets exactly the same to this
when you connect you send the start pocket and anyway the and can you for authentication or don't you your your free to enter this depends on the PGA deviate configuration so if the back and decides that this connection is good because for instance you configure trust for for this has long or you just trust for your entire database or what not that the the symbols in the frame of the of the type of indication OK and then you know you to if not it will send an authentication request and they're actually several types of local indication requests and it depends on the PGA integration so that the back and also for this user and this database and this net loss and so I need this kind of authentication and his ask you so yeah often data and there are several kinds like it said the most common yes most commonly used ones are plain text 5 password wouldn't go into detail about how under-five passwords gets sent it's rather interesting it's kind interesting but the thing is that it's up to the server to require a player trip or in the fight so this means that if you do the SQL your server then the server might decide to take give me a plaintext possible and if you wanted to give you any it it will know that the the process will be sent in plaintext pituitary no from the interface for so theoretically and evil server I'm in the middle attack could swap the the frame and then kind you would unwittingly send the plaintext password out to that the service would be the like guess it's a it's a fairly contributed complicated the scenario but you might reasonably thing that the SQL could tell you OK the faster you then enter is going be sent and make plain text and notes of passion over and there are different there are more advanced kinds of also the server can ask for for users API indication more SSP I all other windows things that don't really know about but
75 of not just because this is the most used or or common way of of and they get into the password that authentication requests for under-five starts with identified that says this is an all request for the flight number length uh than when she yeah it's it's it's said this is an not question the length of and that tight meaning this is andy 503 West and then the salt and the clients what it does is it crashes so the password and the user and then for the hashes the hash and the salt propensity string sense that and this is how this and this is what the server receives and this is how it decides whether you're indicator and so why stop right the soul is there to prevent replay attacks right because without salt if you if you'd just send the fashion of your of your password and then an eavesdropper the connection could would be able to reuse the same pocket 2 remote as you the next line if in in this scenario where he won't be able to make their own reuse the same cash to log in as you because you would need to have the sort and why the double hashing right why is doing rehashing here and then another partial there and this is because the server can only store this hatch this means that you don't need to keep the plaintext passwords on the server so that if someone steals your database and what 1 gets access to the catalogs that contain the passwords they will they will only get the fashion of the plaintext although if you think about it the fastest is as good as a possible as a means to enter the cell to connect to the to the server right so someone steal your hashes they can impersonate you I can connect as you but and if they don't have the actual string that you reuse millions the and I would say thank you for having so if you think about the world but in the end the new year and then the right to another on the
right and on and on and on and on and on 1 yeah right but it is recording right there was just 1 of the working in the ICT vendors who watch has been used for this kind of thing and uncertainty in I also was that parameter status so that's an interesting package it gets sent by the server and it informs the client about some parameters that the Slavic beings important to transmit to the client and so it is a place in the packages main idea and then the parameters that gets sent our for instance servers right so the client knows what the exact version of the service talking to some are important to be sent immediately and connections like planes calling because claimed encoding is critical to be able to escape text strings correctly because of because you can kind of lineup multiplied characters to eat the quote that's that's closing up that had codes things values so if you're confused about plane and building you might not do escape correctly and this is why the the PQ or or other client diverse require as a reference to the connection to the state because they need the value that that sense on on standard and some according to the kind I like dates styles of the claims to know if the dates that are coming in textual form IIR as long as the year or the multiple what are the important thing in the in the curious thing is that every time any of those parameters gets changed the parameter status message just push down the connection to the client so this means if Europe connect it will we can try to see about this happening later on when we will see the actual problem running up when you change any of those on the next occasion when the server has something to say the it will also include a parameter status message passing big style can be changed cluster right and then if you reload your server it will change for the current session right so you need to you need to have the client node so this is this is how the initial exchange works from the start up pack at that in all the requests or in the frication message from the server and the client option they can send us a message if it didn't see enough OK then a bunch of parameters status then something called back key data and to it in a minute and then add a message that's called ready for query which means of identically processing mode now you know we're we're done with the initial Court let's get down to business so far so this is a this is like the basic things and there is there are a few more things that can happen in initial connection set up like a system but if you think about it it's really it's rather weird that opposes has a lot well if you consider it with SSL support listen on SSL it will also uh except plaintiffs connection the same port so there has to be a moment where you where you choose we're we're speaking plaintext or no we're switching to SSL introverted communication and this is done with that dummy start a pocket so he sent a start with the special protocol called the if you if you compare the start up
bucket has a probe abortion is the 2nd field right at the SSL negotiations back and looks like a star fuckin but they have their probable field is repurposed to to be the the negotiations was actually uh if you're PS client sends 3 0 if you're negotiating as sends a probable version 1 2 3 4 6 7 9 1 2 3 4 6 7 8 so something that you'd never see as as probable version it's repurposed to signify to mean I want to do system and then the server writes back a single byte that's as yes will let the lesser so I'm right down for it or no I'm not doing as a cell or you can get an error message if the server is so long that it didn't even include SSL in the in compiled and it doesn't mean it hasn't been linked with a so it means that it never even knew about this is so because it's predates the addition of the possibility of doing that and then you just get there saying that's prodigal version I can speak because it will interpret the the starter pocket as a as a as a regular start upon and the client and decide if the service says no I won't do it so really can say why don't to speak to you if you're not doing as a cell or you can continue and then there are options that the client can provide for them so that this is a place for us is 1 the client can choose whether to continue talking with the server that force wrong reason or another refuses to cell or just so this is somewhat like this you can in some negotiation pocket then you get the single byte which is a yes so supported not supported or an error message if you get supported you can go to that as is a actually and here opens the subjects and you get all the set out to see the connection gets as itself and then you transmit your regular transfer those starter pockets of this I'm interested so from from down here everything is central by 2 it's it's kind of a simple way to to have as a and not and not necessarily the same port are right now there is
another kind of sort of pocket that's called the Council requests are what happens is that there is yet another fake probable version that means that I wanna Council my running queries and this means that in order to counsel we use you actually have to open another connection to the postmaster so if a client and it has and the median is being processed in order to counsel you can just send another connection on the same TCP stream if the open another connection and send a separate counsel request and then hopefully the query will will terminate will get back an without having to wait for it to complete although we don't really have any of you already have any guarantees you can just you open a connection you write the bytes you go and then you you wait what happens of course you can always just close the connection and and that's also an option of so this will say that you send out the answer requests which has this dummy Council this bound approval version that's actually Council gold it says which PID that process uh which is that what was the PAT the process of running the query you want the Council which supposedly easier is the beauty of your collection process and the secret key uh which prevents other people for counseling your queries and the secret key is actually transferred in this back and key data out message right so this is how the client gets a secret key for their connection to counsel their own queries now this theoretically and have been discussed amended this is open with me about so if someone can eavesdrop on your consolation packets they will be able to counsel awful awful fall further queries you got you beyond this connection until the next election what where the key goes generate again but in theory it of repair parts are possible in theory you could send the Council requests only SSL connections to you could do with this is that negotiators cell and then instead of this son of pocket sent consolation and then your your secret code gets transmitted itself but for instance we PQ doesn't do that if you if you use the this the efficiency library for POS resident just send it and think that's regardless of if the connection is a or not so most applications will send the constantly in plane which again it's probably not a big deal but those that that's something to me a lot the error status in general colleges are among our notified or our signal with an a response message and that's a list of key value but again as in version 2 0 it is just a string and then you had to positive or a design and processing in 3 0 8 is it has structure so there's fields for decay hands the actual message the severity you get all those uh and the client and can display and process them in a more structured way you actually also get the source file and the line in the source find where they're originated which means that you can Ferdinand fingerprints holders versions without even being authenticated because if you just send garbage it will tell you all this garbage yielding an error message saying that garbage with you know what the source file and the source line where the message that generated so you know the exact version fulfills just before between releases that the code gets shifted ever-so-slightly so you can you can usually you know which version there talking to just when hear the error message and then there's a Metasploit module that that just doesn't it's that allows the fingerprint server without having of indications on it so that's and the errors the Euler method did use for any kind of the of authentication narrower query Aaron so tools what the used helpful the look of the protocol so tcpdump or or shot work in this dump the traffic on the interface and at the bytes Wireshark has has support for for departing a protocol so you can just fire up where shot and then you can click and tell you this kind of message and show all the fields that only 4 3 0 4 2 0 and this might my version of my copy of why shouldn't work and there is a tool that I found when I when I was preparing will PG shocked at Sapporo tool that's the that does probable passing and some other nice things about out and then just just let you know OK as think the tool is is not widely known and it's useful it's nice it's has several binaries that to do things with with the hope captures the either captured life political messages and display it link that the body of the of the past frames it can write on the summary from capture fine like you've run this many select state so you had this in these this connections so if you don't really have access you can always go to the longer can't change loading you can just dump the protocol and then use the shot to John lies at end does support for a coalition to which the norm of the other tools that do so we can
try to take a look at you don't wanna start jumping traffic from my my local instances of run I willing to round but just prints of the protocol right and then connect to my local instance that notice that I'm using the TCP explicitly because otherwise it would go over doing you know when sockets and I wouldn't see them on the in in the park and and any necessary because otherwise I won't be able to so I will connect and stuff
appears here right so as you
can see if there's a bunch of
parameters status messages like server encoding UTF 8 server measure 9 1 and so on and so on
and so on and then it ends with back and key data with the PAT of my connection and the secret key and then it says ready for query type II I I don't know where to look good doing great and Tyson and play with more we might just
revisit this later clearing
are there 2 protocols for for actually review database but before we go into them but let's take your binary vs Text Data the data pulled a sentence can be either text or binary the every time has the text and the binary representation for an integer it can be can represent as such like the decimal notation of integer or as network order integer same for floats for for everything every type has the both from to shift can be represented both as as something that's text and that's something that's binary mn this for instance becomes a problem for time stance because sounds can be i . 64 bit aunts or floating point numbers there also 64 bit long but the interpretation is different and this is why in parameter status the the server tells you what option it has been compiled with right because if you have a working fast 64 bit type you want they times to be by the way if not you prefer the to results of each query the client can choose they want the server to give a text or binary data and the actual except for what text formats rather simple because this is what you know what you want to see the Consol this it's going to be I can found a source for the documents the binary format except for the actual called that that generates and so I guess it's a way of documenting things you have your sleep proteins that have read but there's no I haven't found any real reference for driver office for instance you know what the wonders presentation of some of the more astute on so stood for the simple query protocol it's simple it were saying that the client sends a command the server replies with with a message that says this is the structure that I'm innocent you then it sends a bunch of data messages of that structure we then assess sense quite complete which means your command has has been executed and ready for query which means I'm back at at the start of the processing you can send more queries and underweight so clear query is essentially it's a it's a strange to just fight off road destruction is a bit more complex because it has a number it's variable length depending on the number of columns that you will be getting a for each column you get the name of the column for instance so this is in the you could not fly because you know the query you know the column names but some of the comments are generated and for this reason or another the server will tell you the names of the columns that that will be sent it will also tell you a mansion information about in the table the data is coming from the and what's the what's the actual column of the table and then he would tell you the type of the thing is gonna sound so to the whole idea of the type which means you need to be there no the way these of query them beforehand if you wanna match them away these 2 to actual payloads by need to know that text is 32 is a 32 here it means is that the text and then the larynx and then modified to make call 16 will have a modified that says something approximately 60 and the last 2 byte here what 16 bit integer is the format which is 0 for attacks 1 for binary this means that in the URI every column can be either text or binary if you inferior could mix and match but in a simple protocol it's always text except if you user declare cursor binary that is 5 that is always in a single product and then data role which is the actual data is coming back about down it's just how many fields and then field data cube length they later just that current completely is just a string tag which is what using T-SQL when you execute something that says inside you want hours so that 8 thousand of Macbeth and ready for querying is is the the thing fire the end of the transaction status so it can be the I will introduction or errors and where is this useful but it is useful for instance for as well because if you're still transaction you might be a school might change the prompt right nose because the server tells that you're still in the transaction disconnection still in open introduction but it's actually very useful for tools like PG bouncer that look at the protocol that the proxy the protocol between the client and the server and they need to detect whether the transaction is already close the because if it's close depending settings it might want to reuse that connection for another point right so it makes it easy for me to to know if the particular connection has finished its transaction processing and can report was for different transaction this only happens in 3 . into it all the way from the message does not carry the transaction status and you know there are some string comparisons to kind of know whether the the weather's was transaction status and there's this nice
comment in in the in the C which says of my watching for managing the possible job of tracking the status but this does not work at all on 7 3 services or a committee of mammals the feature every mistake caveat so have 4 to the 0 you give you can kind of fake it but it's a it's a 3 0 feature that they got now ruled that some of the
quirks of features the probable is that you can send and 1 prehistory you can send that 1 Seneca select you sign the contract 3 Senegal and send them off as 1 query message and the simple reply with 1 common complete query so if you don't want common complete and then an error message you need to figure out also my 2nd query failed out there is that if you send an empty query you get a special kind of response that I'm the why but this there's is a special concept of and and the response message so if a just and then this trend you'll get a special kind of patterns and and so spectators is set up so the simple protocol is simple but it's a query you have description of their own you know they're also going complete pray from we as a simple this is the simple we approach onto the extent protocols are the
extended protocol for from being much more complicated doesn't change some what say fundamental things 1st of all these institutions split 2 stages it's not like a 7 1 3 and you get the results of there stages in which you build up the whole thing out every step gets a confirmation letters from the server so we do step was OK that objects of your OK but you can send all in 1 without splitting it into separate TCP packets of that weighting can just send all summer long and then the central confirm it all back so you don't have to do a which would be disastrous for performance you can just send all the steps and then get will see in the in the example of how this really works so of it allows the separating the parameters from the actual reason I have to send a string you constant parameters so why is that because typically queries like that in the simple protocol end up looking like that which is not something you'd like to see in your server it also this allows sending several queries can send set once summer constant some economies why because the future of sending multiple queries by pair a message is usually used to transform quiz like that integrates like that so we don't want that and in the extended protocol they would look like and that's this would be this would be safe because you you have to do escaping interpolation you just have a place so placeholders that the extended protocol starts in the past message you 1st after the server to parse your query texts and preparing the it's institution for you to use in the past Message it has a name it has the the string query but it can contain parameters and then you say for each parameter you can tell the server what's playing with the why is this important why would you need to tell the server will type the parameter will be that since you'll see that in this the next message you will specified again because if you have a function like and 1 version takes an and the other version takes the text and you send the query that's fooled dollar 1 the federal we need to know which food you mean so this is where you can design the weights by even at the parsing stage tell the server OK this this 1st column there will be an and you can give deny name so we can use it later if you don't give a name which means sending an empty string force they complain this is the so-called unnamed statement which is this is the default for the rest of the messages if you never use the name is notes and I always use the last name statement is be automatically fleeing when you create another and state if you create a named statement from parsed will keep a reference to it so can as long as the connection is gonna be there and you will be able to reuse it in in in future messages the next step and this is the complicated stuff is buying which is where you take what parts you back and you why should you you take what you since the past the statement when you refer to that state finding any bind it to an output portal is called portal is actually the the identifier of the channel you will be fetching data from or or executing all so it's it's it's fairly complex ideas have done you refer to the paths taken by name use to strengthen the young and statement you give the alkyl portal and name or use an unknown on an unnamed portal and then for each parameter you have whether text are binary then for each parameter you have the the actual data of the parameter and then for each result columns you you tell the server I want this column in this form and that's it this is my one's mind completes you have the the query you have the the ultimate portal ready and even start executing on it but not so fast there's a message called the strike that can use to describe the the format of what will be executed so if you deposit mind you're not really sure which functional chosen was the type of the columns so clients can also send a special message that will that will uh the several for this past statement that I sent to you family once the of what was wasn't a given and that tells you actually for paras is gonna give you want 1 of the parameters is then expected and was the result of that is going to come back and this is useful and drive is actually do that to in in order to that and you get the data that is going to be coming down the wire so you've you've parse the statement
you bounded to a to a name that will be your output portal it's and executed and is a is very simple you just tell it execute this alpha particle anyone at most this many rows now the the part where it says no limit is the is the complex part because if you say I won't tendrils and the portal with the query that you executing actually refers when euros you will get back a message called the atomic slide you'll get it back a special message that says I'm not done you are otherwise you just get what you see is a literal messages and then an hour and then either that message that says I'm not done yet or if you're done then the measures show that says I've done as you can see no role destruction is sent which means that when you do as a cue to you need to know what data is coming down because you're just going invites you to be able to interpret them and that's why the drivers usually do I described before so yeah in the portal has been run-to-completion which means you ask for all the rows for instance you get a common complete message but you don't get you don't get prolifically because you haven't still closed your extended probable session where you get complete if not you portal suspended message thing is a the q doesn't have called to to to handle that because the Q and functions you can find the you always run the portal to completion I've tried other drivers to see that they actually were aware of that of portal that can get suspended and on the JDBC this from the 4 from the driver tried I tried Python driver cell-cycle PG only uses a single query protocol that title just you the Python 3 2 0 0 driver asks for the maximum number of roles instead of asking for all the rules which would be sending 0 0 limit it sends out that the maximum number that can fit and 16 bytes in 32 bytes which that make sense but they do this so the driver of sometimes them to be getting things right and it's something useful to look at the actual messages that the generate in case you were wondering what's what's going on and yet in Indiana circuit message you there's a 32 bit when it is I want this manuals if you say 0 it's going around to completion north and you all the roads but some diverse as a as I said just stand up to the power of 32 minus 1 hour and my guess and they don't handle suspension so if you have a table this and it will generate too much help with the discretion of the probable cause of new ideas are and then the last message is saying so what happens is that you can sample that involved and only a furious and sync with the server source and the new stuff back this means you can do positive mind execute execute by a execute past and then and then the server will send all the conformations and all the data but this is actually useful to sync up areas because if if you're an extended problem with extended we probably you a bunch of stuff and ideas and sink and then you're 3rd statement in the series fails like with a foreign key violations the server will know that it should give you an error message for for that row and then ignore everything until you're saying because you weren't aware that you bombed out there there was never there and so it it come into 1 tree process over the other messages because it has a about but it will sink up with the client on same uh and also meaning and means of reducing of traffic as it can send out a bunch of stuff and only after sensing will you get stuff on the network so that's a preverbal were saying that you get your queries parse the forest stage you get them planned and buying state and then they get things very executed and execute and if you have statement loading on you might use cryptic lines say this these this many milliseconds pass this MS mind this actually corresponds to pass I'm plant time and actually kitchen so to sum up the extended protocol horse like that your client you send passer-by describe executes saying and you can all do this in 1 the the the fact that the you so so this is 1 bunch of data send and then get back parser OK yeah buying OK OK description for describing a bunch of minerals from execute command complete and ready for querying the sensing now as so you could and if you set if you send if you would like to reuse that the plan and not incur parsing over has full of but the same query like if your your had a simple set next start from postwar ID equals 0 or 1 and you you want to retain the parts of the that path statement and not be possible for every connection you can do that in the past St. immediately it will tell you all this past years that means that and I'm very for formal and then you can do a bunch of binary in line indicates however many want sink up and only then get the get the response so this is an and again I only I think the only JDBC gets you the level of detail the level control in order to to have an execution like that most other drivers are evenly PQ if you use PQ functions you can't really have this this conversation the server little inject sinks between the mind the execute because it will sink up on every execution you country do this with with with the C library which is an annoying with about but I'm not good time so i'm gonna arms and they just go through copy refer escape the the lesson features and then the last so copy is a special state of the protocol uh some commands like copy from somewhere in the cupboard to some the input put the connection special motorcycle copying in copying around that the expected messages are a bit different so what happens is that you send a copy from standard in and their service says OK I'm ready to start copy and it was the connection and copy mode so we get a copy in response that says this is the number of fields and these are the formats I expect for them and then you start sending copy data copied data is just the lot it just it just data there's no no there's nothing is just data because a designed for fast both transfer so the client sends all its corporate data that at once and then it says that's it sends a special message it says copy done which means go out of the Cauchy processing mode where we're back to normal regular including mode and then the back and says OK for its has announced that was never going to copy it at the front and that started I mean because the front and this receiving the data you come restore the surrealist will be pushing data to until you disconnect or counseling we you don't have to copy fail copy stall please don't send more data you in just keep pushing it down the down the line so what happened is that use a normal creatinine extended query for a simple greedy and the responses OK now in copy processing but then you send all your data and then you say OK I'm done and the service as your command your copula has completed and I'm grateful to the system the probable inside the product are there just skip over those because they're really
weird things and since we're running out of time so let's go directly to this the 3 0 protocol as you've seen has been working from 6 3 5 or 6 for for but over and there's been surprisingly few gripes about how bad is it because it's pretty good get our even that there hasn't really need a lot of pushed to be extend it to change it you know to introduce new version I tried to gather some of the things and floated mailing this uh to kind of give you grams of where it might go if the project decides to introduce a new protocol version of about our so compression people were asking to be able to do compression in the protocol itself uh if you're doing what about transfer it could be very beneficial to be able to compress traffic and have the the probable understand you're sending Jesus bytes for instance or whatever but some people argue that you can just use open as a cell with the null safer and compressed and the discussion the just ended the that's not to be interesting the interesting thing is multistage of indication if you recall the whole process there is no provision for choosing between different authentication mechanism the server tells you give me a number in the 5 points and if you'd rather use GSS API you cut is the sum of the drugs and the server you can say 1 sorry counts and the server will go and see so I have this other methods for you you don't get any negotiations we just get a challenge if you don't respond to your new directions so this is often if people want to to have a back to different of methods a way to actually negotiate the prodigal version was right out some 3 negotiation you connect to the server you send the probable version and the server can say I'm not talking with the employer can say yeah go ahead and I can understand everything that you will say there is no provision for negotiating the extensions or it's it's very simple some people express their needs to have inbound query consolation which means you can't you don't need to open our connection to the server and send them the Council request you can just send it in stream which is problematic because if you're back this processing data it will not listen for a method they will be less than in the current buffer of the receiving machine and that they will linger the query is down to actually if usually need to do autobahn what's that for for some things I guess that would be nice to be in the Council inbound and people would like to extend the query message to include the got which would mean I wanna execute this query with state and by woman writers right now you have to set or sets beginning Sept local executes commit this is the way you get uh 1 of execution of a query that you want to have a time of not and people would say what I'd just like to attach McGuffin directly to the query methods when I have the mask with transactions and 7 3 3 different nodes are queries and this is
the end if you have questions would have picked out of time so this do is the this quick or you can just come down here and kind the end


  839 ms - page object


AV-Portal 3.21.3 (19e43a18c8aa08bcbdf3e35b975c18acb737c630)