date:20100208

[GENERAL] Problem about export/import postgresql tables use pg_dump/pg_restore

2010-02-08 Thread Ma, Dong (vinc...@bas-becom-bj)

Hi all,

Recently I want to import a new table into a existing database, I run with 
follow steps:

1.   Export my whole database schema from test machine database: pg_dump -s 
-F t -f dumpfile database

2.   Import only one table into existing database: pg_restore -F t -d 
database -t tablename dumpfile

The problem is the table 'tablename' schema import correctly, but didn't import 
the indexes, constrains and column comments of this table. Can somebody do me a 
favor about understanding why this doesn't work?

Thanks,
Best Regards,
Vincent Ma(Ma Dong)

Hewlett-Packard Co.
IDD: +86 010 6564 5733
Email: dong...@hp.com

[GENERAL]

2010-02-08 Thread jehanzeb mirza

hi
im facing a problem of installing postgre sql in my windows based system
please guide me the way
i have downloaded the application but i cannot find any setup file in it.

secondly has anyone had experience on working both on geoserver and
postgresql(POSTGIS) how do i interrelate them

jehanzeb

Re: [GENERAL]

2010-02-08 Thread Filip Rembiałkowski

2010/2/8 jehanzeb mirza 

> hi
> im facing a problem of installing postgre sql in my windows based system
> please guide me the way
> i have downloaded the application but i cannot find any setup file in it.
>

which installer have you downloaded? you should use this one:
http://www.enterprisedb.com/products/pgdownload.do#windows



> secondly has anyone had experience on working both on geoserver and
> postgresql(POSTGIS) how do i interrelate them
>

no experience here, but http://docs.geoserver.org/2.0.x/en/user/ seems
helpful.



>
> jehanzeb
>



-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [GENERAL] Multiple buffer cache?

2010-02-08 Thread Alvaro Herrera

Greg Stark wrote:
> I doubt pinning buffers ever improve system on any halfway modern system. It
> will often *look* like it has improved performance because it improves the
> performance of the queries you're looking at -- but at the expense of
> slowing down everything else.
> 
> There is a use case it would be useful for though. When you have some
> queries that are latency critical. Then you might want to pin the buffers
> those queries use to avoid having larger less urgent queries purge those
> buffers.
> 
> If we had a way to mark latency critical queries that might be a more
> flexible interface but ewe would need some way to control just how critical
> they are. we wouldn't want to keep those buffets pinned forever.

This should be easy to test, no?  Just set some variable while running
latency-critical queries that makes PinBuffer increment usage_count by
more than one when pinning a buffer.  Such a buffer would have its usage
count typically higher than a buffer only used for regular queries.

To make this work we'd probably need a slightly larger value of
BM_MAX_USAGE_COUNT, I think.


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] weird bug in rebuilding RPMs

2010-02-08 Thread Alvaro Herrera

zhong ming wu escribió:

> I can now rebuild rpms so that all files go under a specific directory
> specified by _prefix directive in ~/.rpmmacros  Forgetting lots of
> other bugs that I managed to fix, this weird bug is making me use
>  _prefix that does not include the word "pgsql" in the path name any
> where.  For example if _prefix path has the word pgsqL or pgsq
> or pgsqA then rebuilding is successful but if the path includes the
> word pgsql or pgsql-8.1.18 or pgsqll then rebuilding breaks down.

Yeah, the Makefiles contain a trick to put append "/pgsql" to the
include dir (and others) if the prefix does not already contain "pgsql"
or "postgresql" or similar.  This is probably what is causing the build
to fail.  See src/Makefile.global.in:

##
#
# Installation directories
#
# These are set by the equivalent --xxxdir configure options.  We
# append "postgresql" to some of them, if the string does not already
# contain "pgsql" or "postgres", in order to avoid directory clutter.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] weird bug in rebuilding RPMs

2010-02-08 Thread Tom Lane

Alvaro Herrera  writes:
> zhong ming wu escribió:
>> I can now rebuild rpms so that all files go under a specific directory
>> specified by _prefix directive in ~/.rpmmacros  Forgetting lots of
>> other bugs that I managed to fix, this weird bug is making me use
>> _prefix that does not include the word "pgsql" in the path name any
>> where.

> Yeah, the Makefiles contain a trick to put append "/pgsql" to the
> include dir (and others) if the prefix does not already contain "pgsql"
> or "postgresql" or similar.  This is probably what is causing the build
> to fail.  See src/Makefile.global.in:

Yeah, I suspect that the RPMs depend in various ways on the assumption
that those pathname substitutions will happen.  I haven't got round to
testing, but it might be a good idea to change rpm-pgsql.patch to make
the substitutions unconditional, so that the file layout below the
_prefix is fixed regardless of what the _prefix is.

But having said that, I don't get the point of trying to build a
nonstandard installation from the RPM.  That seems more or less
antithetical to most of the design concepts of RPM-based distros;
and it certainly seems pretty silly if your intent is to transition
to the standard RPM later.

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

[GENERAL] which the best way to start postgres.

2010-02-08 Thread erobles


Which is the best way to start postgres

througth  pc_ctl or postmaster



--
La información contenida en este correo electrónico es confidencial de Sensa 
Control Digital, S.A. de C.V. Está dirigido solamente a la dirección de correo 
señalada. El acceso a este correo electrónico por cualquier otra persona, No 
está autorizado. Si Ud. no es el receptor deliberado de este correo 
electrónico, cualquier difusión, copia o distribución está prohibida y puede 
ser ilegal. Si lo ha recibido por error, por favor notifique al emisor e 
inmediatamente bórrelo de forma permanente y destruya cualquier copia impresa.

The information in this Internet e-mail is confidential, belongs to Sensa 
Control Digital, S.A. de C.V. It is intended solely for the addressee(s). 
Access to this Internet e-mail by anyone else is unauthorized. If you are not 
the intended recipient of this e-mail, any disclosure, copying, or distribution 
of it is prohibited and may be unlawful. If you have received this e-mail in 
error, please notify the sender and immediately and permanently delete it and 
destroy any copies of it that were printed out.

--
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] PostgreSQL + Hibernate, Apache Mod Security, SQL Injection and you (a love story)

2010-02-08 Thread David Kerr

On Fri, Feb 05, 2010 at 12:09:57PM -0800, John R Pierce wrote:
- that would be a function of how you use Postgresql.   if you do the 
- typical PHP hacker style of building statements with inline values then 
- executing them, you're vunerable unless you totally sanitize all your 
- inputs. see http://xkcd.com/327/

Right, so when dealing with a high security environment you want to assume
someone made a mistake and left you vunerable in this area.

- >Does anyone have experience here? One of our security people found a 
- >generic mod_security config file that had a couple of postgres entries 
- >in it. Is there a full Postgres config for mod_security that the 
- >community recommends?
- >
- >Can anyone give me a good pros or cons of using mod_security when you 
- >have Postgres + Hibernate?
- >
- 
- isn't mod_security purely for Apache httpd applications?  if you're not 
- using apache httpd, it seems like there's no point in using mod_security.

We'll have httpd handing off to Geronimo. From what i can gather mod_security
will balk at any url that contains one of it's keywords. 

Dave

-- 
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] PostgreSQL + Hibernate, Apache Mod Security, SQL Injection and you (a love story)

2010-02-08 Thread David Kerr

On Fri, Feb 05, 2010 at 09:19:40PM +0100, Sebastian Hennebrueder wrote:
- John R Pierce schrieb:
- >David Kerr wrote:
- >>Howdy all,
- >>
- >>We're using Postgres 8.3 with all of our apps connecting to the database
- >>with Hibernate / JPA.
- >>
- >>Our security team is concerned about SQL Injection attacks, and would 
- >>like to implement some mod_security rules to protect against it.
- >>
- >>From what I've read Postgres vanilla is pretty robust when it comes to
- >>dealing with SQL Injection attacks,
- >>
- >
- >that would be a function of how you use Postgresql.   if you do the 
- >typical PHP hacker style of building statements with inline values then 
- >executing them, you're vunerable unless you totally sanitize all your 
- >inputs. see http://xkcd.com/327/
- >
- >if you use parameterized calls (easy in perl, java, etc but not so easy 
- >in php), you're should be immune.  in the past there were some issues 
- >with specific evil mis-coded UTF8 sequences, but afaik, thats been 
- >cleared up for quite a while.
- >
- >
- >>and when you put an abstraction layer like Hibernate on top of it, 
- >>you're basically rock solid against them.
- >
- >I would assume so, but I'm not familiar with the implementation details 
- >of Hibernate.
- >
- >
- >
- It dependends how you use Hibernate. If you do String concatenation
- instead of parameterized queries, then you can encounter the same
- injection problems like SQL.

Ok so Hibernante could suffer from the same issues as any framework.

Thanks

Dave

-- 
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] which the best way to start postgres.

2010-02-08 Thread erobles

i forgot, the version of postgresql is 8.3.1

througth pg_ctl or postmaster or postgres ??

erobles wrote:
Which
is the best way to start postgres

througth pc_ctl or postmaster

The information in this Internet e-mail is confidential, belongs to
Sensa Control Digital, S.A. de C.V. It is intended solely for the
addressee(s). Access to this Internet e-mail by anyone else is
unauthorized. If you are not the intended recipient of this e-mail, any
disclosure, copying, or distribution of it is prohibited and may be
unlawful. If you have received this e-mail in error, please notify the
sender and immediately and permanently delete it and destroy any copies
of it that were printed out.

La información contenida en este correo electrónico es confidencial de Sensa Control Digital, S.A. de C.V. Está dirigido solamente a la dirección de correo señalada. El acceso a este correo electrónico por cualquier otra persona, No está autorizado. Si Ud. no es el receptor deliberado de este correo electrónico, cualquier difusión, copia o distribución está prohibida y puede ser ilegal. Si lo ha recibido por error, por favor notifique al emisor e inmediatamente bórrelo de forma permanente y destruya cualquier copia impresa.The information in this Internet e-mail is confidential, belongs to Sensa Control Digital, S.A. de C.V. It is intended solely for the addressee(s). Access to this Internet e-mail by anyone else is unauthorized. If you are not the intended recipient of this e-mail, any disclosure, copying, or distribution of it is prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender and immediately and permanently delete it and destroy any copies of it that were printed out.

Re: [GENERAL] Multiple buffer cache?

2010-02-08 Thread Greg Smith


Alvaro Herrera wrote:

This should be easy to test, no?  Just set some variable while running
latency-critical queries that makes PinBuffer increment usage_count by
more than one when pinning a buffer.  Such a buffer would have its usage
count typically higher than a buffer only used for regular queries.
  


Yeah, the only problem is that if the latency-critical ones don't happen 
often enough, just the constant passes of the clock sweep hand over them 
to allocate for the big activity might kick them out regardless. I was 
thinking more along the lines of just adding another buffer flag that 
protected them instead--once you get in the cache, if you came from a 
relation that's pinned, you never leave again if that flag is on. That's 
more along the lines of what they think they want--whether or not that's 
really best would take some profiling to prove.



To make this work we'd probably need a slightly larger value of
BM_MAX_USAGE_COUNT, I think.
  


That alone might help their case significantly, if in fact the 
latency-critical bits do happen often. Given a repeatable test case, the 
first thing I'd consider doing for this problem is instrumenting the 
distribution of data in the buffer cache with usage counts, double 
BM_MAX_USAGE_COUNT, then run it again and see what's different. Would 
learn a lot with that test.


As far as the sort of issues that Tom and Greg both mentioned, there are 
plenty of situations where people are willing to trade-off a significant 
amount of average or best performance in return for lowering worst-case 
latency for some queries. I'm not completely aligned with presuming the 
database will always have enough information to make that call on its 
own, and "pinning hints" are common enough in other systems that maybe 
they're worth an investigation here too. Some of the things people ask 
for optimizer hints for might even be addressed as a side-effect 
here--knowing the relation was likely nailed down in the cache would 
certainly adjust the plan costs in a way the optimizer could use. That's 
a slightly different case than the direct requests for optimizer hints, 
which are usually a bad idea because they encourage the optimizer to 
make decisions based on data that's likely out of date.


We (myself, Greg Stark, Robert Haas, Stephen Frost) had a little meeting 
last year on the topic of more advanced buffer cache methods and what 
the optimizer might do with them, and I still owe everyone a written 
report on that I haven't forgotten about. While I agree that wandering 
in that direction is the ideal approach here, there may be a role for 
relation pinning in all this somewhere too.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.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] which the best way to start postgres.

2010-02-08 Thread John R Pierce


erobles wrote:

Which is the best way to start postgres

througth  pc_ctl or postmaster


depends on your OS too.   on a RH/Fedora kind of install, where there is 
a /etc/init.d script to start postgres, the best way of starting it is...


   # /etc/init.d/postgresql start

on  a windows system, where postgres is installed as a system service...

   C:\> net start pgsql-8.3

on a solaris 10 system, where postgres is under control of the SMF 
service manager,


   # svcadm enable postgresql:version_83_64bit


if you're rolling your own service descriptor, or don't have one yet, 
then yes, use pg_ctl






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

[GENERAL] second concurrent update takes forever

2010-02-08 Thread Janning Vygen

Hi folks,

I don't need this list very often because postgresql works like a charm! But 
today we encountered a rather complicated puzzle for us. We really need your 
help!

we are using postgresql 8.4 on a debian lenny with latest security patches 
applied.

We are running a rather complicated Update statement from time to time which 
is only triggered by administrators. The statement updates about 50.000 rows. 
It takes usually about 10-30 seconds to execute and that's fine for us. 

This time two administrator did run this update at approximately  the same 
time, so the second update started before the first finished.

The first update took about 30 seconds which is quite long but not a problem 
for us. the second update was useless, because everything was already updated. 
Anyway, the second statement took 5 hours!

We were able to reproduce this scenario very easily. We had a local database, 
switched off auto-vacuum daemon and had no other concurrent tasks.

We just ran the statement and while it was running we started the very same 
statement in a different database session. the first statement finished in 3 
seconds, the second statement never returned (we canceled it after an hour or 
so).

so we read the documentation about transaction isolation
http://www.postgresql.org/docs/current/static/transaction-iso.html
we are in read committed isolation. 

It says about UPDATE statements "The search condition of the command (the 
WHERE clause) is re-evaluated to see if the updated version of the row still 
matches the search condition."

the update statement has an additional from clause, I show you slightly  
simplified version of it (to make it more readable)
 
UPDATE 
   queue 
SET 
   queue_status =
  CASE WHEN status = 0 OR status = 2 
  THEN status + 1 
  ELSE status  
  END, 
   queue_index = 
  CASE WHEN status = 0 OR status = 2 
  THEN updates.index 
  ELSE
CASE WHEN queue.index >  updates.index 
THEN updates.index 
ELSE queue.index 
END 
  END 
FROM 
( 
   SELECT 
  matchday.group_id AS group_id, 
  min (matchday.index) AS index 
   FROM event NATURAL LEFT JOIN matchday
   WHERE event.event_id IN ( 3033445 ) 
   GROUP BY matchday.group_id 
) AS updates 
WHERE 
   queue.group_id = updates.group_id
;

so the essence is: the statement has a rather long executing sub-select 
statement which takes about a 800ms. I "suspect" postgresql to do the sub-
selection and while updating the first row of this sub-select it sees that this 
row was updated since the update statement (or the transaction) was started. 
And then it re-checks the condition just for this row (or maybe for a bunch of 
rows on the same page). if it comes to updating the next row it happens again.

re-checking 4+ rows with a very expensive sub-select plan is not good at 
all. 4 times  800 ms = 10 hours. 

Am I right with my suspicion?
If yes, how can I fix it?

kind regards
Janning











-- 
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] second concurrent update takes forever

2010-02-08 Thread Tom Lane

Janning Vygen  writes:
> We are running a rather complicated Update statement from time to time which 
> is only triggered by administrators. The statement updates about 50.000 rows. 
> It takes usually about 10-30 seconds to execute and that's fine for us. 

> This time two administrator did run this update at approximately  the same 
> time, so the second update started before the first finished.

> The first update took about 30 seconds which is quite long but not a problem 
> for us. the second update was useless, because everything was already 
> updated. 
> Anyway, the second statement took 5 hours!

> ...

> so the essence is: the statement has a rather long executing sub-select 
> statement which takes about a 800ms. I "suspect" postgresql to do the sub-
> selection and while updating the first row of this sub-select it sees that 
> this 
> row was updated since the update statement (or the transaction) was started. 
> And then it re-checks the condition just for this row (or maybe for a bunch 
> of 
> rows on the same page). if it comes to updating the next row it happens again.

> re-checking 4+ rows with a very expensive sub-select plan is not good at 
> all. 4 times  800 ms = 10 hours. 

> Am I right with my suspicion?

Yeah, that's a fairly accurate description of how EvalPlanQual works in
current releases.

> If yes, how can I fix it?

Don't do that ;-).

If there's only one of these that's supposed to run at a time, you might
try taking out some self-exclusive lock type before running it.

There is a rewritten version of EvalPlanQual in CVS tip --- if you have
the ability to test your problem situation on 8.5alpha3 or newer, I'd be
interested to know whether it (a) works faster and (b) gets the right
answers.

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

[GENERAL] turning a tsvector without position in a weighted tsvector

2010-02-08 Thread Ivan Sergio Borgonovo

If I convert a string to a tsvector just casting (::tsvector) I
obtain a vector without positions.
tsvectors without positions don't have weights too.

I haven't found a way to turn a vector without weight/pos, into a
vector with weight/pos.

Is there a way to apply weight/add positions to tsvectors without
positions?
Is there any use-case?

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

[GENERAL] Creating subsets on timestamp with modulo, date_trunc and ?suggestions?

2010-02-08 Thread Davor J.

A simple way I came up is to truncate the date. So if you have 2009-08-08, 
and you want a subset on month, then just truncate the day-part: 2009-08-00 
on the whole column, and SELECT DISTINCT so you have a subset. You can use 
this subset then to join the dates, GROUP BY and aggregate

An other way I found to do this is in Celko's 'SQL for smarties'. He uses 
modulo there. It seems powerful, but also tricky to implement.

I was wondering if anyone knew some other way to create a subset of a 
timestamp column. Any input is welcome.

Regards,
Davor 



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

[GENERAL] One column to multiple columns based on constraints?

2010-02-08 Thread Davor J.

Let's say you have a table:
CREATE TABLE t (
time date,
data integer
)

Suppose you want a new table that has columns similar to the following:
"(x.time, x.data, y.time, y.data, z.time, z.data)" where x.time, y.time and 
z.time columns are constrained (for example x.time >2007  AND x.time <2008, 
y.time >2008 AND y.time < 2009, z.time > 2010)

How would you do this. Note that you can not use JOIN as there is no 
relationship.

Currently I came up with something like this:

SELECT X.*, (SELECT Y.time, Y.data FROM t AS Y WHERE Y.time = X.time + 1), 
(SELECT Z.time .) FROM t AS X WHERE  X.time >2007  AND X.time <2008

But it's somewhat awkward. I thought maybe someone has better idea's. Any 
input is welcome. 



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

[GENERAL] R: One column to multiple columns based on constraints?

2010-02-08 Thread Vincenzo Romano

Look for crosstab in the documentation.

Il giorno 8 feb, 2010 8:21 p., "Davor J."  ha scritto:

Let's say you have a table:
CREATE TABLE t (
time date,
data integer
)

Suppose you want a new table that has columns similar to the following:
"(x.time, x.data, y.time, y.data, z.time, z.data)" where x.time, y.time and
z.time columns are constrained (for example x.time >2007  AND x.time <2008,
y.time >2008 AND y.time < 2009, z.time > 2010)

How would you do this. Note that you can not use JOIN as there is no
relationship.

Currently I came up with something like this:

SELECT X.*, (SELECT Y.time, Y.data FROM t AS Y WHERE Y.time = X.time + 1),
(SELECT Z.time .) FROM t AS X WHERE  X.time >2007  AND X.time <2008

But it's somewhat awkward. I thought maybe someone has better idea's. Any
input is welcome.



--
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] turning a tsvector without position in a weighted tsvector

2010-02-08 Thread Oleg Bartunov


Ivan,

what's wrong with:

postgres=# select 'abc:1'::tsvector;
 tsvector
--
 'abc':1

postgres=# select setweight('abc:1'::tsvector,'a');
 setweight
---
 'abc':1A

or just use to_tsvector() instead of casting?

Oleg
On Mon, 8 Feb 2010, Ivan Sergio Borgonovo wrote:


If I convert a string to a tsvector just casting (::tsvector) I
obtain a vector without positions.
tsvectors without positions don't have weights too.

I haven't found a way to turn a vector without weight/pos, into a
vector with weight/pos.

Is there a way to apply weight/add positions to tsvectors without
positions?
Is there any use-case?




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

[GENERAL] WINDOW functions - proposed addition weight (dp) for percent_rank, cume_dist

2010-02-08 Thread Michael van der Kolff

Dear all,

I have a bunch of CDRs which I turned into a running list showing how
many were in progress at any one time:

WITH
parameters as (
  SELECT
(timestamp with time zone '2010-01-19 00:00:01+11') as beginning,
(timestamp with time zone '2010-01-19 00:00:01+11') + (interval
'24 hours' * 17) as ending
),
call_times as (
  SELECT
greatest(calldate,beginning) as callstart,
least(calldate + duration * (interval '1 second'),p.ending) as callend
  FROM
cdr, parameters p
  where ((calldate BETWEEN beginning AND ending) or ((calldate +
duration * (interval '1 second')) BETWEEN beginning AND ending))
/*AND dcontext <> 'internalNumbers' */ --uncomment for outgoing
call statistics
),
call_starts as (
  SELECT
callstart as eventTime,
1::int8 as numInProgress
  FROM
call_times
),
call_ends as (
  SELECT
callend as eventTime,
-1::int8 as numInProgress
  FROM
call_times
),
call_times_numInProgress as (
  select period(eventTime, lead(eventTime,1,p.ending) over
byEventTime) as timeSlice, sum(numInProgress) over byEventTime as
callsInProgress
  FROM
(select eventTime, numInProgress from call_starts union all select
eventTime, numInProgress from call_ends) events, parameters p
  WINDOW byEventTime as (order by eventTime asc)
)
SELECT *, percent_rank() OVER (ORDER BY callsInProgress ASC) AS
percentile FROM call_times_numInProgress ORDER BY callsInProgress DESC

using the asterisk 'standard' definition for CDRs with one extra field
asking whether it's been copied to an accounting program (should do
something more sensible, but it's using firebird for the moment...):
CREATE TABLE cdr
(
  acctid bigserial NOT NULL,
  calldate timestamp with time zone NOT NULL DEFAULT now(),
  clid character varying(45) NOT NULL DEFAULT ''::character varying,
  src character varying(45) NOT NULL DEFAULT ''::character varying,
  dst character varying(45) NOT NULL DEFAULT ''::character varying,
  dcontext character varying(45) NOT NULL DEFAULT ''::character varying,
  channel character varying(45) NOT NULL DEFAULT ''::character varying,
  dstchannel character varying(45) NOT NULL DEFAULT ''::character varying,
  lastapp character varying(45) NOT NULL DEFAULT ''::character varying,
  lastdata character varying(45) NOT NULL DEFAULT ''::character varying,
  duration integer NOT NULL DEFAULT 0,
  billsec integer NOT NULL DEFAULT 0,
  disposition character varying(45) NOT NULL DEFAULT ''::character varying,
  amaflags integer NOT NULL DEFAULT 0,
  accountcode character varying(45) NOT NULL DEFAULT ''::character varying,
  uniqueid character varying(45) NOT NULL DEFAULT ''::character varying,
  "copiedToSPInfo" boolean DEFAULT false,
  CONSTRAINT cdr_pkey PRIMARY KEY (acctid)
)
WITH (
  OIDS=FALSE
);


But percent_rank here simply shows the direct percentile.  What I
would like is to say percent_rank(length(timeSlice)), which would give
the result of the cumulative sum of the length of each period divided
by the sum of the entire period

So what I propose is a minor extension:  Add a weight parameter to
percent_rank/cume_dist/others, which defaults to 1.  Current behaviour
should stay the same, AFAICT...

Cheers,

Michael

-- 
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] turning a tsvector without position in a weighted tsvector

2010-02-08 Thread Ivan Sergio Borgonovo

On Mon, 8 Feb 2010 23:01:45 +0300 (MSK)
Oleg Bartunov  wrote:

> Ivan,
> 
> what's wrong with:
> 
> postgres=# select 'abc:1'::tsvector;
>   tsvector
> --
>   'abc':1

Yes you're right. I think I misplaced some quotes.
But still, once a vector has no position, I can't add the weights.

test=# select setweight('tano'::tsvector, 'A');
 setweight
---
 'tano'
(1 row)

test=# select setweight('tano:1'::tsvector, 'A');
 setweight
---
 'tano':1A
(1 row)

Since I'm writing some helper to manipulate tsvectors I was
wondering if
a) there is any reasonable use case of adding weights to
vectors with no position
b) I missed any obvious way to add weights to tsvectors that were
initially without positions

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

[GENERAL] How do I delete duplicate rows in a table?

2010-02-08 Thread Wang, Mary Y

Hi,

I have a table that have that duplicate rows.  How do I find them and delete 
them?

Please advise.
Mary




-- 
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 do I delete duplicate rows in a table?

2010-02-08 Thread Ivan Sergio Borgonovo

On Mon, 8 Feb 2010 15:32:51 -0800
"Wang, Mary Y"  wrote:

> Hi,
> 
> I have a table that have that duplicate rows.  How do I find them
> and delete them?

http://www.webthatworks.it/d1/node/page/eliminating_duplicates_place_without_oid_postgresql

Most likely I've learned it here...

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

[GENERAL] vacuumdb ERROR: out of memory

2010-02-08 Thread David Kerr

I'm getting error:

When I try
vacuumdb -z assessment
or
vacuumdb assessment

I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.

The only way i can actually analyze the DB is if i do a vacuumdb -f

The database is currently sitting at aproximatly 1/10th of my total data. 

I'm on 8.3.5, SLES 11 Linux .

Any ideas?

Thanks

Dave

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

[GENERAL] viewing large queries in pg_stat_activity

2010-02-08 Thread David Kerr

It seems like pg_stat_activity truncates the current_query to about 1024 
characters.

The field is a text, so i'm wondering if there is a way to see the full query?

(I know i can turn on log_statement=all, or log_min_duration_statement) but i'd 
like
something that doesn't require a restart.

Thanks

Dave

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

[GENERAL] Order by and strings

2010-02-08 Thread Fredric Fredricson


Hi!
New to the list with a question that I cannot find the answer to in the 
manual or on the internet but I suspect is trivial. If somebody could 
point me in the correct direction I would be greatful.


This is what I do (condensed, of course):
# create table tmp ( x text ) ;
CREATE TABLE
# insert into tmp(x) values ('a'),('c'),('-b') ;
INSERT 0 3
# select * from tmp order by x ;
x

a
-b
c
(3 rows)

I would expect a string that start with a hyphen to be sorted before or 
after 'a' and 'c' and not between them. I have tried with a few other 
characters (space, opening parenthesis, etc) but the result is the same.


What I want is the strings sorted by their ascii (or UTF-8) values, 
without some "smart" heuristic. How do I accomplish this?


I cannot find this described in the manual (it should be there!) or on 
the net.


/Fredric

PS. 8.2, Fedora Linux

<>
-- 
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] vacuumdb ERROR: out of memory

2010-02-08 Thread Tom Lane

David Kerr  writes:
> I'm getting error:
> When I try
> vacuumdb -z assessment
> or
> vacuumdb assessment

> I get:
> vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
> DETAIL:  Failed on request of size 1073741820.

What have you got maintenance_work_mem set to?

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] Order by and strings

2010-02-08 Thread Justin Graf

On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
> Hi!
> New to the list with a question that I cannot find the answer to in 
> the manual or on the internet but I suspect is trivial. If somebody 
> could point me in the correct direction I would be greatful.
>
> This is what I do (condensed, of course):
> # create table tmp ( x text ) ;
> CREATE TABLE
> # insert into tmp(x) values ('a'),('c'),('-b') ;
> INSERT 0 3
> # select * from tmp order by x ;
>  x
> 
>  a
>  -b
>  c
> (3 rows)
>

It has to do with the collation you are using
see
http://www.postgresql.org/docs/8.1/interactive/charset.html

All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [GENERAL] vacuumdb ERROR: out of memory

2010-02-08 Thread David Kerr


Tom Lane wrote:

David Kerr  writes:

I'm getting error:
When I try
vacuumdb -z assessment
or
vacuumdb assessment



I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.


What have you got maintenance_work_mem set to?

regards, tom lane



maintenance_work_mem = 1GB

I don't know if it matters but my biggest relation is 7GB
(total including indexes is 16GB) with my total DB size being 20GB


Dave




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

[GENERAL] How do I drop a CONSTRAINT TRIGGER?

2010-02-08 Thread Wang, Mary Y

Hi,

How do I drop a CONSTRAINT TRIGGER?

Thanks
Mary




-- 
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] vacuumdb ERROR: out of memory

2010-02-08 Thread Tom Lane

David Kerr  writes:
> Tom Lane wrote:
>> David Kerr  writes:
>>> I get:
>>> vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
>>> DETAIL:  Failed on request of size 1073741820.
>> 
>> What have you got maintenance_work_mem set to?

> maintenance_work_mem = 1GB

So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.

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] vacuumdb ERROR: out of memory

2010-02-08 Thread David Kerr


Tom Lane wrote:

David Kerr  writes:

Tom Lane wrote:

David Kerr  writes:

I get:
vacuumdb: vacuuming of database "assessment" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 1073741820.

What have you got maintenance_work_mem set to?



maintenance_work_mem = 1GB


So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.

regards, tom lane



AHhh, ok. I was thinking that it was filling up the 1GB i allocated to it.

I just dropped the memory allocated to the instance down by about 10GB 
and i'm still getting the error though.


> free
 total   used   free sharedbuffers cached
Mem:  34997288   328218282175460  0 227420   32541844
-/+ buffers/cache:  52564   34944724
Swap:   530136 36 530100


seems like i've got 2GB free.

Food for thought..

I'll look more into it tomorrow morning though.

Thanks

Dave

--
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] vacuumdb ERROR: out of memory

2010-02-08 Thread John R Pierce


David Kerr wrote:

maintenance_work_mem = 1GB


So evidently, when it tries to actually allocate 1GB, it can't do it.
Ergo, that setting is too high for your machine.
...


seems like i've got 2GB free.



is this a 64bit postgres build?


if not, you're probably running out of virtual address space in the 32 
bit user space, which is limited to like 2gb.


the other possibility, and here I'm not sure, is that 
maintenance_work_mem is coming out of shared memory, and if so, you've 
exceeeded your SHMMAX kernel limit.




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

[GENERAL] pg_stat_user_indexes and pg_stat_user_tables description?

2010-02-08 Thread AI Rumman

Could anyone please tell me where may I get the details of the following
views:
pg_stat_user_indexes
pg_stat_user_tables

I want to know the meaning of every column like idx_tup_read and
idx_tup_fetch.

Re: [GENERAL] pg_stat_user_indexes and pg_stat_user_tables description?

2010-02-08 Thread Ben Chobot

On Feb 8, 2010, at 9:02 PM, AI Rumman wrote:

> Could anyone please tell me where may I get the details of the following 
> views:
> pg_stat_user_indexes
> pg_stat_user_tables
>  
> I want to know the meaning of every column like idx_tup_read and 
> idx_tup_fetch.

The fine user manual is helpful for questions like this:

http://www.postgresql.org/docs/8.4/interactive/monitoring-stats.html


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

[GENERAL] importing from a file with UTF-8 escape characters

2010-02-08 Thread Seb

Hi,

I'm trying to import the geonames table from
http://download.geonames.org/export/dump/allCountries.zip.  A PostgreSQL
user recommended using:

create table geonames (
 geonameid   int,
 namevarchar(200),
 asciinamevarchar(200),
 alternatenames  varchar(4000),
 latitudefloat,
 longitude   float,
 fclass  char(1),
 fcode   varchar(10),
 country varchar(2),
 cc2 varchar(60),
 admin1  varchar(20),
 admin2  varchar(80),
 admin3  varchar(20),
 admin4  varchar(20),
 population  bigint,
 elevation   int,
 gtopo30 int,
 timezone varchar(40),
 moddate date
 );

but some lines in the file have strings of the form:

5881673 {1}útsaw Lake   {1}utsaw Lake   62.699499389-136.737319993  
H   LK  CA  12  0   
600 America/Dawson  2006-01-18

which I take as UTF-8 escaped characters.  Any tips on how to import
this into the table so that they print properly?  Thanks.

Cheers,

-- 
Seb


-- 
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] questions about a table's row estimates

2010-02-08 Thread Ben Chobot

On Feb 5, 2010, at 12:14 PM, Ben Chobot wrote:

> I'm looking at pg_stat_user_tables in 8.4.2, and I'm confused about 
> n_live_tup. Shouldn't that be at least fairly close to (n_tup_ins - 
> n_tup-del)? It doesn't seem to be, but I'm unclear why.

Is everybody else unclear as well?
-- 
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 do I drop a CONSTRAINT TRIGGER?

2010-02-08 Thread A. Kretschmer

In response to Wang, Mary Y :
> Hi,
> 
> How do I drop a CONSTRAINT TRIGGER?

Just with DROP TRIGGER:

test=# create table foo(a int);
CREATE TABLE
test=*# create function foo_proc() returns trigger as $$begin return new; end; 
$$ language plpgsql;
CREATE FUNCTION
test=*# create constraint trigger foo_trg after insert on foo for each row 
execute procedure foo_proc();
CREATE TRIGGER
test=*# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
Triggers:
foo_trg AFTER INSERT ON foo NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW 
EXECUTE PROCEDURE foo_proc()

test=*# drop trigger foo_trg on foo;
DROP TRIGGER
test=*# \d foo
  Table "public.foo"
 Column |  Type   | Modifiers
+-+---
 a  | integer |

test=*#


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

38 matches

Mail list logo