Re: [GENERAL] Index greater than 8k

The problem I am after is the 8k index size issue. It is very easy to get a GIST index (especially when using tsearch2) that is larger than that. Hmm, tsearch2 GIST index is specially designed for support huge index entry: first, every lexemes in tsvectore are transformed to hash value (with a h

Re: [GENERAL] postgresql books and convertion utilities

On Monday 30 October 2006 07:55, Richard Broersma Jr wrote: > > Alexander Staubo wrote: > > >> Which one is worth from following books? > > >> > > >> 1. PostgreSQL (2nd Edition) by Korry Douglas (Paperback - Jul 26, > > >> 2005) > > > > > > This is a good book. As far as I know it's the most up-to-

Re: [GENERAL] postgresql books and convertion utilities

On Monday 30 October 2006 20:50, Ganbold wrote: > >>> Also I'm thinking to convert our mysql db and application to > >>> postgresql. Is there any method to convert mysql db/app to > >>> postgresql, maybe at least DB (tables, indexes, queries)? Are > >>> there any tools that can convert php functi

[GENERAL] PostgreSQL Mhash functions

I created a set of PostgreSQL functions which implement the extended set of digest/hashing functions provided by the Mhash library (http://mhash.sourceforge.net/). For anyone interested, the code is available here: http://www.yellowbank.com/code/PostgreSQL/y_mhash/ Best. -- Ron Peterson https:

Re: [GENERAL] Bad performance in bulky updates

> Is there any other suggestion to improve the performance of those updates? Perhaps turning on query duration logging so see if any particular queries are giving you grief. Other than that I would repost this email on the preformance list as you will get better responses from that list. :o) R

Re: [GENERAL] creating a dumpfile from a view

On Tue, Oct 31, 2006 at 02:38:27AM +0200, Taras Kopets wrote: > Richard Yen wrote: > > >Would anyone know how to dump the data from the view? > > You should check COPY command in the manual: > http://www.postgresql.org/docs/current/static/sql-copy.html to save all your > data. > But you have to u

Re: [GENERAL] postgresql books and convertion utilities

> As lot of people recommend I will definitely buy first book. Also > will consider second book since it seems like easy to read and follow. Don't for get one of the best "books" of all: http://www.postgresql.org/docs/8.1/interactive/index.html Regards, Richard Broersma Jr. ---

Re: [GENERAL] Bad performance in bulky updates

"Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > When one of the biggest tables has all lines updated for example, it takes > at about 30 minutes for processing. If we drop all indexes (21) and let only > the primary index the same update takes 2 minutes. 21 indexes?? If update performance is imp

[GENERAL] Bad performance in bulky updates

Hi,   We have very bad performance issues in one of our customer PostgreSQL servers and we would like some suggestions to improve the performance for bulky updates.   When one of the biggest tables has all lines updated for example, it takes at about 30 minutes for processing. If we drop all

Re: [GENERAL] postgresql books and convertion utilities

Hi, David Fetter wrote: On Mon, Oct 30, 2006 at 09:10:36AM -0500, Chander Ganesan wrote: Ganbold wrote: Hi, I'm new to postgresql and I'm looking for references and books. We are usually coding in php/C. Can somebody recommend me good books which covers Postgres

Re: [GENERAL] limiting connections on "per database/per user" basis

On Monday 30 October 2006 17:17, "Hakka Ville" <[EMAIL PROTECTED]> wrote: > Dear Sirs, > > I have to setup shared web-hosting and I think of limiting connections to > PostgreSQL cluster on database/user basis. > > Any idea how to do that ? > > I simply don't want situation when single user (on of h

[GENERAL] limiting connections on "per database/per user" basis

Dear Sirs,I have to setup shared web-hosting and I think of limiting connections to PostgreSQL cluster on database/user basis.Any idea how to do that ?I simply don't want situation when single user (on of hundreds) will take all the available connections. Cheers!

Re: [GENERAL] Deleting Problem

On Tue, 2006-10-31 at 10:24 +1100, Jamie Deppeler wrote: Here is my problem i have delete triggers on table5 and table6 which update summary information on table1,table2,table3,table4 if table5 or 6 gets delete, problem i am faced with is when eg table1 record gets deleted i

Re: [GENERAL] creating a dumpfile from a view

On Mon, 2006-10-30 at 16:02 -0800, Richard Yen wrote: Hi, I'm trying to create a dumpfile for a client. The data is gathered from about 7 tables, and I need to output all the columns as  the client wishes. [snip] Would anyone know how to dump the data from the view?  I tried the  fol

Re: [GENERAL] creating a dumpfile from a view

Hi! Richard Yen wrote: Would anyone know how to dump the data from the view? You should check COPY command in the manual: http://www.postgresql.org/docs/current/static/sql-copy.html to save all your data. But you have to use tables with copy, not views. Probably the solution will be to store al

Re: [GENERAL] Deleting Problem

On Tue, 31 Oct 2006, Jamie Deppeler wrote: > 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 >

[GENERAL] creating a dumpfile from a view

Hi, I'm trying to create a dumpfile for a client. The data is gathered from about 7 tables, and I need to output all the columns as the client wishes. I figure the best way to this is to collect data from multiple tables and putting them into a view, and using the client's desired names to

Re: [GENERAL] Stripping empty space from all fields in a table?

Worked perfectly...thank you!

[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

Re: [GENERAL] CREATE TABLE initial value for PRIMARY KEY

Maurice Yarrow wrote: So it turned out to be possible to do it like this: CREATE SEQUENCE id_seq; SELECT setval('id_seq',100111); FYI, you could have done this: CREATE SEQUENCE id_seq START 100111; - John D. Burger MITRE ---(end of broadcast)---

Re: [GENERAL] WAL Archiving under Windows

Hi!> archive_command = 'copy %p d:\\backup\\logs\\%f' I think you should try something like this:archive_command = 'copy %p d:/backup/logs/%f'(using single / instead of \\).In documentation it is said:restore_command = 'copy /mnt/server/archivedir/%f "%p"' # Windows So I think the same format of

Re: [GENERAL] WAL Archiving under Windows

Tim Tassonis wrote: We use version 8.1.3 and the following archive_coomand: archive_command = 'copy %p d:\\backup\\logs\%f' ^^^ Could the lack of a double-backslash be causing the problem? Sorry, that was a problem on my quoting. The config file re

Re: [GENERAL] 8.1.5 RPMS

> Hi, > > On Mon, 2006-10-30 at 09:17 -0700, Leonel Nunez wrote: >> is it safe to go back to 8.1.4 ? >> or can we safely wait for the fix ? > > If it is a fresh install, then it will probably not work for you. It > sets a wrong homedir for postgres user. However, if this is an upgrade, > then there

Re: [GENERAL] WAL Archiving under Windows

Richard Huxton wrote: Tim Tassonis wrote: Hi Has anybody got any expierience with PITR recovery under Windows. PostgreSQL just doesn't seem to copy the WAL Files to the acrive location. We have done the initial backup and the first wal file was copied, but after that, it seems to ignore the

Re: [GENERAL] WAL Archiving under Windows

Tim Tassonis wrote: Hi Has anybody got any expierience with PITR recovery under Windows. PostgreSQL just doesn't seem to copy the WAL Files to the acrive location. We have done the initial backup and the first wal file was copied, but after that, it seems to ignore the newer ones. We now have

Re: [GENERAL] 8.1.5 RPMS

Hi, On Mon, 2006-10-30 at 09:17 -0700, Leonel Nunez wrote: > is it safe to go back to 8.1.4 ? > or can we safely wait for the fix ? If it is a fresh install, then it will probably not work for you. It sets a wrong homedir for postgres user. However, if this is an upgrade, then there will be no pr

Re: [GENERAL] postgresql books and convertion utilities

On Mon, Oct 30, 2006 at 09:10:36AM -0500, Chander Ganesan wrote: > Ganbold wrote: > >Hi, > > > >I'm new to postgresql and I'm looking for references and books. We are > >usually coding in php/C. > > > >Can somebody recommend me good books which covers Postgresql 8.x? > > > >Which one is worth from

Re: [GENERAL] Stripping empty space from all fields in a table?

On Fri, Oct 27, 2006 at 05:21:47PM -0700, David Fetter wrote: > On Fri, Oct 27, 2006 at 03:39:21PM -0400, J B wrote: > > Guys, > > I have a table that has various fields that have whitespace in the > > values. I'd like to roll through and strip the left and right > > whitespace out of all fields t

[GENERAL] Index greater than 8k

Hello, I recently posted about a word being too long with Tsearch2. That isn't actually the problem I am trying to solve (thanks for the feedback though, now I understand it). The problem I am after is the 8k index size issue. It is very easy to get a GIST index (especially when using tsearch2) t

[GENERAL] WAL Archiving under Windows

Hi Has anybody got any expierience with PITR recovery under Windows. PostgreSQL just doesn't seem to copy the WAL Files to the acrive location. We have done the initial backup and the first wal file was copied, but after that, it seems to ignore the newer ones. We now have two more wal filex

Re: [GENERAL] 8.1.5 RPMS

Leonel Nunez wrote: > Hello > > I've read on the postgresql weekly news the RPMS for 8.1.5 has a serious bug > > http://archives.postgresql.org/pgsql-announce/2006-10/msg00018.php > > is it safe to go back to 8.1.4 ? > or can we safely wait for the fix ? If they are already working for you, it

Re: [GENERAL] log_duration and JDBC V3 Preparded Statements

On Mon, 2006-10-30 at 10:27 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > Will do. Is this strictly an 8.2 patch, or will it be back-ported to > > 8.1 and 7.4? > > We aren't going to change the behavior of logging that much in existing > releases --- or were you just thi

[GENERAL] 8.1.5 RPMS

Hello I've read on the postgresql weekly news the RPMS for 8.1.5 has a serious bug http://archives.postgresql.org/pgsql-announce/2006-10/msg00018.php is it safe to go back to 8.1.4 ? or can we safely wait for the fix ? thank you Leonel ---(end of broadcast)

Re: [GENERAL] NOTICE: word is too long INSERT 0 3014

For example, redefine by follow way: typedef struct { uint32 haspos:1, len:31; uint32 pos; } WordEntry; /* <= 1Gb */ #define MAXSTRLEN ( 1<<30 ) #define MAXSTRPOS ( 1<<30 ) Teodor Sigaev wrote: I am running into this limitation ALOT with Tsearch2.

Re: [GENERAL] log_duration and JDBC V3 Preparded Statements

Brad Nicholson <[EMAIL PROTECTED]> writes: > Will do. Is this strictly an 8.2 patch, or will it be back-ported to > 8.1 and 7.4? We aren't going to change the behavior of logging that much in existing releases --- or were you just thinking of that core-dump condition in 8.1? That's fixed in 8.1.

Re: [GENERAL] Wordpress & PostgreSQL ...

On 10/30/06, Ashley Moran <[EMAIL PROTECTED]> wrote: ... clean, elegant nature of PHP seems taken from MySQL. Ashley XML parsing failed (2 errors) :-) -- Best regards, Nikolay ---(end of broadcast)--- TIP 5: don't forget to increase your free

Re: [GENERAL] log_duration and JDBC V3 Preparded Statements

On Mon, 2006-10-30 at 10:14 -0500, Tom Lane wrote: > Brad Nicholson <[EMAIL PROTECTED]> writes: > > I'm wondering what that status of the fix for this is. > > AFAIK it all works ... grab beta2 and try it. > Will do. Is this strictly an 8.2 patch, or will it be back-ported to 8.1 and 7.4? -- B

Re: [GENERAL] log_duration and JDBC V3 Preparded Statements

Brad Nicholson <[EMAIL PROTECTED]> writes: > I'm wondering what that status of the fix for this is. AFAIK it all works ... grab beta2 and try it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the p

Re: [GENERAL] access and security

On Mon, 2006-10-30 at 15:36 +0100, Martijn van Oosterhout wrote: > On Mon, Oct 30, 2006 at 01:34:34PM +0100, Andrew Kelly wrote: > > Hi all, > > > > please forgive a (likely) less than clever question. > > > > Are the barriers provided by pg_hba.conf enough from a security > > standpoint, or is i

Re: [GENERAL] postgresql books and convertion utilities

I was new to postgresql and have had to use it with both C and PHP. Having purchased both books, I have found #2 in your list a lot more useful than #1 (don't let the "beginning" title fool you into thinking it's just a 'hello world' book). #2 is better organized for what I'm doing, especia

Re: [GENERAL] access and security

On Mon, Oct 30, 2006 at 01:34:34PM +0100, Andrew Kelly wrote: > Hi all, > > please forgive a (likely) less than clever question. > > Are the barriers provided by pg_hba.conf enough from a security > standpoint, or is it best to put up some iptable rules duplicating the > restrictions? iptables c

[GENERAL] log_duration and JDBC V3 Preparded Statements

I'm wondering what that status of the fix for this is. Looking at the archives, it looks like Bruce had a patch http://beta.linuxports.com/pgsql-jdbc/2006-08/msg00036.php I don't see anything in the release notes though. What's the status on this? -- Brad Nicholson 416-673-4106 Database Admini

Re: [GENERAL] postgresql books and convertion utilities

Ganbold wrote: Hi, I'm new to postgresql and I'm looking for references and books. We are usually coding in php/C. Can somebody recommend me good books which covers Postgresql 8.x? Which one is worth from following books? 1. PostgreSQL (2nd Edition) by Korry Douglas (Paperback - Jul 26, 200

Re: [GENERAL] postgresql books and convertion utilities

> Alexander Staubo wrote: > >> Which one is worth from following books? > >> > >> 1. PostgreSQL (2nd Edition) by Korry Douglas (Paperback - Jul 26, > >> 2005) > > > > This is a good book. As far as I know it's the most up-to-date > > general-purpose PostgreSQL book. > > It covers up to 8.0. >

Re: [GENERAL] access and security

am Mon, dem 30.10.2006, um 13:34:34 +0100 mailte Andrew Kelly folgendes: > Hi all, > > please forgive a (likely) less than clever question. > > Are the barriers provided by pg_hba.conf enough from a security > standpoint, or is it best to put up some iptable rules duplicating the > restrictions?

Re: [GENERAL] Wordpress & PostgreSQL ...

On Monday 30. October 2006 10:31, Ashley Moran wrote: >On 30 Oct 2006, at 06:15, Nikolay Samokhvalov wrote: >> Could you please give some example of such "inspired by MySQL >> features of PHP design"? > >Sorry... perhaps I should have wrapped my comment in bitterness="10"/> to make it more clear w

[GENERAL] access and security

Hi all, please forgive a (likely) less than clever question. Are the barriers provided by pg_hba.conf enough from a security standpoint, or is it best to put up some iptable rules duplicating the restrictions? Andy ---(end of broadcast)--- TIP 2:

Re: [GENERAL] postgresql books and convertion utilities

Alexander Staubo wrote: >> Which one is worth from following books? >> >> 1. PostgreSQL (2nd Edition) by Korry Douglas (Paperback - Jul 26, >> 2005) > > This is a good book. As far as I know it's the most up-to-date > general-purpose PostgreSQL book. It covers up to 8.0. It's a little verbose

Re: [GENERAL] Wordpress & PostgreSQL ...

On 30 Oct 2006, at 06:15, Nikolay Samokhvalov wrote: Could you please give some example of such "inspired by MySQL features of PHP design"? Sorry... perhaps I should have wrapped my comment in bitterness="10"/> to make it more clear what I meant. I didn't mean that MySQL literally inspir

Re: [GENERAL] postgresql books and convertion utilities

On Oct 30, 2006, at 03:30 , Ganbold wrote: Which one is worth from following books? 1. PostgreSQL (2nd Edition) by Korry Douglas (Paperback - Jul 26, 2005) This is a good book. As far as I know it's the most up-to-date general-purpose PostgreSQL book. If you are interested in previewing

Re: [GENERAL] How to know the type of an expression ?

> Is there a means to know the type of an expression ? > Something like a "typeof" function ? > > As I work a lot with numbers of type "numeric", I often want to know > the precision of the result returned by an expression, in case the > returned result is "numeric". > I would also like to know if

Re: [GENERAL] Replicating changes

I'd start with something fairly straightforward. dbmirror is very simple but does a lot. There are two parts: A trigger function and set of database tables to collect replication data. The trigger function is written is 'C' and performance is good. The second part is a Perl script that attaches t

Re: [GENERAL] Replicating changes

Taras Kopets wrote: Hi! I think you should try to use triggers with dbi-linkto achieve this. AFAIK there is no such replication with other RDBMS as you need. Taras Kopets Seems like I introduced a small misunderstanding here. The data doesn't necessar

Re: [GENERAL] NOTICE: word is too long INSERT 0 3014

I am running into this limitation ALOT with Tsearch2. What are my options to get around it. Do I have to compile PostgreSQL with a different block size? If yes, what are the downsides to doing so (outside of not being able to do straight upgrades)? If you really need that, your should play arou