[GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
I am going to install Postgresql 9.0 for my running applicaiton which is at 8.1. My Db size is 3 GB. Server Specification: dual-core 4 cpu RAM: 32 GB OS: Centos What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. Any help please.
Re: [GENERAL] Problem with to_tsquery() after restore on PostgreSQL 9.0.1
Hi! Tom Lane writes: > So far as I can see offhand, the only way you'd get that error message > is if to_tsquery were declared to take OID not regconfig as its first > argument. > > > I suspect it has to do with the Tsearch2-compatibility modules from > > contrib - these were compiled and installed to a couple of databases > on > > the old cluster; I haven't yet compiled and installed them to the new > > databases as I'd like to get rid of some dead weight in the migration > > process. > > contrib/tsearch2 does provide a "to_tsquery(oid, text)" function ... > I'm > not sure why offhand, nor how come that's managing to show up in front > of the standard definition in your search path. Try dropping that. Now I simply used the postgresql-9.0.1/contrib/tsearch2/uninstall_tsearch2.sql to get rid of the contrib DDL - that seems to have fixed the problem alright; maybe we'll have to fix some of our code that may use some old function signatures, but since we've switched to Sphinx for the more demanding FTS tasks, we're not making as much use of TSearch2 as we used to, anyway. > (Another question is why it wasn't failing already in your 8.3 DB. > The behavior shouldn't be any different AFAICS. Are you sure you > are using the same search_path as before?) Yes, as I simply copied my old postgresql.conf to the test server. Strange, but as the primary problem seems to be solved alright, I'm happy anyway :) Kind regards Markus COMPUTEC MEDIA zieht in neue Verlagsraume! Adresse ab dem 27. September 2010: COMPUTEC MEDIA AG, Dr.-Mack-Stra?e 83, 90762 Furth. Alle sonstigen Kontaktdaten bleiben unverandert. Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
I have same question My Computer is running POS with Postgres 8.9.11 database Ram : 16GB OS : Windows 2008 R2 CPU XEON 2G User : 50-60 user (connect ~ 200 connects, I increase Windows SharedSection=1024,20480,1024 for > 125 connects). DISK : RAID 1 What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. My application run slowly when >= 30 users Thank for your help. Tuan Hoang Anh On Thu, Nov 11, 2010 at 2:59 PM, AI Rumman wrote: > I am going to install Postgresql 9.0 for my running applicaiton which is at > 8.1. > My Db size is 3 GB. > Server Specification: > dual-core 4 cpu > RAM: 32 GB > OS: Centos > What will be good settings for DB parameters such as shared_buffers, > checkpoint_segment and etc. > Any help please. >
Re: [GENERAL] REINDEX requirement?
Hello, When reindexing we keep the previous and new reltuples/relpages ratio in in a reference table in order to track the reindex efficiency. We also have a maintenance jobs that compare this table with the stats from pg_class and automatically reindex the relations where the ratio degraded too much. (There is also a list of relations that need to be ignored by the job.) This is a simplistic approach , but it matches our needs. CREATE TABLE reindex_list ( nspname character varying, index_name character varying, nspname_oid oid NOT NULL, index_oid oid NOT NULL, old_ratio double precision, new_ratio double precision, old_pagecount integer, new_pagecount integer, execution_count integer, reindex_time bigint, CONSTRAINT reindex_list_pk PRIMARY KEY (nspname_oid, index_oid) ) regards, Marc Mamin From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Robert Treat Sent: Mittwoch, 10. November 2010 22:40 To: Igor Neyman Cc: AI Rumman; pgsql-general General Subject: Re: [GENERAL] REINDEX requirement? On Tue, Nov 9, 2010 at 1:51 PM, Igor Neyman wrote: > -Original Message- > From: AI Rumman [mailto:rumman...@gmail.com] > Sent: Tuesday, November 09, 2010 3:26 AM > To: pgsql-general General > Subject: REINDEX requirement? > > How do I know that index require REINDEX? > > Look at the results of pgstatindex(...) function for specific index. It's part of pgstattupple contrib module - read it up in the docs. If you are looking for a "poor mans" tool, we have a script that will output numbers on table/index bloat. It's not entirely accurate (patches welcome), but usually good enough to highlight the problems. See http://labs.omniti.com/labs/pgtreats/log/trunk/tools/pg_bloat_report.pl Robert Treat play: http://www.xzilla.net work: http://www.omniti.com/is/hiring
Re: [GENERAL] dblink_get_result issue
For now I just ignore the first exception.
BEGIN
PERFORM * from dblink_get_result('myconn')as x (t text);
EXCEPTION WHEN datatype_mismatch THEN NULL;
END;
PERFORM * from dblink_get_result('myconn')as x (t text);
thanks,
Marc Mamin
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Donnerstag, 11. November 2010 00:54
To: Marc Mamin
Cc: pgsql-general@postgresql.org; Joe Conway
Subject: Re: [GENERAL] dblink_get_result issue
"Marc Mamin" writes:
> I'm experimenting with dblink, and have an issue with async. queries
> that do not return result sets.
> (Postgres 8.3.9)
> --youll need your own connect string to repeat:
> select dblink_connect_u('myconn1', 'hostaddr=127.0.0.1 port=5432
> dbname=postgres user=postgres password=x');
> select dblink_send_query('myconn1', 'create temp table bah(s
int8);');
> select pg_sleep(.3);
> SELECT * FROM dblink_get_result('myconn1', true ) as x (t text);
> =>
> ** Error **
> remote query result rowtype does not match the specified FROM clause
> rowtype
Hmm. I can reproduce this in 8.4 too, but it seems OK in 9.0. The only
one of the 9.0 commits that seems likely to be relevant is
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=56cbb61
1ec749ba867a4cfc09c8b7df0f4446620
which looks a bit overcomplex for a back-patch. Joe, any thoughts?
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
SSD caveats are well described here: http://www.postgresql.org/about/news.1249 HTH, Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau Sent: Donnerstag, 11. November 2010 07:42 To: Postgres General Postgres General Subject: [GENERAL] Considering Solid State Drives Hi, As part of datamining activity. I have some plpgsql functions (executed in parallel, up to 6 such concurrent calls) that perform some reads and writes of large number of (maybe 1) records at a time to a table having multi-column primary key. It seems the writing of these few thousands records is taking a long time (up to 5mins in some cases). Running vmstat reports %iowait between 15 and 24 on a single 7200rpm SATA drive, six core (Phenom II) "server". I am now thinking of investing in a SSD (Solid State Drive), and maybe choosing between "Crucial Technology 256GB Crucial M225 Series 2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) - Intel MLC". I have looked at the comparison statistics given here "http://www.anandtech.com/bench/Product/137?vs=126"; that suggests that the Intel offering is more geared for small random write operations. After googling I found little resent content (including survival statistics) of using SSDs in a write intensive database environment. Kindly advice, Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ignore errors for COPY
Hello I have postrges 8.3.12 and I have the following issue: I have a table create table test( table_id integer, datetime timestamp, MMSI integer, lat real, lng real, ); and I bulk insert data to this table with COPY. A tiny portion of the data in the file are wrong. For example one date is "2009-93-29 05:27:08" which obviously has a wrong month (93). COPY encounters this row and stop the insertion with ERROR: date/time field value out of range: "2009-93-29 05:27:08" Is there a way I can "turn" this error into a warning (or suppress the error) and make COPY simply to skip this row? I have a big amount of data (~100G) so iterating through them to find all the possible wrong timestamp, reals, and integers will be quite tedious and time consuming. Regards Vangelis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ignore errors for COPY
Le 11/11/2010 13:01, Vangelis Katsikaros a écrit : > Hello > > I have postrges 8.3.12 and I have the following issue: > > I have a table > create table test( > table_id integer, > datetime timestamp, > MMSI integer, > lat real, > lng real, > ); > > and I bulk insert data to this table with COPY. > > A tiny portion of the data in the file are wrong. For example one date > is "2009-93-29 05:27:08" which obviously has a wrong month (93). COPY > encounters this row and stop the insertion with > ERROR: date/time field value out of range: "2009-93-29 05:27:08" > > Is there a way I can "turn" this error into a warning (or suppress the > error) and make COPY simply to skip this row? > Nope. > I have a big amount of data (~100G) so iterating through them to find > all the possible wrong timestamp, reals, and integers will be quite > tedious and time consuming. > You should better look at pgloader which will use COPY to put your data in your table and found the lines in error. Of course, it takes time to detect lines in error. But at least, all "good" lines will be in your table, and all "bad" lines will be in a file, so that you can modify them to inject later. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 2PC w/ dblink
On Wed, Nov 10, 2010 at 12:39 PM, John R Pierce wrote: > My developers are complaining about the lack of support for 2 phase commit > in this scenario. Can we get any mileage on PREPARE TRANSACTION in a > dblink sort of environment like this? > Yes, that's an ideal case for this. We use it outside of dblink with two direct connections to two databases. Just be sure you have some monitoring that will alert you to prepared transactions that are lingering for a long time. Also, if you have a pending prepared transaction on a host with a newly inserted row, and you retry inserting that row from another connection, you will get an immediate "statement timeout" error. This is a bit confusing at first but once you know what the cause is, it is easy to work with. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NOTIFY/LISTEN why is not a callback as notice processing.
On Wed, Nov 10, 2010 at 5:20 PM, Tom Lane wrote: > Not in libpq. libpq is just a passive library, it can't cause actions > to happen when you aren't calling it. So there's no point in a > callback: you might as well just test for occurrences of a NOTIFY at > times when you're prepared to handle it. > On my systems where I use them more or less as "wakeup and do work" signals, I just do a select() call on the connection socket. When the signal arrives, the socket becomes readable, and then I just query for the notification. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ignore errors for COPY
On Thu, Nov 11, 2010 at 8:05 AM, Guillaume Lelarge wrote: > Le 11/11/2010 13:01, Vangelis Katsikaros a écrit : >> Hello >> >> I have postrges 8.3.12 and I have the following issue: >> >> I have a table >> create table test( >> table_id integer, >> datetime timestamp, >> MMSI integer, >> lat real, >> lng real, >> ); >> >> and I bulk insert data to this table with COPY. >> >> A tiny portion of the data in the file are wrong. For example one date >> is "2009-93-29 05:27:08" which obviously has a wrong month (93). COPY >> encounters this row and stop the insertion with >> ERROR: date/time field value out of range: "2009-93-29 05:27:08" >> >> Is there a way I can "turn" this error into a warning (or suppress the >> error) and make COPY simply to skip this row? >> > > Nope. > >> I have a big amount of data (~100G) so iterating through them to find >> all the possible wrong timestamp, reals, and integers will be quite >> tedious and time consuming. >> > > You should better look at pgloader which will use COPY to put your data > in your table and found the lines in error. Of course, it takes time to > detect lines in error. But at least, all "good" lines will be in your > table, and all "bad" lines will be in a file, so that you can modify > them to inject later. > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > olé!!! Maybe you could import it as a text column and then deal with the conversion in the DBi do that sometimesnever had 100GB of data to work with though Rhys -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman wrote: > Server Specification: > dual-core 4 cpu > RAM: 32 GB > OS: Centos > What will be good settings for DB parameters such as shared_buffers, > checkpoint_segment and etc. I'll take this one ... :) On my 24GB quad-core Opteron servers running FreeBSD 8.1, with big external fibre connected RAID array, I use the following changes relative to the default 9.0.1 postgresql.conf. You probably don't need to adjust the prepared transactions setting, unless you use them :-) The default config is pretty darned good, compared to what used to ship with older releases like 8.1 :) listen_addresses = '*' max_connections = 200 shared_buffers = 4200MB max_prepared_transactions = 100 # guideline: same number as max_connections work_mem = 512MB maintenance_work_mem = 1024MB vacuum_cost_delay = 15 checkpoint_segments = 64 checkpoint_timeout = 15min checkpoint_completion_target = 0.8 random_page_cost = 1.3 # fast disk with big buffer effective_cache_size = 6400MB # shared_buffers + `sysctl -n vfs.hibufspace` / 8192 (BLKSZ) log_checkpoints = on update_process_title = on log_autovacuum_min_duration = 0 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
On Thu, Nov 11, 2010 at 3:30 AM, tuanhoanganh wrote: > My Computer is running POS with Postgres 8.9.11 database > Ram : 16GB > OS : Windows 2008 R2 > CPU XEON 2G > User : 50-60 user (connect ~ 200 connects, I increase Windows > SharedSection=1024,20480,1024 for > 125 connects). > DISK : RAID 1 > What will be good settings for DB parameters such as shared_buffers, > checkpoint_segment and etc. > My application run slowly when >= 30 users I'd start by optimizing your queries, and looking for both extra and missing indexes that would help your queries. What kind of disk do you have? Are these just local SATA drives? Perhaps you need faster drives. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau wrote: > After googling I found little resent content (including survival > statistics) of using SSDs in a write intensive database environment. > We use the Texas Memory RAMSan-620 external disk units. It is designed specifically to survive high write loads, and uses internal monitoring and load leveling and spare parts with internal RAID configuration to keep from losing data in the eventual case when the SSD chips wear out. When that happens, you just swap out the failed piece and it keeps humming along. I wouldn't trust an SSD as a stand-alone drive for a DB. At minimum, I'd RAID1 them using the SATA controller... but be sure to get SATA3 or even better SAS connected drives if you want to maximize the speed. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
> On Thu, Nov 11, 2010 at 3:30 AM, tuanhoanganh wrote: >> My Computer is running POS with Postgres 8.9.11 database Not sure which version is that. There's nothing like 8.9.11 ... >> Ram : 16GB >> OS : Windows 2008 R2 >> CPU XEON 2G >> User : 50-60 user (connect ~ 200 connects, I increase Windows >> SharedSection=1024,20480,1024 for > 125 connects). >> DISK : RAID 1 >> What will be good settings for DB parameters such as shared_buffers, >> checkpoint_segment and etc. >> My application run slowly when >= 30 users > > I'd start by optimizing your queries, and looking for both extra and > missing indexes that would help your queries. Well, it's always useful to have a decent settings (default one is very conservative and may significantly hurt performance in some cases). Anyway, on Linux I'd bump up shared buffers (to something like 512MB) and work_mem (maybe 4MB), increased effective_cache_size (to about 10GB), etc. But he mentions Windows 2008 and I have no experience with running PG on this OS. > What kind of disk do you have? Are these just local SATA drives? > Perhaps you need faster drives. I think it's too early to recommend buying faster drives. You have not identified the bottleneck and what's causing it. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
Hello, When choosing SSD drive you need to consider * number of writes to particular sector which is about 100k to 200k and then sector will fail * in case of DB grow, limitied size of those dirvers. > As part of datamining activity. I have some plpgsql functions > (executed in parallel, up to 6 such concurrent calls) that perform > some reads and writes of large number of (maybe 1) records at a > time to a table having multi-column primary key. > It seems the writing of these few thousands records is taking a long > time (up to 5mins in some cases). This time is realy long, it is about 30 seconds to insert one row! You should check if your functions dosen't generate locks, check if some triggers are executed. Your functions can query on non-indexed columns, as well. Try to execute above functions in one, non-paraller thread, if the time per row will be less then paraller execution it means you generate locks. You can as well try to disable fsync in PSQL config. Kind regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
ZFS has an option to use an SSD as cache for the spinning drives. ZFS under Solaris has turned in some really good IO numbers. The problem is with the new Sun I am not feeling so good about the open nature of Solaris. ZFS performance under BSD I have read does not match ZFS under Solaris. On 11/11/2010 8:35 AM, Vick Khera wrote: > On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau wrote: >> After googling I found little resent content (including survival >> statistics) of using SSDs in a write intensive database environment. >> > We use the Texas Memory RAMSan-620 external disk units. It is > designed specifically to survive high write loads, and uses internal > monitoring and load leveling and spare parts with internal RAID > configuration to keep from losing data in the eventual case when the > SSD chips wear out. When that happens, you just swap out the failed > piece and it keeps humming along. > > I wouldn't trust an SSD as a stand-alone drive for a DB. At minimum, > I'd RAID1 them using the SATA controller... but be sure to get SATA3 > or even better SAS connected drives if you want to maximize the speed. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
On Thu, Nov 11, 2010 at 08:30:16AM -0500, Vick Khera wrote: > On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman wrote: > > Server Specification: > > dual-core 4 cpu > > RAM: 32 GB > > OS: Centos > > What will be good settings for DB parameters such as shared_buffers, > > checkpoint_segment and etc. > > I'll take this one ... :) On my 24GB quad-core Opteron servers > running FreeBSD 8.1, with big external fibre connected RAID array, I > use the following changes relative to the default 9.0.1 > postgresql.conf. You probably don't need to adjust the prepared > transactions setting, unless you use them :-) > > The default config is pretty darned good, compared to what used to > ship with older releases like 8.1 :) > > listen_addresses = '*' > max_connections = 200 > shared_buffers = 4200MB > max_prepared_transactions = 100 # guideline: same number as max_connections This should be either 0 (no 2PC) or the bounded from below by max_connections. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ignore errors for COPY [solved]
On 11/11/2010 03:05 PM, Guillaume Lelarge wrote: You should better look at pgloader which will use COPY to put your data in your table and found the lines in error. Of course, it takes time to detect lines in error. But at least, all "good" lines will be in your table, and all "bad" lines will be in a file, so that you can modify them to inject later. Hi Thanks for the answer, indeed it solves the issue. Now, I would like to ask a second question (sorry for using the same thread :) I would like to create a specific database on a another location (not in "data_directory = '/var/lib/postgresql/8.3/main'" ). I was wondering if I have to run initdb -D /other/path/ of if there is another way (for example in mysql I can do the same thing by creating a symlink to the other location inside "datadir = /var/lib/mysql" ) Regards Vangelis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema tool
A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would display a graphic schema of whatever database you connect to but I can't seem to find it again (web searching.)I did come across one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database which I have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQL table definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? -Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
Am 11.11.2010 16:40, schrieb David Siebert: ZFS has an option to use an SSD as cache for the spinning drives. ZFS under Solaris has turned in some really good IO numbers. The problem is with the new Sun I am not feeling so good about the open nature of Solaris. ZFS performance under BSD I have read does not match ZFS under Solaris. Very true. Also, we experienced (with a 32-core/64GB Sun X4600 machine and a direct-attached storage array with 24 10k-spindles) that PostgreSQL scaled *badly* (with the number of active, long-running queries) when using ZFS. Installing CentOS with XFS, the same hardware delivered very good scaling and higher absolute performance. Admitted, this were more or less out-of-the-box configurations (but we did some tuning with ZFS, which resulted in a 2x speedup - still not enough). But the difference was drastic. We went with Linux/XFS then. Joachim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Hey Aram, I recommend dbWrench by Nizana. It has a nice synchronization capabilities, forward / reverse engineering and supports many built-in PostgreSQL types and user-defined types as well. NB: it is commercial application. Another alternative is a MicroOLAP Database Designer. The both tools are not require running PostgreSQL server. If you wish to work with database directly you may look at tools like PostgreSQL Maestro. Good luck! 2010/11/11 Aram Fingal > A while back, I thought I remembered seeing a Mac OS X client for > PostgreSQL which had a feature where it would display a graphic schema of > whatever database you connect to but I can't seem to find it again (web > searching.)I did come across one post which said that this was a planned > feature for pgAdmin. I need to do a formal schema of a database which I > have in development and it would be nice to have an automated tool, whether > it connects to the database or uses SQL table definition code to generate > graphics. Is there such a thing? if not, what do you all use? OpenOffice > Draw? OmniGraffle? > > -Aram > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
Re: [GENERAL] Schema tool
On 11/11/2010 09:50 AM, Aram Fingal wrote: > A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL > which had a feature where it would display a graphic schema of whatever > database you connect to but I can't seem to find it again (web searching.) > I did come across one post which said that this was a planned feature for > pgAdmin. I need to do a formal schema of a database which I have in > development and it would be nice to have an automated tool, whether it > connects to the database or uses SQL table definition code to generate > graphics. Is there such a thing? if not, what do you all use? OpenOffice > Draw? OmniGraffle? > > -Aram DbVisualizer has a free and a commercial release and can do a decent job of diagramming a schema. Nothing compared to Embarcadaro, put not as pricey either. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ignore errors for COPY [solved]
Le 11/11/2010 17:46, Vangelis Katsikaros a écrit : > On 11/11/2010 03:05 PM, Guillaume Lelarge wrote: >> >> You should better look at pgloader which will use COPY to put your data >> in your table and found the lines in error. Of course, it takes time to >> detect lines in error. But at least, all "good" lines will be in your >> table, and all "bad" lines will be in a file, so that you can modify >> them to inject later. >> >> > > Hi > > Thanks for the answer, indeed it solves the issue. > Great. > Now, I would like to ask a second question (sorry for using the same > thread :) > > I would like to create a specific database on a another location (not in > "data_directory = '/var/lib/postgresql/8.3/main'" ). I was wondering if > I have to run > initdb -D /other/path/ > > of if there is another way > To create another database in another location, you first need to create a tablespace, and then create a database in that tablespace. For example, in psql: CREATE TABLESPACE otherlocation LOCATION '/some/specific/directory'; CREATE DATABASE newdb TABLESPACE otherlocation; > (for example in mysql I can do the same thing by creating a symlink to > the other location inside "datadir = /var/lib/mysql" ) > Don't know MySQL, so can't say :) -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Le 11/11/2010 18:58, Rob Sargent a écrit : > > > On 11/11/2010 09:50 AM, Aram Fingal wrote: >> A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL >> which had a feature where it would display a graphic schema of whatever >> database you connect to but I can't seem to find it again (web searching.) >> I did come across one post which said that this was a planned feature for >> pgAdmin. I need to do a formal schema of a database which I have in >> development and it would be nice to have an automated tool, whether it >> connects to the database or uses SQL table definition code to generate >> graphics. Is there such a thing? if not, what do you all use? OpenOffice >> Draw? OmniGraffle? >> >> -Aram > > DbVisualizer has a free and a commercial release and can do a decent job > of diagramming a schema. Nothing compared to Embarcadaro, put not as > pricey either. > I actually use DBVisualizer when I need to get a graphical view of an existing database. BTW, this is a planned feature of pgAdmin. We have an interesting patch from a GSoC student (Luis Ochoa), but it still needs (a lot of) work. I still hope to include it for the next release. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ipv4 data type does not allow to use % as subnet mask delimiter
Windows uses % as subnet mask delimiter.
Trying to use it like
create temp table test (test inet) on commit drop;
insert into test values('fe80::f1ea:f3f4:fb48:7155%10')
returns error
ERROR: invalid input syntax for type inet: "fe80::f1ea:f3f4:fb48:7155%10"
LINE 2: insert into test values('fe80::f1ea:f3f4:fb48:7155%10')
How to fix ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Hello, may schemaspy help you ? http://schemaspy.sourceforge.net/sample/relationships.html HTH, Marc Mamin -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Aram Fingal Sent: Donnerstag, 11. November 2010 17:51 To: Postgres-General General Subject: [GENERAL] Schema tool A while back, I thought I remembered seeing a Mac OS X client for PostgreSQL which had a feature where it would display a graphic schema of whatever database you connect to but I can't seem to find it again (web searching.)I did come across one post which said that this was a planned feature for pgAdmin. I need to do a formal schema of a database which I have in development and it would be nice to have an automated tool, whether it connects to the database or uses SQL table definition code to generate graphics. Is there such a thing? if not, what do you all use? OpenOffice Draw? OmniGraffle? -Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
On Thu, Nov 11, 2010 at 11:45 AM, David Fetter wrote: >> max_prepared_transactions = 100 # guideline: same number as max_connections > > This should be either 0 (no 2PC) or the bounded from below by > max_connections. > In general, sure. I have one app that uses 2PC, and it makes maybe 2% of the connections, so it is pointless to have it set very high. If it were fatal, I'd hope Pg would enforce it with at least a warning. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 2PC w/ dblink
On 11/11/10 5:17 AM, Vick Khera wrote: On Wed, Nov 10, 2010 at 12:39 PM, John R Pierce wrote: My developers are complaining about the lack of support for 2 phase commit in this scenario.Can we get any mileage on PREPARE TRANSACTION in a dblink sort of environment like this? Yes, that's an ideal case for this. We use it outside of dblink with two direct connections to two databases. Just be sure you have some monitoring that will alert you to prepared transactions that are lingering for a long time. Also, if you have a pending prepared transaction on a host with a newly inserted row, and you retry inserting that row from another connection, you will get an immediate "statement timeout" error. This is a bit confusing at first but once you know what the cause is, it is easy to work with. I guess my question really is, can a pl/pgsql trigger procedure that in turn is using dblink to talk to another database use BEGIN, PREPARE TRANSACTION, and COMMIT PREPARED a transaction over the dblink ? afaik, this code currently isn't using a remote transaction at all, its just doing simple INSERT or UPDATE on the remote database. and how does that interact with the parent transaction on the local server? I'm pretty sure our trigger can't exactly do the PREPARE TRANSACTION from within the trigger procedure. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
On 11/11/10 12:45, Marc Mamin wrote: Hello, may schemaspy help you ? http://schemaspy.sourceforge.net/sample/relationships.html HTH, Marc Mamin Thanks for this link! I've been looking for something that can run on the command line for quite a while, and even better it outputs to html and shows me some, um, interesting things in my database that probably need addressing. Warm regards from snowy Wyoming! Jeff Ross -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Marc, > may schemaspy help you ? > http://schemaspy.sourceforge.net/sample/relationships.html Thank you *VERY* much for suggesting this tool! -- Gary Chambers /* Nothing fancy and nothing Microsoft! */ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Thanks, each of you for all the suggestions on schema generating tools. I haven't had a chance to evaluate them all yet but DBVisualizer looks pretty good. In the meanwhile I found SQL Power Architect, which is also free/open source, and can do this kind of diagraming but is not as good as DBVisualizer for my purposes. It has some other interesting features which would be of interest to someone working in an environment with several different kinds of databases. SchemaSpy looks to be a good option since the description mentions that they had to implement product-specific queries to support views but I haven't had a chance to try it out yet. Support for views is important to me and DBVisualizer and the other tools I have looked at so far, just display them as disconnected objects. I was thinking of reporting back to this forum with advantages/disadvantages of each tool, as I see it, but realized that I was rapidly getting too far off topic for a list focused specifically on PostgreSQL. -Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Aram Fingal wrote on 11.11.2010 22:45: I was thinking of reporting back to this forum with advantages/disadvantages of each tool, as I see it, but realized that I was rapidly getting too far off topic for a list focused specifically on PostgreSQL. I don't think this woul be off-topic here if you post your experience using those tools together with PostgreSQL Actually I think it would be worthwhile documenting your experience in the PostgreSQL Wiki as well: http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Seeking advice on database replication.
Hello, I am interested in sharing/replicating data between different databases, and I'd like to ask if what I'd like to do is possible in postgresql. I have read a fair amount of documentation and was looking forward to PostgreSQL 9, but I don't think it will do for me what I want. I have an astronomical database at one site, let's call it A. At my own institution (across the country), I have another database, B. I want to replicate all of the tables of A into a read-only copy in B, in as close to real-time as possible. The time isn't a critical factor here - if it's delayed by even an hour, I'm ok with that. Tables in B will need to JOIN against tables from A. The total size of A is ~80MB and grows slowly. After reading the documentation for PG9's replication, it seems I cannot do this since it only supports replicating a cluster. It appears that I'd want to put the tables in B into one schema, the tables from A into another schema in the same database (let's call it B.a), and replicate the tables from A into B.a. Is this at all possible? This promises to be a very powerful tool for us, but I don't know how best to accomplish this. Further, I'd like A to be replicated to several institutions. Again, this is not a real-time operation, but something that doesn't require user intervention is ideal. I tried to run Slony-I last year, but found it to be very frustrating and never got it to work. (In retrospect, I don't even know if it supports schema-level replication). Any advice would be greatly appreciated! Cheers, Demitri Center for Cosmology and Particle Physics New York University -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Instructions/status of modpglogger
Hi All ! I just started with postgres and wish to use a postgres db as a logging destination for my apache webserver in my debian environment [this was not the reason to start with postgres]. Does someone, if this works [thre is no package in my distribution]? Any tips would be great! br++mabra -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
Thomas Kellerer writes: > Aram Fingal wrote on 11.11.2010 22:45: >> I was thinking of reporting back to this forum with >> advantages/disadvantages of each tool, as I see it, but realized that >> I was rapidly getting too far off topic for a list focused >> specifically on PostgreSQL. > I don't think this woul be off-topic here if you post your experience using > those tools together with PostgreSQL Agreed, that seems well within the list's area of interest. If you told us about some tool that couldn't be used with PG, maybe we'd get impatient. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seeking advice on database replication.
On Thu, Nov 11, 2010 at 3:05 PM, Demitri Muna wrote: > Hello, > > I am interested in sharing/replicating data between different databases, and > I'd like to ask if what I'd like to do is possible in postgresql. I have read > a fair amount of documentation and was looking forward to PostgreSQL 9, but I > don't think it will do for me what I want. > > I have an astronomical database at one site, let's call it A. At my own > institution (across the country), I have another database, B. I want to > replicate all of the tables of A into a read-only copy in B, in as close to > real-time as possible. The time isn't a critical factor here - if it's > delayed by even an hour, I'm ok with that. Tables in B will need to JOIN > against tables from A. The total size of A is ~80MB and grows slowly. > > After reading the documentation for PG9's replication, it seems I cannot do > this since it only supports replicating a cluster. It appears that I'd want > to put the tables in B into one schema, the tables from A into another schema > in the same database (let's call it B.a), and replicate the tables from A > into B.a. Is this at all possible? This promises to be a very powerful tool > for us, but I don't know how best to accomplish this. > > Further, I'd like A to be replicated to several institutions. Again, this is > not a real-time operation, but something that doesn't require user > intervention is ideal. > > I tried to run Slony-I last year, but found it to be very frustrating and > never got it to work. (In retrospect, I don't even know if it supports > schema-level replication). Next time, post questions to the slony mailing list if you get stuck, we're pretty helpful over there. also look at some of the "my first replicated db" tutorials for it. Slony, btw, supports replicating whatever you want to replicate. You build a set with the tables in it and replicate that set. That set can be almost any group of tables and sequences yuo want to define. > Any advice would be greatly appreciated! Well, I was gonna suggest slony, however... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Instructions/status of modpglogger
On 11/11/10 14:48, ma...@manfbraun.de wrote: Hi All ! I just started with postgres and wish to use a postgres db as a logging destination for my apache webserver in my debian environment [this was not the reason to start with postgres]. Does someone, if this works [thre is no package in my distribution]? Any tips would be great! br++mabra If that doesn't work (and something that hasn't been updated since 2004 is doubtful) there is an opensource utility called pglogd http://www.digitalstratum.com/oss/pglogd that works pretty well for me through 8.4.5. I haven't tried it yet on 9. Digital Stratum EOLed pglogd and I sort of tried to fork the project and fix a couple of things that it doesn't do very well--like allow the backend to shutdown because it opens a connection and never lets it go--but my C skills are lacking and time more so. If the version from Digital Stratum doesn't work and you (or anyone else for that matter) would like my slightly modified version let me know off list and I'll get it to you. Jeff Ross -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem using CASTed timestamp column
Hi all, I've got an odd problem. I've got a column with a non-standard date/time in it that I carve up and re-cast as a timestamp. This works fine. Now though, I am trying to check/restrict my results to dates before now() and it's telling me that the columns doesn't exist. http://pastebin.com/sExiBynp Any hints on what I am doing wrong? Thanks! -- Digimer E-Mail: digi...@alteeve.com AN!Whitepapers: http://alteeve.com Node Assassin: http://nodeassassin.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem using CASTed timestamp column
On Thu, Nov 11, 2010 at 2:26 PM, Digimer wrote: > http://pastebin.com/sExiBynp > Any hints on what I am doing wrong? You can't refer to a column alias name in the order by clause unfortunately. You'll need to either nest this query in another query to use the alias name, or you have to use a physical column name or an expression. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema tool
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Marc Mamin > Sent: Thursday, November 11, 2010 11:46 AM > To: Aram Fingal; Postgres-General General > Subject: Re: [GENERAL] Schema tool > > Hello, > > may schemaspy help you ? > http://schemaspy.sourceforge.net/sample/relationships.html This thing is also nice: http://sourceforge.net/projects/mogwai/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Considering Solid State Drives
Search the PG performance mailing list archive. There has been some good posts about SSD drives there related to PG use. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Allan Kamau Sent: Wednesday, November 10, 2010 11:42 PM To: Postgres General Postgres General Subject: [GENERAL] Considering Solid State Drives Hi, As part of datamining activity. I have some plpgsql functions (executed in parallel, up to 6 such concurrent calls) that perform some reads and writes of large number of (maybe 1) records at a time to a table having multi-column primary key. It seems the writing of these few thousands records is taking a long time (up to 5mins in some cases). Running vmstat reports %iowait between 15 and 24 on a single 7200rpm SATA drive, six core (Phenom II) "server". I am now thinking of investing in a SSD (Solid State Drive), and maybe choosing between "Crucial Technology 256GB Crucial M225 Series 2.5-Inch Solid State Drive (CT256M225)" and "Intel X25-M G2 (160GB) - Intel MLC". I have looked at the comparison statistics given here "http://www.anandtech.com/bench/Product/137?vs=126"; that suggests that the Intel offering is more geared for small random write operations. After googling I found little resent content (including survival statistics) of using SSDs in a write intensive database environment. Kindly advice, Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] More then 1600 columns?
Greeting PostgreSQL Gurus. >From my goggling I know this has been discussed before and from what I have >read it seems that the consensus is you can re-compile postgres and increase >the block size from 8k to 16k or 32k to double or quadruple this limit. I re-compiled the latest 9.0.1 source rpm on CentOS 5 x64 setting --with-blocksize=32 but when I tested it I still get the error "Error: tables can have at most 1600 columns" pg_controldata verifies the block size is "Database block size: 32768" I also tried to increate the WAL block size to see if that had any effect but it does not. So is what I have read wrong? Is there are hard limit of 1600 that you cannot get around? - Mark
Re: [GENERAL] More then 1600 columns?
"Mark Mitchell" writes: > Is there are hard limit of 1600 that you cannot get around? Yes. Generally, wanting more than a few dozen columns is a good sign that you need to rethink your schema design. What are you trying to accomplish exactly? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] More then 1600 columns?
On 11/11/10 9:24 PM, Tom Lane wrote: "Mark Mitchell" writes: Is there are hard limit of 1600 that you cannot get around? Yes. Generally, wanting more than a few dozen columns is a good sign that you need to rethink your schema design. What are you trying to accomplish exactly? indeed.I'd say a good read on 'data normalization' and the Third Normal Form would be in order. relational databases are *not* spreadsheets (and, for that matter, spreadsheets make lousy relational databases) if these 1600+ elements come from an ORM, you probably need to rethink your object model, as no sane object class should have that many members. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] More then 1600 columns?
Hey Mark, Yeah, I can't imagine an entity in a real project even with more than 100 columns. Its rare case. But if you entities (rows/tuples) of some class (table) can contains variable set of columns (properties) you can look at hstore contrib module. 2010/11/12 John R Pierce > On 11/11/10 9:24 PM, Tom Lane wrote: > >> "Mark Mitchell" writes: >> >>> Is there are hard limit of 1600 that you cannot get around? >>> >> Yes. >> >> Generally, wanting more than a few dozen columns is a good sign that you >> need to rethink your schema design. What are you trying to accomplish >> exactly? >> >> > > indeed.I'd say a good read on 'data normalization' and the Third Normal > Form would be in order. > > relational databases are *not* spreadsheets (and, for that matter, > spreadsheets make lousy relational databases) > > if these 1600+ elements come from an ORM, you probably need to rethink your > object model, as no sane object class should have that many members. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
Re: [GENERAL] More then 1600 columns?
On Fri, Nov 12, 2010 at 10:17:50AM +0300, Dmitriy Igrishin wrote: > Hey Mark, > > Yeah, I can't imagine an entity in a real project even with more than 100 > columns. Its rare case. > But if you entities (rows/tuples) of some class (table) can contains > variable > set of columns (properties) you can look at hstore contrib module. What can also work extremely well is storing the data in an array. If you need to access the array based on more meaningful keys you could store key/index pairs in another table. This approach only works well if you have multiple arrays with the same layout. You probably also need to build up your query dynamically if you need to access variable numbers of datapoints. Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] More then 1600 columns?
Hey Peter, Unfortunately, there is no indexes on arrays (only on expressions). With hstore we can easily create GiST index for effective access. 2010/11/12 Peter Bex > On Fri, Nov 12, 2010 at 10:17:50AM +0300, Dmitriy Igrishin wrote: > > Hey Mark, > > > > Yeah, I can't imagine an entity in a real project even with more than 100 > > columns. Its rare case. > > But if you entities (rows/tuples) of some class (table) can contains > > variable > > set of columns (properties) you can look at hstore contrib module. > > What can also work extremely well is storing the data in an array. > If you need to access the array based on more meaningful keys you could > store key/index pairs in another table. > > This approach only works well if you have multiple arrays with the same > layout. You probably also need to build up your query dynamically if > you need to access variable numbers of datapoints. > > Cheers, > Peter > -- > http://sjamaan.ath.cx > -- > "The process of preparing programs for a digital computer > is especially attractive, not only because it can be economically > and scientifically rewarding, but also because it can be an aesthetic > experience much like composing poetry or music." >-- Donald Knuth > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
Re: [GENERAL] More then 1600 columns?
On Fri, Nov 12, 2010 at 10:43:14AM +0300, Dmitriy Igrishin wrote: > Hey Peter, > > Unfortunately, there is no indexes on arrays (only on expressions). > With hstore we can easily create GiST index for effective access. True. In my project I only ever needed to search on a particular key, and I made sure that that key always had a fixed position in the array. You can then create an index on the expression that extracts that index from the array. Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
