Re: [GENERAL] Work table

2013/10/27 Robert James > On 10/27/13, Thomas Kellerer wrote: > > Robert James wrote on 27.10.2013 20:47: > >> I'm using Postgres for data analysis (interactive and batch). I need > >> to focus the analysis on a subset of one table, and, for both > >> performance and simplicity, have a function

[GENERAL] Replication by file syncing and data directory permissions

Hello All, I am trying to automate setting up a hot standby slave replication on Ubuntu Precise, with PostgreSQL 9.1, following the Binary Replication Tutorial: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial . (Distribution-specific conventions: postmaster runs as a dedicated sy

[GENERAL] Table partitioning

I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like the idea of creating and dropping tables all the time. I'm thinking of simply creating 12 child tables, in which the check condition will be, for example, date_part('month'', time

Re: [GENERAL] Table partitioning

On 2013-10-28 12:27, Herouth Maoz wrote: I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like the idea of creating and dropping tables all the time. I'm thinking of simply creating 12 child tables, in which the check condition wil

Re: [GENERAL] Table partitioning

Thanks. Assuming there is an index on the time_arrived column, and that there are about 10.5 million records in each child table, how bad will performance be if the query actually accesses all the 12 tables? Will it be as bad as using the full table? On 28/10/2013, at 18:31, Elliot wrote: > On

Re: [GENERAL] Table partitioning

On 2013-10-28 12:47, Herouth Maoz wrote: Thanks. Assuming there is an index on the time_arrived column, and that there are about 10.5 million records in each child table, how bad will performance be if the query actually accesses all the 12 tables? Will it be as bad as using the full table?

Re: [GENERAL] Work table

It could be > 1 Million rows. SELECT is: SELECT * FROM another_table WHERE eventtime > (SELECT e FROM tags WHERE id = $1) AND eventtime < (SELECT e FROM tags WHERE id = $2) ; $1 and $2 are integers. SELECT ran just now, returning >1Million rows, i

Re: [GENERAL] Table partitioning

On 10/28/2013 09:27 AM, Herouth Maoz wrote: I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like the idea of creating and dropping tables all the time. What is slow about it? Inserting? Selecting? Deleting? Partitioning can assist

[GENERAL] Cursor Example Needed

When I execute the SELECT statement directly I get: psql:table.sql:28: out of memory for query result I've read the way around this is to use cursors. So I read and I see that I can use a FOR statement but I need that inside a function. So far, I've come up with this: > CREATE OR REPLACE FUNC

Re: [GENERAL] Table partitioning

Everything is slow about it - selects, deletes and inserts, that is. I don't do updates on that table. The inserts and deletes are less of an issue because they are done once a week. Of course it would be nicer if they were faster, but that's less of an issue. The real issue is with self-joins,

Re: [GENERAL] Cursor Example Needed

On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: > When I execute the SELECT statement directly I get: > > psql:table.sql:28: out of memory for query result psql will do this automatically if you tell it to: http://doginpool.blogspot.com/2011/11/retrieving-large-resultsets-from.html cursors c

Re: [GENERAL] Cursor Example Needed

On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote: > On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: >> When I execute the SELECT statement directly I get: >> >> psql:table.sql:28: out of memory for query result > > psql will do this automatically if you tell it to: > http://doginpool.blo

[GENERAL] pg_dump of only range of tables

Is there any way to do a pg_dump (or equivalent) of only part of a table? Say I want to send data to someone for only part of the table (expressable with a WHERE clause). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] pg_dump of only range of tables

On 28 October 2013 22:27, Robert James wrote: > Is there any way to do a pg_dump (or equivalent) of only part of a > table? Say I want to send data to someone for only part of the table > (expressable with a WHERE clause). > > > > Hi, you can use COPY or \COPY for that: http://www.postgresql.org/

Re: [GENERAL] pg_dump of only range of tables

On 10/28/2013 02:30 PM, Szymon Guz wrote: On 28 October 2013 22:27, Robert James mailto:srobertja...@gmail.com>> wrote: Is there any way to do a pg_dump (or equivalent) of only part of a table? Say I want to send data to someone for only part of the table (expressable with a WHERE c

Re: [GENERAL] Cursor Example Needed

On 10/28/2013 02:27 PM, Perry Smith wrote: On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote: On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: When I execute the SELECT statement directly I get: psql:table.sql:28: out of memory for query result psql will do this automatically if you t

Re: [GENERAL] Cursor Example Needed

On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote: > On 10/28/2013 02:27 PM, Perry Smith wrote: >> >> On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote: >> >>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: When I execute the SELECT statement directly I get: psql:table.sq

Re: [GENERAL] Work table

On 10/28/2013 12:50 PM, Robert James wrote: It could be > 1 Million rows. Well that would be a reason. SELECT is: SELECT * FROM another_table WHERE eventtime > (SELECT e FROM tags WHERE id = $1) AND eventtime < (SELECT e FROM tags WHERE id = $

Re: [GENERAL] Cursor Example Needed

On 10/28/2013 03:49 PM, Perry Smith wrote: On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote: On 10/28/2013 02:27 PM, Perry Smith wrote: On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote: On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: When I execute the SELECT statement directly I

Re: [GENERAL] Cursor Example Needed

On Mon, Oct 28, 2013 at 5:49 PM, Perry Smith wrote: > On Oct 28, 2013, at 5:21 PM, Adrian Klaver wrote: > >> On 10/28/2013 02:27 PM, Perry Smith wrote: >>> >>> On Oct 28, 2013, at 4:11 PM, Merlin Moncure wrote: >>> On Mon, Oct 28, 2013 at 4:06 PM, Perry Smith wrote: > When I execute th

Re: [GENERAL] Cursor Example Needed

On 10/28/2013 3:58 PM, Adrian Klaver wrote: The docs do a good job of illustrating: http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html thats for cursors created within a plpgsql function. I think what the OP wants is a top level cursor, which is a different thing... see ht

[GENERAL] json datatype and table bloat?

I have a table that is triggering my nagios database bloat alert regularly. Usually, I have to give it the vacuum full more than once to get it under the threshold. Today I tried repeatedly and cannot get the alert to resolve. I had a discussion with one of the primary developers about how the tab

Re: [GENERAL] Cursor Example Needed

On Oct 28, 2013, at 6:13 PM, John R Pierce wrote: > On 10/28/2013 3:58 PM, Adrian Klaver wrote: >> The docs do a good job of illustrating: >> >> http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html > > thats for cursors created within a plpgsql function. > > I think what the OP

Re: [GENERAL] Cursor Example Needed

On 10/28/2013 04:36 PM, Perry Smith wrote: On Oct 28, 2013, at 6:13 PM, John R Pierce wrote: On 10/28/2013 3:58 PM, Adrian Klaver wrote: The docs do a good job of illustrating: http://www.postgresql.org/docs/9.3/interactive/plpgsql-cursors.html thats for cursors created within a plpgsql f

Re: [GENERAL] Work table

On Mon, 28 Oct 2013 15:50:42 -0400 Robert James wrote: > It could be > 1 Million rows. > > SELECT is: > > SELECT * > FROM another_table > WHERE > eventtime > (SELECT e FROM tags WHERE id = $1) AND > eventtime < (SELECT e FROM tags WHERE id = $2) > ; > >

[GENERAL] INSERT/UPDATE statements sometimes choosing not to use PK index?

I have a relatively simple data load script, which upserts (UPDATE existing rows, INSERT new rows), which should be supported by the primary key index, the only index on this table: UPDATE destination SET ... FROM staging WHERE staging.pk = destination.pk; INSERT INTO destination SELECT ..

[GENERAL] copy 5.3000000000e+01 to bigint column error while Insert ok

when use insert command,we can insert 5.30e+01 to table,but when use copy command,we get this: ERROR: invalid input syntax for integer: "'5.30e+01'" CONTEXT: COPY tdsort_metrics, line 1, column monitor_value: "'5.30e+01'" any idea? Jov blog: http:amutu.com/blog

Re: [GENERAL] copy 5.3000000000e+01 to bigint column error while Insert ok

On 10/28/2013 11:34 PM, Jov wrote: when use insert command,we can insert 5.30e+01 to table,but when use copy command,we get this: ERROR: invalid input syntax for integer: "'5.30e+01'" CONTEXT: COPY tdsort_metrics, line 1, column monitor_value: "'5.30e+01'" any idea?