Re: [GENERAL] Restore postgres to specific time

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

[GENERAL] create table in memory

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

Re: [GENERAL] create table in memory

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 >

Re: [GENERAL] create table in memory

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 mem

Re: [GENERAL] Restore postgres to specific time

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 mad

[GENERAL] w7 vs linux

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] w7 vs linux

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

Re: [GENERAL] create table in memory

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(

Re: [GENERAL] create table in memory

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 sign

Re: [GENERAL] COPY FROM in psql

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

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

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

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

Re: [GENERAL] w7 vs linux

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 so

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

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 QUE

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

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

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

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 retur

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

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

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

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 h

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

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

[GENERAL] Prevent x005F from xml

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

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

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 vari

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

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

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

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

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

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

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

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

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

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

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

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

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

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 t

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

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

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

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

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

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

[GENERAL] Npgsql

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

Re: [GENERAL] create table in memory

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 tes

Re: [GENERAL] Npgsq

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 ad

Re: [GENERAL] Restore postgres to specific time

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

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

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

Re: [GENERAL] obtain the difference between successive rows

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

[GENERAL] pgAgent reporting success for non-zero results

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 la

[GENERAL] Invalid argument

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] Querying information_schema [bug?]

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

Re: [GENERAL] High SYS CPU - need advise

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

Re: [GENERAL] High SYS CPU - need advise

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 fu

Re: [GENERAL] High SYS CPU - need advise

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 databas

Re: [GENERAL] obtain the difference between successive rows

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 successi

Re: [GENERAL] obtain the difference between successive rows

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',00301

Re: [GENERAL] High SYS CPU - need advise

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 exa

Re: [GENERAL] Invalid argument

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

Re: [GENERAL] High SYS CPU - need advise

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

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

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

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