Re: Add SPI results constants available for PL/*

I'm using PL/Python, and when getting the result object from a
plpy.execute(), I can access to the result.status().

E.g.: the result.status() is 4. But to know what 4 corresponds to, I must
open the "spi.h" file from the sources to see :

#define SPI_OK_CONNECT 1
#define SPI_OK_FINISH 2
#define SPI_OK_FETCH 3
#define SPI_OK_UTILITY 4
#define SPI_OK_SELECT 5
#define SPI_OK_SELINTO 6
#define SPI_OK_INSERT 7
#define SPI_OK_DELETE 8
#define SPI_OK_UPDATE 9
#define SPI_OK_CURSOR 10
#define SPI_OK_INSERT_RETURNING 11
#define SPI_OK_DELETE_RETURNING 12
#define SPI_OK_UPDATE_RETURNING 13
#define SPI_OK_REWRITTEN 14

Is there a way to have access to these constants from the PL/python code
and other PL/* (Tcl, Perl, etc.) ?

Thanks,

--
Samuel PHAN

On Tue, Jan 3, 2012 at 5:55 AM, Samuel PHAN <samuel(at)nomao(dot)com> wrote:
> I'm using PL/Python, and when getting the result object from a
> plpy.execute(), I can access to the result.status().
>
> E.g.: the result.status() is 4. But to know what 4 corresponds to, I must
> open the "spi.h" file from the sources to see :
>
> #define SPI_OK_CONNECT 1
> #define SPI_OK_FINISH 2
> #define SPI_OK_FETCH 3
> #define SPI_OK_UTILITY 4
> #define SPI_OK_SELECT 5
> #define SPI_OK_SELINTO 6
> #define SPI_OK_INSERT 7
> #define SPI_OK_DELETE 8
> #define SPI_OK_UPDATE 9
> #define SPI_OK_CURSOR 10
> #define SPI_OK_INSERT_RETURNING 11
> #define SPI_OK_DELETE_RETURNING 12
> #define SPI_OK_UPDATE_RETURNING 13
> #define SPI_OK_REWRITTEN 14
>
> Is there a way to have access to these constants from the PL/python code and
> other PL/* (Tcl, Perl, etc.) ?

I'd suppose it interesting to add a table to pg_catalog containing this data.

That would be one of the easier ways to allow all languages to have
access to the constants.

It might be a SMOP (Simple Matter Of Programming) to write a script to
transform the .h file into a suitable INSERT statement for such a
table.

I wonder if there are other sets of constants worth having. I'd think
that the various forms of "command OK" codes would also be interesting
to have as a table like this.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

2012/1/3 Christopher Browne <cbbrowne(at)gmail(dot)com>:
> On Tue, Jan 3, 2012 at 5:55 AM, Samuel PHAN <samuel(at)nomao(dot)com> wrote:
>> I'm using PL/Python, and when getting the result object from a
>> plpy.execute(), I can access to the result.status().
>>
>> E.g.: the result.status() is 4. But to know what 4 corresponds to, I must
>> open the "spi.h" file from the sources to see :
>>
>> #define SPI_OK_CONNECT 1
>> #define SPI_OK_FINISH 2
>> #define SPI_OK_FETCH 3
>> #define SPI_OK_UTILITY 4
>> #define SPI_OK_SELECT 5
>> #define SPI_OK_SELINTO 6
>> #define SPI_OK_INSERT 7
>> #define SPI_OK_DELETE 8
>> #define SPI_OK_UPDATE 9
>> #define SPI_OK_CURSOR 10
>> #define SPI_OK_INSERT_RETURNING 11
>> #define SPI_OK_DELETE_RETURNING 12
>> #define SPI_OK_UPDATE_RETURNING 13
>> #define SPI_OK_REWRITTEN 14
>>
>> Is there a way to have access to these constants from the PL/python code and
>> other PL/* (Tcl, Perl, etc.) ?
>
> I'd suppose it interesting to add a table to pg_catalog containing this data.

>
> That would be one of the easier ways to allow all languages to have
> access to the constants.

- it is useless overhead

I am expecting so definition some constants in Perl, Python is simple

Regards

Pavel

>
> It might be a SMOP (Simple Matter Of Programming) to write a script to
> transform the .h file into a suitable INSERT statement for such a
> table.
>
> I wonder if there are other sets of constants worth having.  I'd think
> that the various forms of "command OK" codes would also be interesting
> to have as a table like this.
> --
> When confronted by a difficult problem, solve it by reducing it to the
> question, "How would the Lone Ranger handle this?"
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jan 3, 2012 at 9:33 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> I'd suppose it interesting to add a table to pg_catalog containing this data.
>
> - it is useless overhead

I tend to agree.

> I am expecting so definition some constants in Perl, Python is simple

Presumably one could instead write a script to transform the list of
constants into a .pm file that could be loaded into the background, or
whatever PL/python's equivalent of that concept is. Not sure if
there's a better way to do it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/03/2012 08:40 PM, Robert Haas wrote:
> On Tue, Jan 3, 2012 at 9:33 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> I'd suppose it interesting to add a table to pg_catalog containing this data.
>> - it is useless overhead
> I tend to agree.
>
>> I am expecting so definition some constants in Perl, Python is simple
> Presumably one could instead write a script to transform the list of
> constants into a .pm file that could be loaded into the background, or
> whatever PL/python's equivalent of that concept is. Not sure if
> there's a better way to do it.

Yeah, I'm with you and Pavel. Here's my quick perl one-liner to produce
a set of SPI_* constants for pl/perl. I'm looking at the best way to
include this in the bootstrap code.

perl -ne 'BEGIN { print "use constant\n{\n"; } END { print "};\n"; }
print "\t$1 => $2,\n" if /#define (SPI_\S+)\s+\(?(-?\d+)\)?/;'
src/include/executor/spi.h

cheers

andrew

I agree with Pavel also. Putting these constants in the pg_catalog isn't
the cleanest solution.

Though one can make its own little lib in python, perl, whatever, to store
these constants, it would be better if through the compilation, these C
constants were copied in a way for PL/*.

I can't really tell for the procedure languages other than PL/python but
for this one, typically, it would be nice to have them in "*plpy*" module.

result = plpy.execute(sql_query)
if result.status() == plpy.SPI_OK_SELECT:
# something...

Since the PG developers are the one who decide these constant values, it's
quite logic that the equivalent constants for each PL are made available by
the PG developers and not each geek to redo the wheel on it's own.

Well, it's not a crucial feature of course, but a nice to have, that was my
point ;-)

Have a nice day, guys,

Samuel

On Wed, Jan 4, 2012 at 3:11 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
>
> On 01/03/2012 08:40 PM, Robert Haas wrote:
>
>> On Tue, Jan 3, 2012 at 9:33 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)**com<pavel(dot)stehule(at)gmail(dot)com>>
>> wrote:
>>
>>> I'd suppose it interesting to add a table to pg_catalog containing this
>>>> data.
>>>>
>>> - it is useless overhead
>>>
>> I tend to agree.
>>
>> I am expecting so definition some constants in Perl, Python is simple
>>>
>> Presumably one could instead write a script to transform the list of
>> constants into a .pm file that could be loaded into the background, or
>> whatever PL/python's equivalent of that concept is. Not sure if
>> there's a better way to do it.
>>
>
> Yeah, I'm with you and Pavel. Here's my quick perl one-liner to produce a
> set of SPI_* constants for pl/perl. I'm looking at the best way to include
> this in the bootstrap code.
>
> perl -ne 'BEGIN { print "use constant\n{\n"; } END { print "};\n"; }
> print "\t$1 => $2,\n" if /#define (SPI_\S+)\s+\(?(-?\d+)\)?/;'
> src/include/executor/spi.h
>
>
> cheers
>
> andrew
>
>

On Tue, Jan 03, 2012 at 09:11:17PM -0500, Andrew Dunstan wrote:
> Yeah, I'm with you and Pavel. Here's my quick perl one-liner to
> produce a set of SPI_* constants for pl/perl. I'm looking at the
> best way to include this in the bootstrap code.
>
> perl -ne 'BEGIN { print "use constant\n{\n"; } END { print "};\n"; }
> print "\t$1 => $2,\n" if /#define (SPI_\S+)\s+\(?(-?\d+)\)?/;'
> src/include/executor/spi.h

Well, there's also h2ph, but that may be overkill...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

On 01/03/2012 09:11 PM, Andrew Dunstan wrote:
>
>
> On 01/03/2012 08:40 PM, Robert Haas wrote:
>> On Tue, Jan 3, 2012 at 9:33 AM, Pavel
>> Stehule<pavel(dot)stehule(at)gmail(dot)com> wrote:
>>>> I'd suppose it interesting to add a table to pg_catalog containing
>>>> this data.
>>> - it is useless overhead
>> I tend to agree.
>>
>>> I am expecting so definition some constants in Perl, Python is simple
>> Presumably one could instead write a script to transform the list of
>> constants into a .pm file that could be loaded into the background, or
>> whatever PL/python's equivalent of that concept is. Not sure if
>> there's a better way to do it.
>
> Yeah, I'm with you and Pavel. Here's my quick perl one-liner to
> produce a set of SPI_* constants for pl/perl. I'm looking at the best
> way to include this in the bootstrap code.
>
> perl -ne 'BEGIN { print "use constant\n{\n"; } END { print "};\n"; }
> print "\t$1 => $2,\n" if /#define (SPI_\S+)\s+\(?(-?\d+)\)?/;'
> src/include/executor/spi.h
>
>
>

Actually, now I look closer I see that PLPerl passes back a stringified
status from SPI_execute(), so there is no great need for setting up
these constants. It's probably water under the bridge now, but maybe
PLPython should have done this too.

cheers

andrew

2012/1/10 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/03/2012 09:11 PM, Andrew Dunstan wrote:
>>
>>
>>
>> On 01/03/2012 08:40 PM, Robert Haas wrote:
>>>
>>> On Tue, Jan 3, 2012 at 9:33 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)com>
>>>  wrote:
>>>>>
>>>>> I'd suppose it interesting to add a table to pg_catalog containing this
>>>>> data.
>>>>
>>>> - it is useless overhead
>>>
>>> I tend to agree.
>>>
>>>> I am expecting so definition some constants in Perl, Python is simple
>>>
>>> Presumably one could instead write a script to transform the list of
>>> constants into a .pm file that could be loaded into the background, or
>>> whatever PL/python's equivalent of that concept is.  Not sure if
>>> there's a better way to do it.
>>
>>
>> Yeah, I'm with you and Pavel. Here's my quick perl one-liner to produce a
>> set of SPI_* constants for pl/perl. I'm looking at the best way to include
>> this in the bootstrap code.
>>
>>   perl -ne 'BEGIN { print "use constant\n{\n"; } END { print "};\n"; }
>>   print "\t$1 => $2,\n" if /#define (SPI_\S+)\s+\(?(-?\d+)\)?/;'
>>   src/include/executor/spi.h
>>
>>
>>
>
>
> Actually, now I look closer I see that PLPerl passes back a stringified
> status from SPI_execute(), so there is no great need for setting up these
> constants. It's probably water under the bridge now, but maybe PLPython
> should have done this too.
>

This is not documented well - I see nothing about result value in doc.
Does it raise exception when SPI returns some bad result value?

Regards

Pavel

> cheers
>
> andrew

On 01/10/2012 12:34 PM, Pavel Stehule wrote:
>>
>> Actually, now I look closer I see that PLPerl passes back a stringified
>> status from SPI_execute(), so there is no great need for setting up these
>> constants. It's probably water under the bridge now, but maybe PLPython
>> should have done this too.
>>
> This is not documented well - I see nothing about result value in doc.
> Does it raise exception when SPI returns some bad result value?

The docs state:

You can then access the command status (e.g., SPI_OK_INSERT) like this:

$res = $rv->{status};

And it works like this:

andrew=# do 'my $rv = spi_exec_query("select 1 as a");
elog(NOTICE,$rv->{status});' language plperl;
NOTICE: SPI_OK_SELECT
CONTEXT: PL/Perl anonymous code block
DO
andrew=#

An error causes the function to end, so it never sees the error status:

andrew=# do 'my $rv = spi_exec_query("select blurfl");
elog(NOTICE,$rv->{status});' language plperl;
ERROR: column "blurfl" does not exist at line 1.
CONTEXT: PL/Perl anonymous code block
andrew=#

If you think more documentation is needed, submit a patch.

cheers

andrew

2012/1/10 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/10/2012 12:34 PM, Pavel Stehule wrote:
>>>
>>>
>>> Actually, now I look closer I see that PLPerl passes back a stringified
>>> status from SPI_execute(), so there is no great need for setting up these
>>> constants. It's probably water under the bridge now, but maybe PLPython
>>> should have done this too.
>>>
>> This is not documented well - I see nothing about result value in doc.
>> Does it raise exception when SPI returns some bad result value?
>
>
>
> The docs state:
>
>   You can then access the command status (e.g., SPI_OK_INSERT) like this:
>
>   $res = $rv->{status};
>
>
> And it works like this:
>
>   andrew=# do 'my $rv = spi_exec_query("select 1 as a");
>   elog(NOTICE,$rv->{status});' language plperl;
>   NOTICE:  SPI_OK_SELECT
>   CONTEXT:  PL/Perl anonymous code block
>   DO
>   andrew=#
>
> An error causes the function to end, so it never sees the error status:
>
>   andrew=# do 'my $rv = spi_exec_query("select blurfl");
>   elog(NOTICE,$rv->{status});' language plperl;
>   ERROR:  column "blurfl" does not exist at line 1.
>   CONTEXT:  PL/Perl anonymous code block
>   andrew=#
>
>
> If you think more documentation is needed, submit a patch.

I was blind, I am sorry - I am missing explicit note, so command
status is string, that is all.

Regards

Pavel

>
>
> cheers
>
> andrew
>