[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 because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
- Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60
rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
- Seq Scan on stat_views (cost=1.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
--
Best regards,
Uros mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 Tel: 972-4-6991122 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Optimizing query
Do something like:
CREATE OR REPLACE FUNCTION my_date_part( timestamp)
RETURNS DOUBLE precision AS 'DECLAREmydate ALIAS FOR
$1;BEGINreturn date_part( ''day'', mydate );END;' LANGUAGE
'plpgsql' IMMUTABLE;
create index idx_tmp on stat_views( my_date_part( created) );
or add an extra date_part column to your table
which pre-calculates date_part('day',
created) and put an index on this.
Cheers
Matthew
--
- Original Message -
From:
Uros
To: [EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 10:41
AM
Subject: [GENERAL] Optimizing query
Hello!I have some trouble getting good results from my
query.here is
structurestat_viewsid |
integerid_zone | integercreated |
timestampI have btree index on created and also id and there
is 1633832 records inthat tableFirst of all I have to
manualy set seq_scan to OFF because I always getseq_scan. When i set it to
off my explain show:explain SELECT count(*) as views FROM stat_views
WHERE id =
12;
QUERY
PLANAggregate
(cost=122734.86..122734.86 rows=1 width=0) - Index
Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60
rows=40904 width=0) Index
Cond: (id = 12)But what I need is to count views for some day, so I
useexplain SELECT count(*) as views FROM stat_views WHERE
date_part('day', created) =
18;
QUERY
PLANAggregate
(cost=100101618.08..100101618.08 rows=1 width=0) -
Seq Scan on stat_views (cost=1.00..100101565.62 rows=20984
width=0) Filter:
(date_part('day'::text, created) = 18::double precision)How can I
make this to use index and speed the query. Now it takes about
12seconds. --
Best
regards,Uros
mailto:[EMAIL PROTECTED]---(end
of broadcast)---TIP 1: subscribe and unsubscribe
commands go to [EMAIL PROTECTED]_This
e-mail has been scanned for viruses by MCI's Internet Managed Scanning
Services - powered by MessageLabs. For further information visit http://www.mci.com
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 width=0)
- Seq Scan on stat_views (cost=1.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
Create an index on date_part('day', created). In 7.3 and earlier you need
to create a wrapper function and index that, in 7.4 you can index
arbitrarz expressions directly. The documentation contains more
information about that.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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 to OFF because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
- Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60
rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
- Seq Scan on stat_views (cost=1.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
Can you post explain analyze for the same?
Shridhar
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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.
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 because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
- Index Scan using stat_views_id_idx on stat_views
(cost=0.00..122632.60 rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) =
18;
QUERY PLAN
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
- Seq Scan on stat_views (cost=1.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
SD Can you post explain analyze for the same?
SD Shridhar
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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 the opposite to be the case. It is our replication product that is going to allow PITR, even from 7.3. Sincerely, Joshua Drake For example the Sybase Replication is implemented in this way. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 SQL END DECLARE SECTION;
But, when I do: ecpg -I/include_file_path test.pgc,
I get: test.pgc:7: ERROR: invalid datatype 'struct_cursor'
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
bigest).
How can I resolve this?
Thanks.
CG
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
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.suse.com/us/private/download/ftp/int_mirrors.html http://www.suse.com/us/private/download/ftp/germ_mirrors.html -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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 unsubscribe commands go to [EMAIL PROTECTED]
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 making a fool of yourself, you're creating actual technical problems. I take offense at it because for years you've simply ignored all requests to do something about it. But I'm certainly not the only one who considers it odd. And you think this kind of infight about file naming on the general mailing list is making us look good? Think again ... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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. SELECT i_auto_id, v_name, f_price, i_year FROM v_auto_detail_seller; takes time 2 sec. but this time is not good for me. I need time max. around 0,2 sec. I must do this: SELECT * INTO t_auto FROM from v_auto_detail_seller; then queries on t_auto are very fast; But I must provide reference integrity by triggers. sometimes I need lock entire database for access, because this: BEGIN WORK; DROP t_auto; SELECT * INTO t_auto FROM from v_auto_detail_seller; COMMIT WORK; May I provide exlusive access to database??? Or exists some other solutions for my problem??? :) sorry for english... thanx, miso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
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 it lear about your struct definition? You can of course use a EXEC SQL TYPE command to define it for ECPG as well. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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 previously been set for naming RPM releases (regardless of the source). And then you neglected to put group write permissions on the directory so that other binaries could be uploaded. So now I wouldn't be able to upload RPMs in the customary place. Many thanks. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[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 are not allowed for user names. But a »createuser www-data« is working correct an I can access the database with that user. I tried to quote the www-data with 'www-data', www\-data with the same result. Any hints? I'm using 7.3.2 Thanks and best regards Herbie -- Herbert Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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 approaches.
1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list. This works, but you have to
do some massive updating when you what to insert a rule between i.e 12 and
13.
CREATE TABLE FW_CHAIN (
FW_CHAIN_ID INT4 not null,
PARENT_IDINT4 null,
fields ..
constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);
2) Use a Parent_Id, as described intended in the table above. I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries. As I see it
some kind of recursive select statement is needed. Cursors ? Stored
Procedure?
If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.
--
Johan Fredrik Øhman
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
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 tuning can get you orders of magnitude. I tunned my querys and all uses indexes and seems to be fast, but when my client app acccess the pg data it seems a little slow. I installed MSDE (mssql limited version) and copied the same data from pg to MSDE, I was surprised cuz running the same query with the same data and MSDE seems to be a little bit faster, after the changes in postgresql.conf described in the previus message pg perfomance increased a little but still there is no big difference against MSDE, considering hardware, pg is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh with 256RAM y should expect better performace from pg. I wonder if ODBC could be affect performance so high, my current ODBC driver is 7.03.02. Thanks -- Josu Maldonado. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 SQL blocks, so how else should it lear about your struct definition? You can of course use a EXEC SQL TYPE command to define it for ECPG as well. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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] On Behalf Of Rob Sell Sent: Wednesday, November 19, 2003 8:05 AM To: [EMAIL PROTECTED] Subject: [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 unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 again.. Shridhar ---(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
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 - at character 64 Seems to me that hyphens are not allowed for user names. But a ?createuser www-data? is working correct an I can access the database with that user. I tried to quote the www-data with 'www-data', www\-data with the same result. Any hints? I'm using 7.3.2 Double-quotes: test= GRANT SELECT, UPDATE, INSERT, DELETE ON test TO www-data; -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 PATH that is looking at the old binaries. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] CASE tools
FYI I have found 2 CASE tools that support PostgreSQL CASE Studio 2 http://www.casestudio.com/enu/products.aspx DDS-Lite http://www.dds-lite.com/ However, they are both for Windows. Can you point me to others? Hopefully multi-platform ones. I like this one a lot -- it's written in Java and it's open source: Druid, The Database Manager http://sourceforge.net/projects/druid/ -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 libpq has great support for large objects, and you can store large object without actually storing them on server filesystem, so You do not need any file permissions on upload directory or something like that. The pictures are prepared for web. Storing in files is faster from that side. That system is calling db every 30 mins... [sNip] Has anyone done any benchmarks on this to know what the performance differences are for downloads (I'm not concerned about uploads since they're far less frequent) from a web server such as Apache HTTPd accessing the file system directly vs. a CGI script/program sending it dynamically through Apache HTTPd? -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 99% utilization (which is great!). Is this normal for RAM utilization? It depends on what you have set shared_buffers to in your postgresql.conf file. If you have not tuned this value yet you should do so as the default setting is very conservative. There is a lot of discussion on appropriate shared_buffers settings if you search the mailing list archives... -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[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 approaches.
1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list. This works, but you have to
do some massive updating when you what to insert a rule between i.e 12 and
13.
CREATE TABLE FW_CHAIN (
FW_CHAIN_ID INT4 not null,
PARENT_IDINT4 null,
fields ..
constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);
2) Use a Parent_Id, as described intended in the table above. I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries. As I see it
some kind of recursive select statement is needed. Cursors ? Stored
Procedure?
If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.
--
Johan Fredrik Øhman
---(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
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 used for the dynamic generation of web pages (and possibly other types of documents for any other systems which are able to embed PHP on the server-side in a similar fashion to Dynamic HTML in the way Apache HTTPd does). You may have more success if you search on PERL or other languages. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[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 PERFORM and EXECUTE; no good. Thanks Regards, Brian H Mayo [EMAIL PROTECTED]
[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 Presicient Corp. www.presicient.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 Depends on what test.h contains. ecpg has to learn the definition of your struct somewhere. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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, distributors build packages for their distributions and their customers, so making them similar to other spec files is just a secondary effect. So Red Hat's use of the same is just a 'coincidence'. Ok. My effort has been expended not in directly building for every distribution, but for providing a starting point that the distributions can use and modify to their heart's content. By keeping the PGDG set in that role, the various distributions have a common starting point, so at least postgresql works pretty much the same way across distributions. True, but you're under the misimpression that distributors always use your set and then add on their things. But development also flows the other way. So at any one point, one set is never a subset of the other. So there is no hierarchy. No, I'm not under any such misimpression. And the set is 'our' set, not just mine, since the group has thus far allowed the use of the postgresql.org server for distribution. I do not see the RPMs as just being 'mine' -- they are the community's. By having the PostgreSQL Global Development Group's name, download site, and support behind this set means that they are consisdered 'upstream' and the current feel, at least in the Red Hat niche, is to use upstream whenever possible, and to refer bugs, patches, etc back upstream whenever practical. In this particular case, Red Hat employs upstream developers (which is a common thing for Red Hat to do, as they employ many upstream developers in many projects). They do not empoy me; I volunteer my time. But, as I said in another post, if the community is of the consensus that having the upstream RPM set is not a good thing, then I have no problem letting the distributors do their own thing. I just want to make things easier for the users. As to development flowing multiple directions, it's called cooperation. Thus far, most distributors have chosen to use things from our set, and I have chosen to use things that were useful from their sets. Would the same things happen at the same level if our set did not exist? This started in 1999 during the release cycle for Red Hat 6.1, when they chose to use the exact same set I was working on at the time. The exact set I had built was distributed on the Red Hat CD for 6.1. Was it built on others work? Sure it was. Did it use good ideas from other people? I am not a NIHilist, so it certainly did. Was it 'official' in any way? Once I was allowed by PGDG to upload it to the ftp.postgresql.org site, it became, in the view of the PostgreSQL group, 'official' for the group. Have I always done the best job? Not necessarily. Have the distributors' RPMs differed from ours? Yes, their needs and our needs differ. Have they synchronized to our set periodically? Yes, they have. Do they call our set the 'official' set? Yes, they do. Why do you have a problem with this? The directory structure is a mirror of the SuSE FTP site. On ftp.postgresql.org? I'm only talking about ftp.postgresql.org. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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 Liechti http://www.thinx.ch The content management company. Visit http://www.contentx.ch ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 gracefully. I don't see why this would be different on other Operating Systems since the code base is essentially the same (isn't it?). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[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 owner of this view, is any more in the database but if I do select from table it works fine. Is this a bug? Jose ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
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 Postgresql shop. - PostgreSQL supports constraints. MySQL doesn't; programmers need to take care of that from the client side Wow. That's so rediculous that I don't want to believe it because this feature is just so basic. - Define a 32-bit field in MySQL. Insert a 64-bit number instead. Common sense tells you the value would be rejected. Yet MySQL happily folds it in and carries on its merry way. That's unacceptable. To me, this is a complete show-stopper because I simply won't tolerate data loss due to an idiotic design flaw. - Triggers: PostgreSQL yes, MySQL no. Translates into more work for your MySQL developers in both creating your app and moving it forward with each rev. There are also circumstances where PostgreSQL will create implicit triggers, which means to me that MySQL must be lacking in some other features as a result of not supporting this. - Transactions: We've been here before. Suffice to say, MySQL+InnoDB is almost there. Plain ol' MySQL doesn't have it, which tells you something about their philosophy towards database design. It also indicates that Transactions are new to this product, and so one may be better off with a more experienced group of developers who've already earned their battle scars as is obviously the case with PostgreSQL. - Speed: mHz for mHz, MySQL has PostgreSQL beat for simple searches. Once you start getting complex, PostgreSQL is competitive. I think this speed issue is overrated: over time, PostgreSQL has sped up and MySQL has slowed down which is pretty impressive, considering both have added features from their early versions. Do you know of any published benchmarks for this? I need to convince some people who are hell-bent on MySQL being fast for everything that they're mis-informed, and they refuse to take anyone's word for it. - Scalability: MySQL dies before PostgreSQL does. PostgreSQL under extreme load may slow down, but it'll finish. MySQL simply gives up. [sNip] I've experienced this very problem with MySQL actually. It seems that Apache James (an open source Java-based SMTP/POP3 mail server) running on FreeBSD will trigger this problem very quickly as soon as multiple users attempt to send large (greater than 10 MBs) file attachments -- perhaps JDBC is part of the problem, but in the Apache James error logs there is indication of MySQL connectivity problems (also during busy times on systems sending approximately 500,000 eMails per day). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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, DevNet SysOp 29 wrote: 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 used for the dynamic generation of web pages (and possibly other types of documents for any other systems which are able to embed PHP on the server-side in a similar fashion to Dynamic HTML in the way Apache HTTPd does). You may have more success if you search on PERL or other languages. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 for large scale applications (and even some small ones that will use a table in many different ways). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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
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 answer from the company, and it should be treated as such. If you think an employee is spreading mis-information, then you should contact the company directly and ask for further clarification with a short explanation of your doubts about the information you were provided. Making insults to discredit someone because you don't like their official response due to their job title is a childish tactic that doesn't help anyone. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 someone claim that mailman version 2.1 was supposed to allow you to use your own database. ezmlm-idx has support for mysql and so may not be too hard to get to work with postgres. It does require qmail for an MTA. Bruce Guenter has just volunteered to take over maintainance from Fred Lindberg so the project should start seeing some activity again. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1= \d ERROR: current transaction is aborted, queries ignored until end of transaction block template1= abort; ROLLBACK template1= \d ERROR: nodeRead: did not find '}' at end of plan node Every command that tries to access a table gives this error... pg_dumpall says: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: nodeRead: did not find '}' at end of plan node pg_dump: The command was: select (select usename from pg_user where usesysid = datdba) as dba, encoding, datpath from pg_database where datname = 'cinema' pg_dumpall: pg_dump failed on cinema, exiting What can I do ? (postgresql 7.3.4 on a gentoo linux box, with psql from the same package) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 how they are ordered. As I see it there are at
least 2 approaches.
1) Use a Sequence number in the table. This way it is possible to use ORDER
BY sequencenumber to retrive the correct list. This works, but you have to
do some massive updating when you what to insert a rule between i.e 12 and
13.
CREATE TABLE FW_CHAIN (
FW_CHAIN_ID INT4 not null,
PARENT_IDINT4 null,
fields ..
constraint PK_FW_CHAIN primary key (FW_CHAIN_ID)
);
2) Use a Parent_Id, as described intended in the table above. I feel that
this is a little prettier, even if it might be somewhat less efficient.
Unfortunately, I'm really not sure how to perform the queries. As I see it
some kind of recursive select statement is needed. Cursors ? Stored
Procedure?
If somebody has the time, I'd really appreciate some links to relevant
examples, documentation or advice on how to solve this.
--
Johan Fredrik Øhman
Very interesting idea. I build Linux firewalls with netfilter as well and I
never considered storing the rules in a database since as routers I keep them
pretty closed.
However, if I were to do this, I would use a non-integer field that would
contain the rule number (i.e. the place in the chain). When you insert the rule
you could simply take the mid-point between the two rules. So for example to
insert between rule 10 and 11 you would use 10.5. That would work for some time
but would become unreadable if you have a lot of updates happening (i.e. your
intrustion detection system automatically writes rules). To handle that, you
could cron a job to renumber the rules with whole numbers so again:
current rule order:
10.0
11.0
12.0
after 1st update
10.0
10.5
11.0
12.0
after second update
10.0
10.5
11.0
11.5
12.0
after 3rd update
10.0
10.25
10.5
11.0
11.5
12.0
after a renumbering
10
11
12
13
14
15
You'll want to vacuum after that renumbering as well.
--
Keith C. Perry, MS E.E.
Director of Networks Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 Novell NetWare 6 it shut down gracefully. I don't see why this would be different on other Operating Systems since the code base is essentially the same (isn't it?). No it's not. Don't confuse the PostgreSQL code base with the operating system it's running on. On Mac OS X (desktop version, at least) there are no shutdown scripts. All running applications are simply sent the TERM signal, then later sent the KILL signal. Luckily enough, PostgreSQL seems to respond to TERM by shutting down gracefully. Totally off topic, but this lack of shutdown scripts, along with a lack of proper package management are the two most painful faults in Mac OS X. Alex ---(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
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 might get you a few percent performance once you pick a reasonable value; query tuning can get you orders of magnitude. I tunned my querys and all uses indexes and seems to be fast, but when my client app acccess the pg data it seems a little slow. I installed MSDE (mssql limited version) and copied the same data from pg to MSDE, I was surprised cuz running the same query with the same data and MSDE seems to be a little bit faster, after the changes in postgresql.conf described in the previus message pg perfomance increased a little but still there is no big difference against MSDE, considering hardware, pg is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh with 256RAM y should expect better performace from pg. I wonder if ODBC could be affect performance so high, my current ODBC driver is 7.03.02. Thanks I have never used ODBC so I don't know how much that will effect performance. What are the schemata for the tables, and what does explain analyze tell you about the query? Stephen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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(epr_qty2,0)::numeric(12,4) as epr_qty2,
coalesce(epr_qty3,0)::numeric(12,4) as epr_qty3,
case when epr_qty=0 then 0 else
coalesce((epr_costo/epr_qty),0)::numeric(12,4)
end as cost_prom,
case when epr_qty=0 then 0 else
coalesce((epr_venta/epr_qty),0)::numeric(12,4)
end as prec_prom,
case when epr_costo=0 then 0 else
coalesceepr_venta/epr_costo)*100)-100),0)::numeric(12,4)
end as margen
FROM estprod WHERE pro_code = '1017' and epr_periodo = '200211' and
epr_periodo = '200311'
This is the explain:
Index Scan using idx_estx on estprod (cost=0.00..38.29 rows=9 width=67)
(actual time=0.52..1.64 rows=13 loops=1)
Index Cond: ((pro_code = '1017'::bpchar) AND (epr_periodo =
'200211'::bpchar) AND (epr_periodo = '200311'::bpchar))
Total runtime: 1.70 msec
(3 rows)
Table estprod is:
CREATE TABLE public.estprod
(
pk_estprod int4 DEFAULT nextval('sqestprod'::text),
product_fk int4,
epr_periodo char(6),
epr_venta numeric(12,4),
epr_costo numeric(12,4),
epr_qty numeric(12,4),
epr_venta2 numeric(12,4),
epr_costo2 numeric(12,4),
epr_qty2 numeric(12,4),
epr_venta3 numeric(12,4),
epr_costo3 numeric(12,4),
epr_qty3 numeric(12,4),
epr_ventax numeric(12,2),
pro_code char(4),
xmes varchar(6),
imes int4
) WITH OIDS;
and it contains 355,513 rows
Stephen Robert Norris wrote:
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 might get you a few percent
performance once you pick a reasonable value; query tuning can get you
orders of magnitude.
I tunned my querys and all uses indexes and seems to be fast, but when
my client app acccess the pg data it seems a little slow. I installed
MSDE (mssql limited version) and copied the same data from pg to MSDE, I
was surprised cuz running the same query with the same data and MSDE
seems to be a little bit faster, after the changes in postgresql.conf
described in the previus message pg perfomance increased a little but
still there is no big difference against MSDE, considering hardware, pg
is in a real server (dual Xeon 2.4 Ghz, 2G RAM, 3 36G SCSI drives on a
RAID5) and the server is not in production, MSDE is in a AMD athlon 1Gh
with 256RAM y should expect better performace from pg. I wonder if ODBC
could be affect performance so high, my current ODBC driver is 7.03.02.
Thanks
I have never used ODBC so I don't know how much that will effect
performance. What are the schemata for the tables, and what does explain
analyze tell you about the query?
Stephen
--
Josu Maldonado.
---(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
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 different combinations to run the function. I tried PERFORM and EXECUTE; no good. You have done CREATE FUNCTION myfunction() ... LANGUAGE 'plpgsql'; Now you can say SELECT myfunction() FROM mytable; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C To show forth thy lovingkindness in the morning, and thy faithfulness every night. Psalms 92:2 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
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 ! Sounds about right ! Something new to learn every day :-) Actually, bytea and text are chunked behind the scenes (the technique known as TOAST). They are also compressed(LZ) by default. However if you anticipate substringing them a lot (or if they are not very compressible anyway), use ALTER TABLE xxx ALTER COLUMN yyy SET STORAGE EXTERNAL to turn off the automatc compression of values. This allows the substr operation to read the minimum number of chunks necessary of disk. Bear in mind of course, that the whole value will take up more disk space, so reading it in its entirety will be slower (IO is normally the limiting performance factor -CPU on a DB server is often cheap by comparison, so decompressing/compressing to save IO is a good idea). If however you always fetch small parts (e.g. you store large images and usually want to read the header info from them, EXTERNAL is a good bet (and depending on the image format, the compression might not compress them very much anyway). Finally, note that the substr optimisation for text only really buys you anything if the character-set is single-byte. Hope this helps John Gray (implementer of substr optimisation many moons ago!) ---(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
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 with this? If you manage to build RPMs for several operating systems out of one source RPM, great job. But the above still applies. The directory structure is a mirror of the SuSE FTP site. On ftp.postgresql.org? I'm only talking about ftp.postgresql.org. Yes. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Oliver Elphick Sent: Wednesday, November 19, 2003 6:22 PM To: Brian H Mayo Cc: PostgreSQL general list Subject: 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 different combinations to run the function. I tried PERFORM and EXECUTE; no good. You have done CREATE FUNCTION myfunction() ... LANGUAGE 'plpgsql'; Now you can say SELECT myfunction() FROM mytable; -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C To show forth thy lovingkindness in the morning, and thy faithfulness every night. Psalms 92:2 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 GCC 2.96 (1 row) rapidb=# create table test (x int primary key, y int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test' CREATE TABLE rapidb=# create view test_proxy as select * from test; CREATE VIEW rapidb=# create rule new_test_proxy as on insert to test_proxy do delete from test where x=new.x; CREATE RULE rapidb=# create rule new_test as on insert to test_proxy do instead insert into test values (new.x, new.y); CREATE RULE rapidb=# insert into test_proxy values (1,1); INSERT 663399483 1 rapidb=# select * from test; x | y ---+--- (0 rows) I create a table test, and a view test_proxy, then it create two on insert rules on test proxy - first rule deletes the row with the same PK as the one being inserted from test (so that I don't need to check for it before hand if I want to replace the row), the second - INSTEAD rule just does the insert on the actual table. The problem is that the new row seems to NEVER get inserted - the last two commands try to insert a row into test_proxy, and then look at it - the table is empty! This used to work in 7.2: rapidb=# select version(); version - PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) rapidb=# create table test (x int, y int); CREATE rapidb=# create view test_proxy as select * from test; CREATE rapidb=# create rule new_test as on insert to test_proxy do instead insert into test values (new.x, new.y); CREATE rapidb=# create rule new_test_proxy as on insert to test_proxy do delete from test where x=new.x; CREATE rapidb=# insert into test_proxy values (1,1); INSERT 0 0 rapidb=# select * from test; x | y ---+--- 1 | 1 (1 row) Does anyone have any idea what is going on here? I suspect, my problem is that the rules get executed in the wrong order - so that a row gets inserted first, and then deleted right away... Is that right? If so, was this change from 7.2.4 done intentionally, or is it a bug? If the former, is there any way (a config option or something) to get the old behaviour back? Thanks a lot for your help! Dima ---(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] 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
