Re: [GENERAL] Installation of postgresql-6.5.1 data missing ?
At 14:37 +0300 on 24/07/1999, Jesper K. Pedersen wrote: I have been using a much older version of postgresql and decided to "trash" it and go to the 6.5.1beta1 All installation of the server/client/devel went fine - but i am unable to install the default database - postgresql-data-X.X.X as i cant find any for 6.5.1 Anyone have any help that will get me through this ? I cant find any help in the doc's for this problem - so I am sure its just that there is some basic "thing" i overlooked. Were you installing from an RPM perhaps? Common PostgreSQL RPMs were somehow separated into three packages, though for the life of me I can't understand why the data package is needed. You should be able to create the default database using initdb - unless they didn't RPM the initdb executable? Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Re: [GENERAL] Installation of postgresql-6.5.1 data missing ?
--- Herouth Maoz [EMAIL PROTECTED] wrote: At 14:37 +0300 on 24/07/1999, Jesper K. Pedersen wrote: I have been using a much older version of postgresql and decided to "trash" it and go to the 6.5.1beta1 All installation of the server/client/devel went fine - but i am unable to install the default database - postgresql-data-X.X.X as i cant find any for 6.5.1 Anyone have any help that will get me through this ? I cant find any help in the doc's for this problem - so I am sure its just that there is some basic "thing" i overlooked. Were you installing from an RPM perhaps? Common PostgreSQL RPMs were somehow separated into three packages, though for the life of me I can't understand why the data package is needed. You should be able to create the default database using initdb - unless they didn't RPM the initdb executable? Herouth I believe he's referring to the RPMS packaged by Thomas Lockhart. The RedHat RPMS were composed of: postgresql (The server component, postmaster, etc.) postgresql-clients (psql, libpq, etc.) postgresql-devel (headers, etc.) postgresql-data (inital database structure) Thomas Lockhart's RPM packages were composed of: postgresql (Common files, documentation) postgresql-devel (headers, etc.) postgresql-server (postmaster, initdb, etc.) And various interface RPMS for java, odbc, tcl, etc. The RedHat RPMS have a postgresql-data RPM so that you can install an initial database structure from the installation CD and never have to run an initdb by hand to automate the process. Since I assume Jesper installed Thomas Lockhart's RPMS's all he has to do, as user postgres is: initdb --pglib=/usr/lib/pgsql --pgdata=/var/lib/pgsql and he should be good to go. Hope that helps, Mike Mascari ([EMAIL PROTECTED]) _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[GENERAL] speeding up INTERSECT/EXCEPT
I've searched the archives and docs without finding any help on this. I want to use: select * from rel1 except select * from rel2; where rel1 and and rel2 each currently have about 2000 records and are likely to grow to twice that size or more. The query works but takes inordinately long. Looking at the query plan, it seems that the statement is being executed using a nested scan, so the query is taking time proportional to the product of the sizes of the two relations. The result set will usually be a few hundred records. My question: are there any indices I could define on the relations that would speed up the query? Since noting the problem, I've added an index on one of the fields (the same for both relations). Another index covers 7 of the fields in both relations (7 because that is the maximum for an index). Creating these indices made no difference in the query plan nor in the execution time of the query. Should I pursue this approach further or do the differencing outside of the database? -- Carl Hauser -- Xerox Palo Alto Research Center
Re: [GENERAL] speeding up INTERSECT/EXCEPT
--- Carl Hauser [EMAIL PROTECTED] wrote: I've searched the archives and docs without finding any help on this. I want to use: select * from rel1 except select * from rel2; where rel1 and and rel2 each currently have about 2000 records and are likely to grow to twice that size or more. The query works but takes inordinately long. Looking at the query plan, it seems that the statement is being executed using a nested scan, so the query is taking time proportional to the product of the sizes of the two relations. The result set will usually be a few hundred records. My question: are there any indices I could define on the relations that would speed up the query? Since noting the problem, I've added an index on one of the fields (the same for both relations). Another index covers 7 of the fields in both relations (7 because that is the maximum for an index). Creating these indices made no difference in the query plan nor in the execution time of the query. Should I pursue this approach further or do the differencing outside of the database? -- Carl Hauser -- Xerox Palo Alto Research Center If there is a primary key on each table that could be used as the oprands in a comparison operator, then I think you might find a correlated subquery significantly faster than EXCEPT using the EXISTS clause. For example, I have two tables, supplies and deletedsupplies, with the primary key being the supply field: SELECT * FROM supplies WHERE NOT EXISTS (SELECT deletedsupplies.supply FROM deletedsupplies WHERE deletedsupplies.supply = supplies.supply) Because I have an index on the deletedsupplies' "supply" field, the plan performs a Sequential scan on the supplies table, and, for each record, performs an Index scan for the corresponding record in the deletedsupplies table. So, for your example, the query might be: SELECT * FROM rel1 WHERE NOT EXISTS (SELECT * FROM rel2 WHERE rel2.key = re1l.key) with indexes on rel1.key and rel2.key. If the ENTIRE record must match before it is excluded then you must use a multi-field index and write the query like: CREATE INDEX k_rel2 ON rel2(field1,field2,...) SELECT * FROM rel1 WHERE NOT EXISTS (SELECT * FROM rel2 WHERE (rel2.field1,rel2.field2,...) = (rel1.field1,rel1.field2,...)) I have found that the EXISTS implementation is usually good enough to yield the same functionality (with superior performance) as intersect, except, and as a poor-man's substitue for outer-joins... Hope that helps, Mike Mascari ([EMAIL PROTECTED]) _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [GENERAL] pg-dump -- primary Key
I understand this, but does it set the indisprimary flag in the pg_index table? The reason I ask is because I am writing a web based app to administer a pgsql database and am attempting to keep track of the indices/keys. This is the property I am looking at determine whether the index is a primary key. Is there a better property or flag to examine for this purpose? -Dan On Sun, Jul 25, 1999 at 03:13:39PM +1000, Chris Bitmead wrote: Dan Wilson wrote: Yes, I am aware that the primary key does not really mean anything except implicitly making it a unique key, but it's supposed to be there for compatibility and it's not even in the dump. Someone mentioned recently that primary key enforces nulls as unique whereas unique index doesn't. Actually, I belive it enforces NOT NULL on primary keys, which it also dumps in the pg_dump output. Ross
[GENERAL] escaping wildcard chars
I am trying to pull in the different types allowed by postgres for a create function statement and am having problems with the types that start with an underscore (_). Because the underscore is the wildcard for a single character, I cannot perform the following query correctly. SELECT typname from pg_type WHERE NOT LIKE '_%' It gives me an empty set. Is there any way to escape the underscore. I tried to use '\_%', but that didn't help. Any suggestions? -Dan
