Re: problem with partitioned table and indexed json field

[Raphael Bauduin <rblists@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:

Raphael Bauduin <rblists@xxxxxxxxx> 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