Re: [GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread Merlin Moncure
On Thu, Jan 12, 2017 at 2:19 PM, bto...@computer.org wrote: > > > - Original Message - >> From: "Jonathan Vanasco" >> To: "pgsql-general general" >> Sent: Thursday, January 12, 2017 3:06:14 PM >> Subject: [GENERAL]

Re: [GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread David G. Johnston
On Thu, Jan 12, 2017 at 2:45 PM, Adrian Klaver wrote: > > > so our migration is then based on that `is_migrate` column: > > > > BEGIN; > > UPDATE table_a__live SET is_migrate = TRUE WHERE record_timestamp > < transaction_timestamp() AT TIME ZONE 'UTC' -

Re: [GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread Adrian Klaver
On 01/12/2017 12:06 PM, Jonathan Vanasco wrote: > I'm just wondering if there's a more efficient way of handling a certain > periodic data migration. > > We have a pair of tables with this structure: > > table_a__live > column_1 INT > column_2 INT >

Re: [GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread John R Pierce
On 1/12/2017 12:06 PM, Jonathan Vanasco wrote: I'm just wondering if there's a more efficient way of handling a certain periodic data migration. partition the tables by some date interval such as week (if you do this archiving weekly). each week, disconnect the oldest partition from the

Re: [GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread bto...@computer.org
- Original Message - > From: "Jonathan Vanasco" > To: "pgsql-general general" > Sent: Thursday, January 12, 2017 3:06:14 PM > Subject: [GENERAL] efficiently migrating 'old' data from one table to another > > I'm just wondering if

[GENERAL] efficiently migrating 'old' data from one table to another

2017-01-12 Thread Jonathan Vanasco
I'm just wondering if there's a more efficient way of handling a certain periodic data migration. We have a pair of tables with this structure: table_a__live column_1 INT column_2 INT record_timestamp TIMESTAMP table_a__archive

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread Ian Lewis
On Wed, Jan 11, 2017 at 5:36 PM, Adrian Klaver wrote: > > So what is the relationship of clients to sessions? Most of our client applications use one session. But, a few use multiple sessions, largely to support threaded access to the database. In our current setup, a

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

2017-01-12 Thread Jonathan Vanasco
On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote: > > Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither > PostgreSQL version or O/S, so we can't even be sure that is > an option. That is why I stated "I cannot confirm". I didn't think that would matter, but

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread Adrian Klaver
On 01/12/2017 12:37 AM, George Neuner wrote: On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce wrote: On 1/11/2017 2:07 PM, Ian Lewis wrote: I am working on porting from an SQL Anywhere server that has support for general temporary tables. It appears that PostgreSQL does

[GENERAL] RUM-index and support for storing BIGINT as part of a tsvector+timestamp

2017-01-12 Thread Andreas Joseph Krogh
Hi PostgresPro-guys.   I've asked this before but didn't get any response, so I'll try again. I know you have a lot on you plate...   On RUM's TODO-list is this:   * Allow multiple additional information (lexemes positions + timestamp). * Add support for arrays. Will any of these items

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread Berend Tober
Karsten Hilbert wrote: On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: I don't see where "call a setup function immediately after connecting" Sounds like a "login trigger", more generally an ON CONNECT event trigger, which we don't have at the moment as far as I know. One

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread Pavel Stehule
Hi 2017-01-12 10:06 GMT+01:00 Karsten Hilbert : > On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: > > > I don't see where "call a setup function immediately after connecting" > > Sounds like a "login trigger", more generally an ON CONNECT > event

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread Karsten Hilbert
On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: > I don't see where "call a setup function immediately after connecting" Sounds like a "login trigger", more generally an ON CONNECT event trigger, which we don't have at the moment as far as I know. One of the main arguments

Re: [GENERAL] Means to emulate global temporary table

2017-01-12 Thread George Neuner
On Wed, 11 Jan 2017 15:23:10 -0800, John R Pierce wrote: >On 1/11/2017 2:07 PM, Ian Lewis wrote: >> I am working on porting from an SQL Anywhere server that has support >> for general temporary tables. It appears that PostgreSQL does not have >> such support. > >postgres