Re: [GENERAL] multimaster
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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.
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
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
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
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
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
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 ?
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
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
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
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
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
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
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
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
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??
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
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
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??
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
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
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
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 ...
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
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
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
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
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
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
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
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
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??
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
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
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
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
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
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.
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
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
-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
--- 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
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
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
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
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
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
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
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 ...
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