[GENERAL] Postgresql Web Hosting
Hi, I'm trying to migrate a site to a new hosting company. The backend uses postgresql 8 and php. Anyone have thoughts on decent hosting companies for this sort of thing? I'm just looking at shared hosting as this isn't a resource intensive site. Thanks, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Baffled by "Group By" Resolved and Thanks
Yeah, it was right under my nose. Figured it out just before I got your email. Thank you. Matt Friedman - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Matt Friedman" <[EMAIL PROTECTED]> Cc: "PgSql General List" <[EMAIL PROTECTED]> Sent: Saturday, March 24, 2001 1:34 PM Subject: Re: Baffled by "Group By" - Please help! > "Matt Friedman" <[EMAIL PROTECTED]> writes: > > What am I missing? I'm not seeing why this query doesn't group everything > > and then return unique rows. > > Since you didn't show an example of the input and output, it's hard to > tell what your gripe is ... but I'll venture that you didn't want to > group by index.word_count. It doesn't make a lot of sense to group by > something you are using as an aggregate input. > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > ---(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
[GENERAL] Baffled by "Group By" - Please help!
For some reason, probably because I'm not understanding very well, I'm getting some duplicated rows for this query (below). My understanding is that "group by" brings all the rows together where the indicated columns are equal and then applies the aggregate to the column indicated; in this case: "sum". What am I missing? I'm not seeing why this query doesn't group everything and then return unique rows. Please let me know if you need more info and I will send it. SELECT index_uri.uri, index_uri.description, index_uri.title, index_type.type, index_type.icon, SUM(index.word_count) FROM index, index_word, index_uri, index_type WHERE ( index_word.word ~'eat.*' -- could be more or fewer comparisons here. OR index_word.word ~'disord.*' ) AND index_word.word_id=index.word_id AND index_uri.uri_id = index.uri_id AND index_type.type_id = index_uri.type_id GROUP BY index_uri.uri, index_uri.description, index_uri.title, index_type.type, index.word_count, index_type.icon ORDER BY sum DESC Matt Friedman ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Bad book review
I have it and have found it very useful. Matt Friedman Spry New Media - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "PostgreSQL-general" <[EMAIL PROTECTED]> Sent: Tuesday, February 13, 2001 11:06 PM Subject: Bad book review > I am not sure how many people have looked at my book on Amazon.com, but > I have received my first negative book review: > > http://www.amazon.com/exec/obidos/ASIN/0201703319/o/qid%3D976592762/sr%3D8-1 /ref%3Daps%5Fsr%5Fb%5F1%5F3/104-0116316-8891907 > > I guess everyone isn't going to like my book. :-) > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > >
[GENERAL] Re: Re: SELECT (sometimes) returning Zero Rows?
> This has the same smell about it as the one a couple of days ago > where the CR characters in a file loaded into the database using COPY were > upsetting things. I didn't use COPY but... I used a dump created by pg_dump. We had some problems with the database a while ago so I recreated it using a recent dump file. To reimport it I had psql read it's commands from the file and do it's thing that way. It ran smoothly and I didn't think anything of it. The command I used would have been: $psql -f /path/to/sql/file.sql Could this be causing the problem? If so, how would I go about fixing it? Many thanks, Matt Friedman - Original Message - From: "Christopher Sawtell" <[EMAIL PROTECTED]> To: "Matt Friedman" <[EMAIL PROTECTED]> Cc: "PgSql General List" <[EMAIL PROTECTED]> Sent: Tuesday, February 13, 2001 6:58 PM Subject: Re: Re: SELECT (sometimes) returning Zero Rows? > On Wed, 14 Feb 2001 15:03, you wrote: > > It's version 7.0.3 > > > > Here's the output for the selects using explain below. > > We had planned to do all of the optimization later. We just haven't had > > time to get to it yet. Could that be the problem? > > > > Anyhow here's the info. Please let me know if you need more information. > > > So, How did you populate your database? > > -- > Sincerely etc., > > NAME Christopher Sawtell > CELL PHONE 021 257 4451 > ICQ UIN45863470 > EMAIL csawtell @ xtra . co . nz > CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz > > -->> Please refrain from using HTML or WORD attachments in e-mails to me > <<-- > > >
[GENERAL] Re: SELECT (sometimes) returning Zero Rows?
It's version 7.0.3 Here's the output for the selects using explain below. We had planned to do all of the optimization later. We just haven't had time to get to it yet. Could that be the problem? Anyhow here's the info. Please let me know if you need more information. Appreciatively, Matt Friedman. parent_report_new=> explain parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new-> pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new-> = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new-> pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new-> LIMIT 2; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) -> Nested Loop (cost=0.00..4.38 rows=1 width=24) -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) -> Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new=> explain parent_report_new-> SELECT pr_article.title, pr_article.article_id FROM pr_article WHERE parent_report_new-> pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new-> = 1 AND pr_article.is_active = true AND pr_article.is_approved = true AND parent_report_new-> pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or parent_report_new(> (pr_article.end_date > 982093578 )) ORDER BY pr_article.release_date DESC parent_report_new-> LIMIT 3; NOTICE: QUERY PLAN: Sort (cost=4.39..4.39 rows=1 width=24) -> Nested Loop (cost=0.00..4.38 rows=1 width=24) -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) -> Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new=> explain parent_report_new-> SELECT DISTINCT ON (release_date, article_id) pr_article.title, parent_report_new-> pr_article.article_id, pr_cat.prog_name FROM pr_article WHERE parent_report_new-> pr_article.article_id = pr_article_age.article_id AND pr_article_age.age_id parent_report_new-> = 1 AND pr_article_cat.cat_id=pr_cat.cat_id AND pr_article.article_id = parent_report_new-> pr_article_cat.article_id AND pr_article.is_active = true AND parent_report_new-> pr_article.is_approved = true AND pr_article.release_date < 982093578 AND parent_report_new-> ((pr_article.end_date = 0) or (pr_article.end_date > 982093578 )) ORDER BY parent_report_new-> pr_article.release_date DESC LIMIT 5; NOTICE: QUERY PLAN: Unique (cost=7.63..7.64 rows=0 width=48) -> Sort (cost=7.63..7.63 rows=1 width=48) -> Nested Loop (cost=0.00..7.62 rows=1 width=48) -> Nested Loop (cost=0.00..5.58 rows=1 width=28) -> Nested Loop (cost=0.00..4.36 rows=1 width=12) -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) -> Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) -> Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=16) -> Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=20) EXPLAIN parent_report_new=> explain parent_report_new-> SELECT DISTINCT ON (pr_cat.name) pr_cat.name, pr_cat.prog_name FROM parent_report_new-> pr_article, pr_article_age, pr_article_cat, pr_cat WHERE parent_report_new-> pr_article_age.age_id = 1 AND pr_article_age.article_id = parent_report_new-> pr_article.article_id AND pr_article.article_id = pr_article_cat.article_id parent_report_new-> AND pr_article_cat.cat_id = pr_cat.cat_id AND pr_article.is_active = true parent_report_new-> AND pr_article.is_audio = false AND pr_article.is_approved = true AND parent_report_new-> pr_article.release_date < 982093578 AND ((pr_article.end_date = 0) or parent_report_new(> (pr_article.end_date > 982093578 )) parent_report_new-> ; NOTICE: QUERY PLAN: Unique (cost=7.64..7.64 rows=0 width=44) -> Sort (cost=7.64..7.64 rows=1 width=44) -> Nested Loop (cost=0.00..7.63 rows=1 width=44) -> Nested Loop (cost=0.00..6.40 rows=1 width=16) -> Nested Loop (cost=0.00..4.36 rows=1 width=12) -> Seq Scan on pr_article_age (cost=0.00..1.68 rows=1 width=4) -> Index Scan using pr_article_cat_article_id_key on pr_article_cat (cost=0.00..2.01 rows=1 width=8) -> Index Scan using pr_article_pkey on pr_article (cost=0.00..2.03 rows=1 width=4) -> Seq Scan on pr_cat (cost=0.00..1.10 rows=10 width=28) EXPLAIN
[GENERAL] Vacuum and Owner
I run as user "spry" on psql, I created the database as "spry" but when I run vacuum, all the system tables get skipped because "spry" is not the owner. Is there a way to fully vacuum my db and the system tables as a user other than "postgres"? Is is very important that the system tables are "vaccumed" in addition to my db tables? Matt Friedman
[GENERAL] Re: Trapping NOTICE using PHP
If you can run the program psql via php's exec command or something then the stdout might be available to you. Might work. If php is in safe mode this can be a challenge. Matt Friedman Spry New Media - Original Message - From: "Dan Wilson" <[EMAIL PROTECTED]> To: "pgsql general" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, February 12, 2001 10:43 PM Subject: Trapping NOTICE using PHP > Is there any way to trap the NOTICE output (specifically in PHP)? I would > like to impliment an EXPLAIN feature in phpPgAdmin and have been > unsuccessful in finding a way to do this because all the information > returned by EXPLAIN is in a NOTICE. > > Any ideas? > > -Dan > > >
[GENERAL] Lock won't release
I have a lock on a table that won't release. I know this seems very unusual but there appears to be a severe deadlock and pgsql has not release the lock. I've tried everything including recreating the db and trying to delete the old one. psql wouldn't even let me delete the old db. It says that other users are using the database. I'm assuming it's saying this because there's a lock on the table. Thanks for any help. Matt Friedman Spry New Media http://www.sprynewmedia.com Lead Programmer/Partner email: [EMAIL PROTECTED] phone: 250 744 3655 fax: 250 370 0436
[GENERAL] Table Locked - removing lock
I have a locked table that won't unlock. I do not appear to be able to select from the table. I suppose it's a deadlock, but I don't know how to release it. thanks, Matt Friedman
[GENERAL] pg_dump shell or php?
Any one written a shell or php script that runs pg_dump on a db say everyday or something? Any suggestions? thanks, Matt Friedman
[GENERAL] Re: postgres limitation -what does it mean? (8k row limit)
I've been following this thread but it's not clear to me what an 8k row limit means exactly. Does it mean that the size of all the data in that row must not be greater than 8k? That seems very small to me. Also, how does one change BLCKSZ? Some more detail on the subject would be great. thank you. Matt Friedman - Original Message - From: "Mitch Vincent" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 26, 2001 7:28 PM Subject: Re: postgres limitation > > > the max size of a row... > > > > 8k in pre v7.1, no limit in v7.1 an dlater ... > > 32k really... BLCKSZ can be changed.. I've had no trouble running a > production database with BLCKSZ set to 32k though there might be issues I'm > not aware of.. > > -Mitch > > >
