Re: [GENERAL] Large Objects
Joshua D. Drake wrote: Frank D. Engel, Jr. wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'd advise use of BYTEA as well. It's much simpler to work with than the OIDs, and has simpler semantics. You do need to escape data before handing it to the query string, and handle escaped results (see the docs), but overall much nicer than working with OIDs. BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs? Intresting. What is the size when bytea become inafective ? Currently i keep all my products images in bytea record. is it practical ? how slower is it then accessing an image on a file system ( like ext3 ) ? Cheers pg_largeobject is more efficient than BYTEA for larger binaries. Sincerely, Joshua D. Drake -- -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Cel: 972-52-8555757 Fax: 972-4-6990098 http://www.canaan.net.il -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ECPG Segfaulting on EXEC SQL connect
On Tue, Dec 28, 2004 at 10:16:04PM -, John Smith wrote: I'm trying to convert a series of C programs written originally using Informix ESQL to use Postgres' ECPG. ... 575 EXEC SQL connect to pdev_changename; ... I'm using Postgres 8.0.0rc1 on Redhat 9 (kernel 2.4.20-31.9). The same thing happens on fedora core 3, and using Postgres 7.4.6-1.FC3-1. Could you please try rc3? I did fix some segfaults in the connect statement. However, I'm not sure you hit the same bug, actually I expect it to be a different one. The ability to define variables of type timestamp etc. is so useful, so I really want to keep using -C INFORMIX if I can. You can use the datatypes without informix mode. You just have to include the pgtypes_*.h header files yourself. Can anyone help shed any light on this? I will try if you could send me an example to reproduce the problem. As you said it does not happen on a small self written test case. Maybe you can send me one of your source files stripped down to just connect. 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 8: explain analyze is your friend
Re: [GENERAL] Large Objects
On Sat, Jan 01, 2005 at 01:28:04PM +0300, Michael Ben-Nes wrote: Joshua D. Drake wrote: Frank D. Engel, Jr. wrote: I'd advise use of BYTEA as well. It's much simpler to work with than the OIDs, and has simpler semantics. You do need to escape data before handing it to the query string, and handle escaped results (see the docs), but overall much nicer than working with OIDs. BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs? Intresting. What is the size when bytea become inafective ? I don't think it's so much a matter of effectiveness, it makes no difference at all in storage space. The issue is that if you store it in a field, accessing it becomes an all or nothing affair, which means if it's a 100Mb object, it's all going to be accessed whenever you ask for it. OTOH, large objects have lo_read/write/seek meaning you can access small parts at a time. So I imagine if you're storing large PDF files and all you're doing is dumping them to a client when they ask, it doesn't matter. But if the objects have structure and you might be interested in looking inside them without pulling the whole object down, the LO interface is better suited. When you delete a row, the object contained in it goes away too. Large Objects have a lifecycle outside of normal table values, and so may need separate managing... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpGn1cJHmA6x.pgp Description: PGP signature
Re: [GENERAL] Large Objects
Intresting. What is the size when bytea become inafective ? Currently i keep all my products images in bytea record. is it practical ? Well I am going to make the assumption that you product images are small... sub 100k or something. Bytea is just fine for that. The problem is when the binary you want to store is 50 megs. When you access that file you will be using 50 megs of ram to do so. Large Objects don't work that way, you don't have the memory overhead. So it really depends on what you want to store. how slower is it then accessing an image on a file system ( like ext3 ) ? Well that would be an interesting test. Ext3 is very slow. I would assume that Ext3 would be faster just because of the database overhead. However you gain from having the images in the database for flexibility and manageability. Sincerely, Joshua D. Drake Cheers pg_largeobject is more efficient than BYTEA for larger binaries. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(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] ECPG Segfaulting on EXEC SQL connect
Hi Michael, I'll try and get a nice small pared-down source for you to play with that demonstrates the problem. Once I get that, I could certainly try rc3, although I was hoping to wait for the RPM... John. -Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: 01 January 2005 15:08 To: John Smith Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ECPG Segfaulting on EXEC SQL connect On Tue, Dec 28, 2004 at 10:16:04PM -, John Smith wrote: I'm trying to convert a series of C programs written originally using Informix ESQL to use Postgres' ECPG. ... 575 EXEC SQL connect to pdev_changename; ... I'm using Postgres 8.0.0rc1 on Redhat 9 (kernel 2.4.20-31.9). The same thing happens on fedora core 3, and using Postgres 7.4.6-1.FC3-1. Could you please try rc3? I did fix some segfaults in the connect statement. However, I'm not sure you hit the same bug, actually I expect it to be a different one. The ability to define variables of type timestamp etc. is so useful, so I really want to keep using -C INFORMIX if I can. You can use the datatypes without informix mode. You just have to include the pgtypes_*.h header files yourself. Can anyone help shed any light on this? I will try if you could send me an example to reproduce the problem. As you said it does not happen on a small self written test case. Maybe you can send me one of your source files stripped down to just connect. 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 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] many similar indexbased selects are extremely slow
psql8:
I use a bigger psql-table to store information and keep an id-value of
each row in memory of my application for faster access.
My applications is able to calculate a list of needed id's in very short
time and then wants to retrieve all rows corresponding to this id's.
So in fact I perform a lot of operations like:
select field1,field2,field3 from mytable where id=XX;
There is a index on the id-field and the id-field is of type OID, so
everything should be quite fast. Unfortunately it is not.
On a 2GHz-machine with 1GB RAM it takes approx. 10seconds to fetch 1
rows. In this testscenario I only fetch the OID and no other col.
I dont understand this. Am I expecting far to much? Is 10seconds for the
retrieval of 1 OIDs a fine value? I want it to be less than one
second and from my experience with postgres this operation is extremely
slow compared to the impressive speed of most other operations.
I also tried to use the IN-operator, which is much more slower. Is there
any other way to speed up things? I can order the list of id's to
retrieve in my application if there is a way to tell psql not to search
the whole index every time but somehow do better.
If it is of any interest, here is the table. The id is stored as id_artikel.
Table public.artikelindex
Column |Type |
Modifiers
---+-+
autor_artikel | text|
titel_artikel | text|
jahrgang_zeitschrift | integer |
jahr_zeitschrift | character varying(20) |
heftnummer| character varying(30) |
seitenzahl_artikel| character varying(30) |
bemerkungen_artikel | text|
deskriptoren_alt | text|
deskriptoren_neu | text|
personennamen_artikel | text|
orte_artikel | text|
id_artikel| oid |
id_titel | oid |
cdate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
udate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
uid | oid |
gid | oid |
mod | boolean |
Indexes:
id_artikel_idx btree (id_artikel)
id_titel_idx btree (id_titel)
idx_artikelindeax_autor btree (autor_artikel)
idx_artikelindex_fingerprint btree (id_artikel)
idx_artikelindex_jahr btree (jahrgang_zeitschrift)
idx_artikelindex_jahrgang btree (jahr_zeitschrift)
idx_artikelindex_zeitschrift btree (id_titel)
Rules:
delete_val AS
ON DELETE TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
insert_val AS
ON INSERT TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
update_val AS
ON UPDATE TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
And more: here is my retrieving program. I use perl and the DBI-module
and the following code-snip
--
my $sth=$dbh-prepare(
'select OID from artikelindex where id_artikel=?');
foreach (@id) {
my $ret=$sth-execute($_);
my $x=$sth-fetchrow_arrayref;
}
-
thnx a lot for any idea,
peter
--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Function Parameters
Hi, Compliments of the season. I tried to create a function with about 60 input parameters and got an error message that a function cannot take more than 32 parameters. Is there a way around this? Or Am I in error? Best regards. Tope -- ---(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] many similar indexbased selects are extremely slow
Well, first it would be a good idea to see what postgres is actually
doing. Send the output of:
= EXPLAIN ANALYZE SELECT OID FROM atrikelindex WHERE id_artikel=?;
(where ? is replaced by some id value)
It will either say index lookup or sequential scan since it's just a
select from one table. That will tell you whether the index is being
used or not.
Next, if it is doing something that seems unreasonable, try doing:
= VACUUM ANALYZE artikelindex;
And we should also look at the EXPLAIN output on the IN query version
like:
= EXPLAIN ANALYZE SELECT OID FROM artikelindex WHERE id_artikel IN
(?,?,...,?);
Although I'm not sure what kind of performance to expect if you have 10k
values in that list.
Also, what version are you using? And how many rows in the table? Is it
regularly vacuumed? Are there a lot of updates/deletes going to that
table?
I suspect that the fastest way that postgres can get you those rows
would be the IN query on a recent version of postgresql. It may choose a
sequential scan, which likely would be good since one seq scan will
hopefully take less than 10 seconds.
Regards,
Jeff Davis
On Sun, 2005-01-02 at 00:52 +0100, peter pilsl wrote:
psql8:
I use a bigger psql-table to store information and keep an id-value of
each row in memory of my application for faster access.
My applications is able to calculate a list of needed id's in very short
time and then wants to retrieve all rows corresponding to this id's.
So in fact I perform a lot of operations like:
select field1,field2,field3 from mytable where id=XX;
There is a index on the id-field and the id-field is of type OID, so
everything should be quite fast. Unfortunately it is not.
On a 2GHz-machine with 1GB RAM it takes approx. 10seconds to fetch 1
rows. In this testscenario I only fetch the OID and no other col.
I dont understand this. Am I expecting far to much? Is 10seconds for the
retrieval of 1 OIDs a fine value? I want it to be less than one
second and from my experience with postgres this operation is extremely
slow compared to the impressive speed of most other operations.
I also tried to use the IN-operator, which is much more slower. Is there
any other way to speed up things? I can order the list of id's to
retrieve in my application if there is a way to tell psql not to search
the whole index every time but somehow do better.
If it is of any interest, here is the table. The id is stored as id_artikel.
Table public.artikelindex
Column |Type |
Modifiers
---+-+
autor_artikel | text|
titel_artikel | text|
jahrgang_zeitschrift | integer |
jahr_zeitschrift | character varying(20) |
heftnummer| character varying(30) |
seitenzahl_artikel| character varying(30) |
bemerkungen_artikel | text|
deskriptoren_alt | text|
deskriptoren_neu | text|
personennamen_artikel | text|
orte_artikel | text|
id_artikel| oid |
id_titel | oid |
cdate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
udate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
uid | oid |
gid | oid |
mod | boolean |
Indexes:
id_artikel_idx btree (id_artikel)
id_titel_idx btree (id_titel)
idx_artikelindeax_autor btree (autor_artikel)
idx_artikelindex_fingerprint btree (id_artikel)
idx_artikelindex_jahr btree (jahrgang_zeitschrift)
idx_artikelindex_jahrgang btree (jahr_zeitschrift)
idx_artikelindex_zeitschrift btree (id_titel)
Rules:
delete_val AS
ON DELETE TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
insert_val AS
ON INSERT TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
update_val AS
ON UPDATE TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
And more: here is my retrieving program. I use perl and the DBI-module
and the following code-snip
--
my $sth=$dbh-prepare(
'select OID from artikelindex where id_artikel=?');
foreach (@id) {
my
Re: [GENERAL] many similar indexbased selects are extremely slow
For what it's worth, I put 100k rows into a table in 8.0beta5, and
selected 10k at a time. When doing each SELECT seperately using the
index, it took about 2.5s to do 10k SELECTs. When using an IN query
containing all the id's that I wanted, it took less than a second.
Jeff
On Sat, 2005-01-01 at 17:34 -0800, Jeff Davis wrote:
Well, first it would be a good idea to see what postgres is actually
doing. Send the output of:
= EXPLAIN ANALYZE SELECT OID FROM atrikelindex WHERE id_artikel=?;
(where ? is replaced by some id value)
It will either say index lookup or sequential scan since it's just a
select from one table. That will tell you whether the index is being
used or not.
Next, if it is doing something that seems unreasonable, try doing:
= VACUUM ANALYZE artikelindex;
And we should also look at the EXPLAIN output on the IN query version
like:
= EXPLAIN ANALYZE SELECT OID FROM artikelindex WHERE id_artikel IN
(?,?,...,?);
Although I'm not sure what kind of performance to expect if you have 10k
values in that list.
Also, what version are you using? And how many rows in the table? Is it
regularly vacuumed? Are there a lot of updates/deletes going to that
table?
I suspect that the fastest way that postgres can get you those rows
would be the IN query on a recent version of postgresql. It may choose a
sequential scan, which likely would be good since one seq scan will
hopefully take less than 10 seconds.
Regards,
Jeff Davis
On Sun, 2005-01-02 at 00:52 +0100, peter pilsl wrote:
psql8:
I use a bigger psql-table to store information and keep an id-value of
each row in memory of my application for faster access.
My applications is able to calculate a list of needed id's in very short
time and then wants to retrieve all rows corresponding to this id's.
So in fact I perform a lot of operations like:
select field1,field2,field3 from mytable where id=XX;
There is a index on the id-field and the id-field is of type OID, so
everything should be quite fast. Unfortunately it is not.
On a 2GHz-machine with 1GB RAM it takes approx. 10seconds to fetch 1
rows. In this testscenario I only fetch the OID and no other col.
I dont understand this. Am I expecting far to much? Is 10seconds for the
retrieval of 1 OIDs a fine value? I want it to be less than one
second and from my experience with postgres this operation is extremely
slow compared to the impressive speed of most other operations.
I also tried to use the IN-operator, which is much more slower. Is there
any other way to speed up things? I can order the list of id's to
retrieve in my application if there is a way to tell psql not to search
the whole index every time but somehow do better.
If it is of any interest, here is the table. The id is stored as id_artikel.
Table public.artikelindex
Column |Type |
Modifiers
---+-+
autor_artikel | text|
titel_artikel | text|
jahrgang_zeitschrift | integer |
jahr_zeitschrift | character varying(20) |
heftnummer| character varying(30) |
seitenzahl_artikel| character varying(30) |
bemerkungen_artikel | text|
deskriptoren_alt | text|
deskriptoren_neu | text|
personennamen_artikel | text|
orte_artikel | text|
id_artikel| oid |
id_titel | oid |
cdate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
udate | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
uid | oid |
gid | oid |
mod | boolean |
Indexes:
id_artikel_idx btree (id_artikel)
id_titel_idx btree (id_titel)
idx_artikelindeax_autor btree (autor_artikel)
idx_artikelindex_fingerprint btree (id_artikel)
idx_artikelindex_jahr btree (jahrgang_zeitschrift)
idx_artikelindex_jahrgang btree (jahr_zeitschrift)
idx_artikelindex_zeitschrift btree (id_titel)
Rules:
delete_val AS
ON DELETE TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character varying::text
insert_val AS
ON INSERT TO artikelindex DO UPDATE counter SET val = counter.val + 1
WHERE counter.tab::text = 'artikelindex'::character
Re: [GENERAL] disabling OIDs?
On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote: OK, thanks. So is there any real benefit in doing this in a generic (non-dspam) sense, or is it just a hack that wouldn't be noticable? Any risks or potential problems down the line? I'd just like to add that some 3rd party applications/interfaces make use of OIDs, as a convenient id to use if there is no primary key (or if the 3rd party software doesn't take the time to find the primary key). One might argue that those 3rd party applications/interfaces are broken, but you still might want to keep OIDs around in case you have a use for one of those pieces of software. Regards, Jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Question on a select
Hi all, This is my first post here so please let me know if I miss any list guidelines. :) I was hoping to get some help, advice or pointers to an answer for a somewhat odd (to me at least) SELECT. What I am trying to do is select that values from one table where matching values do not exist in another table. For example: Let's say 'table_a' has the columns 'a_name, a_type, a_dir, a_others' and 'table_b' has the columns 'b_name, b_type, b_dir, b_others' where 'others' are columns unique to each table. What I need to do is select all the values in 'a_name, a_type, a_dir' from 'table_a' where there is no matching entries in table_b's 'b_name, b_type, b_dir'. I know I could do something like: SELECT a_name, a_type, a_dir FROM table_a; and then loop through all the returned values and for each do a matching select from 'table_b' and use my program to catch the ones not in 'table_b'. This is not very efficient though and I will be searching through tables that could have several hundred thousand entries so the inefficiency would be amplified. Is there some way to use a join or something similar to do this? Thank you all! Madison ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question on a select
There are several ways. I am making the simplifying assumption that name, type and dir cannot be NULL in either table. If they are the query is a little more complicated. The following are a couple of many techniques. SELECT a.a_name , a.a_type , a.a_dir FROM a_table a WHERE NOT EXISTS ( SELECT NULL FROM b_table b WHERE b.b_name = a.a_name AND b.b_type= a.a_type AND b.b_dir = a.a_dir ) SELECT a.a_name , a.a_type , a.a_dir FROM a_table a LEFT JOIN b_table b ON a.a_table= b.b_table AND a.a_type = b.b_type AND a.a_dir = b.b_type WHERE b.b_table IS NULL // assumes that b.b_table is a not null column. Let's say that dir could be null and dir is a string, then (assuming that dir can never be 'xyz') you could say something like COALESCE(a.a_dir,'xyz') = COALESCE(b.b_dir,'xyz') Since NULL never equal NULL, if you want NULL in one table to match a NULL in another table, you need to change it to something not NULL. However this depends on what you want in your application. Queries like this are used often to check the integrity of your data. Examples of this are 1) What orders don't have order items? 2) What books have no authors? etc. - Original Message - From: Madison Kelly [EMAIL PROTECTED] To: PgSQL General List pgsql-general@postgresql.org Sent: Saturday, January 01, 2005 7:32 PM Subject: [GENERAL] Question on a select Hi all, This is my first post here so please let me know if I miss any list guidelines. :) I was hoping to get some help, advice or pointers to an answer for a somewhat odd (to me at least) SELECT. What I am trying to do is select that values from one table where matching values do not exist in another table. For example: Let's say 'table_a' has the columns 'a_name, a_type, a_dir, a_others' and 'table_b' has the columns 'b_name, b_type, b_dir, b_others' where 'others' are columns unique to each table. What I need to do is select all the values in 'a_name, a_type, a_dir' from 'table_a' where there is no matching entries in table_b's 'b_name, b_type, b_dir'. I know I could do something like: SELECT a_name, a_type, a_dir FROM table_a; and then loop through all the returned values and for each do a matching select from 'table_b' and use my program to catch the ones not in 'table_b'. This is not very efficient though and I will be searching through tables that could have several hundred thousand entries so the inefficiency would be amplified. Is there some way to use a join or something similar to do this? Thank you all! Madison ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Question on a select
On Sat, Jan 01, 2005 at 22:32:17 -0500, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, This is my first post here so please let me know if I miss any list guidelines. :) I was hoping to get some help, advice or pointers to an answer for a somewhat odd (to me at least) SELECT. What I am trying to do is select that values from one table where matching values do not exist in another table. For example: Let's say 'table_a' has the columns 'a_name, a_type, a_dir, a_others' and 'table_b' has the columns 'b_name, b_type, b_dir, b_others' where 'others' are columns unique to each table. What I need to do is select all the values in 'a_name, a_type, a_dir' from 'table_a' where there is no matching entries in table_b's 'b_name, b_type, b_dir'. SELECT a_name, a_type, a_dir, a_others FROM table_a WHERE a_name, a_type, a_dir NOT IN ( SELECT b_name, b_type, b_dir FROM table_b) ; In pre 7.4 versions or if there are NULLs in the key columns for table_b then you probably want to use NOT EXISTS (with a moodified WHERE clause) instead on NOT IN. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Function Parameters
On Sun, Jan 02, 2005 at 01:31:22AM +, Oluwatope Akinniyi wrote: I tried to create a function with about 60 input parameters and got an error message that a function cannot take more than 32 parameters. What's the function's purpose? Why does it need so many arguments? You might be able to get around the limitation with a composite type, but maybe there's a different way to do what you want. Another possibility would be to rebuild PostgreSQL and change the limit. I don't know what the implications are other than what the comment in the code says: There is no specific upper limit, although large values will waste system-table space and processing time and Changing these requires an initdb. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Can't change password?
Pierre-Frédéric Caillaud wrote: Is your authentication set to Trust in the config files ? On Fri, 31 Dec 2004 08:45:34 -0600, Eric Scott [EMAIL PROTECTED] wrote: Heya; I have PostGreSQL 7.3.4 on Mandrake Linux 9.2. For some reason Webmin, when I tell it to change the password of a pgsql user, acts like it's working, but still leaves requires password set to no. I've tried running psql and executing ALTER USER [username] WITH PASSWORD '[password]' with the same results. It gives no error messages... but apparently the user still has no password. I'm writing a C# program that accesses the database, and authentication fails basically no matter what I try. Any help? I'm stumped. Thanx in advance, Sigma Yes. I have the same pg_hba.conf setup for host permissions as on my Fedora 2 box, which doesn't have the same problem. Help! Sigma -- Registered Linux Freak #: 366,862 Red Hat Linux Fedora 2 x86/Athlon (Primary Workstation, this comp) Debian GNU/Linux 3.1 PPC/604e (Secondary Workstation) Mandrake Linux 9.2 x86/AthlonXP (Server) Spyro's Linux port ARM/XScale (PDA) 'For the eyes of the Lord range throughout the earth to strengthen those whose hearts are fully commited to him.' 2 Chronicles 16:9a ---(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] Question on a select
Vincent Hikida wrote: There are several ways. I am making the simplifying assumption that name, type and dir cannot be NULL in either table. If they are the query is a little more complicated. The following are a couple of many techniques. SELECT a.a_name , a.a_type , a.a_dir FROM a_table a WHERE NOT EXISTS ( SELECT NULL FROM b_table b WHERE b.b_name = a.a_name AND b.b_type= a.a_type AND b.b_dir = a.a_dir ) SELECT a.a_name , a.a_type , a.a_dir FROM a_table a LEFT JOIN b_table b ON a.a_table= b.b_table AND a.a_type = b.b_type AND a.a_dir = b.b_type WHERE b.b_table IS NULL // assumes that b.b_table is a not null column. Let's say that dir could be null and dir is a string, then (assuming that dir can never be 'xyz') you could say something like COALESCE(a.a_dir,'xyz') = COALESCE(b.b_dir,'xyz') Since NULL never equal NULL, if you want NULL in one table to match a NULL in another table, you need to change it to something not NULL. However this depends on what you want in your application. Queries like this are used often to check the integrity of your data. Examples of this are 1) What orders don't have order items? 2) What books have no authors? etc. This is wonderful! Thank you for responding so quickly! :) I should mention that I am still very much learning so I apologise in advance if I miss the obvious. ^.^; They are all 'not null' and I am trying to do exactly the kind of task you described. I tried the first example on my DB and got a syntax error: tle-bu= SELECT a.file_name, a.file_parent_dir, a.file_type FROM file_info_1 a WHERE NOT EXIST (SELECT NULL FROM file_set_1 b WHERE b.fs_name=a.file_name, b.fs_parent_dir=a.file_parent_dir, b.fs_type=a.file_type); ERROR: syntax error at or near SELECT at character 88 The second example you gave seems to work perfectly (as I will show near the bottom of this email). What are the benefits and down sides of each method? Is there a simple reason why the first method failed (probably a typo I imagine...)? A little more specifics about my DB: 'file_info_1' and 'file_set_1' are two tables I use to store information of files and directories (this is a backup program). 'file_info_#' stores dynamic info like file size, owner and such. This table is dropped and recreated before a new scan of the partition creates a mass 'COPY' load (the '_1' indicates the first partition). 'file_set_#' stores static information such as has the file been selected for backup which is why I keep it in a separate table. I want to run this select first to write entries for newly added files and directories (the values will match the file's parent) and then again in reverse to remove from 'file_set_#' entries that no longer exist on the partition. If it helps, here is the structure of the tables: CREATE TABLE file_info_ID ( file_acc_time bigint not null, file_group_name varchar(255)not null, file_group_uid int not null, file_mod_time bigint not null, file_name varchar(255)not null, file_parent_dir varchar(255)not null, file_perm varchar(10) not null, file_size bigint not null, file_type varchar(2) not nulldefault 'f', file_user_name varchar(255)not null, file_user_uid int not null ); CREATE TABLE file_set_# ( fs_backup boolean not nulldefault 't', fs_display boolean not nulldefault 'f', fs_name varchar(255)not null, fs_parent_dir varchar(255)not null, fs_restore boolean not nulldefault 'f', fs_type varchar(2) not nulldefault 'f' ); And here is some sample data that I have to work with (yes, it's a win2k partition... I use it to test other aspects of my program and, if I blow it away, I won't be upset. ^.^; All of this is being done on a Fedora Core 3 install in case it makes a difference): tle-bu= SELECT file_type, file_parent_dir, file_name FROM file_info_1 WHERE file_parent_dir='/' LIMIT 30; file_type | file_parent_dir | file_name ---+-+ d | / | . d | / | downloads d | / | Documents and Settings d | / | Program Files f | / | io.sys f | / |
Re: [GENERAL] Question on a select
Bruno Wolff III wrote: SELECT a_name, a_type, a_dir, a_others FROM table_a WHERE a_name, a_type, a_dir NOT IN ( SELECT b_name, b_type, b_dir FROM table_b) ; In pre 7.4 versions or if there are NULLs in the key columns for table_b then you probably want to use NOT EXISTS (with a moodified WHERE clause) instead on NOT IN. Hi Bruno, Thank you for replying! I tried your example but I am getting a syntax error: tle-bu= SELECT file_name, file_parent_dir, file_type FROM file_info_1 WHERE file_name, file_parent_dir, file_type NOT IN (SELECT fs_name, fs_parent_dir, fs_type FROM file_set_1); ERROR: syntax error at or near , at character 78 I just replied to Vincent's post with a lot of detail on what I am trying to do and how my DB is constructed. His second example worked but I also had a syntax error on his first example. This program will be working with very large data sets so I would love to get your method working so that I could try benchmarking them to see which, in my application, would be most effective. Thank you very kindly for helping! Madison ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Question on a select
On Sun, Jan 02, 2005 at 01:58:20 -0500, Madison Kelly [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: SELECT a_name, a_type, a_dir, a_others FROM table_a WHERE a_name, a_type, a_dir NOT IN ( SELECT b_name, b_type, b_dir FROM table_b) ; In pre 7.4 versions or if there are NULLs in the key columns for table_b then you probably want to use NOT EXISTS (with a moodified WHERE clause) instead on NOT IN. Hi Bruno, Thank you for replying! I tried your example but I am getting a syntax error: tle-bu= SELECT file_name, file_parent_dir, file_type FROM file_info_1 WHERE file_name, file_parent_dir, file_type NOT IN (SELECT fs_name, fs_parent_dir, fs_type FROM file_set_1); ERROR: syntax error at or near , at character 78 There should be parenthesis around the list to test. WHERE a_name, a_type, a_dir NOT IN ( should be WHERE (a_name, a_type, a_dir) NOT IN ( I just replied to Vincent's post with a lot of detail on what I am trying to do and how my DB is constructed. His second example worked but I also had a syntax error on his first example. This program will be working with very large data sets so I would love to get your method working so that I could try benchmarking them to see which, in my application, would be most effective. I believe that the NOT IN query should run comparably to the LEFT JOIN example supplied by the other person (at least in recent versions of Postgres). I would expect this to run faster than using NOT EXISTS. You probably want to try all 3. The semantics of the three ways of doing this are not all equivalent if there are NULLs in the data being used to eliminate rows. As you indicated you don't have NULLs this shouldn't be a problem. Another way to write this is using set different (EXCEPT or EXCEPT ALL) using the key fields and then joining back to table a to pick up the other fields. However this will almost certianly be slower than the other methods. ---(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
