[GENERAL] Two rules on a view do not like each other :-(

Hi, everybody! Here is a weird problem I ran into with 7.3.4. This is the complete test case: rapidb=# select version (); version - PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GC

Re: [GENERAL] A newbie question

And in fact you can even do: SELECT myfunction() if the function does not rely on data/parameters from a table, which would be the case if there are no parameters at all to myfunction() Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTE

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

Lamar Owen writes: > Why do you have a problem with this? Development technicalities aside, when people go to the FTP site in search for binaries, they primarily search for binaries for their operating system. So the operating system becomes the top directory hierarchy. Why do you have a problem

Re: [GENERAL] uploading files

On Tue, 18 Nov 2003 21:22:56 +0100, Karsten Hilbert wrote: >> I think the field will still be competely loaded into memory on the >> server side though, while LOs are stored in "chunks" and can >> theoretically be streamed to the client. I'm not really a definitive >> authority, though... > Ah !

Re: [GENERAL] A newbie question

On Wed, 2003-11-19 at 02:43, Brian H Mayo wrote: > I am a newbie to PostgreSQL. I added plpgsql to the database and > wrote a function in the plpgsql language. How do I execute it? > > I read all of the manuals but I cannot seem to figure it out. I > pulled up the pgadmin III SQL and tried diffe

Re: [GENERAL] Tunning postgresql

Stephen, This is the query code: SELECT (cmes(substr(epr_periodo,5,2))||'-'||substr(epr_periodo,3,2))::char(6) AS hmes, epr_periodo, coalesce(epr_venta,0)::numeric(12,4) as epr_venta, coalesce(epr_costo,0)::numeric(12,4) as epr_costo, coalesce(epr_qty,0)::numeric(12,4) as epr_qty, coalesce(e

Re: [GENERAL] Tunning postgresql

On Thu, 2003-11-20 at 02:06, Josuà Maldonado wrote: > Hi Stephen, > > Stephen Robert Norris wrote: > > > > > > Speaking from long experimentation, you're much, much better off making > > sure your indices and queries are optimal that messing around with > > buffer space. Buffer space tuning migh

Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

Randolf Richardson, DevNet SysOp 29 wrote: [sNip] Do a shutdown -h on a live database machine with pg. It will gracefully shut itself down. Is that true for all OS flavors and is it dependent upon the DBA having set up proper shutdown scripts? [sNip] When I tested this on PostgreSQL on Novel

Re: [GENERAL] Storing a chain

Quoting Johan Fredrik Øhman <[EMAIL PROTECTED]>: > Hi, > I'm seeking some opinion on the following problem > > For those familiar with "iptables/netfilter", I am actually storing firewall > rules in the database. The order of these rules is critical, so there has to > be some kind of system to ho

[GENERAL] with(isstrict) vs ISSTRICT

Does with(isStrict) still work ? If not when did postgres drop its support ? TIA --strk; ---(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] ERROR: nodeRead: did not find '}'

Hello, it seems my postgresql data has somehow become corrupted (by a forced shutdown I think): psql template1 -U shadow Password: ERROR: nodeRead: did not find '}' at end of plan node Welcome to psql 7.3.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \

Re: [GENERAL] Good open source mailing list system PHP / Postgresql

> > An ISP I belong to uses Majordomo for their mailing list system. I'd like > > to encourage them to move to a system that uses a database, preferably > > psql which they already run on their server. Anything out there in Php? Some possible starting points are mailman and ezmlm-idx. I saw some

Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

>>Here's an interesting response from mysql.com sales. Frankly, I don't see >>how using it on multiple internal servers violates the GPL?!?: > > You're talking to a sales droid, a suit, someone whose brain > cells have died off because his tie was tied to tight. [sNip] That's an official

Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

[sNip] > "We have all the features they do! Nobody uses views or triggers!" Which cave has that person been hiding in all these years? Views are a very important part of SQL, and any SQL server that doesn't support Views is, in my view (sorry, I couldn't resist), simply isn't suitable

Re: [GENERAL] Good open source mailing list system PHP / Postgresql

Check Majordomo2, which is what we use for the lists ... it has the ability to use database backends now, at least for MySQL, and I know a good portion of code is in place for PostgreSQL also (just not sure to what extent, haven't braved it yet) ... On Wed, 19 Nov 2003, Randolf Richardson, DevNe

Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

Thanks for this information, it's very helpful. I've included some additional comments to further demonstrate how a qualified business planner may look at this... >> I'm preparing to enter a discussion with management at my company >> regarding going forward as either a MySql shop or a

[GENERAL] pg_aclcheck: invalid user id #

Hi, I found a strange behavior in PostgreSQL. I created a table as user pippo then I created a view based upon this tables, latter someone drops user pippo and now when I do: select from view PostgreSQL show me this: pg_aclcheck: invalid user id # I suppose it is because the user pippo the o

Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)

[sNip] >> Do a shutdown -h on a live database machine with pg. It will gracefully >> shut itself down. > > Is that true for all OS flavors and is it dependent upon the DBA having > set up proper shutdown scripts? [sNip] When I tested this on PostgreSQL on Novell NetWare 6 it shut down gr

Re: [GENERAL] Storing a chain

Am Mittwoch, 19. November 2003 16.08 schrieben Sie: > Hi, > I'm seeking some opinion on the following problem May be this article is interesting for you: http://www.dbmsmag.com/9603d06.html Best regards Herbie -- Herbert Liec

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

On Wednesday 19 November 2003 02:11 pm, Peter Eisentraut wrote: > "Official" is in the eye of the beholder. If it's on a SuSE CD, then it's > official. Everything else is just a series of coincidences. You call > yours "official", so the SuSE spec file refers to them as such. But in > fact, dis

[GENERAL] Japanese and PostgreSQL

Hello There , I hope u answer these questions: I have installed postgresql DBMS , with SQL_ASCII encoding, so far, it is working ok, I think I might have probelems when manipulating the strings(substring, lenght), but now I don't do i. Could anyone tell me , with reasons, the best encoding? Also I

Re: [GENERAL] Problem with exec sql include

On Wed, Nov 19, 2003 at 12:26:07PM -0300, Carmen Gloria Sepulveda Dedes wrote: > In the test.pgc file, I do: > >EXEC SQL BEGIN DECLARE SECTION; > exec sql include test; > struct_cursor t_cursor; >EXEC SQL END DECLARE SECTION; > > ... and it works fine. It is correct

[GENERAL] Seeking concise PL/pgSQL syntax diagram

I checked the 7.4 PL/pgSQL docs but couldn't find a concise grammar description e.g. a BNF diagram...is any such thing available online ? I'm looking to possibly port a stored procedure debugger I'm writing to support Pg. (I'd prefer *not* to wade thru a lex/yacc definition) TIA, Dean Arnold Presi

[GENERAL] A newbie question

I am a newbie to PostgreSQL.  I added plpgsql to the database and wrote a function in the plpgsql language.  How do I execute it? I read all of the manuals but I cannot seem to figure it out.  I pulled up the pgadmin III SQL and tried different combinations to run the function.  I tried PE

Re: [GENERAL] Good open source mailing list system PHP / Postgresql

> An ISP I belong to uses Majordomo for their mailing list system. I'd like > to encourage them to move to a system that uses a database, preferably > psql which they already run on their server. Anything out there in Php? I doubt there's anything in PHP since PHP is a language purely us

[GENERAL] Storing a chain

Hi, I'm seeking some opinion on the following problem For those familiar with "iptables/netfilter", I am actually storing firewall rules in the database. The order of these rules is critical, so there has to be some kind of system to how they are ordered. As I see it there are at least 2 approache

Re: [GENERAL] Better Unilization of Memory

Doug, Thanks... Looks like I need to do some tuning! Lynn Quoting Doug McNaught <[EMAIL PROTECTED]>: > [EMAIL PROTECTED] writes: > > > In looking at top, I have noticed when doing some > > long queries that RAM memory is only being used > > at 1-3%. While, CPU often jumps to 97, 98, even > >

Re: [GENERAL] Picture with Postgres and Delphi

>> There is no need for manually storing files on filesystem, because >> large objects are doing that for You. I am storing whole binary files >> in blobs(synonym for large objects from some other platforms), and I do >> not remember that I had a single problem with that. Do not forget that >> libp

Re: [GENERAL] CASE tools

> FYI I have found 2 CASE tools that support PostgreSQL > > CASE Studio 2 > > > DDS-Lite > > > However, they are both for Windows. > > Can you point me to others? Hopefully multi-platform ones. I like this one a l

Re: [GENERAL] Oracle and regular expressions ???

> New features in version 10g (commin soon) : > > SQL Regular Expressions > ... Hmm, I wonder if they've been influenced by PostgreSQL? ;-D -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer & Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

Lamar Owen writes: > In the case of FreeBSD, isn't it the preference to use the ports system? The preference is to use the ports system once and then use the resulting packages the subsequent times. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)-

Re: [GENERAL] 7.4 broke psql

Rob Sell wrote: > I hate replying to my own posts but its not broken for everyone, just > regular users. It works for postgres and root, but not for my user "robs" > robs is a database super user... weird I guess but I can live with it... If it works for some users and not others, it must be his P

Re: [GENERAL] Wanted: Trick for granting permissions to users with hyphens

Herbert Liechti wrote: > Hello > > I like to grant some permissions to the user www-data (default user > for apache under debian) > > GRANT SELECT, UPDATE, INSERT, DELETE ON customer TO herbie, www-data; > > The statements throws the following error: > ERROR: parser: parse error at or near "-"

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

Jan Wieck wrote: Peter Eisentraut wrote: SuSE RPMs for PostgreSQL 7.4 are available at ftp://ftp.postgresql.org/pub/binary/v7.4/suse or a mirror http://www.postgresql.org/mirrors-www.html or at ftp://ftp.suse.com/pub/people/max/postgresql-7.4 Isn't there a "v" missing here? Not

Re: [GENERAL] 7.4 broke psql

I hate replying to my own posts but its not broken for everyone, just regular users. It works for postgres and root, but not for my user "robs" robs is a database super user... weird I guess but I can live with it... Rob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

Re: [GENERAL] Problem with exec sql include

Thanks for you suggestion. In the test.pgc file, I do: EXEC SQL BEGIN DECLARE SECTION; exec sql include test; struct_cursor t_cursor; EXEC SQL END DECLARE SECTION; ... and it works fine. It is correct Thank you again. CG > ECPG does not parse anything outside EXEC

Re: [GENERAL] Tunning postgresql

Hi Stephen, Stephen Robert Norris wrote: Speaking from long experimentation, you're much, much better off making sure your indices and queries are optimal that messing around with buffer space. Buffer space tuning might get you a few percent performance once you pick a reasonable value; query tu

[GENERAL] Storing a chain

Hi, I'm seeking some opinion on the following problem For those familiar with "iptables/netfilter", I am actually storing firewall rules in the database. The order of these rules is critical, so there has to be some kind of system to how they are ordered. As I see it there are at least 2 approache

[GENERAL] Wanted: Trick for granting permissions to users with hyphens

Hello I like to grant some permissions to the user www-data (default user for apache under debian) GRANT SELECT, UPDATE, INSERT, DELETE ON customer TO herbie, www-data; The statements throws the following error: ERROR: parser: parse error at or near "-" at character 64 Seems to me that hyphens

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

On Monday 17 November 2003 05:18 pm, Peter Eisentraut wrote: > SuSE RPMs for PostgreSQL 7.4 are available at > ftp://ftp.postgresql.org/pub/binary/v7.4/suse Hey, Peter, for one who consistently complains about lack of consistency in naming, you completely diregarded the precedent that has p

Re: [GENERAL] Problem with exec sql include

On Wed, Nov 19, 2003 at 11:09:58AM -0300, Carmen Gloria Sepulveda Dedes wrote: > I know that I can include the typedef at DECLARE SECTION, but I don't > want to use that (test.h and test.pgc are only examples of the problem ECPG does not parse anything outside EXEC SQL blocks, so how else should i

[GENERAL] exclusive access

Hi, I develop web application. My case model consists of many tables. I have VIEW through the 5 tables and 4 JOINs. first table has 14000 rows, second 42000 rows and others have around 20 rows. Time of query depends on WHERE condition. SELECT COUNT(*) FROM v_auto_detail_seller; takes time 1 sec.

Re: [GENERAL] PostgreSQL v7.4 Released

Peter Eisentraut wrote: Marc G. Fournier writes: Odd that you are the only one that *ever* seems to take offence at it ... *shrug* Here it's just the release announcements that make us look like some kind of weirdos. But when you're making tarballs like erserver_v1.2.tar.gz then you're not only m

[GENERAL] 7.4 broke psql

Greetings all, Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the following error. psql: relocation error: psql: undefined symbol: get_progname Any ideas out there? Rob ---(end of broadcast)--- TIP 1: subscribe and unsubscr

Re: [GENERAL] SuSE RPMs available for PostgreSQL 7.4

Peter Eisentraut wrote: SuSE RPMs for PostgreSQL 7.4 are available at ftp://ftp.postgresql.org/pub/binary/v7.4/suse or a mirror http://www.postgresql.org/mirrors-www.html or at ftp://ftp.suse.com/pub/people/max/postgresql-7.4 Isn't there a "v" missing here? or a mirror http://www

[GENERAL] Problem with exec sql include

Hello. I have a 2 files: test.h and test.pgc. In test.h, I defined: typedef struct { long curid; char curnombre[20]; char curfecha[10]; } struct_cursor; And, in test.pgc, I want to declare: EXEC SQL BEGIN DECLARE SECTION; struct_cursor t_cursor; EXEC SQ

Re: [GENERAL] Point-in-time data recovery - v.7.4

So are you donating your replication work to the community then? Or do we still have to write PITR? Dave On Tue, 2003-11-18 at 19:55, Joshua D. Drake wrote: > > I think the opposit: > > > > Once the PITR is written it can be used for Replication as side > > effect. > > > Command Prompt has found t

Re: [GENERAL] Optimizing query

Greetings all, Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the following error. psql: relocation error: psql: undefined symbol: get_progname Any ideas out there? Rob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shridhar Dait

Re: [GENERAL] Optimizing query

Hello Shridhar, I use Matthew's solution and it works. Query takes only half a second. I didn't know that i can index function to. Thanks Uros Wednesday, November 19, 2003, 1:23:26 PM, you wrote: SD> Uros wrote: >> Hello! >> >> I have some trouble getting good results from my query. >> >> h

Re: [GENERAL] Optimizing query

Uros wrote: Hello! I have some trouble getting good results from my query. here is structure stat_views id| integer id_zone | integer created | timestamp I have btree index on created and also id and there is 1633832 records in that table First of all I have to manualy set seq_scan

Re: [GENERAL] Optimizing query

Uros writes: > explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = > 18; > > QUERY PLAN > > Aggregate (cost=100101618.08..100101618.08 rows=1

Re: [GENERAL] Optimizing query

Do something like:   CREATE OR REPLACE FUNCTION my_date_part( timestamp) RETURNS DOUBLE precision AS 'DECLARE mydate ALIAS FOR $1;BEGIN return date_part( ''day'', mydate );END;' LANGUAGE 'plpgsql' IMMUTABLE; create index idx_tmp on stat_views( my_date_part( created ) ); or add an extra date_p

[GENERAL] defferable update & unique

Hi All Does 7.4 support defferable updates on unique column ? example unique INT colum named u_test has the following values: 1, 2, 3, 4, 5 can i do update table set u_test = u_test + 1; cheers -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager

[GENERAL] Optimizing query

Hello! I have some trouble getting good results from my query. here is structure stat_views id| integer id_zone | integer created | timestamp I have btree index on created and also id and there is 1633832 records in that table First of all I have to manualy set seq_scan to OFF be