[GENERAL] 64bits or 32 bits on ESX?

[Bjørn T Johansen]

We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 
4... Is there any performance improvement by choosing 64bits Linux over
32bits Linux as the guest OS or is it almost the same?


Regards,

BTJ

-- 
---
Bjørn T Johansen

b...@havleik.no
---
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic 
messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
---

-- 
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] function to grant select on all tables in several schemas

[Gerd Koenig]

Hello Andreas,

thanks for your support, perfect :-)

regardsGERD

On Wednesday 20 January 2010 4:59:57 pm Andreas Kretschmer wrote:
> Gerd Koenig  wrote:
> > Hello,
> >
> > I'm looking for a solution to grant select to a group to have "read-only"
> > group across all tables/views/.. in several schemas. I already found some
> > workarounds and I decided to create a function to cover this topic.
> > But calling this function throws the error:
> > ""
> > ERROR:  column "´r´" does not exist
> > LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´,
> > ´v´,´... ^
> > QUERY:   SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> > pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t,
> > pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid
> > AND s.nspname = ´tisys´ order by s.nspname
> > CONTEXT:  PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over
> > SELECT rows
> > ""
> >
> > The function was created by:
> > ""
> > CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> > RETURNS TEXT AS '
> > DECLARE
> > sql text;
> > rel record;
> > BEGIN
> > FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> >   pg_catalog.quote_ident(t.relname) AS relation_name
> > FROM pg_class t, pg_namespace s
> > WHERE t.relkind IN (´r´, ´v´,´S´)
> > AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
> > LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' ||
> > rel.relation_name || '' TO ro_group'';
> > RAISE NOTICE ''%'', sql;
> > EXECUTE sql;
> > END LOOP;
> > RETURN ''OK'';
> > END;
> > ' LANGUAGE 'plpgsql';
> > COMMENT ON FUNCTION grant_select_to_ro_group()
> > IS 'Give select privilege ON all relations in the given schema TO
> > ro_group.'; ""
> >
> > ...and has been called by:
> > ""
> > select grant_select_to_ro_group();
> > ""
> >
> > any hints appreciated..GERD
>
> You should better use $$ instead of ' for the function-body.
> (unless you have a very old pg-version ...)
>
> I think the ´ as quoting-sign is also wrong...
>
>
> Rewrite your function to:
>
> CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> RETURNS TEXT AS $$
> DECLARE
> sql text;
> rel record;
> BEGIN
> FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
>   pg_catalog.quote_ident(t.relname) AS relation_name
> FROM pg_class t, pg_namespace s
> WHERE t.relkind IN ('r', 'v','S')
> AND t.relnamespace=s.oid AND s.nspname = 'tisys' order by s.nspname
> LOOP sql := 'GRANT SELECT ON ' || rel.schema_name || '.' ||
> rel.relation_name || ' TO ro_group';
> RAISE NOTICE '%', sql;
> EXECUTE sql;
> END LOOP;
> RETURN 'OK';
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> now it works: (i have copy&paste your function into a file and edit it
> there)
>
> kretsch...@tux:~$ psql test
> Zeitmessung ist an.
> psql (8.4.2)
> Geben Sie »help« für Hilfe ein.
>
> test=# \i grant.sql
> CREATE FUNCTION
> Zeit: 239,453 ms
> test=*# select grant_select_to_ro_group();
>  grant_select_to_ro_group
> --
>  OK
> (1 Zeile)
>
> Zeit: 48,836 ms
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°



-- 
/\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Magirus-Deutz-Str. 16 | Stadtregal
| DE - 89077 Ulm
|
| Tel: +49 [0]731 16906 106
| Fax: +49 [0]731 16906 99
| koe...@transporeon.com
| www.transporeon.com
|
\/


TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Peter Förster, Roland Hötzl, Marc-Oliver Simon

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

[A. Kretschmer]

In response to beulah prasanthi :
> I am using postgres 8.4
> using views i retrived some details from multiple tables
> can i insert the data into multiple tables using views.if not
> how can i insert the data into multiple tables in a single trip
> is there any option .Please help me
> 

You can use a RULE, simple example:

test=# create table a (a int);
CREATE TABLE
test=*# create table b (b int);
CREATE TABLE
test=*# commit;
COMMIT
test=# create view view_ab as select a.a, b.b from a left join b on a.a=b.b;
CREATE VIEW
test=*# commit;
COMMIT
test=# create rule rule_ab as on insert to view_ab do instead (insert into a 
values (new.a); insert into b values(new.b););
CREATE RULE
test=*# insert into view_ab values (1,1);
INSERT 0 1
test=*# select * from a;
 a
---
 1
(1 row)

test=*# select * from b;
 b
---
 1
(1 row)

test=*# select * from view_ab;
 a | b
---+---
 1 | 1
(1 row)

test=*#

Bernd Helme is working on 'updateable VIEWS', but i don't know the
actual status of this patch.

Upcoming new release (8.4+1) maybe contains writeable CTE, another way
to do this. (insert into multiple tables)

And yes, as said, you can use a transaction (begin; insert ...; insert
...; commit;)



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

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

Re: [GENERAL] more docs on extending postgres in C

[Adrian von Bidder]

On Wednesday 20 January 2010 15.42:14 Ivan Sergio Borgonovo wrote:
> I'd also appreciate some suggestion about dev environment and best
> practices on Debian, something that could help me to compile,
> install, test easily on Debian.

(Disclaimer: Haven't done any postgres related programming so far, so this 
might need adjusting.)

The desciption for package postgresql-server-dev-8.4 includes 

[[[
 This package also contains the Makefiles necessary for building add-on
 modules of PostgreSQL, which would otherwise have to be built in the
 PostgreSQL source-code tree.
]]]

so this package plus C compiler and the other usual tools should get you 
started.  (There is also the 'build-essential' Debian package which will 
ensure you have a basic C/C++ development environment.  For other packages 
you might need to build extensions you could look at the build dependencies 
of the postgresql-8.4 *source* Debian package; I'm offline right now but I 
think  should get you this 
information.)

If you find that you can not compile an extension with only this server-dev 
package but need other stuff from the PostgreSQL source, I'm sure the 
PostgreSQL packagers would appreciate a bug report.)

(I hope I'm not just telling you stuff you alredy know; sorry if so.)

cheers
-- vbi

-- 
Today is Setting Orange, the 20th day of Chaos in the YOLD 3176


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] helo

[John R Pierce]

beulah prasanthi wrote:

I am using postgres 8.4
using views i retrived some details from multiple tables
can i insert the data into multiple tables using views.if not
how can i insert the data into multiple tables in a single trip
is there any option .Please help me


use a transaction bracketed by BEGIN; and COMMIT;  to execute a series 
of inserts or updates as a single atomic operation.




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

[GENERAL] helo

[beulah prasanthi]

I am using postgres 8.4
using views i retrived some details from multiple tables
can i insert the data into multiple tables using views.if not
how can i insert the data into multiple tables in a single trip
is there any option .Please help me

Re: [GENERAL] more docs on extending postgres in C

[Greg Smith]

Ivan Sergio Borgonovo wrote:

It would be nice to at least a list of functions that could be used
in extension development to avoid reading all the source.
  

psql -E
\df *

This will dump out a list of all the built-in functions in the server.  
It will also show you the query that did so, I get one that looks like this:


SELECT n.nspname as "Schema",
 p.proname as "Name",
 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
 WHEN p.proisagg THEN 'agg'
 WHEN p.proiswindow THEN 'window'
 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 
'trigger'

 ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
 AND n.nspname <> 'pg_catalog'
 AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

If you run that, and maybe filter it down to only look at stuff similar 
to what you're looking for (example:  only show things where the Result 
data type returns 'text' because that's what you need), that will give 
you an idea what functions might be suitable to borrow from.  grep for 
them in the source code, you'll find them only once in the table that 
maps them to actual function names on the code.  Grab that name, grep 
for it, and then you'll be sitting at example code that might be 
interesting to you.


The other thing I'd recommend is surfing the code via 
http://doxygen.postgresql.org/ , along with reading through the modules 
in contrib/ as already suggested (which won't be found by the query 
above because they're optional).


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


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

Re: [GENERAL] Can I use LIKE to achieve the following result

[Ricardo Fuentes]

Hi.
You can return a set of record of that table with a sentence including the
LIKE operator, i.e.

SELECT ID, Item FROM your_table WHERE item LIKE 'Car_';

and you would get this result:


ID | Item
=
0  | Car1
1  | Car2
2  | Car3

Take a look of the documentation in the postgresql documentation for pattern
matching:
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html

greets.
Ricardo Ramirez

2010/1/20 Harry McCarney 

> Something like this will be possible but your data sets are contradictory.
> There is no
> 3  | Cat
> Row in the original table..
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yan Cheng Cheok
> Sent: 20 January 2010 18:23
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Can I use LIKE to achieve the following result
>
> I have the following original table :
>
> ID | Item
> =
> 0  | Car1
> 1  | Car2
> 2  | Car3
> 3  | Cat1
> 4  | Cat2
> 5  | Cat3
>
> I would like to return setof record, with the following :
>
> ID | Item
> =
> 0  | Car1
> 1  | Car2
> 2  | Car3
> 3  | Cat
>
> Is it possible to achieve using LIKE command?
>
> Thanks and Regards
> Yan Cheng CHEOK
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Can I use LIKE to achieve the following result

[Harry McCarney]

Something like this will be possible but your data sets are contradictory.
There is no 
3  | Cat
Row in the original table..

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Yan Cheng Cheok
Sent: 20 January 2010 18:23
To: pgsql-general@postgresql.org
Subject: [GENERAL] Can I use LIKE to achieve the following result

I have the following original table :

ID | Item
=
0  | Car1
1  | Car2
2  | Car3
3  | Cat1
4  | Cat2
5  | Cat3

I would like to return setof record, with the following :

ID | Item
=
0  | Car1
1  | Car2
2  | Car3
3  | Cat

Is it possible to achieve using LIKE command?

Thanks and Regards
Yan Cheng CHEOK


  


-- 
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] Can I use LIKE to achieve the following result

[Yan Cheng Cheok]

I have the following original table :

ID | Item
=
0  | Car1
1  | Car2
2  | Car3
3  | Cat1
4  | Cat2
5  | Cat3

I would like to return setof record, with the following :

ID | Item
=
0  | Car1
1  | Car2
2  | Car3
3  | Cat

Is it possible to achieve using LIKE command?

Thanks and Regards
Yan Cheng CHEOK


  


-- 
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] About partitioning

[Filip Rembiałkowski]

W dniu 20 stycznia 2010 12:01 użytkownik Grzegorz Jaśkiewicz <
gryz...@gmail.com> napisał:

> please use search before asking.
>

please use search before advising to use search; it's not so easy to find.

can you share a link to archived post?



Filip

Re: [GENERAL] Statement level triggers

[Harald Fuchs]

In article <4b5702b9.50...@postnewspapers.com.au>,
Craig Ringer  writes:

>> What'd be the behavior of a (plpgsql) trigger function when called as
>> a statement level trigger?
>> Let's say that a statement will involve more than one row.
>> The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says:
>> 
>> "Statement-level triggers do not currently have any way to examine the
>> individual row(s) modified by the statement."

> It means you don't have NEW or OLD record-variables.

Other databases have NEW and/or OLD pseudo-tables for that.  My
suggestion about implementing that got turned down because, without a
primary key, you can't say which NEW and OLD rows belong to each
other.

Since tables often have a primary key I still think that this would be
an addition making statement-level triggers much more useful than they
are now.


-- 
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] more docs on extending postgres in C

[Ivan Sergio Borgonovo]

On Wed, 20 Jan 2010 17:38:17 +0100
Pavel Stehule  wrote:

> > Is there a reference of all macro and functions?

> no, only source code

It would be nice to at least a list of functions that could be used
in extension development to avoid reading all the source.

Since I'm a new entry in pg C coding and internals it will take me
ages to just find what's worth to know. I'll try to take some notes
while I grasp stuff and publish them somewhere.

I'm still trying to digest:

There are two ways you can build a composite data value (henceforth
a "tuple"): you can build it from an array of Datum values, or from
an array of C strings that can be passed to the input conversion
functions of the tuple's column data types. In either case, you
first need to obtain or construct a TupleDesc descriptor for the
tuple structure. When working with Datums, you pass the TupleDesc to
BlessTupleDesc, and then call heap_form_tuple for each row. When
working with C strings, you pass the TupleDesc to
TupleDescGetAttInMetadata, and then call BuildTupleFromCStrings for
each row. In the case of a function returning a set of tuples, the
setup steps can all be done once during the first call of the
function.

I grep throu contrib and I wasn't able to find anything that really
enlighted me about BlessTupleDesc.

I'll try to see if tomorrow things will look clearer.

thanks

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


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

Re: [GENERAL] About partitioning

[Vincenzo Romano]

2010/1/20 Vincenzo Romano :
> 2010/1/20 Adrian von Bidder :
>> Hi,
>>
>> On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
>>> 2010/1/20 Adrian von Bidder :
>>
>>> > [ creating db partitions on demand ]
>>> >
>>> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
>>> >> In case 1 I need to inspect the catalog with at least a select, while
>>> >> in case 2 I need to trap errors.
>>> >> In my (little) experience trapping errors is slow, so I would go for
>>> >>  option 1.
>>> >
>>> > Trapping/handling the error might be slow, but remember that creating a
>>> > new partition (presumably) doesn't happen often (and creating the
>>> > partition is slow anyway.)
>>
>>> Hmmm ... also trapping would happen for every single line being inserted
>>
>> Why?
>>
>> By "trapping" I mean: reacting to the error if the INSERT statement fails.
>> If the INSERT does not fail, there is no error, so there is no error
>> condition to handle.
>>
>> Compare:
>>
>>  * query server to see if partition exists
>>   (!!! slow: this uses the database server)
>>  * if partition does not exist (this is almost never the case), create it
>>  * insert row
>>
>> Against:
>>
>>  * try inserting (same speed as the final step above)
>>  * if (error)
>>   (this is fast, since it only uses the return value from the insert.  No
>> additional database action)
>>   -> then create partition (this, again, is slow but almost never happens)
>>   -> and then re-try the insert.
>
> "trapping" should have the same meaning as in "38.6.5. Trapping Errors",
> that is the BEGIN...EXCEPTION...END.
> In my case:
>
> BEGIN
>  INSERT INTO a_child_table SELECT NEW.*;
> EXCEPTION
>  WHEN the_table_doesn_t_exist THEN
>    CREATE TABLE a_child_table ...
> END;
>
> "Tip: A block containing an EXCEPTION clause is significantly more
> expensive to enter and exit than
> a block without one. Therefore, don’t use EXCEPTION without need."
>
> So my fear is that having such a trapping block defined at runtime for
> every INSERT
> would yield to a slow implementation.

It looks like Adrian is right and my fear is unfounded.
I have created both implementations, one with test-create-insert and
one with insert-trap-create-insert.
The first implementation shows an average execution time longer than
the second with the worse case of the second one
very close to the worse case of the first one.
This means that that only the EXCEPTION...WHEN sub-block is slow, not
also the BEGIN...EXCEPTION.
I would advise to make this thing clearer in the documentation (tip at
chapter "38.6.5. Trapping Errors").

Thanks again to everyone.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] more docs on extending postgres in C

[Pavel Stehule]

2010/1/20 Ivan Sergio Borgonovo :
> On Wed, 20 Jan 2010 16:56:04 +0100
> Dimitri Fontaine  wrote:
>
>> Ivan Sergio Borgonovo  writes:
>> > I haven't been able to find anything better than the online
>> > manual and pg source code to learn how to write extensions.
>>
>> Maybe this will help:
>>   http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf
>>   http://github.com/dimitri/prefix
>
> Thanks to all.
>
> Is there a reference of all macro and functions?
>

no, only source code

Pavel

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

-- 
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] Is there a messaging system for Postresql

[Dimitri Fontaine]

"Romeliz Valenciano Jr."  writes:
> We're evaluating Postgresql for a possible transition from MS
> SqlServer. We have 100s of MS Sql servers and one way of replicating
> data changes is to use MQ Series Queue management system, some servers
> who were recently changed to Sql 2005 are using Service Broker. We're
> interested if there's an existing messaging system/tool present in
> Postgresql as well.

I don't get what your tools are about, but the keywords do ring some
bells. I'd bet Golconde and PGQ could serve you.

  http://wiki.postgresql.org/wiki/Skytools
  http://code.google.com/p/golconde/

Regards,
-- 
dim

-- 
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] C: extending text search: from where to start

[Dimitri Fontaine]

Ivan Sergio Borgonovo  writes:
> Any other resource that will help me to write my own contrib?

You could try out the following, but it deals a lot with GiST specifics…

  http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf

Regards,
-- 
dim

-- 
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] more docs on extending postgres in C

[Ivan Sergio Borgonovo]

On Wed, 20 Jan 2010 16:56:04 +0100
Dimitri Fontaine  wrote:

> Ivan Sergio Borgonovo  writes:
> > I haven't been able to find anything better than the online
> > manual and pg source code to learn how to write extensions.
> 
> Maybe this will help:
>   http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf
>   http://github.com/dimitri/prefix

Thanks to all.

Is there a reference of all macro and functions?

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


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

[GENERAL] Is there a messaging system for Postresql

[Romeliz Valenciano Jr.]

Hi,

We're evaluating Postgresql for a possible transition from MS SqlServer. We 
have 100s of MS Sql servers and one way of replicating data changes is to use 
MQ Series Queue management system, some servers who were recently changed to 
Sql 2005 are using Service Broker. We're interested if there's an existing 
messaging system/tool present in Postgresql as well.

Thanks


  

Re: [GENERAL] About partitioning

[Vincenzo Romano]

2010/1/20 Adrian von Bidder :
> Hi,
>
> On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
>> 2010/1/20 Adrian von Bidder :
>
>> > [ creating db partitions on demand ]
>> >
>> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
>> >> In case 1 I need to inspect the catalog with at least a select, while
>> >> in case 2 I need to trap errors.
>> >> In my (little) experience trapping errors is slow, so I would go for
>> >>  option 1.
>> >
>> > Trapping/handling the error might be slow, but remember that creating a
>> > new partition (presumably) doesn't happen often (and creating the
>> > partition is slow anyway.)
>
>> Hmmm ... also trapping would happen for every single line being inserted
>
> Why?
>
> By "trapping" I mean: reacting to the error if the INSERT statement fails.
> If the INSERT does not fail, there is no error, so there is no error
> condition to handle.
>
> Compare:
>
>  * query server to see if partition exists
>   (!!! slow: this uses the database server)
>  * if partition does not exist (this is almost never the case), create it
>  * insert row
>
> Against:
>
>  * try inserting (same speed as the final step above)
>  * if (error)
>   (this is fast, since it only uses the return value from the insert.  No
> additional database action)
>   -> then create partition (this, again, is slow but almost never happens)
>   -> and then re-try the insert.

"trapping" should have the same meaning as in "38.6.5. Trapping Errors",
that is the BEGIN...EXCEPTION...END.
In my case:

BEGIN
  INSERT INTO a_child_table SELECT NEW.*;
EXCEPTION
  WHEN the_table_doesn_t_exist THEN
CREATE TABLE a_child_table ...
END;

"Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than
a block without one. Therefore, don’t use EXCEPTION without need."

So my fear is that having such a trapping block defined at runtime for
every INSERT
would yield to a slow implementation.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] About partitioning

[Adrian von Bidder]

Hi,

On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
> 2010/1/20 Adrian von Bidder :

> > [ creating db partitions on demand ]
> >
> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
> >> In case 1 I need to inspect the catalog with at least a select, while
> >> in case 2 I need to trap errors.
> >> In my (little) experience trapping errors is slow, so I would go for
> >>  option 1.
> >
> > Trapping/handling the error might be slow, but remember that creating a
> > new partition (presumably) doesn't happen often (and creating the
> > partition is slow anyway.)

> Hmmm ... also trapping would happen for every single line being inserted

Why?

By "trapping" I mean: reacting to the error if the INSERT statement fails.  
If the INSERT does not fail, there is no error, so there is no error 
condition to handle.

Compare:

 * query server to see if partition exists
   (!!! slow: this uses the database server)
 * if partition does not exist (this is almost never the case), create it
 * insert row

Against:

 * try inserting (same speed as the final step above)
 * if (error)
   (this is fast, since it only uses the return value from the insert.  No 
additional database action)
   -> then create partition (this, again, is slow but almost never happens)
   -> and then re-try the insert.

cheers
-- vbi

-- 
featured link: Debian Bookmark Collection - http://bookmarks.debian.net/


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] function to grant select on all tables in several schemas

[Andreas Kretschmer]

Gerd Koenig  wrote:

> Hello,
> 
> I'm looking for a solution to grant select to a group to have "read-only" 
> group across all tables/views/.. in several schemas. I already found some 
> workarounds and I decided to create a function to cover this topic.
> But calling this function throws the error:
> ""
> ERROR:  column "´r´" does not exist
> LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´...
>  ^
> QUERY:   SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, 
> pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, 
> pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND 
> s.nspname = ´tisys´ order by s.nspname
> CONTEXT:  PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over 
> SELECT rows
> ""
> 
> The function was created by:
> ""
> CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> RETURNS TEXT AS '
> DECLARE
> sql text;
> rel record;
> BEGIN
> FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
>   pg_catalog.quote_ident(t.relname) AS relation_name
> FROM pg_class t, pg_namespace s
> WHERE t.relkind IN (´r´, ´v´,´S´) 
>   AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
> LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' || 
> rel.relation_name || '' TO ro_group'';
> RAISE NOTICE ''%'', sql;
> EXECUTE sql;
> END LOOP;
> RETURN ''OK'';
> END;
> ' LANGUAGE 'plpgsql';
> COMMENT ON FUNCTION grant_select_to_ro_group()
> IS 'Give select privilege ON all relations in the given schema TO ro_group.';
> ""
> 
> ...and has been called by:
> ""
> select grant_select_to_ro_group();
> ""
> 
> any hints appreciated..GERD

You should better use $$ instead of ' for the function-body.
(unless you have a very old pg-version ...)

I think the ´ as quoting-sign is also wrong...


Rewrite your function to:

CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
RETURNS TEXT AS $$
DECLARE
sql text;
rel record;
BEGIN
FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
  pg_catalog.quote_ident(t.relname) AS relation_name
FROM pg_class t, pg_namespace s
WHERE t.relkind IN ('r', 'v','S')
AND t.relnamespace=s.oid AND s.nspname = 'tisys' order by s.nspname
LOOP sql := 'GRANT SELECT ON ' || rel.schema_name || '.' ||
rel.relation_name || ' TO ro_group';
RAISE NOTICE '%', sql;
EXECUTE sql;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE 'plpgsql';


now it works: (i have copy&paste your function into a file and edit it
there)

kretsch...@tux:~$ psql test
Zeitmessung ist an.
psql (8.4.2)
Geben Sie »help« für Hilfe ein.

test=# \i grant.sql
CREATE FUNCTION
Zeit: 239,453 ms
test=*# select grant_select_to_ro_group();
 grant_select_to_ro_group
--
 OK
(1 Zeile)

Zeit: 48,836 ms



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] function to grant select on all tables in several schemas

[Pavel Stehule]

Hello

I am not sure, but maybe .. you are doesn't use correct quotes:

you use ´´, but you have to use ''

Regards
Pavel Stehule


2010/1/20 Gerd Koenig :
> Hello,
>
> I'm looking for a solution to grant select to a group to have "read-only"
> group across all tables/views/.. in several schemas. I already found some
> workarounds and I decided to create a function to cover this topic.
> But calling this function throws the error:
> ""
> ERROR:  column "´r´" does not exist
> LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´...
>                                                             ^
> QUERY:   SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t,
> pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND
> s.nspname = ´tisys´ order by s.nspname
> CONTEXT:  PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over
> SELECT rows
> ""
>
> The function was created by:
> ""
> CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> RETURNS TEXT AS '
> DECLARE
>    sql text;
>    rel record;
> BEGIN
>    FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
>                      pg_catalog.quote_ident(t.relname) AS relation_name
>        FROM pg_class t, pg_namespace s
>        WHERE t.relkind IN (´r´, ´v´,´S´)
>        AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
>    LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' ||
> rel.relation_name || '' TO ro_group'';
>        RAISE NOTICE ''%'', sql;
>        EXECUTE sql;
>    END LOOP;
>    RETURN ''OK'';
> END;
> ' LANGUAGE 'plpgsql';
> COMMENT ON FUNCTION grant_select_to_ro_group()
> IS 'Give select privilege ON all relations in the given schema TO ro_group.';
> ""
>
> ...and has been called by:
> ""
> select grant_select_to_ro_group();
> ""
>
> any hints appreciated..GERD
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [GENERAL] more docs on extending postgres in C

[Dimitri Fontaine]

Ivan Sergio Borgonovo  writes:
> I haven't been able to find anything better than the online manual
> and pg source code to learn how to write extensions.

Maybe this will help:
  http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf
  http://github.com/dimitri/prefix

Regards,
-- 
dim

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

[GENERAL] function to grant select on all tables in several schemas

[Gerd Koenig]

Hello,

I'm looking for a solution to grant select to a group to have "read-only" 
group across all tables/views/.. in several schemas. I already found some 
workarounds and I decided to create a function to cover this topic.
But calling this function throws the error:
""
ERROR:  column "´r´" does not exist
LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´...
 ^
QUERY:   SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, 
pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, 
pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND 
s.nspname = ´tisys´ order by s.nspname
CONTEXT:  PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over 
SELECT rows
""

The function was created by:
""
CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
RETURNS TEXT AS '
DECLARE
sql text;
rel record;
BEGIN
FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
  pg_catalog.quote_ident(t.relname) AS relation_name
FROM pg_class t, pg_namespace s
WHERE t.relkind IN (´r´, ´v´,´S´) 
AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' || 
rel.relation_name || '' TO ro_group'';
RAISE NOTICE ''%'', sql;
EXECUTE sql;
END LOOP;
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';
COMMENT ON FUNCTION grant_select_to_ro_group()
IS 'Give select privilege ON all relations in the given schema TO ro_group.';
""

...and has been called by:
""
select grant_select_to_ro_group();
""

any hints appreciated..GERD


-- 
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] more docs on extending postgres in C

[Pavel Stehule]

2010/1/20 Ivan Sergio Borgonovo :
> I haven't been able to find anything better than the online manual
> and pg source code to learn how to write extensions.
>
> I couldn't find a reference of all the function/macros I could use
> and some more examples on how to use them.

look on contrib directory in pg source code. Every subdir is good example.

Pavel

>
> I'd also appreciate some suggestion about dev environment and best
> practices on Debian, something that could help me to compile,
> install, test easily on Debian.
>



> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] more docs on extending postgres in C

[Pavel Stehule]

Hello

I wrote one article some years ago

http://www.postgres.cz/index.php/Project_of_UDF_and_its_realization_at_C_for_PostgreSQL

The core is correct, but there are some issues, it is for 8.0.

regards
Pavel Stehule

2010/1/20 Ivan Sergio Borgonovo :
> I haven't been able to find anything better than the online manual
> and pg source code to learn how to write extensions.
>
> I couldn't find a reference of all the function/macros I could use
> and some more examples on how to use them.
>
> I'd also appreciate some suggestion about dev environment and best
> practices on Debian, something that could help me to compile,
> install, test easily on Debian.
>
> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] more docs on extending postgres in C

[Vincenzo Romano]

2010/1/20 Ivan Sergio Borgonovo :
> I haven't been able to find anything better than the online manual
> and pg source code to learn how to write extensions.
>
> I couldn't find a reference of all the function/macros I could use
> and some more examples on how to use them.
>
> I'd also appreciate some suggestion about dev environment and best
> practices on Debian, something that could help me to compile,
> install, test easily on Debian.

Try this:
www.joeconway.com/tut_oscon_2004.pdf

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

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

[GENERAL] more docs on extending postgres in C

[Ivan Sergio Borgonovo]

I haven't been able to find anything better than the online manual
and pg source code to learn how to write extensions.

I couldn't find a reference of all the function/macros I could use
and some more examples on how to use them.

I'd also appreciate some suggestion about dev environment and best
practices on Debian, something that could help me to compile,
install, test easily on Debian.

thanks

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


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

Re: [GENERAL] Varidic function (printf) not found within a trigger

[Pavel Stehule]

2010/1/20 Vincenzo Romano :
> Hi all.
>
> Within a trigger function I need to call a variadic function (actually
> the printf found on  http://wiki.postgresql.org/wiki/Sprintf).
> When the trigger is fired I get the following:
>
> function printf(text, timestamp without time zone, timestamp without
> time zone, text) does not exist
> LINE 1: SELECT  printf( $l1$
>                ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
>

you have to do explicit cast from timestamp to text

Pavel


> Is this a bug or am I missing something important?
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>
> --
> 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] Varidic function (printf) not found within a trigger

[Vincenzo Romano]

Hi all.

Within a trigger function I need to call a variadic function (actually
the printf found on  http://wiki.postgresql.org/wiki/Sprintf).
When the trigger is fired I get the following:

function printf(text, timestamp without time zone, timestamp without
time zone, text) does not exist
LINE 1: SELECT  printf( $l1$
^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

Is this a bug or am I missing something important?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] Statement level triggers

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> What'd be the behavior of a (plpgsql) trigger function when called as
> a statement level trigger?
> Let's say that a statement will involve more than one row.
> The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says:
>
> "Statement-level triggers do not currently have any way to examine the
> individual row(s) modied by the statement."

It's not clear what you are asking, but statement-level triggers imply
that the actual data (e.g. row changes) are not relevant (or visible)
to the trigger. Thus, it's more of "something" has changed trigger, rather
than a row-level "this stuff" has changed trigger. For example, Bucardo
uses both kinds: the row-level triggers keep track of what rows have
changed, and statement-level triggers which send a NOTIFY to let the
Bucardo daemon know that the table has changed.

- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201001200828
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAktXBSAACgkQvJuQZxSWSsj3HwCeN/RhPN/Zo7KmKT9gHafDLLAf
ZEQAn0pkS1sX5jb3+ycQIN6wK0OHxgtC
=V/4j
-END PGP SIGNATURE-



-- 
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] Statement level triggers

[Craig Ringer]

Vincenzo Romano wrote:
> Hi all.
> 
> What'd be the behavior of a (plpgsql) trigger function when called as
> a statement level trigger?
> Let's say that a statement will involve more than one row.
> The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says:
> 
> "Statement-level triggers do not currently have any way to examine the
> individual row(s) modified by the statement."

It means you don't have NEW or OLD record-variables. You can still
perform any queries you need to on the table affected and any other
tables of interest.

I find statement-level triggers to be useful mostly for sanity-checking.
I'll have an AFTER statement level trigger verify a certain invariant on
a table - like a table-level CHECK (as opposed to row-level CHECK).

--
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] C: extending text search: from where to start

[Ivan Sergio Borgonovo]

On Tue, 19 Jan 2010 10:12:21 +0100
Dimitri Fontaine  wrote:

> Ivan Sergio Borgonovo  writes:
> > I'd appreciate any pointer that will quickly put me on the right
> > track.
> 
> I'd guess you begin here:
>   http://wiki.postgresql.org/wiki/Developer_FAQ

With the exception of formatting style info I didn't find anything
useful there.

Right now I'm reading:
http://www.postgresql.org/docs/8.3/static/xfunc-c.html
There is just an example on how to build up the data returned but it
uses C string. I couldn't find an example that uses TupleDesc
BlessTupleDesc.

I'm installing deb contrib source package.

Any other resource that will help me to write my own contrib?


thanks

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


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

Re: [GENERAL] About partitioning

[Grzegorz Jaśkiewicz]

I submitted a solution here once in the past, please use search before asking.
the exception handling will be quicker in general, cos you won't have
to test for existence of the table before every insert.

-- 
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] About partitioning

[Vincenzo Romano]

2010/1/20 Adrian von Bidder :
> Hi,
>
> [ creating db partitions on demand ]
>
> On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
>> In case 1 I need to inspect the catalog with at least a select, while
>> in case 2 I need to trap errors.
>> In my (little) experience trapping errors is slow, so I would go for
>>  option 1.
>
> Trapping/handling the error might be slow, but remember that creating a new
> partition (presumably) doesn't happen often (and creating the partition is
> slow anyway.)  In case 1 (check if the partition exists before insert),
> *every* *single* *insert* is slower because you first query the catalog,
> while in case 2, the normal case (insert into existing partition) is fast
> and only the rare case (how is your partitioning?  100 or more rows per
> partition on average?  I'd think it's quite a lot because why else partition
> the data at all...?) is slow.
>
> cheers
> -- vbi

Hmmm ... also trapping would happen for every single line being inserted ...

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] Size of row-metadata?

[Thomas Kellerer]

tmp, 20.01.2010 11:25:

http://www.postgresql.org/docs/8.4/static/storage-page-layout.html


I fail to find the size of the *row* header on that link.


"All table rows are structured in the same way. There is a fixed-size header 
(occupying 23 bytes on most machines), followed by an optional null bitmap, an optional 
object ID field, and the user data. The header is detailed in Table 53-4."

http://www.postgresql.org/docs/8.4/static/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE




--
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] About partitioning

[Adrian von Bidder]

Hi,

[ creating db partitions on demand ]

On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
> In case 1 I need to inspect the catalog with at least a select, while
> in case 2 I need to trap errors.
> In my (little) experience trapping errors is slow, so I would go for
>  option 1.

Trapping/handling the error might be slow, but remember that creating a new 
partition (presumably) doesn't happen often (and creating the partition is 
slow anyway.)  In case 1 (check if the partition exists before insert), 
*every* *single* *insert* is slower because you first query the catalog, 
while in case 2, the normal case (insert into existing partition) is fast 
and only the rare case (how is your partitioning?  100 or more rows per 
partition on average?  I'd think it's quite a lot because why else partition 
the data at all...?) is slow.

cheers
-- vbi


-- 
Udall's Fourth Law:
Any change or reform you make is going to have consequences you
don't like.

-- 
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] Size of row-metadata?

[tmp]

http://www.postgresql.org/docs/8.4/static/storage-page-layout.html


I fail to find the size of the *row* header on that link.

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

[GENERAL] About partitioning

[Vincenzo Romano]

A main issue in my case with the suggested (chapter 5.9)
implementation is that child tables need to be created in advance,
For a number of reasons (complex partitioning schemas, rows also
related to the past and the future) it'd be unpractical
to create all of them in advance.
So I'm thinking about an "on demand" creation. I see two options only:
1. I check the child table existence before inserting the row or
2. I create the missing table as the result of an insert error (no table found).

In case 1 I need to inspect the catalog with at least a select, while
in case 2 I need to trap errors.
In my (little) experience trapping errors is slow, so I would go for option 1.

Unless there is a better advise.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

1

-- 
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] Size of row-metadata?

[A. Kretschmer]

In response to tmp :
> What is the width (in bytes) of the meta data associated with each row 
> in postgresql?

http://www.postgresql.org/docs/8.4/static/storage-page-layout.html

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

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

[GENERAL] Statement level triggers

[Vincenzo Romano]

Hi all.

What'd be the behavior of a (plpgsql) trigger function when called as
a statement level trigger?
Let's say that a statement will involve more than one row.
The documentation (v8.4.2, "35.1. Overview of Trigger Behavior") says:

"Statement-level triggers do not currently have any way to examine the
individual row(s) modified by the statement."

Many thanks.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

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

[GENERAL] Size of row-metadata?

[tmp]

What is the width (in bytes) of the meta data associated with each row 
in 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] Help on constructing a query that matches array

[Dean Rasheed]

2010/1/19 BlackMage :
>
> Hey all,
>
> I need help on constructing a query with arrays that matches the arrays up
> as foriegn keys to another table. For example, say that I have two tables,
> owners and pets.
>
> Owner Table
> owner_id | pet_ids
> 1             |    {1,2,3}
> 2             |    {2,3}
>
> Pet Table
> pet_ids   |   Pet Type
> 1            |  Dog
> 2            |  Cat
> 3            |  Fish
> 4            |  Bird
>
> Basically I am trying to create a SELECT query that returns the type of pets
> an owner has by matching the pet_ids up. Can anyone help me with this?

You can use the built-in unnest() array function (see
http://www.postgresql.org/docs/8.4/static/functions-array.html) to
convert the array to a set of rows which you can then join in the
standard way. For example:

select o.owner_id, o.pet_id, p.pet_type from
(select owner_id, unnest(pet_ids) as pet_id from owner) as o, pet as p
where p.pet_id = o.pet_id and owner_id=1;

Note: the unnest() function is only defined as standard in postgresql
8.4. If you have an older version, you'll need to define it yourself,
as described here:
http://wiki.postgresql.org/wiki/Array_Unnest

Regards,
Dean

-- 
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] Why Stored Procedure is Slower In The Following Case?

[Yan Cheng Cheok]

Thanks. I am able to solve my problem using the following (EXECUTE) :

CREATE OR REPLACE FUNCTION get_measurements(IN bigint, IN text)
  RETURNS TABLE(_type text, _value double precision, _unit text) AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
_type ALIAS FOR $2;
BEGIN
RETURN QUERY EXECUTE 'SELECT measurement_type.value, measurement.value, 
measurement_unit.value
FROM unit, lot, measurement, measurement_unit, measurement_type
WHERE lot_id = fk_lot_id AND fk_unit_id = unit_id AND 
fk_measurement_unit_id = measurement_unit_id AND 
  fk_measurement_type_id = measurement_type_id AND
  lot_id = $1 AND measurement_type.value LIKE $2'
USING _lotID, _type;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION get_measurements(bigint) OWNER TO postgres;

Thanks and Regards
Yan Cheng CHEOK


--- On Wed, 1/20/10, Yan Cheng Cheok  wrote:

> From: Yan Cheng Cheok 
> Subject: Why Stored Procedure is Slower In The Following Case?
> To: pgsql-general@postgresql.org
> Date: Wednesday, January 20, 2010, 3:10 PM
> I have a stored procedure, which
> perform single RETURN QUERY SELECT..., by taking in 2
> function parameters.
> 
> It takes around 7 seconds to complete the operation.
> 
> When I perform outside stored procedure, with exact same
> SELECT statement, it only takes 0.5 seconds, with same
> result being returned.
> 
> Testing using Stored Procedure
> ==
> SELECT * FROM get_measurements(1, 'Pad%');
> 6949.593 ms
> 
> EXPLAIN SELECT * FROM get_measurements(1, 'Pad%');
>                
>                
> QUERY PLAN
> ---
>  Function Scan on get_measurements  (cost=0.00..260.00
> rows=1000 width=72)
> (1 row)
> 
> 
> 
> Testing using SQL statement
> ===
> SemiconductorInspection=# SELECT measurement_type.value,
> measurement.value, measurement_unit.value FROM unit, lot,
> measurement,
>  measurement_unit, measurement_type WHERE lot_id =
> fk_lot_id AND fk_unit_id = unit_id AND
> fk_measurement_unit_id = measurement_
> unit_id AND fk_measurement_type_id = measurement_type_id
> AND lot_id = 1 AND measurement_type.value LIKE 'Pad%';
> 552.234 ms
> 
> SemiconductorInspection=# EXPLAIN SELECT
> measurement_type.value, measurement.value,
> measurement_unit.value FROM unit, lot, meas
> urement, measurement_unit, measurement_type WHERE lot_id =
> fk_lot_id AND fk_unit_id = unit_id AND
> fk_measurement_unit_id = meas
> urement_unit_id AND fk_measurement_type_id =
> measurement_type_id AND lot_id = 1 AND
> measurement_type.value LIKE 'Pad%';
>                
>                
>              
>    QUERY PLAN
> 
>  Nested Loop  (cost=51.33..44328.65 rows=3629
> width=53)
>    ->  Index Scan using pk_lot_id on
> lot  (cost=0.00..8.27 rows=1 width=8)
>          Index Cond: (lot_id
> = 1)
>    ->  Hash Join 
> (cost=51.33..44284.10 rows=3629 width=61)
>          Hash Cond:
> (measurement.fk_measurement_unit_id =
> measurement_unit.measurement_unit_id)
>          ->  Hash
> Join  (cost=13.65..44196.52 rows=3629 width=33)
>            
>    Hash Cond:
> (measurement.fk_measurement_type_id =
> measurement_type.measurement_type_id)
>            
>    ->  Hash Join 
> (cost=11.38..44138.71 rows=5134 width=24)
>                
>      Hash Cond: (measurement.fk_unit_id
> = unit.unit_id)
>                
>      ->  Seq Scan on
> measurement  (cost=0.00..36261.81 rows=2083781
> width=24)
>                
>      ->  Hash 
> (cost=10.08..10.08 rows=104 width=16)
>                
>            -> 
> Index Scan using idx_fk_lot_id on unit 
> (cost=0.00..10.08 rows=104 width=16)
>                
>              
>    Index Cond: (fk_lot_id = 1)
>            
>    ->  Hash  (cost=1.76..1.76
> rows=41 width=17)
>                
>      ->  Seq Scan on
> measurement_type  (cost=0.00..1.76 rows=41 width=17)
>                
>            Filter: (value
> ~~ 'Pad%'::text)
>          -> 
> Hash  (cost=22.30..22.30 rows=1230 width=36)
>            
>    ->  Seq Scan on
> measurement_unit  (cost=0.00..22.30 rows=1230
> width=36)
> (18 rows)
> 
> 
> The content for stored procedure is as follow :
> 
> CREATE OR REPLACE FUNCTION get_measurements(IN bigint, IN
> text)
>   RETURNS TABLE(_type text, _value double precision,
> _unit text) AS
> $BODY$DECLARE
>     _lotID ALIAS FOR $1;
>     _type ALIAS FOR $2;
> BEGIN
>     RETURN QUERY SELECT measurement_type.value,
> measurement.value, measurement_unit.value
>     FROM unit, lot, measurement,
> measurement_unit, measurement_type
>     WHERE lot_id = fk_lot_id AND fk_unit_id =
> unit_id AND fk_measurement_unit_id = measurement_unit_id AND
> 
>           fk_measurement_type_id =
> measurement_type_id AND
>           lot_id = _lotID AND
> measurement_type.value LIKE _type;
> END;$BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>