[GENERAL] How to split a table?

[Felix Zhang]

Hi,
 
I want to split a table to 2 small tables. The 1st one contains 60% records which are randomly selected from the source table.
How to do it?
 
Regards,
Felix

[GENERAL] Any documatation about porting from Oracle to PostgreSQL

[Felix Zhang]

Hi all,
 
I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle to PostgreSQL.
Anyone can share with me some good documatations?
 
Thanks and regards,
Felix

Re: [GENERAL] How to split a table?

[Andreas Kretschmer]

Felix Zhang <[EMAIL PROTECTED]> schrieb:

> Hi,
>  
> I want to split a table to 2 small tables. The 1st one contains 60% records
> which are randomly selected from the source table.
> How to do it?

Why do you want to do this?


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

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

   http://archives.postgresql.org/

Re: [GENERAL] Any documatation about porting from Oracle to PostgreSQL

[Andreas Kretschmer]

Felix Zhang <[EMAIL PROTECTED]> schrieb:

> Hi all,
>  
> I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle
> to PostgreSQL.
> Anyone can share with me some good documatations?

http://techdocs.postgresql.org/#convertfrom


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

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

   http://archives.postgresql.org/

Re: [GENERAL] Any documatation about porting from Oracle to PostgreSQL

[Thomas Kellerer]

On 17.10.2006 10:36 Andreas Kretschmer wrote:


http://techdocs.postgresql.org/#convertfrom



I just noticed that the link "Porting from Oracle PL/SQL" still points 
to the 7.4 manuals. Shouldn't that be updated to point to the current 
release?


And the link "Ora2Pg - Oracle to PostgreSQL database schema converter"

(http://techdocs.postgresql.org/redir.php?link=http://www.samse.fr/GPL/ora2pg/ora2pg.html)

gives a 404 error.


Thomas


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

  http://archives.postgresql.org/

[GENERAL] Can we convert from Postgres to Oracle !!???

[Sandeep Kumar Jakkaraju]

Can we convert from Postgres to Oracle !!???

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

Re: [SQL] [GENERAL] How to split a table?

[Gregory S. Williamson]

Perhaps something like:

CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60);

?

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Andreas Kretschmer
Sent:   Tue 10/17/2006 1:34 AM
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; [EMAIL 
PROTECTED]
Cc: 
Subject:Re: [SQL] [GENERAL] How to split a table?

Felix Zhang <[EMAIL PROTECTED]> schrieb:

> Hi,
>  
> I want to split a table to 2 small tables. The 1st one contains 60% records
> which are randomly selected from the source table.
> How to do it?

Why do you want to do this?


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

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


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4534936b271274356172766&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4534936b271274356172766!
---






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

Re: [NOVICE] [GENERAL] How to split a table?

[Felix Zhang]

to do some statistics analysis.
2006/10/17, Andreas Kretschmer <[EMAIL PROTECTED]>:
Felix Zhang <[EMAIL PROTECTED]> schrieb:
> Hi,>> I want to split a table to 2 small tables. The 1st one contains 60% records> which are randomly selected from the source table.> How to do it?Why do you want to do this?
Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect.  (Linus Torvalds)"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°---(end of broadcast)---TIP 4: Have you searched our list archives?  
http://archives.postgresql.org

Re: [GENERAL] Any documatation about porting from Oracle to PostgreSQL

[Shane Ambler]

Felix Zhang wrote:

Hi all,

I'm a newbie of PostgreSQL. I'm searching materials about porting from
Oracle to PostgreSQL.
Anyone can share with me some good documatations?

Thanks and regards,
Felix



A quick search of pgfoundry finds
http://pgfoundry.org/projects/orafce/
which may help.

Depending on your needs and plans have you looked at EnterpriseDB?
They claim to run most Oracle stuff straight up. Their free version will 
run on 1cpu with up to 4GB data and 1GB RAM.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

[GENERAL] ERRORDATA_STACK_SIZE exceeded

[Stefan Sassenberg]

Hello,

I've got a failing sql-Script that I execute with the psql command. The 
Script contains:


8<-
SET CLIENT_ENCODING = 'LATIN1';

(Some other statements)

CREATE TABLE "public"."init_camera_type" (
  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
8<-

where it bails out. The console output is:
8<-
PANIK:  ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
8<-

The postgresql.log writes:
8<-
2006-10-17 13:04:03 CEST FEHLER:  22021: ungültige Byte-Sequenz für 
Kodierung »UTF8«: 0xbb

2006-10-17 13:04:03 CEST ORT:  report_invalid_encoding, wchar.c:1328
2006-10-17 13:04:03 CEST ANWEISUNG:  CREATE TABLE 
"public"."init_camera_type" (

  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
2006-10-17 13:04:03 CEST FEHLER:  22021: ungültige Byte-Sequenz für 
Kodierung »UTF8«: 0xfc6c74696765

2006-10-17 13:04:03 CEST ORT:  report_invalid_encoding, wchar.c:1328
2006-10-17 13:04:03 CEST ANWEISUNG:  CREATE TABLE 
"public"."init_camera_type" (

  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
2006-10-17 13:04:03 CEST FEHLER:  22021: ungültige Byte-Sequenz für 
Kodierung »UTF8«: 0xfc6c74696765

2006-10-17 13:04:03 CEST ORT:  report_invalid_encoding, wchar.c:1328
2006-10-17 13:04:03 CEST ANWEISUNG:  CREATE TABLE 
"public"."init_camera_type" (

  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
2006-10-17 13:04:03 CEST FEHLER:  22021: ungültige Byte-Sequenz für 
Kodierung »UTF8«: 0xfc6c74696765

2006-10-17 13:04:03 CEST ORT:  report_invalid_encoding, wchar.c:1328
2006-10-17 13:04:03 CEST ANWEISUNG:  CREATE TABLE 
"public"."init_camera_type" (

  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
2006-10-17 13:04:03 CEST PANIK:  XX000: ERRORDATA_STACK_SIZE exceeded
2006-10-17 13:04:03 CEST ORT:  errstart, elog.c:274
2006-10-17 13:04:03 CEST ANWEISUNG:  CREATE TABLE 
"public"."init_camera_type" (

  "id_camera_type" SMALLINT NOT NULL,
  "name" VARCHAR(75) NOT NULL,
  "is_video_camera" BOOLEAN DEFAULT false NOT NULL,
  "description" VARCHAR(75),
  PRIMARY KEY("id_camera_type")
) WITH OIDS;
2006-10-17 13:04:03 CEST DEBUG:  0: reaping dead processes
2006-10-17 13:04:03 CEST ORT:  reaper, postmaster.c:2021
2006-10-17 13:04:03 CEST DEBUG:  0: Serverprozess (PID 32435) wurde 
von Signal 6 beendet

2006-10-17 13:04:03 CEST ORT:  LogChildExit, postmaster.c:2428
8<-

I don't think there are invalid byte sequences in my script. In fact 
there is no occurrence of 0xbb anywhere in the script.


The database that script is applied to is UTF8 encoded.
The postgresql version is 8.1.4.
My system is Linux pc01 2.6.17-gentoo-r8 #2 SMP Mon Oct 16 11:23:12 CEST 
2006 i686 Intel(R) Pentium(R) 4 CPU 2.60GHz GNU/Linux


Is there someone who can explain what's going on here?

Regards

Stefan

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

  http://archives.postgresql.org/

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Markus Schiltknecht]

Hallo Stefan,

Stefan Sassenberg wrote:

Hello,

I've got a failing sql-Script that I execute with the psql command. The 
Script contains: 


I've been unable to reproduce the error with just that snippet (on 
debian with PostgreSQL 8.1.4). Can you provide a stripped down test case?



8<-
PANIK:  ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
8<-


'Exceeding stack size' sound somewhat like an infinite loop of a 
recursive function... but that's just a very general guess. I think the 
root cause of problems is somewhere else.


Regards

Markus

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

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Stefan Sassenberg]

Hallo Markus,

Markus Schiltknecht wrote:

Hallo Stefan,

Stefan Sassenberg wrote:

Hello,

I've got a failing sql-Script that I execute with the psql command. 
The Script contains: 


I've been unable to reproduce the error with just that snippet (on 
debian with PostgreSQL 8.1.4). Can you provide a stripped down test case?


Reproduction is difficult. The script runs fine with the same db on 
another server with a Linux derived from Knoppix, but also postgresql 8.1.4.



8<-
PANIK:  ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
8<-


'Exceeding stack size' sound somewhat like an infinite loop of a 
recursive function... but that's just a very general guess. I think the 
root cause of problems is somewhere else.


As I see it, ERRORDATA_STACK_SIZE is a constant that is defined in the 
code (pgsql/src/backend/utils/error/elog.c) as 5. 5 illegal bytecodes 
that postgresql finds in my statement are enough to cause the panic.



Stefan

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

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

Re: [GENERAL] Any documatation about porting from Oracle to PostgreSQL

[A. Kretschmer]

am  Tue, dem 17.10.2006, um 10:44:52 +0200 mailte Thomas Kellerer folgendes:
> On 17.10.2006 10:36 Andreas Kretschmer wrote:
> >
> >http://techdocs.postgresql.org/#convertfrom
> >
> 
> I just noticed that the link "Porting from Oracle PL/SQL" still points 
> to the 7.4 manuals. Shouldn't that be updated to point to the current 
> release?
> 
> And the link "Ora2Pg - Oracle to PostgreSQL database schema converter"
> 
> (http://techdocs.postgresql.org/redir.php?link=http://www.samse.fr/GPL/ora2pg/ora2pg.html)
> 
> gives a 404 error.

Sorry.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] [NOVICE] How to split a table?

[Christoph Frick]

On Tue, Oct 17, 2006 at 03:39:21PM +0800, Felix Zhang wrote:

> I want to split a table to 2 small tables. The 1st one contains 60%
> records which are randomly selected from the source table.  How to do
> it?

i do my A/B-Group splitting usually by &1 the serial of the table.
assuming, that there are no irregularities in the process of creating
your entries, this should give a 50/50 split, which is reuseable for
future things and there never is a intersection of the two groups.

-- 
cu


pgpHaMIrIiAlt.pgp
Description: PGP signature

Re: [GENERAL] [NOVICE] How to split a table?

[Obe, Regina]

I would do
 
select * into mynewtable 
from myoldtable ORDER by random() LIMIT 15000
 
where 15000 in this case is *.6
 
If you want to create another table with 40% of the 
remaining data then something like
 
select * into mynewtable2 from myoldtable where 
myoldtable.primarykey NOT IN(select primarykey from 
mynewtable)
]
In this case primarykey you would replace with the 
primary key field of your table.


From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Felix 
ZhangSent: Tuesday, October 17, 2006 3:39 AMTo: 
pgsql-sql@postgresql.org; pgsql-general@postgresql.org; 
[EMAIL PROTECTED]Subject: [NOVICE] How to split a 
table?

Hi,
 
I want to split a table to 2 small tables. The 1st one contains 60% records 
which are randomly selected from the source table.
How to do it?
 
Regards,
Felix




The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Shane Ambler]

Stefan Sassenberg wrote:

Hello,

I've got a failing sql-Script that I execute with the psql command. The 
Script contains:


8<-
SET CLIENT_ENCODING = 'LATIN1';





The database that script is applied to is UTF8 encoded.


Stefan Sassenberg wrote:
> As I see it, ERRORDATA_STACK_SIZE is a constant that is defined in the
> code (pgsql/src/backend/utils/error/elog.c) as 5. 5 illegal bytecodes
> that postgresql finds in my statement are enough to cause the panic.


Could the difference between CLIENT_ENCODING = 'LATIN1' and 
database_encoding = UTF8 create the illegal bytecodes?



I just found a way to get a similar error -
create db with utf8 encoding
save file with the create table command - save as unicode-utf16

I then get -
%>psql enctest < test
ERROR:  invalid byte sequence for encoding "UTF8": 0xfe
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".


If I save the file as latin1 or utf8 I have no problems.

I would be looking for the error somewhere in the (some other 
statements) part and would expect an invisible/unprintable char of some 
sort.




SET CLIENT_ENCODING = 'LATIN1';

(Some other statements)

CREATE TABLE 



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Stefan Sassenberg]

Hello Shane,

Shane Ambler wrote:

Stefan Sassenberg wrote:

Hello,

I've got a failing sql-Script that I execute with the psql command. 
The Script contains:


8<-
SET CLIENT_ENCODING = 'LATIN1';





The database that script is applied to is UTF8 encoded.


Stefan Sassenberg wrote:
 > As I see it, ERRORDATA_STACK_SIZE is a constant that is defined in the
 > code (pgsql/src/backend/utils/error/elog.c) as 5. 5 illegal bytecodes
 > that postgresql finds in my statement are enough to cause the panic.


Could the difference between CLIENT_ENCODING = 'LATIN1' and 
database_encoding = UTF8 create the illegal bytecodes?



I just found a way to get a similar error -
create db with utf8 encoding
save file with the create table command - save as unicode-utf16

I then get -
%>psql enctest < test
ERROR:  invalid byte sequence for encoding "UTF8": 0xfe


In my case, the illegal byte code is 0xbb. I used hexdump|grep to find 
occurrences of "bb" in my file script file but there isn't any.


I'm using that way of writing and encoding scripts for years. Nothing 
seems to have changed.


Stefan

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

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

[Sander Steffann]

Hi,


The POSIX timezone notation as understood by the zic code includes
the possibility of

zoneabbrev[+-]hh[:mm[:ss]]

but the meaning is that hh:mm:ss *is* the offset from GMT, and
zoneabbrev is being defined as the abbreviation for that offset.
What the datetime.c code is doing is trying to find the zoneabbrev
in a built-in timezone table, and then adding the two together.
This is simply wacko.


I think that if anyone has ever tried to use this notation they would have 
noticed this misinterpretation of the specs.



Given where the code stands now, I think the best solution is to
rip out DecodePosixTimezone and instead pass the syntax off to the
zic code (which can handle it via tzparse()).  Since the datetime
input parser is ultimately only interested in the GMT offset value,
this would mean that the zoneabbrev part would become a noise word.


Sounds like a good idea to me.
Sander



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

Re: [GENERAL] Permission problem with create tablespace

[Andras Simon]

On 10/17/06, Leonel Nunez <[EMAIL PROTECTED]> wrote:


Correction to my   prior  mail

do this as root :

chcon system_u:object_r:postgresql_db_t  /opt/home/pgdata/mspace


When you need to know what config has any directory for SELinux
do a:

ls -lZ   /your/dir

and if you need  /your/otherdir  the same  set the configuration for
SELinux with  chcon

check the  /var/lib/pgsql  and you get :
[EMAIL PROTECTED] ~]# ls -lZ /var/lib/pgsql/
drwx--  postgres postgres system_u:object_r:var_lib_t  backups
drwx--  postgres postgres system_u:object_r:postgresql_db_t data
-rw---  postgres postgres system_u:object_r:postgresql_log_t
pgstartup.lo

we set the SELinux permissions  to yourdir  as the permissions that
/var/lib/pgsql/data has


Thanks, this is exactly what I was looking for! To try it, I have to
wait for the next reboot, because in order to get moving, I checked

SELinux > Modify SELinux Policy > SELinux Service Protection > Disable
SELinux Protection for postgresql daemon

in system-config-securitylevel, and these kind of changes don't seem
to take effect until the next reboot (although I think they should; I
see 'avc: denied...' messages when I'm doing this, so there just may
be some other SELinux problems here).

Thanks a lot,

Andras

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

  http://archives.postgresql.org/

Re: [GENERAL] Can we convert from Postgres to Oracle !!???

[Berend Tober]

Sandeep Kumar Jakkaraju wrote:


Can we convert from Postgres to Oracle !!???


Umm, this would be the wrong forum for that.

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

  http://archives.postgresql.org/

Re: [GENERAL] Can we convert from Postgres to Oracle !!???

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/17/06 03:51, Sandeep Kumar Jakkaraju wrote:
> Can we convert from Postgres to Oracle !!???

Are you asking permission?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFNNkVS9HxQb37XmcRAiReAKDYU9/bWzTdlZ2KM9SWk9M9lH5BVQCgrIDl
8ey+EncXhj4NWjs2AzaMvSg=
=gBjq
-END PGP SIGNATURE-

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

Re: [GENERAL] Can we convert from Postgres to Oracle !!???

[Harald Armin Massa]

Sandeep Kumar,


Can we convert from Postgres to Oracle !!???


technically: partial. Oracle supports most of the bleeding edge
enterprise features of PostgreSQL. There are some limits however, esp.
concerning inheritance, arbitrary length text fields and especially
the missing support for pl/python, pl/pgsql and pl/perl. You might be
able to replace those advanced features of PostgreSQL using PL/SQL.
There are external database development tools available for Oracle if
you need to match the functionality of PostgreSQLs integrated clients
psql and pgAdmin III.

support-wise: please ask your Oracle authorized support contract
consultant to find out which Oracle support plan gives you a support
comparable to the PostgreSQL one with the possibility to directly
correspond with the database core architects and developers as well as
unlimited access to the applications source code.

economically: not enough information to give a fitting answer. But if
you need a high cash burn rate to attract certain kinds of investors,
Oracle will propably make that easier.

Best wishes,

Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

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

Re: [GENERAL] A query planner that learns

[AgentM]

On Oct 16, 2006, at 16:17 , Madison Kelly wrote:


Alvaro Herrera wrote:

Jochem van Dieten wrote:

Scott Marlowe wrote:
While all the talk of a hinting system over in hackers and  
perform is
good, and I have a few queries that could live with a simple  
hint system
pop up now and again, I keep thinking that a query planner that  
learns

>from its mistakes over time is far more desirable.
Is it reasonable or possible for the system to have a way to  
look at
query plans it's run and look for obvious mistakes its made,  
like being
off by a factor of 10 or more in estimations, and slowly learn  
to apply

its own hints?
Technically it is very feasible. But I think you might want to  
check US Patent 6,763,359 before you start writing any code.
I think it would be a very good idea if you guys stopped looking  
at the
US patent database.  It does no good to anyone.  There's no way we  
can
avoid stomping on a patent or another -- there are patents for  
everything.


Hasn't IBM release a pile of it's patents for use (or at least  
stated they won't sue) to OSS projects? If so, is this patent  
covered by that "amnesty"?


Simply ignoring patents because "there is a patent for everything"  
is a recipe for disaster. Companies like MS are running out of ways  
to tear open OSS and they are certainly not above (below?) suing  
the heck out of OSS projects for patent infringement.


What's needed is reform in the USPO. Call you congress (wo)man and  
complain, but don't flaunt the law; you will lose.


Alvaro's advice is sound. If the patent holder can prove that a  
developer looked at a patent (for example, from an email in a mailing  
list archive) and the project proceeded with the implementation  
regardless, malice can been shown and "damages" can be substantially  
higher. You're screwed either way but your safest bet is to never  
look at patents.


Disclaimer: I am not a lawyer- I don't even like lawyers.

-M

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

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

Re: [SQL][GENERAL] Any documatation about porting from Oracle to PostgreSQL

[Lewis Cunningham]

Felix,

You might want to look at EnterpriseDB, which is PostgreSQL with
Oracle compatibility extensions.

www.enterprisedb.com

LewisC


--- Felix Zhang <[EMAIL PROTECTED]> wrote:

> Hi all,
> 
> I'm a newbie of PostgreSQL. I'm searching materials about porting
> from
> Oracle to PostgreSQL.
> Anyone can share with me some good documatations?
> 
> Thanks and regards,
> Felix
> 

---
Lewis R Cunningham

ItToolBox Blog: An Expert's Guide to Oracle 
http://blogs.ittoolbox.com/oracle/guide/

EnterpriseDB: The Definitive Reference
http://www.rampant-books.com/book_2007_1_enterprisedb.htm
--

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

   http://archives.postgresql.org/

Re: Aother tablespace permission issue (was Re: [GENERAL] Permission problem ...)

[Tom Lane]

Ron Johnson <[EMAIL PROTECTED]> writes:
> ~$ touch /data/02/share/database/testing.testing
> ~$ dir /data/02/share/database
> total 8
> drwxrwxr-x  2 me postgres 4096 2006-10-16 21:53 ./
> drwxrwxr-x 16 me people   4096 2006-10-16 21:38 ../
> -rw-r--r--  1 me me  0 2006-10-16 21:53 testing.testing

> dupe_filenames=# create tablespace thisisatest   
> dupe_filenames-# owner me
> dupe_filenames-# location '/data/02/share/database';
> ERROR:  could not set permissions on directory "/data/02/share/database":
>  Operation not permitted

The specified directory has to be owned by the postgres operating system
user, not by anyone else.  (The SQL-level notion of ownership is not
relevant --- the SQL owner might not correspond to any OS user at all.)

regards, tom lane

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

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

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Tom Lane]

Stefan Sassenberg <[EMAIL PROTECTED]> writes:
> PANIK:  ERRORDATA_STACK_SIZE exceeded
> server closed the connection unexpectedly

What LC_CTYPE and LC_MESSAGES settings are you using?  We've seen
problems of this ilk when gettext() produces messages encoded in the
"wrong" encoding (ie, not what the database encoding is).

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Stefan Sassenberg]

Hello Tom,

Tom Lane wrote:

Stefan Sassenberg <[EMAIL PROTECTED]> writes:

PANIK:  ERRORDATA_STACK_SIZE exceeded
server closed the connection unexpectedly


What LC_CTYPE and LC_MESSAGES settings are you using?  We've seen
problems of this ilk when gettext() produces messages encoded in the
"wrong" encoding (ie, not what the database encoding is).



postgresql.conf has an entry lc_messages = '[EMAIL PROTECTED]'
postgresql.log says [...] [EMAIL PROTECTED]

I can't find anything about LC_CTYPE. No user on my machine has any of 
those variables as environment variables.


Update: I copied the script text into a pgadmin query window and found 
out, that the error vanishes when I comment out all CONSTRAINT lines 
from my CREATE TABLE statements.


Stefan

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

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Tom Lane]

Stefan Sassenberg <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> What LC_CTYPE and LC_MESSAGES settings are you using?  We've seen
>> problems of this ilk when gettext() produces messages encoded in the
>> "wrong" encoding (ie, not what the database encoding is).

> postgresql.log says [...] [EMAIL PROTECTED]

Hm.  You'll need to check your platform's locale settings, but I bet
that that setting implies LATIN1 encoding (or LATINsomething anyway).
So any message text that gettext produces that happens to contain
high-bit-set LATIN characters would be invalidly encoded in a UTF8
database.  The particular case that causes error stack overflow is
where the message about invalid conversion itself has the problem :-(

The real bottom line here is that your locale settings have to imply
an encoding that is the same as the database encoding.  This is just
one of the things that can go wrong when they're different.

> I can't find anything about LC_CTYPE.

"show lc_type" would tell you, but it's probably the same.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] A query planner that learns

[Madison Kelly]

AgentM wrote:
Alvaro's advice is sound. If the patent holder can prove that a 
developer looked at a patent (for example, from an email in a mailing 
list archive) and the project proceeded with the implementation 
regardless, malice can been shown and "damages" can be substantially 
higher. You're screwed either way but your safest bet is to never look 
at patents.


Disclaimer: I am not a lawyer- I don't even like lawyers.



Nor am I a lawyer, but I still hold that hoping "ignorance" will be a 
decent defense is very, very risky. In the end I am not a pgSQL 
developer so it isn't in my hands either way.


Madi

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

Re: [GENERAL] A query planner that learns

[Brian Mathis]

On 10/17/06, Madison Kelly <[EMAIL PROTECTED]> wrote:
AgentM wrote:> Alvaro's advice is sound. If the patent holder can prove that a> developer looked at a patent (for example, from an email in a mailing> list archive) and the project proceeded with the implementation
> regardless, malice can been shown and "damages" can be substantially> higher. You're screwed either way but your safest bet is to never look> at patents.>> Disclaimer: I am not a lawyer- I don't even like lawyers.
Nor am I a lawyer, but I still hold that hoping "ignorance" will be adecent defense is very, very risky. In the end I am not a pgSQLdeveloper so it isn't in my hands either way.Madi
I also am NAL, but I know enough about the patent system (in the US) to know that ignorance *IS* a defense.  If you are ignorant of the patent, you only have to pay the damages.  If you knew about the patent and did it anyway, you have to pay *triple* damages.  Ignorance will save you lots of money.
You may not like it, but that's the way it is.

[GENERAL] Database users Passwords

[DEV]

Hello all,

  I have
user information in a table that I want to use to add users to the user roles
tables that are part of postgresql.  My question is this: the passwords in my
user table are in there as a text file with the data being encrypted using the
crypt function, is there a way I can use this crypt password when I do a “CREATE
ROLE userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE”  I know that in the current CREATE ROLE I have listed will
take a clear text password and encrypt it for me.  What do I need to change to
use an encrypted password?

 

Brian

 

 

Re: [GENERAL] A query planner that learns

[Madison Kelly]

Brian Mathis wrote:
I also am NAL, but I know enough about the patent system (in the US) to 
know that ignorance *IS* a defense.  If you are ignorant of the patent, 
you only have to pay the damages.  If you knew about the patent and did 
it anyway, you have to pay *triple* damages.  Ignorance will save you 
lots of money.


You may not like it, but that's the way it is.



I got that part. :) If you _do_ end up in court, plausible deniability 
helps.


My position though is that it is better, in the long term, to be aware 
of the patents and take the time to work around them so that *no* 
damages need to be paid. Or, as might be that chance in this case, to 
get a written "okay" from the patent holder for the use of the methods 
protected by the patent in a given program.


Colour me funny, but wouldn't staying out of the courts in the first 
place not be the best option?


Madi

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

[GENERAL] How to _really_use a non-default tablespace

[Andras Simon]

It seems that PG uses the PGDATA directory even for operations
that affect databases on different tablespaces. For example, when
an index is created on a table that is in tablespace TS, the
index ends up in TS (as it should), but first a temporary
file (with size comparable to the final index) is written below
PGDATA, and is truncated only at the end of the operation.

As a more drastic example: I created a DB on a tablespace TS, and
copied a 25 MB file into one of its tables. I ended up having 60
MB of extra data in $PGDATA/pg_xlog that doesn't go away even
after dropping the database.

The docs (19.6 Tablespaces) says

"The tablespace associated with a database is used to store the
system catalogs of that database, as well as any temporary files
created by server processes using that database."

so I must be missing something.

Andras

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

  http://archives.postgresql.org/

Re: [GENERAL] Fast backup/restore

[mengel]

We just tar/gzip the entire data directory.
 It takes all of 20 sec.  We've successfully restored from that
also.  The machine you are restoring to *must* be running the save
version of postgresql you backed up from.


Matthew Engel







Jeff Davis <[EMAIL PROTECTED]>

Sent by: [EMAIL PROTECTED]
10/16/2006 02:35 PM




To
Gandalf <[EMAIL PROTECTED]>


cc
pgsql-general@postgresql.org


Subject
Re: [GENERAL] Fast backup/restore








On Mon, 2006-10-16 at 16:29 +0530, Gandalf wrote:
> I am looking for a *fast* backup/restore tools for Postgres. I've
> found the current used tools pg_dump and pg_restore to be very slow
on
> large databases (~30-40GB). Restore takes time in the tune of 6 hrs
on
> a Linux, 4 proc, 32 G RAM machine which is not acceptable.
>  
> I am using "pg_dump -Fc" to take backup. I understand binary
> compression adds to the time, but there are other databases (like
DB2)
> which take much less time on similar data sizes.
>  
> Are there faster tools available?
>  

http://www.postgresql.org/docs/8.1/static/backup-online.html

With that backup system, you can backup with normal filesystem-level
tools (e.g. tar) while the database is online.

Make sure to backup the remaining active WAL segments. Those are
necessary for the backup to be complete. This step will be done
automatically in 8.2.

If your filesystem has snapshot capability, you have nothing to worry
about. Just snapshot the fs and backup the data directory plus any WAL
segments and tablespaces.

Regards,
                
Jeff Davis


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

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Stefan Sassenberg]

Tom Lane wrote:

Stefan Sassenberg <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

What LC_CTYPE and LC_MESSAGES settings are you using?  We've seen
problems of this ilk when gettext() produces messages encoded in the
"wrong" encoding (ie, not what the database encoding is).



postgresql.log says [...] [EMAIL PROTECTED]


Hm.  You'll need to check your platform's locale settings, but I bet
that that setting implies LATIN1 encoding (or LATINsomething anyway).
So any message text that gettext produces that happens to contain
high-bit-set LATIN characters would be invalidly encoded in a UTF8
database.  The particular case that causes error stack overflow is
where the message about invalid conversion itself has the problem :-(

The real bottom line here is that your locale settings have to imply
an encoding that is the same as the database encoding.  This is just
one of the things that can go wrong when they're different.


I can't find anything about LC_CTYPE.


"show lc_type" would tell you, but it's probably the same.


[EMAIL PROTECTED] is ISO-8859-15, if that helps. I changed the locale to 
en_US.UTF-8 and LC_CTYPE in the environment is set to that value too. 
Nevertheless "show lc_ctype" says [EMAIL PROTECTED], even after a postgresql 
restart. How can I change that?


Stefan

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] ERRORDATA_STACK_SIZE exceeded

[Tom Lane]

Stefan Sassenberg <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] is ISO-8859-15, if that helps. I changed the locale to 
> en_US.UTF-8 and LC_CTYPE in the environment is set to that value too. 
> Nevertheless "show lc_ctype" says [EMAIL PROTECTED], even after a postgresql 
> restart. How can I change that?

initdb is the only way to change the database's LC_CTYPE or LC_COLLATE :-(

regards, tom lane

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

Re: [GENERAL] How to _really_use a non-default tablespace

[Martijn van Oosterhout]

On Tue, Oct 17, 2006 at 04:56:09PM +0200, Andras Simon wrote:
> As a more drastic example: I created a DB on a tablespace TS, and
> copied a 25 MB file into one of its tables. I ended up having 60
> MB of extra data in $PGDATA/pg_xlog that doesn't go away even
> after dropping the database.

The xlog is not split by database, all databases share the same xlog.

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


signature.asc
Description: Digital signature

Re: [GENERAL] A query planner that learns

[Brian Mathis]

On 10/17/06, Madison Kelly <[EMAIL PROTECTED]> wrote:
Brian Mathis wrote:> I also am NAL, but I know enough about the patent system (in the US) to> know that ignorance *IS* a defense.  If you are ignorant of the patent,> you only have to pay the damages.  If you knew about the patent and did
> it anyway, you have to pay *triple* damages.  Ignorance will save you> lots of money.>> You may not like it, but that's the way it is.>I got that part. :) If you _do_ end up in court, plausible deniability
helps.My position though is that it is better, in the long term, to be awareof the patents and take the time to work around them so that *no*damages need to be paid. Or, as might be that chance in this case, to
get a written "okay" from the patent holder for the use of the methodsprotected by the patent in a given program.Colour me funny, but wouldn't staying out of the courts in the firstplace not be the best option?
MadiYes, good idea :)

Re: [GENERAL] Fast backup/restore

[Tom Lane]

[EMAIL PROTECTED] writes:
> We just tar/gzip the entire data directory.  It takes all of 20 sec. We've 
> successfully restored from that also.

You've been very lucky ... unless you stopped the postmaster while
taking the backup.  Without that, this method WILL screw you someday.

(But as long as you're willing to stop the postmaster, it's a perfectly
reasonable option.)

regards, tom lane

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

[GENERAL] help with sql query

[Peter]

Hello,

Lets suppose I have a table like this one

id  id_1id_2   date_time
1   101 10002006-07-04 11:25:43
2   102 10012006-07-04 11:26:43
3   101 10052006-07-04 11:27:43
4   103 10002006-07-04 11:25:43


I want to find all records have same id_1, but different id_2 and have
difference in time less than 5 minutes.

In this case this is record 1 and record 3.

How can I do this ?

Thanks in advance for your help.

Peter




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

Re: [GENERAL] A query planner that learns

[Scott Ribe]

> Colour me funny, but wouldn't staying out of the courts in the first
> place not be the best option?

Yes, however some people feel that given the way the patent office is
spewing huge quantities of patents, many on old well-known techniques, and
the the absurd difficulty of reading patent claims, and the deliberately
vague language used in the hope of broadening the scope later, that it is
actually not possible to keep track of them, therefore not possible to avoid
infringement with any certainty. So, better to fall back to the 2nd-best
position...

It's a difficult question to answer because there are 0 good answers.

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



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

   http://archives.postgresql.org/

Re: [GENERAL] A query planner that learns

[Alvaro Herrera]

Madison Kelly wrote:
> Brian Mathis wrote:
> >I also am NAL, but I know enough about the patent system (in the US) to 
> >know that ignorance *IS* a defense.  If you are ignorant of the patent, 
> >you only have to pay the damages.  If you knew about the patent and did 
> >it anyway, you have to pay *triple* damages.  Ignorance will save you 
> >lots of money.
> >
> >You may not like it, but that's the way it is.
> >
> 
> I got that part. :) If you _do_ end up in court, plausible deniability 
> helps.
> 
> My position though is that it is better, in the long term, to be aware 
> of the patents and take the time to work around them so that *no* 
> damages need to be paid. Or, as might be that chance in this case, to 
> get a written "okay" from the patent holder for the use of the methods 
> protected by the patent in a given program.
> 
> Colour me funny, but wouldn't staying out of the courts in the first 
> place not be the best option?

Yeah.  I invite you to do all the extra (useless) development work
required.  But please do not charge other people with it.  Whoever
investigates patents and lets pgsql-hackers know about them, is charging
the Postgres community with that work.  We sure don't need it.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] help with sql query

[Richard Broersma Jr]

> idid_1id_2   date_time
> 1 101 10002006-07-04 11:25:43
> I want to find all records have same id_1, but different id_2 and have
> difference in time less than 5 minutes.
> In this case this is record 1 and record 3.
> How can I do this ?

I am sure that this will need some "tuning" to get it to work correctly but 
should give you one
way to get what you want.

select a.id, a.id_1, a.id_2, a.date_time

from table1 a join table1 b on (a.id_1 = b.id_1)
where 
  a.id_2 != b.id_2
and
 abs( a.date_time - b.date_time) < ' 5 minutes'
;

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

Re: [GENERAL] A query planner that learns

[Madison Kelly]

Alvaro Herrera wrote:

Yeah.  I invite you to do all the extra (useless) development work
required.  But please do not charge other people with it.  Whoever
investigates patents and lets pgsql-hackers know about them, is charging
the Postgres community with that work.  We sure don't need it.


As a developer (different project that uses pgsql), I am no more happy 
about the current status of the USPO than you are. I think it's a giant 
cess-pool of greed, ignorance and waste... BUT... it is currently the 
law in the USA.


How fast would Oracle, Microsoft or others jump on a chance to tear 
PostgreSQL apart by dragging it through the courts and crushing it under 
any fines (regardless of the amount)? If that happened, *all* of the 
pgsql-hacker's time would be wasted, never mind the countless other 
projects that rely on PgSQL.


As Scott said, there are 0 good answers to this problem. If PgSQL is 
going to be active in the US then it has to play by the (bent and 
twisted) rules. That is why I started my comments with "call your 
congress(wo)man". Simply ignoring that which you don't like is just far 
to risky with the sharks in our waters.


It's extra work, and it's unfair to the developers who already put their 
free time into this program, but IMHO it's the only safe way to go. 
Again though, not being a pgsql-hacker my comments here are just that.


Madi

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

Re: [GENERAL] How to _really_use a non-default tablespace

[Andras Simon]

On 10/17/06, Martijn van Oosterhout  wrote:

On Tue, Oct 17, 2006 at 04:56:09PM +0200, Andras Simon wrote:
> As a more drastic example: I created a DB on a tablespace TS, and
> copied a 25 MB file into one of its tables. I ended up having 60
> MB of extra data in $PGDATA/pg_xlog that doesn't go away even
> after dropping the database.

The xlog is not split by database, all databases share the same xlog.


OK, I see. The question then is how far does it grow. If its size is
comparable to that of the actual data, then having separate
tablespaces is not as useful as it first seemed to me. But I might be
missing something.

And there's still the question of indices...

Thanks,

Andras

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

Re: [GENERAL] How to _really_use a non-default tablespace

[Martijn van Oosterhout]

On Tue, Oct 17, 2006 at 05:54:42PM +0200, Andras Simon wrote:
> >The xlog is not split by database, all databases share the same xlog.
> 
> OK, I see. The question then is how far does it grow. If its size is
> comparable to that of the actual data, then having separate
> tablespaces is not as useful as it first seemed to me. But I might be
> missing something.

xlogs are recycled. You can control the growth somewhat by playing with
the xlog settings in the config. It should stabilise at about 16MB
times the wal segments.

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


signature.asc
Description: Digital signature

Re: [GENERAL] How to _really_use a non-default tablespace

[Andras Simon]

On 10/17/06, Martijn van Oosterhout  wrote:


xlogs are recycled. You can control the growth somewhat by playing with
the xlog settings in the config. It should stabilise at about 16MB
times the wal segments.


This is very good news! Thanks,

Andras

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

Re: [GENERAL] Database users Passwords

[Jeff Davis]

On Tue, 2006-10-17 at 10:41 -0400, DEV wrote:
> Hello all,
> 
>   I have user information in a table that I want to use to add
> users to the user roles tables that are part of postgresql.  My
> question is this: the passwords in my user table are in there as a
> text file with the data being encrypted using the crypt function, is
> there a way I can use this crypt password when I do a “CREATE ROLE
> userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB
> NOCREATEROLE”  I know that in the current CREATE ROLE I have listed
> will take a clear text password and encrypt it for me.  What do I need
> to change to use an encrypted password?
> 

If user is foo and password is bar, do:

=# select md5('barfoo');
LOG:  duration: 0.140 ms  statement: select md5('barfoo');
   md5
--
 96948aad3fcae80c08a35c9b5958cd89
(1 row)

=# create role foo login password 'md596948aad3fcae80c08a35c9b5958cd89'
nosuperuser inherit nocreatedb nocreaterole;

This seems to be lacking in the docs. At least, the only place I found
this information was a user comment in the 8.0 docs. Is this already in
the 8.1 docs? Should we add a description of the way postgresql does the
md5 hashes in the CREATE ROLE section?

Regards,
Jeff Davis


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

Re: [GENERAL] Database users Passwords

[DEV]

Okay but the issue I have is that I have the passwords already generated and
in crypt() format and would love to just use them if at all possible?

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeff Davis
Sent: Tuesday, October 17, 2006 12:36 PM
To: DEV
Cc: pgsql-general@postgresql.org; pgsql-docs@postgresql.org
Subject: Re: [GENERAL] Database users Passwords

On Tue, 2006-10-17 at 10:41 -0400, DEV wrote:
> Hello all,
> 
>   I have user information in a table that I want to use to add
> users to the user roles tables that are part of postgresql.  My
> question is this: the passwords in my user table are in there as a
> text file with the data being encrypted using the crypt function, is
> there a way I can use this crypt password when I do a "CREATE ROLE
> userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB
> NOCREATEROLE"  I know that in the current CREATE ROLE I have listed
> will take a clear text password and encrypt it for me.  What do I need
> to change to use an encrypted password?
> 

If user is foo and password is bar, do:

=# select md5('barfoo');
LOG:  duration: 0.140 ms  statement: select md5('barfoo');
   md5
--
 96948aad3fcae80c08a35c9b5958cd89
(1 row)

=# create role foo login password 'md596948aad3fcae80c08a35c9b5958cd89'
nosuperuser inherit nocreatedb nocreaterole;

This seems to be lacking in the docs. At least, the only place I found
this information was a user comment in the 8.0 docs. Is this already in
the 8.1 docs? Should we add a description of the way postgresql does the
md5 hashes in the CREATE ROLE section?

Regards,
Jeff Davis



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




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

[Tom Lane]

"Sander Steffann" <[EMAIL PROTECTED]> writes:
>> What the datetime.c code is doing is trying to find the zoneabbrev
>> in a built-in timezone table, and then adding the two together.
>> This is simply wacko.

> I think that if anyone has ever tried to use this notation they would have 
> noticed this misinterpretation of the specs.

Well, it'd work without surprise for the case of "GMT+-n", which is
undoubtedly the most common case ...

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] [GENERAL] Anyone using "POSIX" time zone offset capability?

[Sander Steffann]

Hi,


"Sander Steffann" <[EMAIL PROTECTED]> writes:

What the datetime.c code is doing is trying to find the zoneabbrev
in a built-in timezone table, and then adding the two together.
This is simply wacko.


I think that if anyone has ever tried to use this notation they would 
have

noticed this misinterpretation of the specs.


Well, it'd work without surprise for the case of "GMT+-n", which is
undoubtedly the most common case ...


H. I hadn't thought of that, but then: with the changes you proposed 
they would still get what they expect. Even though that notation would not 
conform to the POSIX docs.


Still seems like a good idea :)
Sander



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

Re: [GENERAL] A query planner that learns

[AgentM]

On Oct 17, 2006, at 10:46 , Madison Kelly wrote:


Brian Mathis wrote:
I also am NAL, but I know enough about the patent system (in the  
US) to know that ignorance *IS* a defense.  If you are ignorant of  
the patent, you only have to pay the damages.  If you knew about  
the patent and did it anyway, you have to pay *triple* damages.   
Ignorance will save you lots of money.

You may not like it, but that's the way it is.


I got that part. :) If you _do_ end up in court, plausible  
deniability helps.


My position though is that it is better, in the long term, to be  
aware of the patents and take the time to work around them so that  
*no* damages need to be paid. Or, as might be that chance in this  
case, to get a written "okay" from the patent holder for the use of  
the methods protected by the patent in a given program.


Colour me funny, but wouldn't staying out of the courts in the  
first place not be the best option?


That would be a nice, but naïve, approach. It is likely that (without  
admitting any guilt by any party) postgresql already steps on some  
patents. In fact, any project you can think of likely steps on some  
patents. There are patents on network communication: the "getting a  
message from a server to client" sort of thing.


If you spent the next twenty years searching through patents and  
creating patches for postgresql to circumvent the patents, not only  
would you turn postgresql into a shriveled raisin of its current  
self, you would be exposing postgresql to greater damages than if you  
had never looked at the patents.


***Please- for the safety of everyone on these lists- do not mention  
or link to any specific patents. This mailing list has a publicly- 
accessible archive which could be used against me or you (at least in  
the USA).***


Best regards,
M

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

Re: [GENERAL] pg_locks: who is locking ? (SOLVED!)

[Alexandre Arruda]

Tom Lane wrote:

Alexandre Arruda <[EMAIL PROTECTED]> writes:
But pg_stat_activity joined with pg_locks only give me informations 
about the lock itself.

Realy, I want a (possible) simple information: Who is locking me ?


You need a self-join to pg_locks to find the matching lock that is held
(not awaited) by some process, then join that to pg_stat_activity to
find out who that is.


Tom, thanks for explanation !!!
And if someone need, here will go my views (sorry if I made this in the 
long and complicated way)... ;)


1) For transaction locks

create or replace view locks_tr_aux as SELECT a.transaction,a.pid as 
pid_locked,b.pid as pid_locker,c.usename as user_locked FROM pg_locks a, 
pg_locks b, pg_stat_activity c where b.granted=true and a.granted=false 
and a.transaction=b.transaction and a.pid=c.procpid;


create or replace view locks_tr as select a.*,c.usename as user_locker 
from locks_tr_aux a,pg_stat_activity c where a.pid_locker=c.procpid;



2) For tables locks

create or replace view locks_tb_aux as SELECT a.relation::regclass as 
table,a.transaction,a.pid as pid_locked,b.pid as pid_locker,c.usename as 
user_locked FROM pg_locks a, pg_locks b, pg_stat_activity c where 
b.granted=true and a.granted=false and a.relation=b.relation and 
a.pid=c.procpid;


create or replace view locks_tb as select a.*,c.usename as user_locker 
from locks_tb_aux a,pg_stat_activity c where a.pid_locker=c.procpid;



3) For transactionid locks

create or replace view locks_trid_aux as SELECT a.transaction,a.pid as 
pid_locked,b.pid as pid_locker,c.usename as user_locked FROM pg_locks a, 
pg_locks b, pg_stat_activity c where b.granted=true and a.granted=false 
and a.transactionid=b.transactionid and a.pid=c.procpid and 
a.locktype='transactionid';


create or replace view locks_trid as select a.*,c.usename as user_locker 
from trava_trid_aux a,pg_stat_activity c where a.pid_lockedr=c.procpid;



select * from locks_tr;
select * from locks_tb;
select * from locks_trid;


Best Regads,


Alexandre
Aldeia Digital

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

Re: [GENERAL] Database users Passwords

[Jeff Davis]

On Tue, 2006-10-17 at 12:54 -0400, DEV wrote:
> Okay but the issue I have is that I have the passwords already generated and
> in crypt() format and would love to just use them if at all possible?
> 

PostgreSQL won't doesn't recognize crypt passwords, as far as I know.
That means that it's pretty much impossible.

Use a password recovery tool to get the plain text back, and encrypt it
using md5.

Regards,
Jeff Davis


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

Re: [GENERAL] Fast backup/restore

[Vivek Khera]

On Oct 17, 2006, at 10:43 AM, [EMAIL PROTECTED] wrote:We just tar/gzip the entire data directory.  It takes all of 20 sec.  We've successfully restored from that also.  The machine you are restoring to *must* be running the save version of postgresql you backed up from. If you successfully backed up in 20 seconds, then you have a tiny DB.  Also, if you successfully restored from that style backup, your DB must not be written to much, or you were extremely lucky to get a consistent state.

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Database users Passwords

[Shane Ambler]

Jeff Davis wrote:

On Tue, 2006-10-17 at 10:41 -0400, DEV wrote:

Hello all,

  I have user information in a table that I want to use to add
users to the user roles tables that are part of postgresql.  My
question is this: the passwords in my user table are in there as a
text file with the data being encrypted using the crypt function, is
there a way I can use this crypt password when I do a “CREATE ROLE
userid LOGIN PASSWORD 'crypt password' NOSUPERUSER INHERIT NOCREATEDB
NOCREATEROLE�  I know that in the current CREATE ROLE I have listed
will take a clear text password and encrypt it for me.  What do I need
to change to use an encrypted password?



If user is foo and password is bar, do:

=# select md5('barfoo');
LOG:  duration: 0.140 ms  statement: select md5('barfoo');
   md5
--
 96948aad3fcae80c08a35c9b5958cd89
(1 row)

=# create role foo login password 'md596948aad3fcae80c08a35c9b5958cd89'
nosuperuser inherit nocreatedb nocreaterole;

This seems to be lacking in the docs. At least, the only place I found
this information was a user comment in the 8.0 docs. Is this already in
the 8.1 docs? Should we add a description of the way postgresql does the
md5 hashes in the CREATE ROLE section?



That works the way you have done it - what you have done is calculate 
the encrypted password the same way that postgres encrypts it (using 
md5) instead of using ENCRYPTED within the create role.


The issue is that the 'crypted' version will not work if entered in 
create role that way. The entered password at login will be md5ed which 
won't match the crypt version stored.


What Dev would want to look for (probably create) is a small script that 
will read his list of crypt passwords and un-crypt them into a create 
role string that is fed to psql.


I am going on the assumption that the crypt function you refer to is the 
system level crypt (also called enigma).


something along the lines of (just in pseudo code)

for each user {
$userid = SELECT userid FROM table;
$userPass = crypt < SELECT userCryptedPasswordText FROM table;

$psqlCommand = "CREATE ROLE $userid LOGIN ENCRYPTED PASSWORD $userPass 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE"


psql < $psqlCommand
}

Not sure if you can achieve this from an sql command - my guess is you 
may get it if you setup a function in say pl/Perlu to do the 
un-crypting. But that would mean using INSERT INTO pg_authid which 
is not the recommended way (CREATE ROLE doesn't support sub-selects).
Creating a client that reads, un-crypts, then sends the CREATE ROLE 
commands would be the best and simplest way.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

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

Re: [GENERAL] Fast backup/restore

[Steve Poe]

Vivek,What methods of backup do you recommend for medium to large databases? In our example, we have a 20GB database and it takes 2 hrs to load from a pg_dump file.Thanks.Steve Poe
On 10/17/06, Vivek Khera <[EMAIL PROTECTED]> wrote:
On Oct 17, 2006, at 10:43 AM, [EMAIL PROTECTED] wrote:

We just tar/gzip the entire data directory.  It takes all of 20 sec.  We've successfully restored from that also.  The machine you are restoring to *must* be running the save version of postgresql you backed up from.
 If you successfully backed up in 20 seconds, then you have a tiny DB.  Also, if you successfully restored from that style backup, your DB must not be written to much, or you were extremely lucky to get a consistent state.

Re: [GENERAL] Fast backup/restore

[Vivek Khera]

On Oct 17, 2006, at 2:35 PM, Steve Poe wrote:


Vivek,

What methods of backup do you recommend for medium to large  
databases? In our example, we have a 20GB database and it takes 2  
hrs to load from a pg_dump file.




my largest db is about 60Gb with indexes.  reloading the data (about  
30Gb) takes 1 hour from compressed format pg_dump, and another two to  
reindex.


for increasing reload size, bump your checkpoint_segments to  
something big, like 128 or 256 depending on how much disk space you  
can spare for it.





smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] A query planner that learns

[Rafal Pietrak]

On Tue, 2006-10-17 at 10:24 -0400, Madison Kelly wrote:
> Nor am I a lawyer, but I still hold that hoping "ignorance" will be a 
> decent defense is very, very risky. In the end I am not a pgSQL 
> developer so it isn't in my hands either way.

If I may.

The "hoping, ignorance will save you" line of defence is in fact very
very risky.

But "civil disobidience" is not. Well, it may be risky, but it is valid.

There is already strong support for the opinion (e.g. European battle
against software patents), that current patent/copyright regulations,
are devastating to inovations. And in fact contradict their 'intended
goal' - the stimulation of inovations.

At this point, there is no other way but to ignore such legislation - to
the point where we can: like, by not reading patent files. Be warrned,
when the law strikes back, you loose.

Calming down a little, I'd add, that this particular piece of 16'th
century law does not quite fit todays reality.

Just my 2c  and I admit, it's not on the subject in subject :)

-- 
-R

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

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

Re: [GENERAL] Database users Passwords

[Jorge Godoy]

Shane Ambler <[EMAIL PROTECTED]> writes:

> What Dev would want to look for (probably create) is a small script that will
> read his list of crypt passwords and un-crypt them into a create role string
> that is fed to psql.

Except that the hash used is unidirectional, i.e., there's no way to decrypt
it besides a brute force attack or something like that. 

If he's got, e.g., 10 users with strong passwords this kind of thing can take
some weeks.

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] uuid c function contrib

[Ron Peterson]

Hi,

I've written some PostgreSQL C functions which expose the functionality
of Theodore Ts'o's UUID library.  I need to add a few sanity clauses
here and there, but working (mostly) code can be found here:

http://www.yellowbank.com/code/PostgreSQL/uuid/

I have one problem.  My y_uuid_time function works on my laptop
(Kubuntu/Dapper), but not on another box (Debian Sarge).  PostgreSQL
8.1.4 in both cases.

When it works, it looks like this:

rpeterso=# select y_uuid_time( y_uuid_generate_time() );
 y_uuid_time
-
 2006-10-17 14:29:34

When it doesn't, it looks like this:

rpeterso=# select y_uuid_time( y_uuid_generate_time() );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

I've been staring at my array bounds so hard it's giving me headache.
If anyone can identify what might be wrong, I'd greatly appreciate it.

Best.

-- 
Ron Peterson
https://www.yellowbank.com/

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

   http://archives.postgresql.org/

Re: [GENERAL] Database users Passwords

[Shane Ambler]

Jorge Godoy wrote:

Shane Ambler <[EMAIL PROTECTED]> writes:


What Dev would want to look for (probably create) is a small script that will
read his list of crypt passwords and un-crypt them into a create role string
that is fed to psql.


Except that the hash used is unidirectional, i.e., there's no way to decrypt
it besides a brute force attack or something like that. 


If he's got, e.g., 10 users with strong passwords this kind of thing can take
some weeks.



crypt may be a custom function (or what Dev calls something else 
altogether) which is one way and complex - that info wasn't given.


The only crypt I know of is the crypt command (FreeBSD has it at 
/usr/bin/crypt) and is also known as enigma. This is a two way 
encryption and is fast.
If that is what he is using then decrypting will not be part of the time 
issue and is the basis of the advice I gave.


According to time - decrypting a 3K file takes about .002 seconds

If a strong one way encryption has been used then he is out of luck and 
will need the users to re-enter their passwords after the accounts are 
created with another password of some sort.
Which is also another option for him even if he can decrypt what is 
currently stored.


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

  http://archives.postgresql.org/

Re: [GENERAL] Database users Passwords

[Jorge Godoy]

Shane Ambler <[EMAIL PROTECTED]> writes:

> The only crypt I know of is the crypt command (FreeBSD has it at
> /usr/bin/crypt) and is also known as enigma. This is a two way encryption
> and is fast.
> If that is what he is using then decrypting will not be part of the time
> issue and is the basis of the advice I gave.

>From the man page of the crypt that I have here:


GNU EXTENSION
   The glibc2 version of this function has the following additional
   features.  If salt is a character string starting with the three
   characters "$1$" followed by at most eight characters, and optionally
   terminated by "$", then instead of using the DES machine, the glibc
   crypt function uses an MD5-based algorithm, and outputs up to 34 bytes,
   namely "$1$$", where "" stands for the up to 8
   characters following "$1$" in the salt, followed by 22 bytes chosen
   from the set [a-zA-Z0-9./].  The entire key is significant here
   (instead of only the first 8 bytes).

   Programs using this function must be linked with -lcrypt.

CONFORMING TO
   SVID, X/OPEN, 4.3BSD, POSIX 1003.1-2001


Well...  I suppose DES is not Enigma, but I may be wrong.  I just quoted this
"extension" because it shows both algorithms used here.


-- 
Jorge Godoy  <[EMAIL PROTECTED]>


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

Re: [GENERAL] more anti-postgresql FUD

[Karen Hill]

"Merlin Moncure" wrote:

> > SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N;
>
> using offset to walk a table is extremely poor form because of:
> * poor performance
> * single user mentality
> * flat file mentality
>
> databases are lousy at this becuase they inheritly do not support
> abolute addressing of data -- nore should they, beause this is not
> what sql is all about.  in short, 'offset' is a hack, albeit a useful
> one in some cases, but dont gripe when it doesn't deliver the goods.
>
> for server side browsing use cursors or a hybrid pl/pgqsl loop. for
> client side, browse fetching relative to the last key:
>
> select * from foo where p > p1 order by p limit k;
>
> in 8.2, we get proper comparisons so you can do this with multiple part keys:
>
> select * from foo where (a1,b1,b1) > (a,b,c) order by a,b,c limit k;
>

I have 8.2 Beta 1 (Win32) on my home pc  and offset was faster than
fetching relative to the last key as measured by explain analyze.  This
was on a table with about 1,000 rows.  

regards,

karen


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

Re: [GENERAL] more anti-postgresql FUD

[Alvaro Herrera]

Karen Hill wrote:

> I have 8.2 Beta 1 (Win32) on my home pc  and offset was faster than
> fetching relative to the last key as measured by explain analyze.  This
> was on a table with about 1,000 rows.  

For such a small table the difference is probably irrelevant.  Try with
several million rows.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] query log corrupted-looking entries

[George Pavlov]

> Hmm.  If the messages are less than PIPE_BUF bytes long (4096 bytes on
> Linux) then the writes are supposed to be atomic.  Can you 
> check whether
> the interspersal cases involve messages whose total length (all lines)
> exceeds 4K?

Tom,

Some of them involve long messages (>4K), but there are many that do not
(like the ones I had posted at the start of this thread). 

I am not entirely sure where to start counting? I have been counting
from the last "LOG: statement: " string immediately preceding the
corruption and I have sometimes >4K, sometimes under. Also what is the
beginning of the message? (after the string "statement: "?) In any case
I have plenty that are way under 4K.

The good news is that most of these don't trip the query analysis tool I
am using (pgFouine). I get about 50-80 entries like this in the logs per
day and only maybe 1 in 300-400 trips the tool. Even the ones that don't
trip the tool probably cause problems and skew the stats a bit, but
that's, I hope, negligible. Still it would be good to get it to do the
right thing.

Thanks!

George

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

   http://archives.postgresql.org/

[GENERAL] not so sequential sequences

[Rhys Stewart]

Hi all, looking for a method to number a table sequentially, but the
sequence only increments if the value in a certain column is
different. as in


seq|   parish

1  | Kingston
1  | Kingston
1  | Kingston
1  | Kingston
2  | Lucea
3  | Morant Bay
3  | Morant Bay
3  | Morant Bay
4  | Port Antonio
5  | Savannah-La-Mar
5  | Savannah-La-Mar
5  | Savannah-La-Mar

so i guess i would order by a certain column and then the 'magic
sequence' would be a column that only increments when the column
changes.

Rhys

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

Re: [GENERAL] query log corrupted-looking entries

[Tom Lane]

"George Pavlov" <[EMAIL PROTECTED]> writes:
>> Hmm.  If the messages are less than PIPE_BUF bytes long (4096 bytes on
>> Linux) then the writes are supposed to be atomic.

> Some of them involve long messages (>4K), but there are many that do not
> (like the ones I had posted at the start of this thread). 

I checked around with some kernel/glibc gurus in Red Hat, and the
consensus seemed to be that we'd be better off to bypass fprintf() and
just send message strings to stderr using write() --- ie, instead of
elog.c doing

fprintf(stderr, "%s", buf.data);

do

write(fileno(stderr), buf.data, strlen(buf.data));

Anyone have any comments on possible portability risks?  In particular,
will this work on Windows?

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] exploiting features of pg to obtain polymorphism

[Karsten Hilbert]

On Mon, Oct 16, 2006 at 11:41:25AM +0200, Ivan Sergio Borgonovo wrote:

> > You can put a unique constraint and a serial default on the
> > parent table (such as a primary key). Insertion on a child
> > table will fail if the key in question already exists in the
> > base table. It may have come from another child table.
> 
> Is it really true?
> http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
True enough. I just tried it against 7.4.13.

However, it works for us because

a) we don't need unique parent table fields apart from the
   parent table pk

b) we never insert *explicitely* into the primary key field,
   neither via the base table nor via any child tables

To make this safe we should probably put triggers onto the
tables to make sure the pk isn't alter (IOW set it to
DEFAULT in a BEFORE INSERT/UPDATE trigger).

> > Updating the base table updates all the relevant child
> > tables, too. Delete will extend from base to child tables,
> > too. That way I'll have a unique serial across all the child
> > tables. I just need to take care to not use ONLY on
> > update/delete on the base table or to INSERT into the base
> > table directly (the latter isn't really harmful to the
> > issue, however).
> 
> It would be nice if at least delete fired by triggers on the parent worked.
> But it doesn't since rows inserted in children don't get inserted in parents 
> (that's OK on a OO perspective).
They do get inserted into the parent. But actions on the
child tables do not fire parent table triggers :-(

> > > Audit tables have their own pk/fk relationships and their
> > > triggers but according to my knowledge they won't be considered
> > > unless you operate on those table directly. If you operate on the
> > > data tables those triggers pk/fk won't be seen.
> > True. But I still get the unique pks since I don't operate
> > on them directly. Eventually, PG will enforce those
> > constraints, too.
> 
> You get a serial in children, not uniqueness.
I do but only because I never change the PKs explicitely,
not (yet) because PG enforces it.

Establishing use cases may over time contribute to raising
inheritance improvements further up the TODO list in terms
of priorities.

> > > even if I've the suspect the code is not complete enough
> > > to implement the features
> > Yes. Eventually it is going to be something like Veil. Or
> > rather, I suppose it will *be* (as in use) Veil.
> 
> I didn't understand. Are you referring to this?
> http://veil.projects.postgresql.org/curdocs/index.html
Yes. And, BTW, it got nothing much to do with inheritance
:-)   But it could, thinking that tables might inherit from
a Veil-enabled parent table or some such.

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

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

Re: [GENERAL] Database users Passwords

[Shane Ambler]

Jorge Godoy wrote:

Shane Ambler <[EMAIL PROTECTED]> writes:


The only crypt I know of is the crypt command (FreeBSD has it at
/usr/bin/crypt) and is also known as enigma. This is a two way encryption


Well...  I suppose DES is not Enigma, but I may be wrong.  I just quoted this
"extension" because it shows both algorithms used here.


%man crypt
ENIGMA(1)   FreeBSD General Commands Manual

NAME
 enigma, crypt -- very simple file encryption

SYNOPSIS
 enigma [-s] [-k] [password]
 crypt [-s] [-k] [password]

DESCRIPTION
The enigma utility, also known as crypt is a very simple encryption pro-
gram, working on a ``secret-key'' basis.  It operates as a filter, i.e.,
it encrypts or decrypts a stream of data from standard input, and writes
the result to standard output.  Since its operation is fully 
symmetrical,feeding the encrypted data stream again through the engine 
(using the same secret key) will decrypt it.



Which is all irrelevant and guesses unless Dev specifies which crypt he 
actually used to create his data.


--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

Re: [GENERAL] not so sequential sequences

[Alexander Staubo]

On Oct 17, 2006, at 23:18 , Rhys Stewart wrote:


Hi all, looking for a method to number a table sequentially, but the
sequence only increments if the value in a certain column is
different. as in

[snip]

Normalization could solve your problem and also improve your schema:

-- The parish table
create table parishes (
  id serial primary key,
  name varchar
);

-- Your main table
create table ... (
  ...
  parish_id integer references parishes (id)
);

Now you have the parishes table:

id |   name

1  | Kingston
2  | Lucea
3  | Morant Bay
4  | Port Antonio
5  | Savannah-La-Mar

And your main table:

...| parish_id

...| 1
...| 1
...| 1
...| 1
...| 2
...| 3
...| 3
...| 3
...| 4
...| 5
...| 5
...| 5

As you can see, the parish_id field is now your "magic sequence".


so i guess i would order by a certain column and then the 'magic
sequence' would be a column that only increments when the column
changes.


I'm assuming (perhaps wrongly) here that you don't care about a  
strictly sequential number; for example, a field indicating the  
position of something in a queue or list. I'm also assuming that the  
number has no semantic meaning, which might not fit your use case.


Alexander.


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

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

Re: [GENERAL] not so sequential sequences

[Chris Browne]

[EMAIL PROTECTED] ("Rhys Stewart") writes:
> Hi all, looking for a method to number a table sequentially, but the
> sequence only increments if the value in a certain column is
> different. as in
>
>
> seq|   parish
> 
> 1  | Kingston
> 1  | Kingston
> 1  | Kingston
> 1  | Kingston
> 2  | Lucea
> 3  | Morant Bay
> 3  | Morant Bay
> 3  | Morant Bay
> 4  | Port Antonio
> 5  | Savannah-La-Mar
> 5  | Savannah-La-Mar
> 5  | Savannah-La-Mar
>
> so i guess i would order by a certain column and then the 'magic
> sequence' would be a column that only increments when the column
> changes.

The usual way to do this would be to separate out what clearly is a
dependant table; the seq and parish values could be associated with a
smaller table that increments any time you get a new parish.

The process of doing this sort of separation is called "normalization."

-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxfinances.info/info/internet.html
Rules of the Evil Overlord #32. "I will not fly into a rage and kill a
messenger who brings me bad news  just to illustrate how evil I really
am. Good messengers are hard to come by."


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

[GENERAL] Strange behavior on non-existent field in subselect?

[Ken Tanzer]

We're a little puzzled by this (apparently) strange behavior, and would 
be curious to know what you folks make of it.  Thanks.


Ken

CREATE TABLE foo (
foo_field   integer );

CREATE TABLE par(
par_field   integer );

SELECT VERSION();

SELECT foo_field FROM par;
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
INSERT INTO foo VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
INSERT INTO par VALUES (1);
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
/* One row for every foo record, provided at least one record in par */

Which (for us) yields the following output:

Chasers=> \i strangefield.sql
CREATE TABLE
CREATE TABLE
version
---
 PostgreSQL 8.1.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
3.4.4 20050721 (Red Hat 3.4.4-2)
(1 row)

psql:strangefield.sql:11: ERROR:  column "foo_field" does not exist
 foo_field
---
(0 rows)

INSERT 0 1
 foo_field
---
(0 rows)

INSERT 0 1
 foo_field
---
 1
(1 row)


begin:vcard
fn:Kenneth Tanzer
n:Tanzer;Kenneth
org:Downtown Emergency Service Center;Information Services
adr:;;515 Third Avenue;Seattle;WA;98104;USA
email;internet:[EMAIL PROTECTED]
title:Director of Information Services
tel;work:(206) 464-1570 x 3061
tel;fax:(206) 624-4196
x-mozilla-html:TRUE
url:http://www.desc.org
version:2.1
end:vcard


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

Re: [GENERAL] Strange behavior on non-existent field in subselect?

[Ragnar]

On þri, 2006-10-17 at 15:58 -0700, Ken Tanzer wrote:
> We're a little puzzled by this (apparently) strange behavior, and would 
> be curious to know what you folks make of it.  Thanks.

not sure exactly what you are referring to, but:
(rearranged quotes to group output with SQL)

> SELECT foo_field FROM par;
> psql:strangefield.sql:11: ERROR:  column "foo_field" does not exist

hopefully, no mystery here.


> SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);

if par is empty, then this SELECT will return 0 rows,
otherwise it is equivalent to SELECT foo_field from foo

>  foo_field
> ---
> (0 rows)

foo is empty, so no rows returned

> INSERT INTO foo VALUES (1);
> SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
>   foo_field
> ---
> (0 rows)

par is empty, so the IN operator fails for the foo row

> INSERT INTO par VALUES (1);
> SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
>   foo_field
> ---
>   1
> (1 row)

when par contains at least one row, the subselect will 
return foo_field once per row of par.
the IN operator will ignore duplicates, so the result
is the same for any number of rows in par greater than 0

gnari



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

[GENERAL] Fixed-point scalars?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

PG 8.1.5

The closed-source RDBMS that we are hoping to archive on PostgreSQL
8.1.5 has fixed-point scalars, where the data is *stored* as a plain
old scalar, but is run-time *interpreted* as having a decimal point.
 For example:

  SMALLINT(2)
  INTEGER(2)
  BIGINT(2)

We use INTEGER(2) *extensively* for monetary values that won't
exceed $21,474,836.47, and BIGINT(2) for those times where it might.

This is very useful since integer arithmetic is so fast, and you
know the field will always be 4 bytes.

Are these data-types not in PG, or am I missing something?

Also, how do you calculate the size of a NUMERIC?

Lastly, I know they are the same, but which is the
"preferred/standard" type: NUMERIC or DECIMAL?

Thanks
- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFNXl0S9HxQb37XmcRAsoiAJ0f8UGrYRm8eE3eX6EJYDJn6riV1wCfScHC
J7l8E1S7WS++1wDxW/9k6b0=
=zhgS
-END PGP SIGNATURE-

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

Re: [GENERAL] uuid c function contrib

[Ron Peterson]

On Tue, Oct 17, 2006 at 03:25:05PM -0400, Ron Peterson wrote:

> I've written some PostgreSQL C functions which expose the functionality
> of Theodore Ts'o's UUID library.  I need to add a few sanity clauses
> here and there, but working (mostly) code can be found here:
> 
> http://www.yellowbank.com/code/PostgreSQL/uuid/

> I have one problem.  My y_uuid_time function works on my laptop
> (Kubuntu/Dapper), but not on another box (Debian Sarge).

The usual remedy of walking away from the problem and coming back fresh
helped me realize my stupid mistake.  I still believe in sanity clauses,
so I have a little more finessing to do, but the posted code works.

Implements uuid_generation functions which return bytea values, a
function to convert the bytea values to the canonical hex form, and a
function to extract the time from time-based uuids.  If you can use
this, please do.

Examples:

rpeterso=# select y_uuid_to_hex( y_uuid_generate() );
y_uuid_to_hex
--
 0bf56bb1-1e47-472f-82a8-cc8134d23d91
(1 row)

rpeterso=# select y_uuid_to_hex( y_uuid_generate_random() );
y_uuid_to_hex
--
 8e239390-1bd2-4b01-9ad9-8b3f0cfbb5b5
(1 row)

rpeterso=# select y_uuid_to_hex( y_uuid_generate_time() );
y_uuid_to_hex
--
 e3a3a694-5e43-11db-82ad-0014bf7c74ed
(1 row)

rpeterso=# select y_uuid_time( y_uuid_generate_time() );
y_uuid_time

 2006-10-17 20:59:25.101671

Best.

-- 
Ron Peterson
https://www.yellowbank.com/


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

   http://archives.postgresql.org/

[GENERAL] Maximum size of database

[roopa perumalraja]

Hi     I would like to know that what can be the maximum size of database in postgres 8.1.4. Currently my database size is 37GB & its pretty slow. I wonder if its b'cos of huge amount of data in it.     Thanks in advance.  Roopa 
		How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

Re: [GENERAL] Fixed-point scalars?

[Michael Glaesemann]

On Oct 18, 2006, at 9:46 AM, Ron Johnson wrote:


  SMALLINT(2)
  INTEGER(2)
  BIGINT(2)



Are these data-types not in PG, or am I missing something?



http://www.postgresql.org/docs/current/interactive/ 
datatype.html#DATATYPE-NUMERIC


The docs list 2 byte, 4 byte, and 8 byte integer types.



Also, how do you calculate the size of a NUMERIC?

Lastly, I know they are the same, but which is the
"preferred/standard" type: NUMERIC or DECIMAL?


Later on, same page:

http://www.postgresql.org/docs/current/interactive/ 
datatype.html#DATATYPE-NUMERIC-DECIMAL


The actual storage requirement is two bytes for each group of four  
decimal digits, plus eight bytes overhead.




The types decimal and numeric are equivalent. Both types are part  
of the SQL standard.


The PostgreSQL are quite extensive and helpful.

Michael Glaesemann
grzm seespotcode net



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

  http://archives.postgresql.org/

Re: [GENERAL] Maximum size of database

[Michael Fuhr]

On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> I would like to know that what can be the maximum size of database in
> postgres 8.1.4.

http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

> Currently my database size is 37GB & its pretty slow.  I wonder if
> its b'cos of huge amount of data in it.

37GB isn't all that huge; as the FAQ mentions, much larger databases
exist.  Without more information we'll have to ask some of the
standard questions:

What's your hardware configuration?
What operating system and version are you using?
What are your non-default postgresql.conf settings?
Are you vacuuming and analyzing the database regularly?
How much concurrent activity do you have?
Does anything other than PostgreSQL run on the box?

If you have a specific query that's slow then please post the EXPLAIN
ANALYZE output.  Also, you might get more help on the pgsql-performance
list.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Maximum size of database

[Edward Macnaghten]

roopa perumalraja wrote

 
I would like to know that what can be the maximum size of database in 
postgres 8.1.4. Currently my database size is 37GB & its pretty slow. 
I wonder if its b'cos of huge amount of data in it.
 



http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

Slowness reason cannot be really investigated without more information 
of the specifics, but could well be indexing issues.


Eddy

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

Re: [GENERAL] not so sequential sequences

[louis gonzales]

Rhys,
You could create a sequence, then make the "seq" attribute to your table 
have a default value of:

seq integer default nextval('your_sequence')

Then every time an insert is done into your table, the "seq" will 
increment.  You alternatively could make your "insert" statement have 
for that position, the nextval('your_sequence')


Does that help?

Rhys Stewart wrote:


Hi all, looking for a method to number a table sequentially, but the
sequence only increments if the value in a certain column is
different. as in


seq|   parish

1  | Kingston
1  | Kingston
1  | Kingston
1  | Kingston
2  | Lucea
3  | Morant Bay
3  | Morant Bay
3  | Morant Bay
4  | Port Antonio
5  | Savannah-La-Mar
5  | Savannah-La-Mar
5  | Savannah-La-Mar

so i guess i would order by a certain column and then the 'magic
sequence' would be a column that only increments when the column
changes.

Rhys

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




--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

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

[GENERAL] Postgres Database Slow

[roopa perumalraja]

Hi     Currently my database size is 38GB and it is pretty slow in whatever I do with it like take a backing up, vaccuming, reindexing, running all queries. Why is that? Is it possible to improve the performance.     Thanks in advance  Roopa    
		Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

Re: [GENERAL] Postgres Database Slow

[Gregory S. Williamson]

Please do not simply repost your obscure and almost meaningless original 
question.

Please respond to the earlier posts asking for more information. People might 
be willing to help, but they can't unless you respond to them.

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of roopa perumalraja
Sent:   Tue 10/17/2006 9:49 PM
To: pgsql-general@postgresql.org
Cc: 
Subject:[GENERAL] Postgres Database Slow

Hi
   
  Currently my database size is 38GB and it is pretty slow in whatever I do 
with it like take a backing up, vaccuming, reindexing, running all queries. Why 
is that? Is it possible to improve the performance.
   
  Thanks in advance
  Roopa
   


-
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ 
countries) for 2¢/min or less.


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4535b0a088521470421014&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4535b0a088521470421014!
---




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

[GENERAL] Connection Pooling for Postgres

[Sandeep Kumar Jakkaraju]

Hi All

Is there any inbuilt facility in postgres for connection pooling .. ??

Thanks
-
sandeep

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

  http://archives.postgresql.org/

[GENERAL] Is Postgres good for large Applications

[Sandeep Kumar Jakkaraju]

Hi All

Is Postgres good for large Applications ??
I mean where we have to make many simulataneous connections...

Thanks
Sandeep

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

Re: [GENERAL] Is Postgres good for large Applications

[louis gonzales]

Is your server capable?  Does it have enough resources to handle many 
connections?


many = ???  100, 200, 1,000,000,000 are they concurrent users?

'good for large applications' = ??? I'd say, how large your 
application is doesn't matter, right... cause that's the front end.  How 
well is it coded and does it make efficient logical SQL calls to a well 
structured database... that's another question.


I've got a question, "who wants to play, ask 20 questions?"

Sorry for the sarcasm... but this is now 2:00 a.m. EST, and questions 
have to be specific to warrant an answer.


If I were to say:

many = YES
good for large applications = YES

Wouldn't you come back then with, "How many?" and "How large of 
applications?"



Sandeep Kumar Jakkaraju wrote:


Hi All

Is Postgres good for large Applications ??
I mean where we have to make many simulataneous connections...

Thanks
Sandeep

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




--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

Re: [GENERAL] Maximum size of database

[roopa perumalraja]

Thanks for your reply.     I have answered your questions below.      1 & 2) System: Microsoft Windows XP Professional  Version 2002    Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM     3) shared_buffers = 2    autovaccum = on     4) Yes, I am vacuuming & analyzing the database once every day.     5) No concurrent activities, means I run one command at a time.     6) Nothing else running on the box other than Postgres.     I hope these answers will try to solve my problem. Thanks again.     Roopa  Michael Fuhr <[EMAIL PROTECTED]> wrote:  On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:> I would like to know that what can be the maximum size of database in> postgres 8.1.4.http://www.postgresql.org/docs/faqs.FAQ.html#item4.4> Currently my database size is 37GB & its pretty slow. I wonder if> its b'cos of huge amount of data in it.37GB isn't all that huge; as the FAQ mentions, much larger databasesexist. Without more information we'll have to ask some of thestandard questions:What's your hardware configuration?What operating system and version are you using?What are your non-default postgresql.conf settings?Are you vacuuming and analyzing the database regularly?How much concurrent activity do you have?Does anything other than PostgreSQL run on the box?If you have a specific query that's slow then please post the
 EXPLAINANALYZE output. Also, you might get more help on the pgsql-performancelist.-- Michael Fuhrsignature 
	

	
		Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

[GENERAL] COPY FROM STDIN instead of INSERT

[Ilja Golshtein]

Hello!

One important use case in my libpq based application (PostgreSQL 8.1.4) is a 
sort of massive data loading.

Currently it is implemented as a series of plain normal INSERTs
(binary form of PQexecParams is used) and the problem here it is pretty slow.

I've tried to play with batches and with peculiar constructions
like INSERT (SELECT .. UNION ALL SELECT ..) to improve performance, but not 
satisfied with the result I've got.

Now I try to figure out if it is possible to use COPY FROM STDIN instead of 
INSERT if I have to insert, say, more then 100 records at once. 

Hints are highly appreciated.

The only limitaion mentioned in Manual is about Rules and I don't care about 
this since I don't use Rules. 
Am I going to come across with any other problems (concurrency, reliability, 
compatibility, whatever) on this way? 

Many thanks.

-- 
Best regards
Ilja Golshtein

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

Re: [GENERAL] Maximum size of database

[louis gonzales]

also, run
EXPLAIN
on any command, show the results of this.  In particular, if you have 
some commands that are taking 'even longer?'




roopa perumalraja wrote:


Thanks for your reply.
 
I have answered your questions below.
 
1 & 2) System: Microsoft Windows XP Professional

  Version 2002
  Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM
 
3) shared_buffers = 2

autovaccum = on
 
4) Yes, I am vacuuming & analyzing the database once every day.
 
5) No concurrent activities, means I run one command at a time.
 
6) Nothing else running on the box other than Postgres.
 
I hope these answers will try to solve my problem. Thanks again.
 
Roopa

*/Michael Fuhr <[EMAIL PROTECTED]>/* wrote:

On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> I would like to know that what can be the maximum size of
database in
> postgres 8.1.4.

http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

> Currently my database size is 37GB & its pretty slow. I wonder if
> its b'cos of huge amount of data in it.

37GB isn't all that huge; as the FAQ mentions, much larger databases
exist. Without more information we'll have to ask some of the
standard questions:

What's your hardware configuration?
What operating system and version are you using?
What are your non-default postgresql.conf settings?
Are you vacuuming and analyzing the database regularly?
How much concurrent activity do you have?
Does anything other than PostgreSQL run on the box?

If you have a specific query that's slow then please post the EXPLAIN
ANALYZE output. Also, you might get more help on the pgsql-performance
list.

-- 
Michael Fuhr





signature


Get your own web address for just $1.99/1st yr 
<%20http://us.rd.yahoo.com/evt=43290/*http://smallbusiness.yahoo.com/domains>. 
We'll help. Yahoo! Small Business 
. 




--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

Re: [GENERAL] Anyone using "POSIX" time zone offset capability?

[James Cloos]

> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes:

Tom> The weird thing about this allegedly-POSIX notation is the combination
Tom> of a symbolic name and a further offset from it.

AIUI, it is not a further offset but rather (mostly-)redundant data
specifying the exact offset from UTC¹ the text tz specifies.  Having
both provides easy parsing both for humans (the text) and for code
(the number).

-JimC

[1] Of course POSIX time is not really offset from UTC, since POSIX
pretends there have been no leap seconds since 1970.  As such
the timestamps are technically ambiguous as to whether the specify
real UTC-based time or POSIX time   (Currently there is a
23-second difference between the two.)

-- 
James Cloos <[EMAIL PROTECTED]> OpenPGP: 0xED7DAEA6

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly