Re: [GENERAL] [pgsql-advocacy] PostgreSQL Conference 08 East!
Are there any possibilities to sponsor me and Teodor ? Oleg On Mon, 19 Nov 2007, Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It's that time, after a wildly successful conference last October in Portland, Oregon we are now beginning to ramp up for the East Coast 08 conference! The current plan is to host a two day conference of Tutorials (new) and Talks on March 28th and 29th. The currently designated location for the conference is the Univserity of Maryland. This will be confirmed within two weeks. For now, we are making a call out to the community, it was the hands of the community that made the October conference great. It will be the hands of the community that makes the March conference great! We have already had a couple of offers for help which we are grateful for but we want to make sure that we open this up for anyone who may want to help organize the conference. Of specific interest are community members that are geographically close to the Maryland area. We will need boots on the ground to help us follow up with others (such as student unions etc..) to make sure we kick this conference off without a hitch. As a reminder all proceeds from the Conference series go directly to PostgreSQL via Software in the Public Interest, a 501(c)3 non-profit, and will be used for PostgreSQL development, support and advocacy. So if you are on the east coast and can help with organizing this conference please let me know. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHQiXYATb/zqfZUUQRApK+AJ0WPG39t8CF2oOFF/uHhtgzo7zELgCghYy+ FNjnokvLINAvh8DxJxmctAI= =gvjX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GIN: any ordering guarantees for the hits returned?
Alex Drobychev wrote: I agree with this maybe 98% - but not 100%. :-) Unfortunately performance can change rather unpredictably when the DB stops fitting in memory - say, 3-4 months after a production roll-out, too late for profiling experiments. :-( Surely you're capable of inventing random data to simulate the load you'll have in 3-4 months or even a year? David is correct in that the order is not guaranteed. It's not just a matter of which order the rows were inserted -- the executor can do a lot of things internally that would make the result appear in a different order. Even when the data is CLUSTER'ed the ordering can be lost. If you want to have a guaranteed order, use ORDER BY. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ Thou shalt not follow the NULL pointer, for chaos and madness await thee at its end. (2nd Commandment for C programmers) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql storage and performance questions
We are working on migrating our database from oracle to postgres. Postgres tablesize is twice than oracle tablesize for all my tables.And so the query also takes twice as much time than oracle. So we were checking to see what makes postgres slower than oracle even for basic full tablescan queries. There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? 2. Oracle seems to be reading larger bocks than postgresql (when we examined the iostat and vmstat) (we had set postgres' db block size as 8 and oracle's is 16kb...) Do you have any comments on this? Thanks in advance josh On Nov 20, 2007 12:37 AM, Trevor Talbot [EMAIL PROTECTED] wrote: On 11/19/07, Josh Harrison [EMAIL PROTECTED] wrote: I have 2 tables with 2 cols each( 1 numeric(8,0) and 1 varchar(3) ). In table1 both the cols are filled and in table2 the varchar colm is null So when I checked the tablesize for these two tables (using pg_relation_size) table1 - 57344 bytes (no null columns) table2 - 49152 bytes (varchar colm is null) There is not much difference between the two sizes.So even if a column is null postgresql still has lots of overhead. Does postgres occupy space even when the column is NULL? PostgreSQL's disk storage works in pages, where each page is 8KB. It will use as much space within each page as it can. Filip's last link details this. Is there a specific reason you're looking at this, as in you have some requirement to meet? Or just curious how it works? ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql storage and performance questions
Josh Harrison escribió: On 11/19/07, Josh Harrison [EMAIL PROTECTED] wrote: I have 2 tables with 2 cols each( 1 numeric(8,0) and 1 varchar(3) ). In table1 both the cols are filled and in table2 the varchar colm is null There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? No, NULLs are stored as a bitmap for each tuple and they are quite efficient. Probably the reason for the difference is the numeric field which Oracle may be optimizing as a plain integer. Did you try declaring the column as INTEGER in Postgres? Please do not top-post. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Everything that I think about is more fascinating than the crap in your head. (Dogbert's interpretation of blogger philosophy) ---(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 storage and performance questions
2007/11/20, Josh Harrison [EMAIL PROTECTED]: We are working on migrating our database from oracle to postgres. Postgres tablesize is twice than oracle tablesize for all my tables. Interesting. Which postgresql version? And so the query also takes twice as much time than oracle. This is even more interesting :) What query? can you show it here along with EXPLAIN ANALYZE? Did you do some index tuning or do you just expect the indexes ported from Oracle schema to work? Did you run ANALYZE after populating database? What are server parameters and did you tune postgres config to fit them? So we were checking to see what makes postgres slower than oracle even for basic full tablescan queries. I'm curious too :) please let me know if you resolve this mystery :) There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? I've expained this previously - you have a bitmap in each tuple. Bitmap size is (NATTS+7) % 8 2. Oracle seems to be reading larger bocks than postgresql (when we examined the iostat and vmstat) (we had set postgres' db block size as 8 and oracle's is 16kb...) yes, 8 kB is default pg block size. it is not recommended to change it - however it could be useful in some situations - but I doubt it would speedup your queries twice, whatever they are. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Timestamp without timezone
I'm using Microsoft Visual Foxpro 9 developing an ERP application, using PostgreSQL 8.2.5 and ODBC connection in version 7. If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an error like this: function saldo_estoque(unknown, unknown, unknown, unknown, timestamp without time zone) does not exist However, the unknown types are BPCHAR on function saldo_estoque, and timestamp without time zone is DATE type. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Timestamp without timezone
Il Tuesday 20 November 2007 15:01:53 T.J. Adami ha scritto: I'm using Microsoft Visual Foxpro 9 developing an ERP application, using PostgreSQL 8.2.5 and ODBC connection in version 7. If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an error like this: function saldo_estoque(unknown, unknown, unknown, unknown, timestamp without time zone) does not exist However, the unknown types are BPCHAR on function saldo_estoque, and timestamp without time zone is DATE type. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ It's very likely that you have to do some explici casting on all other paramter types, as the only one the DB has recognised is the last one, aka TIMESTAMPTZ. -- Reg me Please Non quietis maribus nauta ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL Conference 08 East!
Joshua D. Drake wrote: It's that time, after a wildly successful conference last October in Portland, Oregon we are now beginning to ramp up for the East Coast 08 conference! The current plan is to host a two day conference of Tutorials (new) and Talks on March 28th and 29th. The currently designated location for the conference is the Univserity of Maryland. This will be confirmed within two weeks. I just checked... that is Sweet16 weekend for NCAA tournament, but games will not be in the area, so that is not an issue. I'm guessing the facilities will be donated, but it'll be pretty pricey for attendees to stay and move about since it will be the week after spring break, and nothing is cheap in that area unless you are a UM student and there is no incremental cost to attend. I'd like to send three people, but I'd also like to keep the cost of the trip to less than a MS-SQL workgroup processor license. Perhaps a future East coast event could just take over an Embassy Suites and have the entire event self contained. At least in a place like that, you have a hot breakfast included, and free drinks from 5 to 7 or so. Sponsors can hang banners over the rails. It's just a matter of driving or flying in. Networking opportunities are multiplied, and if you get tired, you just take the elevator back to your room. -- Walter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql storage and performance questions
On Nov 20, 2007 8:10 AM, Filip Rembiałkowski [EMAIL PROTECTED] wrote: 2007/11/20, Josh Harrison [EMAIL PROTECTED]: We are working on migrating our database from oracle to postgres. Postgres tablesize is twice than oracle tablesize for all my tables. Interesting. Which postgresql version? Version 8.2.3 And so the query also takes twice as much time than oracle. This is even more interesting :) What query? can you show it here along with EXPLAIN ANALYZE? explain analyze select count(*) from dummy1 QUERY PLAN --- Aggregate (cost=1192999.60..1192999.61 rows=1 width=0) (actual time=109792.239..109792.239 rows=1 loops=1) - Seq Scan on dummy1 (cost=0.00..1119539.48 rows=29384048 3000 width=0) (actual time=0.027..101428.016 rows=29384048 loops=1) Total runtime: 109792.332 ms Postgresql takes 1m 40s for this query Oracle takes 45 sec It is just a count(*) query. I know count(*) query is slower in postgres becoz it doesn't use index. But in Oracle I give the query as select /*+full(dummy1)*/ count(*) from dummy1 with the hint so that oracle uses full table scan and not the index scan. Did you do some index tuning or do you just expect the indexes ported from Oracle schema to work? I created the indexes and Im not sure what kind of tuning neds to be done for the indexes. But this above query doesnt use any indexes. Did you run ANALYZE after populating database? Yes What are server parameters and did you tune postgres config to fit them? I had attached my config file and the table structure So we were checking to see what makes postgres slower than oracle even for basic full tablescan queries. I'm curious too :) please let me know if you resolve this mystery :) There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? I've expained this previously - you have a bitmap in each tuple. Bitmap size is (NATTS+7) % 8 2. Oracle seems to be reading larger bocks than postgresql (when we examined the iostat and vmstat) (we had set postgres' db block size as 8 and oracle's is 16kb...) yes, 8 kB is default pg block size. it is not recommended to change it - however it could be useful in some situations - but I doubt it would speedup your queries twice, whatever they are. -- Filip Rembiałkowski Thanks again josh # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #tcpip_socket=true listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections.
Re: [GENERAL] Timestamp without timezone
On 20 nov, 12:35, [EMAIL PROTECTED] (Reg Me Please) wrote: Il Tuesday 20 November 2007 15:01:53 T.J. Adami ha scritto: I'm using Microsoft Visual Foxpro 9 developing an ERP application, using PostgreSQL 8.2.5 and ODBC connection in version 7. If I upgrade my ODBC drivers to use PostgreSQL ANSI, becomes an error like this: function saldo_estoque(unknown, unknown, unknown, unknown, timestamp without time zone) does not exist However, the unknown types are BPCHAR on function saldo_estoque, and timestamp without time zone is DATE type. How can I solve this? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ It's very likely that you have to do some explici casting on all other paramter types, as the only one the DB has recognised is the last one, aka TIMESTAMPTZ. -- Reg me Please Non quietis maribus nauta ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ It appears to be a VFP handling error, once I use parametrized calls with memvars. This means that VFP will create SQL statement for me translating variables into SQL values (something like PreparedStatement on Java). It's quite simple to make a explicit cast, but due to the size of application, this will be not worth. ODBC driver version 7 is working perfectly, but it's very old and I'm afraid of get some errors. Assuming that PostgreSQL will increase it's versions (now on 8.3 beta 2), there is a need to upgrade the Windows ODBC driver to version 8? ---(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] postgresql storage and performance questions
On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote: There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? Did you by any chance have an aborted load of the data? If you load in a table, and that load fails or does not commit, it will still occupy the space until you vacuum. If you try to load again, the table will be twice the size. If you want to compact the physical space the table occupies, you can try running VACUUM FULL on it, and possibly a redindex afterwards. This will bring the physical space down to the minimum. Both of these operations will lock out access to the tables though. 2. Oracle seems to be reading larger bocks than postgresql (when we examined the iostat and vmstat) (we had set postgres' db block size as 8 and oracle's is 16kb...) Do you have any comments on this? 8k is the defualt. You can change the block size if you need to. You need to modify src/include/pg_config_manual.h recompile and re-initdb. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres file structure doubt
Hello Scott, Thanks for clear my doubt. Yes, I'm planning to do PITR backup. Another question, from what I understand, when there are data transaction going on, postgres will store in the log file, which is in /usr/local/pgsql/data/pg_xlog, when these data will finally save it into /database/pg/mydata? For e.g, if I did a pg_dump backup a week ago, and the system crash today, but I'm able to get all the files in /usr/local/pgsql/data/pg_xlog, can I still recover my database to the latest state? Regards Louis - Original Message From: Scott Marlowe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Monday, November 19, 2007 5:38:12 PM Subject: Re: [GENERAL] Postgres file structure doubt On Nov 19, 2007 11:24 AM, [EMAIL PROTECTED] wrote: Hi everyone, Got a doubt in my setup, please correct me if I'm wrong. In my postgres setup, /usr/local/pgsql (where postgres install) /usr/local/pgsql/data (PGDATA) /database/pg/mydata (tablespace which use for all the table I create) /database/pg/myindex (index which use for all the table I create) 1) In this setup, the actual user data are store in PGDATA the table structure index are store in /database/pg/mydata /database/pg/myindex Am I correct? The data that defines users, and tables, and other objects are in PGDATA. The data from users are stored in mydata/myindex. Not sure if that matches what you wrote or not... 2) So to backup (not pg_dump), I should make sure it include these 2 folder right? /usr/local/pgsql/data /database/pg/ To backup, you should generally use pg_dump. Are you planning on using PITR? Are you planning on shutting down your database when you back it up? if you're not using PITR, you must shut down postgresql to take a file system backup. 3) I think my setup is not quite right, I should move the PGDATA to /database/pg right? Sorta a personaly choice really. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres file structure doubt
On Tue, 2007-11-20 at 08:38 -0800, [EMAIL PROTECTED] wrote: For e.g, if I did a pg_dump backup a week ago, and the system crash today, but I'm able to get all the files in /usr/local/pgsql/data/pg_xlog, can I still recover my database to the latest state? No, pg_dump never allows any form of Point in Time Recovery. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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: [pgsql-advocacy] [GENERAL] PostgreSQL Conference 08 East!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 20 Nov 2007 10:49:25 -0500 Walter Vaughan [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: It's that time, after a wildly successful conference last October in Portland, Oregon we are now beginning to ramp up for the East Coast 08 conference! The current plan is to host a two day conference of Tutorials (new) and Talks on March 28th and 29th. The currently designated location for the conference is the Univserity of Maryland. This will be confirmed within two weeks. Perhaps a future East coast event could just take over an Embassy Suites and have the entire event self contained. At least in a place like that, you have a hot breakfast included, and free drinks from 5 to 7 or so. Sponsors can hang banners over the rails. It's just a matter of driving or flying in. Networking opportunities are multiplied, and if you get tired, you just take the elevator back to your room. The other geek advantage is that the rooms sleep 3 easy (two full beds + couch bed) Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHQxbVATb/zqfZUUQRAghDAJsEsv822XP43I8slkYODCVAeIec2wCdEsVW XpRgwN9bcqqwkLcn8nqMMl8= =91FI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Conference 08 East!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 20 Nov 2007 11:50:03 +0300 (MSK) Oleg Bartunov [EMAIL PROTECTED] wrote: Are there any possibilities to sponsor me and Teodor ? I will contact off list about this. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHQxXBATb/zqfZUUQRAggnAJ9O+NxcW6ABEoTTVNBU8QKFLYM3ewCfVENp FuZTR/a0J1l3CCbK4+maWIk= =r5dk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL Conference 08 East!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 20 Nov 2007 10:49:25 -0500 Walter Vaughan [EMAIL PROTECTED] wrote: Perhaps a future East coast event could just take over an Embassy Suites and have the entire event self contained. At least in a place like that, you have a hot breakfast included, and free drinks from 5 to 7 or so. Sponsors can hang banners over the rails. It's just a matter of driving or flying in. Networking opportunities are multiplied, and if you get tired, you just take the elevator back to your room. As the facilities are not 100% confirmed, we have been talking about the hotel idea. More to come after Thanksgiving. Joshua D. Drake -- Walter ---(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 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHQxY2ATb/zqfZUUQRArO8AJ48JOjRwJ4LMTpv7x3SwYmGnpfCpgCgpIF0 AAlZtFg74Y/9zE5CJjyjg3Y= =TlOx -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql storage and performance questions
On Nov 20, 2007 11:13 AM, Brad Nicholson [EMAIL PROTECTED] wrote: On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote: There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? Did you by any chance have an aborted load of the data? If you load in a table, and that load fails or does not commit, it will still occupy the space until you vacuum. If you try to load again, the table will be twice the size. If you want to compact the physical space the table occupies, you can try running VACUUM FULL on it, and possibly a redindex afterwards. This will bring the physical space down to the minimum. Both of these operations will lock out access to the tables though. I ran vacuum full on this table already. I haven't re-indexed it. But this will not affect the table size...right...since indexes are stored separately? 2. Oracle seems to be reading larger bocks than postgresql (when we examined the iostat and vmstat) (we had set postgres' db block size as 8 and oracle's is 16kb...) Do you have any comments on this? 8k is the defualt. You can change the block size if you need to. You need to modify src/include/pg_config_manual.h recompile and re-initdb. Does changing the block size has any side effects on any other operations in particular? Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql storage and performance questions
On 11/20/07, Josh Harrison [EMAIL PROTECTED] wrote: We are working on migrating our database from oracle to postgres. Postgres tablesize is twice than oracle tablesize for all my tables.And so the query also takes twice as much time than oracle. So we were checking to see what makes postgres slower than oracle even for basic full tablescan queries. A similar question came up recently: http://archives.postgresql.org/pgsql-general/2007-11/msg00619.php You won't see anything that dramatic, but you might try to see how 8.3beta does with your test data. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql storage and performance questions
On Nov 20, 2007, at 1:04 PM, Josh Harrison wrote: I ran vacuum full on this table already. I haven't re-indexed it. But this will not affect the table size...right...since indexes are stored separately? Yes, but your indexes are probably bloated at this point, so to reduce the space they use run a reindex. ---(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] possible to create multivalued index from xpath() results in 8.3?
Matt Magoffin [EMAIL PROTECTED] writes:
Should the xpath() function return 3 individual text nodes like this:
/[EMAIL PROTECTED]mykey]/text() = {
value1,
value2,
value3
}
rather than concatenating these into a single text node result?
AFAICT that's exactly what it does.
regression=# select xpath('//[EMAIL PROTECTED]mykey]/text()',
'valueABCfoo key=mykeyXYZ/foo/valuefoo
key=mykeyRST/foofooDEF/foo');
xpath
---
{XYZ,RST}
(1 row)
regression=#
Of course this is of type xml[], but you can cast to text[] and then
index.
regards, tom lane
---(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 storage and performance questions
On Tue, 2007-11-20 at 13:04 -0500, Josh Harrison wrote: On Nov 20, 2007 11:13 AM, Brad Nicholson [EMAIL PROTECTED] wrote: On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote: There were a couple of things we noted. 1. Tablesize twice as much than oracle-- Im not sure if postgres null columns has any overhead since we have lots of null columns in our tables.Does postgresql has lots of overhead for null columns? Did you by any chance have an aborted load of the data? If you load in a table, and that load fails or does not commit, it will still occupy the space until you vacuum. If you try to load again, the table will be twice the size. If you want to compact the physical space the table occupies, you can try running VACUUM FULL on it, and possibly a redindex afterwards. This will bring the physical space down to the minimum. Both of these operations will lock out access to the tables though. I ran vacuum full on this table already. I haven't re-indexed it. But this will not affect the table size...right...since indexes are stored separately? You are correct about the indexes. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] IP addresses
Hi, - Original Message - From: Harald Fuchs [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Monday, November 19, 2007 7:21 PM Subject: Re: [GENERAL] IP addresses In article [EMAIL PROTECTED], Tom Allison [EMAIL PROTECTED] writes: I am planning on doing a LOT of work with ip addresses and thought that the inet data type would be a great place to start. Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be happy. I would be happy if it would support IPv6 :-) Are there plans to make ip6r or something like that? Thanks, Sander ---(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] Static linking of libpq with a windows application.
Hi, I am developing a windows application and will statically link pgsql frontend with as part of final binary (so that it will not require libpq.dlland other dll at runtime). Any pointers on how to do it? Do we have to compile the source code to generate a new lib file ?? Anyone with prior experience ??? Thanks .. Farhan
Re: [GENERAL] IP addresses
Sander Steffann [EMAIL PROTECTED] writes: From: Harald Fuchs [EMAIL PROTECTED] Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be happy. I would be happy if it would support IPv6 :-) Are there plans to make ip6r or something like that? What's the point? You might as well use the regular inet type if you need to handle ipv6. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] IP addresses
On Nov 20, 2007, at 3:41 PM, Tom Lane wrote: Sander Steffann [EMAIL PROTECTED] writes: From: Harald Fuchs [EMAIL PROTECTED] Forget inet. Check out http://pgfoundry.org/projects/ip4r/ and be happy. I would be happy if it would support IPv6 :-) Are there plans to make ip6r or something like that? What's the point? You might as well use the regular inet type if you need to handle ipv6. ip4r's main advantage over inet is that it allows you to answer the question is this IP address in any of these large number of address ranges efficiently. It's useful for customer address allocation, email filtering blacklists, things like that. A range-indexable ipv6 type would be useful in theory, but I've not seen a need for it in production yet. When there is, extending ip4r to become ip6r would be possible. Cheers, Steve ---(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] Calculation for Max_FSM_pages : Any rules of thumb?
On Mon, 2007-11-19 at 08:24 -0500, Bill Moran wrote: In response to Ow Mun Heng [EMAIL PROTECTED]: Even with the regular vacuuming and even a vacuum full ( on my test DB) I still see that perhaps something is wrong (from the below) (I got this gem from the mailling list archives) hmxmms= SELECT c.relname, c.reltuples::bigint as rowcnt, pg_stat_get_tuples_inserted(c.oid) AS inserted, pg_stat_get_tuples_updated(c.oid) AS updated, pg_stat_get_tuples_deleted(c.oid) AS deleted FROM pg_class c WHERE c.relkind = 'r'::char GROUP BY c.oid, c.relname, c.reltuples HAVING pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid) 1000 ORDER BY pg_stat_get_tuples_updated(c.oid) + pg_stat_get_tuples_deleted(c.oid) DESC; relname| rowcnt | inserted | updated | deleted ---+--+--+-+-- tst_r | 11971691 |0 | 0 | 22390528 -- pg_statistic | 1465 | 280 |7716 | 153 dr_ns | 2305571 | 1959 | 0 | 1922 pg_attribute | 3787 | 1403 | 184 | 1292 No matter how many times I vacuum/full the deleted number still doesn't go down. Are you sure you're interpreting that number correctly? I took it to mean a counter of the number of delete operations since server start. You are right. This is definitely a snafu in my interpretation. After I restarted PG on the laptop, the numbers went away. So, then I'm confused as to why the above gem was provided as a means to see which tables needs more vacumming. ANyway... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL 8.3 Beta3 released!
Thanks to all the testing, feedback and bug reports the community has performed with the first and second betas, we now have our third beta of 8.3. We hope that this will be our last beta before release candidate so please download and continue testing to ensure that any issues you raised have have been resolved. As always, our community is the first line of defense to help us find any corner cases of possible issues. Further Beta information is available here: http://www.postgresql.org/developer/beta Binaries for Windows are already available; binary packages for Red Hat and Solaris should be available soon. Joshua D. Drake PostgreSQL Liaison -- PostgreSQL - The world's most advanced open source database http://www.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] VB ADODB .Open failing
I'm new to this, so please bear with me.
Here is a VB code snippet:
Dim dbOut As ADODB.Connection
Dim rsOut As ADODB.Recordset
Set dbOut = New ADODB.Connection
Set rsOut = New ADODB.Recordset
With dbOut
.ConnectionString = Driver={PostgreSQL
ANSI};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=none;
.Open
With rsOut
rsOut.Open Contact1, dbOut, adOpenDynamic,
adLockOptimistic, adCmdTable
The .Open statement fails with:
ERROR: column oid does not exist;
Error while executing the query.
I get the same error with the following:
rsOut.Open select * from Contact1;, dbOut, adOpenDynamic,
adLockOptimistic, adCmdText
Should I have created the table Contact1 WITH OIDS ?
Finn
--
Finn Lassen
Deputy CIO
Axiom
1805 Drew Street
Clearwater, Florida 33765
727-442-7774 voice
727-442-8344 fax
[EMAIL PROTECTED]
www.AxiomInt.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] IP addresses
Steve Atkins wrote: On Nov 20, 2007, at 3:41 PM, Tom Lane wrote: Sander Steffann [EMAIL PROTECTED] writes: I would be happy if it would support IPv6 :-) Are there plans to make ip6r or something like that? What's the point? You might as well use the regular inet type if you need to handle ipv6. ip4r's main advantage over inet is that it allows you to answer the question is this IP address in any of these large number of address ranges efficiently. It's useful for customer address allocation, email filtering blacklists, things like that. Another advantage is that it's not varlena (this is less of a problem in 8.3 due to short varlenas, but having a fixed-length field is still better). -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 I must say, I am absolutely impressed with what pgsql's implementation of VALUES allows me to do. It's kind of ridiculous how much work goes away in my code. Too bad I can't do this at work (Oracle 8/9). (Tom Allison) http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php ---(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] PostgreSQL is not behaving consistently across platforms
Hello guys, Posted this msg on general hackers list to get most accurate responses as possible... I plan to use PG with an OpenCRX project... but I read that there's some probs with PG and strings comparisons Is it true? Look at: http://www.opencrx.org/opencrx/1.11/pg.htm Thanks Bruno ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL is not behaving consistently across platforms
Bruno Lavoie [EMAIL PROTECTED] writes: Look at: http://www.opencrx.org/opencrx/1.11/pg.htm [ shrug... ] Text sort ordering is dependent on the locale you use. If these folk want C-locale sorting, they need to initdb in C locale. regards, tom lane ---(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] possible to create multivalued index from xpath() results in 8.3?
AFAICT that's exactly what it does.
regression=# select xpath('//[EMAIL PROTECTED]mykey]/text()',
'valueABCfoo
key=mykeyXYZ/foo/valuefoo key=mykeyRST/foofooDEF/foo');
xpath
---
{XYZ,RST}
(1 row)
regression=#
Of course this is of type xml[], but you can cast to text[] and then
index.
Ugh, you're right of course! Somehow I had this wrong. So I tried to
create an index on the xml[] result by casting to text[] but I got the
function must be immutable error. Is there any reason the xml[] to
text[] cast is not immutable?
I worked around it by writing a function like
CREATE OR REPLACE FUNCTION xpath_to_text(xml_array xml[]) RETURNS text[] AS
$BODY$
BEGIN
RETURN xml_array::text[];
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
and wrapping my CREATE INDEX call with that, like:
create index type_flag_idx on lead using gin (
(xpath_to_text(xpath('/[EMAIL PROTECTED]foo]/text()', xml)))
);
-- m@
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] possible to create multivalued index from xpath() results in 8.3?
Matt Magoffin [EMAIL PROTECTED] writes: Ugh, you're right of course! Somehow I had this wrong. So I tried to create an index on the xml[] result by casting to text[] but I got the function must be immutable error. Is there any reason the xml[] to text[] cast is not immutable? Hmm ... I see that xmltotext() is marked 'stable' in pg_proc.h, but texttoxml() is marked 'immutable', which is at best inconsistent. It looks to me like they both depend on the GUC setting xmloption, which would mean they should both be stable. Peter, is there a bug there? Also, is there a way to get rid of the GUC dependency so that there's a reasonably safe way to index XML values? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] VB ADODB .Open failing
--- On Tue, 11/20/07, Finn Lassen [EMAIL PROTECTED] wrote:
Here is a VB code snippet:
Dim dbOut As ADODB.Connection
Dim rsOut As ADODB.Recordset
Set dbOut = New ADODB.Connection
Set rsOut = New ADODB.Recordset
.ConnectionString = Driver={PostgreSQL
ANSI};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=none;
Should I have created the table Contact1 WITH
OIDS ?
I wonder if this is a problem with the way your ODBC driver is configured. If
it is a problem with your ODBC driver configuration, you might also try posting
to the PostgreSQL-ODBC mailing list.
Also, why did you choose the ANSI driver over the Unicode driver?
Regards,
Richard Broersma Jr.
---(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] MAybe a FAQ
Hi all. What'd be the right place to put a feature request for the next releases and for bugs in the current one? Thanks. -- Reg me Please Non quietis maribus nauta ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Normalization tools for postgres?
Anyone have recommendations on tools/utilities or SQL approaches to
quickly break apart a large imported flat file into normal forms,
ideally 1NF or 2NF?
I noticed this tool for mySQL which captures what I am looking for:
http://www.sqldbu.com/eng/sections/tips/normalize.html
Given the fields with data to be output into separate tables, it
takes a csv and automatically generates a set of INSERT queries to
build all the related tables with new a new primary key for the main
table and serialized codes for each of the new tables to maintain
relationships.
Perhaps a customized php script could accomplish the same thing for
postgres?
Any suggestions would be helpful.
Dane
field names for which to break out into their own tables
On Nov 20, 2007, at 8:33 PM, Richard Broersma Jr wrote:
--- On Tue, 11/20/07, Finn Lassen [EMAIL PROTECTED] wrote:
Here is a VB code snippet:
Dim dbOut As ADODB.Connection
Dim rsOut As ADODB.Recordset
Set dbOut = New ADODB.Connection
Set rsOut = New ADODB.Recordset
.ConnectionString = Driver={PostgreSQL
ANSI};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=n
one;
Should I have created the table Contact1 WITH
OIDS ?
I wonder if this is a problem with the way your ODBC driver is
configured. If it is a problem with your ODBC driver
configuration, you might also try posting to the PostgreSQL-ODBC
mailing list.
Also, why did you choose the ANSI driver over the Unicode driver?
Regards,
Richard Broersma Jr.
---(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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Restart a sequence regularly
Hi, I am using Postgresql 8.0.3 in Fedora Core 4. In my database, it contains a sequence. And, I need to alter the range of this sequence and restart it to the start of the new range at 00:00:00 on 1st January on every year. 5 seconds before and after that time, I need to prevent users from calling nextval() to retrieve the next number from this sequence. I can write a Perl script to alter the sequence and schedule to run this script at 23:59:55 on 31st December on every year. But, I don't know how to lock the sequence to prevent others from accessing this sequence to get next number and Postgresql does not support to lock a sequence. How can I prevent others from accessing the sequence, like locking a table? That means, when others want to access the sequence between 31-Dec 23:59:55 and 1-Jan 00:00:05, they are waiting instead of getting an error. Thank -- Kathy Lo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
