|
|
|
|
|
|
| Author |
Message |
George Essig
*nix forums beginner
Joined: 01 Mar 2005
Posts: 5
|
 Posted: Tue Mar 01, 2005 4:17 pm Post subject:
Re: Backupping the table values
|
|
|
On Tue, 1 Mar 2005 17:48:44 +0200, Vitaly Belman <vitalyb@gmail.com> wrote:
| Quote: | --------------------------------------------------------------------------------
CREATE TABLE functions.temp1
(
id1 int4 NOT NULL,
id2 int4,
CONSTRAINT pk_temp1 PRIMARY KEY (id1),
CONSTRAINT temp2_id2 FOREIGN KEY (id2) REFERENCES functions.temp2
(id2) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY
DEFERRED
)
WITHOUT OIDS;
|
Remove 'ON UPDATE RESTRICT ON DELETE RESTRICT' from your create table
statement.
The manual says the following about RESTRICT:
"Produce an error indicating that the deletion or update would create
a foreign key constraint violation. This is the same as NO ACTION
except that the check is not deferrable."
George Essig
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend |
|
| Back to top |
|
 |
josue
*nix forums beginner
Joined: 01 Mar 2005
Posts: 12
|
| Posted: Tue Mar 01, 2005 5:26 pm Post subject:
Re: row numbering
|
|
|
I figured it out, maybe is not the most elegant way but it work for my
case where only small sets are retrieved
create table foo2 (pk int, valor numeric(12,2), porce numeric(5,2));
insert into foo2 values (1,7893.45,0.4);
insert into foo2 values (5,7893.45,0.3);
insert into foo2 values (9,7893.45,0.3);
select *,
(select count(*) from foo2 as f2
where f2.oid <= foo2.oid) as counter
from foo2;
--
Sinceramente,
Josué Maldonado.
.... "Un científico es un hombre tan endeble y humano como cualquiera;
sin embargo, la búsqueda científica puede ennoblecerle, incluso en
contra de su voluntad." -- Isaac Asimov
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings |
|
| Back to top |
|
|
Jim C. Nasby
*nix forums Guru Wannabe
Joined: 01 Mar 2005
Posts: 241
|
| Posted: Tue Mar 01, 2005 6:14 pm Post subject:
Re: Fast major-version upgrade (was: postgresql 8.0 advantages)
|
|
|
On Mon, Feb 28, 2005 at 09:27:46PM +0100, Martijn van Oosterhout wrote:
| Quote: | On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote:
I used a straight copy of the filesystem with running database
(over the net in my case) and immediately after that,
stop the db and rsync for the last changes. This took only
10 minutes (compared to 1.5h for the full filesystem copy)
and I could start up the db in new location.
this could work for you too.
I hadn't thought about using rsync; that's a great idea!
Is there somewhere this could be documented? In an FAQ maybe?
It works only in the special case where the PostgreSQL version number
is the same and you're running on the same platform. How often are you
transferring databases like that. Even transferring from i386 to amd64
wouldn't work like this AFAIUI.
|
Absolutely true, although in the case of database version PostgreSQL
will check that itself. But in the context this was originally brought
up in (using Sloney to upgrade a machine from 7.4.x to 8.x), it would
work great, and rsync would make a huge difference in downtime.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings |
|
| Back to top |
|
|
Kris Jurka
*nix forums beginner
Joined: 01 Mar 2005
Posts: 28
|
| Posted: Tue Mar 01, 2005 11:49 pm Post subject:
Re: JDBC and Portals . Clarification
|
|
|
On Fri, 25 Feb 2005, Dave Smith wrote:
| Quote: | Is a portal using the V3 protocol the same as a cursor? I am trying to
debug a slow query and I want the JDBC driver to use the cursor syntax.
It says it is using a portal but the performance seems like just a
regular sql statement.
|
Yes, portals and cursors are pretty much the same thing. Depending on
your query using a cursor may not actually speed it up, consider SELECT *
FROM tab ORDER BY col; The whole result must be sorted before any row is
returned. What it will do in this case is just reduce the memory
consumption on the client by not fetching all the rows at once. Also
please check the list of restrictions for the JDBC driver to actually use
a portal/cursor backed ResultSet:
http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend |
|
| Back to top |
|
|
Martijn van Oosterhout
*nix forums Guru
Joined: 02 Mar 2005
Posts: 674
|
| Posted: Wed Mar 02, 2005 9:32 am Post subject:
Re: to_char bug?
|
|
|
If the number is negative there needs to be room for the minus sign...
On Tue, Mar 01, 2005 at 09:25:02AM -0000, Ben Trewern wrote:
| Quote: | From the docs:
"FM suppresses leading zeroes and trailing blanks that would otherwise be
added to make the output of a pattern be fixed-width"
It works now but for one I don't understand why the space is added in the
firs place and two I think the docs don't tell the whole story ie leading
blanks and I assume trailing zeros if applicable.
|
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
| Quote: | Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them. |
|
|
| Back to top |
|
|
Ben Trewern
*nix forums beginner
Joined: 01 Mar 2005
Posts: 14
|
| Posted: Wed Mar 02, 2005 1:23 pm Post subject:
Re: to_char bug?
|
|
|
Thanks, sometimes the obvious just passes me by. :-(
| Quote: | If the number is negative there needs to be room for the minus sign...
|
"Martijn van Oosterhout" <kleptog@svana.org> wrote in message
news:20050302103212.GA4567@svana.org...
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) |
|
| Back to top |
|
|
Bernt Andreas Drange
*nix forums beginner
Joined: 02 Mar 2005
Posts: 1
|
| Posted: Wed Mar 02, 2005 1:28 pm Post subject:
Re: [ADMIN] Database Name
|
|
|
On Tue, 2005-03-01 at 09:44 +1300, Envbop wrote:
| Quote: | Hi
I've just inherited a PostgreSQL database, for which I do not have any
details of, like database name or the users.
This used to be a library database which was managed via a web page written
in php.
Its running on a Linux box.
The front end was also written in php. The original writers of this
application is no longer.
I've just started to get familiarisd with postgresql in the last week. and
have installed PostgreSQL v8.0.1 on a Windows server.
And I am trying to connect to the linux box via the phppgadmin tool. as well
as trying an odbc connection.
Can someone tell me where I can find the database names.
|
If you look at the php source code, you will find the database name,
user name and password that the php scripts use to connect to the
database.
That should be a good start.
If php uses odbc to connect, you might also check /etc/odbc.ini on the
linux box.
Good luck!
Bernt
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Wed Mar 02, 2005 2:46 pm Post subject:
Re: Vacuum time degrading
|
|
|
On 2/28/05 6:53 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | Again, VACUUM VERBOSE info would be informative (it's sufficient to look
at your larger tables for this).
|
It took 5.2 hours again tonight to do the vacuum. I don't see anything out
of the ordinary - no explanation for the non-linear increases in vacuum
time.
This is what shows up at the end:
INFO: free space map: 93 relations, 282 pages stored; 1712 total pages
needed
DETAIL: Allocated FSM size: 1000 relations + 1000000 pages = 5920 kB shared
memory.
The tables all show something like:
INFO: "blah": found 0 removable, 366326534 nonremovable row versions in
3241829 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
Wes
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Wed Mar 02, 2005 5:16 pm Post subject:
Re: Vacuum time degrading
|
|
|
Wes <wespvp@syntegra.com> writes:
| Quote: | It took 5.2 hours again tonight to do the vacuum. I don't see anything out
of the ordinary - no explanation for the non-linear increases in vacuum
time.
|
Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines
would help us identify where the time is going.
| Quote: | This is what shows up at the end:
INFO: free space map: 93 relations, 282 pages stored; 1712 total pages
needed
DETAIL: Allocated FSM size: 1000 relations + 1000000 pages = 5920 kB shared
memory.
|
Well, you don't have a problem with FSM being too small anyway ;-)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Wed Mar 02, 2005 5:21 pm Post subject:
Re: Vacuum time degrading
|
|
|
On 3/2/05 12:16 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines
would help us identify where the time is going.
|
I'll send it to you directly - its rather long.
| Quote: | DETAIL: Allocated FSM size: 1000 relations + 1000000 pages = 5920 kB shared
memory.
Well, you don't have a problem with FSM being too small anyway 
|
Nope... Preparation for when deletes start kicking in down the road. If I
can only do a vacuum once a week, I've got to have lots of space.
Wes
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Wed Mar 02, 2005 5:36 pm Post subject:
Re: Vacuum time degrading
|
|
|
On 3/2/05 12:16 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | Would you post the complete VACUUM VERBOSE log? The CPU/elapsed time lines
would help us identify where the time is going.
|
Mailed.
I do see stats like:
CPU 518.88s/25.17u sec elapsed 10825.33 sec.
CPU 884.96s/64.35u sec elapsed 13793.13 sec.
CPU 132.46s/6.66u sec elapsed 2435.42 sec.
CPU 49.25s/4.15u sec elapsed 414.71 sec.
This is a dual CPU hyperthreaded (which probably makes little difference
here) 2.4Ghz RedHat 3.0. The database is on an 8-disk SCSI hardware RAID 5
with 10k rpm disks. Pg_xlog is on a separate volume.
I thought it was a 2.6 kernel, but it looks like it is 2.4.20. I need to
monitor the system when the vacuum is running to see if sar/top show
anything. I wonder if it's hitting the kswapd thrashing problem?
Wes
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org) |
|
| Back to top |
|
|
Wes
*nix forums addict
Joined: 01 Mar 2005
Posts: 57
|
| Posted: Wed Mar 02, 2005 8:31 pm Post subject:
Re: Vacuum time degrading
|
|
|
Watching the system as vacuum is running, I can see that we are encountering
the kswapd/kscand problem in the 2.4.20 kernel. This could very well
account for the non-linear increase in vacuum time.
This problem is fixed in the 2.6 kernel, but we can't upgrade because DELL
is dragging their feet in releasing hardware monitoring compatible with 2.6
kernel. So, we're going to try a 2.4.29 kernel and hope that the problem is
fixed there. With any luck, by Friday I'll know if the kswapd problem is
fixed in 2.4.29 and if that solves the excessive vacuum times.
Wes
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Wed Mar 02, 2005 8:51 pm Post subject:
Re: Vacuum time degrading
|
|
|
Wes <wespvp@syntegra.com> writes:
| Quote: | Watching the system as vacuum is running, I can see that we are encountering
the kswapd/kscand problem in the 2.4.20 kernel. This could very well
account for the non-linear increase in vacuum time.
|
Hmm. Looking at the vacuum verbose output you sent me, it's clear that
the bulk of the time is going into scanning a couple of the larger
indexes. On an index that's been growing for awhile, this involves a
highly nonsequential access pattern (it wants to visit the index leaf
pages in sort order, which will not look much like physical order after
a lot of page splits have occurred). I don't know whether that would
tend to set off the kswapd/kscand problems, but just in terms of
physical I/O time it might be annoying. I was going to suggest
REINDEXing those indexes to see if that cuts the vacuum time at all.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match |
|
| Back to top |
|
|
Wes Palmer
*nix forums beginner
Joined: 03 Mar 2005
Posts: 1
|
| Posted: Thu Mar 03, 2005 3:46 am Post subject:
Re: Vacuum time degrading
|
|
|
On 3/2/05 3:51 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
| Quote: | I was going to suggest
REINDEXing those indexes to see if that cuts the vacuum time at all.
|
The problem with that is it takes a very long time. I've got a couple of
things to try yet on the kswapd problem. If that doesn't work, maybe I can
rebuild one of the indexes and see how much that one improves. I wasn't
aware that the indexes were scanned non-sequentially. The under one hour
time was probably shortly after a full reload. Any chance of change that
behavior to scan in physical storage order?
The index from the largest table that has:
CPU 216.15s/18.13u sec elapsed 2110.84 sec.
is inserted in sequential order. The index
CPU 518.88s/25.17u sec elapsed 10825.33 sec.
has records inserted in essentially a random order, and is also something
like twice as large (key size).
We're going to try to test the 2.4.29 kernel tomorrow.
Wes
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org |
|
| Back to top |
|
|
Tom Lane
*nix forums Guru
Joined: 24 Mar 2005
Posts: 2070
|
| Posted: Thu Mar 03, 2005 3:50 am Post subject:
Re: Vacuum time degrading
|
|
|
Wes Palmer <Wesley.R.Palmer@syntegra.com> writes:
| Quote: | Any chance of change that
behavior to scan in physical storage order?
|
It wouldn't be easy --- there are some locking considerations that say
btbulkdelete needs to scan the index in the same order that an ordinary
scan would do. See the nbtree README for details.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Tue Dec 02, 2008 6:27 am | All times are GMT
|
|
Myspace Layouts | Loans | Debt Help | Mortgage insurance | Loans
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
PostgreSQL
