Re: problem with partitioned table and indexed json field

Correction: It happens when I create said index on an empty *table*.

Raph

On Thu, Nov 7, 2013 at 11:10 AM, Raphael Bauduin <rblists(at)gmail(dot)com> wrote:

> Hi,
>
> I have narrowed it a bit. It happens when I create said index on an empty
> field. Here's the scenario to reproduce it:
>
> Let me know if you need more info
>
> Cheers
>
> Raph
>
>
> create table events(id SERIAL,
> timestamp timestamp,
> event json);
>
>
> create table events_2012_01( CHECK (timestamp>='2012-01-01' and
> timestamp<'2012-2-01' )) inherits (events) ;
> create table events_2012_02( CHECK (timestamp>='2012-02-01' and
> timestamp<'2012-3-01' )) inherits (events) ;
> insert into events_2012_01 (timestamp, event) values ('2012-01-22
> 08:38:56', '{"_id":"4f93c3a044650105b5074c9a","type":"t1"}');
> insert into events_2012_02 (timestamp, event) values ('2012-02-22
> 08:38:56', '{"_id":"5f93c3a044650105b5074c9a","type":"t2"}');
>
>
> -- create empty table
> create table events_2012_03( CHECK (timestamp>='2012-03-01' and
> timestamp<'2012-4-01' )) inherits (events) ;
> explain select max(event->>'_id') from events where event is not null;
> --OK
>
> --create index
> create index events_2012_03_event_id_index on events_2012_03
> ((event->>'_id'));
> explain select max(event->>'_id') from events where event is not null;
> --BANG
>
> drop index events_2012_03_event_id_index;
> explain select max(event->>'_id') from events where event is not null;
> --OK
>
>
>
> On Mon, Nov 4, 2013 at 8:39 AM, Raphael Bauduin <rblists(at)gmail(dot)com> wrote:
>
>> I'll look at providing such an example later this week.
>>
>> Raph
>>
>>
>> On Thu, Oct 31, 2013 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Raphael Bauduin <rblists(at)gmail(dot)com> writes:
>>> > An explain returns an error:
>>> > => explain select max(event->>'_id') from events;
>>> > ERROR: no tlist entry for key 2
>>>
>>> This is certainly a bug. Can we see a self-contained example that
>>> triggers that?
>>>
>>> regards, tom lane
>>>
>>
>>
>>
>> --
>> Web database: http://www.myowndb.com
>> Free Software Developers Meeting: http://www.fosdem.org
>>
>
>
>
> --
> Web database: http://www.myowndb.com
> Free Software Developers Meeting: http://www.fosdem.org
>

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