[GENERAL] Building 3rd-party contrib/extension on Windows?
Hi, PostgreSQL has lots of useful modules/extentions even outside the core distribution. I'm trying to find out how I can build such 3rd-party contrib/extention on Windows. For example, I'm using PostgreSQL on Windows, bulit and distributed by EnterpriseDB, and I want to run pgTAP on it. AFAIK, there's no Windows binary distribution for pgTAP. So, I guess I need to build this extention myself. Other useful extentions as well. Is there any good guide to build such 3rd-party contrib/extention on Windows, particularly to work with EDB distribution? Or do you have any experience which can be shared? Regards, -- Satoshi Nagayasu Uptime Technologies, LLC. http://www.uptime.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hosting PG on AWS in 2013
First I need to say that I'm asking this question on behalf of "a friend", who asked me what I thought on the subject -- I host all the databases important to me and my livelihood, on physical machines I own outright. That said, I'm curious as to the current thinking on a) whether it is wise, and b) if so how to deploy, PG servers on AWS. As I recall, a couple years ago it just wasn't a wise plan because Amazon's I/O performance and reliability wasn't acceptable. Perhaps that's no longer the case.. Just to set the scene -- the application is a very high traffic web service where any down time is very costly, processing a few hundred transactions/s. Scanning through the latest list of AWS instance types, I can see two plausible approaches: 1. High I/O Instances: (regular AWS instance but with SSD local storage) + some form of replication. Replication would be needed because (as I understand it) any AWS instance can be "vanished" at any time due to Amazon screwing something up, maintenance on the host, etc (I believe the term of art is "ephemeral"). 2. EBS-Optimized Instances: these allow the use of EBS storage (SAN-type service) from regular AWS instances. Assuming that EBS is maintained to a high level of availability and performance (it doesn't, afaik, feature the vanishing property of AWS machines), this should in theory work out much the same as a traditional cluster of physical machines using a shared SAN, with the appropriate voodoo to fail over between nodes. Any thoughts, wisdom, and especially from-the-trenches experience, would be appreciated. In the Googlesphere I found this interesting presentation : http://www.pgcon.org/2012/schedule/attachments/256_pg-aws.pdf which appears to support option #2 with s/w (obviously) RAID on the PG hosts, but with replication rather than SAN cluster-style failover, or perhaps in addition to. Note that I'm not looking for recommendations on PG hosting providers (in fact my friend is looking to transition off one of them, to bare-AWS machines, for a variety of reasons). Thanks. -- 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] how to get collation according to Unicode Collation Algorithm?
On 2013-04-06, rudolf wrote:
> Hi,
>
> I have a problem with proper collation of UTF-8 strings using PostgreSQL
> version 9.2.4 under Debian Linux 6.0 with de_DE.utf8 (but en_US behaves
> the same) locale:
>
> CREATE TABLE test_collation ( q text );
> INSERT INTO test_collation (q) VALUES ('aa'), ('ac'), ('a&b');
> SELECT * FROM test_collation ORDER BY q COLLATE "de_DE";
>q
> -
> aa
> a&b
> ac
postgres uses the collation provided by libc (wccasecmp() I think)
> Is there a way to achieve this collation (note also the order of the
> characters with umlaut on the screenshot) with PostgreSQL? Or is it a
> glibc bug?
yeah, it's a feature of glibc.
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] REL/Centos4 release of 8.4.17?
I know Centos 4 is EOL, but will there be a REL/Centos 4 release of postgres 8.4.17? The latest here is 8.4.16: http://yum.postgresql.org/8.4/redhat/rhel-4-i386/repoview/ If not, thanks for the many Centos 4 packages over the years. -- Jared Beck - Singlebrook - (607) 330-1493 -- 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] Need advice to avoid ORDER BY
On 2013-04-04, Condor wrote: > Hello, > > I have one query in my postgresql 9.2.3 that took 137 ms to me executed > and looking a way > what I can do to optimize it. I have one table generated numbers from 1 > to 1 000 000 and > I need to get first free id, meanwhile id's when is taken can be free > (deleted data and id > is free for next job). Table is simple: > > > id serial, > jobid text, > valids int default 0 > > (Yes, I have index). > > > my query is: SELECT jobid FROM mytable WHERE valids = 0 ORDER BY id ASC > LIMIT 1 > > I need the first id only. > > My question is: Is there a way how I can avoid using ORDER BY to > receive the first > free id from mytable ? create index freejobs on mytable(id) where valids = 0 ; retry the same query. -- ⚂⚃ 100% natural -- 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] bug in COPY implementation (all versions of Postgres)?
On 2013-04-05, Konstantin Izmailov wrote: > > 2. Insert value 'Galaxy\040Tab' using command COPY TEST(description) FROM > stdin WITH DELIMITER '|' CSV. > > The following error is returned: value too long for type character > varying(10) CSV is essentially a binary format. there is no ecaping in CSV (except quotes are doubled inside quoted values) In CSV format \040 is 4 characters. -- ⚂⚃ 100% natural -- 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] Using varchar primary keys.
On 2013-04-02, Joe Van Dyk wrote: > On Tue, Apr 2, 2013 at 11:16 AM, Merlin Moncure wrote: >> On Tue, Apr 2, 2013 at 10:34 AM, Joe Van Dyk wrote: >> > >> > I've been wishing for a smaller uuid type for a while. >> > I've been using a unique text column with a default of >> > random_characters(12) >> >> This is pseudo random and can be guessed, which is maybe dangerous >> depending on circumstance. For stronger random stream go to >> pgcrypto.gen_random_bytes(). > > Right, but it's better than using serial's as far as being guessable. > > The probability for collisions are fairly low, if you are using 12 or more > characters (with 30 possible characters). Not sure what the math is on the > probability of collisions (birthday problem) though.. for n samples of p possibles it's approximately n(n-1)/2p for n^2 < p a alphabet of 30 symbols is almost 5 bits per symbol so for 12 symbols you get about 60 bits which almost half as wide as a UUID (128b) > and you could have a > trigger that checked for the existence of a matching key before > inserts/updates. And the associated race condition... no thanks. you could encrypt a serial type using some reversible encryption eg: there's a feistel cypher implemented in plpgsql in the wiki somewhere >> My historical comments in this debate are noted. To summarize, I >> strongly believe that natural keys are often (but not always) better. I'll use natural keys where they are short enough to be useful and guaranteed constant. -- ⚂⚃ 100% natural -- 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] High CPU usage of stats collector
On Sun, Mar 31, 2013 at 2:44 PM, komunca wrote: > On my db server I have noticed a very high CPU usage of process: > *postgres: stats collector process* > > It is not below 50% of CPU. (Info from htop). > > What is best way to decrease CPU usage of this process? > This is a known issue when you have a large number of databases with a large number of objects each. It will be fixed in 9.3. In the meantime you can perhaps increase autovacuum_naptime, but that has tradeoffs. Cheers, Jeff
Re: [GENERAL] Source code and database object identifiers
brad st writes: > We are planning to add PostgreSQL database support to our application. We > have run into the issue of where in PostgreSQL is converting all the > database object identifiers into lower case. I understand that's the how > PostgreSQL works and I can double quote the identifiers and preserve the > camel case. Unfortunately I cannot double quote the identifiers and need to > preserve the camel case (mixed case) for the identifiers for our > application to work. You would really, really, really be better off fixing your application to double-quote as needed. Otherwise you're locking yourself into an entirely nonstandard variant of SQL. > Can someone please provide some guidance where I should make the changes to > preserve mixed case for identifiers? Well, it's not exactly hard to lobotomize downcase_truncate_identifier, or maybe better s/downcase_truncate_identifier/truncate_identifier/g in parser/scan.l. The problem is dealing with all the ensuing breakage. The first thing I imagine you'd hit is that there's general lack of consistency among applications as to whether the names of built-in functions are spelled in upper or lower case; this is partly because many of those names are actually keywords according to the standard. 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] BEFORE UPDATE trigger doesn't change column value
Kevin Grittner wrote: > Your BEFORE UPDATE trigger could leave the "synced" value in NEW > alone if force_sync was false, and set "synced" to false > otherwise. It could then set NEW.force_sync to false, to leave you > ready for the next update. Sorry, that's the wrong way around. I should have said: Your BEFORE UPDATE trigger could leave the "synced" value in NEW alone if force_sync was **true**, and set "synced" to false otherwise. It could then set NEW.force_sync to false, to leave you ready for the next update. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Source code and database object identifiers
Hi all, We are planning to add PostgreSQL database support to our application. We have run into the issue of where in PostgreSQL is converting all the database object identifiers into lower case. I understand that's the how PostgreSQL works and I can double quote the identifiers and preserve the camel case. Unfortunately I cannot double quote the identifiers and need to preserve the camel case (mixed case) for the identifiers for our application to work. I wouldn't mind changing the source code to help us in this issue. I have set up the debug environment on eclipse and able to compile + debug the PostgreSQL. Can someone please provide some guidance where I should make the changes to preserve mixed case for identifiers? Thank you Brad.
Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value
Clemens Eisserer wrote: > Here is what I am trying to achieve: Set "synced" to false at any > update, except when it has been set explicitly to true. > This does not seem to be possible, without checking the value SET > by UPDATE? Right; since there is no way to check whether a 'true' value there was explicitly set or just carrying over from the old version of the row without being set, you need some other mechanism for handling this. You could, for exampe, add a "force_sync" column which could be tested in a trigger. Your BEFORE UPDATE trigger could leave the "synced" value in NEW alone if force_sync was false, and set "synced" to false otherwise. It could then set NEW.force_sync to false, to leave you ready for the next update. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to get collation according to Unicode Collation Algorithm?
Hi,
I have a problem with proper collation of UTF-8 strings using PostgreSQL
version 9.2.4 under Debian Linux 6.0 with de_DE.utf8 (but en_US behaves
the same) locale:
CREATE TABLE test_collation ( q text );
INSERT INTO test_collation (q) VALUES ('aa'), ('ac'), ('a&b');
SELECT * FROM test_collation ORDER BY q COLLATE "de_DE";
q
-
aa
a&b
ac
I need the "&" character to be sorted at the beginning or at the end of
the alphabet, but it seems like it is simply ignored. The space
character (" ") is treated the same way (just swap the ampersand in
previous example with a space).
I made a test on ICU pages (http://site.icu-project.org/) and there I
get proper collation: 1. a&b, 2. aa, 3. ac. Screenshot:
http://software.eq.cz/icu_collation_de_DE.png
Is there a way to achieve this collation (note also the order of the
characters with umlaut on the screenshot) with PostgreSQL? Or is it a
glibc bug?
Thanks,
r.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] optimizer's cost formulas
Hello everybody! I'm trying to understand the the query planner's cost estimator. I was not able to find anywhere the formulas that estimate the cost of each operation that the optimizer produces. I only found this for the sequential scan: SEQ SCAN = ( cpu_tuple_cost * rows ) + ( number of pages * seq_page_cost ) where : cpu_tuple_cost=0.01 seq_page_cost=1.0 and "rows" and "number of pages" are given in the query plan (via EXPLAIN ANALYZE) I am wondering where could I find the rest formulas for the rest operations (e.g. HashAggregate, Nested Loop, Hash Join, Index Scan, Sort, etc) I also looked at costsize.c but could not find a formula like the above or maybe I couldn't make sense of it Thank you in advance!
