[ADMIN] Hello to pgsql!
Hi there, i'm new to this mailing list(s) and just want to say hello to everybody here. Allow to introduce myself a little. I'm German (so, please excuse any language problems i have) and work for an company (you see "Organization:"? ;-) as Unix- and Network Admin (though i'm still enrolled to an university to gain an engineering degree). I've worked with Linux about five or six years now in several places, don't really remember exactly how long (first contacts to kernel 0.98) and since 2 year also with SUN servers. Here a little AIX is coming up to me. I'm interested in PostgreSQL and the possibilities coming with it, and i want to use it whithin my diploma thesis. No special experiences to any RDBMS or SQL yet. I started getting closer to PostgreSQL in late spring of '00 by looking at it's docu and especially bruce's draft. Now i do my first real steps to design small DB's in SQL. Therefore i subscribed to this mailinglist(s). So, that's enough for now. I'll continue with writing "real" mails. Have fun -- Gerhard Kroder
[ADMIN] Optimize question: Why joined query slower far more than two queries?
I am confusing that Postgres 7.0.2 plan for a joined query. I have tow tables, each contains about 36000 rows. the first tables' structure likes following: username varchar(20) primary key, other columns; and the 2nd table's structure: username varchar(20) primary key, other columns; on each table I excute a query to find a user: explain select * from users where username='lt'; and the result Index Scan using users_username_key on users (cost=0.00..267.01 rows=306 width=142) the 2nd: explain select * from wquserdata where username='lt'; result: Index Scan using wquserdata_pkey on wquserdata (cost=0.00..240.80 rows=306 width=48) according to that, I expect it should take 267.01+240.80 to join them(btw: why rows=306?) But the joined query takes far more time than what I expected: explain select t0.*,t1.* from wquserdata t0, users t1 where t0.username=t1.username and t0.username='lt'; the result: Merge Join (cost=0.00..3033.27 rows=93670 width=190) -> Index Scan using users_username_key on users t1 (cost=0.00..2406.06 rows=30607width=142) -> Index Scan using wquserdata_pkey on wquserdata t0 (cost=0.00..240.80 rows=306 width=48) but if I use this: explain select t0.*,t1.* from wquserdata t0, users t1 where t0.username='lt' and t1.username='lt' and t0.username=t1.username; the result is exactly what I expected: Merge Join (cost=0.00..515.46 rows=937 width=190) -> Index Scan using users_username_key on users t1 (cost=0.00..267.01 rows=306 width=142) -> Index Scan using wquserdata_pkey on wquserdata t0 (cost=0.00..240.80 rows=306 width=48) I am using a view to select, so the latest query can not be executed in my application. Is there a way to get best performence and can be used in my view? (my view definition: Create View wqusers as SELECT t0.username, ..., t1 FROM users t0, wquserdata t1 WHERE (t0.username = t1.username); and I am performing query like this: select * from wqusers where username='lt';)
[ADMIN] foreign key question: the backend end the connection
Hi, I am using Postgresql 7.0.2, and have used some foreign key in my database, but I get some error like "the backend ended the connection" where I am trying to insert or delete a row to the master table(Delete cascade and update cascade). Of course, this error is no use to find the reason. So I am forced to forget foreign keys.
Re: [ADMIN] foreign key question: the backend end the connection
Have you renamed any tables used in the foreign keys (or dumped and restored with different names or some missing tables). Under 7.0.2 and earlier there is a problem with postgres crashing if a constraint trigger has bad values (due to renames or incomplete restores). This should be fixed for 7.1 with a message that it's unable to find the tablename. I believe that if you look through the archives (I forget which list though), you will find a patch for 7.0.2 that will turn the crash into an exception message with more information. Stephan Szabo [EMAIL PROTECTED] On Tue, 10 Oct 2000, lt wrote: > Hi, >I am using Postgresql 7.0.2, and have used some foreign key in my database, but I >get some error like "the backend ended the connection" where I am trying to insert or >delete a row to the master table(Delete cascade and update cascade). Of course, this >error is no use to find the reason. So I am forced to forget foreign keys. >
Re: [ADMIN] help
> please help in starting the postmaster and keeping it in the same state > always ..ie started , i'm a beginner, i tried > pg_ctl,postmaster,postgres... Read the INSTALL file with the source, it should tell you alot. > my database is kept in a dir where permission is denied to the user , Which user is not denied? > and if i login as the superuser, it doesn't recognize above commands.. This might be because you have not included the binaries in your PATH? Which superuser, by the way? Best wishes Gunnar.
Re: [ADMIN] foreign key question: the backend end the connection
This happens for me when the tables referenced in the foreign key don't exisit. Hope that helps! Gavin lt wrote: Hi, I am using Postgresql 7.0.2, and have used some foreign key in my database, but I get some error like "the backend ended the connection" where I am trying to insert or delete a row to the master table(Delete cascade and update cascade). Of course, this error is no use to find the reason. So I am forced to forget foreign keys.
[GENERAL] Re: [ADMIN] PostgreSQL encoding question
Anton Kalauzky wrote: [Problem with sort order in RH 6.1 upgraded to 6.2 versus straight 6.2 install] > So, do you have any idea about solving this problem? Thank you for your > time. This is due to the collation sequence set up in RH 6.2 when you do a fresh install. The upgrade doesn't touch the file in question, /etc/sysconfig/i18n. Copy this file as it is from the fresh RH 6.2 install over to the upgraded RH 6.1 install and see what happens. Red Hat changed, for better or for worse, the collation in RH 6.2 and later to more closely conform to ISO standards. Ask on pgsql-hackers, or directly e-mail [EMAIL PROTECTED] for more details. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] Re: [ADMIN] PostgreSQL encoding question
Lamar Owen <[EMAIL PROTECTED]> writes: > Anton Kalauzky wrote: > [Problem with sort order in RH 6.1 upgraded to 6.2 versus straight 6.2 > install] > > > So, do you have any idea about solving this problem? Thank you for your > > time. > > This is due to the collation sequence set up in RH 6.2 when you do a > fresh install. The upgrade doesn't touch the file in question, > /etc/sysconfig/i18n. Copy this file as it is from the fresh RH 6.2 > install over to the upgraded RH 6.1 install and see what happens. > > Red Hat changed, for better or for worse, the collation in RH 6.2 and > later to more closely conform to ISO standards. > > Ask on pgsql-hackers, or directly e-mail [EMAIL PROTECTED] for more > details. I'm reading here, and what Lamar's saying is correct. -- Trond Eivind Glomsrød Red Hat, Inc.
