[GENERAL] How to join to delete

[Chris Velevitch]

I'm using pg 7.4.5 on Win XP SP1.

I want to delete rows from a table B, but need to join it to another
table A in order to select the rows in B to be deleted.

Eg. Delete from A join B on (A.bk = B.bk) where A.ak = x;

but only delete rows from B.

How do I acheive this?


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

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

[GENERAL] Return value (instead of reference) for user defined type

[Don Y]

Hi,

I've successfully built several user types.  But, I'm having
problems with the one I am working on currently.  The server
SIGSEGV's at the end of the _in routine.

Nearest I can tell, the problem is related to my attempt to
return an int by value (all of my other types return
references but this type fits in an int4 so value seems
more economical).

I don't see anything in the documentation that suggests that
I can NOT do this... :-/

Below, snippets of the implementation and associated SQL.

Thanks for any pointers!
--don


typedef int4 foo;

PG_FUNCTION_INFO_VI(foo_in);

Datum
foo_in(PG_FUNCTION_ARGS)
{
  char *string;
  foo value;

  string = PG_GETARG_CSTRING(0);

  ...

  value = 0;
  PG_RETURN_INT32(value);
}

===
DROP TYPE foo;

CREATE TYPE foo;

CREATE FUNCTION foo_in(cstring)
RETURNS foo
AS '$libdir/foo'
LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION foo_out(foo)
RETURNS cstring
AS '$libdir/foo'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE foo (
INTERNALLENGTH = 4,
ALIGNMENT = int4,
STORAGE = plain,
INPUT = foo_in,
OUTPUT = foo_out
);


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

Re: [GENERAL] Return value (instead of reference) for user defined type

[Tom Lane]

Don Y [EMAIL PROTECTED] writes:
 Nearest I can tell, the problem is related to my attempt to
 return an int by value

If that's what you intend, try cluing in CREATE TYPE (see
PASSEDBYVALUE).

regards, tom lane

---(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] How to join to delete

[Noel Faux]

Hi Chris,

I think the best bet would be to use a nested select:

Delete from A WHERE A.bk = (SELECT bk FROM B) where A.ak = x;

Cheers
Noel

Chris Velevitch wrote:

I'm using pg 7.4.5 on Win XP SP1.

I want to delete rows from a table B, but need to join it to another
table A in order to select the rows in B to be deleted.

Eg. Delete from A join B on (A.bk = B.bk) where A.ak = x;

but only delete rows from B.

How do I acheive this?


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

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


begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:[EMAIL PROTECTED]
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard


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

Re: [GENERAL] PG_RETURN_?

[Richard Huxton]

Don Y wrote:

Hi,

I have a set of functions for a data type that return
small integers (i.e. [0..12]).  I can, of course, represent
it as a char, short or long (CHAR, INT16 or INT32).
re there any advantages/drawbacks to chosing one particular
PG_RETURN_ type over another (realizing that they are
effectively just casts)?


If they are integers then an int would be the obvious choice. If you are 
going to treat them as int2 outside the function then int2, otherwise 
just integer. Oh, it's int2/int4 not int16/int32.

--
  Richard Huxton
  Archonet Ltd

---(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] How to join to delete

[Richard Huxton]

Chris Velevitch wrote:

I'm using pg 7.4.5 on Win XP SP1.


The first official release for Windows was 8.0, and there have been 
several bugfixes to 7.4.x since then. Upgrade as soon as is practical.



I want to delete rows from a table B, but need to join it to another
table A in order to select the rows in B to be deleted.

Eg. Delete from A join B on (A.bk = B.bk) where A.ak = x;

but only delete rows from B.


Perhaps something like:
 DELETE FROM B WHERE bk IN (SELECT ak FROM A WHERE ak2 = x)

Or maybe rephrase it as EXISTS for 7.4

--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] How would I write this query...

[Alban Hertroys]

Guy Rouillier wrote:

Jim Fitzgerald wrote:


Hi -

 I have two tables, one of them has names of people and an associated
integer ID.  The other table is a list of the people (from the first
table) by their ID number that have signed up for a class.  How would
I write a query that would list all the people from the first table
that do not have any entries in the second table?   Basically, I want
a listing of all my people who have not signed up for the class.



select * 
from people 
where id not in

(
select id
from class_registration
)


Wouldn't a NOT EXISTS be faster? After all, the current record can be 
disposed of as soon as there's any reference to it from class_registration.


For example:
select *
  from people
 where not exists (
select 1
  from class_registration
 where id = people.id
 );

It may be faster to use * or a specific column name in the subquery 
instead of the constant value 1. EXPLAIN ANALYZE will tell ;)


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(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] How would I write this query...

[Martijn van Oosterhout]

On Mon, May 01, 2006 at 05:23:41PM -0400, John D. Burger wrote:
 In my experience, queries like the OUTER LEFT JOIN version posted 
 earlier are usually much more efficient than NOT IN queries like the 
 above.  The planner seems to be pretty smart about turning (positive) 
 IN queries into joins, but NOT IN queries usually turn into nested 
 table scans, in my experience.

That's because they're not equivalent. IN/NOT IN have special semantics
w.r.t. NULLs that make them a bit more difficult to optimise. OUTER
JOINs on the other hand is easier since in a join condition anything =
NULL evaluates to NULL - FALSE.

I think there's been some discussion about teaching the planner about
columns that cannot be NULL (like primary keys) thus allowing it to
perform this transformation safely. I don't know if anyone has done it
though...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] Alternative for vacuuming queue-like tables

[Csaba Nagy]

  I guess what I'm asking for is a kind of REBUILD TABLE which is not
  MVCC by definition but it would be useful in the mentioned queue table
  case.
 
 
 vaccum full ?

Nope, it won't work, it will still leave in all the dead tuples
potentially visible by old transactions, even if those transactions
didn't touch yet the vacuumed table. In my case that means for some
tables sometimes 1000s times of bloat, given that the table always has
few active rows and high insert/update/delete rates...

CLUSTER is locking the table exclusively, which means it will wait until
all transactions which actually touched the table are finished, and then
rebuild the table, ignoring dead rows, without caring about old
transactions who could still see the dead rows (at least this is how I
think it works based on my observations). Perfect for my purpose, but
not MVCC...

Cheers,
Csaba.



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

[GENERAL] Using a pointer as statetype for an aggregate

[Florian G. Pflug]

Hi

I've now completed my implementation of a collect_distinct aggregate, and
it seems to work. My statetype is basically a pointer to a hashtable (allocated
from the aggcontext). Since using internal as statetype is unsupported,
I use int8, and just cast my pointer back and forth from int8.

It appears to work on both my dev (32bit) and my production (64bit) machine,
and it doesn't crash even when used heavily. Is there some drawback to my
approach? If not - why doesn't postgres allow internal as statetype?

greetings, Florian Pflug

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

[GENERAL] Unnecessary function calls

[Markus Schiltknecht]

Hi,

when using LIMIT, how do I tell the planner to only call a function for
rows it returns?

An example: I want to fetch the top five categories. A function
get_category_text_path(cat_id int) returns the textual representation of
the category. For that I do something like:

SELECT id, get_category_text_path(id)
   FROM category
   ORDER BY rank
   LIMIT 5

Unfortunately this takes very long because it calls
get_category_text_path() for all of the 450'000 categories in the table.
But I only need the full text path of the top five rows.

It does not matter if I declare the function to be IMMUTABLE, STABLE or
VOLATILE - it gets called for every row in category (which normally is
what you want I guess).

How can I rewrite the query to call get_category_text_path() only for
the top five rows?

Thanks for hints.

Markus



---(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] Unnecessary function calls

[Richard Huxton]

Markus Schiltknecht wrote:

Hi,

when using LIMIT, how do I tell the planner to only call a function for
rows it returns?

An example: I want to fetch the top five categories. A function
get_category_text_path(cat_id int) returns the textual representation of
the category. For that I do something like:

SELECT id, get_category_text_path(id)
   FROM category
   ORDER BY rank
   LIMIT 5

Unfortunately this takes very long because it calls
get_category_text_path() for all of the 450'000 categories in the table.
But I only need the full text path of the top five rows.


SELECT id, get_category_text_path(id)
FROM (
SELECT id FROM category ORDER BY rank LIMIT 5
) AS foo

HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Unnecessary function calls

[Martijn van Oosterhout]

On Tue, May 02, 2006 at 01:37:54PM +0200, Markus Schiltknecht wrote:
 Hi,
 
 when using LIMIT, how do I tell the planner to only call a function for
 rows it returns?
 
 An example: I want to fetch the top five categories. A function
 get_category_text_path(cat_id int) returns the textual representation of
 the category. For that I do something like:
 
 SELECT id, get_category_text_path(id)
FROM category
ORDER BY rank
LIMIT 5

How about:

SELECT id, get_category_text_path(id)
FROM (SELECT id FROM category
ORDER BY rank
LIMIT 5) as x;

Evidently you don't have an index on rank, otherwise it would've used
the index to cut down on the number of rows that needed to be examined.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] Unnecessary function calls

[Markus Schiltknecht]

Hello Terry,

Thanks a lot. That's so simple I didn't see it. (The original query is
much more complex.)

The only problem is, rank is not a column of category itself, but a
joined row. With this solution, the join will have to be performed
twice. But since this doesn't cost that much and because the second join
is only done for 5 rows at the max this does not hurt.

The more complete query now looks a little ugly:

SELECT id, get_category_text_path(id), r.rank
FROM category
JOIN rank_lookup AS r ON cat_id = id
WHERE id IN (
SELECT c.id
FROM category AS c
JOIN rank_lookup AS rr ON rr.cat_id = c.id
ORDER BY rr.rank
LIMIT 5
)

It's not possible to optimize out that second join, is it?

Regards

Markus

On Tue, 2006-05-02 at 07:39 -0400, Terry Fielder wrote:
 SELECT id, get_category_text_path(id)
 FROM category
 WHERE id IN (
SELECT c.id
FROM category AS c
ORDER BY c.rank
LIMIT 5
 )



---(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] Unnecessary function calls

[Terry Fielder]

SELECT id, get_category_text_path(id)
FROM category
WHERE id IN (
  SELECT c.id
  FROM category AS c
  ORDER BY c.rank
  LIMIT 5
)

Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Markus Schiltknecht wrote:

Hi,

when using LIMIT, how do I tell the planner to only call a function for
rows it returns?

An example: I want to fetch the top five categories. A function
get_category_text_path(cat_id int) returns the textual representation of
the category. For that I do something like:

SELECT id, get_category_text_path(id)
   FROM category
   ORDER BY rank
   LIMIT 5

Unfortunately this takes very long because it calls
get_category_text_path() for all of the 450'000 categories in the table.
But I only need the full text path of the top five rows.

It does not matter if I declare the function to be IMMUTABLE, STABLE or
VOLATILE - it gets called for every row in category (which normally is
what you want I guess).

How can I rewrite the query to call get_category_text_path() only for
the top five rows?

Thanks for hints.

Markus



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

  


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

Re: [GENERAL] Unnecessary function calls

[Markus Schiltknecht]

On Tue, 2006-05-02 at 14:02 +0200, Martijn van Oosterhout wrote:
 How about:
 
 SELECT id, get_category_text_path(id)
 FROM (SELECT id FROM category
 ORDER BY rank
 LIMIT 5) as x;

Oh that works? Great!

Let me see, with 'rank' from a joined table that looks like:

SELECT id, get_category_text_path(id), rank
FROM (
SELECT c.id
FROM category AS c
JOIN rank_lookup AS r ON r.cat_id = c.id
ORDER BY r.rank
LIMIT 5
) as x;

That works perfectly. It prevents a second join and thus solves the
question in my previous mail.

 Evidently you don't have an index on rank, otherwise it would've used
 the index to cut down on the number of rows that needed to be examined.

No, there is no index. I need to think about creating one...

Thank you very much.

Markus



---(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] Unnecessary function calls

[Terry Fielder]

1) If the join to rank_lookup is done across Pkey and/or unique
indexed fields and a foreign key is defined for said join, I don't know
how the subquery could possibly be more optimized then it is (the
reasoning being beyond the scope of this discussion and possibly even
beyond the scope of area in which I can safely comment :)

2) It is my understanding and experience (I could be unaware of a trick
or special case specifics, however) that using an IN clause is LESS
efficient then joining to the table. The only reason I used the in
clause is because, as you indicated, you were only ask for the top
five, which is a very small set (you probably would not want to do that
if the set was large).
Indeed, the IN clause is a de-optimization, it only HAPPENS to make the
query run faster because it allows you to avoid calling the select
function for all but the selected 5 rows (which was the goal you
requested)

3) In SQL there is almost always more then 1 way of doing something,
you have now seen 2. There may be more, possibly even better ways.
Experts care to comment? :)


Terry Fielder
[EMAIL PROTECTED]
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085


Markus Schiltknecht wrote:

  Hello Terry,

Thanks a lot. That's so simple I didn't see it. (The original query is
much more complex.)

The only problem is, rank is not a column of category itself, but a
joined row. With this solution, the join will have to be performed
twice. But since this doesn't cost that much and because the second join
is only done for 5 rows at the max this does not hurt.

The more complete query now looks a little ugly:

SELECT id, get_category_text_path(id), r.rank
FROM category
JOIN rank_lookup AS r ON cat_id = id
WHERE id IN (
SELECT c.id
FROM category AS c
JOIN rank_lookup AS rr ON rr.cat_id = c.id
ORDER BY rr.rank
LIMIT 5
)

It's not possible to optimize out that second join, is it?

Regards

Markus

On Tue, 2006-05-02 at 07:39 -0400, Terry Fielder wrote:
  
  
SELECT id, get_category_text_path(id)
FROM category
WHERE id IN (
   SELECT c.id
   FROM category AS c
   ORDER BY c.rank
   LIMIT 5
)

  
  


---(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] How to join to delete

[Chris Velevitch]

On 5/2/06, Richard Huxton dev@archonet.com wrote:

Chris Velevitch wrote:
 I'm using pg 7.4.5 on Win XP SP1.

The first official release for Windows was 8.0, and there have been


I'm the cygwin version of pg for testing.


several bugfixes to 7.4.x since then. Upgrade as soon as is practical.


I'm deploying to a shared hosted database and they are currently
supporting 7.4.x.


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

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

[GENERAL] number of page slots needed exceeds max_fsm_pages

[David Brain]

I have a cron job that vacuums one of my dbs daily (Postgres 7.3.11)- 
using 'vacuumdb -a  -f -z -q -U postgres'.  Currently I get an email 
containing the folowing error messages:


NOTICE:  number of page slots needed (38320) exceeds max_fsm_pages (2)
HINT:  Consider increasing the configuration parameter max_fsm_pages 
to a value over 38320.

NOTICE:  number of page slots needed (40912) exceeds max_fsm_pages (2)
...
...

However if I run the command by hand it appears to complete without 
error, any thoughts as to why this might be? The max_fsm_pages has been 
set to 5 in postgresql.conf.


Thanks,

David.


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

Re: [GENERAL] How would I write this query...

[John D. Burger]

Martijn van Oosterhout wrote:


That's because they're not equivalent. IN/NOT IN have special semantics
w.r.t. NULLs that make them a bit more difficult to optimise. OUTER
JOINs on the other hand is easier since in a join condition anything =
NULL evaluates to NULL - FALSE.


Which is why Hash IN Joins were added, presumably.  But there's nothing 
analogous for NOT IN, I guess, perhaps there can't be.



I think there's been some discussion about teaching the planner about
columns that cannot be NULL (like primary keys) thus allowing it to
perform this transformation safely. I don't know if anyone has done it
though...


Yeah, I've noticed cases where I've thought Ah, the planner doesn't 
know that column can't be null.  Similarly, it has seemed to me that 
knowing that a column was UNIQUE could have made for a better plan, 
although I can't think of any examples off-hand.  Maybe where I saw it 
using a Hash aggregate on a unique column, and I thought it could just 
use the index, although that may not make sense either.


- John D. Burger
  MITRE


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

Re: [GENERAL] Using a pointer as statetype for an aggregate

[Tom Lane]

Florian G. Pflug [EMAIL PROTECTED] writes:
 why doesn't postgres allow internal as statetype?

Because it's not a type.  If it were, it certainly wouldn't have the
semantics you seem to hope for (ie, pass by reference type but don't
actually try to copy the bits)

regards, tom lane

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

[GENERAL] convert LO to BYTEA

[Markus Schiltknecht]

Hi,

is there an easy way to convert a large object to a bytea field?

Thanks

Markus



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

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

Re: [GENERAL] Socket command type I unknown

[Joel Richard]

Sorry to spam you guys with my noise, but for the sake of posterity,  
I thought I'd reply with the solution to this problem.


I learned a lot about the PGSQL protocol during this time. So, I  
ended up getting a Ethereal installed on a machine so I could follow  
the protocol and see what exactly was being sent to it. As it turns  
out, a separate piece of software on this server, which uses a single  
database handle to do its work was somehow sending an error message  
from another perl module as a message to the database.


It's a guess that somehow it got its file handles mixed up or  
something along those lines. So the 73 (PgSQL 7.4) or the  
I (PgSQL 7.3) was truly an I since the message going to the server  
was something like:


Invalid blah blah blah

instead of:

QSELECT * FROM TABLE

Mystery solved. I've disabled the offensive code until I can more  
closely investigate in a controlled environment. Give that we didn't  
really need it, turning it off is an adequate solution for a  
production server.


--Joel


On Feb 4, 2006, at 1:16 AM, Tom Lane wrote:


Joel Richard [EMAIL PROTECTED] writes:

... we occasionally get the following message in our apache log
file. I'm looking for information on what it means:



   DBD::Pg::db selectrow_array failed: FATAL:  Socket command type I
unknown


This looks to me like a protocol-level incompatibility: probably the
client code is sending data in a slightly different format than the
server is expecting, or one side or the other is off-by-one about
message lengths, or something like that.  One way or another the
server is receiving an 'I' when it wasn't expecting that.

I'm not aware of any such bugs on the server side in 7.3.4.  What I
suspect is a problem on the DBD::Pg side, where you did not specify
what version you are using ... but if it's recent, it probably thinks
that talking to 7.3.4 is a legacy problem ...

regards, tom lane




Joel Richard
The Richard Group
[EMAIL PROTECTED]
703-584-5802




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

Re: [GENERAL] PG_RETURN_?

[Don Y]

Richard Huxton wrote:

Don Y wrote:

Hi,

I have a set of functions for a data type that return
small integers (i.e. [0..12]).  I can, of course, represent
it as a char, short or long (CHAR, INT16 or INT32).
re there any advantages/drawbacks to chosing one particular
PG_RETURN_ type over another (realizing that they are
effectively just casts)?


If they are integers then an int would be the obvious choice. If you are 
going to treat them as int2 outside the function then int2, otherwise 
just integer.


Yes, I was more interested in what might be going on behind the
scenes inside the server that could bias my choice of WHICH
integer type to use.  E.g., if arguments are marshalled as
byte arrays vs. as Datum arrays, etc.  (I would suspect the
latter).  Since I could use something as small as a char to
represent the values, the choice is more interested in how
OTHER things would be affected...

 Oh, it's int2/int4 not int16/int32.

The *data type* is int2/int4 but the PG_RETURN_? macro is
PG_RETURN_INT16 or PG_RETURN_INT32 -- hence the reason
I referred to them as CHAR, INT16 or INT32 instead of
char, int2 or int4  :

--don


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

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

Re: [GENERAL] PG_RETURN_?

[Richard Huxton]

Don Y wrote:

Richard Huxton wrote:

Don Y wrote:

Hi,

I have a set of functions for a data type that return
small integers (i.e. [0..12]).  I can, of course, represent
it as a char, short or long (CHAR, INT16 or INT32).
re there any advantages/drawbacks to chosing one particular
PG_RETURN_ type over another (realizing that they are
effectively just casts)?


If they are integers then an int would be the obvious choice. If you 
are going to treat them as int2 outside the function then int2, 
otherwise just integer.


Yes, I was more interested in what might be going on behind the
scenes inside the server that could bias my choice of WHICH
integer type to use.  E.g., if arguments are marshalled as
byte arrays vs. as Datum arrays, etc.  (I would suspect the
latter).  Since I could use something as small as a char to
represent the values, the choice is more interested in how
OTHER things would be affected...


I must admit I've never tested, but I strongly suspect any differences 
will be below the level you can accurately measure. Certainly from the 
point of view of 8/16/32 bit integers I'd guess they'd all time the same 
(they should all end up as a Datum). With a 64-bit CPU I'd guess that 
would extend to 64 bits too. Hmm - looking at comments it seems int64 is 
 a reference type regardless of CPU (include/postgres.h)



  Oh, it's int2/int4 not int16/int32.

The *data type* is int2/int4 but the PG_RETURN_? macro is
PG_RETURN_INT16 or PG_RETURN_INT32 -- hence the reason
I referred to them as CHAR, INT16 or INT32 instead of
char, int2 or int4  :


You're quite right. I was thinking from the other side.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] PG_RETURN_?

[Martijn van Oosterhout]

On Tue, May 02, 2006 at 08:43:03AM -0700, Don Y wrote:
 Richard Huxton wrote:
 Don Y wrote:
 Hi,
 
 I have a set of functions for a data type that return
 small integers (i.e. [0..12]).  I can, of course, represent
 it as a char, short or long (CHAR, INT16 or INT32).
 re there any advantages/drawbacks to chosing one particular
 PG_RETURN_ type over another (realizing that they are
 effectively just casts)?
 
 If they are integers then an int would be the obvious choice. If you are 
 going to treat them as int2 outside the function then int2, otherwise 
 just integer.
 
 Yes, I was more interested in what might be going on behind the
 scenes inside the server that could bias my choice of WHICH
 integer type to use.  E.g., if arguments are marshalled as
 byte arrays vs. as Datum arrays, etc.  (I would suspect the
 latter).  Since I could use something as small as a char to
 represent the values, the choice is more interested in how
 OTHER things would be affected...

You should always *always* match the PG_RETURN_* to the declared type
you are returning. anything else will cause problems. PG_RETURN_INT16
means return in a format consistant with a type declared as
pass-by-value two byte width. PostgreSQL does not check that what
you're returning actually matches what you declared.

The type as declared determines the storage required to store it. That
might be a far more useful factor to consider than what it copied
internally which, as has been pointed out, is probably below what you
can measure.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] PG_RETURN_?

[Don Y]

Martijn van Oosterhout wrote:

On Tue, May 02, 2006 at 08:43:03AM -0700, Don Y wrote:

Richard Huxton wrote:

Don Y wrote:

Hi,

I have a set of functions for a data type that return
small integers (i.e. [0..12]).  I can, of course, represent
it as a char, short or long (CHAR, INT16 or INT32).
re there any advantages/drawbacks to chosing one particular
PG_RETURN_ type over another (realizing that they are
effectively just casts)?
If they are integers then an int would be the obvious choice. If you are 
going to treat them as int2 outside the function then int2, otherwise 
just integer.

Yes, I was more interested in what might be going on behind the
scenes inside the server that could bias my choice of WHICH
integer type to use.  E.g., if arguments are marshalled as
byte arrays vs. as Datum arrays, etc.  (I would suspect the
latter).  Since I could use something as small as a char to
represent the values, the choice is more interested in how
OTHER things would be affected...


You should always *always* match the PG_RETURN_* to the declared type
you are returning. anything else will cause problems. PG_RETURN_INT16
means return in a format consistant with a type declared as
pass-by-value two byte width. PostgreSQL does not check that what
you're returning actually matches what you declared.


Yes, but that wasn't the question.

I can PG_RETURN_CHAR(2), PG_RETURN_INT16(2) or PG_RETURN_INT32(2)
and end up with the same result (assuming the function is defined
to return char, int2 or int4, respectively in the SQL interface).


The type as declared determines the storage required to store it. That


Yes, but for a function returning a value that does not exceed
sizeof(Datum), there is no *space* consequence.  I would assume
most modern architectures use 32 bit (and larger) registers.

OTOH, some machines incur a (tiny) penalty for casting char to long.
Returning INT32 *may* be better from that standpoint -- assuming
there is no added offsetting cost marshalling.


might be a far more useful factor to consider than what it copied
internally which, as has been pointed out, is probably below what you
can measure.


Sure.  But, given that the difference ONLY amounts to whether
I type INT32 or INT16 or CHAR in the PG_RETURN_ macro,
an understanding of what is going on inside can contribute
epsilon for or against performance.  I'd be annoyed to have
built dozens of functions ASSUMING INT32 when a *better*
assumption might have been CHAR...  (I'm working in an
embedded environment where spare CPU cycles mean you've
wasted $$$ on hardware that you don't need  :-/ )

--don

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

  http://archives.postgresql.org

[GENERAL] libpq for palm?

[Ottavio Campana]

Is there a libpq for palm os?

That would be great to develop applications.

-- 
Non c'è più forza nella normalità, c'è solo monotonia.



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Unexpected behavior

[Steve V]

On 4/27/06, Strobhen [EMAIL PROTECTED] wrote:

Hey,

I am trying to figure out some unexpected behavior in Postgresql.

When I create a rule that fires on a table after an update, and if
that rule has a SELECT statement in it, it seems to be attempting to
fire (on an empty set) regardless of how the conditional evaluates
after an update.

The result being that if I run an update on a table with such a rule,
instead of getting a message along the lines of UPDATE (# of rows) I
get the column names of the select statement with no rows and the
message row number -1 is out of range 0..-1.

So first off, is having a select statement (I'm actually trying to run
a function) inside a rule that fires on an update considered bad
practice? I could do this through a trigger, but a rule just seems
more natural.


snip


When that rule should never fire (the id hasn't changed). If I change
the conditional of the rule to something that must always be false
(like false, or 1 = 0), it will still behave in this manner.


Does anyone know what's going on here? I'm experiencing an identical
situation, and it doesn't seem logical. If it evaluates to false, why
on earth is the function result set attempting to be returned? Maybe
not a bug, but definitely unexpected behavior

Thanks,
Steve

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

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

Re: [GENERAL] PG_RETURN_?

[Martijn van Oosterhout]

On Tue, May 02, 2006 at 10:06:19AM -0700, Don Y wrote:
 The type as declared determines the storage required to store it. That
 
 Yes, but for a function returning a value that does not exceed
 sizeof(Datum), there is no *space* consequence.  I would assume
 most modern architectures use 32 bit (and larger) registers.

When you return a Datum, it's always the same size. When you're
returning a string, you're still returning a Datum, which may be 4 or 8
bytes depending on the platform.

But what I was referring to was the space to store the data in a tuple
on disk, or to send the data to a client. These are affected by the
choice of representation.

 OTOH, some machines incur a (tiny) penalty for casting char to long.
 Returning INT32 *may* be better from that standpoint -- assuming
 there is no added offsetting cost marshalling.

Within the backend the only representations used are Datum and tuples.
I don't think either of them would have a noticable difference between
various pass-by-value formats.

 ... I'd be annoyed to have
 built dozens of functions ASSUMING INT32 when a *better*
 assumption might have been CHAR...  (I'm working in an
 embedded environment where spare CPU cycles mean you've
 wasted $$$ on hardware that you don't need  :-/ )

Hmm, postgres doesn't try to save on cycles. the philosophy is to get
it right first, then make it fast. The entire fmgr interface is slower
than the original design (old-style functions), but this design works
on all platforms whereas the old one didn't.

I'd go for INT32, it's most likely to be an int which should be the
most natural size for the machine.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] Unexpected behavior

[Martijn van Oosterhout]

On Tue, May 02, 2006 at 12:11:34PM -0500, Steve V wrote:
 When that rule should never fire (the id hasn't changed). If I change
 the conditional of the rule to something that must always be false
 (like false, or 1 = 0), it will still behave in this manner.
 
 Does anyone know what's going on here? I'm experiencing an identical
 situation, and it doesn't seem logical. If it evaluates to false, why
 on earth is the function result set attempting to be returned? Maybe
 not a bug, but definitely unexpected behavior

A rule is a macro. This means that the expression in the rule will
always happen. If you have a rule to do a SELECT after an UPDATE, the
client will see the results of the SELECT.

You almost never want RULEs. It's not clear what you want to do, but a
trigger is more likely to do what you want (as well as a lot easier to
understand).

If you set debug_print_rewritten=on you should be able to see what is
actually being executed...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] Using a pointer as statetype for an aggregate

[Florian G. Pflug]

Tom Lane wrote:
 Florian G. Pflug [EMAIL PROTECTED] writes:
why doesn't postgres allow internal as statetype?

 Because it's not a type.  If it were, it certainly wouldn't have the
 semantics you seem to hope for (ie, pass by reference type but don't
 actually try to copy the bits)

Could I somehow define a type that would fit my needs. Ideally,
it would be
1) pass-by-value
2) 4byte long on 32bit machines
3) 8byte long on 64bit machines
4) Only 0 can be castet to my type, resulting in an all-zero representation
5) Impossible to use as a column type
6) Provide no output function, because the value is meaningless to the user.

I'd think I'd manage to get 4, 6 and maybe 5 working by playing with create 
type.
But I can't see how I could make 1,2 and 3 work. Is an 8byte pass-by-value type
even possible?

I'd realy like to get rid of that ugly casting to int8.. It just seems s 
wrong ;-)

greetings, Florian Pflug

---(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] PG_RETURN_?

[Don Y]

Martijn van Oosterhout wrote:

On Tue, May 02, 2006 at 10:06:19AM -0700, Don Y wrote:

The type as declared determines the storage required to store it. That

Yes, but for a function returning a value that does not exceed
sizeof(Datum), there is no *space* consequence.  I would assume
most modern architectures use 32 bit (and larger) registers.


When you return a Datum, it's always the same size. When you're
returning a string, you're still returning a Datum, which may be 4 or 8
bytes depending on the platform.


Yes.


But what I was referring to was the space to store the data in a tuple
on disk, or to send the data to a client. These are affected by the
choice of representation.


So, as I had mentioned before, you marshall as a *byte* stream
and not a *Datum* stream?


OTOH, some machines incur a (tiny) penalty for casting char to long.
Returning INT32 *may* be better from that standpoint -- assuming
there is no added offsetting cost marshalling.


Within the backend the only representations used are Datum and tuples.
I don't think either of them would have a noticable difference between
various pass-by-value formats.


... I'd be annoyed to have
built dozens of functions ASSUMING INT32 when a *better*
assumption might have been CHAR...  (I'm working in an
embedded environment where spare CPU cycles mean you've
wasted $$$ on hardware that you don't need  :-/ )


Hmm, postgres doesn't try to save on cycles.


grin Yes, I noticed.  :  But it's hard for me to get this
attitude out of the way I approach a problem.  :-(
(e.g., I wouldn't count people at a rally using a *float*!  :)

 the philosophy is to get

it right first, then make it fast. The entire fmgr interface is slower
than the original design (old-style functions), but this design works
on all platforms whereas the old one didn't.


Exactly.  I could more efficiently replace postgres with
dedicated structures to do what I want.  But, that ties my
implementation down to one less portable (and maintainable).


I'd go for INT32, it's most likely to be an int which should be the
most natural size for the machine.


(sigh)  Yes, I suppose so.  Though it can have a big impact
on transport delays (server to client) if things really
are marshalled as byte streams, etc.

shrug  I suppose I should just do it and let technology
catch up with my inefficiencies later!

Thanks!
--don

---(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] Return value (instead of reference) for user defined

[Don Y]

Tom Lane wrote:

Don Y [EMAIL PROTECTED] writes:

Nearest I can tell, the problem is related to my attempt to
return an int by value


If that's what you intend, try cluing in CREATE TYPE (see
PASSEDBYVALUE).


Thanks!  That did the trick!
--don

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

[GENERAL] 8.1.4 anytime soon?

[Matthew T. O'Connor]

Hey all, I was just wondering if there were any plans to get 8.1.4 
release in the near future.  I'm seeing semi-frequent out of memory 
errors that are related to a bugfix that Tom put in post 8.1.3. (Yes I 
know I can compile from source, but I'd rather not do that on my 
production server.)


Thanks,

Matt

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

[GENERAL] Calling V1 function from within the server

[Don Y]

Hi,

If I define:

Datum barcode_checksum(PG_FUNCTION_ARGS)

PG_FUNCTION_INFO_V1(barcode_checksum)

Datum barcode_checksum(PG_FUNCTION_ARGS)
{
  barcode value;
  short result;

  label = (barcode) PG_GETARG_INT32(0);

  // compute barcode

  PG_RETURN_INT16(result);
}

and now want to *use* that function within some other
(related) function, how can I invoke it?  The intuitive
syntax:
  short foo;
  barcode label;
  foo = barcode_checksum(label);
gives compiler warnings (pointer from int without cast)
as well as SIGSEGV's at run time.

The model used for the complex sample data type avoids this
issue by creating an internal function that is used by
other functions -- and *wrapped* in the PG_FUNCTION_INFO_V1
framework under another name (i.e. that name is never used
directly in the rest of the code)

--don

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

[GENERAL] FATAL: database dspace does not exist

[Christo Romberg]

Hi!I have some problems with PostgreSQL v8.1.3.My system is Windows XP Professional Edition.An error occur when I try to connect to the database dspace that I've made. 
It claims that the db does not exist, though it does.
Another error occur when try to create a new database. Then error messages says
that the password is incorrect, though it was correct given by me.
Appreciate the effort!

Best Regards,
Christo 

Re: [GENERAL] Calling V1 function from within the server

[Martijn van Oosterhout]

On Tue, May 02, 2006 at 11:24:34AM -0700, Don Y wrote:
 Hi,
 
 If I define:
 
 Datum barcode_checksum(PG_FUNCTION_ARGS)
 
 PG_FUNCTION_INFO_V1(barcode_checksum)

snip

 and now want to *use* that function within some other
 (related) function, how can I invoke it?  The intuitive

You want DirectFunctionCalln or FunctionCalln as defined in fmgr.h

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   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] 8.1.4 anytime soon?

[Bruno Wolff III]

On Tue, May 02, 2006 at 14:20:03 -0400,
  Matthew T. O'Connor matthew@zeut.net wrote:
 Hey all, I was just wondering if there were any plans to get 8.1.4 
 release in the near future.  I'm seeing semi-frequent out of memory 
 errors that are related to a bugfix that Tom put in post 8.1.3. (Yes I 
 know I can compile from source, but I'd rather not do that on my 
 production server.)

There was some talk a while ago (maybe a month?) about getting a 8.1.4
release out relatively soon. (This was after the partial block write issue
was discovered.), but I haven't seen anything recently.

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

Re: [GENERAL] convert LO to BYTEA

[Jerry LeVan]

Markus,

PgBrowse 1.7a allows saving Large Objects into a file and
the PgBrowse editor also allows inserting Bytea Fields from
a file.

See http://homepage.mac.com/levanj/TclTk

Jerry

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

  http://archives.postgresql.org

[GENERAL] age(datfrozenxid) negative for template0. Is this normal?

[Denis Gasparin]

Today I executed the following query as stated into the Administrator 
Guide to check the XID wraparound problem:


SELECT datname, age(datfrozenxid) FROM pg_database;

All the database report an age of 1 billion except for the template0 
database. This is an extract of the result of the query:


datname |age
-+
template1   | 1073794149
template0   | -686262347

Is it normal that the age for template0 is negative?

The version of the backend is 7.4.6 with pg_autovacuum running.

Please let me know as soon as possible if this is a problem or not...

Thank you,
Denis

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

[GENERAL] shp2pgsql

[gouse shaik]

Hello Friends,I want source code of shp2pgsl.  Please provide link if available.Thank you  Shaik.
	

	
		 
Yahoo! India Answers: Share what you know. Learn something new. Click here

Re: [GENERAL] how can i view deleted records?

[Ian Harding]

There used to be a knob that would allow you to temporarily see
deleted tuples.  Don't know if it's still there.  Sounded kinda
dangerous.

http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php

Also, you could start (now) using PITR so you could simply restore to
the moment before the records you are interested in were deleted.

Good luck,

- Ian

On 4/29/06, Dan Black [EMAIL PROTECTED] wrote:

Hello,  everybody!
How  can  I  view  deleted  records  in  table?

--
Verba volent, scripta manent
Dan Black

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



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

Re: [GENERAL] file I/O in plpgsql

[Jessica M Salmon]

Thanks everyone for their input on this.
-Meghan


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

   http://archives.postgresql.org

Re: [GENERAL] For vim users: Syntax highlighting for PostgreSQL

[brsaweda]

Devrim GUNDUZ wrote:
 Hi,

 I just wrote a pgsql.vim file for vim users. It helps you to colorize
 the file which contains PostgreSQL SQL keywords:
(...)

 - Many of the keywords (let us see if I've missed anything)
 - Comments

 I've used mysql.vim as a template. Thanks to \df, \dT and psql's -E for
 making this process easier. :)

 If you are vim user, please test it and send the possible
 errors/additions to me. I intend to send this file to vim developers,
 too, after the testing.


Thank you! I find this useful and am testing it.
Should feedback go to this thread in the newsgroup or  to your email
account or another channel?

Regards
Erwin


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

[GENERAL] Using the REPLACE command to replace all vowels

[EbGrooveCb]

Is there a way to make seperate replacements in 1 field in one command
in SQL?

I need to remove all vowels (a,e,i,o,u) in a field. How would I go
about that?


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

[GENERAL] charting performance measures with number or records in table

[SunWuKung]

We had a discussion with my friend about whether to use an array or an 
attached table and I was in favor of the attached table while he was 
concerned about the the performance of the select/insert as the number 
or records in the attached table grew and so favored to use an array in 
the parent table.

To persuade him I wanted to see how the time required to select or 
insert records increased as the number of rows in the table grew. I was 
less interested in the actual time as it is very hardware dependent more 
interested in the trend. I tried this with the following table:

CREATE TABLE itemresponse (
  testoccasionid INTEGER NOT NULL, 
  itemorder INTEGER NOT NULL, 
  placeholdertypeid SMALLINT DEFAULT 1 NOT NULL, 
  response_datatype SMALLINT NOT NULL, 
  response TEXT, 
  CONSTRAINT itemresponse_new_idx PRIMARY KEY(testoccasionid, 
itemorder, placeholdertypeid)
) WITHOUT OIDS;

SELECT * FROM itemresponse WHERE testoccasionid=1751
--returns 20 records

I tried this with 10^2, 10^3, 10^4, 10^5, 10^6, 10^7 records in the 
table.
To my surprise neither the time for the select nor the time for the 
insert (1000 additional records) increased measurably.
Can it be real or is it an artefact?


On a more general note I think it would be usefull to make a 
'theoretical' graph to illustrate the behaviour of an index. Probably 
there is already one but I didn't find it.
Say there is a table:

CREATE TABLE test ( 
id INTEGER NOT NULL,
CONSTRAINT id_idx PRIMARY KEY(id)
) WITHOUT OIDS;

and there are 0, 10^1, 10^2, 10^3, 10^4, 10^5, 10^6, 10^7, 10^8, 10^9 
records in it

 - Select id from test Where id=99 - time in whatever unit
 - Insert Into test (id) Values (99) - time in whatever unit
 - Select count(id) from test - time in whatever unit
 - Table size - kb=?
 - Index size - kb=? 
 - omit or add whatever makes/doesn't make sence here (eg. memory 
required to do the select?, time to vacuum?)

and the same thing without an index on the table. I think it would make 
a good addition to the manual.

Its just a thought, let me know what you think.
Balázs

---(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] Socket command type I unknown

[Joel Richard]

Tom (and others who may benefit from this),

I'm sorry for taking so long to reply to this. PostgreSQL  
administration is not my primary task. :) After a client noticed this  
error twice this morning, I spent several hours investigating our  
machine and researching on the web. This email led me in this direction


  http://archives.postgresql.org/pgsql-interfaces/2004-05/msg00011.php

and made me look further into what exactly was going on.

It turns out that we had two versions of libpq installed on the  
server. One was from Debian's package manager which was for version  
7.4.7 and the other was my compiled version for 7.3.X. This weekend I  
upgraded us from 7.3 to 7.4 on both the server and the client. I've  
removed any instances of the 7.3 libraries.


So, now we have a different error happening about as often.

  DBD::Pg::db selectrow_array failed: FATAL:  invalid frontend  
message type 73


I read that 73 is an ASCII value. Surprise 73 is a capital letter I.  
Same error, different method of reporting. DBD::Pg is at version 1.41  
and DBI is 1.46. I'm going to try to bring DBD::Pg up to 1.48 just to  
get on the latest version of everything I can find. At this point, I  
want to say that it's my code that's causing the problem. It's as if  
there's some perl code/query that's creating the error, but a  
subsequent query to the server is what's manifesting the problem and  
causing the entry in the error_log. Am I on the right track here?


I'm pretty sure the error can be traced back to my code. Therefore,  
I've started a global DBI-trace() on the postgres client server. I  
know that's going to be an inordinate amount of data (busy website),  
but sometimes the brute force method is what will work.


I can't have my clients losing faith in my ability to solve their  
problems, so I get to track this down. :)


Thanks,
--Joel


On Feb 4, 2006, at 1:16 AM, Tom Lane wrote:


Joel Richard [EMAIL PROTECTED] writes:

... we occasionally get the following message in our apache log
file. I'm looking for information on what it means:



   DBD::Pg::db selectrow_array failed: FATAL:  Socket command type I
unknown


This looks to me like a protocol-level incompatibility: probably the
client code is sending data in a slightly different format than the
server is expecting, or one side or the other is off-by-one about
message lengths, or something like that.  One way or another the
server is receiving an 'I' when it wasn't expecting that.

I'm not aware of any such bugs on the server side in 7.3.4.  What I
suspect is a problem on the DBD::Pg side, where you did not specify
what version you are using ... but if it's recent, it probably thinks
that talking to 7.3.4 is a legacy problem ...

regards, tom lane


Joel Richard
The Richard Group
[EMAIL PROTECTED]
703-584-5802




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

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

Re: [GENERAL] shp2pgsql

[Michael Fuhr]

On Mon, May 01, 2006 at 03:03:24PM +0100, gouse shaik wrote:
   I want source code of shp2pgsl.
   Please provide link if available.

shp2pgsql is part of PostGIS.

http://www.postgis.org/

-- 
Michael Fuhr

---(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] For vim users: Syntax highlighting for PostgreSQL

[Devrim GUNDUZ]

Hi,

On Mon, 2006-05-01 at 15:15 -0700, [EMAIL PROTECTED] wrote:

 Thank you! I find this useful and am testing it.

Great to hear that. I'm also working on plpgsql.vim file.

 Should feedback go to this thread in the newsgroup or  to your email
 account or another channel? 

I think e-mail to me is fine.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



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

[GENERAL] Recovering a row result

[Luis Alberto Pérez Paz]

Hi everybody!

I'm migratingsome sybase store procedures to postgres functions,
In sybase I can write:
--
create proc algo()
as
if exists (something)
return -900;
else
 SELECT * from tabla
return 0
-

Inside of my procedure I don't use the SELECT result, however I can recover the row result after the procedure execution using db libraries.

I don't know if in postgres exists something like this, does anybody can help me?

thanks in advance!


Luis A. Perez Paz
-- paz, amor y comprensión(1967-1994) 

Re: [GENERAL] Use of ISpell dictionaries with tsearch2 - what is

[Oleg Bartunov]

Don,

let me to answer to your original question 
Use of ISpell dictionaries with tsearch2 - what is the point?.


The purpose of any dictionaries in search engines is to help people
to search words not bothering about different forms (declension, inflexion,...).
Dictionaries could be used to process query as well as in the indexing. 
You may store original form of a word and/or it's stem. Most complete index

stores both variants and could provide exact search, but at index's size cost.
Historically, since tsearch2 was based on gist storage, which is quite 
sensitive to the number of unique words, so we store only stems. This might

be changed in future, since now we could use inverted index with tsearch2.

ISpell dictionary is a (open-source) way  to find word's stem(s), their
quality is very different for different languages. We use russian ispell
dictionary and found it rather useful. Of course, since real language is
much complex than ispell rules, there are errors, which produce noise
in search results.  Ispell dictionary could return several normal forms
for one word, for example, booking has two infinitives - booking and book.

Ispell dictionary support many ways of word building, but are difficult to
build and support. That's why various stemming algorithms become popular,
read http://snowball.tartarus.org/texts/introduction.html for good introduction.
We chose snowball stemmer since it's open-source and written/supported by
well-known Martin Porter.

For each lexeme class there is a configurable dictionary queue (in 
pg_ts_cfgmap).
Lexeme  passes through this queue until it recognized by some dictionary
(currently, there is no possibility to recognize lexeme and pass it to the 
next dictionary). It's tenable to begin from very specific dictionary 
(topic related, synonym), and finish queue with most common dictionary like 
'simple' or 'stemmer', which recognize everything :)


Specific configuration is very depends on the language, availability of
good dictionaries and the goals of search engine.
Snowball stemmer works good for english language, 
since word formation is mostly suffix-oriented (I might be wrong here !),

so having not good ispell dictionary, one could use just snowball stemmer.
On the other side, for russian language we have good ispell dictionary,
which is actively developed and supported, and russian word building is 
quite complex, so we definitely recommend to use ispell dictionary before

snowball stemmer.

It's quite difficult to index mix of several languages which share common
characters, since there is no possibility to recognize language. I'd
definitely warn you against using stemmer except at the very end of queue,
since it recognizes everything and no dictionaries after it will be utilized.
Hopefully, any useful text shoud have only one main language. If, for example,
the main language is French and second one - English, I'd use
French Ispell, English Ispell, French stemmer.

Oleg
On Mon, 1 May 2006, Don Walker wrote:


Are you saying that the English ISpell dictionary isn't particularly useful
for English text if you're using the English stemmer? One of the concerns
that I had about the use of ISpell on English text was that ISpell could
provide two or more alternatives for a single search term that would
increase the number of unique words and hurt performance. The examples I saw
all would have been reduced to a single stem by the English stemmer.

If I have to deal with a mix of English and French would using a French
ISpell dictionary followed by an English stemmer be the best approach? If
I'm wrong about the use of English ISpell, then what would be the best
sequence, e.g. French ISpell, English ISpell, English stemmer?

-Original Message-
From: Teodor Sigaev [mailto:[EMAIL PROTECTED]
Sent: May 1, 2006 10:31
To: Don Walker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Use of ISpell dictionaries with tsearch2 - what is
the point?



1. If I am correct about this then what is the point of using the
ISpell dictionary in the first place?


Yes. The main goal of any dictionaries is a 'normalize' lexeme, ie to
get a infinitive. It's very important for languages with variable word's
form such as french, russian, norwegian etc. So, if dictionaries are
used, user don't think about exact form of word for searching.

There is two basic approaches for dictionaries: stemming and vocabulary
based. First one tries to remove variable end of word, in tsearch2 it's
a snowball dictionaries. Second is ispell - it tries to find word in
vocabulary with some grammar changes.



2. Is there a solution for correcting spelling mistakes in the
documents you index? I have seen the readme files for pg_trgm,
http://www.sai.msu.su/~megera/postgres/gist/, which would allow me to
suggest other terms for a query if the misspellings were common
enough. I'd rather fix the problem at index time so that querying with
the proper term would find any misspelled terms (within reason).

Re: [GENERAL] age(datfrozenxid) negative for template0. Is this normal?

[Tom Lane]

Denis Gasparin [EMAIL PROTECTED] writes:
 Is it normal that the age for template0 is negative?

template0 doesn't require vacuuming, so it doesn't matter what age it
shows.

regards, tom lane

---(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] Using the REPLACE command to replace all vowels

[Tony Wasson]

On 1 May 2006 13:16:15 -0700, EbGrooveCb [EMAIL PROTECTED] wrote:

Is there a way to make seperate replacements in 1 field in one command
in SQL?

I need to remove all vowels (a,e,i,o,u) in a field. How would I go
about that?



You can do all the replacements in a single SQL statement by nesting
the REPLACE statements. This will just strip those letters.

SELECT
 REPLACE(
 REPLACE(
 REPLACE(
 REPLACE(
 REPLACE(
   'Your string or field here.'
 ,'a','')
 ,'e','')
 ,'i','')
 ,'o','')
 ,'u','')
;

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

Re: [GENERAL] For vim users: Syntax highlighting for PostgreSQL

[Fernan Aguero]

+[ Devrim GUNDUZ [EMAIL PROTECTED] (27.Apr.2006 23:39):
|
| Hi,
| 
| I just wrote a pgsql.vim file for vim users. It helps you to colorize
| the file which contains PostgreSQL SQL keywords:
| 
|   http://www.gunduz.org/postgresql/pgsql.vim

Devrim,

thanks for contributing this!

| In order to use this file, first edit filetype.vim file and add 
| 
|  PgSQL
| au BufNewFile,BufRead *.pgsql   setf pgsql
|
| to the relevant part. We will need to do this until this file becomes an
| official part of vim.

This is not necessary. You can add it to your ~/.vimrc and
it will work. If you want to set this system wide you can
include the above command inthe system wide vimrc (in my
FreeBSD box this is /usr/local/share/vim/vimrc)

| Then put pgsql.vim to the syntax directory of vim
| (/usr/share/vim/vim64/syntax on my Fedora Core 5). After you rename your
| sql file as filename.pgsql, the syntax highlighting will be enabled.
|
+]

Again, you can also put it in ~/.vim/syntax/ and it will
work.

Just wanted to add that without have root access anyone
can still benefit from your work!

Thanks again!

Fernan

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

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

Re: [GENERAL] Locking confusion

[Tom Lane]

David J N Begley [EMAIL PROTECTED] writes:
 My apologies for what may be an obvious answer to others but the documentation
 (for v8.1) leaves me concerned (enough not to trust expected behaviour of
 some commands).

You're confusing table-level locks with row-level locks.  They are
entirely independent beasts.  The names of the table-level lock modes
are rather unfortunately chosen, I think.

regards, tom lane

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

Re: [GENERAL] Calling V1 function from within the server

[Don Y]

Martijn van Oosterhout wrote:

On Tue, May 02, 2006 at 11:24:34AM -0700, Don Y wrote:

Hi,

If I define:

Datum barcode_checksum(PG_FUNCTION_ARGS)

PG_FUNCTION_INFO_V1(barcode_checksum)


snip


and now want to *use* that function within some other
(related) function, how can I invoke it?  The intuitive


You want DirectFunctionCalln or FunctionCalln as defined in fmgr.h


Yikes!  I *never* would have found that!  :-(
Thanks!
--don

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

[GENERAL] Replicating PostgreSQL DB to lightweight system

[pgdb]

Hi,

like to know if I can replicate a PG db to another
lightweight platform like SQLite? The objective is to "push"
updates to remote devices(laptops, PDAs etc) from a central
server, so that the data can be used offline. These devices are
not expected to modify any data, accessing them thru' the
small-footprint db system. Would be glad to hearif there
are examples of existing commercial/OSS products to serve the
purpose. Appreciate any advice. Thanks.

Regards
pgdb

Free POP3 Email from www.gawab.com
Sign up NOW and get your account @gawab.com!!

Re: [GENERAL] Replicating PostgreSQL DB to lightweight system

[Chris]

pgdb wrote:

Hi,
 
like to know if I can replicate a PG db to another lightweight platform 
like SQLite? The objective is to push updates to remote 
devices(laptops, PDAs etc) from a central server, so that the data can 
be used offline. These devices are not expected to modify any data, 
accessing them thru' the small-footprint db system. Would be glad to 
hear if there are examples of existing commercial/OSS products to serve 
the purpose. Appreciate any advice. Thanks.


I doubt you'll find any tools to replicate between database systems.. 
however using pg_dump as a starting point you might be able to create 
your own sqlite database and then replicate/copy that around.


You might have issues with date/time fields (don't know how sqlite 
handles these) amongst other things, so you might need a script of some 
sort to do some conversions.


--
Postgresql  php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Replicating PostgreSQL DB to lightweight system

[David Fetter]

On Wed, May 03, 2006 at 02:31:19AM +, pgdb wrote:
 Hi,

 like to know if I can replicate a PG db to another lightweight
 platform like SQLite?  The objective is to push updates to remote
 devices(laptops, PDAs etc) from a central server, so that the data
 can be used offline.  These devices  are not expected to modify any
 data, accessing them thru' the small-footprint db system.


If they're big enough, you could use Slony and PostgreSQL on the
devices, which has the nice property of disallowing any attempts at
writing.

 Would be glad to hear if there are examples of existing
 commercial/OSS products to serve the purpose.

If you're not using PostgreSQL for the smaller systems, you might be
able to rig something up with pg_dump using the --inserts option.  The
tough part will probably be getting the schema translated into SQLite
(or whatever system you choose) idiom.

You might also consider DBI-Link, but I'm pretty sure that's not a fit
for this case.

 Appreciate any advice.  Thanks.

In future, please post in plain text, as many of us have trouble
reading HTML ;)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter

Remember to vote!

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

Re: [GENERAL] Replicating PostgreSQL DB to lightweight system

[pgdb]

David Fetter writes: 
David Fetter writes: 
 On Wed, May 03, 2006 at 02:31:19AM +, pgdb wrote: 
 Hi, 
 
 like to know if I can replicate a PG db to another lightweight 
 platform like SQLite? The objective is to push updates to remote 
 devices(laptops, PDAs etc) from a central server, so that the data 
 can be used offline. These devices are not expected to modify any 
 data, accessing them thru' the small-footprint db system. 
 
 
 If they're big enough, you could use Slony and PostgreSQL on the 
 devices, which has the nice property of disallowing any attempts at 
 writing. 

Agreed. I have been using Slony, it's definitely feasible with
all-PGSQL.

 
 Would be glad to hear if there are examples of existing 
 commercial/OSS products to serve the purpose. 
 
 If you're not using PostgreSQL for the smaller systems, you might be 
 able to rig something up with pg_dump using the --inserts option. The 
 tough part will probably be getting the schema translated into SQLite 
 (or whatever system you choose) idiom.
 
 You might also consider DBI-Link, but I'm pretty sure that's not a fit 
 for this case. 

Of course you're sure, you developed it :)

 
 Appreciate any advice. Thanks. 
 
 In future, please post in plain text, as many of us have trouble 
 reading HTML ;) 

Apologies to all :) It's not the first time, guess the problem
could be with Gawab. I will subscribe with another provider if
there's no solution.

 
 Cheers, 
 D 
 -- 
 David Fetter http://fetter.org/ 
 phone: +1 415 235 3778 AIM: dfetter666 
 Skype: davidfetter 
 
 Remember to vote! 
 
 ---(end of broadcast)--- 
 TIP 5: don't forget to increase your free space map settings
-
Free POP3 Email from www.Gawab.com 
Sign up NOW and get your account @gawab.com!!

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