Re: [GENERAL] multimaster

[Karsten Hilbert]

On Sun, Jun 03, 2007 at 07:47:04PM +0200, Alexander Staubo wrote:

 For example, part of the point of having validations declared on the
 model is so that you can raise user-friendly errors (and pipe them
 through gettext for localization) such as Your password must be at
 least 4 characters long and contain only letters and digits.

If anyone is interested, we have sort of re-implemented gettext in SQL:

http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/?root=gnumed

(see the gmI18n-*.sql stuff)

In essence it enables you to write queries like so

select pk, name, _(name) as l10n_name from states

which will give you a localized name for states.name in l10n_name.

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

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

Re: [GENERAL] Corruption of files in PostgreSQL

[Paolo Bizzarri]

Hi Scott,

in fact, we were using a 2.6.12 kernel. Can this be a problem?

Best regards.

Paolo Bizzarri

On 6/4/07, Scott Marlowe [EMAIL PROTECTED] wrote:

Paolo Bizzarri wrote:
 On 6/2/07, Tom Lane [EMAIL PROTECTED] wrote:
 Paolo Bizzarri [EMAIL PROTECTED] writes:
  On 6/2/07, Tom Lane [EMAIL PROTECTED] wrote:
  Please provide a reproducible test case ...

  as explained above, the problem seems quite random. So I need to
  understand what we have to check.

 In this context reproducible means that the failure happens
 eventually.  I don't care if the test program only fails once in
 thousands of tries --- I just want a complete self-contained example
 that produces a failure.

 As said above, our application is rather complex and involves several
 different pieces of software, including Zope, OpenOffice both as
 server and client, and PostgreSQL. We are absolutely NOT sure that the
 problem is inside PostgreSQL.

 What we are trying to understand is, first and foremost, if there are
 known cases under which PostgreSQL can truncate a file.

I would suspect either your hardware (RAID controller, hard drive, cache
etc) or your OS (kernel bug, file system bug, etc)

For instance:

http://lwn.net/Articles/215868/

documents a bug in the 2.6 linux kernel that can result in corrupted
files if there are a lot of processes accessing it at once.




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

  http://archives.postgresql.org/

[GENERAL] First cut for 8.3 RPM set

[Devrim GÜNDÜZ]

Hello,

http://developer.postgresql.org/~devrim/rpms/v8.3devel/

is the SRPM that I built using yesterday's CVS snaphot (the tarball in
FTP site).

This is for the people who want to test 8.3+RPM in their distros.

Please let me know if you find packaging errors.

Regards, 
-- 
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/




signature.asc
Description: This is a digitally signed message part

[GENERAL] Foreign keys and indexes

[Marc Compte]

Dear list,

This might be too basic for a question but I just couldn't find the 
answer so far.


Does PostgreSQL create an implicit index also for foreign keys? or must 
I create it explicitly?


Thank you,

Marc Compte

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

Re: [GENERAL] Encrypted column

[Tino Wildenhain]

Ranieri Mazili schrieb:

Hello,

I need to store users and passwords on a table and I want to store it 
encrypted, but I don't found documentation about it, how can I create a 
table with columns user and password with column password 
encrypted and how can I check if user and password are correct using 
a sql query ?


Passwords are usually not encrypted but hashed instead. A common hash
function is available in postgres w/o any additional extension:

md5()

The rule is, if two hashes compare equal, then the original data must
be equal (yes, there are chances for collisions, but practically very
low. See also sha1 and friends in the pgcrypto contrib module)

Regards
Tino

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

Re: [GENERAL] High-availability

[Simon Riggs]

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:

 It's not too hard to put together a warm standby synchronous
 replication mechanism with overhead that isn't too much more than what
 you incur by enabling PITR...  Such systems can also have very fast
 failover on failure detection (via heartbeat2), and be synchronous.

Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



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

   http://archives.postgresql.org/

[GENERAL] Database design wisdom needed

[Erick Papadakis]

Hi

Sorry for this somewhat long email but I think it is relevant to most
people who run online databases. I am having trouble optimizing UPDATE
queries on a certain semi-large table that is only growing larger.
I've come across some very interesting thoughts from this list, so I
thought I'll post my conundrum here. Would truly appreciate any
guidance or pointers.

I have done the messy DBA's job of breaking one table into three,
which should actually have been one. Just did it to make sure the
large main table doesn't keep getting updated with small crumbs of
information.

This main table stores what users of our online service have created.
These are small Flash games, but over the last seven years, we have
collected about 5 million of these. The primary key of this table is
GAME_ID. In our online account management interface I also need to
show each producer's creations only to himself, so of course I need a
PRODUCER_ID.

Above that, in our service we have had three more bits of information
for each created game:

- Number of views for the game (online views)
- Number of plays of the game (online people playing)
- Number of unique plays of the game (based on ip or
subscriber_id..some games do not require login to play)

Because each of these pieces of information was individual to each
game, my original table looked like this:


   GAME Table
   ---
   GAME_ID
   PRODUCER_ID
   VIEWS_COUNT
   PLAYED_COUNT
   PLAYED_COUNT_UNIQUE


In the beginning, everytime someone played a game online, we updated
the PLAYED_COUNT in this table. When someone viewed a game, we updated
the VIEWS_COUNT. It was simple and it worked as it reflected the
business logic.

Over time, however, I realized that while GAME_ID and PRODUCER_ID
remained basically static, the next three columns had a very high rate
of change. For example, VIEWS_COUNT would increment every second for
popular games. The PLAYED_COUNT would increment everytime someone
played, but the fact that we have unique into the equation means
that I had to break this away into a log table, so that I could GROUP
BY ip address and then update the columns accordingly in the GAME
table.  So I had:


   GAME_PLAYED_LOG Table
   --
   GAME_ID
   PLAYER_IP_ADDR



From here, I would select COUNT(PLAYER_IP_ADDR) and COUNT(DISTINCT

PLAYER_IP_ADDR), grouped by GAME_ID.  I would then update the main
GAME table's columns with these values. This process was done every
hour to make sure we didn't have a huge number of rows to manipulate.

Anyway, now we're realizing that we are becoming increasingly popular
and our tables are large enough for us to require optimization.
Because GAME table is mostly for static information, and is also
required to be highly available because of our online interface, I
have just taken away the VIEWS_COUNT and PLAYED_COUNT into another
table.


   GAME Table (~5 million rows)
   
   GAME_ID
   PRODUCER_ID


   GAME_COUNTS Table (also ~5 million rows of course)
   ---
   GAME_ID
   VIEWS_COUNT
   PLAYED_COUNT
   PLAYED_COUNT_UNIQUE


This way, from our GAME_PLAYED_LOG, we do not need to update the main
GAME table every hour, but only the GAME_COUNTS. This leaves the GAME
table free to do its job. That is our main table, so keeping it static
was our priority.

My problems:

1. Because we are a popular website, the GAME_PLAYED_LOG table grows
at massive rates every hour. About 1,000,000 records. Doing COUNT
queries on this table already is pretty resource hogging, even if we
do them every hour. Especially the DISTINCT grouping to get unique
played count.

1.5. After we update the GAME_COUNTS table, we also truncate the
GAME_PLAYED_LOG table because its records have no significance
anymore. This hourly deletion leads to fragmentation etc in the table.

2. The UPDATE queries to GAME_COUNTS table are also a problem. The
sheer number of UPDATE queries is massive. Of the 1,000,000 views
every hour, many are just one or two views of many, many games
(remember, we have 5,000,000 games). So every hour we end up running
thousands of small UPDATE queries like:

  update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1...

3. Finally, the JOIN queries between the GAME table and GAME_COUNTS
table are not very fast. They take about 1 second each, even if I do a
LIMIT 20 in every query.


Now I suspect this scenario is *very* common in online databases that
must report viewer statistics. How are you guys doing it? What am I
missing? Isn't the decoupling of the static information from the more
frequently updated information a good idea? Would using a different
storage engine for different kinds of tables help -- engines that were
better for INSERT and SELECT, while others that were good for UPDATE?
We ran MySQL until a year ago but we have switched to Pg since we had
data corruption issues. I am sure I'm missing some tricks in the Pg
world, and would 

Re: [GENERAL] There can be only one! How to avoid the highlander-problem.

[Gregory Stark]

Erwin Brandstetter [EMAIL PROTECTED] writes:

 I postulate further that a king only be king of his own people (rules out
 multiple kingships, too).

That's not how it's worked in the past :)

If you have a nation table wouldn't you just have a king_id column in that
table which is a foreign key reference to man_id?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


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

   http://archives.postgresql.org/

Re: [GENERAL] High-availability

[Chander Ganesan]

Simon Riggs wrote:

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:

  

It's not too hard to put together a warm standby synchronous
replication mechanism with overhead that isn't too much more than what
you incur by enabling PITR...  Such systems can also have very fast
failover on failure detection (via heartbeat2), and be synchronous.



Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.

  
Aside from a bunch of customized pgbench benchmarks (on the 9.6 GB 
sample database we use), which are better than nothing, but far from 
the best, not really.  In my experience, the larger the database; 
slower the commit rate; and less frequently the checkpoints - the better 
the performance of synchronous warm-replication.  In our tests, higher 
commit rates and more frequent checkpoints incur a higher penalty.  
Basically, the more WAL activity the higher the cost.


If I have time I'll see if we can run a more meaningful metric (need to 
generate a smaller database for that) the next time we have a 
performance tuning class (in August).


The failover time is tunable to some extent...via heartbeat2 (incurs  
1% performance penalty, but with sub-second failover this can go up a 
bit), and can be pretty quick (I usually set it up with around a 3 
second failover time on node failure, then factor that in with the 
amount of time required for WAL auto-recovery)...it really depends a lot 
on what your metric is for failure (since node failover is probably 
the worst worst case).


--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Re: [GENERAL] Foreign keys and indexes

[Filip Rembiałkowski]

2007/6/5, Marc Compte [EMAIL PROTECTED]:

Dear list,

This might be too basic for a question but I just couldn't find the
answer so far.

Does PostgreSQL create an implicit index also for foreign keys? or must
I create it explicitly?


FK is just a constraint, you wil have to create indexes manually if
you need them.


--
Filip Rembiałkowski

---(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] $libdir

[Martijn van Oosterhout]

On Mon, Jun 04, 2007 at 08:25:22PM -0700, Ian Harding wrote:
 I know this is a question that gets asked a zillion times and is
 almost always pilot error.

I don't know much about this but the complaint is this:

 The usual error about file does not exist relative to
 $libdir/tsearch2 gets generated.

And you have:

 In that directory are the libtsearch2.so... files along with lots of
 others, with 755 permissions, owned by root.

Either the library should be tsearch.so not libtsearch.so, or the
request should be for $libdir/libtsearch or something (perhaps a
directory is missing or something and it should be
tsearch/libtsearch.so).

Please provide the *exact* error messages if you want more detailed
help.

Hope this helps,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

[GENERAL] CREATE RULE with WHERE clause

[Ranieri Mazili]

Hello,

I need to create a rule, but I need that it have a WHERE clause, how bellow:


CREATE RULE rule_role_sul AS
   ON SELECT TO t1 WHERE roles = 'role_sul'
   DO INSTEAD 
   SELECT field1, field2 FROM t2;


CREATE RULE rule_role_sul AS
   ON SELECT TO t1 WHERE roles = 'role_norte'
   DO INSTEAD 
   SELECT field3, field4 FROM t2;


Someone knows how can I do it?

I appreciate any help

Thanks 



---(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] Foreign keys and indexes

[Ragnar]

On þri, 2007-06-05 at 11:49 +0200, Marc Compte wrote:

 Does PostgreSQL create an implicit index also for foreign keys?

no


  or must I create it explicitly?

if you want one, yes.

not everyone wants an index on all their foreign keys,
but they can be useful in some circumstances.

gnari



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

Re: [GENERAL] Foreign keys and indexes

[Michael Fuhr]

On Tue, Jun 05, 2007 at 11:49:20AM +0200, Marc Compte wrote:
 Does PostgreSQL create an implicit index also for foreign keys? or must 
 I create it explicitly?

PostgreSQL doesn't create an index on the referencing column(s) of
a foreign key constraint; if you want an index then you'll need to
create it yourself.

-- 
Michael Fuhr

---(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] COPY error

[Michael Fuhr]

On Mon, Jun 04, 2007 at 02:12:00PM -0400, ABHANG RANE wrote:
 Im trying to load data from a file using copy command. At the end of 
 the data, I have appended copy statement as
 
 copy tablename(col1, col2) from stdin with delimiter as '\t';
 .\

COPY should go before the data and end-of-data (\. not .\) should
go after the data.  Also, in text mode the default delimiter is the
tab character (\t) so you can omit it unless you prefer to be
explicit.  Try something like this (with a tab after 2713):

copy tablename (col1, col2) from stdin;
2713{3.70952,1.45728,0.134339,3.99197,2.22381,-0.435095,6.9}
\.

-- 
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] Encrypted column

[Brian Mathis]

On 6/5/07, Tino Wildenhain [EMAIL PROTECTED] wrote:

Ranieri Mazili schrieb:
 Hello,

 I need to store users and passwords on a table and I want to store it
 encrypted, but I don't found documentation about it, how can I create a
 table with columns user and password with column password
 encrypted and how can I check if user and password are correct using
 a sql query ?

Passwords are usually not encrypted but hashed instead. A common hash
function is available in postgres w/o any additional extension:

md5()

The rule is, if two hashes compare equal, then the original data must
be equal (yes, there are chances for collisions, but practically very
low. See also sha1 and friends in the pgcrypto contrib module)

Regards
Tino



Remember, you would also want to add some sort of salt before you ran
the hash, otherwise your password list is vulnerable to a simple
brute-force attack.

---(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] Foreign keys and indexes

[Richard Broersma Jr]

 Does PostgreSQL create an implicit index also for foreign keys? or must 
 I create it explicitly?

No, you foreign keys are not automatically indexed.  They only way they would 
be is if the FK is
part of a composite unique or primary key.  So you will probably have to create 
your one indexes
on FKs.

Regards,
Richard Broersma Jr.


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

   http://archives.postgresql.org/

Re: [GENERAL] Database design wisdom needed

[Ron Johnson]

On 06/05/07 00:46, Erick Papadakis wrote:

Hi

Sorry for this somewhat long email but I think it is relevant to most
people who run online databases. I am having trouble optimizing UPDATE
queries on a certain semi-large table that is only growing larger.
I've come across some very interesting thoughts from this list, so I
thought I'll post my conundrum here. Would truly appreciate any
guidance or pointers.

I have done the messy DBA's job of breaking one table into three,
which should actually have been one. Just did it to make sure the
large main table doesn't keep getting updated with small crumbs of
information.

This main table stores what users of our online service have created.
These are small Flash games, but over the last seven years, we have
collected about 5 million of these. The primary key of this table is
GAME_ID. In our online account management interface I also need to
show each producer's creations only to himself, so of course I need a
PRODUCER_ID.

Above that, in our service we have had three more bits of information
for each created game:

- Number of views for the game (online views)
- Number of plays of the game (online people playing)
- Number of unique plays of the game (based on ip or
subscriber_id..some games do not require login to play)

Because each of these pieces of information was individual to each
game, my original table looked like this:


   GAME Table
   ---
   GAME_ID
   PRODUCER_ID
   VIEWS_COUNT
   PLAYED_COUNT
   PLAYED_COUNT_UNIQUE


In the beginning, everytime someone played a game online, we updated
the PLAYED_COUNT in this table. When someone viewed a game, we updated
the VIEWS_COUNT. It was simple and it worked as it reflected the
business logic.

Over time, however, I realized that while GAME_ID and PRODUCER_ID
remained basically static, the next three columns had a very high rate
of change. For example, VIEWS_COUNT would increment every second for
popular games. The PLAYED_COUNT would increment everytime someone
played, but the fact that we have unique into the equation means
that I had to break this away into a log table, so that I could GROUP
BY ip address and then update the columns accordingly in the GAME
table.  So I had:


   GAME_PLAYED_LOG Table
   --
   GAME_ID
   PLAYER_IP_ADDR


 From here, I would select COUNT(PLAYER_IP_ADDR) and COUNT(DISTINCT
PLAYER_IP_ADDR), grouped by GAME_ID.  I would then update the main
GAME table's columns with these values. This process was done every
hour to make sure we didn't have a huge number of rows to manipulate.

Anyway, now we're realizing that we are becoming increasingly popular
and our tables are large enough for us to require optimization.
Because GAME table is mostly for static information, and is also
required to be highly available because of our online interface, I
have just taken away the VIEWS_COUNT and PLAYED_COUNT into another
table.


   GAME Table (~5 million rows)
   
   GAME_ID
   PRODUCER_ID


   GAME_COUNTS Table (also ~5 million rows of course)
   ---
   GAME_ID
   VIEWS_COUNT
   PLAYED_COUNT
   PLAYED_COUNT_UNIQUE


This way, from our GAME_PLAYED_LOG, we do not need to update the main
GAME table every hour, but only the GAME_COUNTS. This leaves the GAME
table free to do its job. That is our main table, so keeping it static
was our priority.


And good database theory, too, since the number of times that a game 
is played is not tied to the PRODUCER_ID.


golf clap


My problems:

1. Because we are a popular website, the GAME_PLAYED_LOG table grows
at massive rates every hour. About 1,000,000 records. Doing COUNT
queries on this table already is pretty resource hogging, even if we
do them every hour. Especially the DISTINCT grouping to get unique
played count.


Any index on GAME_PLAYED_LOG?


1.5. After we update the GAME_COUNTS table, we also truncate the
GAME_PLAYED_LOG table because its records have no significance
anymore. This hourly deletion leads to fragmentation etc in the table.

2. The UPDATE queries to GAME_COUNTS table are also a problem. The
sheer number of UPDATE queries is massive. Of the 1,000,000 views
every hour, many are just one or two views of many, many games
(remember, we have 5,000,000 games). So every hour we end up running
thousands of small UPDATE queries like:

  update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1...

3. Finally, the JOIN queries between the GAME table and GAME_COUNTS
table are not very fast. They take about 1 second each, even if I do a
LIMIT 20 in every query.


Now I suspect this scenario is *very* common in online databases that
must report viewer statistics. How are you guys doing it? What am I
missing? Isn't the decoupling of the static information from the more
frequently updated information a good idea? Would using a different
storage engine for different kinds of tables help -- engines that were
better for INSERT and 

Re: [GENERAL] jdbc pg_hba.conf error

[Bhavana.Rakesh]

Starting the postmaster with a -i option did the trick. 

/ -i Allows clients to connect via TCP/IP (Internet domain)  
connections.  Without  this
 option,  only local Unix domain socket connections are 
accepted. This option corre-

 sponds to setting tcpip_socket=true in postgresql.conf.

 --tcpip-socket=false has the opposite effect of this option.

/However, this still does not solve my problem of having a java 
application connect to the postgres DB server.  I get the following error:


Couldn't connect: print out a stack trace and exit.
org.postgresql.util.PSQLException: A connection error has occurred: 
org.postgres  ql.util.PSQLException: FATAL: no 
pg_hba.conf entry for host 127.0.0.1, user b  
rakesh, database testing123, SSL off


   at 
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(Abstrac  
tJdbc1Connection.java:337)
   at 
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ  
dbc1Connection.java:214)

   at org.postgresql.Driver.connect(Driver.java:139)
   at java.sql.DriverManager.getConnection(DriverManager.java:559)
   at java.sql.DriverManager.getConnection(DriverManager.java:189)
   at db_connect_pgsql.main(db_connect_pgsql.java:25)


-Bhavana
Tom Lane wrote:

Bhavana.Rakesh [EMAIL PROTECTED] writes:
  

Here's what happens when I specify the port number



  

[EMAIL PROTECTED] ~]$ psql -U brakesh -p 5000 -h 127.0.0.1 -d testing123
psql: could not connect to server: Connection refused
Is the server running on host 127.0.0.1 and accepting
TCP/IP connections on port 5000?



5000 being a nonstandard port number, it's certainly possible that the
kernel is filtering this connection attempt.  Connection refused might
mean either that there's no process listening to TCP port 5000, or that
the kernel rejected the connection before looking for a listening process.

In any case it seems highly probable that you do have two postmasters
running on this machine, one at port 5000 and one at port 5432.  The
psql calls without an explicit -p switch would have defaulted to port
5432 unless you've done something strange to your installation.

regards, tom lane
  

Re: [GENERAL] Encrypted column

[Ron Johnson]

On 06/04/07 17:54, Guy Rouillier wrote:

Ranieri Mazili wrote:

Hello,

I need to store users and passwords on a table and I want to store it 
encrypted, but I don't found documentation about it, how can I create 
a table with columns user and password with column password 
encrypted and how can I check if user and password are correct 
using a sql query ?


Many people consider two-way encryption to be insecure; two-way 
encryption means you can decrypt a value if you know the key, and it is 
insecure because you usually have to put the key into the source code. 
That means at least one person in your company, the programmer 
maintaining the source code, can learn all of your users' passwords.


Two-way encryption is needed for companies that store customer 
credit cards.


But yes, I've always worried about that.

 One 
way around that is to hash the value instead.  Then to validate, at 
runtime you hash the user-entered password using the same hash function, 
and validate that it matches the stored hash.  No one in your company 
ever knows end-user passwords.





--
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


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

Re: [GENERAL] Foreign keys and indexes

[A. Kretschmer]

am  Tue, dem 05.06.2007, um 11:49:20 +0200 mailte Marc Compte folgendes:
 Dear list,
 
 This might be too basic for a question but I just couldn't find the 
 answer so far.
 
 Does PostgreSQL create an implicit index also for foreign keys?

No, only for primary keys to enforce the uniqueness.


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

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

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

Re: [GENERAL] Foreign keys and indexes

[Gregory Stark]

Marc Compte [EMAIL PROTECTED] writes:

 Does PostgreSQL create an implicit index also for foreign keys? or must I
 create it explicitly?

It won't allow you to create a foreign key that points to a column without a
unique index on it.

postgres=# create table b (i integer references a(i));
ERROR:  there is no unique constraint matching given keys for referenced table 
a

However if you ever update or delete the referenced records then it also helps
performance to have an index on the referencing column which Postgres doesn't
enforce.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


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

Re: [GENERAL] There can be only one! How to avoid the highlander-problem.

[Erwin Brandstetter]

On Jun 5, 8:35 am, [EMAIL PROTECTED] (Gregory Stark) wrote:
 Erwin Brandstetter [EMAIL PROTECTED] writes:
  I postulate further that a king only be king of his own people (rules out
  multiple kingships, too).

 That's not how it's worked in the past :)

Yeah i know. :) That's why I had to postulate this one explicitly.


 If you have a nation table wouldn't you just have a king_id column in that
 table which is a foreign key reference to man_id?

Have a look at my model 3.) above ..

Regards
Erwin


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

Re: [GENERAL] Database design wisdom needed

[Andrew Sullivan]

On Tue, Jun 05, 2007 at 01:46:42PM +0800, Erick Papadakis wrote:
 
GAME_COUNTS Table (also ~5 million rows of course)
---
GAME_ID
VIEWS_COUNT
PLAYED_COUNT
PLAYED_COUNT_UNIQUE

This is a poor normalisation.  While views_count is necessarily a
superset of played_count, the two values are not functions of one
another really (for instance, there will be games that have been
viewed but never played); therefore they should be in separate
tables, I'd say.  I'd also like to point out that a 5 million row
table is actually not very big.

In order to make this fast, I'd probably implement a dirtyish hack
along the following lines.

create table game_views_summ (
game_id int references games(id),
views bigint,
counted_at timestamp with time zone);

create table game_views (
game_id int references games(id),
viewed_at timestamp with time zone);

Then, you have a daemon that summarizes data in game_views regularly
into game_views_summ and deletes the data in game_views that's
just been updated.  If you do this more or less all the time, you can
keep the vacuums up to date and keep the bloat to a minimum.  It's
an increase in overall I/O, but it ought to be smoother than just
trying to cope with it in big lumps.  (A similar strategy will work
for the play counts.)

One thing to be careful about is that you don't end up with
game_views with very few rows, but a huge number of dead rows.  This
two-table approach can rapidly become a pessimal implementation in
the event you are too efficient at eliminating the
rows-to-be-summarized, but have a lot of dead rows that are
unrecoverable because of running transactions.  You'll end up with a
seqscan on a table that contains four live rows, except that it's
400M.  That pattern is a killer.


 1.5. After we update the GAME_COUNTS table, we also truncate the
 GAME_PLAYED_LOG table because its records have no significance
 anymore. This hourly deletion leads to fragmentation etc in the table.

Are you truncating, or deleting?  TRUNCATE leaves no dead rows,
fragmentation, c.  That's its point. 

 (remember, we have 5,000,000 games). So every hour we end up running
 thousands of small UPDATE queries like:
 
   update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1...

Why is this bad?  (You don't actually need thousands of these, I
think, because you ought to be able to design one query to do it all. 
But I'd be unhappy with the locking, I think, given what you're
trying to do.)

 3. Finally, the JOIN queries between the GAME table and GAME_COUNTS
 table are not very fast. They take about 1 second each, even if I do a
 LIMIT 20 in every query.

This sounds like something's wrong in your query or your plan. 
EXPLAIN ANALYSE is your friend here.  I'd suspect vacuum issues.

Oh, one other thing.  I noted you're storing the player's IP address. 
You do know that maps very poorly to actual individuals on the other
end, right?

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

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

Re: [GENERAL] There can be only one! How to avoid the highlander-problem.

[Erwin Brandstetter]

On Jun 5, 5:10 am, Lew [EMAIL PROTECTED] wrote:
 Erwin Brandstetter wrote:
  CREATE TABLE king
  (
 king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE
  CASCADE ON DELETE CASCADE,
 nation_id INTEGER UNIQUE,
 FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id)
  ON UPDATE CASCADE ON DELETE CASCADE
  );

 I like this.

On Jun 5, 5:10 am, Lew [EMAIL PROTECTED] wrote:
 Erwin Brandstetter wrote:
  CREATE TABLE king
  (
 king_id INTEGER PRIMARY KEY REFERENCES man (man_id) ON UPDATE
  CASCADE ON DELETE CASCADE,
 nation_id INTEGER UNIQUE,
 FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id)
  ON UPDATE CASCADE ON DELETE CASCADE
  );

 I like this.

On a second inspection, I had a typo in the code above, and the second
foreign key is redundant. So we get:

CREATE TABLE king
(
   man_id INTEGER PRIMARY KEY,
   nation_id INTEGER UNIQUE,
   FOREIGN KEY (man_id, nation_id) REFERENCES man (man_id, nation_id)
ON UPDATE CASCADE ON DELETE CASCADE
);

(...)
  We are still avoiding circular references.

 I'm not so sure we need to avoid that.

Yeah, I don't think we have to avoid it. But as it comes at no cost,
I'd take it. I have commented on possible complications arising from
circular references above.

Regards
Erwin


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

   http://archives.postgresql.org/

Re: [GENERAL] Encrypted column

[Alvaro Herrera]

Ron Johnson wrote:
 On 06/04/07 17:54, Guy Rouillier wrote:

 Many people consider two-way encryption to be insecure; two-way 
 encryption means you can decrypt a value if you know the key, and it is 
 insecure because you usually have to put the key into the source code. 
 That means at least one person in your company, the programmer 
 maintaining the source code, can learn all of your users' passwords.
 
 Two-way encryption is needed for companies that store customer 
 credit cards.

I thought that the advice for companies storing customer CCs was: don't.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [GENERAL] CREATE RULE with WHERE clause

[Richard Broersma Jr]

 CREATE RULE rule_role_sul AS
 ON SELECT TO t1 WHERE roles = 'role_sul'
 DO INSTEAD 
 SELECT field1, field2 FROM t2;
 
 CREATE RULE rule_role_sul AS
 ON SELECT TO t1 WHERE roles = 'role_norte'
 DO INSTEAD 
 SELECT field3, field4 FROM t2;

From: http://www.postgresql.org/docs/8.2/interactive/sql-createrule.html
...
WHERE condition
Any SQL conditional expression (returning boolean). The condition expression 
may not refer to any
tables except NEW and OLD, and may not contain aggregate functions. 
...

This statement to me implies that only ON {INSERT | UPDATE | DELETE } actions 
can use the WHERE
syntax since only inserts, updates, and deletes product the NEW.* and OLD.* 
tables.  

Also, NEW.* and OLD.* only apply to a single tuple in the view that is being 
changed.  So I do not
think you can not use the where syntax in your query since it does not and 
cannot reference a NEW
or OLD tuple.

Regards,
Richard Broersma Jr.

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

Re: [GENERAL] Encrypted column

[Marko Kreen]

On 6/5/07, Tino Wildenhain [EMAIL PROTECTED] wrote:

Ranieri Mazili schrieb:
 Hello,

 I need to store users and passwords on a table and I want to store it
 encrypted, but I don't found documentation about it, how can I create a
 table with columns user and password with column password
 encrypted and how can I check if user and password are correct using
 a sql query ?

Passwords are usually not encrypted but hashed instead. A common hash
function is available in postgres w/o any additional extension:

md5()

The rule is, if two hashes compare equal, then the original data must
be equal (yes, there are chances for collisions, but practically very
low. See also sha1 and friends in the pgcrypto contrib module)


Both md5 and sha1 are bad for passwords, no salt and easy to
bruteforce - due to the tiny amount of data in passwords.

Proper ways is to use crypt() function from pgcrypto module.
Due to historical accident is has bad name which hints at
encryption, actually its only purpose is to hash passwords.
Read more in pgcrypto doc.

--
marko

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

Re: [GENERAL] Encrypted column

[Brian Mathis]

On 6/5/07, Marko Kreen [EMAIL PROTECTED] wrote:

On 6/5/07, Tino Wildenhain [EMAIL PROTECTED] wrote:
 Ranieri Mazili schrieb:
  Hello,
 
  I need to store users and passwords on a table and I want to store it
  encrypted, but I don't found documentation about it, how can I create a
  table with columns user and password with column password
  encrypted and how can I check if user and password are correct using
  a sql query ?

 Passwords are usually not encrypted but hashed instead. A common hash
 function is available in postgres w/o any additional extension:

 md5()

 The rule is, if two hashes compare equal, then the original data must
 be equal (yes, there are chances for collisions, but practically very
 low. See also sha1 and friends in the pgcrypto contrib module)

Both md5 and sha1 are bad for passwords, no salt and easy to
bruteforce - due to the tiny amount of data in passwords.

Proper ways is to use crypt() function from pgcrypto module.
Due to historical accident is has bad name which hints at
encryption, actually its only purpose is to hash passwords.
Read more in pgcrypto doc.

--
marko



If you salt them yourself, there's no problem with md5 or sha1, and
they are arguably more secure than the old crypt call.  Most modern
linuxes use md5 for password storage.

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

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

Re: [GENERAL] Encrypted column

[Ron Johnson]

On 06/05/07 08:59, Alvaro Herrera wrote:

Ron Johnson wrote:

On 06/04/07 17:54, Guy Rouillier wrote:


Many people consider two-way encryption to be insecure; two-way 
encryption means you can decrypt a value if you know the key, and it is 
insecure because you usually have to put the key into the source code. 
That means at least one person in your company, the programmer 
maintaining the source code, can learn all of your users' passwords.
Two-way encryption is needed for companies that store customer 
credit cards.


I thought that the advice for companies storing customer CCs was: don't.


Sometimes you must.

An example from my industry: transponder toll tags and toll roads. 
 The customer pre-pays a certain amount based on expected usage, 
and every time he drives thru a plaza, his balance decreases.  Once 
it drops to a certain threshold, more money needs to be added to the 
account.


If he is a CASH/CHEK customer, a light at the lane flashes yellow 
and (depending on the Agency) a message pops up saying, Balance 
low, so he drives over to the customer service center, stands in 
line and pays his cash.


If he is a CC customer, the system (which I am DBA of) bills his 
card directly, saving the customer much time and effort.


--
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


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

Re: [GENERAL] Can someone have a look at my pg_hba.conf file ?

[Oliver Elphick]

On Mon, 2007-06-04 at 17:05 +0200, Steven De Vriendt wrote:
 Hi,
 
 I'm trying to reach my postgres database via a remote connection. Yet
 my connection is refused when I try to do that.
 I'm using Ubuntu Feisty
 Following lines are now in my pg_hba.conf-file:
...
 # Connections for all PCs on the subnet
 #
 # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
 local  all all 127.0.0.1/32 255.255.255.0  trust

That should be host, not local.  Local means unix sockets on the
same machine.  Secondly, 127.0.0.1 is itself only the local machine.
Third, if you specify a CIDR address (with /nn) you don't specify a mask
as well.  You want something like 192.168.1.0/24 rather than
127.0.0.1/32 255.255.255.0.


 When I try the following command:
 
 netstat -nlp | grep 5432
 
 I see my subnet mask isn't included:
 
 [EMAIL PROTECTED]:~# netstat -nlp | grep 5432
 tcp0  0 127.0.0.1:5432  0.0.0.0:*
 LISTEN 8292/postmaster
 unix  2  [ ACC ] STREAM LISTENING 27162
 8292/postmaster /var/run/postgresql/.s.PGSQL.5432

As Tom has already said, change listen_addresses in postgresql.conf and
restart the postmaster.  If you were connecting with the pg_hba.conf you
have listed, you should be seeing:

psql: FATAL:  missing or erroneous pg_hba.conf file
HINT:  See server log for details.

 
 Can someone help me out ?
 
 Thanks !
 
 Steven
 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend
-- 
Oliver Elphick  [EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
 
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


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

Re: [GENERAL] Encrypted column

[Marko Kreen]

On 6/5/07, Brian Mathis [EMAIL PROTECTED] wrote:

On 6/5/07, Marko Kreen [EMAIL PROTECTED] wrote:
 Both md5 and sha1 are bad for passwords, no salt and easy to
 bruteforce - due to the tiny amount of data in passwords.

 Proper ways is to use crypt() function from pgcrypto module.
 Due to historical accident is has bad name which hints at
 encryption, actually its only purpose is to hash passwords.
 Read more in pgcrypto doc.

If you salt them yourself, there's no problem with md5 or sha1, and
they are arguably more secure than the old crypt call.  Most modern
linuxes use md5 for password storage.


No, both md5 and sha1 are actually easier to bruteforce than
the old DES-based crypt.  Ofcourse that does not mean that
old DES-crypt is good idea.  Pgcrypto's crypt() supports bit
more modern md5crypt and bf-crypt algoriths which give much
higher security margin.  It can be argued that bf-crypt is the
state-of-the-art algorithm for password hashing.

--
marko

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

Re: [GENERAL] CREATE RULE with WHERE clause

[Michael Glaesemann]

On Jun 5, 2007, at 8:11 , Ranieri Mazili wrote:


CREATE RULE rule_role_sul AS
   ON SELECT TO t1 WHERE roles = 'role_sul'
   DO INSTEADSELECT field1, field2 FROM t2;

CREATE RULE rule_role_sul AS
   ON SELECT TO t1 WHERE roles = 'role_norte'
   DO INSTEADSELECT field3, field4 FROM t2;


I don't believe you can include a WHERE clause like this. From the  
documentation[1]:


http://www.postgresql.org/docs/8.2/interactive/rules-views.html#RULES- 
SELECT


Currently, there can be only one action in an ON SELECT rule, and  
it must be an unconditional SELECT action that is INSTEAD. This  
restriction was required to make rules safe enough to open them for  
ordinary users, and it restricts ON SELECT rules to act like views.


You can use views instead (which are implemented using the rule  
system), but I'm not sure how you would handle it in this case. I  
believe you'd have to implement two views:


CREATE VIEW rule_role_sul AS
SELECT field1, field2
FROM t2
WHERE roles = 'role_sul';

CREATE VIEW rule_role_norte AS
SELECT field3, field4
FROM t2
WHERE roles = 'role_norte';

Hope this helps.

Michael Glaesemann
grzm seespotcode net



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

Re: [GENERAL] Corruption of files in PostgreSQL

[Greg Smith]

On Tue, 5 Jun 2007, Paolo Bizzarri wrote:


On 6/4/07, Scott Marlowe [EMAIL PROTECTED] wrote:

http://lwn.net/Articles/215868/
documents a bug in the 2.6 linux kernel that can result in corrupted
files if there are a lot of processes accessing it at once.


in fact, we were using a 2.6.12 kernel. Can this be a problem?


That particular problem appears to be specific to newer kernels so I 
wouldn't think it's related to your issue.


Tracking down random crashes of the sort you're reporting is hard.  As 
Scott rightly suggested, the source of problem could be easily be any 
number of hardware components or low-level software like the kernel.  The 
tests required to really certify that a server is suitable for production 
use can take several days worth of testing.  The normal approach here 
would be to move this application+data to another system and see if the 
problem is still there; that lets you rule out all the hardware at once. 
That would do something else you should be thinking about--making 
absolutely sure you can backup and restore your data, and that the 
corruption you're seeing isn't causing information to be lost in your 
database.


The general flow of figuring out the cause for random problems goes 
something like this:


1) Check for memory errors.  http://www.memtest86.com/ is a good tool for 
PCs.  That will need to run for many hours.


2) Run the manufacturer's disk utilities to see if any of your disks are 
going bad.  You might be able to do this using Linux's SMART tools instead 
without even taking the server down; if you're not using those already you 
should look into that.  http://www.linuxjournal.com/article/6983 is a good 
intro here.


3) Boot another version of Linux and run some low-level disk tests there. 
A live CD/DVD like Knoppix and Ubuntu is the easiest way to do that.


4) If everything above passes, upgrade to the kernel version used on the 
live CD/DVD and see if the problem goes away.


You can try skipping right to #4 here and playing with the kernel first, 
but understand that if your underlying hardware has issues, that may cause 
more corruption (with possible data loss) rather than less.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Foreign keys and indexes

[Marc Compte]

Thanks to everyone for the prompt reply :)

Good thing about answers is when they raise up new questiosn, so you can 
keep on learning all the time.


This one answer, for instance, brings me another question. Does having a 
composite primary mean the system will create an individual index on 
each of the fields? or is the index created on the composition only?


For instance, in the implementation of a N:M relationship, declaring the 
primary as (foreign1, foreign2) will create two indexes? or just one?


Thanks again

Marc Compte

En/na Richard Broersma Jr ha escrit:
Does PostgreSQL create an implicit index also for foreign keys? or must 
I create it explicitly?



No, you foreign keys are not automatically indexed.  They only way they would 
be is if the FK is
part of a composite unique or primary key.  So you will probably have to create 
your one indexes
on FKs.

Regards,
Richard Broersma Jr.


  



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

Re: [GENERAL] TSEARCH2: disable stemming in indexes and triggers

[Erwin Moller]

On Thu, 2007-05-31 at 20:27, Teodor Sigaev wrote:
  I found out that using 'simple' instead of 'default' when using 
  to_tsvector() does excactly that, but I don't know how to change my 
  triggers and indexes to keep doing the same (using 'simple'). 
 
 Suppose, your database is initialized with C locale. So, just mark 
 simple configuration as default:
 
 # update pg_ts_cfg set locale=null where ts_name='default';
 # update pg_ts_cfg set locale='C' where ts_name='simple';

Thanks Teodor.

That did the trick. :-)
At first I didn't see a change, but after reconnecting to the database
it worked for some reason beyound my meager knowledge.

Thanks to Oleg Bartunov too for his suggestion about writing my own
procedure.
This solution seemed simpler and worked the first time right away.

Thanks for your help!

Regards,
Erwin Moller

 
 If your locale setting is not C then mark needed configuration with your 
   locale.
 
 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?
 
http://archives.postgresql.org/
 


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

Re: [GENERAL] Encrypted column

[Marko Kreen]

On 6/5/07, Marko Kreen [EMAIL PROTECTED] wrote:

both md5 and sha1 are actually easier to bruteforce than
the old DES-based crypt.


If this statement seems weird - the problem is the speed.
MD5 and SHA1 are just faster algorithms than des-crypt.

And there's nothing wrong with fast general-purpose algorithms,
as long their cryptographic properties hold.  Starting from
20-30 bytes the bruteforce is really not an option.

But if you have under 10 bytes (let be honest - you have
6 bytes...) the speed start to matter, because it is possible
on random laptop to simply try all combinations.

--
marko

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

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

Re: [GENERAL] Foreign keys and indexes

[Scott Marlowe]

Marc Compte wrote:

Thanks to everyone for the prompt reply :)

Good thing about answers is when they raise up new questiosn, so you 
can keep on learning all the time.


This one answer, for instance, brings me another question. Does having 
a composite primary mean the system will create an individual index on 
each of the fields? or is the index created on the composition only?


For instance, in the implementation of a N:M relationship, declaring 
the primary as (foreign1, foreign2) will create two indexes? or just one? 


Just one (and please don't top post.  :) )

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

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

Re: [GENERAL] Foreign keys and indexes

[Oliver Elphick]

On Tue, 2007-06-05 at 17:07 +0200, Marc Compte wrote:
 
 For instance, in the implementation of a N:M relationship, declaring
 the 
 primary as (foreign1, foreign2) will create two indexes? or just one?

Just one

-- 
Oliver Elphick  [EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
 
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


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

[GENERAL] lo or bytea streaming engine??

[volunteer]

helloany future for lo or bytea streaming engine withsecurity likehttp://pbxt.blogspot.com/2007/06/geting-blob-out-of-database-with-blob.html
many thank youssincerelysiva

Re: [GENERAL] Encrypted column

[Brian Mathis]

On 6/5/07, Marko Kreen [EMAIL PROTECTED] wrote:

On 6/5/07, Marko Kreen [EMAIL PROTECTED] wrote:
 both md5 and sha1 are actually easier to bruteforce than
 the old DES-based crypt.

If this statement seems weird - the problem is the speed.
MD5 and SHA1 are just faster algorithms than des-crypt.

And there's nothing wrong with fast general-purpose algorithms,
as long their cryptographic properties hold.  Starting from
20-30 bytes the bruteforce is really not an option.

But if you have under 10 bytes (let be honest - you have
6 bytes...) the speed start to matter, because it is possible
on random laptop to simply try all combinations.

--
marko



pgcrypto also supports md5, so I'm not sure what you're referring to
here.  As I already mentioned, *salting* before you hash is a very
important step.  I'm not sure if you saw that in my post.  Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths.  Then it would be
very simple to look up each hash and get the original text.  With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.

In any case, pgcrypto seems to be a nice and full featured tool, so
one should use that instead of rolling their own.

---(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] Encrypted column

[Andrew Sullivan]

On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:
 
 If he is a CC customer, the system (which I am DBA of) bills his 
 card directly, saving the customer much time and effort.

So surely what you have is a completely separate system that has
exactly one interface to it, that is signaled to provide a
transaction number and that only ever returns such a transaction
number to the online system, and that is very tightly secured,
right?

It is possible to make trade-offs in an intelligent manner, for sure,
but you sure as heck don't want that kind of data stored online with
simple reversible encryption.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

---(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] lo or bytea streaming engine??

[Martijn van Oosterhout]

On Tue, Jun 05, 2007 at 08:12:26AM -0700, [EMAIL PROTECTED] wrote:
 
hello
any future for lo or bytea streaming engine with security
like [1]http://pbxt.blogspot.com/2007/06/geting-blob-out-of-database-w
ith-blob.html

If that page is all there is on it it seems to me to be (a) a terrible
idea (b) trivial to implement.

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   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] Encrypted column

[Steve Atkins]

On Jun 5, 2007, at 7:28 AM, Ron Johnson wrote:


On 06/05/07 08:59, Alvaro Herrera wrote:

Ron Johnson wrote:

On 06/04/07 17:54, Guy Rouillier wrote:
Many people consider two-way encryption to be insecure; two-way  
encryption means you can decrypt a value if you know the key,  
and it is insecure because you usually have to put the key into  
the source code. That means at least one person in your company,  
the programmer maintaining the source code, can learn all of  
your users' passwords.
Two-way encryption is needed for companies that store customer  
credit cards.
I thought that the advice for companies storing customer CCs was:  
don't.


Sometimes you must.

An example from my industry: transponder toll tags and toll  
roads.  The customer pre-pays a certain amount based on expected  
usage, and every time he drives thru a plaza, his balance  
decreases.  Once it drops to a certain threshold, more money needs  
to be added to the account.


If he is a CASH/CHEK customer, a light at the lane flashes yellow  
and (depending on the Agency) a message pops up saying, Balance  
low, so he drives over to the customer service center, stands in  
line and pays his cash.


If he is a CC customer, the system (which I am DBA of) bills his  
card directly, saving the customer much time and effort.


Public key encryption can help here. Encrypt with the public key when  
it goes into the database, keep the private key on a separate, well  
protected system that's just used for recurring CC billing.


Cheers,
  Steve


---(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] $libdir

[Ian Harding]

On 6/5/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote:

On Mon, Jun 04, 2007 at 08:25:22PM -0700, Ian Harding wrote:
 I know this is a question that gets asked a zillion times and is
 almost always pilot error.

I don't know much about this but the complaint is this:

 The usual error about file does not exist relative to
 $libdir/tsearch2 gets generated.

And you have:

 In that directory are the libtsearch2.so... files along with lots of
 others, with 755 permissions, owned by root.

Either the library should be tsearch.so not libtsearch.so, or the
request should be for $libdir/libtsearch or something (perhaps a
directory is missing or something and it should be
tsearch/libtsearch.so).



I saw that discrepancy, but assumed there was a prepending of lib
somewhere in the search.  Turns out that is exactly the problem, and
changing the tsearch2.sql file to reflect

$libdir/libtsearch2

works.  I will try to figure out how this happened and let the NetBSD
package maintainer know.

Thanks!

- Ian

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

Re: [GENERAL] Encrypted column

[Tino Wildenhain]

Marko Kreen schrieb:

On 6/5/07, Tino Wildenhain [EMAIL PROTECTED] wrote:

Ranieri Mazili schrieb:
 Hello,

 I need to store users and passwords on a table and I want to store it
 encrypted, but I don't found documentation about it, how can I create a
 table with columns user and password with column password
 encrypted and how can I check if user and password are correct 
using

 a sql query ?

Passwords are usually not encrypted but hashed instead. A common hash
function is available in postgres w/o any additional extension:

md5()

The rule is, if two hashes compare equal, then the original data must
be equal (yes, there are chances for collisions, but practically very
low. See also sha1 and friends in the pgcrypto contrib module)


Both md5 and sha1 are bad for passwords, no salt and easy to
bruteforce - due to the tiny amount of data in passwords.


Err. I did not mention salt but nobody prevents you from using
a salt with md5 and sha.

Regards
Tino

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

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

Re: [GENERAL] Large Database \d: ERROR: cache lookup failed for relation ...

[Erik Jones]

I originally sent this message from my gmail account yesterday as we  
were having issues with our work mail servers yesterday, but seeing  
that it hasn't made it to the lists yet, I'm resending from my  
registered address.  You have my apologies if you receive this twice.


Thomas F. O'Connell tf ( at ) o ( dot ) ptimized ( dot ) com writes:
 I'm dealing with a database where there are ~150,000 rows in

 information_schema.tables. I just tried to do a \d, and it came back
 with this:

 ERROR:  cache lookup failed for relation [oid]

 Is this indicative of corruption, or is it possibly a resource issue?

Greetings,

This message is a follow-up to Thomas's message quoted above (we're  
working together on the same database). He received one response when  
he sent the above message which was from Tom Lane and can be easily  
summarized as him having said that that could happen tables were  
being created or dropped while running the \d in psql. Unfortunately,  
that wasn't the case, we have now determined that there is some  
corruption in our database and we are hoping some of you back-end  
gurus might have some suggestions.


How we verified that there is corruption was simply to reindex all of  
our tables in addition to getting the same errors when running a dump  
this past weekend.  We so far have a list of five tables for which  
reindex fails with the error: ERROR: could not open relation with  
OID  (sub  with the five different #s) and one that fails  
reindexing with ERROR: x is an index where is an index on a  
completely different table. After dropping all of the indexes on  
these tables (a couple didn't have any to begin with), we still  
cannot run reindex on them. In addition, we can't drop the tables  
either (we get the same errors). We can however run alter table  
statements on them. So, we have scheduled a downtime for an evening  
later this week wherein we plan on bringing the database down for a  
REINDEX SYSTEM and before that we are going to run a dump excluding  
those tables, restore that on a separate machine and see if these  
errors crop up there anywhere. Is there anything else anyone can  
think of that we can do to narrow down where the actual corruption is  
or how to fix it?


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate  market in style.
Visit us online at http://www.myemma.com



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

[GENERAL] Join field values

[veejar]

Hello!

I have such field in my table:

field1
---
1
2
3
4
5


I want to get such result from select:
'1,2,3,4,5' - join all values in field1 with ','
// result must be varchar.

Help to write SELECT-query for this task.

Re: [GENERAL] Encrypted column

[Marko Kreen]

On 6/5/07, Brian Mathis [EMAIL PROTECTED] wrote:

pgcrypto also supports md5, so I'm not sure what you're referring to
here.


digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))


As I already mentioned, *salting* before you hash is a very
important step.  I'm not sure if you saw that in my post.  Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths.  Then it would be
very simple to look up each hash and get the original text.  With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.


I dont think its practical method tho'.  Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.

But if they have salts then for each word you try you need to
hash it for each salt.  Which basically gives the effect that
each hash needs to be attacked separately.

In case of attacking one hash the salt does not matter,
only the algorithm counts then.  In that case as i said,
event salted md5 is weaker than des-crypt.

--
marko

---(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] PITR Base Backup on an idle 8.1 server

[Marco Colombo]

Simon Riggs wrote:
 Marco Colombo wrote:

my method


...is dangerous


Ok, but why? Once again, I'm asking: what _exactly_ can go wrong?

 so we don't get loads of new DBAs picking up this idea
but missing the exact point of danger.

I'm one of them. I'm _am_ missing the exact point of danger.

 Making the assumption that its OK to archive WAL files in the pg_xlog
^^
 directory exposes you to the risk of having them deleted by the
 archiver, which will invalidate your backup.
  

I'm sorry I'm really having a hard time following you here... what is 
to archive and the archiver? The archive_command? The tar in the 
backup procedure? What do you mean by deleted? AFAIK, files in pg_xlog 
are first renamed (and only if and after the archive_command returned 
true) and later overwritten to. Never deleted. Anyway, how could that 
invalidate the backup? It's all about making a self-contained backup. 
What happens after that, it's irrelevant.


Hey, I haven't come here proposing a new revolutionary way to perform 
backups! I've made pretty clear it was for a not-so-common case. And 
anyway, I've just asked what may be wrong with my procedure, since it 
seems to fit _my_ needs and it makes _my_ life simpler, and _I_ don't 
see any flaw in it. It may be useful to others, _if_ it's correct. If 
not, I'd like to know why.


Can you provide a simple failure scenario, please? That would help me 
understand what I'm missing...


.TM.


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

Re: [GENERAL] PITR Base Backup on an idle 8.1 server

[Simon Riggs]

On Tue, 2007-06-05 at 18:39 +0200, Marco Colombo wrote:
 I'm asking: what _exactly_ can go wrong?

If a checkpoint occurs while taking the backup then the contents of the
files will be overwritten and you will be unable to rollforward from
before the backup until after the backup. This will give you the FATAL
error message WAL ends before end time of backup dump. You won't know
this until you have attempted recovery using those files, even if the
scripts give rc=0.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



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

Re: [GENERAL] Corruption of files in PostgreSQL

[Scott Marlowe]

Greg Smith wrote:

On Tue, 5 Jun 2007, Paolo Bizzarri wrote:


On 6/4/07, Scott Marlowe [EMAIL PROTECTED] wrote:

http://lwn.net/Articles/215868/
documents a bug in the 2.6 linux kernel that can result in corrupted
files if there are a lot of processes accessing it at once.


in fact, we were using a 2.6.12 kernel. Can this be a problem?


That particular problem appears to be specific to newer kernels so I 
wouldn't think it's related to your issue.


That is not entirely correct.  The problem was present all the way back 
to the 2.5 kernels, before the 2.6 kernels were released.  However, 
there was an update to the 2.6.18/19 kernels that made this problem much 
more likely to bite.  There were reports of data loss for many people 
running on older 2.6 kernels that mysteriously went away after updating 
to post 2.6.19 kernels (or in the case of redhat, the updated 2.6.9-44 
or so kernels, which backported the fix.)


So, it IS possible that it's the kernel, but not likely.  I'm still 
betting on a bad RAID controller or something like that.  But updating 
the kernel probably wouldn't be a bad idea.


---(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] PITR Base Backup on an idle 8.1 server

[Greg Smith]

On Tue, 5 Jun 2007, Marco Colombo wrote:

AFAIK, files in pg_xlog are first renamed (and only if and after the 
archive_command returned true) and later overwritten to. Never deleted.


No, they get deleted sometimes, too.  Not often, but it can happen under 
heavy load if more segments get temporarily created than are normally 
needed.  At checkpoint time, only 2*checkpoint_segments+1 xlog files are 
kept; if there are more than that, they are removed.  Probably never 
happen on your system from what you've described of it, but it is a 
possibility.


As Simon just pointed out, the danger with the approach you're taken comes 
from what happens if a checkpoint occurs in the middle of your backup. 
You've probably never seen that happen either.  As long as that continues 
to be true, you might be OK for now, but you really need to get to where 
you're following the recommended procedure rather than trying to do 
something a little different.  There are too many edge cases here that 
could^H^H^H^H^Hwill bite you one day.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(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] $libdir

[Tom Lane]

Ian Harding [EMAIL PROTECTED] writes:
 I saw that discrepancy, but assumed there was a prepending of lib
 somewhere in the search.  Turns out that is exactly the problem, and
 changing the tsearch2.sql file to reflect
 $libdir/libtsearch2
 works.  I will try to figure out how this happened and let the NetBSD
 package maintainer know.

Yeah, this is definitely a packaging error, because tsearch2.so is what
the file is supposed to be named.

regards, tom lane

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

Re: [GENERAL] Join field values

[Jeff Davis]

On Tue, 2007-06-05 at 19:33 +0300, veejar wrote:
 Hello!
 
 I have such field in my table:
 
 field1
 ---
 1 
 2
 3
 4
 5 
 
 
 I want to get such result from select:
 '1,2,3,4,5' - join all values in field1 with ','
 // result must be varchar. 
 

Look into writing a simple function:

http://www.postgresql.org/docs/current/static/plpgsql.html

Also, consider that you should have an ORDER BY somewhere, to make sure
the values get joined in the order that you expect.

Regards,
Jeff Davis


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

Re: [GENERAL] lo or bytea streaming engine??

[volunteer]

bytea streaming engine is easy?? kindlt explain how?? how
http://localhost:8080/database/table/bytea_column/id_column=value get
data without select??

sincerely
siva

 Original Message 
Subject: Re: [GENERAL] lo or bytea streaming engine??
From: Martijn van Oosterhout [EMAIL PROTECTED]
Date: Tue, June 05, 2007 11:29 am
To: [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org

On Tue, Jun 05, 2007 at 08:12:26AM -0700, [EMAIL PROTECTED]
wrote:
 
 hello
 any future for lo or bytea streaming engine with security
 like
[1]http://pbxt.blogspot.com/2007/06/geting-blob-out-of-database-w
 ith-blob.html
 
If that page is all there is on it it seems to me to be (a) a terrible
idea (b) trivial to implement.
 
Have a nice day,
-- 
Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/
 From each according to his ability. To each according to his ability
to litigate.


---(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] Join field values

[Ragnar]

On þri, 2007-06-05 at 19:33 +0300, veejar wrote:
 Hello!
 
 I have such field in my table:
 
 field1
 ---
 1 
 2
 3
 4
 5 
 
 
 I want to get such result from select:
 '1,2,3,4,5' - join all values in field1 with ','
 // result must be varchar. 
 
 Help to write SELECT-query for this task.

create an aggregate function and use that in your select.
http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html

google reminded me of the mysql compatibility project,
whose implementation for group_concat() can be found here:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mysqlcompat/mysqlcompat/aggregate.sql?rev=1.2content-type=text/x-cvsweb-markup


gnari


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

Creditcard Number Security was Re: [GENERAL] Encrypted column

[Peter Childs]

On 05/06/07, Andrew Sullivan [EMAIL PROTECTED] wrote:


On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:

 If he is a CC customer, the system (which I am DBA of) bills his
 card directly, saving the customer much time and effort.

So surely what you have is a completely separate system that has
exactly one interface to it, that is signaled to provide a
transaction number and that only ever returns such a transaction
number to the online system, and that is very tightly secured,
right?

It is possible to make trade-offs in an intelligent manner, for sure,
but you sure as heck don't want that kind of data stored online with
simple reversible encryption.

A





Unfortunately you still need to store them somewhere,  and all systems can
be hacked.  Yes its a good idea to store them on a separate system and this
is an important part of designing your systems to ensure that the simple
user interface is somehow limited.

Peter.

pl/pgsql debuging, was Re: [GENERAL] debugging C functions

[David Gardner]

This post got me thinking, is there a similar procedure for PL/pgSQL functions?

---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joe Conway
Sent: Friday, June 01, 2007 9:00 PM
To: Islam Hegazy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] debugging C functions

Islam Hegazy wrote:
 I wrote a C function to call from PostgreSQL8.2.4 under Linux. The
 functions returns unexpected results. I did an extensive analysis to the
 function and it seems correct. I want to know if there is a way to debug
 C functions that are passed to PostgreSQL.

Yes. Something along these lines (where plr.so is an example shared
object library with a function called throw_notice installed in a
database called contrib_regression):

1. Build and install your function. Ensure both postgres and your
library are built with debug symbols (--enable-debug)

2. start a psql session in the database where your function has
been created

#psql contrib_regression

3. Load the shared object library in psql

contrib_regression=# load '$libdir/plr';
LOAD

4. Start another console and determine the PID for the backend
session (this will wrap poorly -- I'll do my best to make it
readable)

ps -ef | grep postgres

postgres 24496 1  0 18:23 ?00:00:00
  /usr/local/pgsql-dev/bin/postgres -D /opt/data/pgsql/data -p
  65432 -i -F
postgres 24498 24496  0 18:23 ?00:00:00
  postgres: writer process
postgres 24499 24496  0 18:23 ?00:00:00
  postgres: stats collector process
postgres 24500 24496  0 18:23 ?00:00:00
  postgres: autovacuum launcher process
postgres 31233 24496  1 20:37 ?00:00:00
  postgres: postgres contrib_regression [local] idle

   You want the PID associated with the idle session -- 31233

5. Run gdb and attach to the backend in question

gdb /usr/local/pgsql-dev/bin/postgres 31233

6. Set breakpoints, etc, and then continue the gdb session

[...]
Reading symbols from
  /usr/lib64/R/library/stats/libs/stats.so...done.
Loaded symbols for /usr/lib64/R/library/stats/libs/stats.so
0x003fef4cdf45 in recv () from /lib64/libc.so.6
(gdb) break throw_notice
Breakpoint 1 at 0x636cb7: file plr.c, line 2908.
(gdb) continue
Continuing.

7. Return to the psql session, run your function

contrib_regression=# select throw_notice('hello');

8. Return to gdb session, debug away...


HTH,

Joe

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

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


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

Re: Creditcard Number Security was Re: [GENERAL] Encrypted column

[Marko Kreen]

On 6/5/07, Peter Childs [EMAIL PROTECTED] wrote:

On 05/06/07, Andrew Sullivan [EMAIL PROTECTED] wrote:
 On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:
 
  If he is a CC customer, the system (which I am DBA of) bills his
  card directly, saving the customer much time and effort.

 So surely what you have is a completely separate system that has
 exactly one interface to it, that is signaled to provide a
 transaction number and that only ever returns such a transaction
 number to the online system, and that is very tightly secured,
 right?

 It is possible to make trade-offs in an intelligent manner, for sure,
 but you sure as heck don't want that kind of data stored online with
 simple reversible encryption.

 Unfortunately you still need to store them somewhere,  and all systems can
be hacked.  Yes its a good idea to store them on a separate system and this
is an important part of designing your systems to ensure that the simple
user interface is somehow limited.


If you really need the number in cleartext you should use
public-key encryption, either via pgcrypto or in application.

Thus you can have only public-key in public database,
credit-card numbers are encrypted with it, later actual
billing happens in separate, highly secured system that
has corresponding private key available to decrypt the data.

--
marko

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

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

Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions

[Pavel Stehule]

2007/6/5, David Gardner [EMAIL PROTECTED]:

This post got me thinking, is there a similar procedure for PL/pgSQL functions?



No. You can debug PL/pgSQL function via debug plugin API. Currently
exists only one debugger, which can do it - Enterprisedb debugger.

Regards
Pavel Stehule

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

Re: [GENERAL] There can be only one! How to avoid the highlander-problem.

[Erwin Brandstetter]

Oh my, it took me a ton of text to finally come up with a better idea.

5.) The Sun King solution
L'etat c'est moi!. The model is as simple as can be:

CREATE TABLE nation
(
   nation_id SERIAL PRIMARY KEY
);
CREATE TABLE man
(
   man_id SERIAL PRIMARY KEY,
   nation_id INTEGER NOT NULL REFERENCES nation (nation_id) ON UPDATE
CASCADE ON DELETE CASCADE
);

Instead of drawing his man_id from the sequence, a king copies the
nation_id. Kingship can be tested by (man.man_id = nation.nation_id)
or (man.man_id = man.nation_id).
(Yeah, he is bound to come to mind here: http://en.wikipedia.org/wiki/Sun_King)
If you know the man_id of the king, you also know the nation_id, and
vice versa.

The caveat is, you have to make sure that the two sequences for nation
and man yield mutually exclusive values. One absolutely reliable way
would be to attach both primary keys to one sequence. This just works.

But, you don't have to stop at that. If you can guarantee that nation
will never burn more that, say, 100 000 nation_id's, and sequence wrap-
around is otherwise no concern, you can keep two separate sequences,
start nation_id at 1 and man_id at 100 000. Now you also know a king
when you see one: (man_id  100 000) is king.

If the kingship of a nation is passed around, though, this can be a
problem. You could guard yourself against that with ON UPDATE CASCADE
for every foreign key constraint referencing man.man_id. But it would
be asking for trouble, still.

If you can meet both conditions - I have such cases here -, then go
with this one. Fastest, simplest, smallest.


Regards
Erwin


---(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] Join field values

[Erwin Brandstetter]

On Jun 5, 7:39 pm, [EMAIL PROTECTED] (Ragnar) wrote:

 create an aggregate function and use that in your 
 select.http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html

Of course you could do that. And it would look like that:


CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
  RETURNS text AS
$BODY$
BEGIN
RETURN $1 || ', ' || $2;
END;
$BODY$
  LANGUAGE 'plpgsql' STABLE IMMUTABLE;

CREATE AGGREGATE concat_comma(
  BASETYPE=text,
  SFUNC=f_concat_comma,
  STYPE=text
);

SELECT concat_comma(field1) FROM mytbl;

--Or, if want the values ordered:
SELECT concat_comma(field1) FROM (SELECT field1 FROM mytbl ORDER by
field1)  x;


And that's what I did - before I found out about this simpler way:

SELECT array_to_string(ARRAY(SELECT field1 FROM mytbl ORDER by
field1), ', ');

More info:
http://www.postgresql.org/docs/8.2/static/functions-array.html


Regards
Erwin


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

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

Re: Creditcard Number Security was Re: [GENERAL] Encrypted column

[Joris Dobbelsteen]

-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] On Behalf Of Marko Kreen
Sent: dinsdag 5 juni 2007 21:38
To: Peter Childs
Cc: pgsql-general@postgresql.org
Subject: Re: Creditcard Number Security was Re: [GENERAL] 
Encrypted column

On 6/5/07, Peter Childs [EMAIL PROTECTED] wrote:
 On 05/06/07, Andrew Sullivan [EMAIL PROTECTED] wrote:
  On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:
  
   If he is a CC customer, the system (which I am DBA of) bills his 
   card directly, saving the customer much time and effort.
 
  So surely what you have is a completely separate system that has 
  exactly one interface to it, that is signaled to provide a 
  transaction number and that only ever returns such a transaction 
  number to the online system, and that is very tightly secured, 
  right?
 
  It is possible to make trade-offs in an intelligent manner, for 
  sure, but you sure as heck don't want that kind of data stored 
  online with simple reversible encryption.

  Unfortunately you still need to store them somewhere,  and all 
 systems can be hacked.  Yes its a good idea to store them on a 
 separate system and this is an important part of designing your 
 systems to ensure that the simple user interface is somehow limited.

If you really need the number in cleartext you should use 
public-key encryption, either via pgcrypto or in application.

Thus you can have only public-key in public database, 
credit-card numbers are encrypted with it, later actual 
billing happens in separate, highly secured system that has 
corresponding private key available to decrypt the data.

Even better is to have security experts/specialists design and formally
validate the system before use. In general people will screw up security
in so much ways that it easilly goes beyond your imagination.

You can also take established systems, like kerberos. The problem here
is the integration of different data systems. But generally these
systems (not all) are well-designed and have received attention from
specialists, giving you a much higher confidence in their secure
operation than something you build yourselfs.
Of course, this still doesn't mean the entire system you are buidling is
secure.


For elaboration only:

Obviously for credit cards we are looking only at the database. Did
anyone realize where the credit cards numbers happened to pass through?
First of, at the user side they are entered into the browser. Then they
are (securely) transmitted to the web server, which already can see
them. Eventually they end up in a database and get send to a bank.

Obviously putting the information using public key encyrption in a
database isn't going to help you securing your web server, is it? So
though considering a small part of the system, many important aspects
are already overlooked. Yet the weakest chain determines the strength of
the entire system.


Leave security to specialist, it's a really really hard to get right.


- Joris Dobbelsteen


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

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

Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions

[Dave Page]

 --- Original Message ---
 From: Pavel Stehule [EMAIL PROTECTED]
 To: David Gardner [EMAIL PROTECTED]
 Sent: 05/06/07, 21:01:49
 Subject: Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions
 
 2007/6/5, David Gardner [EMAIL PROTECTED]:
  This post got me thinking, is there a similar procedure for PL/pgSQL 
  functions?
 
 
 No. You can debug PL/pgSQL function via debug plugin API. Currently
 exists only one debugger, which can do it - Enterprisedb debugger.


Or dev builds of pgAdmin - but you still need the plugin.

Regards, Dave

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

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

Re: Creditcard Number Security was Re: [GENERAL] Encrypted column

[Andrew Sullivan]

On Tue, Jun 05, 2007 at 07:29:02PM +0100, Peter Childs wrote:
 Unfortunately you still need to store them somewhere,  and all systems can
 be hacked.  

Yes.  I agree, in principle, that don't store them is the best
advice -- this is standard _Translucent Databases_ advice, too.  For
the least-stealable data is the data you don't have.

But if there is a business case, you have to do the trade off.  And
security is always a tradeoff (to quote Schneier); just do it well. 
(Someone else's advice about hiring a security expert to audit this
sort of design is really a good idea.)

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

---(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] Running v8.1 amd v8.2 at the same time for a transition

[Scott Marlowe]

Vincenzo Romano wrote:

Hi all.
I need to run both 8.1 and 8.2 at the same time in order to check
everything in 8.2 *before* shutting 8.1 down.
I need to run both as I only have one machine available.
I'm using a debian derivateive (Kubuntu) that provides a nice pg_wrapper
mechanism to direct connections for tools to either version you want.
Infact I see both versions running, one on port 5432 and one on port 5433.
The point is thay I have no clue on ow to choose which instance attach to.
I've already posted this question to the KUbuntu team with no answer in 14+
hours.


You choose one or the other by changing the port.  If you're not sure 
which is running on which port, you can try connecting.


something along the lines of:

psql -p 5433 -U postgres template1
select version();

should tell you.


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

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

[GENERAL] Running v8.1 amd v8.2 at the same time for a transition

[Vincenzo Romano]

Hi all.
I need to run both 8.1 and 8.2 at the same time in order to check
everything in 8.2 *before* shutting 8.1 down.
I need to run both as I only have one machine available.
I'm using a debian derivateive (Kubuntu) that provides a nice pg_wrapper
mechanism to direct connections for tools to either version you want.
Infact I see both versions running, one on port 5432 and one on port 5433.
The point is thay I have no clue on ow to choose which instance attach to.
I've already posted this question to the KUbuntu team with no answer in 14+
hours.

Is there anyone with a good hint?

Thanks.

-- 
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]

---(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] Running v8.1 amd v8.2 at the same time for a transition

[Oliver Elphick]

On Tue, 2007-06-05 at 23:40 +0200, Vincenzo Romano wrote:
 Hi all.
 I need to run both 8.1 and 8.2 at the same time in order to check
 everything in 8.2 *before* shutting 8.1 down.
 I need to run both as I only have one machine available.
 I'm using a debian derivateive (Kubuntu) that provides a nice pg_wrapper
 mechanism to direct connections for tools to either version you want.
 Infact I see both versions running, one on port 5432 and one on port 5433.
 The point is thay I have no clue on ow to choose which instance attach to.
 I've already posted this question to the KUbuntu team with no answer in 14+
 hours.
 
 Is there anyone with a good hint?

man pg_wrapper

psql --cluster 8.1/main -d your_database

-- 
Oliver Elphick  [EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
 
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


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

Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions

[David Gardner]

I grabbed the May 10th dev snapshot of pgAdmin3, first a little bit of praise 
to the pgAdmin3 team for allowing me to run both pgAdmin3 1.7 and 1.6.2 side by 
side.

However what is the debug plugin API? I looked around in postgresql/contrib, 
and PostgreSQL.org. I'm assuming this plugin is something that needs to run 
server side?

---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855

-Original Message-
From: Dave Page [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 05, 2007 1:38 PM
To: Pavel Stehule
Cc: David Gardner; pgsql-general@postgresql.org
Subject: Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions



 --- Original Message ---
 From: Pavel Stehule [EMAIL PROTECTED]
 To: David Gardner [EMAIL PROTECTED]
 Sent: 05/06/07, 21:01:49
 Subject: Re: pl/pgsql debuging, was Re: [GENERAL] debugging C functions

 2007/6/5, David Gardner [EMAIL PROTECTED]:
  This post got me thinking, is there a similar procedure for PL/pgSQL 
  functions?
 

 No. You can debug PL/pgSQL function via debug plugin API. Currently
 exists only one debugger, which can do it - Enterprisedb debugger.


Or dev builds of pgAdmin - but you still need the plugin.

Regards, Dave


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

[Robert Treat]

On Monday 04 June 2007 04:53, Richard Huxton wrote:
 John Gardner wrote:
  I've been testing one of our apps on PostgreSQL for the last few months
  and I'm about ready to put it on the production server, but I need
  advice on where to locate the tablespace.  I've been so concerned
  getting the app working, I haven't even considered this yet.
 
  I'm using a RPM install of Postgres, so the data directory is located at
  /var/lib/pgsql/data/.  Shall I just create a directory under here and
  point the tablespace to there?  Any advice would be appreciated.

 If you're not going to be spreading your installation over multiple
 disks (well, mount-points), there's no need to play with tablespaces at
 all.

Well, one could use them as part of a disk quota utilization scheme... granted 
doesn't sound like the OP needs them... just saying is all. 

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

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

Re: [GENERAL] Encrypted column

[Joe Conway]

Marko Kreen wrote:

On 6/5/07, Brian Mathis [EMAIL PROTECTED] wrote:

pgcrypto also supports md5, so I'm not sure what you're referring to
here.


digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))


As I already mentioned, *salting* before you hash is a very
important step.  I'm not sure if you saw that in my post.  Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths.  Then it would be
very simple to look up each hash and get the original text.  With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.


I dont think its practical method tho'.  Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.

But if they have salts then for each word you try you need to
hash it for each salt.  Which basically gives the effect that
each hash needs to be attacked separately.

In case of attacking one hash the salt does not matter,
only the algorithm counts then.  In that case as i said,
event salted md5 is weaker than des-crypt.


The best method as far as I understand it is HMAC 
(http://www.faqs.org/rfcs/rfc2104.html).


It has some significant cryptanalysis behind it to ensure it does not 
leak information that would compromise the password. Even MD5 and SHA1, 
which have been shown to have certain weaknesses, are not at issue when 
used with HMAC (see, for example, section 3.1.1 of 
http://www.apps.ietf.org/rfc/rfc4835.html)


The way you would use HMAC is:
1. generate a random token, whatever length you want (the salt)
2. use HMAC (implemented with either md5 or sha1 or something newer) to
   hash the salt with the password
3. store the salt and the resulting HMAC hash
4. on login, calculate the HMAC of the token using the provide password,
   and compare to the stored hash

pgcrypto appears to support HMAC. It is also relatively easy to 
implement on top of the built in md5 function if you'd rather not 
install pgcrypto. And I'm sure there are HMAC functions available that 
could be used in PL/Perl and/or PL/Python.


Joe

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

Re: [GENERAL] Large Database \d: ERROR: cache lookup failed for relation ...

[Jim Nasby]

I'm working with these guys to resolve the immediate issue, but I  
suspect there's a race condition somewhere in the code.


What's happened is that OIDs have been changed in the system. There's  
not a lot of table DDL that happens, but there is a substantial  
amount of view DDL that can take place. In a nutshell, tables will  
sometimes have fields added to them, and when that happens a whole  
set of views needs to be re-created to take the new fields into account.


The files for corrupted tables do exist; this seems to be mostly a  
catalog corruption issue. I'm seeing both what appear to be  
inconsistencies between relcache and the catalog tables as well as  
corruption between tables themselves:


emma2=# select * from userdata_8464_campaigns;
ERROR:  could not open relation with OID 138807643
emma2=# \d userdata_8464_campaigns
Table  
public.userdata_8464_campaigns
  Column |Type  
|Modifiers
---+- 
+--
campaign_id  | bigint  | not null  
default nextval(('emma_campaigns_seq'::text)::regclass)

account_id   | bigint  | not null
cep_object_id | bigint  | not  
null default nextval(('cep_object_seq'::text)::regclass)

campaign_name| character varying(255)  | not null
campaign_subject | character varying(255)  | not null
layout_page_id   | bigint  | not null
layout_content_id| bigint  | not null
campaign_create_date | timestamp without time zone | not null  
default now()
campaign_last_mod_date   | timestamp without time zone | not null  
default now()

campaign_status  | character varying(50)   | not null
campaign_parent_id   | bigint  |
published_campaign_id| bigint  |
campaign_plaintext   | text|
campaign_plaintext_ds| timestamp without time zone |
delivery_old_score   | double precision|
campaign_person_defaults | text|
Inherits: emma_campaigns

select oid from pg_class where relname='userdata_8464_campaigns';
  oid

533438
(1 row)

And that file actually does exist on disk...

select * from pg_index where indexrelid=138807643;
indexrelid | indrelid | indnatts | indisunique | indisprimary |  
indisclustered | indisvalid | indkey | indclass | indexprs | indpred
+--+--+-+-- 
++++--+--+-
  138807643 |   533438 |1 | t   | t|  
f  | t  | 1  | 1980 |  |

(1 row)

select * from pg_class where oid=138807643;
relname | relnamespace | reltype | relowner | relam | relfilenode |  
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid  
| relhasindex | relisshared | relkind | relnatts | relchecks |  
reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey  
| relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
-+--+-+--+---+- 
+---+--+---+--- 
+---+-+-+-+-- 
+---+-+--+--+- 
+++-+ 
+--++

(0 rows)

On Jun 5, 2007, at 11:27 AM, Erik Jones wrote:

I originally sent this message from my gmail account yesterday as  
we were having issues with our work mail servers yesterday, but  
seeing that it hasn't made it to the lists yet, I'm resending from  
my registered address.  You have my apologies if you receive this  
twice.


Thomas F. O'Connell tf ( at ) o ( dot ) ptimized ( dot ) com  
writes:

 I'm dealing with a database where there are ~150,000 rows in

 information_schema.tables. I just tried to do a \d, and it came back
 with this:

 ERROR:  cache lookup failed for relation [oid]

 Is this indicative of corruption, or is it possibly a resource  
issue?


Greetings,

This message is a follow-up to Thomas's message quoted above (we're  
working together on the same database). He received one response  
when he sent the above message which was from Tom Lane and can be  
easily summarized as him having said that that could happen tables  
were being created or dropped while running the \d in psql.  
Unfortunately, that wasn't the case, we have now determined that  
there is some corruption in our database and we are hoping some of  
you back-end gurus might have some suggestions.


How we verified that there is corruption was simply to reindex all  
of our tables in addition to