Re: [GENERAL] Bulk processing & deletion

Unlogged tables can't be temporary. 2011/10/13, Ivan Voras : > On 13/10/2011 14:34, Alban Hertroys wrote: > >>> Any other ideas? >> >> CREATE TABLE to_delete ( >> job_created timestamp NOT NULL DEFAULT now(), >> fk_id int NOT NULL >> ); >> >> -- Mark for deletion >> INSERT INTO to_delete (fk_i

Re: [GENERAL] Tablespace files deleted during continuous run

On Thu, Oct 13, 2011 at 10:32 PM, Scott Marlowe wrote: > On Thu, Oct 13, 2011 at 10:12 PM, Vishnu S. wrote: >> Hi, >> >> >> The tablespace is created using CREATE TABLESPACE query. > > Is it a local drive?  USB or eSATA maybe? Also is it formatted NTFS? FAT? remote mounted via a network share?

Re: [GENERAL] Tablespace files deleted during continuous run

On Thu, Oct 13, 2011 at 10:12 PM, Vishnu S. wrote: > Hi, > > > The tablespace is created using CREATE TABLESPACE query. Is it a local drive? USB or eSATA maybe? > Also Slony-I replication is working on the tablespace for Master - slave > implementation. should work. I've done it before with

Re: [GENERAL] PostGIS: Approximating a house number from street address range

Thanks Andy for thinking about this for me. I tried using that function, but get this error: gc3=# SELECT gc3-# ST_AsText(the_geom) as street, strunamefr, l_adddirfg, l_hnumf, l_hnuml, l_stname_c, l_placenam, r_adddirfg, r_hnumf, r_hnuml, r_stname_c, r_placenam, gc3-# ST_Distance(ST_GeomFromText

Re: [GENERAL] exclusive OR possible within a where clause?

David Salisbury writes: > Short version, is there a way to implement an exclusive OR in a where clause? The boolean <> operator will do the trick. (x = y) <> (a = b) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Error: timestamp with timezone + interval is not immutable while creating index

On 10/13/2011 04:32 PM, Tom Lane wrote: Phil Couling writes: main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; ERROR: functions in index expression must be marked IMMUTABLE... timestamptz + interval is not immutable because the results can vary depending on timez

Re: [GENERAL] Error: timestamp with timezone + interval is not immutable while creating index

On 10/13/11 4:38 PM, Phil Couling wrote: Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an erro

[GENERAL] exclusive OR possible within a where clause?

I'm guessing that this isn't possible, but you guys are pretty smart. :) Short version, is there a way to implement an exclusive OR in a where clause? table1 dt1(timestamp) - 3 mins 5 mins 7 mins table2 dt2(timestamp), timedifference(interval) --- -- 4

Re: [GENERAL] Drill-downs and OLAP type data

Notes are below ... 2011/10/12 Ondrej Ivanič > Hi, > > > The *problem* with Greenplum is that it's ultra-expensive once you leave > the > > CE version - and you're not supposed to be using the CE version for > > commercial usage last I read the license. Has that changed? > > Not sure. I haven't

Re: [GENERAL] Error: timestamp with timezone + interval is not immutable while creating index

Phil Couling writes: > main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; > ERROR: functions in index expression must be marked IMMUTABLE > Does anyone know why adding two fields like this results in anything > other than an immutable function? Under what circumstance

Re: [GENERAL] Tablespace files deleted during continuous run

On Thu, Oct 13, 2011 at 6:55 AM, Vishnu S. wrote: > > I am using PostgreSQL 8.4 in windows.  I have  created a database and some > tables on it. Also created a table space and some tables in it. > > My application inserts data into these tables in every second. The > application is a continuous ru

[GENERAL] Error: timestamp with timezone + interval is not immutable while creating index

Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an error though: main=> create index foo_next_updat

[GENERAL] Error: timestamp with timezone + interval is not immutable while creating index

Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an error though: main=> create index foo_next_updat

Re: [GENERAL] I need to load mysql dump to postgres...

On Thu, 13 Oct 2011, unclebob wrote: Looks like it's not exactly what I need. It migrates data from db to db, but I need to get data from a file(mysql dump) and load it to postgres. It's a large file and I don't want to load it to mysql first and then migrate data. thanks. When you write, "m

Re: [GENERAL] Test for cascade delete in plpgsql

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Robert Fitzpatrick Sent: Thursday, October 13, 2011 4:39 PM To: PostgreSQL Subject: [GENERAL] Test for cascade delete in plpgsql My contacts table has a FK with cascade dele

Re: [GENERAL] I need to load mysql dump to postgres...

On 10/13/2011 05:31 AM, Devrim GÜNDÜZ wrote: On Wed, 2011-10-12 at 20:12 -0400, unclebob wrote: I'm under debian squeeze and pgsql 8.4. Do you know which version of mysqlmigrator I can try? Just download the tarball from EDB website, extract it, and run: ant compile ant dist java -jar dist/

Re: [GENERAL] pg 8.3 replication causing corruption

On Thu, Oct 13, 2011 at 4:07 PM, Bob Hatfield wrote: >> have you had any power events?  hard shutdowns, etc? I wonder if the problem >> is in the clog files, and not the heap itself. > > Nothing unusual for as long as I can tell.  Reminder that as long as I > don't restart the primary's pg proces

Re: [GENERAL] Are file system level differential/incremental backups possible?

> If you drop or truncate a table between the full and the incremental backup, > will that file be "resurrected"? > > Such resurrected files will not disturb PostgreSQL, but if you keep them > around, you might end up with a lot of dead files if you have to restore a > couple of times. That mak

Re: [GENERAL] pg 8.3 replication causing corruption

> have you had any power events?  hard shutdowns, etc? I wonder if the problem > is in the clog files, and not the heap itself. Nothing unusual for as long as I can tell. Reminder that as long as I don't restart the primary's pg process, everything works fine (secondary's data is intact). It's

[GENERAL] Test for cascade delete in plpgsql

My contacts table has a FK with cascade delete to foreign table companies using the company_id column. I have a DELETE AFTER trigger on my contacts table that checks to see if there are any contacts left with an email address or it won't allow you to delete the record for a company. However, if th

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

I pretty much didn't change anything in the config files except what was in the tutorial at http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial on the slave I created a recovery.conf file containing: standby_mode = 'on' primary_conninfo = 'host=' on the master postgresql.conf I set listen

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

Do not rsync the pg_xlog. Basically that error means that the restore_command in your recovery.conf is not working. You have hot_standby archiving going on the master and a recovery_command on the slave, right? On Thu, Oct 13, 2011 at 10:41 AM, Evan Walter wrote: > > > Hello, > I am somewhat n

Re: [GENERAL] Bulk processing & deletion

On 13 October 2011 20:08, Steve Crawford wrote: > On 10/13/2011 05:20 AM, Ivan Voras wrote: >> >> Hello, >> >> I have a table with a large number of records (millions), on which the >> following should be performed: >> >>        1. Retrieve a set of records by a SELECT query with a WHERE >> condit

Re: [GENERAL] Monitoring Replication

There is also http://bucardo.org/wiki/Check_postgres but I haven't been able to get it to work for monitoring replication. I am using a similar custom script as Mahlon, but written in perl. Looking at Mahlon's code has shown me an error in how I have been thinking about calculating the replicatio

Re: [GENERAL] How to make replica and use it when master is down ?

It is possible. See http://www.postgresql.org/docs/9.0/interactive/high-availability.html On Wed, Oct 12, 2011 at 3:31 AM, Condor wrote: > Hello everyone, > from a few days I want to ask how to make replica server of my database and > when spontaneously my master server going down due to loosi

Re: [GENERAL] Ideas for query

On 10/13/2011 08:31 AM, Raymond O'Donnell wrote: On 13/10/2011 12:17, Steve Clark wrote: Hello List, I am a not very experienced writing sql and I have a problem I can't readily solve, so I was hoping to get some help from this great list. Here is my problem I have a table that has event data

Re: [GENERAL] Bulk processing & deletion

On 10/13/2011 05:20 AM, Ivan Voras wrote: Hello, I have a table with a large number of records (millions), on which the following should be performed: 1. Retrieve a set of records by a SELECT query with a WHERE condition 2. Process these in the application 3. Delete them

Re: [GENERAL] plpgsql syntax error

Many Thanks!!! It works! Regards, Josef 2011/10/10 Ondrej Ivanič : > Hi, > > On 10 October 2011 21:35, József Kurucz wrote: >> ERROR:  syntax error at or near "$1" >> LINE 1: create table  $1  ( ) >>                      ^ >> QUERY:  create table  $1  ( ) >> CONTEXT:  SQL statement in PL/PgSQL

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

Yes, sorry. Type Evan On Thu, Oct 13, 2011 at 12:20 PM, Scott Marlowe wrote: > On Thu, Oct 13, 2011 at 9:41 AM, Evan Walter > wrote: > > > > > > Hello, > > I am somewhat new with postgresql trying to find a good method of > > replication for my company. > > I am running through the tutorials o

Re: [GENERAL] [ADMIN] Trying to use binary replication - from tutorial

On Thu, Oct 13, 2011 at 9:41 AM, Evan Walter wrote: > > > Hello, > I am somewhat new with postgresql trying to find a good method of > replication for my company. > I am running through the tutorials on binary replication for postgresql > 9.1.  Both servers are virtual box Ubuntu 10.10 on a laptop

Re: [GENERAL] Tablespace files deleted during continuous run

On Thu, Oct 13, 2011 at 6:55 AM, Vishnu S. wrote: > > > HI, > > I am using PostgreSQL 8.4 in windows.  I have  created a database and some > tables on it. Also created a table space and some tables in it. > > My application inserts data into these tables in every second. The > application is a con

Re: [GENERAL] could not reattach to shared memory

On Thu, Oct 13, 2011 at 9:35 AM, Sabin Coanda wrote: > Sorry, for the unfinished text, > > So, I run a long transaction with a lot of data, and after a while I got the > messages: > > NOTICE: max_fsm_relations(1000) equals the number of relations checked > HINT: You have at least 1000 relations. C

[GENERAL] Trying to use binary replication - from tutorial

Hello, I am somewhat new with postgresql trying to find a good method of replication for my company. I am running through the tutorials on binary replication for postgresql 9.1. Both servers are virtual box Ubuntu 10.10 on a laptop. I ran this rsync -av --exclude pg_xlog --exclude postgresql.conf

Re: [GENERAL] Dynamic sql real examples

On 13/10/11 17:55, Gabriel Filipiak wrote: Hi all, I have lately learned what is dynamic sql and one of the most interesting features of it to me is that we can use dynamic columns names and tables. But I cannot think about useful real life examples. The only one that came into my mind is sta

Re: [GENERAL] Dynamic sql real examples

On 13/10/11 18:44, Gabriel Filipiak wrote: 2011/10/13 Gavin Flower > On 13/10/11 18:35, Gabriel Filipiak wrote: Thx Gavin, any other suggestions from others? Gabe 2011/10/13 Gavin Flower mailto:gavinflo...@archidevsys.co.nz>>

[GENERAL] Tablespace files deleted during continuous run

HI, I am using PostgreSQL 8.4 in windows. I have created a database and some tables on it. Also created a table space and some tables in it. My application inserts data into these tables in every second. The application is a continuous running application. My issue is that after a 20-3

[GENERAL] could not reattach to shared memory

Hi there, I run a long transaction with a lot of data, and after a while I got the messages: -- 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] could not reattach to shared memory

Sorry, for the unfinished text, So, I run a long transaction with a lot of data, and after a while I got the messages: NOTICE: max_fsm_relations(1000) equals the number of relations checked HINT: You have at least 1000 relations. Consider increasing the configuration parameter "max_fsm_relation

[GENERAL] Video of Activity on PostgreSQL GIT repository

Hi all, I like to share this cool video which I build [1] (using gource [2]) to show the activity of PostgreSQL GIT repository in the few months ago. I hope you enjoy it. [1] http://www.youtube.com/watch?v=gzTBJW2EVJY [2] code.google.com/​p/​gource/ [3] github.com/​postgres/​postgres -- Fabríz

Re: [GENERAL] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist

Rebecca Clarke writes: > I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the > db schema on 8.3. When I pg_restore it to 8.4 I get the following: > pg_restore: creating FUNCTION gtsq_in(cstring) > pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 > FUNCT

Re: [GENERAL] Slow query: select * order by XXX desc offset 10 limit 10

In response to Alexander Farber : > Hello, > > I use PostgreSQL 8.4.7 on CentOS 6.0 / 64 bit machine > with Quad-Core AMD Opteron(tm) Processor 2352 and > 16 GB RAM and use it for 1 PHP script - which selects > and displays data in jQuery DataTables (i.e. an > HTML-table which can be viewed page

Re: [GENERAL] Slow query: select * order by XXX desc offset 10 limit 10

On Oct 13, 2011, at 9:41, Alexander Farber wrote: > > Does anybody please have an idea, > how to speed up my select statements? > Create one or more indexes. David J.

[GENERAL] Slow query: select * order by XXX desc offset 10 limit 10

Hello, I use PostgreSQL 8.4.7 on CentOS 6.0 / 64 bit machine with Quad-Core AMD Opteron(tm) Processor 2352 and 16 GB RAM and use it for 1 PHP script - which selects and displays data in jQuery DataTables (i.e. an HTML-table which can be viewed page by page). I select records from 1 view which uni

Re: [GENERAL] Bulk processing & deletion

On 10/13/2011 16:20, Ivan Voras wrote: Hello, I have a table with a large number of records (millions), on which the following should be performed: 1. Retrieve a set of records by a SELECT query with a WHERE condition 2. Process these in the application 3. Delete them fr

Re: [GENERAL] pg 8.3 replication causing corruption

On Wed, Oct 12, 2011 at 5:17 PM, Bob Hatfield wrote: >> Something about your setup is suspect. Disks perhaps. > > Disk: Fusion IOdrive (1.2TB NAND drive) > > I've read that one should set wal_sync_method=fsync_writethrough for > Windows servers.  It's currently set to open_datasync, I have no idea

Re: [GENERAL] Bulk processing & deletion

On 13/10/2011 14:34, Alban Hertroys wrote: >> Any other ideas? > > CREATE TABLE to_delete ( > job_created timestamp NOT NULL DEFAULT now(), > fk_id int NOT NULL > ); > > -- Mark for deletion > INSERT INTO to_delete (fk_id) SELECT id FROM table WHERE condition = true; > > -- Process in app >

Re: [GENERAL] Bulk processing & deletion

If you don't need the data for more then a transaction, or connection length - use temporary tables to store ids of data you need to delete. If those change, or move, or something - it means you are missing PK on that table. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Bulk processing & deletion

On 13 October 2011 14:20, Ivan Voras wrote: > Hello, > > I have a table with a large number of records (millions), on which the > following should be performed: > >        1. Retrieve a set of records by a SELECT query with a WHERE condition >        2. Process these in the application >        3.

Re: [GENERAL] Ideas for query

On 13/10/2011 12:17, Steve Clark wrote: > Hello List, > > I am a not very experienced writing sql and I have a problem I can't > readily solve, so > I was hoping to get some help from this great list. > > Here is my problem I have a table that has event data about the status > of units in the fie

[GENERAL] Bulk processing & deletion

Hello, I have a table with a large number of records (millions), on which the following should be performed: 1. Retrieve a set of records by a SELECT query with a WHERE condition 2. Process these in the application 3. Delete them from the table Now, in the default read-co

[GENERAL] Ideas for query

Hello List, I am a not very experienced writing sql and I have a problem I can't readily solve, so I was hoping to get some help from this great list. Here is my problem I have a table that has event data about the status of units in the field. It has many kinds of events one of which has down

[GENERAL] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist

Hi I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the db schema on 8.3. When I pg_restore it to 8.4 I get the following: pg_restore: creating FUNCTION gtsq_in(cstring) pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 FUNCTION gtsq_in(cstring) postgres p

[GENERAL] Transfer 8.3 to 8.4 - FUNCTION gtsq_in(cstring) does not exist

Hi I'm transferring a database from 8.3 to 8.4 and I've done a pg_dump of the db schema on 8.3. When I pg_restore it to 8.4 I get the following: pg_restore: creating FUNCTION gtsq_in(cstring) pg_restore: [archiver (db)] Error from TOC entry 550; 1255 4231868780 FUNCTION gtsq_in(cstring) postgres p

Re: [GENERAL] I need to load mysql dump to postgres...

On Wed, 2011-10-12 at 20:12 -0400, unclebob wrote: > > > I'm under debian squeeze and pgsql 8.4. Do you know which version of > mysqlmigrator I can try? Just download the tarball from EDB website, extract it, and run: ant compile ant dist java -jar dist/MigrationWizard.jar openjdk will just

Re: [GENERAL] Are file system level differential/incremental backups possible?

Bob Hatfield wrote: > Is it possible to do a full file system level backup of the data > directory, say once a week, and differentials or incrementals daily? > > I'm wondering if there are files that would normally be removed that a > restore: Full then diff/inc would not remove and perhaps > corr

Re: [GENERAL] how to list or array of key value pairs

Probably this will help you http://www.postgresql.org/docs/current/interactive/hstore.html ps. Look at the each() function. On 12 October 2011 22:45, J.V. wrote: > I need to rephrase this because of some confusion as to what I was looking > for. > > I want to create and initialize a list or arra