FFaasstt PPaatthh 11.. GGeenneerraall The term _F_a_s_t _P_a_t_h is used throughout the POSTGRES doc- umentation to denote two different ideas. The first is the notion of being able to call a POSTGRES backend function directly from a frontend application. The second is the notion of being able to directly access a major subsystem of the postgres backend from within a user-defined function which is itself loaded into the backend. These are really two very different ideas and unfortunately the same term is sometimes used to describe both. This document describes the latter: accessing the major subsystems of postgres from within a backend resident user-defined function. Details of the former can be found in the LIBPQ documentation describ- ing the PPQQffnn(()) function, but it should be noted that this is by far less frequently used and is certainly of limited capability (constrained mainly by the fact that the frontend and backend applications are running in completely different address spaces). A user-defined function (that is not defined as "untrusted") is loaded into the POSTGRES backend and shares the address space with it. Because of this it is possible for a user-defined function to access any and every routine resident in the backend. So at a low level, strictly speak- ing, the user-defined function can do anything including step on random memory and call functions that should not be called. At this low level there is no "interface" as the user-defined function just calls other functions that happen to reside in the POSTGRES backend. The purpose of the _F_a_s_t _P_a_t_h interface, then, is conceptual. It is to document at a higher level just what the major subsystems in the POSTGRES backend are and what routines a user-defined function can call and get predictable and usable results. There are six functional levels that can be accessed. They are 11)) PPoossttqquueell iinntteerrffaaccee ((ttrraaffffiicc ccoopp lleevveell)) AA ffuunnccttiioonn ccaann ppaassss aa ssttrriinngg wwiitthh ppoossttqquueell ccoommmmaannddss.. 22)) PPaarrsseerr AA ffuunnccttiioonn ccaann aacccceessss tthhee PPOOSSTTGGRREESS ppaarrsseerr,, ggeettttiinngg aa ppaarrssee ttrreeee iinn rreettuurrnn.. 33)) QQuueerryy OOppttiimmiizzeerr AA ffuunnccttiioonn ccaann ccaallll tthhee qquueerryy ooppttiimmiizzeerr,, ppaassssiinngg iitt aa ppaarrssee ttrreeee aanndd oobbttaaiinniinngg aa qquueerryy ppllaann iinn rreettuurrnn.. 44)) EExxeeccuuttoorr AA ffuunnccttiioonn ccaann ccaallll tthhee eexxeeccuuttoorr aanndd ppaassss iitt aa qquueerryy ppllaann ttoo 11 bbee eexxeeccuutteedd.. 55)) AAcccceessss mmeetthhooddss AA ffuunnccttiioonn ccaann ddiirreeccttllyy ccaallll tthhee aacccceessss mmeetthhooddss.. 66)) FFuunnccttiioonn mmaannaaggeerr AA ffuunnccttiioonn ccaann ccaallll ootthheerr rreeggiisstteerreedd PPOOSSTTGGRREESS ffuunnccttiioonnss uussiinngg tthhiiss lleevveell.. The most common levels for a function to access are 1 and 5. Level 1 allows the function to perform postquel queries almost as if it were a frontend application and level 5 allows a function to access and manipulate tuples in a relation directly. 22.. LLeevveell 11)) PPoossttqquueell iinntteerrffaaccee The internal backend function called is cchhaarr ** PPQQeexxeecc((qquueerryy)) cchhaarr **qquueerryy;; and is similar to the PPQQeexxeecc(()) function that a frontend application using LIBPQ would call except for some important restrictions on what you can do and how data is retrieved. In addition, the following LIBPQ functions are not available from a user-defined function in the backend. PPQQsseettddbb(()) PPQQddbb(()) PPQQrreesseett(()) PPQQffiinniisshh(()) PPQQNNoottiiffiieess(()) PPQQRReemmoovveeNNoottiiffyy(()) PPQQggeettlliinnee(()) PPQQppuuttlliinnee(()) PPQQeennddccooppyy(()) The environment variables discussed in the LIBPQ docu- ment (PGHOST, PGDATABASE, PGPORT, etc) are also not used in the backend. 22..11.. RReettrriieevviinngg ddaattaa In a frontend application using LIBPQ a user can do a retrieve into a named portal using the "retrieve portal ..." syntax. This does not work in the backend because the back- end PPQQeexxeecc(()) interface manages portals in a different way 22 and also because you cannot issue LIBPQ "begin" or "end" statements. When issuing a POSTQUEL query that returns tuples, the PPQQeexxeecc(()) function will return a string that starts with "P" and the remainder of which is the name of the portal where the tuples are. This is not different from the frontend LIBPQ PPQQeexxeecc(()) function _e_x_c_e_p_t that in the backend there is no default portal named "blank" and so the portal name must be looked at and passed to the PPQQppaarrrraayy(()) function to get the portal buffer. Also, each PPQQeexxeecc(()) that returns data in a portal allocates a new unique portal rather than overwriting data in the same one (as with the blank portal in the frontend PPQQeexxeecc(())). There is also a very low limit (around 10 currently) on the number of unique portals the backend may have in use, so it is wise for the backend function to call the PPQQcclleeaarr(()) function to deallo- cate the portal after you are finished accessing the data in it. While this inconsistency between the frontend and back- end handling of portals may seem like a bug at first glance, it is necessary because it is possible in the backend to have one user-defined function that uses PPQQeexxeecc(()) call another user-defined function that also uses PPQQeexxeecc(()). If PQexec() overwrites the same buffer, the second call to PQexec() might overwrite the data from the previous PQexec() before the first function got a chance to access it. We are looking at other ways we may implement this to avoid the problem, but for now it is wise to just call PQclear() on the portal after you are finished with the data. 22..22.. OOtthheerr RReessttrriiccttiioonnss The backend PQexec() interface is meant to be used by a user-defined function. In general, a user-defined function might be called during the course of executing a single POSTQUEL command, and so certain semantics concerning atom- icity and visibility of updates may make the POSTQUEL com- mand that is executed by the backend function behave differ- ently than if it were executed from the frontend LIBPQ interface. Specifically, it is not supposed to be possible for a single command to see its own updates. For example, consider the following POSTQUEL fragment: TThhee ffuunnccttiioonn ""ssaall"" rreettuurrnnss tthhee ssaallaarryy ooff tthhee eemmppllooyyeeee wwhhoossee nnaammee mmaattcchheess tthhee ssuupppplliieedd aarrgguummeenntt.. ((bbeeffoorree)) NNAAMMEE SSAALLAARRYY jjooee 110000 bboobb 110000 jjiimm 110000 33 rreeppllaaccee eemmpp((ssaall == 22 ** ssaall((""bboobb"")))) ((aafftteerr)) NNAAMMEE SSAALLAARRYY jjooee 220000 bboobb 220000 jjiimm 220000 <> Consider if the previous command could see its own updates. The replace command would makes its way through all the employees giving them 2 times what bob makes. But as soon as "bob" himself get two times his salary, then the remain- ing employees would get two times his _n_e_w salary, which would be 400 instead of 200. If the order that the replace command happened to access the tuples in was the order listed above, then the results would be: ((aafftteerr)) NNAAMMEE SSAALLAARRYY jjooee 220000 bboobb 220000 jjiimm 440000 and this would be wrong. So the normal semantics are that a single command cannot see its own updates. The reason we metion this here is that in a front-end LIBPQ application, each POSTQUEL command is indeed a separate command (and each can see the results of the previous command, but not its own), however, in the backend the POSTQUEL commands called through PQexec() from a user-defined function are all in the _s_a_m_e command, and so cannot see the updates they make. If they could, then the instantiation of the user-defined func- tion that was running this POSTQUEL command would itself span more than one command and would break the visibility semantics. This can be very confusing since the function could retrieve tuples into another relation, and then try to retrieve them out of the new relation (in the same instantiation of the function) and not find anything. Because there may be a very real and practical need for a user-defined function to see the results of updates made during the course of its execution, we have provided a new function that temporarily disables the visibility semantics that were just discussed. It is important to understand that when using these func- tions you are temporarily breaking the visibility semantics in POSTGRES and you must be aware of any side effects, such as in the example cited above, that this may have. The side effects could be more serious than just wrong answers. The following function turns off the visibililty rules: 44 blah() # name to be decided The following function turns back on the visibililty rules: blahblah() # name to be decided Another restriction is that since the command that calls the user defined function is already inside a transac- tion, and POSTGRES doesn't support nested transactions, it would be wrong to call either "begin" or "end" in the postquel that is passed to the backend PQexec(). Probably the biggest problem with using PQexec() in the backend is that currently if any error occurs in processing the POSTQUEL commands, or if the executed commands happen to call the backend wwaarrnn(()) routine, then the call to PQexec() never returns. This is a restriction that we consider a bug and hope we can fix for the next release (5.0). 22..33.. EExxaammpplleess The following code fragment shows a user-defined func- tion called xconnect that issues a POSTQUEL command to retrieve all the tuples from a relation. iinntt44 **xxccoonnnneecctt ((aarrgg)) iinntt44 **aarrgg;; {{ PPoorrttaallBBuuffffeerr **bbuuffffeerr;; iinntt nn__ggrroouuppss;; iinntt nn__ttuupplleess;; iinntt nn__ffiieellddss;; iinntt ii,, jj,, kk;; iinntt tt;; cchhaarr **rreettvvaall;; iinntt nn;; cchhaarr **ppoorrttaall;; //** ** HHeerree wwee iissssuuee aa PPOOSSTTQQUUEELL ccoommmmaanndd ttoo rreettrriieevvee ** aallll tthhee iinnssttaanncceess ffrroomm rreellaattiioonn II **// rreettvvaall == ((cchhaarr **)) PPQQeexxeecc ((""rreettrriieevvee ((II..aallll))""));; //** ** LLeett''ss pprriinntt tthhee rreettuurrnn ccooddee aanndd ppoorrttaall nnaammee.. NNoottee tthhaatt tthhee ** ppoorrttaall nnaammee iiss nnoott ""bbllaannkk"",, aass iitt wwoouulldd bbee iiff tthhiiss ccooddee wweerree ** rruunn iinn tthhee ffrroonntteenndd.. **// pprriinnttff((""PPQQeexxeecc rreettuurrnneedd %%ss\\nn"",, rreettvvaall));; 55 iiff ((**rreettvvaall ==== ''PP'')) {{ //** ** TThhee rreettrriieevvee rreettuurrnndd oouurr ddaattaa iinn aa ppoorrttaall.. TThhee ppoorrttaall ** nnaammee ssttaarrttss rriigghhtt aafftteerr tthhee ''PP'',, ssoo ppooiinntt ""ppoorrttaall"" ** oonnee cchhaarraacctteerr ppaasstt tthhee ''PP'' **// ppoorrttaall== rreettvvaall++11;; }} eellssee {{ //** ** OOtthheerrwwiissee ssoommeetthhiinngg eellssee hhaappppeenneedd.. FFoorr bbrreevviittyy wwee''llll ** lleeaavvee oouutt tthhee eerrrroorr hhaannddlliinngg ccooddee **// ...... }} //** ** CCoonnvveerrtt tthhee ppoorrttaall nnaammee ttoo aa ppoorrttaall bbuuffffeerr **// bbuuffffeerr == PPQQppaarrrraayy ((ppoorrttaall));; //** ** PPrriinntt tthhee ttuupplleess.. ** TThhee rreesstt llooookkss jjuusstt aass iitt wwoouulldd iinn aa ffrroonntteenndd LLIIBBPPQQ ** aapppplliiccaattiioonn.. **// nn__ggrroouuppss == PPQQnnggrroouuppss ((bbuuffffeerr));; tt == 00;; ffoorr ((kk==00;; kk << nn__ggrroouuppss;; kk++++)) {{ pprriinnttff ((""nneeww iinnssttaannccee ggrroouupp nnuummbbeerr==((%%dd))\\nn"",, kk));; nn__ttuupplleess == PPQQnnttuupplleessGGrroouupp ((bbuuffffeerr,, kk));; nn__ffiieellddss == PPQQnnffiieellddssGGrroouupp ((bbuuffffeerr,, kk));; //** PPrriinntt AAtttt--NNaammeess **// ffoorr ((ii==00;; ii << nn__ffiieellddss;; ii++++)) {{ pprriinnttff ((""%%--1155ss"",, PPQQffnnaammeeGGrroouupp ((bbuuffffeerr,, kk,, ii)) ));; }} pprriinnttff ((""\\nn""));; //** PPrriinntt vvaalluueess **// ffoorr ((ii==00;; ii<< nn__ttuupplleess;; ii++++)) {{ ffoorr ((jj==00;; jj << nn__ffiieellddss;; jj++++)) {{ pprriinnttff ((""%%--1155ss"",, PPQQggeettvvaalluuee ((bbuuffffeerr,, tt++ii,, jj))));; }} pprriinnttff ((""\\nn""));; }} tt ++== nn__ttuupplleess;; }} //** ** NNooww tthhaatt wwee''rree ddoonnee,, rreemmeemmbbeerr ttoo cclleeaarr tthhee ppoorrttaall ootthheerrwwiissee ** wwee''llll ssoooonn rruunn oouutt ooff tthheemm.. **// PPQQcclleeaarr((ppoorrttaall));; 66 rreettuurrnn ((aarrgg));; }} 33.. LLeevveell 22 PPaarrsseerr The parser can be used to convert a POSTQUEL query string into a parse tree. The parse tree will be needed for both the planner and executor. The following routine is used to get a parse tree (from .../src/backend/parser/ylib.c ... made by gram.y): ppaarrsseerr((ssttrr,, ll,, ttyyppeevv,, nnaarrggss)) cchhaarr **ssttrr;; LLiissppVVaalluuee ll;; OObbjjeeccttIIdd **ttyyppeevv;; iinntt nnaarrggss;; "str" is the query string, ie. "retrieve (x.all) where....". The argument "l" is a pointer to the list of parse trees where the result will be stored. Note that the function has no return type, the result is the second argument. Once the parse tree is obtained, it can be filtered through the rewrite rules and some other time routines. There is no single routine to call to do this. Please refer to the following section entitled "Parser and Planner Com- bined Call". It has all the code to do this. Some of the functions needed to make the parse tree complete are : rreewwrriitttteenn__ttrreeee == QQuueerryyRReewwrriittee ((ppaarrssee__ttrreeee)) This takes the initial parse-tree and applies the current rules to re-write the query. It is important to note that utilities should not be rewritten so they must be moved to a seperate parse-tree list, and then re-combined after the re- write. This function is called for every non-utility parse tree. 44.. LLeevveell 33)) QQuueerryy OOppttiimmiizzeerr ((PPllaannnneerr)) The Planner is used to optimize the parse-tree. It can be used on every parse-tree in the list except for utili- ties. The planner must first be initialized for each call, using the following (code is in .../src/backend/planner/util/internal.c): 77 iinniitt__ppllaannnneerr(()) Then it is ready to receive a parse-tree with the following command (code in .../src/backend/planner/plan/planner.c): PPllaann ppllaannnneerr((ppaarrssee--ttrreeee)) LLiissppVVaalluuee ppaarrssee;; This returns a plan of type "Plan". By looping through those two commands for every non-utility parse-tree, a plan list can be made to match the parse-tree list. You must link each Plan together to form a plan-list of type "List". 55.. PPaarrsseerr aanndd PPllaannnneerr CCoommbbiinneedd CCaallll The following function is available to combine the parser and planner steps described above. It is a valueable function, as it does all the details necessary to tidy up the parse-tree-list after calling parser() (code is in .../src/backend/tcop/postgres.c): LLiisstt ppgg__ppllaann((qquueerryy__ssttrriinngg,, ttyyppeevv,, nnaarrggss,, ppaarrsseettrreeeePP,, ddeesstt)) SSttrriinngg qquueerryy__ssttrriinngg;; //** ssttrriinngg ttoo eexxeeccuuttee **// OObbjjeeccttIIdd **ttyyppeevv;; //** aarrgguummeenntt ttyyppeess **// iinntt nnaarrggss;; //** nnuummbbeerr ooff aarrgguummeennttss **// LLiissppVVaalluuee **ppaarrsseettrreeeePP;; //** ppooiinntteerr ttoo tthhee ppaarrssee ttrreeeess **// CCoommmmaannddDDeesstt ddeesstt;; //** wwhheerree rreessuullttss sshhoouulldd ggoo **// TThhiiss rreettuurrnnss aa lliisstt ooff ppllaannss.. qquueerryy__ssttrriinngg iiss aa ppoossttqquueell qquueerryy.. ttyyppeevv,, nnaarrggss aarree aarrgguummeennttss ((??????)) ppaarrsseettrreeeePP iiss aa ppooiinntteerr ttoo tthhee ppaarrssee ttrreeee wwhhiicchh wwiillll bbee ccrreeaatteedd iinn tthhee pprroocceessss ddeesstt iiss tthhee llooccaattiioonn ffoorr tthhee oouuttppuutt ((uusseedd ffoorr eennddiinngg ttrraannssaaccttiioonnss)) ------------------------------------------------------------------------------------------------------------------------------ MMOORREE AABBOOUUTT TTHHEE AARRGGUUMMEENNTTSS:: tthhee ""ddeesstt"" aarrgguummeenntt wwhhiicchh iiss ppaasssseedd ttoo mmaannyy ooff tthheessee ffuunnccttiioonnss iiss:: ** -- ssttddoouutt iiss tthhee ddeessiinnaattiioonn oonnllyy wwhheenn wwee aarree rruunnnniinngg aa ** bbaacckkeenndd wwiitthhoouutt aa ppoossttmmaasstteerr aanndd aarree rreettuurrnniinngg rreessuullttss ** bbaacckk ttoo tthhee uusseerr.. ** ** -- aa llooccaall ppoorrttaall bbuuffffeerr iiss tthhee ddeessttiinnaattiioonn wwhheenn aa bbaacckkeenndd ** eexxeeccuutteess aa uusseerr--ddeeffiinneedd ffuunnccttiioonn wwhhiicchh ccaallllss PPQQeexxeecc(()) oorr ** PPQQffnn(()).. IInn tthhiiss ccaassee,, tthhee rreessuullttss aarree ccoolllleecctteedd iinnttoo aa ** PPoorrttaallBBuuffffeerr wwhhiicchh tthhee uusseerr''ss ffuunnccttiioonn mmaayy ddiiddddllee wwiitthh.. 88 ** ** -- aa rreemmoottee ppoorrttaall bbuuffffeerr iiss tthhee ddeessttiinnaattiioonn wwhheenn wwee aarree ** rruunnnniinngg aa bbaacckkeenndd wwiitthh aa ffrroonntteenndd aanndd tthhee ffrroonntteenndd eexxeeccuutteess ** PPQQeexxeecc(()) oorr PPQQffnn(()).. IInn tthhiiss ccaassee,, tthhee rreessuullttss aarree sseenntt ** ttoo tthhee ffrroonntteenndd vviiaa tthhee ppqq__ ffuunnccttiioonnss ((iinn ttccoopp//ddeesstt..cc)).. ** ** -- NNoonnee iiss tthhee ddeessttiinnaattiioonn wwhheenn tthhee ssyysstteemm eexxeeccuutteess ** aa qquueerryy iinntteerrnnaallllyy.. TThhiiss iiss nnoott uusseedd nnooww bbuutt iitt mmaayy bbee ** uusseeffuull ffoorr tthhee ppaarraalllleell ooppttiimmiisseerr//eexxeeccuuttoorr.. ** ssoo ddeesstt ccaann bbee NNoonnee,, //** rreessuullttss aarree ddiissccaarrddeedd **// DDeebbuugg,, //** rreessuullttss ggoo ttoo ddeebbuuggggiinngg oouuttppuutt **// LLooccaall,, //** rreessuullttss ggoo iinn llooccaall ppoorrttaall bbuuffffeerr **// RReemmoottee,, //** rreessuullttss sseenntt ttoo ffrroonntteenndd pprroocceessss **// CCooppyyBBeeggiinn,, //** rreessuullttss sseenntt ttoo ffrroonntteenndd pprroocceessss bbuutt aarree ssttrriinnggss **// CCooppyyEEnndd,, //** rreessuullttss sseenntt ttoo ffrroonntteenndd pprroocceessss bbuutt aarree ssttrriinnggss **// RReemmootteeIInntteerrnnaall //** rreessuullttss sseenntt ttoo ffrroonntteenndd pprroocceessss iinn iinntteerrnnaall ((bbiinnaarryy)) ffoorrmm **// 66.. LLeevveell 44)) EExxeeccuuttoorr The executor is called once the parse-tree-list and plan-list have been obtained. It works off both lists to produce the desired action. The user must separate the two different kinds of actions, utilities and regular commands. The executor must be called for each parse-tree in the parse-tree-list. Note that there is no plan for a utility parse-tree. Also, no matter which type you execute, you must call CommandCounterIncrement() if you have more parse- trees to execute. This makes the results visible to future queries. To check whether a parse-tree represents a util- ity, use: iiff ((aattoomm((CCAARR((ppaarrssee__ttrreeee)))))) ((tthheenn uuttiilliittyy)) ((eellssee nnoorrmmaall)) UTILITY EXECUTOR: wwhhiicchh__uuttiill == LLIISSPPVVAALLUUEE__IINNTTEEGGEERR((CCAARR((ppaarrsseettrreeee))));; PPrroocceessssUUttiilliittyy((wwhhiicchh__uuttiill,, CCDDRR((ppaarrsseettrreeee)),, qquueerryy__ssttrriinngg,, ddeesstt));; (code in src/backend/tcop/utility.c) vvooiidd PPrroocceessssUUttiilliittyy((ccoommmmaanndd,, aarrggss,, ccoommmmaannddSSttrriinngg,, ddeesstt)) 99 iinntt ccoommmmaanndd;; //** ""ttaagg"" **// LLiissppVVaalluuee aarrggss;; cchhaarr **ccoommmmaannddSSttrriinngg;; CCoommmmaannddDDeesstt ddeesstt;; CCoommmmaanndd iiss aa iinntteeggeerr vvaalluuee iiddeennttiiffiinngg wwhhiicchh uuttiilliittyy ttoo eexxeeccuuttee aarrggss iiss ???????? CCoommmmaannddSSttrriinngg iiss tthhee aaccttuuaallllyy ppoossttqqeeuull qquueerryy ssttrriinngg DDeesstt iiss tthhee llooccaattiioonn ttoo sseenndd tthhee rreessuulltt NNOOTTEE:: tthhee ffiirrsstt tthhrreeee aarrgguummeennttss ccaann bbee ttaakkeenn ffrroomm tthhee ppaarrssee--ttrreeee NORMAL EXECUTOR: (for non-utility queries) (code in src/backend/tcop/pquery.c) vvooiidd PPrroocceessssQQuueerryy((ppaarrsseerrttrreeee,, ppllaann,, aarrggvv,, ttyyppeevv,, nnaarrggss,, ddeesstt)) LLiisstt ppaarrsseerrttrreeee;; PPllaann ppllaann;; cchhaarr **aarrggvv;; OObbjjeeccttIIdd **ttyyppeevv;; iinntt nnaarrggss;; CCoommmmaannddDDeesstt ddeesstt;; ppaarrsseettrreeee iiss jjuusstt aa ssiinnggllee ppaarrssee ttrreeee ffrroomm tthhee ppaarrsseerr ppllaann iiss aa ssiinnggllee ppllaann ffrroomm tthhee ppllaannnneerr aarrggvv,, ttyyppeevv,, nnaarrggss iiss jjuusstt aarrgguummeenntt ddeessccrriippttiioonnss ddeesstt iiss tthhee llooccaattiioonn ffoorr oouuttppuutt ttoo ggoo.. 77.. LLeevveell 55)) AAcccceessss MMeetthhooddss 77..11.. PPooiinntteerr ttoo AAcccceessss MMeetthhoodd ddooccuummeennttaattiioonn Calling the access method routines gives the caller the ability to open relations directly and manipulate, as well as retrieve, the tuples within. The actual interface to the access methods is explained in great detail in the document entitled "The Postgres Access Methods". All of the relevent functions are described there. Rather than duplicate that information here, it is recommended that you use that document as your reference, and instead we will provide a number of examples as a small tutorial here. From here on it is assumed that you are familar with the access method routines as described in that document. 77..22.. EExxaammpplleess 1111