Re: [GENERAL] need a hand with my backup strategy please...

network this takes 5-10 mins or so, so not a big deal. I'll retain wals for 48 hours. Having to go to a PIT before the last hour or so would be a major undertaking anyway. Cheers, jamie === Please access the att

Re: [GENERAL] need a hand with my backup strategy please...

p to the last base backup. Which to me indicates I need to do a base backup every day...? Cheers, jamie === Please access the attached hyperlink for an important electronic communications disclaimer:

[GENERAL] need a hand with my backup strategy please...

ase backup. If it does do this, how is it possible to restore to a point in time? Thanks for any help. Cheers, jamie === Please access the attached hyperlink for an important electronic communications disclaimer: ht

[GENERAL] unique constraint

ce while they are getting shuffled around. to fix this I tried deferring the constraints, but as we're using version 8.2.5, realized this isn't supported and might not be the best approach? Any ideas from the community that might be usefull? Thanks, Jamie K.

Re: [GENERAL] Increase Query Speed

essions_campaign_idx (cost=0.00..19196.54 rows=1039330 width=0) (actual time=421.587..421.587 rows=1044475 loops=1) Index Cond: (campaign = 42) Total runtime: 2209.869 ms (10 rows) Thanks, Jamie K. 2010/7/27 Grzegorz Jaśkiewicz > show us explain analyze on this >

[GENERAL] Increase Query Speed

s there any way I can squeeze more speed out of this? Is there a better way to store the data for the type of info I'm trying to extract? I'm open to all suggestions Thanks, Jamie K.

[GENERAL] copy/duplicate database schemas

- schema_ar - schema_ru - etc... Thanks, Jamie K.

[GENERAL] How to debug efficiently

Hi All In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in the sql and run.. yay great debugging scenario. e.g. (please note I am rusty and syntax may be incorrect) declare @x as varchar(10) set @x = 'abc' select * from sometable

[GENERAL] Merge data from two databases

will collide I have three dumpes, how do i merge the data into one database please Many thanks Jamie

[GENERAL] app table names

ybe I'm reading to much into this. Any insights or words of wisdom would be greatly appreciated! Thanks, Jamie K.

[GENERAL] Data Generators

I'm looking for a data generator. Free would be nice, if possible. Has anyone had good luck w/ anything? maybe point me in a good direction :) Thanks, Jamie K.

[GENERAL] Calling a plpgsql function with composite type as parameter?

I'm working on an e-commerce site that calls various plpgsql functions from a Python app.  One of the things I need to do is create a shopping cart and add several items to it.  I'd like for both of these steps to be contained within the same transaction so if an error occurs adding an item to the

[GENERAL] split to table by space

s that can handle this in my version that I am unaware of? Or does anyone know how to write an easy function to handle this in in plpgsql or something? Thanks, Jamie K.

[GENERAL] best way to manage indexes

ch parameters (regexp & metaphone)? perhaps there is a solution that I haven't thought of. any input would be appreciated! Thanks, Jamie K.

[GENERAL] SSIS and Postgres

Hi there we are having some problems using OLEDB PGNP and SSIS, this is a post we have added to experts exchange, but we were wondering whether anyone here could shed some light on this. We are also interested how others manage ETL Cheers Jamie Data Warehousing Postgres We&#x

Re: [GENERAL] Postgres and multiple updates in one statement

thanks Jamie -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of nha Sent: 27 July 2009 14:35 To: Jamie Lawrence-Jenner Cc: PgSQL-general Subject: Re: [GENERAL] Postgres and multiple updates in one statement Hello, Le 27/07/09

[GENERAL] Postgres and multiple updates in one statement

statement? Many thanks, Jamie

Re: [GENERAL] large object does not exist after pg_migrator

through 3, in the post- database there are no results. Thanks for any advice, Jamie On Mon, Jul 13, 2009 at 2:13 PM, Jamie Fox wrote: > Hi - > After what seemed to be a normal successful pg_migrator migration from > 8.3.7 to 8.4.0, in either link or copy mode, vacuumlo fails on both o

[GENERAL] large object does not exist after pg_migrator

n what I might look for to try and track down this problem? pg_restore on our production database takes too long so it would be really nice to use pg_migrator instead. Thanks, Jamie

Re: [GENERAL] Making a RPM installer

Tried to use "ldd" but i am getting not a dynamic executable Tom Lane wrote: Jamie Deppeler <[EMAIL PROTECTED]> writes: I am building a rpm for RHEL 5 The rpm contains a compiled version for postgresql-8.3.3 and tomcat 6 and java 1.6. When i try to in

Re: [GENERAL] Making a RPM installer

There is a few reason why I am making this installer 1. Need to make a installer that just uses one rpm file 2. It needs to come with a prebuild database 3. Custom changes have been made to tomcat configuration Joshua D. Drake wrote: On Thu, 2008-07-10 at 10:28 +1000, Jamie Deppeler wrote

[GENERAL] Making a RPM installer

Hi, Not sure if this the correct place to post this issue but hopefully someone can help. I am building a rpm for RHEL 5 The rpm contains a compiled version for postgresql-8.3.3 and tomcat 6 and java 1.6. When i try to install the rpm on blank test system i get the following depend

[GENERAL] Need some help

trying to install Postgresql 8.3 and i keep getting these errors libodbc.so is needed libodbcinst.so is needed Hopefully someone can help me -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gene

[GENERAL] Postgresql 8.3 Installer issue

Hi, I am trying to build a new installer application. I am in the process of upgrading postgresql 8.1 to 8.3.3 but i am having a issue which i can't seemed to resolve. Error output from rpmbuilder + su -c - user'$RPM_BUILD_ROOT/usr/local/app/pgsql/bin/postmaster -D $RPM_BUILD_ROOT/usr/local/

Re: [GENERAL] Time Zone Error

Richard Huxton wrote: Jamie Deppeler wrote: Hi, We currently running Windows 2003 issues with time zone set to (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have with Postgresql 8.1.5 is that time zone is being reported with a incorrect off set –4 not –5 and thi

[GENERAL] Time Zone Error

Hi, We currently running Windows 2003 issues with time zone set to (GMT-05:00) Eastern Time (US & Canada). Problem that we currently have with Postgresql 8.1.5 is that time zone is being reported with a incorrect off set –4 not –5 and this is causing incorrect time to be set. Below is exampl

Re: [GENERAL] PostgreSql replication and load balancing ( is Slony-I a solution?)

Alexander Staubo wrote: On 5/1/07, Jan Bilek <[EMAIL PROTECTED]> wrote: Is Slony-I capable of load balancing and how to set it up? We searched the web and some people mentioned that Slony-I could do load balancing, but haven't found how to make Slony-I to do it. You might want to check pgclus

[GENERAL] OS X Kernel settings

Is this a safe setting for g5 1gig ram Os 10.4.9 sysctl -w kern.sysv.shmmax=167772160 sysctl -w kern.sysv.shmmin=1 sysctl -w kern.sysv.shmmni=32 sysctl -w kern.sysv.shmseg=8 sysctl -w kern.sysv.shmall=65536 ---(end of broadcast)--- TIP 9: In versio

[GENERAL] OS X Kernel settings

postgresql control script. Jamie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[GENERAL] plpgsql and insert

Hi, Have a quick question is possible to record a primary from a insert stament eg xprimary := insert into schema.table(.,.,.,.) VALUES (); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] When to use compound PK instead of a FK?

I typically use compound primary keys when creating a table that represents a many-to-many relationship. I was wondering if anyone else had other situations in which you would use a compound PK? Why do you use it instead of just a foreign key? ---(end of broadcast)

[GENERAL] Deleting Problem

Here is my problem I have a level structure which is 5 levels deep with 6 tables, for this example i will call it table1,table2,table3,table4,table5,table6 (1)table1 (2)table2 (3)table3 (4)table4 (5)table5,table6 (6)table7,table8 table5 and table6 have fk keys pointing to table1,table2,table3

[GENERAL] Function Help

Hi, I am having a problem with a delete function. The delete function uses 3 tables (tablea,tableb,tablec) tablec stores actual values and tablea+b stores summaries when a entry in tablec is deleted i updated the values in tablea+b, The problem when i do a cascade delete on tablea tablec dele

Re: [GENERAL] Delete Problem

PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6) Michael Fuhr wrote: On Thu, Jul 13, 2006 at 04:58:26PM +1000, Jamie Deppeler wrote: db=# \d job Table "job"

Re: [GENERAL] Delete Problem

happens when the disk is nearing to full cant this be avoided, why does postgres hang. thanks, regards Surabhi *From:* [EMAIL PROTECTED] on behalf of Michael Fuhr *Sent:* Thu 7/13/2006 11:48 AM *To:* Jamie Deppeler *Cc

Re: [GENERAL] Delete Problem

; PRIMARY KEY, btree ("primary") Michael Fuhr wrote: On Thu, Jul 13, 2006 at 04:28:50PM +1000, Jamie Deppeler wrote: From psql db=# select "primary" from job where "primary" = 370; primary - 370 (1 row) db=# delete from job where "primary&q

Re: [GENERAL] Delete Problem

Michael Fuhr wrote: [Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Thu, Jul 13, 2006 at 03:32:43PM +1000, Jamie Deppeler wrote: Michael Fuhr wrote: On Thu, Jul 13, 2006 at 01:45:36PM +1000, Jamie Deppeler wrote: Just

[GENERAL] Delete Problem

Just today i have noticed i have one certain table that i cannot delete any records from 1 have delete all Triggers, Vacuumed the tables removed all foreign keys that linked to this table. -- *Jamie Deppeler *Database Administrator *once:technologies pty ltd * *Do It Once!* 46 Roseneath

[GENERAL] Updating within Triggers

Hi trying to wtite a trigger to update summary fields in a seperate table to do this i am planning on using trigger. problem i have at the moment the update trigger doesnt seem to be fireing but the insert works. Trigger CREATE TRIGGER "setSummary" AFTER INSERT OR UPDATE ON "parts" FOR EACH RO

[GENERAL] Autogenerated backup of a password protected database

Hi, Since pg_dump doesnt support password is there a way that password can be supplied -w option. Currently writting a application to do backups in java as i need a solution that is cross platform. Any help would be greatfully recieved. ---(end of broadcast)-

[GENERAL] update problem in triggers

I am having with this simple trigger i wrote, worked when i created it but now i get this error "Stack depth Limit Exceeded" If someone could tell me what i am doing wrong i would be greatfull Trigger CREATE TRIGGER "updateKeys" AFTER UPDATE ON "projects"."resource" FOR EACH ROW EXECUTE PROC

Re: [GENERAL] stack depth limit exceeded

ON "contacts"."addContactField" () RETURNS trigger AS $body$ begin update contacts.person set "contact" = new.firstname where person."primary" = new."primary"; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT

[GENERAL] stack depth limit exceeded

At the moment i am trying to execute a very simple function but i am getting the following error stack depth limit exceeded function CREATE OR REPLACE FUNCTION "contacts"."addContactField" () RETURNS trigger AS $body$ begin update contacts.person set "contact" = new.firstname where person.

Re: [GENERAL] Problems compiling Postgresql 8.0.3 on 10.4

Vivek Khera wrote: On Jul 20, 2005, at 3:37 AM, Jamie Deppeler wrote: Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - Wendif-labels -fno-strict-aliasing -dynamiclib

Re: [GENERAL] [HACKERS] No user being created during initdb for OS X

hville, TN 37203-6320 615-260-0005 On Jul 20, 2005, at 8:35 PM, Jamie Deppeler wrote: Hi, Having an issue with 10.4.2 at the moment when i initialize the database no user is being created. I have setup Postgresql 8.0.3 on 10.3 without any issue, has anyone come accross this prob

[GENERAL] No user being created in os X

Hi, Having an issue with 10.4.2 at the moment when i initialize the database no user is being created. I have setup Postgresql 8.0.3 on 10.3 without any issue, has anyone come accross this problem before? ---(end of broadcast)--- TIP 1: if po

[GENERAL] Problems compiling Postgresql 8.0.3 on 10.4

Hi, i have just installed 10.4 on one of our machines and cannot get past this error during make gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -dynamiclib -install_name /usr/local/pgsql/lib/libpq.4.dylib -compatibility_version 4 -curr

[GENERAL] off topic

Sorry this question is off topic. Does anyone know a good resource for tomcat tuning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] Looking for a good ERD Tool

There are probability* *a few around, but i think most people just use a flowcharting software to created designs. I know for the work that i do with postgresql i use smartdraw as it can do anything i want then i apply these designs to postgresql using EMS Manager. Rob Brenart wrote: I'm loo

[GENERAL] SET AUTOCOMMIT TO OFF is no longer supported

Hi, I am application that we have built using postgresql 8.0.3 and tomcat 5.0.28. In the java code i have put a state set autocomit to false which should be fine but comes up with the error SET AUTOCOMMIT TO OFF is no longer supported. I have tried using 2 drivers 7.4 and 8 jdbc driver st

[GENERAL] Executing SQL Script

This is a simple question what is the best way to execute a SQL script, this script will create schemas and tables for a database i created. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] postgresql books

I would recomend Postgresql by Korry and Susan Douglas, I think most people use this as there resource :) I think a new version for postgres 8 is comming out soon ---(end of broadcast)--- TIP 8: explain analyze is your friend

[GENERAL] Issue with OS X

Hi, I am trying to install postgresql 8 on a G5 OS X machine and cannot get past this error i have tried changing the shared buffer with no luck. Error message: /usr/local/pgsql/data postgres$ FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was sh

[GENERAL] International Characters

Hi, We are having an issue importing international characters into postgresql 8. example Renée The error message we get is ERROR: invalid byte sequence for encoding "UNICODE": 0xe92044 Any help would be greatfully recieved Jamie ---(end of

[GENERAL] converting to hex

Hi, I am trying to write a function that will allow a postgres to convert a text string into a hex value but there doesnt seem a function to do it, only one i could find is to_hex(number) example of what i have done is update table1 Set field2 = encode((select md5('field1')),'hex') where n

[GENERAL] getGeneratedKeys()

Hi to all, I have one problem with PostgreSQL and Java. I have a table with Primary key(serial) field, but after I insert a record i am unable to retrieve this value. I have tried getGeneratedKeys() and i get nothing returned. There is another method to retrieve this field? thanks -

[GENERAL] PIT Recovery

Does any one know if this can be applied to windows server as through the beta this only worked on linux based machines. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [

[GENERAL] Catching delete

Hi have a pretty simple question i know its possible to catch a delete statement through a trigger is it possible to prevent the delete from happing through the same trigger function? ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] update in triggers

What i am trying to do is to update a field based on a sql query set notes='hello' is just being used as a test but i can not seem to make this simple update work Michael Fuhr wrote: On Wed, Jan 19, 2005 at 03:45:53PM +1100, Jamie Deppeler wrote: Think im doing somet

[GENERAL] update in triggers

Hi, Think im doing something wrong here, cant seem to resolve the problem i have a trigger which is calling a update function and when it gets to a update it goes into a infinite loop code Trigger CREATE TRIGGER "new_trigger" AFTER INSERT OR UPDATE ON "chargeratetest" FOR EACH ROW EXECUTE PROCED

Re: [GENERAL] PostgreSQL 8 on windows very slow

Daniel Schuchardt wrote: lol wrote: Hi, I'm currently testing several databases for an application written in Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000 SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT request. Have-you seen this problem ? May be some

[GENERAL] replicator

Can anyone suggest good open source replicator system for pgsql Thanks JD ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[GENERAL] problems with date

Hi, having a small problem in a trigger i want to automatically update a date field but when i use for example update table set field = current_date where table.pk = new.pk or update table set field = now() where table.pk = new.pk this crashes think link between server and db, i have tried using

[GENERAL] sorting problem

Problem i am having at the moment i cant get a true alpha sort to work as Order By is sorting A..Z then a..z where i need aA..zZ sort independant of case. SQL Query SELECT * FROM person WHERE (salutation LIKE '%To%') ORDER BY person.lastname Results Ahsteit Bloggs Cap Carrey Diver Duckula Golds

Re: [GENERAL] MD5

Tino Wildenhain wrote: Hi, Am Freitag, den 17.12.2004, 09:41 +1100 schrieb Jamie Deppeler: Hi, I was just wondering is it possible to encrypt a filed in the database with md5? i know it is possible to do it with DB users No. You cannot encrypt with md5 because you

[GENERAL] MD5

Hi, I was just wondering is it possible to encrypt a filed in the database with md5? i know it is possible to do it with DB users ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister You

Re: [GENERAL] Detecting Temporary Tables

What i want to be able to do is create a tempory table and when i need a value stored in the table i want to check that the table has been created if it has not do a create temp table or just do an update. Michael Fuhr wrote: On Tue, Dec 07, 2004 at 09:22:51AM +1100, Jamie Deppeler wrote

[GENERAL] More problems

Hi, Newbie question I have a result set returned through a function or rule, i know i will have process the result set through a for loop, my question is how can i get length of result set? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postma

[GENERAL] Detecting Temporary Tables

Is it possible to detect the instance of a Temporary through a function? ---(end of broadcast)--- TIP 8: explain analyze is your friend

[GENERAL] Rules

Hi, What i am trying to do is i have a join table eg primarykey field1 field2 and based on SQL Select have the values of field1 inserted into a new table which will scroll which will launch a trigger but im having problems getting this to work? Any ideas anyone? sql select may return 1 or more r

[GENERAL] Function Problem

Problem I am trying to store value in a TEMPORARY table and I am getting the following error ERROR: relation with OID 51533 does not exist Trigger CREATE TRIGGER "createtemporytable" AFTER INSERT ON "component" FOR EACH ROW EXECUTE PROCEDURE "createtemp"(); Function begin CREATE temporary TABLE

[GENERAL] Rules

HI, Planning on witting a rule for a view, and i was wondering if anyone could suggest a good Internet resource? thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database

[GENERAL] Trigger problem 2

t;old" is not yet assigned would be greatful for any help thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database Admin tel;work:+61 3 52278 6699 url:http://w

[GENERAL] Trigger Problems

Hi , i have designed a trigger function called test2(Integer) im trying to use it with a trigger but get errors that function cannot be found Trigger statement is CREATE TRIGGER "new_trigger566" BEFORE INSERT ON "customer" FOR EACH ROW EXECUTE PROCEDURE test2("primary"); get the error test2() can

[GENERAL] Triggers

Hi, Just doing some testing at the moment and trying to make a trigger that consists of a insert statement which as a result of an sql querty! Is this possible? if so could someone give me some pointers as i am having no luck trying to get this to work, have even tried this but still doesnt wor

[GENERAL] rules

This may be a bit of stupid question but it is the first time i have played around with rules and i am trying to convert a sql statement into a rule sample sql querty INSERT INTO schema.table2 ( "field1", "field2", "field3", "field4", "field5", "field6", "field7",

[GENERAL] off Topic: Looking for a good sql query

Hi, This post is a bit off topic im looking a good sql book can someone give me a recommendation? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] tableoid

Hi have a bit of a issue im planning on using tableoid to select the appropate table, but im not sure that you can in sql select statement? If not is there another approch i could be using? -- *Jamie Deppeler *Database Administrator ---(end of broadcast

[GENERAL] OID

l change making referencing impossible. Any ideas would helpful -- Jamie Deppeler Database Administrator Cormoran Communication Do It Once!

[GENERAL] OID's

impossible.   -- Jamie Deppeler

[GENERAL] Can you help a newbie?

! Jamie Bumsted Systems Engineer Evertek, Inc.