[GENERAL] Money data type in PostgreSQL?

[David Garamond]

What do people recommend for storing money amounts? I've seen people use 
NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate 
and why? This is considering various existing currencies, some having 
low rates (like IDR, in which you can have large amount up to hundreds 
of trillions) and some high rates (like USD, in which you can have small 
amount like 0.1 cent). Are there places/industries which involve values 
lower than 0.1 cent?

And what about 'factor' field in currency conversion table? Should I use 
REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC 
(exact numbers). The factor should range between 1E-3 (e.g. converting 
IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros).

--
dave


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] DBD::Pg problem

[Ausrack Webmaster]

The thing is...I am not. I am inserting it into a varchar field.

$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),'$parent')";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(2, $to, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(3, $subject, {pg_type => DBD::Pg::PG_TEXT});
$sth->execute;

\d it_contact;
   Table "public.it_contact"
   Column   |Type |
Modifiers
+-+-
---
 contact_id | integer | not null default
nextval('public.it_contact_contact_id_seq'::text)
 email  | character varying(100)  |
 to_email   | character varying(100)  |
 subject| text|
 fname  | character varying(30)   |
 lname  | character varying(30)   |
 kafname| character varying(30)   |
 kalname| character varying(30)   |
 details| text|
 modify | timestamp without time zone |
 status | smallint|
 parent | integer |


Jason

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] DBD::Pg problem


pg_atoi is the string to int converter. You're trying to insert it into
an integer field.

On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
> Hi
>  
> I am trying to insert a simple email address into a text field,
> and I get the below error:
> 
> DBD::Pg::st execute failed: ERROR:  pg_atoi: error in
> "<[EMAIL PROTECTED]>": can't parse "<[EMAIL PROTECTED]>"
> 
> I figure it is because of the < and @ in the value, but why does it 
> take these as operators even when the value has single quotes around 
> it? I have even tried binding the values and PG_TEXT beforehand and 
> still not luck.
> 
> Any help would be greatly appreciated.
> 
> Jason Frisch
> 
> 
> 
> ---(end of 
> broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough 
> good men to do nothing." - Edmond Burke "The penalty good people pay 
> for not being interested in politics is to be governed by people worse

> than themselves." - Plato



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

[GENERAL] Transaction Question

[John Sidney-Woollett]

I have to convert an java web application currently using an Oracle DB
back end to one using a Postgres backend.

In Oracle much of the application logic is abstracted away from the java
middleware layer using stored procedures within the Oracle database. There
are certain features in Oracle that appear to be missing from Postgres
which are causing us some concern, and we wondered how much we're going to
have to butcher the backend and db stored procs.

Issue - nested transactions
=

Oracle provides the #pragma autonomous hint which allows you to declare
that a procedure/function will run in its own transaction and which can be
committed regardless of the final commit/rollback state of the enclosing
transaction.

This is an issue for us because some procedures make use of a function
which issues a row level lock on a table (select ... for update) in order
to read and then update a counter, and which then commits to release the
lock. The nested function returns the new counter value on return. We
cannot use Sequence objects, because the counter is tied directly to the
record which contains it, and there are any number of these record types.

We have the function being called by many threads simultaneously, and if
the lock is only released at the end of the enclosing transaction, then
the subsequent calls after the first will block until the first completes.
In other words, although threads are making calls in parallel, they will
only run serially because of the bottleneck.

I have seen a note about using separate connections/threads to resolve
this issue. There is NO possibility of our java middleware using two
threads/connections to separate out the transaction as the idea is that
the java makes one call to the database, and it handles all concurrency
issues (beautifully) without us having to embed db specific code/logic in
the middleware.

Is there a simple/elegant solution to this problem? And is there a good
document on dealing with concurrency issues - I have read the manual for
7.4 and while it describes the transaction isolation levels, and MVCC - it
doesn't really offer any practical tips or solutions to this problem.

Thanks for any info anyone can provide.

John Sidney-Woollett




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Money data type in PostgreSQL?

[Oliver Elphick]

On Wed, 2003-12-03 at 07:02, David Garamond wrote:
> What do people recommend for storing money amounts? I've seen people use 
> NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate 
> and why? This is considering various existing currencies, some having 
> low rates (like IDR, in which you can have large amount up to hundreds 
> of trillions) and some high rates (like USD, in which you can have small 
> amount like 0.1 cent). Are there places/industries which involve values 
> lower than 0.1 cent?

I think you should match the customer's data and use whatever precision
is necessary for it. The needs of a small shop will not be the same as a
currency trader's.

You should not regard amounts in different currencies as equivalent. 
You cannot add Euros to dollars and get a meaningful figure; so they
should not be in the same column.  If you are handling multiple
currencies, your database design needs to be a lot more sophisticated
than having a single money column.

> And what about 'factor' field in currency conversion table? Should I use 
> REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC 
> (exact numbers). The factor should range between 1E-3 (e.g. converting 
> IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros).

You should only use NUMERIC for money; any kind of floating point
representation will lose detail somewhere along the line.  (I suppose
you could use BIGINT for Japanese Yen.)

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "What shall we then say to these things? If God be for 
  us, who can be against us?"  Romans 8:31 


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

Re: [GENERAL] Money data type in PostgreSQL?

[David Garamond]

Oliver Elphick wrote:
You should not regard amounts in different currencies as equivalent. 
You cannot add Euros to dollars and get a meaningful figure; so they
should not be in the same column. 
I plan to store amount in a column (NUMERIC) and currency id in another 
(CHAR(3)). Plus another column for amount in 'standard' currency (e.g. 
USD; all addition/sum will be done to this column).

You should only use NUMERIC for money; any kind of floating point
representation will lose detail somewhere along the line.  (I suppose
you could use BIGINT for Japanese Yen.)
--
dave


---(end of broadcast)---
TIP 3: 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

[GENERAL] Cast text to bytea

[Alvar Freude]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

I want to change a column from text to bytea; since it seems that alter
table can't change the column type, i have to add a temporary column and
copy the data from the old one to the new, delete the old and rename the
new.


But it seems that Postgres can't cast text to bytea:

 odem=> UPDATE forum_gtree SET gid2=gid::bytea;
 ERROR:  Cannot cast type text to bytea


The same with casting only:

 odem=> SELECT 'abc'::text::bytea;
 ERROR:  Cannot cast type text to bytea
 odem=> SELECT '\000'::text::bytea;
 ERROR:  Cannot cast type text to bytea


How can I do this?

encode/decode seems to handle only bytea data:

  http://www.postgresql.org/docs/current/interactive/functions-string.html


And in http://www.postgresql.org/docs/current/static/datatype-binary.html I
also found no solution -- perhaps I missed something?


Thanks && Ciao
  Alvar


- -- 
** Alvar C.H. Freude -- http://alvar.a-blast.org/
**   Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
**   ODEM.org-Tour: http://tour.odem.org/
**   Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zZ/hOndlH63J86wRAmvoAJ4jwJp5R0nrmf8FzG9O599ED/8eCgCeOO2L
RpqleJNIv5RKvorYzh5+lo4=
=7mYs
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Transaction Question

[Richard Huxton]

On Wednesday 03 December 2003 08:08, John Sidney-Woollett wrote:
> I have to convert an java web application currently using an Oracle DB
> back end to one using a Postgres backend.
[snip]
> Issue - nested transactions
> =
>
[snip]
> This is an issue for us because some procedures make use of a function
> which issues a row level lock on a table (select ... for update) in order
> to read and then update a counter, and which then commits to release the
> lock. The nested function returns the new counter value on return. We
> cannot use Sequence objects, because the counter is tied directly to the
> record which contains it, and there are any number of these record types.

Can you elaborate on what this counter is/how you are using it? It sounds like 
the "counter" gets incremented regardless of whether an insert/update gets 
committed, which makes me wonder what it is counting.

> Is there a simple/elegant solution to this problem? And is there a good
> document on dealing with concurrency issues - I have read the manual for
> 7.4 and while it describes the transaction isolation levels, and MVCC - it
> doesn't really offer any practical tips or solutions to this problem.

Hmm - we don't seem to have any items dealing with concurrency issues on 
techdocs.postgresql.org, which is a shame since they are exactly the sort of 
thing benefit from having examples of pitfalls.

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] DBD::Pg problem

[Alex Satrapa]

Ausrack Webmaster wrote:
The thing is...I am not. I am inserting it into a varchar field.
Are there any single quotes in the message body?  They will wreak havoc 
with the rest of the query. And why are you putting single quotes around 
'$parent'?

What happens if you move the '$body' to the end:

$sql="insert into it_contact (email, to_email, 
subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')";

Alex

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

Re: [GENERAL] Cast text to bytea

[Alvar Freude]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

- -- Alvar Freude <[EMAIL PROTECTED]> wrote:

> But it seems that Postgres can't cast text to bytea:
> 
>  odem=> UPDATE forum_gtree SET gid2=gid::bytea;
>  ERROR:  Cannot cast type text to bytea

I tryed it with DECODE:

  odem=> UPDATE forum_gtree SET gid2=(DECODE(gid::text, 'escape'));
  ERROR:  decode: Bad input string for type bytea

hmmm ...


The text columns contains values from \x01 to \xff -- everything exept the
null byte.


So, hmmm, it seems I'm faster writing a small piece of Perl to convert this
then searching a solution ... ;)


Ciao
  Alvar

- -- 
** Alvar C.H. Freude -- http://alvar.a-blast.org/
**   Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
**   ODEM.org-Tour: http://tour.odem.org/
**   Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/za68OndlH63J86wRAmE/AKCCaQU/YDFNv6fnri6D7I6Q74ts7QCfcqE1
S+I44j/Lx3mots/pt5EWBJE=
=LVOG
-END PGP SIGNATURE-


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

Re: [GENERAL] DBD::Pg problem

[Ausrack Webmaster]

Tried that ...it is definetely the to_email field, not any others that
is causing 
the problem. 

Jason


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alex Satrapa
Sent: Wednesday, December 03, 2003 6:31 PM
To: Ausrack Webmaster
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] DBD::Pg problem


Ausrack Webmaster wrote:
> The thing is...I am not. I am inserting it into a varchar field.

Are there any single quotes in the message body?  They will wreak havoc 
with the rest of the query. And why are you putting single quotes around

'$parent'?

What happens if you move the '$body' to the end:

$sql="insert into it_contact (email, to_email, 
subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')";

Alex


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



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

Re: [GENERAL] DBD::Pg problem

[Richard Huxton]

On Wednesday 03 December 2003 09:43, Ausrack Webmaster wrote:
> Tried that ...it is definetely the to_email field, not any others that
> is causing
> the problem.

With the table schema you gave, the following seems to work fine for me. Only 
changes from your example are to remove quoting on $parent and let 
bind_param() sort out types by itself.


#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=DBNAMEHERE", "", "");

do_ins('alpha','beta','Blah1');
do_ins('[EMAIL PROTECTED]','[EMAIL PROTECTED]','Blah2');
do_ins('<[EMAIL PROTECTED]>','[EMAIL PROTECTED]','Blah3');
do_ins('<[EMAIL PROTECTED]>','<[EMAIL PROTECTED]>','Blah4');

$dbh->disconnect;
exit;

sub do_ins {
my ($from,$to,$subject) = @_;
my $body = 'BBB';
my $parent = 0;

print STDERR "Trying f/t = $from / $to\n";
$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),$parent)";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from);
$sth->bind_param(2, $to);
$sth->bind_param(3, $subject);
$sth->execute;
print STDERR "Ending f/t = $from / $to\n\n";
}



-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Accessing fields in RECORD data type using variables as field names

[Richard Huxton]

On Wednesday 03 December 2003 13:36, Alistair Hopkins wrote:
> Hi,
>
> I am trying to write a generic audit-trail trigger function which will
> record changes on a field-by-field basis to a single table for all audited
> tables.
>
> However, I find that I can only access a field in OLD and NEW if I know the
> name in advance.  Is there any way I can access the fields when I only have
> a variable containing the name of the field?
>
> If I can do this, I can make a really simple, schema-change-resistent, low
> storage size audit trail for high read / low change databases.

Not in plpgsql, but I believe you can in pl/tcl, which I think is quite 
mature.

-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Transaction Question

[Richard Huxton]

On Wednesday 03 December 2003 13:34, John Sidney-Woollett wrote:
> Richard
>
> You summized correctly. The field being updated is basically a sequence
> per volume. BUT the sequences cannot be shared across the volumes...

Why not? Are you worried about running out of numbers, or is there some other 
reason?

> I did wonder about "binding" one sequence object to each Volume record, and
> modifying the function so that the identified the volume to use, and then
> obtained the next value from the appropriate sequence object. Is it
> possible to do the following:
>
> --declare var to "hold" the sequence name
> vVolSeqName := "SEQ_VOLUME_1";
>
> --access the sequence from the name in the variable
> select nextval(vVolSeqName) into vFileSeq;

If nothing else, you could use EXECUTE and build your select in a string. See 
the manual for details of EXECUTE.

> If I cannot do this, can you suggest a solution to my original bottleneck
> issue. And also the problem of the sequencing number being rolled back in
> the event that the CreateFile function aborts or is rolled back.

A sequence number will never be rolled back. The sequence generator guarantees

> However, for me a bigger area of confusion is how to deal with concurrency
> issues in Postgres generally. Are there any good docs with examples of
> different scenarios?

Not as far as I know (if you would like to contribute some as you learn, I'm 
sure it would be appreciated). The isolation levels are listed with the 
corresponding standard SQL though, so it might be worth googling for a 
general reference.

-- 
  Richard Huxton
  Archonet Ltd

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

   http://archives.postgresql.org

Re: [GENERAL] Good open source mailing list system PHP / Postgresql

[Christopher Murtagh]

On Wed, 2003-12-03 at 11:08, Randal L. Schwartz wrote:
> > "Christopher" == Christopher Murtagh <[EMAIL PROTECTED]> writes:
> 
> Christopher>  3) Building templates with embedded code is much
> Christopher> easier/more intuitive in PHP than Perl.
> 
> Did you look at Apache::Template and Template-Toolkit?  The work in
> that area has really become a PHP killer for me.  If you did, and still
> have the opinion you have, I'd be curious.

 I did, but that was some time ago, I saw a talk about it when YAPC was
at McGill 2+(?) years ago. I was definitely impressed with it, and we
had considered moving to it (this was right around the time when we were
considering rebuilding in PHP or Perl). Also, at the time one of the big
reasons why it seemed interesting was because I hadn't done any PHP
programming, and I think version 4 was either still beta or really new
(I never would have considered moving from Perl to PHP 3.x).

Cheers,

Chris

-- 
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group 
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017

---(end of broadcast)---
TIP 3: 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] postgresql locks the whole table!

[Scott Ribe]

> My question is why??? The two insert operations do not
> conflict with each other (at least not in the
> real-world situation). Also, why does the foreign key
> make a difference?

I don't know if this would help, but given the other explanations you've
gotten I would try setting the foreign key constraint to deferrable, then at
the beginning of the transaction defer constraints. The reasoning being that
if the check is deferred until commit, maybe the lock won't be taken until
commit, thus the window of time during which your 2 example inserts could
conflict would be more like what you expect, a brief instant.


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


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

[GENERAL] C/C++ access with no postmaster running?

[Gregory Stone]

Is there a way from C++ to essentailly use postgresql as an embedded
database? I have a situation where I'd rather have a postgrtes daemon
running on my customer's machine except when my client application is up.
Preferably I'd like to not have to establish a network connection, and
hence use the daemon at all, to the datbase but just perform queries from
my c++ code. I've only used postgresql over the net, with a daemon, and
JDBC which is the traditional method so this is new territory. Can anyone
point me in the right direction in the docs?

Thanks in advance for the help.

-Gregory

=
--
~
Gregory Stone   |  "Suppose you were an idiot, and suppose you were

[EMAIL PROTECTED] |a member of congress; but I repeat myself."
|  - Mark Twain
~

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

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

Re: [GENERAL] C/C++ access with no postmaster running?

[Jonathan Bartlett]

PG only runs as a daemon.  However, you can connect  over UNIX-domain
sockets rather than Internet sockets if you want.

Jon

On Wed, 3 Dec 2003, Gregory Stone wrote:

> Is there a way from C++ to essentailly use postgresql as an embedded
> database? I have a situation where I'd rather have a postgrtes daemon
> running on my customer's machine except when my client application is up.
> Preferably I'd like to not have to establish a network connection, and
> hence use the daemon at all, to the datbase but just perform queries from
> my c++ code. I've only used postgresql over the net, with a daemon, and
> JDBC which is the traditional method so this is new territory. Can anyone
> point me in the right direction in the docs?
>
> Thanks in advance for the help.
>
> -Gregory
>
> =
> --
> ~
> Gregory Stone   |  "Suppose you were an idiot, and suppose you were
> [EMAIL PROTECTED] |a member of congress; but I repeat myself."
> |  - Mark Twain
> ~
>
> __
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>


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

   http://archives.postgresql.org

Re: [GENERAL] postgresql locks the whole table!

[Dr NoName]

> If you cannot make your transactons shorter (and
> please don't tell me 
> that you have user interaction going on while
> holding any open 
> transactions), then you might be able to increase
> your concurrency by 
> deferring the foreign key check until commit.

oh! my! gawd!!!
THANK YOU!

__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

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

Re: [GENERAL] postgresql locks the whole table!

[Dr NoName]

> If you cannot make your transactons shorter (and
> please don't tell me 
> that you have user interaction going on while
> holding any open 
> transactions), then you might be able to increase
> your concurrency by 
> deferring the foreign key check until commit.

oh! my! gawd!!!
THANK YOU! The deferred foreign key checks are exactly
what I needed. They are quite useful for other reasons
too. I think that should be the default for foreign
keys. Interestingly, the severe concurrency
degradation caused by immediate foreign key checks is
not explained in any of the documentation I looked at.

btw, there is no user interaction during the
transaction, just a lot of CPU- and IO-intensive
processing.

thanks,

Eugene

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Re: [GENERAL] postgresql locks the whole table!

[Jan Wieck]

Dr NoName wrote:

If you cannot make your transactons shorter (and
please don't tell me 
that you have user interaction going on while
holding any open 
transactions), then you might be able to increase
your concurrency by 
deferring the foreign key check until commit.
oh! my! gawd!!!
THANK YOU! The deferred foreign key checks are exactly
what I needed. They are quite useful for other reasons
too. I think that should be the default for foreign
The way it is is the way it is defined by the standard.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Feature Request for 7.5

[Keith C. Perry]

Jan,

To continue the brain-dump.  I was curious how the GC protocol was going to be
implemented (if you had any ideas thus far).

Several years ago, I started working on a network security and intrusion
detection system for a client where the audit/logging system needed to be
redundant- they wanted 3 servers each on a different LANs in fact.

The work in that design was centered around making sure that the aggregated data
set was exactly the same on each of the 3 servers.  Not only were all the
event timestamps the same but the events were ordered the same way in the logs.

The solution I was working on was a multicast IPv4 (possibly IPv6) network where
the "packet" of information had an id of some sort and the event data inside the
datagram had a timestamp (of course).

The obviously problem is that multicasting is not reliable so in order sure all
event were on all servers, there would be a periodic polling that would give a
server with say 2 missing event the chance to "catch-up".  This catch-up"
function make sure all events were ordered an had the same last event.  This
would be much more of an issue with the server a couple of hops away than with a
server on the same LAN.  The client never went ahead with the system so I
apologize for not having some reference examples.

This is totally different from what true replication is amongst a group of
database servers but it seems to me that if the servers are in multicast group,
at least transactions would be theoretically sent to all servers at the same
time.  I would think that a homogenous system of servers is already ordering
events the same way so transactions would occur properly unless it was missed. 
A "catch-up" function here was be difficult to implement because if the servers
are committing asyncronously then you can't catch-up and one of your datasets
has lost integrity.  Syncronously (meaning, "we'll all commit now because we all
 agree on the current list of transactions") seems a bit messy and not as scalable.

I didn't mean to get into all that but how the GC is going to work in this
project is something that I'm curious about.


-- 
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

Quoting Jan Wieck <[EMAIL PROTECTED]>:

> The following is more or less a brain-dump ... not finally thought out 
> and not supposed to be considered a proposal at this time.
> 
> The synchronous multi-master solution I have in mind needs a few 
> currently non existent support features in the backend. One is 
> non-blocking locks and another one is a callback mechanism just before 
> marking a transaction in clog as committed.
> 
> It will use reliable group communication (GC) that can guarantee total 
> order. There is an AFTER trigger on all replicated tables. A daemon 
> started for every database will create a number of threads/subprocesses. 
> Each of these workers has his separate DB connection and is a member of 
> a different group in the GC. The number of these groups determines the 
> maximum number of concurrent UPDATE-transactions, the cluster can handle.
> 
> At the first call of the trigger inside of a transaction (this is the 
> first modifying statement), the trigger allocates one of the replication 
> groups (possibly waiting for one to become free). It now communicates 
> with one daemon thread on every database in the cluster. The triggers 
> now send the replication data into this group. It is not necessary to 
> wait for the other cluster members as long as the GC guarantees FIFO by 
> sender.
> 
> At the time the transaction commits, it sends a commit message into the 
> group. This message has another service type level which is total order. 
> It will wait now for all members in the replication group to reply with 
> the same. When every member in the group replied, all agreed to commit 
> and are just before stamping clog.
> 
> Since the service type is total order, the GC guarantees that either all 
> members get the messages in the same order, or if one cannot get a 
> message a corresponding LEAVE message will be generated. Also, all the 
> replication threads will use non-blocking locking. If any of them ever 
> finds a locked row, it will send an ABORT message into the group, 
> causing the whole group to roll back.
> 
> This way, either all members of the group reach the "just before 
> stamping clog" state together and know that everyone got there, or they 
> will get an abort or leave message from any of their co-workers and roll 
> back.
> 
> There is a gap between reporting "ready" and really stamping clog in 
> which a database might crash. This will cause all other cluster members 
> to go ahead and commit while the crashed DB does not commit. But this is 
> limited to crashes only and a restarting database must rejoin/resync 
> with the cluster anyway and doubt its own data. So this is not really a 
> problem.
> 
> 
> With this synchronous model, read only transactions can be handled o

[GENERAL] bytea, index and like operator

[Alvar Freude]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

the following I posted already on pgsql-bugs -- perhaps someone has a good
workaround or fix or can say me that I'm wrong?

There seems to be a bug in handling bytea columns with index and the
like-operator.

When an index scan on a bytea column is active, a query with "like" and "%"
in the search doesn't give the correct result: it finds always 0 rows. 

At least in 7.3.4. When the FreeBSD Port for 7.4 is ready (yes, why isn't
it ready?), I'll test this again ... ;-)


Look the test here:


  select version();
   version
  -
   PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4


  begin;
  create table test (b bytea);
  create index tst_idx on test(b);
  insert into test values ('\001abc\006');
  insert into test values ('\001xabc\006');
  insert into test values ('\001\002abc\006');
  insert into test values ('\000\001\002abc\006');
  insert into test values ('\002\003abc\006');

  select * from test where b like '\001%';


  Result:
  
   b
  ---
  (0 Zeilen) [0 rows]


  explain analyze select * from test where b like '\001%';
  QUERY PLAN
  
   Index Scan using tst_idx on test  (cost=0.00..17.07 rows=5 width=32)  
   (actual time=0.04..0.04 rows=0 loops=1)
 Index Cond: (b = '0'::bytea)
 Filter: (b ~~ '\\001%'::bytea)
   Total runtime: 0.14 msec


But with seq scan (after vacuuming, creating index later, dropping the
index...) it works as expected:

  drop index tst_idx;
  online_demo=> select * from test where b like '\001%';
  b
  -
   \001abc\006
   \001xabc\006
   \001\002abc\006
  (3 Zeilen)


  explain analyze select * from test where b like '\001%';
  QUERY PLAN
  
   Seq Scan on test  (cost=0.00..22.50 rows=5 width=32) (actual 
time=0.05..0.08 rows=3 loops=1)
 Filter: (b ~~ '\\001%'::bytea)
   Total runtime: 0.16 msec


hmmm ...

It seems, that bytea is no good idea for production use?


Ciao
  Alvar



- -- 
** Alvar C.H. Freude -- http://alvar.a-blast.org/
**   Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
**   ODEM.org-Tour: http://tour.odem.org/
**   Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zkjvOndlH63J86wRAiNGAKCM/PQL1HxJj55WI0ZaUnk/wFazXgCggdIK
N1CiyG/+HtFT4lp4pZpfSD4=
=fa7q
-END PGP SIGNATURE-


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Upgrading from 7.2.3 to....??

[Vivek Khera]

> "CM" == Carlos Moreno <[EMAIL PROTECTED]> writes:

CM> What would be your advice?  Is 7.3.4 the recommended one
CM> if we need reliability?  Or has 7.4 earned sufficient
CM> trust by now?

CM> Also, how about back compatibility?  Should I expect some
CM> trouble?  I remember with one of the previous upgrades

I just did that switch from 7.2 over to 7.4 this past weekend.  It
went very smoothly.  The major things to watch for that I found in the
history/release notes which were not very obvious were:

1) implicit converstion of empty string to numeric 0 is no longer
   there.  be sure you either specify numeric 0 or string '0' when you
   mean that.

2) if you have default 'NOW()' (in quotes!) you have to change that to
   default NOW() without quotes.  Similar for other date/time default
   thingies.

3) 7.2 used to nicely order your group by's implicitly.  this is no
   longer the case -- you must explicitly list any ordering you wish
   imposed on group by's.

I think that was it.  The speed seems to be faster, but that could
also be due to moving from a 4-disk RAID10 to a 14 disk RAID5 with
faster spindles ;-)

This is running my production site which does a *lot* of
inserts/updates daily.  I'm very happy with it.  You will be to :-)


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED]   Rockville, MD   +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Feature Request for 7.5

[Jan Wieck]

Keith C. Perry wrote:

Jan,

To continue the brain-dump.  I was curious how the GC protocol was going to be
implemented (if you had any ideas thus far).
The stuff I've been kicking around so far is Spread 3.17.1 with a simple 
self made Tcl/Tk binding library used from inside PG via PL/TclU. You 
can download the Spread toolkit here:

http://www.spread.org

What I have is way too premature to be shown, so don't ask. But it has 
replicated one UPDATE once, so it is a bit more than a pure braindump.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 3: 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] Misplaced modifier in Postgresql license

[Bruce Momjian]

Tom Lane wrote:
> "Chris Travers" <[EMAIL PROTECTED]> writes:
> > Also, I am a little confused by Tom's statement that we don't have the right
> > to modify the license.
> 
> I don't see what's confusing about it.  Our implicit contract with
> contributors (past and present) is that we'd distribute their work under
> the terms of the same license they saw when they contributed.  Altering
> the license without their agreement is breaking faith.
> 
> All of the arguments about license changes have been gone over in great
> detail in the archives (I think the last major go-round on the topic was
> in the summer of 2000).  No one who has been around long enough to
> remember those flame wars is interested in re-opening the topic.  Not
> even just to move a comma.

What we could do is add a blurb on our web site or in the FAQ clarifying
this issue.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Upgrading from 7.2.3 to....??

[Eric Soroos]

On Dec 3, 2003, at 1:05 PM, Vivek Khera wrote:

"CM" == Carlos Moreno <[EMAIL PROTECTED]> writes:
CM> What would be your advice?  Is 7.3.4 the recommended one
CM> if we need reliability?  Or has 7.4 earned sufficient
CM> trust by now?
CM> Also, how about back compatibility?  Should I expect some
CM> trouble?  I remember with one of the previous upgrades
I just did that switch from 7.2 over to 7.4 this past weekend.  It
went very smoothly.  The major things to watch for that I found in the
history/release notes which were not very obvious were:
...
The one thing that I've run across in that upgrade path is with 
triggers. If you use:

Create or replace function trigger_foo()
returns opaque
...
It's going to change the function to return a trigger.
If you subsequently use the create or replace syntax and don't change 
the return type to trigger (because you haven't updated your code), the 
back end will refuse to replace the function complaining of a mismatch 
in return types.

eric

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

Re: [GENERAL] postgresql locks the whole table!

[Scott Ribe]

> The deferred foreign key checks are exactly
> what I needed. They are quite useful for other reasons
> too.

I believe Postgres is just following standards.

Yes, deferred is very useful for other things, like a real data model layer
mediating between UI and database--without it you have to worry about
performing inserts (and updates) in a particular order. That can be really
painful to code, and in some cases (cyclic relationships) impossible to do
except by leaving some constraints out.


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


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

[GENERAL] [Fedora Core 1] yum repositories with 7.4?

[Boris Popov]

Hello pgsql-general,

It appears 7.4 is not published in all the usual places so I was
wondering if there's a yum repository with updated packages available
and if not then what's the approach that will cause the least amount
of pain? I do realize I'll need to dump/restore, just want some expert
advice on how to upgrade the installation without breaking it.

Thanks!

--
-Boris



---(end of broadcast)---
TIP 3: 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

[GENERAL] Pronouncing PostgreSQL

[John Wells]

Ok...this has irritated me for sometime.  I've seen reference to dividing
it up into proper syllables in the mailing archives, but couldn't find
pronunciation of the "gres" part...

Is it:

Post - grease - queue - el

Post - greee - es - queue - el

or

Post - gress - queue - el (as in impress)

Thanks,

Johnn



---(end of broadcast)---
TIP 3: 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] Pronouncing PostgreSQL

[John Wells]

Yeah... I found a link to the sound file in a archived message, but it's
404 now...

John

Dann Corbit said:
> I always say:
>
> Pigee - squeal.
>
> At least it's easy to remember.
>
> None of the below exactly roll off the tongue.  I seem to remember a
> site that had a sound file attachment for how to pronounce PostgreSQL,
> but I can't remember where I saw it.
>
>> -Original Message-
>> From: John Wells [mailto:[EMAIL PROTECTED]
>> Sent: Wednesday, December 03, 2003 4:43 PM
>> To: [EMAIL PROTECTED]
>> Subject: [GENERAL] Pronouncing PostgreSQL
>>
>>
>> Ok...this has irritated me for sometime.  I've seen reference
>> to dividing it up into proper syllables in the mailing
>> archives, but couldn't find pronunciation of the "gres" part...
>>
>> Is it:
>>
>> Post - grease - queue - el
>>
>> Post - greee - es - queue - el
>>
>> or
>>
>> Post - gress - queue - el (as in impress)
>>
>> Thanks,
>>
>> Johnn
>>
>>
>>
>> ---(end of
>> broadcast)---
>> TIP 3: 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
>>
>


---(end of broadcast)---
TIP 3: 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] Pronouncing PostgreSQL

[Dann Corbit]

I always say:

Pigee - squeal.

At least it's easy to remember.

None of the below exactly roll off the tongue.  I seem to remember a
site that had a sound file attachment for how to pronounce PostgreSQL,
but I can't remember where I saw it.

> -Original Message-
> From: John Wells [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, December 03, 2003 4:43 PM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] Pronouncing PostgreSQL
> 
> 
> Ok...this has irritated me for sometime.  I've seen reference 
> to dividing it up into proper syllables in the mailing 
> archives, but couldn't find pronunciation of the "gres" part...
> 
> Is it:
> 
> Post - grease - queue - el
> 
> Post - greee - es - queue - el
> 
> or
> 
> Post - gress - queue - el (as in impress)
> 
> Thanks,
> 
> Johnn
> 
> 
> 
> ---(end of 
> broadcast)---
> TIP 3: 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
> 

---(end of broadcast)---
TIP 3: 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] Pronouncing PostgreSQL

[Chris Stokes]

I use your third pronunciation

Regards,
Chris Stokes
Senior Systems Consultant
Bass Software
[EMAIL PROTECTED]  
Main +613 8415 9300
Direct   +613 8415 9305


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of John Wells
Sent: Thursday, 4 December 2003 11:43 AM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Pronouncing PostgreSQL


Ok...this has irritated me for sometime.  I've seen reference to dividing
it up into proper syllables in the mailing archives, but couldn't find
pronunciation of the "gres" part...

Is it:

Post - grease - queue - el

Post - greee - es - queue - el

or

Post - gress - queue - el (as in impress)

Thanks,

Johnn



---(end of broadcast)---
TIP 3: 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Pronouncing PostgreSQL

[Craig O'Shannessy]

Me too.

On Thu, 4 Dec 2003, Chris Stokes wrote:

> I use your third pronunciation
> 
> Regards,
> Chris Stokes
> Senior Systems Consultant
> Bass Software
> [EMAIL PROTECTED]  
> Main +613 8415 9300
> Direct   +613 8415 9305
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of John Wells
> Sent: Thursday, 4 December 2003 11:43 AM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] Pronouncing PostgreSQL
> 
> 
> Ok...this has irritated me for sometime.  I've seen reference to dividing
> it up into proper syllables in the mailing archives, but couldn't find
> pronunciation of the "gres" part...
> 
> Is it:
> 
> Post - grease - queue - el
> 
> Post - greee - es - queue - el
> 
> or
> 
> Post - gress - queue - el (as in impress)
> 
> Thanks,
> 
> Johnn
> 
> 
> 
> ---(end of broadcast)---
> TIP 3: 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
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 


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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Pronouncing PostgreSQL

[Alex Satrapa]

John Wells wrote:
Ok...this has irritated me for sometime.  I've seen reference to dividing
it up into proper syllables in the mailing archives, but couldn't find
pronunciation of the "gres" part...
FOLDOC (Free Online Dictionary of Computing) says:

PostgreSQL
/'post-gres-kyu-el/ An enhancement of the
   POSTGRES database system.
Though I tend to pronounce that last syllable as "ell" not "el".  The 
difference is one of those trivial matters which Alexander Pope observed 
are responsible for starting the greatest conflicts.

"you will go to *Dell* if you don't print my *label*".

Alex

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Pronouncing PostgreSQL

[Joshua D. Drake]

I just say postgres

Dann Corbit wrote:

I always say:

Pigee - squeal.

At least it's easy to remember.

None of the below exactly roll off the tongue.  I seem to remember a
site that had a sound file attachment for how to pronounce PostgreSQL,
but I can't remember where I saw it.
 

-Original Message-
From: John Wells [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 03, 2003 4:43 PM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Pronouncing PostgreSQL

Ok...this has irritated me for sometime.  I've seen reference 
to dividing it up into proper syllables in the mailing 
archives, but couldn't find pronunciation of the "gres" part...

Is it:

Post - grease - queue - el

Post - greee - es - queue - el

or

Post - gress - queue - el (as in impress)

Thanks,

Johnn



---(end of 
broadcast)---
TIP 3: 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

   

---(end of broadcast)---
TIP 3: 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
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL


---(end of broadcast)---
TIP 3: 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] Pronouncing PostgreSQL

[Marc G. Fournier]

Post - gress - Q - L

On Wed, 3 Dec 2003, John Wells wrote:

> Ok...this has irritated me for sometime.  I've seen reference to dividing
> it up into proper syllables in the mailing archives, but couldn't find
> pronunciation of the "gres" part...
>
> Is it:
>
> Post - grease - queue - el
>
> Post - greee - es - queue - el
>
> or
>
> Post - gress - queue - el (as in impress)
>
> Thanks,
>
> Johnn
>
>
>
> ---(end of broadcast)---
> TIP 3: 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
>


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 3: 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] Pronouncing PostgreSQL

[Marc G. Fournier]

On Thu, 4 Dec 2003, Craig O'Shannessy wrote:

> Me too.

Its the way I've always pronounced it ...


>
> On Thu, 4 Dec 2003, Chris Stokes wrote:
>
> > I use your third pronunciation
> >
> > Regards,
> > Chris Stokes
> > Senior Systems Consultant
> > Bass Software
> > [EMAIL PROTECTED] 
> > Main +613 8415 9300
> > Direct   +613 8415 9305
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Behalf Of John Wells
> > Sent: Thursday, 4 December 2003 11:43 AM
> > To: [EMAIL PROTECTED]
> > Subject: [GENERAL] Pronouncing PostgreSQL
> >
> >
> > Ok...this has irritated me for sometime.  I've seen reference to dividing
> > it up into proper syllables in the mailing archives, but couldn't find
> > pronunciation of the "gres" part...
> >
> > Is it:
> >
> > Post - grease - queue - el
> >
> > Post - greee - es - queue - el
> >
> > or
> >
> > Post - gress - queue - el (as in impress)
> >
> > Thanks,
> >
> > Johnn
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 3: 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
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

Re: [GENERAL] Transaction Question

[Scott Ribe]

You could do something with threads on the backend, invisible to your Java
middleware. I don't have enough experience to feel confident about trying to
evaluate the pros and cons of (possibly) different ways of doing this. But
given that you can write functions in C and load them into Postgres so that
they can be called from plpgsql, I think you could in essence add to
Postgres a function which when called would hand off the sequence load &
update on a separate thread/connection, wait for its commit and completion,
and return the value. Of course you still have to be careful about
concurrency issues with this approach, so that you don't wind up with the 2
threads deadlocked.

That may well strike you as a gross hack. I don't particularly like it
either, but I think it would get the job done without requiring any changes
to your current code base except for the rewrite of GetVolumeFileReference.

BTW, in reference to other suggestions: I believe that a sequence name is
indeed just a string, so you can build the name and pass it to sequence
functions on the fly; I know that sequences do not roll back, once a value
is issued it is "burned" regardless of whether the enclosing transaction
commits or not. So you should be able to have a trigger that on insert of a
WDVolume row creates a corresponding sequence, then use that sequence within
GetVolumeFileReference. Whether this is a good idea depends I'm sure on how
many WDVolumes you'll have. I feel confident that dozens or hundreds would
be no problem; thousands I don't have any idea; millions I wouldn't try.


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


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

[GENERAL] PostgreSQL 7.3.5 Now Available ...

[Marc G. Fournier]

After several fixes were backpatches to the 7_3_STABLE branch, we have now
released a 7.3.5.  As the list of Changes since 7.3.4 is quite small, they
are included in this email:

 * Force zero_damaged_pages to be on during recovery from WAL
 * Prevent  some  obscure  cases  of  "variable not in subplan target
   lists"
 * Force  stats  processes  to  detach  from  shared memory, ensuring
   cleaner shutdown
 * Make PQescapeBytea and byteaout consistent with each other (Joe)
 * Added missing SPI_finish() calls to get_tuple_of_interest() (Joe)
 * Fix  for  possible foreign key violation when rule rewrites INSERT
   (Jan)
 * Support qualified type names in PL/Tcl's spi_prepare command (Jan)
 * Make  pg_dump  handle  a  procedural  language  handler located in
   pg_catalog
 * Make  pg_dump  handle  cases  where a custom opclass is in another
   schema
 * Make pg_dump dump binary-compatible casts correctly (Jan)
 * Fix  insertion  of  expressions  containing  subqueries  into rule
   bodies
 * Fix  incorrect  argument  processing  in  clusterdb  script (Anand
   Ranganathan)
 * Fix problems with dropped columns in plpython triggers
 * Repair  problems  with  to_char()  reading  past  end of its input
   string (Karel)
 * Fix GB18030 mapping errors (Tatsuo)
 * Fix  several problems with SSL error handling and asynchronous SSL
   I/O
 * Remove  ability  to bind a list of values to a single parameter in
   JDBC (prevents possible SQL-injection attacks)
 * Fix some errors in HAVE_INT64_TIMESTAMP code paths
 * Fix  corner case for btree search in parallel with first root page
   split

This version, as with most minor versions, does not require a dump/reload
to put into place.

As always, this release is available on all our mirrors, viable at:

http://www.postgresql.org/mirrors-www.html

And, thanks to David Fetter, is also available via BitTorrent at:

http://bt.postgresql.org


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Pronouncing PostgreSQL

[Williams, Travis L, NEO]

They're used to be a sound bite on postgres.org on how to pronounce it
properly.. any idea where that went?

Travis

-Original Message-
From: Marc G. Fournier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 7:46 PM
To: Craig O'Shannessy
Cc: Chris Stokes; John Wells; [EMAIL PROTECTED]
Subject: Re: [GENERAL] Pronouncing PostgreSQL


On Thu, 4 Dec 2003, Craig O'Shannessy wrote:

> Me too.

Its the way I've always pronounced it ...


>
> On Thu, 4 Dec 2003, Chris Stokes wrote:
>
> > I use your third pronunciation
> >
> > Regards,
> > Chris Stokes
> > Senior Systems Consultant
> > Bass Software
> > [EMAIL PROTECTED] 
> > Main +613 8415 9300
> > Direct   +613 8415 9305
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] Behalf Of John Wells
> > Sent: Thursday, 4 December 2003 11:43 AM
> > To: [EMAIL PROTECTED]
> > Subject: [GENERAL] Pronouncing PostgreSQL
> >
> >
> > Ok...this has irritated me for sometime.  I've seen reference to
dividing
> > it up into proper syllables in the mailing archives, but couldn't
find
> > pronunciation of the "gres" part...
> >
> > Is it:
> >
> > Post - grease - queue - el
> >
> > Post - greee - es - queue - el
> >
> > or
> >
> > Post - gress - queue - el (as in impress)
> >
> > Thanks,
> >
> > Johnn
> >
> >
> >
> > ---(end of
broadcast)---
> > TIP 3: 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
> >
> > ---(end of
broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
[EMAIL PROTECTED])
> >
>
>
> ---(end of
broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>


Marc G. Fournier   Hub.Org Networking Services
(http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ:
7615664

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

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