Re: problem with partitioned table and indexed json field

It's postgresql 9.3, from the pgdg apt repository:
9.3.0-2.pgdg10.4+1

Raph

On Thu, Oct 31, 2013 at 1:48 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Oct 31, 2013 at 5:46 AM, Raphael Bauduin <rblists(at)gmail(dot)com>
> wrote:
> >
> > Hi,
> >
> > I have a partitioned table events, with one partition for each month, eg
> > events_2013_03. The partition is done on the field timestamp, and
> > constraints are set, but insertion of data is done in the partition
> directly
> > (so not with a trigger on the events table)
> > The field event is of type json, and has a field '_id', which I can
> access:
> >
> > => select event->>'_id' from events limit 1;
> > ?column?
> > --------------------------
> > 4f9a786f44650105b50aafc9
> >
> > I created an index on each partition of the table, but not on the events
> > table itself:
> > create index events_${y}_${m}_event_id_index on events_${y}_${m}
> > ((event->>'_id'));
> >
> > Querying the max event_id from a partition works fine:
> > => select max(event->>'_id') from events_2013_03;
> > max
> > --------------------------
> > 5158cdfe4465012cff522b74
> >
> >
> > However, requesting on the parent table does return the whole json field,
> > and not only the '_id':
> > => select max(event->>'_id') from events;
> > {"_id":"526eb3ad4465013e3e131a43","origin":..... }
> >
> > An explain returns an error:
> > => explain select max(event->>'_id') from events;
> > ERROR: no tlist entry for key 2
> >
> > This problem appeared when I created the indexes, and removing the index
> > make the explain work fine, but the plan implies a sequential scan on the
> > tables which is exactly what I wanted to avoid with the indexes.
> >
> > Does someone have an explanation, and possibly a way to solve this
> problem?
>
> wow, that looks like a bug. Can you post the specific postgres version?
>
> merlin
>

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org