Christoph Zwerschke wrote:

> Am 15.09.2016 um 04:34 schrieb raf:
> >> The question for me is not how frequently arrays are used, but how
> >> frequently arrays with non-default start indices are used. Note that the
> >> ARRAY constructor in Postgres doesn't even have a way to set a different
> >> start index (as far as I know).
> >
> > Yes, they're usually created by something like:
> >
> >   update some_table set array_column[index_other_than_1] = value...
> Right, if you use an index < 1 or update an empty array, the start index
> would be changed. I still don't think this is something that is frequently
> done, but yes, it's possible.
> > I've never really thought of them as having a non-default start
> > index. I've always just thought of the '[#:#]={}' notation as a
> > Postgres-specific "compression" format which needed to be
> > "decompressed" when fetched but that seems not to be the case
> > (and it doesn't explain negative start indexes).
> Not really compression, since all empty values are returned as NULL.
> Actually the end index is redundant in this notation.
> > I suspect that the speed difference is not because of the array
> > parser. There must some other reason so I've attached the test
> > program I used rather than one that just tests the parser in
> > isolation. The array parser in the attachment is slow and only
> > handles 1-dimensional arrays. I think it's safe to say that the
> > C parser would be much faster.
> I think so. But it may point to a performance degradation elsewhere, so I'd
> like to check this. Did you forget to attach the test?

Oops. Sorry about that. Here it is.

> > I'm not sure. I'd be happy for it to return None for indexes
> > between 0 and the "real" start but I wouldn't want it to return
> > None for indexes past the end even though that would mimic
> > Postgres behaviour. I'd rather it behaved like a Python list
> > with enough None values inserted at the beginning to make the
> > indexes match (although being off-by-one of course). In other
> > words, I'd want len(a) in Python to return the same value as
> > array_upper(a, 1) in Postgres. But it sounds like that's too
> > tacky which is fair enough. Just because it's what I want
> > doesn't mean that's what anyone else would want.
> Exactly. As you see, there are many different ways to implement this and if
> we do it one way, there is always somebody who will complain.
> Therefore it is best to implement only the straightforward, obvious way, but
> allow for customization.
> Again, the problem here is that Postgres arrays are different beasts than
> lists in Python, much more similar to Arrays in JavaScript.
> > If inserting None values into an ordinary Python list is not an
> > option, my next thought was maybe the client can request
> > non-optional behaviour somehow that means that, when fetching
> > arrays, if the start index is 1, a Python list is returned (as
> > is the case now) but if the start index is not 1, then a 2-tuple
> > is returned instead containing the Postgres start index as one
> > item and the list that would normally be returned as the other
> > item.
> I was also thinking along these lines, but the return value should always be
> of the same type, otherwise code will always have to handle both cases,
> making it more complicated, or risk raising errors. Also keep in mind you
> can also have multidimensional arrays with more than one start index.
> I'd rather return a list subtype with the start index as an additional
> attribute. However, the question is then how that list should behave when
> retrieving items. Since this is not obvious, we should make it customizable.
> So the idea is that we provide a function for changing the base Python class
> used for PG arrays, which should be a subclass of list. The array parser
> would then only set an additional "lower" attribute in instances of that
> class, and it's up to the class implementation how this is handled when
> items of the array are returned.
> Would that be reasonable solution?

That sounds fine as long as the client doesn't have to provide the list
subclass (unless they really want to).

Then, when fetching arrays, I can test for the existence of the "lower"
attribute and convert it to what the rest of my code is expecting.

> -- Christoph


#!/usr/bin/env python

# Select a 26-element integer array from Postgres 10000 times.
# If we have PyGreSQL-4, it is returned as a string and we parse it in Python.
# If we have PyGreSQL-5, it is returned as an array having been parsed in C.
# On my laptop, with PyGreSQL-4.2.2 it takes 4s and with PyGreSQL-5.0.1 it takes 9s.
# That's probably not due to the array parsing.

import pgdb
import re

def test():
	connection = pgdb.connect(host='XXX', database='XXX', user='XXX', password='XXX')
	cursor = connection.cursor()
	done = 0
	sql = "select cast('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}' as integer[]) from pay_group where code = '61*ADV'"
	for i in range(10000):
		rows = cursor.fetchall()
		a = rows[0][0]
		if isinstance(rows[0][0], basestring):
			aa = get_int_array(a)
			if not done:
				done = 1
				print('PyGreSQL-4 Python array parser')
			aa = a
			if not done:
				done = 1
				print('PyGreSQL-5 C array parser')
	print('%r %s' % (aa, type(aa)))

def r(pattern, opts=''):
	r(pattern, opts='') -> compiled regular expression object

	Like re.compile() but with more compact options. opts is a string containing
	any of the characters 'ilmsxu' each of which corresponds to re.I, re.L etc.
	If the pattern is a unicode object, the option re.U is automatically included.
	usage: pattern = r('([a-e])\d', 'i').
	# Not a string? Must already be compiled
	if not isinstance(pattern, basestring):
		return pattern
	flags, odict = 0, { 'i': re.I, 'l': re.L, 'm': re.M, 's': re.S, 'x': re.X, 'u': re.U }
	for o in opts:
		flags |= odict[o]
	if isinstance(pattern, unicode):
		flags |= re.U
	recomp = re.compile(pattern, flags)
	return recomp

def m(pattern, text, opts='', pos=0, endpos=None):
	m(pattern, text, opts='', pos=0, endpos=None) -> re.MatchObject

	Like but with more compact options. See r().

	usage: m(pattern, text)
	if endpos is None:
		endpos = len(text)
	return r(pattern, opts).search(text, pos, endpos)

def get_int_array(val):
	if val is None:
		return None
	return [get_int(v) for v in _get_array(val)]

def get_int(val):
	'''Interpret val as a Postgres int value (really a string) and
	return the int equivalent. Called by subclasses in get_data().'''
	# If coming from postgres via python (pgdb module)
	if val is None:
		return None
	if isinstance(val, (int, long)):
		return val
	if isinstance(val, basestring):
			return int(val)
		except ValueError:
	raise Exception('Unexpected int syntax: %r' % val)

def _get_array(val):
	'''Parses the string val containing a Postges SQL array literal.
	Returns an array of strings which can be further parsed by other
	methods. If val is already an array, it is returned unaltered.
	Any elements that are the unquoted string "NULL" will be replaced
	by None. Only 1-dimensional arrays are supported.'''
	# If coming from python
	if isinstance(val, list):
		return val
	# If coming from postgres via python (pgdb module 2008)
	if val is None:
		return None
	if val == '{}':
		return []
	# Convert '[17:35]={...}' into complete '{...}' before parsing
	match = m('^\[(\d+):(\d+)\]={', val) if val[0] == '[' else None
	if match is not None:
		itext, ftext =,
		i = int(itext)
		val = '{' + ','.join(['null'] * (i - 1)) + ',' + val[len(itext) + len(ftext) + 5:]
	if val[0] == '{' and val[-1] == '}':
		if set(val[1:-1]).isdisjoint(set('{}"\'')):
			# 1D, no quotes, easy
			return [None if v == 'NULL' or v == 'null' else v for v in val[1:-1].split(',')]
		# 1D still, but handle quotes
		start_state, out_state, inq_state, in_state, end_state = 'start', 'out', 'inq', 'in', 'end'
		i, end, state, vals, v, q = 0, len(val), start_state, [], '', ''
		while i < end:
			c = val[i]
			# print('i %d c %s %s q %s v %s vals %s' % (i, c, state, q, v, repr(vals)))
			if state is start_state:
				# Before {
				if c == '{':
					# After {
					state, vals = out_state, []
					raise Exception('Unexpected array syntax: %s (pos %d)' % (val, i))
			elif state is out_state:
				# Outside elements
				if c == '"':
					# Start quoted string element
					state, q, v = inq_state, c, ''
				elif c == ',':
					# Append previous element
					vals.append(v); v = ''
				elif c == '}':
					# After }
					state = end_state; vals.append(v); v = ''
					# Start unquoted element
					state, v = in_state, c
			elif state is inq_state:
				# Inside quoted element
				if c == '\\':
					# Backslash-quoted character
					if i == end - 1:
						# No next character!
						raise Exception('Unexpected array syntax: %s (pos %d)' % (val, i))
					v += val[i + 1]; i += 1
				elif c == q:
					# End of quoted string literal
					state = out_state; q = ''
					# Other character in quoted string literal
					v += c
			elif state is in_state:
				# Inside unquoted element
				if c == ',':
					# End of element
					state = out_state; vals.append(v); v = ''
				elif c == '}':
					# End of last element
					state = end_state; vals.append(v); v = ''
					# Other character in unquoted element
					v += c
			elif state is end_state:
				# Trailing characters after }
				raise Exception('Unexpected array syntax: %s (pos %d)' % (val, i))
			i += 1
		if state is not end_state:
			raise Exception('Unexpected array syntax: %s (end)' % val)
		return [None if v == 'NULL' or v == 'null' else v for v in vals]
	raise Exception('Unexpected array syntax: %s' % val)

if __name__ == '__main__':

PyGreSQL mailing list

Reply via email to