Re: [GENERAL] Database INNOVATION

[Joe Conway]

On 10/19/2010 10:12 PM, Craig Ringer wrote:
> On 10/20/2010 12:35 PM, Brent Wood wrote:
>> Have a look at PL/R.

> In this case, when I say "graph" or "tree" I'm referring to the concept
> in the graph theory sense, not the "plot" sense. "object graph" not
> "image representation of data".

Analysis of graphs is available via PL/R as well. See:
  http://cran.r-project.org/web/views/gR.html

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Database INNOVATION

[Darren Duncan]

Craig Ringer wrote:

On 20/10/10 13:12, Darren Duncan wrote:

Never mind JSON.  You can fix the outer joins problem and other issues
simply by supporting relation-valued attributes, or in other words, row
field values that are rowsets.


You can for trees/forests yes. How would you handle more general graphs
with cycles or bidirectional relationships, where you still want to be
able to reconstruct them into a traversible graph client-side?

There are existing graph databases for this, of course, but I've
frequently wished to be able to use the power of SQL's query language
and reporting facilities with my data as well as being able to extract
it as (sub)graphs when needed. Using a graph database would usually cost
me ACID, full SQL support, and in most cases all those goodies like
triggers, constraints, etc as well.

I think there's a real use case for using regular relational storage
with a few SQL extensions to support returning graph-style rather than
row-set style results. Even the SQL extensions probably aren't necesary;
I suspect a (limited and somewhat slow) version could be done purely in
a PL under PostgreSQL, and I've been thinking about trying to prototype one.


And recursively.  Parent records in
outer rowset, child records inside.  And this is all perfectly normal
for the relational model, and SQL's differing from this is part of how
SQL is crippled and not really the relational model.  I demonstrate how
it might be better done with my Muldis D language. -- Darren Duncan


You'd really wow people if you could bang together a working JPA 2.0
backend for that, or a dialect for an existing provider like Hibernate.

Personally, I'd love to give someting like your Muldis-D query interface
a go if it could live within PostgreSQL as a contrib module, using the
regular Pg storage and just providing an alternative query facility.

Right now, it looks like it is a perl-all-the-way system, with no
interfaces for other languages and its own database storage system. In
its current form a quick glance at the docs doesn't demonstrate any
obvious advantage of using it over any of the existing well-established
graph databases or object-relational database systems.


Muldis D is a work-in-progress.  The system is specified in terms of a 
programming language of which multiple implementations could exist, essentially 
the same way as SQL is.  I fully intend for it to run on Postgres, using regular 
Pg storage (which may gain features along the way), and be useable both in the 
manner of a PL and for general DDL/DML/queries/etc.  I agree that it is best for 
the initial all-in attempt to be done as a contrib module, and I am happy that 
Pg already has so much of the infrastructure in place that is needed.  The 
first, Perl-all-the-way, system is just the reference implementation, but the 
Pg-contrib version would likely be the first one for serious use.


If you or anyone wants to work on making this a reality, I welcome it.

-- 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: [GENERAL] Database INNOVATION

[Craig Ringer]

On 20/10/10 13:12, Darren Duncan wrote:

> Never mind JSON.  You can fix the outer joins problem and other issues
> simply by supporting relation-valued attributes, or in other words, row
> field values that are rowsets.

You can for trees/forests yes. How would you handle more general graphs
with cycles or bidirectional relationships, where you still want to be
able to reconstruct them into a traversible graph client-side?

There are existing graph databases for this, of course, but I've
frequently wished to be able to use the power of SQL's query language
and reporting facilities with my data as well as being able to extract
it as (sub)graphs when needed. Using a graph database would usually cost
me ACID, full SQL support, and in most cases all those goodies like
triggers, constraints, etc as well.

I think there's a real use case for using regular relational storage
with a few SQL extensions to support returning graph-style rather than
row-set style results. Even the SQL extensions probably aren't necesary;
I suspect a (limited and somewhat slow) version could be done purely in
a PL under PostgreSQL, and I've been thinking about trying to prototype one.



> And recursively.  Parent records in
> outer rowset, child records inside.  And this is all perfectly normal
> for the relational model, and SQL's differing from this is part of how
> SQL is crippled and not really the relational model.  I demonstrate how
> it might be better done with my Muldis D language. -- Darren Duncan

You'd really wow people if you could bang together a working JPA 2.0
backend for that, or a dialect for an existing provider like Hibernate.

Personally, I'd love to give someting like your Muldis-D query interface
a go if it could live within PostgreSQL as a contrib module, using the
regular Pg storage and just providing an alternative query facility.

Right now, it looks like it is a perl-all-the-way system, with no
interfaces for other languages and its own database storage system. In
its current form a quick glance at the docs doesn't demonstrate any
obvious advantage of using it over any of the existing well-established
graph databases or object-relational database systems.

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.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] Database INNOVATION

[Craig Ringer]

On 10/20/2010 12:35 PM, Brent Wood wrote:
> Have a look at PL/R.
>
> You can embed a command to generate a graphic using R via a user defined
> SQL function,

In this case, when I say "graph" or "tree" I'm referring to the concept
in the graph theory sense, not the "plot" sense. "object graph" not
"image representation of data".

http://en.wikipedia.org/wiki/Graph_(mathematics)
http://en.wikipedia.org/wiki/Graph_theory

Sorry, I didn't even think to clarify my usage.

What I'm talking about is a way to query the database and obtain a
representation of matching tuples where each tuple is represented
exactly once, and referential relationships between tuples are included
in an efficient way.

For a simple tree or forest (ie a directed graph with no cycles) this
could be a XML/JSON/YAML/whatever document that uses nesting to
represent relationships.

For more complex graphs, it'd have to be a list of
XML/JSON/YAML/whatever representations of each tuple or (if Pg supported
it) multiple tabular result sets, one for each tuple type. An edge list
could be included to speed mapping out the inter-object references after
deserialization.

To say this would be nice when dealing with document-in-database storage
and certain types of ORM workload is quite an understatement. Getting
rid of all that horrid "multiply left join, filter and de-duplicate" or
"n+1 select" crap would be quite lovely. Sure, it's often better to use
sane SQL directly, but there are tasks for which ORMs or
document-database mappings are a real time and pain saver - it'd just be
nice to be able to teach the database their language.

Plus, that'd help shut up the "NoSQL" crowd and separate "NoSQL" from
"relaxed or no ACID shareded databases", two different things people
currently confuse.

In any case, thanks for the tip. It's nice to know the PL/R can be used
for such in-database processing when I *do* want to plot data.

--
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] Database INNOVATION

[Darren Duncan]

Craig Ringer wrote:
Now, personally, if we're talking "database innovation" what I'd like to 
see is a built-in way to get query results straight from the database as 
graphs of tuples and their relationships. Tabular result sets are poorly 
suited to some kinds of workloads, including a few increasingly common 
ones like document-oriented storage and use via ORMs. In particular, the 
way ORMs tend to do multiple LEFT OUTER JOINs and deduplicate the 
results or do multiple queries and post-process to form a graph is 
wasteful and slow. If Pg had a way to output an object graph (or at 
least tree) natively as, say, JSON, that'd be a marvellous option for 
some kinds of workloads, and might help the NoSQL folks from whining 
quite so much as well ;-)


Never mind JSON.  You can fix the outer joins problem and other issues simply by 
supporting relation-valued attributes, or in other words, row field values that 
are rowsets.  And recursively.  Parent records in outer rowset, child records 
inside.  And this is all perfectly normal for the relational model, and SQL's 
differing from this is part of how SQL is crippled and not really the relational 
model.  I demonstrate how it might be better done with my Muldis D language. -- 
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: [GENERAL] Database INNOVATION

[Brent Wood]

Have a look at PL/R.

You can embed a command to generate a graphic using R via a user defined SQL 
function, 

This example from 
http://www.varlena.com/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html

HTH

  Brent Wood

=
Graphs can be as easy as '123'. Here's an example where two columnsin a table 
are plotted against each other.
Create and populate the table using the following commands:
CREATE TABLE temp (x int, y int);

  INSERT INTO temp VALUES(4,6);INSERT INTO temp VALUES(8,3);INSERT INTO temp 
VALUES(4,7);INSERT INTO temp VALUES(1,5);INSERT INTO temp VALUES(7,8);INSERT 
INTO temp VALUES(2,3);INSERT INTO temp VALUES(5,1);INSERT INTO temp VALUES(9,4);
The function f_graph()generates the graph as a pdf document:
CREATE OR REPLACE FUNCTIONf_graph() RETURNS text AS 
'str <<- pg.spi.exec(''select x as "my a" ,y as"my b" from temp order by 
x,y'');pdf(''/tmp/myplot.pdf'');plot(str,type="l",main="GraphicsDemonstration",sub="Line
 Graph");dev.off();print(''done'');' 
LANGUAGE plr;
  Creating the graph by invoking this query:
SELECT f_graph();   





Craig Ringer said:

Now, personally, if we're talking "database innovation" what I'd like to 
see is a built-in way to get query results straight from the database as 
graphs of tuples and their relationships. Tabular result sets are poorly 
suited to some kinds of workloads, including a few increasingly common 
ones like document-oriented storage and use via ORMs. In particular, the 
way ORMs tend to do multiple LEFT OUTER JOINs and deduplicate the 
results or do multiple queries and post-process to form a graph is 
wasteful and slow. If Pg had a way to output an object graph (or at 
least tree) natively as, say, JSON, that'd be a marvellous option for 
some kinds of workloads, and might help the NoSQL folks from whining 
quite so much as well ;-)



-- 
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric 
Research Ltd.

Re: [GENERAL] [ANNOUNCE] PGDay Europe 2010 Registration Open

[JY Wang]

Hi 

Please count me out of the list of your subscription.

Best Regards
JY Wang
Senior Consultant / SDPS Korea Professional Services
T: +822-2185-1026 | M: +82-10-4308-1562 | F: +822-2185-1050 
www.gxs.com


-Original Message-
From: pgsql-announce-ow...@postgresql.org
[mailto:pgsql-announce-ow...@postgresql.org] On Behalf Of Magnus Hagander
Sent: Tuesday, October 19, 2010 10:11 PM
To: pgsql-announce; pgeu-general; pgsql-general@postgresql.org
Subject: [ANNOUNCE] PGDay Europe 2010 Registration Open

PGDay.EU is by far the largest PostgreSQL conference in Europe. This year it
will be held at the Millennium Hotel in Stuttgart, Germany. PGDay.EU is
a unique chance for developers to learn and network with fellow
professionals
that use and work with PostgreSQL. The event attracts developers, customers
and
vendors including EnterpriseDB, Dalibo, 2ndQuadrant and Servoy.

The event will span two days of talks, with more than 20 sessions in English
and 10 in German.  These sessions will be aimed at users, developers,
hackers
and decision makers in the PostgreSQL community. The event will also provide
in-depth training sessions in both German and English.


The registration for PGDay.EU 2010 is now open at
http://2010.pgday.eu/register.

We are still putting the final touches on the schedule, which you can see
incrementally at http://2010.pgday.eu/schedule.

The schedule for training has been finalized and is available at
http://2010.pgday.eu/training. Note that for the training, there are limited
seats available for each session on a first come first serve basis, so
register
early to avoid disappointment!

On behalf of the team, welcome to Stuttgart and PGDay.EU 2010!


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

---(end of broadcast)---
-To unsubscribe from this list, send an email to:

   pgsql-announce-unsubscr...@postgresql.org



-- 
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] install PostgreSQL 9 .0.1 issue using linux binary package .bin

[Tahir Tamba]

Hi Ashesh,

Thank you for pointing me out the right synthax to install PostgreSQL 9.0.1 
binary version on Ubuntu. I try it right away and i'll get back to you to tell 
you if everything is ok
Regards

Tahir Tamba
6632, Avenue de Gaspé
Montréal, H2S 2Y2




From: ashesh.va...@enterprisedb.com
Date: Wed, 20 Oct 2010 01:12:21 +0530
Subject: Re: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary 
package .bin
To: tahi...@hotmail.com
CC: pgsql-general@postgresql.org; dharmendra.go...@enterprisedb.com

Hi Tahir,

Can you please try the following command?
sudo 

NOTE: The 'sh' is removed from this command.



--


Thanks & Regards,Ashesh Vashi

EnterpriseDB INDIA: Enterprise Postgres Company
 
http://www.linkedin.com/in/asheshvashi



On Wed, Oct 20, 2010 at 12:48 AM, Tahir Tamba  wrote:












From: Dharmendra Goyal To: 
tahi...@hotmail.com

Cc: pgsql-gene...@postgresql.orgsubject: Re: install PostgreSQL 9 .0.1 issue 
using linux binary package .binDate: Tue, 19 Oct 2010 14:51:36 +0530

Message-id: 






Dear Dharmendra,

Thank you for your answer. First to annswer to your question I guest you want 
that I provide you the path of the binary package installer ???

Here it is: tta...@ubuntu:~$ ls -l 
'/home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin' 


-rwxrwxrwx 1 ttamba ttamba 44929834 2010-10-19 00:43 
/home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin

And for your second question: this is the command I used to install the binary.



sudo sh 'installer_pat' as usually done installing binary packages on Ubuntu

Regards
Tahir

> Hi Tahir,

> Can you please send out output of following command:
> ls -l installer_file
> also send the command which you are running to run the installer.

> Regards,


> From: Tahir Tamba 
> Date: Tue, Oct 19, 2010 at 6:15 AM
> Subject: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary
> package .bin
> To: pgsql-general@postgresql.org
>
>
>
> Hi,
>
> I downloaded PostgreSQL 9.0.1 binary package for Linux from Entreprise
> DB Web page, but when I try to install the binary package, I get the
> follwing message error:
> /home/ttamba/Desktop/postgresql-9.0.1-1-linux.bin: 1: Syntax error:
> "(" unexpected
> I'm using ubuntu 10.04 LTS distribution and before running the install
> using .bin package I anable the permission to read and write the .bin
> package.
>
> Any help to resolve this issue will be appreciated.
>
> Regards
>


Tahir Tamba
6632, Avenue de Gaspé
Montréal, H2S 2Y2


  

  

Re: [GENERAL] Database INNOVATION

[Craig Ringer]

On 19/10/2010 10:12 PM, Mauricio Chamati wrote:

> I am recomming

to you guys to have an special "Section" for Audit tables. As we
separate sequence in another seq, we should do the same for Audit
tables. They have their own behavior, are less used, and so on.


As a senior software architect, you should know how to write clearer 
descriptions of what you want :-P


Your suggestion is really broad. It's not clear exactly what you are 
suggesting or what you actually want, or even what kind of audit tables 
you're referring to.


Are you suggesting that there should also be a facility to put some 
tables outside the normal transactional flow, so that writes to them are 
retained when a transaction rolls back? (If so: this can be done, albeit 
hackishly, with dblink).


Are you suggesting support for audit logging of SELECTs? ie "ON SELECT" 
triggers or some similar mechanism?


Do you want separate storage and/or WAL logging of audit tables for 
performance reasons? If Pg ever supports separate WAL logging for 
different databases or different sets of tables, that'd be an attractive 
option for audit tables. As it is, you already can use tablespaces to 
put them on different storage.


Do you want built-in support for marking tables/columns as "audited" so 
the database system automatically manages recording of audit data to 
audit tables with no need to define triggers etc?


If not, what exactly is it that you *do* want?


Now, personally, if we're talking "database innovation" what I'd like to 
see is a built-in way to get query results straight from the database as 
graphs of tuples and their relationships. Tabular result sets are poorly 
suited to some kinds of workloads, including a few increasingly common 
ones like document-oriented storage and use via ORMs. In particular, the 
way ORMs tend to do multiple LEFT OUTER JOINs and deduplicate the 
results or do multiple queries and post-process to form a graph is 
wasteful and slow. If Pg had a way to output an object graph (or at 
least tree) natively as, say, JSON, that'd be a marvellous option for 
some kinds of workloads, and might help the NoSQL folks from whining 
quite so much as well ;-)




--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] Movable Type 5 and Postgres

[Matthew Hixson]

I've posted a short guide on how to get Movable Type 5, Postgres 9, and Tomcat 
7.0 working together.

http://www.greenskagitvalley.com/blog/movable-type-5-with-tomcat-70x-and-postgres-90.html

 -M@
-- 
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] drop view with out cascading the dependents

[Ben Carbery]

You can always recreate the dependent views in the same process, since you
won't lose any data. If there are also linked tables you could use COPY
before deleting and recreating, it's fairly fast.

But..does the structure of your data really need to change? Just guessing,
but it just sounds a little like adjusting the schema to suit an
application, not the data.

2010/10/20 Ravi Katkar 

> That's true . it wont work if we add remove the columns.
> I wanted to add column to view and change the data type to existing column
>
>

Re: [GENERAL] Database INNOVATION

[Scott Marlowe]

On Tue, Oct 19, 2010 at 1:17 PM, Greg Smith  wrote:
> Scott Marlowe wrote:
>>
>> On a side note, MySQL is GPL licensed and cannot simply be taken.  The
>> commercially licensed version is owned by Oracle / Sun, but the GPL
>> code is quite free to be hacked and released and use according to the
>> GPL.
>
> As the MySQL documentation is not GPL licensed, such hacks will have to
> start over from scratch in that area.  Good luck with that.

Actually, given the horrible docs for mysql (sorry but I can't find
anything in there half the time) that's not a wholly bad thing.  Plus
you can always get the docs from somewhere else, if you have to, I
guess.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Date comparison without a year

[Stanislav Orlenko]

Hello
There is a table with user reviews about hotels, every row has columns:
time_of_travel_begin and time_of_travel_end. User can select date range
(interval), for example 15 June - 10 July and DB request should return
all reviews for any years (for 15 June - 10 July 2010, 15 June - 10 July
2009 etc). For now I've constructed this request:

SELECT *
  FROM comments
  WHERE
  hotel_id = 4323
  AND
  (
TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
OR
TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
OR
(TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') <
TO_DATE('06-15', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end,
'MM-DD'), 'MM-DD') > TO_DATE('07-10', 'MM-DD'))
  )

and this select works.

But if first date of interval is in December and the second date of
interval is in January (for example interval 20 December - 10 January) -
it's a problem. Select will be looks like:

SELECT *
  FROM comments
  WHERE
  hotel_id = 4323
  AND
  (
TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
OR
TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD')
BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
OR
(TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') <
TO_DATE('12-20', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end,
'MM-DD'), 'MM-DD') > TO_DATE('01-10', 'MM-DD'))
  )

this select returns wrong results.

How can I retrieve all reviews from 20 Dec to 10 Jan for any year?

Thanks in advance

Re: [GENERAL] Database INNOVATION

[Chris Browne]

mcham...@gmail.com (Mauricio Chamati) writes:
> Postgree is the most amazing DB, even more it will be the only one that will
> remain free (the good ones) as MySQL has been taken. In order to move on with
> this project, as an Java Senior Architect, I am recomming to you guys to have
> an special "Section" for Audit tables. As we separate sequence in another seq,
> we should do the same for Audit tables. They have their own behavior, are less
> used, and so on. I got in the mailing list and am sending the email just to
> give this idea for you guys, keep in mind and implement as you want.

There is value to there being a tutorial (or such) available to explain
strategies for doing this.

I'm not so sure that this is something that properly belongs in the
Postgres reference documentation, as:

  - There are various strategies in the handling of auditing data, and
no forcibly normative/universal solutions;

  - This isn't an explanation of how to use Postgres (ergo part of the
"user guide" aspect of the documentation);

  - This isn't an aspect of the standard behaviour of Postgres (ergo
part of the "reference manual" aspect of the documentation).

It might be sort of neat to have another "Part" to the documentation
that consists of stuff like:
  - Design patterns
  - Design antipatterns (to avoid!)
  - Cookbook
  - Implementation strategies

(and there is overlap between those 4 things, to be sure!)

But that's not a portion of the Official Documentation that presently
exists, so I'm not sure it properly fits.

People have written articles on this sort of thing in the past,
including:

 - Elein Mustain, with material at varlena.com
 - Postgres Online Journal 
 - Articles blogged at Planet PostgreSQL
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://www3.sympatico.ca/cbbrowne/postgresql.html
"There are no threads in a.b.p.erotica, so there's no  gain in using a
threaded news reader."  -- (Unknown source)

-- 
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] install PostgreSQL 9 .0.1 issue using linux binary package .bin

[Ashesh Vashi]

Hi Tahir,

Can you please try the following command?
sudo 

NOTE: The 'sh' is removed from this command.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company



*http://www.linkedin.com/in/asheshvashi*


On Wed, Oct 20, 2010 at 12:48 AM, Tahir Tamba  wrote:

>
>- *From*: Dharmendra Goyal 
>- *To*: tahi...@hotmail.com
>- *Cc*: pgsql-general@postgresql.org
>- *Subject*: Re: install PostgreSQL 9 .0.1 issue using linux binary
>package .bin
>- *Date*: Tue, 19 Oct 2010 14:51:36 +0530
>- *Message-id*: <
>
> aanlktimi=yhy0c9a+ww9bacrjut5=bjbcnnhygohh...@mail.gmail.com
>>
>
> --
>
> Dear Dharmendra,
>
> Thank you for your answer. First to annswer to your question I guest you want 
> that I provide you the path of the binary package installer ???
>
> Here it is: tta...@ubuntu:~$ ls -l 
> '/home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin'
> *-rwxrwxrwx 1 ttamba ttamba 44929834 2010-10-19 00:43 
> /home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin
> *
> And for your second question: this is the command I used to install the 
> binary.
>
> *sudo sh 'installer_pat'* as usually done installing binary packages on Ubuntu
>
> Regards
> Tahir
>
>
> > Hi Tahir,
>
> > Can you please send out output of following command:
> > ls -l installer_file
> > also send the command which you are running to run the installer.
>
> > Regards,
>
>
> > From: Tahir Tamba 
> > Date: Tue, Oct 19, 2010 at 6:15 AM
> > Subject: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary
> > package .bin
> > To: pgsql-general@postgresql.org
> >
> >
> >
> > Hi,
> >
> > I downloaded PostgreSQL 9.0.1 binary package for Linux from Entreprise
> > DB Web page, but when I try to install the binary package, I get the
> > follwing message error:
> > /home/ttamba/Desktop/postgresql-9.0.1-1-linux.bin: 1: Syntax error:
> > "(" unexpected
> > I'm using ubuntu 10.04 LTS distribution and before running the install
> > using .bin package I anable the permission to read and write the .bin
> > package.
> >
> > Any help to resolve this issue will be appreciated.
> >
> > Regards
> >
>
>
>
> *Tahir Tamba*
> 6632, Avenue de Gaspé
> Montréal, H2S 2Y2
>
>
>

Re: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary package .bin

[Tahir Tamba]

From: Dharmendra Goyal To: 
tahi...@hotmail.comcc: pgsql-gene...@postgresql.orgsubject: Re: install 
PostgreSQL 9 .0.1 issue using linux binary package .binDate: Tue, 19 Oct 2010 
14:51:36 +0530Message-id: 







Dear Dharmendra,

Thank you for your answer. First to annswer to your question I guest you want 
that I provide you the path of the binary package installer ???

Here it is: tta...@ubuntu:~$ ls -l 
'/home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin' 
-rwxrwxrwx 1 ttamba ttamba 44929834 2010-10-19 00:43 
/home/ttamba/Bureau/postgresql-9.0.1-1-linux.bin

And for your second question: this is the command I used to install the binary.

sudo sh 'installer_pat' as usually done installing binary packages on Ubuntu

Regards
Tahir

> Hi Tahir,

> Can you please send out output of following command:
> ls -l installer_file
> also send the command which you are running to run the installer.

> Regards,


> From: Tahir Tamba 
> Date: Tue, Oct 19, 2010 at 6:15 AM
> Subject: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary
> package .bin
> To: pgsql-general@postgresql.org
>
>
>
> Hi,
>
> I downloaded PostgreSQL 9.0.1 binary package for Linux from Entreprise
> DB Web page, but when I try to install the binary package, I get the
> follwing message error:
> /home/ttamba/Desktop/postgresql-9.0.1-1-linux.bin: 1: Syntax error:
> "(" unexpected
> I'm using ubuntu 10.04 LTS distribution and before running the install
> using .bin package I anable the permission to read and write the .bin
> package.
>
> Any help to resolve this issue will be appreciated.
>
> Regards
>


Tahir Tamba
6632, Avenue de Gaspé
Montréal, H2S 2Y2


  

Re: [GENERAL] Database INNOVATION

[Greg Smith]

Scott Marlowe wrote:

On a side note, MySQL is GPL licensed and cannot simply be taken.  The
commercially licensed version is owned by Oracle / Sun, but the GPL
code is quite free to be hacked and released and use according to the
GPL.


As the MySQL documentation is not GPL licensed, such hacks will have to 
start over from scratch in that area.  Good luck with that.


--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book


--
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] NoSQL -vs- SQL

[David Fetter]

On Tue, Oct 19, 2010 at 12:36:44PM -0400, Chris Browne wrote:
> dp...@pgadmin.org (Dave Page) writes:
> > On Tue, Oct 12, 2010 at 2:58 AM, Peter C. Lai  wrote:
> >> On 2010-10-11 05:57:37PM -0600, David Boreham wrote:
> >>>   On 10/11/2010 5:46 PM, Carlos Mennens wrote:
> >>> > Just wondering how you guys feel about NoSQL and I just wanted to
> >>> > share the following article...
> >>> >
> >>> > http://www.linuxjournal.com/article/10770
> >>> >
> >>> > Looking to read your feedback and / or opinions.
> >>> http://www.xtranormal.com/watch/6995033/
> >>> (warning: may not be sfw).
> >>
> >> Someone should (or probalby has) made one that sounds exactly the same,
> >> except for replacign the Mongo guy with MySQL and the MySQL guy with
> >> PostgreSQL. That might be more apopros all around ;)
> >
> > Someone did indeed do that:
> > http://nigel.mcnie.name/blog/mysql-is-a-database (also nsfw, iirc)
> 
> Alas, while it's somewhat funny, it's mighty clear that it's a
> second-degree derivation, which rather diminishes its power.  
> 
> It gets confused as to who's the questioner, which yanks some sense out
> of it.  At the start, the MySQL aficionado is the speaker, taking
> questions, but at the end, somehow the Postgres guy ends up thanking
> everyone for their questions.
> 
> To make this work requires that it be rather carefully done; sadly,
> sufficient care doesn't seem to have been taken :-(.

Maybe they stored it in MySQL ;)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Database INNOVATION

[Scott Marlowe]

On Tue, Oct 19, 2010 at 8:12 AM, Mauricio Chamati  wrote:
> Folks,
> Postgree is the most amazing DB, even more it will be the only one that will
> remain free (the good ones) as MySQL has been taken.

On a side note, MySQL is GPL licensed and cannot simply be taken.  The
commercially licensed version is owned by Oracle / Sun, but the GPL
code is quite free to be hacked and released and use according to the
GPL.  Due to the idiocy of GPL licensing the connection libs, there
are issues with integrating it with non-GPL software, but it is most
certainly not taken.

I'm no MySQL fan, just wanted to clear that point up.

-- 
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] NoSQL -vs- SQL

[Chris Browne]

dp...@pgadmin.org (Dave Page) writes:
> On Tue, Oct 12, 2010 at 2:58 AM, Peter C. Lai  wrote:
>> On 2010-10-11 05:57:37PM -0600, David Boreham wrote:
>>>   On 10/11/2010 5:46 PM, Carlos Mennens wrote:
>>> > Just wondering how you guys feel about NoSQL and I just wanted to
>>> > share the following article...
>>> >
>>> > http://www.linuxjournal.com/article/10770
>>> >
>>> > Looking to read your feedback and / or opinions.
>>> http://www.xtranormal.com/watch/6995033/
>>> (warning: may not be sfw).
>>
>> Someone should (or probalby has) made one that sounds exactly the same,
>> except for replacign the Mongo guy with MySQL and the MySQL guy with
>> PostgreSQL. That might be more apopros all around ;)
>
> Someone did indeed do that:
> http://nigel.mcnie.name/blog/mysql-is-a-database (also nsfw, iirc)

Alas, while it's somewhat funny, it's mighty clear that it's a
second-degree derivation, which rather diminishes its power.  

It gets confused as to who's the questioner, which yanks some sense out
of it.  At the start, the MySQL aficionado is the speaker, taking
questions, but at the end, somehow the Postgres guy ends up thanking
everyone for their questions.

To make this work requires that it be rather carefully done; sadly,
sufficient care doesn't seem to have been taken :-(.
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxfinances.info/info/x.html
In case you weren't aware, "ad homineum" is not latin for "the user of
this technique is a fine debater." -- Thomas F. Burdick

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Tools for partitioning and query optimization

[Matt Harrison]

Hey Folks-

I've got 2 projects out that I'm finding useful, so I thought I'd share with
the wider postgres community.

The first is PgPartition [0].  This (python) tool eliminates the monotony of
dealing with partitions.  It generates SQL to create/index/remove/alter
partitions.

The second is PgTweak [1].  This is a somewhat nascent (also python)
project, but I hope for it to be more useful.  The main idea is to try out
different setting combinations and to see what effect they have on query
performance.  It does this right now.  I'd like for it to analyze the
EXPLAIN ANALYZE output and make somewhat intelligent suggestions.

Any feedback is appreciated.  Hopefully these tools are useful to others.

cheers,

matt

0 - http://github.com/mattharrison/PgPartition
1 - http://github.com/mattharrison/PgTweak

Re: [GENERAL] Problem with Crosstab - Allocating value to wrong column

[Joe Conway]

On 10/19/2010 03:07 AM, Stefan Schwarzer wrote:
> For one of the countries, I have a value for 2007, but not for 1960. 
> When using only the inner query, than I see one line: Andorra - 2007
> - 539 But when running the whole SQL, the value for year 2007 get's
> allocated to the year 1960. The table looks as follows:
> 
> name |y_1960|   y_2007
> Andorra  |   539|   NULL


That is documented behavior. See:
  http://www.postgresql.org/docs/8.4/interactive/tablefunc.html

> F.33.1.2. crosstab(text)
...
> The crosstab function produces one output row for each consecutive
> group of input rows with the same row_name value. It fills the output
> value columns, left to right, with the value fields from these rows.
> If there are fewer rows in a group than there are output value
> columns, the extra output columns are filled with nulls; if there are
> more rows, the extra input rows are skipped.

You probably want the other form of crosstab

> F.33.1.4. crosstab(text, text)

> The main limitation of the single-parameter form of crosstab is that
> it treats all values in a group alike, inserting each value into the
> first available column. If you want the value columns to correspond to
> specific categories of data, and some groups might not have data for
> some of the categories, that doesn't work well. The two-parameter form
> of crosstab handles this case by providing an explicit list of the
> categories corresponding to the output columns. 

HTH,

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] drop view with out cascading the dependents

[Ravi Katkar]

That's true . it wont work if we add remove the columns. 
I wanted to add column to view and change the data type to existing column 

-Original Message-
From: Grzegorz Jaśkiewicz [mailto:gryz...@gmail.com] 
Sent: Tuesday, October 19, 2010 7:50 PM
To: Tom Lane
Cc: Ravi Katkar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] drop view with out cascading the dependents

On Tue, Oct 19, 2010 at 3:12 PM, Tom Lane  wrote:
> Ravi Katkar  writes:
>> Is there any feature to drop the view with out cascading the dependents.
>
> No.  But why don't you use CREATE OR REPLACE VIEW?
>
only caveat is, it won't work if he adds/removes any columns. CREATE
OR REPLACE VIEW is rather useless in most practical implementations.



-- 
GJ

-- 
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] drop view with out cascading the dependents

[Grzegorz Jaśkiewicz]

On Tue, Oct 19, 2010 at 3:12 PM, Tom Lane  wrote:
> Ravi Katkar  writes:
>> Is there any feature to drop the view with out cascading the dependents.
>
> No.  But why don't you use CREATE OR REPLACE VIEW?
>
only caveat is, it won't work if he adds/removes any columns. CREATE
OR REPLACE VIEW is rather useless in most practical implementations.



-- 
GJ

-- 
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 INNOVATION

[Mauricio Chamati]

Folks,

Postgree is the most amazing DB, even more it will be the only one that will
remain free (the good ones) as MySQL has been taken. In order to move on
with this project, as an Java Senior Architect, I am recomming to you guys
to have an special "Section" for Audit tables. As we separate sequence in
another seq, we should do the same for Audit tables. They have their own
behavior, are less used, and so on. I got in the mailing list and am sending
the email just to give this idea for you guys, keep in mind and implement as
you want.

Regards.

Re: [GENERAL] drop view with out cascading the dependents

[Tom Lane]

Ravi Katkar  writes:
> Is there any feature to drop the view with out cascading the dependents.

No.  But why don't you use CREATE OR REPLACE VIEW?

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] Problem with initdb: creates database which do not exists

[David Fetter]

On Tue, Oct 19, 2010 at 08:48:13AM +0200, Torsten Zühlsdorff wrote:
> Thom Brown schrieb:
> 
> >initdb creates a database cluster, not a database.  [..]
> 
> Now i'm feeling like fool - this is so obviously. -.- I will stop
> posting stressed to the Usenet.

Yay, an NNTP user :)

We've all been there.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgres services on amazon

[Aljosa Mohorovic]

i was wondering if anybody is providing postgres services comparable
to http://aws.amazon.com/rds/ ?

Aljosa Mohorovic

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PGDay Europe 2010 Registration Open

[Magnus Hagander]

PGDay.EU is by far the largest PostgreSQL conference in Europe. This year it
will be held at the Millennium Hotel in Stuttgart, Germany. PGDay.EU is
a unique chance for developers to learn and network with fellow professionals
that use and work with PostgreSQL. The event attracts developers, customers and
vendors including EnterpriseDB, Dalibo, 2ndQuadrant and Servoy.

The event will span two days of talks, with more than 20 sessions in English
and 10 in German.  These sessions will be aimed at users, developers, hackers
and decision makers in the PostgreSQL community. The event will also provide
in-depth training sessions in both German and English.


The registration for PGDay.EU 2010 is now open at http://2010.pgday.eu/register.

We are still putting the final touches on the schedule, which you can see
incrementally at http://2010.pgday.eu/schedule.

The schedule for training has been finalized and is available at
http://2010.pgday.eu/training. Note that for the training, there are limited
seats available for each session on a first come first serve basis, so register
early to avoid disappointment!

On behalf of the team, welcome to Stuttgart and PGDay.EU 2010!


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Andrus]

1. Tuning wizard adds autovacuum = trueline even if
autovacuum is turned on:

#autovacuum = on   # Enable autovacuum subprocess?  'on'
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2010/10/18 11:19:56
autovacuum = true   # Enable autovacuum subprocess?  'on'

2. Wizards adds values without units. It is difficult to figure out how much 
memory changed settings really take.


How to force tuning wizard to add changes in real units if server supports 
them, e.q. with  KB or MB suffixes like in normal postgresql.conf file?


Andrus. 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Problem with Crosstab - Allocating value to wrong column

[Stefan Schwarzer]

Hi there,

I have a database with statistical tables which look like this:

id|year_start   |value
3 1960  736.1
3 1961  723.3

4 1960  123.4


Years can cover 40 years for each of the countries (ID) and each (variable) 
table .

Now, if for a given year there is no value, expressed not in form of NoData, 
but as missing line in the table, than I have a problem with this SQL: 

SELECT  
* 
FROM 
crosstab( 'SELECT 
COALESCE(c.name, ) AS name,  
d.year_start AS year, 
d.value AS value 
FROM 
co2_total_cdiac AS d 
RIGHT JOIN 
countries_view AS c ON c.id = 
d.id_country 
WHERE 
(d.year_start = 1960 OR d.year_start = 
2007 ) 
ORDER BY 
1,2;', 3) AS ct (name varchar, y_1960 
numeric, y_2007 numeric)


For one of the countries, I have a value for 2007, but not for 1960. When using 
only the inner query, than I see one line: Andorra - 2007 - 539
But when running the whole SQL, the value for year 2007 get's allocated to the 
year 1960. The table looks as follows:

name |y_1960|   y_2007
Andorra 539NULL



(Not sure if the copy/pasted part displays correct:

namey_1960  y_2007
Afghanistan 
415
716
Albania 
2026
4246
Algeria 
6166
140234
Andorra 
539
NULL
)


Can anyone give me a hint in which direction to search for a solution?

Thanks a lot!

Stefan

Re: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary package .bin

[Dharmendra Goyal]

Hi Tahir,

Can you please send out output of following command:
ls -l installer_file

also send the command which you are running to run the installer.

Regards,


> From: Tahir Tamba 
> Date: Tue, Oct 19, 2010 at 6:15 AM
> Subject: [GENERAL] install PostgreSQL 9 .0.1 issue using linux binary
> package .bin
> To: pgsql-general@postgresql.org
>
>
>
> Hi,
>
> I downloaded PostgreSQL 9.0.1 binary package for Linux from Entreprise
> DB Web page, but when I try to install the binary package, I get the
> follwing message error:
> /home/ttamba/Desktop/postgresql-9.0.1-1-linux.bin: 1: Syntax error:
> "(" unexpected
> I'm using ubuntu 10.04 LTS distribution and before running the install
> using .bin package I anable the permission to read and write the .bin
> package.
>
> Any help to resolve this issue will be appreciated.
>
> Regards
>
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



-- 
Dharmendra Goyal
Senior Software Engineer
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +91-20-30589493
Mobile: +91-9552103323

Website: http://www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are not
the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.