Re: [HACKERS] libpq and SPI

>"Gerald L. Gay" <glgay(at)pass(dot)korea(dot)army(dot)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