Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

[Scott Marlowe]

On Thu, May 28, 2009 at 6:45 PM, Douglas Alan  wrote:
> Scott Marlowe  wrote:
>
>> Douglas Alan wrote:
>
>>> I'm worried that turning off seqscan would distort other queries.
>>> (Remember, I don't have control of the individual queries.  The
>>> user of the application can specify all sorts of queries, and
>>> there's an ORM in the middle.)
>
>> You are aware you can turn off seq scans for just the current
>> connection, right?
>
> Yes, of course.  I thought that the suggestion was to change this
> setting in production, not just for diagnostic purposes.  As I
> previously reported, I've already changed some settings for diagnostic
> purposes and also reported what values I had to set them to to force
> an index scan for the query in question.

Well there are varying degrees of doing this in production, if needed.
 You can use a separate account for these queries that has different
settings so the query planner makes the right decision etc.

>> No one's saying to do it all the time.  They're saying to do it and
>> then run explain analyze on your query, then post the results of
>> both let us have a look.
>
> Okay -- no problem:
>
>    set enable_seqscan = on;
>    explain analyze select * from maindb_astobject
>    where survey_id = 2
>    limit 1000;
>
>    "Limit  (cost=0.00..48.03 rows=1000 width=78) (actual
> time=84837.835..265938.258 rows=1000 loops=1)"
>    "  ->  Seq Scan on maindb_astobject  (cost=0.00..3538556.10
> rows=73675167 width=78) (actual time=84837.825..265932.121 rows=1000
> loops=1)"
>    "    Filter: (survey_id = 2)"
>    "Total runtime: 265942.416 ms"
>
>    set enable_seqscan = off;
>    explain analyze select * from maindb_astobject
>    where survey_id = 2
>    limit 1000;
>
>    "Limit  (cost=0.00..67.37 rows=1000 width=78) (actual
> time=172.248..225.219 rows=1000 loops=1)"
>    "  ->  Index Scan using maindb_astobject_survey_id on
> maindb_astobject  (cost=0.00..4963500.87 rows=73675167 width=78)
> (actual time=172.240..221.078 rows=1000 loops=1)"
>    "    Index Cond: (survey_id = 2)"
>    "Total runtime: 227.412 ms"

What was the random_page_cost during these two queries?  Assuming
seq_page_cost is 1, and random_page_cost was 4 or something, lowering
it should force the move to an index scan.  If you were already at 1.0
or so, then yeah, the cost estimation is off.  Since index scans cost
CPU (at least I think they do), you might try lowering your cpu_*
costs to see if that helps


>
>>> Slow query:
>>>
>>> psql> select * from maindb_astobject
>>>  where survey_id = 2
>>>  limit 20 offset 1000;
>
>> You'll notice that in your previous posts you never mentioned offset,
>> which greatly affects the plan chosen.
>
> It makes little difference for this query.  The plan is the same with
> or without the offset.  The purpose of the offset was just to more or
> less simulate a "limit 1020" without getting deluged by 1,020 results.

Ah yes, I can see that now.

>>> P.P.S. Many other queries were very slow due to Postgres wanting to use hash
>>> joins and merge joins rather than nested index scans.
>
>> Then it's quite possible you have a problem with misestimation of
>> values in your db.  Since we have no explain ANALYZE output, we cannot
>> judge if this is the case.
>
> Okay, well if you can also tell me how to fix the hash join / merge
> join problem we are seeing without resorting to the current hack I am
> using to fix it, I will be very thankful!

Most of the time that happens, it's a question of estimates being way
off, OR various cost values.  Explain analyze with hash joins enabled
and disabled can give you an idea where the planner might be making a
mistake.  With large tables it's a good idea to crank up your default
stats target and reanalyzing anyway, so that's worth a first try.

>>> The nested index
>>> scans were, in fact, much faster.  I just turned off hash joins and merge
>>> joins:  problem solved.
>
>>One problem solved, another one created is more likely.
>
> Such as?  I haven't yet seen a nested index scan perform poorly yet.

Who knows?  Pgsql has enough other join methods it's not likely to be
a huge issue.  But if your type of query changes a lot, the nested
loops might get really slow really fast.

> Though if there is some database parameter that is set incorrectly,
> and this is causing the hash join / merge join problem, then I can see
> that having that parameter not be set correctly could be a source of
> many future woes, so I certainly would appreciate any insight into
> that.

Most common cause of these being the wrong choice are default stats
target too low for your data.  But you've only got like 4 values in
this one field, so I doubt that has a big effect.

>
> |>ouglas
>
>
> P.S. Here are the stats on the column. It appears that my recollection
> of 25% of the table matching was a bit off. It's actually 98.5%!  That
> might explain more why Postgres wants to do a sequential scan. The
> problem is that still means that it has t

Re: [GENERAL] Transaction settings: nowait

[Scott Marlowe]

On Thu, May 28, 2009 at 9:52 PM, Yasser Idris  wrote:
>
> Dude, all what u wrote make sense. Only your missing one thing, consider the
> following scenario that u already reply to:
>
>>> For. example: I forget to make commit, or rollback on exception then all
>>> resources I used (updated) is locked.
>
>>Yes - that's an application bug.
>
> Even if it's application bug, resources shouldn't be locked indefinitely.

But some people run very long running transactions on purpose.  Should
we break their applications to make you happy?

> What if there are other clients that access this db? Forget abt this, what
> if the same problem arises because of a network failure during a transaction
> that issued some locks?? What's the solution then, should the resources be
> locked forever till someone finds out and kills the process manually?

No, if the network connection is lost, then tcp_keepalive_timeout will
kick in and the process will eventually get killed off.

> Although the odds of this scenario might look unlikely to happen, the
> results are extremely inconvenient. That's why those features (lock timeout,
> transaction time out) are there in every other dbms. It's not abt that u
> don't want ur client to wait much before aborting a statement, it's more of
> telling ur stupid server to abort a transaction or release a lock in case of
> non-graceful abortion (without commit, rollback, or release lock) from the
> client side whether it was a programmers fault or any other reason

I agree that those would be useful settings / features to have.  But
there are other ways of handling this situation.  One of the most
common is to use a firewall between your db and the other servers that
times out idle connections, which will result in pg seeing the lost
connection after the tcp keepalive timeout and killing the session.
It's not perfect, but it does work.

-- 
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] Transaction settings: nowait

[Yasser Idris]

Dude, all what u wrote make sense. Only your missing one thing, consider the
following scenario that u already reply to:

>> For. example: I forget to make commit, or rollback on exception then all 
>> resources I used (updated) is locked.

>Yes - that's an application bug.

Even if it's application bug, resources shouldn't be locked indefinitely.
What if there are other clients that access this db? Forget abt this, what
if the same problem arises because of a network failure during a transaction
that issued some locks?? What's the solution then, should the resources be
locked forever till someone finds out and kills the process manually?
Although the odds of this scenario might look unlikely to happen, the
results are extremely inconvenient. That's why those features (lock timeout,
transaction time out) are there in every other dbms. It's not abt that u
don't want ur client to wait much before aborting a statement, it's more of
telling ur stupid server to abort a transaction or release a lock in case of
non-graceful abortion (without commit, rollback, or release lock) from the
client side whether it was a programmers fault or any other reason 


Craig Ringer wrote:
> 
> durumdara wrote:
> 
>> If set wait and timeout, the Firebird is waiting for the locked resource 
>> (record) for X seconds before it show deadlock error.
>> 
>> But when you set no wait, the deadlock error immediately shown by the 
>> server.
> 
> Waiting on a lock doesn't mean there's a deadlock. A deadlock only 
> occurs if two transactions are each waiting on resources the other 
> transaction holds.
> 
> PostgreSQL *DOES* let you control how long it waits before it checks to 
> see if transactions waiting on locks might be deadlocked with another 
> waiting transaction. See:
> http://www.postgresql.org/docs/current/static/runtime-config-locks.html
> 
> However, it sounds like what you want is the ability to tell PostgreSQL 
> that you don't want your queries to ever wait on a lock at all, even if 
> it's just that you're waiting for another transaction to finish work.
> 
> If that's what you mean: I'm not aware of any way in PostgreSQL to set 
> lock wait timeouts at a transaction-wide level, or to set Pg to report 
> an error if it would have to wait for a lock.
> 
> Others seem to have wanted it enough to write patches for it, but I 
> don't think they got merged. See, for example:
> http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
> 
> (Please read the discussion on that thread to get an idea of some of the 
> problems with the approach you appear to be requesting - if I'm reading 
> your post correctly.).
> 
> Anyway, it *IS* possible, as others have noted, to use
>   SELECT ... FOR { UPDATE | SHARE } NOWAIT
> and/or
>   LOCK TABLE ... NOWAIT
> before issuing work. However, this generally means you need to know what 
> tuples your queries will touch, including tuples accessed by triggers 
> and the like, before you issue your query.
> 
> It's also really clumsy, since you often WANT queries to wait on locks 
> at least briefly, otherwise you'll get intermittent errors from queries 
> that're operating normally just because another query that happened to 
> run concurrently briefly locked something the first query wanted.
> 
> I must say that I personally can't really see the use of a 
> transaction-wide lock wait timeout. Sure, most applications have to be 
> prepared to re-issue queries anyway due to transient errors of one sort 
> or another, and it's not unreasonable to want to be able to detect the 
> difference between a query blocking on a lock, and a query that's just 
> taking a long time to do work ... but I don't think that aborting the 
> transaction/statement with an error is the right way to do that.
> 
> First: In PostgreSQL an error from a transaction results in rollback of 
> the whole transaction. This means that if you wanted to re-try after a 
> lock wait timeout, you'd need to re-issue the whole transaction, or 
> you'd need to use savepoints before each statement to give you 
> statement-level retry. Both are clumsy and inefficient. It could also 
> easily result in situations where the same group of transactions keep on 
> re-trying and fighting over the same locks over and over; you'd waste a 
> lot of CPU and I/O repeating work pointlessly, when if you just let the 
> transaction wait on the lock everything would go just fine.
> 
> In other words, it'd be ugly. I'm also not really sure how much 
> difference it makes in practice WHY a statement is taking a long time, 
> only that it is. Who cares whether you're waiting on a lock held by 
> another transaction, or whether another transaction is using so much 
> memory and disk I/O bandwidth that your query is taking forever?
> 
> If you really did care about lock waits specifically, it might almost be 
> nicer to be able to have the server send asynchronous NOTICE-level 
> messages informing the application that the query is blocked 

Re: [GENERAL] composite type and domain

[Scott Bailey]

Grzegorz Jaśkiewicz wrote:

2009/5/27 Scott Bailey :


Who said anything about the application level?



can you give an example please ?



Did you read the article I sent you earlier? I'm doing almost the exact 
same thing you are doing save the bytea field. I create a timespan 
composite type that has a start and end times.


But instead of doing casts and using a domain catch any errors like you 
are trying to do, I'm using constructors to return a timespan. The 
signature for the constructors look like so:

timespan(timestamp, timestamp)
timespan(timestamp, numeric)
timespan(timestamp, interval)
timespan(varchar, varchar)
And the constructors do the work of making sure that the start time 
always comes before the end time.


Anyhow read the article, it has all the examples you need.

http://scottrbailey.wordpress.com/2009/05/19/timespan-postgresql/

--
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] Debugging pgsql function date error

[Adrian Klaver]

On Thursday 28 May 2009 5:36:10 pm Rory Campbell-Lange wrote:
> I have a long plpgsql function which takes a date as one of its
> arguments but I am receiving an error for another date! I'm using 8.3 on
> Debian.
>
> CREATE OR REPLACE FUNCTION
> fn_alert01_maker(integer, integer, integer, integer, date, integer)
> RETURNS SETOF alert_info
> AS $$
> DECLARE
> userid  ALIAS for $1;
> alert   ALIAS for $2;
> since   ALIAS for $3;
> waitALIAS for $4;
> date_from   ALIAS for $5;
> days_until  ALIAS for $6;
> resulteralert_info%rowtype;
> since_int   INTERVAL;
> wait_intINTERVAL;
> days_intINTERVAL;
>
> BEGIN
>
> days_int := days_until || ' days';
> since_int := since || ' seconds';
> wait_int := wait || ' seconds';
> ...
>
> and has that argument as one of its WHERE conditions:
> ...
> WHERE
> rs.dt_modified > ((now() - wait_int) - since_int)
> AND
> rs.d_date >= date_from
> AND
> rs.d_date <= date_from + days_int
> ...
>
> Wierdly, if I call it like this, it is fine:
> SELECT * FROM fn_alert01_maker(0, 1, 845, 5, '2009-05-31', 5);
>
> But like this it throws an error:
>
> test=> SELECT * FROM fn_alert01_maker(0, 1, 8450, 5, '2009-05-31', 5);
> ERROR:  invalid input syntax for integer: "2009-06-01"
> CONTEXT:  PL/pgSQL function "fn_alert01_maker" line 30 at FOR over
> SELECT rows
>
>
> Any ideas?
>
> Rory

What is the code at line 30 ?

-- 
Adrian Klaver
akla...@comcast.net

-- 
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] Please remove me from the list!

[Geoffrey]

Marcelo Giovane wrote:

Please, remove me from the list!


Generally, when you find your way into a location, you are expected to 
find your way out...


--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

--
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] Please remove me from the list!

[Guy Rouillier]

Marcelo Giovane wrote:

Please, remove me from the list!
 
Marcelo Giovane
 


You may remove yourself.  At the bottom of *every* message sent from the 
mail list server are these instructions:


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


--
Guy Rouillier

--
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] Please remove me from the list!

[Bill Moran]

"Marcelo Giovane"  wrote:
>
> Please, remove me from the list!

Look in the message headers:
List-Unsubscribe: 

-- 
Bill Moran
http://www.potentialtech.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] Please remove me from the list!

[Alan McKay]

Look into the headers of any email message from the list and look for
the "List-Unsubscribe" line


On Thu, May 28, 2009 at 2:26 PM, Marcelo Giovane  wrote:
> Please, remove me from the list!
>
> Marcelo Giovane
>

-- 
“Mother Nature doesn’t do bailouts.”
 - Glenn Prickett

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

[GENERAL] Debugging pgsql function date error

[Rory Campbell-Lange]

I have a long plpgsql function which takes a date as one of its
arguments but I am receiving an error for another date! I'm using 8.3 on
Debian.

CREATE OR REPLACE FUNCTION
fn_alert01_maker(integer, integer, integer, integer, date, integer)
RETURNS SETOF alert_info
AS $$
DECLARE
userid  ALIAS for $1;
alert   ALIAS for $2;
since   ALIAS for $3;
waitALIAS for $4;
date_from   ALIAS for $5;
days_until  ALIAS for $6;
resulteralert_info%rowtype;
since_int   INTERVAL;
wait_intINTERVAL;
days_intINTERVAL;
   
BEGIN

days_int := days_until || ' days';
since_int := since || ' seconds';
wait_int := wait || ' seconds';
...

and has that argument as one of its WHERE conditions:
...
WHERE
rs.dt_modified > ((now() - wait_int) - since_int)
AND
rs.d_date >= date_from
AND
rs.d_date <= date_from + days_int
...

Wierdly, if I call it like this, it is fine:
SELECT * FROM fn_alert01_maker(0, 1, 845, 5, '2009-05-31', 5);

But like this it throws an error:

test=> SELECT * FROM fn_alert01_maker(0, 1, 8450, 5, '2009-05-31', 5);
ERROR:  invalid input syntax for integer: "2009-06-01"
CONTEXT:  PL/pgSQL function "fn_alert01_maker" line 30 at FOR over
SELECT rows


Any ideas?

Rory

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Douglas Alan]

Scott Marlowe  wrote:

> Douglas Alan wrote:

>> I'm worried that turning off seqscan would distort other queries.
>> (Remember, I don't have control of the individual queries.  The
>> user of the application can specify all sorts of queries, and
>> there's an ORM in the middle.)

> You are aware you can turn off seq scans for just the current
> connection, right?

Yes, of course.  I thought that the suggestion was to change this
setting in production, not just for diagnostic purposes.  As I
previously reported, I've already changed some settings for diagnostic
purposes and also reported what values I had to set them to to force
an index scan for the query in question.

> No one's saying to do it all the time.  They're saying to do it and
> then run explain analyze on your query, then post the results of
> both let us have a look.

Okay -- no problem:

   set enable_seqscan = on;
   explain analyze select * from maindb_astobject
   where survey_id = 2
   limit 1000;

   "Limit  (cost=0.00..48.03 rows=1000 width=78) (actual
time=84837.835..265938.258 rows=1000 loops=1)"
   "  ->  Seq Scan on maindb_astobject  (cost=0.00..3538556.10
rows=73675167 width=78) (actual time=84837.825..265932.121 rows=1000
loops=1)"
   "    Filter: (survey_id = 2)"
   "Total runtime: 265942.416 ms"


   set enable_seqscan = off;
   explain analyze select * from maindb_astobject
   where survey_id = 2
   limit 1000;

   "Limit  (cost=0.00..67.37 rows=1000 width=78) (actual
time=172.248..225.219 rows=1000 loops=1)"
   "  ->  Index Scan using maindb_astobject_survey_id on
maindb_astobject  (cost=0.00..4963500.87 rows=73675167 width=78)
(actual time=172.240..221.078 rows=1000 loops=1)"
   "    Index Cond: (survey_id = 2)"
   "Total runtime: 227.412 ms"

>> Slow query:
>>
>> psql> select * from maindb_astobject
>>  where survey_id = 2
>>  limit 20 offset 1000;

> You'll notice that in your previous posts you never mentioned offset,
> which greatly affects the plan chosen.

It makes little difference for this query.  The plan is the same with
or without the offset.  The purpose of the offset was just to more or
less simulate a "limit 1020" without getting deluged by 1,020 results.

> Is survey_id indexed?

Yes, of course.

>> P.P.S. Many other queries were very slow due to Postgres wanting to use hash
>> joins and merge joins rather than nested index scans.

> Then it's quite possible you have a problem with misestimation of
> values in your db.  Since we have no explain ANALYZE output, we cannot
> judge if this is the case.

Okay, well if you can also tell me how to fix the hash join / merge
join problem we are seeing without resorting to the current hack I am
using to fix it, I will be very thankful!

>> The nested index
>> scans were, in fact, much faster.  I just turned off hash joins and merge
>> joins:  problem solved.

>One problem solved, another one created is more likely.

Such as?  I haven't yet seen a nested index scan perform poorly yet.

Though if there is some database parameter that is set incorrectly,
and this is causing the hash join / merge join problem, then I can see
that having that parameter not be set correctly could be a source of
many future woes, so I certainly would appreciate any insight into
that.

|>ouglas


P.S. Here are the stats on the column. It appears that my recollection
of 25% of the table matching was a bit off. It's actually 98.5%!  That
might explain more why Postgres wants to do a sequential scan. The
problem is that still means that it has to scan a million rows
sequentially before it finds a single matching row, as the matching
rows are at the end of the database:

select * from pg_stats
where tablename='maindb_astobject' and attname = 'survey_id';

 schemaname |tablename |  attname  | null_frac | avg_width |
n_distinct | most_common_vals |most_common_freqs
  | histogram_bounds | correlation
+--+---+---+---++--+--+--+-
 public | maindb_astobject | survey_id | 0 | 4 |
   5 | {2,4,10,3,5} |
{0.985347,0.00966,0.00286667,0.0019,0.000226667} |  |
  0.998872

-- 
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] Pl/java in 8.4 bet1 sources compilation failed

[Kris Jurka]

On Wed, 27 May 2009, Emanuel Calvo Franco wrote:


Hi community,

I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but
it gives me 20 errors at the end:


To build against 8.4 you need pljava from CVS.  Also pljava can only be 
built with the 1.4 or 1.5 JDK, not with the 1.6 version you are using.


Kris Jurka


--
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] vista failed to install postgresql

[Craig Ringer]

douglas wrote:

> the binary , wouldn't start service, install ended then and there .

Did you check the event viewer to see what the service startup error was?

Did you look at the log file PostgreSQL created to see if there were any
errors produced?

Do you have a virus scanner installed? If so, which one?

--
Craig Ringer

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

Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed

[Craig Ringer]

Emanuel Calvo Franco wrote:
> Hi community,
> 
> I'm trying to compile pl/java sources for 8.4 beta1 (for a test) but
> it gives me 20 errors at the end:

Which compiler and JDK are you using?

... and no, there isn't really any way to ignore the lib version; it
wouldn't work, because 8.3 and 8.4 aren't binary compatible for server
plugins like PL languages.

--
Craig Ringer

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

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

[Jeff Davis]

On Thu, 2009-05-28 at 15:12 -0400, Douglas Alan wrote:
> The most obvious solution would be an option to tell Postgres not to
> assume that the value is evenly distributed throughout the table, and
> to take account of the fact that the data in question might very well
> be clustered at the very end of the table.

There's no use adding a new statistic (user supplied or collected) to
PostgreSQL until we know that correlation isn't useful for that purpose.
Can you tell us the correlation that PostgreSQL has already collected (I
apologize if I missed this elsewhere in the thread)?

Hopefully, correlation is useful enough. With some planner tweaks
similar to the ones Tom mentioned, and a few more data points, maybe
we'll have a real solution.

Regards,
Jeff Davis


-- 
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] pg_stats.avg_width differs by a factor of 4 on different machines

[Tom Lane]

Craig de Stigter  writes:
> We are using the PostgreSQL pg_stats view to estimate file sizes for some
> geodata exports. However, the following query gives us totally different
> results on different servers:

> select avg_width from pg_stats where tablename='some_geodata' and attname =
> 'GEOMETRY';

I'm afraid that query is pretty much completely useless for what you
want to do.  What it should be giving you is the average width of the
field values on-disk, which is to say after compression and toasting.
It would probably be all right for narrow columns but it's likely to be
a huge underestimate of the external textual size for wide field values.

Having said that, though, these numbers make no sense to me:

> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu
> 4.3.2-1ubuntu11) 4.3.2
>> 81803

> PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
> (Ubuntu 4.1.2-0ubuntu4)
>> 20450

It should be impossible to get a value larger than the block size, or
even more than about a quarter of the block size because that's where
TOAST will start doing its thing.  Are you running modified source code?

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] Please remove me from the list!

[Marcelo Giovane]

Please, remove me from the list!

Marcelo Giovane

[GENERAL] pg_stats.avg_width differs by a factor of 4 on different machines

[Craig de Stigter]

Hi list

We are using the PostgreSQL pg_stats view to estimate file sizes for some
geodata exports. However, the following query gives us totally different
results on different servers:

select avg_width from pg_stats where tablename='some_geodata' and attname =
> 'GEOMETRY';
>

PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu
4.3.2-1ubuntu11) 4.3.2

> 81803
>

PostgreSQL 8.2.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)

> 20450
>

Both tables have had VACUUM FULL ANALYZE run on them and have identical
data. Note that 81803 is almost exactly 4x20450, though I don't know what
significance this has. x64/i386 makes no difference.

I couldn't find anything in the 8.3 release notes that looked relevant. Any
help appreciated.

Regards
Craig de Stigter

-- 
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

[Douglas Alan]

On Thu, May 28, 2009 at 5:52 PM, Simon Riggs  wrote:

>
> On Thu, 2009-05-28 at 15:03 -0400, Douglas Alan wrote:
>
> > The application in question is a kind of data warehousing thing (of
> > astronomical stars), and there's an ORM in the middle, so it's not
> > easy for us to hand-tune how individual queries are specified.
> > Instead, we have to structure the database and the indexes so that
> > things generally perform well, without having to tweak specific
> > queries.
>
> You should look at indexing solutions, or take professional advice on
> that.


I'm not sure that I know what an "indexing solution" is.


>
> > Users can specify fairly arbitrary search criteria.  All of the
> > queries should perform well.  By "well", I mean within 10 seconds or
> > so.
>
> That's an unrealistic expectation, unfortunately. Fast search requires
> some form of preparatory action and without any clue as to what that
> should be you cannot guarantee response times.
>

Unrealistic or not, it mostly currently works, modulo Postgres sometimes
deciding to do a slow sequence scan when there is a perfectly good index.

For instance, I just tried a query that results in 137,042 results (out of
150 million rows) is constrained in several different ways, involves a table
join on another large table, and it took nine seconds.

|>ouglas

Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?

[Simon Riggs]

On Thu, 2009-05-28 at 15:03 -0400, Douglas Alan wrote:

> The application in question is a kind of data warehousing thing (of
> astronomical stars), and there's an ORM in the middle, so it's not
> easy for us to hand-tune how individual queries are specified.
> Instead, we have to structure the database and the indexes so that
> things generally perform well, without having to tweak specific
> queries.

You should look at indexing solutions, or take professional advice on
that.

> Users can specify fairly arbitrary search criteria.  All of the
> queries should perform well.  By "well", I mean within 10 seconds or
> so. 

That's an unrealistic expectation, unfortunately. Fast search requires
some form of preparatory action and without any clue as to what that
should be you cannot guarantee response times.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] What is the right way to deal with a table with rows that are not in a random order?

[Martin Gainty]

ORM should'nt be a factor as you have many configureable properties available 
in hibernate.cfg.xml or hibernate.properties
hibernate.connection.driver_classjdbc driver classhibernate.connection.urljdbc 
URLhibernate.connection.usernamedatabase 
userhibernate.connection.passworddatabase user 
passwordhibernate.connection.pool_sizemaximum number of pooled connections
Hibernate's own connection pooling algorithm is however quite 
rudimentary. 
It is intended to help you get started and is not intended for use 
in a production system or even for performance testing. You should
use a third party pool for best performance and stability. Just 
replace the 
hibernate.connection.pool_size property with connection 
pool specific settings. This will turn off Hibernate's internal 
pool. For
example, you might like to use C3P0.


C3P0 is an open source JDBC connection pool distributed along with 
Hibernate in the lib directory. Hibernate will use its
C3P0ConnectionProvider for connection pooling if you set 
hibernate.c3p0.* properties. If you'd like to use Proxool
refer to the packaged hibernate.properties and the Hibernate
web site for more information.


Here is an example hibernate.properties file for C3P0:

hibernate.connection.driver_class = org.postgresql.Driver
hibernate.connection.url = jdbc:postgresql://localhost/mydatabase
hibernate.connection.username = myuser
hibernate.connection.password = secret
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50
hibernate.dialect = 
org.hibernate.dialect.PostgreSQLDialecthttps://www.hibernate.org/214.html

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> From: darkwate...@gmail.com
> Date: Thu, 28 May 2009 15:03:32 -0400
> Subject: Re: [GENERAL] What is the right way to deal with a table with rows   
> that are not in a random order?
> To: pgsql-general@postgresql.org
> CC: si...@2ndquadrant.com
> 
> On Thu, May 28, 2009 at 4:14 AM, Simon Riggs  wrote:
> 
> > Partition the table, then scan the correct partition.
> 
> If I do that, will Postgres figure out the "right thing" to do if the
> parent table is queried instead?  Also, what are the performance
> implications then for doing queries that span all the partitions,
> which will be the norm for our application?
> 
> The application in question is a kind of data warehousing thing (of
> astronomical stars), and there's an ORM in the middle, so it's not
> easy for us to hand-tune how individual queries are specified.
> Instead, we have to structure the database and the indexes so that
> things generally perform well, without having to tweak specific
> queries.
> 
> Users can specify fairly arbitrary search criteria.  All of the
> queries should perform well.  By "well", I mean within 10 seconds or
> so.  Scanning all of the 150 million rows takes much longer than 10
> seconds, unfortunately.
> 
> Any one of these "solutions" will cause Postgres to do an index scan
> in the problematic case where Postgres is deciding to a sequential
> scan.  The index scan performs snappily enough:
> 
>- Using "order by" on the query.
> 
>- Changing the search value for the column to a value that occurs
> less frequently.
> 
>- Fetching the value to search for via a sub-query so that Postgres
> can't determine a priori that the
>  value being searched value occurs so commonly.
> 
> Unfortunately, as I mentioned, due to the ORM, none of these solutions
> really work for us in practice, as opposed to at a psql prompt.
> 
> |>ouglas
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_
Hotmail® has ev

Re: [GENERAL] ubuntu 9.04 and auto-start

[Daniel Verite]

johnf wrote:

I have installed postgres 8.3 and it runs if I manually start 

postgres.
That	is to say I '/etc/init.d/postgresql start' as root. Runs 

perfectly.

But if I restart the computer postgres does not auto-start.


Did you install the package named postgresql-8.3?
Because it contains /etc/init.d/postgresql-8.3 (note the version number 
in the filename) as shown here:

http://packages.ubuntu.com/jaunty/i386/postgresql-8.3/filelist
and the installation procedure normally sets up the links so that it 
starts at boot time.


Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

--
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] What is the right way to deal with a table with rows that are not in a random order?

[Scott Marlowe]

On Thu, May 28, 2009 at 1:12 PM, Douglas Alan  wrote:
> On Thu, May 28, 2009 at 10:24 AM, Scott Marlowe  
> wrote:
>
>>  OTOH, if you've got it all sussed out, then ignore the request for more 
>> information.
>
> I don't *know* if I have it "all sussed out", but I *do* know why
> Postgres is doing what it is doing in this particular case.  It's
> assuming that the value in question is evenly distributed throughout
> the table, when in actuality, the value in question is clustered at
> the very end of the table.

It's doing way more than that.  My point above was that the query
planner is not JUST assuming the values are wel ordered.  It's
assuming random_page_cost is x times more than sequential page cost,
it's assuming the table doesn't fit in effective cache, or shared
buffers, it's assuming lots of things based on how you've tuned (or
not) your database.

I'll finish in reply to your other post.

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Scott Marlowe]

On Thu, May 28, 2009 at 1:45 PM, Douglas Alan  wrote:
> On Thu, May 28, 2009 at 10:41 AM, Scott Marlowe 
> wrote:
>
>> Note that in the OPs case I'd probably try testing things like turning
>> off seqscan, or lowering random_page_cost.  I'd also look at
>> clustering on the index for the field you're selecting on.
>
> I'm worried that turning off seqscan would distort other queries.
>  (Remember, I don't have control of the individual queries.  The user of the
> application can specify all sorts of queries, and there's an ORM in the
> middle.)

You are aware you can turn off seq scans for just the current connection, right?

set enable_seqscan=off;

> In order to force Postgres to do an index scan for this query, I had to set
> random_page_cost to 0.  Even 0.1 was not small enough.  Alternatively, I
> could set seq_page_cost to 39.  (38 was not big enough.)  Again, I'm worried
> that by using such a big hammer, I would distort Postgres's query planning
> for other queries.

No one's saying to do it all the time.  They're saying to do it and
then run explain analyze on your query, then post the results of both
let us have a look.

> P.S. Here is the actual data that some people have been clamoring for:

No, it's not.  It's just explain output.  Unless the actual query
running takes hours, it's much more useful to have explain analyze
output.  With the analyze part, it's only showing what the planner
expects, not what actually happens.

> Slow query:
>
> psql> select * from maindb_astobject
>      where survey_id = 2
>      limit 20 offset 1000;

You'll notice that in your previous posts you never mentioned offset,
which greatly affects the plan chosen.

Is survey_id indexed?

> Time: 18073.691 ms
>
> Here's the explanation:
>
>                                     QUERY PLAN
>
> -
> Limit  (cost=47.99..48.95 rows=20 width=153)
>   ->  Seq Scan on maindb_astobject  (cost=0.00..3538556.10 rows=73736478
> width=153)
>         Filter: (survey_id = 2)
>
> The same query made fast by removing Postgres's ability to know a priori
> what particular value is being searched for:
>
> psql> select * from maindb_astobject join maindb_enumentity
>      on maindb_astobject.survey_id = maindb_enumentity.id
>      where entityname = 'MACHO'
>      limit 20 offet 1000;

That's not the same query.

> Time: 1.638 ms
>
> Here's the explanation for the above query showing that it is using the
> index:

So, can we see explain analyze?

> QUERY PLAN
>   --
>    Limit  (cost=164.97..168.27 rows=20 width=215)
>      ->  Nested Loop  (cost=0.00..1233523.72 rows=7477081 width=215)
>    ->  Seq Scan on maindb_enumentity  (cost=0.00..1.12 rows=1 width=62)
>  Filter: ((entityname)::text = 'MACHO'::text)
>    ->  Index Scan using maindb_astobject_survey_id on maindb_astobject
>  (cost=0.00..1046595.57 rows=14954162 width=153)
>  Index Cond: (maindb_astobject.survey_id = maindb_enumentity.id)
>
>
> P.P.S. Many other queries were very slow due to Postgres wanting to use hash
> joins and merge joins rather than nested index scans.

Then it's quite possible you have a problem with misestimation of
values in your db.  Since we have no explain ANALYZE output, we cannot
judge if this is the case.


> The nested index
> scans were, in fact, much faster.  I just turned off hash joins and merge
> joins:  problem solved.

One problem solved, another one created is more likely.

> It might be nice at some point to figure out what
> is going with Postgres trying to use these fancier joins that turn out to be
> much slower for us, but that's a worry for another day, since I have a
> perfectly good work-around at the moment.

Yes, let us see explain analyze output and maybe we can help.  You
know, the thing that was asked for at the beginning.

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Douglas Alan]

On Thu, May 28, 2009 at 10:41 AM, Scott Marlowe 
wrote:

> Note that in the OPs case I'd probably try testing things like turning
> off seqscan, or lowering random_page_cost.  I'd also look at
> clustering on the index for the field you're selecting on.

I'm worried that turning off seqscan would distort other queries.
 (Remember, I don't have control of the individual queries.  The user of the
application can specify all sorts of queries, and there's an ORM in the
middle.)

In order to force Postgres to do an index scan for this query, I had to set
random_page_cost to 0.  Even 0.1 was not small enough.  Alternatively, I
could set seq_page_cost to 39.  (38 was not big enough.)  Again, I'm worried
that by using such a big hammer, I would distort Postgres's query planning
for other queries.

|>ouglas

P.S. Here is the actual data that some people have been clamoring for:

Slow query:

psql> select * from maindb_astobject
 where survey_id = 2
 limit 20 offset 1000;

Time: 18073.691 ms


Here's the explanation:

QUERY PLAN

-
Limit  (cost=47.99..48.95 rows=20 width=153)
  ->  Seq Scan on maindb_astobject  (cost=0.00..3538556.10 rows=73736478
width=153)
Filter: (survey_id = 2)

The same query made fast by removing Postgres's ability to know a priori
what particular value is being searched for:

psql> select * from maindb_astobject join maindb_enumentity
 on maindb_astobject.survey_id = maindb_enumentity.id
 where entityname = 'MACHO'
 limit 20 offet 1000;

Time: 1.638 ms

Here's the explanation for the above query showing that it is using the
index:

QUERY PLAN
  --
   Limit  (cost=164.97..168.27 rows=20 width=215)
 ->  Nested Loop  (cost=0.00..1233523.72 rows=7477081 width=215)
   ->  Seq Scan on maindb_enumentity  (cost=0.00..1.12 rows=1 width=62)
 Filter: ((entityname)::text = 'MACHO'::text)
   ->  Index Scan using maindb_astobject_survey_id on maindb_astobject
 (cost=0.00..1046595.57 rows=14954162 width=153)
 Index Cond: (maindb_astobject.survey_id = maindb_enumentity.id)


P.P.S. Many other queries were very slow due to Postgres wanting to use hash
joins and merge joins rather than nested index scans.  The nested index
scans were, in fact, much faster.  I just turned off hash joins and merge
joins:  problem solved.  It might be nice at some point to figure out what
is going with Postgres trying to use these fancier joins that turn out to be
much slower for us, but that's a worry for another day, since I have a
perfectly good work-around at the moment.

Re: [GENERAL] How to pass parameters into a sql script ?

[Osvaldo Kussama]

2009/5/28 Atul Chojar :
> We are unable to pass parameters into any sql script. Could anyone look at
> the test below and give any suggestions? PostgreSQL version is 8.2.7,
> running on Linux.
>
>
>
> Test Script
>
> 
>
> $ cat chk_param.sql
>
> select ||:p_date::char(8)||;
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> (select ||:p_date::char(8)||);
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> '20081023';
>
>
>
> Test Results
>
> =
>
> $ psql -d us_audit -e -1 -v p_date='20081023' -f chk_param.sql
>
> Timing is on.
>
> select ||20081023::char(8)||;
>
>   ?column?
>
> 
>
>  '20081023'
>
> (1 row)
>
>
>
> Time: 1.474 ms
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> (select ||20081023::char(8)||);--same results with direct assignment
> and to_date
>
>  count
>
> ---
>
>  0
>
> (1 row)
>
>
>
> Time: 36.290 ms
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> '20081023';
>
>  count
>
> ---
>
>    193
>
> (1 row)
>
>
>
> Time: 17.722 ms
>


bdteste=# select '20081023' =  ||20081023::char(8)||;
 ?column?
--
 f
(1 registro)

bdteste=# select '20081023', length('20081023'),
||20081023::char(8)||, length(||20081023::char(8)||);
 ?column? | length |  ?column?  | length
--+++
 20081023 |  8 | '20081023' | 10
(1 registro)

Osvaldo

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Douglas Alan]

On Thu, May 28, 2009 at 10:24 AM, Scott Marlowe  wrote:

>  OTOH, if you've got it all sussed out, then ignore the request for more 
> information.

I don't *know* if I have it "all sussed out", but I *do* know why
Postgres is doing what it is doing in this particular case.  It's
assuming that the value in question is evenly distributed throughout
the table, when in actuality, the value in question is clustered at
the very end of the table.

What I don't have sussed out his how best to address this issue.

The most obvious solution would be an option to tell Postgres not to
assume that the value is evenly distributed throughout the table, and
to take account of the fact that the data in question might very well
be clustered at the very end of the table.

|>ouglas

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Douglas Alan]

On Thu, May 28, 2009 at 4:14 AM, Simon Riggs  wrote:

> Partition the table, then scan the correct partition.

If I do that, will Postgres figure out the "right thing" to do if the
parent table is queried instead?  Also, what are the performance
implications then for doing queries that span all the partitions,
which will be the norm for our application?

The application in question is a kind of data warehousing thing (of
astronomical stars), and there's an ORM in the middle, so it's not
easy for us to hand-tune how individual queries are specified.
Instead, we have to structure the database and the indexes so that
things generally perform well, without having to tweak specific
queries.

Users can specify fairly arbitrary search criteria.  All of the
queries should perform well.  By "well", I mean within 10 seconds or
so.  Scanning all of the 150 million rows takes much longer than 10
seconds, unfortunately.

Any one of these "solutions" will cause Postgres to do an index scan
in the problematic case where Postgres is deciding to a sequential
scan.  The index scan performs snappily enough:

   - Using "order by" on the query.

   - Changing the search value for the column to a value that occurs
less frequently.

   - Fetching the value to search for via a sub-query so that Postgres
can't determine a priori that the
 value being searched value occurs so commonly.

Unfortunately, as I mentioned, due to the ORM, none of these solutions
really work for us in practice, as opposed to at a psql prompt.

|>ouglas

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

[GENERAL] PL-Debugger installation problem

[Marcos Davi Reis]

Hello All,

I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to
install pldebugger (edb-debugger) to write complex stored procedures.
The problem is i didn't use the source code to install the database and the
instructions found in the documentation is using postgres source code.

how can i instal this utility without the source code?

ps. i'm not a Linux expert nor a DBA, im an application developer!

tks,
Marcos Davi Reis

Re: [GENERAL] ubuntu 9.04 and auto-start

[johnf]

On Thursday 28 May 2009 07:28:04 am Scott Marlowe wrote:
> update-rc.d
>
> On Thu, May 28, 2009 at 7:25 AM, johnf  wrote:
> > Hi,
> > I have installed postgres 8.3 and it runs if I manually start postgres.
> >  That is to say I '/etc/init.d/postgresql start' as root. Runs perfectly.
> >
> > But if I restart the computer postgres does not auto-start.  I've done a
> > little research but did not see anything that jumped off the page to tell
> > me how to get postgres to auto-start.  On openSUSE there is a utility
> > under YAST that allows the user to check postgres to auto-start.  I can't
> > find the same in Ubuntu 9.04.
> >
> > So how do I auto-start Postgres on Ubuntu 9.04?
> > --
> > John Fabiani
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.

Thanks - that sounds easy to do.  I'll research what and how it works this 
evening.

-- 
John Fabiani

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

[GENERAL] configurar el hba

[inf200468]

como puedo modificar el fichero para poder acceder con el pg_admin a una base 
de datos que


--
Participe en Universidad 2010,
del 8 al 12 de febrero del 2010
La Habana, Cuba
http://www.universidad2010.cu
http://www.universidad2010.com

[GENERAL] plperl booleans

[Kevin Field]

When I use spi_prepare and spi_exec_prepared on a query that selects a
boolean value, it comes back as the text 't' or 'f', which in perl
both evaluate to true, so I have to either use perl to solve that or
cast to integer in the query, both of which options seem less than
elegant.  Is this a bug or is it by design?

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

[GENERAL] configurar el hba

[inf200468]

׎<~Ë­­ªuóžwëùÛ_6çOtÓ

como puedo modificar el fichero para poder acceder con el pg_admin a una base 
de datos que
como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] configurar el hba

[inf200468]

ãmõ~Ë­­ªtßmøïùÛ_6çOtÓ

como puedo modificar el fichero para poder acceder con el pg_admin a una base 
de datos que
como puedo modificar el fichero para poder acceder con el pg_admin a una base de datos que׎<~Ë­­ªtßmøïùÛ_6çOtÓ
-- 
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 registry access using java

[Anirban Pal]

In the pg_settings table, all reference to folder is with reference to data 
directory. Like "config_file"   "C:/Program 
Files/PostgreSQL/8.3/data/postgresql.conf""data_directory"   "C:/Program 
Files/PostgreSQL/8.3/data""hba_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_hba.conf""ident_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_ident.conf"No reference to postgres instalation 
base directory, i mean where postgres have been installed. We know it is 
possible to install data directory as users's convinient location, may be 
outside instalation directory. In that case how to fetch postgres 
instalation directory? I am looking for lib directory, which I can found out 
by traversing directory path, once I find what is base installed directory 
for postgresql. Whatever thanks again for your reply. I have opened every 
system table, to find base directory location. I didn't find, may be I am 
missing something. Can you enrich me with the table name, where home 
directory (or installed directory not the data directory) location is 
mentioned.RegardsAnirban



Anirban Pal wrote:

  1st of all thanks for your kind reply. But I can access registry values
using java.


Ok, then you have some additional native code installed :^)

Does the documentation of this add-on suggest a way to search and
traverse Windoes registry keys?


Can any body tell me how to fetch directory path for lib or
data directory for postgres installation in windows using
registry. Solution using Java language will be highly solicited.


Asking the database server does not work for you?

Yours,
Laurenz Albe 

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. 



--
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] Multidimensional array definition in composite type appears parsed as string -- SOLVED

[Tom Lane]

miller_2555  writes:
> I had assumed that the whole slice of a sub-array would have been returned
> as a 1-D array by accessing an element of the "outer" array, but that does
> not appear the case. 

No, it's not.  The semantics are constrained here by the fact that we
don't consider 1-D and 2-D arrays (or any-D arrays) to be distinct
types.  So the parser determines whether the result of a subscript
expression has the element type or the (same) array type based on
whether or not there's a [subscript:subscript] anywhere, not on
how many subscripts there are.

There's been some discussion of tightening things up to throw an
error rather than just returning NULL if an inappropriate number of
subscripts are given, but so far nothing's been done about it.

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] How to speed up the first-time-searching in pgsql?

[Tim Bruce - Postgres]

On Thu, May 28, 2009 01:23, Richard Huxton wrote:
> zxo102 ouyang wrote:
>> Hi there,
>> Thanks for your suggestions. I do have an application running on the
>> machine
>> all the time. In fact, the application keeps writing real-time
>> monitoring
>> data into the database. Based on my understanding of your messages, I
>> can't
>> do anything to speed up the first-time-searching. Probably I can give a
>> waiting process bar to the users and let them wait for the results.
>
> No, I think you missed the detail on some of the answers. There are
> limitations (as discussed in the answers), but the simple suggestion in
> the first answer will probably help a lot.
>
> Set up a scheduled task to run a big search of the database an hour
> before people start work. This can be as simple as a .BAT file running
> "SELECT * FROM big_table" triggered by Windows' Task Scheduler.
>
> --
>Richard Huxton
>Archonet Ltd
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

As a point of note - if you're running Windows XP / Windows NT or higher
(i.e., not Win 9x) you should use .CMD files instead of traditional .BAT
files.  While they accomplish the same thing, the .BAT files run in a
"shared" 16-bit environment as opposed to the 32-bit (or 64-bit) memory
space.  Any application that crashes in the 16-bit environment will crash
or cause instability in other applications running in the same 16-bit
environment (since it's shared).

Functionally, there is no difference (other than expanded functions) in
the .CMD scripts.  All .BAT commands work the same in .CMD - you just need
to rename your .BAT files to .CMD files.

Tim
-- 
Timothy J. Bruce



-- 
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 registry access using java

[John R Pierce]

Anirban Pal wrote:
 
Thanks Josh for your kind reply. In the pg_settings table all reference to the folder is with reference to data directory. Like 
"config_file"   "C:/Program Files/PostgreSQL/8.3/data/postgresql.conf"

"data_directory"   "C:/Program Files/PostgreSQL/8.3/data"
"hba_file"   "C:/Program Files/PostgreSQL/8.3/data/pg_hba.conf"
"ident_file"   "C:/Program Files/PostgreSQL/8.3/data/pg_ident.conf"
No reference to postgres instalation base directory, i mean where postgres have been installed. And it is possible to install data directory as users's convinient location, may be outside instalation directory. In that case how to fetch postgres instalation directory? I am looking for lib directory, which I can found out by traversing directory path once I find what is base installed directory for postgresql. Whatever thanks again for your reply. 



in fact, the data directory can be somewhere quite different from where 
the software is installed.  further, there can be several postgres 
installs on a single system.


that said, in Windows registry key...

HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\Services, you'll 
find a subkey for each install, such as...

HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pgsql-8.3

and in that subkey, you'll find value "Product Code"... append this 
value to...
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\  and you'll find 
values like "Base Directory"



here's that whole section of my registry...


[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pgsql-8.3]
   "Display Name"="PostgreSQL Database Server 8.3"
   "Service Account"="MYCOMPUTER\\postgres"
   "Data Directory"="D:\\postgres\\8.3\\data\\"
   "Port"=dword:1538
   "Database Superuser"="postgres"
   "Encoding"="UTF8"
   "Locale"="English_United States"
   "Product Code"="{B823632F-3B72-4514-8861-B961CE263224}"

[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\{B823632F-3B72-4514-8861-B961CE263224}]
   "Base Directory"="D:\\postgres\\8.3\\"
   "Data Directory"="D:\\postgres\\8.3\\data\\"
   "Version"="8.3"
   "Service ID"="pgsql-8.3"



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

[GENERAL] Continuent (was: Postgres Clustering)

[Alan McKay]

Hmmm.   Anyone out there have the Continuent solution working with PostgreSQL?
If so, what release?  We're at 8.3 right now.

thanks,
-Alan
p.s. I'm continuing the cross-post because that is the way I started
this thread.  Future threads will not be cross-posted.

On Thu, May 28, 2009 at 9:34 AM, Scot Kreienkamp  wrote:
> We didn't have much luck with Continuent.  They had to make multiple
> code level changes to get their product to work correctly with our app
> on PG 8.2.  We never did get it successfully implemented.  At this point
> I'm stuck with WAL shipping as I can't find anything that fits my
> constraints.
>
> Thanks,
>
> Scot Kreienkamp


-- 
“Mother Nature doesn’t do bailouts.”
 - Glenn Prickett

-- 
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] Multidimensional array definition in composite type appears parsed as string -- SOLVED

[miller_2555]

Tom Lane-2 wrote:
> 
> miller_2555  writes:
>> I appreciate the clarification on the output. Given the assignment
>> appears
>> correct, what is the appropriate method to access the elements of the
>> multidimensional array?
> 
> I think what you're missing is the distinction between slice and simple
> element access, ie instead of this
> 
>  (myvar[i]).multidimarray[j]
> 
> you'd need something like this
> 
>  (myvar[i]).multidimarray[j][1:3]
> 
> The proposed loop coding is uselessly overcomplicated because it
> supposes that 2-D arrays could be nonrectangular.  Just use array_lower
> and array_upper on dimension 2 of the 2-D array in the innermost loop.
> 
> You might want to practice a bit with a plain 2-D array field before
> getting into the complexity of embedding it in a composite embedded
> in an array ...
> 
>   regards, tom lane
> 

Thanks - The example is overdone, but it seemed the most illustrative. For
those who would like to see corrected code in case they run into the same
issue, I have appended a new function definition:

CREATE OR REPLACE FUNCTION myschema.mytestfunction() RETURNS void AS $BODY$
DECLARE
myvar myschema.mytype[] := ARRAY[
   
ROW('textaa',ARRAY['textab'],ARRAY[ARRAY['textac1','textac2']])::myschema.mytype,
   
ROW('textba',ARRAY['textbb'],ARRAY[ARRAY['textbc1','textbc2']])::myschema.mytype
];
BEGIN
-- Nested loop example to output each element in multidimensional array
for each composite type
FOR i IN array_lower(myvar,1)..array_upper(myvar,1) LOOP
FOR j IN
array_lower((myvar[i]).multidimarray,1)..array_upper((myvar[i]).multidimarray,1)
LOOP
  /*
  OLD:
  FOR k IN
array_lower((myvar[i]).multidimarray[j],1)..array_upper((myvar[i]).multidimarray[j],1)
LOOP 
  */
  FOR k IN
array_lower((myvar[i]).multidimarray,2)..array_upper((myvar[i]).multidimarray,2)
LOOP
RAISE INFO '%',(myvar[i]).multidimarray[j][k];
END LOOP;
END LOOP;
END LOOP;
END
$BODY$ LANGUAGE 'plpgsql'; 

I had assumed that the whole slice of a sub-array would have been returned
as a 1-D array by accessing an element of the "outer" array, but that does
not appear the case. 

Thanks for the quick help and great product!
-- 
View this message in context: 
http://www.nabble.com/Multidimensional-array-definition-in-composite-type-appears-parsed-as-string-tp23749072p23764714.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] modo texto

[Joao Ferreira gmail]

On Thu, 2009-05-28 at 16:43 +0100, Grzegorz Jaśkiewicz wrote:
> On Thu, May 28, 2009 at 4:24 PM,   wrote:
> > hi, sorry my english
> > I need to copy a data base from windows to linux, how can I save my data
> > base from windows with pg_dump, and where the file is??
> > and after how can I create the data base in linux without graphic enviroment
> > , just text mode, with commands
> 
> yes, pg_dump is just the tool for it.
> 
> when you run pg_dump., you decide where the file is going to be
> stored, by redirecting output to a file:
> 
> pg_dump database > C:\tmp\foo.sql
> 

take a look at:

a) the --clean option and also 
b) pg_dumpall

depending on what exactly you have in hands these options would be
helpfull.

Joao


> 
> -- 
> GJ
> 


-- 
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] modo texto

[Grzegorz Jaśkiewicz]

On Thu, May 28, 2009 at 4:24 PM,   wrote:
> hi, sorry my english
> I need to copy a data base from windows to linux, how can I save my data
> base from windows with pg_dump, and where the file is??
> and after how can I create the data base in linux without graphic enviroment
> , just text mode, with commands

yes, pg_dump is just the tool for it.

when you run pg_dump., you decide where the file is going to be
stored, by redirecting output to a file:

pg_dump database > C:\tmp\foo.sql


-- 
GJ

-- 
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] modo texto

[inf200468]

ßnõ~Ë­­ª{çm8ӎ6×_6çOtÓhi, sorry my englishI need to copy a data base from windows to linux, how can I
save my data base from windows with pg_dump, and where the file is??and after
how can I create the data base in linux without graphic enviroment , just text mode,
with commandsThank> Olá, > > envia a tua
pergunta em inglês. > > Joao > > faz assim:
> > no Windows: > > pg_dumpall --clean -o
myDumpFile.sql > > copia o myDumpFile.sql para o Ubuntu > > no Ubunto > > #sudo bash > #su postgres >
#psql -f myDumpFile.sql postgres > > > > >
On Thu, 2009-05-28 at 10:57 -0400, inf200...@ucf.edu.cu wrote: >> El
servidor de postgres esta en windows, y quiero pasar una base de >> datos a
un servidor en linux con modo texto, segun voy entendiendo >> primero tengo
que hacer pg_dump Base , a mi base de datos en windows, >> lo hago y no me
muestra nada , no se si lo hizo o no , en caso de >> hacerlo genera algun
fichero que no se donde esta >> Gracias >> >> >> __ >> >> Participe en Universidad 2010, >> del 8 al 12 de
febrero del 2010 >> La Habana, Cuba >>
http://www.universidad2010.cu >> http://www.universidad2010.com >> > 
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] modo texto

[inf200468]

El servidor de postgres esta en windows, y quiero pasar una base de datos� a un
servidor en linux con modo texto, segun voy entendiendo primero tengo que hacer 
pg_dump
Base , a mi base de datos en windows, lo hago y no me muestra nada , no se si 
lo hizo o
no , en caso de hacerlo genera algun fichero que no se donde esta
Gracias


--
Participe en Universidad 2010,
del 8 al 12 de febrero del 2010
La Habana, Cuba
http://www.universidad2010.cu
http://www.universidad2010.com

[GENERAL] How to pass parameters into a sql script ?

[Atul Chojar]

We are unable to pass parameters into any sql script. Could anyone look at the 
test below and give any suggestions? PostgreSQL version is 8.2.7, running on 
Linux.

 

Test Script



$ cat chk_param.sql 

select ||:p_date::char(8)||;

select count(*) from prod_debit_payments_unapplied where when_received = 
(select ||:p_date::char(8)||);

select count(*) from prod_debit_payments_unapplied where when_received = 
'20081023';

 

Test Results

=

$ psql -d us_audit -e -1 -v p_date='20081023' -f chk_param.sql 

Timing is on.

select ||20081023::char(8)||;

  ?column?  



 '20081023'

(1 row)

 

Time: 1.474 ms

select count(*) from prod_debit_payments_unapplied where when_received = 
(select ||20081023::char(8)||);--same results with direct assignment 
and to_date

 count 

---

 0

(1 row)

 

Time: 36.290 ms

select count(*) from prod_debit_payments_unapplied where when_received = 
'20081023';

 count 

---

   193

(1 row)

 

Time: 17.722 ms

 

Thanks!

atul

  AirFacts, Inc.8120 Woodmont Ave., Suite 
700Bethesda, MD 20814Tel: 301-760-7315

<>

Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string

[Tom Lane]

miller_2555  writes:
> I appreciate the clarification on the output. Given the assignment appears
> correct, what is the appropriate method to access the elements of the
> multidimensional array?

I think what you're missing is the distinction between slice and simple
element access, ie instead of this

 (myvar[i]).multidimarray[j]

you'd need something like this

 (myvar[i]).multidimarray[j][1:3]

The proposed loop coding is uselessly overcomplicated because it
supposes that 2-D arrays could be nonrectangular.  Just use array_lower
and array_upper on dimension 2 of the 2-D array in the innermost loop.

You might want to practice a bit with a plain 2-D array field before
getting into the complexity of embedding it in a composite embedded
in an array ...

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

[Fwd: Re: [GENERAL] a modo texto]

[inf200468]

۝ú~Ë­­ª}ï­<ë~÷Ó_6çOtÓmi salva es base.backup, con el pg_dump que hagoyo personalizo al ususario
postgres, sudo su postgres, luego psql y despues quegracias>
2009/5/28 : >> Hola, tengo un problema , tengo
que montar una base de datos hecha en >> postgres en ubuntu a modo texto,
alguna idea?? >> Saludos JL >> > > > COPY? > http://www.postgresql.org/docs/current/interactive/sql-copy.html
> > pg_dumpall? >
http://www.postgresql.org/docs/current/interactive/app-pg-dumpall.html > > Osvaldo > 
-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Scott Marlowe]

Note that in the OPs case I'd probably try testing things like turning
off seqscan, or lowering random_page_cost.  I'd also look at
clustering on the index for the field you're selecting on.

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Scott Marlowe]

On Wed, May 27, 2009 at 8:43 PM, Douglas Alan  wrote:
> On Wed, May 27, 2009 at 8:54 PM, Jeff Davis  wrote:
>
>>
>> If you're putting a LIMIT on it, why does it return millions of results?
>
> It doesn't return millions of results with the LIMIT on it.  It just does a
> sequential scan of the table and doesn't find any results until it gets to
> the last quarter of the table. Sequentially scanning through 3/4 of the huge
> table before it gets a single match takes a very long time.
>
> As I said, in my original post, Postgres's approach would be completely
> reasonable in this case, if the rows that it was looking for were sprinkled
> randomly throughout the table.  But they're not in this case -- they're all
> at the end.
>
>> Can you pick out an interesting query and give some specifics, like:
>> * the query
>> * the EXPLAIN ANALYZE output (or EXPLAIN without ANALYZE if it takes too
>> long to even run once)
>> * EXPLAIN ANALYZE output if you force the index scan
>> * the statistics for the relevant columns, such as histogram and
>> correlation
>
> As I mentioned, the situation is very simple, and easy to understand what is
> going on.  There's absolutely no mystery as to why Postgres is doing what
> it's doing.  25% of the table matches the query.  Postgres knows this due to
> the statistics histogram for the column.  Postgres is deciding to do a
> sequential scan because it knows that 25% of the rows match the query.
>
> Unfortunately, in this case, that's a poor approach.

Postgres has a very smart query planner, that has a lot of knobs you
can fiddle with to change how and when it changes from one plan to
another.  It's possible by changing some of those that pgsql will use
a different query plan that is more efficient.  Seeing things like
explain analyze might help someone make a suggestion.  OTOH, if you've
got it all sussed out, then ignore the request for more information.

-- 
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] What is the right way to deal with a table with rows that are not in a random order?

[Tom Lane]

Douglas Alan  writes:
> As I said, in my original post, Postgres's approach would be completely
> reasonable in this case,* if* the rows that it was looking for were
> sprinkled randomly throughout the table.  But they're *not* in this case --
> they're all at the end.

There's been some talk of penalizing the seqscan+limit combination
(perhaps by increasing the estimated start cost for the seqscan) if
the WHERE clause involves any variables that have a correlation stat
significantly different from zero.  But nobody's done the legwork
to see if this would really be useful or what an appropriate penalty
curve might be.

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] ubuntu 9.04 and auto-start

[Scott Marlowe]

Also, sysvconfig tools like RH uses are available:

http://www.cyberciti.biz/tips/how-to-controlling-access-to-linux-services.html

-- 
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] ubuntu 9.04 and auto-start

[Scott Marlowe]

update-rc.d

On Thu, May 28, 2009 at 7:25 AM, johnf  wrote:
> Hi,
> I have installed postgres 8.3 and it runs if I manually start postgres.  That
> is to say I '/etc/init.d/postgresql start' as root. Runs perfectly.
>
> But if I restart the computer postgres does not auto-start.  I've done a
> little research but did not see anything that jumped off the page to tell me
> how to get postgres to auto-start.  On openSUSE there is a utility under YAST
> that allows the user to check postgres to auto-start.  I can't find the same
> in Ubuntu 9.04.
>
> So how do I auto-start Postgres on Ubuntu 9.04?
> --
> John Fabiani
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
When fascism comes to America, it will be intolerance sold as diversity.

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

[GENERAL] Debugger install

[Marcos Davi Reis]

Hello All,

I did install the pgsql 8.3.7 on Ubuntu 8.04 using apt-get, now i need to
install pldebugger (edb-debugger) to write complex stored procedures.
The problem is i didn't use the source code to install the database and the
instructions found in the documentation is using postgres source code.

how can i do to instal this utility without the source code?

ps. i'm not an Linux expert nor a DBA, im an application developer!


Tks
Marcos Davi Reis

Re: [GENERAL] Postgres Clustering

[Scot Kreienkamp]

We didn't have much luck with Continuent.  They had to make multiple
code level changes to get their product to work correctly with our app
on PG 8.2.  We never did get it successfully implemented.  At this point
I'm stuck with WAL shipping as I can't find anything that fits my
constraints.

Thanks,
 
Scot Kreienkamp

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alan McKay
Sent: Wednesday, May 27, 2009 1:57 PM
To: pgsql-performa...@postgresql.org; pgsql-general@postgresql.org
Subject: [GENERAL] Postgres Clustering

Hey folks,

I have done some googling and found a few things on the matter.  But
am looking for some suggestions from the experts out there.

Got any good pointers for reading material to help me get up to speed
on PostgreSQL clustering?   What options are available?  What are the
issues?  Terminology.  I'm pretty new to the whole data-warehouse
thing.   And once I do all the reading, I'll even be open to product
recommendations :-)

And in particular since I already have heard of this particular
product - are there any opinions on Continuent?

thanks,
-Alan

-- 
"Mother Nature doesn't do bailouts."
 - Glenn Prickett

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

[GENERAL] a modo texto

[inf200468]

Hola, tengo un problema , tengo que montar una base de datos hecha en postgres 
en ubuntu
a modo texto, alguna idea??
Saludos JL


--
Participe en Universidad 2010,
del 8 al 12 de febrero del 2010
La Habana, Cuba
http://www.universidad2010.cu
http://www.universidad2010.com

[GENERAL] ubuntu 9.04 and auto-start

[johnf]

Hi,
I have installed postgres 8.3 and it runs if I manually start postgres.  That 
is to say I '/etc/init.d/postgresql start' as root. Runs perfectly.

But if I restart the computer postgres does not auto-start.  I've done a 
little research but did not see anything that jumped off the page to tell me 
how to get postgres to auto-start.  On openSUSE there is a utility under YAST 
that allows the user to check postgres to auto-start.  I can't find the same 
in Ubuntu 9.04.

So how do I auto-start Postgres on Ubuntu 9.04?
-- 
John Fabiani

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

[GENERAL] ERROR: could not find array type for data type numeric[]

[Suha Onay]

Hi,
I want to execute the following query in my Postgresql 8.3 but gives error:
ERROR:  could not find array type for data type numeric[]

When i googled, no result.

 SELECT
*
 FROM (
 SELECT
 *
 FROM(
 SELECT
 ARRAY(
 (SELECT
 (SELECT
-- The problem is here
 ARRAY[sum(borc_alacak[1]), 0.0] as
kalantutar
-- if the line is not an array: "sum(borc_alacak[1]) as kalantutar" no
problem. But, i need more results in an array.

 FROM (
 SELECT
 fat.xxx as borc_alacak, fat.vadetarihi
 FROM scf_fatura fat
 WHERE yyy

UNION ALL

 SELECT
 fissiz.xxx as borc_alacak, fissiz.vadetarihi
 FROM scf_odeme_fissiz fissiz
 WHERE yyy

 ) as ignore

 WHERE vadetarihi <= tarihler[2] AND vadetarihi
>= tarihler[1]

 ) as kalantutar

 FROM (
 SELECT ARRAY[column1, column2] as tarihler
FROM
 ( VALUES  ('2009-05-29'::date,
'2999-01-01'::date) ,
 ('2009-05-01'::date, '2009-05-28'::date) ,
 ('2009-04-01'::date, '2009-04-30'::date) ,
 ('2009-03-01'::date, '2009-03-31'::date) ,
 ('2009-02-01'::date, '2009-02-28'::date) ,
 ('2009-01-01'::date, '2009-01-31'::date) ,
 ('2008-12-01'::date, '2008-12-31'::date) ,  ('2008-11-01'::date,
'2008-11-30'::date) ,
 ('1899-01-01'::date, '2008-10-31'::date) )
as foo
 ) as ddd
 )
 ) as tutarlar,

 cari.*
 FROM scf_carikart cari
 WHERE cari.carikartkodu like '%DENEME%'
 ) as ignore
 ) AS ST


--
Some parts of the sql is automatically generated. Lots of tables so i do not
attached their structure.
It should be easy to find the problem.
Thanks in advance

Suha

Re: [GENERAL] \dt+ on 8.4

[Grzegorz Jaśkiewicz]

now I know it boils down to pg_relation_size at the end of the day,
cos that's what psql uses. But please tell me guys, that I am not the
only one who would expect true size of table in \dt+ ?
At the end of the day, postgresql 'cheats' behind scenes with toast,
and avid user shouldn't really need to account for that!.

Btw, pgAdmin is able to display that information properly.

-- 
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 registry access using java

[Jasen Betts]

On 2009-05-27, Anirban Pal  wrote:
> This is a multi-part message in MIME format.
>
> --=_NextPart_000_0041_01C9DECE.11E417C0
> Content-Type: text/plain;
>   charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi all,
>
> Can any body tell me how to fetch directory path for lib or data =
> directory for postgres installation in windows using registry. Solution =
> using Java language will be highly solicited.=20

why do you want to know that?

here's one way using SQL:
  select setting from pgcatalog.pg_settings where name='data_directory'


-- 
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] composite type and domain

[Grzegorz Jaśkiewicz]

2009/5/27 Scott Bailey :

> Who said anything about the application level?


can you give an example please ?



-- 
GJ

-- 
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 registry access using java

[Albe Laurenz]

Anirban Pal wrote:
> In the pg_settings table, all reference to folder is with reference to data 
> directory.

> No reference to postgres instalation 
> base directory, i mean where postgres have been installed.

That's true, you won't find that in the database.

Yurs,
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] Postgres registry access using java

[Albe Laurenz]

Anirban Pal wrote:
>   1st of all thanks for your kind reply. But I can access registry values 
> using java.

Ok, then you have some additional native code installed :^)

Does the documentation of this add-on suggest a way to search and
traverse Windoes registry keys?

>> Can any body tell me how to fetch directory path for lib or
>> data directory for postgres installation in windows using
>> registry. Solution using Java language will be highly solicited.

Asking the database server does not work for you?

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] Postgres registry access using java

[Dave Page]

On Thu, May 28, 2009 at 10:33 AM, Anirban Pal  wrote:
> @  Laurenz Albe, All
>
>  1st of all thanks for your kind reply. But I can access registry values
> using java. The problem I am facing is, in case of postgres settings
> informations are kept under a subdirectory named after the product key. I am
> able to access from the registry if the path is unique for all the
> computers. Since product key is different for each installed computer, my
> code written for one computer will not work in another.

The product key is unique to each major version of PostgreSQL (ie.
8.3, 8.4 and so on) - it doesn't change for each computer.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.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] Multidimensional array definition in composite type appears parsed as string

[miller_2555]

Tom Lane-2 wrote:
> 
> It sounds like you are using some code that mistakenly thinks that
> double quotes have a semantic meaning here.  They do not.  They are just
> there to delimit members of the row value, not to tell you what type the
> members are.
> 
Note: quoted text abridged per mailing list rules. 

I appreciate the clarification on the output. Given the assignment appears
correct, what is the appropriate method to access the elements of the
multidimensional array? I had mistakenly assumed the following would be
correct:

CREATE TYPE myschema.mytype AS (
sometext text,
onedimarray text[],
multidimarray text[][]
);

CREATE OR REPLACE FUNCTION myschema.mytestfunction() RETURNS void AS $BODY$
DECLARE
myvar myschema.mytype[] := ARRAY[

ROW('textaa',ARRAY['textab'],ARRAY[ARRAY['textac1','textac2']])::myschema.mytype,

ROW('textba',ARRAY['textbb'],ARRAY[ARRAY['textbc1','textbc2']])::myschema.mytype
];
BEGIN
-- Nested loop example to output each element in multidimensional array
for each composite type
FOR i IN array_lower(myvar,1)..array_upper(myvar,1) LOOP
FOR j IN
array_lower((myvar[i]).multidimarray,1)..array_upper((myvar[i]).multidimarray,1)
LOOP
FOR k IN
array_lower((myvar[i]).multidimarray[j],1)..array_upper((myvar[i]).multidimarray[j],1)
LOOP
RAISE INFO '%',(myvar[i]).multidimarray[j][k];
END LOOP;
END LOOP;
END LOOP;
END
$BODY$ LANGUAGE 'plpgsql';

When I attempt the above, it appears (myvar[i]).multidimarray[j] is text
instead of an array of text. Currently, (myvar[i]).multidimarray[j][k]
yields NULL in each case and (myvar[i]).multidimarray[j] yields
"{{textac1,textac2}}" and "{{textbc1,textbc2}}". 

Is there an alternate/ preferred method used to access the elements?

Nearly forgot: running Postgres v8.3 on Fedora 10. 

Thanks again.
-- 
View this message in context: 
http://www.nabble.com/Multidimensional-array-definition-in-composite-type-appears-parsed-as-string-tp23749072p23750913.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Postgres registry access using java

[Anirban Pal]

@  Laurenz Albe, All

 1st of all thanks for your kind reply. But I can access registry values 
using java. The problem I am facing is, in case of postgres settings 
informations are kept under a subdirectory named after the product key. I am 
able to access from the registry if the path is unique for all the 
computers. Since product key is different for each installed computer, my 
code written for one computer will not work in another. If it is possible to 
traverse folders under registry pane, as seen left side of registry, my 
problem will be solved. In my computer.


HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\{B823632E-3B72-4514-8865-B961CE263224}

The last value is different from machine to machine. I am able to fetch key 
values upto HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations. But not 
after that. Yes it is true registry will not make code compatible for linux, 
but I have OS checking part in my code. So, registry part will not run while 
on linux. I have different logic (sompe file reading/scanning) for linux , 
solaris.


Regards
Anirban

Anirban Pal wrote:

Can any body tell me how to fetch directory path for lib or
data directory for postgres installation in windows using
registry. Solution using Java language will be highly solicited.


Java does not give you access to the Windows registry as this is not
portable to other operating systems.

You'd have to use native methods.

Yours,
Laurenz Albe 

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. 



--
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 Install Postgres 8.3 in Solaris 10 as a service

[Anirban Pal]

@Merlin,
@Emanuel

Thank you for your reply. It will surely solve my problem.




Dear all members,

I have been working on postgres (windows), for last 8 months. Recently, a
client requrement requires me to install postgres in solaris 10. What I 
did

is this. Untar the deliverables from postgresql.org website, for correct
version. Open a command window and write necessary commands to run it. If 
I
want to close postgres, I have to close previously opened window and then 
do
the comand writing task again. And If somebody wants to close or restart 
the

postgres server, he/she have to come physically to the server(in some case
remote is not possible). Is it possible to run postgres as a service, in
Solaris 10 (64 bit) also, like windows. Waiting for positive reply.


On solaris (like most unixen), services are controlled with init scripts.

read the following article for a general overview:
http://www.securityfocus.com/infocus/1359

merlin 

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. 



--
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 registry access using java

[Anirban Pal]

Thanks Josh for your kind reply. In the pg_settings table all reference to the 
folder is with reference to data directory. Like "config_file"   "C:/Program 
Files/PostgreSQL/8.3/data/postgresql.conf""data_directory"   "C:/Program 
Files/PostgreSQL/8.3/data""hba_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_hba.conf""ident_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_ident.conf"No reference to postgres instalation 
base directory, i mean where postgres have been installed. And it is possible 
to install data directory as users's convinient location, may be outside 
instalation directory. In that case how to fetch postgres instalation 
directory? I am looking for lib directory, which I can found out by traversing 
directory path once I find what is base installed directory for postgresql. 
Whatever thanks again for your reply. RegardsAnirban
Disclaimer :- This e-mail and any attachment may contain confidential, 
proprietary or legally privileged information. If you are not the original 
intended recipient and have erroneously received this message, you are 
prohibited from using, copying, altering or disclosing the content of this 
message. Please delete it immediately and notify the sender. Newgen Software 
Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising 
from the use of the information transmitted by this email including damages 
from virus and further acknowledges that no binding nature of the message shall 
be implied or assumed unless the sender does so expressly with due authority of 
NSTL. 

Re: [GENERAL] Postgres registry access using java

[Anirban Pal]

Thanks Josh for your kind reply. In the pg_settings table all reference to 
the folder is with reference to data directory. Like "config_file" 
"C:/Program Files/PostgreSQL/8.3/data/postgresql.conf""data_directory" 
"C:/Program Files/PostgreSQL/8.3/data""hba_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_hba.conf""ident_file"   "C:/Program 
Files/PostgreSQL/8.3/data/pg_ident.conf"No reference to postgres instalation 
base directory, i mean where postgres have been installed. And it is 
possible to install data directory as users's convinient location, may be 
outside instalation directory. In that case how to fetch postgres 
instalation directory? I am looking for lib directory, which I can found out 
by traversing directory path once I find what is base installed directory 
for postgresql. Whatever thanks again for your reply. RegardsAnirban



Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL. 



--
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 registry access using java

[Albe Laurenz]

Anirban Pal wrote:
> Can any body tell me how to fetch directory path for lib or 
> data directory for postgres installation in windows using 
> registry. Solution using Java language will be highly solicited. 

Java does not give you access to the Windows registry as this is not
portable to other operating systems.

You'd have to use native methods.

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] How to speed up the first-time-searching in pgsql?

[Richard Huxton]

zxo102 ouyang wrote:

Hi there,
Thanks for your suggestions. I do have an application running on the machine
all the time. In fact, the application keeps writing real-time monitoring
data into the database. Based on my understanding of your messages, I can't
do anything to speed up the first-time-searching. Probably I can give a
waiting process bar to the users and let them wait for the results.


No, I think you missed the detail on some of the answers. There are 
limitations (as discussed in the answers), but the simple suggestion in 
the first answer will probably help a lot.


Set up a scheduled task to run a big search of the database an hour 
before people start work. This can be as simple as a .BAT file running 
"SELECT * FROM big_table" triggered by Windows' Task Scheduler.


--
  Richard Huxton
  Archonet Ltd

--
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] What is the right way to deal with a table with rows that are not in a random order?

[Simon Riggs]

On Wed, 2009-05-27 at 19:53 -0400, Douglas Alan wrote:
> We have a very large table (150 million rows) where the rows are not
> in a random order.  Some common queries will have millions of results,
> and this of course is slow.  For an interactive interface to the
> database, we want to put a limit on all queries so that queries will
> return quickly even if there are millions of results. 

Partition the table, then scan the correct partition.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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