Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[Peter Eisentraut]

On 3/22/17 11:46, Andrew Dunstan wrote:
> This is a very simple patch that does what it advertises. It applies
> cleanly and provides tests for both the new methods (plan.cursor and
> plan.execute).
> 
> Marking Ready For Committer.

committed

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[Dagfinn Ilmari Mannsåker]

Jim Nasby  writes:

> On 2/25/17 10:27 AM, Peter Eisentraut wrote:
>> So I'm also wondering here which style people prefer so
>> I can implement it there.
>
> I think the more OO style is definitely better. I expect it would
> simplify the code as well.

I'm not a Python person, but I'd argue that the "more OO" style should
be the primary style documented, and the old style should just be
mentioned for reference.

 - ilmari

-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.   - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.  - Calle Dybedahl


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[Jim Nasby]

On 2/25/17 10:27 AM, Peter Eisentraut wrote:

So I'm also wondering here which style people prefer so
I can implement it there.


I think the more OO style is definitely better. I expect it would 
simplify the code as well.

--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[Andrew Dunstan]

On 03/21/2017 06:27 PM, Andrew Dunstan wrote:
> On 03/16/2017 05:32 PM, David Steele wrote:
>> On 2/25/17 1:27 PM, Peter Eisentraut wrote:
>>> Something that has been bothering me in PL/Python for a long time is the
>>> non-object-oriented way in which plans are prepared and executed:
>>>
>>> plan = plpy.prepare(...)
>>> res = plpy.execute(plan, ...)
>>>
>>> where plpy.execute() takes either a plan or a query string.
>>>
>>> I think a better style would be
>>>
>>> plan = plpy.prepare(...)
>>> res = plan.execute(...)
>>>
>>> so that the "plan" is more like a statement handle that one finds in
>>> other APIs.
>>>
>>> This ended up being very easy to implement, so I'm proposing to allow
>>> this new syntax as an alternative.
>>>
>>> I came across this again as I was developing the background sessions API
>>> for PL/Python.  So I'm also wondering here which style people prefer so
>>> I can implement it there.
>> This patch applies cleanly at cccbdde.
>>
>> Any Python folks out there who would like to take a crack at reviewing this?
>>
>>
> I'm not particularly a Python folk, but I've done enough over the years
> with PLs, including PLPython, that I think I can review this :-)
>


This is a very simple patch that does what it advertises. It applies
cleanly and provides tests for both the new methods (plan.cursor and
plan.execute).

Marking Ready For Committer.

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[Andrew Dunstan]

On 03/16/2017 05:32 PM, David Steele wrote:
> On 2/25/17 1:27 PM, Peter Eisentraut wrote:
>> Something that has been bothering me in PL/Python for a long time is the
>> non-object-oriented way in which plans are prepared and executed:
>>
>> plan = plpy.prepare(...)
>> res = plpy.execute(plan, ...)
>>
>> where plpy.execute() takes either a plan or a query string.
>>
>> I think a better style would be
>>
>> plan = plpy.prepare(...)
>> res = plan.execute(...)
>>
>> so that the "plan" is more like a statement handle that one finds in
>> other APIs.
>>
>> This ended up being very easy to implement, so I'm proposing to allow
>> this new syntax as an alternative.
>>
>> I came across this again as I was developing the background sessions API
>> for PL/Python.  So I'm also wondering here which style people prefer so
>> I can implement it there.
> This patch applies cleanly at cccbdde.
>
> Any Python folks out there who would like to take a crack at reviewing this?
>
>


I'm not particularly a Python folk, but I've done enough over the years
with PLs, including PLPython, that I think I can review this :-)

cheers

andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: Add cursor and execute methods to plan object

[David Steele]

On 2/25/17 1:27 PM, Peter Eisentraut wrote:
> Something that has been bothering me in PL/Python for a long time is the
> non-object-oriented way in which plans are prepared and executed:
> 
> plan = plpy.prepare(...)
> res = plpy.execute(plan, ...)
> 
> where plpy.execute() takes either a plan or a query string.
> 
> I think a better style would be
> 
> plan = plpy.prepare(...)
> res = plan.execute(...)
> 
> so that the "plan" is more like a statement handle that one finds in
> other APIs.
> 
> This ended up being very easy to implement, so I'm proposing to allow
> this new syntax as an alternative.
> 
> I came across this again as I was developing the background sessions API
> for PL/Python.  So I'm also wondering here which style people prefer so
> I can implement it there.

This patch applies cleanly at cccbdde.

Any Python folks out there who would like to take a crack at reviewing this?

-- 
-David
da...@pgmasters.net


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python: Add cursor and execute methods to plan object

[Peter Eisentraut]

Something that has been bothering me in PL/Python for a long time is the
non-object-oriented way in which plans are prepared and executed:

plan = plpy.prepare(...)
res = plpy.execute(plan, ...)

where plpy.execute() takes either a plan or a query string.

I think a better style would be

plan = plpy.prepare(...)
res = plan.execute(...)

so that the "plan" is more like a statement handle that one finds in
other APIs.

This ended up being very easy to implement, so I'm proposing to allow
this new syntax as an alternative.

I came across this again as I was developing the background sessions API
for PL/Python.  So I'm also wondering here which style people prefer so
I can implement it there.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 9dccf70110d9d5818318c651c2662f2b8f86b2bc Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Sat, 25 Feb 2017 08:42:25 -0500
Subject: [PATCH] PL/Python: Add cursor and execute methods to plan object

Instead of

plan = plpy.prepare(...)
res = plpy.execute(plan, ...)

you can now write

plan = plpy.prepare(...)
res = plan.execute(...)

or even

res = plpy.prepare(...).execute(...)

and similarly for the cursor() method.

This is more in object oriented style, and makes the hybrid nature of
the existing execute() function less confusing.
---
 doc/src/sgml/plpython.sgml| 14 --
 src/pl/plpython/expected/plpython_spi.out | 19 ---
 src/pl/plpython/plpy_cursorobject.c   |  3 +--
 src/pl/plpython/plpy_cursorobject.h   |  1 +
 src/pl/plpython/plpy_planobject.c | 31 +++
 src/pl/plpython/plpy_spi.c|  3 +--
 src/pl/plpython/plpy_spi.h|  1 +
 src/pl/plpython/sql/plpython_spi.sql  | 18 --
 8 files changed, 79 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 46397781be..6888ce1ae3 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -1048,6 +1048,14 @@ Database Access Functions
  
 
  
+  Alternatively, you can call the execute method on
+  the plan object:
+
+rv = plan.execute(["name"], 5)
+
+ 
+
+ 
   Query parameters and result row fields are converted between PostgreSQL
   and Python data types as described in .
  
@@ -1082,7 +1090,9 @@ Database Access Functions
   as plpy.execute (except for the row limit) and returns
   a cursor object, which allows you to process large result sets in smaller
   chunks.  As with plpy.execute, either a query string
-  or a plan object along with a list of arguments can be used.
+  or a plan object along with a list of arguments can be used, or
+  the cursor function can be called as a method of
+  the plan object.
  
 
  
@@ -1126,7 +1136,7 @@ Database Access Functions
 CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
 odd = 0
 plan = plpy.prepare("select num from largetable where num % $1 <> 0", ["integer"])
-rows = list(plpy.cursor(plan, [2]))
+rows = list(plpy.cursor(plan, [2]))  # or: = list(plan.cursor([2]))
 
 return len(rows)
 $$ LANGUAGE plpythonu;
diff --git a/src/pl/plpython/expected/plpython_spi.out b/src/pl/plpython/expected/plpython_spi.out
index 0d78ca1de4..e54dca9e2e 100644
--- a/src/pl/plpython/expected/plpython_spi.out
+++ b/src/pl/plpython/expected/plpython_spi.out
@@ -31,6 +31,19 @@ except Exception, ex:
 return None
 '
 	LANGUAGE plpythonu;
+CREATE FUNCTION spi_prepared_plan_test_two(a text) RETURNS text
+	AS
+'if "myplan" not in SD:
+	q = "SELECT count(*) FROM users WHERE lname = $1"
+	SD["myplan"] = plpy.prepare(q, [ "text" ])
+try:
+	rv = SD["myplan"].execute([a])
+	return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
+except Exception, ex:
+	plpy.error(str(ex))
+return None
+'
+	LANGUAGE plpythonu;
 CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text
 	AS
 'if "myplan" not in SD:
@@ -80,8 +93,8 @@ select spi_prepared_plan_test_one('doe');
  there are 3 does
 (1 row)
 
-select spi_prepared_plan_test_one('smith');
- spi_prepared_plan_test_one 
+select spi_prepared_plan_test_two('smith');
+ spi_prepared_plan_test_two 
 
  there are 1 smiths
 (1 row)
@@ -372,7 +385,7 @@ plan = plpy.prepare(
 ["text"])
 for row in plpy.cursor(plan, ["w"]):
 yield row['fname']
-for row in plpy.cursor(plan, ["j"]):
+for row in plan.cursor(["j"]):
 yield row['fname']
 $$ LANGUAGE plpythonu;
 CREATE FUNCTION cursor_plan_wrong_args() RETURNS SETOF text AS $$
diff --git a/src/pl/plpython/plpy_cursorobject.c b/src/pl/plpython/plpy_cursorobject.c
index 7bb8992148..18e689f141 100644
--- a/src/pl/plpython/plpy_cursorobject.c
+++ b/src/pl/plpython/plpy_cursorobject.c
@@ -25,7 +25,6 @@
 
 
 static PyObject *PLy_cursor_query(const char *query);
-static PyObject *PLy_cursor_

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

2016-10-26 10:03 GMT+02:00 Heikki Linnakangas :

> On 10/24/2016 10:33 PM, Pavel Stehule wrote:
>
>> Hi
>>
>> 2016-10-14 10:53 GMT+02:00 Heikki Linnakangas :
>>
>> Please review. Are the docs and the error messages now clear enough on
>>> this? We'll need a mention in the release notes too, when it's time for
>>> that.
>>>
>>
>> The error message is clear.
>>
>
> Ok, great!
>
> I tested patches - and the regression test is broken (is not actualized)
>>
>
> Ah, fixed.
>
> There are no new tests for multidimensional array of composites - there is
>> only new negative test.
>>
>
> Added one.
>
> Thanks for the review! Committed, with those little fixes, and some little
> last-minute comment tweaks.
>

Thank you very much

nice feature

Regards

Pavel


>
> - Heikki
>
>

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 10/24/2016 10:33 PM, Pavel Stehule wrote:

Hi

2016-10-14 10:53 GMT+02:00 Heikki Linnakangas :


Please review. Are the docs and the error messages now clear enough on
this? We'll need a mention in the release notes too, when it's time for
that.


The error message is clear.


Ok, great!


I tested patches - and the regression test is broken (is not actualized)


Ah, fixed.


There are no new tests for multidimensional array of composites - there is
only new negative test.


Added one.

Thanks for the review! Committed, with those little fixes, and some 
little last-minute comment tweaks.


- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

Hi

2016-10-14 10:53 GMT+02:00 Heikki Linnakangas :

> On 10/11/2016 08:56 AM, Pavel Stehule wrote:
>
>> 2016-10-11 7:49 GMT+02:00 Heikki Linnakangas :
>>
>> Unfortunately there are cases that are fundamentally ambiguous.
>>>
>>> create type comptype as (intarray int[]);
>>> create function array_return() returns comptype[] as $$
>>>   return 1;
>>> $$ language plpython;
>>>
>>> What does the function return? It could be two-dimension array of
>>> comptype, with a single-dimension intarray, or a single-dimension
>>> comptype,
>>> with a two-dimension intarray.
>>>
>>> We could resolve it for simpler cases, but not the general case. The
>>> simple cases would probably cover most things people do in practice. But
>>> if
>>> the distinction between a tuple and a list feels natural to Python
>>> programmers, I think it would be more clear in the long run to have
>>> people
>>> adjust their applications.
>>>
>>
>> I agree. The distinction is natural - and it is our issue, so we don't
>> distinguish strongly.
>>
>
> Ok, let's do that then. Here is a patch set that does that. The first is
> the main patch. The second patch adds some code to give a hint, if you do
> that thing that whose behavior changed. That code isn't very pretty, but I
> think a good error message is absolutely required, if we are to make this
> change. Does anyone have better suggestions on how to catch the common
> cases of that?
>
> Please review. Are the docs and the error messages now clear enough on
> this? We'll need a mention in the release notes too, when it's time for
> that.
>

The error message is clear.

I tested patches - and the regression test is broken (is not actualized)

+ -- Starting with PostgreSQL 10, a composite type in an array cannot be
represented as
+ -- a Python list, because it's ambiguous with multi-dimensional arrays.
So this
+ -- throws an error now. The error should contain a useful hint on the
issue.
+ CREATE FUNCTION composite_type_as_list()  RETURNS type_record[] AS $$
+   return [['first', 1]];
+ $$ LANGUAGE plpythonu;
+ SELECT * FROM composite_type_as_list();
+ ERROR:  malformed record literal: "first"
+ DETAIL:  Missing left parenthesis.
+ HINT:  To return a composite type in an array, return the composite type
as a Python tuple, e.g. "[('foo')]"
+ CONTEXT:  while creating return value
+ PL/Python function "composite_type_as_list"

I tested Pyhon 3.5 and 2.7 and there are not any other issues

There are no new tests for multidimensional array of composites - there is
only new negative test.

Regards

Pavel


>
> - Heikki
>
>

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 14 October 2016 19:18:01 EEST, Jim Nasby  wrote:
>On 10/14/16 3:53 AM, Heikki Linnakangas wrote:
>> Composite types in arrays must now be returned as
>> Python tuples, not lists, to resolve the ambiguity. I.e. "[(col1,
>col2),
>> (col1, col2)]".
>
>Shouldn't dicts be allowed as well? 

Ah yes, dicts are also allowed, as before. And strings. The only change is that 
a list is interpreted as an array dimension, instead of a composite type.

- Heikki



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Jim Nasby]

On 10/14/16 3:53 AM, Heikki Linnakangas wrote:

Composite types in arrays must now be returned as
Python tuples, not lists, to resolve the ambiguity. I.e. "[(col1, col2),
(col1, col2)]".


Shouldn't dicts be allowed as well? I'm not sure they would 
automatically be considered as tuples (unlike something that extends 
tuples, such as namedtuples).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 10/11/2016 08:56 AM, Pavel Stehule wrote:

2016-10-11 7:49 GMT+02:00 Heikki Linnakangas :


Unfortunately there are cases that are fundamentally ambiguous.

create type comptype as (intarray int[]);
create function array_return() returns comptype[] as $$
  return 1;
$$ language plpython;

What does the function return? It could be two-dimension array of
comptype, with a single-dimension intarray, or a single-dimension comptype,
with a two-dimension intarray.

We could resolve it for simpler cases, but not the general case. The
simple cases would probably cover most things people do in practice. But if
the distinction between a tuple and a list feels natural to Python
programmers, I think it would be more clear in the long run to have people
adjust their applications.


I agree. The distinction is natural - and it is our issue, so we don't
distinguish strongly.


Ok, let's do that then. Here is a patch set that does that. The first is 
the main patch. The second patch adds some code to give a hint, if you 
do that thing that whose behavior changed. That code isn't very pretty, 
but I think a good error message is absolutely required, if we are to 
make this change. Does anyone have better suggestions on how to catch 
the common cases of that?


Please review. Are the docs and the error messages now clear enough on 
this? We'll need a mention in the release notes too, when it's time for 
that.


- Heikki

>From 945b33bd77fae0605ae9ff9d9282ab543cb16ec8 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas 
Date: Fri, 14 Oct 2016 11:51:45 +0300
Subject: [PATCH 1/2] Support multi-dimensional arrays in PL/python.

Multi-dimensional arrays can now be used as arguments to a PL/python function
(used to throw an error), and they can be returned as nested Python lists.

This makes a backwards-incompatible change to the handling of composite
types in arrays. Previously, you could return an array of composite types
as "[[col1, col2], [col1, col2]]", but now that is interpreted as a two-
dimensional array. Composite types in arrays must now be returned as
Python tuples, not lists, to resolve the ambiguity. I.e. "[(col1, col2),
(col1, col2)]".

To avoid breaking backwards-compatibility, when not necessary, () is still
accepted for arrays at the top-level, but it is always treated as a
single-dimensional array. Likewise, [] is still accepted for composite types,
when they are not in an array. Update the documentation to recommend using []
for arrays, and () for composite types, with a mention that those other things
are also accepted.

This needs to be mentioned in the release notes.

Alexey Grishchenko, Dave Cramer and me.

Discussion: 
---
 doc/src/sgml/plpython.sgml  |  41 +++-
 src/pl/plpython/expected/plpython_composite.out |   6 +-
 src/pl/plpython/expected/plpython_types.out | 151 -
 src/pl/plpython/expected/plpython_types_3.out   | 151 -
 src/pl/plpython/plpy_typeio.c   | 280 
 src/pl/plpython/sql/plpython_composite.sql  |   6 +-
 src/pl/plpython/sql/plpython_types.sql  |  86 
 7 files changed, 658 insertions(+), 63 deletions(-)

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index bb69c75..4639778 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -451,13 +451,13 @@ $$ LANGUAGE plpythonu;
   
SQL array values are passed into PL/Python as a Python list.  To
return an SQL array value out of a PL/Python function, return a
-   Python sequence, for example a list or tuple:
+   Python list:
 
 
 CREATE FUNCTION return_arr()
   RETURNS int[]
 AS $$
-return (1, 2, 3, 4, 5)
+return [1, 2, 3, 4, 5]
 $$ LANGUAGE plpythonu;
 
 SELECT return_arr();
@@ -467,6 +467,34 @@ SELECT return_arr();
 (1 row)
 
 
+   Multidimensional arrays are passed into PL/Python as nested Python lists.
+   A 2-dimensional array is a list of lists, for example. When returning
+   a multi-dimensional SQL array out of a PL/Python function, the inner
+   lists at each level must all be of the same size. For example:
+
+
+CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
+INFO:  ([[1, 2, 3], [4, 5, 6]], )
+ test_type_conversion_array_int4 
+-
+ {{1,2,3},{4,5,6}}
+(1 row)
+
+
+   Other Python sequences, like tuples, are also accepted for
+   backwards-compatibility with PostgreSQL versions 9.6 and below, when
+   multi-dimensional arrays were not supported. However, they are always
+   treated as one-dimensional arrays, because they are ambiguous with
+   composite types. For the same reason, when a composite type is used in a
+   multi-dimensional array, it must be represented by a tuple, rather than a
+   list.
+  
+  
Note that in Python, strings are sequences, which can have
   

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

2016-10-11 7:49 GMT+02:00 Heikki Linnakangas :

> On 10/10/2016 08:42 PM, Pavel Stehule wrote:
>
>> 2016-10-10 12:31 GMT+02:00 Heikki Linnakangas :
>>
>> On 10/01/2016 02:45 AM, Jim Nasby wrote:
>>>
>>> On 9/29/16 1:51 PM, Heikki Linnakangas wrote:

 Now, back to multi-dimensional arrays. I can see that the Sequence
> representation is problematic, with arrays, because if you have a
> python
> list of lists, like [[1, 2]], it's not immediately clear if that's a
> one-dimensional array of tuples, or two-dimensional array of integers.
> Then again, we do have the type definitions available. So is it really
> ambiguous?
>
>
 [[1,2]] is a list of lists...
 In [4]: b=[[1,2]]

 In [5]: type(b)
 Out[5]: list

 In [6]: type(b[0])
 Out[6]: list

 If you want a list of tuples...
 In [7]: c=[(1,2)]

 In [8]: type(c)
 Out[8]: list

 In [9]: type(c[0])
 Out[9]: tuple


>>> Hmm, so we would start to treat lists and tuples differently? A Python
>>> list would be converted into an array, and a Python tuple would be
>>> converted into a composite type. That does make a lot of sense. The only
>>> problem is that it's not backwards-compatible. A PL/python function that
>>> returns an SQL array of rows, and does that by returning Python list of
>>> lists, it would start failing.
>>>
>>
>> is not possible do decision in last moment - on PL/Postgres interface?
>> There the expected type should be known.
>>
>
> Unfortunately there are cases that are fundamentally ambiguous.
>
> create type comptype as (intarray int[]);
> create function array_return() returns comptype[] as $$
>   return 1;
> $$ language plpython;
>
> What does the function return? It could be two-dimension array of
> comptype, with a single-dimension intarray, or a single-dimension comptype,
> with a two-dimension intarray.
>
> We could resolve it for simpler cases, but not the general case. The
> simple cases would probably cover most things people do in practice. But if
> the distinction between a tuple and a list feels natural to Python
> programmers, I think it would be more clear in the long run to have people
> adjust their applications.
>

I agree. The distinction is natural - and it is our issue, so we don't
distinguish strongly.

Regards

Pavel


>
> - Heikki
>
>

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 10/10/2016 08:42 PM, Pavel Stehule wrote:

2016-10-10 12:31 GMT+02:00 Heikki Linnakangas :


On 10/01/2016 02:45 AM, Jim Nasby wrote:


On 9/29/16 1:51 PM, Heikki Linnakangas wrote:


Now, back to multi-dimensional arrays. I can see that the Sequence
representation is problematic, with arrays, because if you have a python
list of lists, like [[1, 2]], it's not immediately clear if that's a
one-dimensional array of tuples, or two-dimensional array of integers.
Then again, we do have the type definitions available. So is it really
ambiguous?



[[1,2]] is a list of lists...
In [4]: b=[[1,2]]

In [5]: type(b)
Out[5]: list

In [6]: type(b[0])
Out[6]: list

If you want a list of tuples...
In [7]: c=[(1,2)]

In [8]: type(c)
Out[8]: list

In [9]: type(c[0])
Out[9]: tuple



Hmm, so we would start to treat lists and tuples differently? A Python
list would be converted into an array, and a Python tuple would be
converted into a composite type. That does make a lot of sense. The only
problem is that it's not backwards-compatible. A PL/python function that
returns an SQL array of rows, and does that by returning Python list of
lists, it would start failing.


is not possible do decision in last moment - on PL/Postgres interface?
There the expected type should be known.


Unfortunately there are cases that are fundamentally ambiguous.

create type comptype as (intarray int[]);
create function array_return() returns comptype[] as $$
  return 1;
$$ language plpython;

What does the function return? It could be two-dimension array of 
comptype, with a single-dimension intarray, or a single-dimension 
comptype, with a two-dimension intarray.


We could resolve it for simpler cases, but not the general case. The 
simple cases would probably cover most things people do in practice. But 
if the distinction between a tuple and a list feels natural to Python 
programmers, I think it would be more clear in the long run to have 
people adjust their applications.


- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

On 10 October 2016 at 13:42, Pavel Stehule  wrote:

>
>
> 2016-10-10 12:31 GMT+02:00 Heikki Linnakangas :
>
>> On 10/01/2016 02:45 AM, Jim Nasby wrote:
>>
>>> On 9/29/16 1:51 PM, Heikki Linnakangas wrote:
>>>
 Now, back to multi-dimensional arrays. I can see that the Sequence
 representation is problematic, with arrays, because if you have a python
 list of lists, like [[1, 2]], it's not immediately clear if that's a
 one-dimensional array of tuples, or two-dimensional array of integers.
 Then again, we do have the type definitions available. So is it really
 ambiguous?

>>>
>>> [[1,2]] is a list of lists...
>>> In [4]: b=[[1,2]]
>>>
>>> In [5]: type(b)
>>> Out[5]: list
>>>
>>> In [6]: type(b[0])
>>> Out[6]: list
>>>
>>> If you want a list of tuples...
>>> In [7]: c=[(1,2)]
>>>
>>> In [8]: type(c)
>>> Out[8]: list
>>>
>>> In [9]: type(c[0])
>>> Out[9]: tuple
>>>
>>
>> Hmm, so we would start to treat lists and tuples differently? A Python
>> list would be converted into an array, and a Python tuple would be
>> converted into a composite type. That does make a lot of sense. The only
>> problem is that it's not backwards-compatible. A PL/python function that
>> returns an SQL array of rows, and does that by returning Python list of
>> lists, it would start failing.
>>
>
> is not possible do decision in last moment - on PL/Postgres interface?
> There the expected type should be known.
>
> Regards
>
> Pavel
>
>
>>
>> I think we should bite the bullet and do that anyway. As long as it's
>> clearly documented, and the error message you get contains a clear hint on
>> how to fix it, I don't think it would be too painful to adjust existing
>> application.
>>
>> We could continue to accept a Python list for a plain composite type,
>> this would only affect arrays of composite types.
>>
>> I don't use PL/python much myself, so I don't feel qualified to make the
>> call, though. Any 3rd opinions?
>
>
Can't you determine the correct output based on the function output
definition ?

For instance if the function output was an array type then we would return
the list as an array
if the function output was a set of then we return tuples ?


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

2016-10-10 12:31 GMT+02:00 Heikki Linnakangas :

> On 10/01/2016 02:45 AM, Jim Nasby wrote:
>
>> On 9/29/16 1:51 PM, Heikki Linnakangas wrote:
>>
>>> Now, back to multi-dimensional arrays. I can see that the Sequence
>>> representation is problematic, with arrays, because if you have a python
>>> list of lists, like [[1, 2]], it's not immediately clear if that's a
>>> one-dimensional array of tuples, or two-dimensional array of integers.
>>> Then again, we do have the type definitions available. So is it really
>>> ambiguous?
>>>
>>
>> [[1,2]] is a list of lists...
>> In [4]: b=[[1,2]]
>>
>> In [5]: type(b)
>> Out[5]: list
>>
>> In [6]: type(b[0])
>> Out[6]: list
>>
>> If you want a list of tuples...
>> In [7]: c=[(1,2)]
>>
>> In [8]: type(c)
>> Out[8]: list
>>
>> In [9]: type(c[0])
>> Out[9]: tuple
>>
>
> Hmm, so we would start to treat lists and tuples differently? A Python
> list would be converted into an array, and a Python tuple would be
> converted into a composite type. That does make a lot of sense. The only
> problem is that it's not backwards-compatible. A PL/python function that
> returns an SQL array of rows, and does that by returning Python list of
> lists, it would start failing.
>

is not possible do decision in last moment - on PL/Postgres interface?
There the expected type should be known.

Regards

Pavel


>
> I think we should bite the bullet and do that anyway. As long as it's
> clearly documented, and the error message you get contains a clear hint on
> how to fix it, I don't think it would be too painful to adjust existing
> application.
>
> We could continue to accept a Python list for a plain composite type, this
> would only affect arrays of composite types.
>
> I don't use PL/python much myself, so I don't feel qualified to make the
> call, though. Any 3rd opinions?
>
> - Heikki
>
>

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 10/01/2016 02:45 AM, Jim Nasby wrote:

On 9/29/16 1:51 PM, Heikki Linnakangas wrote:

Now, back to multi-dimensional arrays. I can see that the Sequence
representation is problematic, with arrays, because if you have a python
list of lists, like [[1, 2]], it's not immediately clear if that's a
one-dimensional array of tuples, or two-dimensional array of integers.
Then again, we do have the type definitions available. So is it really
ambiguous?


[[1,2]] is a list of lists...
In [4]: b=[[1,2]]

In [5]: type(b)
Out[5]: list

In [6]: type(b[0])
Out[6]: list

If you want a list of tuples...
In [7]: c=[(1,2)]

In [8]: type(c)
Out[8]: list

In [9]: type(c[0])
Out[9]: tuple


Hmm, so we would start to treat lists and tuples differently? A Python 
list would be converted into an array, and a Python tuple would be 
converted into a composite type. That does make a lot of sense. The only 
problem is that it's not backwards-compatible. A PL/python function that 
returns an SQL array of rows, and does that by returning Python list of 
lists, it would start failing.


I think we should bite the bullet and do that anyway. As long as it's 
clearly documented, and the error message you get contains a clear hint 
on how to fix it, I don't think it would be too painful to adjust 
existing application.


We could continue to accept a Python list for a plain composite type, 
this would only affect arrays of composite types.


I don't use PL/python much myself, so I don't feel qualified to make the 
call, though. Any 3rd opinions?


- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Michael Paquier]

On Sat, Oct 1, 2016 at 8:45 AM, Jim Nasby  wrote:
> On 9/29/16 1:51 PM, Heikki Linnakangas wrote:
>>
>> Jim, I was confused, but you agreed with me. Were you also confused, or
>> am I missing something?
>
>
> I was confused by inputs:

I have marked the patch as returned with feedback. Or Heikki, do you
plan on looking at it more and commit soon?
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Jim Nasby]

On 9/29/16 1:51 PM, Heikki Linnakangas wrote:

Jim, I was confused, but you agreed with me. Were you also confused, or
am I missing something?


I was confused by inputs:

CREATE FUNCTION repr(i foo[]) RETURNS text LANGUAGE plpythonu AS 
$$return repr(i)$$;

select repr(array[row(1,2)::foo, row(3,4)::foo]);
repr

 ['(1,2)', '(3,4)']
(1 row)

(in ipython...)

In [1]: i=['(1,2)', '(3,4)']

In [2]: type(i)
Out[2]: list

In [3]: type(i[0])
Out[3]: str

I wonder if your examples work only


Now, back to multi-dimensional arrays. I can see that the Sequence
representation is problematic, with arrays, because if you have a python
list of lists, like [[1, 2]], it's not immediately clear if that's a
one-dimensional array of tuples, or two-dimensional array of integers.
Then again, we do have the type definitions available. So is it really
ambiguous?


[[1,2]] is a list of lists...
In [4]: b=[[1,2]]

In [5]: type(b)
Out[5]: list

In [6]: type(b[0])
Out[6]: list

If you want a list of tuples...
In [7]: c=[(1,2)]

In [8]: type(c)
Out[8]: list

In [9]: type(c[0])
Out[9]: tuple
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 09/23/2016 10:27 PM, Jim Nasby wrote:

On 9/23/16 2:42 AM, Heikki Linnakangas wrote:

How do we handle single-dimensional arrays of composite types at the
moment? At a quick glance, it seems that the composite types are just
treated like strings, when they're in an array. That's probably OK, but
it means that there's nothing special about composite types in
multi-dimensional arrays. In any case, we should mention that in the docs.


That is how they're handled, but I'd really like to change that. I've
held off because I don't know how to handle the backwards
incompatibility that would introduce. (I've been wondering if we might
add a facility to allow specifying default TRANSFORMs that should be
used for specific data types in specific languages.)

The converse case (a composite with arrays) suffers the same problem
(array is just treated as a string).


I take that back, I don't know what I was talking about. Without this 
patch, an array of composite types can be returned, using any of the 
three representations for the composite type explained in the docs: a 
string, a sequence, or a dictionary. So, all these work, and return the 
same value:


create table foo (a int4, b int4);

CREATE FUNCTION comp_array_string() RETURNS foo[] AS $$
return ["(1, 2)"]
$$ LANGUAGE plpythonu;

CREATE FUNCTION comp_array_sequence() RETURNS foo[] AS $$
return [[1, 2]]
$$ LANGUAGE plpythonu;

CREATE FUNCTION comp_array_dict() RETURNS foo[] AS $$
return [{"a": 1, "b": 2}]
$$ LANGUAGE plpythonu;

Jim, I was confused, but you agreed with me. Were you also confused, or 
am I missing something?


Now, back to multi-dimensional arrays. I can see that the Sequence 
representation is problematic, with arrays, because if you have a python 
list of lists, like [[1, 2]], it's not immediately clear if that's a 
one-dimensional array of tuples, or two-dimensional array of integers. 
Then again, we do have the type definitions available. So is it really 
ambiguous?


The string and dict representations don't have that ambiguity at all, so 
I don't see why we wouldn't support those, at least.


- Heikki



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

On 27 September 2016 at 14:58, Heikki Linnakangas  wrote:

> On 09/27/2016 02:04 PM, Dave Cramer wrote:
>
>> On 26 September 2016 at 14:52, Dave Cramer  wrote:
>>
>>> This crashes with arrays with non-default lower bounds:

 postgres=# SELECT * FROM test_type_conversion_array_int
 4('[2:4]={1,2,3}');
 INFO:  ([1, 2, ], )
 server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

 Attached patch fixes this bug, and adds a test for it.

>>>
> I spent some more time massaging this:
>
> * Changed the loops from iterative to recursive style. I think this indeed
> is slightly easier to understand.
>
> * Fixed another segfault, with too deeply nested lists:
>
> CREATE or replace FUNCTION test_type_conversion_mdarray_toodeep() RETURNS
> int[] AS $$
> return [[1]]
> $$ LANGUAGE plpythonu;
>
> * Also, in PLySequence_ToArray(), we must check that the 'len' of the
> array doesn't overflow.
>
> * Fixed reference leak in the loop in PLySequence_ToArray() to count the
> number of dimensions.
>
> I'd like to see some updates to the docs for this. The manual doesn't
 currently say anything about multi-dimensional arrays in pl/python, but
 it
 should've mentioned that they're not supported. Now that it is
 supported,
 should mention that, and explain briefly that a multi-dimensional array
 is
 mapped to a python list of lists.

 If the code passes I'll fix the docs
>>>
>>
> Please do, thanks!
>
>
see attached



Dave Cramer

da...@postgresintl.com
www.postgresintl.com


0002-WIP-Multi-dimensional-arrays-in-PL-python.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 09/27/2016 02:04 PM, Dave Cramer wrote:

On 26 September 2016 at 14:52, Dave Cramer  wrote:

This crashes with arrays with non-default lower bounds:

postgres=# SELECT * FROM test_type_conversion_array_int
4('[2:4]={1,2,3}');
INFO:  ([1, 2, ], )
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Attached patch fixes this bug, and adds a test for it.


I spent some more time massaging this:

* Changed the loops from iterative to recursive style. I think this 
indeed is slightly easier to understand.


* Fixed another segfault, with too deeply nested lists:

CREATE or replace FUNCTION test_type_conversion_mdarray_toodeep() 
RETURNS int[] AS $$

return [[1]]
$$ LANGUAGE plpythonu;

* Also, in PLySequence_ToArray(), we must check that the 'len' of the 
array doesn't overflow.


* Fixed reference leak in the loop in PLySequence_ToArray() to count the 
number of dimensions.



I'd like to see some updates to the docs for this. The manual doesn't
currently say anything about multi-dimensional arrays in pl/python, but it
should've mentioned that they're not supported. Now that it is supported,
should mention that, and explain briefly that a multi-dimensional array is
mapped to a python list of lists.


If the code passes I'll fix the docs


Please do, thanks!

- Heikki

>From 6bcff9d1787fc645118a3ecbb71d1ef7561a5bfd Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas 
Date: Tue, 27 Sep 2016 21:53:17 +0300
Subject: [PATCH 1/1] WIP: Multi-dimensional arrays in PL/python

---
 src/pl/plpython/expected/plpython_types.out   | 151 +-
 src/pl/plpython/expected/plpython_types_3.out | 151 +-
 src/pl/plpython/plpy_typeio.c | 272 +-
 src/pl/plpython/sql/plpython_types.sql|  86 
 4 files changed, 608 insertions(+), 52 deletions(-)

diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index f0b6abd..947244e 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -537,9 +537,133 @@ INFO:  (None, )
 (1 row)
 
 SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
-ERROR:  cannot convert multidimensional array to Python list
-DETAIL:  PL/Python only supports one-dimensional arrays.
-CONTEXT:  PL/Python function "test_type_conversion_array_int4"
+INFO:  ([[1, 2, 3], [4, 5, 6]], )
+ test_type_conversion_array_int4 
+-
+ {{1,2,3},{4,5,6}}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]);
+INFO:  ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], )
+  test_type_conversion_array_int4  
+---
+ {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}');
+INFO:  ([1, 2, 3], )
+ test_type_conversion_array_int4 
+-
+ {1,2,3}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_int8(x int8[]) RETURNS int8[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_int8(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]::int8[]);
+INFO:  ([[[1L, 2L, None], [None, 5L, 6L]], [[None, 8L, 9L], [10L, 11L, 12L]]], )
+  test_type_conversion_array_int8  
+---
+ {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_float4(x float4[]) RETURNS float4[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_float4(ARRAY[[[1.2,2.3,NULL],[NULL,5.7,6.8]],[[NULL,8.9,9.345],[10.123,11.456,12.6768]]]::float4[]);
+INFO:  ([[[1.200476837158, 2.29952316284, None], [None, 5.69809265137, 6.80190734863]], [[None, 8.89618530273, 9.345000267028809], [10.123000144958496, 11.456000328063965, 12.676799774169922]]], )
+  test_type_conversion_array_float4   
+--
+ {{{1.2,2.3,NULL},{NULL,5.7,6.8}},{{NULL,8.9,9.345},{10.123,11.456,12.6768}}}
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_float8(x float8[]) RETURNS float8[] AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_float8(ARRAY[[[1.2,2.3,NULL],[NULL,5.7,6.8]],[[NULL,8.9,9.345],[10.123,11.456,12.6768]]]::float8[]);
+INFO:  ([[[1.2, 2.3, None], [None, 5.7, 6.8]], [[None, 8.9, 9.345], [10.123, 11.456, 12.6768]]], )
+  test_type_conversion_array_float8   
+--
+ {{{1.2,2.3,NULL},{NULL,5.7,6.8}},{{NULL,8.

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

On 26 September 2016 at 14:52, Dave Cramer  wrote:

>
>
>
>>
>> This crashes with arrays with non-default lower bounds:
>>
>> postgres=# SELECT * FROM test_type_conversion_array_int
>> 4('[2:4]={1,2,3}');
>> INFO:  ([1, 2, ], )
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>>
>> Attached patch fixes this bug, and adds a test for it.
>
>>
>> I'd like to see some updates to the docs for this. The manual doesn't
>> currently say anything about multi-dimensional arrays in pl/python, but it
>> should've mentioned that they're not supported. Now that it is supported,
>> should mention that, and explain briefly that a multi-dimensional array is
>> mapped to a python list of lists.
>>
>> If the code passes I'll fix the docs
>
>> It seems we don't have any mention in the docs about arrays with
>> non-default lower-bounds ATM. That's not this patch's fault, but it would
>> be good to point out that the lower bounds are discarded when an array is
>> passed to python.
>>
>> I find the loop in PLyList_FromArray() quite difficult to understand. Are
>> the comments there mixing up the "inner" and "outer" dimensions? I wonder
>> if that would be easier to read, if it was written in a recursive-style,
>> rather than iterative with stacks for the dimensions.
>>
>> Yes, it is fairly convoluted.
>
>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>
>


PL-Python-adding-support-for-multi-dimensional-arrays-20160926.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

>
> This crashes with arrays with non-default lower bounds:
>
> postgres=# SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}');
> INFO:  ([1, 2, ], )
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
>
> Attached patch fixes this bug, and adds a test for it.

>
> I'd like to see some updates to the docs for this. The manual doesn't
> currently say anything about multi-dimensional arrays in pl/python, but it
> should've mentioned that they're not supported. Now that it is supported,
> should mention that, and explain briefly that a multi-dimensional array is
> mapped to a python list of lists.
>
> If the code passes I'll fix the docs

> It seems we don't have any mention in the docs about arrays with
> non-default lower-bounds ATM. That's not this patch's fault, but it would
> be good to point out that the lower bounds are discarded when an array is
> passed to python.
>
> I find the loop in PLyList_FromArray() quite difficult to understand. Are
> the comments there mixing up the "inner" and "outer" dimensions? I wonder
> if that would be easier to read, if it was written in a recursive-style,
> rather than iterative with stacks for the dimensions.
>
> Yes, it is fairly convoluted.


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Jim Nasby]

On 9/23/16 2:42 AM, Heikki Linnakangas wrote:

How do we handle single-dimensional arrays of composite types at the
moment? At a quick glance, it seems that the composite types are just
treated like strings, when they're in an array. That's probably OK, but
it means that there's nothing special about composite types in
multi-dimensional arrays. In any case, we should mention that in the docs.


That is how they're handled, but I'd really like to change that. I've 
held off because I don't know how to handle the backwards 
incompatibility that would introduce. (I've been wondering if we might 
add a facility to allow specifying default TRANSFORMs that should be 
used for specific data types in specific languages.)


The converse case (a composite with arrays) suffers the same problem 
(array is just treated as a string).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Heikki Linnakangas]

On 09/22/2016 10:28 AM, Pavel Stehule wrote:

Now, the tests are enough - so I'll mark this patch as ready for commiters.

I had to fix tests - there was lot of white spaces, and the result for
python3 was missing


Thanks Pavel!

This crashes with arrays with non-default lower bounds:

postgres=# SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}');
INFO:  ([1, 2, ], )
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.


I'd like to see some updates to the docs for this. The manual doesn't 
currently say anything about multi-dimensional arrays in pl/python, but 
it should've mentioned that they're not supported. Now that it is 
supported, should mention that, and explain briefly that a 
multi-dimensional array is mapped to a python list of lists.


It seems we don't have any mention in the docs about arrays with 
non-default lower-bounds ATM. That's not this patch's fault, but it 
would be good to point out that the lower bounds are discarded when an 
array is passed to python.


I find the loop in PLyList_FromArray() quite difficult to understand. 
Are the comments there mixing up the "inner" and "outer" dimensions? I 
wonder if that would be easier to read, if it was written in a 
recursive-style, rather than iterative with stacks for the dimensions.


On 08/03/2016 02:49 PM, Alexey Grishchenko wrote:

This patch does not support multi-dimensional arrays of composite types, as
composite types in Python might be represented as iterators and there is no
obvious way to find out when the nested array stops and composite type
structure starts. For example, if we have a composite type of (int, text),
we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'], [4,'d'] ] ]", and
it is hard to find out that the first two lists are lists, and the third
one represents structure. Things are getting even more complex when you
have arrays as members of composite type. This is why I think this
limitation is reasonable.


How do we handle single-dimensional arrays of composite types at the 
moment? At a quick glance, it seems that the composite types are just 
treated like strings, when they're in an array. That's probably OK, but 
it means that there's nothing special about composite types in 
multi-dimensional arrays. In any case, we should mention that in the docs.


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

Hi

2016-09-21 19:53 GMT+02:00 Dave Cramer :

>
> On 18 September 2016 at 09:27, Dave Cramer  wrote:
>
>>
>> On 10 August 2016 at 01:53, Pavel Stehule 
>> wrote:
>>
>>> Hi
>>>
>>> 2016-08-03 13:54 GMT+02:00 Alexey Grishchenko :
>>>
 On Wed, Aug 3, 2016 at 12:49 PM, Alexey Grishchenko <
 agrishche...@pivotal.io> wrote:

> Hi
>
> Current implementation of PL/Python does not allow the use of
> multi-dimensional arrays, for both input and output parameters. This 
> forces
> end users to introduce workarounds like casting arrays to text before
> passing them to the functions and parsing them after, which is an
> error-prone approach
>
> This patch adds support for multi-dimensional arrays as both input and
> output parameters for PL/Python functions. The number of dimensions
> supported is limited by Postgres MAXDIM macrovariable, by default equal to
> 6. Both input and output multi-dimensional arrays should have fixed
> dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
> represent MxNxK cube, etc.
>
> This patch does not support multi-dimensional arrays of composite
> types, as composite types in Python might be represented as iterators and
> there is no obvious way to find out when the nested array stops and
> composite type structure starts. For example, if we have a composite type
> of (int, text), we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'],
> [4,'d'] ] ]", and it is hard to find out that the first two lists are
> lists, and the third one represents structure. Things are getting even 
> more
> complex when you have arrays as members of composite type. This is why I
> think this limitation is reasonable.
>
> Given the function:
>
> CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS
> int4[] AS $$
> plpy.info(x, type(x))
> return x
> $$ LANGUAGE plpythonu;
>
> Before patch:
>
> # SELECT * FROM test_type_conversion_array_int
> 4(ARRAY[[1,2,3],[4,5,6]]);
> ERROR:  cannot convert multidimensional array to Python list
> DETAIL:  PL/Python only supports one-dimensional arrays.
> CONTEXT:  PL/Python function "test_type_conversion_array_int4"
>
>
> After patch:
>
> # SELECT * FROM test_type_conversion_array_int
> 4(ARRAY[[1,2,3],[4,5,6]]);
> INFO:  ([[1, 2, 3], [4, 5, 6]], )
>  test_type_conversion_array_int4
> -
>  {{1,2,3},{4,5,6}}
> (1 row)
>
>
> --
> Best regards,
> Alexey Grishchenko
>

 Also this patch incorporates the fix for https://www.postgresql.org
 /message-id/CAH38_tkwA5qgLV8zPN1OpPzhtkNKQb30n3xq-2NR9jUfv3q
 wHA%40mail.gmail.com, as they touch the same piece of code - array
 manipulation in PL/Python


>>> I am sending review of this patch:
>>>
>>> 1. The implemented functionality is clearly benefit - passing MD arrays,
>>> pretty faster passing bigger arrays
>>> 2. I was able to use this patch cleanly without any errors or warnings
>>> 3. There is no any error or warning
>>> 4. All tests passed - I tested Python 2.7 and Python 3.5
>>> 5. The code is well commented and clean
>>> 6. For this new functionality the documentation is not necessary
>>>
>>> 7. I invite more regress tests for both directions (Python <-> Postgres)
>>> for more than two dimensions
>>>
>>> My only one objection is not enough regress tests - after fixing this
>>> patch will be ready for commiters.
>>>
>>
Now, the tests are enough - so I'll mark this patch as ready for commiters.

I had to fix tests - there was lot of white spaces, and the result for
python3 was missing

Regards

Pavel




>
>>> Good work, Alexey
>>>
>>> Thank you
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
 --
 Best regards,
 Alexey Grishchenko

>>>
>>>
>>
>> Pavel,
>>
>> I will pick this up.
>>
>>
>> Pavel,
>
> Please see attached patch which provides more test cases
>
> I just realized this patch contains the original patch as well. What is
> the protocol for sending in subsequent patches ?
>
>>
>> Dave Cramer
>>
>> da...@postgresintl.com
>> www.postgresintl.com
>>
>>
>
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index f0b6abd..296ef8b 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -537,9 +537,126 @@ INFO:  (None, )
 (1 row)
 
 SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
-ERROR:  cannot convert multidimensional array to Python list
-DETAIL:  PL/Python only supports one-dimensional arrays.
-CONTEXT:  PL/Python function "test_type_conversion_array_int4"
+INFO:  ([[1, 2, 3], [4, 5, 6]], )
+ test_type_conversion_array_int4 
+-
+ {{1,2,3},{4,5,6}}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

On 18 September 2016 at 09:27, Dave Cramer  wrote:

>
> On 10 August 2016 at 01:53, Pavel Stehule  wrote:
>
>> Hi
>>
>> 2016-08-03 13:54 GMT+02:00 Alexey Grishchenko :
>>
>>> On Wed, Aug 3, 2016 at 12:49 PM, Alexey Grishchenko <
>>> agrishche...@pivotal.io> wrote:
>>>
 Hi

 Current implementation of PL/Python does not allow the use of
 multi-dimensional arrays, for both input and output parameters. This forces
 end users to introduce workarounds like casting arrays to text before
 passing them to the functions and parsing them after, which is an
 error-prone approach

 This patch adds support for multi-dimensional arrays as both input and
 output parameters for PL/Python functions. The number of dimensions
 supported is limited by Postgres MAXDIM macrovariable, by default equal to
 6. Both input and output multi-dimensional arrays should have fixed
 dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
 represent MxNxK cube, etc.

 This patch does not support multi-dimensional arrays of composite
 types, as composite types in Python might be represented as iterators and
 there is no obvious way to find out when the nested array stops and
 composite type structure starts. For example, if we have a composite type
 of (int, text), we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'],
 [4,'d'] ] ]", and it is hard to find out that the first two lists are
 lists, and the third one represents structure. Things are getting even more
 complex when you have arrays as members of composite type. This is why I
 think this limitation is reasonable.

 Given the function:

 CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS
 int4[] AS $$
 plpy.info(x, type(x))
 return x
 $$ LANGUAGE plpythonu;

 Before patch:

 # SELECT * FROM test_type_conversion_array_int
 4(ARRAY[[1,2,3],[4,5,6]]);
 ERROR:  cannot convert multidimensional array to Python list
 DETAIL:  PL/Python only supports one-dimensional arrays.
 CONTEXT:  PL/Python function "test_type_conversion_array_int4"


 After patch:

 # SELECT * FROM test_type_conversion_array_int
 4(ARRAY[[1,2,3],[4,5,6]]);
 INFO:  ([[1, 2, 3], [4, 5, 6]], )
  test_type_conversion_array_int4
 -
  {{1,2,3},{4,5,6}}
 (1 row)


 --
 Best regards,
 Alexey Grishchenko

>>>
>>> Also this patch incorporates the fix for https://www.postgresql.org
>>> /message-id/CAH38_tkwA5qgLV8zPN1OpPzhtkNKQb30n3xq-2NR9jUfv3q
>>> wHA%40mail.gmail.com, as they touch the same piece of code - array
>>> manipulation in PL/Python
>>>
>>>
>> I am sending review of this patch:
>>
>> 1. The implemented functionality is clearly benefit - passing MD arrays,
>> pretty faster passing bigger arrays
>> 2. I was able to use this patch cleanly without any errors or warnings
>> 3. There is no any error or warning
>> 4. All tests passed - I tested Python 2.7 and Python 3.5
>> 5. The code is well commented and clean
>> 6. For this new functionality the documentation is not necessary
>>
>> 7. I invite more regress tests for both directions (Python <-> Postgres)
>> for more than two dimensions
>>
>> My only one objection is not enough regress tests - after fixing this
>> patch will be ready for commiters.
>>
>> Good work, Alexey
>>
>> Thank you
>>
>> Regards
>>
>> Pavel
>>
>>
>>> --
>>> Best regards,
>>> Alexey Grishchenko
>>>
>>
>>
>
> Pavel,
>
> I will pick this up.
>
>
> Pavel,

Please see attached patch which provides more test cases

I just realized this patch contains the original patch as well. What is the
protocol for sending in subsequent patches ?

>
> Dave Cramer
>
> da...@postgresintl.com
> www.postgresintl.com
>
>


0002-PL-Python-adding-support-for-multi-dimensional-arrays.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Dave Cramer]

On 10 August 2016 at 01:53, Pavel Stehule  wrote:

> Hi
>
> 2016-08-03 13:54 GMT+02:00 Alexey Grishchenko :
>
>> On Wed, Aug 3, 2016 at 12:49 PM, Alexey Grishchenko <
>> agrishche...@pivotal.io> wrote:
>>
>>> Hi
>>>
>>> Current implementation of PL/Python does not allow the use of
>>> multi-dimensional arrays, for both input and output parameters. This forces
>>> end users to introduce workarounds like casting arrays to text before
>>> passing them to the functions and parsing them after, which is an
>>> error-prone approach
>>>
>>> This patch adds support for multi-dimensional arrays as both input and
>>> output parameters for PL/Python functions. The number of dimensions
>>> supported is limited by Postgres MAXDIM macrovariable, by default equal to
>>> 6. Both input and output multi-dimensional arrays should have fixed
>>> dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
>>> represent MxNxK cube, etc.
>>>
>>> This patch does not support multi-dimensional arrays of composite types,
>>> as composite types in Python might be represented as iterators and there is
>>> no obvious way to find out when the nested array stops and composite type
>>> structure starts. For example, if we have a composite type of (int, text),
>>> we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'], [4,'d'] ] ]", and
>>> it is hard to find out that the first two lists are lists, and the third
>>> one represents structure. Things are getting even more complex when you
>>> have arrays as members of composite type. This is why I think this
>>> limitation is reasonable.
>>>
>>> Given the function:
>>>
>>> CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS
>>> int4[] AS $$
>>> plpy.info(x, type(x))
>>> return x
>>> $$ LANGUAGE plpythonu;
>>>
>>> Before patch:
>>>
>>> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
>>> ERROR:  cannot convert multidimensional array to Python list
>>> DETAIL:  PL/Python only supports one-dimensional arrays.
>>> CONTEXT:  PL/Python function "test_type_conversion_array_int4"
>>>
>>>
>>> After patch:
>>>
>>> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
>>> INFO:  ([[1, 2, 3], [4, 5, 6]], )
>>>  test_type_conversion_array_int4
>>> -
>>>  {{1,2,3},{4,5,6}}
>>> (1 row)
>>>
>>>
>>> --
>>> Best regards,
>>> Alexey Grishchenko
>>>
>>
>> Also this patch incorporates the fix for https://www.postgresql.org
>> /message-id/CAH38_tkwA5qgLV8zPN1OpPzhtkNKQb30n3xq-
>> 2NR9jUfv3qwHA%40mail.gmail.com, as they touch the same piece of code -
>> array manipulation in PL/Python
>>
>>
> I am sending review of this patch:
>
> 1. The implemented functionality is clearly benefit - passing MD arrays,
> pretty faster passing bigger arrays
> 2. I was able to use this patch cleanly without any errors or warnings
> 3. There is no any error or warning
> 4. All tests passed - I tested Python 2.7 and Python 3.5
> 5. The code is well commented and clean
> 6. For this new functionality the documentation is not necessary
>
> 7. I invite more regress tests for both directions (Python <-> Postgres)
> for more than two dimensions
>
> My only one objection is not enough regress tests - after fixing this
> patch will be ready for commiters.
>
> Good work, Alexey
>
> Thank you
>
> Regards
>
> Pavel
>
>
>> --
>> Best regards,
>> Alexey Grishchenko
>>
>
>

Pavel,

I will pick this up.



Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Pavel Stehule]

Hi

2016-08-03 13:54 GMT+02:00 Alexey Grishchenko :

> On Wed, Aug 3, 2016 at 12:49 PM, Alexey Grishchenko <
> agrishche...@pivotal.io> wrote:
>
>> Hi
>>
>> Current implementation of PL/Python does not allow the use of
>> multi-dimensional arrays, for both input and output parameters. This forces
>> end users to introduce workarounds like casting arrays to text before
>> passing them to the functions and parsing them after, which is an
>> error-prone approach
>>
>> This patch adds support for multi-dimensional arrays as both input and
>> output parameters for PL/Python functions. The number of dimensions
>> supported is limited by Postgres MAXDIM macrovariable, by default equal to
>> 6. Both input and output multi-dimensional arrays should have fixed
>> dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
>> represent MxNxK cube, etc.
>>
>> This patch does not support multi-dimensional arrays of composite types,
>> as composite types in Python might be represented as iterators and there is
>> no obvious way to find out when the nested array stops and composite type
>> structure starts. For example, if we have a composite type of (int, text),
>> we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'], [4,'d'] ] ]", and
>> it is hard to find out that the first two lists are lists, and the third
>> one represents structure. Things are getting even more complex when you
>> have arrays as members of composite type. This is why I think this
>> limitation is reasonable.
>>
>> Given the function:
>>
>> CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[]
>> AS $$
>> plpy.info(x, type(x))
>> return x
>> $$ LANGUAGE plpythonu;
>>
>> Before patch:
>>
>> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
>> ERROR:  cannot convert multidimensional array to Python list
>> DETAIL:  PL/Python only supports one-dimensional arrays.
>> CONTEXT:  PL/Python function "test_type_conversion_array_int4"
>>
>>
>> After patch:
>>
>> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
>> INFO:  ([[1, 2, 3], [4, 5, 6]], )
>>  test_type_conversion_array_int4
>> -
>>  {{1,2,3},{4,5,6}}
>> (1 row)
>>
>>
>> --
>> Best regards,
>> Alexey Grishchenko
>>
>
> Also this patch incorporates the fix for https://www.postgresql.
> org/message-id/CAH38_tkwA5qgLV8zPN1OpPzhtkNKQb30n3x
> q-2NR9jUfv3qwHA%40mail.gmail.com, as they touch the same piece of code -
> array manipulation in PL/Python
>
>
I am sending review of this patch:

1. The implemented functionality is clearly benefit - passing MD arrays,
pretty faster passing bigger arrays
2. I was able to use this patch cleanly without any errors or warnings
3. There is no any error or warning
4. All tests passed - I tested Python 2.7 and Python 3.5
5. The code is well commented and clean
6. For this new functionality the documentation is not necessary

7. I invite more regress tests for both directions (Python <-> Postgres)
for more than two dimensions

My only one objection is not enough regress tests - after fixing this patch
will be ready for commiters.

Good work, Alexey

Thank you

Regards

Pavel


> --
> Best regards,
> Alexey Grishchenko
>

Re: [HACKERS] PL/Python adding support for multi-dimensional arrays

[Alexey Grishchenko]

On Wed, Aug 3, 2016 at 12:49 PM, Alexey Grishchenko  wrote:

> Hi
>
> Current implementation of PL/Python does not allow the use of
> multi-dimensional arrays, for both input and output parameters. This forces
> end users to introduce workarounds like casting arrays to text before
> passing them to the functions and parsing them after, which is an
> error-prone approach
>
> This patch adds support for multi-dimensional arrays as both input and
> output parameters for PL/Python functions. The number of dimensions
> supported is limited by Postgres MAXDIM macrovariable, by default equal to
> 6. Both input and output multi-dimensional arrays should have fixed
> dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
> represent MxNxK cube, etc.
>
> This patch does not support multi-dimensional arrays of composite types,
> as composite types in Python might be represented as iterators and there is
> no obvious way to find out when the nested array stops and composite type
> structure starts. For example, if we have a composite type of (int, text),
> we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'], [4,'d'] ] ]", and
> it is hard to find out that the first two lists are lists, and the third
> one represents structure. Things are getting even more complex when you
> have arrays as members of composite type. This is why I think this
> limitation is reasonable.
>
> Given the function:
>
> CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[]
> AS $$
> plpy.info(x, type(x))
> return x
> $$ LANGUAGE plpythonu;
>
> Before patch:
>
> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
> ERROR:  cannot convert multidimensional array to Python list
> DETAIL:  PL/Python only supports one-dimensional arrays.
> CONTEXT:  PL/Python function "test_type_conversion_array_int4"
>
>
> After patch:
>
> # SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
> INFO:  ([[1, 2, 3], [4, 5, 6]], )
>  test_type_conversion_array_int4
> -
>  {{1,2,3},{4,5,6}}
> (1 row)
>
>
> --
> Best regards,
> Alexey Grishchenko
>

Also this patch incorporates the fix for
https://www.postgresql.org/message-id/CAH38_tkwA5qgLV8zPN1OpPzhtkNKQb30n3xq-2NR9jUfv3qwHA%40mail.gmail.com,
as they touch the same piece of code - array manipulation in PL/Python

-- 
Best regards,
Alexey Grishchenko

[HACKERS] PL/Python adding support for multi-dimensional arrays

[Alexey Grishchenko]

Hi

Current implementation of PL/Python does not allow the use of
multi-dimensional arrays, for both input and output parameters. This forces
end users to introduce workarounds like casting arrays to text before
passing them to the functions and parsing them after, which is an
error-prone approach

This patch adds support for multi-dimensional arrays as both input and
output parameters for PL/Python functions. The number of dimensions
supported is limited by Postgres MAXDIM macrovariable, by default equal to
6. Both input and output multi-dimensional arrays should have fixed
dimension sizes, i.e. 2-d arrays should represent MxN matrix, 3-d arrays
represent MxNxK cube, etc.

This patch does not support multi-dimensional arrays of composite types, as
composite types in Python might be represented as iterators and there is no
obvious way to find out when the nested array stops and composite type
structure starts. For example, if we have a composite type of (int, text),
we can try to return "[ [ [1,'a'], [2,'b'] ], [ [3,'c'], [4,'d'] ] ]", and
it is hard to find out that the first two lists are lists, and the third
one represents structure. Things are getting even more complex when you
have arrays as members of composite type. This is why I think this
limitation is reasonable.

Given the function:

CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS
$$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpythonu;

Before patch:

# SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
ERROR:  cannot convert multidimensional array to Python list
DETAIL:  PL/Python only supports one-dimensional arrays.
CONTEXT:  PL/Python function "test_type_conversion_array_int4"


After patch:

# SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
INFO:  ([[1, 2, 3], [4, 5, 6]], )
 test_type_conversion_array_int4
-
 {{1,2,3},{4,5,6}}
(1 row)


-- 
Best regards,
Alexey Grishchenko


0001-PL-Python-adding-support-for-multi-dimensional-arrays.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Heikki Linnakangas]

On 11/27/13 14:15, Marko Kreen wrote:

On Tue, Nov 26, 2013 at 07:12:00PM -0200, Rodolfo Campero wrote:

2013/11/26 Heikki Linnakangas 

Oops, sorry about that. Fixed.


Maybe be you forgot to modify
plpython_types_3.out


Yes.  Heikki, please fix plpython_types_3.out too.

See attached patch.


Ah, sorry. Committed..

- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Marko Kreen]

On Tue, Nov 26, 2013 at 07:12:00PM -0200, Rodolfo Campero wrote:
> 2013/11/26 Heikki Linnakangas 
> > Oops, sorry about that. Fixed.
> 
> Maybe be you forgot to modify
> plpython_types_3.out

Yes.  Heikki, please fix plpython_types_3.out too.

See attached patch.

-- 
marko

diff --git a/src/pl/plpython/expected/plpython_types_3.out b/src/pl/plpython/expected/plpython_types_3.out
index 25331f2..e104356 100644
--- a/src/pl/plpython/expected/plpython_types_3.out
+++ b/src/pl/plpython/expected/plpython_types_3.out
@@ -664,6 +664,9 @@ SELECT * FROM test_type_conversion_array_error();
 ERROR:  return value of function with array return type is not a Python sequence
 CONTEXT:  while creating return value
 PL/Python function "test_type_conversion_array_error"
+--
+-- Domains over arrays
+--
 CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]);
 CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$
 plpy.info(x, type(x))

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

2013/11/26 Heikki Linnakangas 

> On 11/26/13 19:07, Kevin Grittner wrote:
>
>> Heikki Linnakangas  wrote:
>>
>>  Ok, committed.
>>>
>>
>> make check-world failure:
>>
>
> Oops, sorry about that. Fixed.


Maybe be you forgot to modify
plpython_types_3.out
?

-- 
Rodolfo

Re: [HACKERS] PL/Python: domain over array support

[Heikki Linnakangas]

On 11/26/13 19:07, Kevin Grittner wrote:

Heikki Linnakangas  wrote:


Ok, committed.


make check-world failure:


Oops, sorry about that. Fixed.

- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Kevin Grittner]

Heikki Linnakangas  wrote:

> Ok, committed.

make check-world failure:

*** /home/kgrittn/pg/master/src/pl/plpython/expected/plpython_types.out 
2013-11-26 10:52:04.173441894 -0600
--- /home/kgrittn/pg/master/src/pl/plpython/results/plpython_types.out  
2013-11-26 10:55:58.229445970 -0600
***
*** 664,669 
--- 664,672 
  ERROR:  return value of function with array return type is not a Python 
sequence
  CONTEXT:  while creating return value
  PL/Python function "test_type_conversion_array_error"
+ --
+ -- Domains over arrays
+ --
  CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 
AND VALUE[1] < VALUE[2]);
  CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) 
RETURNS ordered_pair_domain AS $$
  plpy.info(x, type(x))

==

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Heikki Linnakangas]

On 11/26/13 11:56, Marko Kreen wrote:

On Tue, Nov 26, 2013 at 12:23:48AM +0200, Heikki Linnakangas wrote:

The new behavior is clearly better, but it is an incompatibility
nonetheless. I don't do anything with PL/python myself, so I don't
have a good feel of how much that'll break people's applications.
Probably not much I guess. But warrants a mention in the release
notes at least. Any thoughts on that?


Yes it warrants a mention but nothing excessive, in 9.0 the non-domain
arrays has same non-compatible change with only one sentence in notes.


Ok, committed. Thank you, Rodolfo and Marko!

- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Marko Kreen]

On Tue, Nov 26, 2013 at 12:23:48AM +0200, Heikki Linnakangas wrote:
> The new behavior is clearly better, but it is an incompatibility
> nonetheless. I don't do anything with PL/python myself, so I don't
> have a good feel of how much that'll break people's applications.
> Probably not much I guess. But warrants a mention in the release
> notes at least. Any thoughts on that?

Yes it warrants a mention but nothing excessive, in 9.0 the non-domain
arrays has same non-compatible change with only one sentence in notes.

-- 
marko



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

2013/11/25 Heikki Linnakangas 
[...]

> This does change the behavior of any existing functions that return a
> domain over array. For example:
>
> postgres=# create domain intarr as integer[];
> CREATE DOMAIN
> postgres=# create function intarr_test() returns intarr as $$
> return '{1,2}'
> $$ language plpythonu;
> CREATE FUNCTION
>
> Before patch:
>
> postgres=# select intarr_test();
>  intarr_test
> -
>  {1,2}
> (1 row)
>
> After patch:
>
> postgres=# select intarr_test();
> ERROR:  invalid input syntax for integer: "{"
> CONTEXT:  while creating return value
> PL/Python function "intarr_test"
>
>
> The new behavior is clearly better, but it is an incompatibility
> nonetheless. I don't do anything with PL/python myself, so I don't have a
> good feel of how much that'll break people's applications. Probably not
> much I guess. But warrants a mention in the release notes at least. Any
> thoughts on that?
>
> - Heikki
>

Bear in mind that the same goes for receiving domains over arrays as
parameters; instead of seeing a string (previous behavior), with this patch
a function will see a list from the Python side (the function
implementation). A mention in the release notes is in order, I agree with
that.

I can't speak for other people, but I guess using domains over arrays as
parameters and/or return values in plpythonu functions should be rare,
considering the current behavior and especially given the possibility of
using a regular array in order to handle array values a lists in Python.

Regards,
-- 
Rodolfo

Re: [HACKERS] PL/Python: domain over array support

[Heikki Linnakangas]

On 24.11.2013 18:44, Marko Kreen wrote:

On Sat, Nov 23, 2013 at 11:09:53AM -0200, Rodolfo Campero wrote:

2013/11/22 Marko Kreen 

One more thing - please update Python 3 regtests too.


The attached patch (version 3) includes the expected results for Python 3
(file plpython_types_3.out).


Thanks.  Looks good now.


Looks good to me too.

This does change the behavior of any existing functions that return a 
domain over array. For example:


postgres=# create domain intarr as integer[];
CREATE DOMAIN
postgres=# create function intarr_test() returns intarr as $$
return '{1,2}'
$$ language plpythonu;
CREATE FUNCTION

Before patch:

postgres=# select intarr_test();
 intarr_test
-
 {1,2}
(1 row)

After patch:

postgres=# select intarr_test();
ERROR:  invalid input syntax for integer: "{"
CONTEXT:  while creating return value
PL/Python function "intarr_test"


The new behavior is clearly better, but it is an incompatibility 
nonetheless. I don't do anything with PL/python myself, so I don't have 
a good feel of how much that'll break people's applications. Probably 
not much I guess. But warrants a mention in the release notes at least. 
Any thoughts on that?


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

Thank you very much Marko.


2013/11/24 Marko Kreen 

> On Sat, Nov 23, 2013 at 11:09:53AM -0200, Rodolfo Campero wrote:
> > 2013/11/22 Marko Kreen 
> > > One more thing - please update Python 3 regtests too.
> > >
> > The attached patch (version 3) includes the expected results for Python 3
> > (file plpython_types_3.out).
>
> Thanks.  Looks good now.
>
> Tagging as ready for committer.
>
> --
> marko
>
>


-- 
Rodolfo Campero
Anachronics S.R.L.
Tel: (54 11) 4899 2088
rodolfo.camp...@anachronics.com
http://www.anachronics.com

Re: [HACKERS] PL/Python: domain over array support

[Marko Kreen]

On Sat, Nov 23, 2013 at 11:09:53AM -0200, Rodolfo Campero wrote:
> 2013/11/22 Marko Kreen 
> > One more thing - please update Python 3 regtests too.
> >
> The attached patch (version 3) includes the expected results for Python 3
> (file plpython_types_3.out).

Thanks.  Looks good now.

Tagging as ready for committer.

-- 
marko



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

2013/11/22 Marko Kreen 

>
> One more thing - please update Python 3 regtests too.
>
>
The attached patch (version 3) includes the expected results for Python 3
(file plpython_types_3.out).
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index 91106e0..785ffca 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -664,6 +664,34 @@ SELECT * FROM test_type_conversion_array_error();
 ERROR:  return value of function with array return type is not a Python sequence
 CONTEXT:  while creating return value
 PL/Python function "test_type_conversion_array_error"
+CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]);
+CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain);
+INFO:  ([0, 100], )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ {0,100}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain);
+INFO:  (None, )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ 
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$
+return [2,1]
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_domain_check_violation();
+ERROR:  value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check"
+CONTEXT:  while creating return value
+PL/Python function "test_type_conversion_array_domain_check_violation"
 ---
 --- Composite types
 ---
diff --git a/src/pl/plpython/expected/plpython_types_3.out b/src/pl/plpython/expected/plpython_types_3.out
index 523c2ec..25331f2 100644
--- a/src/pl/plpython/expected/plpython_types_3.out
+++ b/src/pl/plpython/expected/plpython_types_3.out
@@ -664,6 +664,34 @@ SELECT * FROM test_type_conversion_array_error();
 ERROR:  return value of function with array return type is not a Python sequence
 CONTEXT:  while creating return value
 PL/Python function "test_type_conversion_array_error"
+CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]);
+CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpython3u;
+SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain);
+INFO:  ([0, 100], )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ {0,100}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain);
+INFO:  (None, )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ 
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$
+return [2,1]
+$$ LANGUAGE plpython3u;
+SELECT * FROM test_type_conversion_array_domain_check_violation();
+ERROR:  value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check"
+CONTEXT:  while creating return value
+PL/Python function "test_type_conversion_array_domain_check_violation"
 ---
 --- Composite types
 ---
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index caccbf9..0a2307a 100644
--- a/src/pl/plpython/plpy_typeio.c
+++ b/src/pl/plpython/plpy_typeio.c
@@ -373,7 +373,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup)
 	arg->typioparam = getTypeIOParam(typeTup);
 	arg->typbyval = typeStruct->typbyval;
 
-	element_type = get_element_type(arg->typoid);
+	element_type = get_base_element_type(arg->typoid);
 
 	/*
 	 * Select a conversion function to convert Python objects to PostgreSQL
@@ -427,7 +427,8 @@ static void
 PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup)
 {
 	Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
-	Oid			element_type = get_element_type(typeOid);
+	/* It's safe to handle domains of array types as its base array type. */
+	Oid			element_type = get_base_element_type(typeOid);
 
 	/* Get the type's conversion information */
 	perm_fmgr_info(typeStruct->typoutput, &arg->typfunc);
@@ -808,6 +809,7 @@ static Datum
 PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
 {
 	ArrayType  *array;
+	Datum   rv;
 	int			i;
 	Datum	   *elems;
 	bool	   *nulls;
@@ -844,8 +846,15 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
 
 	lbs = 1;
 	array = construct_md_array(elems, nulls, 1, &len, &lbs,
-			   get_element_type(

Re: [HACKERS] PL/Python: domain over array support

[Marko Kreen]

On Fri, Nov 22, 2013 at 08:45:56AM -0200, Rodolfo Campero wrote:
> There are other cosmetic changes in this patch, wrt previous version (not
> preexistent code):
>  * adjusted alignment of variable name "rv" in line 12
>  * reworded comment in line 850, resulting in more than 80 characters, so I
> splitted the comment into a multiline comment following the surrounding
> style.

Good.

One more thing - please update Python 3 regtests too.

-- 
marko



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

Marko,

2013/11/22 Marko Kreen 

> On Sat, Oct 26, 2013 at 11:17:19AM -0200, Rodolfo Campero wrote:
> > The attached patch add support of domains over arrays to PL/Python (eg:
> > CREATE DOMAIN my_domain AS integer[]).
> >
> > Basically it just uses get_base_element_type instead of get_element_type
> > in plpy_typeio.c, and uses domain_check before returning a sequence as
> > array in PLySequence_ToArray whenever appropriate.
>
> Generally looks fine.  Please lose the C++ comments though, this style
> is not used in Postgres sources.
>

Done.


> > There's one line I'm not sure about; I modified a switch statement (line
> > 427):
> > switch (element_type ? element_type : getBaseType(arg->typoid))
> > The rationale is that when element_type is set, it is already a base
> type,
> > because there is no support of arrays of domains in PostgreSQL, but this
> > may not held true in the future.
>
> Was there any actual need to modify that?  Or was it just performance
> optimization?  ATM it creates asymmetry between PLy_output_datum_func2
> and PLy_input_datum_func2.
>
> If it's just performace optimization, then it should be done in both
> functions, but seems bad idea to do it in this patch.  So I think
> it's better to leave it out.
>
>
There was no actual need to modify that, so I dropped that change in this
new patch.

There are other cosmetic changes in this patch, wrt previous version (not
preexistent code):
 * adjusted alignment of variable name "rv" in line 12
 * reworded comment in line 850, resulting in more than 80 characters, so I
splitted the comment into a multiline comment following the surrounding
style.

Thanks for your review.
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index 91106e0..785ffca 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -664,6 +664,34 @@ SELECT * FROM test_type_conversion_array_error();
 ERROR:  return value of function with array return type is not a Python sequence
 CONTEXT:  while creating return value
 PL/Python function "test_type_conversion_array_error"
+CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]);
+CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$
+plpy.info(x, type(x))
+return x
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain);
+INFO:  ([0, 100], )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ {0,100}
+(1 row)
+
+SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain);
+INFO:  (None, )
+CONTEXT:  PL/Python function "test_type_conversion_array_domain"
+ test_type_conversion_array_domain 
+---
+ 
+(1 row)
+
+CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$
+return [2,1]
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_array_domain_check_violation();
+ERROR:  value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check"
+CONTEXT:  while creating return value
+PL/Python function "test_type_conversion_array_domain_check_violation"
 ---
 --- Composite types
 ---
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index caccbf9..0a2307a 100644
--- a/src/pl/plpython/plpy_typeio.c
+++ b/src/pl/plpython/plpy_typeio.c
@@ -373,7 +373,7 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup)
 	arg->typioparam = getTypeIOParam(typeTup);
 	arg->typbyval = typeStruct->typbyval;
 
-	element_type = get_element_type(arg->typoid);
+	element_type = get_base_element_type(arg->typoid);
 
 	/*
 	 * Select a conversion function to convert Python objects to PostgreSQL
@@ -427,7 +427,8 @@ static void
 PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup)
 {
 	Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
-	Oid			element_type = get_element_type(typeOid);
+	/* It's safe to handle domains of array types as its base array type. */
+	Oid			element_type = get_base_element_type(typeOid);
 
 	/* Get the type's conversion information */
 	perm_fmgr_info(typeStruct->typoutput, &arg->typfunc);
@@ -808,6 +809,7 @@ static Datum
 PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
 {
 	ArrayType  *array;
+	Datum   rv;
 	int			i;
 	Datum	   *elems;
 	bool	   *nulls;
@@ -844,8 +846,15 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
 
 	lbs = 1;
 	array = construct_md_array(elems, nulls, 1, &len, &lbs,
-			   get_element_type(arg->typoid), arg->elm->typlen, arg->elm->typbyval, arg->elm->typalign);
-	return PointerGetDatum(array);
+			   get_base_element_type(arg->typoid), arg->elm->typlen, arg->elm->typbyval, arg->elm->typalign);
+	/*
+	 * If the result type is a 

Re: [HACKERS] PL/Python: domain over array support

[Marko Kreen]

On Sat, Oct 26, 2013 at 11:17:19AM -0200, Rodolfo Campero wrote:
> The attached patch add support of domains over arrays to PL/Python (eg:
> CREATE DOMAIN my_domain AS integer[]).
> 
> Basically it just uses get_base_element_type instead of get_element_type
> in plpy_typeio.c, and uses domain_check before returning a sequence as
> array in PLySequence_ToArray whenever appropriate.

Generally looks fine.  Please lose the C++ comments though, this style
is not used in Postgres sources.

> There's one line I'm not sure about; I modified a switch statement (line
> 427):
> switch (element_type ? element_type : getBaseType(arg->typoid))
> The rationale is that when element_type is set, it is already a base type,
> because there is no support of arrays of domains in PostgreSQL, but this
> may not held true in the future.

Was there any actual need to modify that?  Or was it just performance
optimization?  ATM it creates asymmetry between PLy_output_datum_func2
and PLy_input_datum_func2.

If it's just performace optimization, then it should be done in both
functions, but seems bad idea to do it in this patch.  So I think
it's better to leave it out.

-- 
marko



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

Done, thanks.


2013/10/28 Robert Haas 

> On Sat, Oct 26, 2013 at 9:17 AM, Rodolfo Campero
>  wrote:
> > The attached patch add support of domains over arrays to PL/Python (eg:
> > CREATE DOMAIN my_domain AS integer[]).
> >
> > Basically it just uses get_base_element_type instead of get_element_type
> in
> > plpy_typeio.c, and uses domain_check before returning a sequence as
> array in
> > PLySequence_ToArray whenever appropriate.
> >
> > There's one line I'm not sure about; I modified a switch statement (line
> > 427):
> > switch (element_type ? element_type : getBaseType(arg->typoid))
> > The rationale is that when element_type is set, it is already a base
> type,
> > because there is no support of arrays of domains in PostgreSQL, but this
> may
> > not held true in the future.
>
> Please add your patch here so that it doesn't get forgotten about:
>
> https://commitfest.postgresql.org/action/commitfest_view/open
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] PL/Python: domain over array support

[Robert Haas]

On Sat, Oct 26, 2013 at 9:17 AM, Rodolfo Campero
 wrote:
> The attached patch add support of domains over arrays to PL/Python (eg:
> CREATE DOMAIN my_domain AS integer[]).
>
> Basically it just uses get_base_element_type instead of get_element_type in
> plpy_typeio.c, and uses domain_check before returning a sequence as array in
> PLySequence_ToArray whenever appropriate.
>
> There's one line I'm not sure about; I modified a switch statement (line
> 427):
> switch (element_type ? element_type : getBaseType(arg->typoid))
> The rationale is that when element_type is set, it is already a base type,
> because there is no support of arrays of domains in PostgreSQL, but this may
> not held true in the future.

Please add your patch here so that it doesn't get forgotten about:

https://commitfest.postgresql.org/action/commitfest_view/open

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python: domain over array support

[Rodolfo Campero]

The attached patch add support of domains over arrays to PL/Python (eg:
CREATE DOMAIN my_domain AS integer[]).

Basically it just uses get_base_element_type instead of get_element_type
in plpy_typeio.c, and uses domain_check before returning a sequence as
array in PLySequence_ToArray whenever appropriate.

There's one line I'm not sure about; I modified a switch statement (line
427):
switch (element_type ? element_type : getBaseType(arg->typoid))
The rationale is that when element_type is set, it is already a base type,
because there is no support of arrays of domains in PostgreSQL, but this
may not held true in the future.

Regards,
Rodolfo


plpython_domain_over_array_v1.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Peter Eisentraut]

On Sat, 2013-02-02 at 15:43 -0500, Steve Singer wrote:
> I've looked through the code and everything looks fine.
> 
> The patch includes no documentation.   Adding a few lines to the 
> "Utility Functions" section of the plpython documentation so people know 
> about this feature would be good.

Added some documentation and committed.  Thanks.




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Steve Singer]

On 13-01-07 09:58 PM, Peter Eisentraut wrote:

By implementing a "str" handler for the result object, it now prints
something like



Patch attached for review.



Here is a review:

This patch adds a function that pl/python functions can call to convert 
a query result hash into a string suitable for debug purposes. The use 
case for this feature is primarily for debugging and logging purposes.   
I feel that this is useful since a lot of debugging of stored functions 
is usually done with print/elog style debugging.


There already some discussion on the thread as if the number of rows 
printed should be limited, the consensus seemed to be 'no' since someone 
would be unhappy with any limit and printing everything is the same 
behaviour you get with the standard python print.


I've tested this with python2.6 and 3.1 and it seems to work as described.

I've looked through the code and everything looks fine.

The patch includes no documentation.   Adding a few lines to the 
"Utility Functions" section of the plpython documentation so people know 
about this feature would be good.


Other than that I think it is fine to commit.  I am setting this as 
ready for committer,  I assume you'll commit this yourself and that you 
can add a paragraph to the docs as you commit it.



Steve










--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Peter Eisentraut]

On 1/8/13 11:55 AM, Daniele Varrazzo wrote:
>>> >> 'bar': '22'}]>
> This looks more a repr-style format to me (if you implement repr but
> not str, the latter will default to the former).

The repr style was the only guideline I found.  There is no guideline
for how str should look like when it's not repr.  Do you have a better
suggestion for the output format?

(The reason this is str and not repr is that it doesn't contain other
information such as the tuple descriptor, so str of two different
results could easily be the same.)




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Magnus Hagander]

On Tue, Jan 8, 2013 at 10:23 PM, Peter Eisentraut  wrote:
> On 1/8/13 4:32 AM, Magnus Hagander wrote:
>> How does it work if there are many rows in there? Say the result
>> contains 10,000 rows - will the string contain all of them? If so,
>> might it be worthwhile to cap the number of rows shown and then follow
>> with a "..." or something?
>
> I don't think so.  Any number you pick will be too low for someone.
> Since this would only be executed when explicitly asked for, it's up to
> the user to manage this.  It's analogous to print(long_list) -- you
> wouldn't truncate that.

Fair enough. I was thinking of a specific example when I wrote that,
bu I can't recall what it was, and clearly using print or the python
console would be the most similar scenarios. And they both do it the
way you suggest. So that's probably the right thing to do.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Peter Eisentraut]

On 1/8/13 4:32 AM, Magnus Hagander wrote:
> How does it work if there are many rows in there? Say the result
> contains 10,000 rows - will the string contain all of them? If so,
> might it be worthwhile to cap the number of rows shown and then follow
> with a "..." or something?

I don't think so.  Any number you pick will be too low for someone.
Since this would only be executed when explicitly asked for, it's up to
the user to manage this.  It's analogous to print(long_list) -- you
wouldn't truncate that.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Daniele Varrazzo]

On Tue, Jan 8, 2013 at 9:32 AM, Magnus Hagander  wrote:
> On Tue, Jan 8, 2013 at 3:58 AM, Peter Eisentraut  wrote:
>> For debugging PL/Python functions, I'm often tempted to write something
>> like
>>
>> rv = plpy.execute(...)
>> plpy.info(rv)
>>
>> which prints something unhelpful like
>>
>> 
>>
>> By implementing a "str" handler for the result object, it now prints
>> something like
>>
>> > '22'}]>

This looks more a repr-style format to me (if you implement repr but
not str, the latter will default to the former).


>> Patch attached for review.
>
> How does it work if there are many rows in there? Say the result
> contains 10,000 rows - will the string contain all of them? If so,
> might it be worthwhile to cap the number of rows shown and then follow
> with a "..." or something?

I think it would: old django versions were a pain in the neck because
when a page broke an entire dump of gigantic queries was often dumped
as debug info.

-- Daniele


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result object str handler

[Magnus Hagander]

On Tue, Jan 8, 2013 at 3:58 AM, Peter Eisentraut  wrote:
> For debugging PL/Python functions, I'm often tempted to write something
> like
>
> rv = plpy.execute(...)
> plpy.info(rv)
>
> which prints something unhelpful like
>
> 
>
> By implementing a "str" handler for the result object, it now prints
> something like
>
>  '22'}]>
>
> Patch attached for review.

How does it work if there are many rows in there? Say the result
contains 10,000 rows - will the string contain all of them? If so,
might it be worthwhile to cap the number of rows shown and then follow
with a "..." or something?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python result object str handler

[Peter Eisentraut]

For debugging PL/Python functions, I'm often tempted to write something
like

rv = plpy.execute(...)
plpy.info(rv)

which prints something unhelpful like



By implementing a "str" handler for the result object, it now prints
something like



Patch attached for review.

diff --git a/src/pl/plpython/expected/plpython_spi.out b/src/pl/plpython/expected/plpython_spi.out
index 3cda958..b07a429 100644
--- a/src/pl/plpython/expected/plpython_spi.out
+++ b/src/pl/plpython/expected/plpython_spi.out
@@ -263,6 +263,24 @@ CONTEXT:  PL/Python function "result_empty_test"
  
 (1 row)
 
+CREATE FUNCTION result_str_test(cmd text) RETURNS text
+AS $$
+plan = plpy.prepare(cmd)
+result = plpy.execute(plan)
+return str(result)
+$$ LANGUAGE plpythonu;
+SELECT result_str_test($$SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'$$);
+   result_str_test
+--
+ 
+(1 row)
+
+SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$);
+   result_str_test
+--
+ 
+(1 row)
+
 -- cursor objects
 CREATE FUNCTION simple_cursor_test() RETURNS int AS $$
 res = plpy.cursor("select fname, lname from users")
diff --git a/src/pl/plpython/plpy_resultobject.c b/src/pl/plpython/plpy_resultobject.c
index ea93ad7..077bde6 100644
--- a/src/pl/plpython/plpy_resultobject.c
+++ b/src/pl/plpython/plpy_resultobject.c
@@ -22,6 +22,7 @@
 static PyObject *PLy_result_item(PyObject *arg, Py_ssize_t idx);
 static PyObject *PLy_result_slice(PyObject *arg, Py_ssize_t lidx, Py_ssize_t hidx);
 static int	PLy_result_ass_slice(PyObject *arg, Py_ssize_t lidx, Py_ssize_t hidx, PyObject *slice);
+static PyObject *PLy_result_str(PyObject *arg);
 static PyObject *PLy_result_subscript(PyObject *arg, PyObject *item);
 static int	PLy_result_ass_subscript(PyObject *self, PyObject *item, PyObject *value);
 
@@ -74,7 +75,7 @@
 	&PLy_result_as_mapping,		/* tp_as_mapping */
 	0,			/* tp_hash */
 	0,			/* tp_call */
-	0,			/* tp_str */
+	&PLy_result_str,			/* tp_str */
 	0,			/* tp_getattro */
 	0,			/* tp_setattro */
 	0,			/* tp_as_buffer */
@@ -249,6 +250,26 @@
 }
 
 static PyObject *
+PLy_result_str(PyObject *arg)
+{
+	PLyResultObject *ob = (PLyResultObject *) arg;
+
+#if PY_MAJOR_VERSION >= 3
+	return PyUnicode_FromFormat("<%s status=%S nrows=%S rows=%S>",
+Py_TYPE(ob)->tp_name,
+ob->status,
+ob->nrows,
+ob->rows);
+#else
+	return PyString_FromFormat("<%s status=%ld nrows=%ld rows=%s>",
+			   ob->ob_type->tp_name,
+			   PyInt_AsLong(ob->status),
+			   PyInt_AsLong(ob->nrows),
+			   PyString_AsString(PyObject_Str(ob->rows)));
+#endif
+}
+
+static PyObject *
 PLy_result_subscript(PyObject *arg, PyObject *item)
 {
 	PLyResultObject *ob = (PLyResultObject *) arg;
diff --git a/src/pl/plpython/sql/plpython_spi.sql b/src/pl/plpython/sql/plpython_spi.sql
index 6250e90..7a84473 100644
--- a/src/pl/plpython/sql/plpython_spi.sql
+++ b/src/pl/plpython/sql/plpython_spi.sql
@@ -169,6 +169,16 @@ CREATE FUNCTION result_empty_test() RETURNS void
 
 SELECT result_empty_test();
 
+CREATE FUNCTION result_str_test(cmd text) RETURNS text
+AS $$
+plan = plpy.prepare(cmd)
+result = plpy.execute(plan)
+return str(result)
+$$ LANGUAGE plpythonu;
+
+SELECT result_str_test($$SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'$$);
+SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$);
+
 -- cursor objects
 
 CREATE FUNCTION simple_cursor_test() RETURNS int AS $$

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python custom datatype parsers

[Hannu Krosing]

Did any (committed?) code result from this thread ?

On 11/10/2011 09:13 PM, Peter Eisentraut wrote:

On tis, 2011-11-08 at 16:08 -0500, Andrew Dunstan wrote:

On 03/01/2011 11:50 AM, Peter Eisentraut wrote:

On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote:

I believe it's (b). But as we don't have time for that discussion that
late in the release cycle, I think we need to consider it identical to (c).

As I previously mentioned, I think that there should be an SQL-level way
to tie together languages and types.  I previously mentioned the
SQL-standard command CREATE TRANSFORM as a possibility.  I've had this
on my PL/Python TOTHINK list for a while.  Thankfully you removed all
the items ahead of this one, so I'll think of something to do in 9.2.

Of course we'll be able to use the actual transform code that you
already wrote.


Peter,

Did you make any progress on this?

No, but it's still somewhere on my list.  I saw your blog post related
to this.

I think the first step would be to set up some catalog infrastructure
(without DDL commands and all that overhead), and try to adapt the big
"case" statement of an existing language to that, and then check whether
that works, performance, etc.

Some other concerns of the top of my head:

- Arrays: Would probably not by handled by that.  So this would not be
able to handle, for example, switching the array handling behavior in
PL/Perl to ancient compatible mode.

- Range types: no idea

I might work on this, but not before December, would be my guess.






--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Peter Eisentraut]

On fre, 2012-05-11 at 11:28 +0200, Jan Urbański wrote:
> On 10/05/12 19:45, Peter Eisentraut wrote:
> > On lör, 2012-05-05 at 22:45 +0200, Jan Urbański wrote:
> >> Apparently once you implement PyMappingMethods.mp_subscript you can
> >> drop PySequenceMethods.sq_slice, but I guess there's no harm in
> >> keeping it (and I'm not sure it'd work on Python 2.3 with only
> >> mp_subscript implemented).
> >
> > Committed this now.
> >
> >  From test coverage reports, I now see that PLy_result_ass_item() is no
> > longer called.  That's probably OK, if assignments are now handled
> > through the mapping methods.  But should we remove the function then?
> 
> Have you tried on Python 2.3 as well? People on #python said that if you 
> implement the mapping functions, the sequence slicing functions are no 
> longer used, but maybe we should revisit for the next release, rather 
> than risk introducing a regression for the benefit of removing a few 
> dead lines.

I did test Python 2.3, but you're right, we should leave this alone
during beta.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Jan Urbański]

On 10/05/12 19:45, Peter Eisentraut wrote:

On lör, 2012-05-05 at 22:45 +0200, Jan Urbański wrote:

Apparently once you implement PyMappingMethods.mp_subscript you can
drop PySequenceMethods.sq_slice, but I guess there's no harm in
keeping it (and I'm not sure it'd work on Python 2.3 with only
mp_subscript implemented).


Committed this now.

 From test coverage reports, I now see that PLy_result_ass_item() is no
longer called.  That's probably OK, if assignments are now handled
through the mapping methods.  But should we remove the function then?


Have you tried on Python 2.3 as well? People on #python said that if you 
implement the mapping functions, the sequence slicing functions are no 
longer used, but maybe we should revisit for the next release, rather 
than risk introducing a regression for the benefit of removing a few 
dead lines.


Cheers,
Jan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Peter Eisentraut]

On lör, 2012-05-05 at 22:45 +0200, Jan Urbański wrote:
> Apparently once you implement PyMappingMethods.mp_subscript you can
> drop PySequenceMethods.sq_slice, but I guess there's no harm in
> keeping it (and I'm not sure it'd work on Python 2.3 with only
> mp_subscript implemented).

Committed this now.

>From test coverage reports, I now see that PLy_result_ass_item() is no
longer called.  That's probably OK, if assignments are now handled
through the mapping methods.  But should we remove the function then?

> 
> Do we want to backpatch this? If so, I'd need to produce a version
> that applies to the monolithic plpython.c file from the previous
> releases. 

I don't think this should be backpatched.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Peter Eisentraut]

On tor, 2012-05-10 at 12:37 -0400, Robert Haas wrote:
> On Sat, May 5, 2012 at 4:45 PM, Jan Urbański  wrote:
> >> I found some instructions on how to deal with the Python 2/Python 3
> >> slicing mess:
> >>
> >>
> >> http://renesd.blogspot.com/2009/07/python3-c-api-simple-slicing-sqslice.html
> >
> >
> > Thanks to the helpful folk at #python I found out that the fix is much
> > easier. Attached is a patch that fixes the bug and passes regression tests
> > on Pythons 2.3 through 3.2.
> >
> > Apparently once you implement PyMappingMethods.mp_subscript you can drop
> > PySequenceMethods.sq_slice, but I guess there's no harm in keeping it (and
> > I'm not sure it'd work on Python 2.3 with only mp_subscript implemented).
> >
> > Do we want to backpatch this? If so, I'd need to produce a version that
> > applies to the monolithic plpython.c file from the previous releases.
> 
> Did this get forgotten about?

I'm working on it.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Robert Haas]

On Sat, May 5, 2012 at 4:45 PM, Jan Urbański  wrote:
>> I found some instructions on how to deal with the Python 2/Python 3
>> slicing mess:
>>
>>
>> http://renesd.blogspot.com/2009/07/python3-c-api-simple-slicing-sqslice.html
>
>
> Thanks to the helpful folk at #python I found out that the fix is much
> easier. Attached is a patch that fixes the bug and passes regression tests
> on Pythons 2.3 through 3.2.
>
> Apparently once you implement PyMappingMethods.mp_subscript you can drop
> PySequenceMethods.sq_slice, but I guess there's no harm in keeping it (and
> I'm not sure it'd work on Python 2.3 with only mp_subscript implemented).
>
> Do we want to backpatch this? If so, I'd need to produce a version that
> applies to the monolithic plpython.c file from the previous releases.

Did this get forgotten about?

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Jan Urbański]

On 04/05/12 20:00, Jan Urbański wrote:

On 03/05/12 11:04, Jan Urbański wrote:

On 02/05/12 20:18, Peter Eisentraut wrote:

This doesn't work anymore with Python 3:

rv = plpy.execute(...)
do_something(rv[0:1])


Sounds ugly. I'll take a look.


I found some instructions on how to deal with the Python 2/Python 3
slicing mess:

http://renesd.blogspot.com/2009/07/python3-c-api-simple-slicing-sqslice.html


Thanks to the helpful folk at #python I found out that the fix is much 
easier. Attached is a patch that fixes the bug and passes regression 
tests on Pythons 2.3 through 3.2.


Apparently once you implement PyMappingMethods.mp_subscript you can drop 
PySequenceMethods.sq_slice, but I guess there's no harm in keeping it 
(and I'm not sure it'd work on Python 2.3 with only mp_subscript 
implemented).


Do we want to backpatch this? If so, I'd need to produce a version that 
applies to the monolithic plpython.c file from the previous releases.


Cheers,
Jan
>From 000a1285d66c65c36ae6fa064266f00def5ee9d7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Jan=20Urba=C5=84ski?= 
Date: Sat, 5 May 2012 22:39:26 +0200
Subject: [PATCH] Fix slicing support for result objects for Python 3.

The old way of implementing slicing support by implementing
PySequenceMethods.sq_slice has been deprecated in Python 3, you should
not implement PyMappingMethods.mp_subscript. Do this by simply
proxying the call to the wrapped list of result dictionaries.

While at it, fix an incorrect comment about PLyResultObject->rows being
None if the result set is empty (it actually is an empty list in that
case).
---
 src/pl/plpython/expected/plpython_spi.out |   55 +
 src/pl/plpython/plpy_resultobject.c   |   27 +-
 src/pl/plpython/plpy_resultobject.h   |2 +-
 src/pl/plpython/sql/plpython_spi.sql  |   36 +++
 4 files changed, 118 insertions(+), 2 deletions(-)

diff --git a/src/pl/plpython/expected/plpython_spi.out b/src/pl/plpython/expected/plpython_spi.out
index 671c24e..54ef512 100644
--- a/src/pl/plpython/expected/plpython_spi.out
+++ b/src/pl/plpython/expected/plpython_spi.out
@@ -228,6 +228,61 @@ SELECT result_len_test($$UPDATE foo3 SET b= '' WHERE a = 2$$);
0
 (1 row)
 
+CREATE FUNCTION result_subscript_test() RETURNS void
+AS $$
+result = plpy.execute("SELECT 1 AS c UNION SELECT 2 "
+  "UNION SELECT 3 UNION SELECT 4")
+
+plpy.info(result[1]['c'])
+plpy.info(result[-1]['c'])
+
+plpy.info([item['c'] for item in result[1:3]])
+plpy.info([item['c'] for item in result[::2]])
+
+result[-1] = {'c': 1000}
+result[:2] = [{'c': 10}, {'c': 100}]
+plpy.info([item['c'] for item in result[:]])
+
+# raises TypeError, but the message differs on Python 2.6, so silence it
+try:
+plpy.info(result['foo'])
+except TypeError:
+pass
+else:
+assert False, "TypeError not raised"
+
+$$ LANGUAGE plpythonu;
+SELECT result_subscript_test();
+INFO:  2
+CONTEXT:  PL/Python function "result_subscript_test"
+INFO:  4
+CONTEXT:  PL/Python function "result_subscript_test"
+INFO:  [2, 3]
+CONTEXT:  PL/Python function "result_subscript_test"
+INFO:  [1, 3]
+CONTEXT:  PL/Python function "result_subscript_test"
+INFO:  [10, 100, 3, 1000]
+CONTEXT:  PL/Python function "result_subscript_test"
+ result_subscript_test 
+---
+ 
+(1 row)
+
+CREATE FUNCTION result_empty_test() RETURNS void
+AS $$
+result = plpy.execute("select 1 where false")
+
+plpy.info(result[:])
+
+$$ LANGUAGE plpythonu;
+SELECT result_empty_test();
+INFO:  []
+CONTEXT:  PL/Python function "result_empty_test"
+ result_empty_test 
+---
+ 
+(1 row)
+
 -- cursor objects
 CREATE FUNCTION simple_cursor_test() RETURNS int AS $$
 res = plpy.cursor("select fname, lname from users")
diff --git a/src/pl/plpython/plpy_resultobject.c b/src/pl/plpython/plpy_resultobject.c
index fcf8074..06ba2ee 100644
--- a/src/pl/plpython/plpy_resultobject.c
+++ b/src/pl/plpython/plpy_resultobject.c
@@ -23,6 +23,9 @@ static PyObject *PLy_result_item(PyObject *arg, Py_ssize_t idx);
 static PyObject *PLy_result_slice(PyObject *arg, Py_ssize_t lidx, Py_ssize_t hidx);
 static int	PLy_result_ass_item(PyObject *arg, Py_ssize_t idx, PyObject *item);
 static int	PLy_result_ass_slice(PyObject *rg, Py_ssize_t lidx, Py_ssize_t hidx, PyObject *slice);
+static PyObject *PLy_result_subscript(PyObject *arg, PyObject *item);
+static PyObject *PLy_result_subscript(PyObject *arg, PyObject *item);
+static int PLy_result_ass_subscript(PyObject* self, PyObject* item, PyObject* value);
 
 static char PLy_result_doc[] = {
 	"Results of a PostgreSQL query"
@@ -38,6 +41,12 @@ static PySequenceMethods PLy_result_as_sequence = {
 	PLy_result_ass_slice,		/* sq_ass_slice */
 };
 
+static PyMappingMethods PLy_result_as_mapping = {
+	PLy_result_length,			/* mp_length */
+	PLy_result_subscript,		/* mp_subscript */
+	PLy_result_ass_subscript,	/* mp_ass_subscript */
+};
+
 static PyMethodDef PLy_result_methods[] = {
 	{"colna

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Jan Urbański]

On 03/05/12 11:04, Jan Urbański wrote:

On 02/05/12 20:18, Peter Eisentraut wrote:

This doesn't work anymore with Python 3:

rv = plpy.execute(...)
do_something(rv[0:1])

Apparently, they changed the C API for doing slicing, or rather made one
of the two APIs for it silently do nothing. Details are difficult to
find, but this email message seems to contain something:
.

I'll try to sort this out sometime, but if someone wants to take a shot
at it, go ahead.


Sounds ugly. I'll take a look.


I found some instructions on how to deal with the Python 2/Python 3 
slicing mess:


http://renesd.blogspot.com/2009/07/python3-c-api-simple-slicing-sqslice.html

Apparently you need that egregious hack in order to avoid code 
duplication. I'll try to produce a patch over the weekend.


Cheers,
Jan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result set slicing broken in Python 3

[Jan Urbański]

On 02/05/12 20:18, Peter Eisentraut wrote:

This doesn't work anymore with Python 3:

rv = plpy.execute(...)
do_something(rv[0:1])

Apparently, they changed the C API for doing slicing, or rather made one
of the two APIs for it silently do nothing.  Details are difficult to
find, but this email message seems to contain something:
.

I'll try to sort this out sometime, but if someone wants to take a shot
at it, go ahead.


Sounds ugly. I'll take a look.

Cheers,
Jan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python result set slicing broken in Python 3

[Peter Eisentraut]

This doesn't work anymore with Python 3:

rv = plpy.execute(...)
do_something(rv[0:1])

Apparently, they changed the C API for doing slicing, or rather made one
of the two APIs for it silently do nothing.  Details are difficult to
find, but this email message seems to contain something:
.

I'll try to sort this out sometime, but if someone wants to take a shot
at it, go ahead.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python long-lived allocations in datum->dict transformation

[Tom Lane]

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
> I came up with a stack of context structures that gets pushed when a
> PL/Python starts being executed and popped when it returns. At first
> they contained just a scratch memory context used by PLyDict_FromTuple.
> Then under the premise of confirming the usefulness of introducing such
> contexts I removed the global PLy_curr_procedure variable and changed
> all users to get the current procedure from the context. It seems to
> have worked, so the total count of global variables is unchanged - hooray!

Applied with some adjustments --- mainly, I thought you were being
too incautious about ensuring that the stack got popped once it'd been
pushed.  The easiest way to fix that was to do the pushes after the
SPI_connect calls, which required decoupling the behavior from
CurrentMemoryContext, which seemed like a good idea anyway.

> While testing I found one more leak, this time caused by allocating a
> structure for caching array type I/O functions and never freeing it.
> Attached as separate patch.

Applied also, but surely if we're leaking memory from the input
descriptors then we should worry about the output ones too?
I made it do that, but if that's wrong, somebody explain why.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python long-lived allocations in datum->dict transformation

[Jan Urbański]

On 14/02/12 01:35, Tom Lane wrote:
> =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
>> It's not very comfortable, but
>> I think PLyDict_FromTuple can be allowed to be non-reentrant.
> 
> I think that's pretty short-sighted.  Even if it's safe today (which
> I am not 100% convinced of), there are plenty of foreseeable reasons
> why it might^Wwill break in the future.
> 
>> OTOH if we want to make it reentrant, some more tinkering would be in order.
> 
> I think that's in order.

Here are the results of the tinkering.

I came up with a stack of context structures that gets pushed when a
PL/Python starts being executed and popped when it returns. At first
they contained just a scratch memory context used by PLyDict_FromTuple.
Then under the premise of confirming the usefulness of introducing such
contexts I removed the global PLy_curr_procedure variable and changed
all users to get the current procedure from the context. It seems to
have worked, so the total count of global variables is unchanged - hooray!

While testing I found one more leak, this time caused by allocating a
structure for caching array type I/O functions and never freeing it.
Attached as separate patch.

Cheers,
Jan
diff --git a/src/pl/plpython/plpy_cursorobject.c b/src/pl/plpython/plpy_cursorobject.c
index 4226dc7..46930b0 100644
*** a/src/pl/plpython/plpy_cursorobject.c
--- b/src/pl/plpython/plpy_cursorobject.c
***
*** 14,19 
--- 14,20 
  #include "plpy_cursorobject.h"
  
  #include "plpy_elog.h"
+ #include "plpy_main.h"
  #include "plpy_planobject.h"
  #include "plpy_procedure.h"
  #include "plpy_resultobject.h"
*** PLy_cursor_query(const char *query)
*** 121,126 
--- 122,128 
  	{
  		SPIPlanPtr	plan;
  		Portal		portal;
+ 		PLyExecutionContext	*exec_ctx = PLy_current_execution_context();
  
  		pg_verifymbstr(query, strlen(query), false);
  
*** PLy_cursor_query(const char *query)
*** 129,136 
  			elog(ERROR, "SPI_prepare failed: %s",
   SPI_result_code_string(SPI_result));
  
  		portal = SPI_cursor_open(NULL, plan, NULL, NULL,
!  PLy_curr_procedure->fn_readonly);
  		SPI_freeplan(plan);
  
  		if (portal == NULL)
--- 131,140 
  			elog(ERROR, "SPI_prepare failed: %s",
   SPI_result_code_string(SPI_result));
  
+ 		Assert(exec_ctx->curr_proc != NULL);
+ 
  		portal = SPI_cursor_open(NULL, plan, NULL, NULL,
!  exec_ctx->curr_proc->fn_readonly);
  		SPI_freeplan(plan);
  
  		if (portal == NULL)
*** PLy_cursor_plan(PyObject *ob, PyObject *
*** 210,215 
--- 214,220 
  		Portal		portal;
  		char	   *volatile nulls;
  		volatile int j;
+ 		PLyExecutionContext *exec_ctx = PLy_current_execution_context();
  
  		if (nargs > 0)
  			nulls = palloc(nargs * sizeof(char));
*** PLy_cursor_plan(PyObject *ob, PyObject *
*** 252,259 
  			}
  		}
  
  		portal = SPI_cursor_open(NULL, plan->plan, plan->values, nulls,
!  PLy_curr_procedure->fn_readonly);
  		if (portal == NULL)
  			elog(ERROR, "SPI_cursor_open() failed: %s",
   SPI_result_code_string(SPI_result));
--- 257,266 
  			}
  		}
  
+ 		Assert(exec_ctx->curr_proc != NULL);
+ 
  		portal = SPI_cursor_open(NULL, plan->plan, plan->values, nulls,
!  exec_ctx->curr_proc->fn_readonly);
  		if (portal == NULL)
  			elog(ERROR, "SPI_cursor_open() failed: %s",
   SPI_result_code_string(SPI_result));
diff --git a/src/pl/plpython/plpy_elog.c b/src/pl/plpython/plpy_elog.c
index 741980c..9909f23 100644
*** a/src/pl/plpython/plpy_elog.c
--- b/src/pl/plpython/plpy_elog.c
***
*** 12,17 
--- 12,18 
  
  #include "plpy_elog.h"
  
+ #include "plpy_main.h"
  #include "plpy_procedure.h"
  
  
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 260,265 
--- 261,267 
  			char	   *line;
  			char	   *plain_filename;
  			long		plain_lineno;
+ 			PLyExecutionContext	*exec_ctx = PLy_current_execution_context();
  
  			/*
  			 * The second frame points at the internal function, but to mimick
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 270,276 
  			else
  fname = PyString_AsString(name);
  
! 			proname = PLy_procedure_name(PLy_curr_procedure);
  			plain_filename = PyString_AsString(filename);
  			plain_lineno = PyInt_AsLong(lineno);
  
--- 272,280 
  			else
  fname = PyString_AsString(name);
  
! 			Assert(exec_ctx->curr_proc != NULL);
! 
! 			proname = PLy_procedure_name(exec_ctx->curr_proc);
  			plain_filename = PyString_AsString(filename);
  			plain_lineno = PyInt_AsLong(lineno);
  
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 287,293 
  			 * function code object was compiled with "" as the
  			 * filename
  			 */
! 			if (PLy_curr_procedure && plain_filename != NULL &&
  strcmp(plain_filename, "") == 0)
  			{
  /*
--- 291,297 
  			 * function code object was compiled with "" as the
  			 * filename
  			 */
! 			if (exec_ctx->curr_proc && plain_filename != N

Re: [HACKERS] pl/python long-lived allocations in datum->dict transformation

[Tom Lane]

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
> On 12/02/12 00:48, Tom Lane wrote:
>> What's more, it's unclear that
>> it won't malfunction altogether if the function is used recursively
>> (ie, what if PLyDict_FromTuple ends up calling the same function again?)

> I was a bit worried about that, but the only place where
> PLyDict_FromTuple calls into some other code is when it calls the type's
> specific I/O function, which AFAICT can't call back into user code
> (except for user-defined C I/O routines). It's not very comfortable, but
> I think PLyDict_FromTuple can be allowed to be non-reentrant.

I think that's pretty short-sighted.  Even if it's safe today (which
I am not 100% convinced of), there are plenty of foreseeable reasons
why it might^Wwill break in the future.

* There is no reason to think that datatype I/O functions will never
be written in anything but C.  People have asked repeatedly for the
ability to write them in higher-level languages.  I doubt that would
ever be possible in plpgsql, but with languages that can do
bit-twiddling like plpython or plperl, it seems possible.

* A datatype I/O function, even if written in C, could call user-written
code.  See domain_in for example, which can invoke arbitrary processing
via domain constraint checking.  If you were proposing to patch
PLyObject_ToTuple rather than the other direction, this patch would be
breakable today.  Admittedly the breakage would require some rather
contrived coding ("your domain's constraint check function does
*what*?"), but it would still be a security bug.

* Once we have the ability to associate a temp memory context with
plpython, there will be a temptation to use it for other purposes
besides this one, and it will not be long before such a purpose does
open a recursion risk, even if there's none there today.  (Speaking of
which, it sure looks to me like PLyObject_ToDatum, PLyObject_ToTuple,
etc leak memory like there's no tomorrow.)

> OTOH if we want to make it reentrant, some more tinkering would be in order.

I think that's in order.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python long-lived allocations in datum->dict transformation

[Jan Urbański]

On 12/02/12 00:48, Tom Lane wrote:
> =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
>> This is annoying for functions that plough through large tables, doing
>> some calculation. Attached is a patch that does the conversion of
>> PostgreSQL Datums into Python dict objects in a scratch memory context
>> that gets reset every time.
> 
> As best I can tell, this patch proposes creating a new, separate context
> (chewing up 8KB+) for every plpython procedure that's ever used in a
> given session.  This cure could easily be worse than the disease as far

Yeah, that's not ideal.

> What's more, it's unclear that
> it won't malfunction altogether if the function is used recursively
> (ie, what if PLyDict_FromTuple ends up calling the same function again?)

I was a bit worried about that, but the only place where
PLyDict_FromTuple calls into some other code is when it calls the type's
specific I/O function, which AFAICT can't call back into user code
(except for user-defined C I/O routines). It's not very comfortable, but
I think PLyDict_FromTuple can be allowed to be non-reentrant.

> Can't you fix it so that the temp context is associated with a
> particular function execution, rather than being "statically" allocated
> per-function?

That would be cool, but I failed to easily get a handle on something
that's like the execution context of a PL/Python function... Actually,
if we assume that PLyDict_FromTuple (which is quite a low-level thing)
never calls PL/Python UDFs we could keep a single memory context in
top-level PL/Python memory and pay the overhead once in a session, not
once per function.

OTOH if we want to make it reentrant, some more tinkering would be in order.

Cheers,
Jan

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python long-lived allocations in datum->dict transformation

[Tom Lane]

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?=  writes:
> This is annoying for functions that plough through large tables, doing
> some calculation. Attached is a patch that does the conversion of
> PostgreSQL Datums into Python dict objects in a scratch memory context
> that gets reset every time.

As best I can tell, this patch proposes creating a new, separate context
(chewing up 8KB+) for every plpython procedure that's ever used in a
given session.  This cure could easily be worse than the disease as far
as total space consumption is concerned.  What's more, it's unclear that
it won't malfunction altogether if the function is used recursively
(ie, what if PLyDict_FromTuple ends up calling the same function again?)
Can't you fix it so that the temp context is associated with a
particular function execution, rather than being "statically" allocated
per-function?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pl/python long-lived allocations in datum->dict transformation

[Jan Urbański]

Consider this:

create table arrays as select array[random(), random(), random(),
random(), random(), random()] as a from generate_series(1, 100);

create or replace function plpython_outputfunc() returns void as $$
c = plpy.cursor('select a from arrays')
for row in c:
pass
$$ language plpythonu;

When running the function, every datum will get transformed into a
Python dict, which includes calling the type's output function,
resulting in a memory allocation. The memory is allocated in the SPI
context, so it accumulates until the function is finished.

This is annoying for functions that plough through large tables, doing
some calculation. Attached is a patch that does the conversion of
PostgreSQL Datums into Python dict objects in a scratch memory context
that gets reset every time.

Cheers,
Jan
diff --git a/src/pl/plpython/plpy_main.c b/src/pl/plpython/plpy_main.c
index ae9d87e..79d7784 100644
*** a/src/pl/plpython/plpy_main.c
--- b/src/pl/plpython/plpy_main.c
***
*** 12,17 
--- 12,18 
  #include "executor/spi.h"
  #include "miscadmin.h"
  #include "utils/guc.h"
+ #include "utils/memutils.h"
  #include "utils/syscache.h"
  
  #include "plpython.h"
*** plpython_inline_handler(PG_FUNCTION_ARGS
*** 268,273 
--- 269,279 
  
  	MemSet(&proc, 0, sizeof(PLyProcedure));
  	proc.pyname = PLy_strdup("__plpython_inline_block");
+ 	proc.tmp_ctx = AllocSetContextCreate(TopMemoryContext,
+ 		 "PL/Python temporary ctx",
+ 		 ALLOCSET_DEFAULT_MINSIZE,
+ 		 ALLOCSET_DEFAULT_INITSIZE,
+ 		 ALLOCSET_DEFAULT_MAXSIZE);
  	proc.result.out.d.typoid = VOIDOID;
  
  	PG_TRY();
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index 229966a..f539cec 100644
*** a/src/pl/plpython/plpy_procedure.c
--- b/src/pl/plpython/plpy_procedure.c
***
*** 12,17 
--- 12,18 
  #include "catalog/pg_type.h"
  #include "utils/builtins.h"
  #include "utils/hsearch.h"
+ #include "utils/memutils.h"
  #include "utils/syscache.h"
  
  #include "plpython.h"
*** PLy_procedure_create(HeapTuple procTup,
*** 169,174 
--- 170,180 
  	proc->setof = NULL;
  	proc->src = NULL;
  	proc->argnames = NULL;
+ 	proc->tmp_ctx = AllocSetContextCreate(TopMemoryContext,
+ 		  "PL/Python temporary ctx",
+ 		  ALLOCSET_DEFAULT_MINSIZE,
+ 		  ALLOCSET_DEFAULT_INITSIZE,
+ 		  ALLOCSET_DEFAULT_MAXSIZE);
  
  	PG_TRY();
  	{
*** PLy_procedure_delete(PLyProcedure *proc)
*** 411,416 
--- 417,424 
  		PLy_free(proc->src);
  	if (proc->argnames)
  		PLy_free(proc->argnames);
+ 	if (proc->tmp_ctx)
+ 		MemoryContextDelete(proc->tmp_ctx);
  }
  
  /*
diff --git a/src/pl/plpython/plpy_procedure.h b/src/pl/plpython/plpy_procedure.h
index e986c7e..5ee73fa 100644
*** a/src/pl/plpython/plpy_procedure.h
--- b/src/pl/plpython/plpy_procedure.h
*** typedef struct PLyProcedure
*** 30,35 
--- 30,36 
  	PyObject   *code;			/* compiled procedure code */
  	PyObject   *statics;		/* data saved across calls, local scope */
  	PyObject   *globals;		/* data saved across calls, global scope */
+ 	MemoryContext tmp_ctx;
  } PLyProcedure;
  
  /* the procedure cache entry */
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
index d5cac9f..6f0eb46 100644
*** a/src/pl/plpython/plpy_typeio.c
--- b/src/pl/plpython/plpy_typeio.c
***
*** 23,28 
--- 23,29 
  #include "plpy_typeio.h"
  
  #include "plpy_elog.h"
+ #include "plpy_procedure.h"
  
  
  /* I/O function caching */
*** PLy_output_record_funcs(PLyTypeInfo *arg
*** 258,268 
  	Assert(arg->is_rowtype == 1);
  }
  
  PyObject *
  PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
  {
! 	PyObject   *volatile dict;
! 	int			i;
  
  	if (info->is_rowtype != 1)
  		elog(ERROR, "PLyTypeInfo structure describes a datum");
--- 259,274 
  	Assert(arg->is_rowtype == 1);
  }
  
+ /*
+  * Transform a tuple into a Python dict object. The transformation can result
+  * in memory allocation, so it's done in a scratch memory context.
+  */
  PyObject *
  PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
  {
! 	PyObject		*volatile dict;
! 	MemoryContext	oldcontext;
! 	inti;
  
  	if (info->is_rowtype != 1)
  		elog(ERROR, "PLyTypeInfo structure describes a datum");
*** PLyDict_FromTuple(PLyTypeInfo *info, Hea
*** 271,276 
--- 277,284 
  	if (dict == NULL)
  		PLy_elog(ERROR, "could not create new dictionary");
  
+ 	oldcontext = MemoryContextSwitchTo(PLy_curr_procedure->tmp_ctx);
+ 
  	PG_TRY();
  	{
  		for (i = 0; i < info->in.r.natts; i++)
*** PLyDict_FromTuple(PLyTypeInfo *info, Hea
*** 298,308 
--- 306,322 
  	}
  	PG_CATCH();
  	{
+ 		MemoryContextSwitchTo(oldcontext);
+ 		MemoryContextReset(PLy_curr_procedure->tmp_ctx);
+ 
  		Py_DECREF(dict);
  		PG_RE_THROW();
  	}
  	PG_END_TRY();
  
+ 	MemoryContext

Re: [HACKERS] PL/Python result metadata

[Abhijit Menon-Sen]

At 2012-01-11 22:05:34 +0200, pete...@gmx.net wrote:
>
> I propose to add two functions to the result object:
> 
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers) […]
> 
> Patch attached.  Comments welcome.

Applies, builds, passes tests. Code looks simple and good. Ready for
committer, unless you want to add a typmod accessor too.

-- ams

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Dimitri Fontaine]

Peter Eisentraut  writes:
> I deliberately chose not to do that, because the PL/Python API is
> intentionally totally different from the standard DB-API, and mixing in
> some semi-conforming look-alike would be quite confusing from both ends.

Fair enough.

> I think we should stick with the PL/Python API being a small layer on
> top of SPI, and let the likes of plpydbapi handle the rest.

I'm discovering that, and again, fair enough :)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Peter Eisentraut]

On ons, 2012-01-11 at 22:52 +0100, Dimitri Fontaine wrote:
> Peter Eisentraut  writes:
> > .colnames() returns a list of column names (strings)
> > .coltypes() returns a list of type OIDs (integers)
> >
> > I just made that up because there is no guidance in the other standard
> > PLs for this sort of thing, AFAICT.
> 
> What about having the same or comparable API as in psycopg or DB API
> 
>   http://initd.org/psycopg/docs/cursor.html
> 
> You could expose a py.description structure?

I deliberately chose not to do that, because the PL/Python API is
intentionally totally different from the standard DB-API, and mixing in
some semi-conforming look-alike would be quite confusing from both ends.
I think we should stick with the PL/Python API being a small layer on
top of SPI, and let the likes of plpydbapi handle the rest.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Peter Eisentraut]

On ons, 2012-01-11 at 17:16 -0300, Alvaro Herrera wrote:
> > I propose to add two functions to the result object:
> > 
> > .colnames() returns a list of column names (strings)
> > .coltypes() returns a list of type OIDs (integers)
> 
> No typmods? 

Didn't think about that, but could be added using similar interface and
code.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Jan Urbański]

On 11/01/12 22:52, Dimitri Fontaine wrote:
> Peter Eisentraut  writes:
>> .colnames() returns a list of column names (strings)
>> .coltypes() returns a list of type OIDs (integers)
>>
>> I just made that up because there is no guidance in the other standard
>> PLs for this sort of thing, AFAICT.
> 
> What about having the same or comparable API as in psycopg or DB API
> 
>   http://initd.org/psycopg/docs/cursor.html
> 
> You could expose a py.description structure?

+1 for providing a read-only result.description. Not sure if it's worth
it to follow DB-API there, but maybe yes. Perhaps we could have a
result.description_ex information that's PG-specific or just not present
in PEP 249, like the typmod, collation and so on.

J

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Dimitri Fontaine]

Peter Eisentraut  writes:
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers)
>
> I just made that up because there is no guidance in the other standard
> PLs for this sort of thing, AFAICT.

What about having the same or comparable API as in psycopg or DB API

  http://initd.org/psycopg/docs/cursor.html

You could expose a py.description structure?
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python result metadata

[Alvaro Herrera]

Excerpts from Peter Eisentraut's message of mié ene 11 17:05:34 -0300 2012:
> There is currently no reliable way to retrieve from a result object in
> PL/Python the number, name, or type of the result columns.  You can get
> the number and name if the query returned more than zero rows by looking
> at the row dicts, but that is unreliable.  The type information isn't
> available at all.
> 
> I propose to add two functions to the result object:
> 
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers)

No typmods?

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python result metadata

[Peter Eisentraut]

There is currently no reliable way to retrieve from a result object in
PL/Python the number, name, or type of the result columns.  You can get
the number and name if the query returned more than zero rows by looking
at the row dicts, but that is unreliable.  The type information isn't
available at all.

I propose to add two functions to the result object:

.colnames() returns a list of column names (strings)
.coltypes() returns a list of type OIDs (integers)

I just made that up because there is no guidance in the other standard
PLs for this sort of thing, AFAICT.

Patch attached.  Comments welcome.
diff --git i/doc/src/sgml/plpython.sgml w/doc/src/sgml/plpython.sgml
index 618f8d0..69c9c90 100644
--- i/doc/src/sgml/plpython.sgml
+++ w/doc/src/sgml/plpython.sgml
@@ -886,9 +886,11 @@ $$ LANGUAGE plpythonu;
list or dictionary object.  The result object can be accessed by
row number and column name.  It has these additional methods:
nrows which returns the number of rows
-   returned by the query, and status which is the
-   SPI_execute() return value.  The result object
-   can be modified.
+   returned by the query, status which is the
+   SPI_execute() return value,
+   colnames which is the list of column names, and
+   coltypes which is the list of column type OIDs.  The
+   result object can be modified.
   
 
   
diff --git i/src/pl/plpython/expected/plpython_spi.out w/src/pl/plpython/expected/plpython_spi.out
index 3b4d7a3..cd76147 100644
--- i/src/pl/plpython/expected/plpython_spi.out
+++ w/src/pl/plpython/expected/plpython_spi.out
@@ -117,10 +117,12 @@ SELECT join_sequences(sequences) FROM sequences
 --
 CREATE FUNCTION result_nrows_test() RETURNS int
 AS $$
-plan = plpy.prepare("SELECT 1 UNION SELECT 2")
+plan = plpy.prepare("SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'")
 plpy.info(plan.status()) # not really documented or useful
 result = plpy.execute(plan)
 if result.status() > 0:
+   plpy.info(result.colnames())
+   plpy.info(result.coltypes())
return result.nrows()
 else:
return None
@@ -128,6 +130,10 @@ $$ LANGUAGE plpythonu;
 SELECT result_nrows_test();
 INFO:  True
 CONTEXT:  PL/Python function "result_nrows_test"
+INFO:  ['foo', 'bar']
+CONTEXT:  PL/Python function "result_nrows_test"
+INFO:  [23, 25]
+CONTEXT:  PL/Python function "result_nrows_test"
  result_nrows_test 
 ---
  2
diff --git i/src/pl/plpython/plpy_resultobject.c w/src/pl/plpython/plpy_resultobject.c
index bf46a16..e7d14d4 100644
--- i/src/pl/plpython/plpy_resultobject.c
+++ w/src/pl/plpython/plpy_resultobject.c
@@ -12,6 +12,8 @@
 
 
 static void PLy_result_dealloc(PyObject *arg);
+static PyObject *PLy_result_colnames(PyObject *self, PyObject *unused);
+static PyObject *PLy_result_coltypes(PyObject *self, PyObject *unused);
 static PyObject *PLy_result_nrows(PyObject *self, PyObject *args);
 static PyObject *PLy_result_status(PyObject *self, PyObject *args);
 static Py_ssize_t PLy_result_length(PyObject *arg);
@@ -35,6 +37,8 @@ static PySequenceMethods PLy_result_as_sequence = {
 };
 
 static PyMethodDef PLy_result_methods[] = {
+	{"colnames", PLy_result_colnames, METH_NOARGS, NULL},
+	{"coltypes", PLy_result_coltypes, METH_NOARGS, NULL},
 	{"nrows", PLy_result_nrows, METH_VARARGS, NULL},
 	{"status", PLy_result_status, METH_VARARGS, NULL},
 	{NULL, NULL, 0, NULL}
@@ -96,6 +100,7 @@ PLy_result_new(void)
 	ob->status = Py_None;
 	ob->nrows = PyInt_FromLong(-1);
 	ob->rows = PyList_New(0);
+	ob->tupdesc = NULL;
 
 	return (PyObject *) ob;
 }
@@ -108,11 +113,44 @@ PLy_result_dealloc(PyObject *arg)
 	Py_XDECREF(ob->nrows);
 	Py_XDECREF(ob->rows);
 	Py_XDECREF(ob->status);
+	if (ob->tupdesc)
+	{
+		FreeTupleDesc(ob->tupdesc);
+		ob->tupdesc = NULL;
+	}
 
 	arg->ob_type->tp_free(arg);
 }
 
 static PyObject *
+PLy_result_colnames(PyObject *self, PyObject *unused)
+{
+	PLyResultObject *ob = (PLyResultObject *) self;
+	PyObject   *list;
+	int			i;
+
+	list = PyList_New(ob->tupdesc->natts);
+	for (i = 0; i < ob->tupdesc->natts; i++)
+		PyList_SET_ITEM(list, i, PyString_FromString(NameStr(ob->tupdesc->attrs[i]->attname)));
+
+	return list;
+}
+
+static PyObject *
+PLy_result_coltypes(PyObject *self, PyObject *unused)
+{
+	PLyResultObject *ob = (PLyResultObject *) self;
+	PyObject   *list;
+	int			i;
+
+	list = PyList_New(ob->tupdesc->natts);
+	for (i = 0; i < ob->tupdesc->natts; i++)
+		PyList_SET_ITEM(list, i, PyInt_FromLong(ob->tupdesc->attrs[i]->atttypid));
+
+	return list;
+}
+
+static PyObject *
 PLy_result_nrows(PyObject *self, PyObject *args)
 {
 	PLyResultObject *ob = (PLyResultObject *) self;
diff --git i/src/pl/plpython/plpy_resultobject.h w/src/pl/plpython/plpy_resultobject.h
index 719828a..1b37d1d 100644
--- i/src/pl/plpython/plpy_resultobject.h
+++ w/src/pl/plpython/plpy_resultobject.h
@@ -5,6 +5,9 @@
 #ifndef PLPY_RESULTOBJECT_H
 #define PLPY_RESULTOBJECT_H
 
+#include "access/tupdesc.h"
+
+
 typedef struct PLyResultObject
 {
 	PyObject_HEAD
@@ -12,6 +15,7 @@ t

Re: [HACKERS] PL/Python SQL error code pass-through

[Heikki Linnakangas]

On 24.11.2011 23:56, Jan Urbański wrote:

On 24/11/11 16:15, Heikki Linnakangas wrote:

On 24.11.2011 10:07, Jan Urbański wrote:

On 23/11/11 17:24, Mika Eloranta wrote:

[PL/Python in 9.1 does not preserve SQLSTATE of errors]


Oops, you're right, it's a regression from 9.0 behaviour.

The fix looks good to me, I changed one place to indent with tabs
instead of spaces and added a regression test.


(Forgot to mention earlier: I committed the patch to master and 
REL9_1_STABLE)



In case of SPI errors we're preserving the following from the original
ErrorData:

* sqlerrcode (as of Mika's patch)
* detail
* hint
* query
* internalpos

that leaves us with the following which are not preserved:

* message
* context
* detail_log

The message is being constructed from the Python exception name and I
think that's useful. The context is being taken by the traceback string.
I'm not sure if detail_log is ever set in these types of errors,
probably not? So I guess we're safe.


Ok.


The problem with storing the entire ErrorData struct is that this
information has to be transformed to Python objects, because we attach
it to the Python exception that gets raised and in case it bubbles all
the way up to the topmost PL/Python function, we recover these Python
objects and use them to construct the ereport call. While the exception
is inside Python, user code can interact with it, so it'd be hard to
have C pointers to non-Python stuff there.


Hmm, can the user also change the fields in the exception within python 
code, or are they read-only? Is the spidata attribute in the exception 
object visible to user code?


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python SQL error code pass-through

[Jan Urbański]

On 24/11/11 16:15, Heikki Linnakangas wrote:

On 24.11.2011 10:07, Jan Urbański wrote:

On 23/11/11 17:24, Mika Eloranta wrote:

Hi all,

[PL/Python in 9.1 does not preserve SQLSTATE of errors]


Oops, you're right, it's a regression from 9.0 behaviour.

The fix looks good to me, I changed one place to indent with tabs
instead of spaces and added a regression test.


Thank you, both. Is there some other fields that we should propagate
from the original error message that we're missing? Like, context and
file/line information? Or are those left out on purpose? I wonder if we
should have a more wholesale approach, and store the whole ErrorData
struct somewhere, and only add some extra context information with
errcontext().


In case of SPI errors we're preserving the following from the original 
ErrorData:


 * sqlerrcode (as of Mika's patch)
 * detail
 * hint
 * query
 * internalpos

that leaves us with the following which are not preserved:

 * message
 * context
 * detail_log

The message is being constructed from the Python exception name and I 
think that's useful. The context is being taken by the traceback string. 
I'm not sure if detail_log is ever set in these types of errors, 
probably not? So I guess we're safe.


The problem with storing the entire ErrorData struct is that this 
information has to be transformed to Python objects, because we attach 
it to the Python exception that gets raised and in case it bubbles all 
the way up to the topmost PL/Python function, we recover these Python 
objects and use them to construct the ereport call. While the exception 
is inside Python, user code can interact with it, so it'd be hard to 
have C pointers to non-Python stuff there.


Cheers,
Jan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python SQL error code pass-through

[Heikki Linnakangas]

On 24.11.2011 10:07, Jan Urbański wrote:

On 23/11/11 17:24, Mika Eloranta wrote:

Hi all,

[PL/Python in 9.1 does not preserve SQLSTATE of errors]


Oops, you're right, it's a regression from 9.0 behaviour.

The fix looks good to me, I changed one place to indent with tabs
instead of spaces and added a regression test.


Thank you, both. Is there some other fields that we should propagate 
from the original error message that we're missing? Like, context and 
file/line information? Or are those left out on purpose? I wonder if we 
should have a more wholesale approach, and store the whole ErrorData 
struct somewhere, and only add some extra context information with 
errcontext().



I think this should be backpatched to 9.1, no?


Yeah, it should. Your patch probably makes most sense for backpatching, 
even if we do something more radical on master.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python SQL error code pass-through

[Jan Urbański]

On 23/11/11 17:24, Mika Eloranta wrote:

Hi all,

[PL/Python in 9.1 does not preserve SQLSTATE of errors]


Oops, you're right, it's a regression from 9.0 behaviour.

The fix looks good to me, I changed one place to indent with tabs 
instead of spaces and added a regression test.


I think this should be backpatched to 9.1, no?

Thanks for the report and the patch!

Cheers,
Jan
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
index dbf19fd..bab07fb 100644
*** a/src/pl/plpython/expected/plpython_error.out
--- b/src/pl/plpython/expected/plpython_error.out
*** CONTEXT:  PL/Python function "specific_e
*** 351,356 
--- 351,378 
   
  (1 row)
  
+ /* SPI errors in PL/Python functions should preserve the SQLSTATE value
+  */
+ CREATE FUNCTION python_unique_violation() RETURNS void AS $$
+ plpy.execute("insert into specific values (1)")
+ plpy.execute("insert into specific values (1)")
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$
+ begin
+ begin
+ perform python_unique_violation();
+ exception when unique_violation then
+ return 'ok';
+ end;
+ return 'not reached';
+ end;
+ $$ language plpgsql;
+ SELECT catch_python_unique_violation();
+  catch_python_unique_violation 
+ ---
+  ok
+ (1 row)
+ 
  /* manually starting subtransactions - a bad idea
   */
  CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/expected/plpython_error_0.out b/src/pl/plpython/expected/plpython_error_0.out
index b2194ff..6cb2ed0 100644
*** a/src/pl/plpython/expected/plpython_error_0.out
--- b/src/pl/plpython/expected/plpython_error_0.out
*** CONTEXT:  PL/Python function "specific_e
*** 351,356 
--- 351,378 
   
  (1 row)
  
+ /* SPI errors in PL/Python functions should preserve the SQLSTATE value
+  */
+ CREATE FUNCTION python_unique_violation() RETURNS void AS $$
+ plpy.execute("insert into specific values (1)")
+ plpy.execute("insert into specific values (1)")
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$
+ begin
+ begin
+ perform python_unique_violation();
+ exception when unique_violation then
+ return 'ok';
+ end;
+ return 'not reached';
+ end;
+ $$ language plpgsql;
+ SELECT catch_python_unique_violation();
+  catch_python_unique_violation 
+ ---
+  ok
+ (1 row)
+ 
  /* manually starting subtransactions - a bad idea
   */
  CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 93e8043..afd5dfc 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*** static char *PLy_procedure_name(PLyProce
*** 383,389 
  static void
  PLy_elog(int, const char *,...)
  __attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
! static void PLy_get_spi_error_data(PyObject *exc, char **detail, char **hint, char **query, int *position);
  static void PLy_traceback(char **, char **, int *);
  
  static void *PLy_malloc(size_t);
--- 383,389 
  static void
  PLy_elog(int, const char *,...)
  __attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
! static void PLy_get_spi_error_data(PyObject *exc, int *sqlerrcode, char **detail, char **hint, char **query, int *position);
  static void PLy_traceback(char **, char **, int *);
  
  static void *PLy_malloc(size_t);
*** PLy_spi_exception_set(PyObject *excclass
*** 4441,4447 
  	if (!spierror)
  		goto failure;
  
! 	spidata = Py_BuildValue("(zzzi)", edata->detail, edata->hint,
  			edata->internalquery, edata->internalpos);
  	if (!spidata)
  		goto failure;
--- 4441,4447 
  	if (!spierror)
  		goto failure;
  
! 	spidata = Py_BuildValue("(izzzi)", edata->sqlerrcode, edata->detail, edata->hint,
  			edata->internalquery, edata->internalpos);
  	if (!spidata)
  		goto failure;
*** PLy_elog(int elevel, const char *fmt,...
*** 4481,4486 
--- 4481,4487 
  			   *val,
  			   *tb;
  	const char *primary = NULL;
+ 	intsqlerrcode = 0;
  	char	   *detail = NULL;
  	char	   *hint = NULL;
  	char	   *query = NULL;
*** PLy_elog(int elevel, const char *fmt,...
*** 4490,4496 
  	if (exc != NULL)
  	{
  		if (PyErr_GivenExceptionMatches(val, PLy_exc_spi_error))
! 			PLy_get_spi_error_data(val, &detail, &hint, &query, &position);
  		else if (PyErr_GivenExceptionMatches(val, PLy_exc_fatal))
  			elevel = FATAL;
  	}
--- 4491,4497 
  	if (exc != NULL)
  	{
  		if (PyErr_GivenExceptionMatches(val, PLy_exc_spi_error))
! 			PLy_get_spi_error_data(val, &sqlerrcode, &detail, &hint, &query, &position);
  		else if (PyErr_GivenExceptionMatches(val, PLy_exc_fatal))
  			elevel = FATAL;
  	}
*** PLy_elog(int elevel, const char *fmt,...
*** 4531,4537 
  	PG_TRY();
  	{
  		ereport(elevel,
! (errmsg_internal("

[HACKERS] PL/Python SQL error code pass-through

[Mika Eloranta]

Hi all,

Here's a little SQL snippet that exposes an apparent regression in the 9.1.x 
PL/Python behavior:

---clip---
# cat foo.sql 
\set VERBOSITY 'verbose'

CREATE table bar (a INTEGER CONSTRAINT hello CHECK (a > 1));

CREATE OR REPLACE FUNCTION foo ()
  RETURNS integer
AS $$
  plpy.execute("INSERT INTO bar (a) VALUES (2)")
  plpy.execute("INSERT INTO bar (a) VALUES (1)")
  return 123
$$ LANGUAGE plpythonu;

SELECT * FROM foo();
---clip---


PostgreSQL 9.0 behavior:

---clip---
# psql < foo.sql 
CREATE TABLE
CREATE FUNCTION
WARNING:  01000: PL/Python: plpy.SPIError: unrecognized error in 
PLy_spi_execute_query
CONTEXT:  PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:3532
ERROR:  23514: new row for relation "bar" violates check constraint "hello"
CONTEXT:  SQL statement "INSERT INTO bar (a) VALUES (1)"
PL/Python function "foo"
LOCATION:  ExecConstraints, execMain.c:1330
---clip---

Note the proper 23514 error code.


PostgreSQL 9.1.1 behavior:

---clip---
# psql < foo.sql 
ERROR:  42P07: relation "bar" already exists
LOCATION:  heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR:  XX000: spiexceptions.CheckViolation: new row for relation "bar" 
violates check constraint "hello"
CONTEXT:  Traceback (most recent call last):
  PL/Python function "foo", line 3, in 
plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:4502
---clip---

In fact, all SQL error that occur within PL/Python seem to be returned with the 
"XX000" error code. This is a bit of a problem for client-side logic that 
detects e.g. constraint violations based on the SQL error code.

A small patch that includes passing thru the SQL error code is attached.


Test run with PostgreSQL 9.1.1 + patch:

---clip---
# psql < foo.sql 
ERROR:  42P07: relation "bar" already exists
LOCATION:  heap_create_with_catalog, heap.c:1011
CREATE FUNCTION
ERROR:  23514: spiexceptions.CheckViolation: new row for relation "bar" 
violates check constraint "hello"
CONTEXT:  Traceback (most recent call last):
  PL/Python function "foo", line 4, in 
plpy.execute("INSERT INTO bar (a) VALUES (1)")
PL/Python function "foo"
LOCATION:  PLy_elog, plpython.c:4504
---clip---

Cheers!

- Mika


0001-PL-Python-SQL-error-code-pass-through.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python custom datatype parsers

[Peter Eisentraut]

On tis, 2011-11-08 at 16:08 -0500, Andrew Dunstan wrote:
> 
> On 03/01/2011 11:50 AM, Peter Eisentraut wrote:
> > On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote:
> >> I believe it's (b). But as we don't have time for that discussion that
> >> late in the release cycle, I think we need to consider it identical to (c).
> > As I previously mentioned, I think that there should be an SQL-level way
> > to tie together languages and types.  I previously mentioned the
> > SQL-standard command CREATE TRANSFORM as a possibility.  I've had this
> > on my PL/Python TOTHINK list for a while.  Thankfully you removed all
> > the items ahead of this one, so I'll think of something to do in 9.2.
> >
> > Of course we'll be able to use the actual transform code that you
> > already wrote.
> >
> 
> Peter,
> 
> Did you make any progress on this?

No, but it's still somewhere on my list.  I saw your blog post related
to this.

I think the first step would be to set up some catalog infrastructure
(without DDL commands and all that overhead), and try to adapt the big
"case" statement of an existing language to that, and then check whether
that works, performance, etc.

Some other concerns of the top of my head:

- Arrays: Would probably not by handled by that.  So this would not be
able to handle, for example, switching the array handling behavior in
PL/Perl to ancient compatible mode.

- Range types: no idea

I might work on this, but not before December, would be my guess.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python custom datatype parsers

[Andrew Dunstan]

On 03/01/2011 11:50 AM, Peter Eisentraut wrote:

On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote:

I believe it's (b). But as we don't have time for that discussion that
late in the release cycle, I think we need to consider it identical to (c).

As I previously mentioned, I think that there should be an SQL-level way
to tie together languages and types.  I previously mentioned the
SQL-standard command CREATE TRANSFORM as a possibility.  I've had this
on my PL/Python TOTHINK list for a while.  Thankfully you removed all
the items ahead of this one, so I'll think of something to do in 9.2.

Of course we'll be able to use the actual transform code that you
already wrote.



Peter,

Did you make any progress on this?

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: No stack trace for an exception

[Sushant Sinha]

On Thu, 2011-07-21 at 15:31 +0200, Jan Urbański wrote:
> On 21/07/11 15:27, Sushant Sinha wrote:
> > I am using plpythonu on postgres 9.0.2. One of my python functions was
> > throwing a TypeError exception. However, I only see the exception in the
> > database and not the stack trace. It becomes difficult to debug if the
> > stack trace is absent in Python.
> > 
> > logdb=# select get_words(forminput) from fi;   
> > ERROR:  PL/Python: TypeError: an integer is required
> > CONTEXT:  PL/Python function "get_words"
> > 
> > And here is the error if I run that function on the same data in python:
> > 
> > [traceback]
> > 
> > Is this a known problem or this needs addressing?
> 
> Yes, traceback support in PL/Python has already been implemented and is
> a new feature that will be available in PostgreSQL 9.1.
> 
> Cheers,
> Jan

Thanks Jan! Just one more reason to try 9.1.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: No stack trace for an exception

[Jan Urbański]

On 21/07/11 15:27, Sushant Sinha wrote:
> I am using plpythonu on postgres 9.0.2. One of my python functions was
> throwing a TypeError exception. However, I only see the exception in the
> database and not the stack trace. It becomes difficult to debug if the
> stack trace is absent in Python.
> 
> logdb=# select get_words(forminput) from fi;   
> ERROR:  PL/Python: TypeError: an integer is required
> CONTEXT:  PL/Python function "get_words"
> 
> And here is the error if I run that function on the same data in python:
> 
> [traceback]
> 
> Is this a known problem or this needs addressing?

Yes, traceback support in PL/Python has already been implemented and is
a new feature that will be available in PostgreSQL 9.1.

Cheers,
Jan

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PL/Python: No stack trace for an exception

[Peter Geoghegan]

On 21 July 2011 14:27, Sushant Sinha  wrote:
> I am using plpythonu on postgres 9.0.2. One of my python functions was
> throwing a TypeError exception. However, I only see the exception in the
> database and not the stack trace. It becomes difficult to debug if the
> stack trace is absent in Python.
>
> logdb=# select get_words(forminput) from fi;
> ERROR:  PL/Python: TypeError: an integer is required
> CONTEXT:  PL/Python function "get_words"
>
>
> And here is the error if I run that function on the same data in python:
>
> Traceback (most recent call last):
>  File "valid.py", line 215, in 
>    parse_query(result['forminput'])
>  File "valid.py", line 132, in parse_query
>    dateobj = datestr_to_obj(columnHash[column])
>  File "valid.py", line 37, in datestr_to_obj
>    dateobj = datetime.date(words[2], words[1], words[0])
> TypeError: an integer is required
>
>
> Is this a known problem or this needs addressing?

Traceback information will be added to PL/Python errors in Postgres
9.1, due out in about September.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PL/Python: No stack trace for an exception

[Sushant Sinha]

I am using plpythonu on postgres 9.0.2. One of my python functions was
throwing a TypeError exception. However, I only see the exception in the
database and not the stack trace. It becomes difficult to debug if the
stack trace is absent in Python.

logdb=# select get_words(forminput) from fi;   
ERROR:  PL/Python: TypeError: an integer is required
CONTEXT:  PL/Python function "get_words"


And here is the error if I run that function on the same data in python:

Traceback (most recent call last):
  File "valid.py", line 215, in 
parse_query(result['forminput'])
  File "valid.py", line 132, in parse_query
dateobj = datestr_to_obj(columnHash[column])
  File "valid.py", line 37, in datestr_to_obj
dateobj = datetime.date(words[2], words[1], words[0])
TypeError: an integer is required


Is this a known problem or this needs addressing?

Thanks,
Sushant.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python tracebacks v2

[Peter Eisentraut]

On Wed, 2011-04-06 at 23:54 +0200, Jan Urbański wrote:
> > Ouch, just today I found a flaw in this, namely that it assumes the
> > lineno from the traceback always refers to the PL/Python function. If
> > you create a PL/Python function that imports some code, runs it, and
> > that code raises an exception, PLy_traceback will get utterly confused.
> > 
> > Working on a fix...
> 
> Here's the fix.

Committed.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python tracebacks v2

[Jan Urbański]

On 06/04/11 22:16, Jan Urbański wrote:
> On 06/04/11 21:38, Peter Eisentraut wrote:
>> On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote:
>>> I finally got around to updating the PL/Python tracebacks patch. The
>>> other day I was writing some very simple PL/Python code and the lack of
>>> tracebacks is extremely annoying.
>>
>> I tweaked this a bit to make the patch less invasive, and then committed
>> it. :)
> 
> Ouch, just today I found a flaw in this, namely that it assumes the
> lineno from the traceback always refers to the PL/Python function. If
> you create a PL/Python function that imports some code, runs it, and
> that code raises an exception, PLy_traceback will get utterly confused.
> 
> Working on a fix...

Here's the fix.

The actual bug was funny. The traceback code was fetching the file line
from the traceback and trying to get that line from the original source
to print it. But sometimes that line was refering to a different source
file, like when the exception originated from an imported module.

In my testing I accidentally had the error (in a separate module) on
line 2, so the traceback code tried to fetch line 2 of the function,
which was completely whitespace. This can never happen in theory,
because you can't have a frame starting at an all-whitespace line. The
code to get that line was misbehaving and trying to do a malloc(-2),
which in turn was causing an "ERROR invalid memory allocation".

All that is fixed with the attached patch.

Cheers,
Jan

PS: and thanks for committing that in the first place! :)

J
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 9352580..b2333b8 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*** get_source_line(const char *src, int lin
*** 4507,4512 
--- 4507,4520 
  	if (next == NULL)
  		return pstrdup(s);
  
+ 	/*
+ 	 * Sanity check, next < s if the line was all-whitespace, which should
+ 	 * never happen if Python reported an frame created on that line, but
+ 	 * check anyway.
+ 	 */
+ 	if (next < s)
+ 		return NULL;
+ 
  	return pnstrdup(s, next - s);
  }
  
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4603,4608 
--- 4611,4617 
  		PyObject	*volatile code = NULL;
  		PyObject	*volatile name = NULL;
  		PyObject	*volatile lineno = NULL;
+ 		PyObject	*volatile filename = NULL;
  
  		PG_TRY();
  		{
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4621,4626 
--- 4630,4639 
  			name = PyObject_GetAttrString(code, "co_name");
  			if (name == NULL)
  elog(ERROR, "could not get function name from Python code object");
+ 
+ 			filename = PyObject_GetAttrString(code, "co_filename");
+ 			if (filename == NULL)
+ elog(ERROR, "could not get file name from Python code object");
  		}
  		PG_CATCH();
  		{
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4628,4633 
--- 4641,4647 
  			Py_XDECREF(code);
  			Py_XDECREF(name);
  			Py_XDECREF(lineno);
+ 			Py_XDECREF(filename);
  			PG_RE_THROW();
  		}
  		PG_END_TRY();
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4638,4643 
--- 4652,4658 
  			char	*proname;
  			char	*fname;
  			char	*line;
+ 			char	*plain_filename;
  			long	plain_lineno;
  
  			/*
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4651,4656 
--- 4666,4672 
  fname = PyString_AsString(name);
  
  			proname = PLy_procedure_name(PLy_curr_procedure);
+ 			plain_filename = PyString_AsString(filename);
  			plain_lineno = PyInt_AsLong(lineno);
  
  			if (proname == NULL)
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4662,4668 
  	&tbstr, "\n  PL/Python function \"%s\", line %ld, in %s",
  	proname, plain_lineno - 1, fname);
  
! 			if (PLy_curr_procedure)
  			{
  /*
   * If we know the current procedure, append the exact
--- 4678,4686 
  	&tbstr, "\n  PL/Python function \"%s\", line %ld, in %s",
  	proname, plain_lineno - 1, fname);
  
! 			/* the code object was compiled with "" as the filename */
! 			if (PLy_curr_procedure && plain_filename != NULL &&
! strcmp(plain_filename, "") == 0)
  			{
  /*
   * If we know the current procedure, append the exact
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4670,4676 
   * traceback.py module behavior.  We could store the
   * already line-split source to avoid splitting it
   * every time, but producing a traceback is not the
!  * most important scenario to optimize for.
   */
  line = get_source_line(PLy_curr_procedure->src, plain_lineno);
  if (line)
--- 4688,4696 
   * traceback.py module behavior.  We could store the
   * already line-split source to avoid splitting it
   * every time, but producing a traceback is not the
!  * most important scenario to optimize for.  However,
!  * do not go as far as traceback.py in reading the source
!  * of imported modules.
   */
 

Re: [HACKERS] pl/python tracebacks v2

[Jan Urbański]

On 06/04/11 21:38, Peter Eisentraut wrote:
> On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote:
>> I finally got around to updating the PL/Python tracebacks patch. The
>> other day I was writing some very simple PL/Python code and the lack of
>> tracebacks is extremely annoying.
> 
> I tweaked this a bit to make the patch less invasive, and then committed
> it. :)

Ouch, just today I found a flaw in this, namely that it assumes the
lineno from the traceback always refers to the PL/Python function. If
you create a PL/Python function that imports some code, runs it, and
that code raises an exception, PLy_traceback will get utterly confused.

Working on a fix...

Jan

PS: obviously it'd be great to have PL/Python traceback support in 9.1,
but I sure hope we'll get some testing in beta for issues like this...

J

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python tracebacks v2

[Peter Eisentraut]

On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote:
> I finally got around to updating the PL/Python tracebacks patch. The
> other day I was writing some very simple PL/Python code and the lack of
> tracebacks is extremely annoying.

I tweaked this a bit to make the patch less invasive, and then committed
it. :)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pl/python tracebacks v2

[Peter Geoghegan]

On 20 March 2011 23:40, Jan Urbański  wrote:
> I'll update the commitfest app for the 2011-Next commitfest, but if
> someone would like to pick this up and include it in the 9.1 PL/Python
> revamp pack, I'd be thrilled.

I would also be thrilled. I definitely share your sense of frustration
about the lack of tracebacks available when writing pl/python.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pl/python - thanks!

[Jan Urbański]

Hi,

I just wanted to thank everyone involved in shepherding the PL/Python
patches into the master repo, the testers, reviewers, commenters and
especially Peter, for their help and diligence.

The outstanding tracebacks patch is still being worked on, but
irrelevant of whether it will make it or not, I think PL/Python is now
in a significantly better shape than when we started.

Thanks again!
Jan

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers