Re: [GENERAL] Very slow table
On 8 December 2011 06:28, Richard Weiss richardwe...@richardweiss.orgwrote: Hi, I am using squeryl, which is based on jdbc, and I am trying to run the following statement: delete from Entry where (id = 0) but the query never seems to finish. I have tried it in both the program I am writing, and the above statement in pgadmin3, and both cases never finish. Other queries, like delete from Entry where true complete. The table's initialization statement is: CREATE TABLE Entry ( pID bigint NOT NULL, id bigint NOT NULL, r text NOT NULL, CONSTRAINT Entry_pkey PRIMARY KEY (id), CONSTRAINT EntryFK21 FOREIGN KEY (pID) REFERENCES PP (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); ALTER TABLE Entry OWNER TO riri; CREATE INDEX idx2eda05d9 ON Entry USING btree (id); Other information: Postgres Version: PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit Thanks, Richard Weiss. Hi Richard, first of all: table is never slow - a query could be slow. Could you show us the part of application where you execute the query? Another reason could be some Postgres locking, check if this query hangs in pg_stat_activity table. Value 'true' in column 'waiting' means that the query hangs. Btw, the index idx2eda05d9 is useless, defining a primary key already creates an index. Also show us structure of the table PP, as it must be checked while deleting rows. regards Szymon -- *http://simononsoftware.com/* http://simononsoftware.com/
Re: [GENERAL] Hope for a new PostgreSQL era?
On Dec 8, 2011 1:27 PM, Chris Travers chris.trav...@gmail.com wrote: Additionally I am not entirely sure what he means by the last point. If you look at the work that NTT along with EDB has put into Postgres-XC, for example, it looks to me like the Postgres ecosystem is growing by leaps and bounds and we are approaching an era where Oracle is no longer ahead in any significant use case. While Pg is impressively capable now, I don't agree that Oracle (if DB2, MS-SQL etc) isn't ahead for any significant use case. Not on a purely technical basis anyway - once cost is considered there may be a stronger argument. Multi-tenant hosting is a weak pint for Pg for quite a few reasons, done of which appear below. It's not the only role Pg isn't a great fit for, but probably one of the more obvious. Areas in which Pg seems significantly less capable include: - multi-tenant hosting and row level security - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. - performance monitoring and diagnostics. It's way harder to find out what's causing load on a busy Pg server or report on frequent/expensive queries etc. Tooling is limited and fairly primitive. It's find, but nowhere near as powerful and easy as some if the other DBs. - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. - transparent failover and recovery back to the original master. - shared-storage clustering. Dunno if anyone still cares about this one though.
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 12:24, Craig Ringer wrote: - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. The first thing I'd like to see is user profiles - being able to set things like work_mem, synchronous_commit, etc. on per-user basis separately. - performance monitoring and diagnostics. It's way harder to find out what's causing load on a busy Pg server or report on frequent/expensive queries etc. Tooling is limited and fairly primitive. It's find, but nowhere near as powerful and easy as some if the other DBs. True. Greg Smith actually mentioned this as one of the frequently asked features in his post about two weeks ago (http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html). I've started to build my own tool and got it somehow working for my needs, and there are other tools available, but none of them is really a complete solution. Would be nice to form a dev group that would work on this. - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. -- 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] Hope for a new PostgreSQL era?
On 12/08/2011 08:53 PM, Tomas Vondra wrote: On 8 Prosinec 2011, 12:24, Craig Ringer wrote: - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. o d The first thing I'd like to see is user profilesz- being able to set things like work_mem, synchronous_commit, etc. on per-user basis separately. You can. ALTER USER username SET work_mem = '100MB'; It's not a hard cap - the user can raise/lower it however they like. The initial value can be set globally, per-user, per-database, or globally. I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. Yes, to a limited degree you can prioritise queries using nice and ionice, but it's awkward because: - All queries run as `postgres' so you can't do per-user limiting very easily - The postmaster doesn't have a way to set the nice level and ionice level when it forks a backend, nor does the backend have any way to do it later. You can use your own user-defined C functions for this, though. - Most importantly, even if you nice and ionice using C functions or manually with the cmdline utilities, you can't affect the bgwriter, nor can you affect how much data a low-priority query pushes out of cache. -- 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
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thu, Dec 08, 2011 at 01:53:45PM +0100, Tomas Vondra wrote: I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. This won't work because if you are holding a lock on something someone else needs, your low nice score is going to cause them problems. It could make things worse rather than better. (This suggestion comes up a lot, by the way, so there's been a lot of discussion of it historically.) -- Andrew Sullivan a...@crankycanuck.ca -- 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] Hope for a new PostgreSQL era?
2011/12/08 21:53, Tomas Vondra wrote: - performance monitoring and diagnostics. It's way harder to find out what's causing load on a busy Pg server or report on frequent/expensive queries etc. Tooling is limited and fairly primitive. It's find, but nowhere near as powerful and easy as some if the other DBs. True. Greg Smith actually mentioned this as one of the frequently asked features in his post about two weeks ago (http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html). I've started to build my own tool and got it somehow working for my needs, and there are other tools available, but none of them is really a complete solution. Would be nice to form a dev group that would work on this. Seems a good point. I'm trying to build a complete solution. :) Anyway, one of the reasons of such difficulties to build a complete solution is based on necessity of the support from the *entire* core code. Without the core support, a complete solution would never be built. Obtaining LWLock statistics or write I/O operations is actually pretty tough work for non-experienced PostgreSQL DBA, like me. :) For examples, I've been working on investigating PostgreSQL LWLock behaviors precisely for a few weeks, and it could not be obtained within PostgreSQL itself, therefore, I picked up SystemTap. However, SystemTap could not be used in a production system, because it often kills the target processes. :( How can I observe LWLocks in the production system? There are several tools to monitor system behaviors around operating systems, but it is far from understanding PostgreSQL behavior. And DBAs coming from other RDBMSes, in particular proprietary RDBMSes, need it, because they've already been using such facilities (or tools) in their RDBMSes. That's the reason why we need more facilities to observe inside PostgreSQL. In addition, one more reason of the difficulties is that experienced PostgreSQL DBAs (or hackers) do not need such facilities in general, because they can imagine how PostgreSQL works in such particular situation. I still think we can implement (or enhance) for those facilities if we focus on it, but I sometimes feel it's like a chicken and egg situation. Regards, -- NAGAYASU Satoshi satoshi.nagay...@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] Hope for a new PostgreSQL era?
On Wed, Dec 7, 2011 at 8:52 PM, Rodrigo E. De León Plicet rdele...@gmail.com wrote: http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/ Some of the points mentioned: - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL in some ways. (Database extensibility if nothing else.) There is simply no comparing mysql's backend programming features with those of postgres. Postgres is a development platform in a box, mysql is not. 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] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. It's called the resource manager in Oracle. You define 'resource plans', 'consumer groups', etc… and you get some sort of QoS for your queries. It's mostly about CPU resource allocation if I remember correctly (I never used it, except during training :) ) Being able of changing the backend's nice level may do something similar I guess. I don't think Oracle's resource manager solves the priority inversion due to locking in the database, but I'm not sure of 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] Fwd: postgres 9.0.4 Streaming related question ..
Thanks , it worked On Wed, Dec 7, 2011 at 12:01 PM, Ben Chobot be...@silentmedia.com wrote: On Dec 7, 2011, at 8:45 AM, akp geek wrote: Small Clarification. I have gone through the documentation. I did not find any thing related to start ans stop replication after the replication is setup 1. If I shutdown the primary 2. Shutdown the slave 3. start Primary 4. start slave Will this automatically catch up the streaming. Can you please comment? So long as you leave your recovery.conf file in place, and don't loose any needed wal files between steps 3 and 4 (or can recover them from an archive), then the answer is yes.
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] concat(NULL,NULL) returns empty string
Hi, The documentation of the function concat states: Concatenate all arguments. NULL arguments are ignored. Intuitively I would expect that concat(NULL, NULL) IS NULL but this is not the case as it equals the empty string. As no empty string was passed, the empty string seems to come from nowhere. I found this behavior not immediately clear from the documentation.. Kind regards, Ingmar Brouns
Re: [GENERAL] concat(NULL,NULL) returns empty string
Hello 2011/12/8 Ingmar Brouns swi...@gmail.com: Hi, The documentation of the function concat states: Concatenate all arguments. NULL arguments are ignored. Intuitively I would expect that concat(NULL, NULL) IS NULL but this is not the case as it equals the empty string. As no empty string was passed, the empty string seems to come from nowhere. I found this behavior not immediately clear from the documentation.. yes - it returns empty string - concat is barier to nulls it should be better documented Regards Pavel Stehule Kind regards, Ingmar Brouns -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 08, 2011 at 09:29:28AM -0600, Merlin Moncure wrote: you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. One of the central issues that Postgres has in the enterprise land is exactly this sort of answer: you have good options but they're not part of the core release. Now, we all know that this is a stupid and wrong way of thinking about it. But one cannot complain about being held to those sorts of enterprisey standards when one is having an enterprisey discussion. The original analysis (on the blog) seemed to be primarily aimed at exactly that sort of discussion, and I suspect that this is the kind of thing that was meant by the community leadership not having covered itself in stewardship glory. One of the stewardship tests, from a business-analysis point of view, is whether you're going to be able to find a reliable supply of experienced admins at all levels for your systems. Having a bunch of different, indifferently-documented projects that are all doing similar but slightly different things is, to someone looking from that point of view, a liability and not a strength. I happen to disagree, but it always seemed to me that something the Postgres community did poorly (and I count myself in that number, though less now than in the past) was understanding the hardships of the integrator and coming up with reasonably simple answers for those kinds of questions. It is not unreasonable to say that there are no simple answers here; but as unhappy as it makes me, those reasonably simple answers are necessary for some classes of users. And let's face it: companies like Oracle (and products like MySQL) are in a position to treat those sorts of answers as part of the cost of doing business, because they have revenue associated with their licenses so they can pay for coming up with those answers that way. In Postgres-land, everyone needs to charge money for those answers (i.e. be consultants), because that's the only real place to make a living. Alternatively, you can put together those answers as part of your own package; but in that case, it's not the core PostgreSQL product, but something else. In this respect, the decision of the core team a number of years ago to say, We're going to have 'integrated' replication that does x, y, and z, was the right one, despite the fact that it undermined the momentum of other interesting projects (and ones better suited to some environments). Sometimes, it's better to cut off options. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 09:29:28 -0600, Merlin Moncure mmonc...@gmail.com a écrit : On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Statistics mismatch between n_live_tup and actual row count
Hi, we're currently investigating a statistics issue on postgres. We have some tables which frequently show up with strange values for n_live_tup. If you compare those values with a count on that particular table, there is a mismatch of factor 10-30. This causes the planner to come up with very bad plans (we also have this issue on bigger table like the one below). db=# SELECT relname, n_live_tup, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname = 's' AND relname = 't'; relname | n_live_tup | last_analyze | last_autoanalyze --++---+--- t| 7252 | 2011-12-08 03:00:02.556088+01 | 2011-12-01 18:29:00.536321+01 db=# SELECT COUNT(*) FROM s.t; count --- 280 The strange thing is, if we run an ANALYZE on this table, the statistic is good. 10 minutes later it's bad again. We suspect one of our processes which might do unnecessary (i.e. blind) updates. Can this be the cause of the statistics problem? PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.6.real (Debian 4.6.2-4) 4.6.2, 64-bit The migration to 9.1 did not fix this problem, as we already have this since 8.4. I can provide all postgres configuration, but I don't see anything we changed (compared to the default config), which might be related here. Any clue appreciated here! Best regards Andy -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today. Brad. -- 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] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 14:17, Craig Ringer wrote: You can. ALTER USER username SET work_mem = '100MB'; It's not a hard cap - the user can raise/lower it however they like. The initial value can be set globally, per-user, per-database, or globally. Oh, shame on me! I wasn't aware of this. Too bad it's not possible to restrict this (changing certain config values). A simple 'before SET' hook might do the trick. I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. Yes, to a limited degree you can prioritise queries using nice and ionice, but it's awkward because: - All queries run as `postgres' so you can't do per-user limiting very easily - The postmaster doesn't have a way to set the nice level and ionice level when it forks a backend, nor does the backend have any way to do it later. You can use your own user-defined C functions for this, though. Yes, that's what I meant. - Most importantly, even if you nice and ionice using C functions or manually with the cmdline utilities, you can't affect the bgwriter, nor can you affect how much data a low-priority query pushes out of cache. IMHO bgwriter may be reasonably tuned by bgwriter_* GUC variables. The user backends are probably more interesting here. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 16:11, Marc Cousin wrote: - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. Which is exactly what pgbouncer and other connection pools are for ... - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. It's called the resource manager in Oracle. You define 'resource plans', 'consumer groups', etc… and you get some sort of QoS for your queries. It's mostly about CPU resource allocation if I remember correctly (I never used it, except during training :) ) And it's damn difficult to get it working properly ... the simpler the better here. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 17:11, Marc Cousin wrote: Le Thu, 8 Dec 2011 09:29:28 -0600, - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. True, it is not exactly the same, it's similar. I don't think PostgreSQL will ever provide 'shared backends' the way Oracle does - it would require significant code change. Most of the benefits can be achieved by using a connection pool without the added complexity. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. Yes. But if you expect that PostgreSQL will mimic Oracle architecture, then you're mistaken. It's simply a different solution with different architecture, and that means you may need to use different application design sometimes. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 16:27:56 +, Nicholson, Brad (Toronto, ON, CA) bnichol...@hp.com a écrit : On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today. Between users yes. But there is only one DB per instance in Oracle :) -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 10:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 09:29:28 -0600, Merlin Moncure mmonc...@gmail.com a écrit : On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. yeah -- maybe we could use a server side feature that could allow you to save a session state and load it up later to make life easier for connection pooled applications. however, it's not really that much work to organize most of the things you'd use for this in an application managed session instead of database managed one. regarding the enterprises won't use community supplied postgresql add ons point, this completely true in many cases. I do think pgbouncer should be seriously considered for advancement as a core feature. That said, this should be done on its own merits, not to satisfy the capricious whims of enterprises. 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] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 17:54:20 +0100, Tomas Vondra t...@fuzzy.cz a écrit : On 8 Prosinec 2011, 16:11, Marc Cousin wrote: - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. Which is exactly what pgbouncer and other connection pools are for ... Yep. But with some limitations (not that important, but they exist) as detailed in another message. I like the pgbouncer approach as it is much simpler, but it has the limitation that the real sessions aren't in the database anymore, so context is lost, etc… - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. It's called the resource manager in Oracle. You define 'resource plans', 'consumer groups', etc… and you get some sort of QoS for your queries. It's mostly about CPU resource allocation if I remember correctly (I never used it, except during training :) ) And it's damn difficult to get it working properly ... the simpler the better here. Yep, it's very hard and ugly to use. It's by the way the reason I used it only in training, not in production situations (in production, when it doesn't work, you have to debug the damn thing, not just throw it away :) ) -- 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] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 17:27, Nicholson, Brad (Toronto, ON, CA) wrote: On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today. Yes, each session has a UGA (User Global Area) memory, and this iss placed either in SGA (Shared Global Area) in case of dedicated server or PGA (Process Global Are) in case of shared server. If you use shared server, then each request might be handled by a different backend process. PostgreSQL uses dedicated architecture which means simpler code base, configuration etc. If you need something like a shared server then you can use a connection pool, but you have to handle the session state on your own (in the application). Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 18:00, Marc Cousin wrote: Le Thu, 8 Dec 2011 16:27:56 +, Nicholson, Brad (Toronto, ON, CA) bnichol...@hp.com a écrit : On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. In Oracle - can the pool share connections between DB users and/or databases on the instance? If the answer is yes to either, that is a fair bit better than what we can achieve today. Between users yes. But there is only one DB per instance in Oracle :) Because Oracle uses schemas instead of databases. One schema = one user = one database. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 18:02:51 +0100, Tomas Vondra t...@fuzzy.cz a écrit : On 8 Prosinec 2011, 17:11, Marc Cousin wrote: Le Thu, 8 Dec 2011 09:29:28 -0600, - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. True, it is not exactly the same, it's similar. I don't think PostgreSQL will ever provide 'shared backends' the way Oracle does - it would require significant code change. Most of the benefits can be achieved by using a connection pool without the added complexity. I didn't ask for it. It's just not the same, which was what I was answering to. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. Yes. But if you expect that PostgreSQL will mimic Oracle architecture, then you're mistaken. It's simply a different solution with different architecture, and that means you may need to use different application design sometimes. I just don't understand what you're getting all heated up for. I don't want PostgreSQL to mimic Oracle, and I'm only answering to Simon's question at the begining. I'm just saying that there are differences between the pgbouncer approach and the shared server approach, and benefits to the later (and to the former too by the way, mainly simplicity so less bugs, as shared servers architecture suffered from a lot of bugs). -- 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] Hope for a new PostgreSQL era?
Le Thu, 8 Dec 2011 10:56:39 -0600, Merlin Moncure mmonc...@gmail.com a écrit : On Thu, Dec 8, 2011 at 10:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 09:29:28 -0600, Merlin Moncure mmonc...@gmail.com a écrit : On Thu, Dec 8, 2011 at 9:11 AM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. yeah -- maybe we could use a server side feature that could allow you to save a session state and load it up later to make life easier for connection pooled applications. however, it's not really that much work to organize most of the things you'd use for this in an application managed session instead of database managed one. For us who can change our application code, of course. But some people can't. regarding the enterprises won't use community supplied postgresql add ons point, this completely true in many cases. I do think pgbouncer should be seriously considered for advancement as a core feature. That said, this should be done on its own merits, not to satisfy the capricious whims of enterprises. Sure. I'm not advocating this. Neither am I advocating using Oracle by the way. I'm just as pro-postgresql as anyone else. I just wanted to weigh the pros and cons of Oracle's way of doing things versus pgbouncer. And the shared server approach has its merits. -- 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] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 17:56, Merlin Moncure wrote: regarding the enterprises won't use community supplied postgresql add ons point, this completely true in many cases. I do think pgbouncer should be seriously considered for advancement as a core feature. That said, this should be done on its own merits, not to satisfy the capricious whims of enterprises. I don't think so. In my experience, the enterprises are not using PostgreSQL (or other OS software in genera) because they think there's no guarantee or support available. If there's a third party (might be a local consulting company) providing acceptable guarantees and support for PostgreSQL, it may as well provide guarantees for pgbouncer and the enterprise customer is fine. They simply want a package with guarantees, it does not matter whether it's in core or not. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 18:14, Marc Cousin wrote: It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. Yes. But if you expect that PostgreSQL will mimic Oracle architecture, then you're mistaken. It's simply a different solution with different architecture, and that means you may need to use different application design sometimes. I just don't understand what you're getting all heated up for. I don't want PostgreSQL to mimic Oracle, and I'm only answering to Simon's question at the begining. Sorry, I was not arguing with you nor heated up. It's rather a translation mistake - in my native language we often use 'you' when we actually mean 'someone.' So it should be something like If someone expects ... he's mistaken, etc. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thursday 08 December 2011 18:34:35 Tomas Vondra wrote: On 8 Prosinec 2011, 18:14, Marc Cousin wrote: It means you keep your session variables, your prepared statements, your running transaction, etc… in each individual session while having the multiplexing equivalent of a 'statement level' from pgbouncer. Yes. But if you expect that PostgreSQL will mimic Oracle architecture, then you're mistaken. It's simply a different solution with different architecture, and that means you may need to use different application design sometimes. I just don't understand what you're getting all heated up for. I don't want PostgreSQL to mimic Oracle, and I'm only answering to Simon's question at the begining. Sorry, I was not arguing with you nor heated up. It's rather a translation mistake - in my native language we often use 'you' when we actually mean 'someone.' So it should be something like If someone expects ... he's mistaken, etc. Ok, no problem, that just felt weird :) -- 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] Hope for a new PostgreSQL era?
On 8 Prosinec 2011, 14:20, Andrew Sullivan wrote: On Thu, Dec 08, 2011 at 01:53:45PM +0100, Tomas Vondra wrote: I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. This won't work because if you are holding a lock on something someone else needs, your low nice score is going to cause them problems. It could make things worse rather than better. (This suggestion comes up a lot, by the way, so there's been a lot of discussion of it historically.) I'm aware of that, but there are cases when this may actually work. For example we do have an OLTP system, but we need to build exports to other systems regularly. The export may need to read a lot of data, but I don't want to annoy the people who are using the system. So I could lower the priority for the backend generating the report. Yes, there are cases where this priority inversion makes it unusable. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database system identifier via SELECT?
Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set...
Re: [GENERAL] Hope for a new PostgreSQL era?
On 12/08/2011 03:24 AM, Craig Ringer wrote: - shared-storage clustering. Dunno if anyone still cares about this one though. This one seems to be moving into the legacy category over the next 3-5 years. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Clarification on CONTEXT: xlog redo xid assignment
Looking for some clarification on some log output. We are moving our databases to new physical machines. To do this we are taking zfs snapshots from the slaves copying the snapshots to the new machines and then restarting the new machines with a failover file. The database is able to start and I can access it, run commands against it , create pg_dumps and reload those without any errors. However I am getting the following errors in the logs at startup. Dec 7 12:40:53 postgres[28199]: [ID 748848 local2.info] [1-1] LOG: database system was interrupted while in recovery at log time 2011-12-07 00:32:49 PST Dec 7 12:40:53 postgres[28199]: [ID 748848 local2.info] [1-2] HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. Dec 7 12:40:53 postgres[28199]: [ID 748848 local2.info] [2-1] LOG: entering standby mode Dec 7 12:40:53 postgres[28199]: [ID 748848 local2.info] [3-1] LOG: restored log file 000106060057 from archive Dec 7 12:40:53 postgres[28199]: [ID 748848 local2.info] [4-1] LOG: redo starts at 606/57536038 Dec 7 12:40:55 postgres[28199]: [ID 748848 local2.info] [5-1] LOG: file pg_subtrans/8732 doesn't exist, reading as zeroes Dec 7 12:40:55 postgres[28199]: [ID 748848 local2.info] [5-2] CONTEXT: xlog redo xid assignment xtop 2268215780: subxacts: 2268215781 2268215782 2268215791 2268215792 2268215793 2268215794 2268215799 2268215800 2268215818 2268215819 2268215837 2268215838 2268215841 2268215842 2268215845 2268215846 2268215849 2268215851 2268215857 2268215858 2268215863 2268215864 2268215865 2268215866 2268215867 2268215869 2268215871 2268215872 2268215875 2268215876 2268215878 2268215879 2268215880 2268215881 2268215882 2268215883 2268215886 2268215887 2268215888 2268215889 2268215890 2268215891 2268215892 2268215893 2268215894 2268215895 2268215896 2268215897 2268215898 2268215899 2268215900 2268215901 2268215902 2268215903 2268215904 2268215905 2268215906 2268215907 2268215908 2268215909 2268215910 2268215911 2268215914 2268215915 My questions are 1. What exactly is postgres doing here CONTEXT: xlog redo xid assignment . and here LOG: file pg_subtrans/8732 doesn't exist, reading as zeroes Is it possible we have some data corruption? 2. I am also wondering if the database system was interrupted while in recovery at log time 2011-12-07 00:32:49 PST message is due to taking the snapshots while the servers are running. On launch we will shut them down prior to taking the final snapshot. Any help is appreciated. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Clarification-on-CONTEXT-xlog-redo-xid-assignment-tp5059725p5059725.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] Hope for a new PostgreSQL era?
On 12/08/11 10:14 AM, Joshua D. Drake wrote: - shared-storage clustering. Dunno if anyone still cares about this one though. This one seems to be moving into the legacy category over the next 3-5 years. um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Hope for a new PostgreSQL era?
On 12/08/2011 10:54 AM, John R Pierce wrote: On 12/08/11 10:14 AM, Joshua D. Drake wrote: - shared-storage clustering. Dunno if anyone still cares about this one though. This one seems to be moving into the legacy category over the next 3-5 years. um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] Hope for a new PostgreSQL era?
On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Hope for a new PostgreSQL era?
On 12/08/2011 11:24 AM, John R Pierce wrote: On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. Exactly. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] Clarification on CONTEXT: xlog redo xid assignment
On Thu, Dec 8, 2011 at 6:37 PM, kc5 k...@colandraengineering.com wrote: Dec 7 12:40:55 postgres[28199]: [ID 748848 local2.info] [5-1] LOG: file pg_subtrans/8732 doesn't exist, reading as zeroes Dec 7 12:40:55 postgres[28199]: [ID 748848 local2.info] [5-2] CONTEXT: xlog redo xid assignment xtop 2268215780: subxacts: 2268215781 2268215782 Looks like the bug fixed in 9.1.2 and 9.0.6 -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Database system identifier via SELECT?
Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Database system identifier via SELECT?
On 12/08/2011 12:57 PM, Bruce Momjian wrote: Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). This seems like something we should have in core, don't you think? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] Database system identifier via SELECT?
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bruce Momjian Sent: Thursday, December 08, 2011 3:57 PM To: Chris Redekop Cc: pgsql-general Subject: Re: [GENERAL] Database system identifier via SELECT? Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). --- Maybe I mis-understand the question but how about: SHOW listen_addresses; (of the functional equivalent) You just need to make sure each server is listening to a specific IP and/or Port and just tag off of that. David J. -- 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] Database system identifier via SELECT?
Joshua D. Drake wrote: On 12/08/2011 12:57 PM, Bruce Momjian wrote: Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). This seems like something we should have in core, don't you think? Yeah, kind of, except this is the first request we ever got for this. The identifier is passed as part of streaming replication, so maybe it will be needed more in the future. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OT DBA type question - GRANT PRIVILEDGE
Do many of you implement fine grained security provisions at the DBMS level using GRANT PRIVILEGE ON object TO user/role? My background with DBMS security is mostly in CODASYL shallow network systems where user level access control to datasets and fields was mainly a matter for the application and / or operating system. Therefor the number of user ids known to the DBMS was minimal. The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. This is not really a PG specific question so if anyone wishes to reply privately rather than to the list that is fine with me. Thanks, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Database system identifier via SELECT?
On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: Joshua D. Drake wrote: On 12/08/2011 12:57 PM, Bruce Momjian wrote: Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). select inet_server_addr()? --Scott This seems like something we should have in core, don't you think? Yeah, kind of, except this is the first request we ever got for this. The identifier is passed as part of streaming replication, so maybe it will be needed more in the future. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Database system identifier via SELECT?
You can get the database system identifier from the OS shell as part of the control data: pg_controldata /Library/PostgreSQL/9.1/data Here, '/Library/PostgreSQL/9.1/data' is my data directory on os x; replace it with your own data directory. From there, you can isolate the database system identifier with grep: pg_controldata /Library/PostgreSQL/9.1/data | grep system identifier This is not the same as calling a function within a SELECT statement, but using the shell command above, one could easily write a function that returns the database system identifier as a string in a SQL query. I hope this solves the problem. On Thu, Dec 8, 2011 at 4:57 PM, Scott Mead sco...@openscg.com wrote: On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: Joshua D. Drake wrote: On 12/08/2011 12:57 PM, Bruce Momjian wrote: Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). select inet_server_addr()? --Scott This seems like something we should have in core, don't you think? Yeah, kind of, except this is the first request we ever got for this. The identifier is passed as part of streaming replication, so maybe it will be needed more in the future. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] OT DBA type question - GRANT PRIVILEDGE
On 12/8/2011 3:46 PM, James B. Byrne wrote: Do many of you implement fine grained security provisions at the DBMS level using GRANT PRIVILEGE ON object TO user/role? My background with DBMS security is mostly in CODASYL shallow network systems where user level access control to datasets and fields was mainly a matter for the application and / or operating system. Therefor the number of user ids known to the DBMS was minimal. The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. This is not really a PG specific question so if anyone wishes to reply privately rather than to the list that is fine with me. Thanks, This is probably a bad example. Or maybe a good example of how not to do it. I have a generic user, with create user privileges. When users run my app (its a windows delphi app) they type in their username/password, I try to connect as them, and if it doesnt work I connect as the generic user, create them, and reconnect as them. I pretty much use the user to see who is connected, and for audit logging. I have the rights and stuff in a user table, and the delphi app does all the rights checking. On the website side, I always connect as a webuser, who has full read/write. Even though the website is read only. Err.. I do keep stats, so its mostly read only. -Andy -- 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] Clarification on CONTEXT: xlog redo xid assignment
What were the ramifications of the bug? I don't see any details in the release notes. It is not really an option to upgrade before the move. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Clarification-on-CONTEXT-xlog-redo-xid-assignment-tp5059725p5060301.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] Statistics mismatch between n_live_tup and actual row count
Andreas Brandl m...@3.141592654.de writes: we're currently investigating a statistics issue on postgres. We have some tables which frequently show up with strange values for n_live_tup. If you compare those values with a count on that particular table, there is a mismatch of factor 10-30. This causes the planner to come up with very bad plans (we also have this issue on bigger table like the one below). The planner doesn't use n_live_tup; the only thing that that's used for is decisions about when to autovacuum/autoanalyze. So you have two problems here not one. Can you provide a test case for the n_live_tup drift? That is, something that when done over and over causes n_live_tup to get further and further from reality? 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]
http://happenenstappengroningen.nl/news.html?r5h1h5
Re: [GENERAL]
On 08/12/2011 23:07, sharmi...@yahoo.com wrote: http://happenenstappengroningen.nl/news.html?r5h1h5 Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... There have been a few posts like this recently - links without any commentary or explanation. 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]
On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: On 08/12/2011 23:07, sharmi...@yahoo.com wrote: http://happenenstappengroningen.nl/news.html?r5h1h5 Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... There have been a few posts like this recently - links without any commentary or explanation. Ray. It is not legit in any way. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 3:24 AM, Craig Ringer ring...@ringerc.id.au wrote: On Dec 8, 2011 1:27 PM, Chris Travers chris.trav...@gmail.com wrote: Additionally I am not entirely sure what he means by the last point. If you look at the work that NTT along with EDB has put into Postgres-XC, for example, it looks to me like the Postgres ecosystem is growing by leaps and bounds and we are approaching an era where Oracle is no longer ahead in any significant use case. While Pg is impressively capable now, I don't agree that Oracle (if DB2, MS-SQL etc) isn't ahead for any significant use case. Not on a purely technical basis anyway - once cost is considered there may be a stronger argument. I said approaching an era for a reason. We aren't there yet, but we are fast approaching it. Major areas I didn't think PostgreSQL would ever directly compete in are now within arms reach. Also when I say use case, I am talking like: I have a 2TB database and need to be able to run aggregates across 20M row tables as part of my transactional system. What I see you mentioning are tools missing which in some cases show use cases we aren't so good at (high security databases where row-level security needs to be enforced, or accounting systems for holding companies or the like), but given now that the above use case is now within reach, I have to think the others will be soon as well. Best Wishes, Chris Travers -- 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]
spam, it has a redirect to a spam site. On 9 December 2011 02:15, Joshua D. Drake j...@commandprompt.com wrote: On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: On 08/12/2011 23:07, sharmi...@yahoo.com wrote: http://**happenenstappengroningen.nl/**news.html?r5h1h5http://happenenstappengroningen.nl/news.html?r5h1h5 Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... There have been a few posts like this recently - links without any commentary or explanation. Ray. It is not legit in any way. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.**postgresqlconference.org/http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL]
On 08/12/2011 23:19, Bèrto ëd Sèra wrote: spam, it has a redirect to a spam site. On 9 December 2011 02:15, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.com wrote: On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: On 08/12/2011 23:07, sharmi...@yahoo.com mailto:sharmi...@yahoo.com wrote: http://__happenenstappengroningen.nl/__news.html?r5h1h5 http://happenenstappengroningen.nl/news.html?r5h1h5 Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... There have been a few posts like this recently - links without any commentary or explanation. Ray. It is not legit in any way. OK - thanks. 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
[GENERAL] Function Question
I am trying to create a function that gets fired when a new row is inserted. I can get the following function built but my problem is that the sequence that is created is called NEW.key rather than the value that is being inserted (it should be a UUID). I have tried putting the key into a variable and using no quotes but it either errors or gives me NEW.key rather than whatever-uuid-was inserted. Please can anybody give me some pointers? Thanks Jake CREATE OR REPLACE FUNCTION public.setup_sequence() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE sequence_name UUID; BEGIN IF ( TG_OP = 'INSERT' AND NEW.type = 'auto_increment' ) THEN SELECT NEW.key INTO STRICT sequence_name; -- Set up the sequence CREATE SEQUENCE \NEW.key\; -- Set the start value --SELECT SETVAL(sequence_name, NEW.auto_increment_default); END IF; RETURN NEW; END; -- 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] Hope for a new PostgreSQL era?
On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce pie...@hogranch.com wrote: On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. Happily, the complications and fragility are now utilised by Oracle to help sell ExaData units, on the basis that if you give Oracle even more money, they'll sell you a RAC that actually works! -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 6:54 AM, Merlin Moncure mmonc...@gmail.com wrote: There is simply no comparing mysql's backend programming features with those of postgres. Postgres is a development platform in a box, mysql is not. This and there are annoyances I have run into with MySQL, such as deadlocks on a system with only one running transaction I traced this problem eventually to multi-row inserts and concluded it was a race condition in threaded processing. As I say, any db can and should allow conflicting transactions to deadlock. It takes special talent to allow transactions to deadlock against *themselves*.. Interestingly I have customers who run both MySQL and PostgreSQL and have had similar issues with transactions deadlocking against themselves on MySQL, so I know I am not unique here. MySQL, I am sorry to say, is not catching up to PostgreSQL at least from where I stand except for the few very specific workloads that its query cache are designed to work with. Best Wishes, Chris Travers -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson rodg...@diaspora.gen.nz wrote: On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce pie...@hogranch.com wrote: On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. Happily, the complications and fragility are now utilised by Oracle to help sell ExaData units, on the basis that if you give Oracle even more money, they'll sell you a RAC that actually works! Looking at the general design of Postgres-XC compared to RAC, which workloads would the latter excel at as a matter of design that the former would not? Granted Postgres-XC is still pre-1.0 (latest release iirc is 0.9.6) and it doesn't yet support everything it needs to, but it looks very promising in this area, and it is open source. Best Wishes, Chris Travers -- 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] OT DBA type question - GRANT PRIVILEDGE
On 12/09/2011 05:46 AM, James B. Byrne wrote: The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. User IDs are often used to help secure multi-tenanted databases. I just wrote about this in response to another question, see: http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618 Individual user IDs are often useful the same way, via `SET ROLE' from an unpriveleged account a connection pool uses. Because of connection establishment overheads and the need to pool connections I'm generally reluctant to use setups where the app auths against the database with a given user ID and password directly. -- Craig Ringer
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thu, 8 Dec 2011 16:34:49 -0800, Chris Travers chris.trav...@gmail.com wrote: On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson rodg...@diaspora.gen.nz wrote: On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce pie...@hogranch.com wrote: On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. Happily, the complications and fragility are now utilised by Oracle to help sell ExaData units, on the basis that if you give Oracle even more money, they'll sell you a RAC that actually works! Looking at the general design of Postgres-XC compared to RAC, which workloads would the latter excel at as a matter of design that the former would not? Not having touched -XC I can only theorycraft, but if I've understood the shared-nothing model correctly, I'd expect it to hammer RAC in high-write workloads, based on my experience of contention in RAC servers - a common condition is that since the SGA is (effectively) shared across the RAC interconnect, you're effectively limiting your peak write performance to the throughput and latency of your ethernet link (which is, I assume, why the ExaData uses Infiniband). We've had to do things like set up seperate connection pools which write to one node at a time (with failover, of course) for high-INSERT tables to avoid the RAC becoming painfully slow. On the other hand I don't see anything that would suggest -XC will work as seamlessly for failover as the RAC does. We went through a period where our applications (and DBAs!) failed to notice kernel-panic induced reboots of RAC members at least once every couple of weeks, with absolutely no customer impact. Anyway, my comment was more of a dig at the selling pitch for ExaData: I have had it pitched with a straight face that I should want to buy one because RAC is so hard to configure and maintain. Granted Postgres-XC is still pre-1.0 (latest release iirc is 0.9.6) and it doesn't yet support everything it needs to, but it looks very promising in this area, and it is open source. It looks really interesting. Thanks for the pointer. -- 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] Hope for a new PostgreSQL era?
On 12/08/2011 08:27 PM, Simon Riggs wrote: On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringerring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Marc has, as I was hoping, done so much better than I could. Most of what I know is 2nd hand from Oracle users - I'm not one myself. It's interesting to see the view that the resource manager for query and user prioritisation is hard to use in practice. That's not something I'd heard before, but I can't say I'm entirely surprised given how complicated problems around lock management and priority inversion are to get right even in a system where there *aren't* free-form dynamic user-defined queries running. -- 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
Re: [GENERAL]
Joshua D. Drake j...@commandprompt.com writes: On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... It is not legit in any way. Lately we've been seeing a lot of link-spam with just a link in the body, generally no subject line, and the real tip-off is that it's addressed To: both pgsql-general (or another of our lists) and half a dozen random other addresses. I assume these are coming from addresses that are actually subscribed to our lists, because otherwise the moderators should've rejected them. Probably somebody's found a way to break into large numbers of yahoo and google mail accounts and spam from them to all their address book entries ... 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] Hope for a new PostgreSQL era?
On 12/09/2011 01:02 AM, Tomas Vondra wrote: On 8 Prosinec 2011, 17:11, Marc Cousin wrote: Le Thu, 8 Dec 2011 09:29:28 -0600, - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. you have a couple of very good options to achieve the same in postgres -- pgbouncer, pgpool. I wish it was the same (I use and like both pgbouncer and pgpool too, and they do a good job, I'm not arguing on that). But unfortunately it isn't: you still have the notion of session for each connected client in Oracle when using the shared servers model. True, it is not exactly the same, it's similar. I don't think PostgreSQL will ever provide 'shared backends' the way Oracle does - it would require significant code change. Most of the benefits can be achieved by using a connection pool without the added complexity. Yep - a connection pool that could save and restore session state, separating executor/backend from connection/session, would produce much the same result with a lot less complexity. It's one of the reasons I'd love to see in-core pooling, because I don't see how an out-of-core solution can maintain session state like advisory locks, HOLD cursors, 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
Re: [GENERAL] Function Question
Jake Stride j...@stride.me.uk writes: I am trying to create a function that gets fired when a new row is inserted. I can get the following function built but my problem is that the sequence that is created is called NEW.key rather than the value that is being inserted (it should be a UUID). Instead of this: CREATE SEQUENCE \NEW.key\; you need to do something like this: EXECUTE 'CREATE SEQUENCE ' || quote_ident(NEW.key); The reason is that utility statements don't accept parameters natively, so you have to construct the command as a string and then EXECUTE it. 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 6:47 PM, Rodger Donaldson rodg...@diaspora.gen.nz wrote: On Thu, 8 Dec 2011 16:34:49 -0800, Chris Travers chris.trav...@gmail.com wrote: On Thu, Dec 8, 2011 at 4:09 PM, Rodger Donaldson rodg...@diaspora.gen.nz wrote: On Thu, 08 Dec 2011 11:24:12 -0800, John R Pierce pie...@hogranch.com wrote: On 12/08/11 11:16 AM, Joshua D. Drake wrote: um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. I meant worrying about it for Pg. the odds of Postgres developing something as complex and intricate as RAC are probably between zilch and none. RAC was for many years completely unusable, and even now, its complicated, fragile, and expensive. Happily, the complications and fragility are now utilised by Oracle to help sell ExaData units, on the basis that if you give Oracle even more money, they'll sell you a RAC that actually works! Looking at the general design of Postgres-XC compared to RAC, which workloads would the latter excel at as a matter of design that the former would not? Not having touched -XC I can only theorycraft, but if I've understood the shared-nothing model correctly, I'd expect it to hammer RAC in high-write workloads, based on my experience of contention in RAC servers - a common condition is that since the SGA is (effectively) shared across the RAC interconnect, you're effectively limiting your peak write performance to the throughput and latency of your ethernet link (which is, I assume, why the ExaData uses Infiniband). We've had to do things like set up seperate connection pools which write to one node at a time (with failover, of course) for high-INSERT tables to avoid the RAC becoming painfully slow. I think you have to define shared-nothing in this context. Like a shared-storage architecture, this has two tiers, namely a tier that orchestrates queries and a storage tier for lack of a better word. In other words, XC is attempting to be like Teradata more than like RAC. In fact one of the key issues here is that Postgres-XC is supposed to be write-scalable. On the other hand I don't see anything that would suggest -XC will work as seamlessly for failover as the RAC does. We went through a period where our applications (and DBAs!) failed to notice kernel-panic induced reboots of RAC members at least once every couple of weeks, with absolutely no customer impact. Looking to me like it would be quite possible to set it up so that it would be seemless in the event of node reboots at least on the coordinator tier. On the storage tier, not sure the extent to which this would be possible but it might depend on Postgresql replication options and how well supported they are by XC. Anyway, my comment was more of a dig at the selling pitch for ExaData: I have had it pitched with a straight face that I should want to buy one because RAC is so hard to configure and maintain. In which case it's good that Pg isnt gunning for that market, right? Granted Postgres-XC is still pre-1.0 (latest release iirc is 0.9.6) and it doesn't yet support everything it needs to, but it looks very promising in this area, and it is open source. It looks really interesting. Thanks for the pointer. YW :-) Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Character encoding problems
Sorry for the duplicate postings. I have only recieved one reply so far and that was a suggestion to post to this forum. I trying to build a database to support natural language processing from a variety of data files posted on the internet. Many of them are identified as using UTF-8 encoding. Some of these are dictionary files fro WinEdt. Some are from an Open Source multi-lingual health care package. When I try to build a table from several of the different languages I get the following error ERROR: invalid byte sequence for encoding UTF8: 0x82 I checked the encoding and it is indeed set up for Unicode-8. I tried to create databases using a variety of other encoding types such as WIN1252 and others and I got the same error message from all of them except SQL_ASCII. When I created the database using SQL_ASCII I received the warning that the database could only store 7 bit data. When I loaded the data in this database I did not have any errors and when I look at the data it seems to be the same as in the original text file. Is there a proper encoding type that I should use to load the word lists so they can be interoperable with the WordNet dataset that happily uses the UTF8 encoding? Bruce -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
