Re: [GENERAL] [pgsql-advocacy] European users mailing list
Joshua D. Drake wrote: > Peter Eisentraut wrote: >> Dave Page wrote: >>> As discussed at the first meeting of what will become the European >>> PostgreSQL Users Group after pgDay in Prato, we now have a mailing >>> list setup at [EMAIL PROTECTED] >>> >>> This is for the European users group, so is not really intended as a >>> technical list but as a place to discuss events, advocacy and other >>> topics relevant to our work in Europe. >> >> If it's not a general list, why did you name it "general"? That will >> certainly cause confusion. It sounds like -eu-advocacy might have >> been better. > > +1 -eu-advocacy seems to make more sense. With all due repect JD, you were not at the meeting at which this was discussed and are presumably unaware that we discussed more than just 'advocacy'. I expect there to be 3 or 4 -eu lists eventually, one of which may well be -advocacy. But for now, I was asked to arrange a general mailing list, which I have done. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] query to match '\N'
Hi, Am Freitag, 27. Jul 2007, 18:35:21 - schrieb pc: > I have a table test with columns col1 col2.col2 contains an entry > '\N' .I want to select all entries which have '\N' in col2.How do i > do that? > > select * from test where col2 like '\N' ; > select * from test where col2 like '\\N' ; select * from test where col2 like E'N'; select * from test where col2 =E'\\N'; Why use `like' here at all? Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PQntuples return type
Hi All. My question is simple: can the function PQntuples returns a negative integer? I've found in the PostgreSQL manual the following definition: Returns the number of rows (tuples) in the query result. int PQntuples(const PGresult *res); So, my doubt is: if the return type is int instead of unsigned int, is this function testable for negative return values? Thanks in advance. Luca _ The next generation of Hotmail is here! http://www.newhotmail.co.uk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] query to match '\N'
Bertram Scharpf wrote: > Hi, > > Am Freitag, 27. Jul 2007, 18:35:21 - schrieb pc: >> I have a table test with columns col1 col2.col2 contains an entry >> '\N' .I want to select all entries which have '\N' in col2.How do i >> do that? >> >> select * from test where col2 like '\N' ; >> select * from test where col2 like '\\N' ; > > select * from test where col2 like E'N'; > select * from test where col2 =E'\\N'; > > Why use `like' here at all? Presumably he wanted col2 like E'%N%'. But doesn't \N mean NULL, or would the OP be looking for literal '\N' strings in his data? Because if he's looking for NULLs it may be better to query for col2 IS NULL. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] query to match '\N'
Alban Hertroys skrev: > Presumably he wanted col2 like E'%N%'. > But doesn't \N mean NULL, or would the OP be looking for literal '\N' > strings in his data? Because if he's looking for NULLs it may be better > to query for col2 IS NULL. My guess is that this string was used to signify NULL in the file originally imported into the db. Nis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] query to match '\N'
Nis Jørgensen wrote: > Alban Hertroys skrev: > >> Presumably he wanted col2 like E'%N%'. >> But doesn't \N mean NULL, or would the OP be looking for literal '\N' >> strings in his data? Because if he's looking for NULLs it may be better >> to query for col2 IS NULL. > > My guess is that this string was used to signify NULL in the file > originally imported into the db. Which is basically what I was pointing out ;) It's all speculation anyway, we're guessing at what problem the OP tries to solve. I think either he is looking for NULL column values that exist in his input file as '\N' strings (strings cannot contain NULLs, so using "like" is pointless), or he is looking for failed conversions of \N from his input file that thus may have ended up as literal \N characters in column data. In the latter case there shouldn't be any columns that match "like '%N%'" but not "= 'N'". OTOH, we may be talking about an import failure, in which case anything is possible. Fixing that would probably be more difficult than fixing the cause of the failure and re-doing the import. As I said, it's all speculation. Without input from the OP there's not much point in continuing this discussion. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Possible new feature
All, I'm in the process of moving to PostGres from iAnywhere's SQL Anywhere v 10. One of the neat features from ASA 10 is the ability to create "proxy tables" These tables can be local or remote. The purpose of a proxy table is that once create it can be used just like any other table or view. You can use it in joins, subselects, etc. ASA sees it as just a normal table. The data can be stored in any ODBC compatible file system so it makes it easy to interact with all other database. The synatx is pretty simple : CREATE EXISTING TABLE [owner.]table-name [ (column-definition, ...) ] AT location-string column-definition : column-name data-type [NOT NULL] location-string : remote-server-name.[db-name].[owner].object-name | remote-server-name;[db-name];[owner];object-name example: CREATE EXISTING TABLE blurbs ( author_id ID not null, copy text not null) AT 'server_a.db1.joe.blurbs'; Now you can acces blurbs just like any table. Best Regards, Michael Gould All Coast Intermodal Services Inc.
Re: [GENERAL] [SQL] Tunning PostgreSQL performance for views on Windows
How big are the underlying tables? If they are large, are you partitioning? Since the values only change daily, if the end result is a reasonable size, have you considered using a CTAS rather than views? LewisC --- Ranieri Mazili <[EMAIL PROTECTED]> wrote: > Hello, > > I'm developing a BI and as database it's using postgresql 8.2, how > data > are very detailed, I'm creating a view to consolidate the most > important > data, but the performance of view is very poor, 1 minute to perform > more > or less without where clause. > I need to know how I can increase the performance, if exist some > option > to do cache, because the view will change only one time per day. > My configuration is default, without modifications after install. > I'm using windows 2003 server with a dell server with 4GB of > memory. > > To create the view, I created some functions, and then perform they > on > one select like: > select A.field1, B.field2, ... from function_A() A, function_B() > B... > Is this the best way to do it? > > I appreciate any help. > > Thanks > > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > --- Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ EnterpriseDB: The Definitive Reference http://tinyurl.com/39246e -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Possible new feature
On 7/30/07, mgould <[EMAIL PROTECTED]> wrote: > I'm in the process of moving to PostGres from iAnywhere's SQL Anywhere v 10. > One of the neat features from ASA 10 is the ability to create "proxy > tables" These tables can be local or remote. Check out the dblink contrib module that comes with PostgreSQL. It does the exact same thing, though without special syntax: create view foo as select * from dblink( 'host=1.2.3.4 dbname=remotedb user=dbuser password=secretpass', 'select id, title from foo') as remote_foo(id int, title text); Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Slow query but can't see whats wrong
24 jul 2007 kl. 16:10 skrev Tom Lane:
Henrik Zagerholm <[EMAIL PROTECTED]> writes:
I'm using pgsql 8.2.4 and I have this query which is sooo slow but I
can seem to figure out why.
Seems the core of the problem is the misestimation here:
"-> Index Scan using
tbl_file_idx on tbl_file (cost=0.01..8.34 rows=1 width=39) (actual
time=0.283..3339.003 rows=25039 loops=1)"
" Index Cond:
((lower
((file_name)::text) ~>=~ 'awstats'::character varying) AND (lower
((file_name)::text) ~<~ 'awstatt'::character varying))"
" Filter: (lower
((file_name)::text) ~~ 'awstats%'::text)"
Looks like either your stats are not up to date on this table, or you
need to increase the stats target for file_name. Anytime a scan row
estimate is off by a factor of 25000, you're going to get a bad
plan :-(
Thanks for the tips on getting this query faster.
What I did was.
Changed statistics on both file_name and tbl_file_idx.pg_expression_1
which made the stats look much better. A simple query test on
tbl_file showed that the the new stats worked.
EXPLAIN ANALYZE SELECT file_name FROM tbl_file WHERE lower(file_name)
LIKE lower('awstats%');
"Bitmap Heap Scan on tbl_file (cost=1157.12..11999.34 rows=39868
width=19) (actual time=49.751..258.550 rows=39959 loops=1)"
" Filter: (lower((file_name)::text) ~~ 'awstats%'::text)"
" -> Bitmap Index Scan on tbl_file_idx (cost=0.00..1147.15
rows=35881 width=0) (actual time=47.791..47.791 rows=39959 loops=1)"
"Index Cond: ((lower((file_name)::text) ~>=~
'awstats'::character varying) AND (lower((file_name)::text) ~<~
'awstatt'::character varying))"
"Total runtime: 308.610 ms"
I then added another table which as a many to one relationship with
tbl_file called tbl_file_structure. This is the one I want to do the
count on.
Surprisingly this query is about 100 times slower due to the SEQ scan
on tbl_file_structure. The foreign key fk_file_id is indexed and it
has statistics set to 200.
How come it makes an seq scan on this table? The count retrieved is
75 000 so its way lower than the total 3 834 059 rows.
Should I raise the statistics more? Is there a rule of thumb how much
the statistics should be reagards to the number of rows in the table?
Can I make my database adjust the statistics dynamically? I don't
want to go around to my customers changing statistics every time the
tables starts to fill up.
Anyway here is the slow query.
EXPLAIN ANALYZE SELECT COUNT(pk_file_structure_id) FROM
tbl_file_structure INNER JOIN tbl_file ON fk_file_id = pk_file_id
WHERE lower(file_name) like lower('awstats%');
"Aggregate (cost=172512.17..172512.18 rows=1 width=8) (actual
time=30316.316..30316.317 rows=1 loops=1)"
" -> Hash Join (cost=12673.69..171634.39 rows=351110 width=8)
(actual time=1927.730..30191.260 rows=75262 loops=1)"
"Hash Cond: (tbl_file_structure.fk_file_id =
tbl_file.pk_file_id)"
"-> Seq Scan on tbl_file_structure (cost=0.00..80537.59
rows=3834059 width=16) (actual time=10.056..14419.662 rows=3834059
loops=1)"
"-> Hash (cost=11999.34..11999.34 rows=39868 width=8)
(actual time=1896.859..1896.859 rows=39959 loops=1)"
" -> Bitmap Heap Scan on tbl_file
(cost=1157.12..11999.34 rows=39868 width=8) (actual
time=457.867..1779.792 rows=39959 loops=1)"
"Filter: (lower((file_name)::text) ~~ 'awstats
%'::text)"
"-> Bitmap Index Scan on tbl_file_idx
(cost=0.00..1147.15 rows=35881 width=0) (actual time=450.469..450.469
rows=39959 loops=1)"
" Index Cond: ((lower((file_name)::text)
~>=~ 'awstats'::character varying) AND (lower((file_name)::text) ~<~
'awstatt'::character varying))"
"Total runtime: 30316.739 ms"
Thanks for your help,
Henrik
regards, tom lane
---(end of
broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] PQntuples return type
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > So, my doubt is: if the return type is int instead of unsigned int, > is this function testable for negative return values? A quick glance at the code in fe-exec.c and fe-protocol3.c shows that the underlying variable starts at 0 as an int and in incremented by one every row, so it seems possible that it could wrap around for very large results sets and/or boxes with a low representation of 'int'. There may be some other safeguards in place I did not see to prevent this, but I don't see a reason why we shouldn't use unsigned int or unsigned long int here, both for ntups and the return value of the function. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200707300937 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGrfPXvJuQZxSWSsgRA6gZAJ9O5dkgEIstoqhcYjz87V2REUhLWQCgr+uW 1eIVpiahum4ML0Zz7ANlrl0= =YqJu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] European users mailing list
On Monday 30 July 2007 03:47, Dave Page wrote:
> Joshua D. Drake wrote:
> > Peter Eisentraut wrote:
> >> Dave Page wrote:
> >>> As discussed at the first meeting of what will become the European
> >>> PostgreSQL Users Group after pgDay in Prato, we now have a mailing
> >>> list setup at [EMAIL PROTECTED]
> >>>
> >>> This is for the European users group, so is not really intended as a
> >>> technical list but as a place to discuss events, advocacy and other
> >>> topics relevant to our work in Europe.
> >>
> >> If it's not a general list, why did you name it "general"? That will
> >> certainly cause confusion. It sounds like -eu-advocacy might have
> >> been better.
> >
> > +1 -eu-advocacy seems to make more sense.
>
> With all due repect JD, you were not at the meeting at which this was
> discussed and are presumably unaware that we discussed more than just
> 'advocacy'. I expect there to be 3 or 4 -eu lists eventually, one of
> which may well be -advocacy.
>
*shrug* I wasn't there either, but seems all the emails I have seen reffered
to it as the "European PostgreSQL Users Group", so I expected it to look more
like other users groups, ie. [EMAIL PROTECTED]
> But for now, I was asked to arrange a general mailing list, which I have
> done.
>
Except we already had a general european mailing list, so I'm really not clear
on what the above is meant for accomplishing.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Data on NAS / NFS
I've had trouble with NFS files on nfs filesystems disappearing for a second and reappearing. I had to add a retry loop with a delay in my code that does file reading. I wouldn't try running a production level postgres over nfs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] European users mailing list
Robert Treat wrote: > On Monday 30 July 2007 03:47, Dave Page wrote: >> Joshua D. Drake wrote: >>> Peter Eisentraut wrote: Dave Page wrote: > As discussed at the first meeting of what will become the European > PostgreSQL Users Group after pgDay in Prato, we now have a mailing > list setup at [EMAIL PROTECTED] > > This is for the European users group, so is not really intended as a > technical list but as a place to discuss events, advocacy and other > topics relevant to our work in Europe. If it's not a general list, why did you name it "general"? That will certainly cause confusion. It sounds like -eu-advocacy might have been better. >>> +1 -eu-advocacy seems to make more sense. >> With all due repect JD, you were not at the meeting at which this was >> discussed and are presumably unaware that we discussed more than just >> 'advocacy'. I expect there to be 3 or 4 -eu lists eventually, one of >> which may well be -advocacy. >> > > *shrug* I wasn't there either, but seems all the emails I have seen reffered > to it as the "European PostgreSQL Users Group", so I expected it to look more > like other users groups, ie. [EMAIL PROTECTED] I was at that meeting and for the record: there is no european postgresql user group (yet) - so a general list seems vastly appropriate for discussing "general" european stuff. If we ever get a european user group we can simply add a list for that but what we now need is a list to discuss general topics and only time will tell if we ever need eupug@, pgsql-eu-advocacy@ or nothing at all. > >> But for now, I was asked to arrange a general mailing list, which I have >> done. >> > > Except we already had a general european mailing list, so I'm really not > clear > on what the above is meant for accomplishing. that list was not running on official infrastructure and only meant as a short term solution until there was enough momentum and interest to put it on official infrastructure. Stefan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] European users mailing list
On Monday 30 July 2007 Robert Treat's cat, walking on the keyboard, wrote: > *shrug* I wasn't there either, but seems all the emails I have seen > reffered to it as the "European PostgreSQL Users Group", so I expected it > to look more like other users groups, ie. [EMAIL PROTECTED] This mailing list is supposed to be a coordination infrastructure for discussing about the eupug to be or not to be (of course I hope the former). Moreover I don't see any difference with the mailing list "template" related to french, german, ... mailing lists. > > > But for now, I was asked to arrange a general mailing list, which I have > > done. > > Except we already had a general european mailing list, so I'm really not > clear on what the above is meant for accomplishing. Where is this mailing list? Maybe I'm becoming blind but I cannot see it on the mailing list pageand what is the main purpose of the list you are talking about? Luca ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Data on NAS / NFS
On 28.07.2007 06:48, Jean-Denis Girard wrote: The NAS would be from LaCie, using 4 disks (Raid5 + spare) (http://www.lacie.com/fr/products/product.htm?pid=10876), mounted via NFS from a Linux server running Postgresql . Sorry, but that sounds like a sick setup.. 1.) RAID 5 is a bad choice for a DBMS, due to implicit reads caused by parity calculation. 2.) Most cheap NAS (such as this one) have way to high latency for a DBMS. 3.) NFS is nothing you want to have a DBMS running on top. 4.) I don't think this device has battery backed cache. 5.) At least I have bad experiences with Lacie storage devices (Bigger Disk Extreme) To sum it up.. I'd say what you get with that setup is a dead slow, unstable PostgreSQL experience with a good chance of data corruption on power failure. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Core reported from vaccum function.
Mavinakuli, Prasanna (STSD) wrote: > > Hello Alvaro, > > Thanks for your reply. > > We could see > "Fix potential-data-corruption bug in how VACUUM FULL handles UPDATE > chains (Tom, Pavan Deolasee) " in 7.4.17 release notes. > > Could you please elaborate more on the above problem.Meaning what was > the actual problem and what fix has been done etc? Here is the CVS log entry: 2007-03-14 14:48 tgl * src/backend/commands/vacuum.c (1.263.2.3): Fix a longstanding bug in VACUUM FULL's handling of update chains. The code did not expect that a DEAD tuple could follow a RECENTLY_DEAD tuple in an update chain, but because the OldestXmin rule for determining deadness is a simplification of reality, it is possible for this situation to occur (implying that the RECENTLY_DEAD tuple is in fact dead to all observers, but this patch does not attempt to exploit that). The code would follow a chain forward all the way, but then stop before a DEAD tuple when backing up, meaning that not all of the chain got moved. This could lead to copying the chain multiple times (resulting in duplicate copies of the live tuple at its end), or leaving dangling index entries behind (which, aside from generating warnings from later vacuums, creates a risk of wrong query results or bogus duplicate-key errors once the heap slot the index entry points to is repopulated). The fix is to recheck HeapTupleSatisfiesVacuum while following a chain forward, and to stop if a DEAD tuple is reached. Each contiguous group of RECENTLY_DEAD tuples will therefore be copied as a separate chain. The patch also adds a couple of extra sanity checks to verify correct behavior. Per report and test case from Pavan Deolasee. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Optimising SELECT on a table with one million rows
Hi, I'm fairly new with Postgresql, so I am not sure if the performance problems I'm having are due to poorly constructed queries/indices, or if I bumped into more fundamental problems requiring a design of my database structure. That's why I'm requesting your help. Here's the situation: I have three tables: Users, Stories, and Comments. Stories have an author (a user), and a comment is associated with a story and with the user who posted it. The structure of the database is therefore fairly simple: (there are also some sequences, which I've omitted for clarity) CREATE TABLE users ( user_id int UNIQUE NOT NULL, user_name text, PRIMARY KEY (user_id) ); CREATE TABLE stories ( story_idint UNIQUE NOT NULL, story_title text, story_body text, story_timestamp timestamptz, story_authorint REFERENCES users (user_id) NOT NULL, PRIMARY KEY (story_id) ); CREATE TABLE comments ( comment_id int UNIQUE NOT NULL, comment_title text, comment_bodytext, comment_timestamp timestamptz, comment_story int REFERENCES stories (story_id) NOT NULL, comment_author int REFERENCES users (user_id) NOT NULL, PRIMARY KEY (comment_id) ); I've also populated the database with some test data, comprising 1,000 users, 1,000 stories (one per user), and 1,000,000 comments (one comment per user per story). Now, the query I wish to optimise is also simple: get me all comments (the comment_id suffices) and corresponding user *names* for a given story. If for example the story_id is 100, the query looks like this: SELECT comments.comment_id, users.user_name FROM comments, users WHERE comments.comment_story = 100 AND comments.comment_author = users.user_id; The problem is that this query takes a *very* long time. With the said 1,000,000 comments, it needs at least 1100ms on my system. "Explain analyze" tells me that a sequential scan is being performed on both users and comments: Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual time=3.674..1144.779 rows=1000 loops=1) Hash Cond: ((comments.comment_author)::integer = (users.user_id)::integer) -> Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) (actual time=0.185..1136.067 rows=1000 loops=1) Filter: ((comment_story)::integer = 100) -> Hash (cost=16.00..16.00 rows=1000 width=14) (actual time=3.425..3.425 rows=1000 loops=1) -> Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) (actual time=0.068..1.845 rows=1000 loops=1) Total runtime: 1146.424 ms On the long run, I guess one possible solution to this problem will be to partition the comments table into a number of sub-tables, most likely based on the timestamp attribute (by having current versus historic data). Nevertheless, I am wondering if there are other more straightforward ways to optimise this query. Some clever use of indices, perhaps? Or is the way I am now constructing the select non-optimal? Or do I need some pixie-magic on the Postgresql settings? Anyway, any suggestions are welcome! (and thanks in advance) Regards, C.S. Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Optimising SELECT on a table with one million rows
First question... did you create the appropriate indexes on the appropriate columns for these tables? Foreign keys do not implicitly create indexes in postgres. Bryan On 7/30/07, Cultural Sublimation <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm fairly new with Postgresql, so I am not sure if the performance > problems I'm having are due to poorly constructed queries/indices, > or if I bumped into more fundamental problems requiring a design of > my database structure. That's why I'm requesting your help. > > Here's the situation: I have three tables: Users, Stories, and Comments. > Stories have an author (a user), and a comment is associated with a > story and with the user who posted it. The structure of the database > is therefore fairly simple: (there are also some sequences, which I've > omitted for clarity) > > > CREATE TABLE users > ( > user_id int UNIQUE NOT NULL, > user_name text, > PRIMARY KEY (user_id) > ); > > > CREATE TABLE stories > ( > story_idint UNIQUE NOT NULL, > story_title text, > story_body text, > story_timestamp timestamptz, > story_authorint REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (story_id) > ); > > > CREATE TABLE comments > ( > comment_id int UNIQUE NOT NULL, > comment_title text, > comment_bodytext, > comment_timestamp timestamptz, > comment_story int REFERENCES stories (story_id) NOT > NULL, > comment_author int REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (comment_id) > ); > > > I've also populated the database with some test data, comprising 1,000 > users, 1,000 stories (one per user), and 1,000,000 comments (one comment > per user per story). > > Now, the query I wish to optimise is also simple: get me all comments (the > comment_id suffices) and corresponding user *names* for a given story. > If for example the story_id is 100, the query looks like this: > > SELECT comments.comment_id, users.user_name > FROM comments, users > WHERE comments.comment_story = 100 AND comments.comment_author = > users.user_id; > > The problem is that this query takes a *very* long time. With the said > 1,000,000 comments, it needs at least 1100ms on my system. "Explain > analyze" tells me that a sequential scan is being performed on both > users and comments: > > Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual > time=3.674..1144.779 rows=1000 loops=1) >Hash Cond: ((comments.comment_author)::integer = (users.user_id > )::integer) >-> Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) > (actual > time=0.185..1136.067 rows=1000 loops=1) > Filter: ((comment_story)::integer = 100) >-> Hash (cost=16.00..16.00 rows=1000 width=14) (actual time= > 3.425..3.425 > rows=1000 loops=1) > -> Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) > (actual > time=0.068..1.845 rows=1000 loops=1) > Total runtime: 1146.424 ms > > On the long run, I guess one possible solution to this problem will be > to partition the comments table into a number of sub-tables, most likely > based on the timestamp attribute (by having current versus historic data). > Nevertheless, I am wondering if there are other more straightforward ways > to optimise this query. Some clever use of indices, perhaps? Or is > the way I am now constructing the select non-optimal? Or do I need > some pixie-magic on the Postgresql settings? Anyway, any suggestions > are welcome! (and thanks in advance) > > Regards, > C.S. > > > > > > > Yahoo! oneSearch: Finally, mobile search > that gives answers, not web links. > http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ >
Re: [GENERAL] Optimising SELECT on a table with one million rows
Cultural Sublimation wrote: CREATE TABLE users ( user_id int UNIQUE NOT NULL, user_name text, PRIMARY KEY (user_id) ); CREATE TABLE stories ( story_idint UNIQUE NOT NULL, story_title text, story_body text, story_timestamp timestamptz, story_authorint REFERENCES users (user_id) NOT NULL, PRIMARY KEY (story_id) ); CREATE TABLE comments ( comment_id int UNIQUE NOT NULL, comment_title text, comment_bodytext, comment_timestamp timestamptz, comment_story int REFERENCES stories (story_id) NOT NULL, comment_author int REFERENCES users (user_id) NOT NULL, PRIMARY KEY (comment_id) ); I've also populated the database with some test data, comprising 1,000 users, 1,000 stories (one per user), and 1,000,000 comments (one comment per user per story). Now, the query I wish to optimise is also simple: get me all comments (the comment_id suffices) and corresponding user *names* for a given story. If for example the story_id is 100, the query looks like this: SELECT comments.comment_id, users.user_name FROM comments, users WHERE comments.comment_story = 100 AND comments.comment_author = users.user_id; The problem is that this query takes a *very* long time. With the said 1,000,000 comments, it needs at least 1100ms on my system. "Explain analyze" tells me that a sequential scan is being performed on both users and comments: What else is it supposed to do? You haven't created any indexes. I'm also guessing that you haven't analysed the tables either. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Optimising SELECT on a table with one million rows
Cultural Sublimation skrev: > Hi, > > I'm fairly new with Postgresql, so I am not sure if the performance > problems I'm having are due to poorly constructed queries/indices, > or if I bumped into more fundamental problems requiring a design of > my database structure. That's why I'm requesting your help. > > Here's the situation: I have three tables: Users, Stories, and Comments. > Stories have an author (a user), and a comment is associated with a > story and with the user who posted it. The structure of the database > is therefore fairly simple: (there are also some sequences, which I've > omitted for clarity) > > > CREATE TABLE users > ( > user_id int UNIQUE NOT NULL, > user_name text, > PRIMARY KEY (user_id) > ); > > > CREATE TABLE stories > ( > story_idint UNIQUE NOT NULL, > story_title text, > story_body text, > story_timestamp timestamptz, > story_authorint REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (story_id) > ); > > > CREATE TABLE comments > ( > comment_id int UNIQUE NOT NULL, > comment_title text, > comment_bodytext, > comment_timestamp timestamptz, > comment_story int REFERENCES stories (story_id) NOT NULL, > comment_author int REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (comment_id) > ); You need indices on comment.comment_story (and probably later for comment_author). You should ALWAYS add an index on a FOREIGN KEY column unless you have a very good reason not to. So: CREATE INDEX comments_story_idx ON comments(comment_story); CREATE INDEX comments_author_idx ON comments(comment_author); CREATE INDEX story_author_idx ON story(story_author); Thge first of these should remove the need for a seqscan on comments for your query. The seqscan on users is not a problem - you are returning data from all the rows, so a seqscan is the smart thing to do. Nis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Optimising SELECT on a table with one million rows
Cultural Sublimation wrote: SELECT comments.comment_id, users.user_name FROM comments, users WHERE comments.comment_story = 100 AND comments.comment_author = users.user_id; The problem is that this query takes a *very* long time. With the said 1,000,000 comments, it needs at least 1100ms on my system. "Explain analyze" tells me that a sequential scan is being performed on both users and comments: Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual time=3.674..1144.779 rows=1000 loops=1) Hash Cond: ((comments.comment_author)::integer = (users.user_id)::integer) -> Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) (actual time=0.185..1136.067 rows=1000 loops=1) Filter: ((comment_story)::integer = 100) -> Hash (cost=16.00..16.00 rows=1000 width=14) (actual time=3.425..3.425 rows=1000 loops=1) -> Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) (actual time=0.068..1.845 rows=1000 loops=1) Total runtime: 1146.424 ms If you have no index on comments.comment_author, then a seqscan will be required for your join between comments and users. Similarly, if you have no index on comments.comment_story, then any query against comments that uses that column as part of a predicate will require a seqscan of the comments table. Note that an FK constraint does not automatically create an index on the underlying column. You need to create the actual index yourself if it will be necessary for your queries. On the long run, I guess one possible solution to this problem will be to partition the comments table into a number of sub-tables, most likely based on the timestamp attribute (by having current versus historic data). Partitioning on comments.comment_timestamp won't help you at all for this particular query, since you don't have a condition in your query dependent upon that value. It might help you for other queries (such as gathering up all the comments posted on a particular day, or during some other time range), but it won't make any positive difference for this query. -Jon -- Senior Systems Developer Media Matters for America http://mediamatters.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Optimising SELECT on a table with one million rows
On Jul 30, 12:01 pm, [EMAIL PROTECTED] (Cultural Sublimation) wrote: > Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual > time=3.674..1144.779 rows=1000 loops=1) >Hash Cond: ((comments.comment_author)::integer = (users.user_id)::integer) >-> Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) (actual > time=0.185..1136.067 rows=1000 loops=1) > Filter: ((comment_story)::integer = 100) >-> Hash (cost=16.00..16.00 rows=1000 width=14) (actual time=3.425..3.425 > rows=1000 loops=1) > -> Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) (actual > time=0.068..1.845 rows=1000 loops=1) > Total runtime: 1146.424 ms Create an index on comments.comment_story column. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Optimising SELECT on a table with one million rows
Hi, > If you have no index on comments.comment_author, then a seqscan will be > required for your join between comments and users. Similarly, if you > have no index on comments.comment_story, then any query against comments > that uses that column as part of a predicate will require a seqscan of > the comments table. I see. As I said, I'm still fairly new to this... > Note that an FK constraint does not automatically create an index on the > underlying column. You need to create the actual index yourself if it > will be necessary for your queries. I see what you mean. The basic idea then is to take a look at the typical queries and to create indices based on them. Is there a good guide on index creation for optimisation purposes? > Partitioning on comments.comment_timestamp won't help you at all for > this particular query, since you don't have a condition in your query > dependent upon that value. It might help you for other queries (such as > gathering up all the comments posted on a particular day, or during some > other time range), but it won't make any positive difference for this query. You are right. Come to think of it, partitioning the comments table based on comment_story might make more sense, since the overwhelming majority of queries will be like the one I just mentioned: asking for all comments of a given story. Anyway, thanks a lot for your help! (And that goes for all the other people who also given their 2 cents) Best regards, C.S. Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] tables not in list
Hi List, I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/array of ones that are in my list but not in the db. So for example: CREATE TABLE test ( somecol integer ) WITHOUT OIDS; CREATE TABLE bar ( barcol integer ) WITHOUT OIDS; Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that. select array( select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public' and table_name = any (array['test', 'bar', 'foo'])) Thanks in advance for any help on this. Lee This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
Re: [GENERAL] tables not in list
put all your tables in a new table (public.tables) select table_name from public.tables where public.tables.table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public') On 30 Jul 2007, at 20:31, Lee Keel wrote: Hi List, I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/ array of ones that are in my list but not in the db. So for example: CREATE TABLE test ( somecol integer ) WITHOUT OIDS; CREATE TABLE bar ( barcol integer ) WITHOUT OIDS; Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that. select array( select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public' and table_name = any (array['test', 'bar', 'foo'])) Thanks in advance for any help on this. Lee This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
[GENERAL] plperl syntax highlighting for vi
Has anyone taken a stab at adding plperl syntax highlighting for vi? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Stored Proc Problem
I'm writing a stored procedure that will execute as a trigger. Arguments being passed to the procedure are field names. My goal is to convert those field names into the field values. However, I've not been successful. I thought that the following should have worked. EXECUTE ''field_val := NEW.''||tg_argv[1]||'';''; Nor does EXECUTE ''SELECT INTO field_val NEW.''||tg_argv[1]||'';''; I've tried using open for execute too... No luck. Is this possible in pure SQL? Rob ---(end of broadcast)--- TIP 1: 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] Auto Starting +/or Shutdown on OS X
I'm using scripts in /Library/StartupItems/PostgreSQL
PostgreSQL starts manually just fine via
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l
/usr/local/pgsql/logfile start
PostgreSQL will not start on System restart using files in
/Library/StartupItems/PostgreSQL (See below).
If I execute the start command above just after a System start I get
pg_ctl: another server may be running; trying to start server anyway
server starting
and then it starts and runs fine.
If I shut down PG using
pg_ctl stop (no users connected)
it stops and subsequently starts using code above just fine, w/o the
warning.
---
I added the command:
date > /Users/showmefile
to appropriate portions of /Library/StartupItems/PostgreSQL/PostgreSQL
as seen below, and the script portions ARE appropriately being executed.
How come shutdown isn't clearing things up?
Or is the problem in the startup?
Thanks!
smithrn (at) u (dot) washington (dot) edu
PostgreSQL
#!/bin/sh
##
# PostgreSQL RDBMS Server
##
# PostgreSQL boot time startup script for Darwin/Mac OS X. To install,
change
# the "prefix", "PGDATA", "PGUSER", and "PGLOG" variables below as
# necessary. Next, create a new directory,
"/Library/StartupItems/PostgreSQL".
# Then copy this script and the accompanying "StartupParameters.plist" file
# into that directory. The name of this script file *must* be the same
as the
# directory it is in. So you'll end up with these two files:
#
#/Library/StartupItems/PostgreSQL/PostgreSQL
#/Library/StartupItems/PostgreSQL/StartupParameters.plist
#
# Next, add this line to the /etc/hostconfig file:
#
# POSTGRESQLSERVER=-YES-
#
# The startup bundle will now be ready to go. To prevent this script from
# starting PostgreSQL at system startup, simply change that line in
# /etc/hostconfig back to:
#
# POSTGRESQLSERVER=-NO-
#
# For more information on Darwin/Mac OS X startup bundles, see this article:
#
#
http://www.opensource.apple.com/projects/documentation/howto/html/SystemStarter_HOWTO.html
#
# Created by David Wheeler, 2002.
# modified by Ray Aspeitia 12-03-2003 :
# added log rotation script to db startup
# modified StartupParameters.plist "Provides" parameter to make it
easier to
# start and stop with the SystemStarter utitlity
# use the below command in order to correctly start/stop/restart PG with
log rotation script:
# SystemStarter [start|stop|restart] PostgreSQL
## EDIT FROM HERE
# Installation prefix
prefix="/usr/local/pgsql"
# Data directory
PGDATA="/usr/local/pgsql/data"
# Who to run the postmaster as, usually "postgres". (NOT "root")
PGUSER="postgres"
# the logfile path and name (NEEDS to be writeable by PGUSER)
PGLOG="${PGDATA}/logs/logfile"
# do you want to rotate the log files, 1=true 0=false
ROTATELOGS=1
# logfile rotate in seconds
ROTATESEC="604800"
## STOP EDITING HERE
# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# What to use to start up the postmaster (we do NOT use pg_ctl for this,
# as it adds no value and can cause the postmaster to misrecognize a stale
# lock file)
DAEMON="$prefix/bin/postmaster"
# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"
# The apache log rotation utility
LOGUTIL="/usr/sbin/rotatelogs"
. /etc/rc.common
StartService () {
if [ "${POSTGRESQLSERVER:=-NO-}" = "-YES-" ]; then
ConsoleMessage "Starting PostgreSQL database server"
if [ "${ROTATELOGS}" = "1" ]; then
# sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' |
${LOGUTIL} '${PGLOG}' ${ROTATESEC} &"
sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' 2>&1 |
${LOGUTIL} '${PGLOG}' ${ROTATESEC} &"
else
sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' &" >>$PGLOG 2>&1
fi
fi
date > /Users/PG_Started
}
StopService () {
ConsoleMessage "Stopping PostgreSQL database server"
sudo -u $PGUSER $PGCTL stop -D "$PGDATA" -s -m fast
date > /Users/PG_Stopped
}
RestartService () {
if [ "${POSTGRESQLSERVER:=-NO-}" = "-YES-" ]; then
ConsoleMessage "Restarting PostgreSQL database server"
# should match StopService:
sudo -u $PGUSER $PGCTL stop -D "$PGDATA" -s -m fast
# should match StartService:
if [ "${ROTATELOGS}" = "1" ]; then
# sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' |
${LOGUTIL} '${PGLOG}' ${ROTATESEC} &"
sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' 2>&1 |
${LOGUTIL} '${PGLOG}' ${ROTATESEC} &"
else
sudo -u $PGUSER sh -c "${DAEMON} -D '${PGDATA}' &" >>$PGLOG 2>&1
fi
Re: [GENERAL] Auto Starting +/or Shutdown on OS X
On Jul 30, 2007, at 16:14 , Ralph Smith wrote: I'm using scripts in /Library/StartupItems/PostgreSQL I haven't used a StartupItem for PostgreSQL since launchd was released. I haven't looked too closely at your configuration, but you might consider using a launchd plist instead. Here's one of mine (from /Library/LaunchDaemons/org.postgresql.postgres.v824.plist). Note that if you use one of the plist launchd configuration editors out there you'll probably lose the comments when you save. Hope this helps. Michael Glaesemann grzm seespotcode net www.apple.com/DTDs/PropertyList-1.0.dtd"> Label org.postgresql.postgres.v824 OnDemand Disabled ProgramArguments /usr/local/pgsql/pgsql-8.2.4/bin/postmasterstring> -D /usr/local/pgsql/pgsql-8.2.4/data -c redirect_stderr=YES ServiceDescription PostgreSQL Server v8.2.4 UserName postgres GroupName postgres ---(end of broadcast)--- TIP 1: 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] pg_dump
just checking to be sure. pg_dump does not support SSL connections? I have been using pgsql with ssl connections to my database. But when I tried pg_dump I was hit with the "no ssl" error message. Didn't see an option for it in the RTFM so .. Am I correct in assuming that pg_dump/pg_restore are not supposed to run via ssl? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_dump
On Mon, Jul 30, 2007 at 07:26:45PM -0400, Tom Allison wrote: > pg_dump does not support SSL connections? pg_dump sits atop libpq, which can use SSL if PostgreSQL was built with SSL support. > I have been using pgsql with ssl connections to my database. > But when I tried pg_dump I was hit with the "no ssl" error message. What was the exact command and the exact error message? > Didn't see an option for it in the RTFM so .. Am I correct in > assuming that pg_dump/pg_restore are not supposed to run via ssl? No, that's not correct; pg_dump can use SSL just as any other libpq application can. Are you sure your pg_dump is linked against an SSL-enabled libpq? Have you tried setting the PGSSLMODE environment variable? What version of PostgreSQL are you running? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] European users mailing list
Dave Page wrote: This is for the European users group, so is not really intended as a technical list but as a place to discuss events, advocacy and other topics relevant to our work in Europe. If it's not a general list, why did you name it "general"? That will certainly cause confusion. It sounds like -eu-advocacy might have been better. +1 -eu-advocacy seems to make more sense. With all due repect JD, you were not at the meeting at which this was discussed and are presumably unaware that we discussed more than just 'advocacy'. Fair enough, at least from my perspective it seems odd to have -general because we have pgsql-general and the immediate reaction is going to be, oh I am in EU and have general technical questions. *shrug* if it has all been worked out, so be it. I just think it will be confusing. Sincerely, Joshua D. Drake I expect there to be 3 or 4 -eu lists eventually, one of which may well be -advocacy. But for now, I was asked to arrange a general mailing list, which I have done. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: 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: [GENERAL] PostgreSQL, PGDAY, PGParty and OSCON 2007 Rocked!
Joshua D. Drake wrote: Richard Broersma Jr wrote: --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: Hello, For those who were too square to be there, For us squares, are there any pictures avaliable of this event? Yes :)... they are coming. I hope to have them all this weekend. I call this one: "See! EDB and CMD *are* friends!" http://www.commandprompt.com/images/oscon_2007.png For those who don't know, the big guy with the huge smile across his face is Jim Nasby of EDB, while the guy he is ahem, hugging is me, Joshua Drake. Sincerely, Joshua D. Drake Joshua D. Drake Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL, PGDAY, PGParty and OSCON 2007 Rocked!
Joshua D. Drake wrote: > Joshua D. Drake wrote: >> Richard Broersma Jr wrote: >>> --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: >>> Hello, For those who were too square to be there, >>> >>> For us squares, are there any pictures avaliable of this event? >> Yes :)... they are coming. I hope to have them all this weekend. > > I call this one: > > "See! EDB and CMD *are* friends!" > > http://www.commandprompt.com/images/oscon_2007.png > > For those who don't know, the big guy with the huge smile across his face > is Jim Nasby of EDB, while the guy he is ahem, hugging is me, Joshua Drake. Left to right: Michael from UGA (surely everyone knows what UGA is, right?), Josh Berkus, Jim Nasby, Josh Drake, David Fetter, Robert Treat. Prerna Mamgain and Selena Deckelmann (no Josh, it's not "Dickman") have pictures of the booth with all the bunnies; though neither of them promised they would be sending the pictures, I'll bet they'll try ;-) -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" "People get annoyed when you try to debug them." (Larry Wall) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Proc Problem
Robert Landrum <[EMAIL PROTECTED]> writes: > I'm writing a stored procedure that will execute as a trigger. > Arguments being passed to the procedure are field names. > My goal is to convert those field names into the field values. However, > I've not been successful. I thought that the following should have worked. > EXECUTE ''field_val := NEW.''||tg_argv[1]||'';''; > Nor does > EXECUTE ''SELECT INTO field_val NEW.''||tg_argv[1]||'';''; > I've tried using open for execute too... No luck. > Is this possible in pure SQL? Well, that's not "pure SQL", it's plpgsql ... but the answer is no. plpgsql can't deal with selection of run-time-specified fields from its variables. You'd have better luck coding this in one of the other PL languages. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] alter table table add column
Hi, Anybody knows how to add column with reference to BEFORE or AFTER any given column? Let say here's my table structure: Column | Type| Modifiers --+---+--- surname | character varying | lastname | character varying | address | character varying | And, I want to add the field name age with type integer after lastname OR before the address field. How to I do that? I would really appreciate your response. Thanks in advance. == Ronald Rojas Systems Administrator Linux Registered User #427229 == Arnold's Laws of Documentation: (1) If it should exist, it doesn't. (2) If it does exist, it's out of date. (3) Only documentation for useless programs transcends the first two laws.
Re: [GENERAL] [NOVICE] alter table table add column
[Please don't post the same question to many lists. Choose one. If you're unsure if it's the correct list for your question, ask if there's a more appropriate one. This question is find for -novice or - general. Thanks.] On Jul 30, 2007, at 23:19 , Ronald Rojas wrote: And, I want to add the field name age with type integer after lastname OR before the address field. How to I do that? Can't without dumping the database, altering the schema in the dump, and reloading. But why does it matter? Just call the columns in the order you want. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How do I create a database if I can't connect to it?
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 7/27/07, David Fetter <[EMAIL PROTECTED]> wrote: >>> For a short description, I'll just show the sequence of commands I'm trying >>> to execute: >> >> This looks like a mistake. Unless you plan to develop the PostgreSQL >> code itself, you should not be installing from source. > I don't necessarily agree with this advice. Me either. I was about to complain about this bit: >>> [EMAIL PROTECTED]> createdb foo -p 5435 which is in fact not valid switch syntax for createdb --- the dbname has to come after optional switches --- and experimentation shows that every createdb since about version 7.3 has rejected it. So either Paolo wasn't giving us a verbatim transcript, or he had a really ancient version of createdb in his path. Subsequent discussion suggests a permissions issue but I'm not convinced that was all there was to it. In any case "don't install from source" is bad/irrelevant advice. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-advocacy] [GENERAL] PostgreSQL, PGDAY, PGParty and OSCON 2007 Rocked!
On Monday 30 July 2007 23:39, Alvaro Herrera wrote:
> Joshua D. Drake wrote:
> > http://www.commandprompt.com/images/oscon_2007.png
> >
> Left to right: Michael from UGA
> (surely everyone knows what UGA is, right?), Josh Berkus, Jim Nasby,
> Josh Drake, David Fetter, Robert Treat.
>
Michael is Michael Brewer, from the University of Georgia in Athens.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL install problem
To: [EMAIL PROTECTED] Uh, how is email sent to "[EMAIL PROTECTED]" making it to this mailing list? I seriously hope they haven't just decided to bounce all their support requests to this community... On Jul 27, 2007, at 6:21 PM, Bodó István wrote: I have a problem . I try install PostgreSQL but i cant do that becouse i have an error window. when i am config "initialise database cluster" window and click next then an error window popup , and write : "The "Secondary Logon" service is not running. The service is requied for the installer to initialize the database. Please start the service and try again" What version of windows are you running, and what's the exact installer that you have? -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] plperl syntax highlighting for vi
On Jul 30, 2007, at 3:44 PM, Geoffrey wrote: Has anyone taken a stab at adding plperl syntax highlighting for vi? Hrm, not likely. David Fetter might be able to point you at something. If you come up with something please post it on pgFoundry so that others can find it! :) -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about Postgres
Moving to -general. On Jul 26, 2007, at 12:51 PM, NetComrade wrote: I apologize for cross-posting, but I need some help w/o too many advices RTFM :). After Oracle and MySQL, this becomes the third product that I need to learn to some degree, and I need a few links which would provide a 'quick tutorial' especially for folks with Oracle background like myself. Last time I had to deal with MySql it took me a few days just to figure out how to login, and then how to poke around, and then a few more to finally start writing some useful code in whatever language they use that's similar to PL/SQL. First, let me mention that there's a number of companies that offer PostgreSQL training. If you find a "public class" the cost shouldn't be too bad; likely in the $1500 range for a few days. Coming from Oracle you might also want to look at EnterpriseDB, since we're Oracle compatible (disclosure: I work for EnterpriseDB). For stored procs, take a look at plpgsql, which is similar to PL/SQL (except there's no packages). There's a section on it in the manual. a) how do I access this thing as a DBA to poke around Connect to the database as a database user that has superuser privileges. Details vary depending on how the database was installed, but you can usually do that if you su into the OS account that's running the database. I recommend that you immediately create another superuser so that you're not doing stuff on the machine as the same user that's running the database. b) how do I poke around Uhm... psql (far more useful than sqlplus, IMO)? Or maybe pgAdmin would be more to your liking. c) do I need to make any modifications to config file You probably want to, yes... the default config is *very* conservative. At a minimum change shared_buffers and effective_cache_size, and enable autovacuum. Google for "annotated postgresql.conf". d) what is the most common 'default' values that need to be changed what's the best way to see what a performance bottleneck is (i PostgreSQL relies heavily on OS monitor tools; so things like top, vmstat and cricket are your friends. e) why this doesn't work: [EMAIL PROTECTED] httpd]# vacuumdb -a -f -z -q -h 10.0.1.93 -p5733 vacuumdb: could not connect to database template1: FATAL: no pg_hba.conf entry for host "10.0.1.93", user "root", database "template1", SSL off Because if you don't specify a database user to connect as the default is to connect as the OS user. There's no "root" user in PostgreSQL by default (and I recommend not adding one). Some other comments: Don't use vacuum full (the -f above) unless you really, really have to. Regular 'lazy' vacuuming is what you want. Better yet, use autovacuum. Some 'details' on the server: (ps -ef) 00:00:04 /usr/bin/postmaster -i -h mt-adm.mentora.biz -p 5733 -D /var/opt/scalix/mm/postgres/data -k /var/opt/scalix/mm/postgres/data 00:00:00 postgres: stats buffer process 00:00:00 postgres: stats collector process 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data 00:00:00 postgres: stats buffer process 00:00:00 postgres: stats collector process The above is confusing.. do I have 2 instances (or databases) running on different ports? Should I shutdown the default one? (Scalix is the product that uses the db) Yes, you have two servers running; one on 5733 and one on the default 5432. Should I be 'playing' with /var/opt/scalix/mm/postgres/data/postgresql.conf ? If that's what scalix is using... yes. :) [EMAIL PROTECTED] httpd]# du -skh /var/opt/scalix/mm/postgres/data 276M/var/opt/scalix/mm/postgres/data # "database" is rather small # there are no more than 20-30 users on the server at any given time # the disks are fast (50megs/sec, RAID10, SCSI) # memory is big 8g # cpu count is 2 with hyperthreading (it's a dell 2650) Wow, that's some serious horsepower for a 300MB database... or is the server doing other things? BTW, saying RAID10 doesn't tell us much without mentioning how many drives you have. :) [EMAIL PROTECTED] data]# rpm -qa|grep post postgresql-libs-7.4.17-1.RHEL4.1 postgresql-server-7.4.17-1.RHEL4.1 postgresql-7.4.17-1.RHEL4.1 scalix-postgres-11.0.4.25-1 UGH. Please try and run a recent release... 7.4 is like 4 years old. Files that seem important: /var/opt/scalix/mm/postgres/data [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# hostscalix scalix 10.0.1.201/32 md5 local allall ident sameuser That means that the scalix user can connect to the scalix database from 10.0.1.201 using password (md5) authentication. Any user on the local machine can connect via the filesystem socket using ident sameuser authentication... that means that if your username on the OS is "bob", you can connect to the database as database user "bob" without needing to enter a password. postgresql.conf (comments taken out) max_connections = 100 shared_buffers = 1000 # bunch of locale params
Re: [GENERAL] ascii() for utf8
Moving to -hackers. On Jul 27, 2007, at 1:22 PM, Stuart wrote: Does Postgresql have a function like ascii() that will return the unicode codepoint value for a utf8 character? (And symmetrically same for question chr() of course). I didn't find anything in the docs so I think the answer is no which leads me to ask... Why not? (Hard to believe lack of need without concluding that either ascii() is not needed, of utf8 text is little used.) Are there technical problems in implementing such a function? Has anyone else already done this (ie, is there somewhere I could get it from?) Is there some other non-obvious way to get the cp value for the utf8 character? I think I could use plperl or plpython for this but this seems like an awful lot of overhead for such a basic task. I suspect that this is just a matter of no one scratching the itch. I suspect a patch would be accepted, or you could possibly put something on pgFoundry. I'd set it up so that ascii() and chr() act according to the appropriate locale setting (I'm not sure which one would be appropriate). -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] The leanest, meanest Windows installer possible
Mitchell Vincent wrote: I've been using PG for years and years but bringing it to the Win32 desktop presents some deployment challenges. Since this software will be downloaded I am looking for the smallest possible installer that provides the functionality I'm looking for. Hi Mitchell, I created a slick installer to do just what you want with Inno setup. Nice and easy to use with no MSI/WIX dependencies or complexities. Get it here, full source included with a BSD license for the parts I wrote. www.amsoftwaredesign.com/downloads/pg_installer_setup.zip It's only been tested with 8.1, but should work just fine with 8.2. This program is AS IS..with no support from AM Software. Inno Setup is available from here for free: http://www.jrsoftware.org/isinfo.php I can't remember exactly, but I think this one comes in at around 8mb. -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [NOVICE] alter table table add column
Hi, Sorry for doing the multiple mailing list recipient. Yes I know that procedure but I would like to insert in between because I have at third party software that will call the said schema and one of its dependencies with the mapping is it should have the correct order with what the receiving end will be use for the mapping. And in this case, I can't modify the receiving structure (third-party) and the tables that I will be using is in on production state. So would only mean that I have to schedule a very fast maintenance, probably 5 to 10 secs just to re-initialize the tables. Anyway, thanks for your input, really appreciate it. On Mon, 2007-07-30 at 23:32 -0500, Michael Glaesemann wrote: > [Please don't post the same question to many lists. Choose one. If > you're unsure if it's the correct list for your question, ask if > there's a more appropriate one. This question is find for -novice or - > general. Thanks.] > > On Jul 30, 2007, at 23:19 , Ronald Rojas wrote: > > > And, I want to add the field name age with type integer after > > lastname OR before the address field. How to I do that? > > Can't without dumping the database, altering the schema in the dump, > and reloading. But why does it matter? Just call the columns in the > order you want. > > Michael Glaesemann > grzm seespotcode net > > > == Ronald Rojas Systems Administrator Linux Registered User #427229 == Arnold's Laws of Documentation: (1) If it should exist, it doesn't. (2) If it does exist, it's out of date. (3) Only documentation for useless programs transcends the first two laws.
Re: [GENERAL] [NOVICE] alter table table add column
On Jul 31, 2007, at 0:23 , Ronald Rojas wrote: Yes I know that procedure but I would like to insert in between because I have at third party software that will call the said schema and one of its dependencies with the mapping is it should have the correct order with what the receiving end will be use for the mapping. And in this case, I can't modify the receiving structure (third-party) and the tables that I will be using is in on production state. So would only mean that I have to schedule a very fast maintenance, probably 5 to 10 secs just to re-initialize the tables. Another option would be to use views to change the column order, which would work for selects. I believe you could create rules for insert and update as well, if necessary. Perhaps this would be a solution to your problem. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: 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: [GENERAL] plperl syntax highlighting for vi
On Mon, Jul 30, 2007 at 07:58:14PM -0500, Decibel! wrote: > On Jul 30, 2007, at 3:44 PM, Geoffrey wrote: > >Has anyone taken a stab at adding plperl syntax highlighting for > >vi? > > Hrm, not likely. David Fetter might be able to point you at > something. > > If you come up with something please post it on pgFoundry so that > others can find it! :) I'm flattered, but I haven't seen anything like that. I suppose vim's extensibility might be able to help out. :) Cheers, D (there's more than one way to edit it) -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: 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: [GENERAL] [NOVICE] alter table table add column
Oh yes you have a good point. But then I will still have to test insert and update on views. Thanks a lot michael! On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote: > On Jul 31, 2007, at 0:23 , Ronald Rojas wrote: > > > Yes I know that procedure but I would like to insert in between > > because I have at third party software that will call the said > > schema and one of its dependencies with the mapping is it should > > have the correct order with what the receiving end will be use for > > the mapping. And in this case, I can't modify the receiving > > structure (third-party) and the tables that I will be using is in > > on production state. So would only mean that I have to schedule a > > very fast maintenance, probably 5 to 10 secs just to re-initialize > > the tables. > > Another option would be to use views to change the column order, > which would work for selects. I believe you could create rules for > insert and update as well, if necessary. Perhaps this would be a > solution to your problem. > > Michael Glaesemann > grzm seespotcode net > > == Ronald Rojas Systems Administrator Linux Registered User #427229 == Arnold's Laws of Documentation: (1) If it should exist, it doesn't. (2) If it does exist, it's out of date. (3) Only documentation for useless programs transcends the first two laws.
