Re: [GENERAL] are cursors necessary?

[Jan Wieck]

Richard Huxton wrote:

On Thursday 04 December 2003 22:46, Mark Harrison wrote:
res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from
pg_database"); res = PQexec(conn, "FETCH ALL in myportal");
res = PQexec(conn, "CLOSE myportal");
res = PQexec(conn, "END");

Is there any value in my own query-only programs to declaring the cursor
for each search?
Well - if you want to scroll forward/backward through the resultset, you'd 
want a cursor. Or, if your client had limited memory and the resultset was 
large you might want to do so. PG will return all rows at once, so if your 
SELECT returns 5 million rows you'll use a lot of RAM on the client side.

You really think people would ever want to store more than 640 rows?

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

[Jan Wieck]

Graham Leggett wrote:

Christopher Browne wrote:

But to get the pennies right on a $10,000 USD transaction converted
into GBP (UK Pounds), you need all the official precision that there
is.  And if your calculation is off by 4 cents, some of those
accounting folk are liable to thrash you mercilessly over it.  If you
get calculations WRONG, they get really uncomfortable, and want to
know why.
What I have done is store the currency amounts as bigints, at the same 
precision defined for the currency (ie cents for dollars, pence for 
pounds, etc). This guarantees that you don't get any rounding errors 
when storing the figures as a floating point type. When manipulating the 
numbers, I use Java BigDecimals, which don't lose any precision either, 
and convert back to bigints to store in the database.
You won't get any rounding errors in NUMERIC either. What people should 
be concerned of is to find an arbitrary precision package for the 
frontend programming language they're using.

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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] are cursors necessary?

[Richard Huxton]

On Thursday 04 December 2003 22:46, Mark Harrison wrote:
>   res = PQexec(conn, "BEGIN");
>   res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from
> pg_database"); res = PQexec(conn, "FETCH ALL in myportal");
>   res = PQexec(conn, "CLOSE myportal");
>   res = PQexec(conn, "END");

> Is there any value in my own query-only programs to declaring the cursor
> for each search?

Well - if you want to scroll forward/backward through the resultset, you'd 
want a cursor. Or, if your client had limited memory and the resultset was 
large you might want to do so. PG will return all rows at once, so if your 
SELECT returns 5 million rows you'll use a lot of RAM on the client side.

-- 
  Richard Huxton
  Archonet Ltd

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

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

Re: [GENERAL] Triggers, Stored Procedures, PHP. was: Re: PostgreSQL

[scott.marlowe]

On Tue, 2 Dec 2003, Randolf Richardson wrote:

> >>In dealing with web applications and frontends to database or
> >>even just a dynamic web site PHP has every bit the power and ability that
> >>Java does and the development time is way down.
> > 
> > Uh, how about threads.  I know that you don't need them much but it sure
> > would be nice to be able to do background processing.
> [sNip]
> 
>   PHP doesn't support threads?  I've always thought of Forking as 
> overkill where threads are light and elegant.

True on some platforms (Windows, Solaris) untrue on others (Linux, 
possibly BSD) where threads are not much lighter than processes.

PHP doesn't have thread support.


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

[GENERAL] are cursors necessary?

[Mark Harrison]

In the program testlibpq.c, these five SQL statements are executed:

res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
res = PQexec(conn, "FETCH ALL in myportal");
res = PQexec(conn, "CLOSE myportal");
res = PQexec(conn, "END");
Is this just to illustrate how to create transactions and cursors, or is there
some material difference between trimming the program down to just:
	res = PQexec(conn, "select * from pg_database");

Is there any value in my own query-only programs to declaring the cursor
for each search?
Many TIA,
Mark
--
Mark Harrison
Pixar Animation Studios
---(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] Making a tree with "millions and millions" of dynamic nodes

[Arjen van der Meijden]

> Christian Fowler wrote:
>
> So it seems Materialized Path is my only option, however I am 
> concerned about LIKE performance for the right hand side of 
> the tree, where the path is 8digits x 6 levels = 48 chars. 
> Should I be concerned? I need split-second real-time 
> performance, and can't imagine it will be as fast the Nested 
> Set arithmatic approach.  

Perhaps the contrib/ltree-stuff is interesting if you're going to do
materialized paths.
It's an indexable tree-format for postgresql based on the materialized
paths (so it seems at least).

I haven't really tested it, but wanted to point it out to you.

Regards,

Arjen van der Meijden




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

Re: [GENERAL] Making a tree with "millions and millions" of dynamic

[Joe Conway]

Alvar Freude wrote:

I want to change this to bytea to avoid base255 encoding and the character
set problems, but there are still some Bugs with bytea and the like
operator. :-(
Be patient, I'm working on it. Apparently you are the first person to 
ever really try to *use* the like operator for bytea, because the bug 
has been there since the feature was originally committed.

Joe



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

[Robert Treat]

On Wed, 2003-12-03 at 17:06, Bruce Momjian wrote:
> 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.
> 

Oh, you mean like this paragraph 

"The above is the BSD license, the classic open-source license. It has
no restrictions on how the source code may be used. We like it and have
no intention of changing it."

Which you added to the FAQ damn near two years ago !?!

http://developer.postgresql.org/cvsweb.cgi/pgsql-server/doc/FAQ.diff?r1=1.139&r2=1.140


Quite frankly I think the lawyer they spoke with was... well, this is a
family newsgroup so let's just say I don't feel his opinion is very
credible.  It would have been very easy for them to indemnify themselves
had they wanted to, to not even try to sort this out speaks of some
other agenda on their part IMHO. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(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] Invalid page header

[Martijn van Oosterhout]

Looks bad. Have you got backups. Seriously!

REINDEX works on system indexes but you have to drop to single user mode in
postgres to do it. Check out the -P option in the manpage.

Good luck!

Hope this helps,

On Thu, Dec 04, 2003 at 12:54:07PM -0700, Ed L. wrote:
> I have a server with 20 pgsql clusters on it.  I am seeing a slew of errors 
> like the following across 5 of these clusters, all running 7.3.4 on Linux 
> with kernel 2.4.18-4bigmem:
> 
>   ERROR:  Invalid page header in block N of R
> 
> N and R vary among block numbers and relation names, respectively.  Looking 
> at the archives, it sounds like a possible hardware issue, and we're 
> investigating that.  In the meantime, every query that attempts to access 
> those blocks errors out.
> 
> Taking the server down or losing data is very expensive, so I'm looking for 
> some short-term band-aids to re-enable full production access and recover 
> data while we sort out the hardware issues.  When the corrupted block is 
> within a user index, I've been able to fix the corruption by dropping and 
> recreating the index.  Not sure what I can do for user tables yet.  
> Preparing to run e2fsck/badblocks after I'm convinced there's nothing more 
> to salvage.
> 
> Is there anything I can do for invalid page header in a block of 
> pg_depend_reference_index?
> 
> Other suggestions?
> 
> TIA.
> 
> Ed
> 
> 
> 
> ---(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


pgp0.pgp
Description: PGP signature

Re: [GENERAL] bug in 7.4

[Greg Stark]

Alexander S <[EMAIL PROTECTED]> writes:

> Data in mytable doesn t  order  in alphabetical  russian order

It won't order in russian order unless the database was initdb'd with a
russian locale.

> For db in KOI8 encoding - all looks right.

The encoding is irrelevant.

-- 
greg


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

Re: [GENERAL] Money data type in PostgreSQL?

[Graham Leggett]

Christopher Browne wrote:

But to get the pennies right on a $10,000 USD transaction converted
into GBP (UK Pounds), you need all the official precision that there
is.  And if your calculation is off by 4 cents, some of those
accounting folk are liable to thrash you mercilessly over it.  If you
get calculations WRONG, they get really uncomfortable, and want to
know why.
What I have done is store the currency amounts as bigints, at the same 
precision defined for the currency (ie cents for dollars, pence for 
pounds, etc). This guarantees that you don't get any rounding errors 
when storing the figures as a floating point type. When manipulating the 
numbers, I use Java BigDecimals, which don't lose any precision either, 
and convert back to bigints to store in the database.

YMMV.

Regards,
Graham
--
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] query and pg_dump problem on my postgresql 6.5.3/Redhat 6.2

[Richard Huxton]

On Thursday 04 December 2003 14:55, 吴德文 wrote:
> Help!
>
> A few days ago, my php page began to complain this:
> --
> Warning: PostgresSQL query failed: pqReadData() -- backend closed the
> channel unexpectedly. This probably means the backend terminated abnormally
> before or while processing the request.
[snip]
> NOTE:
> I'm on Redhat 6.2 with Postgresql 6.5.3, the database named "news",
> and the table is "newses", looks like this (dumped from "pg_dump -s -t
> newses news"):

One of the developers will probably be able to help, but bear in mind many are 
in the USA/Canada and so you might have time-zone delays. It will be 
suggested you upgrade to 7.3.5 or 7.4.0 as soon as possible. That might mean 
upgrading from RedHat 6.2 as well.

At present:
1. Dump all the other tables, if you can
2. Stop PostgreSQL 
3. make a file backup of /var/data (or wherever your data is stored)

OK - now at least you know things can't get any worse.

In psql you can use \a to set unaligned output and \o  to output 
query results to a file. You can then try SELECT * FROM newses WHERE news_id 
BETWEEN 1 AND 100, then 101-200 etc. This should let you recover a great deal 
of your data if only one disk-block is damaged.

>From what you say, you should be able to recover your table's data. Then, I'd 
recreate the database from your dumps.

> But I found that pg_dump sometimes does not work on that very table, and
> sometimes work with a long long time then error.

This sounds like either a disk or memory error. I'd guess disk.

-- 
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Making a tree with "millions and millions" of dynamic

[Alvar Freude]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

- -- "Joe \"Nuke Me Xemu\" Foster" <[EMAIL PROTECTED]> wrote:

> Here's an alternative which may not perform very well but may
> still be better than risking a full table-scan...

I use exactly this method in a forum software, and it performs VERY good. I
tested it with about a million of rows, and it is really very fast. Even
with deep trees (1000 sub branches).

The only difference is that I use a base 255 encoded text column instead of
only 0-9. But attention: the character set must be ASCII (ordering!) ...

I want to change this to bytea to avoid base255 encoding and the character
set problems, but there are still some Bugs with bytea and the like
operator. :-(


Ciao
  Alvar

- -- 
** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.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)

iD4DBQE/z59nOndlH63J86wRAsrEAJ9OjO5fXhnw2mmLoB7YNHJFYO/X8QCXc31M
FWdV8T92N3kzctSgOOkVMw==
=Uwtm
-END PGP SIGNATURE-


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

Re: [GENERAL] Pronouncing PostgreSQL

[Dann Corbit]

Tempest in a teapot.

It's just trivia IMO.  But, YMMV.

> -Original Message-
> From: John Wells [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, December 04, 2003 6:51 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [GENERAL] Pronouncing PostgreSQL
> 
> 
> Devrim GUNDUZ said:
> > BTW, this mp3 was on the old web site, AFAIR.
> 
> Seems like there should be a *prominent* link on the site 
> describing just how to pronounce it.  From a marketing 
> standpoint, having everyone uncomfortably stumbling over 
> themselves while struggling to pronounce it in meetings with 
> management is not a good way to gain widespread adoption, 
> collaboration, etc.
> 
> John
> 
> ---(end of 
> broadcast)---
> TIP 8: explain analyze is your friend
> 

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

[GENERAL] unsubscribe

[Doug Christie]

unsubscribe


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

Re: [GENERAL] postgresql locks the whole table!

[Alvar Freude]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1



- -- Dr NoName <[EMAIL PROTECTED]> wrote:

> Help!

you may look in the archives; one day ago there was the same question.


Ciao
  Alvar


- -- 
** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.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/z51FOndlH63J86wRAooRAKCDV1tex7pn1XBDaphLP2ub5UBMEACfUPcg
ewedvwMydauojzsrwQXddfo=
=mLXm
-END PGP SIGNATURE-


---(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] pg_hba.conf change in 7.4

[Seum-Lim Gan]

Title: Re: [GENERAL] pg_hba.conf change in
7.4


Hi Bruce,

I wonder if there is any recommendation to this ?
Is there a way to configure PostgreSQL to not use
IPv6 ?

We are also wonder if there is a version of Ident server
that the PostgreSQL community knows that will work
with IPv6.

Thanks.

Gan

At 11:37 am -0600 2003/11/20, Seum-Lim Gan wrote:
Hi Bruce,

We are using Sun Solaris 9 on Sparc.
uname -a :

SunOS test01 5.9 Generic_112233-04 sun4u
sparc SUNW,Ultra-80

Gan

At 12:29 pm -0500 2003/11/20, Bruce
Momjian wrote:
I think what happens is that when we
listen on IPv4 and IPv6, that all
connections get IPv6.  What OS are you using?

---

Seum-Lim Gan wrote:
> Hi Bruce,
>
> Thanks for the info.
> I captured the netstat output below.
>
> Looks like there is a bunch of IPv4 being used.
>
> Any idea how this can be resolved ?
>
> Thanks.
>
> Gan
>
> UDP: IPv6
> Local
Address
Remote Address
> State  If
> -
-
> -- -
>
localhost.35847  
localhost.35847  
Connected
>
> TCP: IPv4
> Local
Address    Remote
Address    Swind Send-Q Rwind Recv-Q  State
>   - -- -
-- ---
> localhost.32906 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32906 
49152  0 49152 
0 ESTABLISHED
> localhost.32908 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32908 
49152  0 49152 
0 ESTABLISHED
> localhost.32910 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32910 
49152  0 49152 
0 ESTABLISHED
> localhost.32911 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32911 
49152  0 49152 
0 ESTABLISHED
> localhost.32913 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32913 
49152  0 49152 
0 ESTABLISHED
> localhost.32915 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32915 
49152  0 49152 
0 ESTABLISHED
> localhost.32917 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32917 
49152  0 49152 
0 ESTABLISHED
> localhost.32919 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32919 
49152  0 49152 
0 ESTABLISHED
> localhost.32920 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32920 
49152  0 49152 
0 ESTABLISHED
> localhost.32922 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32922 
49152  0 49152 
0 ESTABLISHED
> localhost.32923 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32923 
49152  0 49152 
0 ESTABLISHED
> localhost.32924 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32924 
49152  0 49152 
0 ESTABLISHED
> localhost.32926 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32926 
49152  0 49152 
0 ESTABLISHED
> localhost.32927 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.32927 
49152  0 49152 
0 ESTABLISHED
> localhost.33086 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.33086 
49152  0 49152 
0 ESTABLISHED
> localhost.33087 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.33087 
49152  0 49152 
0 ESTABLISHED
>
localhost.50882 
localhost.14502 
49152  0 49152 
0 ESTABLISHED
> localhost.14502 
localhost.50882 
49152  0 49152 
0 ESTABLISHED
> localhost.50883 
localhost.14500 
49152  0 49152 
0 ESTABLISHED
> localhost.14500 
localhost.50883 
49152  0 49152 
0 ESTABLISHED
>
> At 12:11 pm -0500 2003/11/20, Bruce Momjian wrote:
> >Seum-Lim Gan wrote:
> >>  Hi,
> >>
> >>  In 7.4, I noticed there is this ::1 and : (x8
of them)
> >>  for IPv6.
> >>
> >>  I looked at the documentation and there is nothing
that says
> >>  what the ::1 is for.
> >
> >The ::1 is a IPv6 shorthand for 127.0.0.1 (localhost).
> >
> >>  Commenting out that line will prevent access to
PostgreSQL
> >>  from psql unless I put trust for that line.
> >>
> >>  This is what I had in 7.3.4:
> >>  host   
all
all
127.0.0.1
255.255.255.255
> >  > ident pspmap
> >>  local  
all
all
password
> >>  host   
all

Re: [GENERAL] Transaction Question

[Manfred Koizar]

On Thu, 4 Dec 2003 17:56:33 - (GMT), "John Sidney-Woollett"
<[EMAIL PROTECTED]> wrote:
>It would be nice if nested transactions could be (optionally) decoupled
>from their enclosing transaction.

While I see your point how this could be useful for certain use cases,
unfortunately I don't have any idea how it could be implemented with
respect to MVCC.

Servus
 Manfred

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

Re: [GENERAL] query and pg_dump problem on my postgresql 6.5.3/Redhat

[Jan Wieck]

I would say you're loosing your disk drive. Have you lately checked for
bad blocks?


Jan


吴德文 wrote:

> Help!
> 
> A few days ago, my php page began to complain this:
> --
> Warning: PostgresSQL query failed: pqReadData() -- backend closed the 
> channel unexpectedly. This probably means the backend terminated abnormally 
> before or while processing the request.
> --
> 
> The SQL string in php page is:
> --
> $sql.='Select news_id,title,summary,publish_time,is_html,if_use_url,url,news_pri ';
> $sql.='From newses N,classes C ';
> $sql.="Where N.class_id = C.class_id AND C.classname='$class' ";
> $sql.='Order by publish_time Desc,news_id Desc Limit '.$Nlimit;
> --
> 
> NOTE:
> I'm on Redhat 6.2 with Postgresql 6.5.3, the database named "news",
> and the table is "newses", looks like this (dumped from "pg_dump -s -t newses news"):
> 
> CREATE TABLE "newses" (
> "news_id" int4 DEFAULT nextval ( '"newses_news_id_seq"' ) NOT NULL,
> "title" character varying(100) NOT NULL,
> "class_id" int4 NOT NULL,
> "summary" text DEFAULT '',
> "user_id" int4 NOT NULL,
> "url" character varying(100),
> "img_url" character varying(100),
> "publish_time" date NOT NULL,
> "if_show_news" bool DEFAULT bool 'f' NOT NULL,
> "if_use_url" bool DEFAULT bool 'f' NOT NULL,
> "is_html" bool DEFAULT bool 'f' NOT NULL,
> "view_count" int4 DEFAULT 0 NOT NULL,
> "news_pri" int4);
> CREATE UNIQUE INDEX "newses_pkey" on "newses" using btree ( "news_id" 
> "int4_ops" );
> 
> This table has 243 records, the max news_id is 253.
> 
> Later I found queries like these fails in psql:
> select news_id,title from newses order by news_id desc limit 10;
> select count(news_id) from newses;
> 
> But thess works fine:
> select * from newses where news_id< 300;
> select count(*) from newses where news_id <300;
> select count(news_id) from newses where news_id <300;
> 
> A simple rule is if I'm running query over the whole 
> table without condition, I get same error message mentioned above.
> 
> I thought my postgresql should be patch or upgrade, so I began to backup the 
> database on it. 
> 
> But I found that pg_dump sometimes does not work on that very table,
> and sometimes work with a long long time then error.
> 
> following are the error message of "pg_dump news -t newses -f newses-data.sql":
> --
> pqWait() -- connection not open
> PQendcopy: resetting connection
> SQL query to dump the contents of Table 'newses' did not execute correctly.  After 
> we read all the table contents from the backend, PQendcopy() failed.  Explanation 
> from backend: ''.
> The query was: 'COPY "newses" TO stdout;
> '.
> --
> 
> I read the file(14M) generated and found that after the normally record(91K) there 
> are many character like these:
> --
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> 280368896   \N  \N  0   \N  f   f
>f   0   0
> 280368896   \N  \N  0   \N  f   f
>f   0   0
> 280368896   \N  \N  0   \N  f   f
>f   0   0
> --
> And end with
> --
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N   
>\N  \N
> \.
> --
> 
> It is my nightmare now for I can't get back my data. I googled around with 
> no luck. 
> 
> Anyone help me to get back the data and tell me what was going on?
> 
> 
> 　　Yours Wind Wood
> [EMAIL PROTECTED]
> 　2003-12-04
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster


-- 
#==#
# 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 9: the planner will ignore your desire to choose an inde

Re: [GENERAL] Storing passwords

[Andrew Rawnsley]

You can create MD5 or SHA-1 digests with java.security.MessageDigest. 
They would
be stored as text



On Dec 1, 2003, at 4:01 PM, Oleg Lebedev wrote:

Can Postgres JDBC driver encrypt a password before sending and 
inserting
it into the password column?

-Original Message-
From: Peter Eisentraut [mailto:[EMAIL PROTECTED]
Sent: Monday, December 01, 2003 1:32 PM
To: Oleg Lebedev
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Storing passwords
Oleg Lebedev writes:

My application needs to store user names and passwords in the database

via JDBC connection. What is the right way to do this?
One table, one column for the name, one column for the password.

What should be the database type of the password column?
text or bytea, depending on how you encrypt it.

How do I encrypt the password before sending it to the database?
Check out contrib/pgcrypto.

What other database settings need to be enabled for this to work?
None.

--
Peter Eisentraut   [EMAIL PROTECTED]
*

This e-mail may contain privileged or confidential material intended 
for the named recipient only.
If you are not the named recipient, delete this message and all 
attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise 
using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.

*

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



Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] embedded postgresql + C++ IDE

[Randolf Richardson]

[sNip]
> Thanks for all the answers.
> It seems if I wish to make my idea into 
> reality I will have to invest in the Profesional
> Microsoft Visual C++ studio.
[sNip]

What about OpenWatcom?

Open Watcom - Portable Compilers and Tools
http://www.openwatcom.org/

It's a C++ compiler, linker, etc., that's now open source and free.  It 
used to be very expensive and has been around for a long time, but previously 
known as just "Watcom" before it was upgraded to open source.

-- 
Randolf Richardson - [EMAIL PROTECTED]
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

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

Re: [GENERAL] Storing passwords

[Oleg Lebedev]

Can Postgres JDBC driver encrypt a password before sending and inserting
it into the password column? 

-Original Message-
From: Peter Eisentraut [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 01, 2003 1:32 PM
To: Oleg Lebedev
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Storing passwords


Oleg Lebedev writes:

> My application needs to store user names and passwords in the database

> via JDBC connection. What is the right way to do this?

One table, one column for the name, one column for the password.

> What should be the database type of the password column?

text or bytea, depending on how you encrypt it.

> How do I encrypt the password before sending it to the database?

Check out contrib/pgcrypto.

> What other database settings need to be enabled for this to work?

None.

-- 
Peter Eisentraut   [EMAIL PROTECTED]

*

This e-mail may contain privileged or confidential material intended for the named 
recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information 
in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network. 

*


---(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] EMBEDDED VACUUM SOLVED!!!!!!!!!!!

[Lynn . Tilby]

Michael,

The -t option for ecpg DOES work like a charm

THANK YOU!!

I am also posting this to GENERAL, I noticed 2-3 other people who have been
having the same exact problem!

Thanks Again,
Lynn

Quoting Michael Meskes <[EMAIL PROTECTED]>:

> On Sun, Nov 30, 2003 at 11:08:51PM -0700, [EMAIL PROTECTED] wrote:
> > Here is my previous post...  Please let me know what you find.
> > Thanks,
> 
> I tried reproducing your problem with pgsql 7.4 but couldn't. The only
> thing I changed was that I ran ecpg with option "-t" and your test
> program works like a charm.
> 
> Michael
> -- 
> Michael Meskes
> Email: Michael at Fam-Meskes dot De
> ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
> Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
> 


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

[GENERAL] transaction in progress

[Rick Gigger]

Is there a convenient way to tell in postgres if a transaction has been
started or not?


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

   http://archives.postgresql.org

Re: [GENERAL] Money data type in PostgreSQL?

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Claudio Lapidus") wrote:
> With regard to precision, it is common in certain applications the need to
> handle very small amounts, especially when used as factors of a larger
> calculation. I've even seen once some rates defined in hundredths of cents!

Well, you don't need terribly much precision in the currency exchange
rate if the amount being converted is small.  You only need a couple
significant digits to convert $0.24 USD to the appropriate amount in
$CDN.

But to get the pennies right on a $10,000 USD transaction converted
into GBP (UK Pounds), you need all the official precision that there
is.  And if your calculation is off by 4 cents, some of those
accounting folk are liable to thrash you mercilessly over it.  If you
get calculations WRONG, they get really uncomfortable, and want to
know why.
-- 
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://www3.sympatico.ca/cbbrowne/linuxdistributions.html
"Women who seek to be equal to men lack ambition. "
-- Timothy Leary

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

Re: [GENERAL] Teach Yourself PostgreSQL book

[Christopher Browne]

Oops! [EMAIL PROTECTED] (Bret Busby) was seen spray-painting on a wall:
> In searching on the Internet, I found various references to the book
> "Teach Yourself PostgreSQL In 21 Days", written by Chris Smith,
> published by SAMS, December 2002 (from the Barnes and Noble web site),
> ISBN 0672324830, 600 pages (from
> http://202.113.6.251/cat/books/0672324830.html).

Late 2002 was "not a good time" for technical book publishing.  WROX
went out of business at about that time, and it would be unsurprising
for the project to have been cancelled before the book reached general
release.  (I had a book Fail Utterly at about that time, when WROX
went under...)
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/publications.html
A VAX is virtually a computer, but not quite.

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

[GENERAL] Shared hosting configuration advice requested

[alpheta]

Greetings to pgsql-general list members,

I am configuring a server for my hosting company on which I would like to
offer postgres shared hosting as an option. Postgres has been installed
already but I'm having difficulty sequestering server access by shared
accounts and generally could use some help in setting this up properly. I
would greatly appreciate any advice from list members who might have
experience doing this on a RH9/Apache server, this one having Cpanel
administrative software installed to complicate matters. So as not to
instigate commercial trespass on this list I would welcome private responses
at

dholeman ~at~ alpheta ~dot~ net.

Many thanks in advance,
Don Holeman

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

Re: [GENERAL] Equivalent to DBMS_JOB

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Alvaro Herrera) wrote:
> On Sun, Nov 30, 2003 at 12:19:34AM -0500, Arturo Pérez wrote:
>
>> Does postgreSQL offer anything similar?  And please don't
>> say use cron.  Cron has issues that I'd like to avoid.
>
> Huh, just curious: what issues does cron have?

It does have the demerit that it isn't terribly "transactional."  And
the tools for managing jobs are likewise not much transactional.
-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/spiritual.html
Rules of the Evil Overlord #35. "I  will not grow a goatee. In the old
days they made  you look diabolic. Now they just make  you look like a
disaffected member of Generation X." 

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

[GENERAL] postgresql locks the whole table!

[Dr NoName]

Help!

I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:

CREATE TABLE take2
(
  id serial not null,
  timestamp timestamp NOT NULL DEFAULT now(),
  description text,
  iteration smallint,
  asset_id integer,
--  FOREIGN KEY (asset_id) REFERENCES public.asset
(id),  -- ON UPDATE CASCADE ON DELETE CASCADE,

  primary key(id)
);

(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally, I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).

Now I type the following commands:

shell 1:

1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);


shell 2:

1. BEGIN
2. insert into take2 values(default, 'now()', 't2', 1,
1);

this works.

However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.

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?

looking at pg_locks, I see the following:

 relation | database | transaction |  pid  |  
mode   | granted
--+--+-+---+--+-
39356 |34862 |NULL | 18671 |
AccessShareLock  | t
39356 |34862 |NULL | 18671 |
RowExclusiveLock | t
 NULL | NULL |9914 | 18671 |
ExclusiveLock| t
39354 |34862 |NULL | 18671 |
AccessShareLock  | t
34886 |34862 |NULL | 18671 |
AccessShareLock  | t
34886 |34862 |NULL | 18671 |
RowShareLock | t
16759 |34862 |NULL | 18671 |
AccessShareLock  | t
(7 rows)

Where does the ExclusiveLock come from? What is being
locked?

It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.

So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?

This was tested on PostgreSQL 7.4.0 and 7.3.2.

thanks in advance,

Eugene



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

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

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

Re: [GENERAL] Transaction Question

[Richard Huxton]

On Thursday 04 December 2003 07:57, John Sidney-Woollett wrote:
>
> I'm amazed that nested transactions are still not yet supported. Postgres
> is a mature product (from my understanding), and I can see loads of
> messages asking about nested transaction support.

Loads of messages asking for many other features too ;-)
It will get done sooner or later - the more assistance people offer the core 
the sooner it'll be.

-- 
  Richard Huxton
  Archonet Ltd

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

   http://archives.postgresql.org

Re: [GENERAL] Transaction Question

[John Sidney-Woollett]

Hi Scott

Firstly, your name rings a bell. I'm sure you're a 4D developer - I was
one too (once, but no more sadly)...

You imply that it should be possible to create an embedded C function that
can access the DB in its own thread, and therefore its own transaction. Is
there a document that would explain how this is done?

Also do you know if you can embedded java functions - I'm more comfortable
with java than C these days?

I think that for this problem, I will use SEQUENCE objects who name is
stored within the WDVolume table. It will allow me to avoid the row level
lock bottleneck, and my counters won't be rolled back on a transaction
rollback. There are only going to be less than 100 Volumes - so this
approach will be fine.

I'm amazed that nested transactions are still not yet supported. Postgres
is a mature product (from my understanding), and I can see loads of
messages asking about nested transaction support.

Thanks for your feedback.

John

Scott Ribe said:
> 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
>


---(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 Advocacy, Thoughts and Comments

[Alex Satrapa]

Chris Travers wrote:
  Here is a paper I have written for the purposes of providing some
  additional educational material for the MySQL crowd.
Here's my contribution:

Why I choose PostgreSQL (PostgreSQL in 21 Seconds)

I choose referential integrity, meaning my lookups always work.

I choose stored procedures, meaning all my developers - Windows or Unix, 
Perl, C++ or Java - can access the database in the same way, using the 
same locking, with the same checking and cleaning

I choose subselects and outer joins, which allow me to build complex 
queries to get exactly the information I want from the database, rather 
than wasting my time munging data in my code. Even better, I can put 
those common queries into stored procedures, so other developers can get 
the same results as I do!

I choose partial indexes, so lookups on NULL fields are just as fast if 
not faster.

I choose a user community that believes getting the results right is 
more important than getting them quickly.

I choose getting the right results, right now!

I choose funny capitalisation, and a name that can't be pronounced!

I choose 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