Re: [GENERAL] running maintenance tasks on DB
On Sat, Sep 23, 2000 at 05:26:47PM +0200, Louis-David Mitterrand wrote: > Approaching completion of an online auction system based on Postgres and > mod_perl/Apache I yet have to devise a good way of running certain > maintenance tasks on the DB like closing auctions, notifying winners, > transferring old records to archive tables, etc. > > What is the usual way of programming such tasks? Write a backend > function (in pl/sql or C) and call it from a cron job? From a mod_perl > handler? I don't know if this is the best way, but I usually just write a Perl script and run it through cron. You just have to make sure that you maintain data integrity while doing maintainence - i.e. there is no point where the data is invalid. This usually means putting stuff into transactions, but that's no big deal. I don't believe you can get backend functions to run at certain times, without using a mechanism like cron to connect to the DB and run them. A mod_perl handler is a totally different matter. That's for doing stuff with HTTP requests - isn't this unrelated? I would think that you'd have mod_perl handlers displaying your content and manipulating the DB, and have some scripts set to run every X minutes/hours which do maintainence. I use Perl, but you can obviously use any language you like, provided it has a Postgres interface. HTH, Neil -- Neil Conway <[EMAIL PROTECTED]> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move. -- Douglas Adams PGP signature
[GENERAL] running maintenance tasks on DB
Hello, Approaching completion of an online auction system based on Postgres and mod_perl/Apache I yet have to devise a good way of running certain maintenance tasks on the DB like closing auctions, notifying winners, transferring old records to archive tables, etc. What is the usual way of programming such tasks? Write a backend function (in pl/sql or C) and call it from a cron job? From a mod_perl handler? Thanks in advance for any insight, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org
[GENERAL] Re: Postgresql limits
Hans-Jürgen Schönig wrote: > When doing multiple insert, please consider the following: PostgreSQL > uses fsync (I think) after every insert. This is extremely slow when > inserting large amounts of data at once. On the other hand this helps > you to protect the database if the systems crashes. I would have thought fsync() would be slowest when inserting lots of little pieces of data, not big ones. But I'm just guessing. You can disable fsync() with the '-o -F' flag to postmaster. This speeds everything up, but if your system crashes you may lose some data. Regards, Neil
[GENERAL] Re: [SQL] how to store a query, that results in a table
Summary: Proposed solution, and question on efficiency of technique
I don't know if this is what you are looking for, but I have a database where
I needed a relatively complex view (which I can do thanks to the expanded view
buffer in 7.0!, it didn't fit in 6.5.3), but I need to pass an "effective
date" to the view that needed to be in a range (so I couldn't just use a
column in one of the source tables) to get the results I want. My "solution"
was to come up with an "effective dates" table with one column (primary keyed)
that I can put the dates in. For example:
create table effective_date (date date, primary key (date) );
create view complex_view as select blah, . , effective_date.date
from tablea, tableb, tablec, effective_date
where tablea.foo=tableb.foo
and effective_date.date between tablec.start_date=tablec.end_date
;
then, when I want to select rows from the view, I have to INSERT the date I
want into the "effective_date" table (which is effectively my "parameter"),
then I can select it from the view. i.e.:
insert into effective_date values ('09/23/2000'); -- may "fail" if date is
already in the table, but if it is, who cares?
select *
from complex_view
where date='09/23/2000';
Now it would certainly be nicer if I could set some kind of global system
variable to the date, then reference that system variable in the view, but I
couldn't figure out any way to do it. If anyone in the know is screaming out
loud at this technique, please point me in the right direction, I would love
to be able to skip the "Insert into effective_date..." step.
-paul
Keith Wong wrote:
> This is not really possible with postgresql at the moment.
> Better off trying to work around, perhaps using a view. That way you have a
> way to change the select
> statement without actually modifying your client code.
>
> Keith.
>
> At 06:09 PM 22/09/2000 -0400, Nelson wrote:
> >thank you jie Liang for your response, but my problems are:
> >1. How to store a query in the database.
> >2. How to give a parameter from outside of database, for example:
> >select * from table1 where row1 = my_parameter_outside.
> >Give me an example please.
> >
> >
Re: [GENERAL] users
Read this tutorial it has got everyting you need to know about psql.
Document herewith attached
On Sat, 23 Sep 2000, Chris wrote:
> Greetings,
>
> When ever a normal user (anyaccount besides "postgres") tries to
> run psql they get the following message:
>
> Connection to database 'dank' failed.
> FATAL 1: SetUserId: user "chrisp" is not in "pg_shadow"
>
>
> How do i fix this?
{\rtf1\ansi\deflang1024\deff0{\fonttbl{\f0\froman Times Roman;}{\f1\fnil Times;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;}{\stylesheet{\sl240\slmult1\brdrt0\brdrl0\brdrr0\brdrb0\brdrbtw0 \f1\fs24\cf1 \snext0 Normal;}{\s1\sl240\slmult1
\brdrt0\brdrl0\brdrr0\brdrb0\brdrbtw0 \f1\fs24\cf1 \sbasedon0\snext1 Cell;}{\s2\sl240\slmult1\brdrt0\brdrl0\brdrr0\brdrb0\brdrbtw0 \f1\fs24\cf1 \sbasedon0\snext2 Footnote;}{\s3\sl240\slmult1\tqc\tx5040\tqr\tx10080\tqr\tx13680\brdrt0\brdrl0\brdrr0
\brdrb0\brdrbtw0 \f1\fs24\cf1 \sbasedon0\snext3 HdrFtr;}{\s4\sl240\slmult1\brdrt0\brdrl0\brdrr0\brdrb0\brdrbtw0 \ul\f1\fs24\cf2 \sbasedon0\snext4 html_hyperlink_text;}}
{\info{\title ax00500e.aw}{\author dannyh}{\doccomm Created by ApplixWare Release 4.42 (build 1021.522) #17 RTF Export Filter}}
\paperw12240\paperh15840\margl1080\margr1080\margt1440\margb1440\widowctrl\ftnbj\sectd\marglsxn1080\margrsxn1080\margtsxn1440\margbsxn1440\sbknone\headery360\footery360\endnhere\pard\plain \slmult1 \f1\fs24\cf1 Adding a user
\par I like the way PostgreSQL works. It creates a special user for you, pgsql. This user does all the work. The database run as this user, and all work
\par (database creation, adding users, etc) is done as this user.
\par
\par The first step is to add myself as a user, so I don't have to do all my work as pgql. Here's how I added myself as a user. I typed the bits in bold.
\par
\par $ su -l
\par Password:
\par [root@set:~] # su pgsql
\par $ /usr/local/pgsql/bin/createuser dan
\par Shall the new user be allowed to create databases? (y/n) y
\par Shall the new user be allowed to create more new users? (y/n) y
\par CREATE USER
\par
\par Done. Now that I've added myself as a user who can create databases, I can use my normal login.
\par Adding a database
\par Now I dropped back to my usual login and created a database.
\par
\par $ logout
\par [root@set:~] # logout
\par [dan@set:/usr/home/dan] $ /usr/local/pgsql/bin/createdb mydb
\par CREATE DATABASE
\par
\par Done.
\par Creating a user for this database
\par Now I dropped back to my usual login and created a database.
\par
\par $ /usr/local/pgsql/bin/psql mydb
\par Welcome to psql, the PostgreSQL interactive terminal.
\par
\par Type: \\copyright for distribution terms
\par\\h for help with SQL commands
\par\\? for help on internal slash commands
\par\\g or terminate with semicolon to execute query
\par\\q to quit
\par
\par mydb=#
\par
\par Now I'll create a user, tester, for this database.
\par
\par mydb=# create user tester with password 'mypassword';
\par CREATE USER
\par
\par Creating a table
\par I created a rather simple table for my testing.
\par
\par mydb=# create table test (id serial, name varchar(10));
\par NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq'
\par for SERIAL column 'test.id'
\par NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_id_key'
\par for table 'test'
\par CREATE
\par
\par Then I inserted data:
\par
\par mydb=# insert into test (name) values ('test');
\par INSERT 18879 1
\par mydb=# insert into test (name) values ('test2');
\par INSERT 18880 1
\par
\par Then I read that data back out:
\par
\par freshports2=# select * from test;
\par id | name
\par +---
\par 1 | test
\par 2 | test2
\par (2 rows)
\par
\par Getting php going
\par I create a simple php test in an existing website. For help on creating websites, look at Apache - virtual hosts.
\par
\par I added this to testpsql.php3 in my website. Note the amended while loop at the end of this section.
\par
\par
\par PostgreSQL test
\par
\par
\par \\n";
\par $i = 0;
\par while ($myrow = pg_fetch_array ($result)) \{
\par $i++;
\par echo " " . $myrow["id"] . "" .
\par $myrow["name"] . "\\n";
\par if ($i > 10) break;
\par \}
\par echo "\\n";
\par\} else \{
\par echo "read from test failed";
\par\}
\par
\parpg_exec ($database, "end");
\par \} else \{
\pare
[GENERAL] No Reload
Hi, my problem is that for some page PHP I don't want the browser reload from cache, because I create an ID when the there is the first connection to the site and I use this ID like a unique id to identify the operation made on this site and I store this ID in a Postgres Table. I test ALL tag and javascript but if (With Explorer) I request the url the browser reload the last visited. Enrico
