[GENERAL] Inserts hang in DB and error messages in log

Hi, We're working with version 8.3.5-1. Lately we started seeing insert statements hang in the DB. The statements come from two different clients. When it happens, I see the following messages in the log every second or so: 2009-05-21 08:56:49 IDT ERROR: permission denied to set pa

[GENERAL] changing the locale of the DB cluster

Hi, I know that it is impossible to change the locale. But can I pg_dump all the databases, run initdb again using the desired locale, and then pg_restore? - -- Kent Tong Wicket tutorials freely available at http://www.agileskills2.org/EWDW Axis2 tutorials freely available at http://www.ag

Re: [GENERAL] How should I deal with disconnects during insert?

On Wed, May 20, 2009 at 01:41:33PM -0700, Sergey Samokhin wrote: > There are two different things which may happen with such a buffer: > > 1) I've found that once PostgreSQL has started writing, connection can > safely be lost without affecting the data passed. In this case all the > data will be

Re: [GENERAL] How should I deal with disconnects during insert?

On Wed, May 20, 2009 at 4:41 PM, Sergey Samokhin wrote: > Is there a way to do #1 (it's a bit cleaner and simpler to implement)? You could wrap the whole thing in a transaction. If you don't get to the commit, the whole transaction should roll back. -- - David T. Wilson david.t.wil...@gmail.co

[GENERAL] How should I deal with disconnects during insert?

Hello! Problem I'm talking about in this letter is related to how we deal with extremely big amounts of data to be inserted into DB. Recently I switched to using buffers. The best approach to make a buffer I know (and the only I can use with my driver) is to create a long query (possibly made of

Re: [GENERAL] HOT question - insert/delete

On Wed, May 20, 2009 at 3:11 PM, Gerhard Wiesinger wrote: > Hello! > > Is HOT in 8.3 used in the following scenario: > INSERT ... > DELETE ... > at the same ratio. > > So for example current data is added every minute and all old data older > than 2 years are deleted. > > Would the heap be bloated

Re: [GENERAL] Help with join syntax sought

In article <43639.216.185.71.24.1242834374.squir...@webmail.harte-lyne.ca>, "James B. Byrne" writes: > What I want to be able to do is to return the most recent rate for > all unique rate-pairs, irrespective of type. I also have the > requirement to return the 5 most recent rates for each rate-p

[GENERAL] HOT question - insert/delete

Hello! Is HOT in 8.3 used in the following scenario: INSERT ... DELETE ... at the same ratio. So for example current data is added every minute and all old data older than 2 years are deleted. Would the heap be bloated or is this avoided by the HOT feature of 8.3 and nearly kept constant?

Re: [GENERAL] Regarding visual studio 2008 build

Vikram Patil wrote: > > > Hello All, > > > > I am trying to compile postgresql with visual studio but I am > not successful yet. Actually I just need binaries which are built with > Visual Studio 2008. > > Please provide some idea or input about future plans to compile > postgresql

[GENERAL] Regarding visual studio 2008 build

Hello All, I am trying to compile postgresql with visual studio but I am not successful yet. Actually I just need binaries which are built with Visual Studio 2008. Please provide some idea or input about future plans to compile postgresql with Visual Studio 2008. Thanks & Regards,

Re: [GENERAL] Help with join syntax sought supplemental

James B. Byrne wrote: On Wed, May 20, 2009 13:07, James B. Byrne wrote: This seems to be working. I had to take a different approach as I had misapprehended GROUP BY completely. SELECT * FROM currency_exchange_rates AS xchg1 WHERE id IN ( SELECT id FROM currency_exchange_rates as xc

Re: [GENERAL] Where is tsearch2.sql

Howard Cole wrote: Please help :( I have tried to install postgres 8.3.7 on three machines using the pginstaller and none of them appear to install the contrib/tsearch2.sql file. If I look at my old installations (8.3.4 and 8.3.6) then the contrib/tsearch2.sql file is there. Is there a p

Re: [GENERAL] Help with join syntax sought supplemental

On Wed, May 20, 2009 13:07, James B. Byrne wrote: > This seems to be working. I had to take a different approach as I > had misapprehended GROUP BY completely. > > > SELECT * > FROM currency_exchange_rates AS xchg1 > WHERE id > IN ( > SELECT id > FROM currency_exchange_rates as xchg2 >

Re: [GENERAL] Help with join syntax sought

James B. Byrne wrote: On Tue, May 19, 2009 17:43, Andy Colson wrote: . What field is the source? currency_code_quote? -Andy Here is the layout of the table: # Table name: currency_exchange_rates # # id :integer not null, primary key # currency_code_base

Re: [GENERAL] Help with join syntax sought

This seems to be working. I had to take a different approach as I had misapprehended GROUP BY completely. SELECT * FROM currency_exchange_rates AS xchg1 WHERE id IN ( SELECT id FROM currency_exchange_rates as xchg2 WHERE xchg1.currency_code_base = xchg2.currency_code_base

Re: [GENERAL] [Windows] Feedback on PG?

On Wednesday 20. May 2009, Dave Page wrote: >On Wed, May 20, 2009 at 7:38 AM, Ivan Sergio Borgonovo >> oh and yeah... I know a "Tuning Wizard" is evil and will hide all >> the true unleashed hidden wonderful power you can really squeeze out >> of Postgresql and corrupt your soul... but still ;) >

Re: [GENERAL] [Windows] Feedback on PG?

Ivan Sergio Borgonovo wrote: On Wed, 20 May 2009 11:59:34 +0100 Raymond O'Donnell wrote: On 20/05/2009 11:49, Ivan Sergio Borgonovo wrote: I think you could connect from Delphi through ODBC, meybe even on some .NET driver. I think ODBC should be more mature... but don't trust me.

Re: [GENERAL] Getting ID of last-inserted row

On Wed, May 20, 2009 at 4:35 AM, David wrote: >>> I never found an adequate (simple and efficient) method for getting >>> the primary key ID of the just-inserted row, and usually used >>> transactions and "select last value, ordered by id"-type queries to >>> get the last id value, or other ugly l

Re: [GENERAL] [Windows] Feedback on PG?

(I sent this message four hours ago, but it hasn't appeared on the list. I'll make a second try.) On Wednesday 20. May 2009, Dave Page wrote: >On Wed, May 20, 2009 at 7:38 AM, Ivan Sergio Borgonovo >> oh and yeah... I know a "Tuning Wizard" is evil and will hide all >> the true unleashed hidden

Re: [GENERAL] Help with join syntax sought

On Tue, May 19, 2009 17:43, Andy Colson wrote: . > > What field is the source? currency_code_quote? > > -Andy > Here is the layout of the table: # Table name: currency_exchange_rates # # id :integer not null, primary key # currency_code_base :string(3)

Re: [GENERAL] corruption on data table disconnects other clients silently when page is read

On Wed, May 20, 2009 at 11:25 AM, Tom Lane wrote: > Vick Khera writes: >> Not only are the other clients disconnected, but there is NO >> indication whatsoever in the server log that the DB disconnected any >> clients and is "recovering". > > You sure you're looking at the right server log?  This

Re: [GENERAL] corruption on data table disconnects other clients silently when page is read

Vick Khera writes: > Not only are the other clients disconnected, but there is NO > indication whatsoever in the server log that the DB disconnected any > clients and is "recovering". You sure you're looking at the right server log? This certainly sounds pretty much like a standard crash-and-rec

[GENERAL] corruption on data table disconnects other clients silently when page is read

I have a table that when I run a sequential scan on it results in this error: ERROR: compressed data is corrupt I tried to reindex the table, and I got this error on only one of the indexes: ERROR: index row requires 509139048 bytes, maximum size is 8191 This particular index references the p

Re: [GENERAL] Can I pause the pg_dump or pg_restore

On Wed, May 20, 2009 at 03:39:39PM +0100, Howard Cole wrote: > Sam Mason wrote: > >Note that when used on the pg_dump process all you're doing is stopping > >it from writing out the backup. The server process will still be > >running and waiting for the backup to finish writing the data. It will

Re: [GENERAL] Can I pause the pg_dump or pg_restore

Sam Mason wrote: On Wed, May 20, 2009 at 06:22:46PM +0530, Gurjeet Singh wrote: I don't know if the following would be recommended, but did some research and it seems that one can use STOP and CONT signals on Linux/Unix to pause/continue a process. As David noted; sending a STOP is exa

Re: [GENERAL] Where is tsearch2.sql

Please help :( I have tried to install postgres 8.3.7 on three machines using the pginstaller and none of them appear to install the contrib/tsearch2.sql file. If I look at my old installations (8.3.4 and 8.3.6) then the contrib/tsearch2.sql file is there. Is there a problem with my insta

Re: [GENERAL] Can not decompress a compressed string under plpy!

On Wednesday 20 May 2009 6:06:17 am Timmy wrote: > Hi, >I have stored a compressed string in a table field. > The compressed string is created by zlib using python. > I want to decompress this string by zlib under plpy but plpy fails to do > so. Is plpy not support decompression? Why? > > Thank

Re: [GENERAL] [Windows] Feedback on PG?

Gilles wrote: At 10:29 20/05/2009, Simon Riggs wrote: We get this question a lot. Yes, there are many technical differences between various open source products. Sorry about that. I should have dwelved deeper and read this before asking:

Re: [GENERAL] Table partitioning

=?ISO-8859-1?Q?Robert_Gravsj=F6?= writes: > Is there a reason to avoid populating the master table other that the > cost of the data always being included when querying? You can do it if you want ... I think most people think it complicates data management, but there's nothing in the system desi

[GENERAL] Can not decompress a compressed string by plpy!

Hi, I have stored a compressed string in a table field. I want to decompress this string by plpy but plpy fails to do so. Is plpy not support this? Why? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.

[GENERAL] Can not decompress a compressed string under plpy!

Hi, I have stored a compressed string in a table field. The compressed string is created by zlib using python. I want to decompress this string by zlib under plpy but plpy fails to do so. Is plpy not support decompression? Why? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] Can I pause the pg_dump or pg_restore

On Wed, May 20, 2009 at 06:22:46PM +0530, Gurjeet Singh wrote: > I don't know if the following would be recommended, but did some research > and it seems that one can use STOP and CONT signals on Linux/Unix to > pause/continue a process. As David noted; sending a STOP is exactly what your shell do

Re: [GENERAL] Can I pause the pg_dump or pg_restore

On Wed, May 20, 2009 at 2:53 PM, Chen, Dongdong (GE Healthcare) < dongdongc...@ge.com> wrote: > In my application, when press button "Backup" in UI, it invokes pg_dump > to backup the database. It may take few minutes for the whole process. If I > want to pause the process, what should I do. And

[GENERAL] package for debugger/wizard was: Feedback on PG?

On Wed, 20 May 2009 07:46:02 -0400 Dave Page wrote: > > Beside the fact I can only thank for all the great work around > > postgresql, is there a reason we can't have something similar on > > eg. Debian that will let us have a tuning wizard and a debugger > > that "just works" with an aptitude in

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, 20 May 2009, Dave Page wrote: The machines used to build the installers (which includes regression testing etc) all run either Sophos AV, or AVG Free Edition, neither of which I've ever had any problems with when working on PostgreSQL. I added this to a broken out section focusing on

Re: [GENERAL] [Windows] Feedback on PG?

On 20/05/2009 12:36, Ivan Sergio Borgonovo wrote: > What is the status of the .NET driver? > Are my assumptions about ODBC vs. .NET valuable or where they just a > remaining of the past/some nightmare I just had? > > Any appreciable difference between the Windows .NET driver and the > *nix one? I

Re: [GENERAL] Suggested way of associating PG user metadata with record

David, > A text column should be perfectly fine, and is the simplest > implementation. But it does have a few possible issues that you need > to keep in mind (besides things like users being removed). For > instance, if PostgreSQL users are renamed (eg: ALTER USER name RENAME > TO newname), then y

[GENERAL] Table partitioning

While reading through the docs on Partitioning, http://www.postgresql.org/docs/current/static/ddl-partitioning.html, I got to wonder about the example given. The text in 5.9.2 states, item 1 in the first section, that the master table will contain no data. This is later confirmed by the example

Re: [GENERAL] Get block of N numbers from sequence

On Wed, May 20, 2009 at 3:00 AM, Thomas Guettler wrote: > Thomas Guettler schrieb: >> Hi, > >> how can you get N numbers (without holes) from a sequence? >> > > If sequences could be locked like tables, it would be easy. advisory locks are superior for locking sequences in almost every way m

Re: [GENERAL] Providing an alternative result when there is no result

On Tue, May 19, 2009 at 7:00 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Mon, May 18, 2009 at 3:13 PM, Joshua Berry wrote: >>> Is there an easy and efficient way to return a boolean false for a query >>> that returns no result, and true for one that does return a result? > >> Probably the

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, May 20, 2009 at 7:38 AM, Ivan Sergio Borgonovo wrote: > On Wed, 20 May 2009 06:59:28 -0400 > Dave Page wrote: > >> On Wed, May 20, 2009 at 6:49 AM, Ivan Sergio Borgonovo >> wrote: >> ] >> > I was surprised how polished the installation was. >> >> Thanks :-) > > Beside the fact I can only

Re: [GENERAL] Suggested way of associating PG user metadata with record

>> >> That said, you could also use the value in pg_user.usesysid as a >> unique ID, rather than the account name (if you want to distinguish >> between different users with the same login name, over a period of >> time where users were removed and re-added). > > Perhaps I should just not use a for

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, 20 May 2009 06:59:28 -0400 Dave Page wrote: > On Wed, May 20, 2009 at 6:49 AM, Ivan Sergio Borgonovo > wrote: > ] > > I was surprised how polished the installation was. > > Thanks :-) Beside the fact I can only thank for all the great work around postgresql, is there a reason we can't

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, 20 May 2009 11:59:34 +0100 Raymond O'Donnell wrote: > On 20/05/2009 11:49, Ivan Sergio Borgonovo wrote: > > I think you could connect from Delphi through ODBC, meybe even on > > some .NET driver. I think ODBC should be more mature... but don't > > trust me. > > You can use ODBC and .NET

Re: [GENERAL] Suggested way of associating PG user metadata with record

David, > You should be careful about linking records to PostgreSQL user > accounts. If the PostgreSQL accounts are removed, there could be > problems with the foreign key references, if you don't implement it > carefully. > > That said, you could also use the value in pg_user.usesysid as a > uniqu

Re: [GENERAL] [Windows] Feedback on PG?

Raymond O'Donnell, 20.05.2009 12:59: I second thisit "just works", and the fact that the installer comes with a silent-install option means that you can roll it into your own installer for use in this sort of scenario. I like the fact that there is a ZIP file that I can simply extract which

Re: [GENERAL] Suggested way of associating PG user metadata with record

On Wed, May 20, 2009 at 12:28 PM, Peter Geoghegan wrote: > Hello, > > I'd like to associate the PG user who created certain sorts of records > with that record in my application. The obvious way to do this is by > creating a text column with a default value of current_user in the > record's table,

Re: [GENERAL] [Windows] Feedback on PG?

On 20/05/2009 11:59, Dave Page wrote: > Whilst I'm emailing - the thread earlier mentioned virus scanners as a > common source of problems (in fact, Craig gave a nice explanation of > why). The machines used to build the installers (which includes > regression testing etc) all run either Sophos AV

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, May 20, 2009 at 6:49 AM, Ivan Sergio Borgonovo wrote: ] > I was surprised how polished the installation was. Thanks :-) > On Windows the installer even comes with a couple of goodies more: > namely a tuning wizard and some debugging components. > Nothing that really make you miss the *ni

Re: [GENERAL] [Windows] Feedback on PG?

On 20/05/2009 11:49, Ivan Sergio Borgonovo wrote: > I think you could connect from Delphi through ODBC, meybe even on > some .NET driver. I think ODBC should be more mature... but don't > trust me. You can use ODBC and .NET (depending on which version of Delphi you have), and there are also variou

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, 20 May 2009 09:32:13 +0200 Gilles wrote: > Hello > > I'm looking for an open-source DBMS that runs on Linux and > Windows. In order to make an informed choice, I would like some > feedback on the Windows port of PostgreSQL, as compared to MySQL > and Firebird (if you know of yet ot

Fwd: [GENERAL] Getting ID of last-inserted row

>> I never found an adequate (simple and efficient) method for getting >> the primary key ID of the just-inserted row, and usually used >> transactions and "select last value, ordered by id"-type queries to >> get the last id value, or other ugly logic. > > use currval() instead, see > http://www.p

[GENERAL] Suggested way of associating PG user metadata with record

Hello, I'd like to associate the PG user who created certain sorts of records with that record in my application. The obvious way to do this is by creating a text column with a default value of current_user in the record's table, or perhaps something similar with a trigger. However, because what I

Re: [GENERAL] Getting ID of last-inserted row

On 20/05/2009 11:17, A. Kretschmer wrote: > In response to David : >> Hi there. >> >> I never found an adequate (simple and efficient) method for getting >> the primary key ID of the just-inserted row, and usually used >> transactions and "select last value, ordered by id"-type queries to >> get th

Re: [GENERAL] Getting ID of last-inserted row

In response to David : > Hi there. > > I never found an adequate (simple and efficient) method for getting > the primary key ID of the just-inserted row, and usually used > transactions and "select last value, ordered by id"-type queries to > get the last id value, or other ugly logic. use currva

Re: [GENERAL] [Windows] Feedback on PG?

Gilles, If some users have been using the native Win32 version of PG in production, > I'd like to hear how it runs in terms of stability and performance. This > DBMS is meant to be used in SOHO settings, meaning it should be very easy to > install, use, and kept up-to-date. > I have used the nati

Re: [GENERAL] Can I pause the pg_dump or pg_restore

2009/5/20 Grzegorz Jaśkiewicz : > restore you can, kind of pause, by pausing data feed to it. (assuming > you have a control over pipe, and you are using text dump format). > as for pg dump, I don't think there's a way. > pg_dump should be pauseable, if you use your OS's job-handling tools. For in

[GENERAL] Getting ID of last-inserted row

Hi there. I never found an adequate (simple and efficient) method for getting the primary key ID of the just-inserted row, and usually used transactions and "select last value, ordered by id"-type queries to get the last id value, or other ugly logic. That was until I found how SQLalchemy[1] hand

Re: [GENERAL] Can I pause the pg_dump or pg_restore

restore you can, kind of pause, by pausing data feed to it. (assuming you have a control over pipe, and you are using text dump format). as for pg dump, I don't think there's a way. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://

Re: [GENERAL] Can I pause the pg_dump or pg_restore

On 20/05/2009 10:23, Chen, Dongdong (GE Healthcare) wrote: > In my application, when press button "Backup" in UI, it invokes pg_dump > to backup the database. It may take few minutes for the whole process. > If I want to pause the process, what should I do. And also how to resume > the process? Can

Re: [GENERAL] INTERVAL data type and libpq - what format?

Sam Mason wrote: > You get an error because " 123 11" isn't a valid literal of an > (undecorated) INTERVAL type. Hmm. should it be? Skimming the spec makes me think it might be a valid day-time interval. Quoting the spec: ::= [ ] { | } ... ::= | ::= [ [ [ ] ] ]

[GENERAL] Can I pause the pg_dump or pg_restore

In my application, when press button "Backup" in UI, it invokes pg_dump to backup the database. It may take few minutes for the whole process. If I want to pause the process, what should I do. And also how to resume the process? Can I pause while pg_restore? Thanks KevinChen

Re: [GENERAL] [Windows] Feedback on PG?

Gilles, 20.05.2009 09:32: Is the Windows port on par with the *nix version, and with those other alternatives? Apart from the fact that, unlike MySQL, PostgreSQL doesn't require buying a license when developping commercial applications, are there technical reasons why I should choose PostgreSQL

Re: [GENERAL] [Windows] Feedback on PG?

At 10:58 20/05/2009, Craig Ringer wrote: I suspect a lot of that comes down to user/admin knowledge as much as anything. [...] That said, there are also a few bugs lurking that only affect the Windows version. [...] I've used Pg on my laptop at various points when it's been running Windows, and

Re: [GENERAL] [Windows] Feedback on PG?

Gilles wrote: > I see that "Windows compatibility is available via the Cygwin framework. > Native Windows compatibility is also available with version 8.0 and > above": At this point, is installing the server still a headache? That's hopelessly out of date, wherever it came from. PostgreSQL 8.3

Re: [GENERAL] [Windows] Feedback on PG?

At 10:29 20/05/2009, Simon Riggs wrote: We get this question a lot. Yes, there are many technical differences between various open source products. Sorry about that. I should have dwelved deeper and read this before asking:

Re: [GENERAL] [Windows] Feedback on PG?

At 10:20 20/05/2009, Richard Huxton wrote: You'll probably need to give some details of how you intend to use it. One-off on large server hardware? Hundreds of copies deployed around the world on XP machines 5 years old? Sorry, forgot about this point: It's just for concurrent accesses from a

Re: [GENERAL] [Windows] Feedback on PG?

Richard Huxton wrote: >> Is the Windows port on par with the *nix version, and with those other >> alternatives? > The main Windows problems we see on the mailing lists all revolve around > (1) installation and (2) anti-virus. PostgreSQL runs as a "unprivileged" > user in unix terms, and given th

Re: [GENERAL] [Windows] Feedback on PG?

Gilles wrote: Hello I'm looking for an open-source DBMS that runs on Linux and Windows. In order to make an informed choice, I would like some feedback on the Windows port of PostgreSQL, as compared to MySQL and Firebird (if you know of yet other solutions, I'm also interested.) You'll

Re: [GENERAL] [Windows] Feedback on PG?

On Wed, 2009-05-20 at 09:32 +0200, Gilles wrote: > I'm looking for an open-source DBMS that runs on Linux and > Windows. > In order to make an informed choice, I would like some feedback on > the Windows port of PostgreSQL, as compared to MySQL and Firebird (if > you know of yet other s

Re: [GENERAL] Locking to restrict rowcounts.

Just realised I didn't cc: the list on this. Was obviously having a good evening. Richard Huxton wrote: Glen Parker wrote: > I still don't think it will work. Two concurrent transactions could still screw the data up. Before: 9 rows. Tx 1: See 9 rows, issue insert, see 10 rows. Tx 2: See 9

[GENERAL] [Windows] Feedback on PG?

Hello I'm looking for an open-source DBMS that runs on Linux and Windows. In order to make an informed choice, I would like some feedback on the Windows port of PostgreSQL, as compared to MySQL and Firebird (if you know of yet other solutions, I'm also interested.) Is the Windows port on pa

Re: [GENERAL] Get block of N numbers from sequence

Thomas Guettler schrieb: > Hi, > > how can you get N numbers (without holes) from a sequence? > If sequences could be locked like tables, it would be easy. In old versions of postgres it worked: http://archives.postgresql.org//pgsql-hackers/2001-10/msg00930.php Thomas -- Thomas Guettler

Re: [GENERAL] Get block of N numbers from sequence

Boszormenyi Zoltan schrieb: > Thomas Guettler írta: >> Hi, >> >> how can you get N numbers (without holes) from a sequence? > # create sequence tmp_seq cache 1000; Hi, "alter SEQUENCE ... cache 100" survives a rollback. That's something I like to avoid: foo_esg_modw=# select * from foo_isu_is