Re: [GENERAL] Restart a sequence regularly
On 11/22/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Nov 21, 2007 11:44 AM, Marco Colombo <[EMAIL PROTECTED]> wrote: > > Scott Marlowe wrote: > > > revoke all privs on the sequence to anyone but the user about to reset it > > > reset it > > > grant the options back > > > > Quoting the OP: > > > That means, when others want to access the sequence between > > > 31-Dec 23:59:55 and 1-Jan 00:00:05, they are waiting instead of > > > getting an error. > > > > If you remove the privs, clients will get an error, unless I'm missing > > something. > > Good point. I'm guessing if you need a way to make other users wait, > not get an error, you'll need to use a funtion with a security definer > that will sleep or something during that period. > > hm. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > Thanks for your help! Would you mind to give me an example on how to write this kind of function? -- Kathy Lo ---(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] Restart a sequence regularly
On 11/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
> > Good point. I'm guessing if you need a way to make other users wait,
> > not get an error, you'll need to use a funtion with a security definer
> > that will sleep or something during that period.
>
> What you'd want is to take out an exclusive lock on the sequence.
>
> [ fools around... ] Hmm, we don't let you do LOCK TABLE on a sequence,
> which is perhaps overly restrictive, but you can get the same effect
> with any ALTER TABLE command that works on a sequence. For instance
> a no-op ALTER OWNER:
>
> Session 1:
>
> regression=# create sequence s;
> CREATE SEQUENCE
> regression=# begin;
> BEGIN
> regression=# alter table s owner to postgres;
> ALTER TABLE
>
> Session 2;
>
> regression=# select nextval('s');
> [ hangs ... ]
>
> Session 1:
>
> regression=# alter sequence s restart with 42;
> ALTER SEQUENCE
> regression=# commit;
> COMMIT
>
> Session 2:
>
> nextval
> -
> 42
> (1 row)
>
>
>regards, tom lane
>
> ---(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
>
Thanks for your reply.
But, the owner of the sequence originally is postgres. Does it work?
--
Kathy Lo
---(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] Restart a sequence regularly
On 11/21/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Kathy Lo wrote:
> > Hi,
> >
> > I am using Postgresql 8.0.3 in Fedora Core 4.
> >
> > In my database, it contains a sequence. And, I need to alter the range
> > of this sequence and restart it to the start of the new range at
> > 00:00:00 on 1st January on every year. 5 seconds before and after that
> > time, I need to prevent users from calling nextval() to retrieve the
> > next number from this sequence.
>
> You probably shouldn't attach any meaning to the numbers from a sequence
> - they're just guaranteed to be unique, nothing else.
Yes, the sequence is just for guaranted that every users can get a
unique number. It does not relate to any tables
>
> > I can write a Perl script to alter the sequence and schedule to run
> > this script at 23:59:55 on 31st December on every year.
> >
> > But, I don't know how to lock the sequence to prevent others from
> > accessing this sequence to get next number and Postgresql does not
> > support to lock a sequence.
>
> That would defeat the point of a sequence.
>
> > How can I prevent others from accessing the sequence, like locking a
> > table? That means, when others want to access the sequence between
> > 31-Dec 23:59:55 and 1-Jan 00:00:05, they are waiting instead of
> > getting an error.
>
> If you just want a new range of numbers to start 1st Jan, you could wrap
> nextval() in another function that adds a base-value in depending on the
> current date. Something like:
>
> SELECT EXTRACT('YEAR' FROM CURRENT_DATE)*1000 + nextval(...)
>
> If you really need to lock the sequence again, wrap it in another
> function and have that function sleep for the required changeover period.
>
> Of the top of my head it sounds awkward though - can you explain more
> about how you're using this?
Actually, the sequence is formed by 4-digit of year and 6-digit of
sequence. So, it is required to change and restart the range of
sequence at the beginning of every year. For example, at the beginning
of 2008, the sequence should be changed to the range of 200801 -
200899 and restart at 200801. In the time of changing the
sequence, it does not allow any users to get the unique number from
this sequence. However, our staff don't want to do it manually because
it is difficult for them to make sure no one accessing the sequence
and our service cannot stop at that time. Therefore, I need to let the
users to wait in the period of changing the sequence.
>
> --
> Richard Huxton
> Archonet Ltd
>
--
Kathy Lo
---(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] Restart a sequence regularly
Hi, I am using Postgresql 8.0.3 in Fedora Core 4. In my database, it contains a sequence. And, I need to alter the range of this sequence and restart it to the start of the new range at 00:00:00 on 1st January on every year. 5 seconds before and after that time, I need to prevent users from calling nextval() to retrieve the next number from this sequence. I can write a Perl script to alter the sequence and schedule to run this script at 23:59:55 on 31st December on every year. But, I don't know how to lock the sequence to prevent others from accessing this sequence to get next number and Postgresql does not support to lock a sequence. How can I prevent others from accessing the sequence, like locking a table? That means, when others want to access the sequence between 31-Dec 23:59:55 and 1-Jan 00:00:05, they are waiting instead of getting an error. Thank -- Kathy Lo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] memory leak under heavy load?
On 12/8/05, Martijn van Oosterhout wrote: > On Thu, Dec 08, 2005 at 12:29:11PM +0800, Kathy Lo wrote: > > > But this shouldn't be an issue here. If you set the IPC_RMID flag then > > > the kernel should remove the segment when all users go away. This is > > > standard IPC behaviour and is documentated in the manpage... > > > > > > > Would you please tell me where to find the manpage and how to set IPC_RMID > flag? > > See the shmctl() manpage: > >int shmctl(int shmid, int cmd, struct shmid_ds *buf); > > One of the command ids is IPC_RMID > Do I need to change the source code of postgresql if I want to set IPC_RMID flag to solve this problem? -- Kathy Lo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] memory leak under heavy load?
> But this shouldn't be an issue here. If you set the IPC_RMID flag then > the kernel should remove the segment when all users go away. This is > standard IPC behaviour and is documentated in the manpage... > Would you please tell me where to find the manpage and how to set IPC_RMID flag? -- Kathy Lo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Memory Leakage Problem
On 12/8/05, Scott Marlowe <[EMAIL PROTECTED]> wrote: > Please keep replies on list, this may help others in the future, and > also, don't top post (i.e. put your responses after my responses... > Thanks) > > On Tue, 2005-12-06 at 20:16, Kathy Lo wrote: > > For a back-end database server running Postgresql 8.0.3, it's OK. But, > > this problem seriously affects the performance of my application > > server. > > > > I upgraded my application server from > > > > Redhat 7.3 > > unixODBC 2.2.4 > > Postgresql 7.2.1 with ODBC driver > > > > to > > > > Redhat 9.0 > > unixODBC 2.2.11 > > Postgresql 8.0.3 > > psqlodbc-08.01.0101 > > pg_autovacuum runs as background job > > > > Before upgrading, the application server runs perfectly. After > > upgrade, this problem appears. > > > > When the application server receives the request from a client, it > > will access the back-end database server using both simple and complex > > query. Then, it will create a database locally to store the matched > > rows for data processing. After some data processing, it will return > > the result to the requested client. If the client finishes browsing > > the result, it will drop the local database. > > OK, there could be a lot of problems here. Are you actually doing > "create database ..." for each of these things? I'm not sure that's a > real good idea. Even create schema, which would be better, strikes me > as not the best way to handle this. > Actually, my program is written using C++ so I use "create database" SQL to create database. If not the best way, please tell me another method to create database in C++ program. > > At the same time, this application server can serve many many clients > > so the application server has many many local databases at the same > > time. > > Are you sure that you're better off with databases on your application > server? You might be better off with either running these temp dbs on > the backend server in the same cluster, or creating a cluster just for > these jobs that is somewhat more conservative in its memory usage. I > would lean towards doing this all on the backend server in one database > using multiple schemas. > Because the data are distributed in many back-end database servers (physically, in different hardware machines), I need to use Application server to temporarily store the data retrieved from different machines and then do the data processing. And, for security reason, all the users cannot directly access the back-end database servers. So, I use the database in application server to keep the result of data processing. > > After running the application server for a few days, the memory of the > > application server nearly used up and start to use the swap memory > > and, as a result, the application server runs very very slow and the > > users complain. > > Could you provide us with your evidence that the memory is "used up?" > What is the problem, and what you perceive as the problem, may not be > the same thing. Is it the output of top / free, and if so, could we see > it, or whatever output is convincing you you're running out of memory? > When the user complains the system becomes very slow, I use top to view the memory statistics. In top, I cannot find any processes that use so many memory. I just found that all the memory was used up and the Swap memory nearly used up. I said it is the problem because, before upgrading the application server, no memory problem even running the application server for 1 month. After upgrading the application server, this problem appears just after running the application server for 1 week. Why having this BIG difference between postgresql 7.2.1 on Redhat 7.3 and postgresql 8.0.3 on Redhat 9.0? I only upgrade the OS, postgresql, unixODBC and postgresql ODBC driver. The program I written IS THE SAME. > > I tested the application server without accessing the local database > > (not store matched rows). The testing program running in the > > application server just retrieved rows from the back-end database > > server and then returned to the requested client directly. The memory > > usage of the application server becomes normally and it can run for a > > long time. > > Again, what you think is normal, and what normal really are may not be > the same thing. Evidence. Please show us the output of top / free or > whatever that is showing this. > After I received the user's complain, I just use top to view the memory statistic. I forgot to save the output. But, I am running a test to get back the problem
