Re: [GENERAL] Well, Pervasive is now out....

[EMAIL PROTECTED] (Joe Audette) writes: > My guess is what this really means is they weren't making money on > it. Its a community friendly spin to suggest that community support > is so good that not many companies will pony up for commercial > support. My guess is that its fairly accurate though.

Re: [GENERAL] Dumping database using 8.1 or 7.1

On 8/3/06, Jonathan Vallar <[EMAIL PROTECTED]> wrote: Hi! I have a corrupt database that runs on Postgres 7.1.2. I was able to repair the database where I can now connect to the database and do queries. I have a migration plan to transfer my database to a Postgres 8.1. I encountered problems

Re: [GENERAL] Create function problem

On Fri, Aug 04, 2006 at 06:16:41AM +0300, gustavo halperin wrote: > *OK thank you, you right, but after write "public" I receive again an > empty row, Why??. [...] > mydb=> CREATE OR REPLACE FUNCTION f_describe_tables (v_tbl_scm text, > v_tbl_name text, > mydb(> OUT text, OUT text) as > mydb-> $

Re: [GENERAL] Create function problem

Ron St-Pierre wrote: Check your spelling of public: SELECT * FROM f_describe_tables('pubilc', 'mil_cien_diez'); Ron *OK thank you, you right, but after write "public" I receive again an empty row, Why??. By the way I wrote a short function:* /mydb=> SELECT c.column_name, c.data_type mydb-> F

Re: [GENERAL] Create function problem

On Fri, Aug 04, 2006 at 01:51:19AM +0300, gustavo halperin wrote: > In order to know the names and data types of the table "mil_cien_diez" > from the schema "public" I run the next 'SELECT' but when I try to > create a SQL function and after it I run it, I receive an empty row. Can > you see the

Re: [GENERAL] Best Procedural Language?

I'd say that the biggest benefit of pl/pgsql for postgres is that it is so close to Oracle's own procedural language. This makes the job of porting from Oracle to postgres *nearly* trivial. Convincing a site to switch from Oracle to Postgres is therefroe easier and a major feather in postgres'

Re: [GENERAL] Importance of re-index

Scott Marlowe <[EMAIL PROTECTED]> writes: > Reindex was originally > designed to fix broken indexes, and, at least in earlier encarnations, > should something stop it in the middle of reindexing I believe it is > possible to be left with no index. That was once true but these days reindex is perfe

Re: [GENERAL] Importance of re-index

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Scott Marlowe wrote: > On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote: >> In addition to making sure databases are vacuumed regularly, it is worth >> running REINDEX on tables that see a lot of updates (or insert/deletes). >> >> Running REIND

[GENERAL] Create function problem

*Hello I have a little question In order to know the names and data types of the table "mil_cien_diez" from the schema "public" I run the next 'SELECT' but when I try to create a SQL function and after it I run it, I receive an empty row. Can you see the problem ??* /mydb=> SELECT c.column_n

Re: [GENERAL] Importance of re-index

On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote: > In addition to making sure databases are vacuumed regularly, it is worth > running REINDEX on tables that see a lot of updates (or insert/deletes). > > Running REINDEX on a regular basis will keep the indexes compacted and > can noticeab

[GENERAL] Importance of re-index

In addition to making sure databases are vacuumed regularly, it is worth running REINDEX on tables that see a lot of updates (or insert/deletes). Running REINDEX on a regular basis will keep the indexes compacted and can noticeably improve the database performance. The other benefit is that t

Re: [GENERAL] CREATE DATABASE

Thanks. 'connect to' cannot be sent to server as plain text, surely. I'm stupid :-( On 8/4/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote: > On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > [...] > > BTW, difference vanishes due to expres

Re: [GENERAL] CREATE DATABASE

On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote: > On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > [...] > > BTW, difference vanishes due to expression power of SQL - > > it supports session comands in the same context as DDL commands and > > data manipulation stmts (SQL:200n, 4.33.2.5

[GENERAL] PITR Questions

I'm setting up PITR for a client and have a few questions. I have done some googling for real world archive_command examples and haven't really found anything. The example in the PGSQL Docs are qualified by (This is an example, not a recommendation, and may not work on all platforms.) I hav

Re: [GENERAL] SAVEPOINT and FOR UPDATE

On Thu, 2006-08-03 at 12:43 -0500, Thomas F. O'Connell wrote: > I'm curious to know more about the postgres implementation of > subtransactions via SAVEPOINT. > Locks are held until the end of the outer transaction, see: in the first par

Re: [GENERAL] Well, Pervasive is now out....

My guess is what this really means is they weren't making money on it. Its a community friendly spin to suggest that community support is so good that not many companies will pony up for commercial support. My guess is that its fairly accurate though. A company is only going to use postgreSQL if

Re: [GENERAL] CREATE DATABASE

On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: [...] BTW, difference vanishes due to expression power of SQL - it supports session comands in the same context as DDL commands and data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection statements'). Sorry, I already see my failure

Re: [GENERAL] Well, Pervasive is now out....

On 8/4/06, J B <[EMAIL PROTECTED]> wrote: On 8/3/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > Let's believe that that was the real reason... :-) If not, what was? Is this really a commentary on how many "enterprise"-y types are using Postgres? I'm afraid that many people read only the

Re: [GENERAL] Well, Pervasive is now out....

On 8/3/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: Let's believe that that was the real reason... :-) If not, what was? Is this really a commentary on how many "enterprise"-y types are using Postgres? ---(end of broadcast)--- TIP 2: Don't

Re: [GENERAL] CREATE DATABASE

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote: The reason neither of these are going to happen is that you're confusing a server-side SQL command (viz CREATE DATABASE) with a client-side operation (viz choosing to drop the server connection and make a new one to a different database). Hmm.. I a

Re: [GENERAL] SAVEPOINT and FOR UPDATE

"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes: > I sort of expected the release of the savepoint to be tantamount to a > commit of the subtransaction, but it doesn't appear to have been. But you still haven't committed the outer transaction: rolling it back must undo the effects of the subtra

Re: [GENERAL] Well, Pervasive is now out....

I found this comment particularly telling: Pervasive Software President John Farr said last week that the company "underestimated the high level of quality support and expertise already available within the PostgreSQL community." I don't think I can add anything to that.

Re: [GENERAL] CREATE DATABASE

Rafal Pietrak <[EMAIL PROTECTED]> writes: > To put it the other way around: I don't remember me creating a database > and *needing* to stay within the initiator (like template1) - that's why > I've ventured the feature request. If not a 'CONNECT' option, may be z > "SET parameter" for a session use

Re: [GENERAL] PITR timeline question

Jim Nasby <[EMAIL PROTECTED]> writes: > If you're using multiple timelines with PITR, do you have to re-copy > all the data files into the cluster every time you start a new > timeline? Or can you copy the data files out of the backup once, and > then perform multiple recoveries, each to diff

Re: [GENERAL] Well, Pervasive is now out....

On 8/3/06, Tony Caduto <[EMAIL PROTECTED]> wrote: http://news.zdnet.com/2100-3513_22-6100795.html Let's believe that that was the real reason... :-) " ... In a letter to the PostgreSQL community of developers, Pervasive Software President John Farr said last week that the company "underestimat

Re: [GENERAL] Well, Pervasive is now out....

Tony Caduto wrote: http://news.zdnet.com/2100-3513_22-6100795.html Week late bud :) that was announced during OSCON. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehens

Re: [GENERAL] pg_xlog not cleaned up

Wayne Conrad <[EMAIL PROTECTED]> writes: > On Mon, Jul 31, 2006 at 10:18:31PM +0200, Christian Kastner wrote: >> After the online backup script runs, all subsequent attempts to run >> archive_command fail because the first thing it tries to archive away is >> the still-existing *.backup file. This

[GENERAL] Well, Pervasive is now out....

http://news.zdnet.com/2100-3513_22-6100795.html -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 1: if posting/r

Re: [GENERAL] CREATE DATABASE

On Thu, 2006-08-03 at 18:07 +0100, Richard Huxton wrote: > Rafal Pietrak wrote: > > When working on a new database application I quite frequently: > > --- > > test_xx# \c template1 > > template1# DROP DATABASE test_xx; > > template1# CREATE DATABASE test_xx T

Re: [GENERAL] PostgreSQL on RAM Disk / tmpfs

On 8/2/06, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote: I'm working on a postgres instance (8.1.2 running on Solaris 10) where the data directory (including WAL) is being mounted on tmpfs. Based on this, and with knowledge that fsync is disabled, I'm operating under the assumption that recovera

[GENERAL] PITR timeline question

If you're using multiple timelines with PITR, do you have to re-copy all the data files into the cluster every time you start a new timeline? Or can you copy the data files out of the backup once, and then perform multiple recoveries, each to different timelines? Ultimately, what I'd like t

[GENERAL] SAVEPOINT and FOR UPDATE

I'm curious to know more about the postgres implementation of subtransactions via SAVEPOINT.If I wanted to set up a multi-statement transaction in which I needed multiple SELECT ... FOR UPDATE + UPDATE blocks, it would seem advantageous to be able to combine the SELECT ... FOR UPDATE clauses with t

[GENERAL] Pl/TCL: Is my Windows install missing scripts?

The Windows-PosgreSQL 8.1.4 package I downloaded from the PostgreSQL download site (right here) does not seem to contain the scripts: pltcl_delmod pltcl_listmod pltcl_loadmod Have I missed something, or can anyone else confirm? Carlo ---(end of broadcast)

Re: [GENERAL] Fulltime Opportunities in Silicon Valley!

Maryellen wrote: Hi all, I have a few clients that are looking for Database Developers (not DBA's) with a focus on MS databases (SQL in particular). All are for 8+ year people. The need is high and I would like to help to make some matches, if appropriate. I have a solid company, a midsized compa

Re: [GENERAL] xmin

Ilja Golshtein wrote: Hello! Is it Ok to use xmin column in application programm? Purpose is obvios: I need to know the record was not changed since a moment in the past and things like this. So every UPDATE should somehow change value of the column. The choice it trigger or xmin. Which is b

Re: [GENERAL] Vacuum, Analyze, ReIndex from within VB.NET 2003

Rohit Prakash Khare wrote: I want to use the following features of PostgreSQL from within VB.NET 2003: Vacuum, Analyze, ReIndex. Is there any way to write a VB.NET code to do the following tasks? Is there some reason why you can't issue SQL with "VACCUM", "ANALYSE" and "REINDEX"? -- Rich

Re: [GENERAL] CREATE DATABASE

Rafal Pietrak wrote: Hi All, I'd like to cast a small 'feature request' here for discussion/ evaluation. The case is the following: When working on a new database application I quite frequently: --- test_xx# \c template1 template1# DROP DATABASE test_

[GENERAL] xmin

Hello! Is it Ok to use xmin column in application programm? Purpose is obvios: I need to know the record was not changed since a moment in the past and things like this. So every UPDATE should somehow change value of the column. The choice it trigger or xmin. Which is better (from perfomance, r

[GENERAL] Vacuum, Analyze, ReIndex from within VB.NET 2003

I want to use the following features of PostgreSQL from within VB.NET 2003: Vacuum, Analyze, ReIndex. Is there any way to write a VB.NET code to do the following tasks? Sign Up for your FREE eWallet at www.wallet365.com ---(end of broadcast)--- T

[GENERAL] CREATE DATABASE

Hi All, I'd like to cast a small 'feature request' here for discussion/ evaluation. The case is the following: When working on a new database application I quite frequently: --- test_xx# \c template1 template1# DROP DATABASE test_xx; template1#

Re: [GENERAL] Best Procedural Language?

Joshua, where can I find docs on how to return SETOF from Tcl? ""Joshua D. Drake"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > >>> - Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such >>>requires having an extra level of function manipulations that >>>won't be

Re: [GENERAL] Performance of the listen command

Flemming Frandsen wrote: > Christopher Browne wrote: > >There's a demerit: > >c) If there are a LOT of events, that might not fit in memory nicely. > > If you have that many events then the current implementation is going to > suck hard as well:) The difference is that the current implementation

Re: [GENERAL] pg_restore performance on solaris 10/6

On Aug 1, 2006, at 8:52 AM, Vivek Khera wrote: On Jul 31, 2006, at 3:45 PM, Thomas Burns wrote: our database literally takes 8 times longer on the x4200 as it does on OS X (the x4200 hardware should be considerably faster -- it has better for a restore like this, bump up the value of ch

Re: [GENERAL] LISTEN considered dangerous

On 8/2/06, Flemming Frandsen <[EMAIL PROTECTED]> wrote: Ian Harding wrote: > NOTIFY interacts with SQL transactions in some important ways. > Firstly, if a NOTIFY is executed inside a transaction, the notify > events are not delivered until and unless the transaction is > committed. This is appro

[GENERAL] Fulltime Opportunities in Silicon Valley!

Hi all, I have a few clients that are looking for Database Developers (not DBA's) with a focus on MS databases (SQL in particular). All are for 8+ year people. The need is high and I would like to help to make some matches, if appropriate. I have a solid company, a midsized company and a startup. A

Re: [GENERAL] proper use of array datatype

# [EMAIL PROTECTED] / 2006-08-02 10:49:01 -0700: > On 8/1/06, Reece Hart <[EMAIL PROTECTED]> wrote: > > > > Eric Andrews wrote: > >> I am not much of a schema designer and have a general questoin about > >> the proper use of the array datatype. In my example, I have > >> destinations, and destinati

Re: [GENERAL] Tape backup, 2 versions, same database name, which is pg_dump

Great, Thanks! *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential in

Re: [GENERAL] pg_restore performance on solaris 10/6

On Aug 2, 2006, at 8:05 PM, Thomas Burns wrote: I have 5 shared buffers and 1 temp buffers (the machine has 16G ram). The disks are new/fast SCSI drives what's their configuration? RAID? if so, what controller and setup? if not, what controller and setup? without all the detai

Re: [GENERAL] How to read log files

I have tried messing with the logging settings in postgres.conf but it always displays $1, i have set every level to debug 5 and logging all statements, but i still have had no success.Andy On 8/3/06, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote: On Aug 3, 2006, at 8:33 AM, Andy Dale wrote:> I am

Re: [GENERAL] How to read log files

On Aug 3, 2006, at 8:33 AM, Andy Dale wrote: I am currently trying to debug an issue we are experiencing and i have turned the server logging onto debug level 5 and filtering out the LOG messages, i still need some advice on how to interpret them. I an extract from the log is: [snip] T

[GENERAL] How to read log files

Hi,I am currently trying to debug an issue we are experiencing and i have turned the server logging onto debug level 5 and filtering out the LOG messages, i still need some advice on how to interpret them.  I an extract from the log is: 2006-08-03 09:37:21.643 CEST  LOG:  statement: PREPARE AS SEL

Re: [GENERAL] TSearch: Need debug help

hmm, I don't like this. Why not create synonym dictionary as written on http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes Because I found some more words with the same problem, and I have no idea how much there are in total :/ you need to reindex when you change dictionaries. I just tes

Re: [GENERAL] Row-level style access with sub tables

> In a nutshell, then, I'd like my users to be able to alter the data > in the tables, but only the data that they can see in the views, and > only in a way so it remains in their views after modification. > Has anyone got any advice? I've seen Veil suggested for problems like yours. It is a Post

[GENERAL] Row-level style access with sub tables

This a row-level security style question. Suppose I have a hypothetical warehouse database. In my warehouse everything is stored in a crate. My customers are going to rent crates from me. I'm going to provide a way that they can keep track of what's in each crate. Because my customers are very se

Re: [GENERAL] LISTEN considered dangerous

On Thu, Aug 03, 2006 at 12:43:47AM +0200, Flemming Frandsen wrote: > On Wed, 2 Aug 2006, Tom Lane wrote: > > > Flemming Frandsen <[EMAIL PROTECTED]> writes: > > > The listen should simply listen for events issued at the start of the > > > transaction it's executed in. > > > > BEGIN; > > SE

Re: [GENERAL] Best Procedural Language?

Joshua D. Drake wrote: However, Alvaro is correct there are currently no resources dedicated to PL/php. There will be in the future but for now we are busy with other things. Well consider me your biggest cheerleader, and when circumstances bring it to the front burner that will be great.

Re: [GENERAL] TSearch: Need debug help

but does 'dursten' is a some form of 'durst' ? Yes it is. Hm, even when I remove `dursten' and `durst' all together from the dict I still get `sen'. How can I update a tsvector column stripping the `sen' lexem? Thanks! On 03.08.2006 12:54, Oleg Bartunov wrote: Hannes, I don't know german

Re: [GENERAL] TSearch: Need debug help

Hannes, I don't know german, sorry, but does 'dursten' is a some form of 'durst' ? Probably, here we have false hit from compound word support. I'd suggest to use exclusion dictionary (on the base of synonym dictionary) before ispell. It could be very simple: durst : durst Oleg On Thu, 3 Au

Re: [GENERAL] Query performance

> i have a table with around 57 million tuples, with the following columns: > pid(varchar), crit(varchar), val1(varchar), val2(varchar). Example: > pidcritval1val2 > p1 c1 xy > p1 c2 xz > p1 c3 yx > ... > What i am doing is to quer

Re: [GENERAL] Query performance

Hi, here is the definition of the master table which is inherited by around 30 tables based on the value of chr: CREATE TABLE snp_master ( entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq'::regclass), pid varchar(15) NOT NULL, snp_id varchar(13) NOT NULL, val1 varchar(1), val2 varc

[GENERAL] Dumping database using 8.1 or 7.1

Hi! I have a corrupt database that runs on Postgres 7.1.2. I was able to repair the database where I can now connect to the database and do queries. I have a migration plan to transfer my database to a Postgres 8.1. I encountered problems when I was dumping the database. Postgres 7.1 reported: ge

Re: [GENERAL] Query performance

Hi, maybe you should overthink your db-design, but thats a bit premature whithout your complete table-definitions(including table-names, datatypes, indexes, foreign-key constraints,etc.) If your are using pgadmin3 just cut'n paste the content of the window on the bottom left for the correspo

Re: [GENERAL] Query performance

Hi, i would rather compare int4 too, but the snp_id can be something like "abc123" unfortunately. "Hakan Kocaman" <[EMAIL PROTECTED]> wrote on 08/03/06 11:08 am: > Hi, > > >> -Original Message- >> From: Richard Huxton [mailto:[EMAIL PROTECTED] >> Sent: Thursday, August 03, 2006 11:00

Re: [GENERAL] Query performance

Hi, > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 03, 2006 11:00 AM > To: Christian Rengstl > Cc: Hakan Kocaman; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Query performance > > > Christian Rengstl wrote: > > Hi, > > > > the

Re: [GENERAL] logic/db question

If table reorganization is an option for you, you could consider using integer ranges for describing parant/children relationships. I have seen them in one of Joe Celko books: http://www.amazon.com/gp/product/1558609202/sr=8-2/qid=1154595988/ref=pd_bbs_2/104-2243146-1376759?ie=UTF8 In that way,

Re: [GENERAL] Query performance

Christian Rengstl wrote: Hi, the complete query is the one i posted, but here comes the schema for mytable: entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass), pid varchar(15) NOT NULL, crit varchar(13) NOT NULL, val1 varchar(1), val2 varchar(1), aendat text, aennam

Re: [GENERAL] Query performance

Hi, can you post the complete query,schema- and table-definition,server-version etc. ? This will help to identity the main problem. So at the moment i'm just guessing: Hash Cond: (("outer".snp_id)::text = ("inner".snp_id)::text) -> Bitmap Heap Scan on test2 (cost=232.92..132766.66 rows=3

Re: [GENERAL] Query performance

Hi, the complete query is the one i posted, but here comes the schema for mytable: entry_no int8 NOT NULL DEFAULT nextval('entry_no_seq''::regclass), pid varchar(15) NOT NULL, crit varchar(13) NOT NULL, val1 varchar(1), val2 varchar(1), aendat text, aennam varchar(8), CONSTRAINT "PK

Re: [GENERAL] Tape backup, 2 versions, same database name, which

Thomas F. O'Connell wrote: On Aug 2, 2006, at 4:27 PM, [EMAIL PROTECTED] wrote: You're correct, I cannot use the pg_dump. I get a error message that the pg_dump is aborting because of a version mismatch, then it says to use the i opt. How do I call the pg_dump from the 8.1.4 version? You'l

[GENERAL] TSearch: Need debug help

SELECT ts_debug('durst'); (default_german,lword,"Latin word",durst,"{de_ispell,de}","'dur' 'sen'") SELECT ts_debug('höchsten'); (default_german,word,Word,höchsten,"{de_ispell,de}","'sen' 'höch' 'höchst' 'höchsten'") For some reason both produce the lexem 'sen'. That leads to strange results.

[GENERAL] Query performance

Hi everyone, i have a table with around 57 million tuples, with the following columns: pid(varchar), crit(varchar), val1(varchar), val2(varchar). Example: pidcritval1val2 p1 c1 xy p1 c2 xz p1 c3 yx ... What i am doing is to query a

Re: [GENERAL] Query performance

On 8/3/06, Christian Rengstl <[EMAIL PROTECTED]> wrote: ... Unfortunately the query takes pretty long for the big table, so maybe one of you has a suggestion on how to make it faster. try smth like this: select val1, val2, crit from mytable as a where pid='somepid' and exists(select 1 from m

Re: [GENERAL] SELinux + CREATE TABLESPACE = ?

If you rather keep SELinux on, you can still set the SELinux context on the directory where you want the tablespaces to one postgres will like. To find what is the permissions you need, you can use ls -Z. It will list the SELinux context. Check /var/lib/pgsql/data (or wherever postgres data is po