stopping access to a database

There is no need to block all access, simply use a transaction. IE:

begin work;
delete from table1;
delete from tabl2;
... so on ..
.. import data ...
commit work;

Anyone who connects while the delete/import is taking place will simply
see the old data, when you run the commit work command, all the new
queries will see the newly imported data.

Having just started receiving the list without actually subscribing, I
didn't get the info email from the server that tells me how to
unsubscribe. Can anybody please point me in the right direction? It's been
a while since I've been on this list, and I don't remember the procedure
from last time...

Regards.

Gordan

Having just started receiving the list without actually subscribing, I

Interesting, the same thing happened to me. I figured I must've
subscribed a long time ago and recently botched a procmail rule or
something, but if this has happened to others tooo ...

--

thanks,

Will

On Tue, 2003-01-28 at 11:50, Dennis Gearon wrote:

I would like to stop accesses to database 2, delete all records in the 2-4 tables, and reimport
new records. How would I do this as a user on the system, not as an admin?

How about starting a transaction, acquiring an exclusive lock on all the
tables using LOCK TABLE, deleting & updating the data, and then
committing the transaction?

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Many moons ago, I subscribed. I had long since unsubscribed or set my
profile up to not send messages. I suspect that the list software somehow
munged a database or something.

Is there some demunging facility?

-- Matt

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Will Lowe
Sent: Tuesday, January 28, 2003 2:05 PM
To: Gordan Bobic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I unsubscribe?

Having just started receiving the list without actually subscribing, I

Interesting, the same thing happened to me. I figured I must've subscribed a
long time ago and recently botched a procmail rule or something, but if this
has happened to others tooo ...

--

thanks,

Will

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your message
can get through to the mailing list cleanly

At 11:05 AM -0800 1/28/03, Will Lowe wrote:

Having just started receiving the list without actually subscribing, I

Interesting, the same thing happened to me. I figured I must've
subscribed a long time ago and recently botched a procmail rule or
something, but if this has happened to others tooo ...

I had subscribed a very long time ago, and it just stopped coming. I
tried unsubscribing, re-subscribing, subscribing under a different
address, etc., and I still couldn't get mail. I tried even to get a
hold of a human being, with no luck, so I gave up. All of a sudden,
now I'm getting two messages!!
--
.Michelle

--------------------------
Michelle Murrain, Technology Consulting
tech@murrain.net http://www.murrain.net
413-253-2874 ph
413-222-6350 cell
413-825-0288 fax
AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575

"A vocation is where the world's hunger & your great gladness meet."
Frederick Buechner

So,
the connections that will see the new data won't be able to access data until that
transaction has finished the 'commit' stage, or will they be able to read some of the records
while the importation is partially done?

1/28/2003 9:27:59 AM, Charles Tassell <ctassell@isn.net> wrote:

There is no need to block all access, simply use a transaction. IE:

begin work;
delete from table1;
delete from tabl2;
... so on ..
.. import data ...
commit work;

Anyone who connects while the delete/import is taking place will simply
see the old data, when you run the commit work command, all the new
queries will see the newly imported data.

On Tue, 2003-01-28 at 12:50, Dennis Gearon wrote:

I configuring 2 databases:

1/ One be on a https server, and all important data will be encrypted. The only use this
database will see are:
A/ Updates 2-3 times a day, max.
B/ Exporting a smaller set of 'unnormalized' tables for the database below.
2/ A publically accessible, (through PHP applications), database that has less secure

data

on it and is not encrypted.

I would like to stop accesses to database 2, delete all records in the 2-4 tables, and

reimport

Count me in on that. I get mail without subscribing.

Will Lowe wrote:

Having just started receiving the list without actually subscribing, I

Interesting, the same thing happened to me. I figured I must've
subscribed a long time ago and recently botched a procmail rule or
something, but if this has happened to others tooo ...

--

thanks,

Will

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Lalo Castro
Programmer/Analyst
McHenry Library
(831) 459-5208

Is this a virus???

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gordan Bobic
Sent: Tuesday, January 28, 2003 12:58 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How do I unsubscribe?

Having just started receiving the list without actually subscribing, I
didn't get the info email from the server that tells me how to
unsubscribe. Can anybody please point me in the right direction? It's
been
a while since I've been on this list, and I don't remember the procedure

from last time...

Regards.

Gordan

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

You know,
There are three big threads going on in the list now:

A/ Tablespaces
B/ Backing up the database
C/ Getting people unsubscribed because of OLD DATA being restored.

Methinks that 'C' demonstrates the need for 'B'. If the database for subscribers had been
regularly backed up or was part of a journaling system, the subscriber database would not
have such OLD DATA in it. Having the database capable of having a backup EASILY and
AUTOMATICALLY, which is what 'B' is about, would have prevented 'C'

1/28/2003 11:05:16 AM, Will Lowe <harpo@thebackrow.net> wrote:

Ooooooooohhhhhhhhhhh,
That would work! I didn't know I could get an exclusive lock on tables in a transaction,
I thought it was just for rows, (the opposite of MySQL). This is good, and 'back to the
manual' for more reading!

1/28/2003 12:22:50 PM, Neil Conway <neilc@samurai.com> wrote:

On Tue, 2003-01-28 at 11:50, Dennis Gearon wrote:

I would like to stop accesses to database 2, delete all records in the 2-4 tables, and

reimport

So,
the connections that will see the new data won't be able to
access data until that
transaction has finished the 'commit' stage, or will they be able
to read some of the records
while the importation is partially done?

The former. Other transations won't see the data and also because you went:

LOCK TABLE IN ACCESS EXCLUSIVE MODE

or something, they won't even be able to access the tables...

Chris

No, this is not a virus, the email database for the mailing lists got
reset with a bunch of old accounts in it.

On Tue, 28 Jan 2003, Terri Lerose wrote:

Why do you even need to lock access to the tables???

Just fire up a transaction, delete everything from the tables and import the new data and then commit. Other users will just see the old data right up until you commit.

Chris

Dennis:

You gotta ask yourself -- "how long is the delete / insert going to take
???" -- all of about 3 seconds probably....

I'd just do it in a transaction because you said that database 2 is
"publicly accessible"... What happens if Joe Public is trying to get some of
that public info and you have the systemn locked...

"Dennis Gearon" <gearond@cvc.net> wrote in message
news:PL54ZWTRID64QP95PKD952WTXWQLRLRM.3e36b4bb@cal-lab...

I configuring 2 databases:

1/ One be on a https server, and all important data will be encrypted. The

only use this

database will see are:
A/ Updates 2-3 times a day, max.
B/ Exporting a smaller set of 'unnormalized' tables for the database

below.

2/ A publically accessible, (through PHP applications), database that has

less secure data

on it and is not encrypted.

I would like to stop accesses to database 2, delete all records in the 2-4

tables, and reimport

Dennis Gearon <gearond@cvc.net> writes on
Wed, 29 Jan 2003 01:08:19 +0100 (MET):

You know,
There are three big threads going on in the list now:

A/ Tablespaces
B/ Backing up the database
C/ Getting people unsubscribed because of OLD DATA being restored.

Don't forget to unsubscribe me.

Having the database capable of having a backup EASILY and
AUTOMATICALLY, which is what 'B' is about, would have prevented 'C'

Database snapshots?

Masi