Re: [SQL] postgres8 install on fedora core 3 (or redhat4 beta AS
This seems more less rpm-related. However: Already tried to uninstall the old packages? # man rpm -Original Message-From: Joel Fradkin [mailto:[EMAIL PROTECTED]Sent: Samstag, 05. Februar 2005 23:36To: pgsql-sql@postgresql.orgSubject: [SQL] postgres8 install on fedora core 3 (or redhat4 beta AS) Any help? I got the 8 rpms for fedora and it keeps asking for 7.4 rpm’s, I down load the specific 7.4 stuff and it says already installed? Joel Fradkin Wazagua, Inc.2520 Trailmate DrSarasota, Florida 34243Tel. 941-753-7111 ext 305 [EMAIL PROTECTED]www.wazagua.comPowered by WazaguaProviding you with the latest Web-based technology & advanced tools.© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] How can I use large object on PostgreSQL Linux Version?
Are you using Fedora with SELinux or just SELinux? -Original Message-From: Premsun Choltanwanich [mailto:[EMAIL PROTECTED]Sent: Montag, 07. Februar 2005 05:41To: pgsql-sql@postgresql.orgSubject: [SQL] How can I use large object on PostgreSQL Linux Version? For first information, I already have Large Object that work fine on PostgreSQL Windows Version. Now I plan to migrate all of Windows version to Linux version. But I got some problem that make it cannot work fine like on Windows version. I found that at least Large Object that now failed and no data can be imported to Linux version. The problem I found was shown like: ERROR: could not access file "$libdir/lo": No such file or directory ERROR: could not access file "$libdir/dbsize": No such file or directory ERROR: could not access file "$libdir/admin": No such file or directory In my idea, it look like the error was related on $libdir. Maybe some setting about $libdir is wrong but I don't know How can I check it? and How can I correct it??
Re: [SQL] Rule problem with OLD / NEW record set
Ralph Graulich wrote: > > Hello everyone, > > given is a table with a version history kind of thing I am currently > working on. Upon this table there is a view and the application interacts > with the view only, updating/inserting/deleting is controlled by rules. It > seems like the record set "OLD" gets changed when it is used in a SQL > expression: > > CREATE TABLE table1 >( >id INTEGER NOT NULL, >version INTEGER NOT NULL DEFAULT 0, >vnoflag CHAR(1), >content VARCHAR(20) >); > > INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1, 'Y', > 'Test'); > > CREATE VIEW view_table1 AS SELECT * FROM table1; > > -- create a rule for update > CREATE OR REPLACE RULE ru_view_table1_update > AS > ON UPDATE TO view_table1 DO INSTEAD >( >-- insert a new record with the old id, old version number incremented >-- by one, versionflag set to 'Y' and the new content >INSERT INTO table1 (id, version, vnoflag, content) VALUES (OLD.id, > OLD.version+1, 'Y', NEW.content); >-- update the old version and set its versionflag to 'N' as it is no >-- longer the current record >UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND version = > OLD.version; >); > > SELECT * FROM view_table1; > id | version | vnoflag | content > +-+-+- >1 | 1 | Y | Test > (1 row) > > UPDATE view_table1 SET content = 'New Test' WHERE id = 1 AND vnoflag = > 'Y'; > SELECT * FROM view_table1; > id | version | vnoflag | content > +-+-+-- >1 | 1 | N | Test >1 | 2 | N | New Test > > It seems like the UPDATE statement updates both the old and the new > version. If I correctly go through the statements by hand, they should > read: > > INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1+1, 'Y', > 'New Test'); > UPDATE table1 SET vnoflag = 'N' WHERE id = 1 AND version = 1; > > If I change the UPDATE statement to read: > >UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND vno = NEW.vno-1; > > it works like expected: > > id | version | vnoflag | content > +-+-+-- >1 | 2 | Y | New Test >1 | 1 | N | Test > > Where is my logical error? Shouldn't the first UPDATE statement suffice? > > Best regards > ... Ralph ... > I've read your mail pretty late (meaning today), and I was surprised about what is happening the same you were. But after reading the manual $PGSQLD/doc/html/rules-update.html Chapter 34. The Rule System and especially 34.3.1.1. A First Rule Step by Step it became obvious what's going on behind a rule execution. HTH Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Red hat 3 AS when uptodate is it running 2.6 Kernel?
Any one running Redhat 3 AS? Is it using the new Kernel when it is up to date? Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]
In reading in the manual it seems the new database is based on template0. I created my first database (wazagua) from the command line using dbcreate wazagua. It results in CREATE DATABASE wazagua WITH OWNER = postgres ENCODING = 'SQL_ASCII'; When I created a test data base to load my backup I did this inside pgadmin III and see it CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UNICODE'; So I think I see why when I try to restore I have issues. My question is which is the correct choice? We do have some chars that have French aschii, so is 'SQL_ASCII' or 'UNICODE' the correct choice? I was able to move all my data over to the 'SQL_ASCII' database, so I may be again answering my own question. Values like d`Appréhension show up ok in the table. Am I guessing correctly to just make the database I am restoring to 'SQL_ASCII'? I will try this. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] the best way to get the first record from each group
Hi, Is there any better alternative to get the first record from each group? "subno" is an integer. The record with the smallest subno in each group is the first record in the group. select itemno, measureunit, extaxprice from itmt_purchase where subno in (select min(subno)as subno from itmt_purchase group by itemno order by itemno) order by itemno, measureunit; -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07/02/05 ---(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: [SQL] the best way to get the first record from each group
On Tue, Feb 08, 2005 at 10:36:17AM +1000, q2005 wrote: > > Is there any better alternative to get the first record from each group? PostgreSQL has a non-standard SELECT DISTINCT ON query for just this purpose. http://www.postgresql.org/docs/8.0/static/queries-select-lists.html#QUERIES-DISTINCT http://www.postgresql.org/docs/8.0/static/sql-select.html#SQL-DISTINCT -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How can I use large object on PostgreSQL Linux
I use SuSE only.>>> K(AÖPFERL Robert <[EMAIL PROTECTED]> 07-Feb-05 4:05 PM >>> Are you using Fedora with SELinux or just SELinux? -Original Message-From: Premsun Choltanwanich [mailto:[EMAIL PROTECTED]Sent: Montag, 07. Februar 2005 05:41To: pgsql-sql@postgresql.orgSubject: [SQL] How can I use large object on PostgreSQL Linux Version? For first information, I already have Large Object that work fine on PostgreSQL Windows Version. Now I plan to migrate all of Windows version to Linux version. But I got some problem that make it cannot work fine like on Windows version. I found that at least Large Object that now failed and no data can be imported to Linux version. The problem I found was shown like: ERROR: could not access file "$libdir/lo": No such file or directory ERROR: could not access file "$libdir/dbsize": No such file or directory ERROR: could not access file "$libdir/admin": No such file or directory In my idea, it look like the error was related on $libdir. Maybe some setting about $libdir is wrong but I don't know How can I check it? and How can I correct it??
Re: [SQL] the best way to get the first record from each group
I don't really gr0k your field names so I'll use an easier example : CREATE TABLE groups ( group_id SERIAL PRIMARY KEY, group_name TEXT NULL ) WITHOUT OIDS; CREATE TABLE people ( user_id SERIAL PRIMARY KEY, group_id INTEGER NOT NULL REFERENCES groups(group_id), score INTEGER NOT NULL ) WITHOUT OIDS; CREATE INDEX people_scored ON people( group_id, score ); ... put 1K rows into groups, vacuum analyze ... put 128K rows into people, vacuum analyze So you want the user in each group with the highest score (or the lowest subno... thats the same). 0-- DISTINCT ON SELECT DISTINCT ON (group_id) group_id, user_id, score FROM people ORDER BY group_id, score; Unique (cost=0.00..4968.17 rows=996 width=12) (actual time=0.144..539.667 rows=1000 loops=1) -> Index Scan using people_scored on people (cost=0.00..4640.49 rows=131072 width=12) (actual time=0.141..454.893 rows=131072 loops=1) Total runtime: 540.212 ms It works but is about the slowest thing imaginable : index-scanning (or sorting) the entire table. DISTINCT ON can be very convenient nonetheless ! seq scan => disqualified. 1-- min(), max() max() will give you the score but it won't give you the user_id so you have to resort to a trick just like you did. And it does a seq scan => disqualified. 2-- custom aggregate You could possibly write an aggregate which takes a row from people as an argument, or an ARRAY[score,user_id] and which acts like max and returns the ARRAY[score,user_id] with the highest score so you can have its user_id. As array comparison works as expected in pgsql, you could use max(), unfortunately for you, max() does not work on integer arrays (Why is that so ?) so this solution needs you to write a custom aggregate. Note that this would still need a seq scan, and chould be slower than the DISTINCT => disqualified. 2-- subquery The problem is that you'll need a list of your groups. If you don't have a table, you'll have to extract them from the table people with a (SELECT group_id FROM people GROUP BY group_id) which is a sequential scan. So I'll presume there is a groups table, which is why I put a 'REFERENCES groups(group_id)' in the table declaration above. To get the best score in a group of id GID we write : SELECT user_id FROM people WHERE group_id=5 ORDER BY group_id DESC, score DESC LIMIT 1; Limit (cost=0.00..3.69 rows=1 width=12) (actual time=0.054..0.055 rows=1 loops=1) -> Index Scan Backward using people_scored on people (cost=0.00..480.02 rows=130 width=12) (actual time=0.051..0.051 rows=1 loops=1) Index Cond: (group_id = 5) Total runtime: 0.143 ms To get the best scores for all groups we apply this SELECT to all groups. You see now why we need a groups table to precalculate the groups. SELECT g.group_id, (SELECT user_id FROM people WHERE group_id=g.group_id ORDER BY group_id DESC, score DESC LIMIT 1) as user_id FROM groups g; Seq Scan on groups g (cost=0.00..3702.48 rows=1000 width=4) (actual time=0.079..18.942 rows=1000 loops=1) SubPlan -> Limit (cost=0.00..3.69 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=1000) -> Index Scan Backward using people_scored on people (cost=0.00..486.75 rows=132 width=12) (actual time=0.011..0.011 rows=1 loops=1000) Index Cond: (group_id = $0) Total runtime: 19.475 ms Note that the subselect here can only yield ONE column so another join comes in to get the score : -- Take 1 SELECT * FROM people WHERE user_id IN (SELECT (SELECT user_id FROM people WHERE group_id=g.group_id ORDER BY group_id DESC, score DESC LIMIT 1) as user_id FROM groups g); Nested Loop (cost=21.19..10418.45 rows=1000 width=12) (actual time=29.851..87.289 rows=1000 loops=1) -> HashAggregate (cost=17.50..3704.98 rows=1000 width=4) (actual time=29.789..32.174 rows=1000 loops=1) -> Seq Scan on groups g (cost=0.00..15.00 rows=1000 width=4) (actual time=0.119..27.982 rows=1000 loops=1) SubPlan -> Limit (cost=0.00..3.69 rows=1 width=12) (actual time=0.023..0.023 rows=1 loops=1000) -> Index Scan Backward using people_scored on people (cost=0.00..486.75 rows=132 width=12) (actual time=0.020..0.020 rows=1 loops=1000) Index C
Re: [SQL] the best way to get the first record from each group
On Tue, Feb 08, 2005 at 03:20:21AM +0100, PFC wrote: > Anyway, it was fun to experiment with that ! Interesting -- thanks for taking the time. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
