Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. I won't argue with whether or not nested naming is a good idea, but I will argue with your other comment about breaking relational handling. A relational database is a database in which all data is kept in relation-typed variables, which SQL calls tables, and you can perform all queries and updates with just relation-valued expressions and statements. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The less relational argument above is a red herring or distraction. One can argue against namespace nesting just fine without saying that. -- Darren Duncan -- 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] Creating temp tables inside read only transactions
On Jul8, 2011, at 08:21 , Darren Duncan wrote: Also, the proper way to do temporary tables would be to put them in another database than the main one, where the whole other database has the property of being temporary. FWIW, Microsoft SQL Server does it that way, and as a result temporary tables are severely restricted in a number of ways. For example, custom datatypes defined in a non-temporary database cannot be used in temporary table definitions, because datatypes may only be used within the database they're defined in. You can of course re-define the data type in the temporary database, but then obviously have to do so every time you start new session because you start out with an empty tempdb. best regards, Florian Pflug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema for Website Comments
Dear all, Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The less relational argument above is a red herring or distraction. One can argue against namespace nesting just fine without saying that. -- Darren Duncan I agree with Darren. One thought that came to my mind was to use a different separator between two namespaces and/or between the database identifier and the rest of the path. Examples: ns1!ns2.table OR database@ns1.table OR database@ns1!ns2.table I've been following only some of the discussion but it seems that much ambiguity would be lost by using different separators. Schemas themselves are already non-standard so it isn't like we are constrained here in what is chosen. Just some quick thoughts I've had but haven't fully considered how they would fit in to the existing setup. But is there is any major reason why choosing different separators would not work? Also, within search_path, some form of wild-card selector would be desirable: ns1!*. I'm not opposed to having to be explicit about the search_path in order to avoid name collisions; though it would be nice if VIEWS had some kind of SET syntax, like functions do, so that the definer can specify the search_path that the view will resolve against. 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: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
A relational database is a database in which all data is kept in
relation-typed variables, which SQL calls tables, and you can perform
all queries and updates with just relation-valued expressions and
statements.
Organizing the tables into a multi-level namespace, either fixed-depth
or variable-depth, rather than using a flat namespace, does not make
the database any less relational, because the above definition and
any others still hold.
The point was not, I think, that tables aren't suddenly relations once
namespaces are nested, but that the data model of the dbms *itself*,
i.e. the data model that defines the relationship between namespaces,
types, columns, type, ... becomes harder to map to the relational model.
For example, if namespaces can be nested, you'll need to resort to
recursive SQL and/or arrays far more often if you inspect the structure
of a database.
Btw, another argument against nested namespaces is that it actually
doesn't buy you anything in SQL, even if you solve the parsing
ambiguities. In programming languages, namespaces not only prevent
name clashes, the also defines the possible scopes to resolve unqualified
names with. For example, if you do
void f() { printf(outer); }
namespace a {
void f() { printf(inner); }
namespace b {
void g() {f();}
}
}
in C++, then a::b::g() prints inner. But in PostgreSQL, the scope in
which to resolve unqualified function is entirely determined by the the
search_path setting, *not* by the scope of the object containing the
unqualified name. Nested namespaces thus simply become of matter of
syntax - i.e., whether you can write a.b.c, or need to write a.b.c.
best regards,
Florian Pflug
--
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] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote: On Jul11, 2011, at 07:08 , Darren Duncan wrote: Christopher Browne wrote: Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1, there's a pretty good reason NOT to support that, namely that this breaks relational handling of tables. PostgreSQL is a *relational* database system, hence it's preferable for structures to be relational, as opposed to hierarchical, which is what any of the suggested nestings are. A relational database is a database in which all data is kept in relation-typed variables, which SQL calls tables, and you can perform all queries and updates with just relation-valued expressions and statements. Organizing the tables into a multi-level namespace, either fixed-depth or variable-depth, rather than using a flat namespace, does not make the database any less relational, because the above definition and any others still hold. The point was not, I think, that tables aren't suddenly relations once namespaces are nested, but that the data model of the dbms *itself*, i.e. the data model that defines the relationship between namespaces, types, columns, type, ... becomes harder to map to the relational model. Just so. It's not that it suddenly becomes no longer relational. Rather, the argument is that it was intentional for the structuring of table naming to, itself, be relational, and changing that definitely has some undesirable characteristics. The need for recursive queries is the most obvious undesirable, but it's not the only undesirable thing, by any means. Sure, there's some cool stuff that we can get out of nested namespaces, but I think we'd pay a pretty big price for it, and it shouldn't be treated as obvious that: a) It's a good thing to do so, b) It is desirable to do so, c) There will be agreement to do so. To the contrary, there are pretty good reasons to reject the idea. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Two PHP projects looking for PostgreSQL help
Folks, In the last couple of weeks I've been approached by two PHP projects who could use help improving/maintainig their PostgreSQL support. One is Mediawiki, which just needs help with maintenance, testing, and improving certain features like full text search. The second is Joomla, which is in the middle of implementing a new database abstraction layer, and needs someone from our community to make sure it works well with PostgreSQL. If you are a PHP geek, and use either of these projects -- or are merely looking for a way to contribute to PostgreSQL -- please consider volunteering to help out. Either contact me (off-list), or if you're already on a Mediawiki or Joomla project mailing list, speak up there. Thanks! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Slow queries (high duration) and their log entries appearing out-of-order
I'm trying to debug a jboss/hibernate application that uses PostgreSQL as a backend, for which PostgreSQL is reporting a lot of queries as taking around 4398046 ms (~73 minutes) plus or minus 10 ms to complete. I have two questions about this. First, when I look at the logs, the long queries appear interleaved between log lines from earlier. For example, when I set log_min_duration_statement to 0, with a log_destination of stderr and log_line_prefix of '%t %c ', my log file has entries like this: ... 2011-07-08 19:12:01 WEST 4e174859.1f5b LOG: duration: 0.000 ms execute unnamed: select count(*) as num_sender from madserv_sender where alias = $1 2011-07-08 19:12:01 WEST 4e174859.1f5b DETAIL: parameters: $1 = '965990300' 2011-07-08 20:25:19 WEST 4e174859.1f5b LOG: duration: 4398046.575 ms bind S_4: INSERT INTO madserv_user_sender(user_id,sender_id)values((select id from madserv_admin_user where login = $1),(select id from madserv_sender where alias = $2)) 2011-07-08 20:25:19 WEST 4e174859.1f5b DETAIL: parameters: $1 = '3045530977U80019488', $2 = '965990300' 2011-07-08 20:25:19 WEST 4e174859.1f5b LOG: duration: 0.322 ms execute S_4: INSERT INTO madserv_user_sender(user_id,sender_id)values((select id from adserv_admin_user where login = $1),(select id from madserv_sender where alias = $2)) 2011-07-08 20:25:19 WEST 4e174859.1f5b DETAIL: parameters: $1 = '3045530977U80019488', $2 = '965990300' 2011-07-08 19:12:01 WEST 4e174859.1f5b LOG: duration: 0.339 ms parse unnamed: select count(*) as num_user from madserv_admin_user where login = '8842934' ... Here the entries from 20:25:19 are bracketed by entries from 19:12:01. I noticed that 20:25:19 - 4398046.575 ms is 19:12:01 - but this is strange to me as I thought the log entries would appear in chronological order. Or am I misunderstanding something here? Secondly, I'm trying to understand why the queries are taking a long time. The queries affected are varied, and I see the delays occurring in the parse, bind and execute states. Is there a known issue which would cause this? I've even seen the behaviour on the query select 1, so I don't think it's a problem with the schema design or even general performance: 2011-07-01 01:10:30 WEST LOG: duration: 4398046.526 ms bind unnamed: select 1 2011-07-01 00:00:13 WEST LOG: duration: 4398046.589 ms parse unnamed: select 1 I've put the output from explain analyze at http://explain.depesz.com/s/RYR for the above query INSERT INTO madserv_user_sender. The madserv_user_sender table has 2 columns of type integers, with ~2000 entries and foreign key constraints on the other tables/columns in the query. The madserv_admin_user table has ~4500 rows and has a btree index on the login(int) column. The madserv_sender table has ~300 entries and a btree index on the alias (varchar(32)) column. Between 19:11 and 19:12 there are ~100s of these inserts running per second. The developers say that they haven't seen this before. My version of PostgreSQL is: PostgreSQL 8.3.5 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) running on RHEL 5.4 i686. PostgreSQL was installed from RPMs which came from http://www.postgresql.org/ftp/binary/ Jboss is 4.2.3-GA, running on the Sun JDK 1.6.0u12, with the PostgreSQL JDBC JAR postgresql-8.3-603.jdbc4.jar. I realise that I'm behind on the minor version for the PostgreSQL server, and I'm going to recommend upgrading - but it'd be nice to know if anyone else has seen this behaviour before. Thanks for your help. -- Jonathan Barber jonathan.bar...@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] Schema for Website Comments
On Sun, Jul 10, 2011 at 10:14 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. PostgreSQL supports WITH RECURSIVE as of 8.4 and higher. http://www.postgresql.org/docs/8.4/static/queries-with.html 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] Performance Monitoring of PostGRE
On Wednesday 06 July 2011 14:27:53 BangarRaju Vadapalli wrote: We want to monitor the performance of PostGRE database. Could anyone please suggest any tools tried/working successfully... Munin will graph some usefull postgres stats. It's easy enough to graph another datapoint by creating a new plugin if you need to (for example, in addition to the global stats we graph the size of some specific tables). Pgfouine will create an aggregated report of all your queries, provided you've setup postgre swith sufficient logging. Explain analyze is you friend for individual queries. http://munin.projects.linpro.no/ http://pgfouine.projects.postgresql.org/ http://www.postgresql.org/docs/current/static/sql-explain.html -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sql or pgsql question, accessing a created value
Hope someone's out there for this one. Basically I'm creating a summary table of many underlying tables in one select statement ( though that may have to change ). My problem can be shown in this example.. select my_function( timeofmeasurement, longitude ) as solarnoon, extract(epoch from ( timeofmeasurement - solarnoon ) as solardiff ( case when solardiff 3600 then 'Y' else 'N' end ) as within_solar_hour from my_table; But I get an error along the lines of ERROR: column solarnoon does not exist LINE 8: extract(epoch from (timeofmeasurement - solarnoon) ) as sola... It's probably a compile-time run-time sort of chicken and egg thing. ;) So I' off onto pl/pgsql, but still not having much luck. Full under construction sql right now is: create or replace function load_air_temp_summary() returns void as $$ declare solarnoon timestamp; solardiff interval; BEGIN select count(*) from ( select aird.current_temp, aird.minimum_temp, aird.measured_at, subd.datum_id, subd.datum_type, subm.person_id, subm.site_id, loc.latitude, loc.longitude, select solarnoon( aird.measured_at, loc.longitude ) INTO solarnoon -- ** trying to save the value from air_temp_data aird, submission_data subd, submissions subm, sites sites, locations loc where subd.datum_type = 'AirTempDatum' and subd.datum_id = aird.id and subd.submission_id = subm.id and subm.site_id = sites.id and loc.locatable_type = 'Site' and sites.id = loc.locatable_id ) as fred; END $$ LANGUAGE plpgsql; but it dislikes the third select stmt, or if I remove that select stmt, I get ERROR: syntax error at or near ( LINE 1: ...d, subm.site_id, loc.latitude, loc.longitude, $1 ( aird.mea... Any tips or tricks on how I should approach this are appreciated. How do I store and use values that are calculated on the fly. -ds -- 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] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote:
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
Rather, the argument is that it was intentional for the structuring of
table naming to, itself, be relational, and changing that definitely has
some undesirable characteristics.
The need for recursive queries is the most obvious undesirable, but it's
not the only undesirable thing, by any means.
I do not see how recursive queries (really iteration of records) even enters
the picture...
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
I could give more examples but I hope the basic idea is obvious. The main
thing is that the namespace hierarchy usage is standardized in such a way
that pgAdmin and other GUI tools can reliably use for display purposes and
that search_path can be constructed in a more compact format so that every
schema and sub-schema is still absolutely referenced (you can even have the
SET command resolve search_path at execution time and then remain static
just like CREATE VIEW SELECT * FROM table.
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
[GENERAL] Interesting article, Facebook woes using MySQL
http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ How would PG stack up in a usage situation like this?
Re: [GENERAL] sql or pgsql question, accessing a created value
select my_function( timeofmeasurement, longitude ) as solarnoon, extract(epoch from ( timeofmeasurement - solarnoon ) as solardiff ( case when solardiff 3600 then 'Y' else 'N' end ) as within_solar_hour from my_table; But I get an error along the lines of ERROR: column solarnoon does not exist LINE 8: extract(epoch from (timeofmeasurement - solarnoon) ) as sola... It's probably a compile-time run-time sort of chicken and egg thing. ;) It is. You need to use sub-selects. SELECT solarnoon, solardiff, CASE... AS within_solar_hour FROM SELECT solarnoon, func() AS solardiff FROM ( SELECT func() AS solarnoon ) AS sn -- close solarnoon from ) AS sd -- close solardiff from 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] sql or pgsql question, accessing a created value
On 11/07/2011 20:19, David Salisbury wrote: Hope someone's out there for this one. Basically I'm creating a summary table of many underlying tables in one select statement ( though that may have to change ). My problem can be shown in this example.. select my_function( timeofmeasurement, longitude ) as solarnoon, extract(epoch from ( timeofmeasurement - solarnoon ) as solardiff ( case when solardiff 3600 then 'Y' else 'N' end ) as within_solar_hour from my_table; But I get an error along the lines of ERROR: column solarnoon does not exist LINE 8: extract(epoch from (timeofmeasurement - solarnoon) ) as sola... One (slightly messy) way to do that is create another, outer layer of SELECT - so your on-the-fly calculations are executed in the sub-select, and the values are then available to the outer select. You have three levels of dependency, so you'll need two subqueries: not tested select x.solarnoon, x.solardiff, (case when x.solardiff 3600 then 'Y' else 'N' end) as within_solar_hour from ( select extract(epoch from (y.timeofmeasurement - y.solarnoon) as solardiff, y.timeofmeasurement from ( select my_function(timeofmeasurement, longitude) as solarnoon, timeofmeasurement from my_table ) y ) x; /not tested I think you can also do it more elegantly with a CTE; not something I've played with yet, but you can read about it here: http://www.postgresql.org/docs/9.0/static/queries-with.html HTH, 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: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
I will put my support for David Johnston's proposal, in principle, though minor
details of syntax could be changed if using ! conflicts with something. --
Darren Duncan
David Johnston wrote:
On Mon, Jul 11, 2011 at 10:12 AM, Florian Pflug f...@phlo.org wrote:
On Jul11, 2011, at 07:08 , Darren Duncan wrote:
Christopher Browne wrote:
Vis-a-vis the attempt to do nested naming, that is ns1.ns2.table1,
there's a pretty good reason NOT to support that, namely that this
breaks relational handling of tables. PostgreSQL is a *relational*
database system, hence it's preferable for structures to be
relational, as opposed to hierarchical, which is what any of the
suggested nestings are.
Rather, the argument is that it was intentional for the structuring of
table naming to, itself, be relational, and changing that definitely has
some undesirable characteristics.
The need for recursive queries is the most obvious undesirable, but it's
not the only undesirable thing, by any means.
I do not see how recursive queries (really iteration of records) even enters
the picture...
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
I could give more examples but I hope the basic idea is obvious. The main
thing is that the namespace hierarchy usage is standardized in such a way
that pgAdmin and other GUI tools can reliably use for display purposes and
that search_path can be constructed in a more compact format so that every
schema and sub-schema is still absolutely referenced (you can even have the
SET command resolve search_path at execution time and then remain static
just like CREATE VIEW SELECT * FROM table.
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] Interesting article, Facebook woes using MySQL
Dne 11.7.2011 21:50, Gauthier, Dave napsal(a): http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ How would PG stack up in a usage situation like this? This article (and the slashdot discussion) was already mentioned in the pg-advocacy list http://archives.postgresql.org/pgsql-advocacy/2011-07/msg8.php although it's mostly about the slashdot discussion - misconceptions, falsehoods and flame baits presented there. I don't think the slashdot it worth reading, it's full of nonsense (not a big surprise) and it's 48 hours old (which means 'dead' in slashdot terms). Regarding the article itself, it contains very little information about the new SQL - in short it just says three things: (1) It's difficult and expensive to build ACID-compliant distributed system using traditional RDBMS, especially if you don't know in advance you need to design it like that. This is where Stonebraker pokes into MySQL (or rather how Facebook used it), and I guess about the same could be true for PostgreSQL. (2) The NoSQL may help you to solve this problem when you don't need a relational storage and you have to respect the CAP theorem. (3) The New SQL is said to be the cure, i.e. SQL with advantages of NoSQL and without the disadvantages. As much as I respect Stonebraker, I doubt this can be done without breaking the CAP theorem but maybe I'm missing something ... I personally see the article as a propagation of VoltDB, but that does not mean it's a bad product. I guess it's time to play with it a bit. regards 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] Interesting article, Facebook woes using MySQL
On Mon, Jul 11, 2011 at 2:50 PM, Gauthier, Dave dave.gauth...@intel.com wrote: http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ Well, Stonebraker is pitching (for the Nth time) a revolutionary platform, VoltDB, which naturally brings up concerns about bias. For example, it's not clear why 1,800 servers running mysql is necessarily a 'fate worse than death'. Reading the article I find myself asking, 'what is the problem that needs solving here?'. I bet postgres would do just fine for facebook although it would take a lot of tweaking to get maximal numbers. 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] Interesting article, Facebook woes using MySQL
On Mon, Jul 11, 2011 at 03:53:20PM -0500, Merlin Moncure wrote: example, it's not clear why 1,800 servers running mysql is necessarily a 'fate worse than death'. Speaking personally, I find even one server running mysql (if it's my responsibility) is pretty enervating. I can imagine 1,800 could be worse than death. 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
[GENERAL] ? about Composite Keys + ON DELETE/UPDATE SET NULL
Hi, Is there any way to effect behavior similar to the following: FOREIGN KEY (field1, field2) REFERENCES table2 (field1, field2) ON UPDATE CASCADE ON DELETE (SET field2 = NULL) -- leaving field1 with whatever value is currently holds With MATCH SIMPLE the NULL in field2 is sufficient to break the Foreign Key and let the record on table2 become deleted. Given the general disdain for composite keys I can see why this particular behavior has been overlooked but it does seem reasonable, in the presence of MATCH SIMPLE, to specify that only some of the constrained fields be affected by ON DELETE SET NULL. Some syntax like: ON DELETE SET NULL(fieldn [, fieldn+1 .]) would seem to be the most declarative way to accomplish this. Alternatively, having the ability to fire a trigger function would make custom behavior possible since the trigger function could just do a NEW.field2 = NULL and then return NEW. Like: ON DELETE CALL trigger_function(); In my particular use-case I have a field on the FK table (invoice number) and, in the presence of a (store id) it wants to enforce that the physical invoice exists for that particular store. Should the physical invoice become deleted I want to still leave the (invoice number) present but set the (store id) back to NULL. I know, and can consider, other possibilities but the first thing that came to mind was using ON DELETE SET NULL(field2) and so I figure I might as well toss it out here and see what others think. Thanks, David J.
[GENERAL] Accidentally truncated pg_type
I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? Regards, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Concurrent read from a partition table.
Hello, I’m having a problem with concurrent processing. 2 queries are accessing the same parent table that have 24 partitions. I see “shared lock is not granted “ for one of them on one of the children while the other query is running. Does the “ select from a parent table” make a lock on the children? How I can change it? The one of the queries runs hourly ( 8 min) on the server , the other one can be run by a user , and a few users can run the same query. Thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/Concurrent-read-from-a-partition-table-tp4577154p4577154.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] Interesting article, Facebook woes using MySQL
On Mon, Jul 11, 2011 at 12:50 PM, Gauthier, Dave dave.gauth...@intel.com wrote: http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ How would PG stack up in a usage situation like this? My sense is that Pg would stack up no better. I suspect to make this work at this scale you'd have to sacrifice a lot of RI checking etc. and probably resort to similar tricks as with MySQL. I am not convinced that VoltDB is a magic bullet either. I don't think you can guarantee both consistency and speed across a database of that size, so you end up having to sacrifice one or the other. ACID compliance shouldn't generate nearly as much overhead on inserts of facebook likes as just simple things like verifying that the post one is liking actually exists, etc. In theory column-oriented databases come out ahead on those reads, but I would expect more overhead on writes (random seek for each field written?). So it might solve some problems but whether it would create others and whether there was a positive tradeoff is a good question. 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] Accidentally truncated pg_type
On 12/07/11 08:12, Matthew Byrne wrote: I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? Do not attempt any recovery yet. STOP doing whatever you are doing. If any programs are accessing the database, stop them. Make a file-system level copy of your database ***NOW***. Put one duplicate on a CD, external hard disk or other media you can completely remove from your computer and put it somewhere safe. Keep the duplicate copy on your hard drive to attempt recovery with. Personally I'd make a copy, stop the postmaster, and make a second copy. That's just because I don't know which would work out better. Up to you. I don't suppose you have any backups of any older versions of the database? If they are, are they PITR backups or are they pg_dump backups? Have you already attempted any recovery steps? Document them in detail if you have. If this database is in any way important to you, you should consider hiring an experienced professional to assist you with recovery. See: http://www.postgresql.org/support/professional_support -- 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] Accidentally truncated pg_type
On 12/07/11 08:12, Matthew Byrne wrote: I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? Oh, once you've copied your database you should stop the postmaster and not start it again without further advice/instructions. The data in pg_type may not have been vacuumed or overwritten yet if you haven't been messing about trying to fix it before asking for help. -- 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: [HACKERS] [GENERAL] Creating temp tables inside read only transactions
On Mon, Jul 11, 2011 at 12:49 PM, David Johnston pol...@yahoo.com wrote:
I do not see how recursive queries (really iteration of records) even enters
the picture...
I agree, FWIW. If the feature was that desirable, we could look at
questions of implementation to make recursion either unnecessary or at
least well managed.
Right now I can emulate a hierarchical schema structure via a naming scheme
- for example schemabase_sub1_sub2_etc. I am simply looking for a formal
way to do the above AND also tell the system that I want all schemas under
schemabase to be in the search path. Heck, I guess just allowing for
simply pattern matching in search_path would be useful in this case
regardless of the presence of an actual schema hierarchy. Using LIKE
syntax say: SET search_path TO schemabase_sub1_% or something similar.
The only missing ability becomes a way for graphical tools to represent the
schema hierarchy using a tree-structure with multiple depths.
Right. Semantically myapp_schemaname_subschemaname is no less
hierarchical than myapp.schemaname.subschemaname. The larger issue is
that of potential ambiguity wrt cross-database references (I don't
have a lot of experience reading the SQL standards, but seeing how
different db's implement cross-db references suggests that the
standards contemplate semantic meaning to depth of the namespace).
I can see how adding . and .. and relative paths would confuse the issue
those are not necessary features of a multi-level schema depth.
The above, combined with a different separator for intra-level
namespace/schema delineation, would allow for an unambiguous way to define
and use a hierarchical schema with seemingly minimal invasion into the
current way of doing things. You could almost implement it just by requiring
a specific character to act as the separator and then construct the actual
schema using single-level literals and supporting functions that can convert
them into an hierarchy. In other words, the schema table would still only
contain one field with the full parent!child as opposed to (schema,
parent) with (VALUES('parent',null),('child','parent')).
In other words, if we use ! as the separator, any schema named
parent!child could be stored and referenced as such but then if you run a
getChildren(parent) function it would return child along with any other
schemas of the form parent!%. In this case the % sign could maybe only
match everything except ! and the * symbol could be used to match ! as
well.
Agreed that this would be helpful. I would personally have a lot of
use for this sort of feature, particularly with managing large numbers
of stored procedures. Right now I am using a double underscore which
is error-prone.
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] Accidentally truncated pg_type
On Mon, Jul 11, 2011 at 6:25 PM, Craig Ringer cr...@postnewspapers.com.au wrote: On 12/07/11 08:12, Matthew Byrne wrote: I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? Oh, once you've copied your database you should stop the postmaster and not start it again without further advice/instructions. The data in pg_type may not have been vacuumed or overwritten yet if you haven't been messing about trying to fix it before asking for help. Second the suggestion of copying everything. Of course with autovacuum the chances that things have been vacuumed is not 0 and may be fairly high depending on configuration. In addition to those suggestions, the obvious question is: Do you have backups? What do they contain? How old are they? What sort of backups do you have? 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
[GENERAL] query_to_xml nulls set to false
I am using query_to_xml with nulls set to false in postgresql 9.0.4. (I believe the behavior was also present in 8.4.) The documentation for query_to_xml says that if set to true, nulls with be treated with xsi:nil=true and An appropriate namespace declaration will be added to the result value. If false, columns containing null values are simply omitted from the output. This suggests to me that if set to false, there should be no added namespace declaration, but, in practice, the xsi namespace is present. Is this the designed, intentional behavior or accidental? Lynn Dobbs Chief Technical Officer CreditLink Corporation 858 496 1010 x 103
Re: [GENERAL] Accidentally truncated pg_type
On Tue, 2011-07-12 at 01:12 +0100, Matthew Byrne wrote: I have a large database full of irreplaceable data, and due to a ridiculous happenstance I accidentally executed this code (as a superuser, of course): DELETE FROM pg_catalog.pg_type; Now the database is *seriously* unhappy - every SQL command returns an error message. How do I get at my data? [ Only consider this after you've taken Craig's advice. ] Did you have any user-defined types or extensions? You might try something as simple as (on your throw-away experimental copy, of course): 1. Make a new cluster with initdb (or just connect to a different database, if that still works). 2. Load any extensions or user-defined types into that one, and make sure they get the same OIDs (or hack the output of the next step). 3. Copy out the contents of pg_type, including OIDs. 4. Copy that data back into your empty pg_type. 5. Try to do a logical backup, load that data into a fresh instance, and you might be OK. I haven't really thought this plan through, but that's the first thing I'd try (after doing file-level copies of everything, of course!). Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unexpected results with joins on dates
I have three tables. traffic, sales and dates. Both the traffic table and the sales table has multiple entries per date with each row representing the date, some subdivision, and the total. For example every day five divisions could be reporting their sales so there would be five entries in the sales table for that date. The dates table just has one field and it just has a date in it (unique). I set that up for testing purposes. I have the following query which I am trying to make sense of. select (select count(id) from sales) as sales_count, (select count(id) from traffic) as traffic_count, (select count(traffic.date) from traffic inner join sales on traffic.date = sales.date) as two_table_join_count, (select count(dates.date) from dates inner join traffic on dates.date = traffic.date inner join sales on sales.date = dates.date) as three_table_join_count; running this query gives me this result 169157; 49833 ;25121853; 25121853 On the third select (two table join) it doesn't matter if I change it to a right join, full join left outer join I get the same number so it looks like it's doing a cross join no matter what. It also doesn't matter if I do a select count(*) Could somebody explain what is happening here? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected results with joins on dates
If traffic has 5 records on a date and sales has 4 on the same date you would output 20 records for that date. Instead of dealing with the entire table just pick out a couple of dates and show the results of the join in detail instead of just counts. David J. On Jul 11, 2011, at 22:53, Tim Uckun timuc...@gmail.com wrote: I have three tables. traffic, sales and dates. Both the traffic table and the sales table has multiple entries per date with each row representing the date, some subdivision, and the total. For example every day five divisions could be reporting their sales so there would be five entries in the sales table for that date. The dates table just has one field and it just has a date in it (unique). I set that up for testing purposes. I have the following query which I am trying to make sense of. select (select count(id) from sales) as sales_count, (select count(id) from traffic) as traffic_count, (select count(traffic.date) from traffic inner join sales on traffic.date = sales.date) as two_table_join_count, (select count(dates.date) from dates inner join traffic on dates.date = traffic.date inner join sales on sales.date = dates.date) as three_table_join_count; running this query gives me this result 169157; 49833 ;25121853; 25121853 On the third select (two table join) it doesn't matter if I change it to a right join, full join left outer join I get the same number so it looks like it's doing a cross join no matter what. It also doesn't matter if I do a select count(*) Could somebody explain what is happening here? Thanks. -- 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
