Re: [GENERAL] Performance statistics
On 1/04/2010 9:03 PM, Michael Gould wrote:
Without asking for any blood letting, I'm wondering if there are any
hard statistics available to prove if Windows Server is faster than,
slower than or the same as for performance to one of the various Linux
distributions.
I haven't seen any hard windows vs linux on same hardware benchmarks on
this list or -performance, at least recently.
Pg is certainly more mature on *nix, though the only obvious and
lingering win32 bug ("cannot detach from shared memory") appears to have
been nailed now.
While our application is a commerical application, in our
survey we've been asked for information on running the server on a Linux
box vs a Windows Server.
I suspect that running on a Linux server will be faster, however I'm
concerned about maintenance at customer sites who have no Linux support
and are a Windows based shop. The application is written with a Windows
based language.
Yep, that's a big one. If everything else is on Windows, should another
platform be introduced just for your app? You'll be expected to support
Linux+Pg, not just Pg.
OTOH, is it worth another Win2k8 license + CALs?
If your app and DB can happily live on an existing server with other
apps the client uses, then sticking to Windows is a no-brainer.
Otherwise, can your app run on the same machine as the DB, or will load
force the DB to a separate host? If it can run on the same machine and
needs Windows anyway, adding a Linux box seems rather unnecessary.
If you expect lots of load from the start and intend to separate
app-host from db-host anyway, then perhaps a Linux system is worth
considering. Support it as a DB appliance.
Not all virus scanners play well with Pg. If your client is one of the
nuts ones that has a "virus scanner must be on every Windows machine,
even sealed servers" policy, you might want to look at Linux to get
around that. Of course, if they require a virus scanner on every machine
no matter what platform, and expect to be able to specify a certain
vendor, you might be in trouble no matter what.
It probably depends a bit on the client, too. Do they have platform
policies? Do they have in-house IT? If so, even if they're completely
useless with Linux, they may be willing/able to learn a bit and can at
least be useful as a human remote console. At the very least they'll be
able to set up ssh access via vpn or port forward.
Personally I'd want to be prepared to support both Linux and Windows DB
backends, depending on client needs, deployment scale, etc.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] join two tables without a key
Hi postgresql list, If I have two tables with the same number of rows but different columns and I want to create one table out of them what would be the way to do that in postgresql? Table A has N number of rows and columns X,Y,Z and Table B has N number of rows and P,Q,R as columns. None of the tables have a column which can be used as a key. The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns. 1) How to accomplish this is plain SQL? Join on rowid? 2) What would a PL-pgsql program look like to accomplish ths? 3) If N=1000 and the two tables have 45 columns with mixed integer and character values, what would be the most efficient approach (fastest) and why? Thanks in advanced
Re: [GENERAL] join two tables without a key
On 03/04/2010 11:16, Dino Vliet wrote: > Hi postgresql list, If I have two tables with the same number of rows > but different columns and I want to create one table out of them what > would be the way to do that in postgresql? > > Table A has N number of rows and columns X,Y,Z and Table B has N > number of rows and P,Q,R as columns. None of the tables have a > column which can be used as a key. > > The resulting table should have N number of rows and X,Y,Z,P,Q,R as > columns. How do the rows in the tables relate to each other? You need to decide first how you match the rows in A and B. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] join two tables without a key
On 04/03/2010 11:16 AM, Dino Vliet wrote: If I have two tables with the same number of rows but different columns and I want to create one table out of them what would be the way to do that in postgresql? Table A has N number of rows and columns X,Y,Z and Table B has N number of rows and P,Q,R as columns. None of the tables have a column which can be used as a key. The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns. You haven't said what you want the result to mean. - Jeremy -- 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] join two tables without a key
--- On Sat, 4/3/10, Raymond O'Donnell wrote: From: Raymond O'Donnell Subject: Re: [GENERAL] join two tables without a key To: "Dino Vliet" Cc: pgsql-general@postgresql.org Date: Saturday, April 3, 2010, 1:01 PM On 03/04/2010 11:16, Dino Vliet wrote: > Hi postgresql list, If I have two tables with the same number of rows > but different columns and I want to create one table out of them what > would be the way to do that in postgresql? > > Table A has N number of rows and columns X,Y,Z and Table B has N > number of rows and P,Q,R as columns. None of the tables have a > column which can be used as a key. > > The resulting table should have N number of rows and X,Y,Z,P,Q,R as > columns. How do the rows in the tables relate to each other? You need to decide first how you match the rows in A and B. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Hi Ray, they don' t. It' s pure randomly generated data. Brgds
Re: [GENERAL] join two tables without a key
On 03/04/2010 12:32, Dino Vliet wrote: > --- On Sat, 4/3/10, Raymond O'Donnell wrote: > On 03/04/2010 11:16, Dino Vliet wrote: > >> Hi postgresql list, If I have two tables with the same number of rows >> but different columns and I want to create one table out of them what >> would be the way to do that in postgresql? >> >> Table A has N number of rows and columns X,Y,Z and Table B has N >> number of rows and P,Q,R as columns. None of the tables have a >> column which can be used as a key. >> >> The resulting table should have N number of rows and X,Y,Z,P,Q,R as >> columns. > > How do the rows in the tables relate to each other? You need to decide > first how you match the rows in A and B. > they don' t. It' s pure randomly generated data. In that case, how about getting the cartesian product of the two tables, and then LIMITing the result to N rows? - Something like this: select a.x, a.y, a.z, b.p, b.q, b.r from a, b limit N; substituting your value of N. It'll be slow if there are a lot of rows in A and B, mind. Ray. -- 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] join two tables without a key
Dino Vliet wrote:
> Hi postgresql list,
>
>
> If I have two tables with the same number of rows but different columns and I
> want to create one table out of them what would be the way to do that in
> postgresql?
>
>
>
>
> Table A has N number of rows and columns X,Y,Z and Table B has N number of
> rows
> and P,Q,R as columns. None of the tables have a column which can be used as a
> key.
>
> The resulting table should have N number of rows and X,Y,Z,P,Q,R as columns.
Stupid table design, but okay:
test=# select * from a;
a1 | a2 | a3
-+-+-
100 | 101 | 102
103 | 104 | 105
106 | 107 | 108
109 | 110 | 111
(4 Zeilen)
Zeit: 0,378 ms
test=*# select * from b;
b1 | b2 | b3
-+-+-
201 | 202 | 203
204 | 205 | 206
207 | 208 | 209
210 | 211 | 212
(4 Zeilen)
Zeit: 0,317 ms
test=*# create sequence sa;
CREATE SEQUENCE
Zeit: 18,618 ms
test=*# create sequence sb;
CREATE SEQUENCE
Zeit: 0,939 ms
test=*# select foo_a.*, foo_b.* from (select nextval('sa') as id_a,*
from a) foo_a left join (select nextval('sb') as id_b,* from b) foo_b on
foo_a.id_A=foo_b.id_b;
id_a | a1 | a2 | a3 | id_b | b1 | b2 | b3
--+-+-+-+--+-+-+-
1 | 100 | 101 | 102 |1 | 201 | 202 | 203
2 | 103 | 104 | 105 |2 | 204 | 205 | 206
3 | 106 | 107 | 108 |3 | 207 | 208 | 209
4 | 109 | 110 | 111 |4 | 210 | 211 | 212
(4 Zeilen)
Zeit: 0,618 ms
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] Advice on webbased database reporting
Hi, Davor J. írta: > I need to make certain views from the database visible online (on our > webpage) and I wonder if there is any reasonably quick solution for this > that works with Postgres? > > At best, a query should be specified and the user should be able to select > the layout on certain columns (like stepped, or outlined). > > I don't mind running a whole CMS on our Apache server as long as it allows > me to make reports and is free to use. > > Has anyone any suggestions? > > Kind regards, > Davor > have you seen RLIB? It's a nice XML based reporting tool that can use PostgreSQL. The output can be PDF, HTML, etc. http://rlib.sicompos.com/ You should get the CVS version from sourceforge, it has quite some fixes since the last public release. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] count function alternative in postgres
Is there any alternative of mysql function COUNT(DISTINCT expr, [expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] count function alternative in postgres
Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct Thanks -- View this message in context: http://old.nabble.com/count-function-alternative-in-postgres-tp28126793p28126793.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] count function alternative in postgres
junaid malik writes: > Is there any alternative of mysql function COUNT(DISTINCT expr, > [expr...]) in postgres. We get error if we The SQL-standard way to do that would be select count(*) from (select distinct expr,expr,... from ...) as ss; COUNT with multiple arguments is not anywhere in 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
[GENERAL] ERROR: cache lookup failed for relation X
Hi All, I am facing the error "cache lookup failed for relation X" in Postgres-8.4.2 version. As you all know, its a reproducable and below is the example. This can be generated with two sessions; Am opening two sessions here Session A and Session B Session A = step 1 - creating the table postgres=# create table cache(id integer); step 2 - Create the trigger and the function on particular table postgres=# CREATE FUNCTION cachefunc() RETURNS trigger AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql; postgres=# CREATE TRIGGER cachetrig BEFORE INSERT ON cache FOR EACH ROW EXECUTE PROCEDURE cachefunc(); Step 3 - Inserting a row in a table postgres=# insert into cache values (1); Step 4 - Droping the table in BEGIN block and issuing the same drop in another session B postgres=# begin; postgres=# drop table cache; step 5 - Open the second session B and issue the same command postgres=# drop table cache; --- In session B, this will wait untill commit is issued by the Session A. step 6 - Issue the commit in Session A postgres=# commit; Step -7 now we can the see the error in the session B ERROR: cache lookup failed for relation X Could plese tell me, why this is generated and what is the cause. Thanks in advance Regards Raghavendra
Re: [GENERAL] ERROR: cache lookup failed for relation X
Because You dropped/deleted the table cache in Session A. The simplest way to look at it is Session B was lock out when the Drop table command was issued from Session A. Now when session B finally got its chance to drop/delete the table it was already gone . What kind error were you expecting from Postgresql to Return when it can't find the table??? In the future please don't cross post to multiple lists. Message from mailto:raagavendra@gmail.com raghavendra t raagavendra@gmail.com at 04-03-2010 10:08:11 PM -- step 6 - Issue the commit in Session A postgres=# commit; Step -7 nowwe can the see the error in the session B ERROR: cache lookup failed for relation X Could plese tell me, why this is generated and what is the cause. Thanks in advance Regards Raghavendra All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [GENERAL] ERROR: cache lookup failed for relation X
raghavendra t writes: > I am facing the error "cache lookup failed for relation X" in Postgres-8.4.2 > [ when dropping the same table concurrently in two sessions ] > Could plese tell me, why this is generated and what is the cause. >From the perspective of session B, the table disappeared after it had already found the table in the catalogs and obtained a lock on it. This is not readily distinguishable from a serious problem such as catalog corruption. While we could play dumb and just issue a "table does not exist" message as though we'd never found it in the catalog at all, that behavior could mask real problems, so it seems better to not try to hide that something unusual happened. In general I would say that an application that is trying to do this has got its own problems anyway --- how do you know which version of the table you're dropping, and that that's the right thing? 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] count function alternative in postgres
junaid malik wrote: Is there any alternative of mysql function COUNT(DISTINCT expr, [expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count-distinct Thanks I already answered your question on dbforums.com. But in Postgres you can do: SELECT COUNT( DISTINCT row(col1, col2, col3) ) FROM foo or SELECT COUNT( DISTINCT (col1, col2, col3) ) FROM foo -- 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] any built-in function to get time in seconds?
zhong ming wu wrote: I have been using this one liner c function that I call my_now() to get the number of seconds since some fixed point in the past. I find it more convenient than built-in now() and if I want abstime I do abstime(my_now()). Thing is everytime I do a major version upgrade I had to recompile this and it's a pain in the neck. I feel there must be something built-in with pg to get the same thing since I can get abstime from it like that. Well you haven't specified which fixed point in the past. But if you want to get the number of seconds since the unix epoch use: extract(epoch from current_timestamp) Scott -- 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 implement word wrap
Really, write a stored procedure that accepts (text, line_length) and returns SETOF text. You could even add hyphenation for the appropriate language if you go that route. For the latter it's probably best to write it in C so you can link hyphenation libraries to your code. Another approach that may be viable is to use windowing functions, but I'm not so sure it's possible to have a window that is being defined by the data it's running over (eg. a window defined by the length of an accumulated line of text). Implementations from http://sqlserverpedia.com/wiki/Word_Wrap_a_String and from http://docstore.mik.ua/orelly/oracle/prog2/ch11_02.htm#AUTOID-10508 paragraph 11.2.2 did not work in Postgres. I created method below. Is this best code for this ? Andrus. CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer) RETURNS SETOF text as $$ DECLARE words text[] := string_to_array(line,' '); i integer; res text:=''; BEGIN if trim(line)='' then return next ''; return; end if; for i IN 1 .. array_upper(words,1) LOOP if length(res)+length(words[i]) > linelen THEN return next res; res := ''; END IF ; if res<>'' then res := res || ' '; end if; res := res || words[i]; end loop; return next res; END $$ LANGUAGE plpgsql; -- 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] Connection Pooling
Allan Kamau wrote: You may also have a look at Commons DBCP from Apache software foundation, "http://commons.apache.org/dbcp/";. I have used it for a few projects and have had no problems. John R Pierce wrote: for that matter, JDBC has its own connection pooling in java. David Kerr wrote: It looks like both of those solutions require a coding change. I'm hoping for a middleware solution similar to pgpool/pgbouncer. How's that? If JDBC has its own connection pooling and you're already using JDBC, voilà! no change needed, right? If you're using a DataSource for JDBC, then it's a configuration change, not a code change, to switch to different databases, including to a different brand like Oracle instead of PG, never mind to switch between a pooled or unpooled connection. Most app servers, including Tomcat, provide DBCP inbuilt with no further effort on the programmer's or even the deployer's part. So to what coding changes do you refer? -- Lew -- 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] Connection Pooling
On Fri, Mar 26, 2010 at 5:17 PM, David Kerr wrote: > Howdy all, > > I have some apps that are connecting to my DB via direct JDBC and I'd like to > pool their connections. > > I've been looking at poolers for a while, and pgbouncer and pgpool-ii seem to > be some of the most popular, so > i've started with those. > > > I'm setting up pgbouncer, and i've hit a bit of a snag. Hopefully someone can > tell me if pgbouncer or pgpool are > capable of this (and if so, how to do it) or alternatly a pooler that can... > > What I'd like to be able to do is this (not using pooler syntax, this is just > a high level of what i want to achive) > > Say i set max pool size = 10 connections. and max # of pools = 5. > > That means that i should have 5 connections to my database covering 50 > connections total. > > I can't really seem to make that work with pgbouncer without naming the pools > separetly. (pool1 = dbname = a, pool2 = dbname =a) > which means my app is tied to a pool (or has to specifically code to rotate > pools...) which is not really desireable. I have a lot of respect for pgbouncer (haven't used pgpool). One possible way to do what you're thinking is to rotate the pool on user. In bouncer each database role gets its own pool (if you understand how transaction mode works you can see why it has to work this way). Not sure if this is helpful. Why are you trying to separate the pools like that? merlin -- 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] ERROR: cache lookup failed for relation X
Hi Tom, Thank you for your Prompt reply.. Am getting this error only in the application level that too with multiple sessions. By getting that error am assuming that the table has got dropped in some other session. I understood from here is that its not a serious problem to catalog Corruption. Regards Raghavendra On Sat, Apr 3, 2010 at 10:35 PM, Tom Lane wrote: > raghavendra t writes: > > I am facing the error "cache lookup failed for relation X" in > Postgres-8.4.2 > > [ when dropping the same table concurrently in two sessions ] > > Could plese tell me, why this is generated and what is the cause. > > From the perspective of session B, the table disappeared after it had > already found the table in the catalogs and obtained a lock on it. > This is not readily distinguishable from a serious problem such as > catalog corruption. While we could play dumb and just issue a > "table does not exist" message as though we'd never found it in the > catalog at all, that behavior could mask real problems, so it seems > better to not try to hide that something unusual happened. > > In general I would say that an application that is trying to do this > has got its own problems anyway --- how do you know which version of > the table you're dropping, and that that's the right thing? > >regards, tom lane >
Re: [GENERAL] ERROR: cache lookup failed for relation X
> > Hi Justin, Thank you for your reply.. > In the future please don't cross post to multiple lists. Appoligies for it... Regards Raghavendra On Sat, Apr 3, 2010 at 10:34 PM, jus...@magwerks.com wrote: > Because You dropped/deleted the table cache in Session A. > > The simplest way to look at it is Session B was lock out when the Drop > table command was issued from Session A. Now when session B finally got its > chance to drop/delete the table it was already gone . > > What kind error were you expecting from Postgresql to Return when it can't > find the table??? > > In the future please don't cross post to multiple lists. > > Message from raghavendra t > at 04-03-2010 10:08:11 > PM -- > > > step 6 - Issue the commit in Session A > > postgres=# commit; > > Step -7 now we can the see the error in the session B > > ERROR: cache lookup failed for relation X > Could plese tell me, why this is generated and what is the cause. > > > Thanks in advance > > Regards > Raghavendra > > All legitimate Magwerks Corporation quotations are sent in a .PDF file > attachment with a unique ID number generated by our proprietary quotation > system. Quotations received via any other form of communication will not be > honored. > > CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain > legally privileged, confidential or other information proprietary to > Magwerks Corporation and is intended solely for the use of the individual to > whom it addresses. If the reader of this e-mail is not the intended > recipient or authorized agent, the reader is hereby notified that any > unauthorized viewing, dissemination, distribution or copying of this e-mail > is strictly prohibited. If you have received this e-mail in error, please > notify the sender by replying to this message and destroy all occurrences of > this e-mail immediately. > Thank you. > >
