Thread: Transaction Logs Recycling Problem
Hi I previously had a problem with the transaction logs not being recycled properly with the following configuration: PostgreSQL 7.2.1-2 cygwin dll 1.3.12-2 ipc 1.11-1 The error was as follows: DEBUG: recycled transaction log file 0000000400000015 DEBUG: recycled transaction log file 0000000400000016
So, where is the problem? You said they are *not* recycled, but the messages you quoted say that they *are* :-) Besides, it's not an error, but just what it says - a DEBUG message. In two words - you are OK. There is no problem here, it works just fine. Dima Colin Mangiagalli wrote: > Hi > > I previously had a problem with the transaction logs not being recycled > properly with the following configuration: > > PostgreSQL 7.2.1-2 > cygwin dll 1.3.12-2 > ipc 1.11-1 > > The error was as follows: > > DEBUG: recycled transaction log file 0000000400000015 > DEBUG: recycled transaction log file 0000000400000016 > > ---------------------------(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
Yes, sometimes the logs recycle just fine, but other times not. Sometimes I get an FATAL 2 ERROR because postgresql appears to be trying to write to a log file, that returns a permission denied error. So something isn't working properly. I ran a little test yesterday though. I wrote a bit of code that spawned 4 threads that all insert and update into the same table at the same time. I did 5 million inserts and updates on the same table and it worked just fine. I watched the postmaster.log file and it recycled a log file about every 5-10 minutes. I am starting to think that the problem my not be with the database, but with cygwin and windows 2000 Dima Tkach <dmitry@openratings.com> wrote .. > So, where is the problem? You said they are *not* recycled, but the > messages you quoted say that they *are* :-) > Besides, it's not an error, but just what it says - a DEBUG message. > In two words - you are OK. There is no problem here, it works just fine. > > Dima > > Colin Mangiagalli wrote: > > Hi > > > > I previously had a problem with the transaction logs not being recycled > > properly with the following configuration: > > > > PostgreSQL 7.2.1-2 > > cygwin dll 1.3.12-2 > > ipc 1.11-1 > > > > The error was as follows: > > > > DEBUG: recycled transaction log file 0000000400000015 > > DEBUG: recycled transaction log file 0000000400000016 > > > > ---------------------------(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 > > > ---------------------------(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
"Colin Mangiagalli" <colin@camara.co.za> writes:
> Sometimes I get an FATAL 2 ERROR because postgresql appears to
> be trying to write to a log file, that returns a permission denied
> error.
> I am starting to think that the problem my not be with the database,
> but with cygwin and windows 2000
We've seen reports before of permission-denied errors on log file writes
--- which makes no sense at all, because the ownerships and permissions
of those files don't change. At least Postgres doesn't change them.
Perhaps the problem is that some other process is running around
altering permissions? Next time you see it, look to see what exactly
*is* the ownership and permission of the file being complained of.
I know this has been reported before on Cygwin. I can't recall that it's
been reported on any other platform. It may be a Windows-specific
problem.
regards, tom lane
On Mon, 17 Feb 2003, Tom Lane wrote: > "Colin Mangiagalli" <colin@camara.co.za> writes: > > Sometimes I get an FATAL 2 ERROR because postgresql appears to > > be trying to write to a log file, that returns a permission denied > > error. > > I am starting to think that the problem my not be with the database, > > but with cygwin and windows 2000 > > We've seen reports before of permission-denied errors on log file writes > --- which makes no sense at all, because the ownerships and permissions > of those files don't change. At least Postgres doesn't change them. > Perhaps the problem is that some other process is running around > altering permissions? Next time you see it, look to see what exactly > *is* the ownership and permission of the file being complained of. > > I know this has been reported before on Cygwin. I can't recall that it's > been reported on any other platform. It may be a Windows-specific > problem. > Doesn't Windows have this odd feature that only one process can have a file open at any one time. Would a second process using cygwin see this as a permission denied? Just throwing an idea in to the ring on the off chance. -- Nigel J. Andrews
Tom Lane <tgl@sss.pgh.pa.us> wrote .. > "Colin Mangiagalli" <colin@camara.co.za> writes: > > Sometimes I get an FATAL 2 ERROR because postgresql appears to > > be trying to write to a log file, that returns a permission denied > > error. > > I am starting to think that the problem my not be with the database, > > but with cygwin and windows 2000 > > We've seen reports before of permission-denied errors on log file writes > --- which makes no sense at all, because the ownerships and permissions > of those files don't change. At least Postgres doesn't change them. > Perhaps the problem is that some other process is running around > altering permissions? Next time you see it, look to see what exactly > *is* the ownership and permission of the file being complained of. > > I know this has been reported before on Cygwin. I can't recall that it's > been reported on any other platform. It may be a Windows-specific > problem. > > regards, tom lane Hi Tom I went back to the README on how to install postgresql, just to check I never missed anything. I did. When it tells you to chmod postgres /usr/share/postgresql/data there is a note [10] which says this assumes you have set CYGWIN env. variable to include ntsec. This explains now (8 months later) why chmod never did anything and the data folder, sub-folders and files still belong to Administrator. Anyway... After setting CYGWIN=ntsec tty and reinstalling cgywin and postgresql, the data folder can only be accessed by the postgres user and all files and folders in it belong to postgres. I had to make the data folder accessible to other users though, because my software needs to be able to check for and delete the postmaster.pid file if necessary. All other sub folders are still only accessible to postgres Could these permission denied errors have been cause by the fact that ntsec was off and the permissions weren't set correctly on the data folder. The only thing is, why does it work most of the time and only sometimes I get this error. The problem doesn't seem to happen if I take the machine off the domain. Could another machine (eg. domain controller) be accessing that machine periodically and for some reason accessing those files. I can't see why though. Thanks Colin
"Colin Mangiagalli" <colin@camara.co.za> writes:
> Could these permission denied errors have been cause by the fact that
> ntsec was off and the permissions weren't set correctly on the
> data folder.
If the folder permissions were wrong, then every single attempt to
create or rename a log file would have failed, I'd think. It sounded to
me more like a few of the individual log files had wrong ownership or
permissions. If you ran initdb as Administrator then the
originally-created log file would have belonged to Administrator, but
the additional ones would have belonged to postgres --- that might
explain the behavior.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote .. > "Colin Mangiagalli" <colin@camara.co.za> writes: > > Could these permission denied errors have been cause by the fact that > > ntsec was off and the permissions weren't set correctly on the > > data folder. > > If the folder permissions were wrong, then every single attempt to > create or rename a log file would have failed, I'd think. It sounded to > me more like a few of the individual log files had wrong ownership or > permissions. If you ran initdb as Administrator then the > originally-created log file would have belonged to Administrator, but > the additional ones would have belonged to postgres --- that might > explain the behavior. > > regards, tom lane Hi This morning I see the database was down. I had a look at the permissions on the log files. THey are owned by Administrator and permissions are (rw r r) .This is on the machine that I haven't changed yet, doesn't have ntsec on and is on the domain. The interesting thing is, when the database stops, it always stops at just after 7pm, but the network guys assure me that nothing is happening over the network at that time (ie. backups, etc) The other machine I have, randomly seems to fall over, but since I took it off the domain, it has been working fine. If I set ntsec, do I need to run export the database, run initdb and then import the database again, or can I just change ownership to postgres and set the permissions. I think I need to set ntsec and see if the problem goes away. Thanks for all the help Colin
BTW,
What is ntsec?
2/18/2003 11:09:30 PM, "Colin Mangiagalli" <colin@camara.co.za> wrote:
>Tom Lane <tgl@sss.pgh.pa.us> wrote ..
>> "Colin Mangiagalli" <colin@camara.co.za> writes:
>> > Could these permission denied errors have been cause by the fact that
>> > ntsec was off and the permissions weren't set correctly on the
>> > data folder.
>>
>> If the folder permissions were wrong, then every single attempt to
>> create or rename a log file would have failed, I'd think. It sounded to
>> me more like a few of the individual log files had wrong ownership or
>> permissions. If you ran initdb as Administrator then the
>> originally-created log file would have belonged to Administrator, but
>> the additional ones would have belonged to postgres --- that might
>> explain the behavior.
>>
>> regards, tom lane
>
>Hi
>
>This morning I see the database was down. I had a look at the
>permissions on the log files. THey are owned by Administrator and
>permissions are (rw r r) .This is on the machine that I haven't
>changed yet, doesn't have ntsec on and is on the domain.
>
>The interesting thing is, when the database stops, it always stops at
>just after 7pm, but the network guys assure me that nothing is happening
>over the network at that time (ie. backups, etc)
>
>The other machine I have, randomly seems to fall over, but since I took
>it off the domain, it has been working fine.
>
>If I set ntsec, do I need to run export the database, run initdb and then
>import the database again, or can I just change ownership to postgres and
>set the permissions.
>
>I think I need to set ntsec and see if the problem goes away.
>
>
>Thanks for all the help
>Colin
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
Hi,
I am trying to create a table where one of the fields gets the results from another table,
the table I need to get the field from was created like this:
CREATE TABLE "companies" (
"compid" serial,
"compname" character varying(100) NOT NULL,
"compadd1" character varying(100),
"compadd2" character varying(100),
"compcity" character varying(150),
"comppcode" character varying(10),
"compcounty" character varying(20),
"compphone" character varying(15),
"compfax" character varying(15),
"compwebsite" character varying(50),
"compemail" character varying(50),
"compcontact" character varying(50),
"compdescription" character varying(250),
"comptype" integer NOT NULL,
"complevel" integer NOT NULL
);
and the new table needs to be like this
CREATE TABLE "location" (
"compcity" character varying(150),
"loc_description" character varying(250)
);
where the "compcity" field is the result of "SELECT DISTINCT(compcity) FROM companies;" and update automatically,
I think this may be possible with references but I'm not sure if it is or how to do it.
any help would be much appreciated
regards
Mark Cubitt
On Wednesday 19 Feb 2003 4:26 pm, Mark Cubitt wrote: > I am trying to create a table where one of the fields gets the results from > another table, Please try not to reply to an existing message when starting a new question, it can mess up threading in certain email clients (e.g. mine). Not that I'm entirely innocent of this myself :-/ > the table I need to get the field from was created like this: > > CREATE TABLE "companies" ( > "compid" serial, > "compname" character varying(100) NOT NULL, > "compadd1" character varying(100), > "compadd2" character varying(100), > "compcity" character varying(150), > and the new table needs to be like this > > CREATE TABLE "location" ( > "compcity" character varying(150), > "loc_description" character varying(250) > ); > > where the "compcity" field is the result of "SELECT DISTINCT(compcity) FROM > companies;" and update automatically, Well, there are two ways to crack this nut: > I think this may be possible with references but I'm not sure if it is or > how to do it. 1. Yep - Have "compcity" in "companies" reference "location" and have your application add the required record to "location" if required. Look under foreign keys in the docs. 2. Use triggers so that updates to "companies" check whether the "location" table contains the required "compcity" and add it if not. You could even do similar when entries are deleted from "companies", although that might not be what you want. Personally, I'd be tempted by (1) although I'm unclear why. Maybe it's because this stops idiot users mis-typing "Londoon" or having "New-York" and "New York". -- Richard Huxton