Re: [GENERAL] Scalable cluster
Le dimanche 03 mars 2013 à 23:29 +, Gregg Jaskiewicz a écrit : I wonder however, how others are handing it. There seems to be nothing out there apart from pgbouncer and pgpool. And only the latter can handle (albeit not really that quick) pooling between master and slaves. How do you guys go about designing such cluster. There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] -- Salutations, Vincent Veyron http://marica.fr/site/demonstration Progiciel de gestion des sinistres assurance et des dossiers contentieux pour le service juridique -- 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] query syntax to combine 2 set returning functions ?
On Mon, Mar 4, 2013 at 1:24 AM, Marc Mamin m.ma...@intershop.de wrote:
I can't find the syntax to get the results in multiple columns instaed of a
singel 'record' column:
select get_table_depends('cicpg_logs', t) FROM
get_modeltablelist('efeeds') t
I got it:
select (get_table_depends('cicpg_logs', t)).* FROM
get_modeltablelist('efeeds') t
^ ^
This is another query that get's simplified through 9.3 LATERAL. As
you have written the query, get_table_depends will get executed once
for each column that get_table_depends returns.
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] out of memory issue
On Sun, Mar 3, 2013 at 11:05 AM, G N myte...@gmail.com wrote: Hello Friends, Hope you are all well... I have a specific issue, where my query fails with below error while trying to export data from pgadmin SQL tool. There are no such issues when the result set is small. But it returns error when the result set is bit large. Any inputs please ? Where and how should memory be increased in case ? out of memory for query result I'm guessing your query is returning a lot of data and the export itself is not being produced with COPY. As such, you are subject to the limits of the 32 bit libpq you are probably using (or if you are using 64 bit, you are well and truly running out of memory). Solution to move forward. learn COPY and psql \copy. Refer documentation. merllin -- 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] Scalable cluster
On 4 Mar 2013, at 13:21, Vincent Veyron vv.li...@wanadoo.fr wrote: There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is 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] Scalable cluster
Version 1.1 will be out in this June time frame, with online node addition/removal, Trigger and improved planner. I'm more than happy if you evaluate XC. Regards; -- Koichi Suzuki 2013/3/4 Greg Jaskiewicz gryz...@gmail.com: On 4 Mar 2013, at 13:21, Vincent Veyron vv.li...@wanadoo.fr wrote: There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. -- 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] Scalable cluster
One question about Postgres-XC, can it distribute one single query in all nodes ? Example, Nodes: Node1,Node2,Node3 Query:SELECT sum(foo) FROM bar group by foo (Obvious: Your data has to be shared between the nodes) Query - Compiler (in the pool)- share the query between the nodes - merge - return I know tools to do it, but don't have full implementation like Netezza. Thank you. On 4 March 2013 13:36, Koichi Suzuki koichi@gmail.com wrote: Version 1.1 will be out in this June time frame, with online node addition/removal, Trigger and improved planner. I'm more than happy if you evaluate XC. Regards; -- Koichi Suzuki 2013/3/4 Greg Jaskiewicz gryz...@gmail.com: On 4 Mar 2013, at 13:21, Vincent Veyron vv.li...@wanadoo.fr wrote: There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. -- 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 -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO -- 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] Scalable cluster
just fixing: like Netezza which has full implementation to do it.* On 4 March 2013 14:59, Daniel de Oliveira Mantovani daniel.oliveira.mantov...@gmail.com wrote: One question about Postgres-XC, can it distribute one single query in all nodes ? Example, Nodes: Node1,Node2,Node3 Query:SELECT sum(foo) FROM bar group by foo (Obvious: Your data has to be shared between the nodes) Query - Compiler (in the pool)- share the query between the nodes - merge - return I know tools to do it, but don't have full implementation like Netezza. Thank you. On 4 March 2013 13:36, Koichi Suzuki koichi@gmail.com wrote: Version 1.1 will be out in this June time frame, with online node addition/removal, Trigger and improved planner. I'm more than happy if you evaluate XC. Regards; -- Koichi Suzuki 2013/3/4 Greg Jaskiewicz gryz...@gmail.com: On 4 Mar 2013, at 13:21, Vincent Veyron vv.li...@wanadoo.fr wrote: There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. -- 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 -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Floating point error
On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real difficulty is that there may be more than one storable value that corresponds to 1.23456 to six decimal digits. To be certain that we can reproduce the stored value uniquely, we have to err in the other direction, and print *more* decimal digits than the underlying precision justifies, rather than a bit less. Some of those digits are going to look like garbage to the naked eye. I think part of the difficulty here is that psql (if I understand this correctly) conflates the wire-format text representations with what should be displayed to the user. E.g., a different driver might parse the wire representation into a native representation, and then format that native representation when it is to be displayed. That's what the JDBC driver does, so it doesn't care about how the wire format actually looks. pg_dump cares about reproducing values exactly, and not about whether things are nice-looking, so it cranks up extra_float_digits. The JDBC driver might be justified in doing likewise, to ensure that the identical binary float value is stored on both client and server --- but that isn't even a valid goal unless you assume that the server's float implementation is the same as Java's, which is a bit of a leap of faith, even if IEEE 754 is nigh universal these days. I would hope that any driver cares about reproducing values exactly (or at least as exactly as the semantics of the client and server representations of the data type allow). Once you start talking operations, sure, things get a lot more complicated and you're better off not relying on any particular semantics. But IEEE 754 unambiguously defines certain bit patterns to correspond to certain values, no? If both client and server talk IEEE 754 floating point, it should be possible to round-trip values with no fuss and end up with the same bits you started with (and as far as I can tell, it is, as long as extra_float_digits is set to the max), even if the implementations of actual operations on these numbers behave very differently on client and server. I think given that many ORMs can cause UPDATEs on tuple fields that have not changed as part of saving an object, stable round trips seem like a desirable feature. We could have dumbed it down to a boolean look nice versus reproduce the value exactly switch, but it seemed like there might be applications that could use some additional flexibility. In any case, it's not Postgres' fault that there is an issue here; it's fundamental to the use of binary rather than decimal stored values. It seems like getting things to look nice should be the client's job, no? Why does that factor into wire protocol data representations (and yes, I know part of the answer here--presumably literals are intimately tied to the same code paths, so it's not quite so simple)? Going back to the documentation patch, what should the advice be? How about something along these lines: Due to the nature of floating point numeric values, a faithful textual representation of a typereal/type or typedouble precision/type value requires some decimal digits that are generally insignificant, impairing readability of common values. Because of this, Postgres supports a limited output precision for floating point numbers by default. In order to preserve floating point values more exactly, you can use the xref linkend=guc-extra-float-digits to adjust this setting. Is that reasonable? It still feels like extra_float_digits should be opt-out rather than opt-in (leaving any formatting issues to clients), but this could be a start. It doesn't address non-IEEE 754 platforms, but the note in the other proposed patch is so high-level as to just be hand-waving. -- 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] Scalable cluster
On Mon, Mar 4, 2013 at 1:01 PM, Daniel de Oliveira Mantovani daniel.oliveira.mantov...@gmail.com wrote: just fixing: like Netezza which has full implementation to do it.* On 4 March 2013 14:59, Daniel de Oliveira Mantovani daniel.oliveira.mantov...@gmail.com wrote: One question about Postgres-XC, can it distribute one single query in all nodes ? Example, Nodes: Node1,Node2,Node3 Query:SELECT sum(foo) FROM bar group by foo XC handles queries like this in parallel to a certain extent. A plain old SELECT SUM(foo) FROM bar will get the sum on all of the nodes, then sum it up again at the coordinator. Depending on your table distribution scheme, XC will do a pretty good job at pushing down joins so that they occur locally. For example, one table is distributed, and a second is replicated on all nodes. Any join between the two tables can occur locally on each data node in parallel. You will experience slow query times if you have to join data from one node with data from another node. It all gets shipped to the coordinator for joining. If you are after query parallelism for a data warehouse and want to be PostgreSQL-based, then I recommend a project called Stado. If you are looking for mainly write scalability where the workload is largely singleton type of statements, then I recommend Postgres-XC. Yet another solution you can consider is PL/Proxy, which was developed by Skype. (Obvious: Your data has to be shared between the nodes) Query - Compiler (in the pool)- share the query between the nodes - merge - return I know tools to do it, but don't have full implementation like Netezza. Thank you. On 4 March 2013 13:36, Koichi Suzuki koichi@gmail.com wrote: Version 1.1 will be out in this June time frame, with online node addition/removal, Trigger and improved planner. I'm more than happy if you evaluate XC. Regards; -- Koichi Suzuki 2013/3/4 Greg Jaskiewicz gryz...@gmail.com: On 4 Mar 2013, at 13:21, Vincent Veyron vv.li...@wanadoo.fr wrote: There is this : http://wiki.postgresql.org/wiki/Postgres-XC [Disclaimer : I can't tell whether it's usable or not; I just know they exist] Well, I know of its existence too. Question is how production ready is it. And also how far off the 9.2 line is it. -- 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 -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO -- -dom -- IBM - Business Analytics Optimization Consultant Daniel Mantovani +5511 8538-9897 XOXO -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Mason Sharp StormDB The Database Cloud Postgres-XC Support and Services
Re: [HACKERS] [GENERAL] Floating point error
On Mon, Mar 4, 2013 at 2:27 PM, Maciek Sakrejda m.sakre...@gmail.com wrote: On Sun, Mar 3, 2013 at 9:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real difficulty is that there may be more than one storable value that corresponds to 1.23456 to six decimal digits. To be certain that we can reproduce the stored value uniquely, we have to err in the other direction, and print *more* decimal digits than the underlying precision justifies, rather than a bit less. Some of those digits are going to look like garbage to the naked eye. I think part of the difficulty here is that psql (if I understand this correctly) conflates the wire-format text representations with what should be displayed to the user. E.g., a different driver might parse the wire representation into a native representation, and then format that native representation when it is to be displayed. That's what the JDBC driver does, so it doesn't care about how the wire format actually looks. pg_dump cares about reproducing values exactly, and not about whether things are nice-looking, so it cranks up extra_float_digits. The JDBC driver might be justified in doing likewise, to ensure that the identical binary float value is stored on both client and server --- but that isn't even a valid goal unless you assume that the server's float implementation is the same as Java's, which is a bit of a leap of faith, even if IEEE 754 is nigh universal these days. I would hope that any driver cares about reproducing values exactly (or at least as exactly as the semantics of the client and server representations of the data type allow). Once you start talking operations, sure, things get a lot more complicated and you're better off not relying on any particular semantics. But IEEE 754 unambiguously defines certain bit patterns to correspond to certain values, no? If both client and server talk IEEE 754 floating point, it should be possible to round-trip values with no fuss and end up with the same bits you started with (and as far as I can tell, it is, as long as extra_float_digits is set to the max), even if the implementations of actual operations on these numbers behave very differently on client and server. I think given that many ORMs can cause UPDATEs on tuple fields that have not changed as part of saving an object, stable round trips seem like a desirable feature. I also find the rationale for extra_float digits quite mysterious for the same reason: why would most programs care about precision less than pg_dump does? If a client wants floating point numbers to look nice, I think the rendering should be on them (e.g. psql and pgadmin), and the default should be to expose whatever precision is available to clients that want an accurate representation of what is in the database. This kind of change may have many practical problems that may make it un-pragmatic to alter at this time (considering the workaround is to set the extra float digits), but I can't quite grasp the rationale for well, the only program that cares about the most precision available is pg_dump. It seems like most programs would care just as much. -- fdr -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.2 timestamp function syntax error
I don't understand the error resulting from the following progression on 9.2 (specifically EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit): select sysdate = timestamp without time zone select timestamptz(sysdate) = timestamp with time zone select timestamp(timestamptz(sysdate)) = ERROR: syntax error at or near timestamptz OR select timestamp(sysdate::timestamptz)) = ERROR: syntax error at or near sysdate I see a function in pg_catalog with signature timestamp(timestamp with time zone). Why isn't it being applied? Thanks. -- Guy Rouillier -- 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 (Schema) Objects?
Hi Oracle defines database (schema) objects and non-schema objects (see [1]). Is there also such a thing in Postgres? Yours, Stefan [1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements007.htm -- 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 (Schema) Objects?
On 3/5/2013 2:03 AM, Stefan Keller wrote: Hi Oracle defines database (schema) objects and non-schema objects (see [1]). Is there also such a thing in Postgres? Yes. See, for example, CREATE USER, CREATE TABLESPACE and CREATE SCHEMA. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
