Re: [GENERAL] debian packages for Postgresql 7.1 or higher ?
On Wed, 16 May 2001, Alex wrote: Are there any debian packages for Postgresql 7.1 or higher for Potato? I've tried to get the src-deb from unstable (7.1.1) but it won't compile under potato, not unless I upgrade the perl packages as well, which is not my idea. http://people.debian.org/~elphick/postgresql/potato.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] ER diagrams
Hi, do you know of a tool which could be used to easily generate entity-relationship diagrams (with integrity constraints, etc), in LaTeX for example ? This is a bit unlinked with PostgreSQL but I hope you won't hit me :) Thank you. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] COPY INTO and the SERIAL data type
On Thu, 10 May 2001, Jonathan Sand wrote:
I want to use the COPY command to read a bunch of data files. These
files don't contain an id, so I want to use the SERIAL data type to
auto-number the generated rows. COPY complains.
Destination table:
CREATE TABLE destination (id SERIAL, truc INT, temps DATE);
Temporary table:
CREATE TABLE temp1 (truc INT, temps DATE);
Please do:
COPY temp1 FROM STDIN;
INSERT INTO destination
SELECT nextval('destination_id_seq'), *
FROM temp1;
DROP TABLE temp1;
with data:
34 2001-03-05
52 2001-02-01
\.
and you will get:
SELECT * FROM destination;
id|truc| temps
--++--
1| 34|2001-03-05
2| 52|2001-02-01
(2 rows)
NOTE: this doesn't number the data in sequence, but in the order the
SELECT returns them. If you need an absolute order, use the following Perl
script instead:
my $count = 1;
while () {
print $count++, \t, $_;
}
and insert directly in the database.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Why Size Of Data Backed Up Varies Significantly In SQL 6.5?
On Thu, 26 Apr 2001, Wendy wrote: I backed up a database at night and noted the size to be about over 300MB. Backuped with pg_dump, or dumped the raw database files ? In the latter case you want to stop the PostgreSQL server first. The following morning, I again backed up the same database and found out the size to be less than 100MB. There was no massive deletes by users during that morning. If you dumped the raw database, this difference can be caused by VACUUM being run automatically at night to free deleted items. NB: personnally I recommend backuping using pg_dump or pg_dumpall. And testing that restoration works. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: Slowdown problem when writing 1.7million records
Server (v6.2), I am accessing the files using JDBC from a Windows 2000 PC.
I don't use Microsoft software, nor Java, but a few general suggestions
for you:
- you may want to create the INDEX at the end (using CREATE INDEX)
in one operation
- you may want to run your requests in transactions: e.g. a transaction
which is committed every N insertion.
- in some rare case you may want to disable the fsync() of the
PostgreSQL
- you may want to use a more efficient interface, such as the
COPY command.
Example (using transactions)
#! /usr/bin/perl -wI /home/schaefer/perl-libs
# $Id$
use DBI;
use test_db;
use strict;
my $debug = 0;
my $result = 0; # success
my $reason = "of an unknown error";
$| = 1;
my $dbh = open_database();
if (defined($dbh)) {
my $amount_entries = 400;
my $commit_every= 1;
# Sometimes, large data entries are done better with a COPY.
$dbh-{AutoCommit} = 0; # Use transactions.
foreach (('sol_f', 'sol_i', 'sol_d')) {
# Using transactions should make insertion faster, since fsync()
# are probably not required. However, when changes are very big,
# it might actually make it slower or using much space, this
# is why we have this $commit_every above and below.
# was expecting a BEGIN WORK; but that
# seem to be implicit.
eval {
my $i;
my $failed_reason = "unknown db error";
print "Populating " . $_ . " ...";
for ($i = 0; ($i $amount_entries) ($result == 0); $i++) {
my @titles = ('id', 'ref', 'sentpos', 'wordpos');
if (!do_query($dbh,
"INSERT INTO " . $_ . "("
. join(", ", @titles)
. ") VALUES ("
. join(", ", ('?') x @titles)
. ")",
\@titles,
undef,
[ int(rand(32768)),
'truc',
int(rand(32768)),
'temp'
],
undef,
\$failed_reason)) {
$result = 1;
$reason = "can't insert " . $i . ": " . $failed_reason;
}
else {
if (($i % $commit_every) == 0) {
if ($dbh-commit) {
print "C ";
}
else {
$result = 1;
$reason = "can't commit: " . $dbh-errstr;
}
}
}
}
if ($result == 0) {
print " POPULATED.\n";
}
else {
print " FAILED.\n";
}
}; # DB is not set to die mode, so we will catch only our bugs.
if ($@) {
print;
$result = 1;
$reason = "transaction failed: " . $@; # Not always right.
$dbh-rollback; # res. ign. (in failure mode anyway)
}
elsif ($result) {
$dbh-rollback; # res. ign. (in failure mode anyway)
}
else {
if ($dbh-commit) {
$result = 1;
$reason = "can't commit: " . $dbh-errstr;
}
print "COMMITTED.\n";
}
}
$dbh-{AutoCommit} = 1; # No transactions
if (!$dbh-disconnect) {
$result = 1;
$reason = "disconnect error: " . $dbh-errstr;
}
undef $dbh;
}
else {
# Obviously, can't use $dbh-errstr here.
$reason = "can't database connect: " . $DBI::errstr;
$result = 1;
}
if ($result) {
print $0 . ": failed " . $result . " because " . $reason . "\n";
}
else {
print "SUCCESSFUL.\n";
}
exit $result;
sub create_table {
my($dbh, $name, $val) = @_;
my $result = 0;
my $sth = $dbh-prepare("CREATE TABLE $name ($val)");
if (defined($sth)) {
my $rv = $sth-execute;
if (defined($rv)) {
if ($debug) {
print "$name: succeeded.\n";
}
$result = 1;
}
$sth-finish;
undef $sth;
}
return $result;
}
And using COPY:
#! /usr/bin/perl -wI /home/schaefer/perl-libs
# USAGE
#./copy.pl | psql test_db
# $Id$
my $amount_entries = 400;
my $tell_every = 10;
print "COPY sol_f FROM stdin;\n";
my $i;
for ($i = 1; $i = $amount_entries; $i++) {
print int(rand(32768)) . "\t" . 'truc' . "\t" . int(rand(32768)) . "\t"
. 'temp' . "\n";
if (($i % $tell_every) == 0) {
print STDERR $i . "\n";
}
}
print ".\n";
Re: [GENERAL] Re: Problem with inheritance
On Fri, 26 Jan 2001, Alfonso Peniche wrote: I like the idea, there's just one problem, a user can be both a student and an employee... - If the guy is an user only, then just fill the user template - If the guy is a student, add a tuple to the is_student relation. - If the guy is an employee, add a tuple to the is_employee relation. You do not need to delete the is_student if you insert into is_employee (and backwards). The only problem that I see with my approach is that you can create an user which isn't neither a student nor an employee: if this is an issue you might want to periodically run a query like: SELECT u.* FROM user u WHERE (u.id NOT IN (SELECT user_id FROM is_student)) AND (u.id NOT IN (SELECT user_id FROM is_employee)) to spot illegal entries. (haven't tried it, though). Or someone from the PostgreSQL or SQL experts could tell us if there is a way to do cross-table integrity checking ?
[GENERAL] Search engine doesn't work
Hi, I wanted to try in the archives how to store a md5sum best in a database (origin is 16 bytes binary, if I don't get a good answer I will use ASCII like the output of the md5sum UNIX command since this is easy to debug). I got this error: Not Found The requested URL /mhonarc/pgsql-general/search.mpl was not found on this server. Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80
Re: [GENERAL] Problem with inheritance
On Fri, 26 Jan 2001, Alfonso Peniche wrote: user | -- || student employee Why not store the common data between student and employee in user, and then store the additional data for student and employee in the relation itself, implemented as a table ? CREATE TABLE user (id SERIAL, created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, birth TIMESTAMP NOT NULL, unix_uid INT2 NOT NULL, email VARCHAR(30) NOT NULL, UNIQUE(id), PRIMARY KEY(id)); CREATE TABLE is_student (user_id REFERENCES user NOT NULL, section VARCHAR(2) NOT NULL, /* CS, PH, etc */ year INT4 NOT NULL DEFAULT 1); CREATE TABLE is_employe (user_id REFERENCES user NOT NULL, laboratory INT4 NOT NULL, salary MONEY NOT NULL); Probably the VARCHAR could be changed into TEXT. Now, if you want to get all data about all student named 'Wilhelm Tell': SELECT u.*,is.section,is.year FROM user u, is_student is WHERE (u.first_name LIKE 'Whilhelm') AND (u.last_name LIKE 'Tell') AND (u.id = is.user_id); When the student becomes an employee, as this happens some time, you just need to do something like: BEGIN WORK; DELETE FROM is_student WHERE (user_id = ?); INSERT INTO is_employe (user, laboratory, salary) VALUES (?, 42, 5); COMMIT WORK; ? represents here the user id, as with the Perl DBI binding.
Re: [GENERAL] Help me for DBI-connect failed: Sorry, too many clients already.
On Tue, 19 Dec 2000, Joseph wrote:
$dbh=DBI-connect("dbi:Pg:dbname=$dbname",$dbusername,$dbpassword) or die "can
I would assume that if you never disconnect and are running under
mod_perl, you will have problems.
Re: [GENERAL] Can PostGreSQL handle 100 user database?
On Thu, 30 Nov 2000, The Hermit Hacker wrote: Note that this is a Linux limitation ... and even then, I'm not quite sure how accurate that is anymore ... the *BSDs have supported 2gb file systems for ages now, and, since IBM supports Linux, I'd be shocked if there was a 2GB limit on memory, considering alot of IBMs servers support up to 4 or 8GB of RAM ... Linux 2.2.x on ix86 only supports files upto 2 GB. Linux 2.4.x or any 64-bit plateform (SPARC, Alpha, m68k) fixes this (through the Large File Summit support, and a new libc). Memory: Upto 1 GB is supported stock, 2 GB by recompiling kernel. There is work in progress in 2.4 for supporting the 32 bit ix86 addressing modes available in some processors.
Re: [GENERAL] Some problem on the data base server !
On Mon, 20 Nov 2000, Mickaël Jouanne wrote: We had a database server on Mysql and we move to postgres last month. Not enough. You need to specify the exact version, and also the version of your operating system. Let's assume Linux. 'load average: 3.23' or some times higher than that. Who is responsible for that load ? Remember that on Linux, processes waiting for I/Os are also augmenting the load average. "CPU states: 95.1% user, 4.9% system, 0.0% nice, 59619.1% idle" How is using the 95.1% of CPU ? is it normal ? no, the 59619 value looks like a bug in top, or a mismatching kernel version.
Re: [GENERAL] True ACID under linux (no fsync)?
On 31 Oct 2000, Gary Howland wrote: Just a quickie - I heard that linux does not have a working fsync() call At least the manpage for fsync says that it does. The implementation: /* .. finally sync the buffers to disk */ dev = inode-i_dev; return sync_buffers(dev, 1); It really looks like it IS implemented. But probably on Linux not just the file data/metadata is synced, also all that device's data, which makes it very inefficient, but presumably `safe'. NB: don't forget that fsync() merely ensures that data was sent to the disk controller. Maybe this one has a cache (e.g. a fast SCSI harddrive), and if power fails, well. If fsync() was calling the SCSI FLUSH command, maybe that could be done, but that would not just sync the file.
[GENERAL] CREATE FUNCTION LANGUAGE C
Hi, CREATE FUNCTION LANGUAGE C (and maybe others) allow to load shared libraries. However the path can be specified arbitrarily by the user. Is that a way for a user X to gain the UID rights of the user running the postmaster ?
Re: [GENERAL] Some advanced database features, are they present in PostgreSQL
On Tue, 10 Oct 2000, Peter Eisentraut wrote: Will be in 7.1. [ ... ] Something like this has recently been announced as add-on from PostgreSQL, [ ... ] That has been available for quite a while. [ ... ] So, those are very good news. Thanks, and keep the good work.
