Re: [GENERAL] Testing 9.0beta3 and pg_upgrade
Craig Ringer, 13.07.2010 05:11: On 13/07/10 05:29, Thomas Kellerer wrote: I would suggest to either manually change the autocommit mode from within pg_upgrade or to add a note in the manual to disable/remove this setting from psqlrc.conf before running pg_upgrade. Personally I think the first option would be the better one. Should pg_upgrade be reading psqlrc at all? There are bound to be all sorts of exciting issues that psqlrc settings can create. I interpret the error message such that pg_upgrade _calls_ (i.e spawns) psql to run the CREATE DATABASE command. If that is true, probably the easiest solution would be to run psql using the -X switch. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redundant database objects.
On 07/12/2010 11:07 PM, Andrew Bartley wrote: > > I still need some way of finding redundant functions A bit of a blunt instrument, but you could log all statements for a while, and then grep through the logs using a list of all functions of interest to see which ones never show up. Be wary of the performance hit and rapidly growing log though. Another idea would be to modify a copy of 8.3.x source code (I think that's what you said you were on in an earlier post) to emit a NOTICE with a name whenever a function is called if it meets some criteria. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & Support signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
Hi Scott, Although I can't guarantee for 100% that there was no RAID rebuild at some point, I am almost sure that it wasn't the case. Two machines - the ones which were already in production - exhibited this problem. Both of them were already up for some weeks. Now, the reboot rather "fixed" one of them instead of making it worse (as your theory goes this way) the problem "disappeared" (but I don't know for how long). Now, only one of the production machines has the issue ... the one which wasn't rebooted. Strange, strange. Nevertheless thank you for your idea ... this is exactly the way I try to approach the problem, by making some theories and trying to prove or disapprove them :-) Now I will try to further investigate along the tips from Craig and Greg. Andras Fabian -Ursprüngliche Nachricht- Von: Scott Marlowe [mailto:scott.marl...@gmail.com] Gesendet: Dienstag, 13. Juli 2010 03:43 An: Andras Fabian Cc: Tom Lane; pgsql-general@postgresql.org Betreff: Re: [GENERAL] PG_DUMP very slow because of STDOUT ?? On Mon, Jul 12, 2010 at 7:03 AM, Andras Fabian wrote: > This STDOU issue gets even weirder. Now I have set up our two new servers > (identical hw/sw) as I would have needed to do so anyways. After having PG > running, I also set up the same test scenario as I have it on our problematic > servers, and started the COPY-to-STDOUT experiment. And you know what? Both > new servers are performing well. No hanging, and the 3 GByte test dump was > written in around 3 minutes (as expected). To make things even more > complicated ... I went back to our production servers. Now, the first one - > which I froze up with oprofile this morning and needed a REBOOT - is > performing well too! It needed 3 minutes for the test case ... WTF? BUT, the > second production server, which did not have a reboot, is still behaving > badly. I'm gonna take a scientific wild-assed guess that your machine was rebuilding RAID arrays when you started out, and you had massive IO contention underneath the OS level resulting in such a slow down. Note that you mentioned ~5% IO Wait. That's actually fairly high if you've got 8 to 16 cores or something like that. It's much better to use iostat -xd 60 or something like that and look for IO Utilization at the end of the lines. Again, just a guess. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redundant database objects.
Thanks Alexander, Wish i had thought of that. I still need some way of finding redundant functions Thanks again Andrew On 13 July 2010 15:38, Alexander Pyhalov wrote: > Hello. > When we moved old projects from postgresql 7.x to 8.4, I just looked at > modification time for files in base// . So, I could determine, > that some databases were inactive (precisely,not updated) for about a year > and move them to archive... > > Andrew Bartley wrote: > > Is there an effective way to identify these objects using the stats >> tables? Something like a last accessed/used or some such column? >> Any suggestions welcomed. >> Thanks >> >> Andrew Bartley >> Aimstats Pty Ltd >> >> > > -- > С уважением, > Александр Пыхалов, > системный администратор ЮГИНФО ЮФУ. > >
Re: [GENERAL] Redundant database objects.
Hello. When we moved old projects from postgresql 7.x to 8.4, I just looked at modification time for files in base// . So, I could determine, that some databases were inactive (precisely,not updated) for about a year and move them to archive... Andrew Bartley wrote: Is there an effective way to identify these objects using the stats tables? Something like a last accessed/used or some such column? Any suggestions welcomed. Thanks Andrew Bartley Aimstats Pty Ltd -- С уважением, Александр Пыхалов, системный администратор ЮГИНФО ЮФУ. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why can't I see the definition of my relations
In response to Andrew Falanga :
> Hi,
>
> I just finished defining a couple of tables with PgAdmin III and I'm
> seeing something peculiar. I'm not sure what the problem is. When I
> connect to the DB using psql and do "\d " I get an error saying
> that there's not relations by that name. What? When I do, "\d" I see
> the tables listed. Here's a sample of the output:
>
> cgems=# \d
> List of relations
> Schema | Name | Type | Owner
> +-+---+---
> public | Mineral | table | cgems
> public | Stone | table | cgems
> (2 rows)
>
>
> cgems=# \d Stone
> Did not find any relation named "Stone".
>
> I'm guessing that it has something to do with permissions, but I'm
No, the reason is another:
test=# create table "Stone"(id serial);
NOTICE: CREATE TABLE will create implicit sequence "Stone_id_seq" for serial
column "Stone.id"
CREATE TABLE
test=*# \d Stone
Did not find any relation named "Stone".
test=*# \d "Stone"
Table "public.Stone"
Column | Type | Modifiers
+-+--
id | integer | not null default nextval('"Stone_id_seq"'::regclass)
You have to use the " if the table-name contains upper-case characters.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why can't I see the definition of my relations
Hi, I just finished defining a couple of tables with PgAdmin III and I'm seeing something peculiar. I'm not sure what the problem is. When I connect to the DB using psql and do "\d " I get an error saying that there's not relations by that name. What? When I do, "\d" I see the tables listed. Here's a sample of the output: cgems=# \d List of relations Schema | Name | Type | Owner +-+---+--- public | Mineral | table | cgems public | Stone | table | cgems (2 rows) cgems=# \d Stone Did not find any relation named "Stone". I'm guessing that it has something to do with permissions, but I'm connecting using psql using the same UID that made the DB and the tables. If I connect to the DB using the UID of the table owner, I get the same response. Thanks for any help, Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No PL/PHP ? Any reason?
Excerpts from Joshua D. Drake's message of mar jul 13 00:00:07 -0400 2010: > On Tue, 2010-07-13 at 03:42 +, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: RIPEMD160 > > > > > > Joshua D. Drake wrote: > > >> * No trusted/untrusted versions > > > > > > This is false. There are both. > > > > Ah, good news, glad I was misinformed. I'm curious, what > > mechanism does it use for trusted? > > I would have to defer to Alvaro on that one. PHP's "safe mode" http://www.php.net/manual/en/features.safe-mode.php ... which, now I realize, has been deprecated ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \COPY ... CSV with hex escapes
On 08/07/10 17:42, Alban Hertroys wrote:
> On 8 Jul 2010, at 4:21, Craig Ringer wrote:
>
>> Yes, that's ancient. It is handled quite happily by \copy in csv mode,
>> except that when csv mode is active, \xnn escapes do not seem to be
>> processed. So I can have *either* \xnn escape processing *or* csv-style
>> input processing.
>>
>> Anyone know of a way to get escape processing in csv mode?
>
>
> And what do those hex-escaped bytes mean? Are they in text strings? AFAIK CSV
> doesn't contain any information about what encoding was used to create it, so
> it could be about anything; UTF-8, Win1252, ISO-8859-something, or whatever
> Sybase was using.
>
> I'm just saying, be careful what you're parsing there ;)
Thanks for that. In this case, the escapes are just "bytes" - what's
important is that, after unescaping, the CSV data is interpreted as
latin-1. OK, Windows-1252, but close enough.
In the end Python's csv module did the trick. I just pulled in the CSV
data, and spat out Postgresql-friendly COPY format so that I didn't need
to use the COPY ... CSV modifier and Pg would interpret the escapes
during input.
In case anyone else needs to deal with this format, here's the program I
used.
--
Craig Ringer
Tech-related writing: http://soapyfrogs.blogspot.com/
#!/usr/bin/env python
import os
import sys
import csv
class DialectSybase(csv.Dialect):
delimiter = ','
doublequote = True
escapechar = None
quotechar = '\''
quoting = csv.QUOTE_MINIMAL
lineterminator = '\n'
class DialectPgCOPY(csv.Dialect):
delimiter = '\t'
doublequote = False
escapechar = None
quotechar = None
quoting = csv.QUOTE_NONE
lineterminator = '\n'
#class DialectPgCOPY(csv.Dialect):
#delimiter = '\t'
#doublequote = True
#escapechar = '\\'
#quotechar = '\''
#quoting = csv.QUOTE_NONE
#lineterminator = '\n'
def unescape_item(item):
''' noop so far '''
#if item.find("\\X") >= 0:
#print repr(item)
#return item
return item.replace("\\X","\\x")
def unescape_row(row):
newrow = []
for item in row:
newitem = item
if type(item) == str:
newitem = unescape_item(item)
newrow.append(newitem)
return newrow
def main(infn, outfn):
infile = open(infn,'r')
outfile = open(outfn,'w')
r = csv.reader( infile, dialect=DialectSybase )
w = csv.writer( outfile, dialect=DialectPgCOPY )
for row in r:
w.writerow(unescape_row(row))
if __name__ == '__main__':
print "customers"
main('customer.txt', 'customer_unescaped.txt')
print "class"
main('class.txt', 'class_unescaped.txt')
print "orders"
main('orders.txt', 'orders_unescaped.txt')
print "items"
main('items.txt', 'items_unescaped.txt')
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No PL/PHP ? Any reason?
On Tue, 2010-07-13 at 03:42 +, Greg Sabino Mullane wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > Joshua D. Drake wrote: > >> * No trusted/untrusted versions > > > > This is false. There are both. > > Ah, good news, glad I was misinformed. I'm curious, what > mechanism does it use for trusted? I would have to defer to Alvaro on that one. > > >> * Not even in contrib or pgfoundry or github > > No. No reason to be. > > Reason: easier to find. A github mirror is cheap (only costing a > few minutes of time) and very useful. Yes, we have been looking into github rather aggressively. I would expect something like that to happen. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No PL/PHP ? Any reason?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Joshua D. Drake wrote: >> * No trusted/untrusted versions > > This is false. There are both. Ah, good news, glad I was misinformed. I'm curious, what mechanism does it use for trusted? >> * Not even in contrib or pgfoundry or github > No. No reason to be. Reason: easier to find. A github mirror is cheap (only costing a few minutes of time) and very useful. lvaro Herrera wrote: > I fixed it when I saw Greg's note. Wow, that's some quick service. Thanks Alvaro. Carlo Stonebanks wrote: >> Obviously we need to improve our documentation. What led you to >> believe it does not exist? > This is my fault entirely. When I Googled for this, I flailed around with > fancy terms that didn't connect. And, as you pointed out, its not in the > core distibution or the foundry. But I didn't consider the product would be > logically called pl/php until I wrote this post! Not to belabor the point, but what terms did you use? At the very least, someone can wrote a blog post with such terms so that other people searching for plphp can find it easier. Better, the Official Docs can have the terms (if they are reasonable). >> Nobody uses pl/php. > I'm not a PHP developer (but after programmer, but my understanding is that > the PHP community is over-represented with HTML designers using PHP to > create dynamic content. What I have seen was lots of in-line HTML/PHP > programming with no understanding of seperating the presentation from the > business logic. But this is not PHP's fault. > > However, it stands to reason that there ARE people writing good PHP code > with a seperation between the business/model and the presentation layer. > This code would represent the business process repository and could be > shared with other applications (especially non-PHP ones) either via a web > service or as a stored proc. Web services are fussy things, whereas if you > have a connection to a DB already, a stored proc is a simple thing. Keep in mind the context of my "nobody uses pl/php" was "none of my Postgres clients uses pl/php". Certainly it is, and can be useful to people. As far as separating the presentation from the business logic, it's ironic that most large PHP programs and apps have now completely moved away from the traditional inline HTML+PHP in one file which was (is?) touted as a PHP strength (which indicates that perhaps it is PHP's fault). This new separation is a good thing, because that inline junk is the wrong way to do things except for the quickest and ugliest of hacks. > service or as a stored proc. Web services are fussy things, whereas if you > have a connection to a DB already, a stored proc is a simple thing. Sure, but I'd argue that it's certainly more portable to write it in plpgsql before using any procedural language, especially now that it is enabled by default in the next version. :) Thanks to everyone for staying calm and reasoned in this thread. I'll have to try harder with my PHP baiting next time. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007122337 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw74IUACgkQvJuQZxSWSsgRhQCg6ivis6IEP//FqLVDNeTxIYp1 LugAmwTDeBWbZJcRhaDg75aWcwiKWWD5 =YM6B -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Testing 9.0beta3 and pg_upgrade
On 13/07/10 05:29, Thomas Kellerer wrote: > I would suggest to either manually change the autocommit mode from > within pg_upgrade or to add a note in the manual to disable/remove this > setting from psqlrc.conf before running pg_upgrade. Personally I think > the first option would be the better one. Should pg_upgrade be reading psqlrc at all? There are bound to be all sorts of exciting issues that psqlrc settings can create. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
On 12/07/10 21:03, Andras Fabian wrote:
> This STDOU issue gets even weirder. Now I have set up our two new servers
> (identical hw/sw) as I would have needed to do so anyways. After having PG
> running, I also set up the same test scenario as I have it on our problematic
> servers, and started the COPY-to-STDOUT experiment. And you know what? Both
> new servers are performing well. No hanging, and the 3 GByte test dump was
> written in around 3 minutes (as expected). To make things even more
> complicated ... I went back to our production servers. Now, the first one -
> which I froze up with oprofile this morning and needed a REBOOT - is
> performing well too! It needed 3 minutes for the test case ... WTF? BUT, the
> second production server, which did not have a reboot, is still behaving
> badly.
> Now I tried to dig deeper (without killing a production server again) ... and
> came to comparing the outputs of PS (with '-fax' parameter then, '-axl'). Now
> I have found something interesting:
> - all fast servers show the COPY process as being in the state Rs ("runnable
> (on run queue)")
> - on the still slow server, this process is in 9 out of 10 samples in Ds
> ("uninterruptible sleep (usually IO)")
>
> Now, this "Ds" state seems to be something unhealthy - especially if it is
> there almost all the time - as far as my first reeds on google show (and
> although it points to IO, there is seemingly only very little IO, and IO-wait
> is minimal too). I have also done "-axl" with PS, which brings the following
> line for our process:
> F UID PID PPID PRI NIVSZ RSS WCHAN STAT TTYTIME COMMAND
> 1 5551 2819 4201 20 0 5941068 201192 conges Ds ? 2:05
> postgres: postgres musicload_cache [local] COPY"
Your wchan column isn't wide enough to show the full function name, but
I'd say it's related to some form of throttling or congestion control.
Get a wider view of that column to find out what the full function name
is. Grepping the kernel source for it can then tell you a lot about
where in the kernel it is and what might be going on.
Try:
ps ax -O wchan:40
to get a decently wide view of that col.
--
Craig Ringer
Tech-related writing: http://soapyfrogs.blogspot.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 beta 3 release announcement
Thomas Kellerer wrote: > Bruce Momjian wrote on 12.07.2010 21:34: > > Thom Brown wrote: > >> Could someone clarify the info in this paragraph: > >> > >> "Note that, due to a system catalog change, an initdb and database > >> reload will be required for upgrading from 9.0Beta1. We encourage > >> users to use this opportunity to test pg_upgrade for the upgrade from > >> Beta2 or an earlier version of 9.0. Please report your results." > >> > >> This suggests that the system catalog change only occurred in Beta2, > >> not Beta3. So if that's the case, why would I want to test pg_upgrade > >> going from Beta2 to Beta3 if they use the same system catalog layout? > > > > Yes, this is wrong. It should be "We encourage users to use this > > opportunity to test pg_upgrade for the upgrade from Beta1 or an earlier > > version of 9.0. Please report your results." However, I see the beta3 > > release notes are now on the web site so it seems too late to fix this. > > I'm a bit confused that pg_upgrade is "advertised" in this way, but is > "hidden" in the manual under "additionally supplied modules". > > If I was a new user, I would look in the administration chapter for any > reference on how to do in-place upgrades. > > Is there any reason why pg_upgrade is not documented in the "main" manual? Well, pg_upgrade was only added in beta2, so maybe we need to go back and mention it as part of upgrading. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
On Mon, Jul 12, 2010 at 7:03 AM, Andras Fabian wrote: > This STDOU issue gets even weirder. Now I have set up our two new servers > (identical hw/sw) as I would have needed to do so anyways. After having PG > running, I also set up the same test scenario as I have it on our problematic > servers, and started the COPY-to-STDOUT experiment. And you know what? Both > new servers are performing well. No hanging, and the 3 GByte test dump was > written in around 3 minutes (as expected). To make things even more > complicated ... I went back to our production servers. Now, the first one - > which I froze up with oprofile this morning and needed a REBOOT - is > performing well too! It needed 3 minutes for the test case ... WTF? BUT, the > second production server, which did not have a reboot, is still behaving > badly. I'm gonna take a scientific wild-assed guess that your machine was rebuilding RAID arrays when you started out, and you had massive IO contention underneath the OS level resulting in such a slow down. Note that you mentioned ~5% IO Wait. That's actually fairly high if you've got 8 to 16 cores or something like that. It's much better to use iostat -xd 60 or something like that and look for IO Utilization at the end of the lines. Again, just a guess. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redundant database objects.
Thanks Greg, It seems that the underlying stats tables are reset on a periodic basis, can i stop this process? Is it a .conf setting? I have had a good look around, nothing sticks out. If I can stop it, then i could use pg_stat_reset() then monitor the stat views over an extended period without them being reset by some periodic job. That, at the moment, is my main concern. Thanks again. Also i need to find similar information regarding functions and views Any suggestions? Thanks Andrew Bartley On 13 July 2010 09:45, Greg Smith wrote: > Andrew Bartley wrote: > >> Unfortunately these views only give me what appears to be a certain time >> frame. This does not help all that much. It will give a list of tables, >> indexes and sequences that have been used in the time frame, so that is at >> least a start. >> > > You can use pg_stat_reset() to set those back to 0 again and then see what > actually gets used moving forward from the point you do that. That's a > reasonable idea to do anyway to make all those statistics better reflect > recent activity rather than historical. Just be warned that it will screw > up many monitoring systems if you have them pointed toward those statistics > tables and grabbing snapshots, some will view the reset as the values going > negative which doesn't make any real-world sense. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > g...@2ndquadrant.com www.2ndQuadrant.us > >
Re: [GENERAL] Redundant database objects.
Andrew Bartley wrote: Unfortunately these views only give me what appears to be a certain time frame. This does not help all that much. It will give a list of tables, indexes and sequences that have been used in the time frame, so that is at least a start. You can use pg_stat_reset() to set those back to 0 again and then see what actually gets used moving forward from the point you do that. That's a reasonable idea to do anyway to make all those statistics better reflect recent activity rather than historical. Just be warned that it will screw up many monitoring systems if you have them pointed toward those statistics tables and grabbing snapshots, some will view the reset as the values going negative which doesn't make any real-world sense. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Redundant database objects.
Thanks Joe, Unfortunately these views only give me what appears to be a certain time frame. This does not help all that much. It will give a list of tables, indexes and sequences that have been used in the time frame, so that is at least a start. It would be good if there was a timestamp (last accessed) that would give me a clearer indication. Thanks Andrew On 13 July 2010 08:46, Joe Conway wrote: > On 07/12/2010 02:40 PM, Andrew Bartley wrote: > > We have a large number of orphaned or redundant tables, views, and > > functions, due to many years of inadequate source management. > > > > We are running " PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC > > gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 " > > > > Is there an effective way to identify these objects using the stats > > tables? Something like a last accessed/used or some such column? > > Maybe pg_statio* views? > http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html > > Joe > > -- > Joe Conway > credativ LLC: http://www.credativ.us > Linux, PostgreSQL, and general Open Source > Training, Service, Consulting, & Support > >
Re: [GENERAL] Redundant database objects.
On 07/12/2010 02:40 PM, Andrew Bartley wrote: > We have a large number of orphaned or redundant tables, views, and > functions, due to many years of inadequate source management. > > We are running " PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC > gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 " > > Is there an effective way to identify these objects using the stats > tables? Something like a last accessed/used or some such column? Maybe pg_statio* views? http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & Support signature.asc Description: OpenPGP digital signature
[GENERAL] Redundant database objects.
Hi all, Our project has been running for 10 years now. We have a large number of orphaned or redundant tables, views, and functions, due to many years of inadequate source management. We are running " PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2 " Is there an effective way to identify these objects using the stats tables? Something like a last accessed/used or some such column? Any suggestions welcomed. Thanks Andrew Bartley Aimstats Pty Ltd
[GENERAL] Testing 9.0beta3 and pg_upgrade
Hi,
I'm trying pg_upgrade on my Windows installation and I have two suggestions
for the manual regarding pg_upgrade:
When specifying directories, pg_upgrade *requires* a forward slash as the path
separator. This is (still) uncommon in the Windows world (although Windows does
support it) and even though the example in the manual does show forward
slashes, I think it would be a good idea to specifically mention the fact that
it will *not* work with a backslash.
Actually the error message when you do so is a bit misleading as well ("You must
identify the directory where the old cluster binaries reside") even though the
paramter is there.
After I sorted that out I ran pg_upgrade and it failed somewhere in the middle:
-- snip
C:\etc\pg90-beta3>C:\etc\pg90-beta3\pgsql\bin\pg_upgrade.exe --user=postgres
--old-datadir "c:/Daten/db/pgdata84/" -
-old-bindir "c:/Programme/PostgreSQL/8.4/bin/" --new-datadir
"c:/etc/pg90-beta3/datadir/" --new-port=5434 --new-bind
ir "C:\etc\pg90-beta3\pgsql\bin"
Performing Consistency Checks
-
Checking old data directory (c:/Daten/db/pgdata84) ok
Checking new data directory (c:/etc/pg90-beta3/datadir) ok
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump ok
Checking for presence of required libraries ok
| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from c:/Daten/db/pgdata84/global/pg_control.old.
Performing Migration
Adding ".old" suffix to old global/pg_control ok
Analyzing all rows in the new cluster ok
Freezing all rows on the new clusterok
Deleting new commit clogs ok
Copying old commit clogs to new server 1 Datei(en) kopiert
ok
Setting next transaction id for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster
psql:C:/etc/pg90-beta3/pg_upgrade_dump_globals.sql:29: ERROR: CREATE DATABASE c
annot run inside a transaction block
There were problems executing ""C:\etc\pg90-beta3\pgsql\bin/psql" --port 5434 --username
"postgres" --set ON_ERROR_STOP=on -f "C:\etc\pg90-b
eta3/pg_upgrade_dump_globals.sql" --dbname template1 >> "nul""
-- end of console output
The "cannot run inside a transaction block" rang a bell, and once I removed "\set
AUTOCOMMIT off" from my psqlrc.conf, pg_upgrade went through without problems.
I would suggest to either manually change the autocommit mode from within
pg_upgrade or to add a note in the manual to disable/remove this setting from
psqlrc.conf before running pg_upgrade. Personally I think the first option
would be the better one.
Regards
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
Please don't top post. On Mon, Jul 12, 2010 at 2:20 PM, Tom Wilcox wrote: > On 12/07/2010 19:26, Scott Marlowe wrote: >> >> On Mon, Jul 12, 2010 at 7:57 AM, Thom Brown wrote: >> >>> >>> On 12 July 2010 14:50, Tom Wilcox wrote: >>> Hi Thom, I am performing update statements that are applied to a single table that is about 96GB in size. Much deleted, so my reply to your question is more obvious. Any suggestions? >> >> Is there a way to insert the data with these values already set when >> you first load the db? >> > I could perform the settings manually (set config, restart svr, execute > script, come back 2 days later, reset config, restart svr, execute more > script,...), but that sort of defeats the point. My aim to have the > simplest, automatic setup possible. Preferably completely contained within > PostgreSQL so that all is need is a dump of the DB for a complete backup... Not what I was talking about. Is there a way to NOT perform the update you mention up above, by inserting the data with the values already set properly. I don't see why that can't be incorporated into your solution, but I'm not sure how exactly your solution is working. Note that customer requirement that it all be in SQL is a bit idiotic. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
I could perform the settings manually (set config, restart svr, execute script, come back 2 days later, reset config, restart svr, execute more script,...), but that sort of defeats the point. My aim to have the simplest, automatic setup possible. Preferably completely contained within PostgreSQL so that all is need is a dump of the DB for a complete backup... On 12/07/2010 19:26, Scott Marlowe wrote: On Mon, Jul 12, 2010 at 7:57 AM, Thom Brown wrote: On 12 July 2010 14:50, Tom Wilcox wrote: Hi Thom, I am performing update statements that are applied to a single table that is about 96GB in size. These updates are grouped together in a single transaction. This transaction runs until the machine runs out of disk space. What I am trying to achieve is for postgresql to complete this updating transaction without running out of memory. I assume that this is happening because for a Rollback to be possible, postgres must at least keep track of the previous values/changes whilst the transaction is not complete and committed. I figured this would be the most likely cause for us to run out of disk space and therefore I would like to reconfigure postgresql not to hold onto previous copies somehow. Any suggestions? Is there a way to insert the data with these values already set when you first load the db? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 beta 3 release announcement
Bruce Momjian wrote on 12.07.2010 21:34: Thom Brown wrote: Could someone clarify the info in this paragraph: "Note that, due to a system catalog change, an initdb and database reload will be required for upgrading from 9.0Beta1. We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta2 or an earlier version of 9.0. Please report your results." This suggests that the system catalog change only occurred in Beta2, not Beta3. So if that's the case, why would I want to test pg_upgrade going from Beta2 to Beta3 if they use the same system catalog layout? Yes, this is wrong. It should be "We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta1 or an earlier version of 9.0. Please report your results." However, I see the beta3 release notes are now on the web site so it seems too late to fix this. I'm a bit confused that pg_upgrade is "advertised" in this way, but is "hidden" in the manual under "additionally supplied modules". If I was a new user, I would look in the administration chapter for any reference on how to do in-place upgrades. Is there any reason why pg_upgrade is not documented in the "main" manual? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 beta 3 release announcement
Thom Brown wrote: > Could someone clarify the info in this paragraph: > > "Note that, due to a system catalog change, an initdb and database > reload will be required for upgrading from 9.0Beta1. We encourage > users to use this opportunity to test pg_upgrade for the upgrade from > Beta2 or an earlier version of 9.0. Please report your results." > > This suggests that the system catalog change only occurred in Beta2, > not Beta3. So if that's the case, why would I want to test pg_upgrade > going from Beta2 to Beta3 if they use the same system catalog layout? Yes, this is wrong. It should be "We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta1 or an earlier version of 9.0. Please report your results." However, I see the beta3 release notes are now on the web site so it seems too late to fix this. Sorry I missed seeing this problem earlier. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
Andres Freund wrote: What you can change (and that makes quite a bit of sense in some situations) is the "synchronous_commit" setting. Right. In almost every case where people think they want to disable fsync, what they really should be doing instead is turning off synchronous commit--which is a user-land tunable per session: SET synchronous_commit=false; And potentially increasing wal_writer_delay on the server too: http://www.postgresql.org/docs/current/static/wal-async-commit.html -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
Andras Fabian wrote:
- all fast servers show the COPY process as being in the state Rs ("runnable (on run
queue)")
- on the still slow server, this process is in 9 out of 10 samples in Ds ("uninterruptible sleep (usually IO)")
I've run into significant performance regressions in PostgreSQL
performance due to issues with the Linux scheduler before, specifically
when running a single really intensive client program. You might be
seeing something similar here. I wrote a reference link heavy blog
entry about that at
http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html
you might find useful, one of the batch scheduler tweaks alluded to
there might improve things. Regression here in newer kernels are the
norm rather than the exception, and given the general lack of quality
control in Ubuntu 10.04 I have avoided any performance testing of it
yet. I was going to give it six months after release before I even
thought about that, in hopes more bugs are squashed, but I'm not
optimistic about this distribution for server use at all right now.
There's more information about using oprofile at
http://wiki.postgresql.org/wiki/Profiling_with_OProfile that might help
you dig into the underlying spot it's stuck at.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.us
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On Mon, Jul 12, 2010 at 7:57 AM, Thom Brown wrote: > On 12 July 2010 14:50, Tom Wilcox wrote: >> Hi Thom, >> >> I am performing update statements that are applied to a single table that is >> about 96GB in size. These updates are grouped together in a single >> transaction. This transaction runs until the machine runs out of disk space. >> >> What I am trying to achieve is for postgresql to complete this updating >> transaction without running out of memory. I assume that this is happening >> because for a Rollback to be possible, postgres must at least keep track of >> the previous values/changes whilst the transaction is not complete and >> committed. I figured this would be the most likely cause for us to run out >> of disk space and therefore I would like to reconfigure postgresql not to >> hold onto previous copies somehow. >> >> Any suggestions? Is there a way to insert the data with these values already set when you first load the db? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On Mon, 2010-07-12 at 14:57 +0100, Thom Brown wrote: > On 12 July 2010 14:50, Tom Wilcox wrote: > > Hi Thom, > > > > I am performing update statements that are applied to a single table that is > > about 96GB in size. These updates are grouped together in a single > > transaction. This transaction runs until the machine runs out of disk space. As you are updating this table, you are leaving dead tuples behind for each of the updates that are not hot updates and the table is getting bloated. That is most likely why you are running out of disk space. Turning off fsync will not help you with this. What will help you is trying to get the database to use hot updates instead, or batching the updates and letting the table get vacuumed often enough so that the dead tuples can get marked for re-use. Hot updates would be very beneficial, even if batch updating. They will happen if their is no index on the updated column and there is enough space in the physical page to keep the tuple on the same page. You can adjust the fillfactor to try and favour this. You can check if you are doing hot updates by looking at pg_stat_user_tables for the number of hot updates. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
2010/7/12 Josip Rodin : > On Mon, Jul 12, 2010 at 04:38:48PM +0200, Pavel Stehule wrote: >> 2010/7/12 Josip Rodin : >> > On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: >> >> Meh, personally I'll stick to the good old profiling methods "is it fast >> >> enough", "\timing", and "explain analyze". >> > >> > I agree. Some hint could be included in 'explain analyze' output, maybe >> > just >> > to separate the timings for things that are well covered by the query plan >> > optimizer from those that aren't. I found this in a line like this: >> >> it is useles for functions - explain doesn't show lines of executed >> functions. Can you show some example of some more complex query. > > It doesn't have to show me any lines, but it could tell me which part of > the query is actually being optimized, and OTOH which part is simply being > executed N times unconditionally because it's a function that is marked as > volatile. That alone would be a reasonable improvement. this is different kinds of problems. You can have a very slow a immutable function or very fast volatile function. And with wrong function design your functions can be a 10 times slower. yeah - you can multiply it via wrong or good design with wrong or good stability flag. Regards Pavel Stehule > > -- > 2. That which causes joy or happiness. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 04:38:48PM +0200, Pavel Stehule wrote: > 2010/7/12 Josip Rodin : > > On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: > >> Meh, personally I'll stick to the good old profiling methods "is it fast > >> enough", "\timing", and "explain analyze". > > > > I agree. Some hint could be included in 'explain analyze' output, maybe just > > to separate the timings for things that are well covered by the query plan > > optimizer from those that aren't. I found this in a line like this: > > it is useles for functions - explain doesn't show lines of executed > functions. Can you show some example of some more complex query. It doesn't have to show me any lines, but it could tell me which part of the query is actually being optimized, and OTOH which part is simply being executed N times unconditionally because it's a function that is marked as volatile. That alone would be a reasonable improvement. -- 2. That which causes joy or happiness. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: canceling statement due to statement timeout
On 6 July 2010 16:36, Vick Khera wrote: > On Fri, Jul 2, 2010 at 12:22 PM, Tim wrote: >> I've had a website up for a couple of months and it's starting to get >> these db timeouts as traffic has increased to say 1k pageviews a day. >> > > Are you using any two-phase commit (ie, prepared transactions?) > > We find that if you try to insert data that will conflict with data > pending in a 2PC, you get a statement timeout immediately. I haven't setup anything like that . But geodjango might be doing things like that in the background. I'll get some queries together and post them here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] getting the last N tuples of a query
On Thu, Jul 8, 2010 at 9:09 PM, Kenichiro Tanaka wrote: > Hello. > > I agree Ben. > But,I try your question as an SQL puzzle. > Doses this SQL meet what you want? > > select * from wantlast offset (select count(*)-10 from wantlast); that works, but for any non trivial query it's not optimal because it runs the complete query twice. if you are doing a lot of joins, etc. (or your query involves volatile operations) you might want to avoid this. cursors can do it: begin; declare c scroll cursor for select generate_series(1,1000); fetch last from c; -- discard result fetch backward 10 from c; -- discard result fetch 10 from c; -- your results commit; in 8.4 you can rig it with CTE: with foo as (select generate_series(1,1000) v) select * from foo offset (select count(*) - 10 from foo); the advantage here is you are double scanning the query results, not rerunning the query (this is not guaranteed to be a win, but it often will be). you can often rig it with arrays (dealing with non scalar type arrays is only possible in 8.3+) select unnest(a[array_upper(a, 1)-10:array_upper(a,1)]) from (select array(select generate_series(1,1000) v) as a) q; merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On Monday 12 July 2010 15:29:14 Tom Wilcox wrote: > Hi, > > Is it possible to configure postgres from SQL? > > I am interested in turning off fsync for a set of queries (that take > ages to run) and then turn fsync back on again afterwards. disabling fsync is nearly never a good idea. What you can change (and that makes quite a bit of sense in some situations) is the "synchronous_commit" setting. What kind of queries are those? Many small transactions? Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
Le 12/07/2010 17:02, Tom Wilcox a écrit : > Hi Thom, > > Yeah They can be divided up, but my main issue is that I would like > these functions wrapped up so that the client (who has little to no > experience using PostgreSQL) can just run a SQL function that will > execute all of these updates and prepare many tables and functions for a > product. (Essentially SELECT install_the_program() to setup up the DB > and build the tables). > > However, I keep running into problems because the queries are very time > consuming (several days on fast computers with lots of memory) and > individual queries seem to require different configuration parameters.. > > I have a feeling it is all going to boil down to writing a (python) > script to build the DB from CLI in Linux. But they really want all the > functionality encapsulated in the PostgreSQL server, including this > building process. > Well, you can still use the adminpack contrib module to write the config file from a PostgreSQL connection. But you won't be able to restart PostgreSQL. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
Hi Thom, Yeah They can be divided up, but my main issue is that I would like these functions wrapped up so that the client (who has little to no experience using PostgreSQL) can just run a SQL function that will execute all of these updates and prepare many tables and functions for a product. (Essentially SELECT install_the_program() to setup up the DB and build the tables). However, I keep running into problems because the queries are very time consuming (several days on fast computers with lots of memory) and individual queries seem to require different configuration parameters.. I have a feeling it is all going to boil down to writing a (python) script to build the DB from CLI in Linux. But they really want all the functionality encapsulated in the PostgreSQL server, including this building process. Cheers, Tom On 12/07/2010 14:57, Thom Brown wrote: On 12 July 2010 14:50, Tom Wilcox wrote: Hi Thom, I am performing update statements that are applied to a single table that is about 96GB in size. These updates are grouped together in a single transaction. This transaction runs until the machine runs out of disk space. What I am trying to achieve is for postgresql to complete this updating transaction without running out of memory. I assume that this is happening because for a Rollback to be possible, postgres must at least keep track of the previous values/changes whilst the transaction is not complete and committed. I figured this would be the most likely cause for us to run out of disk space and therefore I would like to reconfigure postgresql not to hold onto previous copies somehow. Any suggestions? Cheers, Tom Hi Tom, Is it not possible to do these updates in batches, or does it have to be atomic? (A small note about replying. Please use "reply to all", and on this mailing list responses should go below.) Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
2010/7/12 Josip Rodin : > On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: >> Meh, personally I'll stick to the good old profiling methods "is it fast >> enough", "\timing", and "explain analyze". > > I agree. Some hint could be included in 'explain analyze' output, maybe just > to separate the timings for things that are well covered by the query plan > optimizer from those that aren't. I found this in a line like this: it is useles for functions - explain doesn't show lines of executed functions. Can you show some example of some more complex query. Pavel > > Filter: (approved AND (NOT archived) AND ((time_to > now()) OR (time_to IS > NULL)) AND ((time_from < now()) OR (time_from IS NULL)) AND > usercandoonobject(1, 1, 'news'::bpchar, news_id)) > > These other referenced columns in the filter were all insignificant > (time-wise) compared to the single function call, but I had to find > that out with a manual search. > > -- > 2. That which causes joy or happiness. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 02:06:43PM +0800, Craig Ringer wrote: > Meh, personally I'll stick to the good old profiling methods "is it fast > enough", "\timing", and "explain analyze". I agree. Some hint could be included in 'explain analyze' output, maybe just to separate the timings for things that are well covered by the query plan optimizer from those that aren't. I found this in a line like this: Filter: (approved AND (NOT archived) AND ((time_to > now()) OR (time_to IS NULL)) AND ((time_from < now()) OR (time_from IS NULL)) AND usercandoonobject(1, 1, 'news'::bpchar, news_id)) These other referenced columns in the filter were all insignificant (time-wise) compared to the single function call, but I had to find that out with a manual search. -- 2. That which causes joy or happiness. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On Monday 12 July 2010 6:29:14 am Tom Wilcox wrote:
> Hi,
>
> Is it possible to configure postgres from SQL?
Yes to a degree, see here:
http://www.postgresql.org/docs/8.4/interactive/functions-admin.html
>
> I am interested in turning off fsync for a set of queries (that take
> ages to run) and then turn fsync back on again afterwards.
This is one of the options not covered by above.
See here:
http://www.postgresql.org/docs/8.4/interactive/runtime-config-wal.html
Example:
test=# SELECT current_setting('fsync');
current_setting
-
on
(1 row)
test=# SELECT set_config('fsync','off',false);
ERROR: parameter "fsync" cannot be changed now
>
> Cheers,
> Tom
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On 12 July 2010 14:50, Tom Wilcox wrote: > Hi Thom, > > I am performing update statements that are applied to a single table that is > about 96GB in size. These updates are grouped together in a single > transaction. This transaction runs until the machine runs out of disk space. > > What I am trying to achieve is for postgresql to complete this updating > transaction without running out of memory. I assume that this is happening > because for a Rollback to be possible, postgres must at least keep track of > the previous values/changes whilst the transaction is not complete and > committed. I figured this would be the most likely cause for us to run out > of disk space and therefore I would like to reconfigure postgresql not to > hold onto previous copies somehow. > > Any suggestions? > > Cheers, > Tom > Hi Tom, Is it not possible to do these updates in batches, or does it have to be atomic? (A small note about replying. Please use "reply to all", and on this mailing list responses should go below.) Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
> Hi, > > Is it possible to configure postgres from SQL? > > I am interested in turning off fsync for a set of queries (that take > ages to run) and then turn fsync back on again afterwards. There are things that can be changed at runtime using SQL - in that case you may just type "SET enable_seqscan = Off" etc. But you can't change fsync, it does not make sense to change this settings for individual queries. As Thom Brown already pointed out, it's not a good way to tune your queries. If you don't need to keep consistency (which is the purpose of fsync), then you may change this directly in postgresql.conf. And if you don't need consistency you must not change that. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Postgres From SQL
On 12 July 2010 14:29, Tom Wilcox wrote: > Hi, > > Is it possible to configure postgres from SQL? > > I am interested in turning off fsync for a set of queries (that take ages to > run) and then turn fsync back on again afterwards. > > Cheers, > Tom > You can only change that option in postgresql.conf and I don't see how that could only apply to a single query. I'd focus more on optimising your queries either by rewriting them, adding indexes (partial/functional where appropriate), keeping things VACUUM'd or using prepared statements. Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Configure Postgres From SQL
Hi, Is it possible to configure postgres from SQL? I am interested in turning off fsync for a set of queries (that take ages to run) and then turn fsync back on again afterwards. Cheers, Tom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
This STDOU issue gets even weirder. Now I have set up our two new servers
(identical hw/sw) as I would have needed to do so anyways. After having PG
running, I also set up the same test scenario as I have it on our problematic
servers, and started the COPY-to-STDOUT experiment. And you know what? Both new
servers are performing well. No hanging, and the 3 GByte test dump was written
in around 3 minutes (as expected). To make things even more complicated ... I
went back to our production servers. Now, the first one - which I froze up with
oprofile this morning and needed a REBOOT - is performing well too! It needed 3
minutes for the test case ... WTF? BUT, the second production server, which did
not have a reboot, is still behaving badly.
Now I tried to dig deeper (without killing a production server again) ... and
came to comparing the outputs of PS (with '-fax' parameter then, '-axl'). Now I
have found something interesting:
- all fast servers show the COPY process as being in the state Rs ("runnable
(on run queue)")
- on the still slow server, this process is in 9 out of 10 samples in Ds
("uninterruptible sleep (usually IO)")
Now, this "Ds" state seems to be something unhealthy - especially if it is
there almost all the time - as far as my first reeds on google show (and
although it points to IO, there is seemingly only very little IO, and IO-wait
is minimal too). I have also done "-axl" with PS, which brings the following
line for our process:
F UID PID PPID PRI NIVSZ RSS WCHAN STAT TTYTIME COMMAND
1 5551 2819 4201 20 0 5941068 201192 conges Ds ? 2:05 postgres:
postgres musicload_cache [local] COPY"
Now, as far as I understood from my google searches, the column WCHAN shows,
where in the kernel my process is hanging. Here it says "conges". Now, can
somebody tell me, what "conges" means Or do I have other options to get
out even more info from the system (maybe without oprofile - as it already
burned my hand :-).
And yes, now I see a reboot as a possible "Fix", but that would not ensure me,
that the problem will not resurface. So, for the time being, I will leave my
current second production server as is ... so I can further narrow down the
potential reasons of this strange STDOUT slow down (especially I someone ha s a
tip for me :-)
Andras Fabian
(in the meantime my "slow" server finished the COPY ... it took 46 minutes
instead of 3 minutes on the fast machines ... a slowdown of factor 15).
-Ursprüngliche Nachricht-
Von: Andras Fabian
Gesendet: Montag, 12. Juli 2010 10:45
An: 'Tom Lane'
Cc: pgsql-general@postgresql.org
Betreff: AW: [GENERAL] PG_DUMP very slow because of STDOUT ??
Hi Tom (or others),
are there some recommended settings/ways to use oprofile on a situation like
this??? I got it working, have seen a first profile report, but then managed to
completely freeze the server on a second try with different oprofile settings
(next tests will go against the newly installed - next and identical - new
servers).
Andras Fabian
-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:t...@sss.pgh.pa.us]
Gesendet: Freitag, 9. Juli 2010 15:39
An: Andras Fabian
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
Andras Fabian writes:
> Now I ask, whats going on here Why is COPY via STDOUT so much slower on
> out new machine?
Something weird about the network stack on the new machine, maybe.
Have you compared the transfer speeds for Unix-socket and TCP connections?
On a Red Hat box I would try using oprofile to see where the bottleneck
is ... don't know if that's available for Ubuntu.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0 beta 3 release announcement
Could someone clarify the info in this paragraph: "Note that, due to a system catalog change, an initdb and database reload will be required for upgrading from 9.0Beta1. We encourage users to use this opportunity to test pg_upgrade for the upgrade from Beta2 or an earlier version of 9.0. Please report your results." This suggests that the system catalog change only occurred in Beta2, not Beta3. So if that's the case, why would I want to test pg_upgrade going from Beta2 to Beta3 if they use the same system catalog layout? Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL PITR - more doubts
Hello all, One doubt about how PostgreSQL PITR works. Let us say I have all the archived WALs for the past week with archive_command = 'cp -i %p /home/postgres/archive/%f http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queries about PostgreSQL PITR
On Mon, Jul 12, 2010 at 5:29 PM, Jayadevan M wrote: > Hi, >>Because you didn't disable recovery_target_inclusive, I guess. >> > http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE > Thanks. I was almost sure this will fix it. But the issue seems to be > something else. Even if I give a time that is a few more minutes before > what I got from select now(), it is always moving upto/or just before > (depending on the above parameter) transaction id 676. The ooutput reads > LOG: recovery stopping before commit of transaction 676, time 2010-07-09 > 07:49:26.580518+05:30 A recovery stops when the commit time > or >= recovery_target_time. So, unless it moves up to the newer commit than recovery_target_time, it cannot stop. > Is there a way to find out the transaction ids and corresponding SQLs, > timeline etc? May be doing the recovery in debug/logging mode or something > like that? xlogviewer reads WAL files and displays the contents of them. But it's been inactive for several years, so I'm not sure if it's available now. http://pgfoundry.org/projects/xlogviewer/ Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG_DUMP very slow because of STDOUT ??
Hi Tom (or others), are there some recommended settings/ways to use oprofile on a situation like this??? I got it working, have seen a first profile report, but then managed to completely freeze the server on a second try with different oprofile settings (next tests will go against the newly installed - next and identical - new servers). Andras Fabian -Ursprüngliche Nachricht- Von: Tom Lane [mailto:t...@sss.pgh.pa.us] Gesendet: Freitag, 9. Juli 2010 15:39 An: Andras Fabian Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] PG_DUMP very slow because of STDOUT ?? Andras Fabian writes: > Now I ask, whats going on here Why is COPY via STDOUT so much slower on > out new machine? Something weird about the network stack on the new machine, maybe. Have you compared the transfer speeds for Unix-socket and TCP connections? On a Red Hat box I would try using oprofile to see where the bottleneck is ... don't know if that's available for Ubuntu. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queries about PostgreSQL PITR
Hi, >Because you didn't disable recovery_target_inclusive, I guess. > http://www.postgresql.org/docs/8.4/static/continuous-archiving.html#RECOVERY-TARGET-INCLUSIVE Thanks. I was almost sure this will fix it. But the issue seems to be something else. Even if I give a time that is a few more minutes before what I got from select now(), it is always moving upto/or just before (depending on the above parameter) transaction id 676. The ooutput reads LOG: recovery stopping before commit of transaction 676, time 2010-07-09 07:49:26.580518+05:30 Is there a way to find out the transaction ids and corresponding SQLs, timeline etc? May be doing the recovery in debug/logging mode or something like that? Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
On Mon, Jul 12, 2010 at 7:06 AM, Craig Ringer wrote: > It seems like a profiler, which is designed to filter and organize the > collected data, and which can be attached only to specific functions that > you want to know about, might be a better job. As there's already a PL/PgSQL > debugger, some of the infrastructure required is already present. There's already a profiler in the same source tree. It just needs to be given a little love. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple functions, huge overhead, no cache
2010/7/12 Craig Ringer : > On 11/07/2010 5:46 AM, Pavel Stehule wrote: > >> any using a non simple expression is very slow - so there can be some >> a warning when people use it. Sometimes people don't know (me too), >> when use expensive expression >> >> for example >> >> rowvar := (10,20) >> >> it isn't simple - I am not sure, if it is true still. > > Rather than warning whenever the SPI is invoked from PL/PgSQL, perhaps this > would be a task better suited for inclusion in a profiler feature for the > PL/PgSQL debugger? > > I'm not particularly interested in the notion myself, but I don't think > warnings about "non-simple" statements would be very helpful. You'd be > drowned in warnings for statements that were a necessary part of the > operation of your functions, things for which there was no other way to do > it. I think, so it is warnings has more education sense. Because it is interactive. It simply show - "don't do it". More - it can be done in validation time - so it hasn't any impact on real speed (I know it is mas/menos). There are a few basic a plpgsql repeated mistakes - and I think so some of these can be catched via warnings. see http://okbob.blogspot.com/2010/04/frequent-mistakes-in-plpgsql-design.html I am thinking so plpgsql has a full functionality - there are not too much issues - so next direction of developing can be a smarted validation and more verbose diagnostics (now diagnostics (syntax error, runtime error) are pretty well). There a issue with assign statement with quite bin-text/text-bin transformation - declare a int; begin a := 4; because "4" is a numeric and "a" is integer, then in runtime time it does numeric/string and string to integer transformations. And it is relative innocent statement. Some like this are traps on beginners. > > It seems like a profiler, which is designed to filter and organize the > collected data, and which can be attached only to specific functions that > you want to know about, might be a better job. As there's already a PL/PgSQL > debugger, some of the infrastructure required is already present. there is a pl/pgsql profiler too. but profiler isn't "interactive". More - profiler/debugger are third-party software - so it isn't availlable everywhere. > > Meh, personally I'll stick to the good old profiling methods "is it fast > enough", "\timing", and "explain analyze". Both tools has a sense - we have a warnings in gcc and we have a profilers too. Regards Pavel Stehule > > -- > Craig Ringer > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
