Re: [GENERAL] Move PGdata to a different drive

David Fetter wrote: On Thu, Jun 04, 2009 at 11:11:29AM -0400, Bruce Momjian wrote: Jennifer Trey wrote: Hi, What file should I be working with? Just shut down the server and move the directory whever you want and restart the server. There are no file contents that need changing. Of course sho

Re: [GENERAL] Why lots of temp schemas are being created

On 08/06/09 13:57, Anirban Pal wrote: Dear all, Our software use postgres as backend database. It works fine, strange thing is that, it creates so many temporary schemas under schema tab, names like pg_toast, pg_toast_temp_1 like this upto pg_toast_temp40. This schemas also reflected in "pg_na

Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?

Tom Lane wrote: > Ron Mayer writes: >> Looks like the original questions from the thread >> got resolved, but I found this behaviour surprising: > >> regression=# select interval '1' day to second; >> interval >> -- >> @ 1 hour >> (1 row) > >> Should this be 1 second? > > That is a bi

[GENERAL] Streaming SFPUG: PostgreSQL & Rails June 9 (tommorrow)

All, We will be doing a streaming broadcast of our next SFPUG meeting, "PostgreSQL: Your Secret Weapon for Rails", tommorrow Tuesday June 9th at approximately 7:40pm PDT. You are encouraged to log in and view the presentation. We'll even take questions over IRC and webchat! Log in here: ht

Re: [GENERAL] max execution time of query

S Arvind wrote: Thanks Chris & kretschmer. But one small doubt in it,, What happens to update or insert query? They will be rolled back. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] Sum of multiplied deltas

Gerhard Wiesinger wrote: I've the following data: datetime | val1 | val2 time1| 4 | 40% time2| 7 | 30% time3| 12 | 20% ... I'd like to sum up the following: (7-4)*30% + (12-7)*20% + ... datetime is ordered (and unique and has also an id). 1.) Self join with

Re: [GENERAL] Adding the host name to the PgSQL shell

Scott Mead wrote: On Mon, Jun 8, 2009 at 12:44 PM, Madison Kelly > wrote: Hi all, I work on a development and production server, and I am always double-checking myself to make sure I am doing something on the right server. Is there a way, lik

[GENERAL] Re: How to get the size of non fixed-length field from system catalog ?

Postgres User wrote on 08.06.2009 23:03: Hi, I'm writing a small ORM tool and have written a number of queries to retrieve table metadata. One piece of data that I'm having trouble hunting down is the size of a CHAR field. For example, one table has a 'user_id' column of type CHAR(36). But wh

[GENERAL] How to get the size of non fixed-length field from system catalog ?

Hi, I'm writing a small ORM tool and have written a number of queries to retrieve table metadata. One piece of data that I'm having trouble hunting down is the size of a CHAR field. For example, one table has a 'user_id' column of type CHAR(36). But when I look at the pg_attribute and pg_type t

Re: [GENERAL] NOT NULL with CREATE TYPE

On Mon, Jun 8, 2009 at 2:18 PM, Jeff Davis wrote: > On Sat, 2009-06-06 at 15:03 -0400, Merlin Moncure wrote: >> sql functions are pretty inflexible...even with recent even with >> recent advancements like varargs and default parameters they are >> designed to do a very particular thing...and insert

Re: [GENERAL] Any way to bring up a PG instance with corrupted data in it?

I had to calculate out the next transaction ID and -f (force) the change, but once I did this the DB came back up. So, thanks for the info, and now I know how this works. The plan now is to dump the databases and reload them to ensure overall database integrity. Thanks for the reply, Keaton

Re: [GENERAL] Sum of multiplied deltas

On 8 Jun 2009, at 19:01, David Fetter wrote: Hello! I've the following data: datetime | val1 | val2 time1|4 | 40% time2|7 | 30% time3| 12 | 20% ... I'd like to sum up the following: (7-4)*30% + (12-7)*20% + ... This is best done in 8.4 using Windowing. Sadly, it's an

Re: [GENERAL] [pgsql-general] Daily digest v1.9081 (14 messages)

On 8 Jun 2009, at 17:23, Merlin Moncure wrote: Is there a way when creating a table to limit it to one row? That is, without using a stored procedure? I searched the documentation, but didn't find anything. CREATE TABLE x (...); CREATE UNIQUE INDEX x_only_one_row ON ((1)); very cleve

Re: [GENERAL] Duplicate key issue in a transaction block

I am actually using Sequoia and the big problem is not the primary key error. I could leave with that! Sometimes the problem is generated not by insert but by the delete because the two transactions don't happen on the exactly same order and time on both backends and then the delete returns di

Re: [GENERAL] Sum of multiplied deltas

Gerhard Wiesinger writes: > Any hints for an 8.3 environment (currently)? Use a FOR loop in plpgsql and remember the previous row's value in a variable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subsc

Re: [GENERAL] Duplicate key issue in a transaction block

On Mon, Jun 8, 2009 at 12:25 PM, Ioana Danes wrote: > > Well, I guess I have my answer... > > I tried to narrow down an issue I get on one of the production sites, where > using a similar transaction I get the same error. > > In my production environment the group id is actually a unique number fo

Re: [GENERAL] Duplicate key issue in a transaction block

Well, I guess I have my answer... I tried to narrow down an issue I get on one of the production sites, where using a similar transaction I get the same error. In my production environment the group id is actually a unique number for the terminal (terminalid) and the same transaction CANNOT be

Re: [GENERAL] Using ANY()

-- From: "Jeff Davis" On Sun, 2009-06-07 at 19:33 +0100, Shakil Shaikh wrote: A less trivial usage of the above would be to pass an array to a simple function using it to return a range of arbitrary rows. I don't know exactly what you mean by

Re: [GENERAL] NOT NULL with CREATE TYPE

On Sat, 2009-06-06 at 15:03 -0400, Merlin Moncure wrote: > sql functions are pretty inflexible...even with recent even with > recent advancements like varargs and default parameters they are > designed to do a very particular thing...and insert/update tend to be > fairly generic in how they operate

Re: [GENERAL] Sum of multiplied deltas

Any hints for an 8.3 environment (currently)? Thnx. Ciao, Gerhard -- http://www.wiesinger.com/ On Mon, 8 Jun 2009, David Fetter wrote: On Mon, Jun 08, 2009 at 08:35:20AM +0200, Gerhard Wiesinger wrote: Hello! I've the following data: datetime | val1 | val2 time1|4 | 40% time2

[GENERAL] Upgrade from Postgres 8.2.4 to 8.3.5

I have successfully installed 8.3.5 as a fresh install on a Redhat 4.5 machine. Now I try to upgrade another Redhat 4.5 machine with an existing 8.2.4 install and the "make check" fails all tests. When I look at the results file for each type being tested I see the same error indicating that ther

Re: [GENERAL] Using ANY()

On Sun, 2009-06-07 at 19:33 +0100, Shakil Shaikh wrote: > Hi all, > > Is it appropriate to use ANY() in a select statement as so? > > SELECT * FROM table t WHERE t.id = ANY(ARRAY[1,2,3]); Yes, that's appropriate. A simpler formulation (for that simple case) is: ... WHERE t.id IN (1,2,3) >

Re: [GENERAL] Any way to bring up a PG instance with corrupted data in it?

Hi, Keaton Adams írta: > This is a QA system and unfortunately there is no recent backup So > as a last resort I am looking for any way to bring up Postgres when it > has corrupt data in it: > > FATAL: could not remove old lock file "postmaster.pid": Read-only file > system > HINT: The file se

Re: [GENERAL] Postgres's Performance degrades after heavy db operation

Vick Khera writes: > On Mon, Jun 8, 2009 at 9:38 AM, Preeti > Khurana wrote: >> Any ideas/pointers on what could be making the DB slow after all these heavy >> operations being performed? > It seems autovacuum is not running often enough. 8.1's autovacuum isn't very aggressive by default. You m

Re: [GENERAL] Duplicate key issue in a transaction block

In response to Vyacheslav Kalinin : > On Mon, Jun 8, 2009 at 8:33 PM, Bill Moran wrote: > > > > > Perhaps you want to take an exclusive lock on the table? The operation > > you describe seems to suggest that you'd want to guarantee exclusive > > write access to the table. > > Exclusive table l

Re: [GENERAL] Any way to bring up a PG instance with corrupted data in it?

Keaton Adams writes: > This is a QA system and unfortunately there is no recent backup So as a > last resort I am looking for any way to bring up Postgres when it has corrupt > data in it: pg_resetxlog? regards, tom lane -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] Adding the host name to the PgSQL shell

Scott Mead wrote: On Mon, Jun 8, 2009 at 1:30 PM, Madison Kelly > wrote: That works like a charm, thank you! No problem :) Next question though; How can I get it to save my custom prompt across sessions/server restarts? It there something eq

Re: [GENERAL] Adding the host name to the PgSQL shell

On Mon, Jun 8, 2009 at 1:30 PM, Madison Kelly wrote: > > > That works like a charm, thank you! No problem :) > > > Next question though; > > How can I get it to save my custom prompt across sessions/server restarts? > It there something equivalent to '.bash_profile'? > ~/.psqlrc --Scott

Re: [GENERAL] Adding the host name to the PgSQL shell

Scott Mead wrote: On Mon, Jun 8, 2009 at 12:44 PM, Madison Kelly > wrote: Hi all, I work on a development and production server, and I am always double-checking myself to make sure I am doing something on the right server. Is there a way, lik

Re: [GENERAL] Adding the host name to the PgSQL shell

On Mon, Jun 8, 2009 at 12:44 PM, Madison Kelly wrote: > Hi all, > > I work on a development and production server, and I am always > double-checking myself to make sure I am doing something on the right > server. > > Is there a way, like in terminal shells, to change the PgSQL shell's > prompt

Re: [GENERAL] Duplicate key issue in a transaction block

--- On Mon, 6/8/09, Bill Moran wrote: > From: Bill Moran > Subject: Re: [GENERAL] Duplicate key issue in a transaction block > To: "Ioana Danes" > Cc: "PostgreSQL General" > Received: Monday, June 8, 2009, 12:33 PM > In response to Ioana Danes : > > > > Hi Everyone, > > > > I have a hard to

Re: [GENERAL] [SQL] 'cost' and 'rows' for volitile function

Old fool that I am - forgot to cc: the list. Richard Huxton wrote: Rob Sargent wrote: pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly VOLATILE functions. Is one hundred the new ninety-nine? and therefore these values are ignored by the planner? No, they're the default expec

[GENERAL] Adding the host name to the PgSQL shell

Hi all, I work on a development and production server, and I am always double-checking myself to make sure I am doing something on the right server. Is there a way, like in terminal shells, to change the PgSQL shell's prompt from 'db=>' to something like 'h...@db=>'? I'm on PgSQL 8.1 (s

Re: [GENERAL] Duplicate key issue in a transaction block

On Mon, Jun 8, 2009 at 8:33 PM, Bill Moran wrote: > > Perhaps you want to take an exclusive lock on the table? The operation > you describe seems to suggest that you'd want to guarantee exclusive > write access to the table. > Exclusive table lock is a bit excessive IMO. Locking particular gro

Re: [GENERAL] Duplicate key issue in a transaction block

In response to Ioana Danes : > > Hi Everyone, > > I have a hard to reproduce scenario for a production siteI tried to > simplify the code and at the end I could get a similar problem with the > following table and java code. > The problem is that I have a transaction that deletes all the re

Re: [GENERAL] Postgres's Performance degrades after heavy db operation

On Mon, Jun 8, 2009 at 9:38 AM, Preeti Khurana wrote: > Any ideas/pointers on what could be making the DB slow after all these heavy > operations being performed? It seems autovacuum is not running often enough. You may wish to include vacuum in your data update/delete programs so that it runs af

Re: [GENERAL] Postgres's Performance degrades after heavy db operation

oh, and take the time to upgrade to newest 8.1.X as the matter of urgency!. -- 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] Sum of multiplied deltas

On Mon, Jun 08, 2009 at 08:35:20AM +0200, Gerhard Wiesinger wrote: > Hello! > > I've the following data: > datetime | val1 | val2 > time1|4 | 40% > time2|7 | 30% > time3| 12 | 20% > ... > > I'd like to sum up the following: > > (7-4)*30% + (12-7)*20% + ... This is best done i

Re: [GENERAL] Postgres's Performance degrades after heavy db operation

On Mon, Jun 8, 2009 at 2:38 PM, Preeti Khurana wrote: > Hi, > >    I am using Postgresql 8.1.2 and performing quite a huge number of db > operations ( insert/update/ delete) in around 4 tables. Around 3 million of > db is inserted and then 600 K rows are updated for around 5-6 times during a > day.

Re: [GENERAL] Entering time into postgresql

On Mon, Jun 8, 2009 at 11:04 AM, BlackMage wrote: > > Is there a field that can actually store time in PostgreSql. And what I mean > by time is not like time of the day, but a specific amount of time. This is known as type "INTERVAL" to postgres. -- Sent via pgsql-general mailing list (pgsql-gen

Re: [GENERAL] Entering time into postgresql

On Mon, Jun 8, 2009 at 11:04 AM, BlackMage wrote: > Is there a field that can actually store time in PostgreSql. And what I mean > by time is not like time of the day, but a specific amount of time. The manual is your friend: http://www.postgresql.org/docs/8.3/static/datatype-datetime.html The

Re: [GENERAL] trigger functions with arguments

On Jun 8, 5:18 am, laurenz.a...@wien.gv.at ("Albe Laurenz") wrote: > Kev wrote: > > I came across this curious behaviour today, at least in the 2009-03-24 > > beta (I can't run a newer beta), that I couldn't find in the docs, > > although maybe I missed it. > > [...] > > > So while that is the way

Re: [GENERAL] Why lots of temp schemas are being created

Thank you for your response. Dear all, Our software use postgres as backend database. It works fine, strange thing is that, it creates so many temporary schemas under schema tab, names like pg_toast, pg_toast_temp_1 like this upto pg_toast_temp40. This schemas also reflected in "pg_names

[GENERAL] Postgres's Performance degrades after heavy db operation

Hi, I am using Postgresql 8.1.2 and performing quite a huge number of db operations ( insert/update/ delete) in around 4 tables. Around 3 million of db is inserted and then 600 K rows are updated for around 5-6 times during a day. Its observed that running this process for few days ( 5-6 days)

[GENERAL] Entering time into postgresql

Is there a field that can actually store time in PostgreSql. And what I mean by time is not like time of the day, but a specific amount of time. The application I am developing records how long it takes for a user to do a task, say 5 minutes and 32 seconds. That time is entered into the database

Re: [GENERAL] How to store text files in the postgresql?

DimitryASuplatov wrote: My task is to store a lot (10^5) of small ( <10 MB) text files in the database with the ability to restore them back to the hard drive on demand. That means that I need two functions. First - grab file from the directory, store it in the database and delete from the disk;

[GENERAL] Any way to bring up a PG instance with corrupted data in it?

This is a QA system and unfortunately there is no recent backup So as a last resort I am looking for any way to bring up Postgres when it has corrupt data in it: FATAL: could not remove old lock file "postmaster.pid": Read-only file system HINT: The file seems accidentally left over, but i

[GENERAL] Duplicate key issue in a transaction block

Hi Everyone, I have a hard to reproduce scenario for a production siteI tried to simplify the code and at the end I could get a similar problem with the following table and java code. The problem is that I have a transaction that deletes all the records in a group and inserts the new recor

Re: [GENERAL] Foreign Key Unique Constraint can be dropped

Vlado Moravec writes: > from postgres docs: "foreign key must reference columns that either are a > primary key or form a unique constraint". It does not allow creating foreign > key constraint on non-unique column - an error is raised. > After playing with a new database I discovered that such a

Re: [GENERAL] limit table to one row

On Sun, Jun 7, 2009 at 1:38 AM, Craig Ringer wrote: > Brandon Metcalf wrote: >> Is there a way when creating a table to limit it to one row?  That is, >> without using a stored procedure? >> >> I searched the documentation, but didn't find anything. >> > > > CREATE TABLE x (...); > > CREATE UNIQUE

Re: [GENERAL] Using results from DELETE ... RETURNING

On Sun, Jun 7, 2009 at 12:29 AM, Tom Lane wrote: > David Fetter writes: >> Would it be super-complicated to do this with CTEs for 8.5?  They seem to >> have sane properties like getting executed exactly once. > > Hmm, interesting thought.  The knock against doing RETURNING as an > ordinary subquer

Re: [GENERAL] PL/PGSQL

mg>hopefully quick response Hello everybody, I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer. I have two questions:

Re: [GENERAL] invalid byte sequence for encoding "UTF8": 0xab

"Grand, Mark D." writes: > It turns out that my problem was that the editor I was using (emacs) > does not properly support utf8 encoding. Emacs does support utf8 properly. http://www.emacswiki.org/emacs/ChangingEncodings It could be I'm biased because I use emacs from CVS, which is going to

Re: [GENERAL] Foreign Key Unique Constraint can be dropped

On Mon, Jun 8, 2009 at 11:48 AM, Vlado Moravec wrote: > Hi, > from postgres docs: "foreign key must reference columns that either are a > primary key or form a unique constraint". It does not allow creating foreign > key constraint on non-unique column - an error is raised. > After playing with a n

Re: [GENERAL] PL/PGSQL

Hi, Welcome aboard :) Jordi Martínez writes: > 1.- Exception throwed by cast('mmdd' as timestamp) > > I do a cast conversion from a string to a timestamp. When I put a bad > date on the string, the cast throw an error on my PL/PGSQL but I can't > capture (I'm new). Which type of exception

Re: [GENERAL] Upgrading Database: need to dump and restore?

On 2009-06-03, Carlos Oliva wrote: > Woudl it be possible to keep the current postgresql version running in a > different port, install a new version of postgresql, and copy the data from > one version to the other while both versions are running? This might give > us time to copy the tables a

Re: [GENERAL] invalid byte sequence for encoding "UTF8": 0xab

It turns out that my problem was that the editor I was using (emacs) does not properly support utf8 encoding. -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, June 08, 2009 5:59 AM To: Grand, Mark D.; pgsql-general@postgresql.org Subject: RE: [GENERAL]

Re: [GENERAL] Schema, database, or tables in different folders?

On 2009-06-02, Carlos Oliva wrote: > This is a multi-part message in MIME format. > > --_=_NextPart_001_01C9E386.D2E5B79B > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Is there a way to create a database or a table of a database in its

[GENERAL] Foreign Key Unique Constraint can be dropped

Hi, from postgres docs: "foreign key must reference columns that either are a primary key or form a unique constraint". It does not allow creating foreign key constraint on non-unique column - an error is raised. After playing with a new database I discovered that such a unique constraint can be dr

Re: [GENERAL] Function: Change data while walking through records

stevesub wrote: > I keep having this need to create a function that will change the row data > as I am walking through the data. For example, I process each row in order, > if column1 change from previous row, set column2 to true. > > Is this possible? I can run another query to modify the data,

Re: [GENERAL] invalid byte sequence for encoding "UTF8": 0xab

Mark D. Grand wrote: > I am having a vexing problem with a script I am writing to > populate reference tables in a new database. > > I am running postgreSQL 8.3 with psql 8.3.7. > > Psql reads this SQL statement: > > INSERT INTO META_AUTH.DOMAIN_META_ASSERTION (TITLE, DESCRIPTION, > META_

[GENERAL] PL/PGSQL

Hello everybody, I'm Jordi from Spain, I'm new on this mailing list. I hope I could help anybody in the future but now I new some help because I read a lot of documentation but I can't find an answer. I have two questions:

Re: [GENERAL] PostgreSQL and XA Distributed Transaction Protocol

Christian Ferrari wrote: > I'm developing a new, free, XA compliant transaction manager. > One of the first resource manager I would be glad to support > is PostgreSQL; after some googling I have found no > information related to PostgreSQL and XA protocol support. > Can anyone give me more infor

Re: [GENERAL] Server admin for Clients ?

Dave Gauthier wrote: > What does Postgres have for managing/controlling user access > to the DB in terms of resource allocation? I remember in > Oracle, you could specify how many server slots were > available for users to share and how much resources they were > allotted. A super-user that n

Re: [GENERAL] trigger functions with arguments

Kev wrote: > I came across this curious behaviour today, at least in the 2009-03-24 > beta (I can't run a newer beta), that I couldn't find in the docs, > although maybe I missed it. [...] > So while that is the way to create the trigger itself, the > trigger procedure must be created like this:

Re: [GENERAL] why dropping a trigger may cause a deadlock

On Fri, 05 Jun 2009 17:35:19 -0400 Tom Lane wrote: > Ivan Sergio Borgonovo writes: > > I don't get it. > > Why dropping the triggers would cause a deadlock anyway? > > > I bet it is due to my naïve view of the problem but I think a > > trigger is a "function". Unless there is concurrent access

Re: [GENERAL] Why lots of temp schemas are being created

On Mon, Jun 8, 2009 at 6:57 AM, Anirban Pal wrote: > Dear all, > > Our software use postgres as backend database. It works fine, strange thing > is that, it creates so many temporary schemas under schema tab, names like > pg_toast, pg_toast_temp_1 like this upto pg_toast_temp40. This schemas also >

Re: [GENERAL] INTERVAL SECOND limited to 59 seconds?

This is of course unexpected to me (one day becomes an hour)... Actually I would even expect an error, because there are missing interval parts. To represent a valid day to second interval, you should write '1 00:00:00' ... '1' would be a valid day to day interval. Always providing all interva

[GENERAL] Why lots of temp schemas are being created

Dear all, Our software use postgres as backend database. It works fine, strange thing is that, it creates so many temporary schemas under schema tab, names like pg_toast, pg_toast_temp_1 like this upto pg_toast_temp40. This schemas also reflected in "pg_namespace" table. Can anybody tell me, i