Re: [GENERAL] something to suggest indexes
John wrote: Hi, Is there something built-in to Postgres that would suggest what indexes I might add to improve performance? I created my required tables (they only contain small amounts of test data) and the performance is great. But as the data starts growing I'm betting that creating a few indexes will be needed. In the past I just started playing with explain using a hit and miss way of doing it. You'll want EXPLAIN once you know which queries you really care about but before that you'll need to identify them. Two things might prove useful: http://www.postgresql.org/docs/8.4/static/monitoring-stats.html The statistics views will let you see which tables and indexes are being used the most. You don't want unnecessary indexes either. Take a copy of the table, leave it 24 hours (or whatever testing time is suitable) and take another copy. Compare the two. You can also turn on query-time logging and use a log analyser to see precisely how much time you spend with each query. Then, you know which to target with EXPLAIN. A couple of log-analyser packages are: http://pgfouine.projects.postgresql.org/ http://pqa.projects.postgresql.org/ -- Richard Huxton Archonet 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] Idle in transaction
Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 From: Sharma, Sid Sent: Friday, July 17, 2009 10:05 AM To: pgsql-general@postgresql.org Subject: Idle in transaction Hi I'm a postgres newbie. I just implemented a new web application using postgres. When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. The application itself remains functional and responsive. It has been up for over 36 hours now without any issues. What is the significance of this state? Does this imply a transaction leak? Then why am I not noticing deadlocks, timeouts etc. Thanks Sid
[GENERAL] Idle in transaction
Hi I'm a postgres newbie. I just implemented a new web application using postgres. When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. The application itself remains functional and responsive. It has been up for over 36 hours now without any issues. What is the significance of this state? Does this imply a transaction leak? Then why am I not noticing deadlocks, timeouts etc. Thanks Sid
[GENERAL] memory leak occur when disconnect database
I'm running postgres 8.1.8 on Debian and I think memory leak occur when
disconnect database.
1. environment setting
1.1 postgresql version:
version
---
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
1.2 kernel version:
Linux PMS 2.6.18-4-686 #2 SMP Fri Aug 1 22:47:38 JST 2008 i686 GNU/Linux
2. test example:
int OpenDataBase(void)
{
int i_ret = 0;
EXEC SQL CONNECT TO test_db;
if(sqlca.sqlcode == 0){
i_ret = 1;
}
return i_ret;
}
sint8 CloseDataBase(void)
{
sint8 i_ret = !0;
EXEC SQL disconnect all;
if(sqlca.sqlcode == 0){
i_ret = 0;
}
return i_ret;
}
int main()
{
OpenDataBase();
CloseDataBase();
}
when I use valgrind to check memory information, the memory leak is in view.
would anyone give me a suggestion. I'm looking forward your help.
best wishes.
--
Winsea.
--
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] Concurrency issue under very heay loads
On Wed, 2009-07-15 at 22:34 -0700, John R Pierce wrote: sounds like you should be using a SERIAL (which is implemented as an INTEGER or BIGINT field with an associated SEQUENCE), as these DO work just fine under heavy concurrency without any gotchas. There is one gotcha, though we're all so used to it (and/or never would've thought to care about it) as to forget it: With a SEQUENCE, as produced by the SERIAL pseudo-type, values may be skipped if a transaction rolls back. That includes automatic rollback on error or disconnect, not just explicit ROLLBACK of course. If you're using sequences to generate synthetic keys that's exactly what you want; you don't care about gaps and you want it fast and concurrency-friendly. If your application can't cope with gaps in the sequence then either (a) fix it so it can, or (b) search this mailing list for gapless sequence implementations and use one of them. Beware the nasty performance implications. -- 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
Re: [GENERAL] something to suggest indexes
On Friday 17 July 2009 12:29:59 am Richard Huxton wrote: John wrote: Hi, Is there something built-in to Postgres that would suggest what indexes I might add to improve performance? I created my required tables (they only contain small amounts of test data) and the performance is great. But as the data starts growing I'm betting that creating a few indexes will be needed. In the past I just started playing with explain using a hit and miss way of doing it. You'll want EXPLAIN once you know which queries you really care about but before that you'll need to identify them. Two things might prove useful: http://www.postgresql.org/docs/8.4/static/monitoring-stats.html The statistics views will let you see which tables and indexes are being used the most. You don't want unnecessary indexes either. Take a copy of the table, leave it 24 hours (or whatever testing time is suitable) and take another copy. Compare the two. You can also turn on query-time logging and use a log analyser to see precisely how much time you spend with each query. Then, you know which to target with EXPLAIN. A couple of log-analyser packages are: http://pgfouine.projects.postgresql.org/ http://pqa.projects.postgresql.org/ -- Richard Huxton Archonet Ltd Yes that's what I was looking for. Thanks for taking the time. Johnf -- 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] [PERFORM] Concurrency issue under very heay loads
On Thu, 2009-07-16 at 00:11 -0600, Scott Marlowe wrote: As others have said, a serial is a good idea, HOWEVER, if you can't have gaps in sequences, or each customer needs their own sequence, then you get to lock the rows / table / etc that you're mucking with to make sure you don't issue the same id number twice. These days can't you just UPDATE ... RETURNING the sequence source table? Or is there some concurrency issue there I'm not seeing? Other than the awful impact on concurrent insert performance of course, but you're stuck with that using any gapless sequence. -- 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
Re: [GENERAL] Idle in transaction
In response to Sharma, Sid ssha...@bjs.com: I'm a postgres newbie. I just implemented a new web application using postgres. You mention that you're using PG 8.1.3, which is very old. You'll save yourself a lot of headaches if you at least upgrade to the latest 8.1. But that is not part of your issue, it's just a side note. When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. That's bad. It means your client program is starting a transaction and leaving it running without doing anything with it. This is an issue with the way the client is programmed, or with the client drivers, not with the server. The server is doing what it's told. The reason this is bad is that PG can't properly complete maintenance if there are transactions that are left open constantly. Eventually your DB will fill up the entire disk with old data that can't be cleaned up. The application itself remains functional and responsive. It has been up for over 36 hours now without any issues. What is the significance of this state? Does this imply a transaction leak? Then why am I not noticing deadlocks, timeouts etc. Check the design of your app. If it issues a BEGIN, then sits there, you need to configure it to only issue a BEGIN when it's actually ready to do some work, and issue a COMMIT when the work is complete. Simply leaving a connection open won't cause this. If you're not explicitly issuing a BEGIN, then it may be a bug in the client driver, or a misunderstanding on your part as to how to use the driver. If you tell the list what client library you're using, I'm sure there are folks who can offer more detailed insight. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Idle in transaction
On Friday 17 July 2009 07:17:15 am Sharma, Sid wrote: Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 From: Sharma, Sid Sent: Friday, July 17, 2009 10:05 AM To: pgsql-general@postgresql.org Subject: Idle in transaction Hi I'm a postgres newbie. I just implemented a new web application using postgres. When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. The application itself remains functional and responsive. It has been up for over 36 hours now without any issues. What is the significance of this state? Does this imply a transaction leak? Then why am I not noticing deadlocks, timeouts etc. Thanks Sid In my case it was caused by a select statement that I neither issued a commit or rollback. So you have started a transaction without closing it somewhere in your app. My app continued to work without apparent issue. The only thing I noticed was pgAdmin3 could not make changes to the data structure without closing my app. So somewhere in your code you have started a transaction without closing it. Johnf -- 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] Asking for assistance in determining storage requirements
On Thu, 2009-07-09 at 11:15 -0400, Chris Barnes wrote: We would like to get as much performance from our file systems as possible. Then avoid RAID 5. Raid 10 is a pretty good option for most loads. Actually, RAID 5 is quite decent for read-mostly large volume storage where you really need to be disk-space efficient. However, if you spread the RAID 5 out over enough disks for it to start getting fast reads, you face a high risk of disk failure during RAID rebuild. For that reason, consider using RAID 6 instead - over a large set of disks - so you're better protected against disk failures during rebuild. If you're doing much INSERTing / UPDATEing then RAID 5/6 are not for you. RAID 10 is pretty much the default choice for write-heavy loads. The postgres database is on 5 drives configured as raid 5 with a global hot spare. We are curious about using SAN with fiber channel hba and if anyone else uses this technology. There are certainly people on the list using PostgreSQL on a FC SAN. It comes up in passing quite a bit. It's really, REALLY important to make sure your SAN honours fsync() though - at least to the point making sure the SAN hardware has the data in battery-backed cache before returning from the fsync() call. Otherwise you risk serious data loss. I'd be unpleasantly surprised if any SAN shipped with SAN or FC HBA configuration that disregarded fsync() but it _would_ make benchmark numbers look better, so it's not safe to assume without testing. From general impressions gathered from the list ( I don't use such large scale gear myself and can't speak personally ) it does seem like most systems built for serious performance use direct-attached SAS arrays. People also seem to separate out read-mostly/archival tables, update-heavy tables, the WAL, temp table space, and disk sort space into different RAID sets. -- 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
Re: [GENERAL] Idle in transaction
Sharma, Sid wrote: Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Why on earth are you using that version? At the very least upgrade to 8.1.17 and if this is a new app, why not use 8.4? When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. You don't say how you are connecting to the DB, but it's probably your connection manager. Are you running some sort of connection pooling perhaps? -- Richard Huxton Archonet 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] Idle in transaction
On Fri, Jul 17, 2009 at 8:05 AM, Sharma, Sidssha...@bjs.com wrote: Hi I’m a postgres newbie. I just implemented a new web application using postgres. When I look at the db connections (via ps), I notice that all existing connections are in ‘Idle in Transaction’ state. They never go to idle state. Then it's likely a bug in your application / connection / pooling software. I'm gonna guess you're using Java and jdbc. But it could be something else. The application itself remains functional and responsive. It has been up for over 36 hours now without any issues. Many ships stay afloat for hours after suffering fatal collisions with icebergs. The likely problem here will be that your tables will slowly bloat with dead tuples because vacuum can't reclaim space. Eventually either your db will slow to a crawl or you'll run out of disk space. What is the significance of this state? Does this imply a transaction leak? No, it implies broken application / connection / pooling code. Then why am I not noticing deadlocks, timeouts etc. Because that's not what happens when connections are left idle in transaction. -- 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] Idle in transaction
On Fri, Jul 17, 2009 at 8:17 AM, Sharma, Sidssha...@bjs.com wrote: Sorry. Forgot to mention the postgres version PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 Oh, and update to the latest 8.1.x version. There are some nasty bugs in 8.1.3 if I recall. Won't fix this issue, but why run old un-updated pg code? -- 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] change database
Since there is no other way, I'll close it then reopen it again. Thank you for your time. Regards, Florian Chis 2009/7/15 Bill Moran wmo...@potentialtech.com In response to Florian Chis florian.c...@gmail.com: I'm working on a port from mysql to postgres. I have a function which connect's to the database, checks for database existance and creates it otherwise. The last thing it does it executes use my_database (mysql stuff). Now I'm trying to find something similar in postgres. I know that psql has \c but that dosen't help me with jdbc. I also know that the easy solution out of this is to close the connection and then open it again with /my_database in the URL but I want to avoid this. Close the connection and reopen it. There's no equivalent. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/http://people.collaborativefusion.com/%7Ewmoran/
Re: [GENERAL] Asking for assistance in determining storage requirements
On Thu, Jul 9, 2009 at 9:15 AM, Chris Barnescompuguruchrisbar...@hotmail.com wrote: You assistance is appreciated. I have question regarding disk storage for postgres servers We are thinking long term about scalable storage and performance and would like some advise or feedback about what other people are using. We would like to get as much performance from our file systems as possible. We use ibm 3650 quad processor with onboard SAS controller ( 3GB/Sec) with 15,000rpm drives. We use raid 1 for the centos operating system and the wal archive logs. The postgres database is on 5 drives configured as raid 5 with a global hot spare. OK, two things jump out at me. One is that you aren't using a hardware RAID controller with battery backed cache, and you're using RAID-5. For most non-db applications, RAID-5 and no battery backed cache is just fine. For some DB applications like a reporting db or batch processing it's ok too. For DB applications that handle lots of small transactions, it's a really bad choice. Looking through the pgsql-performance archives, you'll see RAID-10 and HW RAID with battery backed cache mentioned over and over again, and for good reasons. RAID-10 is much more resilient, and a good HW RAID controller with battery backed cache can re-order writes into groups that are near each other on the same drive pair to make overall throughput higher, as well as making burst throughput to be higher as well by fsyncing immediately when you issue a write. I'm assuming you have 8 hard drives to play with. If that's the case, you can have a RAID-1 for the OS etc and a RAID-10 with 4 disks and two hot spares, OR a RAID-10 with 6 disks and no hot spares. As long as you pay close attention to your server and catch failed drives and replace them by hand that might work, but it really sits wrong with me. We are curious about using SAN with fiber channel hba and if anyone else uses this technology. Yep, again, check the pgsql-perform archives. Note that the level of complexity is much higher, as is the cost, and if you're talking about a dozen or two dozen drives, you're often much better off just having a good direct attached set of disks, either with an embedded RAID controller, or JBOD and using an internal RAID controller to handle them. The top of the line RAID controllers that can handle 24 or so disks run $1200 to $1500. Taking the cost of the drives out of the equation, I'm pretty sure any FC/SAN setup is gonna cost a LOT more than that single RAID card. I can buy a 16 drive 32TB DAS box for about $6k to $7k or so, plug it into a simple but fast SCSI controller ($400 tops) and be up in a few minutes. Setting up a new SAN is never that fast, easy, or cheap. OTOH, if you've got a dozen servers that need lots and lots of storage, a SAN will start making more sense since it makes managing lots of hard drives easier. We would also like to know if people have preference to the level of raid with/out striping. RAID-10, then RAID-10 again, then RAID-1. RAID-6 for really big reporting dbs where storage is more important than performance, and the data is mostly read anyways. RAID-5 is to be avoided, period. If you have 6 disks in a RAID-6 with no spare, you're better off than a RAID-5 with 5 disks and a spare, as in RAID-6 the spare is kind of already built in. -- 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] psql \du [PATCH] extended \du with [+] - was missing
Peter Eisentraut schrieb: On Thursday 16 July 2009 02:12:05 ANdreas Wenk wrote: Hi, I recognized in psql using the internal help (\?) that the *+* sign is missing for the shortcut \du: # \du List of roles Role name | Attributes | Member of --+--+--- # \du+ List of roles Role name | Attributes | Member of | Description --+--+---+- Where shall I place this info? Is this also a bug? Looks like the help is missing this info. If you could provide a patch that also fixes up the translations, that would be most helpful. Hi, here is the patch for help.c . I think updating some translations is not neccessary because there is no change. Am I right? --- src/bin/psql/help.c |2 +- 1 files changed, 1 insertions(+), 1 deletions(-) diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 5f13b8a..8a541e6 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -219,7 +219,7 @@ slashUsage(unsigned short int pager) fprintf(output, _( \\ds[S+] [PATTERN] list sequences\n)); fprintf(output, _( \\dt[S+] [PATTERN] list tables\n)); fprintf(output, _( \\dT[S+] [PATTERN] list data types\n)); - fprintf(output, _( \\du [PATTERN] list roles (users)\n)); + fprintf(output, _( \\du[+] [PATTERN] list roles (users)\n)); fprintf(output, _( \\dv[S+] [PATTERN] list views\n)); fprintf(output, _( \\l[+] list all databases\n)); fprintf(output, _( \\z [PATTERN] same as \\dp\n)); -- 1.6.0.4 0001-extended-du-with-was-missing.patch (END) Cheers 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] Idle in transaction
Thx for all your response @Bill Moran You mention that you're using PG 8.1.3, which is very old. You'll save yourself a lot of headaches if you at least upgrade to the latest 8.1. But that is not part of your issue, it's just a side note. I'm sure it is outdated. When I suggested a new application I should have said a new module within an existing application. In other words, the db pre-exists. We are rewriting our entire application(s) and moving our database to DB2 and Oracle starting pretty soon. So postgres is end of life here and so there is no stomach or interest to upgrade. That's bad. It means your client program is starting a transaction and leaving it running without doing anything with it. This is an issue with the way the client is programmed, or with the client drivers, not with the server. The server is doing what it's told. The reason this is bad is that PG can't properly complete maintenance if there are transactions that are left open constantly. Eventually your DB will fill up the entire disk with old data that can't be cleaned up. The module/application is read-only and so only does SELECTs. There are very infrequent writes to the database. In fact writes have not happened yet since our launch. I can monitor disk utilization to see if it's growing but since we only do queries, I would be surprised that were to be true. But then again, I do not know postgres. If you're not explicitly issuing a BEGIN, then it may be a bug in the client driver, or a misunderstanding on your part as to how to use the driver. If you tell the list what client library you're using, I'm sure there are folks who can offer more detailed insight. It's a jdbc driver. The jar file's manifest does not contain the version. I noticed the timestamp of all the files in the jar are in 2002. How do I find out? @Scott Marlowe Then why am I not noticing deadlocks, timeouts etc. Because that's not what happens when connections are left idle in transaction. Then what should I look for? This is a query-only module. It is not adding any data to the database. The data in the tables remains static. I have been watching the number of connections and that has remained static i.e. at the min level of the connection pool. @Richard Huxton You don't say how you are connecting to the DB, but it's probably your connection manager. Are you running some sort of connection pooling perhaps? Yes a jdbc pool. There is another web application that uses the same implementation of the pool (albeit a different instance of it) and connections within it are in idle state as you would expect. So the connection pool implementation itself appears to have an alibi. @Johnf In my case it was caused by a select statement that I neither issued a commit or rollback. So you have started a transaction without closing it somewhere in your app. My app continued to work without apparent issue. The only thing I noticed was pgAdmin3 could not make changes to the data structure without closing my app. So somewhere in your code you have started a transaction without closing it. So there is no imminent danger then of catastrophic failure such as memory full or disk full or cpu spikes or db slowness given my application only does queries? I'm trying to figure out whether I should chill this weekend on the beach or kiss that good-bye and work this issue? :-) I am setting auto-commit to on, so shouldn't a commit be issued once the select executes (or a rollback on a sql exception)? I guess I'm not 100% sure of txn semantics with the postgres driver that I have. Do you think that despite setting the auto commit mode to off, I still need to issue explicit commits or rollbacks? -Original Message- From: Bill Moran [mailto:wmo...@potentialtech.com] Sent: Friday, July 17, 2009 10:44 AM To: Sharma, Sid Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction In response to Sharma, Sid ssha...@bjs.com: I'm a postgres newbie. I just implemented a new web application using postgres. You mention that you're using PG 8.1.3, which is very old. You'll save yourself a lot of headaches if you at least upgrade to the latest 8.1. But that is not part of your issue, it's just a side note. When I look at the db connections (via ps), I notice that all existing connections are in 'Idle in Transaction' state. They never go to idle state. That's bad. It means your client program is starting a transaction and leaving it running without doing anything with it. This is an issue with the way the client is programmed, or with the client drivers, not with the server. The server is doing what it's told. The reason this is bad is that PG can't properly complete maintenance if there are transactions that are left open constantly. Eventually your DB will fill up the entire disk with old data that can't be cleaned up. The application itself remains functional and responsive. It has been up
Re: [GENERAL] Concurrency issue under very heay loads
Thanks for everyone's inputs and here is an update on the issue: The problem source is that autocommit is not getting unset. The code does the following ( and source code or copyright does not belong to Cisco): . unsets autocommit . starts transaction . SQL for select for update . SQL for update next sequence number . Commits transaction The problem is in unsetting auto commit. Since this runs inside an Jboss app server/EJB environment, this becomes a no-op and hence the ACIDity across the select for update and update. We are using postgres 8.2.12 on Windows with JDBC driver 8.2-506. Thanks Raji -Original Message- From: Greg Smith [mailto:gsm...@gregsmith.com] Sent: Thursday, July 16, 2009 2:03 PM To: Raji Sridar (raji) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Concurrency issue under very heay loads On Wed, 15 Jul 2009, Raji Sridar (raji) wrote: When multiple clients are concurrently accessing this table and updating it, under extermely heavy loads in the system (stress testing), we find that the same order number is being generated for multiple clients. The only clean way to generate sequence numbers without needing to worry about duplicates is using nextval: http://www.postgresql.org/docs/current/static/functions-sequence.html If you're trying to duplicate that logic in your own code, there's probably a subtle race condition in your implementation that is causing the bug. If you had two calls to nextval from different clients get the same value returned, that might be a PostgreSQL bug. Given how much that code gets tested, the more likely case is that there's something to tweak in your application instead. I would advise starting with the presumption it's an issue in your app rather than on the server side of things. P.S. Posting the same question to two lists here is frowned upon; pgsql-general is the right one for a question like this. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Working around spurious unique constraint errors due to SERIALIZABLE bug
On Thu, 2009-07-16 at 14:13 +, Florian Weimer wrote: The drawback is that some of the side effects of the INSERT occur before the constraint check fails, so it seems to me that I still need to perform the select. I was about to foolishly suggest: Instead of: SELECT 1 FROM x WHERE a = 4; IF NOT FOUND THEN INSERT INTO x (a,b) VALUES (4,10); END IF; trying: INSERT INTO x (a, b) SELECT 4, 10 WHERE NOT EXISTS(SELECT 1 FROM x WHERE a = 4); ... but then realised I couldn't come up with any justification for how it'd help (after all, the WHERE clause still has to be evaluated before the INSERT can proceed, there's still no predicate locking, and the statements can be evaluated concurrently) so I thought I'd test it. The test program, attached, demonstrates what I should've known in the first place. In SERIALIZABLE isolation, the above is *guaranteed* to fail every time there's conflict, because concurrent transactions cannot see changes committed by the others. So is a SELECT test then separate INSERT, by the way. In READ COMITTED you get away with it a lot of the time because the statement can see other transaction(s)' committed changes so the subquery often matches - but it's a race, and eventually you'll hit a situation where the subquery for two concurrent transactions is evaluated before either's insert is issued or at least is committed. In my test program I've managed as many as 1283 steps before two racing READ COMMITTED transactions collide. That's in a program designed to synchronize each transaction before each insert for maximum collision potential. With six racing transactions I've rarely seen more than three steps without a collision. ( I've attached the test program in case it's of any interest. It's a Python test controller that spawns slave processes which it synchronises using Pg's advisory locking. It ensures that the slaves all start each INSERT attempt together, and all finish before starting the next attempt. Signals are used for failure notification, cleanup, etc. ) Anyway, the point is that you're actually worse off in this particular situation thanks to your use of SERIALIZABLE isolation. However, READ COMMITTED just gives you a race you're likely to win most of the time instead of a guaranteed failure whenever there's a race, so it's not really a solution. Given that, it seems to me you'll have to rely on Pg's internal lower-level synchonization around unique indexes. Try the insert and see if it fails, then ROLLBACK TO SAVEPOINT (or use a PL/PgSQL exception block). As you noted, this does mean that certain side-effects may occur, including: - advancement of sequences due to nextval(...) calls - triggers that've done work that can't be rolled back, eg dblink calls, external file writes, inter-process communication etc If you really can't afford the INSERT side effects and can't redesign your code to be tolerant of them, you can always lock the table before an INSERT. If you can't afford to lock the table due to its impact on performance, you can potentially use Pg's advisory locking mechanism to protect your inserts. Eg (PL/PgSQL): PERFORM pg_advisory_lock(4); SELECT 1 FROM x WHERE a = 4; IF NOT FOUND THEN INSERT INTO x (a,b) VALUES (4,10); END IF; PERFORM pg_advisory_unlock(4); (You might want to use the two-argument form of the advisory locking calls if your IDs are INTEGER size not INT8, and use the table oid for the first argument.) If every possible INSERTer ensures it holds the lock on the id of interest before inserting, you'll be fine. Yes, it's ugly, but it preserves concurrent insert performance while eliminating failed INSERTs. A possible way to ensure that every possible INSERTer does do the right thing is to drop the INSERT privilege on the table and then use a SECURITY DEFINER function that checks the caller's rights and does the INSERT. Also: Is this really a phantom read? Your issue is not that you read a record that then vanishes or no longer matches your filter criteria; rather, it's that a record is created that matches your criteria after you tested for it. Certainly that wouldn't be possible if the concurrent transactions were actually executed serially, but does the standard actually require that this be the case? If it does, then compliant implementations would have to do predicate locking. Ouch. Does anybody do that? It seems MS-SQL implements very limited predicate locking (enough to handle your issue) but not enough to tackle aggregates or anything complex. -- Craig Ringer #!/usr/bin/env python import psycopg2 import sys import time import os from psycopg2.extensions import ( ISOLATION_LEVEL_READ_COMMITTED, ISOLATION_LEVEL_SERIALIZABLE, STATUS_BEGIN, STATUS_READY) import signal import subprocess import random CONFIGURATION ## debug = False serializable = False # conn = psycopg2.connect() if serializable:
Re: [GENERAL] memory leak occur when disconnect database
8.1.8 is pretty old.
Also you'll have better luck getting help if you actually include the output
from Valgrind.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of tanjunhua
Sent: Friday, July 17, 2009 8:12 AM
To: Postgres General Postgres General
Subject: [GENERAL] memory leak occur when disconnect database
I'm running postgres 8.1.8 on Debian and I think memory leak occur when
disconnect database.
1. environment setting
1.1 postgresql version:
version
---
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
1.2 kernel version:
Linux PMS 2.6.18-4-686 #2 SMP Fri Aug 1 22:47:38 JST 2008 i686 GNU/Linux
2. test example:
int OpenDataBase(void)
{
int i_ret = 0;
EXEC SQL CONNECT TO test_db;
if(sqlca.sqlcode == 0){
i_ret = 1;
}
return i_ret;
}
sint8 CloseDataBase(void)
{
sint8 i_ret = !0;
EXEC SQL disconnect all;
if(sqlca.sqlcode == 0){
i_ret = 0;
}
return i_ret;
}
int main()
{
OpenDataBase();
CloseDataBase();
}
when I use valgrind to check memory information, the memory leak is in view.
would anyone give me a suggestion. I'm looking forward your help.
best wishes.
--
Winsea.
--
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] Please help
On Thu, 2009-07-16 at 04:47 -0700, Roseller A. Romanos wrote: Please help me with this. I really need your advice as to how to retrieve the data in my postgresql database. I have postgresql installed in Windows XP platform five months ago. Just yesterday my OS bugged down and saying NTDLR is missing. What I did was I re-installed my OS. When I finished my installation I found out that I cannot access anymore my postgresql using PGAdmin III. What should I do to access my database and retrieve the important records in it? Back up your data directory NOW. This is the directory containing the PG_VERSION file, the pg_xlog directory, etc. You must back up the WHOLE directory EXACTLY as it is. Unfortunately I have the feeling the Windows installer wants to initdb even if there's already data in the data directory (someone please correct me if that's not so), so you might have to: - rename your data directory before installing PostgreSQL. - Install PostgreSQL. Make sure you install the same major version, eg install 8.3.7 if you had some 8.3.x version installed originally. If you are unsure, open the PG_VERSION file in a text editor to see what it says. (DO NOT save changes). - Stop the PostgreSQL service. Use Start - Run - services.msc, find the PostgreSQL service, and stop it. - Rename the data directory the installer created to something else. - Rename your old data directory to the one the installer created. - Make sure the security settings on the old data directory will allow PostgreSQL to read and write to it. Setting the ownership to be the same as that of the new data directory should do the trick. Use the security tab of the properties window on the folder to do this. - Start the PostgreSQL service. With luck you can now connect. If so, IMMEDIATELY back up your database, and start doing so regularly... -- 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
[GENERAL] COPY command on windows???
Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file d:\relations.csv for reading file or directory not found The PG doc doesn't describe the path-syntax for windows. With google I only find references to permission errors. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] initdb failure on Windows XP
I am using PostgreSQL 8.3 on Windows XP SP2. I am getting this strange error while initdb: Part of the error message i get, The database cluster will be initialized with locale English_United States.1252. The default database encoding has accordingly been set to WIN1252. The default text search configuration will be set to english. fixing permissions on existing directory C:/postgres/data ... ok creating subdirectories ... initdb: could not create directory C:/postgres: File exists initdb: removing contents of data directory C:/postgres/data Looking into the initdb code, it should create sub directories (global, pg_xlog, pg_clog, base etc ) under directory C:/postgres/data. I cannot understand, why it is trying to create C:/postgres. Any thoughts? -- Regards, Sachin Srivastava www.enterprisedb.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] COPY command on windows???
Andreas wrote on 17.07.2009 20:06: Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file d:\relations.csv for reading file or directory not found Try COPY relations FROM 'd:/relations.csv' CSV HEADER; -- 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] initdb failure on Windows XP
On Fri, 2009-07-17 at 23:42 +0530, Sachin Srivastava wrote: I am using PostgreSQL 8.3 on Windows XP SP2. I am getting this strange error while initdb: Part of the error message i get, The database cluster will be initialized with locale English_United States.1252. The default database encoding has accordingly been set to WIN1252. The default text search configuration will be set to english. fixing permissions on existing directory C:/postgres/data ... ok creating subdirectories ... initdb: could not create directory C:/postgres: File exists initdb: removing contents of data directory C:/postgres/data Looking into the initdb code, it should create sub directories (global, pg_xlog, pg_clog, base etc ) under directory C:/postgres/data. I cannot understand, why it is trying to create C:/postgres. Any thoughts? Well the error I see is that C:/postgres already exists. Meaning you had a postgres file or directory already. Initdb will bail out if that is the case. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Idle in transaction
On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote: I'm sure it is outdated. When I suggested a new application I should have said a new module within an existing application. In other words, the db pre-exists. We are rewriting our entire application(s) and moving our database to DB2 and Oracle starting pretty soon. So postgres is end of life here and so there is no stomach or interest to upgrade. But you're developing new code targeted to Pg? That's bad. It means your client program is starting a transaction and leaving it running without doing anything with it. This is an issue with the way the client is programmed, or with the client drivers, not with the server. The server is doing what it's told. The reason this is bad is that PG can't properly complete maintenance if there are transactions that are left open constantly. Eventually your DB will fill up the entire disk with old data that can't be cleaned up. The module/application is read-only and so only does SELECTs. That doesn't matter if any _other_ module is performing UPDATEs (or inserts and deletes) to the tables your module operates on. Your module's open transactions may still prevent PostgreSQL from reclaiming space, because it needs to keep the old versions of tuples around. (I'm struggling, right now, to explain why this is so - if this is so - for READ COMMITTED transactions, though it certainly is for SERIALIZABLE transactions. For READ COMMITTED the transaction's own uncommitted changes can't be discarded vacuumed, but these are presumably read-only transactions anyway - and they don't need to be able to see any old tuples other than any they may have inserted/modified. Will open READ COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER, sure, but plain VACUUM?) Your uncommitted transactions will also make it very hard to make schema changes such as adding or altering columns to your tables. If your module is the only one active on these tables, and it's only doing read activity, then it doesn't really matter very much that the transactions are left open. It's kind of ugly, though. So there is no imminent danger then of catastrophic failure such as memory full or disk full or cpu spikes or db slowness given my application only does queries? I'm trying to figure out whether I should chill this weekend on the beach or kiss that good-bye and work this issue? :-) I am setting auto-commit to on, so shouldn't a commit be issued once the select executes (or a rollback on a sql exception)? Yes, it should. Are you certain your app is using autocommit across the board, though, and that something else (say, your connection pooler) isn't helping you by turning it back off again? If you turn autocommit back off and issue a rollback before handing the connection back to the connection pooler, does the issue go away? Personally I prefer to explicitly manage transactions anyway. Most Java code/libraries seem to expect autocommit to be off, it makes it easier to guarantee that read-only code is exactly that (by issuing a rollback when finished), and for read/write queries I like to know for certain when data has been committed to the database. -- 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
Re: [GENERAL] [PERFORM] Concurrency issue under very heay loads
-Original Message-
From: pgsql-performance-ow...@postgresql.org
We use a typical counter within a transaction to generate
order sequence number and update the next sequence number.
This is a simple next counter - nothing fancy about it. When
multiple clients are concurrently accessing this table and
updating it, under extermely heavy loads in the system (stress
testing), we find that the same order number is being
generated for multiple clients. Could this be a bug? Is there
a workaround? Please let me know.
Are you using for update in your select statements? Are you setting
an appropriate transaction isolation level?
A better way to do this is with a sequence instead. This is guaranteed
to give you a unique value:
select nextval('address_serial_num_seq');
eric
--
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] memory leak occur when disconnect database
Your test case doesn't build, but I've attached a trivially tweaked one
that does.
Valgrind's report (valgrind --leak-check=full ./test) on my Ubuntu 9.04
machine with Pg 8.3.7 is:
==23382== 156 (36 direct, 120 indirect) bytes in 1 blocks are definitely
lost in loss record 1 of 4
==23382==at 0x4026FDE: malloc (vg_replace_malloc.c:207)
==23382==by 0x4211548: nss_parse_service_list (nsswitch.c:547)
==23382==by 0x4211E25: __nss_database_lookup (nsswitch.c:134)
==23382==by 0x4B61F5B: ???
==23382==by 0x4B6400C: ???
==23382==by 0x41B7A51: getpwuid_r@@GLIBC_2.1.2 (getXXbyYY_r.c:253)
==23382==by 0x42A87DD: (within /usr/lib/libpq.so.5.1)
==23382==by 0x4292955: (within /usr/lib/libpq.so.5.1)
==23382==by 0x429749E: (within /usr/lib/libpq.so.5.1)
==23382==by 0x4297528: (within /usr/lib/libpq.so.5.1)
==23382==by 0x4297E24: PQsetdbLogin (in /usr/lib/libpq.so.5.1)
==23382==by 0x4053563: ECPGconnect (in /usr/lib/libecpg.so.6.0)
==23382==
==23382== LEAK SUMMARY:
==23382==definitely lost: 36 bytes in 1 blocks.
==23382==indirectly lost: 120 bytes in 10 blocks.
==23382== possibly lost: 0 bytes in 0 blocks.
==23382==still reachable: 220 bytes in 1 blocks.
==23382== suppressed: 0 bytes in 0 blocks.
If you're seeing the same output, then the issue you're running into is
libnss caching NSS services list ( /etc/services, plus LDAP/NIS services
etc) when it's first used. This memory is leaked in the sense that
it's not free()d when the program exits, but that doesn't matter _at_
_all_. When the program exits, the OS cleans up its allocations anyway,
so the free() would only be wasting CPU doing work that's about to be
thrown away and slowing down the program's exit in the process. It'd
also open up all sorts of exciting issues if another atexit hook tried
to use NSS...
This leak should be added to your valgrind suppressions file and
ignored. You can re-run valgrind with:
valgrind --leak-check=full --gen-suppressions=all ./test
to generate a suppressions file, but you'll usually want to edit it to
make it a bit less specific. For example, this suppressions entry should
do the trick:
{
libnss_service_cache
Memcheck:Leak
fun:malloc
fun:nss_parse_service_list
fun:__nss_database_lookup
}
If I re-run valgrind with the suppressions entry (in the file
ecpg_suppressions)
valgrind --leak-check=full --suppressions=ecpg_suppressions ./test
I get no reported leaks.
Valgrind is a great tool, but you must learn how to identify false
positives and tell the difference between a leak that matters (say 1kb
allocated and not freed in a loop that runs once per second) and a leak
that doesn't.
--
Craig Ringer
all: test
test.c: test.pgc
ecpg $
test: test.c
gcc -g3 -o test test.c -I /usr/include/postgresql/ -lecpg
clean:
rm test.c test
int OpenDataBase(void)
{
int i_ret = 0;
EXEC SQL CONNECT TO test_db;
if(sqlca.sqlcode == 0){
i_ret = 1;
}
return i_ret;
}
int CloseDataBase(void)
{
int i_ret = !0;
EXEC SQL disconnect all;
if(sqlca.sqlcode == 0){
i_ret = 0;
}
return i_ret;
}
int main()
{
OpenDataBase();
CloseDataBase();
}
--
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] initdb failure on Windows XP
On 07/18/2009 12:03 AM, Joshua D. Drake wrote: On Fri, 2009-07-17 at 23:42 +0530, Sachin Srivastava wrote: I am using PostgreSQL 8.3 on Windows XP SP2. I am getting this strange error while initdb: Part of the error message i get, The database cluster will be initialized with locale English_United States.1252. The default database encoding has accordingly been set to WIN1252. The default text search configuration will be set to english. fixing permissions on existing directory C:/postgres/data ... ok creating subdirectories ... initdb: could not create directory C:/postgres: File exists initdb: removing contents of data directory C:/postgres/data Looking into the initdb code, it should create sub directories (global, pg_xlog, pg_clog, base etc ) under directory C:/postgres/data. I cannot understand, why it is trying to create C:/postgres. Any thoughts? Well the error I see is that C:/postgres already exists. Meaning you had a postgres file or directory already. Initdb will bail out if that is the case. I have a directory structure as : C:\ -Postgres --bin --lib --share Now i get this error, when i tried to do initdb setting my PGDATA to C:\postgres\data which is an empty directory and i am running it (initdb) as administrator user. Joshua D. Drake -- Regards, Sachin Srivastava www.enterprisedb.com
Re: [GENERAL] initdb failure on Windows XP
I am using PostgreSQL 8.3 on Windows XP SP2. I am getting this strange error while initdb: Part of the error message i get, The database cluster will be initialized with locale English_United States.1252. The default database encoding has accordingly been set to WIN1252. The default text search configuration will be set to english. fixing permissions on existing directory C:/postgres/data ... ok creating subdirectories ... initdb: could not create directory C:/postgres: File exists initdb: removing contents of data directory C:/postgres/data I have a directory structure as : C:\ -Postgres --bin --lib --share Now i get this error, when i tried to do initdb setting my PGDATA to C:\postgres\data which is an empty directory and i am running it (initdb) as administrator user. Could you post the exact steps, settings and output? That makes it a lot easier to determine the exact issue. Also, have you checked the event log? Regards, Serge Fonville -- 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] memory leak occur when disconnect database
Sorry for the reply-to-self, but I thought I'd take ecpg out of the
equation:
#include sys/types.h
#include pwd.h
int main()
{
struct passwd p;
struct passwd * r;
char buf[500];
getpwuid_r(1000, p, buf[0], 500, r);
}
... produces the same leak report.
Since you didn't include information like the actual errors from
valgrind, I can't be sure that the report you are getting is the same as
the one I am getting. It could be that in your older version there _is_
something wrong. Perhaps you could post the output of running
valgrind --leak-check=full ./testprogram
just to be sure?
--
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
Re: [GENERAL] Idle in transaction
That doesn't matter if any _other_ module is performing UPDATEs (or inserts and deletes) to the tables your module operates on. Your module's open transactions may still prevent PostgreSQL from reclaiming space, because it needs to keep the old versions of tuples around. There are no updates to the underlying tables. The tables are updated as part of a release only. They contain static data only. The data is used for calculations only and is thus read-only. (I'm struggling, right now, to explain why this is so - if this is so - for READ COMMITTED transactions, though it certainly is for SERIALIZABLE transactions. For READ COMMITTED the transaction's own uncommitted changes can't be discarded vacuumed, but these are presumably read-only transactions anyway - and they don't need to be able to see any old tuples other than any they may have inserted/modified. Will open READ COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER, sure, but plain VACUUM?) I had no idea what vacuum was till I read your post and googled. Yup I'm a true blue newbie. I guess I still know very little. But if vacuum is a compaction and stats gathering utility and is table specific, then I guess I'm not worried even if it cannot run on my tables because the data itself is static. Your uncommitted transactions will also make it very hard to make schema changes such as adding or altering columns to your tables. We never do this. I'm not worried about this. If your module is the only one active on these tables, and it's only doing read activity, then it doesn't really matter very much that the transactions are left open. It's kind of ugly, though. I do intend to diagnose and fix the issue. I was trying to ascertain the urgency of the issue to figure out whether to forfeit a spectacular summer weekend. It sounds like that a catastrophe is not imminent and can wait for module version 1.1 in about a month. Cape Cod here I come. Yes, it should. Are you certain your app is using autocommit across the board, though, and that something else (say, your connection pooler) isn't helping you by turning it back off again? I re-checked the code and auto commit mode is not being turned on. So my bad there! I know what to do to fix the issue. Thanks for all your help Sid -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Friday, July 17, 2009 2:55 PM To: Sharma, Sid Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Idle in transaction On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote: I'm sure it is outdated. When I suggested a new application I should have said a new module within an existing application. In other words, the db pre-exists. We are rewriting our entire application(s) and moving our database to DB2 and Oracle starting pretty soon. So postgres is end of life here and so there is no stomach or interest to upgrade. But you're developing new code targeted to Pg? That's bad. It means your client program is starting a transaction and leaving it running without doing anything with it. This is an issue with the way the client is programmed, or with the client drivers, not with the server. The server is doing what it's told. The reason this is bad is that PG can't properly complete maintenance if there are transactions that are left open constantly. Eventually your DB will fill up the entire disk with old data that can't be cleaned up. The module/application is read-only and so only does SELECTs. That doesn't matter if any _other_ module is performing UPDATEs (or inserts and deletes) to the tables your module operates on. Your module's open transactions may still prevent PostgreSQL from reclaiming space, because it needs to keep the old versions of tuples around. (I'm struggling, right now, to explain why this is so - if this is so - for READ COMMITTED transactions, though it certainly is for SERIALIZABLE transactions. For READ COMMITTED the transaction's own uncommitted changes can't be discarded vacuumed, but these are presumably read-only transactions anyway - and they don't need to be able to see any old tuples other than any they may have inserted/modified. Will open READ COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER, sure, but plain VACUUM?) Your uncommitted transactions will also make it very hard to make schema changes such as adding or altering columns to your tables. If your module is the only one active on these tables, and it's only doing read activity, then it doesn't really matter very much that the transactions are left open. It's kind of ugly, though. So there is no imminent danger then of catastrophic failure such as memory full or disk full or cpu spikes or db slowness given my application only does queries? I'm trying to figure out whether I should chill this weekend on the beach or kiss that good-bye and work this issue? :-) I am setting auto-commit to on, so
