Re: [GENERAL] File system level copy

[Albe Laurenz]

Hao Wang wrote:
 I installed PostgresSQL-8.3 on my linux machine.

 The cluster directory is /usr/local/data and I created three
databases
 named db1, db2, and db3. db1 is
 in the default tablespace 'pg_default'. db2 is in
 '/home/tablespace/space1/' and db3 is in
 '/home/tablespace/space2/'.  I want to copy the cluster directory
and
 the db3  tablespace
 folder('/home/tablespace/space2/') without stopping the database
 server. Then I want to use the
 cluster directory and db3's tablespace in another linux machine to
 recover 'db3' database. Does this
 way work? If not, why?
 
 First, you need a correct backup for recovery.
 Before copying, run pg_start_backup, and pg_stop_backup afterwards.
 
 Then you need to have recovery.conf and WAL archives (or be lucky and
all WALs are still in pg_xlog).
 
 WAL contains changes to all databases in the cluster, so you cannot
recover only one database, you'll
 have to recover them all.
 
 Read

http://www.postgresql.org/docs/current/static/continuous-archiving.html
 for background and details.

 This is PITR, right?
 I don't want to use this way because I'm not allowed to change the
configuration
 parameter of database server. I just want to use some whole DB copy to
restore
 db3 in another machine. And I don't want to use pg_dump because I
think db3
 is so large that pg_dump will probably have bad performance.

That's a whole lot of arbitrary restrictions.

If all you want is a copy of the database, pg_dump is what
you should use.  Besides, it is the only way to get a copy
of just one database.  What's the problem if pg_dump takes
a few hours or days (I don't know how big you DB is)?

A side thought: if the DB is not configured for PITR and
pg_dump takes too long, how do you perform your backups?

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] File system level copy

[Wang, Hao]

My purpose is not to do backup for my database. I just want to copy the whole 
db3 database to another machine and restore it. That database could be very 
large so I think directly copy is more efficient than pg_dump.  So I'd like to 
do some test to see if this way works. If it doesn't work, I will consider to 
use pg_dump.  
Thank you for your feedback.

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Albe Laurenz
Sent: Thursday, November 15, 2012 4:52 PM
To: Wang, Hao; pgsql-general@postgresql.org
Subject: Re: [GENERAL] File system level copy

Hao Wang wrote:
 I installed PostgresSQL-8.3 on my linux machine.

 The cluster directory is /usr/local/data and I created three
databases
 named db1, db2, and db3. db1 is
 in the default tablespace 'pg_default'. db2 is in 
 '/home/tablespace/space1/' and db3 is in '/home/tablespace/space2/'.  
 I want to copy the cluster directory
and
 the db3  tablespace
 folder('/home/tablespace/space2/') without stopping the database 
 server. Then I want to use the cluster directory and db3's 
 tablespace in another linux machine to recover 'db3' database. Does 
 this way work? If not, why?
 
 First, you need a correct backup for recovery.
 Before copying, run pg_start_backup, and pg_stop_backup afterwards.
 
 Then you need to have recovery.conf and WAL archives (or be lucky and
all WALs are still in pg_xlog).
 
 WAL contains changes to all databases in the cluster, so you cannot
recover only one database, you'll
 have to recover them all.
 
 Read

http://www.postgresql.org/docs/current/static/continuous-archiving.html
 for background and details.

 This is PITR, right?
 I don't want to use this way because I'm not allowed to change the
configuration
 parameter of database server. I just want to use some whole DB copy to
restore
 db3 in another machine. And I don't want to use pg_dump because I
think db3
 is so large that pg_dump will probably have bad performance.

That's a whole lot of arbitrary restrictions.

If all you want is a copy of the database, pg_dump is what you should use.  
Besides, it is the only way to get a copy of just one database.  What's the 
problem if pg_dump takes a few hours or days (I don't know how big you DB is)?

A side thought: if the DB is not configured for PITR and pg_dump takes too 
long, how do you perform your backups?

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



-- 
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] Finding first free time from reservations table

[hari . fuchs]

Andrus kobrule...@hot.ee writes:

 How to find first free half hour in table which is not reserved ?
  
 E.q if table contains
  
  startdate   starthour  duration
  14  9   1  -- ends at 9:59
  14  10  1.5-- ends at 11:29, e.q there is
 30 minute gap before next
  14  12  2
  14  16  2
  
 result should be:
  
  starthour  duration
  11.5   0.5
  
   
 Probably PostgreSql 9.2 window function should used to find
 first row whose starthour is greater than previous row starthour +
 duration 

Yes, you could use something like this:

SELECT min(c1)
FROM (
SELECT starthour + duration AS c1,
   lead(starthour) OVER (ORDER BY starthour) AS c2
FROM tst
  ) dummy
WHERE c2 = c1 + 0.5



-- 
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] File system level copy

[Albe Laurenz]

Hao Wang wrote:

 My purpose is not to do backup for my database.

I understood that.  It was just a side comment.

 I just want to copy the whole db3 database to another
 machine and restore it. That database could be very large so I think
directly copy is more efficient
 than pg_dump.  So I'd like to do some test to see if this way works.
If it doesn't work, I will
 consider to use pg_dump.

Any attempt to make a file system copy of a live system without
doing PITR will very likely cause data corruption.

You'll have to use pg_dump.

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

[GENERAL] Postgres Index

[Vishalakshi Navaneethakrishnan]

Hi all,

Can we create composite index for one text column and integer column?

Thanks in advance..

-- 
Best Regards,
Vishalakshi.N

Re: [GENERAL] Postgres Index

[Thomas Kellerer]

Vishalakshi Navaneethakrishnan, 15.11.2012 12:11:

Hi all,

Can we create composite index for one text column and integer column?



Yes of course. What happened when you tried?






--
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] Postgres Index

[Albe Laurenz]

Vishalakshi Navaneethakrishnan wrote:
 Can we create composite index for one text column and integer column?

Yes.

It would probably have been faster to try it than
to send this e-mail...

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] Strategies/Best Practises Handling Large Tables

[Chitra Creta]

Thanks for your example Chris. I will look into it as a long-term solution.

Partitioning tables as a strategy worked very well indeed. This will be my
short/medium term solution.

Another strategy that I would like to evaluate as a short/medium term
solution is archiving old records in a table before purging them.

I am aware that Oracle has a tool that allows records to be exported into a
file / archive table before purging them. They also provide a tool to
import these records.

Does PostgreSQL have similar tools to export to a file and re-import?

If PostgreSQL does not have a tool to do this, does anyone have any ideas
on what file format (e.g. text file containing a table of headers being
column names and rows being records) would be ideal for easy re-importing
into a PostgreSQL table?

Thank you for your ideas.


On Mon, Oct 22, 2012 at 12:14 AM, Chris Travers chris.trav...@gmail.comwrote:



 On Sun, Oct 21, 2012 at 5:46 AM, Chitra Creta chitracr...@gmail.comwrote:

 Thank you all.

 Ryan, would you mind sharing your one-time function to move it?

 Merlin, what are your suggestions to improve query performance?

 Shaun, thank you. I will look into facts and dimensions should all else
 fail.

 Chris, would you mind giving me an example of what you mean by your log,
 aggregate and snapshot approach. Also, with indexing, I believe composite
 and partial indexes are better than indexes, am I correct? Do you have any
 recommendations as to which type (e.g btree, hash) is better for which
 situations.


 Sure.  Suppose I have an accounting system.

 I may record the amounts in the transactions in a journal_entry and
 journal_line table.   These will be write once read many.  However time you
 will end up having to digest millions of records (given sufficient volume)
 to find out the balance of a checking account, and this is not really ideal.

 So to deal with this, I might, for example, add a table called
 account_checkpoint which might have the following fields:

 account_id
 end_date
 debits
 credits
 balance

 And then I can snapshot on closing of books the accumulated debits,
 credits, and balance to date.  If I need any of these numbers I can just
 grab the appropriate number from account_checkpoint and roll forward from
 end_date.  If I have too much volume I can have closings on a monthly level
 of whatever.

 The checkpoint table contains sufficient information for me to start a
 report at any point and end it at any other point without having to scan
 interceding checkpointed periods.  Additionally if I want to purge old
 data, I can do so without losing current balance information.

 So what this approach does, in essence is it gives you a way to purge
 without losing some aggregated information, and a way to skip portions of
 the table for aggregation purposes you can't skip otherwise.  The big thing
 is you cannot insert (and if this is in doubt, you need to enforce this
 with a trigger) any records effective before the most recent checkpoint.

 Best Wishes,
 Chris Travers

Re: [GENERAL] Postgres Index

[Dann Corbit]

Here is the current formal definition for index creation:
http://www.postgresql.org/docs/9.2/static/sql-createindex.html


From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vishalakshi 
Navaneethakrishnan
Sent: Thursday, November 15, 2012 3:12 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgres Index

Hi all,

Can we create composite index for one text column and integer column?

Thanks in advance..

--
Best Regards,
Vishalakshi.N

[GENERAL] How to list all schema names inside a PostgreSQL database through SQL

[Xiaobo Gu]

Hi,

How can I list  all schema names inside a PostgreSQL database through
SQL, especially thoese without any objects created inside it.


Regards,

Xiaobo Gu


-- 
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 list all schema names inside a PostgreSQL database through SQL

[Виктор Егоров]

2012/11/15 Xiaobo Gu guxiaobo1...@gmail.com:
 How can I list  all schema names inside a PostgreSQL database through
 SQL, especially thoese without any objects created inside it.

Something like this:
select n.nspname, count(o.oid)
  from pg_namespace n
  left join pg_class o on n.oid=o.relnamespace
 group by 1
 order by count(o.oid)0, 1;

I prefer to query PostgreSQL catalogs.
You can obtain the same information using information_schema queries.


-- 
Victor Y. Yegorov


-- 
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 list all schema names inside a PostgreSQL database through SQL

[Achilleas Mantzios]

On Πεμ 15 Νοε 2012 20:31:05 Xiaobo Gu wrote:
 Hi,
 
 How can I list  all schema names inside a PostgreSQL database through
 SQL, especially thoese without any objects created inside it.
 
 

1st solution :

select catalog_name,schema_name from information_schema.schemata ;

2nd solution :

select * from pg_namespace ;

 Regards,
 
 Xiaobo Gu
 
 
 
-
Achilleas Mantzios
IT DEPT


-- 
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 list all schema names inside a PostgreSQL database through SQL

[sk baji]

If you are looking for list of empty schema's (No objects in schema), then
you can use below query:

select nspname from pg_namespace where oid not in (select relnamespace from
pg_class) and oid not in (select oid from pg_proc);


Regards,
Baji Shaik.

On Thu, Nov 15, 2012 at 6:13 PM, Achilleas Mantzios 
ach...@matrix.gatewaynet.com wrote:

 On Πεμ 15 Ποε 2012 20:31:05 Xiaobo Gu wrote:
  Hi,
 
  How can I list  all schema names inside a PostgreSQL database through
  SQL, especially thoese without any objects created inside it.
 
 

 1st solution :

 select catalog_name,schema_name from information_schema.schemata ;

 2nd solution :

 select * from pg_namespace ;

  Regards,
 
  Xiaobo Gu
 
 
 
 -
 Achilleas Mantzios
 IT DEPT


 --
 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] Strategies/Best Practises Handling Large Tables

[Igor Romanchenko]

On Thu, Nov 15, 2012 at 1:34 PM, Chitra Creta chitracr...@gmail.com wrote:

 Thanks for your example Chris. I will look into it as a long-term solution.

 Partitioning tables as a strategy worked very well indeed. This will be my
 short/medium term solution.

 Another strategy that I would like to evaluate as a short/medium term
 solution is archiving old records in a table before purging them.

 I am aware that Oracle has a tool that allows records to be exported into
 a file / archive table before purging them. They also provide a tool to
 import these records.

 Does PostgreSQL have similar tools to export to a file and re-import?

 If PostgreSQL does not have a tool to do this, does anyone have any ideas
 on what file format (e.g. text file containing a table of headers being
 column names and rows being records) would be ideal for easy re-importing
 into a PostgreSQL table?

 Thank you for your ideas.


PostgreSQL has COPY TO to export records to a file (
http://wiki.postgresql.org/wiki/COPY ).

[GENERAL] Plproxy with returns table() make PG segfault

[Sébastien Lardière]

Hi,

I've a segfault on a PostgreSQL 9.1 cluster, with a plproxy function
call. Both PostgreSQL and plproxy are up to date. I use SQL/MED in that
specific case, but it's the same without. I reproduced the following
scenario on a few clusters, with or without streaming replication.

On a given cluster, I created this function (very stupid, I know) (note
the « returns table () » ) :

p0=# create function testtoto( id int)
returns table(id int, t text)
language sql
as $$
select * from (values(1, 'test'),(2, 'toto') )as toto;
$$;
p0=# \df testtoto
List of functions
Schema | Name | Result data type | Argument data types | Type
+--+---+-+
public | testtoto | TABLE(id integer, t text) | id integer | normal
(1 row)


And I use it on another cluster, by doing this :

test=# CREATE SERVER test FOREIGN DATA WRAPPER plproxy
options( connection_lifetime '200',p0 'host=localhost port=5433
dbname=p0 user=postgres');
CREATE USER MAPPING FOR postgres SERVER test OPTIONS (user 'postgres');

-- this function returns setof record

test=# CREATE OR REPLACE FUNCTION test(IN _account_id integer, OUT id
integer, OUT t text )
RETURNS setof record
LANGUAGE plproxy
AS $function$
CLUSTER 'test';
TARGET testtoto;
$function$;

-- this on returns TABLE()

test=# CREATE OR REPLACE FUNCTION test2(IN _account_id integer)
returns TABLE( id integer, t text )
LANGUAGE plproxyAS $function$
CLUSTER 'test';
TARGET testtoto;
$function$;

When I call test(), everything is OK :

test=# select * from test(1);
id | t
+--
1 | test
2 | toto
(2 rows)

But when I call test2(1) :

test=# select pg_backend_pid();
pg_backend_pid

25330
(1 row)
test=# select * from test2(1);
The connection to the server was lost. Attempting reset: Failed.
!

Badaboum !

And the log reads :

2012-11-15 18:07:55 CET LOG: server process (PID 25330) was terminated
by signal 11: Segmentation fault
2012-11-15 18:07:55 CET LOG: terminating any other active server processes
2012-11-15 18:07:55 CET WARNING: terminating connection because of crash
of another server process
2012-11-15 18:07:55 CET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2012-11-15 18:07:55 CET HINT: In a moment you should be able to
reconnect to the database and repeat your command.

…

When I try to debug the session, I get this trace :

(gdb)
Run till exit from #0 PostgresMain (argc=optimized out,
argv=optimized out, username=optimized out) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/tcop/postgres.c:3932

Program received signal SIGSEGV, Segmentation fault.
pg_detoast_datum_packed (datum=0x8) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:2272
2272 if (VARATT_IS_COMPRESSED(datum) || VARATT_IS_EXTERNAL(datum))
(gdb) bt
#0 pg_detoast_datum_packed (datum=0x8) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:2272
#1 0x7f26ea7c0040 in text_to_cstring (t=0x8) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/adt/varlena.c:135
#2 0x7f26ea80a8c6 in FunctionCall1Coll (flinfo=0x7f26eb7fa2b8,
collation=0, arg1=34359738368) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:1300
#3 0x7f26ea80ba2d in OutputFunctionCall (flinfo=0x7f26eb7fa2b8,
val=34359738368) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:1953
#4 0x7f26611c2577 in plproxy_send_type () from
/usr/lib/postgresql/9.1/lib/plproxy.so
#5 0x7f26611bfe66 in plproxy_exec () from
/usr/lib/postgresql/9.1/lib/plproxy.so
#6 0x7f26611c1634 in ?? () from /usr/lib/postgresql/9.1/lib/plproxy.so
#7 0x7f26611c1865 in plproxy_call_handler () from
/usr/lib/postgresql/9.1/lib/plproxy.so
#8 0x7f26ea677985 in ExecMakeTableFunctionResult
(funcexpr=0x7f26eb7e9020, econtext=0x7f26eb7e7ff0,
expectedDesc=optimized out, randomAccess=optimized out)
at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execQual.c:2146
#9 0x7f26ea688471 in FunctionNext (node=0x7f26eb7e7ee0) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/nodeFunctionscan.c:66
#10 0x7f26ea678657 in ExecScanFetch (recheckMtd=optimized out,
accessMtd=optimized out, node=optimized out) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execScan.c:82
#11 ExecScan (node=0x7f26eb7e7ee0, accessMtd=optimized out,
recheckMtd=optimized out) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execScan.c:132
#12 0x7f26ea670da8 in ExecProcNode (node=0x7f26eb7e7ee0) at
/opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execProcnode.c:416
#13 0x7f26ea66fbf2 in ExecutePlan (dest=optimized out,
direction=optimized out, numberTuples=optimized out,
sendTuples=optimized out, operation=optimized out,
planstate=optimized out, estate=optimized out)
at

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Wed, Nov 14, 2012 at 4:08 PM, John R Pierce pie...@hogranch.com wrote:
 On 11/14/12 1:34 PM, Vlad wrote:

 thanks for your feedback. While implementing connection pooling would make
 resources utilization more efficient, I don't think it's the root of my
 problem. Most of the connected clients are at IDLE. When I do

 select * from pg_stat_activity where current_query not like '%IDLE%';

 I only see several active queries at any given time.


 what about during these spikes?

Yeah -- if you are seeing a lot of queries pile up during these times,
it's time to explore connection pooler because it will keep system
load manageable during these situations.

things to check:
*) blocked queries (pg_locks/pg_stat_activity)
*) i/o wait. in particular, is linux page cache flushing.
*) query stampede: we will want to measure TPS leading into the stall
and out of it.
*) anything else running on the box?
*) you have a large amount of table? maybe this statistics file related?
*) let's log checkpoints to see if there is correlation with stall
*) nice to have vmstat/iostat during/before/after stall.  for example,
massive spike of context switches during stall could point to o/s
scheduler issue.

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]

there is no big spike of queries that cause that, queries come in
relatively stable pace. It's just when the higher rate of queries coming,
the more likely this to happen.  yes, when stall happens , the active
queries pile up - but that's the result of a stall (the server reacts slow
on a keypress, not to mention queries execution), not the cause.


things to check:
 *) blocked queries (pg_locks/pg_stat_activity)


nada


 *) i/o wait. in particular, is linux page cache flushing.


no i/o wait, no IRQ (see below)


 *) query stampede: we will want to measure TPS leading into the stall
 and out of it.
 *) anything else running on the box?


just bare linux + postgresql.


 *) you have a large amount of table? maybe this statistics file related?


over 1000 tables across 4 or 5 schemas in a single database.

*) let's log checkpoints to see if there is correlation with stall


checked, checkpoints happen must more rarely and w/o relation to a high-sys
periods


 *) nice to have vmstat/iostat during/before/after stall.  for example,
 massive spike of context switches during stall could point to o/s
 scheduler issue.



checked that as well - nothing. CS even lower.

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  16.940.009.280.380.00   73.40

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda   6.0048.00 0.00 48  0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  18.060.00   18.430.250.00   63.26

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda  10.00   104.00 0.00104  0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   9.120.00  * 86.74*0.120.004.01

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda   0.97 7.77 0.00  8  0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
   1.440.00   *96.58*0.000.001.98

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
sda   3.2878.69 0.00144  0



procs ---memory-- ---swap-- -io --system--
-cpu-
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
wa st
 1  0  0 279240  12016 144319640032 0 197852 4299 15  9
76  0  0
 4  0  0 225984  12024 1441969600 064 197711 5158 11  9
79  1  0
 0  0  0 260112  12024 144136360048 0 196708 4618 17 10
73  0  0
 6  0  0 233936  12024 1437578400   104 0 179861 4884 19 17
64  0  0
30  0  0 224904  12024 1435481200 8 0 51088 1205  9 *86
 *5  0  0
72  0  0 239144  12024 1433385200   144 0 45601  542  2 *98
 *0  0  0
78  0  0 224840  12024 1432853600 0 0 38732  481  2 *94
 *5  0  0
22  1  0 219072  12032 1425065200   136   100 47323 1231  9 *90
 *1  0  0

Re: [GENERAL] Finding first free time from reservations table

[Steve Crawford]

On 11/14/2012 01:02 PM, Andrus wrote:

I’m looking for a way to find first free time in reservations table.
Reservation table contains reservations start dates, start hours and 
durations.
Start hour is by half hour increments in working hours 8:00 .. 18:00 
in work days.

Duration is also by half hour increments in day.
CREATE TABLE reservation (
  id serial primary key,
  startdate date not null,  -- start date
  starthour numeric(4,1) not null , -- start hour 8 8.5 9 9.5  ..  
16.5 17 17.5
  duration  Numeric(3,1) not null -- duration by hours 0.5 1 1.5 
.. 9 9.5 10

);
table structure can changed if required.


I'm not sure if it will work well for your specific use-case and it 
requires an up-to-date version (9.2+??) but I would recommend 
investigating range types which have some characteristics that are 
useful for reservation and calendaring applications including the 
ability to have a non-overlapping constraint that prevents creating a 
record with a range that overlaps an existing range in the table.


Instead of having three columns (startdate, starthour and duration) you 
would have a single column of type tsrange which includes the starting- 
and ending-times of each reservation.


Here's the info on range types: 
http://www.postgresql.org/docs/9.2/static/rangetypes.html


If you want to limit reservations to start/end at half-hours and/or to 
certain times of the day you will probably want to include those 
constraints in your table definition.


You asked about finding a free half-hour but since you show durations 
that exceed a half-hour, you may want to include the capability to 
search for the first available occurrence of X free-time.


Range-types are new and I'm not experienced with them - others may have 
better ideas - but the method of finding the first occurrence that 
springs to mind is to make a query that uses generate_series to create a 
list of candidate reservation periods of the desired duration and 
select the first one that doesn't overlap an existing reservation. This 
should work fine as long as you are looking a limited time in the future 
(there are fewer than 20 possible start-times in a day so even looking 
100-days ahead is only 2000 candidates) however generating a series of 
ranges may involve a sub-query - I don't know if you can generate a 
series of ranges directly.


Hope this helps.

Cheers,
Steve

Re: [GENERAL] Plproxy with returns table() make PG segfault

[Cédric Villemain]

top post: this looks like a plproxy bug (no ?), I've added Marko in CC.

 I've a segfault on a PostgreSQL 9.1 cluster, with a plproxy function
 call. Both PostgreSQL and plproxy are up to date. I use SQL/MED in that
 specific case, but it's the same without. I reproduced the following
 scenario on a few clusters, with or without streaming replication.
 
 On a given cluster, I created this function (very stupid, I know) (note
 the « returns table () » ) :
 
 p0=# create function testtoto( id int)
 returns table(id int, t text)
 language sql
 as $$
 select * from (values(1, 'test'),(2, 'toto') )as toto;
 $$;
 p0=# \df testtoto
 List of functions
 Schema | Name | Result data type | Argument data types | Type
 +--+---+-+-
 --- public | testtoto | TABLE(id integer, t text) | id integer | normal (1
 row)
 
 
 And I use it on another cluster, by doing this :
 
 test=# CREATE SERVER test FOREIGN DATA WRAPPER plproxy
 options( connection_lifetime '200',p0 'host=localhost port=5433
 dbname=p0 user=postgres');
 CREATE USER MAPPING FOR postgres SERVER test OPTIONS (user 'postgres');
 
 -- this function returns setof record
 
 test=# CREATE OR REPLACE FUNCTION test(IN _account_id integer, OUT id
 integer, OUT t text )
 RETURNS setof record
 LANGUAGE plproxy
 AS $function$
 CLUSTER 'test';
 TARGET testtoto;
 $function$;
 
 -- this on returns TABLE()
 
 test=# CREATE OR REPLACE FUNCTION test2(IN _account_id integer)
 returns TABLE( id integer, t text )
 LANGUAGE plproxyAS $function$
 CLUSTER 'test';
 TARGET testtoto;
 $function$;
 
 When I call test(), everything is OK :
 
 test=# select * from test(1);
 id | t
 +--
 1 | test
 2 | toto
 (2 rows)
 
 But when I call test2(1) :
 
 test=# select pg_backend_pid();
 pg_backend_pid
 
 25330
 (1 row)
 test=# select * from test2(1);
 The connection to the server was lost. Attempting reset: Failed.
 !
 
 Badaboum !
 
 And the log reads :
 
 2012-11-15 18:07:55 CET LOG: server process (PID 25330) was terminated
 by signal 11: Segmentation fault
 2012-11-15 18:07:55 CET LOG: terminating any other active server processes
 2012-11-15 18:07:55 CET WARNING: terminating connection because of crash
 of another server process
 2012-11-15 18:07:55 CET DETAIL: The postmaster has commanded this server
 process to roll back the current transaction and exit, because another
 server process exited abnormally and possibly corrupted shared memory.
 2012-11-15 18:07:55 CET HINT: In a moment you should be able to
 reconnect to the database and repeat your command.
 
 …
 
 When I try to debug the session, I get this trace :
 
 (gdb)
 Run till exit from #0 PostgresMain (argc=optimized out,
 argv=optimized out, username=optimized out) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/tcop/postgres.c:3932
 
 Program received signal SIGSEGV, Segmentation fault.
 pg_detoast_datum_packed (datum=0x8) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:2272
 2272 if (VARATT_IS_COMPRESSED(datum) || VARATT_IS_EXTERNAL(datum))
 (gdb) bt
 #0 pg_detoast_datum_packed (datum=0x8) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:2272
 #1 0x7f26ea7c0040 in text_to_cstring (t=0x8) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/adt/varlena.c:135
 #2 0x7f26ea80a8c6 in FunctionCall1Coll (flinfo=0x7f26eb7fa2b8,
 collation=0, arg1=34359738368) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:1300
 #3 0x7f26ea80ba2d in OutputFunctionCall (flinfo=0x7f26eb7fa2b8,
 val=34359738368) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/utils/fmgr/fmgr.c:1953
 #4 0x7f26611c2577 in plproxy_send_type () from
 /usr/lib/postgresql/9.1/lib/plproxy.so
 #5 0x7f26611bfe66 in plproxy_exec () from
 /usr/lib/postgresql/9.1/lib/plproxy.so
 #6 0x7f26611c1634 in ?? () from /usr/lib/postgresql/9.1/lib/plproxy.so
 #7 0x7f26611c1865 in plproxy_call_handler () from
 /usr/lib/postgresql/9.1/lib/plproxy.so
 #8 0x7f26ea677985 in ExecMakeTableFunctionResult
 (funcexpr=0x7f26eb7e9020, econtext=0x7f26eb7e7ff0,
 expectedDesc=optimized out, randomAccess=optimized out)
 at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execQual.c:2146
 #9 0x7f26ea688471 in FunctionNext (node=0x7f26eb7e7ee0) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/nodeFunctionsca
 n.c:66 #10 0x7f26ea678657 in ExecScanFetch (recheckMtd=optimized out,
 accessMtd=optimized out, node=optimized out) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execScan.c:82
 #11 ExecScan (node=0x7f26eb7e7ee0, accessMtd=optimized out,
 recheckMtd=optimized out) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execScan.c:132
 #12 0x7f26ea670da8 in ExecProcNode (node=0x7f26eb7e7ee0) at
 /opt/src/postgresql-9.1-9.1.6/build/../src/backend/executor/execProcnode.c:
 416 #13 0x7f26ea66fbf2 in 

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Thu, Nov 15, 2012 at 11:50 AM, Vlad marche...@gmail.com wrote:
 there is no big spike of queries that cause that, queries come in relatively
 stable pace. It's just when the higher rate of queries coming, the more
 likely this to happen.  yes, when stall happens , the active queries pile up
 - but that's the result of a stall (the server reacts slow on a keypress,
 not to mention queries execution), not the cause.

 procs ---memory-- ---swap-- -io --system--
 -cpu-
  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
 wa st
  1  0  0 279240  12016 144319640032 0 197852 4299 15  9
 76  0  0
  4  0  0 225984  12024 1441969600 064 197711 5158 11  9
 79  1  0
  0  0  0 260112  12024 144136360048 0 196708 4618 17 10
 73  0  0
  6  0  0 233936  12024 1437578400   104 0 179861 4884 19 17
 64  0  0
 30  0  0 224904  12024 1435481200 8 0 51088 1205  9 86
 5  0  0
 72  0  0 239144  12024 1433385200   144 0 45601  542  2 98
 0  0  0
 78  0  0 224840  12024 1432853600 0 0 38732  481  2 94
 5  0  0
 22  1  0 219072  12032 1425065200   136   100 47323 1231  9 90
 1  0  0

hm.  well, we can definitely rule out i/o.   I reviewed your last
posting, and you said:
Out of the top 50 processes in top, 48 of them are postmasters, one
is syslog, and one is psql. Each of the postmasters have a high %CPU,
the top ones being 80% and higher, the rest being anywhere between 30%
- 60%. Would postmaster 'queries' that are running attribute to the
sys CPU usage, or should they be under the 'us' CPU usage?

Is this still true?  Can we capture strace from one of the high %
postmasters to see if there's any clues there.   Maybe we've uncovered
some type of weird spinlock contention issue.  How large is your
database (or at least the portion of it that's commonly used)?  Would
you characterize your queries as mostly small lookups, scans, or a
mix?

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,

this is not my report, probably from a thread that I've referenced as
having a common symptoms. Here is info about my db:


Postgresql 9.1.6.
Postgres usually has 400-500 connected clients, most of them are idle.
Database is over 1000 tables (across 5 namespaces), taking ~150Gb on disk.
We have mostly select statements (joins across few tables), using indexes
and resulting in a small number of records returned.

So mostly small lookups across few tables joining by a primary key / index.
Also, I just perform an experiment - I switched our app over to using hot
PITR instead of master and it experienced the same problem. So since PITR
db can only perform read-only queries, there is no write-locks (except
maybe when pitr is playing wal records from the master?), nevertheless SYS
CPU jumped sky.

-- Vlad




 Is this still true?  Can we capture strace from one of the high %
 postmasters to see if there's any clues there.   Maybe we've uncovered
 some type of weird spinlock contention issue.  How large is your
 database (or at least the portion of it that's commonly used)?  Would
 you characterize your queries as mostly small lookups, scans, or a
 mix?

 merlin

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Thu, Nov 15, 2012 at 2:20 PM, Vlad marche...@gmail.com wrote:
 Merlin,

 this is not my report, probably from a thread that I've referenced as having
 a common symptoms. Here is info about my db:


 Postgresql 9.1.6.
 Postgres usually has 400-500 connected clients, most of them are idle.
 Database is over 1000 tables (across 5 namespaces), taking ~150Gb on disk.
 We have mostly select statements (joins across few tables), using indexes
 and resulting in a small number of records returned.

 So mostly small lookups across few tables joining by a primary key / index.
 Also, I just perform an experiment - I switched our app over to using hot
 PITR instead of master and it experienced the same problem. So since PITR db
 can only perform read-only queries, there is no write-locks (except maybe
 when pitr is playing wal records from the master?), nevertheless SYS CPU
 jumped sky.

yeah.  ok, nest steps:
*) can you confirm that postgres process is using high cpu (according
to top) during stall time
*) if, so, please strace that process and save some of the log
*) you're using a 'bleeding edge' kernel.  so we must be suspicious of
a regression there, particularly in the scheduler.
*) I am suspicious of spinlock issue. so, if we can't isolate the
problem, is running a hand complied postgres a possibility (for lock
stats)?
*) what is the output of this:
echo /proc/sys/vm/zone_reclaim_mode

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]

 yeah.  ok, nest steps:
 *) can you confirm that postgres process is using high cpu (according
 to top) during stall time


yes, CPU is spread across a lot of postmasters

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
29863 pgsql 20   0 3636m 102m  36m R 19.1  0.3   0:01.33 postmaster
30277 pgsql 20   0 3645m 111m  37m R 16.8  0.3   0:01.27 postmaster
11966 pgsql 20   0 3568m  22m  15m R 15.1  0.1   0:00.66 postmaster
 8073 pgsql 20   0 3602m  60m  26m S 13.6  0.2   0:00.77 postmaster
29780 pgsql 20   0 3646m 115m  43m R 13.6  0.4   0:01.13 postmaster
11865 pgsql 20   0 3606m  61m  23m S 12.8  0.2   0:01.87 postmaster
29379 pgsql 20   0 3603m  70m  30m R 12.8  0.2   0:00.80 postmaster
29727 pgsql 20   0 3616m  77m  31m R 12.5  0.2   0:00.81 postmaster




 *) if, so, please strace that process and save some of the log


https://dl.dropbox.com/u/109778/stall_postmaster.log


 *) you're using a 'bleeding edge' kernel.  so we must be suspicious of
 a regression there, particularly in the scheduler.


this was observed for a while, during which period system went from using
3.4.* kernels to 3.5.*... but I do not deny such a possibility.


 *) I am suspicious of spinlock issue. so, if we can't isolate the
 problem, is running a hand complied postgres a possibility (for lock
 stats)?



Yes, definitely possible. we run manually compiled postgresql anyway. Pls,
provide instructions.




 *) what is the output of this:
 echo /proc/sys/vm/zone_reclaim_mode


I presume you wanted cat instead of echo, and it shows 0.


-- vlad

Re: [GENERAL] How to list all schema names inside a PostgreSQL database through SQL

[Ondrej Ivanič]

Hi,

On 15 November 2012 23:31, Xiaobo Gu guxiaobo1...@gmail.com wrote:
 How can I list  all schema names inside a PostgreSQL database through
 SQL, especially thoese without any objects created inside it.

Use -E psql's option:
  -E, --echo-hiddendisplay queries that internal commands generate

then you get SQL query for each internal command.

The second option is to use information_schema.schemata view (this is
works across databases)


--
Ondrej Ivanic
(http://www.linkedin.com/in/ondrejivanic)


-- 
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 Thu, Nov 15, 2012 at 2:44 PM, Vlad marche...@gmail.com wrote:


 yeah.  ok, nest steps:
 *) can you confirm that postgres process is using high cpu (according
 to top) during stall time


 yes, CPU is spread across a lot of postmasters

   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
 29863 pgsql 20   0 3636m 102m  36m R 19.1  0.3   0:01.33 postmaster
 30277 pgsql 20   0 3645m 111m  37m R 16.8  0.3   0:01.27 postmaster
 11966 pgsql 20   0 3568m  22m  15m R 15.1  0.1   0:00.66 postmaster
  8073 pgsql 20   0 3602m  60m  26m S 13.6  0.2   0:00.77 postmaster
 29780 pgsql 20   0 3646m 115m  43m R 13.6  0.4   0:01.13 postmaster
 11865 pgsql 20   0 3606m  61m  23m S 12.8  0.2   0:01.87 postmaster
 29379 pgsql 20   0 3603m  70m  30m R 12.8  0.2   0:00.80 postmaster
 29727 pgsql 20   0 3616m  77m  31m R 12.5  0.2   0:00.81 postmaster


 *) if, so, please strace that process and save some of the log


 https://dl.dropbox.com/u/109778/stall_postmaster.log

ok, excellent.   reviewing the log, this immediately caught my eye:

recvfrom(8, \27\3\1\0@, 5, 0, NULL, NULL) = 5
recvfrom(8, 
\327\327\nl\231LD\211\346\243@WW\254\244\363C\326\247\341\177\255\263~\327HDv-\3466\353...,
64, 0, NULL, NULL) = 64
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 4000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 6000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 8000})  = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 9000})  = 0 (Timeout)
semop(41713721, {{2, 1, 0}}, 1) = 0
lseek(295, 0, SEEK_END) = 0
lseek(296, 0, SEEK_END) = 8192

this is definitely pointing to spinlock issue. see: slock.c what you
are seeing here is a backend getting caught in a spin loop via a stuck
spinlock. 0-9 = 10 times.  did you by any chance check the logs? did
any backends restart? we are looking for this:

elog(PANIC, stuck spinlock (%p) detected at %s:%d,
 lock, file, line);

Anything else going on in the log?   The way to enable locks status is
via LWLOCK_STATS macro.  But before doing any thing plz check logs for
error/panic.

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

[Merlin Moncure]

On Thu, Nov 15, 2012 at 3:49 PM, Merlin Moncure mmonc...@gmail.com wrote:
 On Thu, Nov 15, 2012 at 2:44 PM, Vlad marche...@gmail.com wrote:


 yeah.  ok, nest steps:
 *) can you confirm that postgres process is using high cpu (according
 to top) during stall time


 yes, CPU is spread across a lot of postmasters

   PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND
 29863 pgsql 20   0 3636m 102m  36m R 19.1  0.3   0:01.33 postmaster
 30277 pgsql 20   0 3645m 111m  37m R 16.8  0.3   0:01.27 postmaster
 11966 pgsql 20   0 3568m  22m  15m R 15.1  0.1   0:00.66 postmaster
  8073 pgsql 20   0 3602m  60m  26m S 13.6  0.2   0:00.77 postmaster
 29780 pgsql 20   0 3646m 115m  43m R 13.6  0.4   0:01.13 postmaster
 11865 pgsql 20   0 3606m  61m  23m S 12.8  0.2   0:01.87 postmaster
 29379 pgsql 20   0 3603m  70m  30m R 12.8  0.2   0:00.80 postmaster
 29727 pgsql 20   0 3616m  77m  31m R 12.5  0.2   0:00.81 postmaster


 *) if, so, please strace that process and save some of the log


 https://dl.dropbox.com/u/109778/stall_postmaster.log

 ok, excellent.   reviewing the log, this immediately caught my eye:

 recvfrom(8, \27\3\1\0@, 5, 0, NULL, NULL) = 5
 recvfrom(8, 
 \327\327\nl\231LD\211\346\243@WW\254\244\363C\326\247\341\177\255\263~\327HDv-\3466\353...,
 64, 0, NULL, NULL) = 64
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 4000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 6000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 8000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 9000})  = 0 (Timeout)
 semop(41713721, {{2, 1, 0}}, 1) = 0
 lseek(295, 0, SEEK_END) = 0
 lseek(296, 0, SEEK_END) = 8192

 this is definitely pointing to spinlock issue. see: slock.c what you
 are seeing here is a backend getting caught in a spin loop via a stuck
 spinlock. 0-9 = 10 times.  did you by any chance check the logs? did
 any backends restart? we are looking for this:

 elog(PANIC, stuck spinlock (%p) detected at %s:%d,
  lock, file, line);

 Anything else going on in the log?   The way to enable locks status is
 via LWLOCK_STATS macro.  But before doing any thing plz check logs for
 error/panic.

ah, scratch that.  reading the code again, slock.c allows for for up
to 1 second (that is, you can delay 1000 times).  so the panic is
unlikely to be in the log.  also spinlock waits shouldn't increase by
exactly 1000 us. investigating...

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

[Alvaro Herrera]

Merlin Moncure escribió:

 ok, excellent.   reviewing the log, this immediately caught my eye:
 
 recvfrom(8, \27\3\1\0@, 5, 0, NULL, NULL) = 5
 recvfrom(8, 
 \327\327\nl\231LD\211\346\243@WW\254\244\363C\326\247\341\177\255\263~\327HDv-\3466\353...,
 64, 0, NULL, NULL) = 64
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 4000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 6000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 8000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 9000})  = 0 (Timeout)
 semop(41713721, {{2, 1, 0}}, 1) = 0
 lseek(295, 0, SEEK_END) = 0
 lseek(296, 0, SEEK_END) = 8192
 
 this is definitely pointing to spinlock issue.

I met Rik van Riel (Linux kernel hacker) recently and we chatted about
this briefly.  He strongly suggested that we should consider using
futexes on Linux instead of spinlocks; the big advantage being that
futexes sleep instead of spinning when contention is high.  That would
reduce the system load in this scenario.

-- 
Álvaro Herrerahttp://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]

sorry - no panics / errors in the log...

-- Vlad

[GENERAL] Percent of Total in Histogram Query

[Paul Jungwirth]

Hello,

I'd like to write a histogram-like query that shows these columns:
  - x-value from 0 to k.
  - number of rows with that x-value.
  - number of rows seen so far (i.e. with the current x-value or less).
  - % of total rows seen so far.

The following query works for the first three columns, but the last column
gives me an error:

SELECT c,
COUNT(*) AS items_count,
SUM(COUNT(*)) OVER (ORDER BY c) AS total_items_count,
SUM(COUNT(*)) OVER (ORDER BY c) / SUM(COUNT(*)) AS
total_items_perc
FROM(SELECT p.id, COUNT(*) c
 FROM   parent p, child ch
 WHERE  p.id = ch.parent_id
 GROUP BY p.id
) x
GROUP BY x.c
ORDER BY x.c

Including that fourth SELECT column gives me this error:

ERROR:  aggregate function calls cannot be nested

Is there any way to get % of total in a query like this?

Thanks,
Paul

-- 
_
Pulchritudo splendor veritatis.

Re: [GENERAL] High SYS CPU - need advise

[Merlin Moncure]

On Thu, Nov 15, 2012 at 4:29 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Merlin Moncure escribió:

 ok, excellent.   reviewing the log, this immediately caught my eye:

 recvfrom(8, \27\3\1\0@, 5, 0, NULL, NULL) = 5
 recvfrom(8, 
 \327\327\nl\231LD\211\346\243@WW\254\244\363C\326\247\341\177\255\263~\327HDv-\3466\353...,
 64, 0, NULL, NULL) = 64
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 4000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 6000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 8000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 9000})  = 0 (Timeout)
 semop(41713721, {{2, 1, 0}}, 1) = 0
 lseek(295, 0, SEEK_END) = 0
 lseek(296, 0, SEEK_END) = 8192

 this is definitely pointing to spinlock issue.

 I met Rik van Riel (Linux kernel hacker) recently and we chatted about
 this briefly.  He strongly suggested that we should consider using
 futexes on Linux instead of spinlocks; the big advantage being that
 futexes sleep instead of spinning when contention is high.  That would
 reduce the system load in this scenario.

Well, so do postgres spinlocks right?  When we overflow
spins_per_delay we go to pg_usleep which proxies to select() --
postgres spinlocks are a hybrid implementation.  Moving to futex is
possible improvement (that's another discussion) in degenerate cases
but I'm not sure that I've exactly zeroed in on the problem.  Or am I
missing something?

What I've been scratching my head over is what code exactly would
cause an iterative sleep like the above.  The code is here:

  pg_usleep(cur_delay * 1000L);

  /* increase delay by a random fraction between 1X and 2X */
  cur_delay += (int) (cur_delay *
((double) random() / (double) MAX_RANDOM_VALUE) + 0.5);
  /* wrap back to minimum delay when max is exceeded */
  if (cur_delay  MAX_DELAY_MSEC)
cur_delay = MIN_DELAY_MSEC;

...so cur_delay is supposed to increase in non linear fashion.  I've
looked around the sleep, usleep, and latch calls as of yet haven't
found anything that advances timeout just like that (yet, need to do
another pass). And we don't know for sure if this is directly related
to OP's problem.

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] Percent of Total in Histogram Query

[David Johnston]

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Jungwirth
Sent: Thursday, November 15, 2012 5:44 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Percent of Total in Histogram Query

 

Hello,

I'd like to write a histogram-like query that shows these columns:
  - x-value from 0 to k.
  - number of rows with that x-value.
  - number of rows seen so far (i.e. with the current x-value or less).
  - % of total rows seen so far.

The following query works for the first three columns, but the last column 
gives me an error:

SELECT c,
COUNT(*) AS items_count,
SUM(COUNT(*)) OVER (ORDER BY c) AS total_items_count,
SUM(COUNT(*)) OVER (ORDER BY c) / SUM(COUNT(*)) AS 
total_items_perc
FROM(SELECT p.id, COUNT(*) c
 FROM   parent p, child ch
 WHERE  p.id = ch.parent_id 
 GROUP BY p.id
) x
GROUP BY x.c
ORDER BY x.c

Including that fourth SELECT column gives me this error:

ERROR:  aggregate function calls cannot be nested

Is there any way to get % of total in a query like this?



 

Yes.  Use a sub-query.

 

In this case modify the fourth column to be:   SUM(COUNT(*)) OVER () AS 
grandtotal_items_count

 

Then in a new query layer you can write:

 

total_items_count / grandtotal_items_count AS total_items_perc

 

I would also rename “total_items_count” to something like 
“runningtotal_items_count”

 

David J.

 

 

 

Re: [GENERAL] High SYS CPU - need advise

[Tom Lane]

Merlin Moncure mmonc...@gmail.com writes:
 What I've been scratching my head over is what code exactly would
 cause an iterative sleep like the above.  The code is here:

   pg_usleep(cur_delay * 1000L);

   /* increase delay by a random fraction between 1X and 2X */
   cur_delay += (int) (cur_delay *
 ((double) random() / (double) MAX_RANDOM_VALUE) + 0.5);
   /* wrap back to minimum delay when max is exceeded */
   if (cur_delay  MAX_DELAY_MSEC)
 cur_delay = MIN_DELAY_MSEC;

IIRC that coding is of relatively recent vintage.  The OP is probably
running some older version that increased cur_delay differently.

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

[Vlad Marchenko]

Tom,

I just checked the version I'm running (9.1.6), and the code is quite 
similar (src/backend/storage/lmgr/s_lock.c)


pg_usleep(cur_delay * 1000L);

#if defined(S_LOCK_TEST)
fprintf(stdout, *);
fflush(stdout);
#endif

/* increase delay by a random fraction between 1X and 2X */
cur_delay += (int) (cur_delay *
  ((double) random() / (double) MAX_RANDOM_VALUE) + 
0.5);

/* wrap back to minimum delay when max is exceeded */
if (cur_delay  MAX_DELAY_MSEC)
cur_delay = MIN_DELAY_MSEC;

-- vlad




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

[Jeff Janes]

On Thu, Nov 15, 2012 at 2:44 PM, Merlin Moncure mmonc...@gmail.com wrote:

 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 1000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 2000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 3000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 4000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 6000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 7000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 8000})  = 0 (Timeout)
 select(0, NULL, NULL, NULL, {0, 9000})  = 0 (Timeout)

This is not entirely inconsistent with the spinlock.  Note that 1000
is repeated 3 times, and 5000 is missing.

This might just be a misleading random sample we got here.  I've seen
similar close spacing in some simulations I've run.

It is not clear to me why we use a resolution of 1 msec here.  If the
OS's implementation of select() eventually rounds to the nearest msec,
that is its business.  But why do we have to lose intermediate
precision due to its decision?

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

[GENERAL] Purge Logs from pgagent

[P. Broennimann]

Hi there

I am using pgagent without problems but I have a few questions:

- I see that pgagent does log its acivities in the tables pga_joblog and
pga_jobsteplog. My log gets quiet big.

1) Is it save to empty these tables once a while manually?

2) Is there a settings to tell pgagent to purge these automatically? Or
tell pgagent to not log at all?

Thanks  cheers,
Peter