Обсуждение: Re: [HACKERS] libpq and SPI
>"Gerald L. Gay" <glgay@pass.korea.army.mil> writes: >> I have seen what I concider to be a bug in either the libpq library or >> in the backend. To see the effects, first, install the execq() function >> from the SPI section of the Programmers Guide. Then do this in psql: > >> template1=> select execq('create user fred', 1); >> Backend sent D message without prior T > >That would be a backend bug, for sure. It's a violation of the FE/BE >protocol to send data row(s) without sending a row description first. > >> At this point psql will hang. I have a patch for libpq that fixes this >> but I am not sure if this is the right place for it. > >I do not believe it is really possible to "ignore" this error inside >libpq. Without the initial T message you have no idea how many fields >are in a row, and thus you cannot even parse a D message to skip over >it --- there's no way to know the length of the null-fields bitmap. What I did in libpq was not to ignore the T message. Instead, if I get a T message, I remember it and ignore any intervening C messages until I either get a D to satisfy the T, or I get a C message of type "SELECT." This happens if the select returns no rows. > >> Is it not reasonable to run "utility" queries from inside SPI? > >Seems reasonable offhand, but I have no idea whether it really is or >not. If the context that the SPI procedure is executing from is a >SELECT, as you illustrate above, then I could see where it would be >a bad idea to allow utility statements to execute before the SELECT >finishes. (Examples of no-nos: altering or dropping tables that the >SELECT has already started using; VACUUM; perhaps other stuff.) What I have seen is: Any internal select/insert/update/delete calls that are performed inside the SPI function get suppressed but any utility functions get their status sent to the front end. So the T message is generated for the return type of the SPI function, then the C messages for any utility functions called, and then the D message for the actual return values. The reason I discovered this in the first place was because the create user .... in group thing doesn't work yet. I am porting an application from Sybase to Postgres and I need to ensure that the group stuff gets created. So I wrote a create_user SPI function that creates the user and then updates pg_group appropriately. This is when I saw this bug. Off-hand, I can't think of anything else you might need this for. But I can envision site-specific triggers on things like create user/drop user that may be tied to groups. Or maybe something like this: Deleting a group causes all the users in that group to also get deleted. In that case you would have quite a few "drop user" calls. Another alternative might be something like Sybase. In Sybase, when you create a server-side procedure, you don't call it via SELECT. You just type it's name (they normally start with sp_, i.e., sp_spaceused - shows how much space is available in a database). So the current paradigm in Postgres requires the SELECT protocol. I personally like this because it works nicely for getting the return status. But it doesn't necessarily have to be SELECT. It could be something like EXEC or CALL or something. > >But either way it's definitely a backend bug: the SPI interface >should either handle utility statements or reject them cleanly. > > regards, tom lane I don't think it would be good to reject utility functions. This seems to me to fall into the category of "what if I need to ....." Jerry
Hi All,This question of an XML based frontend/backend protocol has come up once before in the last few months on this list (or is this the same thread even?) I am guessing that the underlying motivation is that many, if not most, users of Postgres want to connect the database to web-page user interfaces, and they would like the connection to be as seamless as possible. From that point of view the proposal seems reasonable, however I think that that point of view is limited, and that tying the frontend/backend protocol to a specific frontend technology would be a design mistake. Here are two reasons: 1) Frontend technology is notoriously short lived. Postgres -- or at least Ingres -- predates the internet, and since the beginning of Postgres there have been at least three protocols for transmitting formatted data over the internet (gopher, html, and now XML). I would expect that the basic design of Postgres is good for at least another 10 years, could the same be said about the design of XML? 2) Although the majority of applications for Postgres are likely to use web-based interfaces (or their successors), there are a significant number of applications that do not. My use for Postgres is as an indexed data store for large quantities of signal data, a typical front end for me is a scripting language embedded in a numerical application. Fast and simple are my primary requirements for a frontend/backend protocol. More generally, I think that the strength of Postgres' design is that it caters to a broad range of applications, and encourages experimentation with the internals of the DBMS at a fundamental level. GIST, RTREEs, the genetic optimizer, the myriad locking schemes, MVCC are all evidence of this. If you need special support for XML, include it as a configurable module, don't replace an existing generic solution with one that tailors the system to a specific application. Bernie Frankpitt