Re: [GENERAL] Revoke "drop database" even for superusers?

[Edson Richter]

  
  
Wordeful!
  
  Guillaume, Thanks.
  
  I"ll give a try for few weeks in the development and test
  databases before put in production.
  
  Regards,
  

  

  
  Edson Carlos Ericksson Richter

SimKorp Informática Ltda
  


  Fone:
  (51) 3366-7964


  Celular:
  (51)9318-9766/(51)
  8585-0796


  


  


  
  Em 23/11/2012 19:18, Guillaume Lelarge escreveu:


  On Fri, 2012-11-09 at 09:19 -0200, Edson Richter wrote:

  
I've a bunch of databases that cannot be dropped in any case.

I was wondering if it is possible to revoke "drop database" permission 
for all users, in order that even superuser, if he wishes to drop a 
database, he will need first to "grant drop database" first.

I know there is already a safety that does not allow dropping databases 
in use - I just want to make even harder.


  
  
You can also use the hook system to add this feature to PostgreSQL
(without changing PostgreSQL code). With the code available on
https://github.com/gleu/Hooks-in-PostgreSQL/tree/master/examples/deny_drop, you can have a shared library that will take care of denying the drop of a database.

Once compiled and intalled, you need to change the postgresql.conf file
with this new setting:

shared_preload_libraries = 'deny_drop'

After you restart PostgreSQL, it should work like this:

$ psql postgres
psql (9.2.1)
Type "help" for help.

postgres=# create database tryme;
CREATE DATABASE
postgres=# drop database tryme;
ERROR:  cannot drop a database!
postgres=# set deny_drop.iknowwhatiamdoing to true;
SET
postgres=# drop database tryme;
DROP DATABASE
postgres=# \q

AFAICT, this code has never been used in production, but it's so simple
I don't think you risk anything using it.

Anyway, it's still better to actually use the user's permissions to deny
him to drop databases. But this little shared library may still be
usefull.





  

Re: [GENERAL] create table in memory

[Craig Ringer]

On 11/24/2012 02:15 AM, Peter Kroon wrote:
> I found out that declaring tables outside of functions increases the
> execution time of the function.
Strictly, what's probably happening is that creating a table in the same
transaction as populating it is a lot faster than creating it,
committing, and populating it in a new transaction. In the 1st case WAL
logging for the heap can be avoided if you aren't using replication or
PITR (ie wal_level is minimal).

Functions are automatically wrapped in a transaction if you don't open
one explicitly so doing a CREATE TABLE inside a function will be
quicker. The same result should be achieved by beginning a transaction,
creating the table, then calling the function.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



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

Re: [GENERAL] High SYS CPU - need advise

[Vlad]

ok, understood.
I need to give some more thoughts to if it's possible for us to switch to
transaction mode from app standpoint of view.

if yes, then setting pool size to 20 (for 8 cores)  sounds OK?

-- Vlad

Re: [GENERAL] Invalid argument

[Tom Lane]

zhangshzh  writes:
> I am working on hpux itanium platform;
> I am using postgres 9.1.3 version and have compiled 64bit libpq library.
> While conneting I am getting error
> " could not get socket error status: Invalid argument"

Check into 32-vs-64-bit compilation options, and particularly whether
PG's configure script chose the right type for the last argument to
getsockopt().  I suspect it can be either 32 or 64 bits depending on
compile options on HPUX, so it'd be an easy thing to mess up.

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] High SYS CPU - need advise

[Merlin Moncure]

On Wed, Nov 21, 2012 at 11:56 AM, Vlad  wrote:
> ok, understood.
> I need to give some more thoughts to if it's possible for us to switch to
> transaction mode from app standpoint of view.
>
> if yes, then setting pool size to 20 (for 8 cores)  sounds OK?

If it was me, I would be starting with exactly 8 and be tuning from there.

Don't forget: each database role gets its own pool.

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] obtain the difference between successive rows

[Kevin Grittner]

Jasen Betts wrote:

> electricity meter may bis a bad example as usage meters often have 
> fewer digits than are needed to track all historical usage
> 
> eg:
> 
>  '2012-05-07',997743
>  '2012-06-06',999601
>  '2012-07-05',000338
>  '2012-08-06',001290
>  '2012-09-07',002158
>  '2012-10-05',003018

Wrap-around can be handled pretty easily. It's meter replacement
that is a challenge.  :-)

SELECT
    current_reading_date as "reading date",
    lag(current_meter_reading, 1)
      over (order by current_reading_date) as "prior reading",
    current_meter_reading as "current reading",
    (10 + current_meter_reading
      - lag(current_meter_reading, 1)
          over (order by current_reading_date)) % 100 as usage
  from electricity;

 reading date | prior reading | current reading | usage 
--+---+-+---
 2012-05-07   |               |          997743 |      
 2012-06-06   |        997743 |          999601 |  1858
 2012-07-05   |        999601 |             338 |   737
 2012-08-06   |           338 |            1290 |   952
 2012-09-07   |          1290 |            2158 |   868
 2012-10-05   |          2158 |            3018 |   860
(6 rows)

-Kevin


-- 
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] obtain the difference between successive rows

[Jasen Betts]

On 2012-10-20, Berend Tober  wrote:
> Thalis Kalfigkopoulos wrote:
>> On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell  wrote:
>>> On 20/10/2012 11:54, ochaussavoine wrote:
 I have a table 'tmvt' with a field 'created' in the row, and would like to
 compute the difference between successive rows. The solution I found is:
>>>
>>> I think you can do it with a window function.
>>>
>>
>> In particular you're looking probably for the lag() window function.
>
> What about if there is more than one column you want the 
> difference for (... coincidentally I am writing a article on this 
> topic right now! ...), say a table which is used to record a 
> metered quantity at not-quite regular intervals:
>
> CREATE TABLE electricity
> (
>current_reading_date date,
>current_meter_reading integer
> );
>
>
> with sample data:
>
>
> '2012-09-07',2158
> '2012-10-05',3018
>
>
>
> and I want an output such as:
>
>
> Meter Read on October 5
>
> Current  Previous  kWh
> ReadingReading  Used
> ---
> 3018   -  2158   =860
>
> Number service days = 28
>
>
> I am working on a write-up of a neat solution using CTE's, but 
> would be interested in other's views.

electricity meter may bis a bad example as usage meters often have 
fewer digits than are needed to track all historical usage

eg:

 '2012-05-07',997743
 '2012-06-06',999601
 '2012-07-05',000338
 '2012-08-06',001290
 '2012-09-07',002158
 '2012-10-05',003018


-- 
⚂⚃ 100% natural



-- 
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] High SYS CPU - need advise

[Vlad]

It turned out we can't use transaction mode, cause there are prepared
statement used a lot within code, while processing a single http request.

Also, I can't 100% rule out that there won't be any long running
(statistical) queries launched (even though such requests should not come
to this database), which would occupy connection for longer time, but do
not create any race condition... So having pool size at 8 may be too slim .

It's a single database.

-- Vlad


On Wed, Nov 21, 2012 at 12:04 PM, Merlin Moncure  wrote:

> On Wed, Nov 21, 2012 at 11:56 AM, Vlad  wrote:
> > ok, understood.
> > I need to give some more thoughts to if it's possible for us to switch to
> > transaction mode from app standpoint of view.
> >
> > if yes, then setting pool size to 20 (for 8 cores)  sounds OK?
>
> If it was me, I would be starting with exactly 8 and be tuning from there.
>
> Don't forget: each database role gets its own pool.
>
> merlin
>

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Wed, Nov 21, 2012 at 12:17 PM, Vlad  wrote:
> It turned out we can't use transaction mode, cause there are prepared
> statement used a lot within code, while processing a single http request.

prepare statements can be fudged within some constraints.  if prepared
statements are explicitly named via PREPARE, you can simply prepare
them all on server connection via connect_query setting and disable
the manual preparation.  you then change the server_reset_query so
that they are not discarded.  some basic experimentation might confirm
if this is viable strategy.  automatic protocol level statements can
be an issue though.

> Also, I can't 100% rule out that there won't be any long running
> (statistical) queries launched (even though such requests should not come to
> this database), which would occupy connection for longer time, but do not
> create any race condition... So having pool size at 8 may be too slim .

there are a number of simple tricks to deal with this:
1) move long running queries to their own pool (by changing login user
or connection string)
2) bypass pgbouncer in those cases
3) increase pool size

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] High SYS CPU - need advise

[Vlad]

Merlin,

On Wed, Nov 21, 2012 at 2:17 PM, Merlin Moncure  wrote:

> On Wed, Nov 21, 2012 at 12:17 PM, Vlad  wrote:
> > It turned out we can't use transaction mode, cause there are prepared
> > statement used a lot within code, while processing a single http request.
>
> prepare statements can be fudged within some constraints.  if prepared
> statements are explicitly named via PREPARE, you can simply prepare
> them all on server connection via connect_query setting and disable
> the manual preparation.  you then change the server_reset_query so
> that they are not discarded.  some basic experimentation might confirm
> if this is viable strategy.  automatic protocol level statements can
> be an issue though.
>

We have 350k+ lines of code in our app, so this is not quite feasible as
I'd wish.


> > Also, I can't 100% rule out that there won't be any long running
> > (statistical) queries launched (even though such requests should not
> come to
> > this database), which would occupy connection for longer time, but do not
> > create any race condition... So having pool size at 8 may be too slim .
>
> there are a number of simple tricks to deal with this:
> 1) move long running queries to their own pool (by changing login user
> or connection string)
> 2) bypass pgbouncer in those cases
> 3) increase pool size
>
>
It's pretty much already setup so that long running queries should not hit
the same DB cluster as those with (potentially) high connection/query
rates, but I still can't rule out that no long-running queries will be
issued via pgbouncer.

Either case - it seems that the combination of pool size  = 200 / pool mode
= session / server_lifetime = 30 makes things stable for now.

I'm planning to repeat my case on 2.6.x kernel, but it will be a while
before I have chance to do that.


Thanks.

-- vlad

[GENERAL] Querying information_schema [bug?]

[Ivan Radovanovic]

Hello,

I couldn't find anything mentioned about this in documentation and 
googling didn't help either:
- if I connect to database as user who doesn't have permission to access 
all schemas then querying information_schema.schemata returns no rows 
(querying information_schema.tables returns only tables from accessible 
schemas, and executing \dn from psql returns list of all schemas)


Desk=> select schema_name from information_schema.schemata;
 schema_name
-
(0 rows)

Desk=> select distinct table_schema from information_schema.tables;
table_schema

 cards
 information_schema
 pg_catalog
(3 rows)

Desk=> \dn
List of schemas
  Name  | Owner
+---
 cards  | pgsql
 help   | pgsql
 public | pgsql
 storage| pgsql
(4 rows)

Desk=> select version();
   version 


-
 PostgreSQL 9.2.1 on amd64-portbld-freebsd8.3, compiled by cc (GCC) 
4.2.1 20070831 patched [FreeBSD], 64-bit

(1 row)

Regards,
Ivan


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

[GENERAL] Invalid argument

[zhangshzh]

I am working on hpux itanium platform;
I am using postgres 9.1.3 version and have compiled 64bit libpq library.
While conneting I am getting error
" could not get socket error status: Invalid argument"

can someone help here? Why I am getting this error while connecting to database.

Regards

[GENERAL] pgAgent reporting success for non-zero results

[Chris Earle]

   We recently recompiled pgAgent 3.3.0 for our Centos x86 64 environment
to over come the problem of steps with a non-zero result codes being
reported as successful.
What appears to be happening is if the first step has a non-zero result
then it correctly has the status of Failed but if it is a later step that
is non-zero then it always has Successful.

To test this is setup a job of two steps and ran it in two orders
 Job: 'Test'
 Step: 1.exit 1, Definition: exit 1; - Result 1, Status: Failed
Step: 2.exit 0, Definition: exit 0; - 

  Job: 'Test'
Step: 1.exit 0, Definition: exit 0; - Result 0, Status: Successful
Step: 2.exit 1, Definition: exit 1; - Result 1, Status: Successful

Does anyone any ideas of what is causing this behaviour or how to work
around this?

Re: [GENERAL] obtain the difference between successive rows

[Jasen Betts]

On 2012-10-20, Tom Lane  wrote:
> "Raymond O'Donnell"  writes:
>> On 20/10/2012 17:23, Tom Lane wrote:
>>> FWIW, Postgres is reasonably smart about the case of multiple window
>>> functions with identical window definitions --- once you've got one
>>> lag() in the query, adding more isn't going to cost much.
>
>> Out of curiosity, would there be much difference between having multiple
>> lag()s in the SELECT and a single one in a CTE?
>
> Not sure what you're proposing?  I don't see how you'd solve this
> problem with a CTE, at least not without a join, which seems unlikely
> to be a win.


select generate_series(1,20)  as a ,
  ( generate_series(1,20)*9+random()*7)::int as b
  into temp table foo;
delete from foo where random()>0.5;

with 
 j as ( select f,lag(f) over (order by f.a)as g from foo as f )
 select (f).*,(g).a as "lag a",(g).b as "lag b",
 (f).a-(g).a as "diff(a)" ,(f).b-(g).b as "diff(b)" from j;

-- 
⚂⚃ 100% natural



-- 
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] Revoke "drop database" even for superusers?

[Guillaume Lelarge]

On Fri, 2012-11-09 at 09:19 -0200, Edson Richter wrote:
> I've a bunch of databases that cannot be dropped in any case.
> 
> I was wondering if it is possible to revoke "drop database" permission 
> for all users, in order that even superuser, if he wishes to drop a 
> database, he will need first to "grant drop database" first.
> 
> I know there is already a safety that does not allow dropping databases 
> in use - I just want to make even harder.
> 

You can also use the hook system to add this feature to PostgreSQL
(without changing PostgreSQL code). With the code available on
https://github.com/gleu/Hooks-in-PostgreSQL/tree/master/examples/deny_drop, you 
can have a shared library that will take care of denying the drop of a database.

Once compiled and intalled, you need to change the postgresql.conf file
with this new setting:

shared_preload_libraries = 'deny_drop'

After you restart PostgreSQL, it should work like this:

$ psql postgres
psql (9.2.1)
Type "help" for help.

postgres=# create database tryme;
CREATE DATABASE
postgres=# drop database tryme;
ERROR:  cannot drop a database!
postgres=# set deny_drop.iknowwhatiamdoing to true;
SET
postgres=# drop database tryme;
DROP DATABASE
postgres=# \q

AFAICT, this code has never been used in production, but it's so simple
I don't think you risk anything using it.

Anyway, it's still better to actually use the user's permissions to deny
him to drop databases. But this little shared library may still be
usefull.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.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] Restore postgres to specific time

[Jeff Janes]

On Thu, Nov 22, 2012 at 7:29 PM, wd  wrote:
> Thanks for your reply, the logs are something like bellow，postgres will
> restore every wal log I put in the xlog directory，and then continues waiting
> for next wal log. The postgres version is 9.1.6.
>
> [2012-11-22 18:49:24.175 CST 25744 50ae0334.6490 1 0]LOG:  database
> system was shut down in recovery at 2012-11-22 18:49:22 CST
> [2012-11-22 18:49:24.176 CST 25744 50ae0334.6490 2 0]LOG:  entering
> standby mode

Do you have standby_mode=on in your recovery.conf?  If so, remove it.

It is meaningless to specify both standy_mode, and recovery_target_time.

Cheers,

Jeff


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

[Ike Nnabugwu]

From my Android phone on T-Mobile. The first nationwide 4G network.Peter Kroon 
 wrote:I've installed Npgsql via Application stack builder 
without a problem(I guess, no error messages seen).

http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html
The projectpage tells me I have to add:
<%@ Assembly name="System.Data" %>
<%@ Assembly name="Npgsql" %>

When I do this I get an error:
Regel 1:  <%@ Page Language="VB" Debug="true" %>
Regel 2:  <%@ Import Namespace="System.Data.Odbc" %>
Regel 3:  <%@ Assembly name="Npgsql" %>
Regel 4:  <%
Regel 5:  
It's unable to find/load dependencies.
Has anyone ever encountered this problem?
If so, what was your solution.

Thanks,

Peter

Re: [GENERAL] create table in memory

[Peter Kroon]

I found out that declaring tables outside of functions increases the
execution time of the function.
And CREATE UNLOGGED TABLE is very fast.


2012/11/23 Peter Kroon 

> I've put up a small test case for creating TEMP and UNLOGGED tables.
> DROP TABLE IF EXISTS test CASCADE;
> CREATE TEMP TABLE test(
> id serial,
>  the_value text
> );
> Exec time: 54ms
>
> DROP TABLE IF EXISTS test CASCADE;
> CREATE UNLOGGED TABLE test(
>  id serial,
> the_value text
> );
> Exec time: 198ms
>
> There is a significant difference.
>
> Also I need those tables per session, so creating and dropping with TEMP
> tables appear to be faster.
>
> Best,
> Peter KRoon
>
>
>
> 2012/11/23 Raghavendra 
>
>>
>> On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon  wrote:
>>
>>> I've converted some mssql functions and they appear to be slower in
>>> pgsql.
>>> I use a lot of declared tables in mssql as they are created in memory.
>>> Which makes it very fast.
>>>
>>> 2012/11/23 Peter Kroon 
>>>
 Is a temp table created to memory(ram) or disk?
 I've converted some msssq

>>>
>>>
>> Not exactly as MS Sql declare tables.
>> In PostgreSQL, TEMP tables are session-private. These tables are session
>> based and stored in a special schema and visible only to the backend which
>> has created. Memory management is controlled with temp_buffer(shared by all
>> backends) in postgresql.conf.
>>
>> You should check UNLOGGED tables of same family, these tables are visible
>> to all the backends and data shared across backends.
>> Since, data is not written to WAL's  you should get better performance.
>>
>> ---
>> Regards,
>> Raghavendra
>> EnterpriseDB Corporation
>> Blog: http://raghavt.blogspot.com/
>>
>
>

[GENERAL] Npgsql

[Peter Kroon]

I've installed Npgsql via Application stack builder without a problem(I
guess, no error messages seen).

http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html
The projectpage tells me I have to add:

<%@ Assembly name="System.Data" %>
<%@ Assembly name="Npgsql" %>


When I do this I get an error:

Regel 1:  <%@ Page Language="VB" Debug="true" %>
Regel 2:  <%@ Import Namespace="System.Data.Odbc" %>Regel 3:  <%@
Assembly name="Npgsql" %>Regel 4:  <%
Regel 5:

It's unable to find/load dependencies.
Has anyone ever encountered this problem?
If so, what was your solution.

Thanks,

Peter

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

[Tom Lane]

Heikki Linnakangas  writes:
> On 23.11.2012 17:53, Tom Lane wrote:
>> We could avoid this problem if we were prepared to make type "name"
>> be varlena,  ...

> It would actually be nice to do that because it would *reduce* the 
> amount of space and memory used for the catalogs in the typical case, 

Agreed, that would be a nicer place to be ... but the amount of work
required to get there is daunting, and rather out of proportion to the
benefit IMO.

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] [BUGS] Prepared Statement Name Truncation

[Heikki Linnakangas]

On 23.11.2012 17:53, Tom Lane wrote:

Euler Taveira  writes:

On 22-11-2012 04:27, Pavel Stehule wrote:

significantly larger catalog



Less than 5% of catalog columns? I don't buy your argument.


It's not about count, it's about size.  For instance, pg_attribute
currently requires 140 bytes per row (counting the tuple header and
line pointer), so adding 64 bytes would represent 45% bloat.  In
a database with lots of tables that would be painful.

We could avoid this problem if we were prepared to make type "name"
be varlena,  ...


It would actually be nice to do that because it would *reduce* the 
amount of space and memory used for the catalogs in the typical case, 
where the attribute names are much smaller than 64 bytes. I received a 
complaint just the other day that our backend processes consume a lot of 
memory, even when idle; the catalog caches are a large part of that.


- Heikki


--
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] [BUGS] Prepared Statement Name Truncation

[Tom Lane]

Euler Taveira  writes:
> On 22-11-2012 04:27, Pavel Stehule wrote:
>>> significantly larger catalog

> Less than 5% of catalog columns? I don't buy your argument.

It's not about count, it's about size.  For instance, pg_attribute
currently requires 140 bytes per row (counting the tuple header and
line pointer), so adding 64 bytes would represent 45% bloat.  In
a database with lots of tables that would be painful.

We could avoid this problem if we were prepared to make type "name"
be varlena, but that would be rather catastrophic because of the
code's habit of overlaying C structures onto catalog rows.

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] ERROR: query has no destination for result data

[Adrian Klaver]

On 11/23/2012 03:25 AM, Peter Kroon wrote:

Yes, but this means I have to create a function which is something I
don't want.
I just want to debug some of my code in the related function.
So what I want is in pgAdmin declare some vars and run the sql and get
the result.


The way I handle this is to use RAISE NOTICE in place of RETURN.






--
Adrian Klaver
adrian.kla...@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] ERROR: query has no destination for result data

[Peter Kroon]

OK, but how do I run some SQL in pgAdmin with declared variables?


2012/11/23 Pavel Stehule 

> 2012/11/23 Peter Kroon :
> > So this means it's unable to return data?
>
> yes, it means :(
>
> DO "is" void function, so you cannot to return anything
>
> Regards
>
> Pavel Stehule
>
> >
> >
> > 2012/11/23 Bartosz Dmytrak 
> >>
> >> Hi,
> >> according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
> >> DO returns void:
> >> "The code block is treated as though it were the body of a function with
> >> no parameters, returning void."
> >>
> >> Regars
> >> Bartek
> >>
> >> Pozdrawiam,
> >> Bartek
> >>
> >>
> >>
> >> 2012/11/23 Peter Kroon 
> >>>
> >>> Hello,
> >>>
> >>> I wish to return the SELECT statement.
> >>> Ho can I achieve this?
> >>>
> >>> DO $$
> >>>
> >>> DECLARE v_some_id int=14;
> >>>
> >>> BEGIN
> >>> /*
> >>> more queries here...
> >>> */
> >>> SELECT 'this is text';
> >>> END
> >>> $$ LANGUAGE plpgsql;
> >>>
> >>> Best,
> >>> Peter Kroon
> >>>
> >>
> >
>

Re: [GENERAL] ERROR: query has no destination for result data

[Craig Ringer]

On 11/23/2012 06:36 PM, Peter Kroon wrote:
> Hello,
>
> I wish to return the SELECT statement.
> Ho can I achieve this?

RETURN QUERY.

See
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] ERROR: query has no destination for result data

[Craig Ringer]

On 11/23/2012 06:53 PM, Peter Kroon wrote:
> When using:
> RETURN QUERY(
> SELECT 'this is text'
> );
>
> I get another error: 
> ERROR:  cannot use RETURN QUERY in a non-SETOF function

Use a plain SQL function instead of PL/PgSQL, or use `SELECT INTO` and
ordinary `RETURN`.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

[Pavel Stehule]

2012/11/23 Euler Taveira :
> On 22-11-2012 04:27, Pavel Stehule wrote:
>> 2012/11/21 Greg Sabino Mullane : Separately, what are
>> the objections to raising the size limit to 128?
>>
>>> significantly larger catalog
>>
> Less than 5% of catalog columns? I don't buy your argument.

default 6201kB (64) updated 6967kB (128) on empty database - so it is
12% more per one database

in our warehouses we have more than 10 relations per database and
about 1000 databases per warehouse - so these number can be
significantly higher

second issue can be increasing memory allocations for system caches
and some other place

Regards

Pavel

>
>
> --
>Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
>PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


-- 
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] ERROR: query has no destination for result data

[Peter Kroon]

Thanks, I'll have a look at this.


2012/11/23 Pavel Stehule 

> 2012/11/23 Peter Kroon :
> > OK, but how do I run some SQL in pgAdmin with declared variables?
>
> pgAdmin has own client language similar to T-SQL
> http://www.pgadmin.org/docs/dev/pgscript.html - but it is client side
> language
>
> if you like server side code, then you have to write table function.
>
> Regards
>
> Pavel
>
>
> >
> >
> > 2012/11/23 Pavel Stehule 
> >>
> >> 2012/11/23 Peter Kroon :
> >> > So this means it's unable to return data?
> >>
> >> yes, it means :(
> >>
> >> DO "is" void function, so you cannot to return anything
> >>
> >> Regards
> >>
> >> Pavel Stehule
> >>
> >> >
> >> >
> >> > 2012/11/23 Bartosz Dmytrak 
> >> >>
> >> >> Hi,
> >> >> according to doc:
> http://www.postgresql.org/docs/9.2/static/sql-do.html
> >> >> DO returns void:
> >> >> "The code block is treated as though it were the body of a function
> >> >> with
> >> >> no parameters, returning void."
> >> >>
> >> >> Regars
> >> >> Bartek
> >> >>
> >> >> Pozdrawiam,
> >> >> Bartek
> >> >>
> >> >>
> >> >>
> >> >> 2012/11/23 Peter Kroon 
> >> >>>
> >> >>> Hello,
> >> >>>
> >> >>> I wish to return the SELECT statement.
> >> >>> Ho can I achieve this?
> >> >>>
> >> >>> DO $$
> >> >>>
> >> >>> DECLARE v_some_id int=14;
> >> >>>
> >> >>> BEGIN
> >> >>> /*
> >> >>> more queries here...
> >> >>> */
> >> >>> SELECT 'this is text';
> >> >>> END
> >> >>> $$ LANGUAGE plpgsql;
> >> >>>
> >> >>> Best,
> >> >>> Peter Kroon
> >> >>>
> >> >>
> >> >
> >
> >
>

Re: [GENERAL] ERROR: query has no destination for result data

[Peter Kroon]

Yes, but this means I have to create a function which is something I don't
want.
I just want to debug some of my code in the related function.
So what I want is in pgAdmin declare some vars and run the sql and get the
result.


2012/11/23 John R Pierce 

> On 11/23/12 2:53 AM, Peter Kroon wrote:
>
>> I get another error:
>> ERROR:  cannot use RETURN QUERY in a non-SETOF function
>>
>>
> what is your function deined to return?
>
> a query returns a set of records, even if that set is 1 record of 1 field
> (like, select 'some text';)
>
> you could declare a record variable, and use SELECT ... INTO myrecordvar
> [FROM ...];
> http://www.postgresql.org/**docs/current/static/plpgsql-**
> statements.html#PLPGSQL-**STATEMENTS-SQL-ONEROW
> then return a field of that record variable.
>
> see this example...
> http://www.postgresql.org/**docs/current/static/plpgsql-**
> control-structures.html#**PLPGSQL-RECORDS-ITERATING
> ... for how you would loop through query results
>
>
> --
> john r pierceN 37, W 122
> santa cruz ca mid-left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

Re: [GENERAL] ERROR: query has no destination for result data

[John R Pierce]

On 11/23/12 2:53 AM, Peter Kroon wrote:

I get another error:
ERROR:  cannot use RETURN QUERY in a non-SETOF function



what is your function deined to return?

a query returns a set of records, even if that set is 1 record of 1 
field (like, select 'some text';)


you could declare a record variable, and use SELECT ... INTO myrecordvar 
[FROM ...];

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
then return a field of that record variable.

see this example...
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
... for how you would loop through query results


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



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

[GENERAL] Prevent x005F from xml

[Peter Kroon]

When I run this query:
SELECT
xmlelement(name my_xslt_tag,
(SELECT XMLPARSE (CONTENT ''))
)

I get this result:
""

Running soemthing similar in mssql prevents the x005F
SELECT 'data' AS p,'data' AS k
FOR XML RAW('xmlst')

How do I prevent the x005F in the tagname without changing the tagname?

Best,
Peter Kroon

Re: [GENERAL] ERROR: query has no destination for result data

[Pavel Stehule]

2012/11/23 Peter Kroon :
> OK, but how do I run some SQL in pgAdmin with declared variables?

pgAdmin has own client language similar to T-SQL
http://www.pgadmin.org/docs/dev/pgscript.html - but it is client side
language

if you like server side code, then you have to write table function.

Regards

Pavel


>
>
> 2012/11/23 Pavel Stehule 
>>
>> 2012/11/23 Peter Kroon :
>> > So this means it's unable to return data?
>>
>> yes, it means :(
>>
>> DO "is" void function, so you cannot to return anything
>>
>> Regards
>>
>> Pavel Stehule
>>
>> >
>> >
>> > 2012/11/23 Bartosz Dmytrak 
>> >>
>> >> Hi,
>> >> according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
>> >> DO returns void:
>> >> "The code block is treated as though it were the body of a function
>> >> with
>> >> no parameters, returning void."
>> >>
>> >> Regars
>> >> Bartek
>> >>
>> >> Pozdrawiam,
>> >> Bartek
>> >>
>> >>
>> >>
>> >> 2012/11/23 Peter Kroon 
>> >>>
>> >>> Hello,
>> >>>
>> >>> I wish to return the SELECT statement.
>> >>> Ho can I achieve this?
>> >>>
>> >>> DO $$
>> >>>
>> >>> DECLARE v_some_id int=14;
>> >>>
>> >>> BEGIN
>> >>> /*
>> >>> more queries here...
>> >>> */
>> >>> SELECT 'this is text';
>> >>> END
>> >>> $$ LANGUAGE plpgsql;
>> >>>
>> >>> Best,
>> >>> Peter Kroon
>> >>>
>> >>
>> >
>
>


-- 
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] [BUGS] Prepared Statement Name Truncation

[Euler Taveira]

On 22-11-2012 04:27, Pavel Stehule wrote:
> 2012/11/21 Greg Sabino Mullane : Separately, what are
> the objections to raising the size limit to 128?
> 
>> significantly larger catalog
> 
Less than 5% of catalog columns? I don't buy your argument.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


-- 
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] ERROR: query has no destination for result data

[Peter Kroon]

When using plain SQL I get this message:
ERROR:  language "sql" does not support inline code execution
When removing the BEGIN+END block statements the message persists.


2012/11/23 Craig Ringer 

>  On 11/23/2012 06:53 PM, Peter Kroon wrote:
>
> When using:
> RETURN QUERY(
>  SELECT 'this is text'
>  );
>
>  I get another error:
> ERROR:  cannot use RETURN QUERY in a non-SETOF function
>
>
> Use a plain SQL function instead of PL/PgSQL, or use `SELECT INTO` and
> ordinary `RETURN`.
>
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [GENERAL] ERROR: query has no destination for result data

[Pavel Stehule]

2012/11/23 Peter Kroon :
> So this means it's unable to return data?

yes, it means :(

DO "is" void function, so you cannot to return anything

Regards

Pavel Stehule

>
>
> 2012/11/23 Bartosz Dmytrak 
>>
>> Hi,
>> according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
>> DO returns void:
>> "The code block is treated as though it were the body of a function with
>> no parameters, returning void."
>>
>> Regars
>> Bartek
>>
>> Pozdrawiam,
>> Bartek
>>
>>
>>
>> 2012/11/23 Peter Kroon 
>>>
>>> Hello,
>>>
>>> I wish to return the SELECT statement.
>>> Ho can I achieve this?
>>>
>>> DO $$
>>>
>>> DECLARE v_some_id int=14;
>>>
>>> BEGIN
>>> /*
>>> more queries here...
>>> */
>>> SELECT 'this is text';
>>> END
>>> $$ LANGUAGE plpgsql;
>>>
>>> Best,
>>> Peter Kroon
>>>
>>
>


-- 
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] ERROR: query has no destination for result data

[Peter Kroon]

So this means it's unable to return data?


2012/11/23 Bartosz Dmytrak 

> Hi,
> according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
> DO returns void:
> *"The code block is treated as though it were the body of a function with
> no parameters, returning void."*
> *
> *
> Regars
> Bartek
>
> Pozdrawiam,
> Bartek
>
>
>
> 2012/11/23 Peter Kroon 
>
>> Hello,
>>
>> I wish to return the SELECT statement.
>> Ho can I achieve this?
>>
>> DO $$
>>
>> DECLARE v_some_id int=14;
>>
>> BEGIN
>>  /*
>> more queries here...
>> */
>> SELECT 'this is text';
>> END
>> $$ LANGUAGE plpgsql;
>>
>> Best,
>> Peter Kroon
>>
>>
>

Re: [GENERAL] ERROR: query has no destination for result data

[Peter Kroon]

When using:
RETURN QUERY(
SELECT 'this is text'
);

I get another error:
ERROR:  cannot use RETURN QUERY in a non-SETOF function


2012/11/23 Craig Ringer 

>  On 11/23/2012 06:36 PM, Peter Kroon wrote:
>
> Hello,
>
>  I wish to return the SELECT statement.
> Ho can I achieve this?
>
>
> RETURN QUERY.
>
> See
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [GENERAL] w7 vs linux

[Craig Ringer]

On 11/23/2012 05:39 PM, Peter Kroon wrote:
> Is pgsql faster on linux?
> Currently I've made an installation on W7 and the converted queries
> are about 3 times slower then on mssql.
> There's still some optimization to do tho...but the current results
> don't look to good.
In my experience it's somewhat faster on Linux, but I haven't compared
extensively on the same hardware.

It's known to be necessary to set shared_buffers lower on Windows for
reasons not yet firmly established.

Since you have provided no information about the configuration or
hardware, your question isn't much better than "is A faster than B".
What's A? What's B?

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] ERROR: query has no destination for result data

[Bartosz Dmytrak]

Hi,
according to doc: http://www.postgresql.org/docs/9.2/static/sql-do.html
DO returns void:
*"The code block is treated as though it were the body of a function with
no parameters, returning void."*
*
*
Regars
Bartek

Pozdrawiam,
Bartek


2012/11/23 Peter Kroon 

> Hello,
>
> I wish to return the SELECT statement.
> Ho can I achieve this?
>
> DO $$
>
> DECLARE v_some_id int=14;
>
> BEGIN
>  /*
> more queries here...
> */
> SELECT 'this is text';
> END
> $$ LANGUAGE plpgsql;
>
> Best,
> Peter Kroon
>
>

[GENERAL] ERROR: query has no destination for result data

[Peter Kroon]

Hello,

I wish to return the SELECT statement.
Ho can I achieve this?

DO $$

DECLARE v_some_id int=14;

BEGIN
/*
more queries here...
*/
SELECT 'this is text';
END
$$ LANGUAGE plpgsql;

Best,
Peter Kroon

Re: [GENERAL] COPY FROM in psql

[Jasen Betts]

On 2012-11-21, Matthew Vernon  wrote:
> t...@sss.pgh.pa.us (Tom Lane) writes:
>
>> Matthew Vernon  writes:
>>> naiively, you might try:
>>> \set pwd '\'' `pwd` '\''
>>> COPY table FROM :pwd || '/relative/path/to/data' ;
>>
>> Umm ... why don't you just use a relative path as-is, with \copy
>> instead of COPY?
>
> Thanks for the suggestion, but I was avoiding \copy because the
> quantities of data involved are large, and the documentation suggests
> that COPY is better than \copy for large data volumes.

\copy  is translated to "COPY  FROM STDIN"  by psql
performance should be almost as fast, you loose a little in the 
buffering and re-streaming, but not much.

-- 
⚂⚃ 100% natural



-- 
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] create table in memory

[Peter Kroon]

I've put up a small test case for creating TEMP and UNLOGGED tables.
DROP TABLE IF EXISTS test CASCADE;
CREATE TEMP TABLE test(
id serial,
the_value text
);
Exec time: 54ms

DROP TABLE IF EXISTS test CASCADE;
CREATE UNLOGGED TABLE test(
id serial,
the_value text
);
Exec time: 198ms

There is a significant difference.

Also I need those tables per session, so creating and dropping with TEMP
tables appear to be faster.

Best,
Peter KRoon



2012/11/23 Raghavendra 

>
> On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon  wrote:
>
>> I've converted some mssql functions and they appear to be slower in pgsql.
>> I use a lot of declared tables in mssql as they are created in memory.
>> Which makes it very fast.
>>
>> 2012/11/23 Peter Kroon 
>>
>>> Is a temp table created to memory(ram) or disk?
>>> I've converted some msssq
>>>
>>
>>
> Not exactly as MS Sql declare tables.
> In PostgreSQL, TEMP tables are session-private. These tables are session
> based and stored in a special schema and visible only to the backend which
> has created. Memory management is controlled with temp_buffer(shared by all
> backends) in postgresql.conf.
>
> You should check UNLOGGED tables of same family, these tables are visible
> to all the backends and data shared across backends.
> Since, data is not written to WAL's  you should get better performance.
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>

Re: [GENERAL] create table in memory

[Raghavendra]

On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon  wrote:

> I've converted some mssql functions and they appear to be slower in pgsql.
> I use a lot of declared tables in mssql as they are created in memory.
> Which makes it very fast.
>
> 2012/11/23 Peter Kroon 
>
>> Is a temp table created to memory(ram) or disk?
>> I've converted some msssq
>>
>
>
Not exactly as MS Sql declare tables.
In PostgreSQL, TEMP tables are session-private. These tables are session
based and stored in a special schema and visible only to the backend which
has created. Memory management is controlled with temp_buffer(shared by all
backends) in postgresql.conf.

You should check UNLOGGED tables of same family, these tables are visible
to all the backends and data shared across backends.
Since, data is not written to WAL's  you should get better performance.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] w7 vs linux

[raghu ram]

On Fri, Nov 23, 2012 at 3:09 PM, Peter Kroon  wrote:

> Is pgsql faster on linux?
> Currently I've made an installation on W7 and the converted queries are
> about 3 times slower then on mssql.
> There's still some optimization to do tho...but the current results don't
> look to good.
>

Below URL provides more information on this topic:

http://serverfault.com/questions/222430/is-postgresql-suited-to-one-os-is-it-better-on-linux-than-windows

-- 

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/

[GENERAL] w7 vs linux

[Peter Kroon]

Is pgsql faster on linux?
Currently I've made an installation on W7 and the converted queries are
about 3 times slower then on mssql.
There's still some optimization to do tho...but the current results don't
look to good.

Re: [GENERAL] Restore postgres to specific time

[Albe Laurenz]

wd wrote:
>>> I've try to restore Postgres to a specific time but failed.
>>>
>>> The recovery.conf as bellow
>>> restore_command='cp /t/xlog/%f %p'
>>> recovery_target_time='2012-11-22 5:01:09 CST'
>>> pause_at_recovery_target=true
>>> recovery_target_inclusive=false
>>>
>>> The basebackup was made at 2012-11-22 3:10 CST, I've copy wal log files at 
>>> 3:00 to 5:10 into /t/xlog,
>>> but the recovery will not stop at 2012-11-22 5:01:09。
>>>
>>> How to do this?
> 
> Thanks for your reply, the logs are something like bellow，postgres will 
> restore every wal log I put
> in the xlog directory，and then continues waiting for next wal log. The 
> postgres version is 9.1.6.
> 
> [2012-11-22 18:49:24.176 CST 25744 50ae0334.6490 2 0]LOG:  entering 
> standby mode
> [2012-11-22 18:49:24.210 CST 25744 50ae0334.6490 3 0]LOG:  restored log 
> file
> "000100280023" from archive
> [2012-11-22 18:49:24.213 CST 25744 50ae0334.6490 4 0]LOG:  redo starts at 
> 28/23001900
> [2012-11-22 18:49:24.619 CST 25744 50ae0334.6490 5 0]LOG:  consistent 
> recovery state reached at
> 28/23C8
> [2012-11-22 18:49:24.620 CST 25736 50ae0333.6488 2 0]LOG:  database 
> system is ready to accept read
> only connections
> cp: cannot stat `/export/t/xlog/000100280024': No such file or 
> directory
[...]
> cp: cannot stat `/export/t/xlog/000100280024': No such file or 
> directory
> [2012-11-22 18:50:49.828 CST 25744 50ae0334.6490 6 0]LOG:  restored log 
> file
> "000100280024" from archive
[...]
> [2012-11-22 18:50:54.920 CST 25744 50ae0334.6490 17 0]LOG:  restored log 
> file
> "00010028002F" from archive
> cp: cannot stat `/export/t/xlog/000100280030': No such file or 
> directory
> cp: cannot stat `/export/t/xlog/000100280030': No such file or 
> directory

Can you also show us postgresql.conf from the standby?

Try to find out where you are in log replay.

Connect to the standby and run the following query:

SELECT pg_last_xact_replay_timestamp();

It would be interesting what time that is.

Yours,
Laurenz Albe

-- 
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] create table in memory

[raghu ram]

On Fri, Nov 23, 2012 at 2:43 PM, Peter Kroon  wrote:

> I've converted some mssql functions and they appear to be slower in pgsql.
> I use a lot of declared tables in mssql as they are created in memory.
> Which makes it very fast.
>
>
>
> 2012/11/23 Peter Kroon 
>
>> Is a temp table created to memory(ram) or disk?
>> I've converted some msssq
>>
>
>
While the temporary table is in-use, For a small table the data will be in
the memory, For a large table if data is not fit in memory then data will
be flushed to disk periodically as the database engine needs more working
space for other requests.

A permanent table persist after terminating PostgreSQL session, whereas
temporary table is automatically destroyed when PostgreSQL session ends.

The memory is controlled by temp_buffers parameter (postgresql.conf)
-- 

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/

Re: [GENERAL] create table in memory

[Peter Kroon]

I've converted some mssql functions and they appear to be slower in pgsql.
I use a lot of declared tables in mssql as they are created in memory.
Which makes it very fast.



2012/11/23 Peter Kroon 

> Is a temp table created to memory(ram) or disk?
> I've converted some msssq
>

[GENERAL] create table in memory

[Peter Kroon]

Is a temp table created to memory(ram) or disk?
I've converted some msssq

Re: [GENERAL] Restore postgres to specific time

[raghu ram]

On Fri, Nov 23, 2012 at 8:59 AM, wd  wrote:

> Thanks for your reply, the logs are something like bellow，postgres will
> restore every wal log I put in the xlog directory，and then continues
> waiting for next wal log. The postgres version is 9.1.6.
>
> [2012-11-22 18:49:24.175 CST 25744 50ae0334.6490 1 0]LOG:  database
> system was shut down in recovery at 2012-11-22 18:49:22 CST
> [2012-11-22 18:49:24.176 CST 25744 50ae0334.6490 2 0]LOG:  entering
> standby mode
> [2012-11-22 18:49:24.210 CST 25744 50ae0334.6490 3 0]LOG:  restored
> log file "000100280023" from archive
> [2012-11-22 18:49:24.213 CST 25744 50ae0334.6490 4 0]LOG:  redo starts
> at 28/23001900
> [2012-11-22 18:49:24.619 CST 25744 50ae0334.6490 5 0]LOG:  consistent
> recovery state reached at 28/23C8
> [2012-11-22 18:49:24.620 CST 25736 50ae0333.6488 2 0]LOG:  database
> system is ready to accept read only connections
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280024': No such file or
> directory
> [2012-11-22 18:50:49.828 CST 25744 50ae0334.6490 6 0]LOG:  restored
> log file "000100280024" from archive
> [2012-11-22 18:50:50.291 CST 25744 50ae0334.6490 7 0]LOG:  restored
> log file "000100280025" from archive
> [2012-11-22 18:50:50.759 CST 25744 50ae0334.6490 8 0]LOG:  restored
> log file "000100280026" from archive
> [2012-11-22 18:50:51.220 CST 25744 50ae0334.6490 9 0]LOG:  restored
> log file "000100280027" from archive
> [2012-11-22 18:50:51.686 CST 25744 50ae0334.6490 10 0]LOG:  restored
> log file "000100280028" from archive
> [2012-11-22 18:50:52.145 CST 25744 50ae0334.6490 11 0]LOG:  restored
> log file "000100280029" from archive
> [2012-11-22 18:50:52.613 CST 25744 50ae0334.6490 12 0]LOG:  restored
> log file "00010028002A" from archive
> [2012-11-22 18:50:53.073 CST 25744 50ae0334.6490 13 0]LOG:  restored
> log file "00010028002B" from archive
> [2012-11-22 18:50:53.538 CST 25744 50ae0334.6490 14 0]LOG:  restored
> log file "00010028002C" from archive
> [2012-11-22 18:50:53.999 CST 25744 50ae0334.6490 15 0]LOG:  restored
> log file "00010028002D" from archive
> [2012-11-22 18:50:54.459 CST 25744 50ae0334.6490 16 0]LOG:  restored
> log file "00010028002E" from archive
> [2012-11-22 18:50:54.920 CST 25744 50ae0334.6490 17 0]LOG:  restored
> log file "00010028002F" from archive
> cp: cannot stat `/export/t/xlog/000100280030': No such file or
> directory
> cp: cannot stat `/export/t/xlog/000100280030': No such file or
> directory
>
>
>
> As per logs,it is working as a host-standby. For doing the point in time
recovery,recovery.conf file requires only two settings:

restore_command='cp /t/xlog/%f %p'
recovery_target_time='2012-11-22 5:01:09 CST'

--Raghu Ram