Re: [GENERAL] append all columns in where-clause
will trillich <[EMAIL PROTECTED]> writes: > "how can we cast 'varchar' to 'bpchar' or vice-versa?" You just do it: select bpcharfld::varchar ... or whatever. regards, tom lane ---(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] Joined table view - multiple delete action rule
Hi Andrew, I'm sorry but I was a but sloppy in typing. The right version is CREATE TABLE a (x integer PRIMARY KEY,y integer); CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x,z)) CREATE VIEW ab AS SELECT a.x, a.y, b.z FROM a,b WHERE a.x=b.x; /* this -insert- seems to work */ CREATE RULE ab_ins AS ON INSERT TO ab DO INSTEAD ( INSERT INTO a(x,y) VALUES (new.x, new.y); INSERT INTO b(x,z) VALUES (new.x, new.z); ); /* this -delete- does not work */ CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD ( DELETE FROM b WHERE (x=old.x) AND (z=old.z); DELETE FROM a WHERE (x=old.x); ); So I already got the answer: the query rewriter only takes the first action after which the joined row doesn't exist anymore and thus the second delete doesn't affect the tables anymore. I'm now trying to rewrite this as a cascading delete rule on table a; Greetings Lieven ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Joined table view - multiple delete action rule
Lieven Van Acker wrote: > Hi Jan and others, > > thanks for the answer, this clears up the symptom. > > In trying to rewrite the rules, I'm still facing the same problem. > I'll try to simplify the rules and tables (it's emulating the OO concept as > a is the parent and b and c are inherited from a) > > CREATE TABLE a (x integer PRIMARY KEY,y integer); > CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x)); > CREATE TABLE c (x integer REFERENCES a, v integer, PRIMARY KEY(x)); > > CREATE VIEW ab AS > SELECT a.x, a.y, b.z > FROM a,b > WHERE a.x=b.x; > > CREATE VIEW ac AS > SELECT a.x, a.y, c.v > FROM a,c > WHERE a.x=c.x; > > /* this -insert- seems to work */ > > CREATE RULE ab_ins AS ON INSERT TO ab DO INSTEAD ( > INSERT INTO a(x,y) VALUES (new.x, new.y); > INSERT INTO b(x,z) VALUES (new.x, new.z); > ); > > /* cascading delete on a to b and c */ > > CREATE RULE a_del AS ON DELETE TO a DO ( > DELETE FROM b WHERE (x=old.x); > DELETE FROM c WHERE (x=old.x); > ); > > /* delete on view doesn't work */ > > CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD > DELETE FROM a WHERE (x=old.x) > ; > > The last rule seems to have the same effect as the original rule where I > implemented the cascading delete on the delete rule for the ab-view. > So I suppose the query rewriter will end up executing the same sequence of > queries. > > Now, is there a way to implement this delete on the joined view? That's not what I suggested, it's still using rules for the cascaded delete. I meant to setup a FOREIGN KEY constraint with an ON DELETE CASCADE referential action. Add to table "b" and "c" ON DELETE CASCADE after the REFERENCES keyword and leave out the entire a_del rule. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Newbie struggling to set $PGDATA
Geoff Caplan <[EMAIL PROTECTED]> writes: > I have tried setting it in my bash /etc/profile configuration file, > and it shows up ok if I "echo $PGDATA" in the shell. But none of the > postgres utilities such as "initdb" seem to be able to find it. You probably forgot to say "export PGDATA"; without that it's just a local variable in your shell. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] SUM()ming a view's column
On Wed, Apr 25, 2001 at 04:26:14PM +0200, Jan Ploski wrote: > Hello, > > I have run into the following problem: > > workunit=> select * from v_wutest; > estimate > -- > 1650 > 100 > (2 rows) > > workunit=> select sum(estimate) from v_wutest; > sum > -- > 4950 > 100 > (2 rows) > > > > Here, v_wutest is a view which returns two rows. Shouldn't the SUM > aggregate simply add these two values? To me, it looks like a bug. > Isn't it one? > > I am using PostgreSQL 7.0.3 here. I just upgraded to 7.1 release 1, and the problem disappeared. Also, I found a bug in one of my own queries (a missing join condition), but IMHO it should not have led to the anomaly pictured above, anyway. -JPL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] I can not create Index on a feild by type 'timestmp'!
Hasan Mokhtari Sangchi <[EMAIL PROTECTED]> writes: > If I want to create an index on a field by type 'timestamp' > postgres give me following error message : > ' can not find a default operator class for type 1296.' > what can I do ? Upgrade to a more recent version of Postgres. Or, change the field to be type datetime, which is the better-supported datatype in old versions... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Newbie struggling to set $PGDATA
Hello Steve, Thursday, April 19, 2001, 4:53:58 PM, you wrote: SW> I just upgraded from 7.0.3 to 7.1 (using the RH rpms) with no SW> major problems. Nice! SW> However, I'm getting some strange behaviour from the init SW> script (/etc/rc.d/init.d/postgresql). In particular, a SW> restart when the database is running fails because the SW> "sleep 2" in stop() is no longer long enough (on a dual-cpu SW> PIII/650 machine!). So "pidof postmaster" returns a non-null SW> value and *that* results in a syntax error: SW> Checking postgresql installation: [ OK ] SW> ./postgresql: [: 18209: unary operator expected SW> (I think the test should become something like "if [ -n "$pid" ];..." SW> instead of just "if [ $pid ]", which at least prevents the SW> syntax error...) SW> Of course, fixing the syntax error isn't the main problem, which SW> is that the shutdown of the old postmaster(s) hasn't completed yet, SW> resulting in a "false positive" from the pidof operation. SW> I bumped the "sleep 2" up to "sleep 5" and the problem went away. SW> Surely there's a better fix? Hi Please help out a Linx/Postgres newbie. I simply want to set the $PGDATA environmental variable, but can't figure out how. The docs assume you already know... I have tried setting it in my bash /etc/profile configuration file, and it shows up ok if I "echo $PGDATA" in the shell. But none of the postgres utilities such as "initdb" seem to be able to find it. What don't I understand? I have already checked the docs/GreatBridge manual/faqs/archive, so I would very much appreciate some help. Geoff Caplan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Question on Bizarre Sorting (ORDER BY in 7.1)
<[EMAIL PROTECTED]> writes: > I was interested in knowing whether or not this > is a bug, a change in the way PostgreSQL sorts, or possibly some kind of > locale-specific misconfiguration? There is not any (intentional) change in sorting behavior between 7.1 and earlier releases; indeed, since the sort order of text fields is determined by libc's strcmp() or strcoll(), it would be pretty hard for us to change it if we wanted to. My money is on a locale issue ... although the sorting behavior you describe doesn't seem to match any commonly used locale. Things to try: Check whether you built with locale and/or multibyte support (and did you make the same choices before?). Use the contrib/pg_controldata program to see what locale the database is initialized in. Run the regression tests, both "make check" (which should force C locale) and "make runtest" (which will talk to your installed postmaster and hence use whatever locale it's using). I'd not be surprised to get some ordering differences in the runtest results. regards, tom lane ---(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]
On Wed, 25 Apr 2001, Ashley T. Howes wrote: > I was wondering if PostgreSQL takes advantage of multiple CPUs when > placed in a single Linux box. Or are multiple CPUs utilised as long > as the Linux kernel is compiled to take advantage of them? If you compile an SMP kernel Linux will balance processes and threads evenly across the CPUs. One client hitting the database may not see a huge advantage of a multi-processor machine, but the Linux box is also running other processes than the postmaster, even if its the kernel itself. Of course, if two clients hit the box at the same time then each postgres backend process could execute on a different CPU. For a heavily loaded machine I think you would see a measurable difference in using an multi-proc box. -M@ ---(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] SUM()ming a view's column
Jan Ploski <[EMAIL PROTECTED]> writes: > Here, v_wutest is a view which returns two rows. Shouldn't the SUM > aggregate simply add these two values? To me, it looks like a bug. Does the view contain GROUP BY? If so, yes this is broken in pre-7.1 releases ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Joined table view - multiple delete action rule
Hi Jan and others, thanks for the answer, this clears up the symptom. In trying to rewrite the rules, I'm still facing the same problem. I'll try to simplify the rules and tables (it's emulating the OO concept as a is the parent and b and c are inherited from a) CREATE TABLE a (x integer PRIMARY KEY,y integer); CREATE TABLE b (x integer REFERENCES a, z integer, PRIMARY KEY (x)); CREATE TABLE c (x integer REFERENCES a, v integer, PRIMARY KEY(x)); CREATE VIEW ab AS SELECT a.x, a.y, b.z FROM a,b WHERE a.x=b.x; CREATE VIEW ac AS SELECT a.x, a.y, c.v FROM a,c WHERE a.x=c.x; /* this -insert- seems to work */ CREATE RULE ab_ins AS ON INSERT TO ab DO INSTEAD ( INSERT INTO a(x,y) VALUES (new.x, new.y); INSERT INTO b(x,z) VALUES (new.x, new.z); ); /* cascading delete on a to b and c */ CREATE RULE a_del AS ON DELETE TO a DO ( DELETE FROM b WHERE (x=old.x); DELETE FROM c WHERE (x=old.x); ); /* delete on view doesn't work */ CREATE RULE ab_del AS ON DELETE TO ab DO INSTEAD DELETE FROM a WHERE (x=old.x) ; The last rule seems to have the same effect as the original rule where I implemented the cascading delete on the delete rule for the ab-view. So I suppose the query rewriter will end up executing the same sequence of queries. Now, is there a way to implement this delete on the joined view? Thanks, Lieven ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Question on Bizarre Sorting (ORDER BY in 7.1) (fwd)
The behavior seen here seems correct although not intuitive. My guess is that your database executables where created with locale support enabled and your database was inited with a US locale. The sorting logic for the english locales only sort on alpha-numeric characters. All other characters are ignored for sorting purposes. So these values: 000-0987877374-00313 ___-0987877410-00316 ___-0987877430-00317 100-0987877381-00314 100-0987877395-00315 200-0987877461-00318 get sorted as: 98787737400313 098787741000316 098787743000317 100098787738100314 100098787739500315 200098787746100318 thus you see it is sorted correctly. If you look at all of the other examples you will see this explains all of the results you are seeing. What I think you really want here is a binary sort order, not a locale specific sort order. So you should initdb your database with a locale of C, or rebuild postgresql without locale support enabled. I think there is a contrib program that you can use to see what locale your database was created with. Once your database is created you can't change the locale, you will need to re-initdb. thanks, --Barry Justin Clift wrote: > Dunno if this is helpful, but there is a pattern. > > After stripping off the leading non-numeric characters, from the results > in the previous email, this leaves : > > lxp=# SELECT threadid FROM test ORDER BY threadid; >threadid > -- > 000-0987877374-00313 > 0987877410-00316 > 0987877430-00317 > 100-0987877381-00314 > 100-0987877395-00315 > 200-0987877461-00318 > (6 rows) > > lxp=# SELECT substr(threadid,1,5) FROM test ORDER BY substr(threadid, 1, > 5); > substr > > 0 > 0 > 000-0 > 100-0 > 100-0 > 200-0 > (6 rows) > > lxp=# SELECT substr(threadid,1,6) FROM test ORDER BY substr(threadid, > 1,6); > substr > - > 000-09 > 09 > 09 > 100-09 > 100-09 > 200-09 > (6 rows) > > As for why? Don't know. > > But the sorting is consistent. > > Regards and best wishes, > > Justin Clift > > > [EMAIL PROTECTED] wrote: > >> On Tue, 24 Apr 2001, Clayton Vernon wrote: >> >>> seems to be treating 'threadid' as numeric, not alpha. >>> did you run any macros that needed to guess the format of the field? many of >>> these are very poor in terms of looking down the table. sometimes the first >>> row can inadvertently define things for you. >> >> Nope, to my knowledge nothing is going on except for normal INSERTs >> and SELECTs. What confuses me is that 'threadid' is of type text, and even >> if it were being treated as if it were of type numeric, its results are >> still inconsistent, and somewhat inscrutable. >> >> I can't see any reasonable way that this sorting: >> lxp=# SELECT threadid FROM test ORDER BY threadid; threadid -- 000-0987877374-00313 ___-0987877410-00316 ___-0987877430-00317 100-0987877381-00314 100-0987877395-00315 200-0987877461-00318 (6 rows) >>> >> ...would be occuring, unless it's going out of its way to try to strip >> non-numeric characters and treat the remaining assembled numbers as a >> whole number to sort by, possibly? >> >> But if that were the case, that still doesn't explain why the substrings >> (below) behave the way that they do, in re-ordering the underscores. >> >>> lxp=# SELECT substr(threadid,1,5) FROM test ORDER BY substr(threadid, 1, 5); >>> substr >>> >>> ___-0 >>> ___-0 >>> 000-0 >>> 100-0 >>> 100-0 >>> 200-0 >>> (6 rows) >>> Now, the underscores appear to PRECEDE the 0's. This seems at least a >>> >>> little more sane, however this is completely the opposite of where the >>> underscore would be sorted with 7.0.3. Now consider the next substring, of >>> six characters instead of five. >>> >>> lxp=# SELECT substr(threadid,1,6) FROM test ORDER BY substr(threadid, 1,6); >>> substr >>> - >>> 000-09 >>> ___-09 >>> ___-09 >>> 100-09 >>> 100-09 >>> 200-09 >>> (6 rows) >> >> Thanks, though still puzzled, >> Jw @ Command Prompt. >> -- >> By way of [EMAIL PROTECTED] >> >> ---(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 4: Don't 'kill -9' the postmaster
[GENERAL] Re: indices are crashed after installation of rpm
Peter Keller <[EMAIL PROTECTED]> wrote: >we are running PostgreSQL 7.0.2 on Solaris 2.6,/x86 compiled by gcc 2.8.1. There are at least two components in this you might consider upgrading: - PostgreSQL itself (to 7.0.3 or, even better, 7.1) - gcc. Gcc 2.8.1 was a bit of a dead end in gcc development; by the time it was released, the EGCS development effort was quite a bit more advanced. The EGCS work has been incorporated into gcc 2.95(.x), and personally I trust gcc 2.95.3 a lot more than I'd trust 2.8.1. HTH, Ray -- "My golden rule of computing is reboot your system every morning." Jon C.A. DeKeles, Technical Director, ZDNet AnchorDesk in http://www.zdnet.com/anchordesk/story/story_4100.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SUM()ming a view's column
On Wed, Apr 25, 2001 at 12:52:15PM -0400, Tom Lane wrote: > Jan Ploski <[EMAIL PROTECTED]> writes: > > Here, v_wutest is a view which returns two rows. Shouldn't the SUM > > aggregate simply add these two values? To me, it looks like a bug. > > Does the view contain GROUP BY? If so, yes this is broken in pre-7.1 > releases ... Yes, it does. Everything's clear then :-) Thanks for your reply (and for the fix in 7.1 to whoever deserves credit!) -JPL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Performance: sql functions v. plpgsql v. plperl
On Wed, 25 Apr 2001, Tom Lane wrote: > Joel Burton <[EMAIL PROTECTED]> writes: > > Last night, I was doing some amateurish benchmarking and found that, > > contrary to my (admittedly uninformed) expectation, sql functions seem > > *slower* than plsql functions. > > IIRC, sql functions are re-parsed/planned on each execution, whereas > plpgsql functions cache their parse trees and execution plans. > Depending on exactly what you were doing, that might explain the > difference. Why is this? I'm just delving into the source code in earnest for the first time, so, forgive any awful errors, but it seems like we have the plumbing for this in views/rules... couldn't the parse tree be cached from this for each backend? Or are SQL functions mildly deprecated since they could always be replaced by the plpgsql function begin return ... end; ? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Performance: sql functions v. plpgsql v. plperl
Joel Burton <[EMAIL PROTECTED]> writes: > couldn't the parse tree be cached from this for each backend? Yes, if someone wanted to work on it ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] playing with timestamp entries
I record our radius logs in a firly basic table, schema is as follows: -- CREATE TABLE "history_new" ( "username" character varying(50) NOT NULL, "time_stamp" int4 NOT NULL, "acctstatustype" character varying(8) NOT NULL , "acctdelay" int2 NOT NULL, "acctinputoctets" int4 , "acctoutputoctets" int4 , "acctsessionid" character varying(30), "acctsessiontime" int4 , "acctterminatecause" character varying(50), "nasidentifier" character varying(22), "nasport" character varying(4), "framedipaddress" character varying(16), "callingstationid" character varying(16), "ascenddatarate" character varying(16), "calledstationid" character varying(16) ); --- I then create an index on the username column by: --- CREATE INDEX "i_h_uh" on HISTORY using hash (username); --- I use the 'hash' type as queries regarding usage will always be of the form "select .. where username='xxx';" I also calculate a 'summary' in the form of a table I call sumlog: --- CREATE TABLE "sumlog" ( "username" character varying(8) NOT NULL, "period" character varying(8) NOT NULL, "sumtime" int4 DEFAULT '0' , "mbup" float8 DEFAULT '0.0' , "mbdn" float8 DEFAULT '0.0' ); This table is populated by the following query: - insert into sumlog select s.username, to_char(timestamp(h.time_stamp),'-MM') as date, sum(h.acctsessiontime), sum(float8(h.acctinputoctets)/100), sum(float8(h.acctoutputoctets)/100) from subscribers as s,history as h where s.username=h.username group by s.username,date; --- This works fine, but as the database size is constantly growing the summary table takes a while to calculate... I was thinking of doing the following: 1. only update 'sumlog' for the current period (eg. 2001-04) 2. adding an index on the timestamp column to speed-up the query for the insert into sumlog. My questions are: 1. is it possible to create an index entry on the function applied to the time_stamp. eg. something of the form [ create index "i_ts" on history (to_char(timestamp(h.time_stamp),'-MM')) ] 2. what is the best way to access the data from the history table for a known period.. eg. knowing period='2001-04' is there a better function to use than 'to_char' against the timestamp, any sort of indexing I should use, etc... I've been going around in circles, and I'm sure I've missed some basic/common-sense sort of step, but now I'm running out of time to spend on it Has anyone here done anything similar?? -- Dale Walker < [EMAIL PROTECTED] > Independent Computer Retailers (ICR) Pty Ltd http://www.icr.com.au/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Joined table view - multiple delete action rule
Hello,
I came up against the same (or similar) difficulty some month ago.
Then Tom Lane suggested me to write a simple sql or plpgsql function which handles the
multiple delete action.
(BTW, maybe you don't want to use the on delete cascade referential integrity in some
cases.)
Sorry, but I can't find anywhere the answer sent me.
But here's the source code of the function I wrote:
create function f_privateauto_del(ID) returns BOOLEAN as
'DECLARE ID INTEGER;
BEGIN
SELECT INTO ID c_id FROM t_one WHERE t_one.c_id = $1;
IF NOT FOUND THEN RAISE EXCEPTION ''Illegal parameter''; END IF; -- this isn't
necessary
DELETE FROM t_one WHERE oid = $1;
DELETE FROM t_two WHERE c_id = ID;
RETURN true::BOOLEAN;
END;'
language 'plpgsql';
You can figure out from my original letter (below) how much your problems is similar
to the one I had.
Papp Gyozo
- [EMAIL PROTECTED]
>From [EMAIL PROTECTED] Mon Sep 18 12:04:39 2000 +0200
Date: Mon, 18 Sep 2000 12:04:33 +0200 (MET DST)
From: Papp Gyozo <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: rule with multiple DELETE action part
Hello,
now, my only question is how I can make a rule on a view with multiple delete action
part which
works well. I need a mechanism which deletes two rows from two tables
which are in a one-to-one join.
The example listed below is a quite different from the real tables I use.
The table in the same role as "t_two" references the other table ("t_one").
If rules with multiple action don't work correctly this would mean that
my only last chance is using the REFERENCES constraint with ON DELETE CASCADE option?
I hope not, because it is not for the same, I want to deny to delete rows
if it has a pair in the other table.
By the way, multiple inserts seem to work.
try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
CREATE
try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
CREATE
try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
CREATE 81186 1
try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i =
old.c_i;);
CREATE 81187 1
try=# SELECT * FROM v_one_two;
c_oid | c_i |c_d | c_t
---+-++---
81157 | 1 | 2000-09-01 | hello
81158 | 2 | 1999-12-31 | world
81159 | 3 | 2000-08-12 | brave
(3 rows)
try=# DELETE FROM v_one_two WHERE c_i = 2;
DELETE 0
try=# SELECT * FROM t_one;
c_i | c_t
-+---
1 | hello
2 | world
3 | brave
4 | guy
(4 rows)
try=# SELECT * FROM t_two;
c_i |c_d
-+
1 | 2000-09-01
3 | 2000-08-12
(2 rows)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] playing with timestamp entries
Dale Walker <[EMAIL PROTECTED]> writes: > I use the 'hash' type as queries regarding usage will always be of the > form "select .. where username='xxx';" Use a btree anyway. Postgres' btree implementation is much better than its hash index implementation. > insert into sumlog > select s.username, > to_char(timestamp(h.time_stamp),'-MM') as date, > sum(h.acctsessiontime), > sum(float8(h.acctinputoctets)/100), > sum(float8(h.acctoutputoctets)/100) > from subscribers as s,history as h > where s.username=h.username > group by s.username,date; > This works fine, but as the database size is constantly growing the > summary table takes a while to calculate... What plan does EXPLAIN show for this query? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] playing with timestamp entries
Tom Lane wrote: > > Dale Walker <[EMAIL PROTECTED]> writes: > > I use the 'hash' type as queries regarding usage will always be of the > > form "select .. where username='xxx';" > > Use a btree anyway. Postgres' btree implementation is much better than > its hash index implementation. > OK, I'll give that a whirl... > > insert into sumlog > > select s.username, > > to_char(timestamp(h.time_stamp),'-MM') as date, > > sum(h.acctsessiontime), > > sum(float8(h.acctinputoctets)/100), > > sum(float8(h.acctoutputoctets)/100) > > from subscribers as s,history as h > > where s.username=h.username > > group by s.username,date; > > > This works fine, but as the database size is constantly growing the > > summary table takes a while to calculate... > > What plan does EXPLAIN show for this query? > > regards, tom lane psql:zz.sql:7: NOTICE: QUERY PLAN: Aggregate (cost=349984.03..365862.83 rows=127030 width=40) -> Group (cost=349984.03..356335.55 rows=1270304 width=40) -> Sort (cost=349984.03..349984.03 rows=1270304 width=40) -> Hash Join (cost=27.35..87635.90 rows=1270304 width=40) -> Seq Scan on history h (cost=0.00..36786.04 rows=1270304 width=28) -> Hash (cost=25.28..25.28 rows=828 width=12) -> Seq Scan on subscribers s (cost=0.00..25.28 rows=828 width=12) EXPLAIN -- The way I read this, I think my biggest problem is in the sorting/grouping... -- Dale Walker < [EMAIL PROTECTED] > Independent Computer Retailers (ICR) Pty Ltd http://www.icr.com.au/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Newbie struggling to set $PGDATA
Geoff Caplan wrote: >Please help out a Linx/Postgres newbie. > >I simply want to set the $PGDATA environmental variable, but can't >figure out how. The docs assume you already know... > >I have tried setting it in my bash /etc/profile configuration file, >and it shows up ok if I "echo $PGDATA" in the shell. But none of the >postgres utilities such as "initdb" seem to be able to find it. > >What don't I understand? I have already checked the docs/GreatBridge >manual/faqs/archive, so I would very much appreciate some help. Maybe you need to export it. Unless you export a variable, it remains local to the shell where you define it. PGDATA=/path/to/data export PGDATA or export PGDATA=/path/to/data -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Nay, in all these things we are more than conquerors through him that loved us."Romans 8:37 ---(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
[GENERAL] CREATE TABLE AS... syntax?
I'm trying to use CREATE TABLE AS under 7.03. There are no examples in the guide, so I tried a few things: CREATE TABLE foo (test INT4) AS SELECT number FROM account; and CREATE TABLE foo (test INT4) AS number FROM account; and both of them give ERROR: parser: parse error at or near "as". Any tips on how to use this? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] CREATE TABLE AS... syntax?
>CREATE TABLE foo (test INT4) AS SELECT number FROM account; I think you want something like: SELECT number INTO TABLE foo FROM account ; len morgan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Problem with pg_log file corrupted
Hi, I think the pg_log file of my postgreSQL is corrupted after a power failure. I can;t access to tables in my database now. The error message appear when I tried to list all tables is as below : " cannot flush block 8 of pg_log to stable store " Anybody know how can I restore back the pg_log file. Thanks. Emmanuel Wong ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Problem with pg_log file corrupted
[EMAIL PROTECTED] writes: > The error message appear when I tried to list all tables is as below : > " cannot flush block 8 of pg_log to stable store " I can't see any good reason why you'd get a failure there ... unless maybe you are out of disk space? Assuming you are running a 7.0.* release, I'd suggest updating to 7.0.3, which will at least give a more detailed error message. regards, tom lane ---(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] Problem with pg_log file corrupted
-- Forwarded by YekFu Wong on 04/26/2001 02:27 PM --- YekFu Wong 04/26/2001 07:26 AM To: "Tom Lane" <[EMAIL PROTECTED]> @ INTERNET cc: Subject: Re: [GENERAL] Problem with pg_log file corrupted (Document link: YekFu Wong) I am using postgreSQL version 6.5.3 I think probably the transaction was inconsistent when there is a power failure when user try to update data to tables through webpage. Now, I can't do #pgaccess [database name] It will prompt me the same error saying cannot flush block 8 of pg_log file. Since pg_log file is a binary file, I don;t know how to restore it... I tried to delete the file but then, I can;t open any database without the file. I am trying to upgrade the postgreSQL version now...see whether it helps. Thanks. "Tom Lane" <[EMAIL PROTECTED]>@postgresql.org on 04/26/2001 06:27:14 AM Sent by: [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Problem with pg_log file corrupted [EMAIL PROTECTED] writes: > The error message appear when I tried to list all tables is as below : > " cannot flush block 8 of pg_log to stable store " I can't see any good reason why you'd get a failure there ... unless maybe you are out of disk space? Assuming you are running a 7.0.* release, I'd suggest updating to 7.0.3, which will at least give a more detailed error message. regards, tom lane ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Translations of PostgreSQL: Introduction and Concepts
FYI, my book is being translated into Japanese and German. Local publishers will be printing the translations with permission from Addison-Wesley. I hope the translations can be made available online like the English version. Also, my book has been reviewed at: http://Linuxiso.org/bookreviews/postgresql.html The web site for my book is at: http://www.postgresql.org/docs/awbook.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
