Обсуждение: 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