Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

[Francisco Olarte]

On Fri, Nov 8, 2013 at 5:09 AM, Victor Hooi  wrote:
> They think that it might be limited by the network, and how fast the
> PostgreSQL server can push the data across the internet. (The Postgres
> server and the box running the query are connected over the internet).

You previously said you had 600Mb. Over the internet. ¿ Is it a very
fat pipe ? Because otherwise the limitng factor is probably not the
speed at which postgres can push the resuts, but he throughput of your
link.

If, as you stated, you need a single transaction to get a 600Mb
snapshot I would recommend to dump it to disk, compressing on the fly
( you should get easily four o five fold reduction on a CSV file using
any decent compressor ), and then send the file. If you do not have
disk for the dump but can run programs near the server, you can try
compressing on the fly. If you have got none of this but have got
space for a spare table, use a select into, paginate this output and
drop it after. Or just look at the configs and set longer query times,
if your app NEEDS two hour queries, they can be enabled. But anyway,
doing a long transaction over the internet does not seem like a good
idea to me.

Francisco Olarte


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

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

[Victor Hooi]

Hi,

Aha, I spoke to the somebody, apparently we've actually got those values
set to 15 minutes currently...

They think that it might be limited by the network, and how fast the
PostgreSQL server can push the data across the internet. (The Postgres
server and the box running the query are connected over the internet).

Cheers,
Victor


On Fri, Nov 8, 2013 at 1:44 PM, Victor Hooi  wrote:

> Hi,
>
> Hmm, ok, I'll pass that onto our DBA/operations guys, and see if that
> helps.
>
> Do these settings still work if you only have a single Postgres instance?
> (I'll need to check out setup).
>
> So my understanding is that the default is 30 seconds (
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html)
> - but we're increasing it to 600 seconds, and that should give the COPY
> command enough time to pull down the data?
>
> As a rough guide, the dumped CSV file is around 600 Mb.
>
> Is there any other background you might be able to give on what you think
> might be happening, or how this might fix it?
>
> And you'd recommend tweaking these values over trying to chunk up the
> COPY/SELECT, is that right?
>
> I've just realised the LIMIT/ORDER thing may not work well to paginate,
> since there may be new records, or deleted records between each time I call
> it?
>
> Cheers,
> Victor
>
>
> On Fri, Nov 8, 2013 at 1:15 PM, wd  wrote:
>
>> Try this,
>>
>> max_standby_archive_delay = 600s# max delay before canceling queries
>> # when reading WAL from archive;
>> # -1 allows indefinite delay
>> max_standby_streaming_delay = 600s  # max delay before canceling queries
>> # when reading streaming WAL;
>> # -1 allows indefinite delay
>>
>> or try
>> pg_xlog_replay_pause()
>> pg_xlog_replay_resume()
>>
>>
>>
>>
>> On Fri, Nov 8, 2013 at 10:06 AM, Victor Hooi wrote:
>>
>>> Hi,
>>>
>>> We're using psycopg2 with COPY to dump CSV output from a large query.
>>>
>>> The actual SELECT query itself is large (both in number of
>>> records/columns, and also in width of values in columns), but still
>>> completes in around under a minute on the server.
>>>
>>> However, if you then use a COPY with it, it will often time out.
>>>
>>> We're using psycopg2 to run the command, the trace we get is something
>>> like:
>>>
>>> Traceback (most recent call last):
>>>   File "foo.py", line 259, in 
>>> jobs[job].run_all()
>>>   File "foo.py", line 127, in run_all
>>> self.export_to_csv()
>>>   File "foo.py", line 168, in export_to_csv
>>> cur.copy_expert(self.export_sql_statement, f)
>>> psycopg2.extensions.TransactionRollbackError: canceling statement due to
>>> conflict with recovery
>>>  DETAIL:  User was holding shared buffer pin for too long.
>>>
>>> My question is, what are some simple ways we can use to chunk up the
>>> query?
>>>
>>> Could we pull down a list of all the ids (auto-incrementing int), break
>>> this list up, then use a WHERE clause to break it up, running multiple COPY
>>> commands?
>>>
>>> Or would it be better to use LIMIT/OFFSET to break it up? I'm not sure
>>> how we'd figure out when we reached the end of the results set though
>>> (apart from just counting the results?).
>>>
>>> Or are there other approaches you guys could recommend?
>>>
>>> Cheers,
>>> Victor
>>>
>>
>>
>

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

[Victor Hooi]

Hi,

Hmm, ok, I'll pass that onto our DBA/operations guys, and see if that helps.

Do these settings still work if you only have a single Postgres instance?
(I'll need to check out setup).

So my understanding is that the default is 30 seconds (
http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html)
- but we're increasing it to 600 seconds, and that should give the COPY
command enough time to pull down the data?

As a rough guide, the dumped CSV file is around 600 Mb.

Is there any other background you might be able to give on what you think
might be happening, or how this might fix it?

And you'd recommend tweaking these values over trying to chunk up the
COPY/SELECT, is that right?

I've just realised the LIMIT/ORDER thing may not work well to paginate,
since there may be new records, or deleted records between each time I call
it?

Cheers,
Victor


On Fri, Nov 8, 2013 at 1:15 PM, wd  wrote:

> Try this,
>
> max_standby_archive_delay = 600s# max delay before canceling queries
> # when reading WAL from archive;
> # -1 allows indefinite delay
> max_standby_streaming_delay = 600s  # max delay before canceling queries
> # when reading streaming WAL;
> # -1 allows indefinite delay
>
> or try
> pg_xlog_replay_pause()
> pg_xlog_replay_resume()
>
>
>
>
> On Fri, Nov 8, 2013 at 10:06 AM, Victor Hooi  wrote:
>
>> Hi,
>>
>> We're using psycopg2 with COPY to dump CSV output from a large query.
>>
>> The actual SELECT query itself is large (both in number of
>> records/columns, and also in width of values in columns), but still
>> completes in around under a minute on the server.
>>
>> However, if you then use a COPY with it, it will often time out.
>>
>> We're using psycopg2 to run the command, the trace we get is something
>> like:
>>
>> Traceback (most recent call last):
>>   File "foo.py", line 259, in 
>> jobs[job].run_all()
>>   File "foo.py", line 127, in run_all
>> self.export_to_csv()
>>   File "foo.py", line 168, in export_to_csv
>> cur.copy_expert(self.export_sql_statement, f)
>> psycopg2.extensions.TransactionRollbackError: canceling statement due to
>> conflict with recovery
>>  DETAIL:  User was holding shared buffer pin for too long.
>>
>> My question is, what are some simple ways we can use to chunk up the
>> query?
>>
>> Could we pull down a list of all the ids (auto-incrementing int), break
>> this list up, then use a WHERE clause to break it up, running multiple COPY
>> commands?
>>
>> Or would it be better to use LIMIT/OFFSET to break it up? I'm not sure
>> how we'd figure out when we reached the end of the results set though
>> (apart from just counting the results?).
>>
>> Or are there other approaches you guys could recommend?
>>
>> Cheers,
>> Victor
>>
>
>

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

[wd]

Try this,

max_standby_archive_delay = 600s# max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
max_standby_streaming_delay = 600s  # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay

or try
pg_xlog_replay_pause()
pg_xlog_replay_resume()




On Fri, Nov 8, 2013 at 10:06 AM, Victor Hooi  wrote:

> Hi,
>
> We're using psycopg2 with COPY to dump CSV output from a large query.
>
> The actual SELECT query itself is large (both in number of
> records/columns, and also in width of values in columns), but still
> completes in around under a minute on the server.
>
> However, if you then use a COPY with it, it will often time out.
>
> We're using psycopg2 to run the command, the trace we get is something
> like:
>
> Traceback (most recent call last):
>   File "foo.py", line 259, in 
> jobs[job].run_all()
>   File "foo.py", line 127, in run_all
> self.export_to_csv()
>   File "foo.py", line 168, in export_to_csv
> cur.copy_expert(self.export_sql_statement, f)
> psycopg2.extensions.TransactionRollbackError: canceling statement due to
> conflict with recovery
>  DETAIL:  User was holding shared buffer pin for too long.
>
> My question is, what are some simple ways we can use to chunk up the query?
>
> Could we pull down a list of all the ids (auto-incrementing int), break
> this list up, then use a WHERE clause to break it up, running multiple COPY
> commands?
>
> Or would it be better to use LIMIT/OFFSET to break it up? I'm not sure how
> we'd figure out when we reached the end of the results set though (apart
> from just counting the results?).
>
> Or are there other approaches you guys could recommend?
>
> Cheers,
> Victor
>

[GENERAL] Breaking up a PostgreSQL COPY command into chunks?

[Victor Hooi]

Hi,

We're using psycopg2 with COPY to dump CSV output from a large query.

The actual SELECT query itself is large (both in number of records/columns,
and also in width of values in columns), but still completes in around
under a minute on the server.

However, if you then use a COPY with it, it will often time out.

We're using psycopg2 to run the command, the trace we get is something like:

Traceback (most recent call last):
  File "foo.py", line 259, in 
jobs[job].run_all()
  File "foo.py", line 127, in run_all
self.export_to_csv()
  File "foo.py", line 168, in export_to_csv
cur.copy_expert(self.export_sql_statement, f)
psycopg2.extensions.TransactionRollbackError: canceling statement due to
conflict with recovery
 DETAIL:  User was holding shared buffer pin for too long.

My question is, what are some simple ways we can use to chunk up the query?

Could we pull down a list of all the ids (auto-incrementing int), break
this list up, then use a WHERE clause to break it up, running multiple COPY
commands?

Or would it be better to use LIMIT/OFFSET to break it up? I'm not sure how
we'd figure out when we reached the end of the results set though (apart
from just counting the results?).

Or are there other approaches you guys could recommend?

Cheers,
Victor

Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?

[Adrian Klaver]

On 11/07/2013 11:07 AM, Greg Burek wrote:

On Wed, Nov 6, 2013 at 4:36 AM, Leonardo Carneiro
mailto:chesterma...@gmail.com>> wrote:

I don't think that there will be too much trouble, as long as you
follow every changelog tip (9.0->9.1, 9.1->9.2 and 9.2->9.3)


What if we don't follow the changelog tip? In this case, we have only
the 9.0 and 9.3 binaries installed and pg_upgrade directly to 9.3. Does
that cause fear?


Not so much fear as concern. One of my rules is that the good will take 
care of itself it is the bad you have to plan for. In this case it is 
not the nine changes that do not impact your code but the one that does. 
When going through the change logs the incompatibilities are listed at 
the top under the  Migration to Version 9.* header. It does not take to 
long to double check. If you want to save time just look at the major 
version changes, i.e 9.X versus 9.X.x as incompatibilities are allowed 
in major version changes. Not that they do not happen in minor releases, 
but that is generally done to fix a security/major bug and I do not 
recall any in the range you are looking at.


--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] Curious question about physical files to store database

[Martijn van Oosterhout]

On Tue, Nov 05, 2013 at 10:42:36PM +0800, Patrick Dung wrote:
> I have seen some databases product that allocate small number of large files.
> 
> Please correct me if I am wrong:
> 
> MySQL with InnoDB

Actually, InnoDB has a file-per-table mode which I tend to prefer. It
means that when I drop a partition I actually see the disk usage drop.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?

[Greg Burek]

On Wed, Nov 6, 2013 at 4:36 AM, Leonardo Carneiro wrote:

> I don't think that there will be too much trouble, as long as you follow
> every changelog tip (9.0->9.1, 9.1->9.2 and 9.2->9.3)
>
>
What if we don't follow the changelog tip? In this case, we have only the
9.0 and 9.3 binaries installed and pg_upgrade directly to 9.3. Does that
cause fear?

Re: [GENERAL] problem with partitioned table and indexed json field

[Tom Lane]

I wrote:
> It looks like the problem is we're building a MergeAppend plan and not
> getting the targetlist for the MergeAppend node right.

Found it --- simple oversight in building optimized min/max plans.
If you need a patch now, see
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=5d0731da521f090f80ea39529fe274ac6d6bffa1

regards, tom lane


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

Re: [GENERAL] After upgrade to 9.3, streaming replication fails to start--SOLVED

[Jeff Ross]

On 11/6/13, 12:26 PM, Jeff Ross wrote:


On 11/6/13, 11:32 AM, Jeff Janes wrote:
On Wed, Nov 6, 2013 at 9:40 AM, Jeff Ross > wrote:



_postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh
#!/bin/sh
backup_label=wykids_`date +%Y-%m-%d`
#remove any existing wal files on the standby
ssh dukkha.internal rm -rf /wal/*
#stop the standby server if it is running
ssh dukkha.internal sudo /usr/local/bin/svc -d
/service/postgresql.5432
psql -c "select pg_start_backup('$backup_label');" template1
rsync \
--copy-links \
--delete \
--exclude=backup_label \



Excluding backup_label is exactly the wrong thing to do.  The only 
reason backup_label is created in the first place is so that it can 
be copied to the replica, where it is needed.  It's existence on the 
master is a nuisance.



--exclude=postgresql.conf \
--exclude=recovery.done \
-e ssh -avz /var/postgresql/data.93.5432/ \
dukkha.internal:/var/postgresql/data.93.5432/
ssh dukkha.internal rm -f /var/postgresql/data.93.5432/pg_xlog/*
ssh dukkha.internal rm -f
/var/postgresql/data.93.5432/pg_xlog/archive_status/*
ssh dukkha.internal rm -f /var/postgresql/data.93.5432/pg_log/*
ssh dukkha.internal rm -f /var/postgresql/data.93.5432/postmaster.pid
ssh dukkha.internal ln -s /var/postgresql/recovery.conf
/var/postgresql/data.93.5432/recovery.conf
psql -c "select pg_stop_backup();" template1
ssh dukkha.internal sudo /usr/local/bin/svc -u
/service/postgresql.5432


_postgresql@nirvana:/var/postgresql $ sh -x start_hot_standby.sh
+ date +%Y-%m-%d
+ backup_label=wykids_2013-11-06
+ ssh dukkha.internal rm -rf /wal/*
+ ssh dukkha.internal sudo /usr/local/bin/svc -d
/service/postgresql.5432
+ rsync -e ssh /wal/ dukkha.internal:/wal/
skipping directory .



Where is the above rsync coming from?  It doesn't seem to be in the 
shell script you showed.


Anyway, I think you need to copy the wal over after you call 
pg_stop_backup, not before you call pg_start_backup.


Cheers,

Jeff


Hi Jeff,

Thanks for the reply.  Oops, I copied one of the many changes to the 
script, but not the one with the rsync to copy /wal from the primary 
to the standby.


I should have mentioned that wal archiving is setup and working from 
the primary to the standby.  It saves wal both on the locally on the 
primary and remotesly on the standby.


I moved the rsync line to copy wal from primary to secondary after 
pg_stop_backup but I'm still getting the same panic on the standby.


Here's the real, honest version of the script I use to start the hot 
standby:


_postgresql@nirvana:/var/postgresql $ cat start_hot_standby.sh
#!/bin/sh
backup_label=wykids_`date +%Y-%m-%d`
#remove any existing wal files on the secondary
ssh dukkha.internal "rm -rf /wal/*"
ssh dukkha.internal sudo /usr/local/bin/svc -d /service/postgresql.5432
psql -c "select pg_start_backup('$backup_label');" template1
rsync \
--copy-links \
--delete \
--exclude=backup_label \
--exclude=postgresql.conf \
--exclude=recovery.done \
-e ssh -avz /var/postgresql/data.93.5432/ \
dukkha.internal:/var/postgresql/data.93.5432/
ssh dukkha.internal "rm -f /var/postgresql/data.93.5432/pg_xlog/*"
ssh dukkha.internal "rm -f 
/var/postgresql/data.93.5432/pg_xlog/archive_status/*"

ssh dukkha.internal "rm -f /var/postgresql/data.93.5432/pg_log/*"
ssh dukkha.internal "rm -f /var/postgresql/data.93.5432/postmaster.pid"
ssh dukkha.internal "ln -s /var/postgresql/recovery.conf 
/var/postgresql/data.93.5432/recovery.conf"

psql -c "select pg_stop_backup();" template1
rsync -e ssh -avz /wal/ dukkha.internal:/wal/
ssh dukkha.internal sudo /usr/local/bin/svc -u /service/postgresql.5432

Here are the logs on the standby after running the above:

2013-11-06 11:56:30.792461500 <%> LOG:  database system was 
interrupted; last known up at 2013-11-06 11:52:22 MST

2013-11-06 11:56:30.800685500 <%> LOG:  entering standby mode
2013-11-06 11:56:30.800891500 <%> LOG:  invalid primary checkpoint record
2013-11-06 11:56:30.800930500 <%> LOG:  invalid secondary checkpoint 
record
2013-11-06 11:56:30.801004500 <%> PANIC:  could not locate a valid 
checkpoint record


Jeff
My apologies to Jeff--I'd missed his in-line comment above that I should 
*not* exclude the backup label from the rsync of the primary to the 
standby.  As soon as I removed that exclusion and with his other 
suggested change that I should copy the /wal from the primary to the 
standby after pg_stop_backup, streaming replication started on the 
standby exactly as it should.


Logs from the standby:

2013-11-07 09:21:15.273712500 <%> LOG:  database system was interrupted; 
last known up at 2013-11-07 09:16:05 MST

2013-11-07 09:21:15.286834500 <%> LOG:  entering standby mode
2013-11-07 09:21:16.873654500 <%> LOG:  restored log f

Re: [GENERAL] problem with partitioned table and indexed json field

[Tom Lane]

Raphael Bauduin  writes:
> The query is also problematic here, because it returns the full json, and
> not only the data I selected in the json.

Doh, right, you mentioned that in the original bug report, and now that
I'm paying a bit more attention I see it too.  I was looking for
some sort of error from running the query, not just wrong data.

It looks like the problem is we're building a MergeAppend plan and not
getting the targetlist for the MergeAppend node right.  I hacked EXPLAIN
very quickly to not fall over when it fails to find a sort key in the
node's targetlist, and here's what I see:

regression=# explain verbose select max(event->>'_id') from events where event 
is not null;
 QUERY PLAN 
 
-
 Result  (cost=58.75..58.76 rows=1 width=0)
   Output: $0
   InitPlan 1 (returns $0)
 ->  Limit  (cost=58.70..58.75 rows=1 width=32)
   Output: events.event
   ->  Merge Append  (cost=58.70..200.88 rows=3268 width=32)
 Sort Key: [no tlist entry for key 2]
 ->  Sort  (cost=0.01..0.02 rows=1 width=32)
   Output: events.event, ((events.event ->> '_id'::text))
   Sort Key: ((events.event ->> '_id'::text))
   ->  Seq Scan on public.events  (cost=0.00..0.00 rows=1 
width=32)
 Output: events.event, (events.event ->> 
'_id'::text)
 Filter: ((events.event IS NOT NULL) AND 
((events.event ->> '_id'::text) IS NOT NULL))
 ->  Sort  (cost=29.20..31.92 rows=1089 width=32)
   Output: events_2012_01.event, ((events_2012_01.event ->> 
'_id'::text))
   Sort Key: ((events_2012_01.event ->> '_id'::text))
   ->  Seq Scan on public.events_2012_01  (cost=0.00..23.75 
rows=1089 width=32)
 Output: events_2012_01.event, 
(events_2012_01.event ->> '_id'::text)
 Filter: ((events_2012_01.event IS NOT NULL) AND 
((events_2012_01.event ->> '_id'::text) IS NOT NULL))
 ->  Sort  (cost=29.20..31.92 rows=1089 width=32)
   Output: events_2012_02.event, ((events_2012_02.event ->> 
'_id'::text))
   Sort Key: ((events_2012_02.event ->> '_id'::text))
   ->  Seq Scan on public.events_2012_02  (cost=0.00..23.75 
rows=1089 width=32)
 Output: events_2012_02.event, 
(events_2012_02.event ->> '_id'::text)
 Filter: ((events_2012_02.event IS NOT NULL) AND 
((events_2012_02.event ->> '_id'::text) IS NOT NULL))
 ->  Index Scan Backward using events_2012_03_event_id_index on 
public.events_2012_03  (cost=0.15..63.30 rows=1089 width=32)
   Output: events_2012_03.event, (events_2012_03.event ->> 
'_id'::text)
   Index Cond: ((events_2012_03.event ->> '_id'::text) IS 
NOT NULL)
   Filter: (events_2012_03.event IS NOT NULL)
(29 rows)

So everything looks right for the individual table-scan subplans, but
something's going badly wrong when making the MergeAppend ...
dunno what yet.

regards, tom lane


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

Re: [GENERAL] problem with partitioned table and indexed json field

[Raphael Bauduin]

The query is also problematic here, because it returns the full json, and
not only the data I selected in the json.
Below, it should return only '_id', and not the whole json stored in event:

test3=> select max(event->>'_id') from events where event is not null;
  max

 {"_id":"5f93c3a044650105b5074c9a","type":"t2"}

Thanks

raph



On Thu, Nov 7, 2013 at 4:32 PM, Tom Lane  wrote:

> Raphael Bauduin  writes:
> > I have narrowed it a bit. It happens when I create said index on an empty
> > field. Here's the scenario to reproduce it:
>
> Thanks, I've reproduced the problem here.  The query still seems to run OK,
> it's just EXPLAIN that's falling over --- do you see the same?
>
> regards, tom lane
>



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

Re: [GENERAL] problem with partitioned table and indexed json field

[Tom Lane]

Raphael Bauduin  writes:
> I have narrowed it a bit. It happens when I create said index on an empty
> field. Here's the scenario to reproduce it:

Thanks, I've reproduced the problem here.  The query still seems to run OK,
it's just EXPLAIN that's falling over --- do you see the same?

regards, tom lane


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

Re: [GENERAL] problem with partitioned table and indexed json field

[Raphael Bauduin]

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

Raph


On Thu, Nov 7, 2013 at 11:10 AM, Raphael Bauduin  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  wrote:
>
>> I'll look at providing such an example later this week.
>>
>> Raph
>>
>>
>> On Thu, Oct 31, 2013 at 3:23 PM, Tom Lane  wrote:
>>
>>> Raphael Bauduin  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

Re: [GENERAL] problem with partitioned table and indexed json field

[Raphael Bauduin]

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  wrote:

> I'll look at providing such an example later this week.
>
> Raph
>
>
> On Thu, Oct 31, 2013 at 3:23 PM, Tom Lane  wrote:
>
>> Raphael Bauduin  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