[SQL] ODBC Returns 0 records
Hi, I face the following problem: I use PostgreSQL 7.0.3 on RH6.2. When I make a select through ODBC (VB 6, ODBC v.6.50, MDAC 2.6) that contains many text fields per row (i.e. more than 10) there are no records returned to the recordset I use (I use ADO). When I execute the query through psql all records are displayed correctly. Any ideas? Thank you for your help.
[SQL] system catalog info
The HTML programming documentation (e.g. http://www.postgresql.org/devel-corner/docs/programmer/pg-system-catalogs.htm) indicates that more extensive information about the system catalogs can be found in the "Reference Manual". Where can this reference manual be found? Or where can more extensive documentation about the system catalogues be found? -Ron-
[SQL] Optimization recommendations request
Hello, I'm working on an application where I need to design for one table to grow to an extremely large size. I'm already planning to partition the data into multiple tables, and even possibly multiple servers, but even so each table may need to grow to the 10 - 15 million tuple range. This table will be used for a keyed lookup and it is very important that the query return in well under a second. I've done a small test using a dual ppro 200 server with 512 MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i -B 25000"). I used a test table with about 5 million tuples. Details: CREATE TABLE foo( guid varchar(20) not null, ks varchar(20) not null ); --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes -- tried this first -- create index foo_idx1 on foo(guid); -- then tried create index foo_idx1 on foo using HASH (guid); SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; The query currently takes in excess of 40 seconds. I would appreciate any suggestions for optimizing to bring this down substantially. Thanks in advance, Joe Conway
[SQL] left join ?
hi all, I try select purc.trn_no,purcsub.item_no,purcsub.qty,emp_no,emp_na from purc left join purcsub on (purc.trn_no=purcsub.trn_no) left join emp on (purc.emp_no = emp.emp_no) postgresql not run,please help,thank
[SQL] How to debug pgpsql functions
Hi folks, I am getting a strange parser error when I execute a PL/PGSQL function. Q: Is there any way to get more infromation from the parser other than the standard stuff in psql? Q; Is there any support for "debugging" plpgsql functions? Thanks in advance Marcel
Re: [SQL] Optimization recommendations request
Subject: Re: [SQL] Optimization recommendations request > > What does explain show for your query? > I sent this a week ago using the wrong (i.e. not the one I signed up with) reply-to address, so it didn't make it to the list until just now. In the meantime I ran explain and noticed that the index wasn't being used. So I ran vacuum analyze and now I'm getting the expected performance (and explain shows the index being used). If I understand the logged statistics correctly, I'm getting results returned in anywhere from about 3 to 45 ms, depending on cache hit rate. I also repeated my test with 15 million records with similar results. Not bad at all! I am still interested in any generic optimization tips for very large tables. Thanks for taking the time to reply! Joe
Re: [SQL] Optimization recommendations request
What does explain show for your query? On Sat, 23 Dec 2000, Joe Conway wrote: > Hello, > > I'm working on an application where I need to design for one table to grow > to an extremely large size. I'm already planning to partition the data into > multiple tables, and even possibly multiple servers, but even so each table > may need to grow to the 10 - 15 million tuple range. This table will be used > for a keyed lookup and it is very important that the query return in well > under a second. I've done a small test using a dual ppro 200 server with 512 > MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot > of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i -B 25000"). I used a test table > with about 5 million tuples. > > Details: > > CREATE TABLE foo( > guid varchar(20) not null, > ks varchar(20) not null > ); > > --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes > -- tried this first > -- create index foo_idx1 on foo(guid); > -- then tried > create index foo_idx1 on foo using HASH (guid); > > SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; > > The query currently takes in excess of 40 seconds. I would appreciate any > suggestions for optimizing to bring this down substantially.
Re: [SQL] Optimization recommendations request
"Joe Conway" <[EMAIL PROTECTED]> writes: > create index foo_idx1 on foo using HASH (guid); > SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; > The query currently takes in excess of 40 seconds. I would appreciate any > suggestions for optimizing to bring this down substantially. Why are you using a hash index? btree is a lot better for every conceivable purpose. It would also be a good idea to check to make sure that the query is using the index (see EXPLAIN). You didn't mention whether you'd done a VACUUM ANALYZE, so there's a risk the planner will make the wrong choice. regards, tom lane
[SQL] Date/Time problem -(((
Hello I am moving from MySQL to Postgres and while converting my sql code I have a lot of problems with this construction: ($ze is current time - 300 secounds): delete from onlineuser where datum < FROM_UNIXTIME($ze) Is there any Date/Time function in PGSQL? I want as shown in this example delete entries older than 300 secounds. I postet to the list because I have found no solution in the tutorials and search-engine (maybe searched with wrong words?) It would be great if someone could help me / point me to the right way... -- Boris
Re: [SQL] Running a file
Tx, Thomas, "Brett W. McCoy" wrote: > > On Sun, 24 Dec 2000, Thomas SMETS wrote: > > > I'm runnin postgres 7.?? (Last RPM package available from the site). > > > > I want to create a few DB creation scripts so I can "publish" that > > afterwards. > > On Oracle there's a such possibility but I haven't seen anything > > comaprable in the docs ... > > I however think I'm missing one of the very first possibility of > > "pgsql". > > Could someone point me were I could find some more infos on that matter > > ? > > Are you talking about writing external scripts and importing them into > Postgres in a manner similar to the @ function in SQL*Plus? In psql, you > can use \i to import scripts to do that. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > --- > Reading is to the mind what exercise is to the body. -- Sat Dec 30 00:39:50 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Ye've also got to remember that ... respectable people do the most astonishin' things to preserve their respectability. Thank God I'm not respectable. -- Ruthven Campbell Todd = End of Quote ===
Re: [SQL] Looking for comments
Oliver, At the moment Tx for all your remarks... I'll need some times to digest them all! I'll keep this thread inform on updates Regards, Thomas Oliver Elphick wrote: > > Thomas SMETS wrote: > > > >Rather thant making long sentences & comment. > >Anyone willing to give me a little help on this tables definition is > >welcome > > > >http://lautre.org/tsmets/DB.html > > General comment: you use lettercase to divide words in table and field > names; this will be lost as soon as you create the tables, because > PostgreSQL folds everything to lower case. Use _ to separate words > instead. > > book: > ISBN's have a checkdigit; it would be sensible to provide a function > to be used in a CHECK constraint to ensure that the ISBN is valid. > NOT NULL and UNIQUE are implied by PRIMARY KEY; they don't need to be > specified. > > What information goes in reference? > > You create indexes yourself. Indexes on these fields are automatically > created because of the PRIMARY KEY and UNIQUE constraints; your own > indexes add nothing and will decrease performance. On the other hand, > you could well use indexes on author and publisher. Perhaps you > could also do a word index on title. > > What if you have more than 1 copy of the same title? You should have > another table for physical copies, with a foreign key reference to book. > When you lend a book, it is the physical copy you want to track. > The copy table will cross-reference the member who has it on loan > and will also need a field for status (on the shelf, on loan, lost/stolen, > rebinding, etc.) The price belongs here, because you might pay a different > price for a later acquisition of the same ISBN. You will then need > yet another cross-referencing table between book and copy tables. > > Of course, some titles have multiple ISBNs, depending on the type of > binding (e.g. Good News Bible in several different formats). Perhaps > you need yet another table to link ISBNs to titles. Each issue of > many serials has a volume and issue number; you really don't want a > separate definition in book for each issue, do you? > > Author: many books have multiple authors; also editors. > > You probably need fields for place and year of publication > > Type: this seems to refer to attributes of serial publications; these > have ISSN numbers (rather than ISBN) and the ramifications of checking > serial issues are far more complex than you have allowed for. Serials > certainly need a separate table. > > member: > You create a sequence yourself and use it explicitly for person_ref; it is > simpler to define this field as SERIAL instead of INTEGER; this will > do all the sequence maintenance for you. > > If member ids cannot be negative, you need a CHECK constraint to check > the id range. The sequence will not override a direct setting. > > You define person_ref twice; presumably the first occurrence should be `id'. > > You say that one member can reference multiple persons, but you cannot > achieve that by referencing a single person in this table. A single > field can hold only a single reference. You need a member_person table: > > CREATE TABLE member_person ( > member INTEGER CONSTRAINT member_fkey REFERENCES member (id) > ON UPDATE CASCADE ON DELETE NO ACTION, > person INTEGER UNIQUE > CONSTRAINT person_fkey REFERENCES person (id) > ON UPDATE CASCADE ON DELETE NO ACTION, > PRIMARY KEY (member, person) > ); > > which will hold all persons related to the member. If you have a person > who is primarily responsible, his id goes in the person_ref field. > > I should have thought that person_address should have a NOT NULL constraint. > > Why make LastLending NOT NULL? If you have a new member there is no > last lending and the field would naturally be null. > > The CHECK constraint on CreatedOn is invalid; a date field cannot ever > have a value of '' (it is not held as a string). The NOT NULL constraint > is all you need; though you could add a date range check >(CreatedOn > '1 Jan 2001' and CreatedOn <= CURRENT_DATE) > > CountryCodes: > Why not add a name field and preload this table with the ISO country > definitions. (Some of the country codes are not at all obvious, so > you need the names.) I expect the Post Office would prefer to have > names, too. > > The PRIMARY KEY constraint makes UNIQUE NOT NULL unnecessary. There is > no sense in having a DEFAULT on a primary key field. The default belongs > in the address table. > > ZipCodes: > I don't understand the purpose of this table. > Presumably you need a PRIMARY KEY (country_code, zip_codes) constraint. > > Translations: > ditto > > You are defining an unnecessary index. > Your insert will violate the NOT NULL constraint on language. > > My general impression is that you're making this up as you go along. > You could do with findi
[Fwd: [SQL] Looking for comments]
> > Hi Thomas, I would love to help. > Najm > What are your competences ? Needed is java <--> Basic devlopment in Java 2 php <--> Currently hosting free may buy web site later on python <--> Install & config done via Python Admin <--> What to Config, How, Customisable ... Any help is most than welcome. thomas, -- Sat Dec 30 00:39:50 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Ye've also got to remember that ... respectable people do the most astonishin' things to preserve their respectability. Thank God I'm not respectable. -- Ruthven Campbell Todd = End of Quote ===
Re: [SQL] [ADMIN] Running a file
Yep, Tx, Thomas, p.s. Sorry for the stupid question Raju Mathur wrote: > > Hi Thomas, > > Is it possible you're looking for: > >psql -f script.sql > > ? > > Regards, > > -- Raju > > > "Thomas" == Thomas SMETS <[EMAIL PROTECTED]> writes: > > Thomas> I'm runnin postgres 7.?? (Last RPM package available from > Thomas> the site). > > Thomas> I want to create a few DB creation scripts so I can > Thomas> "publish" that afterwards. On Oracle there's a such > Thomas> possibility but I haven't seen anything comaprable in the > Thomas> docs ... I however think I'm missing one of the very > Thomas> first possibility of "pgsql". Could someone point me were > Thomas> I could find some more infos on that matter ? > > Thomas> Tx, > > Thomas> Thomas, > > Thomas> p.s. : Merry Xmas for some / Enjoy Ead / Merry Solstice > Thomas> for the others :-) > -- > Raju Mathur [EMAIL PROTECTED] http://kandalaya.org/ -- Sat Dec 30 00:39:50 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Ye've also got to remember that ... respectable people do the most astonishin' things to preserve their respectability. Thank God I'm not respectable. -- Ruthven Campbell Todd = End of Quote ===
[ADMIN] Toad version for PostgreSQL
Toad (the graphical interface for Oracle) has a functionnality that allows one to dispaly tables & have them defined in the tool accordingly. Is there an equivalent tool for PostgreSQL ? Tx, thomas, -- Sat Dec 30 00:39:50 CET 2000 Thomas SMETSe-mail : [EMAIL PROTECTED] Av. de la Brabançonne 133 / 3 Tel. : +32 (0)2 742. 05. 94. 1030 Bruxelles === Quote of the Day = Ye've also got to remember that ... respectable people do the most astonishin' things to preserve their respectability. Thank God I'm not respectable. -- Ruthven Campbell Todd = End of Quote ===
Re: [SQL] Toad version for PostgreSQL
Thomas SMETS <[EMAIL PROTECTED]> writes: > Toad (the graphical interface for Oracle) has a functionnality that > allows one to dispaly tables & have them defined in the tool > accordingly. > Is there an equivalent tool for PostgreSQL ? I'm not familiar with Toad, so I don't know exactly what functionality it offers, but take a look at pgAdmin, pgAccess and phpPgAdmin. Perhaps one of them does what you want. pgAdmin and phpPgAdmin are available at http://www.greatbridge.org/. I forget pgAccess's URL, but poking around at postgresql.org should turn up a link... regards, tom lane
Re: [SQL] Compiling "C" Functions
Tulio Oliveira wrote: > > I appreciate any "C" Function complete samples, including de command > line for > the compiler. I've attached a generic GNU make snippet for compiling .so files. Adjust to suite your tastes. Like my math textbooks used to say "writing the C code is trivial, and is left as an excercise for the reader." ;) CC = /usr/bin/gcc TARGET = debug #TARGET = final DFLAGS = -Wall -g FFLAGS = -Wall -O2 SFLAGS = -fpic -shared MYINCLUDES = -I/usr/local/include -I/usr/local/src/postgresql/src/include -I/usr/local/postgresql/include MYLIBS = -L/usr/local/lib -L/usr/local/postgresql/lib -lpq ifeq ($(TARGET),final) MYCFLAGS = $(FFLAGS) else MYCFLAGS = $(DFLAGS) endif %.so: $(CC) $(MYCFLAGS) $(MYINCLUDES) $(MYLIBS) $(*F).c -c -o $(*F).o $(CC) $(SFLAGS) $(*F).o -o $(*F).so [ -d $(TARGET) ] || mkdir $(TARGET) mv $(*F).so $(TARGET) rm *.o
[SQL] Sql92..
Hi, I'm working in a database called frontbase..and I dont have a lot of exposure to SQL92.So can u provide me some feedback with some site or tutorials where in I can get myself acquainted with sql92. regards Narayanan begin:vcard n:Palasseri;Narayanan x-mozilla-html:TRUE org:GSI India adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:S/W Engineer fn:Narayanan Palasseri end:vcard
Re: [SQL] Tree structure table normalization problem (do I need a trigger?)
Frank Joerdens wrote: > > In a recent thread (How to represent a tree-structure in a relational > database) I asked how to do a tree structure in SQL, and got lots of > suggestions (thanks!), of which I chose the one below: > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > The one described in Joe Celko's article (the one with the worm that > travels along the edge of the tree . . . ) seemed more evolved but > requires fairly complex SQL stuff, I thought, for simple operations that > are straighforward with the above model. SQL99 (which is what SQL3 became) defines a recursive data model that allows you to implement these types of structures. IBM's DB2 implements at least a subset of this standard (this is based on hearsay, not personal experience). Oracle also provides some SQL extensions to allow you to create recursive queries, but they are nonstandard (CONNECT BY, LEVELS, ...). I don't find any of the solutions to this problem using SQL92 satisfactory. Celko's tree structure can require updates to every node in the tree for operations on a single node. And once you start writing procedural code, you're obviating SQL itself. So for myself, I've basically decided to hold my horses and find other interesting things to do until the SQL99 standard finds widespread adoption. I realize this might not be a satisfactory answer, but if you can afford to wait, a better solution should be on the way. -Ron-
Re: [SQL] How to represent a tree-structure in a relational database
Ron Peterson wrote: > > CREATE TABLE category_edge ( > parent INTEGER > NOT NULL > REFERENCES category_node(id), > > child INTEGER > NOT NULL > REFERENCES category_node(id) > ); Just for the sake of anal-retentive completeness, I'd like to point out that you'd probably want an id field in this table also. Plus what the heck else am I going to do on Christmas break? ;) On a completely unrelated topic: getting the PostgreSQL discussions lists on a news server is great!!! I was overwhelmed with mail that I usually don't have time to deal with. Now when I have a chance, I just go see what's up on the news server. Excellent and thanks! -Ron-
Re: [SQL] How to represent a tree-structure in a relational database
Stuart Statman wrote:
>
> I would suggest, instead, to create a table that represents your hierarchy
> without adding columns. For example :
>
> create table Category (
> CategoryID int4 not null primary key,
> ParentCategoryID int4 not null REFERENCES Category (CategoryID),
> CategoryName varchar(100)
> );
Another possibility would be to use two tables to represent the data
structure.
CREATE SEQUENCE category_node_id_seq;
CREATE TABLE category_node (
nameTEXT
NOT NULL,
id INTEGER
DEFAULT NEXTVAL('category_node_id_seq')
PRIMARY KEY
);
CREATE TABLE category_edge (
parent INTEGER
NOT NULL
REFERENCES category_node(id),
child INTEGER
NOT NULL
REFERENCES category_node(id)
);
This structure is more 'normal' in the sense that nodes without children
(in a tree, the leaf nodes) don't have records in the edge table.
What either of these structures allow to do is create directed graph
structures. If you'd like to constrain this structure to be a tree, you
have to enforce that restriction with procedural code.
-Ron-
[SQL] Re: system catalog info
hi Ron, Try the developers manual: http://www.postgresql.org/devel-corner/docs/postgres/developer.htm actual catalog info is: http://postgresql.mirror.aarnet.edu.au/devel-corner/docs/postgres/catalogs.htm If you find any other sources please let me know - especially any that refer to composite types ;-) cheers, John Ron Peterson wrote: > > The HTML programming documentation (e.g. > http://www.postgresql.org/devel-corner/docs/programmer/pg-system-catalogs.htm) > indicates that more extensive information about the system catalogs can > be found in the "Reference Manual". Where can this reference manual be > found? Or where can more extensive documentation about the system > catalogues be found? > > -Ron- -- -- john reid e-mail [EMAIL PROTECTED] technical officerroom G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
