[GENERAL] What type of index should I use?
I have a varying(200) text column that I need to be able to do lookups on very fast (WHERE col = 'foo') - The data in this table will pretty much never change (when it does I have to restart the entire server, so updates of any sort are extremely rare). I estimate the table will hold around 5,000 rows, never any more. Am I better off using a btree index or should I use GiN? From what I've read, GiN is extremely fast, but very slow for updates. However, it was unclear to me if they're only fast when doing fulltext searches, or if they're just fast period. Thanks! Mike -- 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] Inserting additional data into pg_statistics
--- Hi Tom, Modifying the pg_statistics is not a good idea for most practical purposes. The modification, however, becomes a necessity to implement automatic physical design techniques. We are developing an automatic physical designer for Postgres. The designer will add features that most commercial systems provide right now, such as automatically selecting indexes for queries. My colleagues recently demonstrated a prototype version of the system at SIGMOD, and the demo description can be found at http://www.cs.cmu.edu/~ddash/parinda-sigmod.pdf We want to extend the system by doing the physical design outside the production database, and hence need to replicate the pg_statistics of the production database in another standing database. This is the reason, we would like to move the pg_statistics across the database, and both direct sql/pg_dump-restore mechanisms fail us. -Dash Debabrata Tom Lane wrote: Teodor Macicas teodor.maci...@epfl.ch writes: Why I can't ? And for my purpose is not a bad idea. I mean, I have to do this and somehow I should find a solution. In order to use ANALYZE I need the same data on 2nd machine, but the data is quite large and the only information I need are the statistics from pg_statistic. Er, if you haven't got the data on the second machine, then you *don't* need or want that stuff in its pg_statistic. It won't do you any good to have incorrect information in there. 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] pgpool
So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] pgpool
Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 = * pgpool has to be configured in that way that it connects to the database on port 5434 * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] disable password prompt - command line
Hi List, I need a small help regarding the password options available with PGSQL, I found POSTGRE SQL has -W and -password options available which is prompting for the password. But I want to take the password thru command line argument and keep the password in a variable. Is there any option which will help me to take the password and used in the below way so that it will connect to the DB without asking password again. psql -h %server% -U %username% -d %database% -p %port% --file File.sql xx Please suggest if there is any option to use the %password% variable using some of the options avail with PSQL. Thanks, Ravi Katkar
Re: [GENERAL] disable password prompt - command line
In response to Ravi Katkar : Hi List, I need a small help regarding the password options available with PGSQL, I found POSTGRE SQL has ?W and ?password options available which is prompting for the password. But I want to take the password thru command line argument and keep the password in a variable. Is there any option which will help me to take the password and used in the below way so that it will connect to the DB without asking password again. http://www.postgresql.org/docs/current/static/libpq-pgpass.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Inserting additional data into pg_statistics
2010/6/21 Teodor Macicas teodor.maci...@epfl.ch: --- Hi Tom, Modifying the pg_statistics is not a good idea for most practical purposes. The modification, however, becomes a necessity to implement automatic physical design techniques. We are developing an automatic physical designer for Postgres. The designer will add features that most commercial systems provide right now, such as automatically selecting indexes for queries. My colleagues recently demonstrated a prototype version of the system at SIGMOD, and the demo description can be found at http://www.cs.cmu.edu/~ddash/parinda-sigmod.pdf We want to extend the system by doing the physical design outside the production database, and hence need to replicate the pg_statistics of the production database in another standing database. This is the reason, we would like to move the pg_statistics across the database, and both direct sql/pg_dump-restore mechanisms fail us. If not already there, watch how to hook the statistics when they are used/requested in the query planner, not modifying system catalog. So you can provide false stats to the plannerstats that you can store in another table, not in the pg_catalog. It looks to me that you are doing something similar to that : http://www.pgcon.org/2010/schedule/events/233.en.html (your REF 7) but with the 'offline' option, right ? May I suggest you to read on 'segment exclusion' idea in the postgresql wiki ? http://wiki.postgresql.org/wiki/Segment_Exclusion sometime I am pretty sure the hooks for stats are not there, but ... if you provide a (good) way to hook them without performance impact when the hook is not used, that should be good for more than only your project. -Dash Debabrata Tom Lane wrote: Teodor Macicas teodor.maci...@epfl.ch writes: Why I can't ? And for my purpose is not a bad idea. I mean, I have to do this and somehow I should find a solution. In order to use ANALYZE I need the same data on 2nd machine, but the data is quite large and the only information I need are the statistics from pg_statistic. Er, if you haven't got the data on the second machine, then you *don't* need or want that stuff in its pg_statistic. It won't do you any good to have incorrect information in there. 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 -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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] pgpool
Gerd Koenig wrote: Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 = * pgpool has to be configured in that way that it connects to the database on port 5434 * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] pgpool
Gerd Koenig wrote: Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 = * pgpool has to be configured in that way that it connects to the database on port 5434 What parameter in the config file is this? I can't seem to locate it? * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] pgpool
Geoffrey wrote: Gerd Koenig wrote: Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 = * pgpool has to be configured in that way that it connects to the database on port 5434 What parameter in the config file is this? I can't seem to locate it? I mis-read the docs, figured this out, thanks. * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] pgpool
So I've got the following: port = . . backend_hostname0 = 'localhost' backend_port0 = 5434 backend_weight0 = 1 backend_data_directory0 = '/data/pgsql/master' backend_hostname1 = 'localhost' backend_port1 = 5435 backend_weight1 = 1 backend_data_directory1 = '/data/pgsql/mwv' In my pgpool.conf file and I've restarted the pgpool processes. I can connect to the first entry as follows: psql -p master But if I attempt to connect to the second postmaster as follows: psql -p mwv I can not connect. What am I missing? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] Inserting additional data into pg_statistics
Teodor Macicas teodor.maci...@epfl.ch writes: Modifying the pg_statistics is not a good idea for most practical purposes. That's what I've been telling you. We want to extend the system by doing the physical design outside the production database, and hence need to replicate the pg_statistics of the production database in another standing database. Well, leaving aside the question of whether that's actually anywhere near useful enough to justify the work, I'd *still* not support putting the information into the second database's pg_statistic. pg_statistic should contain the truth for that database's own tables. Seems like what you need here is a second table along the lines of pg_hypothetical_statistic, and then your planner hacks can include the knowledge to look there instead of pg_statistic when doing hypothetical planning. Not that that's going to solve your immediate problem: there just isn't any way at the SQL level to insert data into pg_statistic's anyarray columns. You're going to need some specialized C function that inserts the data, hopefully only after validating that the actual array type matches the column that the stats are alleged to be for. 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] Inserting additional data into pg_statistics
On Monday 21 June 2010 7:23:06 am Tom Lane wrote: Teodor Macicas teodor.maci...@epfl.ch writes: Modifying the pg_statistics is not a good idea for most practical purposes. That's what I've been telling you. We want to extend the system by doing the physical design outside the production database, and hence need to replicate the pg_statistics of the production database in another standing database. Well, leaving aside the question of whether that's actually anywhere near useful enough to justify the work, I'd *still* not support putting the information into the second database's pg_statistic. pg_statistic should contain the truth for that database's own tables. Seems like what you need here is a second table along the lines of pg_hypothetical_statistic, and then your planner hacks can include the knowledge to look there instead of pg_statistic when doing hypothetical planning. Not that that's going to solve your immediate problem: there just isn't any way at the SQL level to insert data into pg_statistic's anyarray columns. You're going to need some specialized C function that inserts the data, hopefully only after validating that the actual array type matches the column that the stats are alleged to be for. regards, tom lane Another idea that just came to mind is to use something like dblink http://www.postgresql.org/docs/current/static/dblink.html or dbi-link http://pgfoundry.org/projects/dbi-link to see the information from the production db in the second db. -- Adrian Klaver adrian.kla...@gmail.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] pgpool
Le 21/06/2010 15:52, Geoffrey a écrit : So I've got the following: port = . . backend_hostname0 = 'localhost' backend_port0 = 5434 backend_weight0 = 1 backend_data_directory0 = '/data/pgsql/master' backend_hostname1 = 'localhost' backend_port1 = 5435 backend_weight1 = 1 backend_data_directory1 = '/data/pgsql/mwv' In my pgpool.conf file and I've restarted the pgpool processes. I can connect to the first entry as follows: psql -p master But if I attempt to connect to the second postmaster as follows: psql -p mwv I can not connect. What am I missing? You can't. backend_hostnamen, with n greater then 0, is only used in replication, and load balancing mode. If you want to be able to connect on various databases from various servers, pgBouncer is what you're looking for. -- 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] A thought about other open source projects
Sim Zacks wrote: database agnostic code is theoretically a great idea. However, you lose most of the advantages of the chosen database engine. For example, if you support an engine that does not support relational integrity you cannot use delete cascades. The most efficient way is to have a separate backend module per database (or db version) supported. The quickest way is to write code that will work on any db but won't take advantage of db-specific features. David Goodenough wrote: This is what I am trying to encourage. I am asking about the best way to encourage it. You want to encourage the use of databases that don't support relational integrity? Really? I think that is a simply terrible idea. -- 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] A thought about other open source projects
David Goodenough wrote: I don't support anyone has written a how to write database agnostic code guide? That way its not a matter of porting, more a matter of starting off right. There is no real way to write database[-]agnostic SQL, although of course middleware code can and should be. SQL dialects cannot even agree on simple things like the syntax for VARCHAR (VARCHAR2 in Oracle) or the semantics of TIMESTAMP, or what is legal in a SELECT. As Sim Zacks said, you lose most of the advantages of the chosen database engine if you write to the lowest common denominator, particularly as such an LCD is nonexistent. -- 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] A thought about other open source projects
On Monday 21 June 2010, Lew wrote: Sim Zacks wrote: database agnostic code is theoretically a great idea. However, you lose most of the advantages of the chosen database engine. For example, if you support an engine that does not support relational integrity you cannot use delete cascades. The most efficient way is to have a separate backend module per database (or db version) supported. The quickest way is to write code that will work on any db but won't take advantage of db-specific features. David Goodenough wrote: This is what I am trying to encourage. I am asking about the best way to encourage it. You want to encourage the use of databases that don't support relational integrity? no, I want to encourage The quickest way is to write code that will work on any db but won't take advantage of db-specific features. David Really? I think that is a simply terrible idea. -- 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] A thought about other open source projects
On Mon, Jun 21, 2010 at 08:35:02AM -0400, Lew wrote: David Goodenough wrote: I don't support anyone has written a how to write database agnostic code guide? That way its not a matter of porting, more a matter of starting off right. There is no real way to write database[-]agnostic SQL, although of course middleware code can and should be. Database-agnostic middleware is not a practical or desirable goal for the same reason that database-agnostic SQL isn't. The original reasoning behind the radical experiment of database-agnostic was an attempt to defend against the depredations of vendors of proprietary RDBMSs, who tended to use strong-arm tactics any time they felt they could get away with it. As a strategy, database-agnostic has failed because the only two (combinable) ways to implement it are enormously expensive even to create, and super-linearly expensive to maintain. I've covered these below: http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html Fortunately, another strategy whose effect is to defend against the above-mentioned strong-arm tactics--making a wide selection of non-proprietary RDBMSs--has succeeded. Just pick an RDBMS and max out its capabilities. Cheers, David. -- David Fetter da...@fetter.org 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] A thought about other open source projects
then you want your code to call stub functions (with DBSpecific stack
parameters)
Insert
Update
Delete(-with-cascade)
Select
I ran into a problem recently where i wanted to LOCK table MySQL which of
course is a no-op in MySQL so I carried the driver string as a stack param e.g.
public int Lock(String driver_string_stack_param)
if(driver_string_stack_param.equalsIgnoreCase(com.mysql.jdbc.Driver) ; //noop
else{
// do Lock Logic
}
hth
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
From: david.goodeno...@btconnect.com
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] A thought about other open source projects
Date: Mon, 21 Jun 2010 16:14:10 +0100
On Monday 21 June 2010, Lew wrote:
Sim Zacks wrote:
database agnostic code is theoretically a great idea. However, you
lose
most of the advantages of the chosen database engine. For
example, if
you support an engine that does not support relational integrity you
cannot use delete cascades.
The most efficient way is to have a separate backend module per
database
(or db version) supported. The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.
David Goodenough wrote:
This is what I am trying to encourage. I am asking about the best
way to encourage it.
You want to encourage the use of databases that don't support
relational
integrity?
no, I want to encourage The quickest way is to write code that will
work on any db but won't take advantage of db-specific features.
David
Really?
I think that is a simply terrible idea.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
_
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccountocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4
Re: [GENERAL] A thought about other open source projects
On Mon, Jun 21, 2010 at 04:14:10PM +0100, David Goodenough wrote: On Monday 21 June 2010, Lew wrote: Sim Zacks wrote: database agnostic code is theoretically a great idea. However, you lose most of the advantages of the chosen database engine. For example, if you support an engine that does not support relational integrity you cannot use delete cascades. The most efficient way is to have a separate backend module per database (or db version) supported. The quickest way is to write code that will work on any db but won't take advantage of db-specific features. David Goodenough wrote: This is what I am trying to encourage. I am asking about the best way to encourage it. You want to encourage the use of databases that don't support relational integrity? no, I want to encourage The quickest way is to write code that will work on any db but won't take advantage of db-specific features. As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Cheers, David. -- David Fetter da...@fetter.org 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] pgpool
Guillaume Lelarge wrote: Le 21/06/2010 15:52, Geoffrey a écrit : So I've got the following: port = . . backend_hostname0 = 'localhost' backend_port0 = 5434 backend_weight0 = 1 backend_data_directory0 = '/data/pgsql/master' backend_hostname1 = 'localhost' backend_port1 = 5435 backend_weight1 = 1 backend_data_directory1 = '/data/pgsql/mwv' In my pgpool.conf file and I've restarted the pgpool processes. I can connect to the first entry as follows: psql -p master But if I attempt to connect to the second postmaster as follows: psql -p mwv I can not connect. What am I missing? You can't. backend_hostnamen, with n greater then 0, is only used in replication, and load balancing mode. If you want to be able to connect on various databases from various servers, pgBouncer is what you're looking for. Ouch, okay, thanks for the clarification. I simply did not pick up on that in the docs. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] A thought about other open source projects
As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. If it's a simple web content management system, the possibility of having 1 db being supported is better than if you need a transactional real time securities trading system, it's a lot less likely to work on 1 db. -- 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] Optimizer: ranges and partial indices? Or use partitioning?
I have the same table as yours with potential to grow over 50 billion of records once operational. But our hardware is currently very limited (8GB RAM). I concur with Tom Lane about the fact that partial indexes aren't really an option, but what about partitioning? I read from the Postgres docs that The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server. http://www.postgresql.org/docs/current/static/ddl-partitioning.html Now, a table with 500M records would exceed our RAM, so I wonder what impact a table of 50G would have on simple lookup performance (i.e. source = fixed, timestamp = range), taking into account that a global index would exceed our RAM on some 1G records. Did anyone do some testing? Is partitioning a viable option in such scenario? Adrian von Bidder avbid...@fortytwo.ch wrote in message news:201003020849.19...@fortytwo.ch... -- 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] pgpool
Guillaume Lelarge wrote: If you want to be able to connect on various databases from various servers, pgBouncer is what you're looking for. It does not appear that pgbouncer will 'limit exceeding connections' as does pgpool. So if I have a pool of 20 connections and 20 connections are used, what happens to the 21st connection attempt? Is it rejected or put into a queue to wait for the next available connection? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] A thought about other open source projects
Scott Marlowe wrote: As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. Or what sort of performance requirements are tied to that app. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- 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] A thought about other open source projects
On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote: Scott Marlowe wrote: As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. Or what sort of performance requirements are tied to that app. It's not performance requirements that tend to tank such projects, but the amount of maintenance involved. Extending the app gets quadratically painful. Cheers, David. -- David Fetter da...@fetter.org 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] pgpool
On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. -- 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 force select to return exactly one row
Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? 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] How to force select to return exactly one row
Try wrapping the entire statement in a COALESCE((statement), DEFAULT_VALUE); -m 2010/6/21 Andrus kobrule...@hot.ee Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? 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] High Availability with Postgres
John R Pierce pie...@hogranch.com writes: Two DB servers will be using a common external storage (with raid). This is also one of the only postgres HA configurations that won't lose /any/ committed transactions on a failure. Most all PITR/WAL replication/Slony/etc configs, the standby storage runs several seconds behind realtime. I'm not clear on what error case it protects against, though. Either the data is ok and a single PostgreSQL system will restart fine, or the data isn't and you're hosed the same with or without the second system. What's left is hardware failure that didn't compromise the data. I didn't see much hardware failure yet, granted, but I'm yet to see a motherboard, some RAM or a RAID controller failing in a way that leaves behind data you can trust. So my question would be, what case do you handle better with a shared external storage compared to shared nothing servers with some sort of replication (including WAL shipping)? Regards, -- dim -- 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 force select to return exactly one row
Martin, Thank you. SELECT statement returns lot of columns. I tried select coalesce( (select 1,2 ), null); but got ERROR: subquery must return only one column How to use your suggestion if select returns lot of columns ? Andrus. - Original Message - From: Martin To: Andrus Cc: pgsql-general@postgresql.org Sent: Monday, June 21, 2010 10:14 PM Subject: Re: [GENERAL] How to force select to return exactly one row Try wrapping the entire statement in a COALESCE((statement), DEFAULT_VALUE); -m 2010/6/21 Andrus kobrule...@hot.ee Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Andrus Moor OÜ Eetasoft Akadeemia 21-G302 Tallinn 12618 www.eetasoft.ee tel. 6654214, 6654215
Re: [GENERAL] High Availability with Postgres
On 06/21/10 12:23 PM, Dimitri Fontaine wrote: John R Piercepie...@hogranch.com writes: Two DB servers will be using a common external storage (with raid). This is also one of the only postgres HA configurations that won't lose /any/ committed transactions on a failure. Most all PITR/WAL replication/Slony/etc configs, the standby storage runs several seconds behind realtime. I'm not clear on what error case it protects against, though. Either the data is ok and a single PostgreSQL system will restart fine, or the data isn't and you're hosed the same with or without the second system. What's left is hardware failure that didn't compromise the data. I didn't see much hardware failure yet, granted, but I'm yet to see a motherboard, some RAM or a RAID controller failing in a way that leaves behind data you can trust. in most of the HA clusters I've seen, the raid controllers are in the SAN, not in the hosts, and they have their own failover, with shared write cache, also extensive use of ECC so things like double-bit memory errors are detected and treated as a failure. the sorts of high end SANs used in these kinds of systems have 5-9's reliability, through extensive use of redundancy, dual port disks, fully redundant everything, mirrored caches, etc. ditto, the servers used in these sorts of clusters have ECC memory, so memory failure should be detected rather than passed on blindly in the form of corrupted data. Server grade CPUs, especially the RISC ones, have extensive ECC internally on their caches, data busses, etc, so any failure there is detected rather than allowed to corrupt data. failure modes can include things like failing fans (which will be detected, resulting in a server shutdown if too many fail), power supply failure (redundant PSUs, but I've seen the power combining circuitry fail). Any of these sorts of failures will result in a failover without corrupting the data. and of course, intentional planned failovers to do OS maintenance... you patch the standby system, fail over to it and verify its good, then patch the other system. We had a large HA system at an overseas site fail over once due to flooding in the primary computer room caused by a sprinkler system failure upstairs. The SAN was mirrored to a SAN in the 2nd DC (fiber inteconnected) and the backup server was also in the second DC across campus, so it all failed over gracefully. This particular system was large Sun hardware and big EMC storage, and it was running Oracle rather than Postgres. We've had several big UPS failures at various sites, too, ditto HVAC, over a 15 year period. -- 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] pgpool
John R Pierce wrote: On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. Can this be done? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] pgpool
On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote: John R Pierce wrote: On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. Can this be done? Yes -- 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] pgpool
Scott Marlowe wrote: On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote: John R Pierce wrote: On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. Can this be done? Yes Can you point me to any docs that explain how this is done? As there is only one pgpool.conf file, I'm wondering how to go about that process. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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 force select to return exactly one row
Andrus kobrule...@hot.ee wrote:
Autogenerated select statement contains 0 .. n left joins:
SELECT somecolumns
FROM ko
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey='someprimarykeyvalue';
This select can return only 0 or 1 rows depending if ko row with primary key
'someprimarykeyvalue' exists or not.
Problem:
if there is no searched primary key row in ko database, select should also
return empty row.
To get this result I added right join:
SELECT somecolumns
FROM ko
RIGHT JOIN (SELECT 1) _forceonerow ON true
LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
...
LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';
but it still does not return row if primary key row 'someprimarykeyvalue'
does not exist.
How to force this statement to return one row always ?
It's a bit difficult to decipher what you're looking for
(what do you mean by empty row?), but you may want to try
something along the lines of:
| SELECT v.primarykey, ko.somecolumns
| FROM (VALUES ('someprimarykeyvalue')) AS v (primarykey)
| LEFT JOIN ko ON v.primarykey = ko.primarykey
| LEFT JOIN t1 ON t1.primarykey = ko.t1foreignkey
| [...]
| LEFT JOIN tn ON tn.primarykey = ko.tnforeignkey;
Whether that suits your needs depends very much on the data
structure and the tools you use.
Tim
--
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] A thought about other open source projects
On Mon, 21 Jun 2010 11:27:20 -0700 David Fetter da...@fetter.org wrote: On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote: Scott Marlowe wrote: As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. Or what sort of performance requirements are tied to that app. It's not performance requirements that tend to tank such projects, but the amount of maintenance involved. Extending the app gets quadratically painful. I perfectly agree. But maybe the number of hackers involved in a project could grow faster if the project serves more purposes. Reuse lower maintenance costs too. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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 force select to return exactly one row
Ah yes sorry I missed the multi-columns. My way doesn't work for that. If Tim's suggestion doesn't work for you, you could try a union... it's fairly nasty and you will always have your fake row in the result. Also I too am confused by empty row. Are you trying to loop through the results in code and it fails if there are no rows at all? Or some other equally odd thing? =) Anyway here is an example UNION that I think would work (but note, this row will always be included even when your statement returns something, so it might not work for you). (YOUR SELECT HERE) UNION (SELECT '','',1,1,perfectly_matched_datatype_cols_here); --those first couple are just examples Mind you, I think this is nasty and would highly suggest taking another look at the code that is using this statement to see if you can deal more gracefully with an empty resultset. hope this helps, -m On Mon, Jun 21, 2010 at 12:32 PM, Andrus kobrule...@hot.ee wrote: Martin, Thank you. SELECT statement returns lot of columns. I tried select coalesce( (select 1,2 ), null); but got ERROR: subquery must return only one column How to use your suggestion if select returns lot of columns ? Andrus. - Original Message - *From:* Martin mgo...@gmail.com *To:* Andrus kobrule...@hot.ee *Cc:* pgsql-general@postgresql.org *Sent:* Monday, June 21, 2010 10:14 PM *Subject:* Re: [GENERAL] How to force select to return exactly one row Try wrapping the entire statement in a COALESCE((statement), DEFAULT_VALUE); -m 2010/6/21 Andrus kobrule...@hot.ee Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Andrus Moor OÜ Eetasoft Akadeemia 21-G302 Tallinn 12618 www.eetasoft.ee tel. 6654214, 6654215
Re: [GENERAL] pgpool
On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey li...@serioustechnology.com wrote: Scott Marlowe wrote: On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote: John R Pierce wrote: On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. Can this be done? Yes Can you point me to any docs that explain how this is done? As there is only one pgpool.conf file, I'm wondering how to go about that process. sudo apt-get install pgpool2 (password, yes, wait...) pgpool --help Usage: pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -n ] [ -d ] pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -m SHUTDOWN-MODE ] stop pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload Common options: -a HBA_CONFIG_FILE Sets the path to the pool_hba.conf configuration file (default: /etc/pool_hba.conf) -f CONFIG_FILE Sets the path to the pgpool.conf configuration file (default: /etc/pgpool.conf) -F PCP_CONFIG_FILE Sets the path to the pcp.conf configuration file (default: /etc/pcp.conf) -h Prints this help Looks like there's a -f and -F option to use various files. Admittedly, you might not be starting it up with /etc/init.d/pgpool2 each time now. But the basics are there. -- 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] pgpool
Scott Marlowe wrote: On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey li...@serioustechnology.com wrote: Scott Marlowe wrote: On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey li...@serioustechnology.com wrote: John R Pierce wrote: On 06/21/10 5:37 AM, Geoffrey wrote: So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools. Can this be done? Yes Can you point me to any docs that explain how this is done? As there is only one pgpool.conf file, I'm wondering how to go about that process. sudo apt-get install pgpool2 (password, yes, wait...) pgpool --help Usage: pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -n ] [ -d ] pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] [ -m SHUTDOWN-MODE ] stop pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload Common options: -a HBA_CONFIG_FILE Sets the path to the pool_hba.conf configuration file (default: /etc/pool_hba.conf) -f CONFIG_FILE Sets the path to the pgpool.conf configuration file (default: /etc/pgpool.conf) -F PCP_CONFIG_FILE Sets the path to the pcp.conf configuration file (default: /etc/pcp.conf) -h Prints this help Looks like there's a -f and -F option to use various files. Admittedly, you might not be starting it up with /etc/init.d/pgpool2 each time now. But the basics are there. Thanks muchly, that should give me a good kick start. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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 force select to return exactly one row
Use a case staement to test for a null output, return whatever you want in the event of it being null, else the actual value: from the top of my head, something like: SELECT case when (select somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue') not null then (select somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue') else 0 end It does require the query to be run twice, so does have extra overhead. You could wrap a function around this to get store the result test that, then having stored it you can use it for the output value without a second query. All depends on how much overhead there is in teh query. HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Andrus 06/22/10 10:12 AM Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water Atmospheric Research Ltd.
Re: [GENERAL] How to force select to return exactly one row
How about: SELECT * from ( SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue' UNION ALL SELECT default_value ) LIMIT 1; -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrus Sent: Tuesday, 22 June 2010 5:08 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How to force select to return exactly one row Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- 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] How to force select to return exactly one row
Brett Mc Bride brett.mcbr...@deakin.edu.au wrote: How about: SELECT * from ( SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue' UNION ALL SELECT default_value ) LIMIT 1; [...] ... with a proper ORDER BY clause. Tim -- 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 force select to return exactly one row
My understanding of UNION ALL is that it won't sort the rows...? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tim Landscheidt Sent: Tuesday, 22 June 2010 9:41 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to force select to return exactly one row Brett Mc Bride brett.mcbr...@deakin.edu.au wrote: How about: SELECT * from ( SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue' UNION ALL SELECT default_value ) LIMIT 1; [...] ... with a proper ORDER BY clause. Tim -- 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] How to force select to return exactly one row
Brett Mc Bride brett.mcbr...@deakin.edu.au wrote: My understanding of UNION ALL is that it won't sort the rows...? [...] It doesn't, but that's not promised for every data set, ev- ery PostgreSQL version, every phase of the moon. To quote URI:http://www.postgresql.org/docs/8.4/interactive/queries-union.html: | UNION effectively appends the result of query2 to the result | of query1 (although there is no guarantee that this is the ^^^ | order in which the rows are actually returned). Furthermore, ^ | it eliminates duplicate rows from its result, in the same | way as DISTINCT, unless UNION ALL is used. SQL deals with (unordered) sets, and therefore any use of LIMIT without ORDER BY indicates a bug waiting to bite you when you least expect it. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trying to install ODBC driver on Windows XP notebook
I have latest Postgresql installed on a notebook and now trying to install the driver which I have downloaded and run so that it is now available in the ODBC Data Source Administrator This machine has CA security suite, latest version installed and I have added a network rule that allows all applications using TCP and UDP on Port 4532 to gain access In and Out I have the correct database name and get the following message when I try to Test the connection. Could not connect to the server No Connection could be made because the target machine actively refused it. (192.168.1.7:5432) I don't know what else I can now do to get the connection. Any suggestions would be appreciated. Cheers Phil Jackson -- 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] Trying to install ODBC driver on Windows XP notebook
Assuming a TCP/IP connection, does the server machine know about your tcp/ip address in pg_hba.conf? Otherwise, what kind of connection protocol are you using? I promise, no Lakers jokes. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Phil Jackson Sent: Tuesday, June 22, 2010 1:20 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Trying to install ODBC driver on Windows XP notebook I have latest Postgresql installed on a notebook and now trying to install the driver which I have downloaded and run so that it is now available in the ODBC Data Source Administrator This machine has CA security suite, latest version installed and I have added a network rule that allows all applications using TCP and UDP on Port 4532 to gain access In and Out I have the correct database name and get the following message when I try to Test the connection. Could not connect to the server No Connection could be made because the target machine actively refused it. (192.168.1.7:5432) I don't know what else I can now do to get the connection. Any suggestions would be appreciated. Cheers Phil Jackson -- 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] Unable to determine what has a particular OID
Tom Lane wrote: Doing pg_dump would expose the mistake if you'd removed an actual table's files. But I'm not sure it would expose it if you removed an index ... Right, but in theory if you screwed up and accidentally deleted a file holding an index, you could recover from that in the possibly distant future by rebuilding it, with some pain but no expected loss. Whereas if you deleted some data by removing a file, you really want to know that's what you did immediately, so you can put it back before you forget where it all was at. Sometimes people who have fully setup replication for backup purposes ask me if they should continue saving pg_dump output somewhere. I think it's reasonable to generate a dump using it periodically whether or not you intend to save the result permanently, just as a paranoid sanity check that you can still read everything. I don't trust disks and filesystems that much. (If you're reading this and feel the need to write a pro-ZFS essay at this point, consider yourself trolled) -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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 RowType Declaration with Table Domains
Hi all,
I have the following (very simplified) scenario:
CREATE DOMAIN orderstatus AS text NOT NULL DEFAULT 'Open';
ALTER DOMAIN orderstatus ADD CONSTRAINT orderstatus_valid
CHECK (VALUE IN ('Open', 'Shipped', Cancelled'));
CREATE TABLE orders ( orderno serial
, status orderstatus
, PRIMARY KEY (orderno));
INSERT INTO orders (status) VALUES('Open'),('Open'),('Shipped');
CREATE OR REPLACE FUNCTION getOrder(int4)
RETURNS
orders
AS
$$DECLARE
orderno_in alias for $1;
saleorder orders%rowtype;
BEGIN
SELECT INTO saleorder * FROM orders WHERE orderno = orderno_in;
RETURN saleorder;
END;
$$
VOLATILE
LANGUAGE 'plpgsql';
test1=# select * from getorder(3);
ERROR: domain orderstatus does not allow null values
CONTEXT: PL/pgSQL function getorder line 4 during statement block local
variable initialization
Is there a way around this?
Thanks,
George
--
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 Availability with Postgres
John R Pierce wrote: the commercial cluster software vendors insist on using dedicated connections for the heartbeat messages between the cluster members and insist on having fencing capabilities (for instance, disabling the fiber switch port of the formerly active server and enabling the port for the to-be-activated server). with linux-ha and heartbeat, you're on your own. This is worth highlighting. As John points out, it's straighforward to build a shared storage implementation using PostgreSQL and either one of the commercial clustering systems or using Linux-HA. And until PostgreSQL gets fully synchronous replication, it's a viable alternate solution for must not lose a transaction deployments when the storage used is much more reliable than the nodes. The hard part of shared storage failover is always solving the shoot the other node in the head problem, to keep a down node from coming back once it's no longer the active one. In order to do that well, you really need to lock the now unavailable node from accessing the storage at the hardware level--fencing--with disabling its storage port being one way to handle that. Figure out how you're going to do that reliably in a way that's integrated into a proper cluster manager, and there's no reason you can't do this with PostgreSQL. There's a description of the fencing options for Linux-HA at http://www.clusterlabs.org/doc/crm_fencing.html ; the cheap way to solve this problem is to have a UPS that disables the power going to the shot node. Once that's done, you can then safely failover the shared storage to another system. At that point, you can probably even turn back on the power, presuming that the now rebooted system will be able to regain access to the storage during a fresh system start. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] No PL/PHP ? Any reason?
Is there any technical obstacle to anyone creating PL/PHP? I am cruious as to why it doesn't alreay exist. I mean, I love my Tcl support, and I know this is part of PG's legacy... but Tcl and no PHP? I figure there's a tech reason for this - the demand must be there! No? -- 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] No PL/PHP ? Any reason?
On Tue, Jun 22, 2010 at 1:28 PM, Carlo Stonebanks stonec.regis...@sympatico.ca wrote: Is there any technical obstacle to anyone creating PL/PHP? I am cruious as to why it doesn't alreay exist. I mean, I love my Tcl support, and I know this is part of PG's legacy... but Tcl and no PHP? I figure there's a tech reason for this - the demand must be there! No? There is one already: https://public.commandprompt.com/projects/plphp -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [GENERAL] No PL/PHP ? Any reason?
On Mon, Jun 21, 2010 at 9:55 PM, Devrim GUNDUZ dev...@gunduz.org wrote: 22.Haz.2010 tarihinde 06:43 saatinde, Shoaib Mir shoaib...@gmail.com şunları yazdı: On Tue, Jun 22, 2010 at 1:28 PM, Carlo Stonebanks stonec.regis...@sympatico.ca wrote: Is there any technical obstacle to anyone creating PL/PHP? I am cruious as to why it doesn't alreay exist. I mean, I love my Tcl support, and I know this is part of PG's legacy... but Tcl and no PHP? I figure there's a tech reason for this - the demand must be there! No? There is one already: https://public.commandprompt.com/projects/plphp IIRC, it does not compile against newer PostgreSQL releases and it is not under development right now. I recall talking to the guys at command prompt and apparently something in the php runtime makes it unsuitable for pl deployment. Been a while, I don't remember what, just that the php folks had no interest in fixing it. -- 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 Availability with Postgres
On 06/21/10 8:08 PM, Greg Smith wrote: The hard part of shared storage failover is always solving the shoot the other node in the head problem, to keep a down node from coming back once it's no longer the active one. In order to do that well, you really need to lock the now unavailable node from accessing the storage at the hardware level--fencing--with disabling its storage port being one way to handle that. Figure out how you're going to do that reliably in a way that's integrated into a proper cluster manager, and there's no reason you can't do this with PostgreSQL. In my dev-lab tests of some clusters, I used the QLogic 5600 FC switch that connects my motly collection of servers... I used RHCS for one test, it supported the qlogic via telnet... I created two zone sets in the qlogic, one for each state, with the standby host blocked from accessing the LUN, and the cluster manager used telnet to talk to the switch.I ran heartbeats over two seperate ethernets (one was the lab LAN segment, the other was a private switch i have all the servers connected to for various tests, and such). The qlogic switch also had another zoneset for all sorts of other servers and storage which wasn't affected by these clustering tests. I don't like power cycling servers, so I'd prefer not to use power switch based fencing, although I believe my blade box's management unit is supported as a power fencing device. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
