Re: [SQL] psql question
On Thu, Nov 23, 2000 at 07:31:36PM +0100, Peter Eisentraut allegedly wrote: > Joe Conway writes: > > > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use > > of the up arrow key for history and the escape/tab key for command > > completion, but on my remote web host (webpipe.net) those keys don't work. > > What do I need to do to get these features working on my remote web host? > > I've seen reference to .psqlrc in the psql man file, but not much else (no > > syntax, etc). > > If you compiled from source, make sure you had the readline-devel package > installed. Also, check the version of the psql shell. 6.x won't do a lot of those neat little things that make life easier in 7.x. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum
Re: [SQL] problems with postmaster
On Fri, Nov 24, 2000 at 12:34:29PM -0600, Mike Castle allegedly wrote: > ipcclean > pg_ctl stop > > Though finding out about ipcclean was not easy considering the output > given. There is no mention of it in any of the FAQs. > > mrc You could also use ipcrm manually to clean up the shm segments postgresql left lying around. Moral of the story: DON'T USE A SIGKILL TO STOP POSTGRESQL. Of course, there are times when you need to be somewhat crude. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum
Re: [SQL] removing a DB??
On Mon, Nov 27, 2000 at 12:34:38PM +0100, Jens Hartwig allegedly wrote: > Hello Bruno, > > you get a list of all available databases by issuing a query on > "pg_database": > > select * from pg_database; > > What do you mean with "internal DB's"? > > Regards, Jens You can also get a list of databases by executing psql -l from the commandline. My 0.02 euro, Mathijs -- "It is a great thing to start life with a small number of really good books which are your very own". Sir Arthur Conan Doyle (1859-1930)
Re: [SQL] a script that queries database periodically
On Mon, Nov 27, 2000 at 10:44:39PM +0100, Poul L. Christiansen allegedly wrote:
> I have a bash script in crontab with the following line:
> "lynx -dump
> http://127.0.0.1/postgres/myPhpFile.php>${currentDir}php_output.html"
>
> You can also just put that line directly into crontab.
>
> Poul L. Christiansen
Be sure to redirect the output of the command in your crontab to /dev/null
or something similar. If you don't, the output (HTML) will be emailed to
you each time the script is run. Whether this side effect is desirable is
of course up to you (or your boss, for instance).
Getting even more off-topic,
Mathijs
--
"Where is human nature so weak as in a bookstore!"
Henry Ward Beecher (1813-1887)
Re: [SQL] a script that queries database periodically
On Mon, Nov 27, 2000 at 11:22:45AM -0800, Bernie Huang allegedly wrote: > Hello, > > My boss wants me to write a script that will send a email report to him > monthly with the result of database queries. > > For example, an email would be like: > > Monthly report > +--+-+---+ > | Vehicles | Mileage | # of Signouts | > +--+-+---+ > | Vehicle1 | 10324 | 5 | > +--+-+---+ > | Vehicle2 | 19384 | 6 | > +--+-+---+ > ... > > I was thinking of writing up a PHP script and put into crontab, which is > somehow easier than a shell script, but PHP is an apache module, so I > cannot execute PHP under crontab (it has to be executed in a browser > right?). I guess a shell script is necessary. So, is it possible to > call 'psql' and returning its query result and I can use sendmail to > email the result? Any other idea? > > Thanks. > > - Bernie Sure you can. You can configure PHP to run seperately from any webserver. You would then be able to write PHP scripts that can be used just like any other UNIX scripts, for instance from a crontab. I don't know if you've configured and built PHP before, but it is actually not that difficult. However, be sure to look at the INSTALL file that comes with PHP. What it comes down to is probably the following: 1 - extract PHP source 2 - change to the PHP source directory and execute the following configure command: ./configure --prefix=/usr/local/standalone-php --without-mysql --with-pgsql 3 - build the source by giving the make command 4 - do a 'make install' after PHP has been succesfully build A bit off-topic, but I hope this is helpful. Cheers, Mathijs Ps. I just thought of something else. You could write a PHP script that runs under apache (like your're used to) and request it with wget, lynx or a similar tool from a crontab. That way you wouldn't need to configure a new PHP installation. -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 04:48:47PM +0100, Frank Joerdens allegedly wrote:
> I am just thinking about the data model for a little content management system that
>I am
> currently planning. Individual articles are sorted under different categories which
>branch
> into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The
> structure should be extensible, i.e. it must be possible to add levels. What I am
>thinking
> now is that you would keep the index in a separate index table (linked with the
>primary
> key in the articles table), which would have 6 or 7 fields initially, and that you'd
>add
> columns with the alter table command, if need be, to make the structure deeper. Is
>this
> the recommended way to go about it? It feels pretty 'right' to me now but since the
> problem should be fairly common, there must be other people who have thought and
>written
> about it and there might even be a recognized 'optimal' solution to the problem.
>
> Comments?
Yeah. I've built something similar.
The way I've done it:
Give each record a unique ID (generated with a sequence) and store
the records in a table. Create a second table in which you store
parent id-child id combinations.
So:
1 - Automotive transport
2 - Cars
3 - Motorcycles
Store in the table:
1-2
1-3
There's one main category (Automotive transport) which has two sub-categories:
Cars & Motorcyles
The way I'd do it if I had to do it again:
Give each record a unique id, generated by the application. Denote levels with
extra letters.
So:
AA - Automotive transport
- Cars
AAAB - Motorcycles
The structures has the added bonus of making it very easy to determine all the
sub-categories of a category, no matter how deep the tree is below the category
you're looking at. With the first approach it is not possible to do this in a
single SQL query. You could do this with a function, I guess.
I hope this is of some use to you.
Cheers,
Mathijs
--
"Borrowers of books -- those mutilators of collections, spoilers of the
symmetry of shelves, and creators of odd volumes."
Charles Lamb (1775-1834)
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 12:09:06PM -0800, Stuart Statman allegedly wrote: > > The way I'd do it if I had to do it again: > > Give each record a unique id, generated by the application. > > Denote levels with extra letters. > > > > So: > > > >AA - Automotive transport > > - Cars > >AAAB - Motorcycles > > > > The structures has the added bonus of making it very easy to > > determine all the > > sub-categories of a category, no matter how deep the tree is > > below the category > > you're looking at. With the first approach it is not possible > > to do this in a > > single SQL query. You could do this with a function, I guess. > > The problem with this method is if you need to insert a category, or move a > category. You'll need to re-id a bunch of categories, and bubble those > changes out to every table that refers to this table. You can solve the last problem by using an extra table that maps unique record id's (numerical) to hierarchical id's (text). Inserting a category, in my case, does not require me to start updating lots of records, since I would only use these strings to store hierarchical information. I'm sorting categories based on alphabet, which you can overrule by increasing the 'weight' of a category, which is a numerical value attached to every category and which normally has a vaue of 1. However, changing the level of a category would require me to modify all categories below that. In my case, this wouldn't be a problem. We're using this stuff for a Yahoo style directory which (atm) has about 2500 different categories. I'm generating a complete tree of all categories and the websites in them once a day, storing them in a souped up DBM style database. For each record I store the children, not the parent. If changing the underlying structure takes a couple of minutes, than this is acceptable. As you can see my number of categories is rather small. If you're going to use this for a forum or something similar, you may run into problems. However, how often do you want to move a thread... Cheers, Mathijs -- "Where is human nature so weak as in a bookstore!" Henry Ward Beecher (1813-1887)
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote: > Stuart, > > > I don't think I'd be comfortable with having the node_level column in the > > table structure. First, because you can derive that value using a function, > > it's duplicate data. Second, if you decide to take an entire segment of your > > hierarchy and move it under another node (by changing the value of > > node_linkup/ParentCategoryID), you'll need to recalculate all of those > > node_level values. And all the node_level values underneath it. > > I can see that. I suppose it depends on the data you're storing. The > project I was working on tracked grocery inventory for a delivery > service, and thus each item had a fixed "level" in the heirarcy (Food > Class, Food Type, Manufacturer, and Item) and thus while items might get > reassigned *across* the heirarcy, they did not get re-assigned *up and > down* the heirarcy. Indeed. If the structure 'rarely' changes, having to do an expensive update may be acceptable, if it increase the overall performance significantly. > Also, I can't think of a way to represent the tree in pure SQL without > having the level identifiers (and a fixed number of levels). Storing only the parent for a record doesn't require you to keep track of levels, since this information can be reconstructed by following the chain of parent id's until you reach the top of your tree. Storing the children for each record (like I'm doing) works exactly the same. Just follow the 'path' (for instance 'Automotive Transport/Cars') to find the category you're looking for. Cheers, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco
[SQL] PostgreSQL crashes on me :(
Hi all, I've been running pgsql database since 6.3 with sizes ranging from a few megabytes to a few hundred megabytes. And ever since 6.5 came out I've had almost no crashes. Until now. We recently installed a small server for an external party to develop websites on. This machine, a K6-233 with 256 MB, is running FreeBSD 3.3 and PostgreSQL 7.0.2 (maybe I'll upgrade to 7.0.3 tonight). The database it's running is about 2 MB in size and gets to process an estimated 1 to 25000 queries per day. Nothing special, I'd say. However, pgsql keeps crashing. It can take days, but pgsql will crash. It spits out the following error: ServerLoop: select failed: No child processes I've done some searching with Google and I've searched the mailinglist archives on the PostgreSQL site, but I couldn't turn up anything useful. I did find a post from someone with the same problem, but appearently nobody ever replyed to that post. Does anyone have any idea what might be going on here? Should I be asking this question on another list? If so, which one? Any information would be very welcome, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco
Re: [SQL] Don't understand creation statement's answer
On Mon, Dec 18, 2000 at 12:32:54AM +0100, Thomas SMETS allegedly wrote:
> tsmets=> create sequence author_seq;
> CREATE
>
> tsmets=> create table author (
> tsmets=> author_id numeric primary key default
> nextval('author_seq'),
> tsmets=> name char(25) not null,
> tsmets=> surname char(25) not null
> tsmets=> );
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
> 'author_pkey' for table 'author'
> Now the question is :
> What is the meaning of the following statement in the table description
> ?
>
> Attribute | Type | Modifier
> ---+---+--
> author_id | numeric(30,6) | not null default
> nextval('author_seq'::text)
>
> Why was my author_id changesdin a "nextval('author_seq'::text)".
> There's no text here ?
nextval takes one parameter of the type text. It's just an explicit typecast.
The output of nextval is a number, as you requested.
I hope this helps,
Mathijs
--
"Books constitute capital."
Thomas Jefferson
Re: [SQL] Speed of SQL statements
On Sun, Feb 18, 2001 at 03:42:43PM -0500, Kevin Quinlan allegedly wrote: > Does anyone have any performance numbers regarding SQL statements, > specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average > how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to > execute? You can use the EXPLAIN command to show the execution plan for a query. This allows you to tweak your query (and maybe your indexes) for optimal performance. | iig=# explain select id from entries where not exists (select * from etree where |siteid = id); | NOTICE: QUERY PLAN: | | Seq Scan on entries (cost=0.00..57838.91 rows=1 width=4) | SubPlan | -> Index Scan using idx_etree_siteid on etree (cost=0.00..2.04 rows=1 | width=8) | | EXPLAIN The PostgreSQL documentation has more information on the EXPLAIN command. Make sure you have a look at the VACUUM command, if you haven't already done so. In order to come up with an optimal execution plan pgsql needs information about the contents of your database. This is why you need to run VACUUM ANALYZE from time to time. It also cleans up your indexes. If pgsql is not using indexes when you think it should, run a VACUUM ANALYZE on the table and see if that makes a difference. I hope this helps, Mathijs -- "Books constitute capital." Thomas Jefferson
Re: [SQL] Speed of SQL statements
On Sun, Feb 18, 2001 at 03:42:43PM -0500, Kevin Quinlan allegedly wrote: > Does anyone have any performance numbers regarding SQL statements, > specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average > how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to > execute? Performance is not easily expressed in time, since it depends a lot on the way you've setup your database and your queries (indexes, etc). Of course, the hardware you use also influences the execution time a lot. Cheers, Mathijs -- "It is a great thing to start life with a small number of really good books which are your very own". Sir Arthur Conan Doyle (1859-1930)
Re: [SQL] Optimizing Query
On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote: > Have you tried VACUUM ANALYZE and CLUSTER? I assume CLUSTER still drops all indexes except the one you're clustering on? Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How do I use text script containing SQL?
On Mon, Mar 05, 2001 at 11:08:40AM -0800, David Olbersen allegedly wrote: > On Mon, 5 Mar 2001, Jeff S. wrote: > > ->I want to be able to use the file to create my table. > ->I've tried psql -d databasename -e < filename.txt > ->but that doesn't work. > > You're making it too dificult :-) > 'psql -d databasename < filename.txt' should work just fine Strange, the syntax you used (psql -d -e) should work... serv0:~$ echo '\d ntree' | psql -d iig -e Table "ntree" Attribute | Type | Modifier ---+-+-- pid | integer | cid | integer | Indices: idx_ntree_cid, idx_ntree_pid I'm using psql 7.0.3 and I'm pretty sure it also works for 6.5.x. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Clustering (was Re: [SQL] Optimizing Query)
On Mon, Mar 05, 2001 at 04:45:47PM -0500, Bruce Momjian allegedly wrote: > Yes. > > > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote: > > > Have you tried VACUUM ANALYZE and CLUSTER? > > > > I assume CLUSTER still drops all indexes except the one you're clustering > > on? > > > > Mathijs So running cluster on the words table (which is indexed with a seperate index on each of the two fields) would probably decrease performance by getting rid of one index without warning (the one NOT specified in the cluster command)? How useful is clustering with pgsql when in a lot of situations systems have enough memory to have the filesystem subsystem cache most or all index data? Seek times shouldn't be an issue in such a situation, since the index doesn't have to be read. Just wondering, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] Two way encryption in PG???
On Mon, Mar 05, 2001 at 08:40:53AM -0800, clayton cottingham allegedly wrote: > Boulat Khakimov wrote: > > > > David Olbersen wrote: > > > > > > On Sun, 4 Mar 2001, Boulat Khakimov wrote: > > > > > > ->How do I encrypt/decrypt something in PG? > > > > > > Perhaps it'd be better to one-way encrypt something? Granted I don't know the > > > details of your project, but allowing a way to "decrypt" something is rather > > > insecure. > > > > > > -- Dave > > > > Now, one way CC encryption doesnt really make any sense, does it :) > > > > Two-way encryption algorithms like blowfish are very hard to break, too > > bad they are > > not build in to PG. > > > > this could be easily done with > PL/perl couldnt it? It's still not clear to me why someone would want to. Having the database server do two-way encryption doesn't really provide you with any extra protection. Also, it puts extra strain on the database server. If you want to store encrypted data on the server, have the application do the encryption. That also would provide for a much more scaleable solution, since it doesn't put any extra load on the database server. Doing the encryption on the application end should be pretty easy. I know there are some database(like) systems that can do this kind of stuff, but the only advantage I can see at the moment is that when someone physically steals your database server (or the disks), they can't read your data. This only works when the key is NOT, again, NOT stored on disk in any form; query the sysadmin (or someone similar) when starting the db server instead. Btw. don't even think about asymmetric (public key) cyphers, such as RSA. They are MUCH too slow, both in setup and processing (throughput) time. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] Optimizing Query
On Mon, Mar 05, 2001 at 04:59:47PM -0500, Justin Long allegedly wrote: > Ok, now I have another question... it doesn't seem to be accessing the index. > > explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1 > WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and > w1.wordid=85369)) > > NOTICE: QUERY PLAN: > > Merge Join (cost=32339.30..35496.97 rows=19262538 width=24) >-> Merge Join (cost=16530.24..16668.77 rows=233274 width=20) > -> Sort (cost=15809.06..15809.06 rows=8257 width=4) >-> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257 > width=4) > -> Sort (cost=721.18..721.18 rows=2825 width=16) >-> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825 > width=16) >-> Sort (cost=15809.06..15809.06 rows=8257 width=4) > -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4) > > Note the sequential scans... there is a wordindex where w0.wordid=42743... > why isn't it doing an indexscan? wouldn't that be more efficient? > > Justin Did you run the 'vacuum analyze' command on the tables concerned (or even better, the whole database)? Without the data this analysis provides psql cannot come up with a good execution plan and falls back to full table scans. Do a 'vacuum analyze' one a week to keep performance levels up. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
On Sun, Mar 04, 2001 at 10:01:37AM +0800, xuyifeng allegedly wrote: > - Original Message - > From: The Hermit Hacker <[EMAIL PROTECTED]> > To: Jaruwan Laongmal <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, March 02, 2001 8:04 PM > Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in >DB? > > > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: > > > > > I had deleted a very large number of records out of my SQL table in order to > > > decrease the harddisk space. But after I use command 'ls -l > > > /usr/local/pgsql/data/base/', it is found that the size of concerning files > > > do not reduce due to the effect of 'delete' SQL command. What should I do > > > if I would like to decrease the harddisk space? > > > > VACUUM > > could anyone remove this nasty bug in 7.2? this is already a big pain and is the >reason > why am I still using MySQL in my product server. another nasty thing is it does not > allow me to reference table in another database. sigh. Why would this be a bug? Sure, maybe it's not what you expected, but I hardly think it qualifies as a bug. For instance, Oracle doesn't release storage (datafiles specifically) after it has allocated space for them. In fact, I wish I could force pgsql to allocate storage it might need in the future. It would be great if I could force pgsql to allocated four datafiles spread across four harddisks, so I would enjoy a) better database performance and b) rest assured I have the diskspace when I need it in the future. Call it a poor mans RAID; I think MySQL can perform this trick. If pgsql can do this, please let me know But back to your problem. One way to get the amount of space allocated to shrink is by recreating the database. Dump it using pg_dump and recreate it using the backup you just made. This is a fairly simple and quick process. Give it a try on a small test database first; you don't want to risk loosing your data. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: [GENERAL] Re: MySQLs Describe emulator!
On Tue, Mar 06, 2001 at 04:37:32PM +0100, Karel Zak allegedly wrote: > On Tue, Mar 06, 2001 at 10:19:13AM -0500, Boulat Khakimov wrote: > > > > Karel Zak wrote: > > > > On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > > > > Tom Lane wrote: > > > > > > > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > > > > Here is a nifty query I came up with > > > > > > that provides a detailed information on any row of any table. > > > > > > Something that is build into mySQL (DESC tablename fieldname) > > > > > > but not into PG. > > > > > > > > > > Er, what's wrong with psql's "\d table" ? > > > > > > > > 2) as a programmer I need to be able to find out as much info as > > > > possible about any given field > > > >which is what "describe" for in mySQL. > > > > > > As a programmer you can see psql source and directly found how SQL > > > query execute this tool. The PostgreSQL needn't non-standard statements > > > like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. > > > > > > Karel > > > > Agreed! Why make someones life easier?? > > Let's complicate things as much as possible that way it's more > > fun,right? ;o) > > > > Dont understand how this works? No problem -- just read the source > > code. > > Dont understand how to get that to work? Not a problem -- read the > > source code! > > > > The only problem tho, the source codes tend to be thousands of lines > > when it comes > > to DBs and time is ... > > Well man, I not write this code, but I need 1 minute for found it > > see src/bin/psql/describe.c: > > SELECTa.attname, format_type(a.atttypid, a.atttypmod), attnotnull, > a.atthasdef, a.attnum, obj_description(a.oid) > FROM pg_class c, pg_attribute a > WHERE c.relname = 'YourTableName' AND >^ > a.attnum > 0 AND > a.attrelid = c.oid > ORDER BY a.attnum; > > If I good remenber anywhere in PG's docs is catalog schema. It isn't > too much difficult write queries like above-mentioned, because catalog > attributes/tables names are intuitive. For start see pg_class and > pg_attribute. Karel, how about this one? It's even easier :) No need to spit through code to find this... serv0:/var/namedsrc$ psql -E -c '\d nodes' iig * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='nodes' * * QUERY * SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'nodes' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * * QUERY * SELECT c2.relname FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'nodes' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY c2.relname * Table "nodes" Attribute | Type | Modifier ---+--+-- id| integer | title | text | ncount| smallint | ecount| smallint | ref | integer | moddate | integer | publish | char(1) | Indices: idx_nodes_id, idx_nodes_ref, idx_nodes_title Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] Undefined symbol
On Tue, Mar 06, 2001 at 06:44:31PM -0500, Boulat Khakimov allegedly wrote:
> testdb=# select encode('bob','bob');
> ERROR: Load of file /home/boulat/Funio.com/database/encode.so failed:
> /home/boulat/Funio.com/database/encode.so: undefined symbol:
> BF_cfb64_encrypt
>
> thats the function that I call from inside my c code...
> but Why??? It compiled with no errors or warning, and I have all the
> right includes in my source code.
>
> Im confused!
Are you linking against a blowfish library? If so, either include the
encryption functions in your shared object, or load that shared object
yourself. Have a look at the dlopen manpage for more information.
I hope this helps a bit,
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Serials.
On Sat, Mar 24, 2001 at 03:55:09PM +1000, Grant allegedly wrote:
> Please see below for my table schema. I have two questions.
>
> (1) Why is a sequence limited to 2147483647, it seems very small?
This is the maximum value a signed integer (32-bit) can contain. If
this really is a problem for you, then it might be a good idea to
look into another database; one specifically suited for huge databases,
such as NCR Terabase.
To put things in perspective: if you have a table with more than two
billion records each about 50 bytes in size you would have more than
(!) 100 GB of data... I don't think PostgreSQL is meant for those
enormous amounts of data, although it may still work porperly. Most
larger commercial database offerings (Oracle and Sybase come to mind)
will still work well, but once you're database contains a few terabytes
of data you're just asking for problems. There is a very good chance
that you can spread the data accross several database, increasing
performance and maintainability.
Of course, writing your own serial implementation is not that difficult.
If you use 64-bit integers (int8), you should have plenty of space :)
Search the mailinglist archive for some clues, since there was a discussion
about this (implementing a sequence that doesn't have holes) one or two
days ago.
> (2) If I reset the sequence, then try another insert. It will not insert
> anything until it cycles through all sequences and finds an unused one. It
> will give the following error each time it tries to insert a row with a
> used sequence:
>
> PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
> index releases_pkey
>
> How can I possibly get around this issue so that I can be sure to always
> have a free id without getting the error above?
Get the maximum primary key value from the table and start the sequence
from the number following it.
I hope this helps,
Mathijs
--
$_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]&48){$h=5;
$_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d=
unxV,xb25,$_;$b=73;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=($t=255)&($d
>>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<9
,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t
^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271))
[$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] all views in database broken at once
On Sat, Mar 24, 2001 at 07:50:00PM -0500, Tom Lane allegedly wrote: > Andrew Perrin <[EMAIL PROTECTED]> writes: > > But I'm intrigued: what is it that causes this? Is it *my* > > recreating the view on which the other views depend, > > Yes. You dropped and recreated the view --- the new version may have > the same name but it's not the same OID, so it isn't the same object. > And the other views refer to it by OID. > > The ultimate solution should have two parts, IMHO: > > 1. Dependency checking so that you *can't* drop a view that is still > referenced. However this will not be complete --- it's not clear that > we can detect references inside PL functions, for example. > > 2. An ALTER VIEW command that lets you change a view's defining query, > while keeping the same OID, as long as the names and types of the output > columns don't change. This would reduce the need to drop and recreate > views. How about being able to recompile them (keeping the SQL around in the system catalogs)? Doesn't Oracle allow you to do something like that? Mathijs -- "It is a great thing to start life with a small number of really good books which are your very own". Sir Arthur Conan Doyle (1859-1930) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] all views in database broken at once
On Sat, Mar 24, 2001 at 11:36:56PM -0500, Tom Lane allegedly wrote: > Mathijs Brands <[EMAIL PROTECTED]> writes: > > How about being able to recompile them (keeping the SQL around in the > > system catalogs)? Doesn't Oracle allow you to do something like that? > > That's another possibility. It's not real clear that there's any > advantage to storing rules in preparsed form to begin with --- if > we just stored the original text and reparsed it each time it was > read, the system would be vastly more flexible, and probably not > noticeably slower. But every bit of performance counts, of course... Mathijs -- "Borrowers of books -- those mutilators of collections, spoilers of the symmetry of shelves, and creators of odd volumes." Charles Lamb (1775-1834) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Calling Java from psql (was Re: [SQL] requesting help)
On Mon, Mar 26, 2001 at 09:53:52AM +0200, Poul L. Christiansen allegedly wrote: > Log into PostgreSQL using psql and run the help command: > "\h create trigger". > > See also: > http://www.postgresql.org/users-lounge/docs/6.5/postgres/sql-createtrigger.htm > > HTH, > Poul L. Christiansen > > On Sun, 25 Mar 2001, [iso-8859-1] Mohamed ebrahim wrote: > > > Hi > > I am an user of postgresql. I want to do some > > manipulation on table and i want to do some java stuff > > after inserting a row into table(i.e like sending > > email). I want to fire the trigger to do this after > > the insertion took place. I know how to insert into > > the table but i want to know that it is possible to > > java stuff after inserting rows each time. Please help > > me in this stuff. I will be thankful to you. > > > > Thank you > > Ebrahim Has anybody ever tried calling Java code from a pgsql trigger written in C? Shouldn't this be possible using JNI? I'm not exactly a Java expert myself, but this is the way PHP allows you to call Java code from your PHP scripts. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Calling Java from psql (was Re: [SQL] requesting help)
On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > Mathijs Brands writes: > > > Has anybody ever tried calling Java code from a pgsql trigger written > > in C? Shouldn't this be possible using JNI? > > I have, and given the current Java implementations it's a desaster. That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to work stable either :( Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: Calling Java from psql (was Re: [SQL] requesting help)
On Thu, Mar 29, 2001 at 10:10:44AM +0100, Peter Mount allegedly wrote: > At 21:37 26/03/01 +0200, Mathijs Brands wrote: > >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > > > Mathijs Brands writes: > > > > > > > Has anybody ever tried calling Java code from a pgsql trigger written > > > > in C? Shouldn't this be possible using JNI? > > > > > > I have, and given the current Java implementations it's a desaster. > > > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to > >work stable either :( > > Not having looked at the PHP-Java link, are they working as one process or > is it some IPC type link? They run a seperate JVM and connect to it. At least that what I gathered. Regards, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 08:54:24AM -0600, Roberto Mello allegedly wrote: > Ah, I see the problem. When you mentioned there were problems I > thought you were talking about the connections themselves. > I am using PHP 4 under AOLserver/OpenNSD, which has been fully > threaded from scratch (1994) so I hope this won't be much of an issue (but > then PHP 4 still has threading problems AFAIK). > Not using persistant connections is just too slow for me. It's > instantaneous for AOLserver to generate a page, when the same page with the > connection overhead in PHP takes A LOT longer. So how does AOLserver/PHP handle connections then? Is AOLserver managing (pooling) connections for PHP or something like that (like websphere and nas do for Java programs)? A big problem (in my opinion) with Apache is that you cannot limit the number of Apache processes that acquire database links. For instance, it would be great if I could instruct Apache to direct PHP request to a maximum of, say, 20 dedicated processes. As it stands now, your best bet is probably a two step approach; have a reverse proxy (such as a very lean apache installation) or some other proxy (such as a loadbalancer) redirect requests to multiple webservers, some of which have the pgsql PHP module. Another approach we've used for our own development environment (DBL) is using a SQL proxy. We've placed a proxy between the webservers and the databases. It provides multiplexing of connections and caching of queries. Fail-over or load balancing is not implemented, but not that difficult in our case, since our webservers do not directly update the databases themselves. There's an Open Source product that provides similar functionality (I think it's called SQL Relay or something similar). There maybe be some commercial offerings for Open Source databases such as pgsql and mysql, but I haven't looked into that. Of course, having lots of db connections may not be necessary. We're running one of the bigger news sites in Holland. It's implemented using Java servlets, run without big-gun application servers such as WAS or NAS (just the M5 webserver) and it uses PostgreSQL 7.0 for it's data storage (both images and xml). It runs on a fairly small server and is able to sustain more than 600,000 hits daily without breaking out in a sweat. Of course, caching is a big thing, since actually generating a page may take a second or more, but due to the way we maintain and prime the cache, the users normally don't notice. However, restarting the (web) server is a bitch though, since it usually takes 10-15 minutes to build up a cache big enough to be able to serve most request directly from the cache. It may be a fairly common setup, but I quite like the way this system (built by two collegues) turned out. It was our second attempt at using pgsql for a bigger production site and while we ran into a lot of problems, in the end it worked out very well :) Regards, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] Re: How to store gif or jpeg? Thanks!
On Tue, May 08, 2001 at 03:16:53PM +0200, Sylte allegedly wrote: > > src/interfaces/jdbc/example/ImageViewer.java > > Somewhere on the internet Please be more specific It's part of the PostgreSQL source. You can download the PostgreSQL source from www.postgresql.org... Regards, Mathijs -- And the beast shall be made legion. Its numbers shall be increased a thousand thousand fold. The din of a million keyboards like unto a great storm shall cover the earth, and the followers of Mammon shall tremble. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Cluster and primary key
On Tue, Jun 05, 2001 at 07:51:11AM +, Mikael Kjellström allegedly wrote: > I can't find a way to cluster a table on another index and keep it's > primary key. > > When I define the table the primary key is created, but it's dropped when I > cluster the table on another index. > > I've tried "alter table add constraint", but that's not supported for > primary keys. > > I'm running 7.1.release4 on Debian Woody. That can't be helped. The current cluster implementation drops all indexes on the table you're clustering, with the exception of the index you're clustering on. You could work around this limitation in the current cluster implementation by recreating the index enforcing the primary key. I hope this is of help to you, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
