[GENERAL] Problem with WITH RECURSIVE

2011-05-22 Thread Frank Millman 
Hi all

I am running PostgreSQL 9.0.3 on Fedora 14.

I am trying to use WITH RECURSIVE on an adjacency list. It is mostly
working, but I have hit a snag.

CREATE TABLE departments (
  row_id SERIAL PRIMARY KEY,
  code VARCHAR NOT NULL
  parent_id INT REFERENCES departments,
  description VARCHAR NOT NULL);

I want to create a query that outputs the data in a 'nested' sequence, the
same sequence that a 'nested set' would produce.

The technique I am trying is to create a computed column called 'seq' - for
the anchor select, it consists of the root's 'code', and for each iteration
I append the next level's 'code', separated by '\'. At the end, I order by
'seq'.

Here is my attempt -

WITH RECURSIVE all_depts AS (
  SELECT row_id, code, description, parent_id,
0 AS level, CAST(code AS VARCHAR) AS seq
  FROM departments
  WHERE code = 'root'
UNION ALL
  SELECT a.row_id, a.code, a.description,
a.parent_id, b.level+1,
CAST(b.seq || '\' || a.code AS varchar) AS seq
FROM departments a, all_depts b
WHERE b.row_id = a.parent_id)
SELECT * FROM all_depts ORDER BY seq

I added the two 'CAST ... AS VARCHAR' in an attempt to fix the following
error, but it made no difference.

When I run it, this is the error message that appears -

==
recursive query "all_depts" column 6 has type character varying(999) in
non-recursive term but type character varying overall

HINT: Cast the output of the non-recursive term to the correct type.
==

As explained above, I tried adding a CAST, but it did not help.

Here are two additional snippets of information that may be of use -

1. I ran the query 'manually', by creating the tables 'fmtemp', 'fmwork',
and 'fminter', and following the sequence explained in the documentation.
This ran correctly without errors.

2. I tried exactly the same exercise using MS SQL SERVER 2005, with syntax
suitably adjusted. Before adding the CAST's, it also gave an error - "Types
don't match between the anchor and the recursive parts in column 'seq' ...".
After adding the CAST's, it ran correctly.

Any assistance will be appreciated.

Frank Millman



-- 
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 WITH RECURSIVE

2011-05-22 Thread Frank Millman 
 

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Frank Millman
> Sent: 22 May 2011 11:22
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Problem with WITH RECURSIVE
> 
> Hi all
> 
> I am running PostgreSQL 9.0.3 on Fedora 14.
> 
> I am trying to use WITH RECURSIVE on an adjacency list. It is mostly
> working, but I have hit a snag.
> 

Please ignore this - I have found my error.

I have just realised that I created the original table with column types of
VARCHAR(999). I deleted everything and recreated it using VARCHAR, and
everything works fine.

Sorry to waste your time.

Frank


-- 
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] Syntax Error for "boolean('value')" Type Casting

2011-05-22 Thread Alban Hertroys 
On 21 May 2011, at 21:17, David Johnston wrote:

> SELECT boolean('true')
> 
>>> SQL Error: ERROR:  syntax error at or near "("
>>> LINE 1: SELECT boolean('true')
>>> ^
> 
> The following work as expected:
> 
> SELECT bool('true')

(...)

> While I'm here.is there any downside to using the "type(value)" form of
> casting versus other forms?

You're comparing apples and oranges here, you're not using a cast at all in 
fact.

Instead, you're calling the transformation function that's used by the cast. It 
just happens to be called the same as the type in some cases, which is why 
boolean(value) doesn't work.

Now calling such functions will probably work just the same as the cast in most 
cases, but there will be some discrepancies, as follows from reading: 
http://www.postgresql.org/docs/9.0/interactive/sql-createcast.html

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4dd8e3fe11921124321257!



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

Re: Fwd: [GENERAL] Unable to Install - "unable to write inside TEMP environment variable path"

2011-05-22 Thread Wei 
Can anyone help? 
On May 20, 2011, Wei  wrote:

 Forwarded message From: Wei Date: May 20, 2011Subject: [GENERAL] Unable to Install - "unable to write inside TEMP environment variable path"To: pgsql-general@postgresql.orgWhen I install the 9.0.x version on my Window Visita Laptop, I get the error. The "Windows Scripting Host" is up. So it is not the topic of this article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.htmlHow to resolve this problem?Thanks.- w-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread jun yang 
we don't need se-linux function now ,so it is not the good idea, and
se-linux don't available on windows.

2011/5/22 Pavel Stehule :
> Hello
>
> 2011/5/22 jun yang :
>> now all the question:
>> 1.how start a procedure or a script after postgresql start.
>> 2.how to get notify when a table created.
>> 3.how to get notify when a database created.
>>
>
> Probably it isn't possible with Pg 9.0 and older. Maybe it is possible
> with callbacks for SE-Linux support in 9.1, but you have to write
> module in C.
>
> Regards
>
> Pavel Stehule
>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread jun yang 
2011/5/22 Scott Marlowe :
> On Sat, May 21, 2011 at 10:57 PM, jun yang  wrote:
>> now all the question:
>> 1.how start a procedure or a script after postgresql start.
>
> Do you need this stored procedure or script to always run at pg
> database start?  Or every time a client connects to the database?  Or
> every  minute, or every hour or every day?
i am just want pg execute the given stored procedure every time after
pg sucess start.
>
>> 2.how to get notify when a table created.
>> 3.how to get notify when a database created.
>
> Set db to log all dll, scrape logs, email timestamp from table or db
> creation.  Do you need more info other than just that it was created?
>
well,it may be not what i want,
if i can start a procedure after pg start,then i would like to get
notify when new object create,of course,i should be subcribe first.
now what i interest is the database created, when a new database
created,i want to init some schme and table,index in the it.

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

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)

2011-05-22 Thread Stefan Keller 
Hi Tom, hi all

Thanks, Tom, for your tipps. You answered 2011/5/1:
> (...), and there's no point in having the
> index column contents be the entire tags value (which is what's leading
> to the failure).  Consider
>
> create index planet_osm_point_amenity on planet_osm_point ((tags->amenity));

To get a more general purpose index I tried also:

CREATE INDEX planet_osm_point_tags ON planet_osm_point USING gist(tags);
-- ERROR: invalid hstore value found
-- SQL state: XX000

And I'm really interested in being able to use GIST. GIST is also
recommended here:
http://www.bostongis.com/PrinterFriendly.aspx?content_name=loading_osm_postgis

But I still get error 'invalid hstore value found' on my machine -
whatever I do!

I tried hard to find out the reason and also to remedy the cause of
this failure.
For example I did an update like this: UPDATE planet_osm_point SET
tags = hstore(hstore_to_array(tags));
...with no success.

I'm running "PostgreSQL 9.1alpha1, compiled by Visual C++ build 1500,
32-bit" on Windows XP SP3.
I have a dump of the table/database at hand to anyone who is
interested in this possible bug.

Yours, Stefan

2011/5/1 Tom Lane :
> Stefan Keller  writes:
>> I'm doing an equality search with success with the '->' operator on
>> the same field 'tags' like in this query (1):
>
>> -- Count all restaurants in database ("amenity = restaurant''):
>> select count(*) from osm_all_v
>> where hstore(tags)->'amenity'='restaurant'
>
>> This query 1 is reasonably fast and could be accelerated using this
>> functional index:
>> CREATE INDEX planet_osm_point_tags_restaurant
>>   ON planet_osm_point
>>   USING btree (tags)
>>   WHERE (tags -> 'amenity'::text) = 'restaurant'::text;
>
> This index seems a bit carelessly defined.  There's no need to confine
> its usefulness to exactly that query, and there's no point in having the
> index column contents be the entire tags value (which is what's leading
> to the failure).  Consider
>
> create index planet_osm_point_amenity on planet_osm_point ((tags->amenity));
>
> which will work for the above query and any other that's looking for a
> specific value of tags->amenity.
>
>> SELECT MIN(keys.key), hstore(p.tags)->keys.key, count(*)
>> FROM planet_osm_point p, (
>>   SELECT key, count(*)
>>   FROM (
>>     SELECT (each(tags)).key FROM planet_osm_point
>>   ) AS stat
>> GROUP BY key HAVING count(*) > 1
>>   AND key NOT LIKE 'note:%'
>>   ...
>>   AND key NOT IN
>> ('ele','ref','url','website','email','maxspeed','converted_by', ... )
>> ) keys
>> WHERE hstore(p.tags)->keys.key >''
>> GROUP BY hstore(p.tags)->keys.key HAVING count(*) > 1
>> ORDER by 1,3 desc
>
> It's pretty much useless to think about indexes for queries like this.
> If it's going to scan the whole table anyway, as this surely is, then
> an index is not going to make it faster.
>
>                        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] Syntax Error for "boolean('value')" Type Casting

2011-05-22 Thread David Johnston 


On May 22, 2011, at 6:22, Alban Hertroys  
wrote:

> On 21 May 2011, at 21:17, David Johnston wrote:
> 
>> SELECT boolean('true')
>> 
 SQL Error: ERROR:  syntax error at or near "("
 LINE 1: SELECT boolean('true')
^
>> 
>> The following work as expected:
>> 
>> SELECT bool('true')
> 
> (...)
> 
>> While I'm here.is there any downside to using the "type(value)" form of
>> casting versus other forms?
> 
> You're comparing apples and oranges here, you're not using a cast at all in 
> fact.
> 
> Instead, you're calling the transformation function that's used by the cast. 
> It just happens to be called the same as the type in some cases, which is why 
> boolean(value) doesn't work.
> 
> Now calling such functions will probably work just the same as the cast in 
> most cases, but there will be some discrepancies, as follows from reading: 
> http://www.postgresql.org/docs/9.0/interactive/sql-createcast.html
> 
> Alban Hertroys
> 
> 

I did end up finding the notes on the how's and caveats of using the direct 
method version for casting.  I also tried "boolean" and indeed got the 
"function not found" error.  All that said, it seems that one could add the 
Boolean function, even if it just calls bool, to get consistent behavior.

Doesn't matter to me at this point since I've decided to use the SQL conforming 
syntax of CAST(v as type).

Thank you the response though

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] repost: unable to install PG

2011-05-22 Thread Wei 
When I install the 9.0.x version on my Window Visita Laptop, I get the error. 
The "Windows Scripting Host" is up. So it is not the topic of this 
article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html
 

How to resolve this problem?

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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread Scott Marlowe 
On Sun, May 22, 2011 at 6:49 AM, jun yang  wrote:
> 2011/5/22 Scott Marlowe :
>> On Sat, May 21, 2011 at 10:57 PM, jun yang  wrote:
>>> now all the question:
>>> 1.how start a procedure or a script after postgresql start.
>>
>> Do you need this stored procedure or script to always run at pg
>> database start?  Or every time a client connects to the database?  Or
>> every  minute, or every hour or every day?
> i am just want pg execute the given stored procedure every time after
> pg sucess start.

Then just add the logic to run it from your init script.If you're
running something like ubuntu there's a script in /etc/init.d to start
postgres you can hack up to add a new step at the end.  Not sure
that's the best place but it'll work.

>>> 2.how to get notify when a table created.
>>> 3.how to get notify when a database created.
>>
>> Set db to log all dll, scrape logs, email timestamp from table or db
>> creation.  Do you need more info other than just that it was created?
>>
> well,it may be not what i want,
> if i can start a procedure after pg start,then i would like to get
> notify when new object create,of course,i should be subcribe first.
> now what i interest is the database created, when a new database
> created,i want to init some schme and table,index in the it.

If you just need each new db to have certain tables, then put them in
template1 and when you create a new db they'll be there.  As is often
the case, instead of asking how to do very specific things, you're
better off telling us what you're trying to accomplish in the bigger
sense so we can recommend better ways to accomplish what you want.

Note that PostgreSQL does NOT support triggers on system tables and
objects so you'll have to find some way of monitoring pg logs if you
want to know when things like new tables are created.  Again, give us
the bigger picture and we can likely be more helpful.

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

[GENERAL] trigger - dynamic WHERE clause

2011-05-22 Thread Tarlika Elisabeth Schmitz 
EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
|| ' FOR UPDATE;';

I am generating the whereclause dynamically as the number of columns
queried varies. 

Am I right in assuming that I cannot use EXECUTE ... USING in this
scenario?

-- 

Best Regards,
Tarlika Elisabeth Schmitz

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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread Darren Duncan 

John R Pierce wrote:

On 05/21/11 10:41 PM, Darren Duncan wrote:
Well, if you can run a stored procedure automatically when Postgres 
starts, that looks like a necessary step to being able to implement an 
entire application inside Postgres.


Starting Postgres is running the application.  The analogy is that 
Postgres is the VM/language interpreter and the stored procedure is 
the script to run.


Now if said stored procedure has access to features that collectively 
let it be computationally complete, including arbitrary user I/O, then 
you're done.


adding a

psql -d dbname -c "select myfunc()" &

to your postgres service start script would satisfy this


Good that this at least is possible, and ostensibly it is good enough.

I was actually thinking of something more on the line of a trigger, such that 
the system allows triggers to respond to a wide variety of stimulus, such as the 
stimulus of the DBMS starting up, rather than just the stimulus of 
data-manipulating a table.  For the purpose I mention, ideally the user wouldn't 
have to know the name of the main program routine, but would just know, its the 
database or cluster.  You could package your database cluster and say that *is* 
the application.


...but your entire application would be running in a single 
transaction.  I don't think thats a good thing.


Absolutely.  But if the kind of stored procedures were supported that can do 
anything a database client can do, including transaction control statements, 
then the main program routine would typically be one of those.


-- 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] Unable to Install - "unable to write inside TEMP environment variable path"

2011-05-22 Thread Alban Hertroys 
On 22 May 2011, at 24:08, Wei wrote:

> Can anyone help? 

According to the error message there's a problem with your TEMP directory, as 
specified in the %TEMP% environment variable. Perhaps the postgres user isn't 
allowed to write there or the disk is full. Something like that.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4dd94d7711929271551138!



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

Re: [GENERAL] Unable to Install - "unable to write inside TEMP environment variable path"

2011-05-22 Thread John R Pierce 

On 05/22/11 10:52 AM, Alban Hertroys wrote:

Can anyone help?

According to the error message there's a problem with your TEMP directory, as 
specified in the %TEMP% environment variable. Perhaps the postgres user isn't 
allowed to write there or the disk is full. Something like that.


note too, thats the %TEMP% variable in the context of the service, NOT 
neccessarily the desktop's %TEMP% ... this is not easy to get at and 
view or modify in Windows.





--
john r pierceN 37, W 123
santa cruz ca mid-left coast


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

Re: [GENERAL] trigger - dynamic WHERE clause

2011-05-22 Thread Pavel Stehule 
Hello

2011/5/22 Tarlika Elisabeth Schmitz :
> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
> || ' FOR UPDATE;';
>
> I am generating the whereclause dynamically as the number of columns
> queried varies.
>
> Am I right in assuming that I cannot use EXECUTE ... USING in this
> scenario?
>

why not? You can use it - just USING has a fixed numbers of
parameters, so you should to use a arrays.

Regards

Pavel Stehule

> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread jun yang 
2011/5/23 Darren Duncan :
> John R Pierce wrote:
>>
>> On 05/21/11 10:41 PM, Darren Duncan wrote:
>>>
>>> Well, if you can run a stored procedure automatically when Postgres
>>> starts, that looks like a necessary step to being able to implement an
>>> entire application inside Postgres.
>>>
>>> Starting Postgres is running the application.  The analogy is that
>>> Postgres is the VM/language interpreter and the stored procedure is the
>>> script to run.
>>>
>>> Now if said stored procedure has access to features that collectively let
>>> it be computationally complete, including arbitrary user I/O, then you're
>>> done.
>>
>> adding a
>>
>>    psql -d dbname -c "select myfunc()" &
>>
>> to your postgres service start script would satisfy this
>
> Good that this at least is possible, and ostensibly it is good enough.
>
> I was actually thinking of something more on the line of a trigger, such
> that the system allows triggers to respond to a wide variety of stimulus,
> such as the stimulus of the DBMS starting up, rather than just the stimulus
> of data-manipulating a table.  For the purpose I mention, ideally the user
> wouldn't have to know the name of the main program routine, but would just
> know, its the database or cluster.  You could package your database cluster
> and say that *is* the application.

wow,you go far ahead of me,it's interesting. since pg has so many
procedure language,pl/perl,pl/python etc.

>
>> ...but your entire application would be running in a single transaction.
>>  I don't think thats a good thing.
>
> Absolutely.  But if the kind of stored procedures were supported that can do
> anything a database client can do, including transaction control statements,
> then the main program routine would typically be one of those.
>
> -- 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
>

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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread John R Pierce 

On 05/22/11 10:45 AM, Darren Duncan wrote:
...but your entire application would be running in a single 
transaction.  I don't think thats a good thing.


Absolutely.  But if the kind of stored procedures were supported that 
can do anything a database client can do, including transaction 
control statements, then the main program routine would typically be 
one of those. 


yes, but postgres doesn't support the idea of stored procedures callable 
outside of transactions, so I don't know how this could be implemented 
without some major rework of the core engine.


for the sake of the novices amongst us, let me clarify my earlier 
statement that a single long running transaction is not a good thing.
Vacuum can not free up tuples newer than the oldest pending 
transaction.   This will put quite a lot of hurt on a update intensive 
database over a period of hours or days.



--
john r pierceN 37, W 123
santa cruz ca mid-left coast


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

Re: Fwd: [GENERAL] Unable to Install - "unable to write inside TEMP environment variable path"

2011-05-22 Thread Craig Ringer 

On 22/05/2011 6:08 AM, Wei wrote:

Can anyone help?


Please don't just forward a message you already sent to re-post it. Most 
people will ignore it, as you discovered.


Make the minimal effort required to say "I asked this earlier and didn't 
hear back. I've done a bunch of Google searching, haven't had any luck 
finding the answer, and am really stuck. Would anyone mind taking a 
moment to have a look? Here's a detailed list of what I've tried to do."


Yes, that requires doing some work for yourself. A quick search should 
have found information including this:


http://stackoverflow.com/questions/5224042/postgresql-9-install-on-winxp-unable-to-write-inside-temp-environment-path

and this:

  http://wiki.postgresql.org/wiki/Troubleshooting_Installation

and of course this:

  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

... all of which would've helped you ask a question with enough 
information that someone could actually help you - or answer it yourself.


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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread jun yang 
2011/5/22 Scott Marlowe :
> On Sun, May 22, 2011 at 6:49 AM, jun yang  wrote:
>> 2011/5/22 Scott Marlowe :
>>> On Sat, May 21, 2011 at 10:57 PM, jun yang  wrote:
 now all the question:
 1.how start a procedure or a script after postgresql start.
>>>
>>> Do you need this stored procedure or script to always run at pg
>>> database start?  Or every time a client connects to the database?  Or
>>> every  minute, or every hour or every day?
>> i am just want pg execute the given stored procedure every time after
>> pg sucess start.
>
> Then just add the logic to run it from your init script.    If you're
> running something like ubuntu there's a script in /etc/init.d to start
> postgres you can hack up to add a new step at the end.  Not sure
> that's the best place but it'll work.
>
 2.how to get notify when a table created.
 3.how to get notify when a database created.
>>>
>>> Set db to log all dll, scrape logs, email timestamp from table or db
>>> creation.  Do you need more info other than just that it was created?
>>>
>> well,it may be not what i want,
>> if i can start a procedure after pg start,then i would like to get
>> notify when new object create,of course,i should be subcribe first.
>> now what i interest is the database created, when a new database
>> created,i want to init some schme and table,index in the it.
>
> If you just need each new db to have certain tables, then put them in
> template1 and when you create a new db they'll be there.  As is often
> the case, instead of asking how to do very specific things, you're
> better off telling us what you're trying to accomplish in the bigger
> sense so we can recommend better ways to accomplish what you want.
>
> Note that PostgreSQL does NOT support triggers on system tables and
> objects so you'll have to find some way of monitoring pg logs if you
> want to know when things like new tables are created.  Again, give us
> the bigger picture and we can likely be more helpful.
>
what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.
when web front has activity,the data send to message broker and  will
route to pg then saved,it may be fire some trigger and some messge
send to message broker then to web front or collect data terminal
when collect data terminal has data,it send to broker.
through the route ability of message broker,the message send to it
will be send to right pg instance,log data to log pg instance,finance
data to finance pg instance,repair data to repair pg instance etc.
message broker has cluster and federation,pg has hot standby and sync
replication, deployment will be easier for the life after the first
time.
the prupose of get notify of object create result,first is to monitor
the system status,second is want to do some special init action with
such a object.

-- 
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] repost: unable to install PG

2011-05-22 Thread Craig Ringer 

On 22/05/2011 10:28 PM, Wei wrote:

When I install the 9.0.x version on my Window Visita Laptop, I get the error. The 
"Windows Scripting Host" is up. So it is not the topic of this article: 
http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html


Tip for mailing list communication: Don't say "the error". Explain the 
exact text of the error. You *did* do one of the other really important 
things, which is to always link to any article or documentation when you 
mention it so people know exactly what you are talking about.


The usual culprit in this situation is virus scanning software. Consider 
disabling it for the installation.


By the way, my previous message to you was a brisk to the point of being 
borderline rude. That wasn't fair; you've clearly done at least a bit of 
reading, and just need to work on your communication skills. It's easy 
for me as a lifetime English speaker who's been on mailing lists for a 
long time to think things are easy and obvious when they are not and to 
get frustrated - but that frustration isn't always reasonable when I 
look at things from where you might be coming from.


Another random little tip: You will also discover that many people will 
not see your message if you reply to an existing, unrelated thread 
they're not interested in. This is true even if you change the subject 
line, because many email programs "thread" messages using hidden 
information on which message is a reply to which other message. Nobody 
expects you to know that and it's not important, but it *is* helpful to 
know, because it means that creating a blank new message to the mailing 
list will be more likely to be read than a reply to an unrelated topic. 
Weird, isn't it?


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

Fwd: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread jun yang 
-- Forwarded message --
From: jun yang 
Date: 2011/5/23
Subject: Re: [GENERAL] how to start a procedure after postgresql started.
To: Craig Ringer 


2011/5/23 Craig Ringer :
> On 23/05/2011 9:37 AM, jun yang wrote:
>
>> what we want to do is explore the ability to move the system to the
>> architecture like below:
>> some pg---message broker(qpid)---(web front and some collect data
>> terminal,some business logic server,some system status monitor)
>> when pg start it subscribe to qpid then process the message send to it.
>> when pg is down,the important message is saved in message broker.
>
> It's probably going to be a *lot* easier and more reliable to have something
> sitting between Pg and the message broker. It can monitor Pg, and
> unsubscribe from the broker when Pg is unavailable, then re-subscribe when
> Pg becomes available again.
>
yes,you are right,so it will be nice if  pg can start the one between
pg  and broker.

> Putting it inside the PostgreSQL process space won't be especially easy,
> because PostgreSQL doesn't support true stored procedures and doesn't have
> any kind of built-in scheduler/event handler that can invoke them without
> application involvement. Because of that, you'd probably have to make
> significant changes to Pg's innards to make it work how you want.
>
actually, we will write the procedure in pl/python,then fork a new
thread or a new process which is easy.

> There's been discussion of adding the ability for the postmaster to start
> helper daemons, and if that were merged you could use a helper started
> alongside the postmaster to do the work. Right now, though, you're better
> off doing things how PgAgent etc do it, that is out-of-process via a regular
> Pg connection.
>
then the one sitting between pg and borker is a helper daemon,it is
great,more info about that?
PgAgent is nice,i am just wondering why it can't be integrated in
standard pg install,cause security? functionality?
if helper daemon integrated in pg,the PgAgent can be a helper daemon too.
i'd like helper daemon can operate like windows service,you can
disable it,make it mannual start, or auto start with pg.
> --
> 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

Re: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread Darren Duncan 

John R Pierce wrote:

On 05/22/11 10:45 AM, Darren Duncan wrote:
Absolutely.  But if the kind of stored procedures were supported that 
can do anything a database client can do, including transaction 
control statements, then the main program routine would typically be 
one of those. 


yes, but postgres doesn't support the idea of stored procedures callable 
outside of transactions, so I don't know how this could be implemented 
without some major rework of the core engine.


Well, one can dream.  I also anticipate that this limitation will be gone some 
day.  I may even help remove it some day if it comes to 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: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread Craig Ringer 

On 23/05/2011 9:37 AM, jun yang wrote:


what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.


It's probably going to be a *lot* easier and more reliable to have 
something sitting between Pg and the message broker. It can monitor Pg, 
and unsubscribe from the broker when Pg is unavailable, then 
re-subscribe when Pg becomes available again.


Putting it inside the PostgreSQL process space won't be especially easy, 
because PostgreSQL doesn't support true stored procedures and doesn't 
have any kind of built-in scheduler/event handler that can invoke them 
without application involvement. Because of that, you'd probably have to 
make significant changes to Pg's innards to make it work how you want.


There's been discussion of adding the ability for the postmaster to 
start helper daemons, and if that were merged you could use a helper 
started alongside the postmaster to do the work. Right now, though, 
you're better off doing things how PgAgent etc do it, that is 
out-of-process via a regular Pg connection.


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

Re: Fwd: [GENERAL] how to start a procedure after postgresql started.

2011-05-22 Thread John R Pierce 

On 05/22/11 7:14 PM, jun yang wrote:

actually, we will write the procedure in pl/python,then fork a new
thread or a new process which is easy


it would have to be a top level process, as you can't have multiple 
threads within a single postgres service connection interacting with 
postgres unless you want it all to blow up in your face.



--
john r pierceN 37, W 123
santa cruz ca mid-left coast


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

[GENERAL] Where are plpy.execute python commands issued?

2011-05-22 Thread Michael McInnis 

I've seen numerous references to this syntax but haven't found where you issue 
the command.

Tried it in a testpython.py file, no luck.
Can't find a plpy file anywhere.

I know it's going to be simple but need help.








rv = plpy.execute("SELECT * FROM my_table", 5)
Thanks

Michael McInnis
6033 44th Ave. N.E.
Seattle, WA 98115
206 517-4701

Re: [GENERAL] Memcached for Database server

2011-05-22 Thread Craig Ringer 
On 20/05/11 17:33, Adarsh Sharma wrote:
> Craig Ringer wrote:
>>
>> category_id   Record_id  fields
>> 7821 {Village:adasrpur, SOI:media, Heading:CM dies
>>   
> 
> Yes , but slight modification , I want Village, SOI Heading as column
> names and adasrpur,media and CM dies their values as there may 1000 of
> rows of it.

You're out of luck, then. You want a view with dynamic columns, and
PostgreSQL does not support that.

You can have have a *function* with a dynamic record as output, but then
you have to name the columns in the query. That's how crosstab works.

You can also have a result where all your dynamic data - the stuff where
you don't know the keys in advance - is a single column of a data type
like 'hstore' that contains key/value mappings. From the above, you
don't seem to want that.

I suspect that in your case, your best bet would be a two-query
solution. Figure out your WHERE clause, then run a query to find out
what keys match he WHERE clause. Use that in your application to produce
a crosstab query with an appropriate column list.

If you really need to do this as a view, I think you're stuffed. You'll
never get dynamic column lists in a view.

> I read hstore first time & find it difficult to understand because I
> don't want the output in one column :
> 
> CREATE TABLE test (col1 integer, col2 text, col3 text);
> INSERT INTO test VALUES (123, 'foo', 'bar');
> 
> SELECT hstore(t) FROM test AS t;
>hstore
> -
>  "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
> (1 row)

Why not? Is the problem converting a hstore value into a set of values
client-side? Something else?

Maybe it would help if you took a step back and explained why you need
this exacty format - a view with dynamic columns. It's a weird
requirement, and it makes me wonder if you're really looking for
something else and have settled on a dynamic-column view as the "only"
solution when there migh tbe an easier way.

What constraints are you under? What app is involved? What language(s)?
Is it old/unmaintained code, or something new and under development?
What role do you play in it?

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