Re: [GENERAL] a JOIN on same table, but 'slided over'

[Gurjeet Singh]

On 6/26/07, Tom Lane <[EMAIL PROTECTED]> wrote:


"news.gmane.org" <[EMAIL PROTECTED]> writes:
> Gurjeet Singh skrev:
>> Also note that this query is much cheaper that the 'distinct on' query
>> by more than two orders on magnitude ( 217.86 vs. 98040.67):

> No it isn't. The estimate is much lower, but the actual times are very
> close:

> [explain of distinct on]
>> Time: 5.003 ms

> [explain of correlated subquery]
>> Time: 4.125 ms

You're both confused:



???

the planner estimate certainly should not be taken

as gospel,



true

but the actual runtime of an EXPLAIN (not EXPLAIN ANALYZE)

only reflects planning effort.



Agree completely

EXPLAIN ANALYZE output would be a lot more suitable to settle the

question which one is faster.



Agree again. I was using the EXPLAIN output just to make a point that
optimizer thinks the query utilizing a subquery is much cheaper (and hence
maybe faster) than the 'distinct on' query.

In a later mail I posted the analyze o/p too...

--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

17°29'34.37"N  78°30'59.76"E - Hyderabad *
18°32'57.25"N  73°56'25.42"E - Pune

Sent from my BlackLaptop device

Re: [GENERAL] growing disk usage problem: alternative solution?

[Vivek Khera]

On Jun 26, 2007, at 3:31 PM, Bill Moran wrote:

VACUUM FULL and REINDEX are not required to maintain disk usage.   
Good old-
fashoned VACUUM will do this as long as your FSM settings are high  
enough.




I find this true for the data but not necessarily for indexes.  The  
other week I reindexed a couple of O(100,000,000) row tables and  
shaved about 20Gb of index bloat.  Those tables are vacuumed  
regularly, but we do a large data purge every few weeks.  I think  
that causes some issues.  I'm running 8.1.


To mitigate the downtime, we make use of the fact that we have live  
replica of the database on similarly capable hardware so we bring the  
replica offline, reindex it, bring it back up, move the "master" to  
it, then reindex the other server, and move the master back.



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] AutoVacuum Behaviour Question

[Alvaro Herrera]

Bruce McAlister wrote:

> I have just checked the pg_stat_all_tables in the pg_catalog schema and
> I can see the index scans etc table values incrementing. The data in the
> tables seems to be updating. Just an FYI, I've enabled manual vacuum
> analyze runs on the blueface-service database up until we've found whats
> going wrong here. The output from the select query you suggested is as
> follows:
> 
>  datname | datconfig
> -+---
>  postgres|
>  blueface-webmail|
[etc]

Ok so it's not that you manually disabled autovacuum.  And pgstat is
working on those databases.  And all databases share the postgresql.conf
autovacuum configuration.

> I enabled the log_line_prefix option and put in a %p in the value, and I
> only get the following output from the logs:
> 
> Jun 26 23:46:07 bfiedb01 postgres[2836]: [ID 748848 local0.debug] [5-1]
>2836 DEBUG:  autovacuum: processing database "blueface-crm"
> Jun 26 23:47:32 bfiedb01 postgres[2863]: [ID 748848 local0.debug] [5-1]
>2863 DEBUG:  autovacuum: processing database "blueface-crm"

I assume that there is no other line for process 2836 before the line
for process 2863.  Can you recheck that?

What does
select datname, age(datfrozenxid) from pg_database;
show?


> I've also just run a manual 'VACUUM ANALYZE FULL VERBOSE;' just to
> ensure it goes through manually.

And it does finish successfully?


> What does the DEBUG1 output of a normal autovacuum run look like in the
> log file?

Nothing interesting shows up:

LOG:  autovacuum: processing database "test1"
LOG:  autovacuum: processing database "test2"

If you try with debug2, it looks a bit more interesting:

LOG:  autovacuum: processing database "test2"
DEBUG:  autovac: will VACUUM foo
DEBUG:  vacuuming "public.foo"
DEBUG:  "foo": removed 1 row versions in 55 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG:  "foo": found 1 removable, 0 nonremovable row versions in 55 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG:  "foo": truncated 55 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.


The only thing I can think of right now is that pgstats does not have
entries for the other databases for some reason.  How can that happen
escapes me.

-- 
Alvaro Herrera  Developer, http://www.PostgreSQL.org/
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem."  (Tom Lane)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] AutoVacuum Behaviour Question

[Bruce McAlister]

Alvaro Herrera wrote:
> Bruce McAlister wrote:
>> Hi All,
>>
>> I have enabled autovacuum in our PostgreSQL cluster of databases. What I
>> have noticed is that the autovacuum process keeps selecting the same
>> database to perform autovacuums on and does not select any of the others
>> within the cluster. Is this normal behaviour or do I need to do
>> something more elaborate with my settings?
> 
> There are two reasons autovacuum would keep picking up the same
> database:
> 
> 1. the other databases do not have pgstat entries.
> 
> 2. this database is in danger of Xid wraparound and the vacuum run
> fails to complete for some reason.
> 
>> Our main concern is the "blueface-service" database. The sipaccounts
>> table has some high traffic, mainly updates.
> 
> Are there non-null values in the pg_stat views for tables in
> blueface-service database?  If there are, then you can discard (1) as
> the problem.  If all values are nulls for all tables, then you have the
> stats collector disabled for that database, or something (maybe by ALTER
> DATABASE ... SET).  In this case, reenable it and issue a manual VACUUM
> so that pgstat is populated.  (I think the easiest way to check is
> SELECT datname, datconfig FROM pg_database).

I have just checked the pg_stat_all_tables in the pg_catalog schema and
I can see the index scans etc table values incrementing. The data in the
tables seems to be updating. Just an FYI, I've enabled manual vacuum
analyze runs on the blueface-service database up until we've found whats
going wrong here. The output from the select query you suggested is as
follows:

 datname | datconfig
-+---
 postgres|
 blueface-webmail|
 blueface-billingreports |
 blueface-service|
 blueface-cards  |
 template1   |
 template0   |
 blueface-crmsupport |
 blueface-qualmon|
 asterisk-cdrgw  |
 hylafax |
 thelab-sipswitch|
 whitelabel-ibb  |
 whitelabel-pleasant |
 whitelabel-rapid|
 whitelabel-test |
 whitelabel-worlddest|
 blueface-crm|
 blueface-billedcalls|
 asterisk-cdr|
 mysipswitch |
 whitelabel-ice  |



> 
> Regarding (2) you would need to check whether the autovacuum run dies
> with an ERROR.  I'd advise setting a log_line_prefix that included the
> PID (%p) so that you can check whether the process goes away cleanly or
> it dies early.  It is not impossible that a corrupt index or table is
> causing autovacuum to die, but it should certainly show up in the logs.
> 
I enabled the log_line_prefix option and put in a %p in the value, and I
only get the following output from the logs:

Jun 26 23:46:07 bfiedb01 postgres[2836]: [ID 748848 local0.debug] [5-1]
   2836 DEBUG:  autovacuum: processing database "blueface-crm"
Jun 26 23:47:32 bfiedb01 postgres[2863]: [ID 748848 local0.debug] [5-1]
   2863 DEBUG:  autovacuum: processing database "blueface-crm"


I cant really tell from these logs if the process is dying early or not.

I have also just run a 'REINDEX DATABASE "blueface-crm";' just to ensure
that the indexes are sane.

I've also just run a manual 'VACUUM ANALYZE FULL VERBOSE;' just to
ensure it goes through manually.

What does the DEBUG1 output of a normal autovacuum run look like in the
log file?

Any other suggestions would be greatly appreciated.

Thanks

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] a JOIN on same table, but 'slided over'

[PFC]

OK, check...

test=> CREATE TABLE test (id INTEGER PRIMARY KEY);
test=> INSERT INTO test SELECT random()*5 + n*10 FROM  
generate_series( 1,10 ) AS n;

test=> SELECT * FROM test LIMIT 10;
  id
-
  11
  23
  31
  41
  52
  63
  70
  85
  94
 103

test=> ANALYZE test;
ANALYZE

-- Self Join 1

test=> EXPLAIN ANALYZE SELECT t1.id AS current_id, t2.id AS next_id
FROM test t1, test t2
WHERE t2.id = ( SELECT min(id) FROM test AS t3 WHERE t3.id > t1.id )
ORDER BY t1.id;
QUERY  
PLAN

---
 Sort  (cost=26703.19..26953.19 rows=10 width=8) (actual  
time=5240.392..5271.529 rows=9 loops=1)

   Sort Key: t1.id
   ->  Hash Join  (cost=2691.00..18398.37 rows=10 width=8) (actual  
time=106.588..5179.737 rows=9 loops=1)

 Hash Cond: ((subplan) = t2.id)
 ->  Seq Scan on test t1  (cost=0.00..1441.00 rows=10 width=4)  
(actual time=0.013..34.782 rows=10 loops=1)
 ->  Hash  (cost=1441.00..1441.00 rows=10 width=4) (actual  
time=106.420..106.420 rows=10 loops=1)
   ->  Seq Scan on test t2  (cost=0.00..1441.00 rows=10  
width=4) (actual time=0.007..43.077 rows=10 loops=1)

 SubPlan
   ->  Result  (cost=0.03..0.04 rows=1 width=0) (actual  
time=0.023..0.023 rows=1 loops=19)

 InitPlan
   ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual  
time=0.021..0.022 rows=1 loops=19)
 ->  Index Scan using test_pkey on test t3   
(cost=0.00..1029.59 rows=3 width=4) (actual time=0.020..0.020 rows=1  
loops=19)

   Index Cond: (id > $0)
   Filter: (id IS NOT NULL)
 Total runtime: 5295.677 ms

-- Self Join 2

test=> set enable_hashjoin TO 0;
test=> EXPLAIN ANALYZE SELECT t1.id AS current_id, t2.id AS next_id
FROM test t1, test t2
WHERE t2.id = ( SELECT min(id) FROM test AS t3 WHERE t3.id > t1.id )
ORDER BY t1.id;
  QUERY  
PLAN

---
 Sort  (cost=30806.48..31056.48 rows=10 width=8) (actual  
time=2876.249..2903.011 rows=9 loops=1)

   Sort Key: t1.id
   ->  Merge Join  (cost=9745.82..22501.66 rows=10 width=8) (actual  
time=2547.830..2820.347 rows=9 loops=1)

 Merge Cond: (t2.id = "inner"."?column2?")
 ->  Index Scan using test_pkey on test t2  (cost=0.00..2828.26  
rows=10 width=4) (actual time=0.035..67.747 rows=10 loops=1)
 ->  Sort  (cost=9745.82..9995.82 rows=10 width=4) (actual  
time=2547.779..2582.889 rows=10 loops=1)

   Sort Key: (subplan)
   ->  Seq Scan on test t1  (cost=0.00..1441.00 rows=10  
width=4) (actual time=0.060..2487.728 rows=10 loops=1)

 SubPlan
   ->  Result  (cost=0.03..0.04 rows=1 width=0)  
(actual time=0.023..0.023 rows=1 loops=10)

 InitPlan
   ->  Limit  (cost=0.00..0.03 rows=1 width=4)  
(actual time=0.021..0.022 rows=1 loops=10)
 ->  Index Scan using test_pkey on  
test t3  (cost=0.00..1029.59 rows=3 width=4) (actual time=0.020..0.020  
rows=1 loops=10)

   Index Cond: (id > $0)
   Filter: (id IS NOT NULL)
 Total runtime: 2923.804 ms

-- DISTINCT ON

test=> EXPLAIN SELECT DISTINCT ON (t1.id) t1.id AS current_id, t2.id AS  
next_id

FROM test t1 JOIN test t2 ON t2.id > t1.id
ORDER BY t1.id, t2.id;
   QUERY PLAN
-
 Unique  (cost=729806679.75..746473346.41 rows=10 width=8)
   ->  Sort  (cost=729806679.75..738140013.08 rows=33 width=8)
 Sort Key: t1.id, t2.id
 ->  Nested Loop  (cost=0.00..100028973.00 rows=33 width=8)
   ->  Seq Scan on test t1  (cost=0.00..1441.00 rows=10  
width=4)
   ->  Index Scan using test_pkey on test t2   
(cost=0.00..583.61 rows=3 width=4)

 Index Cond: (t2.id > t1.id)
(7 lignes)

This one takes much longer (I interrupted it).

-- Using a function

CREATE TYPE test_type AS ( current_id INTEGER, next_id INTEGER );

CREATE OR REPLACE FUNCTION testfunc( )
RETURNS SETOF test_type
LANGUAGE plpgsql
AS
$$
DECLARE
_rowtest_type;
BEGIN
_row.current_id = NULL;

FOR _row.next_id IN SELECT

Re: [GENERAL] Ordering in SELECT statement

[PFC]

On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote:
and what I need is the following ("old fashion", that is, the "SPACE"  
is another character whose ASCII value is before any other LATIN  
letter's!!)

AB  CD
AB  EF
ABAB
ABD  E


What you don't want :

peufeu=> SELECT column1 FROM (VALUES ('ABCD'), ('A BCD'), ('abcd'),  
('ABcd'), ('AB'), ('AbC d')) AS foo  ORDER BY column1;

 column1
-
 AB
 abcd
 AbC d
 ABcd
 ABCD
 A BCD
(6 lignes)

What you want :

peufeu=> SELECT column1 FROM (VALUES ('ABCD'), ('A BCD'), ('abcd'),  
('ABcd'), ('AB'), ('AbC d')) AS foo  ORDER BY string_to_array( column1, '  
' );

 column1
-
 A BCD
 AB
 AbC d
 abcd
 ABcd
 ABCD
(6 lignes)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] escaped rolenames in pg_has_role

[Willy-Bas Loos]

er.. i guess i was misinformed. (by Korry & Susan Douglas´ second edition,
maybe this is relatively new?)
However, could you please answer my question?


On 6/26/07, Tom Lane <[EMAIL PROTECTED]> wrote:


"Willy-Bas Loos" <[EMAIL PROTECTED]> writes:
>> A quote in the data might be real data or quoting, and you have no way
to
>> tell the difference.

> It's not so bad, because a parameter of type name cannot start with a
quote.

Sure it can.

regression=# select '"Fooey"'::name;
  name
-
"Fooey"
(1 row)

regression=# create table foo ( """Fooey""" int);
CREATE TABLE
regression=# \d foo
  Table "public.foo"
Column  |  Type   | Modifiers
-+-+---
"Fooey" | integer |

regression=#

regards, tom lane

Re: [GENERAL] LC_CTYPE and matching accented chars

[Martin Langhoff]

Alvaro Herrera wrote:
> Martin Langhoff wrote:
>>   # this is apparently the right way to
>>   # select base character based on the "equivalence class"
>>   # as defined in the LC_CTYPE
>>   =# select * from test where value ~ 'mart[=i=]n';
> 
> I think it would be much easier if you did something like
> 
> select * from test where lower(to_ascii(value)) = lower(to_ascii('martín'));
> 
> When to_ascii doesn't work (for example because it doesn't work in UTF8)
> you may want to use convert() to recode the text to latin1 or latin9.

Well, with the example above to_ascii doesn't work.

  select to_ascii(value) from test ;
  ERROR:  encoding conversion from UTF8 to ASCII not supported

And neither does convert

  select convert(value using utf8_to_ascii) from test ;
  ERROR:  character 0xc3 of encoding "MULE_INTERNAL" has no equivalent
   in "SQL_ASCII"

  select convert('martín' using utf8_to_ascii) from test ;
  ERROR:  character 0xc3 of encoding "MULE_INTERNAL" has no equivalent
   in "SQL_ASCII"

Any other alternatives?

BTW, this is on Pg8.2 as seen on Ubuntu Feisty PPC.

  $ dpkg -l postgresql-8.2
  ii  postgresql-8.28.2.4-0ubuntu0.7.04

cheers,


martín
-- 
---
Martin @ Catalyst .Net .NZ  Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/   PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224  UK: 0845 868 5733 ext 7224  MOB: +64(21)364-017
  Make things as simple as possible, but no simpler - Einstein
---


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Rafal Pietrak]

I see. (Have actually tried it on a larger dataset - to see it for
myself ... it is optimised :)

Thenx again!

-R


On Tue, 2007-06-26 at 19:56 +0530, Gurjeet Singh wrote:
> It _is_ the optimised version as you can see from the explain
> plans posted in the other mail, the planner shows that the cost is
> drastically less than the 'distinct on' version.
> 
> For smaller data-sets 'distinct-on' version might seem faster, but
> for reasonably larger datasets, it's performance deteriorates
> exponentially... This is because of the Nested-loops involved in the
> plan...
> 
> I increased your data-set to 10240 rows by executing the following
> query 10 times:
> 
> insert into test select id+(select max(id) from test), thread, info
> from test;
> 
> On such data-set (which is not very large by any means), the
> standard SQL version executes in almost a second, and on the other
> hand, I had to cancel the EXPLAIN ANALYZE of the 'distinct on' query
> after letting it run for over three minutes!!!
> 
> postgres=# explain analyze
> postgres-# select   t1.id as id, t2.id as "id+1",
> postgres-#  t1.thread as thread, t2.thread as "thread+1",
> postgres-#  t1.info as info, t2.info as "info+1"
> postgres-# from test as t1, test as t2
> postgres-# where t2.id = ( select min(id) from test as t3 where t3.id
> > t1.id )
> postgres-# order by t1.id asc;
> 
> QUERY PLAN
> --
>  
>  Sort  (cost=2971.36..2996.96 rows=10240 width=24) (actual
> time=1004.031..1030.116 rows=10239 loops=1)
>Sort Key: t1.id
>Sort Method:  external sort  Disk: 416kB
>->  Merge Join  (cost=840.48..2289.28 rows=10240 width=24) (actual
> time=834.218..956.595 rows=10239 loops=1) 
>  Merge Cond: (t2.id = ((subplan)))
>  ->  Index Scan using test_id_key on test t2
> (cost=0.00..332.85 rows=10240 width=12) (actual time=0.060..24.503
> rows=10240 loops=1)
>  ->  Sort  (cost=840.48..866.08 rows=10240 width=12) (actual
> time=834.129..854.776 rows=10240 loops=1)
>Sort Key: ((subplan))
>Sort Method:  quicksort  Memory: 928kB
>->  Seq Scan on test t1  (cost=0.00..158.40 rows=10240
> width=12)(actual time=0.196..797.752 rows=10240 loops=1)
>  SubPlan
>->  Result  (cost= 0.04..0.05 rows=1 width=0)
> (actual time=0.062..0.064 rows=1 loops=10240)
>  InitPlan 
>->  Limit  (cost=0.00..0.04 rows=1
> width=4) (actual time=0.047..0.050 rows=1 loops=10240)
>  ->  Index Scan using test_id_key
> on test t3  (cost=0.00..121.98 rows=3413 width=4) (actual time=
> 0.038..0.038 rows=1 loops=10240)
>Index Cond: (id > $0)
>Filter: (id IS NOT NULL)
>  Total runtime: 1052.802 ms
> (18 rows)
> Time: 1056.740 ms
> 
> postgres=# explain analyze
> postgres-# select
> postgres-# distinct on (t1.id)
> postgres-# t1.*, t2.*
> postgres-# from
> postgres-# test t1
> postgres-# join test t2 on t2.id > t1.id
> postgres-# order by t1.id asc, t2.id asc;
> Cancel request sent
> ERROR:  canceling statement due to user request 
> postgres=#
> 
> 
> 
> On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> OK. Have tried this one looks like close to 6 times slower
> then the
> 'non-standard' phrase with 'distinct on'.
> 
> On the small dataset that I've included in my original post
> (ten rows of
> data within TEST), I've run both queries through EXPLAIN
> ANALYSE, with 
> the following result summary (for clearity, I've cut away the
> details
> from EXPLAIN output):
> 
> ---STANDARD
> Total runtime: 10.660 ms
> ---DISTINCT-ON
> Total runtime: 1.479 ms
> --- 
> 
> Would there be ways to optimise the standard query to get the
> performance closer to the none-standard one?
> 
> 
> -R
> 
> 
> On Tue, 2007-06-26 at 18:05 +0530, Gurjeet Singh wrote:
> > Hi Rafal,
> > 
> > Just a note that this is not standard SQL... 'distinct
> on' is an
> > extension to SQL provided by postgres.
> >
> > Following query utilizes the standard SQL to get the same
> results:
> > 
> > selectt1.id as id, t2.id as "id+1",
> > t1.thread as thread, t2.thread as "thread+1",
> > t1.info as info, t2.info as "info+1"
> > from test as t1, test as t2
> > where t2.id = ( select min(id) from test as t3 where t3.id >
> t1.id);
> 

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

[Tom Lane]

Richard Huxton <[EMAIL PROTECTED]> writes:
> jef peeraer wrote:
>> registratie=# select * from module_info where type_module_id in
>> (select * from get_parent_type_modules(1));

>> The order is completely ignored, although there is an order by in the view
>> 'module_info'

> You're applying a where clause to the output of your view - filtering it 
> after the sort is done. If that filter is e.g. by a hash then the result 
> will be in a different order (or at least might be).

Yeah, that IN-clause will in fact be turned into a join.  EXPLAIN would
give more info about what's happening, but I suspect the planner chose
to do the join via mergejoin, which would sort the inputs by the join
columns ...

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] escaped rolenames in pg_has_role

[Tom Lane]

"Willy-Bas Loos" <[EMAIL PROTECTED]> writes:
>> A quote in the data might be real data or quoting, and you have no way to
>> tell the difference.

> It's not so bad, because a parameter of type name cannot start with a quote.

Sure it can.

regression=# select '"Fooey"'::name;
  name   
-
 "Fooey"
(1 row)

regression=# create table foo ( """Fooey""" int);
CREATE TABLE
regression=# \d foo
  Table "public.foo"
 Column  |  Type   | Modifiers 
-+-+---
 "Fooey" | integer | 

regression=# 

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

[Michael Glaesemann]

On Jun 26, 2007, at 14:52 , jef peeraer wrote:

The order is completely ignored, although there is an order by in  
the view

'module_info'


I don't know what has caused the change in your system, but IIRC, the  
spec does not require a view to return rows in any particular order— 
I'm not even sure the spec allows ORDER BY in a view definition.  
(Whether or not PostgreSQL follows the spec in this case is another  
matter—I don't believe it does. So there's probably something a  
little more interesting going on here that others might help you with.)


A quick, spec-compliant fix would be to add an ORDER BY clause in the  
calling query.


Other than the row order, the results look correct, or am I missing  
something?


You might also look into using a set returning function, in which you  
can specify the order of the results.


Michael Glaesemann
grzm seespotcode net




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

[Richard Huxton]

jef peeraer wrote:

i decide this weekend to upgrade to the latest stable version from an
8.1.4 . Upgrade went smootly, as usual, but today, i've got some
phonecalls of something weird. The query is as follows :


registratie=# select * from module_info where type_module_id = 1;



i combine this with the next query in a subquery



The resulting query , which should return the same result as the first one


Here's where I think you're wrong.


registratie=# select * from module_info where type_module_id in
(select * from get_parent_type_modules(1));



The order is completely ignored, although there is an order by in the view
'module_info'


You're applying a where clause to the output of your view - filtering it 
after the sort is done. If that filter is e.g. by a hash then the result 
will be in a different order (or at least might be).


In general, the only ORDER BY you can rely on is one applied to the 
final results of your SELECT.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Ordering in SELECT statement

[Michael Glaesemann]

On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote:
and what I need is the following ("old fashion", that is, the  
"SPACE" is another character whose ASCII value is before any other  
LATIN letter's!!)

AB  CD
AB  EF
ABAB
ABD  E
Sorting is defined by the locale settings of your computer. I get the  
order you're looking for, and the my computer has LC_COLLATE="C".


$ locale
LANG=
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL="C"

# select * from sorts order by sort;
sort
---
AB CD
AB EF
ABAB
ABD E
(4 rows)

I haven't had any experience setting the locale on my computer, but  
that might be something you want to look into.


Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Ordering in SELECT statement

[Richard Huxton]

[EMAIL PROTECTED] wrote:


Hello,

I use the following command "SELECT * FROM employees ORDER BY name"
(a very simple sql statement) the main thing here is that I get a
list ordered without taking into accound the spaces. For example, I
get the following listing:
ABAB
AB  CD
ABD  E
AB  EF

and what I need is the following ("old fashion", that is, the
"SPACE" is another character whose ASCII value is before any other
LATIN letter's!!)


You'll want to read up on locales - you want the "C" locale. I'm afraid 
it'll mean dumping your database(s) and running initdb again.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] upgrade 8.1.4 -> latest, sort order subquery

[jef peeraer]

i decide this weekend to upgrade to the latest stable version from an
8.1.4 . Upgrade went smootly, as usual, but today, i've got some
phonecalls of something weird. The query is as follows :


registratie=# select * from module_info where type_module_id = 1;
  naam  | korte_beschrijving |  kolom_naam   |   xul_opties|
type_veld_id | lengte | is_verplicht | type_module_id
++---+-+--++--+
 alg_02 | naam   | naam  | | 


3 |  0 | f|  1
 alg_03 | voornaam   | voornamen | | 


3 |  0 | f|  1
 alg_01 | anoniem| anoniem   | | 


2 |  0 | f|  1
 cl_01  | geslacht   | geslacht  | | 


1 |  0 | t|  1
 cl_19  | geboortedatum  | geboortedatum | hide_clock,true | 


7 |  0 | f|  1
 cl_02  | leeftijd   | leeftijd  | | 


1 |  0 | t|  1
 cl_15  | origine| origine   | | 


1 |  0 | f|  1
(7 rows)


i combine this with the next query in a subquery

registratie=# select * from get_parent_type_modules(1);
NOTICE:   first query
 get_parent_type_modules
-
   1
(1 row)


The resulting query , which should return the same result as the first one

registratie=# select * from module_info where type_module_id in
(select * from get_parent_type_modules(1));
NOTICE:   first query
  naam  | korte_beschrijving |  kolom_naam   |   xul_opties|
type_veld_id | lengte | is_verplicht | type_module_id
++---+-+--++--+
 cl_15  | origine| origine   | | 


1 |  0 | f|  1
 cl_02  | leeftijd   | leeftijd  | | 


1 |  0 | t|  1
 cl_19  | geboortedatum  | geboortedatum | hide_clock,true | 


7 |  0 | f|  1
 cl_01  | geslacht   | geslacht  | | 


1 |  0 | t|  1
 alg_01 | anoniem| anoniem   | | 


2 |  0 | f|  1
 alg_03 | voornaam   | voornamen | | 


3 |  0 | f|  1
 alg_02 | naam   | naam  | | 


3 |  0 | f|  1
(7 rows)


The order is completely ignored, although there is an order by in the view
'module_info'



jef peeraer

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Throwing exceptions

[Michael Glaesemann]

On Jun 25, 2007, at 19:01 , Michael Glaesemann wrote:

The message is just a string. Assign the message to a variable and  
use the variable in place of the message. For example, in PL/pgSQL:


k_error_message := 'Boom! %';
RAISE EXCEPTION k_error_message, v_foo.id;


I was wrong. The message is not just a string, but you can  
interpolate the message text (and other variables) like so:


RAISE EXCEPTION '%, %', k_error_message, v_foo_id;

Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] growing disk usage problem: alternative solution?

[Bill Moran]

In response to [EMAIL PROTECTED]:

> Hello List
> 
> I have been a user since 7.x version.  My current server version is 8.1.2.
> 
> As many of you know the disk usage keeps growing for postgresql unless one 
> regularly reindex and/or fully vacuum.  The problem with either method is 
> that they lock the tables and it is not practical for databases that one 
> wants to keep it available for 24x7x365 basis.  For very large tables, while 
> the tables are being reindexed, there will be many waiting 
> update/delete/insert processes and this is not at all desirable.
> 
> Server response is still great even with growing disk usage.  Any one has 
> other ideas to check the growth of the disk usage.

VACUUM FULL and REINDEX are not required to maintain disk usage.  Good old-
fashoned VACUUM will do this as long as your FSM settings are high enough.

There _will_ be _some_ wasted space, but (in my experience, at least) this
will hit a plateau and level off.  Frequent VACUUM FULLs bloat your indexes
and require frequent REINDEXES, so should be avoided unless needed.

-- 
Bill Moran
http://www.potentialtech.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Ordering in SELECT statement

[[EMAIL PROTECTED]]

Hello,

I use the following command "SELECT * FROM employees ORDER BY name"
(a very simple sql statement) the main thing here is that I get a
list ordered without taking into accound the spaces. For example, I
get the following listing:
ABAB
AB  CD
ABD  E
AB  EF

and what I need is the following ("old fashion", that is, the
"SPACE" is another character whose ASCII value is before any other
LATIN letter's!!)
AB  CD
AB  EF
ABAB
ABD  E

Does any one know how to order get an old fashioned SORT??? 

Re: [GENERAL] escaped rolenames in pg_has_role

[Willy-Bas Loos]

A quote in the data might be real data or quoting, and you have no way to

tell the difference.
It´s not so bad, because a parameter of type name cannot start with a quote.

But you´re right, i have been having a hard time with the use of quoting.
The rule i used until now was: "every string that has been entered by a user
must be escaped, so that there can never be sql inserts".
Every function has all string input escaped. When you call another function
from inside your function, don´t pass any un-escaped strings, you can´t
allways be sure that others escape their strings properly.
Just for safety´s sake.
This hasn´t been verfy practical in it´s use.

Do I understand correctly that you mean to say:
"User-input strings do not need to be escaped, except when you build an SQL
string with it, i.e. when using EXECUTE. When calling functions from inside
your functions, it is the responability of the receiving function to handle
escaping of its input."
Or in other words,i suppose, are EXECUTE and PERFORM really the only ways
that sql in the form of input string parameters could be executed inside my
function?

WBL




On 6/26/07, Tom Lane <[EMAIL PROTECTED]> wrote:


"Willy-Bas Loos" <[EMAIL PROTECTED]> writes:
> The problem is that pg_has_role does not recognize the usernames when
they
> are escaped by quote_literal or quote_ident.

Don't use quote_ident here.  In fact, I'd say that you appear to have
completely misunderstood the appropriate rules for quoting at all.
If you are passed data that you don't know if it's quoted or not, what
the heck are you supposed to do with it?  A quote in the data might be
real data or quoting, and you have no way to tell the difference.

You need to redesign your function stack to eliminate that ambiguity.
I'd recommend that data items never have quoting, except at the instant
that they get embedded into SQL strings (which this function isn't doing
at all, and so it does not need to quote the name).

regards, tom lane

Re: [GENERAL] growing disk usage problem: alternative solution?

[Joshua D. Drake]

[EMAIL PROTECTED] wrote:

Hello List

I have been a user since 7.x version.  My current server version is 8.1.2.

As many of you know the disk usage keeps growing for postgresql unless one 
regularly reindex and/or fully vacuum.  The problem with either method is that 
they lock the tables and it is not practical for databases that one wants to 
keep it available for 24x7x365 basis.  For very large tables, while the tables 
are being reindexed, there will be many waiting update/delete/insert processes 
and this is not at all desirable.



That is not true. If you have a proper vacuum strategy then you should 
be able to maintain a specific level of growth that is directly related 
to your dataset growth.


In short if you vacuum enough, you don't have to vacuum full.


Server response is still great even with growing disk usage.  Any one has other 
ideas to check the growth of the disk usage.

Please do not cc the response to me.  I will read it in the list.


Not how this list works, your options are:

1. You only receive the reply
2. You + the list receives the reply

Most users opt for 2.

Sincerely,

Joshua D. Drake




Sincerely

Z-m Wu

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] growing disk usage problem: alternative solution?

[wu_zhong_min]

Hello List

I have been a user since 7.x version.  My current server version is 8.1.2.

As many of you know the disk usage keeps growing for postgresql unless one 
regularly reindex and/or fully vacuum.  The problem with either method is that 
they lock the tables and it is not practical for databases that one wants to 
keep it available for 24x7x365 basis.  For very large tables, while the tables 
are being reindexed, there will be many waiting update/delete/insert processes 
and this is not at all desirable.

Server response is still great even with growing disk usage.  Any one has other 
ideas to check the growth of the disk usage.

Please do not cc the response to me.  I will read it in the list.

Sincerely

Z-m Wu

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] escaped rolenames in pg_has_role

[Tom Lane]

"Willy-Bas Loos" <[EMAIL PROTECTED]> writes:
> The problem is that pg_has_role does not recognize the usernames when they
> are escaped by quote_literal or quote_ident.

Don't use quote_ident here.  In fact, I'd say that you appear to have
completely misunderstood the appropriate rules for quoting at all.
If you are passed data that you don't know if it's quoted or not, what
the heck are you supposed to do with it?  A quote in the data might be
real data or quoting, and you have no way to tell the difference.

You need to redesign your function stack to eliminate that ambiguity.
I'd recommend that data items never have quoting, except at the instant
that they get embedded into SQL strings (which this function isn't doing
at all, and so it does not need to quote the name).

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] A problem in inheritance

[Talha Khan]

to impact that update you'd have to have a rule on
account_login. No rule on a child table will matter.


Well i had the same perception In order to double check this i created
an On insert do nothing rule on the child table and did an insertion to the
master table i.e (account_login) but this time the On insert do nothing rule
did affect and no insert was done to the child table...

Note: I did not have any on insert do nothing rule on the master table.

Regards,

Talha Amjad


On 6/23/07, Jim Nasby <[EMAIL PROTECTED]> wrote:


On Jun 20, 2007, at 12:53 PM, Talha Khan wrote:
> THE ACTUAL TEST:
>
>   DROP RULE account_login_no_update ON account_login;
>
>
>   UPDATE account_login set originating_source = 'xxx';
>
>   Now the update should not effect the child table but it does,
> evident
>   from the output of the following query:

That's because you dropped the rule that would have affected that
query. Remember that rules effectively work on the query strings
themselves, so to impact that update you'd have to have a rule on
account_login. No rule on a child table will matter.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

Re: [GENERAL] problem importing data with psql

[Richard Huxton]

chandresh rana wrote:

Hi Richard,

I have eliminated certain tables while exporting as they the size of the
data in the tables are huge.Am having the schema of all the tables.

Now want to import certain set of records/rows from the eliminated tables.

Is it possible to do that ?? If yes, then can you please tell me how I can
accomplish this?


The only practical way would be to process the rows outside the 
database, perhaps in a Perl script or similar. Pipe COPY-format data 
dumps through your script and have it strip out unwanted lines as it 
feeds them to psql.


Otherwise, you'll have to import all the rows and then delete the ones 
you want.



How postgresql take care of all the constraints/referential integrity while
importing the records from the different tables?


It won't do anything different with a standard COPY vs inserting rows 
normally. You'll probably want to turn off constraints and indexes and 
then re-apply them after the import.


Is there a good reason why you're not using pg_dump/pg_restore for this?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Tom Lane]

"news.gmane.org" <[EMAIL PROTECTED]> writes:
> Gurjeet Singh skrev:
>> Also note that this query is much cheaper that the 'distinct on' query
>> by more than two orders on magnitude ( 217.86 vs. 98040.67):

> No it isn't. The estimate is much lower, but the actual times are very
> close:

> [explain of distinct on]
>> Time: 5.003 ms

> [explain of correlated subquery]
>> Time: 4.125 ms

You're both confused: the planner estimate certainly should not be taken
as gospel, but the actual runtime of an EXPLAIN (not EXPLAIN ANALYZE)
only reflects planning effort.

EXPLAIN ANALYZE output would be a lot more suitable to settle the
question which one is faster.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] View Triggers

[Richard Broersma Jr]

It seems that insertion triggers on views will/may be added in version 8.3.
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php

However, Tom mentioned that adding update and deletion triggers may be a bad 
idea:
http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php
...
However, I'm not sure that the idea scales to cover updates and
deletes; with no concept of physical tuple identity (ctid) for the view
rows, it's not clear that you can write triggers that will reliably do
the right things.
...

I am curious about the dangers could occur if UPDATE triggers were implemented 
on a view?  I
assume that these dangers are most apparent with views that are based upon 
joined tables rather
than views on a single table.  

Do these dangers also apply to update DO INSTEAD rules?

Could there exist any view design guidelines that if followed would allow for 
the safe utilization
of update triggers?

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] a JOIN on same table, but 'slided over'

[news.gmane.org]

Gurjeet Singh skrev:
> I missed the ORDER BY clause... Here it goes:
> 
> selectt1.id  as id, t2.id  as "id+1",
> t1.thread as thread, t2.thread as "thread+1",
> t1.info  as info, t2.info  as
> "info+1"
> from test as t1, test as t2
> where t2.id  = ( select min(id) from test as t3 where
> t3.id  > t1.id  )
> order by t1.id  asc;
> 
> Also note that this query is much cheaper that the 'distinct on' query
> by more than two orders on magnitude ( 217.86 vs. 98040.67):

No it isn't. The estimate is much lower, but the actual times are very
close:

[explain of distinct on]

> Time: 5.003 ms

[explain of correlated subquery]

> Time: 4.125 ms

I tried on a larger table (16384 rows), and in this case the numbers are
strongly in favor of  the subquery. In fact, I am still waiting for the
"distinct on" version to return ...

/Nis


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Rule vs Trigger

[Joshua D. Drake]

Tom Lane wrote:

"Albe Laurenz" <[EMAIL PROTECTED]> writes:

Richard Broersma Jr wrote:



A rule would only
execute one additional statement. So if you can do it with a rule
conveniently, the rule will probably be faster.


I find this unlikely.  The overhead involved in setting up a rule
is probably larger than that involved in calling a trigger.
The real question is whether you need access to the modified data
or not --- a statement-level trigger doesn't currently get that.


Not that Tom ever needs any backing up, but when we started testing 
partitioning, we first tested with rules and then tested with triggers. 
Triggers are quite a bit faster than rules.


Joshua D. Drake




regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Duplicate records returned

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Richard Huxton <[EMAIL PROTECTED]> writes:

>> $the_sql  = " SELECT projectname, username, sum(hours)";
>> $the_sql .= " FROM timerecs";
>> $the_sql .= " WHERE projectname = projects.projectname ";
>> $the_sql .= " AND projectname = restrictions.projectname";
>> $the_sql .= " AND projects.parent = 'Projects'";
>> $the_sql .= " AND projects.pct < 100";
>> $the_sql .= " AND restrictions.hidden = 5";
>> $the_sql .= " AND projectname = 'Testing'";  # just for tsting
>> $the_sql .= " AND username = 'long'";# just for testing
>> $the_sql .= " AND projectname = projects.projectname ";
>> $the_sql .= " GROUP BY projectname, username";
>> $the_sql .= " ORDER BY projectname, username";
>> $the_sql .= " ;";

> You might want to read up on "HERE documents" for multi-line blocks of text.

In case the above code is Perl, I think

  my $sql = q{
SELECT ...
FROM ...
WHERE ...
GROUP ...
  };

looks nicer than a here-document.

> 1. You've also not put all your tables into the FROM clause:
>FROM timerecs, projects, restrictions
>This *should* be generating a warning of some kind
> 2. You're not qualifying which column comes from which table, which
> makes it harder to see what's happening. Try:
>FROM timerecs t, projects p, restrictions r
>WHERE t.projectname = p.projectname
>AND ...
>That's called table aliasing, where you give a short name to tables.

This still mixes JOIN conditions with other result restrictions.

  SELECT ...
  FROM timerecs t
  JOIN projects p ON p.projectname = t.projectname
  ...

makes it more explicit.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Rule vs Trigger

[Albe Laurenz]

>> A trigger FOR EACH STATEMENT will execute the trigger function
>> for each row affacted by the statement.
> 
> Huh?  That would be true for a FOR EACH ROW trigger, but a STATEMENT
> trigger fires once per statement.

Argh. I intended to write FOR EACH ROW.
Thanks for the correction.

>> A rule would only
>> execute one additional statement. So if you can do it with a rule
>> conveniently, the rule will probably be faster.
> 
> I find this unlikely.  The overhead involved in setting up a rule
> is probably larger than that involved in calling a trigger.
> The real question is whether you need access to the modified data
> or not --- a statement-level trigger doesn't currently get that.

Yes, I meant to write about row level triggers, maybe what
I wrote makes more sense then...

What I said was inspired by chapter 35.6. of the documentation.

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] pg_catalog.pg_get_serial_sequence() returns NULL

[Sergey Karin]

Hi!

I use PG 8.1.5

I execute in psql next comands:

create table t_table (gid serial, name varchar);

select pg_catalog.pg_get_serial_sequence('t_table', 'gid');
pg_get_serial_sequence

public.t_table_gid_seq

create table t_table_1() inherits (t_table);

\d t_table_1
Table "public.t_table_1"
column |   Type| Модификаторы
-+---+---
gid | integer   | not null default
nextval('t_table_gid_seq'::regclass)
name| character varying |
Inherit: t_table

select pg_catalog.pg_get_serial_sequence('t_table_1', 'gid');
pg_get_serial_sequence




How I can get name of sequence used by gid column of partition table? And
why for t_table_1 pg_get_serial_sequence() returns NULL?
It returns NULL also if I create new sequence manually and use ALTER TABLE
ALTER COLUMN  SET DEFAULT nextval(::regclass).

In http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL I
can read that type SERIAL is equivalent to

CREATE SEQUENCE *tablename*_*colname*_seq;
CREATE TABLE *tablename* (
   *colname* integer DEFAULT nextval('*tablename*_*colname*_seq') NOT NULL
);

Thanks in advance

Sergey Karin

Re: [GENERAL] how to implement unusual constraint

[Andrew Sullivan]

On Sun, Jun 24, 2007 at 06:45:00PM -, danmcb wrote:
> into two tables, one for the originals, one for the translations (the
> objects are actually phrases in many languages). But I'd rather avoid
> that because in all other ways, the objects have the same properties,
> reference the same objects, and so on. Splitting into two types is not
> really desirable, from the point of view of what they are modelling.

This sounds to me like you have a one:many possibility, and you
should split your columns up instead.  (In general, I find, when you
have a self-join, it's probably an indicator that some of the data is
repeated, and that tells you you're not normalised correctly.)

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] escaped rolenames in pg_has_role

[Willy-Bas Loos]

Dear General,

I have stolen some code from information_schema.applicable_roles, so that i
can query the roles for a user, without having to become that user (a
superuser executes this).
The problem is that pg_has_role does not recognize the usernames when they
are escaped by quote_literal or quote_ident.
I allow a period "." as a character in usernames in the front-end, so
escaping is necessary in most cases. Also, it´s a principle that all
user-typed text is escaped to prevent SQL inserts, even through user names.

I think that the authorization of PostgreSQL has been designed with great
care, so i´m not sure if this might be called a "bug".
But it seems that i can´t use this function.
Does anyone have the surrogate SQL statement lying around? (from before
pg_has_role was born)

here´s my code:
-
CREATE OR REPLACE FUNCTION contacts.user_roles(p_role name)
RETURNS SETOF text
AS
$body$
DECLARE
--non-existant roles will result in an error.
arecord record;
t_role name;
BEGIN
t_role := quote_ident(trim(both '\'' from trim(both '\"' from
p_role)));--'"--quotes might allready have been added by a calling function
--RAISE NOTICE 'getting roles for role: %', t_role;
 FOR arecord IN
   (SELECT b.rolname::information_schema.sql_identifier AS role_name
   FROM pg_auth_members m
   JOIN pg_authid a ON m.member = a.oid
   JOIN pg_authid b ON m.roleid = b.oid
   WHERE pg_has_role(t_role, a.oid, 'MEMBER'::text))
 LOOP
   RETURN NEXT arecord.role_name;
 END LOOP;
END
$body$
LANGUAGE plpgsql STRICT STABLE;
-

WBL

Re: [GENERAL] LC_CTYPE and matching accented chars

[Alvaro Herrera]

Martin Langhoff wrote:
> Trying to understand how I can get Pg 8.2 to match 'martín' when I
> search for 'martin', and going through the documentation, specially
> 
>   http://www.postgresql.org/docs/8.2/static/locale.html
>   http://www.postgresql.org/docs/8.1/static/functions-matching.html
> 
> Here is a transcript of my commandline - I suspect I am quite close, but
> something is missing. Hints _really_ appreciated!

>   =# create table test (value text);
>   CREATE TABLE
>   =#  insert into test values ('martín');
>   INSERT 0 1
> 
>   # this is apparently the right way to
>   # select base character based on the "equivalence class"
>   # as defined in the LC_CTYPE
>   =# select * from test where value ~ 'mart[=i=]n';

I think it would be much easier if you did something like

select * from test where lower(to_ascii(value)) = lower(to_ascii('martín'));

When to_ascii doesn't work (for example because it doesn't work in UTF8)
you may want to use convert() to recode the text to latin1 or latin9.

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Rule vs Trigger

[Tom Lane]

"Albe Laurenz" <[EMAIL PROTECTED]> writes:
> Richard Broersma Jr wrote:
>> My understanding is that Triggers offer better performance
>> than rules do.

> A trigger FOR EACH STATEMENT will execute the trigger function
> for each row affacted by the statement.

Huh?  That would be true for a FOR EACH ROW trigger, but a STATEMENT
trigger fires once per statement.

> A rule would only
> execute one additional statement. So if you can do it with a rule
> conveniently, the rule will probably be faster.

I find this unlikely.  The overhead involved in setting up a rule
is probably larger than that involved in calling a trigger.
The real question is whether you need access to the modified data
or not --- a statement-level trigger doesn't currently get that.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Gurjeet Singh]

I missed the ORDER BY clause... Here it goes:

selectt1.id as id, t2.id as "id+1",
   t1.thread as thread, t2.thread as "thread+1",
   t1.info as info, t2.info as "info+1"
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id > t1.id )
order by t1.id asc;

Also note that this query is much cheaper that the 'distinct on' query by
more than two orders on magnitude (217.86 vs. 98040.67):

postgres=# explain
postgres-# select
postgres-# distinct on (t1.id)
postgres-# t1.*, t2.*
postgres-# from
postgres-# test t1
postgres-# join test t2 on t2.id > t1.id
postgres-# order by t1.id asc, t2.id asc;
  QUERY PLAN

Unique  (cost=95798.00..98040.67 rows=1160 width=80)
  ->  Sort  (cost=95798.00..96919.33 rows=448533 width=80)
Sort Key: t1.id, t2.id
->  Nested Loop  (cost=0.00..13827.29 rows=448533 width=80)
  ->  Seq Scan on test t1  (cost=0.00..21.60 rows=1160
width=40)
  ->  Index Scan using test_id_key on test t2
(cost=0.00..7.06rows=387 width=40)
Index Cond: (t2.id > t1.id)
(7 rows)
Time: 5.003 ms
postgres=# explain
postgres-# select   t1.id as id, t2.id as "id+1",
postgres-#  t1.thread as thread, t2.thread as "thread+1",
postgres-#  t1.info as info, t2.info as "info+1"
postgres-# from test as t1, test as t2
postgres-# where t2.id = ( select min(id) from test as t3 where t3.id >
t1.id )
postgres-# order by t1.id asc;
   QUERY PLAN
--
Sort  (cost=214.96..217.86 rows=1160 width=80)
  Sort Key: t1.id
  ->  Hash Join  (cost=36.10..155.92 rows=1160 width=80)
Hash Cond: ((subplan) = t2.id)
->  Seq Scan on test t1  (cost=0.00..21.60 rows=1160 width=40)
->  Hash  (cost=21.60..21.60 rows=1160 width=40)
  ->  Seq Scan on test t2  (cost=0.00..21.60 rows=1160
width=40)
SubPlan
  ->  Result  (cost=0.13..0.14 rows=1 width=0)
InitPlan
  ->  Limit  (cost=0.00..0.13 rows=1 width=4)
->  Index Scan using test_id_key on test t3  (cost=
0.00..51.02 rows=387 width=4)
  Index Cond: (id > $0)
  Filter: (id IS NOT NULL)
(14 rows)
Time: 4.125 ms


Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

17°29'34.37"N  78°30'59.76"E - Hyderabad *
18°32'57.25"N  73°56'25.42"E - Pune

Sent from my BlackLaptop device

On 6/26/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote:


Hi Rafal,

Just a note that this is not standard SQL... 'distinct on' is an
extension to SQL provided by postgres.

Following query utilizes the standard SQL to get the same results:

selectt1.id as id, t2.id as "id+1",
t1.thread as thread, t2.thread as "thread+1",
t1.info as info, t2.info as "info+1"
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id > t1.id);

HTH
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

17°29'34.37"N  78°30'59.76"E - Hyderabad *
18°32'57.25"N  73°56'25.42 "E - Pune

Sent from my BlackLaptop device

On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
>
> Marvelous! Thenx!
>
> -R
>
> On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> > On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> > Is there an SQL construct to get it?
> >
> > select
> > distinct on (t1.id)
> > t1.*, t2.*
> > from
> > test t1
> > join test t2 on t2.id > t1.id
> > order by t1.id asc, t2.id asc
> >
> > should do the trick.
> >
> > depesz
> >
> > --
> > http://www.depesz.com/ - nowy, lepszy depesz
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Rafal Pietrak]

OK. Have tried this one looks like close to 6 times slower then the
'non-standard' phrase with 'distinct on'. 

On the small dataset that I've included in my original post (ten rows of
data within TEST), I've run both queries through EXPLAIN ANALYSE, with
the following result summary (for clearity, I've cut away the details
from EXPLAIN output):

---STANDARD
 Total runtime: 10.660 ms
---DISTINCT-ON
 Total runtime: 1.479 ms
---

Would there be ways to optimise the standard query to get the
performance closer to the none-standard one?


-R


On Tue, 2007-06-26 at 18:05 +0530, Gurjeet Singh wrote:
> Hi Rafal,
> 
> Just a note that this is not standard SQL... 'distinct on' is an
> extension to SQL provided by postgres.
> 
> Following query utilizes the standard SQL to get the same results:
> 
> selectt1.id as id, t2.id as "id+1",
> t1.thread as thread, t2.thread as "thread+1",
> t1.info as info, t2.info as "info+1"
> from test as t1, test as t2
> where t2.id = ( select min(id) from test as t3 where t3.id > t1.id);
> 
> HTH
> -- 
> [EMAIL PROTECTED]
> [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
> 
> 17°29'34.37"N  78°30'59.76"E - Hyderabad *
> 18°32'57.25"N  73°56'25.42 "E - Pune
> 
> Sent from my BlackLaptop device 
> 
> On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> Marvelous! Thenx!
> 
> -R
> 
> On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski
> wrote:
> > On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote: 
> > Is there an SQL construct to get it?
> >
> > select
> > distinct on (t1.id)
> > t1.*, t2.*
> > from
> > test t1
> > join test t2 on t2.id > t1.id
> > order by t1.id asc, t2.id asc
> >
> > should do the trick.
> >
> > depesz
> >
> > --
> > http://www.depesz.com/ - nowy, lepszy depesz
> 
> ---(end of
> broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org/
> 

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] dynamic table/col names in plpgsql

[Martijn van Oosterhout]

On Sat, Jun 23, 2007 at 09:43:01PM -0600, gary jefferson wrote:
> Is there a way to use a variable as the name of a table or column in 
> plpgsql?

No, plpgsql is statically typed, it can't handle the possiblity of the
types of variables changing. Use a more dynamic
language(perl/tcl/python/etc...)

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Gurjeet Singh]

Hi Rafal,

   Just a note that this is not standard SQL... 'distinct on' is an
extension to SQL provided by postgres.

Following query utilizes the standard SQL to get the same results:

selectt1.id as id, t2.id as "id+1",
   t1.thread as thread, t2.thread as "thread+1",
   t1.info as info, t2.info as "info+1"
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id > t1.id);

HTH
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

17°29'34.37"N  78°30'59.76"E - Hyderabad *
18°32'57.25"N  73°56'25.42"E - Pune

Sent from my BlackLaptop device

On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:


Marvelous! Thenx!

-R

On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> Is there an SQL construct to get it?
>
> select
> distinct on (t1.id)
> t1.*, t2.*
> from
> test t1
> join test t2 on t2.id > t1.id
> order by t1.id asc, t2.id asc
>
> should do the trick.
>
> depesz
>
> --
> http://www.depesz.com/ - nowy, lepszy depesz

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] a JOIN on same table, but 'slided over'

[Rafal Pietrak]

Marvelous! Thenx! 

-R

On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
> On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
> Is there an SQL construct to get it?
> 
> select
> distinct on (t1.id)
> t1.*, t2.*
> from
> test t1
> join test t2 on t2.id > t1.id
> order by t1.id asc, t2.id asc
> 
> should do the trick.
> 
> depesz
> 
> -- 
> http://www.depesz.com/ - nowy, lepszy depesz

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Rule vs Trigger

[Albe Laurenz]

Richard Broersma Jr wrote:
> --- Alfred Zhao <[EMAIL PROTECTED]> wrote:
>> Suppose I want to update a statistics table S after an
>> insertion into an activity table A. In order to update S
>> automatically, I can either create a rule on A or create
>> an after trigger on A. What's the performance implication
>> on using rule vs trigger? Thanks in advance.
>
> My understanding is that Triggers offer better performance
> than rules do.

A trigger FOR EACH STATEMENT will execute the trigger function
for each row affacted by the statement. A rule would only
execute one additional statement. So if you can do it with a rule
conveniently, the rule will probably be faster.

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] postgresql varchar[] data type equivalent in Oracle

[Albe Laurenz]

Murali Doss wrote:
> 
> I like to know about postgresql varchar[ ] data type 
> equivalent in Oracle.

The best I can think of is a VARRAY, though you cannot access
it by index in SQL (you need a stored procedure or client API
for that). Quite clumsy.

Example:

SQL> CREATE TYPE VARCHAR_A AS VARRAY(100) OF VARCHAR2(10);
  2  /

Type created.

SQL> CREATE TABLE N(ID NUMBER(10,0) PRIMARY KEY, A VARCHAR_A);

Table created.

SQL> INSERT INTO N VALUES (1, VARCHAR_A('one', 'two', 'three'));

1 row created.

SQL> SELECT A FROM N WHERE ID=1;

A


VARCHAR_A('one', 'two', 'three')

SQL> SELECT X.* FROM N, TABLE(N.A) X WHERE ID=1;

COLUMN_VALUE

one
two
three


Yours,
Laurenz Albe

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] problem importing data with psql

[Richard Huxton]

cha wrote:


But am not able to import the tables with batch file, though am able to run
the same command from the prompt successfully.


Why? What errors are you getting?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Duplicate records returned

[Richard Huxton]

[EMAIL PROTECTED] wrote:

I'm getting duplicate rows returned.  I don't know know and can't find
out how to construct the SQL to return what I want.  I have an old
version of postgres which I cannot upgrade.  I'm not even sure how to
get the version.  Does this help?

$ grep -i version /pi/bin/setup
wtversion='2.0'
export wtversion


Nope - nothing to do with PostgreSQL I'm afraid.

Try issuing "SELECT version()" as an SQL statement.



My SQL knowledge is quite limited so I've searched all over the net
and read doco but I can't figure this out.

I want to find the total number of hours worked on all projects with
projects.parent="Projects", projects.pct<100, restrictions.hidden=5
(not hidden)

I keep getting duplicate records and I think it's it's becuase I'm
joining a table on 2 other tables.  I've tried INTERSECT but I keep
getting parse errors and can't work out why that is.  I've tried
creating a temp table but still have to do the twin joins!!  I've also
tried creating a view but without success



but there will be many projects to process and to select the
applicable projects requires test on 2 other tables, projects and
restrictions

$the_sql  = " SELECT projectname, username, sum(hours)";
$the_sql .= " FROM timerecs";
$the_sql .= " WHERE projectname = projects.projectname ";
$the_sql .= " AND projectname = restrictions.projectname";
$the_sql .= " AND projects.parent = 'Projects'";
$the_sql .= " AND projects.pct < 100";
$the_sql .= " AND restrictions.hidden = 5";
$the_sql .= " AND projectname = 'Testing'";   # just for tsting
$the_sql .= " AND username = 'long'"; # just for testing
$the_sql .= " AND projectname = projects.projectname ";
$the_sql .= " GROUP BY projectname, username";
$the_sql .= " ORDER BY projectname, username";
$the_sql .= " ;";


You might want to read up on "HERE documents" for multi-line blocks of text.

1. You've also not put all your tables into the FROM clause:
   FROM timerecs, projects, restrictions
   This *should* be generating a warning of some kind
2. You're not qualifying which column comes from which table, which 
makes it harder to see what's happening. Try:

   FROM timerecs t, projects p, restrictions r
   WHERE t.projectname = p.projectname
   AND ...
   That's called table aliasing, where you give a short name to tables.
3. This query *can't* give duplicates for (projectname,username) pairs 
unless you're activating a bug. The GROUP BY eliminates duplicates.



produces

1 Testing|long|254

How do I get the right list of projectname from timerecs by joining
with the projects and restrictions tables?


You've not said what "right" means to you.


I've tried SELECT DISTINCT projectname but make no difference.  If I
take the 'restrictions' join out it's fine.  I've also tried prefacing
all column names with table names without any change


Try the table aliasing, then post the query again (oh, trim the perl/php 
if you could) along with some sample data, the results and what the 
results should be. Otherwise there's not much anyone can say.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] a JOIN on same table, but 'slided over'

[PFC]

Now, I'd like to make a JOIN-ed query of that table with itself, so that
I'd get rows paiwise: every row containing data from *two* rows of the
original TEST table so, that those data come from rows of consequtive
ID's - not neceserly (depending on the TEST table contents) continuesly
consequtive. Like:

SELECT * from view_of_test;
 id | id+X | thread | thread+X | info  | info+X
+--++--+---+-
  2 |3 |763 |  764 | A | B
  3 |6 |764 |5 | B | C
  6 |8 |  5 |88946 | C | Cats
  8 |9 |  88946 |69315 | Cats  | Eifel
  9 |   10 |  69315 |96379 | Eifel | G
-
Is there an SQL construct to get it?


	I would use a plpgsql procedure, select all the rows ORDER BY id, keep  
the current and last row in a variable, and that's it.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] data partitions across different nodes

[hubert depesz lubaczewski]

On 6/26/07, 金星星 <[EMAIL PROTECTED]> wrote:


Are there any solutions based on PostgreSQL that can support
distributing partitions (horizontal fragmentations) across different
nodes. It doesn't need to support distributed transaction, since data
inconsistent is not a critical problem in my situation.



look at skype tools, to be specific - pl/proxy was made to make this kind of
things possible.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

Re: [GENERAL] a JOIN on same table, but 'slided over'

[hubert depesz lubaczewski]

On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:


Is there an SQL construct to get it?



select
   distinct on (t1.id)
   t1.*, t2.*
from
   test t1
   join test t2 on t2.id > t1.id
order by t1.id asc, t2.id asc

should do the trick.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

[GENERAL] a JOIN on same table, but 'slided over'

[Rafal Pietrak]

Hi,

I understand, that this is 'general SQL' question rather then 'general
postgres'. But may be someone here could help me with it anyways.

I have a *single* table:

CREATE TABLE test (id int not null unique, thread int not null, info
text);

The ID, although unique, is not continues. A sample query:

SELECT * from test;
 id | thread | info 
++--
  2 |763 | A
  3 |764 | B
  6 |  5 | C
  8 |  88946 | Cats
  9 |  69315 | Eifel
 10 |  96379 | G
 14 |  23927 | test 1
 16 |  16529 | test 2
 17 |634 | test 3
 20 |  63930 | batman
(10 rows)
-

Now, I'd like to make a JOIN-ed query of that table with itself, so that
I'd get rows paiwise: every row containing data from *two* rows of the
original TEST table so, that those data come from rows of consequtive
ID's - not neceserly (depending on the TEST table contents) continuesly
consequtive. Like:

SELECT * from view_of_test;
 id | id+X | thread | thread+X | info  | info+X 
+--++--+---+-
  2 |3 |763 |  764 | A | B
  3 |6 |764 |5 | B | C
  6 |8 |  5 |88946 | C | Cats
  8 |9 |  88946 |69315 | Cats  | Eifel
  9 |   10 |  69315 |96379 | Eifel | G
-
Is there an SQL construct to get it?

I'd apreciate any hints or sugestions.

-R

---(end of broadcast)---
TIP 6: explain analyze is your friend