[SQL] [OT] Frontend recommendations
Hey postgresql gurus, I was just wondering, what kind of frontend do you recommend in 'bills (windows...)' environment? Is it better to use a tool like Qt from trolltech or is it better to use something like Omnis or Access??? What are you opinions best regards, Ries van Twisk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] is this explain good or bad???
Hey guys, I'm not sure if this is good or bad but when I want to have a record count of a view it takes at least 75 seconds to complete (PII 1.4Ghz 256Mb RAM). Also when I do a record count of one single table (same table but not joined) it just takes a long time. Firt I want to make sure that the DB is setup correctly before I jump to HW issues. This is my sq_logfile table explain select count(*) from sq_logfile; Aggregate (cost=35988.07..35988.07 rows=1 width=0) -> Seq Scan on sq_logfile (cost=0.00..33493.86 rows=997686 width=0) This is ,y sq_flogfile view based on sq_flogfile explain select count(*) from sq_flogile; Aggregate (cost=128282.68..128282.68 rows=1 width=40) -> Hash Join (cost=8.65..125788.46 rows=997686 width=40) -> Hash Join (cost=6.49..105832.58 rows=997686 width=36) -> Hash Join (cost=4.75..88371.34 rows=997686 width=32) -> Hash Join (cost=3.69..68416.56 rows=997686 width=28) -> Hash Join (cost=2.58..50955.94 rows=997686 width=24) -> Seq Scan on sq_logfile sl (cost=0.00..33493.86 rows=997686 width=20) -> Hash (cost=2.26..2.26 rows=126 width=4) -> Seq Scan on sq_contenttypes ct (cost=0.00..2.26 rows=126 width=4) -> Hash (cost=1.09..1.09 rows=9 width=4) -> Seq Scan on sq_requestmethods rm (cost=0.00..1.09 rows=9 width=4) -> Hash (cost=1.05..1.05 rows=5 width=4) -> Seq Scan on sq_hierarchycodes hc (cost=0.00..1.05 rows=5 width=4) -> Hash (cost=1.59..1.59 rows=59 width=4) -> Seq Scan on sq_resultcodes rc (cost=0.00..1.59 rows=59 width=4) -> Hash (cost=1.93..1.93 rows=93 width=4) -> Seq Scan on sq_clientaddrfqdn cafqdn (cost=0.00..1.93 rows=93 width=4) <---> CREATE TABLE sq_logfile ( id SERIAL8, stime NUMERIC(14,3), tstime TIMESTAMP, duration INTEGER, client_addr_dotted INET, client_addr_fqdn_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_clientaddrfqdn (id), resultcode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_resultcodes(id), requestsize INTEGER, requestmethod_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_requestmethods (id), url TEXT, rfc931 TEXT, hierarchycode_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_hierarchycodes (id), hierarchycode TEXT, contenttype_id INTEGER DEFAULT 0 NOT NULL REFERENCES sq_contenttypes (id), PRIMARY KEY(id) ); -- Everything is sorted bu date/time so we need a index??? CREATE INDEX idx_sq_logfile1 ON sq_logfile (tstime); CREATE INDEX idx_sq_logfile2 ON sq_logfile (tstime, client_addr_dotted); CREATE INDEX idx_sq_logfile3 ON sq_logfile (tstime, rfc931); ---(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] is this explain good or bad???
Here are the explain analyze versions: Best regards and thanx in advance Ries explain analyze select count(*) from sq_logfile; NOTICE: QUERY PLAN: Aggregate (cost=35988.07..35988.07 rows=1 width=0) (actual time=71907.64..71907.64 rows=1 loops=1) -> Seq Scan on sq_logfile (cost=0.00..33493.86 rows=997686 width=0) (actual time=12.90..46759.12 rows=997686 loops=1) Total runtime: 71907.76 msec explain analyze select count(*) from sq_flogile; NOTICE: QUERY PLAN: Aggregate (cost=128282.68..128282.68 rows=1 width=40) (actual time=99338.92..99338.92 rows=1 loops=1) -> Hash Join (cost=8.65..125788.46 rows=997686 width=40) (actual time=34.34..93123.02 rows=997686 loops=1) -> Hash Join (cost=6.49..105832.58 rows=997686 width=36) (actual time=23.94..78411.62 rows=997686 loops=1) -> Hash Join (cost=4.75..88371.34 rows=997686 width=32) (actual time=15.68..63115.86 rows=997686 loops=1) -> Hash Join (cost=3.69..68416.56 rows=997686 width=28) (actual time=12.56..45110.94 rows=997686 loops=1) -> Hash Join (cost=2.58..50955.94 rows=997686 width=24) (actual time=9.24..23160.42 rows=997686 loops=1) -> Seq Scan on sq_logfile sl (cost=0.00..33493.86 rows=997686 width=20) (actual time=5.72..11518.14 rows=997686 loops=1) -> Hash (cost=2.26..2.26 rows=126 width=4) (actual time=3.46..3.46 rows=0 loops=1) -> Seq Scan on sq_contenttypes ct (cost=0.00..2.26 rows=126 width=4) (actual time=2.88..3.17 rows=126 loops=1) -> Hash (cost=1.09..1.09 rows=9 width=4) (actual time=3.21..3.21 rows=0 loops=1) -> Seq Scan on sq_requestmethods rm (cost=0.00..1.09 rows=9 width=4) (actual time=3.16..3.19 rows=9 loops=1) -> Hash (cost=1.05..1.05 rows=5 width=4) (actual time=3.06..3.06 rows=0 loops=1) -> Seq Scan on sq_hierarchycodes hc (cost=0.00..1.05 rows=5 width=4) (actual time=3.04..3.05 rows=5 loops=1) -> Hash (cost=1.59..1.59 rows=59 width=4) (actual time=8.20..8.20 rows=0 loops=1) -> Seq Scan on sq_resultcodes rc (cost=0.00..1.59 rows=59 width=4) (actual time=7.93..8.07 rows=59 loops=1) -> Hash (cost=1.93..1.93 rows=93 width=4) (actual time=10.34..10.34 rows=0 loops=1) -> Seq Scan on sq_clientaddrfqdn cafqdn (cost=0.00..1.93 rows=93 width=4) (actual time=9.92..10.13 rows=93 loops=1) Total runtime: 99339.49 msec -Oorspronkelijk bericht- Van: Tomasz Myrta [mailto:[EMAIL PROTECTED] Verzonden: vrijdag 19 september 2003 8:52 Aan: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Onderwerp: Re: [SQL] is this explain good or bad??? > explain select count(*) from sq_logfile; Not too helpful. Better choice is: explain analyze select * from sq_logfile; Your explains show that selecting from view is 4 times slower than selecting from a table (35988:128282). It is possible. Anyway counting 1 million rows usualy takes a long time... Regards, Tomasz Myrta ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] partial unique constraint
You should create a functional index here.
Ries
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Robert Treat
> Verzonden: dinsdag 6 april 2004 16:30
> Aan: [EMAIL PROTECTED]
> Onderwerp: [SQL] partial unique constraint
>
>
> Trying to come up with the proper syntax to meet the
> following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we
> only allow
> one case of bar and baz = true... i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different... did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me...
>
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(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] Dublicates pairs in a table.
Richard,
do you suggest using a stored procedure to handle this? I do expect that the
table will be large (for me large is a around 1-2 records, the table
as more columns but I only need the restriction on c1 & c2) but I dont
expect lots of inserts and deletes. Data entry will be done using a regular
user but of course a update needs to be done within a acceptable time so the
user does not get annoyed.
best regards,
Ries van Twisk
> -Oorspronkelijk bericht-
> Van: Richard Huxton [mailto:[EMAIL PROTECTED]]
> Verzonden: maandag 16 september 2002 17:08
> Aan: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Onderwerp: Re: [SQL] Dublicates pairs in a table.
>
>
> On Monday 16 Sep 2002 3:51 pm, Ries van Twisk wrote:
> > Dear guys/girls,
> >
> > I have a small question which I could not clearly find in
> the postgreSQL
> > manual.
> >
> > if I create this table and index
> > CRAEATE TABLE test (
> > id SERIAL,
> > c1 VARCHAR(32),
> > c2 VARCHAR(32),
> > c3 VARCHAR(32)
> > );
> >
> > CREATE UNIQUE INDEX test_idx ON test(id, c1,c2);
>
> Close, try
>
> CREATE UNIQUE INDEX test_idx ON test (c1,c2)
>
> > what I try to archive here is that I don't want duplicate
> pais in my table:
> > example
> >
> > INSET INTO test (c1,c2) VALUES('a', 'a'); -- Not allowed
> since we already
> > have a duplicate ('a', 'a') pair
>
> > What I want to know is that if this is smart do do, or is
> there a other
> > better way to make sure I don't insert duplicate pairs in
> my database.
> > I'm not sure if a stored procedure is better in my case
> since I don't
> > really need the index on columns c1 or c2.
>
> Unless test is a very small table with lots of
> inserts/deletions I'd just use
> the index, otherwise you'll have to scan the table and check
> for another copy
> anyway.
>
> HTH
>
> - Richard Huxton
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Null not equal to '' (empty)
This is because '' is not equal to NULL
'' Means a empty string
NULL means a empty set
So this: SELECT * FROM tbl WHERE c1 IS NULL;
is totally different then: SELECT * FROM tbl WHERE c1='';
Ries
-Oorspronkelijk bericht-
Van: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Namens Ajit Aranha
Verzonden: vrijdag 20 september 2002 8:09
Aan: [EMAIL PROTECTED]
Onderwerp: [SQL] Null not equal to '' (empty)
Why is ''(empty) not equal to null? Its a major headache when porting
from other RDBMS like Oracle. Anyone knows any easy workarounds?
i.e. if you use:create table tbl (
c1 varchar(5));
insert into tbl values ('');
select * from tbl where c1 is null; will return
zero rows.
Also try this: select TO_DATE('','DD-MM-YY'); and
this TO_DATE('','DD-Mon-YY');
;-)
-Ajit ([EMAIL PROTECTED])
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] [OT] Inventory systems (private)
Hi All, is there any whitepaper, document or website that can point me to how to setup a inventory system? I'm particulary interested how other people solve the problem of a unknown number of attributes to a inventory item. example: BAL<-- Inventory Item - Color <- Attribute - Diameter <- Attribute - Weight <- Attribute Car<-- Inventory Item - Speed <- Attribute - Size <- Attribute - Weight <- Attribute - Color <- Attribute Computer<-- Inventory Item - Brand <- Attribute - Weight <- Attribute - Windows/Linux <- Attribute I can ofcource add any number of columns to a table but for a lot of items there will be a lot of NULL values and currently I don't know how many attrubutes one item can have (possible between 10 and 20). This can even change in feature opon request. Ries ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [OT] Inventory systems (private)
Troy, Andy,
you both are right and it was my first tought. My only concern is that would
this system be fast enough for large tables (for me large is around 250.000
unique items) and thus my attribute table would be around 2.500.000 and
5.000.000 entrys. A record for one attribute is small I think around 128
byte in size.
One thing is that every attribute must be in it's own domain. For weight for
example I have three different meanings ( 1] Pull weight 2] push weight 3]
weight of the items itself) but using the method troy suggested that would
not be a problem if I create some sort of a domain table.
Anyway so far thangs for the quick responses, I've got something to work on.
best regards,
Ries van Twisk
-Oorspronkelijk bericht-
Van: Troy [mailto:[EMAIL PROTECTED]]
Verzonden: dinsdag 3 december 2002 15:47
Aan: Ries van Twisk
CC: [EMAIL PROTECTED]
Onderwerp: Re: [SQL] [OT] Inventory systems (private)
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
'matchbox tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Weight', 20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy KorjuslommiTksoft Inc.
[EMAIL PROTECTED]
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL<-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car<-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer<-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] RFC: A brief guide to nulls (noarchive)
You might add this as an example:
mytable
id value
1 1
2 2
3 3
4
5 4
6 5
-- Count ALL records
SELECT count(*) FROM mytable;
Result: 6
-- Count id records
SELECT count(id) FROM mytable;
Result: 6
-- Count value records
SELECT count(value) FROM mytable;
Result: 5
-- Sum of all values
SELECT sum(value) FROM mytable;
Result: 15
-- Average of the values
SELECT sum(value)/count(value) FROM mytable;
Result: 3
-- !!!WRONG!!! Method of the average
SELECT sum(value)/count(*) FROM mytable;
Result: 2.5
What I try to do and what I advice to the novice database designer is try to
avoid NULLS. Why??? because it's differcult to think in tree based logic.
As for a example in the case of the customer sex.
You can use 'M' for male, 'F' for female, 'U' for unknown and 'N' for 'not
applicapable'.
In this way you can use the NOT NULL contsraint so the novice programmer can
work with actual values instead of 'forgetting' about the NULLS. This idea
can be helpfull for the novice database designer.
Just a thought...
Ries van Twisk
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]Namens [EMAIL PROTECTED]
> Verzonden: woensdag 15 januari 2003 18:23
> Aan: [EMAIL PROTECTED]
> Onderwerp: [SQL] RFC: A brief guide to nulls
>
>
> There have been a few posts recently where people have had
> problems with
> nulls. Anyone got comments on the below before I submit it to
> techdocs?
>
> TIA
>
> - Richard Huxton
>
> A Brief Guide to NULLs
> ==
>
> What is a null?
> ===
> A null is *not* an empty string.
> A null is *not* a value.
> A null is *not* a "special" value.
> A null is the absence of a value.
>
>
> What do nulls mean?
> ===
> Well, they *should* mean one of two things:
> 1. There is no applicable value
> 2. There is a value but it is unknown
>
> Example 1: Imagine you have a customer table with name and sex fields.
> If you get a new customer "ACME Widgets Ltd", the sex field
> is meaningless
> since your customer is a company (case 1).
> If you get a new customer "Jackie Smith" they might be male
> or female, but
> you might not know (case 2).
>
> Example 2: You have an address table with
> (street,city,county,postalcode)
> fields.
> You might insert an address ("10 Downing
> Street","London",Null,"WC1 1AA")
> since you don't have a valid county.
> You might also insert an address ("1 Any
> Street","Maidstone","Kent",Null)
> where there *must be* a valid postalcode, but you don't know
> what it is.
>
> It might be useful to be able to distinguish between these
> two cases - not
> applicable and unknown, but there is only one option "Null"
> available to
> us, so we can't.
>
>
> How do nulls work?
> ==
> There is one very important rule when dealing with nulls. The
> result of
> any operation or comparison, when applied to a null is null. The only
> exception is testing if a value is null.
>
> Example: with the customer table above you could run the
> following queries:
> SELECT * FROM customer WHERE sex='M';
> SELECT * FROM customer WHERE sex<>'M';
> Now you might think this returns all customers, but it will miss those
> where sex is null. You've asked for all rows where the value
> of sex is 'M'
> and all those with values not equal to 'M' but not rows with
> *no value at
> all*
>
> It might help to think of a database as a set of statements
> you *know* to
> be true. A null indicates that you *cannot say anything at
> all* about that
> field. You can't say what it is, you can't say what it isn't,
> you can only
> say there is some information missing.
>
> So, to see all the customers with unknown or inapplicable sex
> you would need:
> SELECT * FROM customer WHERE sex IS NULL;
>
> There are actually three possible results for a test in SQL -
> True (the
> test passed), False (the test failed) and Null (you tested against a
> null). A result of null usually gets treated as False, so
> testing against
> nulls always fails.
>
> If you try to perform an operation on nulls, again the result
> is always
> null. So the results of all of the following are null:
> SELECT 'abc' || null;
> SELECT 1 + null;
> SELECT sqrt(null::numeric);
> The first case can be especially confusing. Concatenating a
> null string to
> a string value will return null, not the original value.
>
>
&g
[SQL] To use a VIEW or not to use a View.....
Dear PostgreSQL users, I have a view and a table, I understand that when a frontend accesses a VIEW that PostgreSQL cannot use a index on that view. For example when I do this: SELECT * FROM full_cablelist WHERE projectocode=5; Correct? Now I just want to make sure for myself if the VIEW I created is the right way to go, or is it better to contruct a SQL in my application that looks like the view and send it to postgreSQL so it will use all indexes correctly. I use postgreSQL 7.2.1 I beliefe there is a change in postgreSQL 7.3.x on which I can cache a view??? Not sure what the issue was. I ask this because I expect pore performance in feature when the cablelist table holds up to around 20.000 rows. Each query to full_cablelist will return around 1200 rows. best regards, Ries van Twisk -- CABLE LIST CREATE TABLE cablelist ( id SERIAL, cableno VARCHAR(8), projectcodeid INTEGER CONSTRAINT cablelist_projectcodes_con NOT NULL REFERENCES projectcodes(id) ON DELETE CASCADE, fromconnid INTEGER CONSTRAINT cablelist_fromconnid_con NOT NULL REFERENCES libconnections(id) ON DELETE CASCADE, toconnidINTEGER CONSTRAINT cablelist_toconnid_con NOT NULL REFERENCES libconnections(id) ON DELETE CASCADE, fromshiplocationid INTEGER CONSTRAINT cablelist_fromshiplocationid_con NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE, toshiplocationidINTEGER CONSTRAINT cablelist_toshiplocationid_con NOT NULL REFERENCES shiplocations(id) ON DELETE CASCADE, marktypesid INTEGER CONSTRAINT cablelist_tomarktypeid_con NOT NULL REFERENCES marktypes(id) ON DELETE CASCADE, cabletypeid INTEGER CONSTRAINT cablelist_cabletypeid_con NOT NULL REFERENCES cabletypes(id) ON DELETE CASCADE, cut BOOLEAN DEFAULT 'false' NOT NULL, placed BOOLEAN DEFAULT 'false' NOT NULL, ok BOOLEAN DEFAULT 'false' ); -- CABLE LIST VIEW CREATE VIEW full_cablelist AS SELECT cl.id, cl.cableno AS cableno, pc.projectcode AS projectcode, pc.id AS projectcodeid, lcf.name AS fconnection, lct.name AS tconnection, lif.name AS fitem, lit.name AS titem, slf.rib AS frib,slt.rib AS trib, slf.name AS fname, slt.name AS tname, ct.cabletype AS cabletype, ct.coretype AS coretype, cl.cut, cl.placed, cl.ok FROM cablelist AS cl, libconnections AS lcf, libconnections AS lct, libitems AS lif, libitems AS lit, shiplocations AS slf, shiplocations AS slt, projectcodes AS pc, cabletypes AS ct WHERE pc.id=cl.projectcodeid AND lcf.id=cl.fromconnid AND lct.id=cl.toconnid AND lif.id=lcf.libitemid AND lit.id=lct.libitemid AND slf.id=cl.fromshiplocationid AND slt.id=cl.toshiplocationid AND ct.id=cl.cabletypeid ---(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] To use a VIEW or not to use a View.....
First of all I want to thank you for all responses! I was overwhelmed with it :D Below you find the schema I'm currently using and the output of explain. I removed all comments so the mail will be small, the schema is still work in progress. I especially I need to take a look at the indexes. Any hints will be appreciated. best reghards, Ries van Twisk <--- Here you find the output of the explain again: I cannot yet read the output of explain si I'm not sure if the output looks good or bad. echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE projectcode=5" | psql testdb > /tmp/explain.txt NOTICE: QUERY PLAN: Hash Join (cost=26.28..39.00 rows=23 width=200) -> Hash Join (cost=24.85..37.17 rows=23 width=182) -> Hash Join (cost=23.43..35.34 rows=23 width=164) -> Seq Scan on libitems lit (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=23.37..23.37 rows=23 width=137) -> Hash Join (cost=11.05..23.37 rows=23 width=137) -> Hash Join (cost=9.75..21.67 rows=23 width=120) -> Seq Scan on libitems lif (cost=0.00..7.39 rows=339 width=27) -> Hash (cost=9.69..9.69 rows=23 width=93) -> Hash Join (cost=4.76..9.69 rows=23 width=93) -> Hash Join (cost=3.46..7.99 rows=23 width=76) -> Hash Join (cost=2.42..6.32 rows=69 width=63) -> Seq Scan on cablelist cl (cost=0.00..2.69 rows=69 width=41) -> Hash (cost=2.06..2.06 rows=106 width=22) -> Seq Scan on cabletypes ct (cost=0.00..2.06 rows=106 width=22) -> Hash (cost=1.04..1.04 rows=1 width=13) -> Seq Scan on projectcodes pc (cost=0.00..1.04 rows=1 width=13) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lcf (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.24..1.24 rows=24 width=17) -> Seq Scan on libconnections lct (cost=0.00..1.24 rows=24 width=17) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slt (cost=0.00..1.34 rows=34 width=18) -> Hash (cost=1.34..1.34 rows=34 width=18) -> Seq Scan on shiplocations slf (cost=0.00..1.34 rows=34 width=18) <-- CREATE FUNCTION ord_fn (text,text) RETURNS text AS ' SELECT (CASE WHEN $1 < $2 THEN $1 || $2 ELSE $2 || $1 END) as t; ' LANGUAGE SQL WITH (iscachable); CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/lib/postgresql/plpgsql.so' LANGUAGE 'C'; CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE cabletypes ( id SERIAL, cabletype VARCHAR(24) NOT NULL CHECK ( length(cabletype) > 1 ), -- Naam van de kabel coretypeVARCHAR(16) NOT NULL CHECK ( length(coretype) > 1 ) -- Type kabel/aantal aders ); CREATE UNIQUE INDEX cabletypes_idx ON cabletypes (id); CREATE FUNCTION f_check_cabletypes() RETURNS OPAQUE AS ' DECLARE check RECORD; BEGIN SELECT INTO check * FROM cabletypes WHERE cabletype=NEW.cabletype AND coretype=NEW.coretype LIMIT 1; IF FOUND THEN RAISE EXCEPTION ''[0001] cabletype and coretype combination already exsists in cabletypes!''; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER tr_cabletypes BEFORE INSERT OR UPDATE ON cabletypes FOR EACH ROW EXECUTE PROCEDURE f_check_cabletypes(); CREATE TABLE marktypes ( id SERIAL, nameVARCHAR(24) NOT NULL UNIQUE,-- Naam van de markering color INTEGER NOT NULL-- Eventuele kleur ); CREATE UNIQUE INDEX marktypes_idx ON marktypes (id); CREATE TABLE projectcodes ( id SERIAL, projectcode VARCHAR(16) NOT NULL UNIQUE,-- Project code naam projectname VARCHAR(64) NOT NULL, -- Project uitleg deleted BOOLEAN DEFAULT 'false' NOT NULL ); CREATE UNIQUE INDEX projectcodes_idx ON projectcodes (id); CREATE TABLE libitems ( id SERIAL, projectcodeid INTEGER DEFAULT 0 NOT NULL REFERENCES projectcodes(id) ON DELETE CASCADE, nameVARCHAR(32) NO
Re: [SQL] [Fwd: SQL book]
Jodi, get some of Joe Celco books for me they where extreemly usefull. Ries ---(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] CHECK constraint on multiple tables
can't you solve it creating a reference between the tables? Ries On Sep 14, 2009, at 8:24 AM, Mario Splivalo wrote: I have two tables, tableA and tableB: CREATE TABLE tableA (idA integer primary key, email character varying unique); CREATE TABLE tableB (idB integer primary key, email character varying unique); Now, I want to create check constraint in both tables that would disallow records to either table where email is 'mentioned' in other table. If CHECK constraints supported SUBSELECTS, I could write: ALTER TABLE tableA ADD CONSTRAINT tableA_chk CHECK (email NOT IN (SELECT email FROM tableB)); Unfortunatley, postgres won't allow me to do so. Now, i could create function, check_for_email, that would return TRUE if email is mentioned in either table, and then call that function when creating a check constraint. Or I could add separate table, emails, like this: CREATE TABLE emails (email_id integer primary key, email character varying unique) And then replace 'email' column in tables tableA and tableB with 'email_id' that would be foreign key refference to the emails table. I could, also, write functions for inserting data to the tableA and tableB tables. What would be the best approach to solve the problem I have? Could I use rules on insert to help me? Mario -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql regards, Ries van Twisk - tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS WebORB PostgreSQL DB-Architect email: r...@vantwisk.nlweb: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: +1-747-690-5133 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Implementing ACLs in Pure SQL?
On Aug 29, 2008, at 5:33 PM, Michael B Allen wrote:
Hello,
I've been tinkering with SQL level access control for web
applications. Currently I have a UNIX style uid / gid for each record
and do a WHERE e.uid IN (10,20,30) where 10, 20 and 30 would be the
IDs
of groups the user is in.
However, I'm not satisfied with this model as it has all of the
problems
UNIX style permissions have. In particular you can only have one
group on
each record which ultimately leads you into a few quantized levels of
privilege.
It would be much better if there were a way to implement ACLs.
Meaning - given a user with the following groups (again using IDs
instead of names):
user_groups:
10
20
30
and an ACL with groups:
acl_groups:
18
19
20
21
an access control check is performed with the following pseudocode:
foreach (acl_groups as ag) {
foreach (user_groups as ug) {
if (ug == ag) {
return true
}
}
}
return false;
Meaning, groups 18, 19, 20 and 21 are allowed to access the resource
protected by the ACL (the database record). When group 20 is reached
by
the outer loop and the inner loop finds 20 in the list of groups the
user is in, the above example would return true to indicate that the
particular user should be granted access to the record (i.e. the WHERE
clause would match).
There is one way to do this. Each record has an ACL field with a
string
like '+18+19+20+21+':
UPDATE e SET acl_groups = '+18+19+20+21+' WHERE eid = 1001
Then to perform the access check and retrieve the record you do:
SELECT * FROM e WHERE
(e.acl_groups LIKE '%+10+%'
OR e.acl_groups LIKE '%+20+%'
OR e.acl_groups LIKE '%+30+%')
INSERT INTO e (name, color) VALUES ('Alice', 'blue') WHERE eid = 1001
AND (e.acl_groups LIKE '%+10+%'
OR e.acl_groups LIKE '%+20+%'
OR e.acl_groups LIKE '%+30+%')
... etc
Using LIKE is a little inefficient but I assume it would be more
efficient
than retrieving all of the records and performing the access check in
loop within the application.
Can anyone suggest a superior method?
Or any other ideas regarding implementing ACLs in SQL would be greatly
appreciated.
Mike
Hey Mike,
currently I am underway implementing this with pure SQL, but I don't
have groups
like unix groups.
Each group has a specific meaning. For example a group marketing,
or a group marketing admin. They are just names and it's up to the
application to implement
what action to take.
In my case I am disabling and enabling specific objects within a adobe
flex interface
and subgroups are handles with trees.
Then in that case a user can be member of specific groups (or sub
groups) in and
my case it would show what application you can access, or what objects
are allowed for this user.
Current I have a user table, a group table and an applciation table
and a object table
Then two MM tables between user and application/object and group and
application/object.
Then in plpgsql I resolve the correct ACL for a user.
Ries
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with pg_connect() in PHP
On Sep 26, 2008, at 12:23 PM, Edward W. Rouse wrote:
Can I assume the missing ‘.”’ From the end of PG_PASSWORD is a cut
and paste error?
Edward W. Rouse
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
] On Behalf Of James Kitambara
Sent: Friday, September 26, 2008 1:22 AM
To: pgsql-sql
Cc: Kenichiro Arakaki; Ken Arakaki
Subject: [SQL] Problem with pg_connect() in PHP
Dear Members of
I have installed the Apache 2.0.61, PHP 5.2.4 and PostgreSQL 8.1 on
my local computer.
All three software were successfully tested. I changed
“;extension=php_pgsql.dll” to
“extension=php_pgsql.dll”in the php.ini file in order to enable
PostgreSQL in PHP.
The problem comes when I try to connect to the PostgreSQL Database
using php function pg_connect
$dbconn = pg_connect("host=".PG_HOST_NAME." port=".PG_PORT_NUM."
dbname=".PG_DB_NAME." user=".PG_USER." password=".PG_PASSWORD);
All the arguments in the function pg_connect() are defined.
Unfortunately I am getting the Fatal error: “Call to undefined
function pg_connect() in C:\Web\html\Staff_Management\example1.php
on line 23”
C:\Web\html is my document root.
What could be the possible mistake?
Anyone to assist me!
Best regards,
James Kitambara
Did you re-start apache after you made the changes to the php.ini file?
Ries
Re: [SQL]
On Nov 5, 2008, at 8:42 AM, Hemant Patel wrote: Hello Everyone, I have a array of ids from my search result and now I want to fetch the details from the database. Now IN query is ruined my sort order.So should I go for Union All? IN doesn't ruin your sort order really You need to think in sets... Is there any other method to fetch the data..? Can't you not directly join against you other table to fetch the result directly?? Show us the SQL you are working with + tables would help aswell. Please help me in this…. Thanks in advance… With Regards, Hemant Patel
Re: [SQL] Left Join Question
On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote: While looking through our data layer code today, I ran across this query: SELECT tasks.*, clients.FirstName, clients.LastName, clients.MiddleInitial, iteminfo.CreatedBy, iteminfo.StationId, iteminfo.CreatedDate, changelog.LastModified, changelog.LastModifiedBy, changelog.LastModifiedAt, ticklers.Due, ticklers.Reminder FROM tasks LEFT JOIN clients ON tasks.ClientId = clients.ClientId LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId LEFT JOIN changelog ON tasks.Id = changelog.ItemId LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId WHERE tasks.Id = '123456'; (I've cleaned it up so it's easier to read.) The basic data structure is that we have a todo list that contains a list of tasks which may or may not be associated with clients, items, log entries, or ticklers (scheduled reminders). The query works as intended: it returns a result-set with all the necessary data to display in the todo list. The performance is not a major concern, although it can be slow for large lists. Since it works, my question is really more about principles: Given that each of the tables in question will contain tens of thousands of rows, is a nested join really the best way to approach this? Thanks! Ryan To answer this better we need to know the exact relations between each table. For example, does each task have 0 or more clients, or 1 or more clients? If it's 1 or more, then you can properly use a JOIN instead of a LEFT JOIN, this would make the operation faster I believe. I hope I did explain myself correctly. Ries
Re: [SQL] Sequence and nextval problem
On Nov 24, 2008, at 2:12 PM, Tk421 wrote:
Hello everybody.
I've got an vb aplication that uses an Access database. I'm trying
to convert the database to postgres. The conversion was done ok, but
i've got a little problem that i don't know how to solve. Let's see
if anyone can help me.
The conversion from access database to postgres worked fine.
Everithing it's ok. But now, when i use my database i've found a
problem with sequences. In the conversion, the "autonumeric" fields
from access have been converted to sequences, everithing ok in a
first view. The problem comes because the autonumeric fields in
access always return the last value of the table +1, but postgres
no. Postgres returns "lost" (i don't know how to call them) values.
An example.
This is an example of a table:
code | description
- |
1 | desc 1
2 | desc 2
6 | desc 6
7 | desc 7
In access if i execute "INSERT INTO table (description) VALUES
('desc 8'), the result row is 8 | desc 8
But in postgres the same query te result row is 3 | desc 8
My question is, can i do something to make ANY sequence to take
the last value from his associated table, and not a "lost" value?
Thank you very much
This sounds like if the start of the sequence is set incorrectly:
Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);
btw, you should also not expect a specific value from the sequence
except that you will always get the next value from the sequence.
it's also generally a bad idea to do select max(someid)+1 from table.
The whole concept of a sequence is thus much better.
Ries
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] wired behaviour
On Nov 28, 2008, at 8:51 AM, Lutz Steinborn wrote: Hello, I have an problem understanding a simple query: For example I have the following: select count(*) from foo; gives me 1000 select count(*) from foo where bar = 'Mr Spock'; gives me 5 select count(*) from foo where NOT bar = 'Mr Spock'; gives me 857 Why I'm not getting 955 for the last query ? Any idea ? Do you happen to have any NULL values on the field bar?? Ries -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] "union" vs. left join
On Mar 3, 2009, at 3:26 PM, Emi Lu wrote: Good morning, Would it be possible to know that whether "union" is quicker than "left join" in a general sense? Queries: (1) union select a.*, b.value from a left join b union select a.*, c.value from a left join c (2) left join select distinct a.*, case when ... then b.value else c.value from a left join b left join c Will (1) be more efficient than (2) or vice versa? Or it really depends on data Thanks a lot! Lu Ying I would say try it in your situation... I never did any real worl testing, but usually I grab a left join before anything else. I would suspect that the planner knows a bit better how to optimize the left join version. Ries -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries On Mar 25, 2009, at 2:20 PM, DM wrote: Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
On Apr 26, 2009, at 7:21 PM, landsharkdaddy wrote:
I have a query that works on SQL Server to return customers that
contain the
string entered by the user by accepting parameters and using the LIKE
keyword. I would like to move this to postgreSQL but I'm just not
sure how
to get it done. This is the query
SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%';
This works great on SQL Server but not on postgreSQL. Any help would
be
appreciated.
Why didn't it work? Any error message), or no result?
Are you expecting case-insensitivity (try ILIKE)
regards, Ries van Twisk
-----
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: r...@vantwisk.nl
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196
SIP: +1-747-690-5133
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
