Re: [HACKERS] libpq and SPI - Mailing list pgsql-hackers

>"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