Re: [GENERAL] Why overlaps is not working

[Alban Hertroys]

Jim Nasby wrote:
> I know that numeric supports +/- infinity; I don't remember off-hand if
> timestamps have that as well.

timestamps do, but dates don't.

-- 
Alban Hertroys
[EMAIL PROTECTED]

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

   http://archives.postgresql.org/

[GENERAL] sequence help

[Matthew Peter]

I'll jump right in with an example

create sequence foo start with 1;

create view foobar as select *, nextval('foo') from bar;

The problem is I need a nextval()-like method that returns the iterations 
without
updating the foo sequence. Therefore, maintaining the sequences original value 
by
starting at 1 each time the foobar VIEW is invoked. 

This is obviously a simplified example for an larger query dependent on row 
order
integrity in a view.

On http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html I did 
not
find any way to avoid updating sequences (albeit they are supposed to update by
design and may need to update in order to pull the next in sequence). Nor was I 
able
to maintain iteration in a nextval()-like function. Any ideas?

Matt


 

Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.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

[GENERAL] Problem with unique key

[Greg Peters]

*Hello all, *
**
*I have a small problem I hope somebody can help with.*
**
*I have a table with the following definition:*


CSG=# \d admin_field_list
 Table "public.admin_field_list"
  Column|Type |
Modifiers
-+-+
key | bigint  | not null default
nextval('admin_field_list_key_seq'::regclass)
field   | character varying(25)   | not null
added_by| character varying(25)   | not null
add_date| timestamp without time zone | not null default now()
mod_date| timestamp without time zone |
modified_by | character varying(25)   |
Indexes:
   "admin_field_list_pkey" PRIMARY KEY, btree ("key")
   "admin_field_list_field_key" UNIQUE, btree (field)

CSG=#

*As you can see, I have a primary key called "key", an autoincrementing
integer as per the usual. I also have a unique constraint set for the
"field" column because I don't want there to be duplicate entries in this
column.*

*This is the data currently in the table:*

**
CSG=# select * from admin_field_list;
key |  field   |  added_by   |add_date | mod_date |
modified_by
-+--+-+-+--+-
  1 | Talinga  | Greg Peters | 2006-08-28 22:14:53.593 |  |
  2 | Spring Gully | Greg Peters | 2006-08-28 22:14:53.609 |  |
  3 | Fairview | Greg Peters | 2006-08-28 22:14:53.609 |  |
  4 | Argyle   | Greg Peters | 2006-08-28 22:14:53.609 |  |
  5 | Berwyndale South | Greg Peters | 2006-08-28 22:14:53.609 |  |
  6 | Peat | Greg Peters | 2006-08-28 22:14:53.625 |  |
  7 | Not Applicable   | Greg Peters | 2006-08-28 22:14:53.625 |  |
  8 | Unknown  | Greg Peters | 2006-08-28 22:14:53.625 |  |
(8 rows)

CSG=#

*Now, as you can see, there is no field called "Kogan North", but when I
enter the command:*
**


CSG=# insert into admin_field_list (field, added_by) values ('Kogan North',
'Greg Peters');

*I get:*

ERROR:  duplicate key violates unique constraint "admin_field_list_pkey"
CSG=#

*I don't understand what is going on. It seems that it can't increment the
primary key properly, or for some reason it's trying to assign an incorrect
value to the key column. If i change the command to include a value for the
key column (in this case the number 9), it seems to work. I've tried
dumping/restoring the DB and also tried it on v8.1 and v8.2 with no
success. Any ideas?*

Kind Regards,

Greg Peters.




* *

* *

* *

* *

Re: [GENERAL] Problem with unique key

[Tomas Vondra]

ERROR:  duplicate key violates unique constraint "admin_field_list_pkey"
CSG=#

/I don't understand what is going on. It seems that it can't increment 
the primary key properly, or for some reason it's trying to assign an 
incorrect value to the key column. If i change the command to include a 
value for the key column (in this case the number 9), it seems to work. 
I've tried dumping/restoring the DB and also tried it on v8.1 and v8.2 
with no success. Any ideas?/


Kind Regards,

Greg Peters.


The problem is in the primary key constraint, not the unique one. I 
guess you have inserted the previous rows with 'key' value specified 
directly just like in:


=# insert into admin_field_list (key, field, added_by) values (1, 
'Talinga', 'Greg Peters');


Thus the sequence is not set to the correct value and generates values 
from 1 ... and these are already in the table. Try this


=# SELECT setval('admin_field_list_key_seq', (SELECT MAX(key) FROM
admin_field_list));

and then the insert again.

Tomas

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

Re: [GENERAL] Problem with unique key

[Richard Huxton]

Greg Peters wrote:

CSG=# \d admin_field_list
 Table "public.admin_field_list"
  Column|Type |
Modifiers
-+-+ 


key | bigint  | not null default
nextval('admin_field_list_key_seq'::regclass)



*Now, as you can see, there is no field called "Kogan North", but when I
enter the command:*



CSG=# insert into admin_field_list (field, added_by) values ('Kogan North',
'Greg Peters');



ERROR:  duplicate key violates unique constraint "admin_field_list_pkey"


It's not complaining about "field" but about "key" - look at the 
constraint name. I'm guessing you've added rows with manual values for 
the "key" column. The sequence doesn't know about these, so is 
generating values already in use.


You can use setval() to update the sequence's value.

--
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] Problem with unique key

[Greg Peters]

Thanks all,

with your help I figured out what happened. You are all correct in that the
sequence is out of sync with the "key" value. I dumped the db and then
selectively restored it from the sql file some time ago by cutting and
pasting directly to the command prompt. I must have left out the commands
that tell it what the current value of the sequence is. Problen solved.

Many thanks,
Greg.

Re: [GENERAL] sequence help

[Alban Hertroys]

Matthew Peter wrote:
> I'll jump right in with an example
> 
> create sequence foo start with 1;
> 
> create view foobar as select *, nextval('foo') from bar;
> 
> The problem is I need a nextval()-like method that returns the iterations 
> without
> updating the foo sequence. Therefore, maintaining the sequences original 
> value by
> starting at 1 each time the foobar VIEW is invoked. 

And what would you expect to see if more than one person simultaneously
queries foobar? A sequence would increment for each session querying it.

I think you want numbers to increase per session, in which case you
could probably use generate_series().

Regards,
-- 
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 3: Have you checked our extensive FAQ?

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

Re: [GENERAL] sequence help

[Ragnar]

On þri, 2006-11-28 at 01:12 -0800, Matthew Peter wrote:
> I'll jump right in with an example
> 
> create sequence foo start with 1;
> 
> create view foobar as select *, nextval('foo') from bar;
> 
> The problem is I need a nextval()-like method that returns the iterations 
> without
> updating the foo sequence. Therefore, maintaining the sequences original 
> value by
> starting at 1 each time the foobar VIEW is invoked.

sounds like you do not really want sequences. (think of 
concurrent selects from such a view)

the most natural way to do this is in the application
layer, but if you have to do this in the backend, you
could do it with a pl/pgsql user defined set returning
function, I guess.

gnari



---(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] A generic trigger to log chanes on database tables

[George Weaver]

- Original Message - 
From: "Charles Ambrose" <[EMAIL PROTECTED]>


Is there a way that I could create a trigger that logs the changes 
(updates, deletes) of a table? I mean, I want to put in a table the 
changes to any table in a database and also put in the table the  column 
that was modified and the corresponding value.


Charles,

You may want to check out the following (may also give you addtional ideas 
for what you're trying to achieve):


http://www.varlena.com/GeneralBits/104.php
(Logging Audit Changes with Composite Typed Columns).

Regards,
George



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

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

Re: [GENERAL] Question about encoding

[Albe Laurenz]

> Is it possible to configure PostgreSQL so that a " LIKE 'a' " query
> will match a 'á' value, ie, make it accent-insensitive ?

Maybe something like this can help you:

test=> select to_ascii(convert('tête-à-tête français', 'LATIN9'),'LATIN9');
   to_ascii   
--
 tete-a-tete francais
(1 row)

If your database encoding is already LATIN9, you can omit the convert().

Yours,
Laurenz Albe

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

   http://archives.postgresql.org/

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Olexandr Melnyk]

2006/11/27, Tony Caduto <[EMAIL PROTECTED]>:

One could also use Mono 1.2 and any .net 1.1 IDE such as Borland
Turbo C# or Delphi.net (with npgsql .net data provider) both of which
you can get for free here:
http://www.turboexplorer.com
or SharpDevelop which you can get here: http://www.icsharpcode.com


Mono/.NET is definately worth the consideration. However, I'd advice you not
to go with Turbo C#, as it only supports .NET 1.*, so you won't be able to
use such goodies as: generics and nullable types, which are quite handy,
especially for the database-oriented applications.

But I'm not sure about .NET 2.0 support of the full-featured Delphi.NET.


Sure you could use Wxwidgets with Python or whatever, but you
development time is going to be more than twice as long as compared to > a

application built with Delphi.


I have also tried MonoDevelop, but it has a way to go before being
really useful.


Initial development phase is really short with Delphi/VCL, I agree. But when
project evolves, many problems appear, which show how odd VCL is in this or
that place. The same, but to a lesser extent, applies to Windows.Forms.

I suggest going with some mature GUI platform, such as Gtk. It has bindings
for .NET and is included in standard Mono download. With it you will get all
benefits of layout-based GUI toolkit, like: you won't have to write extra
code for rendering forms correctly when they are internationalised.

So my advice goes towards Mono/Gtk#. There is a bunch of programming
languages for Mono/.NET to choose from, so choosing one of them mostly
depends on your taste.

-
Olexandr Melnyk,
http://omelnyk.net/

Re: [GENERAL] FK pointing to a VIEW

[Olexandr Melnyk]

Looks like I've missed your mail, so a late reply.


2006/11/11, Lars Heidieker <[EMAIL PROTECTED]>:


> > While I agree in principle that such a thing should be
> > able to be done, it simply isn't possible. (in PostgreSQL, you can't
> > even add an index to a view, which a unique constraint would depend
> > on).
>
> Agreed on that.
>
> But such an extension would require a view to be more than just
> SELECT.

This would mean something like an index spreading over more then one
table in the end, or did I miss something ?


Yes. But that is hardly implementable.

--
Olexandr Melnyk,
http://omelnyk.net/

Re: [GENERAL] vacuum: out of memory error

[Jakub Ouhrabka]

Thanks for the responses!

One thing I've forgotten: it's not reproducible. I can issue vacuum 
command manually without any problems few minutes/seconds after seeing 
the error message "out of memory" in the server log.


I also can't find any corrupted rows manually.

And for the listen/notify problem - it narrowed down to be our software bug.

So I've got "vacuum: out of memory" in server log from time to time and 
no other symptoms.


> That can also be caused by setting maintenance_work_mem too high for
> what your hardware is capable of, though I agree that given the other 
> problems it's likely that there is some kind of corruption.


maintenance_work_mem = 256000

There are 4G of RAM and 4G swap.

There's always:

ERROR:  out of memory
DETAIL:  Failed on request of size 262143996

256000 (work_mem in kb) * 1024 = 262144000

What is the cause of the error? Continuous block of this size can't be 
allocated?


So maybe there's no corruption - what do you think?

Regards,

Kuba

Andrew Sullivan napsal(a):

On Fri, Nov 24, 2006 at 11:59:16AM +0100, Jakub Ouhrabka wrote:
I've done little research in mailing list archives and I found possible 
cause: table corruption caused by flaky hardware. Does it sound about 
right? Are there any other possible causes?


It sounds about right, yes; but the other possible cause is a
software bug.  In the absence of data proving you have no hardware
problems, though, I think you'll find that people are singularly
unwilling to investigate software bugs in this case.

What can be corrupted? 


Anything.

How can I check it? 


You can try stepping through the table in question and seeing if you
run into problems anywhere.  By binary search, you should be able to
narrow it pretty quickly.


How can I correct it?


Well, the corrupt rows are lost.  The usual method is "restore from
backup".

What 
are possible consequences of this corruption? 


You can't read the data.  But you already knew that: it's why your
vacuum is blowing up.

A



---(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 increace nightly backup speed

[Andrus]

> You might try using online backups. By following the steps in this
> document:
>
> http://www.postgresql.org/docs/8.1/static/backup-online.html
>
> you can back up the data at the speed of your filesystem. There's no way
> to make it faster than that.

PITR config is complicated. A lot of manual steps are required.
Requires  postgresql.conf file changing, server re-start, creating PITR 
directory and adding postgres user privilege to it, file copying.
PITR recovery is also complicated.

My goal is to create ERP system which creates backups without any 
attendance.
I don'nt know how to automate this 100% and havent found any such sample.

So I'm waiting when this is automated like 8.2 automates user privilege 
dropping and use pg_dump in current installations.

Andrus. 



---(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 increace nightly backup speed

[Andrus]

> If you've got excess CPU capacity at night, I wonder if -Z1 or -Z2
> would speed the backup since it reduces the amount of data written
> to disk.

Where to find study which pg_dump compares backup speed and backup size by 
using various -Z options ?

I'm wondering by -Z9 increases backup speed.
Info-zip zip.exe  -9  config option does not decrease compiression speed

I expected that pg_dump uses same complression algorithm as zip.exe

I'm really wondering why -Z9 decreases backup speed significantly.

Andrus. 



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

   http://archives.postgresql.org/

Re: [GENERAL] IN clause

[Martijn van Oosterhout]

On Mon, Nov 27, 2006 at 05:24:31PM -0600, Jim Nasby wrote:
> In any case, like Oracle, PostgreSQL does not index NULL values (at  
> least not in btree).

Actually, PostgreSQL does store NULL values in an index, otherwise you
could never use them for full index scans (think multicolumn indexes).
You can't use the index for IS NULL tests, although patches exist for
that.

Have a nice day,
-- 
Martijn van Oosterhout  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] NULLs ;-)

[John D. Burger]

Scott Ribe wrote:

where a <> b or (a is null and b is not null) or (a is not null and  
b is null)


In the absence of IS DISTINCT FROM, I think this has the same semantics:

  where coalesce(a, b) <> coalesce(b, a)

although it's not as concise as one might wish.

- John D. Burger
  MITRE

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

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

Re: [GENERAL] How to increace nightly backup speed

[Michael Nolan]

On 11/28/06, Andrus <[EMAIL PROTECTED]> wrote:



My goal is to create ERP system which creates backups without any
attendance.
I don'nt know how to automate this 100% and havent found any such sample.

Depending on what you plan to do with the backups (like create a fallover

server), I don't know that you'll find a fully automated solution without
going to very expensive high end products like Oracle, and quite possibly
not even then.

I have a three part approach to backups, all implemented via cron (on a
Linux server):

1.  On a daily basis I back up the key tables using pgdump.

2.  On a weekly basis I also backup the two 'productiion' databases using
pgdump.

3.  Also on a weekly basis, I do a full backup (dumpall) of the entire
PostgreSQL database.

The weekly backup of the larger of the two databases produces a file that is
about 20GB and takes about an hour and 15 minutes.  I then compress it down
to about 4 GB, which takes another hour. However, because that's a separate
task, it doesn't impact the database server as much.  (I suspect all that
I/O slows things down a bit, but I haven't noticed any significant effect in
my transaction time reports. That task is run during the slowest 4 hour
period of the week, though).

A 'restore' of that database on a different server takes somewhere between 4
and 6 hours.

BTW, if you've never actually tested your recovery capabilities, can you be
sure they work?
I did a full-blown test in February or March and found a few loose ends.
And when we had to do the real thing in May (due to a power supply failure),
there were STILL a few loose ends, but we were back online within 12 hours
of when I started the recovery process, and half of that time was spent
completing the setup of the 'backup' server, which I had been rebuilding.
I'm working to lower that downtime and will be doing another full-blown test
in January or February.

Re: [GENERAL] vacuum: out of memory error

[Vivek Khera]

On Nov 28, 2006, at 8:40 AM, Jakub Ouhrabka wrote:


There are 4G of RAM and 4G swap.


and what is the per-process resource limit imposed by your OS?

Just because your box has that much RAM doesn't mean your process is  
allowed to use it.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] NULLs ;-)

[Ragnar]

On þri, 2006-11-28 at 09:42 -0500, John D. Burger wrote:
> Scott Ribe wrote:
> 
> > where a <> b or (a is null and b is not null) or (a is not null and  
> > b is null)
> 
> In the absence of IS DISTINCT FROM, I think this has the same semantics:
> 
>where coalesce(a, b) <> coalesce(b, a)

sorry, but no.

test=# create table logic (a int, b int);
CREATE TABLE
test=# insert into logic values (null,null);
INSERT 34495399 1
test=# insert into logic values (null,1);
INSERT 34495400 1
test=# insert into logic values (1,null);
INSERT 34495401 1
test=# insert into logic values (1,1);
INSERT 34495402 1
test=# select a,b,
  coalesce(a, b) <> coalesce(b, a) as coal,
  a IS DISTINCT FROM b as dist from logic;
 a | b | coal | dist
---+---+--+--
   |   |  | f
   | 1 | f| t
 1 |   | f| t
 1 | 1 | f| f
(4 rows)

test=#


gnari



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

[GENERAL] Datafiles binary portable?

[Stephen Harris]

This is probably a silly question, but are the database files binary portable?
eg could I take datafiles from a Sparc and copy them to an Intel machine,
or would the endianness differences kill me?

I expect the answer to be "funny man!  Of course not!" for reasons of speed
(native interger handling being quicker than forcing network-endianness,
for example) but you never know...

How about cross OS (eg from Linux Intel to Windows XP, or from Solaris Intel
to Linux Intel)?

(I almost expected this to be a FAQ, but I didn't see it there).

-- 

rgds
Stephen

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

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

Re: [GENERAL] How to implement backup protocol

[Andrus]

> The weekly backup of the larger of the two databases produces a file that 
> is about 20GB and takes about an hour and 15 minutes.  I then compress it 
> down to about 4 GB, which takes another hour. However, because that's a 
> separate task, it doesn't impact the database server as much.  (I suspect 
> all that I/O slows things down a bit, but I haven't noticed any 
> significant effect in my transaction time reports. That task is run during 
> the slowest 4 hour period of the week, though).

My environment is a bit different. For safety, I need to create backups to 
separate computer over over internet.

1. Backup computer has client category internet connection (ADSL, 600 KBit/s 
download speed).

2.  Query "SELECT sum( relpages * 8/1000)  FROM pg_class" returns 1302  for 
a database restored from backup.
So my data size seems to be approx 1 GB only.

3. Backup file size is 70 M

4. Backup client has all ports closed.

5. Server has *only* 5432 port open.

So I think that 4.5 hours which requires to create backup is because pg_dump 
download the whole database  (1 GB) in uncompressed format over slow 
internet connection.
Compression level does not affect to this almost at all.

I think I can create backup copy fast in server computer but how to send it 
to backup computer?

pg_read_file() can read only text files and is restricted only to 
superusers.

How to add a function pg_read_backup()  to Postgres which creates and 
returns backup file with download speed ?
This problably requires implementing some file download protocol.


> BTW, if you've never actually tested your recovery capabilities, can you 
> be sure they work?
> I did a full-blown test in February or March and found a few loose ends. 
> And when we had to do the real thing in May (due to a power supply 
> failure), there were STILL a few loose ends, but we were back online 
> within 12 hours of when I started the recovery process, and half of that 
> time was spent completing the setup of the 'backup' server, which I had 
> been rebuilding.  I'm working to lower that downtime and will be doing 
> another full-blown test in January or February.

I expect that full database backup created using pd_dump does not have never 
have any problems on restore.

Andrus. 



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

Re: [GENERAL] AIX and getaddrinfo

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> Bill Kurland <[EMAIL PROTECTED]> writes:
>> I did a google search on AIX + getaddrinfo and found
>> http://lists.samba.org/archive/rsync/2002-April/002063.html
>> In that context the author says that adding the port number in 
>> etc/services solved his problem with getaddrinfo.
>
> Interesting.  I wonder whether IBM thinks that there is some
> security-related reason for only allowing programs to bind to port
> numbers that are listed in /etc/services?
>
>> So I tried that and, 
>> lo, it has some effect, though I'm not sure it's 100% desirable. The log 
>> entry is:
>
>> LOG:  could not bind IPv6 socket: The type of socket is not supported in 
>> this protocol family.
>> HINT:  Is another postmaster already running on port 5432? If not, wait 
>> a few seconds and retry.
>
> I think this is OK.  There are many machines on which the userspace
> code supports IPv6 while the kernel doesn't, or vice versa.  It looks
> to me like getaddrinfo returned both IPv4 and IPv6 translations of
> "localhost", but the kernel rejected the IPv6 version when PG tried it.
> Since you evidently have a working IPv4 port, there's nothing to worry
> about.  If it really bugs you, the /etc/netsvc.conf change suggested in
> our FAQ_AIX would probably suppress the log message.
>
> Can anyone else confirm the behavior of getaddrinfo wanting port 5432
> to be listed in /etc/services?  If this is real, we ought to have
> something about it in FAQ_AIX.

That doesn't make a lot of sense to me...

It is fair to say that AIX does include a PostgreSQL entry, by
default:

[EMAIL PROTECTED]:/opt/home/pgorg $ grep 5432 /etc/services 
postgresql  5432/tcp# PostgreSQL Database
postgresql  5432/udp# PostgreSQL Database

[We didn't add these entries ourselves; they were there by default.]

But most of our database backends run on ports other than 5432, and I
haven't noticed anything that seems to tie to that.  We have had
difficulties with getaddrinfo(), but I don't think there's anything
that hasn't either been resolved in modern PG releases or listed in
FAQ_AIX.
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/linux.html
Rules of the Evil Overlord #131. "I will never place the key to a cell
just out of a prisoner's reach." 

---(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] Development of cross-platform GUI for Open Source DBs

[Tony Caduto]

Thomas Kellerer wrote:

On 27.11.2006 17:36 Tony Caduto wrote:
The closest to Delphi in a cross platform system is NetBeans and even 
with their form designer it's still tedious working with databases 
compared to Delphi.  
What about Lazarus? It claims to be cross-platform, but I don't know 
how it compares with regards to the data-aware controls.


Thomas


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


Yes,
Lazarus is not bad and very very similar to Delphi.  It does have issues 
though.
It could be a good choice if you don't mind working around it's bugs. It 
is getting better all the time.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Tony Caduto]

Olexandr Melnyk wrote:



Mono/.NET is definately worth the consideration. However, I'd advice 
you not to go with Turbo C#, as it only supports .NET 1.*, so you 
won't be able to use such goodies as: generics and nullable types, 
which are quite handy, especially for the database-oriented applications.




Initial development phase is really short with Delphi/VCL, I agree. 
But when project evolves, many problems appear, which show how odd VCL 
is in this or that place. The same, but to a lesser extent, applies to 
Windows.Forms.



Mono 1.2 only fully supports .net 1.1 (for winforms)  so the Turbo C# 
would be a good choice as would Delphi.net. 
You would have to stick to the standard winforms controls, but you can 
create a very rich application with just the stock controls.


What kind of problems have you seen with the VCL as a project matures?   
I have never run into any issues and have several mature/large projects.


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] FK pointing to a VIEW

[Lars Heidieker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 28 Nov 2006, at 13:33, Olexandr Melnyk wrote:



Looks like I've missed your mail, so a late reply.

2006/11/11, Lars Heidieker <[EMAIL PROTECTED]>:

> > > While I agree in principle that such a thing should be
> > > able to be done, it simply isn't possible. (in PostgreSQL,  
you can't
> > > even add an index to a view, which a unique constraint would  
depend

> > > on).
> >
> > Agreed on that.
> >
> > But such an extension would require a view to be more than just
> > SELECT.
>
> This would mean something like an index spreading over more then one
> table in the end, or did I miss something ?

Yes. But that is hardly implementable.



I think so too, propagating the changes in one of the views  
underlying tables will be really hard,
as than the index of the view must be maintained as well as the  
change to the view might cause

cascading...
While otherwise a view is simply a view, I don't know in how far this  
can be done by something like

a materialized view (I think Oracle and DB2 etc have those)

- --

Viele Grüße,
Lars Heidieker

[EMAIL PROTECTED]
http://paradoxon.info

- 

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
 -- Friedrich Nietzsche



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFbFcIDAkIK9aNPuIRAoS/AJ9rvEwzTJrMkGAJ0PWUFFo/ftBCEACcCENd
nG0yYwita4L3nr4Tg0IJ7oU=
=kMo/
-END PGP SIGNATURE-

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

[GENERAL] Very minor "configure" issue?

[Stephen Harris]

I didn't see this mentioned in the INSTALL or doc/ directory, so...

I have versions of SSL libraries in my own directories and so used
a command line such as:
   ./configure --prefix=/local/apps/postgres/8.2.rc1.0 \
   --exec-prefix=/local/apps/postgres/8.2.rc1.0/linux \
   --enable-thread-safety \
   --with-openssl \
   --with-pam \
   --with-includes=/opt/mystuff/include \
   --with-libs=/opt/mystuff/lib

This fails at

checking test program... failed
configure: error:
Could not execute a simple test program.  This may be a problem
related to locating shared libraries.  Check the file 'config.log'
for the exact reason.

and the log says:
configure:17444: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing 
-D_GNU_SOURCE  -I/opt/mystuff/include   -L/opt/mystuff/lib conftest.c -lpam 
-lssl -lcrypto -lz -lreadline -ltermcap -lcrypt -ldl -lm  >&5
configure:17447: $? = 0
configure:17449: ./conftest
./conftest: error while loading shared libraries: libssl.so.0.9.7: cannot open 
shared object file: No such file or directory

The solution, obviously, is LDFLAGS=-Wl,-R/opt/mystuff/lib ./configure
and now everything configures and builds cleanly, but it might be nice
for that to be automatic.

*shrug*

-- 

rgds
Stephen

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

Re: [GENERAL] How to increace nightly backup speed

[Andrus]

> $ ssh 81.50.12.18 "pg_dump -Z0 -Fc -ibv -U myuser mydb" | gzip -9

Alexander,

1. My database size seems to be appox 1 GB and download speed is approx 600 
kb/s.  Your solution requires 4.5 hours download time
since 1 GB of data must be downloaded.

2. I have only 5432 port open to public internet on server used by Postgres. 
Your solution requires ssh port to be opened also but sysadmins refuce to 
configure routers for second port.

How to use your solution in those cases ?

Andrus. 



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

Re: [GENERAL] Datafiles binary portable?

[Richard Huxton]

Stephen Harris wrote:

This is probably a silly question, but are the database files binary portable?


No


eg could I take datafiles from a Sparc and copy them to an Intel machine,
or would the endianness differences kill me?


Yes


How about cross OS (eg from Linux Intel to Windows XP, or from Solaris Intel
to Linux Intel)?


Maybe. Wouldn't count on it though.

In fact the data files can be incompatible because of different 
compile-time flags (64-bit integer dates on/off for example).

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] How to implement backup protocol

[Karsten Hilbert]

On Tue, Nov 28, 2006 at 06:01:43PM +0200, Andrus wrote:

> 5. Server has *only* 5432 port open.
>
> pg_read_file() can read only text files and is restricted only to 
> superusers.
> 
> How to add a function pg_read_backup()  to Postgres which creates and 
> returns backup file with download speed ?

You could use an *un*trusted procedural language to create a
function to binary-read the backup from disk and return it
as a bytea field. Not sure how efficient that is, though.

You could then simply do

 select get_backup();

If you allow for parameters you could make it return certain
backups based on, perhaps, timestamp of creation.

 select list_available_backups();

might complete the suite of tools.

One could then always use some hashing tools (mhash with PG
bindings comes to mind) to verify whether a backup has arrived safely:

on local machine: ripemd160(backupfile)

 select yhash.ripemd160(get_backup()) = ;

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] How to implement backup protocol

[Richard Huxton]

Andrus wrote:
So I think that 4.5 hours which requires to create backup is because pg_dump 
download the whole database  (1 GB) in uncompressed format over slow 
internet connection.

Compression level does not affect to this almost at all.


Might well be latency issues too.

I think I can create backup copy fast in server computer but how to send it 
to backup computer?


Use scp. Open port 22 and allow only connections from the backup machine 
with a specified user (e.g. "pgbackup").


Alternatively, you might try dumping in a text-format and using rsync to 
 transfer changes.


pg_read_file() can read only text files and is restricted only to 
superusers.


How to add a function pg_read_backup()  to Postgres which creates and 
returns backup file with download speed ?

This problably requires implementing some file download protocol.


Just don't try and do it within PG - use the tools the system provides.

I expect that full database backup created using pd_dump does not have never 
have any problems on restore.


It's entirely possible to cause problems by restoring as the wrong user 
or with a different character-set / locale.


--
  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 increace nightly backup speed

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 03:29:44PM +0200, Andrus wrote:
> > If you've got excess CPU capacity at night, I wonder if -Z1 or -Z2
> > would speed the backup since it reduces the amount of data written
> > to disk.
> 
> Where to find study which pg_dump compares backup speed and backup size by 
> using various -Z options ?

pg_dump uses zlib, you should be able to find statistic on that.

> I'm wondering by -Z9 increases backup speed.
> Info-zip zip.exe  -9  config option does not decrease compiression speed

I don't beleive that. The whole point of increasing the compression
level is that it spends longer on the data to compress it better. It
you could compress it better in the same time, you'd just do it and not
make it optional.

> I'm really wondering why -Z9 decreases backup speed significantly.

Level 9 is the absolute maximum compression level, it tries really hard
to make the data small, but it's usually not that much better than level
6.

If you're transferring over a 100Mb network, I wouldn't recommend going
higher than level 3. It is not unheard of that increasing the
compression makes the process take longer, because you're saturating
the CPU while your network is idle.

Also, if you have a multicore system, it may be worth having pg_dump
not compress but piping the output through gzip, then you can use both
processors simultaneously.

Have a nice day,
-- 
Martijn van Oosterhout  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] Datafiles binary portable?

[Tom Lane]

Stephen Harris <[EMAIL PROTECTED]> writes:
> This is probably a silly question, but are the database files binary portable?

No.

regards, tom lane

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

Re: [GENERAL] Very minor "configure" issue?

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 11:11:26AM -0500, Stephen Harris wrote:
> The solution, obviously, is LDFLAGS=-Wl,-R/opt/mystuff/lib ./configure
> and now everything configures and builds cleanly, but it might be nice
> for that to be automatic.

RPATH is evil. If you're going to install libraries in non-standard
paths, at least include those paths in the system-wide library search
path or add them to LD_LIBRARY_PATH.

You could even symlink the libs into /usr/lib, that would also solve
the problem.

Have a nice day,
-- 
Martijn van Oosterhout  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] FK pointing to a VIEW

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 03:33:54PM +0200, Olexandr Melnyk wrote:
> >This would mean something like an index spreading over more then one
> >table in the end, or did I miss something ?
> 
> Yes. But that is hardly implementable.

Actually, an index over multiple tables is not really the hard part.
It's setting it up so you don't cause deadlocks that's tricky. And what
people really want is *unique* indexes over multiple tables, but there
the locking considerations are even worse.

My gut feeling is that it actually won't be that bad once someone hits
on the right idea and codes it up, but I've been known to be wrong
before.

Have a nice day,
-- 
Martijn van Oosterhout  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] Datafiles binary portable?

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 11:00:35AM -0500, Stephen Harris wrote:
> This is probably a silly question, but are the database files binary portable?
> eg could I take datafiles from a Sparc and copy them to an Intel machine,
> or would the endianness differences kill me?

No.

It may not even be compatable across the same platform with different
compilers and/or configure flags.

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


signature.asc
Description: Digital signature

[GENERAL] where are wombats?

[wombat]

Can someone tell me whether Wombats live only in Australia,
or also on other continents?

Apart from zoos, of course.

We all know that Wombats are to be found in Zoos.

274


-- 
Posted via a free Usenet account from http://www.teranews.com


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

[GENERAL] are wombats good?

[wombat]

I'm not really sure whether Wombats are any good.

The picture on Wikipedia looks like a horrid little Pig.

Quite Obscene.

Just not English.

274


-- 
Posted via a free Usenet account from http://www.teranews.com


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

   http://archives.postgresql.org/

Re: [GENERAL] [Windows] Pg_Restore on 8.1.5 and so on...

[Terry Yapt]

Terry Yapt wrote:

> I am trying to restore from a pg_dump.  Pg_restore is doing some
> strange behaviour.
> 
> If I open a CMD shell console and execute pg_restore, nothing is
> showed.  If I try to do a "with sense" pg_restore, nothing is showed
> in spite of I have put --verbose option.
> 
> This is my complete command:
> 
> pg_restore --verbose --file=c:\dbt.dump --host=127.0.0.1
> --username=masteruser --password
> 
> Somebody with the same behaviour ?.  I am running 8.1.5 on a Windows
> XP sp2 workstation.
> 
> Thanks in advance.


Same behaviour on 8.2 rc1.


-- 


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

[GENERAL] [Windows] Pg_Restore on 8.1.5 and so on...

[Terry Yapt]

I am trying to restore from a pg_dump.  Pg_restore is doing some
strange behaviour.

If I open a CMD shell console and execute pg_restore, nothing is
showed.  If I try to do a "with sense" pg_restore, nothing is showed in
spite of I have put --verbose option.

This is my complete command:

pg_restore --verbose --file=c:\dbt.dump --host=127.0.0.1
--username=masteruser --password

Somebody with the same behaviour ?.  I am running 8.1.5 on a Windows XP
sp2 workstation.

Thanks in advance.

-- 


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

Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?

[Daniel Serodio]

"Joshua D. Drake" wrote:



>
> That's it, in a nut shell. There is no argument there. That is why you
> don't use artificial keys. That said... pretty much every table I create
> will have an artificial key... because it makes managing data easy. An
> example (to reuse the simple example):
>
> users
> =
> id serial unique,
> first_name text,
> last_name text,
> primary key (first_name,last_name)

Why not

users
=
id serial primary key,
first_name text,
last_name text,
unique (first_name, last_name)

? This way, you can use the "id" as foreign key (more efficient),
allows you to "UPDATE first_name" if needed, and if you find out you
need to add another Joshua Drake to the DB, all you need is to drop the
unique constraint?


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

Re: [GENERAL] AIX and getaddrinfo

[Bill Kurland]

Chris Browne wrote:

[EMAIL PROTECTED] (Tom Lane) writes:
  

Bill Kurland <[EMAIL PROTECTED]> writes:


I did a google search on AIX + getaddrinfo and found
http://lists.samba.org/archive/rsync/2002-April/002063.html
In that context the author says that adding the port number in 
etc/services solved his problem with getaddrinfo.
  

Interesting.  I wonder whether IBM thinks that there is some
security-related reason for only allowing programs to bind to port
numbers that are listed in /etc/services?


So I tried that and, 
lo, it has some effect, though I'm not sure it's 100% desirable. The log 
entry is:
  
LOG:  could not bind IPv6 socket: The type of socket is not supported in 
this protocol family.
HINT:  Is another postmaster already running on port 5432? If not, wait 
a few seconds and retry.
  

I think this is OK.  There are many machines on which the userspace
code supports IPv6 while the kernel doesn't, or vice versa.  It looks
to me like getaddrinfo returned both IPv4 and IPv6 translations of
"localhost", but the kernel rejected the IPv6 version when PG tried it.
Since you evidently have a working IPv4 port, there's nothing to worry
about.  If it really bugs you, the /etc/netsvc.conf change suggested in
our FAQ_AIX would probably suppress the log message.

Can anyone else confirm the behavior of getaddrinfo wanting port 5432
to be listed in /etc/services?  If this is real, we ought to have
something about it in FAQ_AIX.



That doesn't make a lot of sense to me...

It is fair to say that AIX does include a PostgreSQL entry, by
default:

[EMAIL PROTECTED]:/opt/home/pgorg $ grep 5432 /etc/services 
postgresql  5432/tcp# PostgreSQL Database

postgresql  5432/udp# PostgreSQL Database

[We didn't add these entries ourselves; they were there by default.]
  
Odd. I checked 4 different AIX 4.3 machines and none of them have 
postgresql entries. I don't have

any AIX 5 boxes. Is that what you are running?

But most of our database backends run on ports other than 5432, and I
haven't noticed anything that seems to tie to that.  We have had
difficulties with getaddrinfo(), but I don't think there's anything
that hasn't either been resolved in modern PG releases or listed in
FAQ_AIX.
  
I just reread the FAQ_AIX doc for 8.1.5 and I couldn't find any 
reference to getaddrinfo. What difficulties

have you seen and under which AIX ?

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

  http://archives.postgresql.org/

Re: [GENERAL] Only MONO/WinForms is a way to go

[Andrus]

>So my advice goes towards Mono/Gtk#. There is a bunch of programming 
>languages for Mono/.NET to choose from, so choosing one of them mostly 
>depends on your taste.


Here is some information missing from this thread:

1. Gtk# does not support data binding required for database application. It 
even does not have grid control, uses treeview to emulate it.
WinForms controls have native data binding support.

2. Database table Grid requires a lot of memory management (alloc,free, 
compacting).

This is performed faster when managed code in C# is used   rather in 
plain-pointer C++
You cannot image that you re-implement MONO memoy manager in C++ 
application, don't you?

3. wxGrid does not allow to use up and down arrows to move previous/next 
rows when data is edited in grid cells.  You cannot use arrows to move 
prev/next row in pgAdmin when edititng data.
Second (in order of created code) pgAdmin developer, Andres hates wxGrid.

4. wxGrid does not support virtual grid. Whole dataset is rendered in memory 
alwas.
.NET DataGridView has native virtual grid support. It can read new data from 
Postgres database only when page down key is pressed.

5. Java is not LGPL and does not support Generic at bytecode level
Mono libraries are LGPL and it supports Generic at bytecode level.

6. Nowadays Microsoft controls Delphi and forces it to stop running in 
Linux. Kylix has no upgrades.


Conclusion:

Only MONO/WinForms  is a way to go in any serious application.


Andrus. 



---(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] Only MONO/WinForms is a way to go

[Tomi N/A]

2006/11/28, Andrus <[EMAIL PROTECTED]>:


Only MONO/WinForms  is a way to go in any serious application.


Mono needs to show a lot more than beagle and f-spot to be even
considered interesting, let alone a platform to base industrial
strength applications on.
As long as that doesn't radically change for the better (possibly
never, but then again maybe as soon as a year or two), .net will still
be a golden cage, and "multiplatform .net" an oxymoron. That's if you
don't mind all the ethical mumbo-jumbo (easier to dismiss it when you
call it a name like that) of dealing with a company like MS and
securing plentiful licence sales for them through your product.

Cheers,
t.n.a.

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

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

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Olexandr Melnyk]

2006/11/28, Tony Caduto <[EMAIL PROTECTED]>:

Mono 1.2 only fully supports .net 1.1 (for winforms)


Yes. But it already supports most of the .NET 2.0 features (not talking of
WinForms here) including the ones metioned above and has a C# 2.0 compiler.


What kind of problems have you seen with the VCL as a project matures?


As I've already mentioned, it's not layout-based. Anchors only partially
solve the task, so problems can still appear when adding extra languages
interfaces to the application. Also, VCL is quite incomplete. Say, how much
sense does it make for button not to have color property, while other
components, like edit, label and check box have it? There are similar
situations with other objects, when it seems odd, why it doesn't have a
certain feature/property.


Olexandr Melnyk,
http://omelnyk.net/

Re: [GENERAL] Only MONO/WinForms is a way to go

[Joshua D. Drake]

On Tue, 2006-11-28 at 17:31 +, Tomi N/A wrote:
> 2006/11/28, Andrus <[EMAIL PROTECTED]>:
> 
> > Only MONO/WinForms  is a way to go in any serious application.
> 
> Mono needs to show a lot more than beagle and f-spot to be even
> considered interesting, let alone a platform to base industrial
> strength applications on.

Not to mention that Mono is largely developed (controlled) by Novell..

*cough*

> As long as that doesn't radically change for the better (possibly
> never, but then again maybe as soon as a year or two), .net will still
> be a golden cage, and "multiplatform .net" an oxymoron. That's if you
> don't mind all the ethical mumbo-jumbo (easier to dismiss it when you
> call it a name like that) of dealing with a company like MS and
> securing plentiful licence sales for them through your product.
> 
> Cheers,
> t.n.a.
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] Only MONO/WinForms is a way to go

[Joshua D. Drake]

> 
> Conclusion:
> 
> Only MONO/WinForms  is a way to go in any serious application.

Py/QT? Py/GTK?

Joshua D. Drake


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

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] How to implement backup protocol

[Andrus]

> You could use an *un*trusted procedural language to create a
> function to binary-read the backup from disk and return it
> as a bytea field. Not sure how efficient that is, though.
>
> You could then simply do
>
> select get_backup();
>
> If you allow for parameters you could make it return certain
> backups based on, perhaps, timestamp of creation.


Karsten,

This id good idea but it forces to use Postgres protocol for downloading.
This protocol has some timeouts which are too small for large file download.
Postgres protocol has also a lot of overhead added to downloadable data.
It also requires that whole downloadable file must fit into memory.

I used ODBC.
I tried this but was forced to store big files in 1 MB chunks in bytea 
fields and create file from downloaded blocks

So I'm looking a more efficient protocols to download backup file.

Or should I really write code which divides backup file to 1 MB chunks and 
stores them in bytea field ?

Andrus. 



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

Re: [GENERAL] How to implement backup protocol

[Andrus]

Richard,

> Use scp. Open port 22 and allow only connections from the backup machine 
> with a specified user (e.g. "pgbackup").
>
> Alternatively, you might try dumping in a text-format and using rsync to 
> transfer changes.

I really do'nt want to open separate port for backup only.
Pelase, can you recomment a solution which uses port 5432 owned by Postgres 
?

>> pg_read_file() can read only text files and is restricted only to 
>> superusers.
>>
>> How to add a function pg_read_backup()  to Postgres which creates and 
>> returns backup file with download speed ?
>> This problably requires implementing some file download protocol.
>
> Just don't try and do it within PG - use the tools the system provides.

How to force postmaster to run the tool which system provides when it 
receives backup request instead of postgres child process ?

Andrus. 



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

Re: [GENERAL] Editing contrib modules which are loaded by default?

[novnov]

I imagine that there is a step in the windows install that allows one to
select contrib modules. I don't recall. I didn't add these contrib modules,
so they must be included by default, which frankly seems pretty dumb.

I've tried to follow the instructions on the pgsql site for replacing
template1 with template0. It gets pretty complicated, because you can't
delete a template db, so you have to convert it to a non-template, then
delete, then proceed with the replacement. So, I did all of that and as far
as I could tell all steps succeded. But a new db still contains all of the
unwanted functions and types.

I will resort to ininstalling pgsql and reinstalling. I have to say that
this aspect of pgsql is rather difficult to work with. Starting with why
cube and g_cube modules are included by default and ending with the
difficulty of manipulating template1...it could be easier for newbies. I'll
make a request on the pgAdmin list to see if these kinds of settings can't
be somehow included in that tool.



Jim Nasby-2 wrote:
> 
> Isn't there an option when you install on windows that controls what  
> contrib stuff is included?
> 
> Anyway, it's pretty simple to add plpgsql back in using CREATE LANGUAGE.
> 
> On Nov 25, 2006, at 5:16 PM, novnov wrote:
> 
>>
>> I hadn't checked and the page you pointed to here was different  
>> from the one
>> you pointed to on the other list
>> (http://www.postgresql.org/docs/techdocs.22). I've experimented a  
>> bit and
>> have been able to see what template0 consists of by creating a db  
>> based on
>> it. It lacks the ref to the lang plpgsql. So if I made a new  
>> template1 from
>> template0, all new dbs would lack that lang. That leaves me with a  
>> different
>> starting point for my new dbs but still not what I want.
>>
>> Seems like this core config aspect of postgres could be a little  
>> simpler to
>> manage...anyways I'll figure it out, your pointers have helped.
>>
>>
>>
>> novnov wrote:
>>>
>>> Hi Ray - yes I read and checked in to your advice on the other  
>>> list but I
>>> posted here because the procedure looked a little complex and  
>>> possibly
>>> risky for a newbie like me. Rather than ask you for a simpler way  
>>> to go, I
>>> figured I'd post here and see what I came up with...and knew of  
>>> course
>>> that you'd probably be on this list too. I'm just hoping that  
>>> there is a
>>> way to clear those spurious (to me) entries without the level of  
>>> hacking
>>> that the article you pointed me to entailed.
>>>
>>>
>>>
>>> Raymond O wrote:

 On 25 Nov 2006 at 14:24, novnov wrote:

> In all of postgres 8.1.4 (windows) databases I create there are a
> bunch of cube and g_cube functions added to the db. If these are  
> not
> needed, is there a simple way to make sure that they are not  
> added to
> new databases?

 As I was telling you on the pgadmin-support list, these are copied
 from the template1 database whenever you create a new database - the
 CREATE DATABASE command effectively duplicates template1.

 To prevent these functions from appearing in your new databases,
 remove them from template1.

 Have a look at the docs:
 http://www.postgresql.org/docs/8.1/interactive/manage-ag-
 createdb.html

 --Ray O'Donnell

  
 --

 Raymond O'Donnell
 Director of Music, Galway Cathedral, Galway, Ireland
 [EMAIL PROTECTED]
  
 --



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


>>>
>>>
>>
>> -- 
>> View this message in context: http://www.nabble.com/Editing-contrib- 
>> modules-which-are-loaded-by-default--tf2705139.html#a7543210
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>> ---(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
>>
> 
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)
> 
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Editing-contrib-modules-which-are-loaded-by-default--tf2705139.html#a7583745
Sent from the PostgreSQL - general mailing list archive at 

Re: [GENERAL] How to implement backup protocol

[Michael Nolan]

I don't see where doing the backup directly to another computer increases
your safety margin, it may even lower it due to the increased potential for
network issues messing up the backup cycle.  Do it locally then SCP the
completed (and compressed) file to another computer, which is what I do.
(In fact I send it to THREE different computers in two physical locations,
including one 900 miles away.)

On 11/28/06, Andrus <[EMAIL PROTECTED]> wrote:



My environment is a bit different. For safety, I need to create backups to
separate computer over over internet.

--

Mike Nolan

Re: [GENERAL] Editing contrib modules which are loaded by default?

[Joshua D. Drake]

On Tue, 2006-11-28 at 09:38 -0800, novnov wrote:
> I imagine that there is a step in the windows install that allows one to
> select contrib modules. I don't recall. I didn't add these contrib modules,
> so they must be included by default, which frankly seems pretty dumb.
> 
> I've tried to follow the instructions on the pgsql site for replacing
> template1 with template0. It gets pretty complicated, because you can't
> delete a template db, so you have to convert it to a non-template, then
> delete, then proceed with the replacement. So, I did all of that and as far
> as I could tell all steps succeded. But a new db still contains all of the
> unwanted functions and types.

If your new db containted all of the unwanted functions, then you didn't
do *something* correctly.

> 
> I will resort to ininstalling pgsql and reinstalling. I have to say that
> this aspect of pgsql is rather difficult to work with. Starting with why
> cube and g_cube modules are included by default and ending with the
> difficulty of manipulating template1...it could be easier for newbies. I'll
> make a request on the pgAdmin list to see if these kinds of settings can't
> be somehow included in that tool.

There are uninstall scripts for all contrib modules that you could have
used.

Sincerely,

Joshua D. Drake

-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

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

Re: AIX and getaddrinfo (was Re: [GENERAL] Upgrade problem)

[Albe Laurenz]

Tom Lane wrote:
> Can anyone else confirm the behavior of getaddrinfo wanting port 5432
> to be listed in /etc/services?  If this is real, we ought to have
> something about it in FAQ_AIX.

I can compile (64 bit) and run the following code without problem:

#include 
#include 
#include 

int main(int argc, char **argv) {
struct addrinfo *res;
int rc;

rc = getaddrinfo("localhost", "5432", NULL, &res);
if (0 != rc) {
if (EAI_SYSTEM == rc)
perror("System error");
else
fprintf(stderr, "Error: %s\n",
gai_strerror(rc));
return rc;
}

return 0;
}

5432 is not in /etc/services.

This is AIX 5.3 ML 3.

Yours,
Laurenz Albe

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

   http://archives.postgresql.org/

Re: [GENERAL] Editing contrib modules which are loaded by default?

[novnov]

I'm sure I didn't do something properly but the point is, as a newbie, none
of it is obvious.

However I'm going to have to try again, as the setup for pgsql 8.1 does not
seem to have a way to turn off the cube contrib modules. They are not listed
anywhere in the setup console. 

Or I will try to find and execute the contrib module uninstall scripts.

It's all a lot of work.


Joshua D. Drake wrote:
> 
> On Tue, 2006-11-28 at 09:38 -0800, novnov wrote:
>> I imagine that there is a step in the windows install that allows one to
>> select contrib modules. I don't recall. I didn't add these contrib
>> modules,
>> so they must be included by default, which frankly seems pretty dumb.
>> 
>> I've tried to follow the instructions on the pgsql site for replacing
>> template1 with template0. It gets pretty complicated, because you can't
>> delete a template db, so you have to convert it to a non-template, then
>> delete, then proceed with the replacement. So, I did all of that and as
>> far
>> as I could tell all steps succeded. But a new db still contains all of
>> the
>> unwanted functions and types.
> 
> If your new db containted all of the unwanted functions, then you didn't
> do *something* correctly.
> 
>> 
>> I will resort to ininstalling pgsql and reinstalling. I have to say that
>> this aspect of pgsql is rather difficult to work with. Starting with why
>> cube and g_cube modules are included by default and ending with the
>> difficulty of manipulating template1...it could be easier for newbies.
>> I'll
>> make a request on the pgAdmin list to see if these kinds of settings
>> can't
>> be somehow included in that tool.
> 
> There are uninstall scripts for all contrib modules that you could have
> used.
> 
> Sincerely,
> 
> Joshua D. Drake
> 
> -- 
> 
>   === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive  PostgreSQL solutions since 1997
>  http://www.commandprompt.com/
> 
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Editing-contrib-modules-which-are-loaded-by-default--tf2705139.html#a7584226
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Including unique users in huge data warehouse in Postgresql...

[Mark Jensen]

So i've been given the task of designing a data warehouse in
either Postgresql or Mysql for our clickstream data for our sites.  I
started with Mysql but the joins in Mysql are just way too slow
compared to Postgresql when playing with star schemas.  I can't say
which sites i'm working on, but we get close to 3-5 million uniques
users per day, so over time, that's a lot of unique users to keep
around and de-dup your fact tables by.  Need to be able to query normal
analytics like:

- Unique users per time (day/month/week/ etc)
- Unique users per area of the site
-
Unique users by anything really that we have in the dimension tables
and that we know about.  it's all about unique users at all times with
them.  so any report i do, unique users are usually included in
them.  so it's hard to keep summary tables around since it's mostly
adhoc from the raw fact tables.

Let me just get to the
point.  I'm wondering if anyone has had any experience doing a
clickstream data warehouse in postgresql.  Only way I can think of
doing this is to keep a user id in the fact table that you would run a
count(distinct(uu_id)) on to get the number of unique users for that
query.  so to keep this simple, this is what i have so far, and the
fact table is probably close to 1 billion rows for about 20 days of
data.  but of course, this takes forever when you want to dedup by
uu_id by each element.  i can include visits in there as well, but most
of the sales/business guys only really want unique users.  it's pretty
fast when you query against one time_id or hour/day, but when you have
to scan the whole fact table to get LTV so far, it's crazy.

i've
made a lot of optimizations in postgresql.conf by playing with work_mem
and shared_buffers and such and i think the database is using as much
as it can disk/memory/cpu wise.  also vacuuming the tables as much as
possible.  just wondering if anyone had any suggestions or could point
out anything i could be doing wrong, or make it better to get at
uniques.  doing simple queries by not including uu_id (uniques) is
pretty fast and that's no problem.  I've also gotten 3 books from
Kimball about data warehousing including the clickstream one.  i've
also tried bizgres version of postgresql using bitmap indexes, but
didn't see a huge difference for what i need, so i'm back to using the
new beta3 of postgresql right now, since i love the new copy command
you can include queries in.

here's a sample query that takes a while to run... just a simple report that 
shows gender by area of the site.

select A.gender as gender, B.area as area, sum(C.imps) as imps, sum(C.clicks) 
as clicks, count(distinct(C.uu_id)) as users
from uus as A, areas as B, daily_area_fact as C
where A.uu_id = C.uu_id
and B.area_id = C.area_id
group by gender,area;

so
by just having one day of data, with 3,168,049 rows in the user
dimension table (uus), 17,213,420 in the daily_area_fact table that
joins all the dimension tables, takes about 15 minutes.  if i had 30-90
days in this fact table, who knows how long this would take... i know
doing a distinct on uu_id is very expensive, so that's the main problem
here i guess and would want to know if anyone else is doing it this way
or better.

Total query runtime: 878595 ms.
Data retrieval runtime: 1361 ms.
163 rows retrieved.

here's the explain:

  QUERY PLAN
  
--
 GroupAggregate  (cost=3846163.34..4104788.14 rows=24200 width=33)
   ->  Sort  (cost=3846163.34..3889196.89 rows=17213420 width=33)
 Sort Key: a.gender, b.area
 ->  Hash Join  (cost=132111.19..1306685.84 rows=17213420 width=33)
   Hash Cond: (c.uu_id = a.uu_id)
   ->  Hash Join  (cost=86.58..701292.17 rows=17213420 width=25)
 Hash Cond: (c.area_id = b.area_id)
 ->  Seq Scan on daily_area_fact c  (cost=0.00..356937.20 
rows=17213420 width=16)
 ->  Hash  (cost=78.26..78.26 rows=3326 width=17)
   ->  Seq Scan on areas b  (cost=0.00..78.26 rows=3326 
width=17)
   ->  Hash  (cost=124104.49..124104.49 rows=3168049 width=12)
 ->  Seq Scan on uus a  (cost=0.00..124104.49 rows=3168049 
width=12)
(12 rows)

So here is a snapshot of my user dimension table for each user (uus table)

dw_big=# \d uus
 Table "public.uus"
Column|  Type  |  Modifiers 
 
--++-
 uu_id| integer| not null default 
nextval('uus_uu_id_seq'::regclass)
 uu   | character(50)  | not null
 imps | integer| 
 clicks   | integer| 
 gca_clicks   | integer  

Re: [GENERAL] How to implement backup protocol

[Jeff Davis]

On Tue, 2006-11-28 at 19:34 +0200, Andrus wrote:
> > You could use an *un*trusted procedural language to create a
> > function to binary-read the backup from disk and return it
> > as a bytea field. Not sure how efficient that is, though.
> >
> > You could then simply do
> >
> > select get_backup();
> >
> > If you allow for parameters you could make it return certain
> > backups based on, perhaps, timestamp of creation.
> 
> 
> Karsten,
> 
> This id good idea but it forces to use Postgres protocol for downloading.
> This protocol has some timeouts which are too small for large file download.
> Postgres protocol has also a lot of overhead added to downloadable data.
> It also requires that whole downloadable file must fit into memory.

You could backup to a postgresql large object, and then transmit that.
Would that be more efficient? It would be kind of strange, but it might
work if you can't open any other ports.

Regards,
Jeff Davis


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

   http://archives.postgresql.org/

Re: [GENERAL] How to implement backup protocol

[Karsten Hilbert]

On Tue, Nov 28, 2006 at 07:23:44PM +0200, Andrus wrote:

> Pelase, can you recomment a solution which uses port 5432 owned by Postgres 
If you think you know your usage pattern:

Have cron stop PostgreSQL at, say, 2am.

Have cron start ssh on port 5432 at 2:05am if PG is down.

Have cron shutdown ssh on port 5432 at 2:55am.

Have cron KILL ssh on port 5432 if need be.

Have cron start PostgreSQL at 3am if ssh is down.

> How to force postmaster to run the tool which system provides when it 
> receives backup request instead of postgres child process ?
There is no (builtin/obvious/easy) way for good reason.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] How to implement backup protocol

[Karsten Hilbert]

On Tue, Nov 28, 2006 at 07:34:56PM +0200, Andrus wrote:

> This id good idea but it forces to use Postgres protocol for downloading.
Why, of course.

> This protocol has some timeouts which are too small for large file download.
For "sane" values of "large" I doubt this is true. A field
in PG can store about 1 GB of data (says the FAQ) and the
protocol better be able to hand out as much.

It may be that you need to increase statement_timeout -
which can be done on a per-session basis.

> Postgres protocol has also a lot of overhead added to downloadable data.
Yes. But you wanted to use port 5432 on a machine already
running PG.

Not sure but using a binary cursor might improve things.
Using a client library capable of the v3 (?) protocol should
significantly lower the overhead, too.

> It also requires that whole downloadable file must fit into memory.
My PG knowledge isn't up to this task but I have a sneaking
suspicion this isn't really enforced by PG itself.

ODBC
> I tried this but was forced to store big files in 1 MB chunks in bytea 
> fields and create file from downloaded blocks
Other client libraries may do better here.

> Or should I really write code which divides backup file to 1 MB chunks and 
> stores them in bytea field ?
No. I would not even store them in the database at all. I
would use the untrusted language function to read the file
from disk and return a (virtual) bytea field (which doesn't
exist in the database).

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

   http://archives.postgresql.org/

Re: [GENERAL] Only MONO/WinForms is a way to go

[John DeSoi]

On Nov 28, 2006, at 12:17 PM, Andrus wrote:


Only MONO/WinForms  is a way to go in any serious application.



Funny. Did you read the original post? The poster wanted a *cross- 
platform* GUI and his primary development environment was OS X. Mono  
might run on a Mac after you install thousands of packages (see  
instructions below), but the applications won't look like a native  
Mac application. There are supposedly some native Mono toolkits for  
the Mac, but I have yet to see any Mac application that uses it.


As I mentioned previously, XUL is worth a look for cross platform  
applications. I would call FireFox, Mozilla, and Thunderbird serious  
applications. And check out Komodo, an excellent cross platform  
development environment built on this framework.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




While MacOS X has its own GUI toolkit (Aqua/Cocoa), it also includes  
support for X11 (Quartz accelerated). Gtk# (http://mono-project.com/ 
GtkSharp), Mono's cross platform graphical toolkit runs on MacOS X  
but packages are not yet available for it or its dependencies (which  
are numerous).


Today, if you want to run or develop Gtk# applications, Fink (http:// 
fink.sourceforge.net) is the best way to install all necessary  
dependencies (gnome-desktop and all others). Installing GTK# on MacOS  
is still a little effort intensive (instructions from Geoff Norton):


Install the latest Mono.framework
Install Fink (http://fink.sourceforge.net)
Update fink to CVS (fink selfupdate-cvs)
Update fink to unstable
Update all fink core packages (fink update-all)
Install Apple X11
Install gnome and gtkhtml3 from fink (fink install bundle-gnome  
gtkhtml3 gtkhtml3-dev gtkhtml3-shlibs)

Have a coffee / nap / go for a walk while fink installs Gnome.
Install Gtk# from source to the prefix /Library/Frameworks/ 
Mono.framework/Versions/Current (You will need to set the following  
environment variables: PKG_CONFIG_PATH=/sw/lib/pkgconfig:/usr/X11R6/ 
lib/pkgconfig:/Library/

Frameworks/Mono.framework/Versions/Current/lib/pkgconfig)


---(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] Editing contrib modules which are loaded by default?

[Raymond O'Donnell]

On 28 Nov 2006 at 10:01, novnov wrote:

> I'm sure I didn't do something properly but the point is, as a newbie,
> none of it is obvious. 

The documentation that comes with PostgreSQL head-and-shoulders above 
anything that comes with many commercial products. If you're going to 
work woth PostgreSQL, it's a good idea to read as much of it as you 
can.

> It's all a lot of work.

But it's worth it. :)


--

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
[EMAIL PROTECTED]
--



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

Re: [GENERAL] Editing contrib modules which are loaded by default?

[Richard Huxton]

novnov wrote:

I'm sure I didn't do something properly but the point is, as a newbie, none
of it is obvious.

However I'm going to have to try again, as the setup for pgsql 8.1 does not
seem to have a way to turn off the cube contrib modules. They are not listed
anywhere in the setup console. 


What do you mean by "setup console"? Because I just don't believe that 
the installer doesn't let you turn the cube contrib off.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Merlin Moncure]

On 11/28/06, Olexandr Melnyk <[EMAIL PROTECTED]> wrote:

2006/11/28, Tony Caduto <[EMAIL PROTECTED]>:
> Mono 1.2 only fully supports .net 1.1 (for winforms)

Yes. But it already supports most of the .NET 2.0 features (not talking of
WinForms here) including the ones metioned above and has a C# 2.0 compiler.

> What kind of problems have you seen with the VCL as a project matures?

As I've already mentioned, it's not layout-based. Anchors only partially
solve the task, so problems can still appear when adding extra languages




interfaces to the application. Also, VCL is quite incomplete. Say, how much
sense does it make for button not to have color property, while other
components, like edit, label and check box have it? There are similar
situations with other objects, when it seems odd, why it doesn't have a
certain feature/property.


there are many components that solve this.  I was quite astounded when
windows.forms came out that it did not have a functional grid
component.  While mono/c# is nice, I greatly prefer the TDataset
interface to ado.net middleware.  There are some VCL based database
middlewares, like zeos, which completely outclass odbc.

For a general purpose language, lately I've been taking a really good
look at 'D', which looks to be an amazing language.  Has anybody tried
to hook up postgresql to D?

merlin

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

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

Re: [GENERAL] NULLs ;-)

[John D. Burger]

where a <> b or (a is null and b is not null) or (a is not null  
and b is null)


In the absence of IS DISTINCT FROM, I think this has the same  
semantics:


   where coalesce(a, b) <> coalesce(b, a)


sorry, but no.


Argh, my expression is just nonsense - I was thinking of something like:

  coalesce(a, 'SOME MAGIC VALUE') <> coalesce(b, 'SOME MAGIC VALUE')

and wanted to make it work for any types.  Sigh.

- John D. Burger
  MITRE

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

Re: [GENERAL] How to implement backup protocol

[Richard Huxton]

Andrus wrote:

Richard,

Use scp. Open port 22 and allow only connections from the backup machine 
with a specified user (e.g. "pgbackup").


Alternatively, you might try dumping in a text-format and using rsync to 
transfer changes.


I really do'nt want to open separate port for backup only.
Pelase, can you recomment a solution which uses port 5432 owned by Postgres 
?


No.

You seem dead set on making your life harder than it needs to be. If you 
really don't want to have the ssh port open then set the backup to run 
from a cron-job on the main server and have it scp the result over to 
the backup server. That way only the backup server needs ssh open.


pg_read_file() can read only text files and is restricted only to 
superusers.


How to add a function pg_read_backup()  to Postgres which creates and 
returns backup file with download speed ?

This problably requires implementing some file download protocol.

Just don't try and do it within PG - use the tools the system provides.


How to force postmaster to run the tool which system provides when it 
receives backup request instead of postgres child process ?


Don't. You're just making life difficult for yourself. Use the standard 
Unix solution for such things - ssh.


How are you installing updates without ssh access to the server?

--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Only MONO/WinForms is a way to go

[Joshua D. Drake]

On Tue, 2006-11-28 at 13:52 -0500, John DeSoi wrote:
> On Nov 28, 2006, at 12:17 PM, Andrus wrote:
> 
> > Only MONO/WinForms  is a way to go in any serious application.
> 
> 
> Funny. Did you read the original post? The poster wanted a *cross- 
> platform* GUI and his primary development environment was OS X.

I didn't see that in my post either... py/qt will draw to native widgets
I believe.

Joshua D. Drake

-- 

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

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

Re: [GENERAL] AIX and getaddrinfo

[Chris Browne]

[EMAIL PROTECTED] (Bill Kurland) writes:
> Chris Browne wrote:
>> [EMAIL PROTECTED] (Tom Lane) writes:
>>
>>> Bill Kurland <[EMAIL PROTECTED]> writes:
>>>
 I did a google search on AIX + getaddrinfo and found
 http://lists.samba.org/archive/rsync/2002-April/002063.html
 In that context the author says that adding the port number in
 etc/services solved his problem with getaddrinfo.

>>> Interesting.  I wonder whether IBM thinks that there is some
>>> security-related reason for only allowing programs to bind to port
>>> numbers that are listed in /etc/services?
>>>
>>>
 So I tried that and, lo, it has some effect, though I'm not sure
 it's 100% desirable. The log entry is:
   LOG:  could not bind IPv6 socket: The type of socket is not
 supported in this protocol family.
 HINT:  Is another postmaster already running on port 5432? If not,
 wait a few seconds and retry.

>>> I think this is OK.  There are many machines on which the userspace
>>> code supports IPv6 while the kernel doesn't, or vice versa.  It looks
>>> to me like getaddrinfo returned both IPv4 and IPv6 translations of
>>> "localhost", but the kernel rejected the IPv6 version when PG tried it.
>>> Since you evidently have a working IPv4 port, there's nothing to worry
>>> about.  If it really bugs you, the /etc/netsvc.conf change suggested in
>>> our FAQ_AIX would probably suppress the log message.
>>>
>>> Can anyone else confirm the behavior of getaddrinfo wanting port 5432
>>> to be listed in /etc/services?  If this is real, we ought to have
>>> something about it in FAQ_AIX.
>>>
>>
>> That doesn't make a lot of sense to me...
>>
>> It is fair to say that AIX does include a PostgreSQL entry, by
>> default:
>>
>> [EMAIL PROTECTED]:/opt/home/pgorg $ grep 5432 /etc/services
>> postgresql  5432/tcp# PostgreSQL Database
>> postgresql  5432/udp# PostgreSQL Database
>>
>> [We didn't add these entries ourselves; they were there by default.]
>>
> Odd. I checked 4 different AIX 4.3 machines and none of them have
> postgresql entries. I don't have
> any AIX 5 boxes. Is that what you are running?

Yup, we're running 5.3.  I don't think we have any 5.1 boxes left to
look at.

You might also look for port 3306; we've got MySQL listed as
associated with that port.  I suspect AIX 4.3 doesn't include that
either.

I'd be very unkeen on continuing to run 4.anything; we had a nasty
surprise this spring when we discovered that 5.1 was falling out of
service shortly before we were to migrate databases to 5.3.

>> But most of our database backends run on ports other than 5432, and I
>> haven't noticed anything that seems to tie to that.  We have had
>> difficulties with getaddrinfo(), but I don't think there's anything
>> that hasn't either been resolved in modern PG releases or listed in
>> FAQ_AIX.

> I just reread the FAQ_AIX doc for 8.1.5 and I couldn't find any
> reference to getaddrinfo. What difficulties have you seen and under
> which AIX ?

It has always turned into patches.  For 7.4, we needed to apply a
patch to turn parts of the IPv6 functionality off, otherwise we had
intermittent SIG 11 problems.  Nothing directly FAQ-worthy; it was
code that got added into later versions.
-- 
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" 
[name;tld];;
http://linuxdatabases.info/info/advocacy.html
Oh,  boy, virtual memory!  Now I'm  gonna make  myself a  really *big*
RAMdisk!

---(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] How to implement backup protocol

[Tom Lane]

Karsten Hilbert <[EMAIL PROTECTED]> writes:
> Not sure but using a binary cursor might improve things.

Why not use COPY protocol?

regards, tom lane

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

Re: [GENERAL] Only MONO/WinForms is a way to go

[Tony Caduto]

John DeSoi wrote:



As I mentioned previously, XUL is worth a look for cross platform 
applications. I would call FireFox, Mozilla, and Thunderbird serious 
applications. And check out Komodo, an excellent cross platform 
development environment built on this framework.


They are serious applications, but they don't exactly have a lot of 
forms and look how long Mozilla was in development.

Sure XUL might be cross platform, but how productive is it?
The reason there is no highly productive IDE for Linux/Mac with a nice 
forms designer and robust data binding is because in the grand scheme of 
things there are not a lot of
desktop users for anything other than win32.  Sure there are lots of 
geeks that use Linux for their desktop, but not everyday users.
Everyday users are the ones companies etc want to make software for and 
Linux etc just does not have those kind of users yet.

The mac does, but they are small  in number
CodeGear(Borland devtools group) will make a IDE for Mac or Linux when 
they can make a viable return on investment.  They experimented with 
Kylix, but it failed because they initially priced it to high and many 
open source users will not pay even a reasonable amount for a IDE.


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] Editing contrib modules which are loaded by default?

[Dave Page]

> --- Original Message ---
> From: Richard Huxton 
> To: novnov <[EMAIL PROTECTED]>
> Sent: 28/11/06, 18:56:37
> Subject: Re: [GENERAL] Editing contrib modules which are loaded by default?
> 
> Because I just don't believe that 
> the installer doesn't let you turn the cube contrib off.

It does - in fact it's off by default. The libs & scripts are always installed 
with the server, but  only loaded into template1 if the user checks the 
appropriate box.

Regards, Dave

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

Re: [GENERAL] Only MONO/WinForms is a way to go

[Rich Shepard]

On Tue, 28 Nov 2006, Tony Caduto wrote:


The reason there is no highly productive IDE for Linux/Mac with a nice
forms designer and robust data binding is because in the grand scheme of
things there are not a lot of desktop users for anything other than win32. 
Sure there are lots of geeks that use Linux for their desktop, but not

everyday users.


  Hello? Where have you been the past few years? This is one of the most
amusing mis-statements I've read in a long time.

  I know a software engineer with more than 25 years experience who works
with the KDE IDE for multi-language development, and he deploys these
applications on multiple platforms.

  But, wait, ... you'd classify him with the geeks.

  How about municiple workers in Munich, Germany? Are they not everyday
non-geeks? The city dumped Microsoft for linux a couple of years ago. So
havs Brazil, China, and a host of other countries. Many schools -- including
a large number in Oregon and Washington -- run linux for both students and
administrators using the Linux Terminal Server Project (LTSP/K12).

  Well, that's enough. If you look around you, you'll recognize how silly
that reads. At least, I hope that you do.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

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

Re: [GENERAL] How to increace nightly backup speed

[Vivek Khera]

On Nov 28, 2006, at 11:11 AM, Andrus wrote:

1. My database size seems to be appox 1 GB and download speed is  
approx 600

kb/s.  Your solution requires 4.5 hours download time
since 1 GB of data must be downloaded.


If you're running pg_dump on a remote host, you're transferring the  
data over the pipe and compressing locally, since the pg wire  
protocol is not compressed.  The compression time is probably not  
causing any slowness unless your local CPU is incredibly slow and  
can't keep up with the data streaming in at that low speed.


I don't see how you can improve your download speed without doing  
compression at the other end to reduce the number of bits you have to  
push through your network.


SSH seems to be a resonable solution to this (run dump + compress on  
remote host, then copy data over), but if you rule out anything that  
doesn't go over port 5432 then I think you're out of luck...


Well, one thing... is there another host on the remote LAN to which  
you can ssh?  If so, then use SSH port-forwarding and enable  
compression on the ssh connection to that host, then connect to  
postgres via the forwarded port to do your dump locally.  The data  
will be compressed on the wire.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

[Rich Shepard]

On Tue, 28 Nov 2006, Merlin Moncure wrote:


For a general purpose language, lately I've been taking a really good look
at 'D', which looks to be an amazing language.  Has anybody tried to hook
up postgresql to D?


  No, I haven't. But, if you want a cross-platform language and GUI toolkit,
consider Python and wxPython.

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

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

Re: [GENERAL] NULLs ;-)

[Scott Ribe]

>>> where a <> b or (a is null and b is not null) or (a is not null and
>>> b is null)
>> 
>> In the absence of IS DISTINCT FROM, I think this has the same semantics:
>> 
>>where coalesce(a, b) <> coalesce(b, a)
> 
> sorry, but no.

So it would have to be where coalesce(a, b, 0) <> coalesce(b, a, 0) for your
example with ints, and likewise some default value for other column types...

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



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

   http://archives.postgresql.org/

Re: [GENERAL] backend crash following load command

[Merlin Moncure]

On 11/28/06, Tom Lane <[EMAIL PROTECTED]> wrote:

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> We are getting a backend crash after issueing a load command.

No crash from your example here (on Fedora Core 5).  What platform and
gcc are you using exactly?  Can you provide a stack trace from the crash?


ok, an update on this.  we actually covered up the bug in reducing the
problem to our test case.  our make system used cp -f to overwite the
.so file in use by postgresql.  interestingly, this will cause a crash
on the .so reload via LOAD.  There may be a perfectly normal reason
for this.

so,
1. compile just about any c function
2. create a function/load it
3. recompile and cp -f over the one in use (cp works ok)
4. reload...crash

merlin

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

Re: [GENERAL] Only MONO/WinForms is a way to go

[Thomas Kellerer]

Andrus wrote on 28.11.2006 18:17:

5. Java is not LGPL and does not support Generic at bytecode level

I have heard this "Java is not open source" over and over again.
What's the issue with wanting the language to be open source? Where is the 
problem with using Java from a license perspective? You are (and always have 
been) free to bundle the JRE with your app. And besides: Java just went 
OpenSource with (AFAIK) very liberal license.


And who cares if my bytecode knows something about Generics as long as the 
application runs at a good speed?


Thomas


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

  http://archives.postgresql.org/

Re: [GENERAL] Only MONO/WinForms is a way to go

[Tony Caduto]

Rich Shepard wrote:

On Tue, 28 Nov 2006, Tony Caduto wrote:


The reason there is no highly productive IDE for Linux/Mac with a nice
forms designer and robust data binding is because in the grand scheme of
things there are not a lot of desktop users for anything other than 
win32. Sure there are lots of geeks that use Linux for their desktop, 
but not

everyday users.


  Hello? Where have you been the past few years? This is one of the most
amusing mis-statements I've read in a long time.

  I know a software engineer with more than 25 years experience who works
with the KDE IDE for multi-language development, and he deploys these
applications on multiple platforms.

  But, wait, ... you'd classify him with the geeks.

  How about municiple workers in Munich, Germany? Are they not everyday
non-geeks? The city dumped Microsoft for linux a couple of years ago. So
havs Brazil, China, and a host of other countries. Many schools -- 
including
a large number in Oregon and Washington -- run linux for both students 
and

administrators using the Linux Terminal Server Project (LTSP/K12).

  Well, that's enough. If you look around you, you'll recognize how silly
that reads. At least, I hope that you do.

Rich

It's not silly, world wide the vast majority is still win32 and it's 
over 90 percent.
Munich Germany is what 20,000 desktops?  again small numbers in the 
grand scheme of things.
Do you think companies like Adobe etc are going to port their win32 only 
software for the Munich Germany users?
Can you name a significant number of LARGE corps that uses Linux 
Desktops besides IBM or Novell?


I don't like M$ , but the cold hard fact of the matter is they still own 
the desktop market and that's why
commercial companies like Adobe, Intuit etc don't write software for 
Linux or other non windows desktops.
Also when I say everyday users I mean people at home, the moms, the 
dads, the 8 year old kids etc etc


Go into any retail store and try and find commercial shrink wrapped 
software, guess what there isn't any. (at least here in Milwaukee)


You will find a few Linux users here and there, for example the city of 
Kenosha here in Wisconsin uses it a lot, Burlington Coat Factory uses 
it, but in each of these cases they have not been able to fully switch 
because some apps would not run on Linux.
If I go into almost any business/corp in the downtown Milwaukee area 
they all us M$ products many do have Linux or FreeBSD servers, but they 
are limited to things like email gateways, database servers etc.


Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



---(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] How to implement backup protocol

[Ragnar]

On þri, 2006-11-28 at 19:23 +0200, Andrus wrote:
> Richard,
> 
> I really do'nt want to open separate port for backup only.
> Pelase, can you recomment a solution which uses port 5432 owned by Postgres 

I do not want to advice you to do things that might be
counter your company's security policies, but you
could set up a portforwarder on your database machine to
pass incoming port 5432 requests from the backup machine
to sshd, but let all other source ips go to postgres

alternatively, if you have control of cron, and if
the firewall restrictons are for incoming only, and
if you have open ssh port on some other machine, such
as the backupserver, you can have cron do the compressed
backup, and send it via scp to the backupserver.

> How to force postmaster to run the tool which system provides when it 
> receives backup request instead of postgres child process ?

if you cannot control cron, but the firewall restrictions are only for
incoming requests, you might try using some
untrusted procedural language to start a backup script
locally, that finishes by copying the compressed backup
to the backupserver.

whatever you choose to do, you should still get 
permission to do it. security admins are not amused 
when users use tricks to get around restrictons.

maybe they would consider opening the ssh port if
you make it clear that they may restrict it to
requests from the backup machine? 

gnari



---(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] slow inet within cidr query

[Edwin Grubbs]

Under postgres 8.1, the "<<=" comparison yields very slow queries with large
tables. I can rewrite the query without the "<<=" operator by generating all
33 possible netmasks (0 through 32) for a given IP. This ugly rewrite runs
about 12 times faster (6 seconds versus 0.5 seconds). Be aware that EXPLAIN
ANALYZE seems to be run a different query plan since the first query runs
even slower with EXPLAIN ANALYZE. Setting enable_seqscan did not improve the
query speed.

-Edwin

Here is the original query:

SELECT ip_address, ip_block
FROM ip_inventory
   JOIN route ON ip_address  <<= ip_block
WHERE ip_address < '1.5.0.0'
ORDER BY ip_address;


Here is the rewritten query:

SELECT ip_address, ip_block
from ip_inventory
   JOIN route
   ON ip_block IN (
   network(ip_address),
   network((host(ip_address) || '/31')::inet),
   network((host(ip_address) || '/30')::inet),
   network((host(ip_address) || '/29')::inet),
   network((host(ip_address) || '/28')::inet),
   network((host(ip_address) || '/27')::inet),
   network((host(ip_address) || '/26')::inet),
   network((host(ip_address) || '/25')::inet),
   network((host(ip_address) || '/24')::inet),
   network((host(ip_address) || '/23')::inet),
   network((host(ip_address) || '/22')::inet),
   network((host(ip_address) || '/21')::inet),
   network((host(ip_address) || '/20')::inet),
   network((host(ip_address) || '/19')::inet),
   network((host(ip_address) || '/18')::inet),
   network((host(ip_address) || '/17')::inet),
   network((host(ip_address) || '/16')::inet),
   network((host(ip_address) || '/15')::inet),
   network((host(ip_address) || '/14')::inet),
   network((host(ip_address) || '/13')::inet),
   network((host(ip_address) || '/12')::inet),
   network((host(ip_address) || '/11')::inet),
   network((host(ip_address) || '/10')::inet),
   network((host(ip_address) || '/9')::inet),
   network((host(ip_address) || '/8')::inet),
   network((host(ip_address) || '/7')::inet),
   network((host(ip_address) || '/6')::inet),
   network((host(ip_address) || '/5')::inet),
   network((host(ip_address) || '/4')::inet),
   network((host(ip_address) || '/3')::inet),
   network((host(ip_address) || '/2')::inet),
   network((host(ip_address) || '/1')::inet),
   '0.0.0.0'::cidr
   )
WHERE ip_address < '1.5.0.0'
ORDER BY ip_address;


Here is the SQL for creating and populating the test tables:

BEGIN;

CREATE TABLE range (value integer);
COPY range FROM STDIN;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
\.

CREATE TABLE ip_inventory (ip_address inet unique);

-- add 279841 ips
INSERT INTO ip_inventory
SELECT (a || '.' || b || '.' || c || '.' || d)::inet
FROM range AS w(a)
   JOIN range AS x(b) ON TRUE
   JOIN range AS y(c) ON TRUE
   JOIN range AS z(d) ON TRUE;

CREATE TABLE route (ip_block cidr unique);

-- add 12167 routes
INSERT INTO route
SELECT (a || '.' || b || '.' || c || '.0/24')::cidr
FROM range AS w(a)
   JOIN range AS x(b) ON TRUE
   JOIN range AS y(c) ON TRUE;

COMMIT;

Re: [GENERAL] Only MONO/WinForms is a way to go

[Tony Caduto]

Thomas Kellerer wrote:



And who cares if my bytecode knows something about Generics as long as 
the application runs at a good speed?



I totally agree about generics, nice to have but not really needed.

In case not everyone is up to speed about generics, this article is 
really good:


http://www.artima.com/intv/generics.html


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

Re: [GENERAL] backend crash following load command

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 02:38:18PM -0500, Merlin Moncure wrote:
> On 11/28/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> >"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> >> We are getting a backend crash after issueing a load command.
> >
> >No crash from your example here (on Fedora Core 5).  What platform and
> >gcc are you using exactly?  Can you provide a stack trace from the crash?
> 
> ok, an update on this.  we actually covered up the bug in reducing the
> problem to our test case.  our make system used cp -f to overwite the
> .so file in use by postgresql.  interestingly, this will cause a crash
> on the .so reload via LOAD.  There may be a perfectly normal reason
> for this.

Err, that means copy is just rewriting the executable code in the
backend of the server, while it's running, which understandably
crashes. Probably while trying to unload the old library. I suppose the
answer is: don't do that.

The protection of ETXTBUSY only applies to code started via exec().

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


signature.asc
Description: Digital signature

[GENERAL] using a sequence as the functional equivalent to Oracle rownum

[Wm.A.Stafford]

I'm trying to use a temporary sequence to duplicate the functionality of 
the Oracle rownum pseudo-column
as suggested by Scott Marlow in the archives: 
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.


The Oracle based application I'm porting to PostgreSQL used  rownum to 
select the 'next' block of rows to
process by specifying a where clause with something like " where 
rownum>x and rownum

My basic PostgreSQL query is:

drop sequence rownum ;
create temp sequence rownum;

select B.rownum , B.id from
(select nextval('rownum') as rownum, A.*  from
(select distinct id  from ... where ... order by ... DESC
) as A
) as B
where id>0

This basic query produces the following result set:
rownum id
+-
 1   10038
 2   10809
 3   10810
 4   22549
 5   23023

However, if I add a where clause referencing rownum for example: where 
id>0 and rownum>0

I get the following:

rownum   id
---+-
 1110038
 1210809
 1310810
 1422549
 1523023

It appears as if rownum has been incremented as a result of three passes 
over the five row result set.


Can someone explain what is going on?  And more to to point, if this is 
expected behavior, is there a standard PostgreSQL way to select a 
'block' of rows from a result set based on row number?


Thanks,
-=bill



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

Re: [GENERAL] backend crash following load command

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> ok, an update on this.  we actually covered up the bug in reducing the
> problem to our test case.  our make system used cp -f to overwite the
> .so file in use by postgresql.

With that I can reproduce it --- I think it is a glibc bug.  The crash
occurs inside dlsym() while trying to look up "_PG_fini".

(gdb) bt
#0  0x003bf1a08b31 in do_lookup_x () from /lib64/ld-linux-x86-64.so.2
#1  0x003bf1a08e6f in _dl_lookup_symbol_x ()
   from /lib64/ld-linux-x86-64.so.2
#2  0x003bf1cff5ee in do_sym () from /lib64/libc.so.6
#3  0x003bf2101334 in dlsym_doit () from /lib64/libdl.so.2
#4  0x003bf1a0ca36 in _dl_catch_error () from /lib64/ld-linux-x86-64.so.2
#5  0x003bf210173d in _dlerror_run () from /lib64/libdl.so.2
#6  0x003bf21012ea in dlsym () from /lib64/libdl.so.2
#7  0x0061f414 in load_file (filename=Variable "filename" is not 
available.
) at dfmgr.c:352
#8  0x005a3d4c in PortalRunUtility (portal=0x98a828, query=0x9564f0,
dest=0x956798, completionTag=0x7fffb624e4e0 "") at pquery.c:1063

I'd suggest putting together a simple stand-alone test case and filing
a bug report against glibc.  You probably just need

dlopen(...);
system("cp -f over the .so file");
dlsym(...);

regards, tom lane

---(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] backend crash following load command

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 03:23:36PM -0500, Tom Lane wrote:
> I'd suggest putting together a simple stand-alone test case and filing
> a bug report against glibc.  You probably just need
> 
>   dlopen(...);
>   system("cp -f over the .so file");
>   dlsym(...);

How can glibc do anything about this? dlopen() mmaps the .so into
memory and the cp overwrites what was mmaped, changing what is in
memory.

Ideally, the cp should fail with ETXTBSY, but that doesn't happen, so
what else can you do?

Have a nice day,
-- 
Martijn van Oosterhout  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] backend crash following load command

[Tom Lane]

Martijn van Oosterhout  writes:
> On Tue, Nov 28, 2006 at 03:23:36PM -0500, Tom Lane wrote:
>> I'd suggest putting together a simple stand-alone test case and filing
>> a bug report against glibc.

> How can glibc do anything about this? dlopen() mmaps the .so into
> memory and the cp overwrites what was mmaped, changing what is in
> memory.

The test case I was using involved a cp -f that overwrote the .so with
the exact same data (ie, I didn't bother recompiling, just cp -f a
second time from the compilation output file).  So if the above were
the explanation there should have been no crash; moreover, if that were
the explanation then the cp-without-dash-f case should crash too.

I suspect that glibc is playing some undocumented games and is getting
confused because the file's inode number has changed.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] backend crash following load command

[Merlin Moncure]

On 11/28/06, Tom Lane <[EMAIL PROTECTED]> wrote:

Martijn van Oosterhout  writes:
> On Tue, Nov 28, 2006 at 03:23:36PM -0500, Tom Lane wrote:
>> I'd suggest putting together a simple stand-alone test case and filing
>> a bug report against glibc.

> How can glibc do anything about this? dlopen() mmaps the .so into
> memory and the cp overwrites what was mmaped, changing what is in
> memory.

The test case I was using involved a cp -f that overwrote the .so with
the exact same data (ie, I didn't bother recompiling, just cp -f a
second time from the compilation output file).  So if the above were
the explanation there should have been no crash; moreover, if that were
the explanation then the cp-without-dash-f case should crash too.

I suspect that glibc is playing some undocumented games and is getting
confused because the file's inode number has changed.


also, if what Martijn is saying is correct, wouldn't that make the
LOAD command unsupportably dangerous?  The postgresql documentation
suggests its use is for updating libraries in exactly this way
(emphasis mine):

This command loads a shared library file into the PostgreSQL server's
address space. If the file had been loaded previously, it is first
unloaded. This command is primarily useful to unload and reload a
shared library file that *has been changed since the server first
loaded it*. To make use of the shared library, function(s) in it need
to be declared using the CREATE FUNCTION command.

merlin

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

  http://archives.postgresql.org/

Re: [GENERAL] backend crash following load command

[Tom Lane]

Martijn van Oosterhout  writes:
> Err, that means copy is just rewriting the executable code in the
> backend of the server, while it's running, which understandably
> crashes.

No, I don't think so.  "cp -f" means "unlink the old file and create a
new one", as opposed to plain cp which would overwrite in place.  Your
theory would explain an observation that plain cp causes a crash while
cp -f does not, but that's the exact opposite of Merlin's report.

The actual situation is that the mmap is referencing a file that's
disappeared from the directory structure (but still exists on disk,
as long as it's held open).  dlsym seems unable to cope with that
case.  I call that a bug --- it'd be OK for it to return a failure
indication, but not to SIGSEGV.

regards, tom lane

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

Re: [GENERAL] using a sequence as the functional equivalent to Oracle rownum

[Tom Lane]

"Wm.A.Stafford" <[EMAIL PROTECTED]> writes:
> ... is there a standard PostgreSQL way to select a 
> 'block' of rows from a result set based on row number?

LIMIT/OFFSET might be what you are looking for --- it's certainly far
less klugy than a temporary sequence.

regards, tom lane

---(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] Only MONO/WinForms is a way to go

[Merlin Moncure]

On 11/28/06, Tony Caduto <[EMAIL PROTECTED]> wrote:

Thomas Kellerer wrote:
>
>
> And who cares if my bytecode knows something about Generics as long as
> the application runs at a good speed?
>
I totally agree about generics, nice to have but not really needed.


I dont like generics as much as c++ templates, but both are far better
than inheritance style oo, imo. c++ with STL, more than any other
language (except for possibily D, which I'm looking at) allows me to
write fast code with minimum effort.

merlin

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

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

Re: [GENERAL] using a sequence as the functional equivalent to Oracle

[Oisin Glynn]

Wm.A.Stafford wrote:
I'm trying to use a temporary sequence to duplicate the functionality 
of the Oracle rownum pseudo-column
as suggested by Scott Marlow in the archives: 
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.


The Oracle based application I'm porting to PostgreSQL used  rownum to 
select the 'next' block of rows to
process by specifying a where clause with something like " where 
rownum>x and rownum

My basic PostgreSQL query is:

drop sequence rownum ;
create temp sequence rownum;

select B.rownum , B.id from
(select nextval('rownum') as rownum, A.*  from
(select distinct id  from ... where ... order by ... DESC
) as A
) as B
where id>0

This basic query produces the following result set:
rownum id
+-
 1   10038
 2   10809
 3   10810
 4   22549
 5   23023

However, if I add a where clause referencing rownum for example: where 
id>0 and rownum>0

I get the following:

rownum   id
---+-
 1110038
 1210809
 1310810
 1422549
 1523023

It appears as if rownum has been incremented as a result of three 
passes over the five row result set.


Can someone explain what is going on?  And more to to point, if this 
is expected behavior, is there a standard PostgreSQL way to select a 
'block' of rows from a result set based on row number?


Thanks,
-=bill



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


I have done this using limit and offset like the following
select * from foo order by bar limit 10 offset 50;--giving the 10 rows 
from position 51 onwards (offset is zero based)


Oisin




smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] slow inet within cidr query

[Tom Lane]

"Edwin Grubbs" <[EMAIL PROTECTED]> writes:
> Under postgres 8.1, the "<<=" comparison yields very slow queries with large
> tables.

<<= isn't optimizable within joins, and really isn't very suited to
btree indexes at all.  Sometime somebody should try to build a GiST
opclass that supports network sub/sup comparisons ... seems like it
should be pretty easy ...

regards, tom lane

---(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] NULLs ;-)

[Ragnar]

On þri, 2006-11-28 at 12:28 -0700, Scott Ribe wrote:
> >>> where a <> b or (a is null and b is not null) or (a is not null and
> >>> b is null)
> >> 
> >> In the absence of IS DISTINCT FROM, I think this has the same semantics:
> >> 
> >>where coalesce(a, b) <> coalesce(b, a)
> > 
> > sorry, but no.
> 
> So it would have to be where coalesce(a, b, 0) <> coalesce(b, a, 0) for your
> example with ints, and likewise some default value for other column types...

no cigar.

test=# select a,b,
   coalesce(a, b, 0) <> coalesce(b, a, 0) as john,
   a IS DISTINCT FROM b as dist
   from logic;
 a | b | john | dist
---+---+--+--
   |   | f| f
   | 1 | f| t
 1 |   | f| t
 1 | 1 | f| f
(4 rows)


gnari




---(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] Editing contrib modules which are loaded by default?

[novnov]

I was wrong from the get-go, I apparently selected the cube contrib, because
on reinstall it's not automatically selected, at least in 8.1.5.


Richard Huxton wrote:
> 
> novnov wrote:
>> I'm sure I didn't do something properly but the point is, as a newbie,
>> none
>> of it is obvious.
>> 
>> However I'm going to have to try again, as the setup for pgsql 8.1 does
>> not
>> seem to have a way to turn off the cube contrib modules. They are not
>> listed
>> anywhere in the setup console. 
> 
> What do you mean by "setup console"? Because I just don't believe that 
> the installer doesn't let you turn the cube contrib off.
> 
> -- 
>Richard Huxton
>Archonet Ltd
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Editing-contrib-modules-which-are-loaded-by-default--tf2705139.html#a7588330
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   http://archives.postgresql.org/

Re: [GENERAL] backend crash following load command

[Martijn van Oosterhout]

On Tue, Nov 28, 2006 at 04:09:11PM -0500, Tom Lane wrote:
> The mmap man page is pretty vague on the subject, but I wonder whether
> the shlib isn't effectively treated as copy-on-write --- that is, any
> attempted overwrite of the file happens only after the mmap region has
> been fully copied.  Without that, it'd be impossible to update core
> shared libraries like libc.so without a system reboot, but Linux doesn't
> seem to need that.

Hmm? To upgrade libc.so you merely need to delete the old one and
install the new one, there's no need to preserve the inode. The mmap()
is private, but no, Linux does not keep a backup copy of the shared
library if you overwrite it. The behaviour of overwriting the backing
store of a private mapping is explicitly undefined.

I did some digging. At one point there was protection for overwriting
shared libraries, you could pass MAP_DENYWRITE to mmap(), which would
cause any writes to the file to fail with ETXTBSY, just like it does
for normal executables. However:

MAP_DENYWRITE 
This flag is ignored. (Long ago, it signalled that attempts to
write to the underlying file should fail with ETXTBUSY.  But this
was a source of denial-of-service attacks.)

> I suspect that this issue is specific to dlsym() and has nothing to do
> with the safeness of ordinary usage of a shared library.  The reason
> 8.2 is getting bit is that it tries to do a dlsym() lookup during shlib
> unload, which we never did before.  (Merlin, I assume you have been
> doing the same things with 8.1 and before without a problem?)

I wouldn't be surprised if this were the problem. People testing shared
libraries would probably not be testing what happened between the time
the shared-library was overwritten and the LOAD command was reexecuted.

Have a nice day,
-- 
Martijn van Oosterhout  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] slow inet within cidr query

[Steve Atkins]

On Nov 28, 2006, at 12:07 PM, Edwin Grubbs wrote:

Under postgres 8.1, the "<<=" comparison yields very slow queries  
with large tables. I can rewrite the query without the "<<="  
operator by generating all 33 possible netmasks (0 through 32) for  
a given IP. This ugly rewrite runs about 12 times faster (6 seconds  
versus 0.5 seconds). Be aware that EXPLAIN ANALYZE seems to be run  
a different query plan since the first query runs even slower with  
EXPLAIN ANALYZE. Setting enable_seqscan did not improve the query  
speed.


GiST opclasses for inet to make <<= indexable would be nice,
but I don't think anyones done them yet.

Depending on exactly what you're doing you might want to look at
http://pgfoundry.org/projects/ip4r and see if that'll do what you need.
It's not a drop-in replacement, though, so would be a pain to use
with existing code.

Cheers,
  Steve



---(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] NULLs ;-)

[Scott Ribe]

> no cigar.

Well, duh. Showing why IS DISTINCT FROM is useful.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



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

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

Re: [GENERAL] backend crash following load command

[Tom Lane]

Martijn van Oosterhout  writes:
> Hmm? To upgrade libc.so you merely need to delete the old one and
> install the new one, there's no need to preserve the inode. The mmap()
> is private, but no, Linux does not keep a backup copy of the shared
> library if you overwrite it. The behaviour of overwriting the backing
> store of a private mapping is explicitly undefined.

Right, but isn't "cp -f" doing exactly that --- deleting the old one and
installing the new one?

[ experiments a bit... ]  Oh, that's interesting.  I was under the
impression that "cp -f" would always unlink the target file, but
on my machine (reasonably up-to-date Fedora 5, x86_64), this happens
only if it can't do open("foo", O_WRONLY|O_TRUNC).  If the existing
file is overwritable then there is no difference between cp and cp -f
... and *both* crash the backend.  If I "chmod -w" the .so file so that
cp -f is forced to unlink it first, then the backend does not crash!

This is at variance with what Merlin reported --- so I'm asking again
just what platform he's on.  He might want to strace cp to see whether
it's doing an unlink or not in his scenario.

Anyway, on my machine, the behavior is consistent with Martijn's theory.
I suspect the kernel is effectively unmapping the .so when the
overwrite occurs, and then dlsym() naturally SIGSEGV's while trying to
look into the mapped area.  If so, the early-PG_fini-lookup approach
wouldn't really fix anything.

The best solution for Merlin is probably to do "rm" then "cp" to install
a new version of the .so, instead of relying on "cp -f" to do it safely.

regards, tom lane

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

[GENERAL] Windows default editor for psql \e

[Richard Broersma Jr]

Does anyone know how to change the default editor from NOTEPAD to something 
link gVim? It would be
nice to use an editor with a little more smarts.

Regards,

Richard Broersma jr.



---(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] Windows default editor for psql \e

[Bruce Momjian]

Richard Broersma Jr wrote:
> Does anyone know how to change the default editor from NOTEPAD to something 
> link gVim? It would be
> nice to use an editor with a little more smarts.

Sure, see the psql manual page, but setting EDITOR is the easiest way. 
This works on Win32 too.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

   http://archives.postgresql.org/

Re: [GENERAL] Only MONO/WinForms is a way to go

[John DeSoi]

On Nov 28, 2006, at 2:05 PM, Tony Caduto wrote:

They are serious applications, but they don't exactly have a lot of  
forms and look how long Mozilla was in development.


I think the various interfaces in something like Thunderbird shows it  
can do all the standard GUI stuff pretty well.


The reason there is no highly productive IDE for Linux/Mac with a  
nice forms designer and robust data binding is because in the grand  
scheme of things there are not a lot of
desktop users for anything other than win32.  Sure there are lots  
of geeks that use Linux for their desktop, but not everyday users.
Everyday users are the ones companies etc want to make software for  
and Linux etc just does not have those kind of users yet.

The mac does, but they are small  in number
CodeGear(Borland devtools group) will make a IDE for Mac or Linux  
when they can make a viable return on investment.  They  
experimented with Kylix, but it failed because they initially  
priced it to high and many open source users will not pay even a  
reasonable amount for a IDE.




There are highly productive IDEs for the Mac with all the goodies you  
mention. But few are cross-platform.


Your statement about Windows desktop market share is correct, but it  
is not the relevant point. Many people are interested in cross- 
platform tools because they want to serve the Windows desktop market,  
but not have to give up Linux or OS X to do it.





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(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] Development of cross-platform GUI for Open Source DBs

[Ritesh Nadhani]

Hello All

Sorry for the late reply. Been a little busy with my assignments.

I will try to answer all the queries in this mail.

The reason I don't want to develop the project in wxWindows or a C/C++
based toolkit is that in the end I would be able to compile a binary
which will have least dependency and can be bundled for downloaded in
a single binary. With my experience while developing and selling
SQLyog, I came across many customers who were working on a slow dial
up connection for whom downloaded a 10MB package was also a pain. I
have had customers who just had plain vanilla Win98 machines and
SQLyog used to run great on it.

Another reason why suggested wxWidgets is because I have worked with
it before and I am comfortable with the system. As somebody suggested,
even wxPython looks good as Python greatly increases the speed of
implementation.

Also, IDEs like Delphi etc. are out of question as I cant afford to
buy the licenses.

I have no experience with XUL but it looks good. I am not sure, how
easy is to design GUI with lots of forms etc, with XUL. Writing an
initial prototype in XUL will make things more clear.

Even though I am not related to Webyog (developer of SQLyog) anymore,
I had started a project up there called SQLyog Max (which didn't work
due to time constraints rather then technological constraints) and we
even released one BETA release that had support for both MySQL and
PostgreSQL.

I believe developing an actual prototype would help our cause more
then just deciding upon which tool kit to use. My semester gets over
on 15th December. I plan to sit with it after that. Right now I am
thinking of an architecture which can support something like this.

If you have any idea how something like should be designed or
architectured, please provide me with your invaluable suggestions.

In other related question, my primary desktop of usage is Mac OS X but
I will be buying a Ubuntu box soon so expect decent development
parallely in Mac and Linux. I am not sure about Windows but if we
write correct wxWidgets/wxPython code, it should be a 0 issue to get
it compiled in Windows.

Ritesh

On 11/28/06, Rich Shepard <[EMAIL PROTECTED]> wrote:

On Tue, 28 Nov 2006, Merlin Moncure wrote:

> For a general purpose language, lately I've been taking a really good look
> at 'D', which looks to be an amazing language.  Has anybody tried to hook
> up postgresql to D?

   No, I haven't. But, if you want a cross-platform language and GUI toolkit,
consider Python and wxPython.

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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

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



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